实验环境:RHEL 5.4 + Oracle 11.2.0.3 现象:日志切换后没找到归档日志目录。
开启归档模式后,如果不设置归档目录,可以看到默认的归档路径为$ORACLE_HOME/dbs/arch:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
手工切换日志,验证归档日志能否成功归档:
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
咦?!这个归档的目录居然都不存在?
[oracle@edbjr2p1 oracle]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
-bash: cd: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch: No such file or directory
退到上一层目录下,发现归档是正常生成的,只不过将arch当成了命名前缀:
[oracle@edbjr2p1 oracle]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@edbjr2p1 dbs]$ ls -lrth
-rw-r----- 1 oracle oinstall 1.5M May 25 15:48 arch1_13_1008804696.dbf
-rw-r----- 1 oracle oinstall 1.0K May 25 15:48 arch1_14_1008804696.dbf
-rw-r----- 1 oracle oinstall 1.5K May 25 15:48 arch1_15_1008804696.dbf
下面就验证下如果要是存在这个归档目录,Oracle会怎么处理呢?
[oracle@edbjr2p1 oracle]$ mkdir /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
SQL> set lines 180
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 16 52428800 512 1 NO CURRENT 2373438 25-MAY-19 2.8147E+14
2 1 14 52428800 512 1 YES INACTIVE 2373431 25-MAY-19 2373434 25-MAY-19
3 1 15 52428800 512 1 YES INACTIVE 2373434 25-MAY-19 2373438 25-MAY-19
SQL> alter system switch logfile;
System altered.
可以看到如果实际存在这个目录,归档就会正常存到这个目录下,而不会再把它作为前缀处理,这还是比较符合我们正常人的思维的:
[oracle@edbjr2p1 arch]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
[oracle@edbjr2p1 arch]$ ls -lrth
total 16K
-rw-r----- 1 oracle oinstall 16K May 25 15:50 1_16_1008804696.dbf
这个知识点虽然看起来有点无聊哈,不过既然遇到了,还是要记录下来给初学者参考。
实际一般我们开启归档后,都会显示设置LOG_ARCHIVE_DEST_n参数去指定规划的归档目录。
n is an integer from 1 to 31. Archive destinations 1 to 10 are available for local or remote locations. Archive destinations 11 to 31 are available for remote locations only.
而设置的具体路径可以是文件系统目录、ASM磁盘组、远端(通过网络服务名)。具体根据实际需求来定。比如:
LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' LOG_ARCHIVE_DEST_2 = 'LOCATION=+DGROUP1/orcl/arc_1' LOG_ARCHIVE_DEST_3 = 'SERVICE=standby1'
LOG_ARCHIVE_DEST_n = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' The keyword USE_DB_RECOVERY_FILE_DEST to indicate the Fast Recovery Area If you configure a Fast Recovery Area (by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters) and do not specify any local archive destinations, the database automatically selects the Fast Recovery Area as a local archive destination and sets LOG_ARCHIVE_DEST_1 to USE_DB_RECOVERY_FILE_DEST.
此外,可以通过设置LOG_ARCHIVE_FORMAT这个参数自定义归档日志的名称(默认为%t_%s_%r.dbf,比如可修改为arc_%t_%s_%r.dbf)
Optionally, set the LOG_ARCHIVE_FORMAT initialization parameter, using %t to include the thread number as part of the file name, %s to include the log sequence number, and %r to include the resetlogs ID (a timestamp value represented in ub4). Use capital letters (%T, %S, and %R) to pad the file name to the left with zeroes.
通过设置LOG_ARCHIVE_DEST_STATE_n参数来控制LOG_ARCHIVE_DEST_n的可用状态:
The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 31) initialization parameter lets you control the availability state of the specified destination (n).