创建归档日志存放目录
[oracle@host01 ~]$ mkdir /u01/app/backup
SYS@PROD1>alter system set log_archive_dest_1='location=/u01/app/backup';
System altered.
SYS@PROD1>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/backup
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
开启控制文件自动备份
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f'; # default
备份数据库
RMAN> backup database plus archivelog delete input;
Starting backup at 16-DEC-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=18 STAMP=930743583
input archived log thread=1 sequence=14 RECID=19 STAMP=930743609
input archived log thread=1 sequence=15 RECID=20 STAMP=930743654
channel ORA_DISK_1: starting piece 1 at 16-DEC-16
channel ORA_DISK_1: finished piece 1 at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_16/o1_mf_annnn_TAG20161216T115414_d56sc6mc_.bkp tag=TAG20161216T115414 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/backup/1_13_928254317.dbf RECID=18 STAMP=930743583
archived log file name=/u01/app/backup/1_14_928254317.dbf RECID=19 STAMP=930743609
archived log file name=/u01/app/backup/1_15_928254317.dbf RECID=20 STAMP=930743654
Finished backup at 16-DEC-16
Starting backup at 16-DEC-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/ts_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-16
channel ORA_DISK_1: finished piece 1 at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_16/o1_mf_nnndf_TAG20161216T115415_d56sc7tl_.bkp tag=TAG20161216T115415 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 16-DEC-16
Starting backup at 16-DEC-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=21 STAMP=930743670
channel ORA_DISK_1: starting piece 1 at 16-DEC-16
channel ORA_DISK_1: finished piece 1 at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_16/o1_mf_annnn_TAG20161216T115431_d56scq24_.bkp tag=TAG20161216T115431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/backup/1_16_928254317.dbf RECID=21 STAMP=930743670
Finished backup at 16-DEC-16
Starting Control File and SPFILE Autobackup at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-DEC-16
1.删除所有的控制文件
[oracle@host01 PROD1]$ ls control*
control01.ctl control03.ctl control03.ctl.bak
[oracle@host01 PROD1]$ rm control01.ctl control03.ctl
[oracle@host01 PROD1]$ pwd
/u01/app/oracle/fast_recovery_area/PROD1
[oracle@host01 PROD1]$ rm control02.ctl
2.由于控制文件全部丢失，一致性关闭失败，采用abort方式强制关库
SYS@PROD1>shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@PROD1>shutdown abort;
ORACLE instance shut down.
3.利用RMAN备份恢复控制文件
会话失效，重新登录RMAN
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp';
Starting restore at 16-DEC-16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-03114: not connected to ORACLE
RMAN-03002: failure of restore command at 12/16/2016 11:59:29
ORA-03114: not connected to ORACLE
[oracle@host01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 16 11:59:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp';
Starting restore at 16-DEC-16
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/16/2016 11:59:43
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
启动数据库到nomount
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
ORA-00205: error in identifying control file, check alert log for more info
进行恢复
[oracle@host01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 16 12:01:31 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp';
Starting restore at 16-DEC-16
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=/u01/app/oracle/oradata/PROD1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
output file name=/u01/app/oracle/oradata/PROD1/control03.ctl
Finished restore at 16-DEC-16
可以启动到mount状态，控制文件已全部恢复
SYS@PROD1>alter database mount;
Database altered.
执行介质恢复
RMAN> recover database;
Starting recover at 16-DEC-16
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 16-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 16-DEC-16
Starting implicit crosscheck copy at 16-DEC-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-DEC-16
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_11_18/o1_mf_s_928252815_d2xf8zpf_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp
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 17 is already on disk as file /u01/app/oracle/oradata/PROD1/redo02.log
archived log file name=/u01/app/oracle/oradata/PROD1/redo02.log thread=1 sequence=17
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-16
open打开失败，采用open resetlogs方式
SYS@PROD1>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@PROD1>alter database open resetlogs;
Database altered.
总结：采用控制文件自动备份还原丢失的控制文件
[oracle@host01 ~]$ mkdir /u01/app/backup
SYS@PROD1>alter system set log_archive_dest_1='location=/u01/app/backup';
System altered.
SYS@PROD1>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/backup
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
开启控制文件自动备份
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f'; # default
备份数据库
RMAN> backup database plus archivelog delete input;
Starting backup at 16-DEC-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=18 STAMP=930743583
input archived log thread=1 sequence=14 RECID=19 STAMP=930743609
input archived log thread=1 sequence=15 RECID=20 STAMP=930743654
channel ORA_DISK_1: starting piece 1 at 16-DEC-16
channel ORA_DISK_1: finished piece 1 at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_16/o1_mf_annnn_TAG20161216T115414_d56sc6mc_.bkp tag=TAG20161216T115414 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/backup/1_13_928254317.dbf RECID=18 STAMP=930743583
archived log file name=/u01/app/backup/1_14_928254317.dbf RECID=19 STAMP=930743609
archived log file name=/u01/app/backup/1_15_928254317.dbf RECID=20 STAMP=930743654
Finished backup at 16-DEC-16
Starting backup at 16-DEC-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/ts_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-16
channel ORA_DISK_1: finished piece 1 at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_16/o1_mf_nnndf_TAG20161216T115415_d56sc7tl_.bkp tag=TAG20161216T115415 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 16-DEC-16
Starting backup at 16-DEC-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=21 STAMP=930743670
channel ORA_DISK_1: starting piece 1 at 16-DEC-16
channel ORA_DISK_1: finished piece 1 at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_16/o1_mf_annnn_TAG20161216T115431_d56scq24_.bkp tag=TAG20161216T115431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/backup/1_16_928254317.dbf RECID=21 STAMP=930743670
Finished backup at 16-DEC-16
Starting Control File and SPFILE Autobackup at 16-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-DEC-16
1.删除所有的控制文件
[oracle@host01 PROD1]$ ls control*
control01.ctl control03.ctl control03.ctl.bak
[oracle@host01 PROD1]$ rm control01.ctl control03.ctl
[oracle@host01 PROD1]$ pwd
/u01/app/oracle/fast_recovery_area/PROD1
[oracle@host01 PROD1]$ rm control02.ctl
2.由于控制文件全部丢失，一致性关闭失败，采用abort方式强制关库
SYS@PROD1>shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@PROD1>shutdown abort;
ORACLE instance shut down.
3.利用RMAN备份恢复控制文件
会话失效，重新登录RMAN
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp';
Starting restore at 16-DEC-16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-03114: not connected to ORACLE
RMAN-03002: failure of restore command at 12/16/2016 11:59:29
ORA-03114: not connected to ORACLE
[oracle@host01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 16 11:59:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp';
Starting restore at 16-DEC-16
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/16/2016 11:59:43
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
启动数据库到nomount
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
ORA-00205: error in identifying control file, check alert log for more info
进行恢复
[oracle@host01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 16 12:01:31 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp';
Starting restore at 16-DEC-16
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=/u01/app/oracle/oradata/PROD1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
output file name=/u01/app/oracle/oradata/PROD1/control03.ctl
Finished restore at 16-DEC-16
可以启动到mount状态，控制文件已全部恢复
SYS@PROD1>alter database mount;
Database altered.
执行介质恢复
RMAN> recover database;
Starting recover at 16-DEC-16
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 16-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 16-DEC-16
Starting implicit crosscheck copy at 16-DEC-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-DEC-16
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_11_18/o1_mf_s_928252815_d2xf8zpf_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_16/o1_mf_s_930743672_d56scr5w_.bkp
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 17 is already on disk as file /u01/app/oracle/oradata/PROD1/redo02.log
archived log file name=/u01/app/oracle/oradata/PROD1/redo02.log thread=1 sequence=17
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-16
open打开失败，采用open resetlogs方式
SYS@PROD1>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@PROD1>alter database open resetlogs;
Database altered.
总结：采用控制文件自动备份还原丢失的控制文件
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31400681/viewspace-2130782/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
控制文件恢复（二） 丢失一个控制文件
请登录后发表评论
登录
全部评论
