|
文档课题：主库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认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-9-28 11:02:21
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对