主库log_archive_dest_state_2为defer与enable的测试.

文档课题：主库log_archive_dest_state_2为defer与enable的测试.
主库：CentOS 7.9 64位 + oracle 11.2.0.4 64位
备库：CentOS 7.9 64位 + oracle 11.2.0.4 64位
1、主库归档
主库查看归档日志.
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        30 2022-09-26 13:14:39         39 /u01/app/oracle/fast_recovery_area/archivelog/1_30_1101942938.dbf
        29 2022-09-26 13:14:35         39 /u01/app/oracle/fast_recovery_area/archivelog/1_29_1101942938.dbf
        28 2022-09-26 13:14:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_28_1101942938.dbf
        27 2022-09-26 13:12:41         39 /u01/app/oracle/fast_recovery_area/archivelog/1_27_1101942938.dbf
        26 2022-09-26 13:12:40         33 /u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbf
        25 2022-09-26 13:09:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_25_1101942938.dbf
        24 2022-09-26 13:07:10         19 /u01/app/oracle/fast_recovery_area/archivelog/1_24_1101942938.dbf
        23 2022-09-26 13:07:08         39 /u01/app/oracle/fast_recovery_area/archivelog/1_23_1101942938.dbf
        22 2022-09-26 11:57:44         31 /u01/app/oracle/fast_recovery_area/archivelog/1_22_1101942938.dbf
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf
        20 2022-09-26 11:57:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf
        15 2022-08-28 23:05:52          0 /u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf
        14 2022-08-28 23:00:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf

17 rows selected.
2、备库归档
备库查看归档日志.
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        30 2022-09-26 13:14:39         39 /u01/app/oracle/fast_recovery_area/archivelog1_30_1101942938.dbf
        29 2022-09-26 13:14:35         39 /u01/app/oracle/fast_recovery_area/archivelog1_29_1101942938.dbf
        28 2022-09-26 13:14:32         39 /u01/app/oracle/fast_recovery_area/archivelog1_28_1101942938.dbf
        27 2022-09-26 13:12:41         39 /u01/app/oracle/fast_recovery_area/archivelog1_27_1101942938.dbf
        26 2022-09-26 13:12:40         33 /u01/app/oracle/fast_recovery_area/archivelog1_26_1101942938.dbf
        25 2022-09-26 13:09:32         39 /u01/app/oracle/fast_recovery_area/archivelog1_25_1101942938.dbf
        24 2022-09-26 13:07:10         19 /u01/app/oracle/fast_recovery_area/archivelog1_24_1101942938.dbf
        23 2022-09-26 13:07:08         39 /u01/app/oracle/fast_recovery_area/archivelog1_23_1101942938.dbf
        22 2022-09-26 11:57:44         31 /u01/app/oracle/fast_recovery_area/archivelog1_22_1101942938.dbf
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog1_21_1101942938.dbf
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog1_19_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog1_17_1101942938.dbf
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog1_16_1101942938.dbf

14 rows selected.
3、修改参数
将主库log_archive_dest_state_2设置为defer，此后观察主库新产生的归档日志是否会传递到备库.
主库设置：
> alter system set log_archive_dest_state_2=defer;
> create table t1 as select * from all_objects;
> insert into t1 select * from t1;
……(省略若干insert)
> select count(*) from t1;

  COUNT(*)
----------
   2700640
> commit;
4、查询归档
4.1、主库查询
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        38 2022-09-26 13:42:43         39 /u01/app/oracle/fast_recovery_area/archivelog/1_38_1101942938.dbf
        37 2022-09-26 13:42:40         39 /u01/app/oracle/fast_recovery_area/archivelog/1_37_1101942938.dbf
        36 2022-09-26 13:42:36         39 /u01/app/oracle/fast_recovery_area/archivelog/1_36_1101942938.dbf
        35 2022-09-26 13:42:33         39 /u01/app/oracle/fast_recovery_area/archivelog/1_35_1101942938.dbf
        34 2022-09-26 13:42:30         39 /u01/app/oracle/fast_recovery_area/archivelog/1_34_1101942938.dbf
        33 2022-09-26 13:42:29         39 /u01/app/oracle/fast_recovery_area/archivelog/1_33_1101942938.dbf
        32 2022-09-26 13:42:28         29 /u01/app/oracle/fast_recovery_area/archivelog/1_32_1101942938.dbf
        31 2022-09-26 13:14:42         39 /u01/app/oracle/fast_recovery_area/archivelog/1_31_1101942938.dbf
        30 2022-09-26 13:14:39         39 /u01/app/oracle/fast_recovery_area/archivelog/1_30_1101942938.dbf
        29 2022-09-26 13:14:35         39 /u01/app/oracle/fast_recovery_area/archivelog/1_29_1101942938.dbf
        28 2022-09-26 13:14:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_28_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        27 2022-09-26 13:12:41         39 /u01/app/oracle/fast_recovery_area/archivelog/1_27_1101942938.dbf
        26 2022-09-26 13:12:40         33 /u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbf
        25 2022-09-26 13:09:32         39 /u01/app/oracle/fast_recovery_area/archivelog/1_25_1101942938.dbf
        24 2022-09-26 13:07:10         19 /u01/app/oracle/fast_recovery_area/archivelog/1_24_1101942938.dbf
        23 2022-09-26 13:07:08         39 /u01/app/oracle/fast_recovery_area/archivelog/1_23_1101942938.dbf
        22 2022-09-26 11:57:44         31 /u01/app/oracle/fast_recovery_area/archivelog/1_22_1101942938.dbf
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog/1_21_1101942938.dbf
        20 2022-09-26 11:57:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_20_1101942938.dbf
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog/1_19_1101942938.dbf
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog/1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_17_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog/1_16_1101942938.dbf
        15 2022-08-28 23:05:52          0 /u01/app/oracle/fast_recovery_area/archivelog/1_15_1101942938.dbf
        14 2022-08-28 23:00:19          0 /u01/app/oracle/fast_recovery_area/archivelog/1_14_1101942938.dbf

25 rows selected.
说明：sequence#从31到38号为主库新产生归档日志.
4.2、备库查询
> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name from v$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-dd hh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        31 2022-09-26 13:14:42         39 /u01/app/oracle/fast_recovery_area/archivelog1_31_1101942938.dbf
        30 2022-09-26 13:14:39         39 /u01/app/oracle/fast_recovery_area/archivelog1_30_1101942938.dbf
        29 2022-09-26 13:14:35         39 /u01/app/oracle/fast_recovery_area/archivelog1_29_1101942938.dbf
        28 2022-09-26 13:14:32         39 /u01/app/oracle/fast_recovery_area/archivelog1_28_1101942938.dbf
        27 2022-09-26 13:12:41         39 /u01/app/oracle/fast_recovery_area/archivelog1_27_1101942938.dbf
        26 2022-09-26 13:12:40         33 /u01/app/oracle/fast_recovery_area/archivelog1_26_1101942938.dbf
        25 2022-09-26 13:09:32         39 /u01/app/oracle/fast_recovery_area/archivelog1_25_1101942938.dbf
        24 2022-09-26 13:07:10         19 /u01/app/oracle/fast_recovery_area/archivelog1_24_1101942938.dbf
        23 2022-09-26 13:07:08         39 /u01/app/oracle/fast_recovery_area/archivelog1_23_1101942938.dbf
        22 2022-09-26 11:57:44         31 /u01/app/oracle/fast_recovery_area/archivelog1_22_1101942938.dbf
        21 2022-09-26 11:57:26          0 /u01/app/oracle/fast_recovery_area/archivelog1_21_1101942938.dbf

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- -----------------------------------------------------------------
        19 2022-08-28 23:27:25          0 /u01/app/oracle/fast_recovery_area/archivelog1_19_1101942938.dbf
        18 2022-08-28 23:27:22          0 /u01/app/oracle/fast_recovery_area/archivelog1_18_1101942938.dbf
        17 2022-08-28 23:07:19          0 /u01/app/oracle/fast_recovery_area/archivelog1_17_1101942938.dbf
        16 2022-08-28 23:06:07          0 /u01/app/oracle/fast_recovery_area/archivelog1_16_1101942938.dbf

15 rows selected.
5、测试结论
主库新产生的归档日志并未传递到备库.后将log_archive_dest_state_2恢复为enable，在备库未开启日志实时应用的情况下，主库新产生的归档日志并不会主动传递到备库.