中小型数据库 RMAN CATALOG 备份恢复方案(三)

      在前两篇文章中描述了中小型数据库使用RMAN catalog设计备份与恢复方案，并给出了所有相关的脚本来从某种车程度上模拟Oracle Data Guard以减少硬件故障带来Prod服务器上数据库损失。在这边文章中主要描述Prod数据库的变迁在Bak server端如何进行恢复。

          中小型数据库 RMAN CATALOG 备份恢复方案(一)
          中小型数据库 RMAN CATALOG 备份恢复方案(二)

 

1、恢复前提
     按照前两篇文章的描述，我们制定了每天做一个level 0级备份并ftp整个备份集到Bak server。同时定时ftp Prod的归档日志到Bak server。
     其次是每天会对Bak server端的数据库做还原(restore)操作。因此对于Bak server实现数据恢复所要做的是应用归档日志(含定时ftp的归档日志)
     将数据库刷新到最新时刻。对于备份如恢复的间隔也可自行定义，如每2天做一次。下面是恢复的前提条件，否则需要手动备份或还原。
          使用RMAN备份脚本已经完成RMAN备份，且备份被ftp到备份服务器
          使用RMAN恢复脚本已经在备份服务器成功进行了还原

 

2、Prod DB上准备测试数据

SQL> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit ProductionSQL> select instance_name,host_name from v$instance;INSTANCE_NAME    HOST_NAME---------------- ---------------------------------------------Ak3210           N10db03p--为prod添加tablespaceSQL> create tablespace tbs_tmp datafile '/u02/database/Ak3210/oradata/tbs_tmp.dbf' size 10m autoextend on;--基于新的tablespace添加表对象SQL> create table xy(seq varchar2(20),who varchar2(20),dt varchar2(20)) tablespace tbs_tmp;--插入数据SQL> insert into xy select 'FirstArch','Robinson',to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;SQL> commit;--对当前日志归档SQL> alter system archive log current;--下面是生成的归档日志SQL> ho lsarch_818416637_1_157.arc--验证刚刚插入的记录是否存在于归档日志SQL> ho strings arch_818416637_1_157.arc | grep "FirstArch"        FirstArch--再次插入新的数据SQL> insert into xy select 'SecnodArch','Jackson',to_char(sysdate,'yyyymmdd hh:mi:ss') from dual;SQL> commit;SQL> alter system archive log current;SQL> ho lsarch_818416637_1_157.arc  arch_818416637_1_158.arcSQL> ho strings arch_818416637_1_158.arc | grep "SecnodArch"SecnodArch--Author : Robinson Cheng--Blog   : http://blog.csdn.net/robinson_0612--将归档日志文件复制到备份服务器SQL> ho scp *.arc 192.168.250.101:/u02/database/Ak3210/archivearch_818416637_1_157.arc                                       100%   34MB  34.2MB/s   00:00arch_818416637_1_158.arc                                       100%   12KB  12.0KB/s   00:00--Prod数据库的归档情况，当前Log sequence是159SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            /u02/database/Ak3210/archive/Oldest online log sequence     157Next log sequence to archive   159Current log sequence           159SQL> col name format a60SQL> set linesize 160SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';  -->查询归档日志SQL> select name,sequence#,status,COMPLETION_TIME from v$archived_log where status='A';NAME                                                          SEQUENCE# S COMPLETION_TIME------------------------------------------------------------ ---------- - -----------------/u02/database/Ak3210/archive/arch_818416637_1_157.arc               157 A 20130731 16:34:30/u02/database/Ak3210/archive/arch_818416637_1_158.arc               158 A 20130731 16:35:42SQL> select * from xy;SEQ                  WHO                  DT-------------------- -------------------- --------------------FirstArch            Robinson             20130731 16:34:15SecnodArch           Jackson              20130731 16:35:35

3、Bak Server上DB的恢复操作

oracle@BKDB01p:~> export ORACLE_SID=Ak3210oracle@BKDB01p:~> rman target / catalog rman_user/rman@catadb    --在备份服务器上连接target DB 及catalog DBRecovery Manager: Release 10.2.0.3.0 - Production on Wed Jul 31 16:39:45 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database (not started)connected to recovery catalog databaseRMAN> startup mount;                --->启动数据库到mount状态RMAN> restore archivelog all;       --->还原所有的归档日志Starting restore at 20130731 16:41:35allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=1090 devtype=DISKchannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=156channel ORA_DISK_1: reading from backup piece /u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/  2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5t_.bkpchannel ORA_DISK_1: restored backup piece 1piece handle=/u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5t_.bkp tag=ARCHBKchannel ORA_DISK_1: restore complete, elapsed time: 00:00:02channel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=155channel ORA_DISK_1: reading from backup piece /u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/  2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5l_.bkpchannel ORA_DISK_1: restored backup piece 1piece handle=/u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5l_.bkp tag=ARCHBKchannel ORA_DISK_1: restore complete, elapsed time: 00:00:08Finished restore at 20130731 16:41:46RMAN> list copy;                    --->查看刚刚还原出来的日志文件List of Archived Log CopiesKey     Thrd Seq     S Low Time          Name------- ---- ------- - ----------------- ----34428   1    155     A 20130731 01:00:50 /u02/database/Ak3210/archive/arch_818416637_1_155.arc34427   1    156     A 20130731 15:19:54 /u02/database/Ak3210/archive/arch_818416637_1_156.arcRMAN> catalog archivelog '/u02/database/Ak3210/archive/arch_818416637_1_157.arc';  --->将新的归档日志注册到catalogcataloged archive logarchive log filename=/u02/database/Ak3210/archive/arch_818416637_1_157.arc recid=148 stamp=822242629RMAN> catalog archivelog '/u02/database/Ak3210/archive/arch_818416637_1_158.arc';cataloged archive logarchive log filename=/u02/database/Ak3210/archive/arch_818416637_1_158.arc recid=149 stamp=822242639RMAN> list copy;                   --->再次查看时，所有的归档日志已经位于归档目录 List of Archived Log CopiesKey     Thrd Seq     S Low Time          Name------- ---- ------- - ----------------- ----34428   1    155     A 20130731 01:00:50 /u02/database/Ak3210/archive/arch_818416637_1_155.arc34427   1    156     A 20130731 15:19:54 /u02/database/Ak3210/archive/arch_818416637_1_156.arc34495   1    157     A 20130731 15:19:55 /u02/database/Ak3210/archive/arch_818416637_1_157.arc34534   1    158     A 20130731 16:34:30 /u02/database/Ak3210/archive/arch_818416637_1_158.arcRMAN> run{                        --->使用until方式恢复数据库，下面给出了错误提示2> set until sequence 159;3> recover database;}executing command: SET until clauseStarting recover at 20130731 16:45:47using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 155 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_155.arcarchive log thread 1 sequence 156 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_156.arcarchive log thread 1 sequence 157 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_157.arcarchive log thread 1 sequence 158 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_158.arcRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 07/31/2013 16:45:51RMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of log thread 1 seq 94 lowscn 2457942 found to restoreRMAN> exitRecovery Manager complete.oracle@BKDB01p:~> export ORACLE_SID=Ak3210oracle@BKDB01p:~> sqlplus / as sysdba              --->下面在sqlplus进行恢复SQL> recover database using backup controlfile;    --->使用基于备份的控制文件恢复数据库  ORA-00279: change 2654259 generated at 07/31/2013 15:19:26 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_155.arcORA-00280: change 2654259 for thread 1 is in sequence #155Specify log: {<RET>=suggested | filename | AUTO | CANCEL}auto                                               --->输入auto，自动apply日志文件ORA-00279: change 2654361 generated at 07/31/2013 15:19:54 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_156.arcORA-00280: change 2654361 for thread 1 is in sequence #156ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_155.arc' nolonger needed for this recoveryORA-00279: change 2654372 generated at 07/31/2013 15:19:55 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_157.arc  ORA-00280: change 2654372 for thread 1 is in sequence #157    --->日志apply到157ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_156.arc' nolonger needed for this recoveryORA-00283: recovery session canceled due to errors            --->下面提示出现了一个未知的数据文件添加到控制文件 ORA-01244: unnamed datafile(s) added to control file by media recoveryORA-01110: data file 26: '/u02/database/Ak3210/oradata/tbs_tmp.dbf'ORA-01112: media recovery not started                         --->给出错误信息，提示介质恢复没有启动SQL> ho ls /u02/database/Ak3210/oradata/tbs_tmp.dbf           --->查看相应的数据文件，因为这个文件在备份服务器根本就不存在ls: /u02/database/Ak3210/oradata/tbs_tmp.dbf: No such file or directory--->使用下面的命令来重建数据文件，为什么可以这样操作呢？这个是依赖于归档日志记录了这个数据文件                                                 SQL> alter database create datafile 26 as '/u02/database/Ak3210/oradata/tbs_tmp.dbf';Database altered.SQL> ho ls /u02/database/Ak3210/oradata/tbs_tmp.dbf           --->再次查看数据文件已经存在了/u02/database/Ak3210/oradata/tbs_tmp.dbfSQL> recover database using backup controlfile;               --->再次恢复数据库ORA-00279: change 2656873 generated at 07/31/2013 16:33:06 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_157.arcORA-00280: change 2656873 for thread 1 is in sequence #157Specify log: {<RET>=suggested | filename | AUTO | CANCEL}auto                                                          --->输入autoORA-00279: change 2656938 generated at 07/31/2013 16:34:30 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_158.arcORA-00280: change 2656938 for thread 1 is in sequence #158ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_157.arc' nolonger needed for this recoveryORA-00279: change 2656966 generated at 07/31/2013 16:35:42 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_159.arcORA-00280: change 2656966 for thread 1 is in sequence #159ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_158.arc' nolonger needed for this recoveryORA-00308: cannot open archived log'/u02/database/Ak3210/archive/arch_818416637_1_159.arc'  --->寻找sequence为159的，实际上它是不存在的，所以找不到ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> recover database using backup controlfile until cancel;  --->再次恢复数据库ORA-00279: change 2656966 generated at 07/31/2013 16:35:42 needed for thread 1ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_159.arcORA-00280: change 2656966 for thread 1 is in sequence #159Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancel                                                       --->输入cancelMedia recovery cancelled.SQL> alter database open resetlogs;                          --->以resetlogs方式open数据库Database altered.SQL> select * from xy;                                       --->验证结果，数据库恢复成功SEQ                  WHO                  DT-------------------- -------------------- --------------------FirstArch            Robinson             20130731 16:34:15SecnodArch           Jackson              20130731 16:35:35SQL> shutdown immediate;                                     --->关闭数据库oracle@BKDB01p:~> export ORACLE_SID=Ak3210oracle@BKDB01p:~> rman target / catalog rman_user/rman@catadb --->再次连接到catalogRMAN> startup mount;                                          --->启动到mount状态Oracle instance starteddatabase mountednew incarnation of database registered in recovery catalog    --->可以看到新的incarnation被注册到了catalog  starting full resync of recovery catalogfull resync completeRMAN> list incarnation;                                       --->列出当前数据库的incarnationList of Database IncarnationsDB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time------- ------- -------- ---------------- --- ---------- ----------357     358     Ak3210   1008246269       PARENT  1          20130618 09:57:17357     34690   Ak3210   1008246269       CURRENT 2656967    20130731 16:54:39RMAN> reset database to incarnation 358;                      --->重置当前数据库的incarnationdatabase reset to incarnation 358RMAN> resync catalog;                                         --->同步的catalogRMAN> shutdown abort;对于在Prod段删除表空间和数据文件的处理比添加较为简单，无需要单独处理。直接执行restore以及recover就可了。但是其对应的物理数据文件依旧存在于OS系统之上，可以手动删除即可。

 

相关参考
    中小型数据库 RMAN CATALOG 备份恢复方案(一)

    中小型数据库 RMAN CATALOG 备份恢复方案(二)

    RMAN 数据库克隆文件位置转换方法

    基于RMAN的异机数据库克隆(rman duplicate)

    基于 RMAN 的同机数据库克隆

    基于用户管理的同机数据库克隆

    基于RMAN从活动数据库异机克隆(rman duplicate from active DB)

    RMAN duplicate from active 时遭遇 ORA-17627 ORA-12154

    Oracle 冷备份

    Oracle 热备份

    Oracle 备份恢复概念

    Oracle 实例恢复

    Oracle 基于用户管理恢复的处理

    SYSTEM 表空间管理及备份恢复

    SYSAUX表空间管理及恢复

    Oracle 基于备份控制文件的恢复(unsing backup controlfile)

    RMAN 概述及其体系结构

    RMAN 配置、监控与管理

    RMAN 备份详解

    RMAN 还原与恢复

    RMAN catalog 的创建和使用

    基于catalog 创建RMAN存储脚本

    基于catalog 的RMAN 备份与恢复

    RMAN 备份路径困惑

    自定义 RMAN 显示的日期时间格式

    只读表空间的备份与恢复

    Oracle 基于用户管理的不完全恢复

    理解 using backup controlfile

    使用RMAN实现异机备份恢复(WIN平台)

    使用RMAN迁移文件系统数据库到ASM

    基于Linux下 Oracle 备份策略(RMAN)

    Linux 下RMAN备份shell脚本

    使用RMAN迁移数据库到异机

    RMAN 提示符下执行SQL语句

    Oracle 基于 RMAN 的不完全恢复(incomplete recovery by RMAN)

    rman 还原归档日志(restore archivelog)