验证oracle 19c单条命令恢复ADG GAP

Leo 2024-08-11

文档课题：验证oracle 19c单条命令恢复ADG GAP.
数据库：oracle 19.13
主库：IP 192.168.133.101 实例名 tmis    主机名 leo-oracle-19c
备库：IP 192.168.133.38 实例名 tmisdg 主机名 leo-19c-tmisdg
1、主备实时同步确认
--主库insert数据
sys@TMIS> create table test (id number);

Table created.

sys@TMIS> insert into test values(1);

1 row created.

sys@TMIS> commit;

Commit complete.

--备库验证
SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
DGRD      ALLOCATED
DGRD      ALLOCATED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      APPLYING_LOG

10 rows selected.

SQL> select * from test;

        ID
----------
         1

说明：如上所示，主备实时同步正常.

2、模拟异常GAP
2.1、备库停库
SQL> ! ip a | grep 133.38
    inet 192.168.133.38/24 brd 192.168.133.255 scope global noprefixroute ens33

SQL> alter database recover managed standby database cancel;

Database altered.

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

2.2、主库归档文件确认
[oracle@leo-oracle-19c archivelog]$ pwd
/u01/app/oracle/archivelog
[oracle@leo-oracle-19c archivelog]$ ls -ltrh
total 407M
-rw-r----- 1 oracle oinstall 35M Aug 8 20:36 1_12_1174603581.dbf
-rw-r----- 1 oracle oinstall 19M Aug 8 20:36 1_13_1174603581.dbf
-rw-r----- 1 oracle oinstall 69M Aug 9 15:31 1_14_1174603581.dbf
-rw-r----- 1 oracle oinstall 61M Aug 11 08:59 1_15_1174603581.dbf
-rw-r----- 1 oracle oinstall 183M Aug 11 09:01 1_16_1174603581.dbf
-rw-r----- 1 oracle oinstall 11M Aug 11 09:10 1_17_1174603581.dbf
-rw-r----- 1 oracle oinstall 11K Aug 11 09:10 1_18_1174603581.dbf
-rw-r----- 1 oracle oinstall 32M Aug 11 10:22 1_19_1174603581.dbf
-rw-r----- 1 oracle oinstall 900K Aug 11 10:45 1_20_1174603581.dbf
-rw-r----- 1 oracle oinstall 280K Aug 11 10:51 1_21_1174603581.dbf

sys@TMIS> select THREAD#,SEQUENCE#,name,round(BLOCKS*BLOCK_SIZE/1024/1024,0) as "size_M" from v$archived_log;

   THREAD# SEQUENCE# NAME                                                   size_M
---------- ---------- -------------------------------------------------- ----------
         1         12 /u01/app/oracle/archivelog/1_12_1174603581.dbf             35
         1         13 /u01/app/oracle/archivelog/1_13_1174603581.dbf             18
         1         14 /u01/app/oracle/archivelog/1_14_1174603581.dbf             68
         1         15 /u01/app/oracle/archivelog/1_15_1174603581.dbf             60
         1         16 /u01/app/oracle/archivelog/1_16_1174603581.dbf            183
         1         17 /u01/app/oracle/archivelog/1_17_1174603581.dbf             11
         1         18 /u01/app/oracle/archivelog/1_18_1174603581.dbf              0
         1         19 /u01/app/oracle/archivelog/1_19_1174603581.dbf             31
         1         19 tmisdg                                                     31
         1         20 /u01/app/oracle/archivelog/1_20_1174603581.dbf              1
         1         20 tmisdg                                                      1
         1         21 /u01/app/oracle/archivelog/1_21_1174603581.dbf              0
         1         21 tmisdg                                                      0
         1         22 /u01/app/oracle/archivelog/1_22_1174603581.dbf              0
         1         22 tmisdg                                                      0

15 rows selected.

2.3、主库建测试数据
create table books
(
bk_id number(38) not null primary key,
bk_title varchar2(50) not null,
copyright date not null
);

insert into books values (11078,'Learning MYSQL','2010-01-01');
insert into books values (11033,'Study Html','2011-01-01');
insert into books values (11035,'How to use php','2003-01-01');
insert into books values (11072,'Teach yourself javascript','2005-01-01');
insert into books values (11028,'Learning C++','2005-01-01');
insert into books values (11069,'MYSQL professional','2009-01-01');
insert into books values (11026,'Guide to MySQL 5.7','2008-01-01');
insert into books values (11041,'Inside VC++','2011-01-01');
commit;

sys@TMIS> select FILE#,name ,status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/TMIS/system01.dbf          SYSTEM
         3 /u01/app/oracle/oradata/TMIS/sysaux01.dbf          ONLINE
         4 /u01/app/oracle/oradata/TMIS/undotbs01.dbf         ONLINE
         7 /u01/app/oracle/oradata/TMIS/users01.dbf           ONLINE

sys@TMIS> alter system switch logfile;

System altered.

sys@TMIS> select THREAD#,SEQUENCE#,name,round(BLOCKS*BLOCK_SIZE/1024/1024,0) as "size_M" from v$archived_log;

   THREAD# SEQUENCE# NAME                                                   size_M
---------- ---------- -------------------------------------------------- ----------
         1         12 /u01/app/oracle/archivelog/1_12_1174603581.dbf             35
         1         13 /u01/app/oracle/archivelog/1_13_1174603581.dbf             18
         1         14 /u01/app/oracle/archivelog/1_14_1174603581.dbf             68
         1         15 /u01/app/oracle/archivelog/1_15_1174603581.dbf             60
         1         16 /u01/app/oracle/archivelog/1_16_1174603581.dbf            183
         1         17 /u01/app/oracle/archivelog/1_17_1174603581.dbf             11
         1         18 /u01/app/oracle/archivelog/1_18_1174603581.dbf              0
         1         19 /u01/app/oracle/archivelog/1_19_1174603581.dbf             31
         1         19 tmisdg                                                     31
         1         20 /u01/app/oracle/archivelog/1_20_1174603581.dbf              1
         1         20 tmisdg                                                      1
         1         21 /u01/app/oracle/archivelog/1_21_1174603581.dbf              0
         1         21 tmisdg                                                      0
         1         22 /u01/app/oracle/archivelog/1_22_1174603581.dbf              0
         1         22 tmisdg                                                      0
         1         23 /u01/app/oracle/archivelog/1_23_1174603581.dbf              5

16 rows selected.

sys@TMIS> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string     service=tmisdg lgwr async vali
                                                 d_for=(online_logfiles,primary
                                                 _role) db_unique_name=tmisdg

2.4、主库新增数据文件
sys@TMIS> alter tablespace users add datafile '/u01/app/oracle/oradata/TMIS/users02.dbf' size 10M autoextend on;

Tablespace altered.

sys@TMIS> select FILE#,name ,status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/TMIS/system01.dbf          SYSTEM
         3 /u01/app/oracle/oradata/TMIS/sysaux01.dbf          ONLINE
         4 /u01/app/oracle/oradata/TMIS/undotbs01.dbf         ONLINE
         5 /u01/app/oracle/oradata/TMIS/users02.dbf           ONLINE
         7 /u01/app/oracle/oradata/TMIS/users01.dbf           ONLINE

sys@TMIS> alter system switch logfile;

System altered.

sys@TMIS> select THREAD#,SEQUENCE#,name,round(BLOCKS*BLOCK_SIZE/1024/1024,0) as "size_M" from v$archived_log;

   THREAD# SEQUENCE# NAME                                                   size_M
---------- ---------- -------------------------------------------------- ----------
         1         12 /u01/app/oracle/archivelog/1_12_1174603581.dbf             35
         1         13 /u01/app/oracle/archivelog/1_13_1174603581.dbf             18
         1         14 /u01/app/oracle/archivelog/1_14_1174603581.dbf             68
         1         15 /u01/app/oracle/archivelog/1_15_1174603581.dbf             60
         1         16 /u01/app/oracle/archivelog/1_16_1174603581.dbf            183
         1         17 /u01/app/oracle/archivelog/1_17_1174603581.dbf             11
         1         18 /u01/app/oracle/archivelog/1_18_1174603581.dbf              0
         1         19 /u01/app/oracle/archivelog/1_19_1174603581.dbf             31
         1         19 tmisdg                                                     31
         1         20 /u01/app/oracle/archivelog/1_20_1174603581.dbf              1
         1         20 tmisdg                                                      1
         1         21 /u01/app/oracle/archivelog/1_21_1174603581.dbf              0
         1         21 tmisdg                                                      0
         1         22 /u01/app/oracle/archivelog/1_22_1174603581.dbf              0
         1         22 tmisdg                                                     0
         1         23 /u01/app/oracle/archivelog/1_23_1174603581.dbf              5
         1         24 /u01/app/oracle/archivelog/1_24_1174603581.dbf              0

17 rows selected.

[oracle@leo-oracle-19c archivelog]$ ls -ltr
total 421696
-rw-r----- 1 oracle oinstall 36225024 Aug 8 20:36 1_12_1174603581.dbf
-rw-r----- 1 oracle oinstall 19171328 Aug 8 20:36 1_13_1174603581.dbf
-rw-r----- 1 oracle oinstall 71366656 Aug 9 15:31 1_14_1174603581.dbf
-rw-r----- 1 oracle oinstall 62924288 Aug 11 08:59 1_15_1174603581.dbf
-rw-r----- 1 oracle oinstall 191488512 Aug 11 09:01 1_16_1174603581.dbf
-rw-r----- 1 oracle oinstall 11385856 Aug 11 09:10 1_17_1174603581.dbf
-rw-r----- 1 oracle oinstall     10752 Aug 11 09:10 1_18_1174603581.dbf
-rw-r----- 1 oracle oinstall 32526336 Aug 11 10:22 1_19_1174603581.dbf
-rw-r----- 1 oracle oinstall    921088 Aug 11 10:45 1_20_1174603581.dbf
-rw-r----- 1 oracle oinstall    286720 Aug 11 10:51 1_21_1174603581.dbf
-rw-r----- 1 oracle oinstall    390656 Aug 11 10:57 1_22_1174603581.dbf
-rw-r----- 1 oracle oinstall   4772352 Aug 11 11:08 1_23_1174603581.dbf
-rw-r----- 1 oracle oinstall    325120 Aug 11 11:12 1_24_1174603581.dbf

2.5、新增归档文件确认
--备库查询归档日志文件
[oracle@leo-19c-tmisdg archivelog]$ ls -ltr
total 33328
-rw-r----- 1 oracle oinstall 32526336 Aug 11 10:23 1_19_1174603581.dbf
-rw-r----- 1 oracle oinstall   921088 Aug 11 10:45 1_20_1174603581.dbf
-rw-r----- 1 oracle oinstall   286720 Aug 11 10:51 1_21_1174603581.dbf
-rw-r----- 1 oracle oinstall   390656 Aug 11 10:57 1_22_1174603581.dbf

说明：如上所示，主库新产生SEQUENCE#为23和24的归档日志文件没有传递到备库.

2.6、主库删除新增归档文件
--主库手动删除SEQUENCE#为23和24的归档日志文件
[oracle@leo-oracle-19c archivelog]$ rm -rf 1_23_1174603581.dbf
[oracle@leo-oracle-19c archivelog]$ rm -fr 1_24_1174603581.dbf

[oracle@leo-oracle-19c archivelog]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Aug 11 11:17:13 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: TMIS (DBID=362622715)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_12_1174603581.dbf RECID=1 STAMP=1176496608
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_13_1174603581.dbf RECID=2 STAMP=1176496609
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_14_1174603581.dbf RECID=3 STAMP=1176564675
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_15_1174603581.dbf RECID=4 STAMP=1176713982
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_16_1174603581.dbf RECID=5 STAMP=1176714100
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_17_1174603581.dbf RECID=6 STAMP=1176714626
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_18_1174603581.dbf RECID=7 STAMP=1176714645
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_19_1174603581.dbf RECID=8 STAMP=1176718944
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_20_1174603581.dbf RECID=10 STAMP=1176720312
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_21_1174603581.dbf RECID=12 STAMP=1176720683
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/1_22_1174603581.dbf RECID=14 STAMP=1176721039
validation failed for archived log
archived log file name=/u01/app/oracle/archivelog/1_23_1174603581.dbf RECID=16 STAMP=1176721687
validation failed for archived log
archived log file name=/u01/app/oracle/archivelog/1_24_1174603581.dbf RECID=17 STAMP=1176721962
Crosschecked 13 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
List of Archived Log Copies for database with db_unique_name TMIS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
16      1    23      X 11-AUG-24
        Name: /u01/app/oracle/archivelog/1_23_1174603581.dbf

17      1    24      X 11-AUG-24
        Name: /u01/app/oracle/archivelog/1_24_1174603581.dbf

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/archivelog/1_23_1174603581.dbf RECID=16 STAMP=1176721687
deleted archived log
archived log file name=/u01/app/oracle/archivelog/1_24_1174603581.dbf RECID=17 STAMP=1176721962
Deleted 2 EXPIRED objects

2.7、备库开启数据库后确认实时应用情况
SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CLOSING
DGRD      ALLOCATED
DGRD      ALLOCATED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
RFS       IDLE
RFS       IDLE
MRP0      APPLYING_LOG

9 rows selected.

SQL> select name,db_unique_name,open_mode from v$database;

NAME           DB_UNIQUE_NAME                 OPEN_MODE
-------------- ------------------------------ --------------------
TMIS           tmisdg                         READ ONLY WITH APPLY

SQL> select SOURCE_DBID,SOURCE_DB_UNIQUE_NAME,NAME,VALUE,TIME_COMPUTED from v$dataguard_stats;

SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                           VALUE           TIME_COMPUTED
----------- -------------------------------- ------------------------------ --------------- ------------------------------
362622715 tmis                             transport lag                  +00 00:23:12    08/11/2024 11:26:21
362622715 tmis                             apply lag                      +00 00:23:13    08/11/2024 11:26:21
362622715 tmis                             apply finish time                              08/11/2024 11:26:21
          0                                  estimated startup time         11              08/11/2024 11:26:21

SQL> select * from books;
select * from books
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select FILE#,name ,status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/TMISDG/system01.dbf        SYSTEM
         3 /u01/app/oracle/oradata/TMISDG/sysaux01.dbf        ONLINE
         4 /u01/app/oracle/oradata/TMISDG/undotbs01.dbf       ONLINE
         7 /u01/app/oracle/oradata/TMISDG/users01.dbf         ONLINE

注意：
a、此时备库存在GAP，新添加的users02.dbf文件没有在备库生成;
b、备库能成功open，且mrp进程显示正常.
c、主库新增的books表并未同步到备库;

说明：成功模拟出备库归档GAP.

3、恢复备库GAP
3.1、备库单条命令恢复GAP
注意：备库在open的情况下，直接执行recover standby命令.
[oracle@leo-19c-tmisdg ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Aug 11 11:44:24 2024
Version 19.13.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: TMIS (DBID=362622715)

RMAN> recover standby database from service tmis;

Starting recover at 11-AUG-24
using target database control file instead of recovery catalog
Oracle instance started

Total System Global Area    2466249664 bytes

Fixed Size                     8899520 bytes
Variable Size                536870912 bytes
Database Buffers            1912602624 bytes
Redo Buffers                   7876608 bytes

contents of Memory Script:
{
   restore standby controlfile from service 'tmis'; --重新恢复控制文件
   alter database mount standby database;
}
executing Memory Script

Starting restore at 11-AUG-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tmis
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/TMISDG/control01.ctl
Finished restore at 11-AUG-24

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

contents of Memory Script:
{
set newname for datafile 5 to
"/u01/app/oracle/oradata/TMISDG/users02.dbf";
   restore from service 'tmis' datafile
    5;
   catalog datafilecopy "/u01/app/oracle/oradata/TMISDG/users02.dbf";
   switch datafile all;     --修改备库控制文件中数据文件路径
}
executing Memory Script

executing command: SET NEWNAME

Starting restore at 11-AUG-24
Starting implicit crosscheck backup at 11-AUG-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 11-AUG-24

Starting implicit crosscheck copy at 11-AUG-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-AUG-24

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service tmis
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TMISDG/users02.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-AUG-24

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TMISDG/users02.dbf RECID=1 STAMP=1176723893

datafile 5 switched to datafile copy
input datafile copy RECID=1 STAMP=1176723893 file name=/u01/app/oracle/oradata/TMISDG/users02.dbf

contents of Memory Script:
{
recover database from service 'tmis';   --直接在线进行增量备份和恢复
}
executing Memory Script

Starting recover at 11-AUG-24
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 3087398
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service tmis
destination for restore of datafile 00001: /u01/app/oracle/oradata/TMISDG/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 tmis
destination for restore of datafile 00003: /u01/app/oracle/oradata/TMISDG/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 tmis
destination for restore of datafile 00004: /u01/app/oracle/oradata/TMISDG/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 tmis
destination for restore of datafile 00007: /u01/app/oracle/oradata/TMISDG/users01.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 11-AUG-24
Executing: alter system set standby_file_management=auto
Finished recover at 11-AUG-24

RMAN>

3.2、恢复过程解析
--从以上日志可知该命令执行过程描述如下：
This command will internally keep track of standby file locations,refresh standby controlfile from primary,update the new standby controlfile with standby file names,perform incremental backup on primary,transfer the backup-pieces over network to standby and perform recovery on standby.

该命令执行过程分为三个部分：
a、恢复备库控制文件，启动备库到mount阶段.
   contents of Memory Script:
   {
   restore standby controlfile from service 'tmis';
   alter database mount standby database;
   }
b、修改备库控制文件中主库新增的数据文件路径.
   contents of Memory Script:
   {
   set newname for datafile 5 to
    "/u01/app/oracle/oradata/TMISDG/users02.dbf";
      restore from service 'tmis' datafile 5;
      catalog datafilecopy "/u01/app/oracle/oradata/TMISDG/users02.dbf";
      switch datafile all;
   }
c、在线增量恢复
   contents of Memory Script:
   {
     recover database from service 'tmis';
   }

4、open备库并开启实时应用
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.
SQL> recover managed standby database disconnect;
Media recovery complete.

5、备库增量数据确认
SQL> select * from books;

     BK_ID BK_TITLE                                           COPYRIGHT
---------- -------------------------------------------------- ------------------
     11078 Learning MYSQL                                     01-JAN-10
     11033 Study Html                                         01-JAN-11
     11035 How to use php                                     01-JAN-03
     11072 Teach yourself javascript                          01-JAN-05
     11028 Learning C++                                       01-JAN-05
     11069 MYSQL professional                                 01-JAN-09
     11026 Guide to MySQL 5.7                                 01-JAN-08
     11041 Inside VC++                                        01-JAN-11

8 rows selected.

SQL> col name for a50
SQL> select FILE#,name ,status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /u01/app/oracle/oradata/TMISDG/system01.dbf        SYSTEM
         3 /u01/app/oracle/oradata/TMISDG/sysaux01.dbf        ONLINE
         4 /u01/app/oracle/oradata/TMISDG/undotbs01.dbf       ONLINE
         5 /u01/app/oracle/oradata/TMISDG/users02.dbf         ONLINE
         7 /u01/app/oracle/oradata/TMISDG/users01.dbf         ONLINE

[oracle@leo-19c-tmisdg archivelog]$ ls -ltr
total 33704
-rw-r----- 1 oracle oinstall 32526336 Aug 11 10:23 1_19_1174603581.dbf
-rw-r----- 1 oracle oinstall   921088 Aug 11 10:45 1_20_1174603581.dbf
-rw-r----- 1 oracle oinstall   286720 Aug 11 10:51 1_21_1174603581.dbf
-rw-r----- 1 oracle oinstall   390656 Aug 11 10:57 1_22_1174603581.dbf
-rw-r----- 1 oracle oinstall   381952 Aug 11 11:22 1_25_1174603581.dbf

注意：
a、 SEQUENCE#为23和24的归档日志文件并没有在备库重新生成;
b、主库新增的books表数据在备库能正常查询显示;
c、主库新增的users02.dbf数据文件在备库能正常查询显示.

--redo和standby日志文件路径确认
SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                                IS_     CON_ID
---------- ------- ------- ----------------------------------------------------- --- ----------
         3         ONLINE /u01/app/oracle/oradata/TMISDG/redo03.log             NO           0
         2         ONLINE /u01/app/oracle/oradata/TMISDG/redo02.log             NO           0
         1         ONLINE /u01/app/oracle/oradata/TMISDG/redo01.log             NO           0
        11         STANDBY /u01/app/oracle/oradata/TMISDG/redo11_standby.log     NO           0
        12         STANDBY /u01/app/oracle/oradata/TMISDG/redo12_standby.log     NO           0
        13         STANDBY /u01/app/oracle/oradata/TMISDG/redo13_standby.log     NO           0
        14         STANDBY /u01/app/oracle/oradata/TMISDG/redo14_standby.log     NO           0

7 rows selected.

SQL> select a.GROUP#,a.STATUS,b.TYPE,a.BYTES/1024/1024,a.FIRST_CHANGE#,a.NEXT_CHANGE#,b.member from v$log a,v$logfile b where a.group#=b.group#;

    GROUP# STATUS           TYPE    A.BYTES/1024/1024 FIRST_CHANGE# NEXT_CHANGE# MEMBER
---------- ---------------- ------- ----------------- ------------- ------------ --------------------------------------------
         3 INACTIVE         ONLINE                200       3081977      3082974 /u01/app/oracle/oradata/TMISDG/redo03.log
         2 ACTIVE           ONLINE                200       3084340   9.2954E+18 /u01/app/oracle/oradata/TMISDG/redo02.log
         1 INACTIVE         ONLINE                200       3082974      3084340 /u01/app/oracle/oradata/TMISDG/redo01.log

6、主备实时同步验证
--主库删除表books
sys@TMIS> ! ip a | grep 133.101
    inet 192.168.133.101/24 brd 192.168.133.255 scope global ens33

sys@TMIS> select count(*) from books;

COUNT(*)
----------
         8

sys@TMIS> drop table books purge;

Table dropped.

--备库数据验证
SQL> ! ip a | grep 133.38
    inet 192.168.133.38/24 brd 192.168.133.255 scope global noprefixroute ens33

SQL> select count(*) from books;
select count(*) from books
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

说明：如上所示，主备实时同步正常.

7、实验总结
a、从oracle 18c开始，若无有效的归档日志恢复ADG GAP，只需单条命令即可完成GAP修复，该命令将多个步骤集成为单条命令，其原理和增量备份恢复相同;
b、无需再考虑primary和standby端需要有额外存储空间来存储增量备份集，特别对于GAP比较大的场景，优势特别明显;
c、通过网络在线传输，不用手动在主备之间进行备份集传输.

参考网址：https://blog.csdn.net/fanzhuozhuo/article/details/122753393

adg gap修复

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

验证oracle 19c单条命令恢复ADG GAP

1、主备实时同步确认

2、模拟异常GAP

2.1、备库停库

2.2、主库归档文件确认

2.3、主库建测试数据

2.4、主库新增数据文件

2.5、新增归档文件确认

2.6、主库删除新增归档文件

2.7、备库开启数据库后确认实时应用情况

3、恢复备库GAP

3.1、备库单条命令恢复GAP

3.2、恢复过程解析

4、open备库并开启实时应用

5、备库增量数据确认

6、主备实时同步验证

7、实验总结

评论