Oracle 重建控制文件和undo损坏的处理二

Liuxuebo

运行脚本创建控制文件
sqlplus / as sysdba
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  135337420 bytes
Fixed Size                   452044 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "sxzdb" NORESETLOGS
  DATAFILE
   '/oracle/oradata/system01.dbf',
   '/oracle/oradata/undotbs01.dbf',
   '/oracle/oradata/sysaux01.dbf',
   '/oracle/oradata/users01.dbf',
   '/oracle/oradata/tbsaddm.dbf'
  NOARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
  LOGFILE
   GROUP 1 '/oracle/oradata/szxdb/redo01.log',
   GROUP 2 '/oracle/oradata/szxdb/redo02.log',
   GROUP 3 '/oracle/oradata/szxdb/redo03.log'
   CHARACTER SET GBK  ;
Control file created.
这时要恢复数据库，此时恢复数据库若是突然断电造成的报一下错误 查看alter.log

Fri Jan 18 09:57:19 2008
ALTER DATABASE RECOVER    LOGFILE '/oracle/oradata/szxdb/redo03.log'
Fri Jan 18 09:57:19 2008
Media Recovery Log /oracle/oradata/szxdb/redo03.log
Fri Jan 18 09:57:19 2008
Errors in file /oracle/admin/szxdb/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 /oracle/admin/szxdb/bdump/szxdb_p000_6552.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [3770], [6255], [], [], [], []
Fri Jan 18 09:57:19 2008
Errors in file /oracle/admin/szxdb/bdump/szxdb_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: '/oracle/oradata/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 /oracle/admin/szxdb/bdump/szxdb_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: '/oracle/oradata/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 '/oracle/oradata/szxdb/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> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate
SQL>      quit
$sqlplus  / as sysdba
SQL> startup mount
Database mounted.
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 :
/oracle/flash_recovery_area/SZXDB/archivelog/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
'/oracle/flash_recovery_area/SZXDB/archivelog/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
'/oracle/flash_recovery_area/SZXDB/archivelog/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: '/oracle/oradata/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 /oracle/admin/szxdb/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 /oracle/admin/szxdb/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

stit2012

3915290595111

NAXLCQ

好东西谢谢分享好东西谢谢分好东西谢谢分享好东西谢谢分享好东西谢谢分享享好东西谢谢分享

nick_liu24

11111111111111111111111111111111111111

小小学者

11111111111111111111

zwwhb

好东西，学习了

bud407

感谢楼主的分享。厉害。

zhujiang1071

谢谢楼主的分享

dong_xinrui

谢谢楼主分享，学习了。

dong_xinrui

谢谢楼主分享，学习了。