暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle中使用控制文件快照辅助恢复

Oracle中使用控制文件快照辅助恢复

原创 eygle 2019-11-27
1255

Nocatalog方式的备份，控制文件就变得极其重要，一旦控制文件全部丢失，恢复将会平添很多困难，所以在通常建议打开控制文件的自动备份，让数据库在发生重要变化时，自动执行控制文件的备份。

因为控制文件的重要性，除了用户的主动备份之外，Oracle还会执行隐藏的备份。

在RMAN的配置参数中，有这样一个缺省设置：

RMAN> show snapshot controlfile name;
RMAN configuration parameters are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
 '/opt/app/oracle/product/10.2.0/db_1/dbs/snapcf_oradb1.f'; # default

这个文件是Oracle数据库的控制文件快照，在Linux/UNIX上的缺省存储位置为ORACLE_HOME/dbs，通常的缺省文件名称为snapcf_.f；在Windows上缺省的位置为ORACLE_HOME\database，通常的缺省文件名称为SNCF.ORA。

当RMAN需要同步控制文件中记录的信息或者当RMAN需要备份当前的控制文件时，RMAN会对控制文件进行快照备份，那么这个备份将会极为有用，当所有的控制文件都丢失或损坏时，可以尝试使用这个控制文件进行数据恢复。

首先验证快照的机制，我们将快照更改权限，会发现接下来的控制文件备份会失败，因为Oracle无法进行控制文件快照：

[oracle@oracledb3 dbs]$ ls -al snapcf_oradb1.f 
-rw-r----- 1 root oinstall 16138240 Oct  1 22:45 snapcf_oradb1.f
[oracle@oracledb3 dbs]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Oct 1 22:49:29 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORADB (DBID=2475839388)

RMAN> backup current controlfile format
 '/opt/app/oracle/product/10.2.0/db_1/dbs/cf.bak';

Starting backup at 01-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1630 instance=oradb1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/01/2010 22:49:38
ORA-01580: error creating control backup file 
/opt/app/oracle/product/10.2.0/db_1/dbs/snapcf_oradb1.f
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 4

可以看到在备份控制文件的之前，RMAN更新了控制文件的快照，对控制文件进行了备份。但是需要注意的是，当通过RMAN进行备份时，快照的生成和RMAN的设置有关，当未设置控制文件自动备份时，全备份会自动备份控制文件。
来简单看一下这个过程，首先执行一个全备份：

[oracle@jumper backup]$ rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: EYGLE (DBID=1407686520)
RMAN> backup database 
2> format '/opt/oracle/backup/eyglefull_%d_%T_%s';

Starting backup at 28-MAR-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00002 name=/opt/oracle/oradata/eygle/undotbs01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/eygle/system01.dbf
……………………
piece handle=/opt/oracle/backup/eyglefull_EYGLE_20070328_19 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:32
Finished backup at 28-MAR-07

此时生成的这个控制文件快照先于备份集产生，所以快照不包含最后一次的备份信息，因而这个快照文件不能用于最后一次备份集的恢复。
注意在备份之前，Oracle对控制文件执行了snapshot：

[oracle@jumper backup]$ ll $ORACLE_HOME/dbs/snapcf_eygle.f
-rw-r-----    1 oracle   dba       2973696 Mar 28 09:03 
                                /opt/oracle/product/9.2.0/dbs/snapcf_eygle.f

备份集的时间点要晚于控制文件的快照时间：

[oracle@jumper backup]$ ll
total 495936
-rw-r-----    1 oracle   dba      507338752 Mar 28 09:05 eyglefull_EYGLE_20070328_19

在需要时可以使用如下命令恢复这个快照的控制文件：

RMAN> startup nomount;
Oracle instance started
RMAN> restore controlfile
2> from '/opt/oracle/product/9.2.0/dbs/snapcf_eygle.f';
Starting restore at 28-MAR-07

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
replicating controlfile
input filename=/opt/oracle/oradata/eygle/control01.ctl
Finished restore at 28-MAR-07

在一些特殊情况下，这个控制文件可以提供有价值的数据库信息。当然如果试图通过这个控制文件恢复上一个不存在的备份集，那么会收到如下错误（如果之前的备份集存在，则可以通过这个控制文件恢复之前的备份集）：

RMAN> restore database;
Starting restore at 2007-03-28 09:46:14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/28/2007 09:46:14
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

这个控制文件中并未包含这个备份集的信息。所以说，当使用热备份来进行数据库恢复时，控制文件的起点很重要。但是当启用了控制文件的自动备份之后，一切将会不同。
来看一下当前缺省设置：

RMAN> show CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

RMAN> show SNAPSHOT CONTROLFILE NAME;
RMAN configuration parameters are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/9.2.0/dbs/snapcf_eygle.f'; # default

启用控制文件的自动备份功能：

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

同时也可以更改一下控制文件快照的缺省路径：

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/backup/snapcf_eygle.f';

snapshot controlfile name set to: /opt/oracle/backup/snapcf_eygle.f
new RMAN configuration parameters are successfully stored

此时再备份数据库，备份集中将不再包含自动的控制文件备份，自动控制文件备份发生在备份完成之后：

RMAN> backup database
2> format '/opt/oracle/backup/eyglefull_%d_%T_%s';

Starting backup at 2007-03-28 10:48:52
piece handle=/opt/oracle/backup/eyglefull_EYGLE_20070328_5 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 2007-03-28 10:50:49

Starting Control File and SPFILE Autobackup at 2007-03-28 10:50:49
piece handle=/opt/oracle/product/9.2.0/dbs/c-1407686520-20070328-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2007-03-28 10:50:52

来看一下现在的控制文件快照和自动控制文件备份：

[oracle@jumper backup]$ ll
total 495860
-rw-r-----    1 oracle   dba      504217600 Mar 28 10:50 eyglefull_EYGLE_20070328_5
-rw-r-----    1 oracle   dba       3039232 Mar 28 10:50 snapcf_eygle.f
[oracle@jumper backup]$ ll $ORACLE_HOME/dbs/c-1407686520-20070328-01
-rw-r-----    1 oracle   dba       3080192 Mar 28 10:50
                               /opt/oracle/product/9.2.0/dbs/c-1407686520-20070328-01

现在的控制文件备份和快照都是来自备份完成之后。下面来做一个测试，创建一个测试表，向数据库中插入测试数据：

SQL> connect eygle/eygle
Connected.
SQL> create table eyglee(id number,edate date,escn number);
Table created.

SQL> begin
  2  for i in 1 .. 4 loop
  3  insert into eyglee values(i,sysdate,dbms_flashback.get_system_change_number);
  4  commit;
  5  execute immediate 'alter system switch logfile';
  6  dbms_lock.sleep(15);
  7  end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed.

SQL> col escn for 9999999999999999 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

SQL> select * from eyglee;
        ID EDATE                            ESCN
---------- ------------------- -----------------
         1 2007-03-28 11:00:06       18996211373
         2 2007-03-28 11:00:22       18996211382
         3 2007-03-28 11:00:38       18996211391
         4 2007-03-28 11:00:53       18996211399

记录一下生成的归档日志：

SQL> select stamp,FIRST_TIME,name from v$archived_log;
     STAMP FIRST_TIME          NAME
---------- ------------------- --------------------------------------------------
 618404407 2007-03-28 10:25:38 /opt/oracle/product/9.2.0/dbs/arch1_1.dbf
 618404422 2007-03-28 11:00:06 /opt/oracle/product/9.2.0/dbs/arch1_2.dbf
 618404438 2007-03-28 11:00:22 /opt/oracle/product/9.2.0/dbs/arch1_3.dbf
 618404453 2007-03-28 11:00:38 /opt/oracle/product/9.2.0/dbs/arch1_4.dbf

4 rows selected.

接下来可以删除所有当前的控制文件和数据文件模拟一次介质故障。然后，通过控制文件快照（通过自动备份恢复也是可以的）进行恢复尝试：

[oracle@jumper backup]$ rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;
Oracle instance started
RMAN> restore controlfile
2> from '/opt/oracle/backup/snapcf_eygle.f';
Starting restore at 2007-03-28 11:07:01

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
replicating controlfile
input filename=/opt/oracle/oradata/eygle/control01.ctl
Finished restore at 2007-03-28 11:07:06

RMAN> alter database mount;
database mounted

然后可以进行基于SCN的不完全恢复：

RMAN> run
2> {set until scn 18996211390;
3> restore database;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 2007-03-28 11:08:08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf
………
piece handle=/opt/oracle/backup/eyglefull_EYGLE_20070328_5 tag=TAG20070328T104853 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 2007-03-28 11:09:54

Starting recover at 2007-03-28 11:09:54
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file 
/opt/oracle/oradata/eygle/redo05.dbf
archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_1.dbf thread=1 sequence=1
archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_2.dbf thread=1 sequence=2
archive log filename=/opt/oracle/oradata/eygle/redo05.dbf thread=1 sequence=3
media recovery complete
Finished recover at 2007-03-28 11:09:57

然后通过resetlogs强制打开数据库：

RMAN> alter database open resetlogs;
database opened

此时来查询一下恢复后的数据：

SQL> connect eygle/eygle
Connected.
SQL> col escn for 9999999999999999 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';     
Session altered.

SQL> select * from eyglee;
        ID EDATE                            ESCN
---------- ------------------- -----------------
         1 2007-03-28 11:00:06       18996211373
         2 2007-03-28 11:00:22       18996211382

经过确认数据已经恢复。此时可以观察一下警告日志文件中的提示信息：

ORA-279 signalled during: alter database recover if needed
 start until chan...
Wed Mar 28 11:09:56 2007
alter database recover logfile '/opt/oracle/product/9.2.0/dbs/arch1_1.dbf'
Media Recovery Log /opt/oracle/product/9.2.0/dbs/arch1_1.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/produc...
Wed Mar 28 11:09:57 2007
alter database recover logfile '/opt/oracle/product/9.2.0/dbs/arch1_2.dbf'
Media Recovery Log /opt/oracle/product/9.2.0/dbs/arch1_2.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/produc...
Wed Mar 28 11:09:57 2007
alter database recover logfile '/opt/oracle/oradata/eygle/redo05.dbf'
Media Recovery Log /opt/oracle/oradata/eygle/redo05.dbf
Terminal Recovery: done UNTIL CHANGE 18996211391
Media Recovery Complete
Completed: alter database recover logfile '/opt/oracle/oradat
Wed Mar 28 11:11:18 2007
alter database open resetlogs
Wed Mar 28 11:11:18 2007
RESETLOGS after incomplete recovery UNTIL CHANGE 18996211391
Resetting resetlogs activation ID 1437494116 (0x55ae6b64)

当然也可以执行基于时间点的恢复，以下是一个示例。

RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
RMAN> restore controlfile from '/opt/oracle/backup/snapcf_eygle.f';  

Starting restore at 2007-03-28 11:19:39

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
replicating controlfile
input filename=/opt/oracle/oradata/eygle/control01.ctl
Finished restore at 2007-03-28 11:19:44

RMAN> alter database mount;
database mounted
RMAN> run
2> {
3> set until time '2007-03-28 11:00:30';
4> restore database;
5> recover database;
6> }

executing command: SET until clause
Starting restore at 2007-03-28 11:19:59

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf
…………
piece handle=/opt/oracle/backup/eyglefull_EYGLE_20070328_5 tag=TAG20070328T104853 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 2007-03-28 11:21:45

Starting recover at 2007-03-28 11:21:45
using channel ORA_DISK_1

starting media recovery

archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_1.dbf thread=1 sequence=1
archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_2.dbf thread=1 sequence=2
archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_3.dbf thread=1 sequence=3
media recovery complete
Finished recover at 2007-03-28 11:21:48

RMAN> alter database open resetlogs;
database opened

在进行不完全恢复的过程中，until scn和until time是两种常用的恢复方式。

oracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论