//
you're reading...
Oracle Database backup and recovery, Oracle RMAN

Use RMAN to restore the (lost or damaged) control file

Control File Recovery

The loss of the control file divided into two situations, one is one of the control file is damaged or missed, the other is all the control files are missing. Based on the first case, only the good control file copy in case of damage or loss of the control file path. In the second case you need to restore or rebuild the control file manually via the control file backup information.

Analyzing single control file loss and recovery

The database can not shut down because of the need to update the control file SCN number when it is closed

sys@TESTDB01> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/oradata/testdb/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

The database must be forced to shut down

sys@TEST0924> shutdown abort;
ORACLE instance shut down.
  1. Start the database control file fails validation report, check the alert log file
  2. 		sys@TESTDB01> startup
    		ORACLE instance started.
    		Total System Global Area 3340451840 bytes
    		Fixed Size 2232960 bytes
    		Variable Size 1543507328 bytes
    		Database Buffers 1778384896 bytes
    		Redo Buffers 16326656 bytes
    		ORA-00205: error in identifying control file, check alert log for more info
    		
  3. View alarm logs, report suggesting no control01.ctl
  4. 		sys@TESTDB01> startup
    		ALTER DATABASE MOUNT
    		ORA-00210: cannot open the specified control file
    		ORA-00202: control file: '/data01/oradata/testdb/control01.ctl'
    		ORA-27037: unable to obtain file status
    		Linux-x86_64 Error: 2: No such file or directory
    		Additional information: 3
    		ORA-205 signalled during: ALTER DATABASE MOUNT...
    		
  5. From the above information we can conclude that due to the loss of control led to a database file can not be normal startup and shutdown, here we have to do is restore the control file to do, because we know the control files with duplicate multipath property, 11g default there are two control files. Log in now seeing is a control file is lost, can not find, we can control the file 2 to restore 1.

Based on the normal control file restore corrupted control files

  1. Check the control file path exists
  2. 		sys@TESTDB01> show parameter control_file
    		NAME TYPE
    		------------------------------------ ---------------------------------
    		VALUE
    		------------------------------
    		control_file_record_keep_time integer
    		7
    		control_files string
    		/data01/oradata/testdb/control01.ctl, 
    		/data01/oradata/testdb/control02.ctl
    		

    We can see from the above, there are two sets of database control file that is stored in one of the control file

    /data01/oradata/testdb/control01.ctl, in addition a control file exists /data01/oradata/testdb/control02.ctl

    From just above information, we can get it is control01.ctl control file is lost cause database failure.

  3. The control file is not present under examination or damaged
  4. 		[oracle@test testdb]$ ll -ls /data01/oradata/testdb/
    		example01.dbf 
    		testdb02.dbf 
    		sysaux01.dbf 
    		temp01.dbf 
    		undotbs02.dbf
    		testdb01.dbf 
    		rman_cbt.log 
    		system01.dbf 
    		undotbs01.dbf 
    		users01.dbf
    		[oracle@test testdb]$ ll -ls /data01/oradata/testdb/control01.ctl
    		ls: /data01/oradata/testdb/control01.ctl: No such file or directory
    		

    Look at the control file 2 exists.

    		[oracle@test testdb] $ ll -ls /data01/oradata/fast_recovery_area/testdb/control02.ctl
    		/data01/oradata/fast_recovery_area/testdb/control02.ctl
    		

    controlfile2 still exists, so that we can restore controlfile1 by controlfile2.

  5. Close the database
  6. 		sys@TESTDB> shutdown abort
    		ORACLE instance shut down.
    		
  7. Recover corrupted lost control file
  8. 		[oracle@test testdb]$ cp /data01/oradata/fast_recovery_area/testdb/control02.ctl /data01/oradata/oradata/testdb/control01.ctl
    		
  9. Start the database
  10. 		
    		sys@TESTDB> startup
    		ORACLE instance started.
    		Total System Global Area 3375151840 bytes
    		Fixed Size 2241760 bytes
    		Variable Size 1546237328 byte
    		Database Buffers 1795284896 bytes
    		Redo Buffers 16323756 bytes
    		Database mounted.
    		Database opened.
    		

Lost all control files

[oracle@test testdb]$ rm /data01/oradata/fast_recovery_area/testdb/control02.ctl
[oracle@test testdb]$ rm /data01/oradata/testdb/control01.ctl
 

The database can not shut down because of the need to update the control file scn number when it is closed.

sys@TESTDB> shutdowm immediate;
SP2-0734: unknown command beginning "shutdowm i..." - rest of line ignored.
sys@TESTDB> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/oradata/testdb/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

You must be forced to shut down the database

sys@TESTDB> shutdown abort;
ORACLE instance shut down.

Start the database control file fails validation report, check the alarm log file

sys@TESTDB> startup
ORACLE instance started.
Total System Global Area 3375151840 bytes
Fixed Size 2241760 bytes
Variable Size 1546237328 byte
Database Buffers 1795284896 bytes
Redo Buffers 16323756 bytes
ORA-00205: error in identifying control file, check alert log for more info

Check the alarm log, both control files are found, and lost:

Fri July 06 20:34:37 2012
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/oradata/fast_recovery_area/testdb/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/oradata/testdb/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri July 06 20:34:43 2012
Checker run found 1 new persistent data failures
Time drift detected. Please check VKTM trace file for more details.

Using RMAN to restore control file:

  1. Forced to start the database to nomount state
  2. 		sys@TESTDB> startup force nomount;
    		ORACLE instance started.
    		Total System Global Area 3375151840 bytes
    		Fixed Size 2241760 bytes
    		Variable Size 1546237328 byte
    		Database Buffers 1795284896 bytes
    		Redo Buffers 16323756 bytes
    		
  3. Open another window, connection rman, execute restore control file recovery
  4. 		[oracle@rtest ~]$ rman target /
    		Recovery Manager: Release 11.2.0.2.0 - Production on Fri July 21:36:45 2012
    		Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    		connected to target database: TESTDB (not mounted)
     
    		RMAN> restore controlfile from autobackup;
    		Starting restore at 2012-07-06:22:01:03
    		using channel ORA_DISK_1
    		recovery area destination: /redo01/oradata/fast_recovery_area
    		database name (or database unique name) used for search: TEST0924
    		channel ORA_DISK_1: AUTOBACKUP /backup01/rman/testdb/2012_07_06/Ora_db_TESTDB_42559_1_560244593.bkp found in the recovery area
    		AUTOBACKUP search with format "%F" not attempted because DBID was not set
    		channel ORA_DISK_1: restoring control file from AUTOBACKUP /backup01/rman/testdb/2012_07_06/Ora_db_TESTDB_42559_1_560244593.bkp
    		channel ORA_DISK_1: control file restore from AUTOBACKUP complete
    		output file name=/data01/oradata/testdb/control01.ctl
    		output file name=/data01/oradata/testdb/control02.ctl
    		Finished restore at 2012-07-06:21:53:20
    		
  5. Load database
  6. 		SQL>alter database mount;
    		Database altered.
    		
  7. Resume database
  8. 		RMAN> recover database;
    		Starting recover at 2012-07-06:22:32:18
    		Starting implicit crosscheck backup at 2012-07-06:22:32:19
    		allocated channel: ORA_DISK_1
    		channel ORA_DISK_1: SID=231 device type=DISK
    		Crosschecked 2 objects
    		Finished implicit crosscheck backup at 2012-07-06:22:32:21
    		Starting implicit crosscheck copy at 2012-07-06:22:32:21
    		using channel ORA_DISK_1
    		Crosschecked 1 objects
    		Finished implicit crosscheck copy at 2012-07-06:22:32:22
    		searching for all files in the recovery area
    		cataloging files...
    		cataloging done
    		List of Cataloged Files
    		=======================
    		File Name: /backup01/rman/testdb/2012_07_06/Ora_db_TESTDB_42639_1_660242545.bkp
    		File Name: /backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_251_851279004_.arc
    		File Name: /backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_252_851279004_.arc
    		File Name: /backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_253_851279004_.arc
    		File Name: /backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_254_851279004_.arc
    		File Name: /backup01/rman/testdb/2012_07_06/Ora_db_TESTDB_42639_1_660242547.bkp
    		using channel ORA_DISK_1
    		starting media recovery
    		archived log for thread 1 with sequence 251 is already on disk as file 
    		/backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_251_851279004_.arc
    		archived log for thread 1 with sequence 252 is already on disk as file
    		/backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_252_851279004_.arc
    		archived log for thread 1 with sequence 253 is already on disk as file 
    		/backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_253_851279004_.arc
    		archived log for thread 1 with sequence 254 is already on disk as file 
    		/backup01/rman/testdb/archivelog/2012_07_06/arch_testdb_1_254_851279004_.arc
    		archived log for thread 1 with sequence 255 is already on disk as file /redo01/oradata/testdb/redo03.log
    		archived log file name=/logs01/oradata/testdb/archivelog/2012_07_06/arch_testdb_1_251_851279004_.arc thread=1 sequence=251
    		archived log file name=/logs01/oradata/testdb/archivelog/2012_07_06/arch_testdb_1_252_851279004_.arc thread=1 sequence=252
    		archived log file name=/logs01/oradata/testdb/archivelog/2012_07_06/arch_testdb_1_253_851279004_.arc thread=1 sequence=253
    		archived log file name=/logs01/oradata/testdb/archivelog/2012_07_06/arch_testdb_1_254_851279004_.arc thread=1 sequence=254
    		archived log file name=/redo01/oradata/testdb/redo03.log thread=1 sequence=255
    		media recovery complete, elapsed time: 00:00:45
    		Finished recover at 2012-07-06:22:33:25
    		
  9. Open Database
  10. 	
    		RMAN> alter database open resetlogs;
    		database opened
                    
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: