recover database using backup controlfile 使用场景

总结：

recover database using backup controlfile --控制文件丢失，使用备份的控制文件来进行恢复的场景

recover database的原理是数据库使用控制文件的scn作为恢复的终点，将数据文件block恢复到控制文件所记录的scn为止。而使用recover database using backup controlfile;实际上是告诉数据库，我要联机日志的最大scn为终点，对数据文件在block级别进行恢复。recover database using backup controlfile until cancel,既可以完全恢复，也可以指定归档日志、联机日志不完全恢复。
 

测试前准备：
在测试前，先在关库情况下对控制文件做冷备

[oracle@localhost ORCL]$ cp control01.ctl control01.ctlbak
[oracle@localhost ORCL]$ ll

开始测试：
1、手动执行检查点

SQL> alter system checkpoint;
使scn写入到控制文件以及数据文件头部

2、使用之前备份的控制文件，重启数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


[oracle@localhost ORCL]$ rm -rf control01.ctl
[oracle@localhost ORCL]$ rm -rf control02.ctl
[oracle@localhost ORCL]$ mv control01.ctlbak control01.ctl
[oracle@localhost ORCL]$ mv control01.ctlbak control02.ctl

SQL> startup 
ORACLE instance started.

Total System Global Area 1577054664 bytes
Fixed Size                  8896968 bytes
Variable Size            1191182336 bytes
Database Buffers          369098752 bytes
Redo Buffers                7876608 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
ORA-01207: file is more recent than control file - old control file
--可以发现，现在数据库已无法启动，提示数据文件scn比控制文件scn要新，控制文件的scn是旧的

3、查看现在控制文件、数据文件scn

控制文件scn

SQL> select CHECKPOINT_CHANGE# from v$datafile;

CHECKPOINT_CHANGE#
------------------
           2464879
           2464879
           2464879
           2464879

数据文件头部scn 

SQL> select CHECKPOINT_CHANGE# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           2466508
           2466508
           2466508
           2466508

可以发现控制文件的scn 明显小于数据文件头部scn

4、做recover database恢复

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
ORA-01207: file is more recent than control file - old control file
还是会提示，数据文件比控制文件scn新

5、加参数”using backup controlfile“再恢复

SQL> recover database using backup controlfile;
ORA-00279: change 2464879 generated at 07/16/2021 03:24:19 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/19.3.0/db/dbs/arch1_14_1077941831.dbf
ORA-00280: change 2464879 for thread 1 is in sequence #14


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
--输入上面提示的归档日志，但是该日志还未归档，所以报错
/u01/app/oracle/product/19.3.0/db/dbs/arch1_14_1077941831.dbf
ORA-00308: cannot open archived log
'/u01/app/oracle/product/19.3.0/db/dbs/arch1_14_1077941831.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


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

--输入当前状态下current 日志，恢复完毕
/u01/app/oracle/oradata/ORCL/redo02.log
Log applied.
Media recovery complete.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.