Monday, April 4, 2011

How to recreate the control file in oracle database 10g?

RECREATING THE CONTROL FILE:
----------------------------

In 10g, you can create the control file. In addition, you can get
Oracle to create the script for you. To do this, perform the following steps:

1. With the database mounted or open, issue the following commands:

SQL> alter database backup controlfile to trace;
or
SQL> alter database backup controlfile to trace as '/d04/admin/stlbas/udump/contral_04042011_trace.trc' ;
SQL> exit

2. A trace file will have been generated in your 'user_dump_dest'.
User_dump_dest is an init.ora parameter, and can be found by
issuing:

SQL> show parameter user_dump_dest

like
SQL> show parameter user

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest string /d02/admin/stlbas/udump
SQL>


The easiest way to locate the correct trace is to look at its date. A
file will exist with the current date and time. The naming convention
for these files is operating system specific.

3. Once the file is located, search through the file for the word "CONTROL"

You should find:

# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
ETC.ETC.

4. Copy this trace file to some location and rename it to end it ".sql",
for this example, it is called "recr_con.sql".

5. Edit the "recr_con.sql" deleting the trace header information. Then
Change as you want .

6. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).

7. Take a full database backup at this time.

8. Remove the current control files. It is essential to remove all control
files, otherwise, you will receive an error. In addition, you CANNOT
REUSE the control file, since the size of the control file will increase
when you increase MAXDATAFILES.

9. Create the controlfile within SQLDBA:

SQL> connect internal [if using sqldba or svrmgrl]
or
connect / as sysdba [if using sqlplus]
SQL> @recr_con.sql
SQL> alter database open noresetlogs;

If you receive a "Statement processed" message, then your database is
now back up and running with your change value. It is recommended
to shutdown at this time and take a full backup.

No comments: