控制文件全部损坏,有trace文件,数据库shutdown abort的情况下。(试验前请先备份数据库)
1、查询控制文件相关信息
- SQL> col name format a50
- SQL> select * from v$controlfile;
- STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
- ------- -------------------------------------------------- --- ---------- --------------
- C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/CONTROL01.CT NO 16384 450
- C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/CONTROL02.CT NO 16384 450
- C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/CONTROL03:.CT NO 16384 450
2、backup controlfile to trace
- SQL> alter session set tracefile_identifier=kipos;
- 会话已更改。
- SQL> alter database backup controlfile to trace;
- 数据库已更改。
- SQL>
3、模拟破坏控制文件,可以用UltraEdit随便建一个文件,然后另存为控制文件
4、强制执行检查点,系统发现控制文件错误,然后shutdown abort
- SQL> alter system checkpoint;
- alter system checkpoint
- *
- 第 1 行出现错误:
- ORA-00227: 控制文件中检测到损坏的块: (块 , # 块 )
- SQL> select * from v$controlfile;
- ERROR:
- ORA-03114: 未连接到 ORALCE
5、查看alter_sid.log,也可以查看相关trace文件
- alter_sid.log
- Mon Oct 13 15:39:13 2008
- Errors in file c:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc0_2204.trc:
- ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
- ORA-00202: control file: 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/CONTROL01.CTL'
- Master background archival failure: 227
- Mon Oct 13 15:39:50 2008
- Hex dump of (file 0, block 1) in trace file c:/oracle/product/10.2.0/admin/orcl/bdump/orcl_ckpt_2720.trc
- Corrupt block relative dba: 0x00000001 (file 0, block 1)
- Completely zero block found during control file header read
6、恢复控制文件-启动到mount状态,然后根据trace文件重建控制文件
- SQL> startup nomount;
- ORACLE 例程已经启动。
- Total System Global Area 167772160 bytes
- Fixed Size 1247900 bytes
- Variable Size 75498852 bytes
- Database Buffers 88080384 bytes
- Redo Buffers 2945024 bytes
- SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
- 2 MAXLOGFILES 16
- 3 MAXLOGMEMBERS 3
- 4 MAXDATAFILES 100
- 5 MAXINSTANCES 8
- 6 MAXLOGHISTORY 292
- 7 LOGFILE
- 8 GROUP 1 (
- 9 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG'
- 10 ) SIZE 50M,
- 11 GROUP 2 (
- 12 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'
- 13 ) SIZE 50M,
- 14 GROUP 3 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG' SIZE 50M
- 15 -- STANDBY LOGFILE
- 16 DATAFILE
- 17 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSTEM01.DBF',
- 18 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF',
- 19 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSAUX01.DBF',
- 20 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/USERS01.DBF',
- 21 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF',
- 22 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/MIS.DBF',
- 23 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/UNDO_1',
- 24 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/INDEX_TBS.DBF'
- 25 CHARACTER SET ZHS16GBK
- 26 ;
- 控制文件已创建
7、尝试打开数据库,出现ora-01110错误,原因是shutdown abort关闭,需要恢复数据库
- SQL> alter database open;
- alter database open
- *
- 第 1 行出现错误:
- ORA-01113: ?? 1 ??????
- ORA-01110: ???? 1: 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSTEM01.DBF'
8、恢复并打开数据库
- SQL> recover database;
- 完成介质恢复。
- SQL> alter database open;
- 数据库已更改。