中小型数据库 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 Production

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ---------------------------------------------
Ak3210           N10db03p

--为prod添加tablespace
SQL> 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 ls
arch_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 ls
arch_818416637_1_157.arc  arch_818416637_1_158.arc

SQL> 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/archive
arch_818416637_1_157.arc                                       100%   34MB  34.2MB/s   00:00
arch_818416637_1_158.arc                                       100%   12KB  12.0KB/s   00:00

--Prod数据库的归档情况,当前Log sequence是159
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/database/Ak3210/archive/
Oldest online log sequence     157
Next log sequence to archive   159
Current log sequence           159
SQL> col name format a60
SQL> set linesize 160
SQL> 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:42

SQL> select * from xy;

SEQ                  WHO                  DT
-------------------- -------------------- --------------------
FirstArch            Robinson             20130731 16:34:15
SecnodArch           Jackson              20130731 16:35:35

3、Bak Server上DB的恢复操作

oracle@BKDB01p:~> export ORACLE_SID=Ak3210
oracle@BKDB01p:~> rman target / catalog rman_user/rman@catadb    --在备份服务器上连接target DB 及catalog DB

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Jul 31 16:39:45 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> startup mount;                --->启动数据库到mount状态
RMAN> restore archivelog all;       --->还原所有的归档日志

Starting restore at 20130731 16:41:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1090 devtype=DISK

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=156
channel ORA_DISK_1: reading from backup piece /u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/
  2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5t_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5t_.bkp tag=ARCHBK
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=155
channel ORA_DISK_1: reading from backup piece /u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/
  2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5l_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/database/Ak3210/flash_recovery_area/Ak3210/backupset/2013_07_31/o1_mf_annnn_ARCHBK_8zkgnw5l_.bkp tag=ARCHBK
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 20130731 16:41:46

RMAN> list copy;                    --->查看刚刚还原出来的日志文件


List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
34428   1    155     A 20130731 01:00:50 /u02/database/Ak3210/archive/arch_818416637_1_155.arc
34427   1    156     A 20130731 15:19:54 /u02/database/Ak3210/archive/arch_818416637_1_156.arc

RMAN> catalog archivelog '/u02/database/Ak3210/archive/arch_818416637_1_157.arc';  --->将新的归档日志注册到catalog

cataloged archive log
archive log filename=/u02/database/Ak3210/archive/arch_818416637_1_157.arc recid=148 stamp=822242629

RMAN> catalog archivelog '/u02/database/Ak3210/archive/arch_818416637_1_158.arc';

cataloged archive log
archive log filename=/u02/database/Ak3210/archive/arch_818416637_1_158.arc recid=149 stamp=822242639

RMAN> list copy;                   --->再次查看时,所有的归档日志已经位于归档目录 


List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
34428   1    155     A 20130731 01:00:50 /u02/database/Ak3210/archive/arch_818416637_1_155.arc
34427   1    156     A 20130731 15:19:54 /u02/database/Ak3210/archive/arch_818416637_1_156.arc
34495   1    157     A 20130731 15:19:55 /u02/database/Ak3210/archive/arch_818416637_1_157.arc
34534   1    158     A 20130731 16:34:30 /u02/database/Ak3210/archive/arch_818416637_1_158.arc


RMAN> run{                        --->使用until方式恢复数据库,下面给出了错误提示
2> set until sequence 159;
3> recover database;}

executing command: SET until clause

Starting recover at 20130731 16:45:47
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 155 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_155.arc
archive log thread 1 sequence 156 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_156.arc
archive log thread 1 sequence 157 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_157.arc
archive log thread 1 sequence 158 is already on disk as file /u02/database/Ak3210/archive/arch_818416637_1_158.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/31/2013 16:45:51
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 94 lowscn 2457942 found to restore

RMAN> exit

Recovery Manager complete.
oracle@BKDB01p:~> export ORACLE_SID=Ak3210
oracle@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 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_155.arc
ORA-00280: change 2654259 for thread 1 is in sequence #155

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto                                               --->输入auto,自动apply日志文件
ORA-00279: change 2654361 generated at 07/31/2013 15:19:54 needed for thread 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_156.arc
ORA-00280: change 2654361 for thread 1 is in sequence #156
ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_155.arc' no
longer needed for this recovery

ORA-00279: change 2654372 generated at 07/31/2013 15:19:55 needed for thread 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_157.arc  
ORA-00280: change 2654372 for thread 1 is in sequence #157    --->日志apply到157
ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_156.arc' no
longer needed for this recovery

ORA-00283: recovery session canceled due to errors            --->下面提示出现了一个未知的数据文件添加到控制文件 
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-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.dbf

SQL> recover database using backup controlfile;               --->再次恢复数据库
ORA-00279: change 2656873 generated at 07/31/2013 16:33:06 needed for thread 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_157.arc
ORA-00280: change 2656873 for thread 1 is in sequence #157

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto                                                          --->输入auto
ORA-00279: change 2656938 generated at 07/31/2013 16:34:30 needed for thread 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_158.arc
ORA-00280: change 2656938 for thread 1 is in sequence #158
ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_157.arc' no
longer needed for this recovery

ORA-00279: change 2656966 generated at 07/31/2013 16:35:42 needed for thread 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_159.arc
ORA-00280: change 2656966 for thread 1 is in sequence #159
ORA-00278: log file '/u02/database/Ak3210/archive/arch_818416637_1_158.arc' no
longer needed for this recovery

ORA-00308: cannot open archived log
'/u02/database/Ak3210/archive/arch_818416637_1_159.arc'  --->寻找sequence为159的,实际上它是不存在的,所以找不到
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile until cancel;  --->再次恢复数据库
ORA-00279: change 2656966 generated at 07/31/2013 16:35:42 needed for thread 1
ORA-00289: suggestion : /u02/database/Ak3210/archive/arch_818416637_1_159.arc
ORA-00280: change 2656966 for thread 1 is in sequence #159

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel                                                       --->输入cancel
Media recovery cancelled.
SQL> alter database open resetlogs;                          --->以resetlogs方式open数据库

Database altered.

SQL> select * from xy;                                       --->验证结果,数据库恢复成功

SEQ                  WHO                  DT
-------------------- -------------------- --------------------
FirstArch            Robinson             20130731 16:34:15
SecnodArch           Jackson              20130731 16:35:35

SQL> shutdown immediate;                                     --->关闭数据库

oracle@BKDB01p:~> export ORACLE_SID=Ak3210
oracle@BKDB01p:~> rman target / catalog rman_user/rman@catadb --->再次连接到catalog
RMAN> startup mount;                                          --->启动到mount状态

Oracle instance started
database mounted
new incarnation of database registered in recovery catalog    --->可以看到新的incarnation被注册到了catalog  
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;                                       --->列出当前数据库的incarnation


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
357     358     Ak3210   1008246269       PARENT  1          20130618 09:57:17
357     34690   Ak3210   1008246269       CURRENT 2656967    20130731 16:54:39

RMAN> reset database to incarnation 358;                      --->重置当前数据库的incarnation

database reset to incarnation 358

RMAN> resync catalog;                                         --->同步的catalog
RMAN> shutdown abort;

对于在Prod段删除表空间和数据文件的处理比添加较为简单,无需要单独处理。直接执行restore以及recover就可了。但是其对应的物理数据文件依旧
存在于OS系统之上,可以手动删除即可。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

记一次奇怪的ORA-04028: cannot generate diana for object

      开发人员说新建了一个package,在编译的过程中出现了一些错误。提示为PL/SQL:ORA-00942: table or view does n...

6310
来自专栏乐沙弥的世界

Oracle 表空间时点恢复(TSPITR)

表空间时点恢复,是Oracle在基于冷备,热备恢复以外的一种以表空间为粒度的,不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMA...

15220
来自专栏乐沙弥的世界

ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted

    最近的RAC环境中遭遇ORA-00254,ORA-15173,即无法进行归档。通常情况下归档失败我们考虑更多的是归档路径的不可达,或归档所在的磁盘空间不...

10630
来自专栏c#开发者

数据中心使用dtu远程连接oracel 9i数据库问题

数据中心使用dtu远程连接oracel 9i数据库问题 发表人:lurrance | 发表时间: 2005年二月20日, 12:13 经过从网上找来的资料以及看...

378110
来自专栏乐沙弥的世界

使用crs_profile管理RAC资源配置文件

    profile通常指配置文件,crs_profile望文生义可知,就是管理集群的配置文件。在Oraclele RAC中,所有的CRS资源存放在OCR磁盘...

7830
来自专栏杨建荣的学习笔记

关于Oracle重启数据库的一个bug(r5笔记第50天)

关于drop database在oracle中是致命的操作,这个操作自己在测试环境中体验过,会完全删除数据文件,因此这个操作非常敏感但是实用性不强,不过话说过来...

36440
来自专栏乐沙弥的世界

无法成功执行catalog.sql,ORA-04045 ORA-04064

      最近同事报料,数据库无法执行一些关于数据字典的查询。即使是查询dba_users都会出现错误。从错误日志来看,全部是数据字典的一些错误信息,而用户数...

13830
来自专栏杨建荣的学习笔记

alert日志中的两种ORA错误分析(r6笔记第21天)

今天在巡检系统的时候,发现alert日志中有两种类型的ora错误。 Errors in file /U01/app/oracle/diag/rdbms/XX/...

30150
来自专栏乐沙弥的世界

SYSAUX表空间管理及恢复

SYSAUX表空间是在10g之后引入的一个新的表空间,主要用于减轻对SYSTEM表空间的压力而作为SYSTEM表空间的辅助表空间。

11720
来自专栏数据库新发现

使用热备份进行分时恢复----怎样通过归档逐步恢复以缩短数据迁移时间

Last Updated: Monday, 2004-11-15 10:32 Eygle

12030

扫码关注云+社区

领取腾讯云代金券