Description:-
In this article we are going to see how to restore a loss of controlfile using autobackup.
Let’s start the Demo:-
Steps to restore a loss of controlfile using autobackup :-
Step 1:- Check the RMAN configuration and controlfile autobackup feature is ON.
[oracle@18c PROD18C]$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 15 02:09:30 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD18C (DBID=3984767297) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name PROD18C are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/snapcf_prod18c.f'; # default
Step 2:- Simulate a failure when the database is running
SQL> select open_mode,name from v$database; OPEN_MODE NAME -------------------- --------- READ WRITE PROD18C SQL> select name from v$controlfile; NAME ----------------------------------------------------- /u01/app/oracle/oradata/PROD18C/control01.ctl /u01/app/oracle/oradata/PROD18C/control02.ctl [oracle@18c PROD18C]$ cd /u01/app/oracle/oradata/PROD18C/ [oracle@18c PROD18C]$ ls -lrt total 2617276 -rw-r----- 1 oracle oinstall 33562624 Aug 15 01:06 temp01.dbf -rw-r----- 1 oracle oinstall 209715712 Aug 15 01:55 redo02.log -rw-r----- 1 oracle oinstall 209715712 Aug 15 01:56 redo03.log -rw-r----- 1 oracle oinstall 104865792 Aug 15 01:56 dbwrtbs02.dbf -rw-r----- 1 oracle oinstall 5251072 Aug 15 01:56 users01.dbf -rw-r----- 1 oracle oinstall 104865792 Aug 15 01:56 dbwrtbs01.dbf -rw-r----- 1 oracle oinstall 891297792 Aug 15 02:15 system01.dbf -rw-r----- 1 oracle oinstall 597696512 Aug 15 02:17 sysaux01.dbf -rw-r----- 1 oracle oinstall 319823872 Aug 15 02:17 undotbs01.dbf -rw-r----- 1 oracle oinstall 209715712 Aug 15 02:17 redo01.log -rw-r----- 1 oracle oinstall 10600448 Aug 15 02:17 control01.ctl -rw-r----- 1 oracle oinstall 10600448 Aug 15 02:17 control02.ctl [oracle@18c PROD18C]$ rm control* SQL> alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/PROD18C/dbwrtbs03.dbf' size 100m; alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/PROD18C/dbwrtbs03.dbf' size 100m * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/PROD18C/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> select status from v$instance; STATUS ------------ OPEN SQL> shut immediate; ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/PROD18C/control01.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.
Step 3:- Keep the database in NOMOUNT stage and restore the controlfile
SQL> startup nomount; ORACLE instance started. Total System Global Area 1660943808 bytes Fixed Size 8658368 bytes Variable Size 1056964608 bytes Database Buffers 587202560 bytes Redo Buffers 8118272 bytes
Step 4:- Since we are not using a RMAN catalog we need to set the DBID
[oracle@18c PROD18C]$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 00:40:10 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD18C (not mounted) RMAN> set dbid= 3984767297; executing command: SET DBID RMAN> restore controlfile from autobackup; Starting restore at 17-AUG-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK recovery area destination: /u01/fra database name (or database unique name) used for search: PROD18C channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984191518_fq6jt6fy_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180816 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180815 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984191518_fq6jt6fy_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/PROD18C/control01.ctl output file name=/u01/app/oracle/oradata/PROD18C/control02.ctl Finished restore at 17-AUG-18
Step 5:- Mount and recover the database
RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed
Step 6:- Restore a control file from a backup so we need to recover the database
RMAN> recover database; Starting recover at 17-AUG-18 Starting implicit crosscheck backup at 17-AUG-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 device type=DISK Crosschecked 4 objects Finished implicit crosscheck backup at 17-AUG-18 Starting implicit crosscheck copy at 17-AUG-18 using channel ORA_DISK_1 Finished implicit crosscheck copy at 17-AUG-18 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984191518_fq6jt6fy_.bkp File Name: /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcl51bd_.arc using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 1 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcl51bd_.arc archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/PROD18C/redo02.log archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_1_fqcl51bd_.arc thread=1 sequence=1 archived log file name=/u01/app/oracle/oradata/PROD18C/redo02.log thread=1 sequence=2 media recovery complete, elapsed time: 00:00:01 Finished recover at 17-AUG-18
Step 7:- Open the database using resetlogs option
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn’t applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new “incarnation” of the database.
RMAN> alter database open resetlogs; Statement processed
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
