oracle控制文件损坏恢复

lipeng

　　一、使用trace文件恢复

　　SQL>>Database> SQL> @gettrace  --得到trace文件的路径 TRACE_FILE_NAME--------------------------------------------------------------------------------/oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc SQL> ![oracle@chanjet23 ~]$ more /oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc /oracle/app/admin/ora10g/udump/ora10g_ora_31270.trcOracle Database 10g Enterprise Edition>With the Partitioning, OLAP and Data Mining optionsORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1System name:    LinuxNode name:      chanjet23Release:        2.6.18-164.el5                   .                   .                   . --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,  GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,  GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M-- STANDBY LOGFILEDATAFILE  '/oradata/ora10g/system01.dbf',  '/oradata/ora10g/undotbs01.dbf',                            .                            .                            . ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'    >-- End of tempfile additions.-- [oracle@chanjet23 udump]$  cd[oracle@chanjet23 ~]$ vim recontro.sql ——拷贝trace文件中有用的语句重建控制文件。如果在之前没有备份的trace文件，我们可以从init{SID}.ora文件中得到数据文件、日志文件、数据库表空间等信息，把init{SID}.ora文件中的内容按以下格式创建重构控制文件脚本 STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,  GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,  GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50MDATAFILE  '/oradata/ora10g/system01.dbf',  '/oradata/ora10g/undotbs01.dbf',  '/oradata/ora10g/sysaux01.dbf',  '/oradata/ora10g/users01.dbf'CHARACTER SET ZHS16GBK;VARIABLE RECNO NUMBER;EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');RECOVER DATABASEALTER SYSTEM ARCHIVE LOG ALL;ALTER DATABASE OPEN;    >STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,  GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,  GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50MDATAFILE  '/oradata/ora10g/system01.dbf',  '/oradata/ora10g/undotbs01.dbf',  '/oradata/ora10g/sysaux01.dbf',  '/oradata/ora10g/users01.dbf'CHARACTER SET ZHS16GBK;VARIABLE RECNO NUMBER;EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');RECOVER DATABASE USING BACKUP CONTROLFILEALTER DATABASE OPEN RESETLOGS;    >"recontro.sql" 49L, 1641C 已写入[oracle@chanjet23 ~]$ sqlplus / as sysdba SQL*Plus:> Copyright (c) 1982, 2005, Oracle.  All rights reserved.  Connected to:Oracle Database 10g Enterprise Edition>With the Partitioning, OLAP and Data Mining options SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> set echo on       ——打开显示输出SQL> @recontro        ——使用刚建立的重构控制文件的脚本重建控制文件SQL> STARTUP NOMOUNTORACLE instance started. Total System Global Area  209715200 bytesFixed>Variable>Database Buffers          130023424 bytesRedo Buffers                2973696 bytesSQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS     ARCHIVELOG  2      MAXLOGFILES 16  3      MAXLOGMEMBERS 3  4      MAXDATAFILES 100  5      MAXINSTANCES 8  6      MAXLOGHISTORY 292  7  LOGFILE  8    GROUP 1 '/oradata/ora10g/redo01.log'    >  9    GROUP 2 '/oradata/ora10g/redo02.log'    > 10    GROUP 3 '/oradata/ora10g/redo03.log'    > 11  DATAFILE 12    '/oradata/ora10g/system01.dbf', 13    '/oradata/ora10g/undotbs01.dbf', 14    '/oradata/ora10g/sysaux01.dbf', 15    '/oradata/ora10g/users01.dbf' 16  CHARACTER SET ZHS16GBK 17  ; Control file created. SQL> VARIABLE RECNO NUMBER;SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF'); PL/SQL procedure successfully completed. SQL> RECOVER DATABASE SQL>> System> SQL>> Database> SQL>>  2      > Tablespace> SQL> STARTUP NOMOUNTORA-01081: cannot start already-running ORACLE - shut it down firstSQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG  2      MAXLOGFILES 16  3      MAXLOGMEMBERS 3  4      MAXDATAFILES 100  5      MAXINSTANCES 8  6      MAXLOGHISTORY 292  7  LOGFILE  8    GROUP 1 '/oradata/ora10g/redo01.log'    >  9    GROUP 2 '/oradata/ora10g/redo02.log'    > 10    GROUP 3 '/oradata/ora10g/redo03.log'    > 11  DATAFILE 12    '/oradata/ora10g/system01.dbf', 13    '/oradata/ora10g/undotbs01.dbf', 14    '/oradata/ora10g/sysaux01.dbf', 15    '/oradata/ora10g/users01.dbf' 16  CHARACTER SET ZHS16GBK 17  ;CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG SQL> VARIABLE RECNO NUMBER;SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF'); PL/SQL procedure successfully completed. SQL> RECOVER DATABASE USING BACKUP CONTROLFILE SQL>> SQL>>  2      >ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf' Tablespace>SQL> select open_mode from v$database; OPEN_MODE----------READ WRITE