Recovering control files from corruption
Controlfile Corruption:
---------------------------------------------
Scenario 1: One of the control files is corrupted
--------------------
Starting database with one of the control file corrupted:
------------------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 2928728 bytes
Variable Size 452988840 bytes
Database Buffers 272629760 bytes
Redo Buffers 5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info
Look into the alertlog for errors. The below errors in the alertlog show the controlfile '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' is corrupted
Error in the alert log file:
--------------------------------
ALTER DATABASE MOUNT
Sun Jan 07 12:01:38 2018
Using default pga_aggregate_limit of 2048 MB
Sun Jan 07 12:01:38 2018
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/control02.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...
Identify the control files:
-----------------------------
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/orcl/contro
l02.ctl
Solution: As the alert log shows that control file control02.ctl is corrupted.Copying the good control file(control01.ctl) to corrupted control file(control02.ctl) location will solve the problem and then mount and open database.
Copying control file:
-------------------------
[oracle@pavanorcl101 orcl]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
--------------------------------------------------------------------------------------------------------------------------
Scenario2: All controlfiles are corrupted
-------------------
Starting database with all control files corrupted:
-----------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 2928728 bytes
Variable Size 452988840 bytes
Database Buffers 272629760 bytes
Redo Buffers 5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info
Identify the control files:
-------------------------------
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/orcl/contro
l02.ctl
Error in the alert log file:
--------------------------------
ALTER DATABASE MOUNT
Sun Jan 07 13:27:13 2018
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/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: '/u01/app/oracle/oradata/orcl/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...
Solution: Connect to recovery manager and issue restore control file along with path where the backed up control file is located. we have given the entire path because with mounting the database RMAN doesn't know of the backup location. Then restore and reocover the database.
[oracle@pavanorcl101 2017_12_14]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 7 13:29:22 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
Restoring control file from backup:
------------------------------------------
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_14/o1_mf_ncsnf_TAG20171214T121656_f35dlz61_.bkp';
Starting restore at 07-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 07-JAN-18
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Now we can open the database
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/07/2018 13:37:16
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
We just restored the control files. In order to open the database, we have to use the RESETLOGS option.
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/07/2018 13:37:30
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
The above open command throws an error because the control file restored from the backup is older than the datafiles. so, we need to restore the database
Restoring database:
-----------------------
RMAN> restore database;
Starting restore at 07-JAN-18
Starting implicit crosscheck backup at 07-JAN-18
allocated channel: ORA_DISK_1
Recovering database:
--------------------------
RMAN> recover database;
Starting recover at 07-JAN-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 59 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_14/o1_mf_1_59_f35dm0t4_.arc
RMAN> alter database open resetlogs;
Statement processed
---------------------------------------------
Scenario 1: One of the control files is corrupted
--------------------
Starting database with one of the control file corrupted:
------------------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 2928728 bytes
Variable Size 452988840 bytes
Database Buffers 272629760 bytes
Redo Buffers 5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info
Look into the alertlog for errors. The below errors in the alertlog show the controlfile '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' is corrupted
Error in the alert log file:
--------------------------------
ALTER DATABASE MOUNT
Sun Jan 07 12:01:38 2018
Using default pga_aggregate_limit of 2048 MB
Sun Jan 07 12:01:38 2018
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/control02.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...
Identify the control files:
-----------------------------
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/orcl/contro
l02.ctl
Solution: As the alert log shows that control file control02.ctl is corrupted.Copying the good control file(control01.ctl) to corrupted control file(control02.ctl) location will solve the problem and then mount and open database.
Copying control file:
-------------------------
[oracle@pavanorcl101 orcl]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
--------------------------------------------------------------------------------------------------------------------------
Scenario2: All controlfiles are corrupted
-------------------
Starting database with all control files corrupted:
-----------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 2928728 bytes
Variable Size 452988840 bytes
Database Buffers 272629760 bytes
Redo Buffers 5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info
Identify the control files:
-------------------------------
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/orcl/contro
l02.ctl
Error in the alert log file:
--------------------------------
ALTER DATABASE MOUNT
Sun Jan 07 13:27:13 2018
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/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: '/u01/app/oracle/oradata/orcl/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...
Solution: Connect to recovery manager and issue restore control file along with path where the backed up control file is located. we have given the entire path because with mounting the database RMAN doesn't know of the backup location. Then restore and reocover the database.
[oracle@pavanorcl101 2017_12_14]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 7 13:29:22 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
Restoring control file from backup:
------------------------------------------
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_14/o1_mf_ncsnf_TAG20171214T121656_f35dlz61_.bkp';
Starting restore at 07-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 07-JAN-18
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Now we can open the database
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/07/2018 13:37:16
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
We just restored the control files. In order to open the database, we have to use the RESETLOGS option.
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/07/2018 13:37:30
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
The above open command throws an error because the control file restored from the backup is older than the datafiles. so, we need to restore the database
Restoring database:
-----------------------
RMAN> restore database;
Starting restore at 07-JAN-18
Starting implicit crosscheck backup at 07-JAN-18
allocated channel: ORA_DISK_1
Recovering database:
--------------------------
RMAN> recover database;
Starting recover at 07-JAN-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 59 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_14/o1_mf_1_59_f35dm0t4_.arc
RMAN> alter database open resetlogs;
Statement processed
Comments
Post a Comment