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

log_archive_dest_n裡的alternate屬性使用須知

相關文章