oracle重建控制文件
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: {