log_archive_dest_n里的alternate属性使用须知

原创 Oracle 作者：oliseh 时间：2015-09-22 22:03:35 0 删除编辑

log_archive_dest_n里的alternate属性主要用于指定当前主归档目录的备用目录

下面的设置表示如果/oradata06目录无法写入，那么尝试将归档写入到/oradata01目录
log_archive_dest_5='location=/oradata06'
log_archive_dest_state_5=enable
log_archive_dest_1='location=/oradata01 alternate=log_archive_dest_5';
log_archive_dest_state_1=alternate

实际在使用alternate属性的时候有些trick，不注意的话你会发现alternate目录始终无法用上，看下面测试就明白了

<<<设置reopen=10，alternate=log_archive_dest_5>>>

SYS@tstdb1-SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST reopen=10 alternate=log_archive_dest_5 valid_for=(all_logfiles,all_roles) db_unique_name=tstdb1';

System altered.

select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');SYS@tstdb1-SQL>

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST 10 0 0
5 LOG_ARCHIVE_DEST_5 ALTERNATE /oradata06 300 0 0

###修改LOG_ARCHIVE_DEST_1对应目录的权限为root
chown root.system /oradata06/fra/TSTDB1/archivelog/2015_09_22

###第一次Switch logfile
alter system switch logfile;

###归档并没有生成到LOG_ARCHIVE_DEST_5指定的目录
select name,sequence# from v$archived_log where sequence#>=190;

no rows selected

SYS@tstdb1-SQL> select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST 10 1 0
5 LOG_ARCHIVE_DEST_5 ALTERNATE /oradata06 300 0 0

###第二次Switch logfile 归档依然没有生成到LOG_ARCHIVE_DEST_5指定的目录
alter system switch logfile;

select name,sequence# from v$archived_log where sequence#>=190;

no rows selected

SYS@tstdb1-SQL> select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST 10 1 0
5 LOG_ARCHIVE_DEST_5 ALTERNATE /oradata06 300 0 0

###之后每过5分钟后failure_count往上增1,15分钟后，没有定义max_failure，所以不停的尝试访问LOG_ARCHIVE_DEST_1，导致无法使用到备用目录
SYS@tstdb1-SQL> select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST 10 4 0
5 LOG_ARCHIVE_DEST_5 ALTERNATE /oradata06 300 0 0

<<<设置reopen=10，max_failure=1，alternate=log_archive_dest_5>>>

###重新设置LOG_ARCHIVE_DEST_1，将failure_count清零，这次加上了max_failure=1
SYS@tstdb1-SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST reopen=10 max_failure=1 alternate=log_archive_dest_5 valid_for=(all_logfiles,all_roles) db_unique_name=tstdb1';

System altered.

###注意此时的LOG_ARCHIVE_DEST_1状态从VALID变为了ALTERNATE
SYS@tstdb1-SQL> select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 ALTERNATE USE_DB_RECOVERY_FILE_DEST 10 0 1
5 LOG_ARCHIVE_DEST_5 ALTERNATE /oradata06 300 0 0

###过了大约82秒，log_archive_dest_1再一次归档失败状态被置为DISABLED,failure_count=1,同时我们看到LOG_ARCHIVE_DEST_5=VALID，之前没有能归档的seq# 190~192都生成到了LOG_ARCHIVE_DEST_5目录
SYS@tstdb1-SQL> select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 DISABLED USE_DB_RECOVERY_FILE_DEST 10 1 1
5 LOG_ARCHIVE_DEST_5 VALID /oradata06 300 0 0

SYS@tstdb1-SQL> select name,sequence# from v$archived_log where sequence#>=190;

NAME SEQUENCE#
-------------------------------------------------- ----------
/oradata06/1_190_884446916.dbf 190
/oradata06/1_191_884446916.dbf 191
/oradata06/1_192_884446916.dbf 192

select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

###恢复LOG_ARCHIVE_DEST_1对应目录的权限
chown oracle.oinstall /oradata06/fra/TSTDB1/archivelog/2015_09_22

###switch logfile
alter system switch logfile;

###虽然LOG_ARCHIVE_DEST_1权限已恢复，因其状态还处于DISABLED，所以seq# 193这个log还是生成在了LOG_ARCHIVE_DEST_5对应目录下
SYS@tstdb1-SQL> select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');

DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 DISABLED USE_DB_RECOVERY_FILE_DEST 10 1 1
5 LOG_ARCHIVE_DEST_5 VALID /oradata06 300 0 0

SYS@tstdb1-SQL> select name,sequence# from v$archived_log where sequence#>=190;

NAME SEQUENCE#
-------------------------------------------------- ----------
/oradata06/1_190_884446916.dbf 190
/oradata06/1_191_884446916.dbf 191
/oradata06/1_192_884446916.dbf 192
/oradata06/1_193_884446916.dbf 193

###这时如果要强制让后面生成的archivelog写入LOG_ARCHIVE_DEST_1目录，必须作如下设置
SYS@tstdb1-SQL> show parameter LOG_ARCHIVE_DEST_STATE_1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string ENABLE

SYS@tstdb1-SQL> show parameter LOG_ARCHIVE_DEST_STATE_5

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_5 string ALTERNATE

alter system set LOG_ARCHIVE_DEST_STATE_1=enable; <---重新设置一边虽然设置前后的值是一样的

alter system set log_archive_dest_state_5=ALTERNATE; <---重新设置一边虽然设置前后的值是一样的

###确认LOG_ARCHIVE_DEST_1、LOG_ARCHIVE_DEST_5的状态已分别恢复为VALID、ALTERNATE
select dest_id,dest_name,status,destination,REOPEN_SECS,failure_count,max_failure from v$archive_dest where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_5');
DEST_ID DEST_NAME STATUS DESTINATION REOPEN_SECS FAILURE_COUNT MAX_FAILURE
---------- -------------------- --------- ------------------------------------------------------------ ----------- ------------- -----------
1 LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST 10 0 1
5 LOG_ARCHIVE_DEST_5 ALTERNATE /oradata06 300 0 0

###接下来switch logfile产生的归档就生成到了LOG_ARCHIVE_DEST_1目录下
SYS@tstdb1-SQL> select name,sequence# from v$archived_log where sequence#>=190;

NAME SEQUENCE#
-------------------------------------------------- ----------
/oradata06/1_190_884446916.dbf 190
/oradata06/1_191_884446916.dbf 191
/oradata06/1_192_884446916.dbf 192
/oradata06/1_193_884446916.dbf 193
/oradata06/fra/TSTDB1/archivelog/2015_09_22/o1_mf_ 194 <---新的归档
1_194_1mEKL8nn4_.arc

所以使用log_archive_dest_n里alternative属性时一定要设置好max_failure

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1806642/，如需转载，请注明出处，否则将追究法律责任。

上一篇： log_archive_dest_n参数中reopen、max_failure、net_timeout三个属性的组合效果测试

下一篇：解析一下DataGuard环境里出现archivelog gap的两个场景

请登录后发表评论登录

全部评论

oliseh

不仅仅专注Oracle database技术, member of SHOUG

注册时间：2014-04-06

博文量
128
访问量

1641756

ITPub博客

log_archive_dest_n里的alternate属性使用须知

最新文章