目录一、查看控制文件路径和内容二、模拟控制文件丢失情况：1、未丢失全部控制文件2、全部丢失，控制文件存在备份通过备份文件重建控制文件通过rman恢复控制文件3、全部丢失，没有备份三、建议
一、查看控制文件路径和内容SQL> show parameter control_filesNAME 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 listDatabase log mode No Archive ModeAutomatic archival DisabledArchive destination /archivelogOldest online log sequence 10Current log sequence 12SQL> 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 PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE \”ORCL\” NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE 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 LOGFILEDATAFILE \’/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 PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE \”ORCL\” RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE 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 LOGFILEDATAFILE \’/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.–
当前数据库存在两份控制文件，分别位于数据文件路径和快速闪回区路径。
二、模拟控制文件丢失情况：
1、未丢失全部控制文件
删除快闪区中的控制文件，强制关闭数据库。
[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 fileORA-00202: control file:\’/u01/app/oracle/fast_recovery_area/orcl/control02.ctl\’ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> shutdown abortORACLE 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]$ lscontrol02.ctl–开启数据库SQL> startupORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 452988064 bytesDatabase Buffers 1140850688 bytesRedo Buffers 7319552 bytesDatabase mounted.Database opened.
2、全部丢失，控制文件存在备份
有两种方式：
1、通过备份的文件进行重建控制文件
2、通过rman恢复控制文件，需要通过resetlogs方式打开数据库
通过备份文件重建控制文件–通过备份控制文件获取创建控制文件脚本CREATE CONTROLFILE REUSE DATABASE \”ORCL\” NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE 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 LOGFILEDATAFILE \’/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 nomountORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 452988064 bytesDatabase Buffers 1140850688 bytesRedo Buffers 7319552 bytesSQL> 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;Media recovery complete.SQL> alter database open;Database altered.SQL> ALTER TABLESPACE TEMP ADD TEMPFILE \’/oradata/orcl/temp01.dbf\’;Tablespace altered.SQL> select open_mode from v$database;OPEN_MODE——————–READ WRITE
成功恢复控制文件。
通过rman恢复控制文件
rman备份控制文件
RMAN> backup current controlfile format \’/home/oracle/ctrl.ora\’;Starting backup at 15-APR-21allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=29 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setchannel ORA_DISK_1: starting piece 1 at 15-APR-21channel ORA_DISK_1: finished piece 1 at 15-APR-21piece handle=/home/oracle/ctrl.ora tag=TAG20210415T104457 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 15-APR-21RMAN> list backup of controlfile;List of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time——- —- — ———- ———– ———— —————1 Full 9.64M DISK 00:00:01 15-APR-21 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20210415T104457 Piece Name: /home/oracle/ctrl.ora Control File Included: Ckp SCN: 1219869 Ckp time: 15-APR-21
rm删除所有控制文件，尝试添加数据文件，强制关闭数据库，尝试开启到mount状态。
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ rm control02.ctl [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ rm /oradata/orcl/control01.ctl [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ lltotal 0[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ll /oradata/orcl/control01.ctlls: cannot access /oradata/orcl/control01.ctl: No such file or directorySQL> alter tablespace USERS add datafile;alter tablespace USERS add datafile*ERROR at line 1:ORA-00603: ORACLE server session terminated by fatal errorORA-00210: cannot open the specified control fileORA-00202: control file: \’/oradata/orcl/control01.ctl\’ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00210: cannot open the specified control fileORA-00202: control file: \’/oradata/orcl/control01.ctl\’ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Process ID: 2333Session ID: 1 Serial number: 9SQL> shutdown abort;ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 452988064 bytesDatabase Buffers 1140850688 bytesRedo Buffers 7319552 bytesORA-00205: error in identifying control file, check alert log for more info
开启数据库到nomount状态，rman恢复控制文件，恢复数据库
–开启到nomount状态SQL> startup nomountORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 452988064 bytesDatabase Buffers 1140850688 bytesRedo Buffers 7319552 bytes–rman恢复控制文件RMAN> restore controlfile from \’/home/oracle/ctrl.ora\’;Starting restore at 15-APR-21using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/oradata/orcl/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctlFinished restore at 15-APR-21–开启数据库到mount状态SQL> alter database mount;Database altered.–恢复数据库RMAN> recover database;Starting recover at 15-APR-21released channel: ORA_DISK_1Starting implicit crosscheck backup at 15-APR-21allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKFinished implicit crosscheck backup at 15-APR-21Starting implicit crosscheck copy at 15-APR-21using channel ORA_DISK_1Finished implicit crosscheck copy at 15-APR-21searching for all files in the recovery areacataloging files…no files catalogedusing channel ORA_DISK_1RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT rowRMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rowsORACLE error from target database: ORA-19922: there is no parent row with id 0 and level 1starting media recoveryarchived log for thread 1 with sequence 12 is already on disk as file /oradata/orcl/redo03.logarchived log file name=/oradata/orcl/redo03.log thread=1 sequence=12media recovery complete, elapsed time: 00:00:00Finished recover at 15-APR-21
通过resetlogs方式打开数据库
SQL> alter database open resetlogs;Database altered.
3、全部丢失，没有备份
删除全部控制文件，并且没有备份控制文件
SQL> select open_mode from v$database;select open_mode from v$database *ERROR at line 1:ORA-00210: cannot open the specified control fileORA-00202: control file: \’/oradata/orcl/control01.ctl\’ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> shutdown abortORACLE instance shut down.
手动创建控制文件
–通过spfile或者pfile文件获取信息–1.db_name[oracle@orcl:/home/oracle]$ grep \”db_name\” pfile.ora *.db_name=\’orcl\’–2.字符集（通过dd if查看system01数据文件）dd if=system01.dbf of=lucifer bs=8192 skip=801–3.获取数据文件和日志文件名称[oracle@orcl:/oradata/orcl]$ lltotal 2083264-rw-r—–. 1 oracle oinstall 328343552 Apr 15 15:14 example01.dbf-rw-r—–. 1 oracle oinstall 125829632 Apr 15 15:14 redo01.log-rw-r—–. 1 oracle oinstall 125829632 Apr 15 15:14 redo02.log-rw-r—–. 1 oracle oinstall 125829632 Apr 15 15:17 redo03.log-rw-r—–. 1 oracle oinstall 545267712 Apr 15 15:14 sysaux01.dbf-rw-r—–. 1 oracle oinstall 786440192 Apr 15 15:14 system01.dbf-rw-r—–. 1 oracle oinstall 30416896 Apr 15 15:14 temp01.dbf-rw-r—–. 1 oracle oinstall 89137152 Apr 15 15:14 undotbs01.dbf-rw-r—–. 1 oracle oinstall 5251072 Apr 15 15:14 users01.dbf–重建控制文件（这里不需要加临时文件，开启数据库之后需要reuse）–开启数据库到nomountSTARTUP NOMOUNT;–创建控制文件CREATE CONTROLFILE REUSE DATABASE \”ORCL\” NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE 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 LOGFILEDATAFILE \’/oradata/orcl/system01.dbf\’, \’/oradata/orcl/sysaux01.dbf\’, \’/oradata/orcl/undotbs01.dbf\’, \’/oradata/orcl/users01.dbf\’, \’/oradata/orcl/example01.dbf\’CHARACTER SET AL32UTF8;–恢复数据库RECOVER DATABASE;–执行过程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 19 ;Control file created.SQL> RECOVER DATABASE;Media recovery complete.
打开数据库，成功恢复控制文件
SQL> ALTER DATABASE OPEN;Database altered.–这里需要将临时文件重用SQL> ALTER TABLESPACE TEMP ADD TEMPFILE \’/oradata/orcl/temp01.dbf\’ REUSE;Tablespace altered.
三、建议
1、创建多份控制文件，建议分别存放在多个存储的多个文件夹上，防止文件被误删或者存储损坏。
2、打开归档模式。
3、保存当前库的控制文件生成脚本（包括重要信息：dbname，字符集，文件路径）。
4、定期备份数据库文件，防止丢失，建议将备份放置到源端进行保存。
以上就是Oracle控制文件丢失恢复归档模式的详细内容，更多关于Oracle控制文件恢复归档模式的资料请关注悠久资源其它相关文章！
