oracle 控制文件损坏恢复笔记

今天早晨在上班的公交车上客户打电话过来系统无法连接到数据库，到公司通过QQ远程控制连接到客户机器，启动cmd,输入sqlplus sys/password as sysdba，连接到oracle输入

sql>select status from v$instance;查询数据库状态

sql>status

      mounted

唉，怎么在mount状态，open一下应该就可以了

sql>alter database open;

报一些控制文件数据文件相关的错误，google一下错误码，控制文件损坏导致无法打开数据库。之后根据http://chinaitpower.com/A/2004-10-15/158033.html处所说的步骤操作，具体如下：

1.ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

      这时候会在udump目录下生成SID_ora_*.trc文件，根据你是在归档还是非归档模式下，选择一段内容
建立创建脚本
我是在非归档模式下，选择第一段内容

2.从SID_ora_*.trc文件中拷贝 Set #1段（非归档模式，归档模式选择Set #2段），新建一个文本文件，命名为create.sql

-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ONLINELOG/O1_MF_1_3SFJSWF5_.LOG'  SIZE 50M,
  GROUP 2 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ONLINELOG/O1_MF_2_3SFJSY00_.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'C:/ORACLEXE/ORADATA/XE/SYSTEM.DBF',
  'C:/ORACLEXE/ORADATA/XE/UNDO.DBF',
  'C:/ORACLEXE/ORADATA/XE/SYSAUX.DBF',
  'C:/ORACLEXE/ORADATA/XE/USERS.DBF',
  'C:/ORACLEXE/APP/ORACLE/PRODUCT/10.2.0/SERVER/DATABASE/CHENDA01.DBF',
  'C:/ORACLEXE/ORADATA/XE/SYSTEM02.DBF'
CHARACTER SET AL32UTF8
;
-- 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:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ARCHIVELOG/2009_04_10/O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'C:/ORACLEXE/APP/ORACLE/FLASH_RECOVERY_AREA/XE/ARCHIVELOG/2009_04_10/O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:/ORACLEXE/ORADATA/XE/TEMP.DBF' REUSE;
-- End of tempfile additions.

3.输入shutdown abort 关闭数据库。

 

4.运行2中创建的create.sql脚本

数据库恢复正常.