Control file recovery scenarios
What is a control file?
An Oracle control file is a binary file necessary for the database to start and operate successfully. oracle scans the content of the control file in the mount state for the physical location of datafile and redo log files. Oracle continuously updates the control file during the functionality of the database, so it must be available for writing whenever the database is open.If for some reason the control file is not accessible, then the database cannot function properly. Each control file is associated with only one Oracle database.
How to Multiplex the Oracle Control file
Step 1: select the existing control files
|
1 2 3 4 5 6 7 8 9 |
SYS> SYS> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/TESTDB/control01.ctl /u01/TESTDB/control02.ctl /u01/TESTDB/control03.ctl |
Step 2: shutdown the database
|
1 2 3 4 5 6 7 |
SYS> SYS> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS> exit |
Step 3: copy the existing control file to a new location using cp command
|
1 2 3 |
[oracle@stagdb dbs]$ [oracle@stagdb dbs]$ cp /u01/TESTDB/control01.ctl /u01/TESTDB/control04.ctl |
Step 4: Edit the control_files line in pfile
*.control_files=’/u01/TESTDB/control01.ctl’,’/u01/TESTDB/control02.ctl’,’/u01/TESTDB/control03.ctl’,’/u01/TESTDB/control04.ctl’
Step 5: Create a spfile from pfile
|
1 2 3 4 5 |
SYS> SYS> create spfile from pfile; File created. |
Step 6: Start the database
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SYS> SYS> startup; ORACLE instance started. Total System Global Area 7482626048 bytes Fixed Size 2242912 bytes Variable Size 301991584 bytes Database Buffers 7163871232 bytes Redo Buffers 14520320 bytes Database mounted. Database opened. |
Step 7: Confirm the new control file by querying v$controlfile.
|
1 2 3 4 5 6 7 8 9 10 |
SYS> SYS> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/TESTDB/control01.ctl /u01/TESTDB/control02.ctl /u01/TESTDB/control03.ctl /u01/TESTDB/control04.ctl |
How to remove a control file
Steps for removing a control file is same as the one we followed for multiplexing but on a reverse order.
Step 1: Shut down the database.
Step 2: Edit the control_files section in pfile by removing an entry to control file location.
Step 3: Create an spfile from pfile.
Step 4: Startup the database.
Step 5: Query v$control_file for confirmation.
How to restore a control file from trace file
Step 1: Create a backup control file
|
1 2 3 |
SYS> alter database backup controlfile to trace; Database altered. |
Step 2: Rename the trace file to something with an extension “.sql”
SYS> !cp /u01/app/diag/rdbms/testdb/TESTDB/trace/TESTDB_ora_27204.trc /home/oracle/ctrl.sql
Step 3: Edit the ctrl.sql file for the minimal controlfile information.
cat /home/oracle/ctrl.sql
CREATE CONTROLFILE REUSE DATABASE “MYDB” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/mydb/redo01.log’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘/u01/mydb/redo02.log’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘/u01/mydb/redo03.log’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/u01/mydb/system01.dbf’,
‘/u01/mydb/sysaux01.dbf’,
‘/u01/mydb/undotbs01.dbf’,
‘/u01/mydb/users01.dbf’
CHARACTER SET WE8MSWIN1252
;
Step 4: startup the database with nomount option.
|
1 2 3 4 5 6 7 8 9 10 |
SYS> SYS> startup nomount; ORACLE instance started. Total System Global Area 3774713856 bytes Fixed Size 2234000 bytes Variable Size 754977136 bytes Database Buffers 3003121664 bytes Redo Buffers |
Step 5: Execute the ctrl.sql file
|
1 2 3 4 5 6 7 8 9 |
SYS> SYS> @/home/oracle/ctrl.sql Control file created. Media recovery complete. Database altered. |
Step 6: Confirm with v$control_file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SYS> SYS> select status from v$instance; STATUS ------------ OPEN SYS> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/mydb/control01.ctl /u01/mydb/control02.ctl |
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.