[20200219]log_archive_dest_1定義問題.txt

[20200219]log_archive_dest_1定義問題.txt

--//別人的系統,log_archive_dest_1定義問題，在測試環境模擬，並做一個記錄。

1.環境：
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter log_archive_dest_1
NAME               TYPE    VALUE
------------------ ------- ----------------------------------------------------------------------------------------------------
log_archive_dest_1 string  LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book

SYS@book> show parameter recovery
NAME                                 TYPE         VALUE
------------------------------------ ------------ ----------------------------------
db_recovery_file_dest                string       /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer  60G
recovery_parallelism                 integer      0
--//我的伺服器設定FRA，我一般不建議使用FRA作為歸檔的目的。

2.建立錯誤:
SYS@book> alter system set log_archive_dest_1='LOCATION=DB_RECOVERY_FILE_DEST MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book';
System altered.

SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            DB_RECOVERY_FILE_DEST
Oldest online log sequence     856
Next log sequence to archive   858
Current log sequence           858
--//當前歸檔seq=858.

SYS@book> alter system archive log current;
System altered.

SYS@book> column name format a100
SYS@book> select name from V$ARCHIVED_LOG where dest_id=1 and sequence#=858;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/DB_RECOVERY_FILE_DEST1_858_896605872.dbf

--//實際上檔案放在了/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs目錄下,字首是DB_RECOVERY_FILE_DEST。
--//而不是在引數db_recovery_file_dest定義的/u01/app/oracle/fast_recovery_area目錄下。

3.實際上定義的引數log_archive_dest_1錯誤。
--//準確的定義如下：應該寫成USE_DB_RECOVERY_FILE_DEST,不然oracle把它當作建立在$ORACLE_HOME/dbs的目錄下的字首部分。
SYS@book> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book';
System altered.

SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     857
Next log sequence to archive   859
Current log sequence           859

SYS@book> alter system archive log current;
System altered.

SYS@book> select name from V$ARCHIVED_LOG where dest_id=1 and sequence#=859;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/BOOK/archivelog/2020_02_19/o1_mf_1_859_h4svkr4m_.arc
--//現在正確了。我檢查alert.log ，發現以前定義是正確的，不知道為什麼又修改使用FRA。
--//我個人不建議使用FRA存放歸檔日誌。

4.收尾：
--//改正回來。
SYS@book> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book';
System altered.

SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archivelog/book/
Oldest online log sequence     858
Next log sequence to archive   860
Current log sequence           860

SYS@book> alter system archive log current;
System altered.

SYS@book> select name from V$ARCHIVED_LOG where dest_id=1 and sequence#>=858;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/DB_RECOVERY_FILE_DEST1_858_896605872.dbf
/u01/app/oracle/fast_recovery_area/BOOK/archivelog/2020_02_19/o1_mf_1_859_h4svkr4m_.arc
/u01/app/oracle/archivelog/book/1_860_896605872.dbf