在上篇中,我们讨论并且演示了如何利用Trace文件恢复控制文件。但是我们的控制文件中包括的备份信息,却不能保存在Trace文件的create control file语句里面。
那么,我们有什么方法保存住备份信息呢?具体有两个方法,使用catalog和backup set加载。下面我们分别进行演示。
4、Catalog备份信息保留
默认情况下,RMAN将备份信息保存在target数据库的control file中。如果发生我们所实验的情况,备份信息将全部消失。另外一种RMAN推荐的方法是将这些备份信息保存在另外数据库异地保存,就是catalog。
如果我们能够在catalog中备份一份关于target数据库备份数据库的备份信息,就可以实现Backup信息的留存。
首先,我们在实验环境上建立数据库备份。
RMAN> backup database plus archivelog delete all input;
Starting backup at 20-FEB-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
(篇幅原因,省略部分内容……)
handle=/u01/app/oracle/flash_recovery_area/ORA10GL/backupset/2013_02_20/o1_mf_annnn_TAG20130220T152242_8l8yg3jb_.bkp tag=TAG20130220T152242 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/ORA10GL/archivelog/2013_02_20/o1_mf_1_9_8l8yg1bc_.arc recid=5 stamp=807895362
Finished backup at 20-FEB-13
Starting Control File and SPFILE Autobackup at 20-FEB-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GL/autobackup/2013_02_20/o1_mf_s_807895365_8l8ygb77_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-FEB-13
RMAN>
我们在另一台数据库创建catalog,其中包括用户创建、专用表空间建立和权限赋值。
SQL> create tablespace rman_bk datafile size 500M autoextend on extent management local uniform. size 1m segment space management auto;
Tablespace created
SQL> CREATE USER rman IDENTIFIED BY rman
2 DEFAULT TABLESPACE RMAN_bk
3 TEMPORARY TABLESPACE temp;
User created
SQL> GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO rman;
Grant succeeded
之后,登录RMAN创建出catalog目录。
RMAN> create catalog;
恢复目录已创建
登录target数据库,注意使用catalog连接,之后进行注册。
[oracle@bspdev ~]$ rman target / catalog rman/rman@ora11gw
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 20 16:16:30 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10GL (DBID=4006742303)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
下面,我们开始尝试删除控制文件实验,并且恢复。首先需要进行一次完全的关闭过程。
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
删除原有的控制文件。
[oracle@bspdev controlfile]$ ls -l
total 14136
-rw-r----- 1 oracle oinstall 7389184 Feb 20 16:18 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Feb 20 14:47 o1_mf_8brvwzwc_.ctl.bk
[oracle@bspdev controlfile]$ mv control01.ctl control01.ctl.bk
[oracle@bspdev controlfile]$ ls -l
total 14136
-rw-r----- 1 oracle oinstall 7389184 Feb 20 16:18 control01.ctl.bk
-rw-r----- 1 oracle oinstall 7061504 Feb 20 14:47 o1_mf_8brvwzwc_.ctl.bk
启动数据库,进行恢复。
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10GL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '/u01/app/oracle/oradata/ORA10GL/onlinelog/o1_mf_1_8brvx1mo_.log',
10 '/u01/app/oracle/flash_recovery_area/ORA10GL/onlinelog/o1_mf_1_8brvx3s1_.log'
11 ) SIZE 50M,
12 GROUP 2 (
13 '/u01/app/oracle/oradata/ORA10GL/onlinelog/o1_mf_2_8brvx94v_.log',
14 '/u01/app/oracle/flash_recovery_area/ORA10GL/onlinelog/o1_mf_2_8brvxc8g_.log'
15 ) SIZE 50M,
16 GROUP 3 (
17 '/u01/app/oracle/oradata/ORA10GL/onlinelog/o1_mf_3_8brvxfk5_.log',
18 '/u01/app/oracle/flash_recovery_area/ORA10GL/onlinelog/o1_mf_3_8brvxhkc_.log'
19 ) SIZE 50M
20 -- STANDBY LOGFILE
21 DATAFILE
22 '/u01/app/oracle/oradata/ORA10GL/datafile/o1_mf_system_8brvr0gf_.dbf',
23 '/u01/app/oracle/oradata/ORA10GL/datafile/o1_mf_undotbs1_8brvr10h_.dbf',
24 '/u01/app/oracle/oradata/ORA10GL/datafile/o1_mf_sysaux_8brvr0j3_.dbf',
25 '/u01/app/oracle/oradata/ORA10GL/datafile/o1_mf_users_8brvr12n_.dbf'
26 CHARACTER SET AL32UTF8
27 ;
Control file created.
SQL>
后续恢复动作。
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
PL/SQL procedure successfully completed.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA10GL/datafile/o1_mf_temp_8brvy20c_.tmp'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
此时,仅查看控制文件中的备份信息如下。
RMAN> list backup;
RMAN>
原有的备份内容消失不见。
当我们连入catalog的时候,还是可以看到catalog中保存的信息。
[oracle@bspdev ~]$ rman target / catalog rman/rman@ora11gw
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 20 16:26:22 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10GL (DBID=4006742303)
connected to recovery catalog database
RMAN> list backup
2> ;
starting full resync of recovery catalog
full resync complete
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 7.11M DISK 00:00:07 20-FEB-13
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20130220T150218
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10GL/autobackup/2013_02_20/o1_mf_s_807894138_8l8x7y1j_.bkp
Control File Included: Ckp SCN: 545929 Ckp time: 20-FEB-13
SPFILE Included: Modification time: 20-FEB-13
(篇幅原因,省略部分内容……)
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 7.11M DISK 00:00:09 20-FEB-13
BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20130220T152245
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10GL/autobackup/2013_02_20/o1_mf_s_807895365_8l8ygb77_.bkp
Control File Included: Ckp SCN: 546755 Ckp time: 20-FEB-13
SPFILE Included: Modification time: 20-FEB-13
注意,此时即使尝试手工同步。我们也没能将catalog信息同步到控制文件上。
RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
[oracle@bspdev ~]$ rman nocatalog
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 20 16:39:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: ORA10GL (DBID=4006742303)
using target database control file instead of recovery catalog
RMAN> list backup;
说明,使用Catalog可以实现控制文件备份信息的保留。
5、RMAN命令进行强制注册
另外一种方法是在恢复结束之后,手工将备份集合文件注册上。这种方法选择性较强,也比较简单。
首先,我们确定控制文件中应有的备份集合不再其中。
[oracle@bspdev ~]$ rman nocatalog
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 21 15:53:14 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: ORA10GL (DBID=4006742303)
using target database control file instead of recovery catalog
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 7.11M DISK 00:00:05 20-FEB-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130220T170340
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10GL/autobackup/2013_02_20/o1_mf_s_807901420_8l94cgs9_.bkp
Control File Included: Ckp SCN: 551107 Ckp time: 20-FEB-13
SPFILE Included: Modification time: 20-FEB-13
RMAN>
备份集合全部保存在recovery area中。
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 5000M
recovery_parallelism integer 0
确定目录下有文件。
[oracle@bspdev ORA10GL]$ pwd
/u01/app/oracle/flash_recovery_area/ORA10GL
[oracle@bspdev ORA10GL]$ ls -l
total 20
drwxr-x--- 5 oracle oinstall 4096 Feb 21 15:45 archivelog
drwxr-x--- 4 oracle oinstall 4096 Feb 20 15:02 autobackup
drwxr-x--- 3 oracle oinstall 4096 Feb 20 15:22 backupset
drwxr-x--- 2 oracle oinstall 4096 Feb 20 14:49 controlfile
drwxr-x--- 2 oracle oinstall 4096 Nov 21 14:07 onlinelog
登录RMAN,使用catalog命令将备份文件注册上。当备份都在同一目录结构时,可以使用start with “”命令,让RMAN自己定位。
RMAN> catalog start with '/u01/app/oracle/flash_recovery_area/ORA10GL';
searching for all files that match the pattern /u01/app/oracle/flash_recovery_area/ORA10GL
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/ORA10GL/archivelog/2012_11_26/o1_mf_1_5_8c5gxrwf_.arc
File Name:
(篇幅原因,有省略……)
_8l8ygb77_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORA10GL/controlfile/o1_mf_8brvx088_.ctl.bk
--确定是否真正的加载注册。
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA10GL/archivelog/2012_11_26/o1_mf_1_5_8c5gxrwf_.arc
(篇幅原因,有省略……)
List of Files Which Where Not Cataloged
=======================================
File Name: /u01/app/oracle/flash_recovery_area/ORA10GL/controlfile/o1_mf_8brvx088_.ctl.bk
RMAN-07519: Reason: Error while cataloging. See alert.log.
RMAN>
最后有一个之前使用的控制文件没有被识别,是正常现象。下面我们判断结果。
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 7.11M DISK 00:00:05 20-FEB-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130220T170340
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10GL/autobackup/2013_02_20/o1_mf_s_807901420_8l94cgs9_.bkp
Control File Included: Ckp SCN: 551107 Ckp time: 20-FEB-13
SPFILE Included: Modification time: 20-FEB-13
(篇幅原因,有省略……)
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 7.11M DISK 00:00:00 20-FEB-13
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20130220T152245
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10GL/autobackup/2013_02_20/o1_mf_s_807895365_8l8ygb77_.bkp
Control File Included: Ckp SCN: 546755 Ckp time: 20-FEB-13
SPFILE Included: Modification time: 20-FEB-13
RMAN>
加载成功,注意此处backup set的相当于重新编号。除了使用start with命令进行特定目录的全加载,还有catalog autobackup等命令直接有目的的加载特定的备份文件集。
6、总结
Control File是整个数据库的心脏,关系重大。本篇演示了如何进行控制文件的重建,以及之后的相关处理。权当记录,供有需要的朋友不时之需。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-754479/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-754479/