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

简介: 使用RMAN命令手动备份和恢复控制文件
1 环境和数据
1.1 数据库版本
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     1
Next log sequence to archive   1
Current log sequence           1
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
1.4 rman参数配置
[oracle@orclserv1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 3 10:55:59 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL11G (DBID=1118535928)
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; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl11g.f'; # def
2 手动备份和恢复
2.1 手动备份控制文件
RMAN>  backup current controlfile format '/home/oracle/backup/c_%d_%I_%T_%s';
Starting backup at 03-AUG-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 03-AUG-19
channel ORA_DISK_1: finished piece 1 at 03-AUG-19
piece handle=/home/oracle/backup/c_ORCL11G_1118535928_20190803_8 tag=TAG20190803T112625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-AUG-19
使用backup current controlfile 命令手动备份当前控制文件,格式选项里面各项含义如下:
 %d:数据库名称,   %I :dbid , %T: 日期,  %s: 备份集号
 在backup命令的输出里可以看到备份文件
 /home/oracle/backup/c_ORCL11G_1118535928_20190803_8
2.2 在表中增加一些数据
SQL> insert into test_control select sysdate from dual;
1 row created.
SQL> insert into test_control select sysdate from dual;
1 row created.
SQL> commit;
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 删除控制文件,模拟控制文件丢失
[oracle@orclserv1 backup]$ rm /u01/app/oracle/oradata/orcl11g/control01.ctl
[oracle@orclserv1 backup]$ 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

使用abort模式强制关闭数据库

SQL> shutdown abort;
ORACLE instance shut down.
2.3 使用rman恢复控制文件

以mount方式启动数据库

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> restore controlfile from '/home/oracle/backup/c_ORCL11G_1118535928_20190803_8';
Starting restore at 03-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
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 03-AUG-19

从命令输出中可以看到控制文件已经恢复到数据库配置文件中设置的路径中,接下来需要mount数据库。

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

恢复数据库

RMAN> recover database;
Starting recover at 03-AUG-19
Starting implicit crosscheck backup at 03-AUG-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 03-AUG-19
Starting implicit crosscheck copy at 03-AUG-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-AUG-19
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 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=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-AUG-19

以restlogs选项打开数据库

RMAN> alter database open RESETLOGS;
database opened
RMAN>

登陆数据库检查数据,已经恢复到数据库关闭前的数据

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


相关文章
|
7天前
|
开发框架 Oracle 关系型数据库
ASP.NET实验室LIS系统源码 Oracle数据库
LIS是HIS的一个组成部分,通过与HIS的无缝连接可以共享HIS中的信息资源,使检验科能与门诊部、住院部、财务科和临床科室等全院各部门之间协同工作。 
21 4
|
10天前
|
存储 Oracle NoSQL
Oracle 表空间、数据文件、schema的关系
Oracle 表空间、数据文件、schema的关系
16 2
|
12天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库:装上去,飞起来!
本文是一篇关于Oracle数据库安装和使用的博文摘要。作者以轻松幽默的笔调介绍了自己在实验中掌握的Oracle数据库基本操作,包括使用组件查看命令、配置数据库监听器等。作者也分享了在实验中遇到的一些有趣问题,如SQL语句缺少分号导致的意外错误。此外,作者还强调了登录sys用户和启动实例加载数据库的注意事项,并鼓励读者面对挑战时保持乐观,不断提升自己的能力。整体风格风趣严谨,引人入胜。
201 0
|
2月前
|
存储 Java 数据库
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数(二)
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数
25 0
|
2月前
|
SQL Oracle 关系型数据库
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
55 0
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
|
2天前
|
Oracle 关系型数据库 Java
plsql链接远程Oracle数据库步骤
实际工作中,我们往往需要使用 PLSQL Develope 工具连接远程服务器上的 ORACLE 数据库进行管理,但是由于 ORACLE 安装在本地电脑步骤繁琐,并且会耗费电脑的很大一部分资源,因此,我们寻求一种不需要在本地安装 ORACLE 数据库而能直接使用 PLSQL Develope 工具连接到远程服务器 ORACLE 的方法。
14 2
|
7天前
|
关系型数据库 MySQL Linux
Linux环境下定时备份mysql数据库
Linux环境下定时备份mysql数据库
|
8天前
|
Oracle 关系型数据库 数据处理
某教程学习笔记(一):10、oracle数据库注入
某教程学习笔记(一):10、oracle数据库注入
6 0
|
8天前
|
数据库 对象存储 数据安全/隐私保护
在oceanbase数据库中,有没有遇到备份到oss环境,备份进度到98%就一直卡着的问题?
在oceanbase数据库中,有没有遇到备份到oss环境,备份进度到98%就一直卡着的问题?
13 6
|
15天前
|
SQL 存储 关系型数据库
备份数据库
备份数据库
17 9

热门文章

最新文章

推荐镜像

更多