oracle数据库控制文件的备份和恢复之三RMAN自动备份和恢复

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

相关文章
|
7月前
|
Oracle 关系型数据库 数据库
9-2 Oracle数据库(表)的逻辑备份与恢复 --导出与导入
9-2 Oracle数据库(表)的逻辑备份与恢复 --导出与导入
|
3天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
48 5
|
3天前
|
Oracle 关系型数据库 数据库管理
创建oracle备份路径
创建oracle备份路径
|
3天前
|
数据库
使用Navicat Premium 12进行数据库定期自动备份(定时任务)--图文详解
使用Navicat Premium 12进行数据库定期自动备份(定时任务)--图文详解
37 0
|
3天前
|
SQL 存储 Oracle
oracle如何定期备份数据库sql文件
【1月更文挑战第7天】oracle如何定期备份数据库sql文件
62 8
|
9月前
|
Oracle 关系型数据库 数据库
通过数据泵expdp、impdp方式备份与还原(导出与导入)Oracle数据库
通过数据泵expdp、impdp方式备份与还原(导出与导入)Oracle数据库
|
10月前
|
SQL 数据库
sql创建自动备份数据库
前段时间需要建立一个sql数据库定时自动备份的任务,通过查询以及实践,将自己的经历过程记录在下面。
|
10月前
|
SQL 存储 运维
进阶宝典一|SqlServer数据库自动备份设置
进阶宝典一|SqlServer数据库自动备份设置
|
10月前
|
存储 弹性计算 缓存
在阿里云服务器上配置自动备份 MongoDB 数据库的操作
我是吃了没有提前备份数据的亏,因为一次误操作,删除了所有数据。阿里云的ECS服务器控制台有定时与自动化任务-自动备份数据服务,不免费。那么就自己动手来吧。
302 0
|
10月前
|
关系型数据库 MySQL Shell
MySql DUMP 自动备份数据库 Shell 脚本
MySql DUMP 自动备份数据库 Shell 脚本
83 0

推荐镜像

更多