rac+单实例dg 在线日志扩展

最新推荐文章于 2021-04-03 21:53:25 发布

shayuwei

最新推荐文章于 2021-04-03 21:53:25 发布

阅读量494

点赞数

分类专栏：笔记 Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/shayuwei/article/details/106237102

版权

笔记同时被 2 个专栏收录

164 篇文章 5 订阅

订阅专栏

Oracle

129 篇文章 7 订阅

订阅专栏

步骤

在rac双节点+DG的环境中，配置的是实时同步，在线日志扩展的步骤：
1.先备库增加standby redo 删除原standby redo
2.然后主库增加redo删除原redo
3.备库增加新redo删除原redo
4.最后主库删除原standby redo，增加standby redo。

注意事项

1.standby log group 要比 logfile group 至少多一组。
2.standby logfile 和 logfile大小尽量一致。
3.standby logfile 和 logfile 操作逻辑基本上一样，都是要等到状态为inactive或者unused才能drop掉
4.log_file_name_convert如果不配置,将导致备库redo 文件不能被删除
关于Dataguard Online redo log 和 Standby redo log

具体步骤

1.查看现有日志组及重做文件大小及路径

主库：

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#    THREAD#  SEQUENCE#     MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
    11        1      139229    500         2 YES INACTIVE
    12        1      139230    500         2 YES INACTIVE
    13        1      139231    500         2 YES ACTIVE
    14        1      139232    500         2 NO  CURRENT
    15        2      162276    500         2 NO  CURRENT
    16        2      162273    500         2 YES INACTIVE
    17        2      162274    500         2 YES INACTIVE
    18        2      162275    500         2 YES ACTIVE

已选择8行。

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE#     MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
    21        1           0    500 YES UNASSIGNED
    22        1           0    500 YES UNASSIGNED
    23        1           0    500 YES UNASSIGNED
    24        1           0    500 YES UNASSIGNED
    25        1           0    500 YES UNASSIGNED
    26        2           0    500 YES UNASSIGNED
    27        2           0    500 YES UNASSIGNED
    28        2           0    500 YES UNASSIGNED
    29        2           0    500 YES UNASSIGNED
    30        2           0    500 YES UNASSIGNED

已选择10行。

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER					      IS_
---------- ------- ------- -------------------------------------------------- ---
	11	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_11.log	      NO
	11	   ONLINE  +FRA/racdb/onlinelog/redo11.log			      NO
	12	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_12.log	      NO
	12	   ONLINE  +FRA/racdb/onlinelog/redo12.log			      NO
	13	   ONLINE  +FRA/racdb/onlinelog/redo13.log			      NO
	13	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_13.log	      NO
	14	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_14.log	      NO
	14	   ONLINE  +FRA/racdb/onlinelog/redo14.log			      NO
	15	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_15.log	      NO
	15	   ONLINE  +FRA/racdb/onlinelog/redo15.log			      NO
	16	   ONLINE  +FRA/racdb/onlinelog/redo16.log			      NO
	16	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_16.log	      NO
	17	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_17.log	      NO
	17	   ONLINE  +FRA/racdb/onlinelog/redo17.log			      NO
	18	   ONLINE  +RACDB_DATA/racdb/onlinelog/group_18.log	      NO
	18	   ONLINE  +FRA/racdb/onlinelog/redo18.log			      NO
	21	   STANDBY +RACDB_DATA/racdb/onlinelog/group_21.log	      NO
	21	   STANDBY +FRA/racdb/onlinelog/redo21.log			      NO
	22	   STANDBY +FRA/racdb/onlinelog/redo22.log			      NO
	22	   STANDBY +RACDB_DATA/racdb/onlinelog/group_22.log	      NO
	23	   STANDBY +FRA/racdb/onlinelog/redo23.log			      NO
	23	   STANDBY +RACDB_DATA/racdb/onlinelog/group_23.log	      NO
	24	   STANDBY +FRA/racdb/onlinelog/redo24.log			      NO
	24	   STANDBY +RACDB_DATA/racdb/onlinelog/group_24.log	      NO
	25	   STANDBY +FRA/racdb/onlinelog/redo25.log			      NO
	25	   STANDBY +RACDB_DATA/racdb/onlinelog/group_25.log	      NO
	26	   STANDBY +FRA/racdb/onlinelog/redo26.log			      NO
	26	   STANDBY +RACDB_DATA/racdb/onlinelog/group_26.log	      NO
	27	   STANDBY +FRA/racdb/onlinelog/redo27.log			      NO
	27	   STANDBY +RACDB_DATA/racdb/onlinelog/group_27.log	      NO
	28	   STANDBY +FRA/racdb/onlinelog/redo28.log			      NO
	28	   STANDBY +RACDB_DATA/racdb/onlinelog/group_28.log	      NO
	29	   STANDBY +FRA/racdb/onlinelog/redo29.log			      NO
	29	   STANDBY +RACDB_DATA/racdb/onlinelog/group_29.log	      NO
	30	   STANDBY +FRA/racdb/onlinelog/redo30.log			      NO
	30	   STANDBY +RACDB_DATA/racdb/onlinelog/group_30.log	      NO

已选择36行。

备库：

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#    THREAD#  SEQUENCE#     MB    MEMBERS ARC   STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
    14        1      139232    300         2 YES 		CLEARING
    15        1      138942    300         2 YES        CLEARING
    16        1      138943    300         2 YES        CLEARING
    17        1      138944    300         2 YES        CLEARING
    18        2      162275    300         2 YES        CLEARING
    19        2      162054    300         2 YES        CLEARING
    20        2      162055    300         2 YES        CLEARING
    21        2      162052    300         2 YES        CLEARING

已选择8行。

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE#     MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
     2        1           0    300 NO    UNASSIGNED
     3        1           0    300 NO    UNASSIGNED
     4        1           0    300 NO    UNASSIGNED
     5        1           0    300 NO    UNASSIGNED
     6        2           0    300 NO    UNASSIGNED
     7        2           0    300 NO    UNASSIGNED
     8        2           0    300 NO    UNASSIGNED
     9        2           0    300 NO    UNASSIGNED
    10        2           0    300 NO    UNASSIGNED
    22        2           0    300 NO    UNASSIGNED
    23        2           0    300 NO    UNASSIGNED
    24        2           0    300 NO    UNASSIGNED
    25        2           0    300 NO    UNASSIGNED
    26        2           0    300 NO    UNASSIGNED

已选择14行。

SQL> select * from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER                            IS_
---------- ------- ------- ------------------------------------------------------------ ---
     2       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_02.log        NO
     2       STANDBY +FRA/racdb_standby/onlinelog/redo02.log	             NO
     3       STANDBY +FRA/racdb_standby/onlinelog/redo03.log	             NO
     3       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_03.log        NO
     4       STANDBY +FRA/racdb_standby/onlinelog/redo04.log	             NO
     4       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_04.log        NO
     5       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_05.log        NO
     5       STANDBY +FRA/racdb_standby/onlinelog/redo05.log	             NO
     6       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_06.log        NO
     6       STANDBY +FRA/racdb_standby/onlinelog/redo06.log	             NO
     7       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_07.log        NO
     7       STANDBY +FRA/racdb_standby/onlinelog/redo07.log	             NO
     8       STANDBY +FRA/racdb_standby/onlinelog/redo08.log	             NO
     8       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_08.log        NO
     9       STANDBY +FRA/racdb_standby/onlinelog/redo09.log	             NO
     9       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_09.log        NO
    10       STANDBY +FRA/racdb_standby/onlinelog/redo10.log	             NO
    10       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_10.log        NO
    14       ONLINE  +FRA/racdb_standby/onlinelog/redo14.log	             NO
    14       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_14.log        NO
    15       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_15.log        NO
    15       ONLINE  +FRA/racdb_standby/onlinelog/redo15.log	             NO
    16       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_16.log        NO
    16       ONLINE  +FRA/racdb_standby/onlinelog/redo16.log	             NO
    17       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_17.log        NO
    17       ONLINE  +FRA/racdb_standby/onlinelog/redo17.log	             NO
    18       ONLINE  +FRA/racdb_standby/onlinelog/redo18.log	             NO
    18       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_18.log        NO
    19       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_19.log        NO
    19       ONLINE  +FRA/racdb_standby/onlinelog/redo19.log	             NO
    20       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_20.log        NO
    20       ONLINE  +FRA/racdb_standby/onlinelog/redo20.log	             NO
    21       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_21.log        NO
    21       ONLINE  +FRA/racdb_standby/onlinelog/redo21.log	             NO
    22       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_22.log        NO
    23       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_23.log        NO
    24       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_24.log        NO
    25       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_25.log        NO
    26       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_26.log        NO

已选择39行。

当前情况是主备库日志组不一致，原因是，先前调整redo时只调整了主库。原始每个节点4个group，大小300M，有2个member，14-21号。扩展后每个节点4个group，大小500M，2个member，11-18号。

2.备库取消同步

SQL> alter database recover managed standby database cancel;

3.备库添加standby logfile group

SQL> alter database add standby logfile thread 1 group 41 ('+FRA/racdb_standby/onlinelog/redo41.log','+RACDB_DATA/racdb_standby/onlinelog/group_41.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 42 ('+FRA/racdb_standby/onlinelog/redo42.log','+RACDB_DATA/racdb_standby/onlinelog/group_42.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 43 ('+FRA/racdb_standby/onlinelog/redo43.log','+RACDB_DATA/racdb_standby/onlinelog/group_43.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 44 ('+FRA/racdb_standby/onlinelog/redo44.log','+RACDB_DATA/racdb_standby/onlinelog/group_44.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 45 ('+FRA/racdb_standby/onlinelog/redo45.log','+RACDB_DATA/racdb_standby/onlinelog/group_45.log') size 500M reuse;

SQL> alter database add standby logfile thread 2 group 46 ('+FRA/racdb_standby/onlinelog/redo46.log','+RACDB_DATA/racdb_standby/onlinelog/group_46.log') size 500M reuse;  
SQL> alter database add standby logfile thread 2 group 47 ('+FRA/racdb_standby/onlinelog/redo47.log','+RACDB_DATA/racdb_standby/onlinelog/group_47.log') size 500M reuse;
SQL> alter database add standby logfile thread 2 group 48 ('+FRA/racdb_standby/onlinelog/redo48.log','+RACDB_DATA/racdb_standby/onlinelog/group_48.log') size 500M reuse;
SQL> alter database add standby logfile thread 2 group 49 ('+FRA/racdb_standby/onlinelog/redo49.log','+RACDB_DATA/racdb_standby/onlinelog/group_49.log') size 500M reuse;
SQL> alter database add standby logfile thread 2 group 50 ('+FRA/racdb_standby/onlinelog/redo50.log','+RACDB_DATA/racdb_standby/onlinelog/group_50.log') size 500M reuse;

注意，RAC有几个节点，就要创建几个thread standby logfile，否则会主备数据不同步，或报错RFS[79]: No standby redo logfiles created for thread 2

4.备库删除原有standby logfile group

SQL> alter database drop standby logfile group 2;
SQL> alter database drop standby logfile group 3;
SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;
SQL> alter database drop standby logfile group 8;
SQL> alter database drop standby logfile group 9;
SQL> alter database drop standby logfile group 10;

SQL> alter database drop standby logfile group 22;
SQL> alter database drop standby logfile group 23;
SQL> alter database drop standby logfile group 24;
SQL> alter database drop standby logfile group 25;
SQL> alter database drop standby logfile group 26;

如在清除standby redo log组时出现上面出错信息执行下面的命令清理

ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oradata/orcl/stredo02.log'
SQL> alter database clear logfile group 4;

5.主库添加logfile group

SQL> alter database add logfile thread 1 group 31 ('+FRA/racdb/onlinelog/redo31.log','+RACDB_DATA/racdb/onlinelog/group_31.log') size 500M reuse;
SQL> alter database add logfile thread 1 group 32 ('+FRA/racdb/onlinelog/redo32.log','+RACDB_DATA/racdb/onlinelog/group_32.log') size 500M reuse;
SQL> alter database add logfile thread 1 group 33 ('+FRA/racdb/onlinelog/redo33.log','+RACDB_DATA/racdb/onlinelog/group_33.log') size 500M reuse;
SQL> alter database add logfile thread 1 group 34 ('+FRA/racdb/onlinelog/redo34.log','+RACDB_DATA/racdb/onlinelog/group_34.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 35 ('+FRA/racdb/onlinelog/redo35.log','+RACDB_DATA/racdb/onlinelog/group_35.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 36 ('+FRA/racdb/onlinelog/redo36.log','+RACDB_DATA/racdb/onlinelog/group_36.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 37 ('+FRA/racdb/onlinelog/redo37.log','+RACDB_DATA/racdb/onlinelog/group_37.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 38 ('+FRA/racdb/onlinelog/redo38.log','+RACDB_DATA/racdb/onlinelog/group_38.log') size 500M reuse;

6.主库删除原有redo

切换日志组，完成同步：

SQL> alter system switch logfile;

把active变为inactive：手动发起检查点：

SQL> alter system checkpoint;

删除原有redo

SQL> alter database drop logfile group 11;
SQL> alter database drop logfile group 12;
SQL> alter database drop logfile group 13;
SQL> alter database drop logfile group 14;
SQL> alter database drop logfile group 15;
SQL> alter database drop logfile group 16;
SQL> alter database drop logfile group 17;
SQL> alter database drop logfile group 18;

7.备库添加redo

设置standby_file_management为manual

SQL> alter system set standby_file_management='manual';

standby_file_management如果设置为auto，主库增删文件会相应地自动在备库做出修改（结合convert参数）；
standby_file_management如果设置为manual，当在primary删除表空间或数据文件，执行drop tablespace .. including contents and datafiles时，standby 只是在控制文件中将该文件删除，还需要手动将物理文件删除
为什么备库standby_file_management要设置为auto

添加redo

SQL> alter database add logfile thread 1 group 31 ('+FRA/racdb_standby/onlinelog/redo31.log','+RACDB_DATA/racdb_standby/onlinelog/group_31.log') size 500M reuse;
SQL> alter database add logfile thread 1 group 32 ('+FRA/racdb_standby/onlinelog/redo32.log','+RACDB_DATA/racdb_standby/onlinelog/group_32.log') size 500M reuse;
SQL> alter database add logfile thread 1 group 33 ('+FRA/racdb_standby/onlinelog/redo33.log','+RACDB_DATA/racdb_standby/onlinelog/group_33.log') size 500M reuse;
SQL> alter database add logfile thread 1 group 34 ('+FRA/racdb_standby/onlinelog/redo34.log','+RACDB_DATA/racdb_standby/onlinelog/group_34.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 35 ('+FRA/racdb_standby/onlinelog/redo35.log','+RACDB_DATA/racdb_standby/onlinelog/group_35.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 36 ('+FRA/racdb_standby/onlinelog/redo36.log','+RACDB_DATA/racdb_standby/onlinelog/group_36.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 37 ('+FRA/racdb_standby/onlinelog/redo37.log','+RACDB_DATA/racdb_standby/onlinelog/group_37.log') size 500M reuse;
SQL> alter database add logfile thread 2 group 38 ('+FRA/racdb_standby/onlinelog/redo38.log','+RACDB_DATA/racdb_standby/onlinelog/group_38.log') size 500M reuse;

8.备库删除原有redo

查看log_file_name_convert参数：

SQL> show parameter NAME_CONVERT

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert             string     +racdb_data/racdb/, +racdb_data/racdb_standby/
log_file_name_convert             string     +racdb_data/racdb/, +racdb_data/racdb_standby/, +fra/racdb/,+fra/racdb_standby/

删除原有redo

SQL> alter database clear logfile group 14;
SQL> alter database clear logfile group 15;
SQL> alter database clear logfile group 16;
SQL> alter database clear logfile group 17;
SQL> alter database clear logfile group 18;
SQL> alter database clear logfile group 19;
SQL> alter database clear logfile group 20;
SQL> alter database clear logfile group 21;

SQL> alter database drop logfile group 14;
SQL> alter database drop logfile group 15;
SQL> alter database drop logfile group 16;
SQL> alter database drop logfile group 17;
SQL> alter database drop logfile group 18;
SQL> alter database drop logfile group 19;
SQL> alter database drop logfile group 20;
SQL> alter database drop logfile group 21;

如果db_file_name_convert 、log_file_name_convert 为空，则会报错：

SQL> alter database drop logfile group 1;
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance xxxxx(thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/xxxxx/redo01.log'

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/data/oradata/xxxxx/redo01.log'

# 需要修改：
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/MPCDB/','/u01/app/oracle/oradata/MPCDB/' scope=spfile;
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/MPCDB/','/u01/app/oracle/oradata/MPCDB/' scope=spfile;
# 备库的 log_file_name_convert 参数要设置，否则无法运行` alter database clear logfile group xxx; `语句
SQL> shutdown immediate
SQL> startup
SQL> select open_mode,database_role from v$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY
SQL> select group#,status from v$log;
SQL> show parameter file_name_convert

9.主库删除原有standby logfile

SQL> alter database drop standby logfile group 21;
SQL> alter database drop standby logfile group 22;
SQL> alter database drop standby logfile group 23;
SQL> alter database drop standby logfile group 24;
SQL> alter database drop standby logfile group 25;
SQL> alter database drop standby logfile group 26;
SQL> alter database drop standby logfile group 27;
SQL> alter database drop standby logfile group 28;
SQL> alter database drop standby logfile group 29;
SQL> alter database drop standby logfile group 30;

10.主库添加standby logfile group

SQL> alter database add standby logfile thread 1 group 41 ('+FRA/racdb/onlinelog/redo41.log','+RACDB_DATA/racdb/onlinelog/group_41.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 42 ('+FRA/racdb/onlinelog/redo42.log','+RACDB_DATA/racdb/onlinelog/group_42.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 43 ('+FRA/racdb/onlinelog/redo43.log','+RACDB_DATA/racdb/onlinelog/group_43.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 44 ('+FRA/racdb/onlinelog/redo44.log','+RACDB_DATA/racdb/onlinelog/group_44.log') size 500M reuse;
SQL> alter database add standby logfile thread 1 group 45 ('+FRA/racdb/onlinelog/redo45.log','+RACDB_DATA/racdb/onlinelog/group_45.log') size 500M reuse;

SQL> alter database add standby logfile thread 2 group 46 ('+FRA/racdb/onlinelog/redo46.log','+RACDB_DATA/racdb/onlinelog/group_46.log') size 500M reuse;  
SQL> alter database add standby logfile thread 2 group 47 ('+FRA/racdb/onlinelog/redo47.log','+RACDB_DATA/racdb/onlinelog/group_47.log') size 500M reuse;
SQL> alter database add standby logfile thread 2 group 48 ('+FRA/racdb/onlinelog/redo48.log','+RACDB_DATA/racdb/onlinelog/group_48.log') size 500M reuse;
SQL> alter database add standby logfile thread 2 group 49 ('+FRA/racdb/onlinelog/redo49.log','+RACDB_DATA/racdb/onlinelog/group_49.log') size 500M reuse;
SQL> alter database add standby logfile thread 2 group 50 ('+FRA/racdb/onlinelog/redo50.log','+RACDB_DATA/racdb/onlinelog/group_50.log') size 500M reuse;

11.备库恢复同步

备库设置standby_file_management为auto

SQL> alter system set standby_file_management='AUTO';

备库恢复同步

SQL> alter database recover managed standby database using current logfile disconnect;

12.验证是否同步

主库

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#    THREAD#  SEQUENCE#     MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
    31        1      139287    500         2 NO  CURRENT
    32        1      139284    500         2 YES INACTIVE
    33        1      139285    500         2 YES INACTIVE
    34        1      139286    500         2 YES ACTIVE
    35        2      162325    500         2 YES ACTIVE
    36        2      162326    500         2 NO  CURRENT
    37        2      162323    500         2 YES INACTIVE
    38        2      162324    500         2 YES ACTIVE

已选择8行。

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG order by 1;

    GROUP#    THREAD#  SEQUENCE#     MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
    41        1           0    500 YES UNASSIGNED
    42        1           0    500 YES UNASSIGNED
    43        1           0    500 YES UNASSIGNED
    44        1           0    500 YES UNASSIGNED
    45        1           0    500 YES UNASSIGNED
    46        2           0    500 YES UNASSIGNED
    47        2           0    500 YES UNASSIGNED
    48        2           0    500 YES UNASSIGNED
    49        2           0    500 YES UNASSIGNED
    50        2           0    500 YES UNASSIGNED

已选择10行。

SQL> select group#,status,type,member from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
    31       ONLINE  +FRA/racdb/onlinelog/redo31.log
    31       ONLINE  +RACDB_DATA/racdb/onlinelog/group_31.log
    32       ONLINE  +RACDB_DATA/racdb/onlinelog/group_32.log
    32       ONLINE  +FRA/racdb/onlinelog/redo32.log
    33       ONLINE  +FRA/racdb/onlinelog/redo33.log
    33       ONLINE  +RACDB_DATA/racdb/onlinelog/group_33.log
    34       ONLINE  +FRA/racdb/onlinelog/redo34.log
    34       ONLINE  +RACDB_DATA/racdb/onlinelog/group_34.log
    35       ONLINE  +RACDB_DATA/racdb/onlinelog/group_35.log
    35       ONLINE  +FRA/racdb/onlinelog/redo35.log
    36       ONLINE  +FRA/racdb/onlinelog/redo36.log
    36       ONLINE  +RACDB_DATA/racdb/onlinelog/group_36.log
    37       ONLINE  +RACDB_DATA/racdb/onlinelog/group_37.log
    37       ONLINE  +FRA/racdb/onlinelog/redo37.log
    38       ONLINE  +RACDB_DATA/racdb/onlinelog/group_38.log
    38       ONLINE  +FRA/racdb/onlinelog/redo38.log
    41       STANDBY +RACDB_DATA/racdb/onlinelog/group_41.log
    41       STANDBY +FRA/racdb/onlinelog/redo41.log
    42       STANDBY +FRA/racdb/onlinelog/redo42.log
    42       STANDBY +RACDB_DATA/racdb/onlinelog/group_42.log
    43       STANDBY +RACDB_DATA/racdb/onlinelog/group_43.log
    43       STANDBY +FRA/racdb/onlinelog/redo43.log
    44       STANDBY +RACDB_DATA/racdb/onlinelog/group_44.log
    44       STANDBY +FRA/racdb/onlinelog/redo44.log
    45       STANDBY +FRA/racdb/onlinelog/redo45.log
    45       STANDBY +RACDB_DATA/racdb/onlinelog/group_45.log
    46       STANDBY +FRA/racdb/onlinelog/redo46.log
    46       STANDBY +RACDB_DATA/racdb/onlinelog/group_46.log
    47       STANDBY +RACDB_DATA/racdb/onlinelog/group_47.log
    47       STANDBY +FRA/racdb/onlinelog/redo47.log
    48       STANDBY +FRA/racdb/onlinelog/redo48.log
    48       STANDBY +RACDB_DATA/racdb/onlinelog/group_48.log
    49       STANDBY +FRA/racdb/onlinelog/redo49.log
    49       STANDBY +RACDB_DATA/racdb/onlinelog/group_49.log
    50       STANDBY +RACDB_DATA/racdb/onlinelog/group_50.log
    50       STANDBY +FRA/racdb/onlinelog/redo50.log

已选择36行。

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
    162326

备库

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#    THREAD#  SEQUENCE#     MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
    31        1      139287    500         2 YES CURRENT
    32        1      139284    500         2 YES CLEARING
    33        1      139285    500         2 YES CLEARING
    34        1      139286    500         2 YES CLEARING
    35        2      162325    500         2 YES CLEARING
    36        2      162326    500         2 YES CURRENT
    37        2      162323    500         2 YES CLEARING
    38        2      162324    500         2 YES CLEARING

已选择8行。

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG order by 1;

    GROUP#    THREAD#  SEQUENCE#     MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
    41        1      139288    500 YES ACTIVE
    42        1           0    500 NO    UNASSIGNED
    43        1           0    500 YES UNASSIGNED
    44        1           0    500 YES UNASSIGNED
    45        1           0    500 YES UNASSIGNED
    46        2           0    500 NO    UNASSIGNED
    47        2      162327    500 YES ACTIVE
    48        2           0    500 YES UNASSIGNED
    49        2           0    500 YES UNASSIGNED
    50        2           0    500 YES UNASSIGNED

已选择10行。

SQL> select group#,status,type,member from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
    31       ONLINE  +FRA/racdb_standby/onlinelog/redo31.log
    31       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_31.log
    32       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_32.log
    32       ONLINE  +FRA/racdb_standby/onlinelog/redo32.log
    33       ONLINE  +FRA/racdb_standby/onlinelog/redo33.log
    33       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_33.log
    34       ONLINE  +FRA/racdb_standby/onlinelog/redo34.log
    34       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_34.log
    35       ONLINE  +FRA/racdb_standby/onlinelog/redo35.log
    35       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_35.log
    36       ONLINE  +FRA/racdb_standby/onlinelog/redo36.log
    36       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_36.log
    37       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_37.log
    37       ONLINE  +FRA/racdb_standby/onlinelog/redo37.log
    38       ONLINE  +RACDB_DATA/racdb_standby/onlinelog/group_38.log
    38       ONLINE  +FRA/racdb_standby/onlinelog/redo38.log
    41       STANDBY +FRA/racdb_standby/onlinelog/redo41.log
    41       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_41.log
    42       STANDBY +FRA/racdb_standby/onlinelog/redo42.log
    42       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_42.log
    43       STANDBY +FRA/racdb_standby/onlinelog/redo43.log
    43       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_43.log
    44       STANDBY +FRA/racdb_standby/onlinelog/redo44.log
    44       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_44.log
    45       STANDBY +FRA/racdb_standby/onlinelog/redo45.log
    45       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_45.log
    46       STANDBY +FRA/racdb_standby/onlinelog/redo46.log
    46       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_46.log
    47       STANDBY +FRA/racdb_standby/onlinelog/redo47.log
    47       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_47.log
    48       STANDBY +FRA/racdb_standby/onlinelog/redo48.log
    48       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_48.log
    49       STANDBY +FRA/racdb_standby/onlinelog/redo49.log
    49       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_49.log
    50       STANDBY +FRA/racdb_standby/onlinelog/redo50.log
    50       STANDBY +RACDB_DATA/racdb_standby/onlinelog/group_50.log

已选择36行。

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

MAX(SEQUENCE#)
--------------
    162326

SQL>  SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';

NAME                 VALUE                                        TIME_COMPUTED
-------------------------------- -------------------------------------------------------------------------------- ------------------------------
transport lag             +00 00:00:00                                    05/19/2020 16:29:42
apply lag             +00 00:00:00                                    05/19/2020 16:29:42

SQL> select process, sequence#, status, delay_mins from v$managed_standby;

PROCESS    SEQUENCE# 	STATUS      DELAY_MINS
--------- ---------- ------------ ----------
ARCH          162326 	CLOSING           0
ARCH          161605 	CLOSING           0
ARCH          162327 	CLOSING           0
ARCH          139287 	CLOSING           0
RFS           0		 	IDLE 	          0
RFS           0 	 	IDLE        	  0
RFS           0 	 	IDLE              0
RFS           162328 	IDLE              0
RFS           0 	 	IDLE              0
RFS           139288 	IDLE              0
MRP0          162328 	APPLYING_LOG      0
RFS           0 	 	IDLE              0
RFS           0 	 	IDLE              0

已选择13行。