现象
cif老出现
tx_process start!
Update SQL:[LOCK TABLE TB_SeqGen IN EXCLUSIVE MODE] Update SQL:[insert into TB_srvLog(FD_srvDate,FD_srvTime,FD_srvCode,FD_srvName,FD_srvState,FD_srvDetl) values(:FD_srvDate,:FD_srvTime,:FD_srvCode,:FD_srvName,:FD_srvState,:FD_srvDetl)] txcode=[C202] txname=[个人帐户登] retstr=[009011] txdetail=[0027101316032861|0]
(2009-08-20 07:21:30) ===============================================================================
(2009-08-20 07:21:30) 业务处理结束, 回应处理结果.
(2009-08-20 07:21:30) 回应报文:[TX000000009011]
造成网银/callcenter
(debug) (JDBC ) = ExecuteQuery:[select C3XXZS from C3INDF where C3XXM = '9011']
(debug) (JDBC ) = ExecuteQuery Result:1
(debug) (Info ) ==========================================
(debug) (Info ) = 平台应用模块抛出异常
(debug) (Info ) = 错误码 :[ERP9999]
(debug) (Info ) = 错误描述 :[交易错误,错误原因:CIF返回:数据库错误]
(debug) (Info ) = 错误处理方法:[交易错误,错误原因:@]
应用日志查不出来什么东西,009011是交易错误不是数据库错误,
查错
$ Cd /home/oracle/app/oracle/admin/netbank/bdump
$ more alert_netbank.log
Thu Aug 20 19:36:58 2009
Errors in file /home/oracle/app/oracle/admin/netbank/bdump/netbank_smon_970.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/data8/oradata/netbank/undotbs01.dbf'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
说明回滚段错误
操作
SQL> conn /as sysdba
Connected.
SQL> select name ,status from v$datafile;
结果
/data8/oradata/netbank/undotbs01.dbf
RECOVER
/data8/oradata/netbank/drsys01.dbf
ONLINE
………
操作
SQL> select name from v$tablespace;
结果
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
DRSYS
INDX
TOOLS
USERS
EJSADMIN
NETBANK
CIF
NETBANKTMP
NAME
------------------------------
CIFTMP
WEBSITE
WEBTMP
UNDO_TBS2
操作
SQL> alter tablespace UNDOTBS1 offline;
alter tablespace UNDOTBS1 offline
*
ERROR at line 1:
ORA-01191: file 2 is already offline - cannot do a normal offline
ORA-01110: data file 2: '/data8/oradata/netbank/undotbs01.dbf'
操作
SQL> select segment_name, status from dba_rollback_segs where tablespace_name=
2 'UNDOTBS1';
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERY
解决思路:
SQL> select name ,status from v$datafile;
结果
/data8/oradata/netbank/undotbs01.dbf
RECOVER
说明这个undotbs01.dbf 还处在online状态,还被oracle使用,要把这个回滚段offline,然后修复或者删掉
SQL>shutdown immediate
SQL>startup mount
SQL>alter database datafile 2 offline;
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 1 thread 1:
'/home/oracle/app/oracle/oradata/netbank/redo01.log'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database datafile 2 online;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/data8/oradata/netbank/undotbs01.dbf'
解决
SQL> alter database datafile 2 offline drop;
Database altered.
SQL> alter database datafile 12 offline drop;
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN