标签：06 17 16 STANDBY 29 LOGFILES 15 2023 备库
文档课题：备库归档日志文件的删除测试——第二篇：valid_for参数为ONLINE_LOGFILES与STANDBY_LOGFILES.
数据库：oracle 11.2.0.4
架构：rac(2节点)+dg(orcldg与sh_orcl)
场景描述：在该架构中，orcldg备库作为sh_orcl备库归档日志文件的来源，现测试以下两点：
a、归档日志文件从orcldg备库传输到sh_orcl备库后先不应用，此时orcldg端是否能删除未应用的归档日志？
b、sh_orcl无法接收orcldg端的归档日志，此时在orcldg备库端是否能删除未传输的归档日志？
1、valid_for为ONLINE_LOGFILES,ALL_ROLES
说明：现测试orcldg库valid_for为online_logfiles,all_roles的场景.
1.1、环境准备
--当前orcldg备库log_archive_dest_4配置
SYS@orcldg> show parameter log_archive_dest_4
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string SERVICE=sh_orcl COMPRESSION=EN
ABLE LGWR ASYNC VALID_FOR=(ALL
_LOGFILES,ALL_ROLES) DB_UNIQUE
_NAME=sh_orcl
--修改log_archive_dest_4配置参数，将valid_for修改为ONLINE_LOGFILES,ALL_ROLES
SYS@orcldg> alter system set log_archive_dest_4='SERVICE=sh_orcl COMPRESSION=ENABLE LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sh_orcl';
System altered.
SYS@orcldg> show parameter log_archive_dest_4
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string SERVICE=sh_orcl COMPRESSION=EN
ABLE LGWR ASYNC VALID_FOR=(ONL
INE_LOGFILES,ALL_ROLES) DB_UNI
QUE_NAME=sh_orcl
SYS@orcldg> alter system set log_archive_dest_state_4=enable;
System altered.
--将主库端log_archive_dest_3置空.
SYS@orcl1> alter system set log_archive_dest_3='' sid='*';
System altered.
说明：以上配置在于主库停止传输归档日志文件到sh_orcl备库，转为由备库orcldg传输归档日志文件到sh_orcl备库.
1.2、备库日志应用情况
--orcldg备库当前日志应用情况
SYS@orcldg> set line 200
SYS@orcldg> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:45:17 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:45:18 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:45:18 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:45:18 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:45:18 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:16 NO
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:45:20 IN-MEMORY
14 rows selected.
注意：此时orcldg备库相同序列号有两个.
--sh_orcl备库日志应用情况.
SYS@sh_orcl> set line 200
SYS@sh_orcl> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:15 NO
7 rows selected.
'1.3、主库节点1切换日志
SYS@orcl1> alter system switch logfile;
System altered.
1.4、备库日志应用情况
--orcldg备库日志应用情况
SYS@orcldg> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:45:17 YES
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:45:18 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:45:18 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:19:58 IN-MEMORY
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:45:18 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:45:20 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:16 NO
15 rows selected.
注意：此时备库orcldg线程1只有一个349的序列号的归档文件，表明没有归档日志传输到sh_orcl.
--主库节点1再次切换日志
SYS@orcl1> alter system switch logfile;
System altered.
--再次确认orcldg备库日志应用情况
SYS@orcldg> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:45:17 YES
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:45:18 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:45:18 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:19:58 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:25:01 IN-MEMORY
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:45:18 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:45:18 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:16 NO
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:45:20 YES
16 rows selected.
说明：orcldg线程1的350序列号归档文件同样没有传输到sh_orcl备库.
--查sh_orcl备库归档日志应用情况
SYS@sh_orcl> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:15 NO
7 rows selected.
'小结：如上所示，当orcldg备库的valid_for修改为ONLINE_LOGFILES,ALL_ROLES后，新增归档日志文件并不会通过orcldg备库传输到sh_orcl备库.
2、valid_for为STANDBY_LOGFILES,ALL_ROLES
说明：现测试将valid_for修改为STANDBY_LOGFILES,ALL_ROLES的场景.
2.1、环境准备
--orcldg备库log_archive_dest_4配置
SYS@orcldg> show parameter log_archive_dest_4
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string SERVICE=sh_orcl COMPRESSION=EN
ABLE LGWR ASYNC VALID_FOR=(ONL
INE_LOGFILES,ALL_ROLES) DB_UNI
QUE_NAME=sh_orcl
--修改log_archive_dest_4配置参数，将valid_for修改为ONLINE_LOGFILES,ALL_ROLES.
SYS@orcldg> alter system set log_archive_dest_4='SERVICE=sh_orcl COMPRESSION=ENABLE LGWR ASYNC VALID_FOR=(STANDBY_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sh_orcl';
System altered.
SYS@orcldg> show parameter log_archive_dest_4
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string SERVICE=sh_orcl COMPRESSION=EN
ABLE LGWR ASYNC VALID_FOR=(ONL
INE_LOGFILES,ALL_ROLES) DB_UNI
QUE_NAME=sh_orcl
SYS@orcldg> alter system set log_archive_dest_state_4=enable;
System altered.
2.2、备库日志应用情况
--orcldg备库日志应用情况
SYS@orcldg> set line 200
SYS@orcldg> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:45:17 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:45:18 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:45:18 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:19:58 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:34:16 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:25:01 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:34:16 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:45:18 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:45:20 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:16 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:23 IN-MEMORY
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:24 NO
20 rows selected.
--sh_orcl备库日志应用情况.
SYS@sh_orcl> set line 200
SYS@sh_orcl> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
'---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:34:15 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:34:15 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:15 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:23 NO
10 rows selected.
注意：此时orcldg备库349、350序列号日志文件成功传输到备库sh_orcl，并被应用.
2.3、开始测试
2.3.1、接收日志后不应用
2.3.1.1、产生新归档
--sh_orcl备库停止实时应用，但依然正常接收归档日志文件.
SYS@sh_orcl> alter database recover managed standby database cancel;
Database altered.
--主库生成新新归档.
LEO@orcl1> select count(*) from test01;
COUNT(*)
----------
690440
LEO@orcl1> insert into test01 select * from test01;
690440 rows created.
LEO@orcl1> commit;
Commit complete.
LEO@orcl1> conn / as sysdba
Connected.
SYS@orcl1> alter system archive log current;
System altered.
2.3.1.2、备库日志应用情况
--orcldg应用情况
SYS@orcldg> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:45:17 YES
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:45:18 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:45:18 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:19:58 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:34:16 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:34:16 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:25:01 YES
1 351 2023-06-29 17:25:02 2023-06-29 17:46:33 2023-06-29 17:46:33 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 351 2023-06-29 17:25:02 2023-06-29 17:46:33 2023-06-29 17:46:35 NO
1 352 2023-06-29 17:46:33 2023-06-29 17:46:37 2023-06-29 17:46:39 NO
1 352 2023-06-29 17:46:33 2023-06-29 17:46:37 2023-06-29 17:46:38 YES
1 353 2023-06-29 17:46:37 2023-06-29 17:46:55 2023-06-29 17:46:55 YES
1 353 2023-06-29 17:46:37 2023-06-29 17:46:55 2023-06-29 17:46:55 NO
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:45:18 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
'---------- ---------- ------------------- ------------------- ------------------- ---------
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:16 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:45:20 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:23 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:24 NO
2 188 2023-06-29 17:40:24 2023-06-29 17:46:56 2023-06-29 17:46:56 NO
2 188 2023-06-29 17:40:24 2023-06-29 17:46:56 2023-06-29 17:46:56 IN-MEMORY
28 rows selected.
--sh_orcl应用情况
SYS@sh_orcl> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
'---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:34:15 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:34:15 YES
1 351 2023-06-29 17:25:02 2023-06-29 17:46:33 2023-06-29 17:46:36 NO
1 352 2023-06-29 17:46:33 2023-06-29 17:46:37 2023-06-29 17:46:39 NO
1 353 2023-06-29 17:46:37 2023-06-29 17:46:55 2023-06-29 17:46:55 NO
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
'---------- ---------- ------------------- ------------------- ------------------- ---------
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:15 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:23 NO
2 188 2023-06-29 17:40:24 2023-06-29 17:46:56 2023-06-29 17:46:55 NO
14 rows selected.
--系统层面归档日志文件情况.
[oracle@leo-shhisdbdg 2023_06_29]$ ls -ltr
total 158552
-rw-r----- 1 oracle oinstall 197120 Jun 29 16:50 o1_mf_2_184_l9tkg79m_.arc
-rw-r----- 1 oracle oinstall 72704 Jun 29 16:50 o1_mf_2_183_l9tkg7bp_.arc
-rw-r----- 1 oracle oinstall 1010176 Jun 29 16:50 o1_mf_2_182_l9tkg7bn_.arc
-rw-r----- 1 oracle oinstall 22970368 Jun 29 16:50 o1_mf_2_186_l9tkg7l3_.arc
-rw-r----- 1 oracle oinstall 42650112 Jun 29 16:50 o1_mf_2_185_l9tkg7jk_.arc
-rw-r----- 1 oracle oinstall 1024 Jun 29 16:50 o1_mf_1_346_l9tkg868_.arc
-rw-r----- 1 oracle oinstall 1024 Jun 29 16:50 o1_mf_1_345_l9tkg869_.arc
-rw-r----- 1 oracle oinstall 33280 Jun 29 16:50 o1_mf_1_347_l9tkg86d_.arc
-rw-r----- 1 oracle oinstall 3718656 Jun 29 16:50 o1_mf_1_348_l9tkg86z_.arc
-rw-r----- 1 oracle oinstall 163840 Jun 29 17:34 o1_mf_1_350_l9tn0qxg_.arc
-rw-r----- 1 oracle oinstall 3020288 Jun 29 17:34 o1_mf_1_349_l9tn0qxf_.arc
-rw-r----- 1 oracle oinstall 4254720 Jun 29 17:40 o1_mf_2_187_l9tnd7r8_.arc
-rw-r----- 1 oracle oinstall 39873536 Jun 29 17:46 o1_mf_1_351_l9tnqv84_.arc
-rw-r----- 1 oracle oinstall 42650112 Jun 29 17:46 o1_mf_1_352_l9tnqyz2_.arc
-rw-r----- 1 oracle oinstall 1477120 Jun 29 17:46 o1_mf_1_353_l9tnrhjy_.arc
-rw-r----- 1 oracle oinstall 237568 Jun 29 17:46 o1_mf_2_188_l9tnrhyv_.arc
说明：如上所示，o1_mf_1_351_l9tnqv84_.arc、o1_mf_1_352_l9tnqyz2_.arc、o1_mf_1_353_l9tnrhjy_.arc、o1_mf_2_188_l9tnrhyv_.arc归档日志已传输到备库sh_orcl，但此时未应用.
2.3.1.3、删除测试
--在orcldg备库执行.
RMAN> delete noprompt archivelog until time 'sysdate-1/1440';
说明：可将未应用的归档日志文件o1_mf_1_351_l9tnqv84_.arc、o1_mf_1_352_l9tnqyz2_.arc、o1_mf_1_353_l9tnrhjy_.arc删除，但却未能删除o1_mf_2_188_l9tnrhyv_.arc.
RMAN> delete noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_2_188_l9tnrj8r_.arc thread=2 sequence=188
说明：即使o1_mf_2_188_l9tnrj8r_.arc在sh_orcl备库端日志显示已经应用，但orcldg端依然无法删除该归档日志.
2.3.2、无法接收日志
2.3.2.1、产生新归档
--以下测试无法传输归档日志文件的情况.
关闭sh_orcl数据库，使orcldg端的归档日志文件无法传输到备库sh_orcl.
SYS@sh_orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--主库产生新归档日志文件
SYS@orcl1> conn leo/leo;
Connected.
LEO@orcl1> insert into test01 select * from test01;
1380880 rows created.
LEO@orcl1> commit;
Commit complete.
LEO@orcl1> conn / as sysdba
Connected.
SYS@orcl1> alter system archive log current;
System altered.
2.3.2.2、备库日志应用情况
--备库orcldg查看日志应用情况.
SYS@orcldg> select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),to_char(completion_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where first_time>sysdate-1 order by 1,2;
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:45:17 YES
1 346 2023-06-29 15:43:19 2023-06-29 15:43:19 2023-06-29 16:50:16 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:45:18 YES
1 347 2023-06-29 15:43:19 2023-06-29 15:43:24 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:50:16 YES
1 348 2023-06-29 15:43:24 2023-06-29 16:45:17 2023-06-29 16:45:18 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:34:16 YES
1 349 2023-06-29 16:45:17 2023-06-29 17:19:58 2023-06-29 17:19:58 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:34:16 YES
1 350 2023-06-29 17:19:58 2023-06-29 17:25:02 2023-06-29 17:25:01 YES
1 351 2023-06-29 17:25:02 2023-06-29 17:46:33 2023-06-29 17:46:35 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 351 2023-06-29 17:25:02 2023-06-29 17:46:33 2023-06-29 17:46:33 YES
1 352 2023-06-29 17:46:33 2023-06-29 17:46:37 2023-06-29 17:46:38 YES
1 352 2023-06-29 17:46:33 2023-06-29 17:46:37 2023-06-29 17:46:39 YES
1 353 2023-06-29 17:46:37 2023-06-29 17:46:55 2023-06-29 17:46:55 YES
1 353 2023-06-29 17:46:37 2023-06-29 17:46:55 2023-06-29 17:46:55 YES
1 354 2023-06-29 17:46:55 2023-06-29 17:58:11 2023-06-29 17:58:11 YES
1 355 2023-06-29 17:58:11 2023-06-29 17:58:13 2023-06-29 17:58:13 YES
1 356 2023-06-29 17:58:13 2023-06-29 17:58:16 2023-06-29 17:58:16 YES
1 357 2023-06-29 17:58:16 2023-06-29 17:58:21 2023-06-29 17:58:21 YES
1 358 2023-06-29 17:58:21 2023-06-29 17:58:40 2023-06-29 17:58:40 YES
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:45:18 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
2 183 2023-06-29 15:42:49 2023-06-29 15:42:54 2023-06-29 16:50:15 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:45:18 YES
2 184 2023-06-29 15:42:54 2023-06-29 15:43:27 2023-06-29 16:50:15 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:45:18 YES
2 185 2023-06-29 15:43:27 2023-06-29 16:12:47 2023-06-29 16:50:16 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:45:20 YES
2 186 2023-06-29 16:12:47 2023-06-29 16:45:19 2023-06-29 16:50:16 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:24 YES
2 187 2023-06-29 16:45:19 2023-06-29 17:40:24 2023-06-29 17:40:23 YES
2 188 2023-06-29 17:40:24 2023-06-29 17:46:56 2023-06-29 17:46:56 NO
2 188 2023-06-29 17:40:24 2023-06-29 17:46:56 2023-06-29 17:46:56 YES
THREAD# SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' TO_CHAR(COMPLETION_ APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
2 189 2023-06-29 17:46:56 2023-06-29 17:58:17 2023-06-29 17:58:17 YES
2 190 2023-06-29 17:58:17 2023-06-29 17:58:44 2023-06-29 17:58:44 IN-MEMORY
35 rows selected.
--系统层面归档日志文件情况
[oracle@hisdbdg 2023_06_29]$ ls -ltr
total 163296
-rw-r----- 1 oracle oinstall 237568 Jun 29 17:46 o1_mf_2_188_l9tnrj8r_.arc
-rw-r----- 1 oracle oinstall 42644992 Jun 29 17:58 o1_mf_1_354_l9tofm75_.arc
-rw-r----- 1 oracle oinstall 42650112 Jun 29 17:58 o1_mf_1_355_l9tofodj_.arc
-rw-r----- 1 oracle oinstall 42650112 Jun 29 17:58 o1_mf_1_356_l9tofrht_.arc
-rw-r----- 1 oracle oinstall 413184 Jun 29 17:58 o1_mf_2_189_l9tofsb6_.arc
-rw-r----- 1 oracle oinstall 38500864 Jun 29 17:58 o1_mf_1_357_l9tofx44_.arc
-rw-r----- 1 oracle oinstall 89088 Jun 29 17:58 o1_mf_1_358_l9togjqg_.arc
-rw-r----- 1 oracle oinstall 19456 Jun 29 17:58 o1_mf_2_190_l9tognbv_.arc
说明：节点1序列号为354、355、356、357、358，节点2序列号为189、190的归档日志文件为新增，其并未传输到sh_orcl备库.
2.3.2.3、删除测试
--备库orcldg端进行删除测试.
RMAN> delete noprompt archivelog until time 'sysdate-1/1440';
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_1_354_l9tofm75_.arc thread=1 sequence=354
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_1_355_l9tofodj_.arc thread=1 sequence=355
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_1_356_l9tofrht_.arc thread=1 sequence=356
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_1_357_l9tofx44_.arc thread=1 sequence=357
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_1_358_l9togjqg_.arc thread=1 sequence=358
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_2_188_l9tnrj8r_.arc thread=2 sequence=188
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_2_189_l9tofsb6_.arc thread=2 sequence=189
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/2023_06_29/o1_mf_2_190_l9tognbv_.arc thread=2 sequence=190
说明：如上所示，orcldg备库端无法删除sh_orcl备库未接收的归档日志文件.
总结：
a、当orcldg备库的valid_for修改为ONLINE_LOGFILES,ALL_ROLES后，新增归档日志文件并不会通过orcldg备库传输到sh_orcl备库.
b、当orcldg备库的valid_for修改为STANDBY_LOGFILES,ALL_ROLES后,orcldg备库端无法删除sh_orcl备库未接收的归档日志文件.
标签：06,
17,
16,
STANDBY,
29,
LOGFILES,
15,
2023,
备库
From： https://blog.51cto.com/u_12991611/6584790