Oracle backup control file-based recovery

Normally, when the current control file is lost or the current control file is inconsistent with the control file to be restored, we need to re-create a control file or use unsing

Backup controlfile to restore the control file. To put it simply, as long as the backup control file is inconsistent with the current control file to restore the database, you need to use the unsing

Backup controlfile method. Once this method is used, you must use the resetlgos option to open the database.

I. Considerations for restoring backup control files (whether or not the recovery directory catalog is used)

1. Even if no data file needs to be restored, the recover command must be used in the unsing backup controlfile mode.

2. Whether the backup control file is used for point-in-time recovery or full recovery, open resetlogs must be used to open the database.

3. If online logs cannot be accessed, they must be used before they are completely restored to the earliest SCN in the online log file. This is because RMAN does not back up online log files.

4. During recovery, RMAN automatically searches for online logs and archive logs not recorded in the RMAN storage warehouse for recovery.

5. RMAN automatically searches for valid archived and online logs based on the archiving location in the initialization parameter file and the online log information of the control file. Use unsing backup controlfile

During recovery, once the archiving destination and archive format change, or you add a new online log member, you will receive a RMAN-06054 error message.

This article mainly uses hot standby mode to complete the demonstration

Ii. demonstrate the use of unsing backup controlfile

1. All control files are lost (changes occur after backup of control files)

--> First, use the hot backup script for backup.

Sys @ SYBO2SZ> get db_hot_bak. SQL

1 set feedback off heading off verify off

2 set pagesize 0 linesize 200

3 define dir = '/u02/database/SYBO2SZ/backup/hotbak'

4 define script = '/tmp/tmphotbak. SQL'

5 spool & script

6 select 'ho cp' | name | '& dir' from v $ datafile;

7 spool off

8. alter database begin backup;

9 start & script

10 alter database end backup;

11 alter database backup controlfile to '& dir/contlbak. ctl' reuse;

12 create pfile = '& dir/initSYBO2SZ. ora' from spfile;

13 * set feedback on heading on verify on pagesize 100

Sys @ SYBO2SZ> @ db_hot_bak

Sys @ SYBO2SZ> show parameter control_files

NAME TYPE VALUE

-----------------------------------------------------------------------------

Control_files string/u02/database/SYBO2SZ/controlf

/Cntl1SYBO2SZ. ctl,/u02/databa

Se/SYBO2SZ/controlf/cntl2SYBO2

SZ. ctl,/u02/database/SYBO2SZ/

Controlf/cntl3SYBO2SZ. ctl

--> Add a new tablespace to the database. The control file is different from the control file backed up earlier.

Sys @ SYBO2SZ> create tablespace tbs datafile '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf' size 10 m autoextend on;

Tablespace created.

--> Add an object to a database

Sys @ SYBO2SZ> create table tb_emp tablespace tbs as select * from scott. emp;

Table created.

Sys @ SYBO2SZ> select count (*) from tb_emp;

COUNT (*)

----------

14

Sys @ SYBO2SZ> select * from v $ log;

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM

-------------------------------------------------------------------------------------------

3 1 116 20971520 2 no current 1078066 08-SEP-12

4 1 115 20971520 2 yes inactive 1063428 08-SEP-12

Sys @ SYBO2SZ> select to_char (sysdate, 'yyyymmdd hh24: mi: ss') from dual;

TO_CHAR (SYSDATE,

-----------------

16:30:41 20120908

--> Switch logs

Sys @ SYBO2SZ> alter system archive log current;

System altered.

--> Delete some records for post-recovery Verification

Sys @ SYBO2SZ> delete from tb_emp where deptno = 10;

3 rows deleted.

Sys @ SYBO2SZ> commit;

Commit complete.

Sys @ SYBO2SZ> alter system archive log current;

System altered.

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive

Total 143 M

-Rw-r ----- 1 oracle oinstall 15 M 2012-09-08 16:20 arch_792094299_2017115.arc

-Rw-r ----- 1 oracle oinstall 236 K 2012-09-08 arch_792094299_2017116.arc

-Rw-r ----- 1 oracle oinstall 9.0 K 2012-09-08 arch_792094299_2017117.arc

--> Abnormal Shutdown

Sys @ SYBO2SZ> shutdown abort;

ORACLE instance shut down.

--> Simulate the loss of all control files

Sys @ SYBO2SZ> ho rm-rf/u02/database/SYBO2SZ/controlf /*

Sys @ SYBO2SZ> ho ls/u02/database/SYBO2SZ/controlf/

--> A ORA-00205 error is received after startup

Sys @ SYBO2SZ> startup

ORACLE instance started.

Total System Global Area 599785472 bytes

Fixed Size 2074568 bytes

Variable Size 276826168 bytes

Database Buffers 314572800 bytes

Redo Buffers 6311936 bytes

ORA-00205: error in identifying control file, check alert log for more info

Sys @ SYBO2SZ> select instance_name, status from v $ instance;

INSTANCE_NAME STATUS

----------------------------

SYBO2SZ STARTED

Sys @ SYBO2SZ> select name, open_mode from v $ database;

Select name, open_mode from v $ database

*

ERROR at line 1:

ORA-01507: database not mounted

--> Restore control files

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/contlbak. ctl/u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ. ctl

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/contlbak. ctl/u02/database/SYBO2SZ/controlf/cntl2SYBO2SZ. ctl

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/contlbak. ctl/u02/database/SYBO2SZ/controlf/cntl3SYBO2SZ. ctl

--> Mount Database

Sys @ SYBO2SZ> alter database mount;

Database altered.

--> Because only the control file is lost, we only restore the control file.

--> Restore the database, prompting you to use the backup controlfile option because the control file has changed after BACKUP.

Sys @ SYBO2SZ> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the backup controlfile option must be done

--> The system prompts you to apply archive logs to 116.

Sys @ SYBO2SZ> recover database using backup controlfile;

ORA-00279: change 1078785 generated at 09/08/2012 16:20:48 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792094299_2017116.arc

ORA-00280: change 1078785 for thread 1 is in sequence #116

--> The following message indicates that an unknown file is added to the control file during media recovery and the file id is 9.

--> It can be inferred that file 9 is recorded in the archive log with a tail number of 116, exactly the same as the time of the archived log viewed earlier.

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

Auto

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile (s) added to control file by media recovery

ORA-01110: data file 9: '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf'

ORA-01112: media recovery not started

--> Use alter database create datafile to recreate a data file

--> The tbs. dbf file name, which is different from the previous file name, is intentionally used here, and has no effect on it. It is compared with renaming the data file.

Sys @ SYBO2SZ> alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs. dbf ';

Database altered.

--> Try to recover again. Archive logs with the number of digits 116 must be used. After auto is input, logs with the tail number of 116,117 are not required.

Sys @ SYBO2SZ> recover database using backup controlfile;

ORA-00279: change 1078817 generated at 09/08/2012 16:29:19 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792094299_2017116.arc

ORA-00280: change 1078817 for thread 1 is in sequence #116

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: change 1078886 generated at 09/08/2012 16:30:52 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792094299_2017117.arc

ORA-00280: change 1078886 for thread 1 is in sequence #117

ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792094299_2017116.arc' no longer needed for this recovery

ORA-00279: change 1078922 generated at 09/08/2012 16:32:22 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792094299_2017118.arc

ORA-00280: change 1078922 for thread 1 is in sequence #118

ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792094299_2017117.arc' no longer needed for this recovery

--> The system prompts that no archived logs with the ending number of 118 are found, and 118 is not archived. Therefore, online logs are used.

ORA-00308: cannot open archived log '/u02/database/SYBO2SZ/archive/arch_792094299_2017118.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

--> Restore again

Sys @ SYBO2SZ> recover database using backup controlfile;

ORA-00279: change 1078922 generated at 09/08/2012 16:32:22 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792094299_2017118.arc

ORA-00280: change 1078922 for thread 1 is in sequence #118

--> Specify the redo log directly, and the media is restored successfully.

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

/U02/database/SYBO2SZ/redolog/log3aSYBO2SZ. log

Log applied.

Media recovery complete.

--> Use the RESETLOGS option to open the database.

Sys @ SYBO2SZ> alter database open;

Alter database open

*

ERROR at line 1:

The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Sys @ SYBO2SZ> alter database open resetlogs;

Database altered.

--> Verify the total number of records of the new object, which is exactly the same as the number of deleted records 11

Sys @ SYBO2SZ> select count (*) from tb_emp;

COUNT (*)

----------

11

2. Delete tablespaces to simulate changes in control files

The following example is not the best processing method. Here we only demonstrate the usage of unsing backup controlfile and use Incomplete recovery. For a single data file and

Sys @ SYBO2SZ> archive log list; --> check the archiving status. log sequence starts from 1.

Database log mode Archive Mode

Automatic archival Enabled

Archive destination/u02/database/SYBO2SZ/archive/

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

Sys @ SYBO2SZ> alter system switch logfile;

--> First, Hot Standby Database

Sys @ SYBO2SZ> @ db_hot_bak

--> The new incarnation 793471702 has been generated. We can see that at the same time, an archived log of the last incarnation with a tail number of 118 is generated.

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive

Total 54 M

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-09-08 arch_792094299_2017118.arc

-Rw-r ----- 1 oracle oinstall 9.0 K 2012-09-08 arch_792094299_2017117.arc

-Rw-r ----- 1 oracle oinstall 43 K 2012-09-08 arch_791_1702_1_1.arc

--> Delete the tablespace to update the control file.

Sys @ SYBO2SZ> drop tablespace tbs including contents and datafiles;

Tablespace dropped.

Sys @ SYBO2SZ> alter system switch logfile;

System altered.

--> After switching logs, an archive log with the ending number of 2 is added.

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive

Total 54 M

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-09-08 arch_792094299_2017118.arc

-Rw-r ----- 1 oracle oinstall 9.0 K 2012-09-08 arch_792094299_2017117.arc

-Rw-r ----- 1 oracle oinstall 43 K 2012-09-08 arch_791_1702_1_1.arc

-Rw-r ----- 1 oracle oinstall 50 K 2012-09-08 arch_791091702_4262.arc

Sys @ SYBO2SZ> show parameter background_dump_dest

NAME TYPE VALUE

-----------------------------------------------------------------------------

Background_dump_dest string/u02/database/SYBO2SZ/bdump

--> Query the exact time when the table space is deleted from the alert log file.

Sys @ SYBO2SZ> ho cat-n/u02/database/SYBO2SZ/bdump/alert_SYBO2SZ.log | grep tbs. dbf

6959 '/u02/database/SYBO2SZ/oradata/imp_tbs.dbf'

6962 '/u02/database/SYBO2SZ/oradata/imp_tbs.dbf '...

11014 alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs. dbf'

11016 Completed: alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs. dbf'

11273 Deleted file/u02/database/SYBO2SZ/oradata/tbs. dbf

--> The following shows the time when the tablespace and data file were deleted.

--> The control file is automatically backed up because the automatic backup of the control file in the RMAN configuration is set to ON

--> The backup control file is used to restore the test. Therefore, the automatic backup control file is not considered here.

Sys @ SYBO2SZ> ho more + 11270/u02/database/SYBO2SZ/bdump/alert_SYBO2SZ.log

Sat Sep 8 16:57:56 2012

Drop tablespace tbs including contents and datafiles

Sat Sep 8 16:57:58 2012

Deleted file/u02/database/SYBO2SZ/oradata/tbs. dbf

Starting control autobackup

Control autobackup written to DISK device

Handle '/u02/database/SYBO2SZ/backup/rman/20120907/SYBO2SZ_lev1_201209071410_c-209726751-20120908-05'

Completed: drop tablespace tbs including contents and datafiles

Sys @ SYBO2SZ> shutdown immediate;

--> Restore control files and all data files

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/contlbak. ctl/u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ. ctl

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/contlbak. ctl/u02/database/SYBO2SZ/controlf/cntl2SYBO2SZ. ctl

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/contlbak. ctl/u02/database/SYBO2SZ/controlf/cntl3SYBO2SZ. ctl

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/*. dbf/u02/database/SYBO2SZ/oradata /.

Sys @ SYBO2SZ> ho cp/u02/database/SYBO2SZ/backup/hotbak/undotbs */u02/database/SYBO2SZ/undo /.

Sys @ SYBO2SZ> startup mount;

ORACLE instance started.

--> Use the using backup controlfile option and set the time point to restore the database.

Sys @ SYBO2SZ> recover database until time '2017-09-08: 16: 57: 56' using backup controlfile;

ORA-00279: change 1079138 generated at 09/08/2012 16:55:09 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_793471702_4242.arc

ORA-00280: change 1079138 for thread 1 is in sequence #2

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}

Auto

Log applied.

Media recovery complete.

Sys @ SYBO2SZ> alter database open resetlogs;

Database altered.

--> Verify the number of records in the restored object

Sys @ SYBO2SZ> select count (*) from tb_emp;

COUNT (*)

----------

11

Iii. Summary:

1. When all the control files are lost or damaged and the control files are not backed up in a timely manner, unsing backup controlfile must be used to restore them.

2. Use unsing backup controlfile to restore the database. Use resetlogs to open the database.

3. Configure RMAN backup as much as possible to enable automatic backup of Control Files

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html