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

设为首页加入收藏

三通it学院：www.santongit.com

聚焦: 网络技术-思科华为 H3C Juniper 编程开发-软件 Web 移动项目 H3C 华为虚拟化 Juniper

生活: 数据库 Oracle MySQL 服务器 Linux Windows MySQL Linux 微软 IBM Oracle Windows

休闲: 云计算大数据 Hadooop OpenStack Spark 虚拟化 Vmware Citrix F5负载均衡 Docker Vmware Hadooop OpenStack Citrix F5负载均衡 Docker

分类: 软考等考美图美工网络营销 Office

三通IT学院 › 门户 ›技术频道› 数据库 › 查看内容

2014-8-20 15:07

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

摘要 : 运行脚本创建控制文件 sqlplus / as sysdba SQL STARTUP NOMOUNT ORACLE instance started. Total System Global Area135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes Dat ...

时光与梦2014-8-20 15:07192621

原作者: 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