In this post we learn how to recover from a lost control file.
# First lets take a database backup :
# Lets verify our version of database and environment
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:10:11 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@node2 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)
# Now lets take a backup
[oracle@node2 ~]$ rman target/
Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:11:42 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1644501746)
RMAN> backup database;
Starting backup at 22-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-23
channel ORA_DISK_1: finished piece 1 at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbbz9n_.bkp tag=TAG20230522T231150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-23
channel ORA_DISK_1: finished piece 1 at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbglpr_.bkp tag=TAG20230522T231150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-23
channel ORA_DISK_1: finished piece 1 at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbhp0y_.bkp tag=TAG20230522T231150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 22-MAY-23
Starting Control File and SPFILE Autobackup at 22-MAY-23
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-23
# Now after deleting any of your control file, try to restart the database :
SQL> startup force
ORACLE instance started.
Total System Global Area 1073740680 bytes
Fixed Size 9694088 bytes
Variable Size 377487360 bytes
Database Buffers 683671552 bytes
Redo Buffers 2887680 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@node2 ~]$ rman target/
Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:20:11 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp';
Starting restore at 22-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=422 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 22-MAY-23
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> recover database ;
Starting recover at 22-MAY-23
Starting implicit crosscheck backup at 22-MAY-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=430 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 22-MAY-23
Starting implicit crosscheck copy at 22-MAY-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-MAY-23
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/oradata/ORCL/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-MAY-23
RMAN> exit
Recovery Manager complete.
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:20:51 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
No comments:
Post a Comment