重庆思庄Oracle、Redhat认证学习论坛
标题: 主库log_archive_dest_state_2为defer与enable的测试 [打印本页]
作者: denglj 时间: 2022-9-28 11:02
标题: 主库log_archive_dest_state_2为defer与enable的测试
文档课题：主库log_archive_dest_state_2为defer与enable的测试.
主库：CentOS7.9 64位+ oracle 11.2.0.4 64位
备库：CentOS7.9 64位+ oracle 11.2.0.4 64位
1、主库归档主库查看归档日志.
>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_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 rowsselected.
2、备库归档备库查看归档日志.
>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_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 rowsselected.
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') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_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 rowsselected.
说明：sequence#从31到38号为主库新产生归档日志.
4.2、备库查询>select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",name fromv$archived_log where first_time between to_date('2022-08-27 1','yyyy-mm-ddhh24') and to_date('2022-09-26 14','yyyy-mm-dd hh24') and dest_id=1 group byfirst_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 rowsselected.
5、测试结论主库新产生的归档日志并未传递到备库.后将log_archive_dest_state_2恢复为enable，在备库未开启日志实时应用的情况下，主库新产生的归档日志并不会主动传递到备库.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |