文档课题：主库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，在备库未开启日志实时应用的情况下，主库新产生的归档日志并不会主动传递到备库.
活动
天翼云最新优惠活动，涵盖免费试用，产品折扣等，助您降本增效！
主库log_archive_dest_state_2为defer与enable的测试.
主库log_archive_dest_state_2为defer与enable的测试.
2025-05-19 09:04:38 阅读次数：2
版权声明：本文内容来自第三方投稿或授权转载，原文地址：https://blog.51cto.com/u_12991611/5714275，作者：Liujun_Deng，版权归原作者所有。本网站转在其作品的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如因作品内容、版权等问题需要同本网站联系，请发邮件至ctyunbbs@chinatelecom.cn沟通。
上一篇：error : Class declarations lacks Q_OBJECT macro
下一篇：js原生写一个小小轮播案例
作者介绍
天翼云用户
查看更多
最新文章
怎么处理慢查询---------＞简述Myisam和innodb的区别
2025-02-26 07:21:36
【git】Git 指令统计代码行数
2025-02-17 08:52:45
文心一言 VS 讯飞星火 VS chatgpt （59）-- 算法导论6.4 3题
2025-01-16 09:14:17
python 数据归一化/标准化方法与代码（离差标准化，log归一化，标准化，比例归一化，反正切归一化）
2025-01-15 08:07:36
文心一言 VS 讯飞星火 VS chatgpt （50）-- 算法导论6.2 2题
2025-01-14 09:17:44
send message failed [channel: Netty4TcpChanne GC (Allocation Failure, ParNew Desired survivor size
2023-07-17 06:50:53
查看更多
热门文章
send message failed [channel: Netty4TcpChanne GC (Allocation Failure, ParNew Desired survivor size
2023-07-17 06:50:53
文心一言 VS 讯飞星火 VS chatgpt （59）-- 算法导论6.4 3题
2025-01-16 09:14:17
【git】Git 指令统计代码行数
2025-02-17 08:52:45
文心一言 VS 讯飞星火 VS chatgpt （50）-- 算法导论6.2 2题
2025-01-14 09:17:44
怎么处理慢查询---------＞简述Myisam和innodb的区别
2025-02-26 07:21:36
python 数据归一化/标准化方法与代码（离差标准化，log归一化，标准化，比例归一化，反正切归一化）
2025-01-15 08:07:36
售前咨询热线
400-810-9889转1
关注天翼云
©2025
天翼云科技有限公司版权所有 增值电信业务经营许可证A2.B1.B2-20090001
公司地址：北京市东城区青龙胡同甲1号、3号2幢2层205-32室
