Oracle控制文件丢失恢复归档模式方法_oracle_

SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /oradata/orcl/control01.ctl, / u01/app/oracle/fast_recovery_a rea/orcl/control02.ctl [oracle@orcl:/oradata/orcl]$ ll /oradata/orcl/control01.ctl -rw-r----- 1 oracle oinstall 9748480 Apr 15 10:01 /oradata/orcl/control01.ctl [oracle@orcl:/oradata/orcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/control02.ctl -rw-r----- 1 oracle oinstall 9748480 Apr 15 10:03 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl --确保开启归档 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /archivelog Oldest online log sequence 10 Current log sequence 12 SQL> select distinct dbms_rowid.rowid_block_number(rowid) from props$; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ------------------------------------ 801 --控制文件内容 --生成控制文件 SQL> alter database backup controlfile to trace as '/home/oracle/ctlbak.ctl'; Database altered. --查看trace内容 [oracle@orcl:/home/oracle]$ cat ctlbak.ctl -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database 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=/archivelog' -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_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 the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- 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 "ORCL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oradata/orcl/redo01.log' SIZE 120M BLOCKSIZE 512, GROUP 2 '/oradata/orcl/redo02.log' SIZE 120M BLOCKSIZE 512, GROUP 3 '/oradata/orcl/redo03.log' SIZE 120M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oradata/orcl/system01.dbf', '/oradata/orcl/sysaux01.dbf', '/oradata/orcl/undotbs01.dbf', '/oradata/orcl/users01.dbf', '/oradata/orcl/example01.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 '/archivelog/1_1_1069941729.dbf'; -- 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 '/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND OFF; -- End of tempfile additions. -- -- Set #2. RESETLOGS 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. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- 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 "ORCL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oradata/orcl/redo01.log' SIZE 120M BLOCKSIZE 512, GROUP 2 '/oradata/orcl/redo02.log' SIZE 120M BLOCKSIZE 512, GROUP 3 '/oradata/orcl/redo03.log' SIZE 120M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oradata/orcl/system01.dbf', '/oradata/orcl/sysaux01.dbf', '/oradata/orcl/undotbs01.dbf', '/oradata/orcl/users01.dbf', '/oradata/orcl/example01.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 '/archivelog/1_1_1069941729.dbf'; -- 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 '/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND OFF; -- End of tempfile additions. -- 

[oracle@orcl:/oradata/orcl]$ rm -rf /u01/app/oracle/fast_recovery_area/orcl/control02.ctl [oracle@orcl:/oradata/orcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/control02.ctl ls: cannot access /u01/app/oracle/fast_recovery_area/orcl/control02.ctl: No such file or directory --开一个新的session，此时数据库已经处于报错状态，无论什么操作都会报错，此时强制关闭数据库 SQL> select open_mode from v$database; select open_mode from v$database * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort ORACLE instance shut down. SQL>

--复制控制文件 [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ cp /oradata/orcl/control01.ctl control02.ctl [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ls control02.ctl --开启数据库 SQL> startup ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 bytes Database mounted. Database opened. 

--通过备份控制文件获取创建控制文件脚本 CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oradata/orcl/redo01.log' SIZE 120M BLOCKSIZE 512, GROUP 2 '/oradata/orcl/redo02.log' SIZE 120M BLOCKSIZE 512, GROUP 3 '/oradata/orcl/redo03.log' SIZE 120M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oradata/orcl/system01.dbf', '/oradata/orcl/sysaux01.dbf', '/oradata/orcl/undotbs01.dbf', '/oradata/orcl/users01.dbf', '/oradata/orcl/example01.dbf' CHARACTER SET AL32UTF8; --开始恢复 SQL> startup nomount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/oradata/orcl/redo01.log' SIZE 120M BLOCKSIZE 512, 9 GROUP 2 '/oradata/orcl/redo02.log' SIZE 120M BLOCKSIZE 512, 10 GROUP 3 '/oradata/orcl/redo03.log' SIZE 120M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/oradata/orcl/system01.dbf', 14 '/oradata/orcl/sysaux01.dbf', 15 '/oradata/orcl/undotbs01.dbf', 16 '/oradata/orcl/users01.dbf', 17 '/oradata/orcl/example01.dbf' 18 CHARACTER SET AL32UTF8; Control file created. SQL> recover database; Me
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：详解Oracle控制文件及日志文件的管理问题_oracle_
                                                                
下一篇：ORACLE分区表转换在线重定义DBMS_REDEFINITION_oracle_