Summary: in this tutorial, you will learn how to restore the Control file if the Control file got corrupted or deleted by someone's Oracle Database.
Remainders:
In this case, we should have a current backup of the control file previously.
Likewise below!
Task1:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/backup/ramesh/control.bkp';
Database altered.
**********************************************************************************************************************************************************************
Step1:
Find the control file location using the below commands
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TEST/controlfile/o1_mf_l79fd0g5_.ctl
/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_l79fd0h3_.ctl
**********************************************************************************************************************************************************************
Step2:
This work log is about deleting the control files
[oracle@oracle ramesh]$ cd /u01/app/oracle/oradata/TEST/controlfile/
[oracle@oracle controlfile]$
[oracle@oracle controlfile]$ ll
total 9808
-rw-r-----. 1 oracle oinstall 10043392 May 29 20:07 o1_mf_l79fd0g5_.ctl
[oracle@oracle controlfile]$
[oracle@oracle controlfile]$
[oracle@oracle controlfile]$
[oracle@oracle controlfile]$ rm o1_mf_l79fd0g5_.ctl
[oracle@oracle controlfile]$
[oracle@oracle controlfile]$ ll
total 0
[oracle@oracle controlfile]$
[oracle@oracle controlfile]$ cd /u01/app/oracle/fast_recovery_area/TEST/controlfile/
[oracle@oracle controlfile]$ ll
total 9808
-rw-r-----. 1 oracle oinstall 10043392 May 29 20:07 o1_mf_l79fd0h3_.ctl
[oracle@oracle controlfile]$ rm o1_mf_l79fd0h3_.ctl
[oracle@oracle controlfile]
**********************************************************************************************************************************************************************
Step3:SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/TEST/controlfile/o1_mf_l79fd0g5_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1677721600 bytes
Fixed Size 2925120 bytes
Variable Size 1073745344 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
SQL>
Step4:
This is the way to connect the RMAN Utility
[oracle@oracle controlfile]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon May 29 20:10:52 2023
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (not mounted)
RMAN>
*********************************************************************************************************************************************************************
Step6:
Using the below command to restore the control files
RMAN> RESTORE CONTROLFILE FROM '/u01/backup/ramesh/control.bkp';
Starting restore at 29-MAY-23
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/TEST/controlfile/o1_mf_l79fd0g5_.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_l79fd0h3_.ctl
Finished restore at 29-MAY-23
Step7:
Once restore is completed then apply the Archivelog using the below commands
RMAN> recover database;
Starting recover at 29-MAY-23
Starting implicit crosscheck backup at 29-MAY-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 29-MAY-23
Starting implicit crosscheck copy at 29-MAY-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-MAY-23
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_l79fd3h9_.log
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_l79fd3h9_.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-MAY-23
**********************************************************************************************************************************************************************
Step8:
Once done the recovery needs to open the database reset logs
Resetlogs nothing but it applies the log starting from the first number!
RMAN> alter database open resetlogs;
Statement processed
Step 9: We are fixed the issues!
[oracle@oracle controlfile]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon May 29 20:28:47 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL>
SQL> select name,open_mode from V$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TEST/controlfile/o1_mf_l79fd0g5_.ctl
/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_l79fd0h3_.ctl
1 Comments
RESTORE CONTROLFILE FROM '/u01/backup/ramesh/control.bkp'; you should also explain how you identified this control file backup location
ReplyDelete