ORACLE控制文件丢失的处理方法.doc

内部技术资料 【保密】oracle控制文件故障及修复方法环境说明：本文以oracle 10.1为演示环境。oracle主目录：C:oracleproduct10.1.0db_1数据库SID为ORWL，非归档模式。数据文件路径为C:oracleproduct10.1.0oradataORWL测试准备：建一个名为ORWL的数据库。停掉oracleserviceORWL服务。将C:oracleproduct10.1.0oradataORWL整个文件夹备份。为了在以下的测试中让oracle的相关提示显示英文而不是问号，需要将注册表项HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraDb10g_home1下NLS_LANG的值由SIMPLIFIED CHINESE_CHINA.ZHS16GBK改为american_CHINA.ZHS16GBK1.模拟单个控制文件损坏。错误模拟：将C:oracleproduct10.1.0oradataORWLCONTROL01.CTL删除运行以下命令：运行cmdMicrosoft Windows 版本 5.2.3790(C) 版权所有 1985-2003 Microsoft Corp.C:Documents and Settingschenzqcd 命令解释：回到根目录，减少路径长度，看起来方便一些。C:set oracle_sid=ORWL命令解释：设置默认数据库C:oradim -startup -sid ORWL命令解释：启动数据库C:sqlplus /nolog命令解释：以nolog方式打开sqlplusSQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 30 17:37:31 2010Copyright (c) 1982, 2004, Oracle. All rights reserved.SQL conn / as sysdba命令解释：用sysdba方式连接到默认数据库Connected.SQL select status from v$instance;命令解释：查看当前数据库的状态。started表示数据库处理nomount状态。STATUS-STARTEDSQL alter database mount;命令解释：将数据库由nomount状态变为mount状态。alter database mount*ERROR at line 1:ORA-00205: error in identifying controlfile, check alert log for more info命令解释：根据命令返回提示，查看C:oracleproduct10.1.0adminORWLbdumpalert_ORWL.log在最后面，发现有这么一段：Fri Apr 30 17:37:01 2010Controlfile identified with block size 0Fri Apr 30 17:37:01 2010ORA-205 signalled during: alter database mount exclusive.Fri Apr 30 17:38:10 2010alter database mountFri Apr 30 17:38:10 2010ORA-00202: ?: C:ORACLEPRODUCT10.1.0ORADATAORWLCONTROL01.CTLORA-27041: ?OSD-04002: unable to open fileO/S-Error: (OS 2) 系统找不到指定的文件。提示很明确，control01.ctl文件无法找到。解释方法：将还存在的control02.ctl复制为control01.ctl。SQL exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsC:net stop oracleserviceORWLOracleServiceORWL 服务正在停止.OracleServiceORWL 服务已成功停止。C:copy C:oracleproduct10.1.0oradataORWLCONTROL02.CTL C:oracleproduct10.1.0oradataORWLCONTROL01.CTL已复制 1 个文件。C:oradim -startup -sid ORWLC:sqlplus /nologSQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 30 17:55:58 2010Copyright (c) 1982, 2004, Oracle. All rights reserved.SQL conn / as sysdbaConnected.SQL select status from v$instance;STATUS-OPEN命令解释：open说明数据库修复已经完成。2.模拟全部控制文件的丢失。运行以下命令cmdMicrosoft Windows 版本 5.2.3790(C) 版权所有 1985-2003 Microsoft Corp.C:Documents and Settingschenzqcd C:oradim -startup -sid ORWLC:sqlplus /nologSQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 30 18:03:06 2010Copyright (c) 1982, 2004, Oracle. All rights reserved.SQL conn / as sysdbaConnected.SQL alter database backup controlfile to trace;Database altered.命令解释：上一条命令的意思是将控制文件的建立脚本备份到trace文件中。当控制文件全部丢失后，我们就可以通过获取的脚本来重建控制文件。在实际有复用户数据库时，全部控制文件丢失后是无法备份控制文件的，我们另外建一个数据库来获取相关的脚本后再作修改。在C:oracleproduct10.1.0adminORWLudump目录中，按日期时间排序，找到最后修改的文件，打开后，从后往前找，找到以下部分的内容：STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE ORWL RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 454LOGFILE GROUP 1 C:ORACLEPRODUCT10.1.0ORADATAORWLREDO01.LOG SIZE 10M, GROUP 2 C:ORACLEPRODUCT10.1.0ORADATAORWLREDO02.LOG SIZE 10M, GROUP 3 C:ORACLEPRODUCT10.1.0ORADATAORWLREDO03.LOG SIZE 10M, GROUP 4 C:ORACLEPRODUCT10.1.0ORADATAORWLLOGORWL1A.LOG SIZE 5M, GROUP 5 C:ORACLEPRODUCT10.1.0ORADATAORWLLOGORWL2A.LOG SIZE 5M, GROUP 6 C:ORACLEPRODUCT10.1.0ORADATAORWLLOGORWL3A.LOG SIZE 5M, GROUP 7 C:ORACLEPRODUCT10.1.0ORADATAORWLLOGORWL4A.LOG SIZE 5M, GROUP 8 C:ORACLEPRODUCT10.1.0ORADATAORWLLOGORWL5A.LOG SIZE 5M- STANDBY LOGFILEDATAFILE C:ORACLEPRODUCT10.1.0ORADATAORWLSYSTEM01.DBF, C:ORACLEPRODUCT10.1.0ORADATAORWLUNDOTBS01.DBF, C:ORACLEPRODUCT10.1.0ORADATAORWLSYSAUX01.DBF, C:ORACLEPRODUCT10.1.0ORADATAORWLUSERS01.DBF, C:ORACLEPRODUCT10.1.0ORADATAORWLGADATA0001_T.DAT, C:ORACLEPRODUCT10.1.0ORADATAORWLRBORWL.DBF, C:ORACLEPRODUCT10.1.0ORADATAORWLORACLEUSER_T.DAT, C:ORACLEPRODUCT10.1.0ORADATAORWLGAA8120091130_TCHARACTER SET ZHS16GBK;- Commands to re-create incarnation table- Below log names MUST be changed to existing filenames on- disk. Any one log file from each branch can be used to- re-create incarnation records.- ALTER DATABASE REGISTER LOGFILE C:ORACLEPRODUCT10.1.0DB_1RDBMSARC00001_0520387048.001;- ALTER DATABASE REGISTER LOGFILE C:ORACLEPRODUCT10.1.0DB_1RDBMSARC00001_0711720249.001;- Recovery is required if any of the datafiles are restored backups,- or if the last shutdown was not normal or immediate.RECOVER DATABASE USING BACKUP CONTROLFILE- Database can now be opened zeroing the online logs.ALTER DATABASE OPEN RESETLOGS;- Commands to add tempfiles to temporary tablespaces.- Online tempfiles have complete space information.- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE C:ORACLEPRODUCT10.1.0ORADATAORWLTEMP01.DBF SIZE 232783872 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;ALTER TABLESPACE GADATA0001_TMP ADD TEMPFILE C:ORACLEPRODUCT10.1.0ORADATAORWLGADATA0001_TMP.DAT SIZE 5242880 REUSE AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M;将以上内容保存为d:recreacontrol.sql回到dos窗口SQL exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsC:net stop oracleserviceORWLOracleServiceORWL 服务正在停止.OracleServiceORWL 服务已成功停止。现象模拟：删除三个控制文件C:del C:oracleproduct10.1.0oradataORWLCONTROL01.CTLC:del C:oracleproduct10.1.0oradataORWLCONTROL02.CTLC:del C:oracleproduct10.1.0oradataORWLCONTROL03.CTLC:oradim -startup -sid ORWLC:sqlplus /nologSQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 30 18:28:27 2010Copyright (c) 1982, 2004, Oracle. All rights reserved.SQL conn / as sysdbaConnected.SQL select status from v$instance;STATUS-STARTEDSQL alter database mount;alter database mount*ERROR at line 1:ORA-00205: error in identifying controlfile, check alert log for more info根据提示查看C:oracleproduct10.1.0adminORWLbdumpalert_ORWL.log的最后部分，发现以下内容：Fri Apr 30 18:28:17 2010Controlfile identified with block size 0Fri Apr 30 18:28:17 2010ORA-205 signalled during: alter database mount exclusive.Fri Apr 30 18:29:23 2010alter database mountFri Apr 30 18:29:23 2010ORA-00202: ?: C:ORACLEPRODUCT10.1.0ORADATAORWLCONTROL01.CTLORA-27041: ?OSD-04002: unable to open fileO/S-Error: (OS 2) 系统找不到指定的文件。关闭数据库：SQLshutdown immediate;将上面获得的脚本中的第二行中的resetlogs修改为