Oracle回滚段

现象

 

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