1. 在mount状态下获得trc文件启动到mounted状态idle>startup mountORACLE instance started. Total System Global Area 1653518336 bytesFixed Size 2253784 bytesVariable Size 1006636072 bytesDatabase Buffers 637534208 bytesRedo Buffers 7094272 bytesDatabase mounted.2. 获取trc文件idle>alter database backup controlfileto trace as ‘/home/oracle/control_trace.trc’; Database altered.3. 文件内容如下– The following are current System-scopeREDO Log Archival related– parameters and can be included in thedatabase initialization file.–注明数据库启动时检查的信息– LOG_ARCHIVE_DEST=”– LOG_ARCHIVE_DUPLEX_DEST=”—- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf—- DB_UNIQUE_NAME=”orcl”—- LOG_ARCHIVE_CONFIG=’SEND, RECEIVE,NODG_CONFIG’– LOG_ARCHIVE_MAX_PROCESSES=4– STANDBY_FILE_MANAGEMENT=MANUAL– STANDBY_ARCHIVE_DEST=?/dbs/arch– FAL_CLIENT=”– FAL_SERVER=”—-LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/11.2.0/db_1/dbs/arch’– LOG_ARCHIVE_DEST_1=’MANDATORY NOREOPENNODELAY’– LOG_ARCHIVE_DEST_1=’ARCH NOAFFIRMEXPEDITE NOVERIFY SYNC’– LOG_ARCHIVE_DEST_1=’NOREGISTERNOALTERNATE NODEPENDENCY’– LOG_ARCHIVE_DEST_1=’NOMAX_FAILURENOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME’–LOG_ARCHIVE_DEST_1=’VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)’– LOG_ARCHIVE_DEST_STATE_1=ENABLE —- Below are two sets of SQL statements,each of which creates a new– control file and uses it to open thedatabase. The first set opens– the database with the NORESETLOGS optionand should be used only if– the current versions of all online logsare available. The second– set opens the database with theRESETLOGS option and should be used– if online logs are unavailable.– The appropriate set of statements can becopied from the trace into– a script file, edited as necessary, andexecuted when there is a– need to re-create the control file.—- Set #1. NORESETLOGS case—- The following commands will create a newcontrol file and use it– to open the database.– Data used by Recovery Manager will belost.– Additional logs may be required formedia recovery of offline– Use this only if the current versions ofall online logs are– available.– After mounting the created controlfile,the following SQL– statement will place the database in theappropriate– protection mode:– ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT–创建控制文件需要用到的脚本CREATE CONTROLFILE REUSE DATABASE”ORCL” NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’ SIZE 50M BLOCKSIZE 512– STANDBY LOGFILEDATAFILE ‘/u01/app/oracle/oradata/orcl/system01.dbf’, ‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’, ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’, ‘/u01/app/oracle/oradata/orcl/users01.dbf’, ‘/u01/app/oracle/oradata/orcl/example01.dbf’CHARACTER SET WE8MSWIN1252; — Commands to re-create incarnation table– Below log names MUST be changed toexisting filenames on– disk. Any one log file from each branchcan be used to– re-create incarnation records.– ALTER DATABASE REGISTER LOGFILE’/u01/app/oracle/11.2.0/db_1/dbs/arch1_1_824297850.dbf’;– ALTER DATABASE REGISTER LOGFILE’/u01/app/oracle/11.2.0/db_1/dbs/arch1_1_897435168.dbf’;– Recovery is required if any of thedatafiles are restored backups,– or if the last shutdown was not normalor immediate.RECOVER DATABASE — Database can now be opened normally.ALTER DATABASE OPEN; — Commands to add tempfiles to temporarytablespaces.– Online tempfiles have complete spaceinformation.– Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE’/u01/app/oracle/oradata/orcl/temp01.dbf’ REUSE;– End of tempfile additions.– Set #2. RESETLOGS case—- The following commands will create a newcontrol file and use it– to open the database.– Data used by Recovery Manager will belost.– The contents of online logs will be lostand all backups will– be invalidated. Use this only if onlinelogs are damaged. — After mounting the created controlfile,the following SQL– statement will place the database in theappropriate– protection mode:– ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE”ORCL” RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’ SIZE 50M BLOCKSIZE 512– STANDBY LOGFILEDATAFILE ‘/u01/app/oracle/oradata/orcl/system01.dbf’, ‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’, ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’, ‘/u01/app/oracle/oradata/orcl/users01.dbf’, ‘/u01/app/oracle/oradata/orcl/example01.dbf’CHARACTER SET WE8MSWIN1252; — Commands to re-create incarnation table– Below log names MUST be changed toexisting filenames on– disk. Any one log file from each branchcan be used to– re-create incarnation records.– ALTER DATABASE REGISTER LOGFILE’/u01/app/oracle/11.2.0/db_1/dbs/arch1_1_824297850.dbf’;– ALTER DATABASE REGISTER LOGFILE’/u01/app/oracle/11.2.0/db_1/dbs/arch1_1_897435168.dbf’;– Recovery is required if any of thedatafiles are restored backups,– or if the last shutdown was not normalor immediate.RECOVER DATABASE USING BACKUP CONTROLFILE — Database can now be opened zeroing theonline logs.ALTER DATABASE OPEN RESETLOGS; — Commands to add tempfiles to temporarytablespaces.– Online tempfiles have complete spaceinformation.– Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE’/u01/app/oracle/oradata/orcl/temp01.dbf’ REUSE;– End of tempfile additions.4. 查看控制文件的位置idle>select name from v$controlfile; NAME——————————————————————————-/u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control02.ctl关闭数据库idle>shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.5. 重命名控制文件，使其失效[oracle@orcl orcl]$ mv control01.ctlcontrol01.ctl_bak[oracle@orcl orcl]$ mv control02.ctlcontrol02.ctl_bak6. 编辑trc文件获取创建控制文件的语句CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS NOARCHIVELOG –使用NORESETLOGS 选项 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1’/u01/app/oracle/oradata/orcl/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2’/u01/app/oracle/oradata/orcl/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3’/u01/app/oracle/oradata/orcl/redo03.log’ SIZE 50M BLOCKSIZE 512– STANDBY LOGFILEDATAFILE ‘/u01/app/oracle/oradata/orcl/system01.dbf’, ‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’, ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’, ‘/u01/app/oracle/oradata/orcl/users01.dbf’, ‘/u01/app/oracle/oradata/orcl/example01.dbf’CHARACTER SET WE8MSWIN12527. 启动到nomountidle>startup nomountORACLE instance started. Total System Global Area 1653518336 bytesFixed Size 2253784 bytesVariable Size 1006636072 bytesDatabase Buffers 637534208 bytesRedo Buffers 7094272 bytes8. 执行创建控制文件的语句idle>startup nomountORACLE instance started. Total System Global Area 1653518336 bytesFixed Size 2253784 bytesVariable Size 1006636072 bytesDatabase Buffers 637534208 bytesRedo Buffers 7094272 bytesCREATE CONTROLFILE REUSE DATABASE”ORCL” NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’ SIZE 50M BLOCKSIZE 512– STANDBY LOGFILEDATAFILE ‘/u01/app/oracle/oradata/orcl/system01.dbf’, ‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’, ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’, ‘/u01/app/oracle/oradata/orcl/users01.dbf’, ‘/u01/app/oracle/oradata/orcl/example01.dbf’ 18 CHARACTER SET WE8MSWIN1252 19 ; Control file created.9. 修复数据库并改为打开状态idle>RECOVER DATABASE;Media recovery complete.idle>ALTER DATABASE OPEN; Database altered. 注：重建控制文件中未使用NORESETLOGS时出现的错误idle>recover databaseORA-00283: recovery session canceled due toerrorsORA-01610: recovery using the BACKUPCONTROLFILE option must be done idle>recover database using backupcontrolfile;ORA-00279: change 973052 generated at12/03/2015 06:40:51 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/11.2.0/db_1/dbs/arch1_4_897435168.dbfORA-00280: change 973052 for thread 1 is insequence #4 Specify log: {=suggested |filename | AUTO | CANCEL}cancelMedia recovery cancelled.idle>recover database using backupcontrolfile until cancel;ORA-00279: change 973052 generated at 12/03/201506:40:51 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/11.2.0/db_1/dbs/arch1_4_897435168.dbfORA-00280: change 973052 for thread 1 is insequence #4 Specify log: {=suggested |filename | AUTO | CANCEL}autoORA-00308: cannot open archived log’/u01/app/oracle/11.2.0/db_1/dbs/arch1_4_897435168.dbf’ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3 ORA-00308: cannot open archived log’/u01/app/oracle/11.2.0/db_1/dbs/arch1_4_897435168.dbf’ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3 ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to beconsistentORA-01110: data file 1:’/u01/app/oracle/oradata/orcl/system01.dbf’