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 安全 关系型数据库
【Oracle】使用Navicat Premium连接Oracle数据库两种方法
以上就是两种使用Navicat Premium连接Oracle数据库的方法介绍,希望对你有所帮助!
104 28
|
16天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回数据库
Oracle闪回数据库功能类似于“倒带按钮”,可快速将数据库恢复至 earlier 状态,无需还原备份。本文介绍了闪回数据库的使用方法及实战案例:包括设置归档模式、开启闪回功能、记录SCN号、执行误操作后的恢复步骤等。通过具体 SQL 操作演示了如何利用闪回数据库恢复被误删的用户数据。注意,使用此功能前需确保数据库为归档模式。
|
18天前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle数据库的闪回表
本文介绍了Oracle数据库中的闪回表(Flashback Table)功能,它能够将表的数据快速恢复到特定时间点或系统改变号(SCN),无需备份。文章通过实战示例详细演示了如何使用闪回表恢复数据,包括授权、创建测试表、记录时间与SCN号、删除数据、启用行移动功能、执行闪回操作以及验证恢复结果等步骤。同时,还展示了如何通过触发器禁止插入操作,并在闪回过程中处理触发器的启用问题。文末附有视频讲解,帮助读者更好地理解闪回表的使用方法。
64 10
|
20天前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle数据库的闪回查询
本文介绍了Oracle数据库的闪回查询(Flashback Query)功能及其实际应用。闪回查询通过`AS OF`子句,结合时间戳或SCN号,可查询历史数据状态,帮助分析数据差异。文中通过具体示例演示了如何使用闪回查询:创建测试表、记录当前SCN号、更新数据并提交事务,最后通过闪回查询获取历史数据。附带的视频和代码块详细展示了操作步骤与结果。
|
1月前
|
Oracle 关系型数据库 网络安全
崖山异构数据库迁移利器YMP初体验-Oracle迁移YashanDB
文章是作者小草对崖山异构数据库迁移利器 YMP 的初体验分享,包括背景、YMP 简介、体验环境说明、YMP 部署(含安装前准备、安装、卸载、启动与停止)、数据迁移及遇到的问题与解决过程。重点介绍了 YMP 功能、部署的诸多细节和数据迁移流程,还提到了安装和迁移中遇到的问题及解决办法。
|
21天前
|
Oracle 关系型数据库 数据管理
【赵渝强老师】Oracle数据库的闪回技术
在Oracle数据库操作中,难免会遇到误删表或提交错误事务等问题,可能导致数据丢失甚至数据库停止运行。传统解决方法依赖备份恢复,但需提前准备正确备份。为此,Oracle提供了闪回技术,无需备份即可快速恢复数据。它支持7种类型的操作,如闪回查询、版本查询、表恢复等,能有效应对逻辑损坏和用户错误。闪回技术基于还原(undo)数据管理,启用自动管理后可实现高效恢复。
|
22天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle数据库的客户端工具
本文介绍了Oracle数据库的三种客户端工具:SQL*Plus、Oracle Enterprise Manager Database Express(EM)和SQL Developer的使用方法。首先通过命令行工具SQL*Plus登录数据库,创建用户并授权,建立部门与员工表,插入数据并查询;接着讲解了如何通过浏览器访问EM界面监控数据库及表空间状态;最后演示了SQL Developer的下载安装、连接配置以及执行查询的过程,帮助用户快速上手Oracle数据库管理与操作。
|
8月前
|
监控 Oracle 关系型数据库
"深度剖析:Oracle SGA大小调整策略——从组件解析到动态优化,打造高效数据库性能"
【8月更文挑战第9天】在Oracle数据库性能优化中,系统全局区(SGA)的大小调整至关重要。SGA作为一组共享内存区域,直接影响数据库处理能力和响应速度。本文通过问答形式介绍SGA调整策略:包括SGA的组成(如数据缓冲区、共享池等),如何根据负载与物理内存确定初始大小,手动调整SGA的方法(如使用`ALTER SYSTEM`命令),以及利用自动内存管理(AMM)特性实现智能调整。调整过程中需注意监控与测试,确保稳定性和性能。
567 2
|
9月前
|
存储 缓存 Oracle
Oracle数据库可扩展性和性能
【7月更文挑战第6天】
160 7

热门文章

最新文章

推荐镜像

更多