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和控制文件自动备份格式,如果无法取得这两项信息,就必须采取和手动控制文件备份同样的方法,在恢复时需要指定控制文件备份文件。