数据库教程: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;  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> backup current controlfile format '/home/oracle/ctrl.ora';  starting backup at 15-apr-21  allocated channel: ora_disk_1  channel ora_disk_1: sid=29 device type=disk  channel ora_disk_1: starting full datafile backup set  channel ora_disk_1: specifying datafile(s) in backup set  including current control file in backup set  channel ora_disk_1: starting piece 1 at 15-apr-21  channel ora_disk_1: finished piece 1 at 15-apr-21  piece handle=/home/oracle/ctrl.ora tag=tag20210415t104457 comment=none  channel ora_disk_1: backup set complete, elapsed time: 00:00:01  finished backup at 15-apr-21  rman> 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  

[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]$ ll  total 0  [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ll /oradata/orcl/control01.ctl  ls: cannot access /oradata/orcl/control01.ctl: no such file or directory  sql> alter tablespace users add datafile;  alter tablespace users add datafile  *  error at line 1:  ora-00603: oracle server session terminated by fatal error  ora-00210: cannot open the specified control file  ora-00202: control file: '/oradata/orcl/control01.ctl'  ora-27041: unable to open file  linux-x86_64 error: 2: no such file or directory  additional information: 3  ora-00210: cannot open the specified control file  ora-00202: control file: '/oradata/orcl/control01.ctl'  ora-27041: unable to open file  linux-x86_64 error: 2: no such file or directory  additional information: 3  process id: 2333  session id: 1 serial number: 9  sql> shutdown abort;  oracle instance shut down.  sql> startup mount  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  ora-00205: error in identifying control file, check alert log for more info  

--开启到nomount状态  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  --rman恢复控制文件  rman> restore controlfile from '/home/oracle/ctrl.ora';  starting restore at 15-apr-21  using target database control file instead of recovery catalog  allocated channel: ora_disk_1  channel ora_disk_1: sid=20 device type=disk  channel ora_disk_1: restoring control file  channel ora_disk_1: restore complete, elapsed time: 00:00:01  output file name=/oradata/orcl/control01.ctl  output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl  finished restore at 15-apr-21  --开启数据库到mount状态  sql> alter database mount;  database altered.  --恢复数据库  rman> recover database;  starting recover at 15-apr-21  released channel: ora_disk_1  starting implicit crosscheck backup at 15-apr-21  allocated channel: ora_disk_1  channel ora_disk_1: sid=20 device type=disk  finished implicit crosscheck backup at 15-apr-21  starting implicit crosscheck copy at 15-apr-21  using channel ora_disk_1  finished implicit crosscheck copy at 15-apr-21  searching for all files in the recovery area  cataloging files...  no files cataloged  using channel ora_disk_1  rman-06900: warning: unable to generate v$rman_status or v$rman_output row  rman-06901: warning: disabling update of the v$rman_status and v$rman_output rows  oracle error from target database:   ora-19922: there is no parent row with id 0 and level 1  starting media recovery  archived log for thread 1 with sequence 12 is already on disk as file /oradata/orcl/redo03.log  archived log file name=/oradata/orcl/redo03.log thread=1 sequence=12  media recovery complete, elapsed time: 00:00:00  finished recover at 15-apr-21  

sql> alter database open resetlogs;  database altered.  

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: '/oradata/orcl/control01.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.  

--通过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]$ ll  total 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）  --开启数据库到nomount  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  ;  --恢复数据库  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.