Oracle RMAN自动备份和恢复控制文件

原创听见风的声音 2023-04-28

1572

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

oracle rman

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者