<转>ora-01112
日他妈的会宁的数据在测试健康龙卡的时候搞坏了，今天再日鬼不好就不睡觉了。。。。妈的。不过还是胡日鬼好了。。转载一下。
年关事情多，最近设备运行极度不正常，好像是过年了，也想罢工休息一样！昨日一台oracle db主板坏掉，数据库也随之崩溃，oracle服务器重启后，无法open，以下是恢复过程！
/home/oracle$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 15:50:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 616562688 bytes
Fixed Size 1220868 bytes
Variable Size 167775996 bytes
Database Buffers 440401920 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>
检查alter日志，发现如下错误：
Fri Jan 18 09:57:19 2008
ALTER DATABASE RECOVER LOGFILE '/opt/oracle/oradata/oradb/redo03.log'
Fri Jan 18 09:57:19 2008
Media Recovery Log /opt/oracle/oradata/oradb/redo03.log
Fri Jan 18 09:57:19 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_p002_6556.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [4099], [6101], [], [], [], []
Fri Jan 18 09:57:19 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_p000_6552.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [3770], [6255], [], [], [], []
Fri Jan 18 09:57:19 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_p002_6556.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 4099)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 517
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [4099], [6101], [], [], [], []
Fri Jan 18 09:57:20 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_p000_6552.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 3770)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 482
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [3770], [6255], [], [], [], []
Recovery interrupted!
Recovered data files to a consistent state at change 2433115
Fri Jan 18 09:57:24 2008
Media Recovery failed with error 12801
ORA-283 signalled during: ALTER DATABASE RECOVER LOGFILE '/opt/oracle/oradata/oradb/redo03.log' ...
Fri Jan 18 09:57:24 2008
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Shutting down instance: further logons disabled
初步断定是当前redo由于突然断电造成损坏，重启db后，造成系统表空间不一致，db无法起动。
经过询问和检查，数据库没有备份，并且处于非归档模式。
解决办法只有一个加上隐含参数起动试试！
_ALLOW_RESETLOGS_CORRUPTION = TRUE
SQL> show parameter spfile
NAME TYPE VALUE
-------------------- ---------------- ---------------------
spfile string /opt/oracle/product/10.2.0.1/d
b_1/dbs/spfileoradb.ora
SQL> show parameter '_all'*
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
_allow_resetlogs_corruption boolean
FALSE
fast_start_parallel_rollback string
LOW
parallel_adaptive_multi_user boolean
TRUE
parallel_automatic_tuning boolean
FALSE
parallel_execution_message_size integer
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
2148
parallel_instance_group string
parallel_max_servers integer
80
parallel_min_percent integer
0
parallel_min_servers integer
0
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
parallel_server boolean
FALSE
parallel_server_instances integer
1
parallel_threads_per_cpu integer
2
recovery_parallelism integer
0
SQL>
SQL> select status,name from v$datafile;
STATUS
--------------
NAME
--------------------------------------------------------------------------------
SYSTEM
/opt/oracle/oradata/oradb/bak/system01.dbf
RECOVER
/opt/oracle/oradata/oradb/undotbs01.dbf
RECOVER
/opt/oracle/oradata/oradb/sysaux01.dbf
STATUS
--------------
NAME
--------------------------------------------------------------------------------
RECOVER
/opt/oracle/oradata/oradb/users01.dbf
RECOVER
/opt/oracle/oradata/oradb/tools01.dbf
RECOVER
/opt/oracle/oradata/oradb/indx01.dbf
STATUS
--------------
NAME
--------------------------------------------------------------------------------
RECOVER
/opt/oracle/oradata/oradb/cicrodb.dbf
7 rows selected.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc
ORA-00280: change 2433115 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc
ORA-00280: change 2433115 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'
上面显示系统表空间需要恢复
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc
ORA-00280: change 2433115 for thread 1 is in sequence #42
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: '/opt/oracle/oradata/oradb/bak/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'
仍然提示要恢复系统表空间！
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
加入隐含参数！
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
/home/oracle$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 15:56:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 616562688 bytes
Fixed Size 1220868 bytes
Variable Size 167775996 bytes
Database Buffers 440401920 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
--------------
/opt/oracle/oradata/oradb/bak/system01.dbf
SYSTEM
/opt/oracle/oradata/oradb/undotbs01.dbf
RECOVER
/opt/oracle/oradata/oradb/sysaux01.dbf
RECOVER
NAME
--------------------------------------------------------------------------------
STATUS
--------------
/opt/oracle/oradata/oradb/users01.dbf
RECOVER
/opt/oracle/oradata/oradb/tools01.dbf
RECOVER
/opt/oracle/oradata/oradb/indx01.dbf
RECOVER
NAME
--------------------------------------------------------------------------------
STATUS
--------------
/opt/oracle/oradata/oradb/cicrodb.dbf
RECOVER
7 rows selected.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2433115 generated at 01/17/2008 04:00:40 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc
ORA-00280: change 2433115 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/ORADB/archivelog/2008_01_18/o1_mf_1_42_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
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: '/opt/oracle/oradata/oradb/bak/system01.dbf'
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open RESETLOGS;
此时，系统好像hang住了一样，很长一段时间没有反映，过了一会，报错如下！
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
此时查看alter日志信息：又出现2662错误，
Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10369.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []
Fri Jan 18 15:58:21 2008
Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10369.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []
Fri Jan 18 15:58:21 2008
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 10369
ORA-1092 signalled during: alter database open RESETLOGS...
SQL>
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
再次重启登录：
/home/oracle$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 15:58:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 616562688 bytes
Fixed Size 1220868 bytes
Variable Size 167775996 bytes
Database Buffers 440401920 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/oracle/oradata/oradb/bak/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
检查日志如下：爽呀，又出现了ORA-00600 中的4194错误，
Database Characterset is UTF8
Fri Jan 18 16:00:14 2008
Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10443.trc:
ORA-00600: internal error code, arguments: [4194], [38], [34], [], [], [], [], []
Fri Jan 18 16:00:14 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:
ORA-00600: internal error code, arguments: [4194], [59], [57], [], [], [], [], []
Doing block recovery for file 2 block 1135
Block recovery from logseq 2, block 43 to scn 2453192
Fri Jan 18 16:00:15 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log
Fri Jan 18 16:00:15 2008
Doing block recovery for file 2 block 281
Block recovery from logseq 2, block 45 to scn 2453193
Fri Jan 18 16:00:15 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log
Fri Jan 18 16:00:15 2008
Block recovery stopped at EOT rba 2.47.16
Block recovery completed at rba 2.47.16, scn 0.2453190
Fri Jan 18 16:00:15 2008
Block recovery stopped at EOT rba 2.47.16
Block recovery completed at rba 2.47.16, scn 0.2453190
Fri Jan 18 16:00:15 2008
Doing block recovery for file 2 block 153
Block recovery from logseq 2, block 43 to scn 2453187
Fri Jan 18 16:00:15 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log
Fri Jan 18 16:00:15 2008
Doing block recovery for file 2 block 105
Block recovery from logseq 2, block 45 to scn 2453189
Fri Jan 18 16:00:15 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log
Fri Jan 18 16:00:15 2008
Block recovery completed at rba 2.45.16, scn 0.2453189
Fri Jan 18 16:00:15 2008
Block recovery completed at rba 2.47.16, scn 0.2453190
Fri Jan 18 16:00:15 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [59], [57], [], [], [], [], []
Fri Jan 18 16:00:15 2008
Errors in file /opt/oracle/admin/oradb/udump/oradb_ora_10443.trc:
ORA-00600: internal error code, arguments: [4193], [688], [1013], [], [], [], [], []
Fri Jan 18 16:00:15 2008
DEBUG: Replaying xcb 0x43625908, pmd 0x4379add8 for failed op 8
Doing block recovery for file 2 block 612
No block recovery was needed
Fri Jan 18 16:00:16 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:
ORA-00600: internal error code, arguments: [4194], [58], [56], [], [], [], [], []
Fri Jan 18 16:00:17 2008
Doing block recovery for file 2 block 930
Block recovery from logseq 2, block 47 to scn 2453198
Fri Jan 18 16:00:17 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log
Block recovery stopped at EOT rba 2.48.16
Block recovery completed at rba 2.48.16, scn 0.2453197
Doing block recovery for file 2 block 9
Block recovery from logseq 2, block 47 to scn 2453196
Fri Jan 18 16:00:18 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/oradb/redo01.log
Block recovery completed at rba 2.48.16, scn 0.2453197
Fri Jan 18 16:00:18 2008
Errors in file /opt/oracle/admin/oradb/bdump/oradb_smon_10429.trc:
ORA-01595: error freeing extent (2) of rollback segment (1))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [58], [56], [], [], [], [], []
4149错误一般根undo有关系，因为当前redo损坏，undo数据也大都不一致了。
设置隐含参数：
将undo改变成手工管理的，然后重启数据库。
/home/oracle$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on星期五 1月 18 16:11:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 616562688 bytes
Fixed Size 1220868 bytes
Variable Size 167775996 bytes
Database Buffers 440401920 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> show parameter undo
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
SQL> alter system set undo_management='manual' scope=spfile;
System altered.
然后重启数据库
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
/home/oracle$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 18 16:13:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 616562688 bytes
Fixed Size 1220868 bytes
Variable Size 167775996 bytes
Database Buffers 440401920 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> col name format a30
SQL> select status,name from v$datafile;
STATUS NAME
-------------- ------------------------------
SYSTEM /opt/oracle/oradata/oradb/bak/system01.dbf
ONLINE /opt/oracle/oradata/oradb/undotbs01.dbf
ONLINE /opt/oracle/oradata/oradb/sysaux01.dbf
ONLINE /opt/oracle/oradata/oradb/users01.dbf
STATUS NAME
-------------- ------------------------------
ONLINE /opt/oracle/oradata/oradb/tools01.dbf
ONLINE /opt/oracle/oradata/oradb/indx01.dbf
ONLINE /opt/oracle/oradata/oradb/cicrodb.dbf
7 rows selected.
SQL> show parameter undo
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string
UNDOTBS1
SQL> alter database open;
Database altered.
SQL> !top
正常起动！
总结：
系统掉电一般引起redo损坏，最糟糕的就是引起当前redo损坏，针对当前redo损坏，又没有备份，数据库处于非归档模式时，只能通过加入隐含参数方式恢复！
一般步骤如下：
1：首先加入_ALLOW_RESETLOGS_CORRUPTION = TRUE隐含参数，最后resetlogs打开数据库，如果无法打开，进入第二步。
2：查看日志信息，根据具体报错信息，确认方法，本例是出现ORA-00600: internal error code, arguments: [2662], [0], [2433141], [0], [2443300], [12595947], [], []，
ORA-600 [2662] "Block SCN is ahead of Current SCN"，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了.于是想到使用ADJUST_SCN事件来调整当前的SCN，使其大于dependent SCN. 此时我们可以通过Oracle的内部事件来调整SCN:
解决这个问题一般方法是：
调整SCN有两种常用方法:
1.通过immediate trace name方式(在数据库Open状态下，即通过用_allow_resetlogs_corruption=TRUE隐含参数打开数据库之后报错600)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
2.通过10015事件(在数据库无法打开，mount状态下)
alter session set events '10015 trace name adjust_scn level x';
注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。
本文通过此种方法设置无效，scn没有得到提升！
3：一般情况下redo损坏的时候，undo数据也大都不一致，因此通过scn调整完毕，resetlogs数据库后还会出现ORA-00600: internal error code, arguments: [4194]错误，这个错误可以通过设置undo解决：
设置隐含参数：_corrupted_rollback_segments
将undo改变成手工管理的，然后重启数据库，
本例只是将undo改变成手工管理，问题解决。
