归档错误（ORA-16038）致数据库不能open

今天，研发说他们新搭建的Data Guard的主库起不来，让帮忙查一下。

SQL> conn /as sysdba
Connected.
SQL> 
SQL> 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 24552
Session ID: 191 Serial number: 61

远程登录到数据库所在的服务器，在open数据库的时候跟踪告警日志文件看其错误信息。

引用

[oracle@rac5 trace]$ tail -0f alert_prodb.log
Wed Sep 18 15:54:52 2013
alter database open
Wed Sep 18 15:54:52 2013
LGWR: STARTING ARCH PROCESSES
Wed Sep 18 15:54:52 2013
ARC0 started with pid=20, OS id=24555
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
ARCH: Error 19504 Creating archive log file to '/u01/app/oracle/oradata/prodb/archivelog/1_6_823603498.dbf'
Wed Sep 18 15:54:53 2013
ARC1 started with pid=21, OS id=24558
Errors in file /u01/app/oracle/diag/rdbms/prodb/prodb/trace/prodb_ora_24552.trc:
ORA-16038: log 3 sequence# 6 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/prodb/redo03.log'
Wed Sep 18 15:54:53 2013
ARC2 started with pid=22, OS id=24560
USER (ospid: 24552): terminating the instance due to error 16038
Wed Sep 18 15:54:54 2013
System state dump requested by (instance=1, osid=24552), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/prodb/prodb/trace/prodb_diag_19278.trc
Dumping diagnostic data in directory=[cdmp_20130918155454], requested by (instance=1, osid=24552), summary=[abnormal instance termination].
Instance terminated by USER, pid = 24552

很显然，从告警信息可以知道在线日志3号文件不能成功归档导致数据不能成功open。通常来说，出现此类错误，我们可以从两个方面考虑：
1.  归档路径的存储空间不足了
2.  当前用户没有归档路径下的读写权限

查看归档路径

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/prodb/archivelog
Oldest online log sequence     6
Next log sequence to archive   6
Current log sequence           8

查看磁盘空间

引用

[root@rac5 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       50G   11G   37G  22% /
tmpfs                 3.9G  224K  3.9G   1% /dev/shm
/dev/sda1             485M   38M  422M   9% /boot
/dev/mapper/VolGroup-lv_home
                      189G  2.7G  177G   2% /home

归档文件是存放在根目录下的，但是根目录的可用空间还有37G。所以在线日志不能正常归档显然不是“归档路径的存储空间不足”造成的。

查询归档路径/u01/app/oracle/oradata/prodb/archivelog的属主、权限

引用

[oracle@rac5 ~]$ cd /u01
[oracle@rac5 u01]$ ls -ld
drwxr-xr-x 3 root root 4096 8? 16 10:16 .
[oracle@rac5 u01]$ cd /u01/app/oracle/
[oracle@rac5 oracle]$ ls -ld
drwxr-xr-x 9 oracle oinstall 4096 8? 16 10:44 .
[oracle@rac5 oracle]$ cd /u01/app/oracle/oradata/prodb/archivelog
[oracle@rac5 archivelog]$ ls -ld
drwxr-xr-x 2 root root 4096 9? 18 09:56 .

很显然「文件夹u01、archivelog的属主没有正确设置」，造成oracle用户无权限在该目录下写归档。正确改为oracle.oinstall后，数据库正常启动。

BTW:
在分析在线日志文件不能成功归档的原因时，我们可以采用errorstack来跟踪错误进一步获取有用信息。

[oracle@rac5 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 18 16:36:51 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> 
SQL> 
SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
MOUNTED

SQL> alter session set tracefile_identifier='16038error';

Session altered.

SQL> alter session set events '16038 trace name errorstack level 3';

Session altered.

SQL> alter database open;
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 25550
Session ID: 191 Serial number: 3


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

引用

[oracle@rac5 ~]$
[oracle@rac5 ~]$ find /u01/app/ -name '*16038error*'
/u01/app/oracle/diag/rdbms/prodb/prodb/trace/prodb_ora_25550_16038error.trm
/u01/app/oracle/diag/rdbms/prodb/prodb/trace/prodb_ora_25550_16038error.trc
[oracle@rac5 ~]$

查看跟踪文件，注意红色字体

引用

[oracle@rac5 ~]$ more /u01/app/oracle/diag/rdbms/prodb/prodb/trace/prodb_ora_25550_16038error.trc
Trace file /u01/app/oracle/diag/rdbms/prodb/prodb/trace/prodb_ora_25550_16038error.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/db
System name:    Linux
Node name:      rac5
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: prodb
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 25550, image: oracle@rac5 (TNS V1-V3)


*** 2013-09-18 16:38:19.409
*** SESSION ID:(191.3) 2013-09-18 16:38:19.409
*** CLIENT ID:() 2013-09-18 16:38:19.409
*** SERVICE NAME:() 2013-09-18 16:38:19.409
*** MODULE NAME:(sqlplus@rac5 (TNS V1-V3)) 2013-09-18 16:38:19.409
*** ACTION NAME:() 2013-09-18 16:38:19.409

Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Failed to create file '/u01/app/oracle/oradata/prodb/archivelog/1_6_823603498.dbf' (file not accessible?)
*** 2013-09-18 16:38:19.410 4320 krsh.c
ARCH: Error 19504 Creating archive log file to '/u01/app/oracle/oradata/prodb/archivelog/1_6_823603498.dbf'