一、模拟GAP
备库：
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
主库：
SQL> alter tablespace TBS add datafile '/u01/app/oracle/oradata/ORCL/tbs02.dbf' size 10m autoextend off;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/orcl/archive
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
[oracle@oracle-19c01 archive]$ mv 1_21_1119747570.arc 1_21_1119747570.arc.bak
[oracle@oracle-19c01 archive]$ mv 1_22_1119747570.arc 1_22_1119747570.arc.bak
[oracle@oracle-19c01 archive]$ mv 1_23_1119747570.arc 1_23_1119747570.arc.bak
启动备库：
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID
---------- ------------- -------------- ----------
1 21 23 1
SQL> SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
20
缺失的归档日志为21-23，总共3个归档日志
二、确认GAP
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2377704
SQL> select min(checkpoint_change#) from v$datafile_header;
MIN(CHECKPOINT_CHANGE#)
-----------------------
2377705
确认GAP期间是否新增数据文件
SQL> col name for a50
SQL> select file#,name from v$datafile where creation_change# > 2377705;
FILE# NAME
---------- --------------------------------------------------
9 /u01/app/oracle/oradata/ORCL/tbs02.dbf
三、GAP处理
1.取消备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2.恢复备库
采用rman的新功能，recover standby using service，通过RMAN连接到target备库，然后用主库的service执行恢复备库命令。
语法：
RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;
这里的< PRIMARY DB SERVICE NAME >即 TNSNAME。
RMAN> RECOVER STANDBY DATABASE FROM SERVICE TNSPRI;
Starting recover at 14-FEB-23
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
contents of Memory Script:
{
restore standby controlfile from service 'TNSPRI';
alter database mount standby database;
}
executing Memory Script
Starting restore at 14-FEB-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service TNSPRI
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/oradata/ORCL/control02.ctl
Finished restore at 14-FEB-23
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for datafile 9 to
"/u01/app/oracle/oradata/ORCL/tbs02.dbf";
restore from service 'TNSPRI' datafile
9;
catalog datafilecopy "/u01/app/oracle/oradata/ORCL/tbs02.dbf";
switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
Starting restore at 14-FEB-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=263 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service TNSPRI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCL/tbs02.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-FEB-23
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/ORCL/tbs02.dbf RECID=2 STAMP=1128815397
datafile 9 switched to datafile copy
input datafile copy RECID=2 STAMP=1128815397 file name=/u01/app/oracle/oradata/ORCL/tbs02.dbf
contents of Memory Script:
{
recover database from service 'TNSPRI';
}
executing Memory Script
Starting recover at 14-FEB-23
using channel ORA_DISK_1
skipping datafile 9; already restored to SCN 2385344
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TNSPRI
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TNSPRI
destination for restore of datafile 00002: /u01/app/oracle/oradata/ORCL/test02.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 TNSPRI
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCL/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 TNSPRI
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCL/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 TNSPRI
destination for restore of datafile 00005: /u01/app/oracle/oradata/ORCL/test01.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 TNSPRI
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCL/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 TNSPRI
destination for restore of datafile 00008: /u01/app/oracle/oradata/ORCL/tbs01.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 14-FEB-23
Executing: alter system set standby_file_management=auto
Finished recover at 14-FEB-23
RMAN>
3.备库开启日志应用
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
四、查看主备库状态
主库：
SQL> set pages 100 lines 180
SQL> col db_unique_name for a10
SQL> select dbid,db_unique_name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID DB_UNIQUE_ CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------
1647528304 orclpri 2387420 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
备库：
SQL> set pages 100 lines 180
SQL> col db_unique_name for a10
SQL> select dbid,db_unique_name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID DB_UNIQUE_ CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------
1647528304 orclstb 2387425 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
