1 环境和数据
1.1 数据库版本
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
1.2 日志模式
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 11 Next log sequence to archive 13 Current log sequence 13
1.3 实验用数据
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select * from test_control; CURR_TIME ------------------- 2019-07-21 16:25:33 2019-07-27 09:32:21 2019-07-27 09:33:39 2019-08-03 11:52:17 2019-08-03 11:53:05
2 自动控制文件备份和恢复
2.1 打开控制文件自动备份
检查自动控制文件备份是否打开
RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL11G are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default 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; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl11g.f'; # default
控制文件自动备份为关闭状态（缺省设置），打开控制文件自动备份
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
控制文件自动备份已打开
RMAN> show controlfile autobackup; RMAN configuration parameters for database with db_unique_name ORCL11G are: CONFIGURE CONTROLFILE AUTOBACKUP ON;
控制文件自动备份已打开，在控制文件自动备份打开时，每次成功备份结束后RMAN自动备份控制文件和当前参数文件。当数据库处于归档模式时，每次发生影响控制文件内容的结构性改变时，RMAN执行控制文件自动备份。
2.2 执行一次数据库备份，触发控制文件自动备份
RMAN> backup database; Starting backup at 17-AUG-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=40 device type=DISK 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/orcl11g/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/orcl11g/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl11g/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl11g/users01.dbf channel ORA_DISK_1: starting piece 1 at 17-AUG-19 channel ORA_DISK_1: finished piece 1 at 17-AUG-19 piece handle=/u01/app/oracle/fast_recovery_area/ORCL11G/backupset/2019_08_17/o1_mf_nnndf_TAG20190817T155823_gohdwzok_.bkp tag=TAG20190817T155823 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 17-AUG-19 Starting Control File and SPFILE Autobackup at 17-AUG-19 piece handle=/u01/app/oracle/fast_recovery_area/ORCL11G/autobackup/2019_08_17/o1_mf_s_1016553538_gohdy30z_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 17-AUG-19
从命令输出上可以看到，在备份成功执行后，rman执行了控制文件和参数自动备份。
2.3 查看rman库中的控制文件备份
RMAN> list backup of controlfile; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 8 Full 9.67M DISK 00:00:01 10-AUG-19 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20190810T065316 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL11G/backupset/2019_08_10/o1_mf_ncsnf_TAG20190810T065316_gnvyd0dg_.bkp Control File Included: Ckp SCN: 1028605 Ckp time: 10-AUG-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 9.67M DISK 00:00:01 17-AUG-19 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20190817T155858 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL11G/autobackup/2019_08_17/o1_mf_s_1016553538_gohdy30z_.bkp Control File Included: Ckp SCN: 1076675 Ckp time: 17-AUG-19
2.4 加入点测试数据
SQL> select * from test_control; CURR_TIME ----------------- 20190721 16:25:33 20190727 09:32:21 20190727 09:33:39 20190803 11:52:17 20190803 11:53:05 20190817 16:08:27 20190817 16:08:29 20190817 16:08:29 20190817 16:08:30 9 rows selected.``
2.5 删除控制文件
[oracle@orclserv1 ~]$ rm /u01/app/oracle/oradata/orcl11g/control01.ctl [oracle@orclserv1 ~]$ rm /u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl
关闭数据库
SQL> shutdown immediate; ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/orcl11g/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.
2.6 从自动备份中恢复控制文件
设置dbid，启动数据库到nomount模式
RMAN> set dbid 1118535928; executing command: SET DBID RMAN> startup nomount; Oracle instance started Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1006636072 bytes Database Buffers 637534208 bytes Redo Buffers 7094272 bytes
从自动备份中恢复控制文件
RMAN> run { 2> set controlfile autobackup format 3> for device type disk to '%F'; 4> restore controlfile from autobackup;} executing command: SET CONTROLFILE AUTOBACKUP FORMAT Starting restore at 17-AUG-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK recovery area destination: /u01/app/oracle/fast_recovery_area database name (or database unique name) used for search: ORCL11G channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL11G/autobackup/2019_08_17/o1_mf_s_1016553538_gohdy30z_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190817 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL11G/autobackup/2019_08_17/o1_mf_s_1016553538_gohdy30z_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/orcl11g/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/orcl11g/control02.ctl Finished restore at 17-AUG-19
2.7 打开数据库，检查数据
RMAN> alter database mount;/*mount 数据库*/ database mounted released channel: ORA_DISK_1 RMAN> recover database;/*恢复数据库*/ Starting recover at 17-AUG-19 Starting implicit crosscheck backup at 17-AUG-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 17-AUG-19 Starting implicit crosscheck copy at 17-AUG-19 using channel ORA_DISK_1 Finished implicit crosscheck copy at 17-AUG-19 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/fast_recovery_area/ORCL11G/autobackup/2019_08_17/o1_mf_s_1016553538_gohdy30z_.bkp using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/oradata/orcl11g/redo01.log archived log file name=/u01/app/oracle/oradata/orcl11g/redo01.log thread=1 sequence=13 media recovery complete, elapsed time: 00:00:00 Finished recover at 17-AUG-19 RMAN> alter database open noresetlogs;/* 不能以noresetlogs模式打开数据库*/ RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;" RMAN-01008: the bad identifier was: noresetlogs RMAN-01007: at line 1 column 21 file: standard input RMAN> alter database open resetlogs;/* 加resetlogs成功打开数据库*/ database opened
2.8 检验数据
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss'; Session altered. SQL> select * from test_control;/*数据成功恢复，没有丢失*/ CURR_TIME ----------------- 20190721 16:25:33 20190727 09:32:21 20190727 09:33:39 20190803 11:52:17 20190803 11:53:05 20190817 16:08:27 20190817 16:08:29 20190817 16:08:29 20190817 16:08:30 9 rows selected.
3 总结
从上面的恢复过程来看，从rman自动备份中恢复控制文件需要知道dbid和控制文件自动备份格式，如果无法取得这两项信息，就必须采取和手动控制文件备份同样的方法，在恢复时需要指定控制文件备份文件。
