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


归档错误（ORA-16038）致数据库不能open解决
 
oracle manage 
今天，研发说他们新搭建的Data Guard的主库起不来，让帮忙查一下。 
Sql代码  
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代码  
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来跟踪错误进一步获取有用信息。 
Sql代码  
[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