中小型数据库 RMAN CATALOG 备份恢复方案(三)
中小型数据库 RMAN CATALOG 备份恢复方案(三)
Leshami
发布于 2018-08-13 15:29:57
发布于 2018-08-13 15:29:57
在前两篇文章中描述了中小型数据库使用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:353、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系统之上，可以手动删除即可。本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2013年08月13日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读