oracle数据库控制文件的备份和恢复之二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