环境准备
源 oracle18：oracle18c-standby 192.168.17.26
目标 oracle18-2：oracle18c-primary 192.168.17.109
实例名：orcl
Oracle版本：18.03
提前准备
SELECT CURRENT_SCN FROM V$DATABASE; alter session set NLS_date_FORMAT=‘yyyy-mm-dd amhh12:mi:ss’; select checkpoint_time from v$datafile_header;
源
SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 2497376 SQL> select dbid,checkpoint_change# from v$database; DBID CHECKPOINT_CHANGE# ---------- ------------------ 1517707395 2485187
目标：
SQL> select dbid,checkpoint_change# from v$database; DBID CHECKPOINT_CHANGE# ---------- ------------------ 1517707395 2407208 SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 0 SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID ---------- ------- ------- ----------------------------------------------------------------- ---------- --------- ---------- 1 ONLINE ONLINE 2407208 13-APR-21 1 3 ONLINE ONLINE 2407208 13-APR-21 1 4 ONLINE ONLINE 2407208 13-APR-21 1 7 ONLINE ONLINE 2407208 13-APR-21 1 SQL> select file#,recover,fuzzy,CHECKPOINT_CHANGE# from v$datafile_header; FILE# REC FUZ CHECKPOINT_CHANGE# ---------- --- --- ------------------ 1 NO YES 2498751 3 NO YES 2498751 4 NO YES 2498751 5 NO 1559612 6 NO 1559612 7 NO YES 2498751 8 NO 1559612 9 NO 1569930 10 NO 1569930 11 NO 1569930 12 NO 1569930 11 rows selected.
alter session set NLS_date_FORMAT='yyyy-mm-dd amhh12:mi:ss'; set linesize 200 col name form a30 select name,SEQUENCE#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from V$ARCHIVED_LOG; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------------------------------ ---------- ------------- --------------------- ------------ --------------------- /u01/app/oracle/product/18.0.0 34 2407208 2021-04-13 pm02:49:29 2447653 2021-04-14 am12:02:04 /db_1/dbs/arch1_34_989926663.d bf /u01/app/oracle/product/18.0.0 35 2447653 2021-04-14 am12:02:04 2485187 2021-04-14 am09:00:35 /db_1/dbs/arch1_35_989926663.d bf /u01/app/oracle/product/18.0.0 36 2485187 2021-04-14 am09:00:35 2498751 2021-04-14 pm02:08:03 /db_1/dbs/arch1_36_989926663.d bf
开始恢复
使用下面的命令把日志传输到目标端：
scp oracle@192.168.17.26:/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_36_989926663.dbf . scp oracle@192.168.17.26:/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_35_989926663.dbf . scp oracle@192.168.17.26:/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_34_989926663.dbf . RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; ORA-00279: change 2447653 generated at 04/14/2021 00:02:04 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/18.0.0/db_1/dbs/arch1_35_989926663.dbf ORA-00280: change 2447653 for thread 1 is in sequence #35 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /home/oracle/arch1_35_989926663.dbf ORA-00279: change 2485187 generated at 04/14/2021 09:00:35 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/18.0.0/db_1/dbs/arch1_36_989926663.dbf ORA-00280: change 2485187 for thread 1 is in sequence #36 ORA-00278: log file '/home/oracle/arch1_35_989926663.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /home/oracle/arch1_36_989926663.dbf ORA-00279: change 2498751 generated at 04/14/2021 14:08:03 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/18.0.0/db_1/dbs/arch1_37_989926663.dbf ORA-00280: change 2498751 for thread 1 is in sequence #37 ORA-00278: log file '/home/oracle/arch1_36_989926663.dbf' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> SQL> alter database open resetlogs; Database altered.
输入下面的文件进行恢复：
/home/oracle/arch1_34_989926663.dbf /home/oracle/arch1_35_989926663.dbf /home/oracle/arch1_36_989926663.dbf
查询是否有datafile需要恢复：
SQL> select * from V$RECOVER_FILE ;
如果使用RECOVER DATABASE USING BACKUP CONTROLFILE，也就是没有UNTIL CANCEL，会出现下面的错误：
SQL> alter database open RESETLOGS; alter database open RESETLOGS * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
