代码改变世界

咦?Oracle归档文件存哪了?

2019-05-25 18:01 AlfredZhao 阅读(...) 评论(...) 编辑 收藏

实验环境:RHEL 5.4 + Oracle 11.2.0.3
现象:日志切换后没找到归档日志目录。

1.查看归档日志路径

开启归档模式后,如果不设置归档目录,可以看到默认的归档路径为$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

2.日志切换后并未找到归档目录

手工切换日志,验证归档日志能否成功归档:

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

3.创建归档目录后再次观察

下面就验证下如果要是存在这个归档目录,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).

  • ENABLE indicates that the database can use the destination.
  • DEFER indicates that the location is temporarily disabled.
  • ALTERNATE indicates that the destination is an alternate. The availability state of an alternate destination is DEFER. If its parent destination fails, the availability state of the alternate becomes ENABLE. ALTERNATE cannot be specified for destinations LOG_ARCHIVE_DEST_11 to LOG_ARCHIVE_DEST_31.