參考文章：RESTORE/Recover from Service

參考官方檔案：Restoring and Recovering Files Over the Network（DG）

Restoring and Recovering Files Over the Network（RMAN）

Rolling Forward a Standby With One Command 18C

一、介紹

DG GAP顧名思義就是：DG不同步，當備庫不能接受到一個或多個主庫的歸檔日志檔案時候，就發生了GAP，

1、主庫歸檔日志存在，可以通過配置Fetch Archive Log(FAL)引數，自動解決歸檔GAP，

2、主庫歸檔日志丟失，需要人工干預來修復：

a.11G and before的常規處理步驟：

1.在主庫上創建一個備庫的控制檔案
2.以備庫的當前SCN號為起點，在主庫上做一個增量備份
3.將增量備份拷貝到備庫上
4.使用新的控制檔案將備庫啟動到mount狀態
5.將增量備份注冊到RMAN的catalog，取消備庫的恢復應用，恢復增量備份
6.開啟備庫的恢復行程

b.12C and later的新特性（RECOVER … FROM SERVICE）

c.18C and later的新特性（RECOVER STANDBY DATABASE FROM SERVICE）

Oracle隨著版本的升級，逐漸將步驟縮減，進行封裝，18C之后可謂是達到了所謂的一鍵重繪，恢復DG同步，

下面我們通過實驗來進行展示：

首先，模擬備庫斷電，主庫切幾個最新的歸檔，然后手工刪掉，重新開啟DG同步，

##備庫：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate

##主庫：
alter system switch logfile;多次切歸檔

##洗掉最近幾個歸檔日志：
rm 1_34_1070147137.arc 
rm 1_33_1070147137.arc

##備庫：
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

##查看GAP
SQL> SELECT * FROM V$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1            32             34

SQL> SELECT max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            31

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1         38          1         40
ARCH      CLOSING               1         39          1          2
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         37          1          2
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         40         83          1
MRP0      APPLYING_LOG          1         33          1     245760

8 rows selected.

二、11G and Before

當前GAP：32---34

1.在主庫上創建一個備庫的控制檔案

SQL> alter database create standby controlfile as '/tmp/standby.ctl';

Database altered.

2.以備庫的當前SCN號為起點，在主庫上做一個增量備份

--備庫
SQL> select  to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
1086639

--主庫
rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup INCREMENTAL from scn 1086639 database format '/tmp/incre_%U';
release channel c1;
release channel c2;
}

3.將增量備份拷貝到備庫上

--主庫
[oracle@orcl:/tmp]$ scp incre_0* oracle@orcl_stby:/home/oracle
oracle@orcl_stby's password: 
incre_0cvsjs8b_1_1                                                                                                                                                         100%  144KB 144.0KB/s   00:00    
incre_0dvsjs9a_1_1                                                                                                                                                         100%  416KB 416.0KB/s   00:00    
incre_0evsjs9a_1_1                                                                                                                                                         100%  144KB 144.0KB/s   00:00    
incre_0fvsjs9b_1_1                                                                                                                                                         100% 9856KB   9.6MB/s   00:00    
[oracle@orcl:/tmp]$ scp standby.ctl oracle@orcl_stby:/home/oracle        
oracle@orcl_stby's password: 
standby.ctl

4.使用新的控制檔案將備庫啟動到mount狀態

shutdown immediate
startup nomount

rman target /
RMAN> restore controlfile from '/home/oracle/standby.ctl';

Starting restore at 18-APR-21
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: copied control file copy
output file name=/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 18-APR-21

alter database mount;

5.增量備份注冊到RMAN的catalog，取消日志應用，恢復增量備份

--備庫
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

rman target /
RMAN> catalog start with '/home/oracle/';
YES

RMAN> recover database noredo;

Starting recover at 18-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/orcl/system01.dbf
destination for restore of datafile 00004: /oradata/orcl/users01.dbf
destination for restore of datafile 00006: /oradata/orcl/test01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/incre_0evsjs9a_1_1
channel ORA_DISK_1: piece handle=/home/oracle/incre_0evsjs9a_1_1 tag=TAG20210418T133122
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oradata/orcl/sysaux01.dbf
destination for restore of datafile 00003: /oradata/orcl/undotbs01.dbf
destination for restore of datafile 00005: /oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/incre_0dvsjs9a_1_1
channel ORA_DISK_1: piece handle=/home/oracle/incre_0dvsjs9a_1_1 tag=TAG20210418T133122
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 18-APR-21

6.開啟備庫的恢復行程

alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--GAP已修復
SQL> SELECT * FROM V$ARCHIVE_GAP;

no rows selected

SQL> SELECT max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            41

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         41          1        257
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         42      19969          1
MRP0      APPLYING_LOG          1         42      19969     245760

8 rows selected.

三、12C and Later

SQL> SELECT * FROM V$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID
---------- ------------- -------------- ----------
	 1	      30	     31 	 1

SQL> SELECT max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
	    29

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS	  THREAD#  SEQUENCE#	 BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH	  CONNECTED		0	   0	      0 	 0
DGRD	  ALLOCATED		0	   0	      0 	 0
DGRD	  ALLOCATED		0	   0	      0 	 0
ARCH	  CONNECTED		0	   0	      0 	 0
ARCH	  CLOSING		1	  29	      1      268
ARCH	  CONNECTED		0	   0	      0 	 0
RFS	      IDLE			1	  34	    184 	 1
RFS	      IDLE			1	   0	      0 	 0
RFS	      IDLE			0	   0	      0 	 0
RFS	      IDLE			0	   0	      0 	 0
RFS	      IDLE			0	   0	      0 	 0
MRP0	  WAIT_FOR_GAP	1	  30	      0 	 0
DGRD	  ALLOCATED		0	   0	      0 	 0

13 rows selected.

[oracle@orcl_stby:/archivelog]$ ll
total 11508
-rw-r-----. 1 oracle oinstall 11634176 Apr 19 02:05 1_28_1069567741.arc
-rw-r-----. 1 oracle oinstall   137728 Apr 19 02:08 1_29_1069567741.arc
-rw-r-----. 1 oracle oinstall     1536 Apr 19 02:08 1_32_1069567741.arc
-rw-r-----. 1 oracle oinstall     3584 Apr 19 02:08 1_33_1069567741.arc

當前GAP：30---31，缺少歸檔30，31

1.記錄主庫和備庫當前SCN號

--主庫
SQL> set line222
SQL> col HXFNM for a100
SQL> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

  FILE_NUM HXFNM												FHSCN
---------- ---------------------------------------------------------------------------------------------------- --------------------
	 1 /oradata/ORCL/system01.dbf										2600522
	 3 /oradata/ORCL/sysaux01.dbf										2600522
	 4 /oradata/ORCL/undotbs01.dbf										2600522
	 5 /oradata/ORCL/pdbseed/system01.dbf								2155383
	 6 /oradata/ORCL/pdbseed/sysaux01.dbf								2155383
	 7 /oradata/ORCL/users01.dbf										2600522
	 8 /oradata/ORCL/pdbseed/undotbs01.dbf								2155383
	 9 /oradata/ORCL/BFA6BEE45A1E3605E053AC01A8							2600522
	10 /oradata/ORCL/BFA6BEE45A1E3605E053AC01A8							2600522
	11 /oradata/ORCL/BFA6BEE45A1E3605E053AC01A8							2600522
	12 /oradata/ORCL/test01.dbf										    2600522




--備庫
SQL> set line222
SQL> col HXFNM for a100
SQL> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

  FILE_NUM HXFNM												FHSCN
---------- ---------------------------------------------------------------------------------------------------- --------------------
	 1 /oradata/ORCL_STBY/system01.dbf									2600488
	 3 /oradata/ORCL_STBY/sysaux01.dbf									2600488
	 4 /oradata/ORCL_STBY/undotbs01.dbf									2600488
	 5 /oradata/ORCL_STBY/pdbseed/system01.dbf							2155383
	 6 /oradata/ORCL_STBY/pdbseed/sysaux01.dbf							2155383
	 7 /oradata/ORCL_STBY/users01.dbf									2600488
	 8 /oradata/ORCL_STBY/pdbseed/undotbs01.dbf							2155383
	 9 /oradata/ORCL_STBY/PDB01/o1_mf_system_j7							2600488
	10 /oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j7							2600488
	11 /oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_							2600488
	12 /oradata/ORCL_STBY/test01.dbf									2600488


SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2600487

2.使用recover standby using service恢復

采用rman的新功能，recover standby using service，通過RMAN連接到target備庫，然后用主庫的service執行恢復備庫命令，

語法：

RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;

模擬GAP期間，有資料檔案添加的情況：

--主庫添加資料檔案
SQL> alter tablespace TEST add datafile '/oradata/ORCL/test02.dbf' size 100M autoextend off;

Tablespace altered.

--檢查備庫scn是否添加資料檔案
SQL> select file# from v$datafile where creation_change# > =2600487;

     FILE#
----------
	13

--備庫啟動到nomount狀態
shutdown immediate
startup nomount

--rman恢復
rman target /

##恢復控制檔案
RMAN> restore standby controlfile from service orcl;

Starting restore at 19-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=739 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/ORCL_STBY/control01.ctl
output file name=/oradata/ORCL_STBY/control02.ctl
Finished restore at 19-APR-21

##mount資料庫
alter database mount;

##restore新添加的資料檔案
run
{
SET NEWNAME FOR DATABASE TO '/oradata/ORCL_STBY/%f_%U';
RESTORE DATAFILE 13 FROM SERVICE orcl;
}

executing command: SET NEWNAME

Starting restore at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=503 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /oradata/ORCL_STBY/13_data_D-ORCL_TS-TEST_FNO-13
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 19-APR-21

##由于主備的資料檔案目錄不一致，需要修改controlfile中資料檔案位置
catalog start with '/oradata/ORCL_STBY';
YES
SWITCH DATABASE TO COPY;

##rename tempfile && logfile
alter system set standby_file_management=MANUAL;
##logfile
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database rename file '/oradata/ORCL/redo03.log' to '/oradata/ORCL_STBY/redo03.log';
alter database rename file '/oradata/ORCL/redo02.log' to '/oradata/ORCL_STBY/redo02.log';
alter database rename file '/oradata/ORCL/redo01.log' to '/oradata/ORCL_STBY/redo01.log';
alter database rename file '/oradata/ORCL/standby_redo04.log' to '/oradata/ORCL_STBY/standby_redo04.log';
alter database rename file '/oradata/ORCL/standby_redo05.log' to '/oradata/ORCL_STBY/standby_redo05.log';
alter database rename file '/oradata/ORCL/standby_redo06.log' to '/oradata/ORCL_STBY/standby_redo06.log';
alter database rename file '/oradata/ORCL/standby_redo07.log' to '/oradata/ORCL_STBY/standby_redo07.log';
##tempfile
alter database rename file '/oradata/ORCL/temp01.dbf' to '/oradata/ORCL_STBY/temp01.dbf';
alter database rename file '/oradata/ORCL/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf' to '/oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf';
alter database rename file '/oradata/ORCL/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf' to '/oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf';

alter system set standby_file_management=AUTO;

##恢復資料庫
RMAN> recover database from service orcl noredo using compressed backupset;

Starting recover at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2 device type=DISK
skipping datafile 5; already restored to SCN 2155383
skipping datafile 6; already restored to SCN 2155383
skipping datafile 8; already restored to SCN 2155383
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00001: /oradata/ORCL_STBY/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00003: /oradata/ORCL_STBY/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00004: /oradata/ORCL_STBY/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00007: /oradata/ORCL_STBY/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00009: /oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00010: /oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00011: /oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
destination for restore of datafile 00012: /oradata/ORCL_STBY/test01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 19-APR-21

Notes：如果主備庫檔案目錄不一致，則需要catalog切換控制檔案中路徑，否則報錯：

RMAN> recover database from service orcl noredo using compressed backupset;

Starting recover at 19-APR-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcl
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2021 02:25:29
ORA-19625: error identifying file /oradata/ORCL/system01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

3.開啟備庫日志應用

--此時主備庫的sch已恢復一致
SQL> col HXFNM for a100
SQL> set line222
SQL> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

  FILE_NUM HXFNM												FHSCN
---------- ---------------------------------------------------------------------------------------------------- --------------------
	 1 /oradata/ORCL_STBY/system01.dbf									2603512
	 3 /oradata/ORCL_STBY/sysaux01.dbf									2603514
	 4 /oradata/ORCL_STBY/undotbs01.dbf									2603516
	 5 /oradata/ORCL_STBY/pdbseed/system01.dbf							2155383
	 6 /oradata/ORCL_STBY/pdbseed/sysaux01.dbf							2155383
	 7 /oradata/ORCL_STBY/users01.dbf									2603518
	 8 /oradata/ORCL_STBY/pdbseed/undotbs01.dbf							2155383
	 9 /oradata/ORCL_STBY/PDB01/o1_mf_system_j7							2603521
	10 /oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j7							2603524
	11 /oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_							2603527
	12 /oradata/ORCL_STBY/test01.dbf									2603530

11 rows selected.

--主庫需要切幾次歸檔
ALTER SYSTEM ARCHIVE LOG CURRENT;

或者

ALTER SYSTEM SWITCH LOGFILE;

--開啟備庫應用日志
alter database open;
alter pluggable database all open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

4、測驗同步情況

--查看standby日志是否正常
set line222
col member for a60
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;


    GROUP#    THREAD# T1.BYTES/1024/1024 STATUS     MEMBER
---------- ---------- ------------------ ---------- ------------------------------------------------------------
	 4	    1		     120 ACTIVE     /oradata/ORCL/standby_redo04.log
	 5	    1		     120 UNASSIGNED /oradata/ORCL/standby_redo05.log
	 6	    1		     120 UNASSIGNED /oradata/ORCL/standby_redo06.log
	 7	    1		     120 UNASSIGNED /oradata/ORCL/standby_redo07.log

--主庫插入資料
sqlplus test/test@pdb01
insert into test values (999);
commit;
 
--備庫查詢
SQL> alter session set container=pdb01;
SQL> select * from test.test;
 
	ID
----------
	 1
	 2
     999
 
--備庫已同步

三、18C and Later

將RECOVER STANDBY DATABASE命令與FROM SERVICE子句一起使用，以通過對主資料庫進行的更改來重繪物理備用資料庫，

備庫可以直接在開啟狀態進行重繪，

語法：

RECOVER STANDBY DATABASE FROM SERVICE primary_db;

模擬GAP期間，有資料檔案添加的情況：

--備庫存在GAP
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS	  THREAD#  SEQUENCE#	 BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH	  CONNECTED		0	   0	      0 	 0
DGRD	  ALLOCATED		0	   0	      0 	 0
DGRD	  ALLOCATED		0	   0	      0 	 0
ARCH	  CONNECTED		0	   0	      0 	 0
ARCH	  CONNECTED		0	   0	      0 	 0
ARCH	  CONNECTED		0	   0	      0 	 0
RFS	      IDLE			1	   0	      0 	 0
RFS	      IDLE			1	  72	    119 	 1
RFS	      IDLE			0	   0	      0 	 0
RFS	      IDLE			0	   0	      0 	 0
MRP0	  WAIT_FOR_GAP	1	  70	      0 	 0

11 rows selected.

SQL> SELECT max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
	    69

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID
---------- ------------- -------------- ----------
	 1	      70	     70 	 1

--主庫添加資料檔案
SQL> alter tablespace TEST add datafile '/oradata/ORCL/test02.dbf' size 100M autoextend off;

Tablespace altered.

1、執行RECOVER STANDBY DATABASE FROM SERVICE重繪備庫：

通過執行程序可以發現：

RECOVER STANDBY DATABASE命令重新啟動備用實體，從主資料庫重繪控制檔案，并自動重命名資料檔案，臨時檔案和聯機日志，它可以還原添加到主資料庫中的新資料檔案，并還原到當前時間的備用資料庫，

##備庫
##取消日志應用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

##開始重繪備庫
rman target /
RMAN> RECOVER STANDBY DATABASE FROM SERVICE orcl;

Starting recover at 19-APR-21
using target database control file instead of recovery catalog
Oracle instance started

Total System Global Area    3355441944 bytes

Fixed Size                     9141016 bytes
Variable Size                671088640 bytes
Database Buffers            2667577344 bytes
Redo Buffers                   7634944 bytes

contents of Memory Script:
{
   restore standby controlfile from service  'orcl';
   alter database mount standby database;
}
executing Memory Script

Starting restore at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=502 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/oradata/ORCL_STBY/control01.ctl
output file name=/oradata/ORCL_STBY/control02.ctl
Finished restore at 19-APR-21

released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual

contents of Memory Script:
{
set newname for tempfile  1 to 
 "/oradata/ORCL_STBY/temp01.dbf";
set newname for tempfile  2 to 
 "/oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf";
set newname for tempfile  3 to 
 "/oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf";
   switch tempfile all;
set newname for datafile  1 to 
 "/oradata/ORCL_STBY/system01.dbf";
set newname for datafile  3 to 
 "/oradata/ORCL_STBY/sysaux01.dbf";
set newname for datafile  4 to 
 "/oradata/ORCL_STBY/undotbs01.dbf";
set newname for datafile  5 to 
 "/oradata/ORCL_STBY/pdbseed/system01.dbf";
set newname for datafile  6 to 
 "/oradata/ORCL_STBY/pdbseed/sysaux01.dbf";
set newname for datafile  7 to 
 "/oradata/ORCL_STBY/users01.dbf";
set newname for datafile  8 to 
 "/oradata/ORCL_STBY/pdbseed/undotbs01.dbf";
set newname for datafile  9 to 
 "/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf";
set newname for datafile  10 to 
 "/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf";
set newname for datafile  11 to 
 "/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf";
set newname for datafile  12 to 
 "/oradata/ORCL_STBY/test01.dbf";
set newname for datafile  14 to 
 "/oradata/ORCL/test02.dbf";
   restore from service  'orcl' datafile
    14;
   catalog datafilecopy  "/oradata/ORCL_STBY/system01.dbf", 
 "/oradata/ORCL_STBY/sysaux01.dbf", 
 "/oradata/ORCL_STBY/undotbs01.dbf", 
 "/oradata/ORCL_STBY/pdbseed/system01.dbf", 
 "/oradata/ORCL_STBY/pdbseed/sysaux01.dbf", 
 "/oradata/ORCL_STBY/users01.dbf", 
 "/oradata/ORCL_STBY/pdbseed/undotbs01.dbf", 
 "/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf", 
 "/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf", 
 "/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf", 
 "/oradata/ORCL_STBY/test01.dbf", 
 "/oradata/ORCL/test02.dbf";
   switch datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/ORCL_STBY/temp01.dbf in control file
renamed tempfile 2 to /oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf in control file
renamed tempfile 3 to /oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=504 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /oradata/ORCL/test02.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 19-APR-21

cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/system01.dbf RECID=4 STAMP=1070263316
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/sysaux01.dbf RECID=5 STAMP=1070263317
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/undotbs01.dbf RECID=6 STAMP=1070263317
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/pdbseed/system01.dbf RECID=7 STAMP=1070263317
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/pdbseed/sysaux01.dbf RECID=8 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/users01.dbf RECID=9 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/pdbseed/undotbs01.dbf RECID=10 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf RECID=11 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf RECID=12 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf RECID=13 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL_STBY/test01.dbf RECID=14 STAMP=1070263318
cataloged datafile copy
datafile copy file name=/oradata/ORCL/test02.dbf RECID=15 STAMP=1070263318

datafile 14 switched to datafile copy
input datafile copy RECID=15 STAMP=1070263318 file name=/oradata/ORCL/test02.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1070263316 file name=/oradata/ORCL_STBY/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1070263317 file name=/oradata/ORCL_STBY/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1070263317 file name=/oradata/ORCL_STBY/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1070263317 file name=/oradata/ORCL_STBY/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1070263318 file name=/oradata/ORCL_STBY/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1070263318 file name=/oradata/ORCL_STBY/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1070263318 file name=/oradata/ORCL_STBY/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1070263318 file name=/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1070263318 file name=/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1070263318 file name=/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1070263318 file name=/oradata/ORCL_STBY/test01.dbf
Executing: alter database rename file '/oradata/ORCL/redo01.log' to '/oradata/ORCL_STBY/redo01.log'
Executing: alter database rename file '/oradata/ORCL/redo02.log' to '/oradata/ORCL_STBY/redo02.log'
Executing: alter database rename file '/oradata/ORCL/redo03.log' to '/oradata/ORCL_STBY/redo03.log'

contents of Memory Script:
{
  recover database from service  'orcl';
}
executing Memory Script

Starting recover at 19-APR-21
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 2155383
skipping datafile 6; already restored to SCN 2155383
skipping datafile 8; already restored to SCN 2155383
skipping datafile 14; already restored to SCN 2658548
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00001: /oradata/ORCL_STBY/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00003: /oradata/ORCL_STBY/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00004: /oradata/ORCL_STBY/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00007: /oradata/ORCL_STBY/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00009: /oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00010: /oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00011: /oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00012: /oradata/ORCL_STBY/test01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-21
Executing: alter system set standby_file_management=auto
Finished recover at 19-APR-21

2、rename standby log

--重繪過后，redo log路徑已修改，standby log路徑未修改，
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/ORCL_STBY/redo03.log
/oradata/ORCL_STBY/redo02.log
/oradata/ORCL_STBY/redo01.log
/oradata/ORCL/standby_redo04.log
/oradata/ORCL/standby_redo05.log
/oradata/ORCL/standby_redo06.log
/oradata/ORCL/standby_redo07.log


--rename tempfile && logfile
alter system set standby_file_management=MANUAL;

--clear log
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
--standby log
alter database rename file '/oradata/ORCL/standby_redo04.log' to '/oradata/ORCL_STBY/standby_redo04.log';
alter database rename file '/oradata/ORCL/standby_redo05.log' to '/oradata/ORCL_STBY/standby_redo05.log';
alter database rename file '/oradata/ORCL/standby_redo06.log' to '/oradata/ORCL_STBY/standby_redo06.log';
alter database rename file '/oradata/ORCL/standby_redo07.log' to '/oradata/ORCL_STBY/standby_redo07.log';

3、主庫切日志，備庫開啟日志應用

--主庫需要切幾次歸檔
ALTER SYSTEM ARCHIVE LOG CURRENT;
 
或者
 
ALTER SYSTEM SWITCH LOGFILE;
 
--開啟備庫應用日志
alter database open;
alter pluggable database all open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

4、測驗同步情況

--查看standby日志是否正常
set line222
col member for a60
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;
 
 
    GROUP#    THREAD# T1.BYTES/1024/1024 STATUS     MEMBER
---------- ---------- ------------------ ---------- ------------------------------------------------------------
	 4	    1		     120 ACTIVE     /oradata/ORCL/standby_redo04.log
	 5	    1		     120 UNASSIGNED /oradata/ORCL/standby_redo05.log
	 6	    1		     120 UNASSIGNED /oradata/ORCL/standby_redo06.log
	 7	    1		     120 UNASSIGNED /oradata/ORCL/standby_redo07.log
 
--主庫插入資料
sqlplus test/test@pdb01
insert into test values (999);
commit;
 
--備庫查詢
SQL> alter session set container=pdb01;

Session altered.

SQL> select * from test.test;

	ID
----------
	 1
	 2
       999
       999
       233
      7788

6 rows selected.

--備庫已同步

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/278145.html

標籤：其他

上一篇：Oracle查詢庫結構及表屬性SQL總結

下一篇：MySQL資料庫進階版

DataGuard GAP修復的幾種方式（11G 12C 18C）

一、介紹

二、11G and Before

1.在主庫上創建一個備庫的控制檔案

2.以備庫的當前SCN號為起點，在主庫上做一個增量備份

3.將增量備份拷貝到備庫上

4.使用新的控制檔案將備庫啟動到mount狀態

5.增量備份注冊到RMAN的catalog，取消日志應用，恢復增量備份

6.開啟備庫的恢復行程

三、12C and Later

1.記錄主庫和備庫當前SCN號

2.使用recover standby using service恢復

3.開啟備庫日志應用

4、測驗同步情況

三、18C and Later

1、執行RECOVER STANDBY DATABASE FROM SERVICE重繪備庫：

2、rename standby log

3、主庫切日志，備庫開啟日志應用

4、測驗同步情況