ora-4193/4194故障处理一则
ora-4193/4194故障处理一则
在线日志丢失，强制打开数据库后，导致undo redo 不一致
当前处于 curren t的 redolog 损坏，这里尝试设置隐含参数 "_allow_resetlogs_corruption" =true 来跳过open时的一致性检查，来强制打开数据库。
SQL> alter database clear logfile group 4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance lixora (thread 1)
ORA-00312: online log 4 thread 1:
'/oracle/flash_recovery_area/LIXORA/onlinelog/o1_mf_4_92cyr3h3_.log'
SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;
System altered.
----关闭数据库并重启至mount状态：
SQL> startup force
ORACLE instance started.
Total System Global Area 583008256 bytes
Fixed Size 2022504 bytes
Variable Size 180355992 bytes
Database Buffers 398458880 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1:
'/oracle/flash_recovery_area/LIXORA/onlinelog/o1_mf_4_92cyr3h3_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> recover database until cancel;
ORA-00279: change 5630464 generated at 10/21/2013 09:17:01 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/LIXORA/archivelog/2013_10_21/o1_mf_1_84_%u_.arc
ORA-00280: change 5630464 for thread 1 is in sequence #84
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata/lixora/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
----这里不知道什么原因断开了和数据库的通信
SQL> alter database open resetlogs;
ERROR:
ORA-03114: not connected to ORACLE
----再次连接尝试去open resetlogs
SQL> startup mount;
ORACLE instance started.
Total System Global Area 583008256 bytes
Fixed Size 2022504 bytes
Variable Size 180355992 bytes
Database Buffers 398458880 bytes
Redo Buffers 2170880 bytes
Database mounted.
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 open;
alter database open
*
ERROR at line 1:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [22], [39], [], [], [], [],
[]
-----开始报ora-600 错误：
ORA-600[4194]/[4193]，第一感觉就是undo出现问题。
4193：表示undo和redo不一致（Arg [a] Undo record seq number，Arg [b] Redo record seq number ）；
4194：表示也是undo和redo不一致（Arg [a] Maximum Undo record number in Undo block，Arg [b] Undo record number from Redo block）
查看alert日志，如下：
Mon Oct 21 09:50:53 2013
Errors in file /oracle/admin/lixora/bdump/lixora_q001_8303.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [1442], [1446], [], [], [], [], []
Mon Oct 21 09:50:53 2013
Doing block recovery for file 2 block 630
Block recovery from logseq 2, block 73 to scn 5650681
Mon Oct 21 09:50:53 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/lixora/redo01.log
Block recovery completed at rba 2.76.16, scn 0.5650685
Doing block recovery for file 2 block 105
Block recovery from logseq 2, block 73 to scn 5650895
Mon Oct 21 09:50:53 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/lixora/redo01.log
Block recovery completed at rba 2.350.16, scn 0.5650896
Mon Oct 21 09:51:07 2013
Errors in file /oracle/admin/lixora/udump/lixora_ora_8410.trc:
ORA-00600: internal error code, arguments: [4194], [21], [28], [], [], [], [], []
Mon Oct 21 09:51:07 2013
Errors in file /oracle/admin/lixora/udump/lixora_ora_8408.trc:
ORA-00600: internal error code, arguments: [4193], [1620], [1623], [], [], [], [], []
Mon Oct 21 09:51:12 2013
Doing block recovery for file 2 block 172
----尝试直接设置了下面参数，数据库就意外的open成功，这位朋友比较幸运
undo_tablespace=SYSTEM
undo_management=MANUAL
既然库已经open，然后新建undo空间，删除出问题的undo，做如下修改，数据库恢复完成
undo_tablespace=新undo
undo_management=AUTO
如果出现极端的情况可能需要做如下处理:
1.使用_offline_rollback_segments和_corrupted_rollback_segments屏蔽掉有问题的undo segment
2.继续可能出现ora-600[2662]，需要推进scn
SQL> create pfile='/tmp/pfile.ora' from spfile; File created. SQL> shutdown immediate ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4193], [3393], [3397], [], [], [], [], [] SQL> shutdow abort ORACLE instance shut down. SQL> startup pfile='/tmp/pfile.ora' ORACLE instance started. Total System Global Area 583008256 bytes Fixed Size 2022504 bytes Variable Size 184550296 bytes Database Buffers 394264576 bytes Redo Buffers 2170880 bytes Database mounted. Database opened. SQL> SQL> SQL> SQL> select * from dual; D - X
-----创建新的undo 表空间 undotbs02
SQL> create undo tablespace undotbs02 2 datafile '/oracle/oradata/lixora/undotbs02.dbf' 3 size 50M 4 EXTENT MANAGEMENT local; Tablespace created. SQL> alter system set undo_tablespace=undotbs02; ------------注意这里不能直接设置，因为使用的是pfile alter system set undo_tablespace=undotbs02 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-30014: operation only supported in Automatic Undo Management mode SQL> alter system set undo_tablespace=undotbs02 scope=spfile; alter system set undo_tablespace=undotbs02 scope=spfile * ERROR at line 1: ORA-32001: write to SPFILE requested but no SPFILE specified at startup SQL> shutdwon immediate SP2-0734: unknown command beginning "shutdwon i..." - rest of line ignored. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. ---编辑pfile [oracle@localhost lixora]$ vi /tmp/pfile.ora lixora.__db_cache_size=394264576 lixora.__java_pool_size=4194304 lixora.__large_pool_size=4194304 lixora.__shared_pool_size=171966464 lixora.__streams_pool_size=4194304 #*._allow_resetlogs_corruption=TRUE *.audit_file_dest='/oracle/admin/lixora/adump' *.background_dump_dest='/oracle/admin/lixora/bdump' *.compatible='10.2.0.1.0' *.control_files='/oracle/oradata/lixora/control01.ctl','/oracle/oradata/lixora/control02.ctl','/oracle/oradata/lixora/control03.ctl' *.core_dump_dest='/oracle/admin/lixora/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='lixora' *.db_re
