[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