备份异常引发的ORA-01438，ORA-20242故障解析

早晨检查备份，发现一台ERP的测试机备份异常，检查日志发现如下错误

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of crosscheck command at 02/17/2017 12:22:58

ORA-00604: error occurred at recursive SQL level 1

ORA-01438: value larger than specified precision allowed for this column

ORA-20242: No archived logs in the range specified

看到604和1438这样的异常第一时间感觉很诧异，一个普普通通的备份不应该会有这样报错提示啊。分析了一下。。原来备份脚本在执行crosscheck和delete expired时报的错误，于是将这两个命令注释掉，重启发起了备份，成功了。

那么问题来了，crosscheck和delete expired都是rman中很常见的命令（这里解释一下，crosscheck是交叉校验备份集，delete expired是同步catalog与物理备份，清理过期的数据字典，这两条命令是配合使用的），那么为什么会引起某个表的列值不够大呢。

于是trace了一下rman命令：

RMAN> sql "alter session set tracefile_identifier=''rman_verify01438''";

RMAN> sql "alter session set events ''10046 trace name context forever,level 12''";

RMAN> sql "alter session set events ''1438 trace name errorstack level 10''";

RMAN> set echo on;

RMAN> crosscheck archivelog all

RMAN> exit;

检查trace文件发现如下信息：

<<<<<<<<<<<<<<<<Q01_ora_18582_rman_verify01438.trc

 

----- Error Stack Dump -----

ORA-01438: value larger than specified precision allowed for this column

ORA-20242: No archived logs in the range specified

ORA-06512: at "SYS.DBMS_RCVMAN", line 9172

ORA-06512: at "SYS.DBMS_RCVMAN", line 18221

ORA-06512: at line 1

----- Current SQL Statement for this session (sql_id=5w48h65mm7zu7) -----

INSERT INTO STATS$USER_LOG VALUES( USER, SYS_CONTEXT('USERENV','SESSIONID'), SYS_CONTEXT('USERENV','HOST'), NULL, NULL, NULL, SYSDATE, TO_CHAR(SYSDATE, 'hh24:mi:ss'), NULL, NULL, NULL )

----- PL/SQL Stack -----

怀疑是插入stats$user_log这个表时引发的，那么看一下这个表的owner和结构

SQL> select owner , object_type, status from dba_objects where object_name='STATS$USER_LOG';


OWNER       OBJECT_TYPE   STATUS
------------------------------ ------------------- -------
SYSTEM       TABLE   VALID


SQL> desc system.STATS$USER_LOG
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID    VARCHAR2(30)
 SESSION_ID    NUMBER(8)
 HOST    VARCHAR2(30)
 LAST_PROGRAM    VARCHAR2(48)
 LAST_ACTION    VARCHAR2(32)
 LAST_MODULE    VARCHAR2(32)
 LOGON_DAY    DATE
 LOGON_TIME    VARCHAR2(10)
 LOGOFF_DAY    DATE
 LOGOFF_TIME    VARCHAR2(10)
 ELAPSED_MINUTES    NUMBER(8)

由于插入的是sessionid和host，因此怀疑sessionid长度超限，那么尝试修改了字段类型，不限number的位数

SQL> alter table system.stats$user_log modify session_id number;

 

Table altered.

执行命令，发现依然报错，不过这次的错误代码变了

RMAN> crosscheck archivelog all;

 

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=929 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of crosscheck command at 02/17/2017 15:20:40

ORA-04045: errors during recompilation/revalidation of SYSTEM.LOGON_AUDIT_TRIGGER2

ORA-01031: insufficient privileges

ORA-20242: No archived logs in the range specified

 

RMAN> delete expired archivelog all;

 

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=929 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of delete command at 02/17/2017 15:20:58

ORA-04045: errors during recompilation/revalidation of SYSTEM.LOGON_AUDIT_TRIGGER2

ORA-01031: insufficient privileges

ORA-20242: No archived logs in the range specified

根据报错信息，发现这个一个trigger，跟之前怀疑的吻合，trigger触发了insert导致插入失败，那么修改字段type，需要将trigger重新进行编译

alter trigger SYSTEM.LOGON_AUDIT_TRIGGER2 compile;

编译之后，一切正常，没有报错信息了。

RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=622 device type=DISK
specification does not match any archived log in the repository

RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=622 device type=DISK
specification does not match any archived log in the repository

总结：

LOGON_AUDIT_TRIGGER2这个trigger应该是自定义的，看名字是用来做登陆审计，里面包含了表stats$user_log， 而这个表的sessionid 列值默认设置的比较小。

当rman操作触发此trigger时，写入的session id值大于允许的列值导致错误。当表更改后，trigger需要重新编译一下生效。所以进行以下修改后，触发的审计trigger正常运行，Rman命令正常。 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24862754/viewspace-2133819/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24862754/viewspace-2133819/