ITPub博客

首页 > 数据库 > Oracle > 利用RMAN备份异机恢复

利用RMAN备份异机恢复

原创 Oracle 作者:itpub120 时间:2006-11-23 17:22:36 0 删除 编辑

利用RMAN备份异机恢复
--rman全备份,异机恢复

关键字:RMAN,备份,恢复,参数文件,控制文件,数据文件,oracle

[@more@]1 概述
1.1试验目的
先暂定两个服务器:
服务器A 151 ip:192.168.1.151
服务器B 152 ip:192.168.1.152
服务器A 是主服务器,服务器B是备份服务器.两个数据库的目录结构一样.
首先每周二和五在A机器本地一个rman全备份,并且备份完毕后作恢复测试.
如果服务器A 的数据文件或者其它损坏了,可以利用本机备份恢复.
但是如果服务器A 整个都坏了,那就没有办法,所以在机器A做完rman全备份后
每次自动ftp把备份集传到机器B上.
试验目的:机器A上作一个rman全备份 然后在机器B上恢复.
1.2试验环境
OS: Linux ES 4.0
DB:oracle 9.2.0.1
数据库名称:sms
2 rman全备份
2.1 目录结构
[oracle@oracleserver2 oracle]$ echo $ORACLE_BASE
/ora9/oradata
[oracle@oracleserver2 oracle]$ echo $ORACLE_HOME
/ora9/oracle/product
[oracle@oracleserver2 oracle]$
2.2 DBID,参数文件,密码文件
DBID: 629320509
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select dbid from v$database;
DBID
----------
629320509
2.3 数据文件,控制文件,归档日志文件
执行备份脚本: rman_deldata.sh
[oracle@oracleserver2 oracle]$ more /home/oracle/rman/rman_back.sh
# write by hancy
# date 2006-1-16
(
export NLS_LANG=american_america.ZHS16GBK
export ORACLE_HOME=/ora9/oracle/product
export ORACLE_BASE=/ora9/oradata
export ORACLE_SID=sms
export PATH=$PATH:$ORACLE_HOME/bin
date;
rman target=sys/h523fp@151 <run{
allocate channel c1 type disk format '/backup2/rman_back/%T%U';
allocate channel c2 type disk format '/backup2/rman_back/%T%U';
set limit channel c1 kbytes=2097150;
set limit channel c2 kbytes=2097150;
backup full tag 'db151full' database include current controlfile;
sql 'alter system archive log current';
sql 'alter system switch logfile ';
backup archivelog from time 'sysdate-1';
release channel c1;
release channel c2;
}
exit
EOF
date;
)>/home/oracle/rman/`date +%Y%m%d`rman.log
2.4 复制备份集到机器B上
把一下备份集ftp到机器B上,然后在机器B 上安装同版本的数据库软件,但是不要安装数据库,如果安装了可以删除数据等文件.
2099196 -rw-rw---- 1 oracle 2147475456 11月 14 06:11 20061114fki2bp8u_1_1
2099196 -rw-rw---- 1 oracle 2147475456 11月 14 06:21 20061114fki2bp8u_2_1
2099196 -rw-rw---- 1 oracle 2147475456 11月 14 06:32 20061114fki2bp8u_3_1
2099196 -rw-rw---- 1 oracle 2147475456 11月 14 06:42 20061114fki2bp8u_4_1
2099196 -rw-rw---- 1 oracle 2147475456 11月 14 06:53 20061114fki2bp8u_5_1
2099196 -rw-rw---- 1 oracle 2147475456 11月 14 07:04 20061114fki2bp8u_6_1
1858084 -rw-rw---- 1 oracle 1900814336 11月 14 07:13 20061114fki2bp8u_7_1
1564484 -rw-rw---- 1 oracle 1600462848 11月 14 07:22 20061114fli2bti9_1_1
1587844 -rw-rw---- 1 oracle 1624358912 11月 14 07:30 20061114fmi2bu1p_1_1
2098276 -rw-rw---- 1 oracle 2146533376 11月 14 07:40 20061114fni2bugv_1_1
2098276 -rw-rw---- 1 oracle 2146533376 11月 14 07:51 20061114foi2bv55_1_1
1526976 -rw-rw---- 1 oracle 1562091520 11月 14 07:59 20061114fpi2bvp3_1_1
357236 -rw-rw---- 1 oracle 365445120 11月 14 08:01 20061114fqi2c080_1_1
2564 -rw-rw---- 1 oracle 2621440 11月 14 08:01 c-629320509-20061114-00
2099204 -rw-rw---- 1 oracle 2147481600 11月 14 08:28 20061114fsi2c0cq_1_1
754256 -rw-rw---- 1 oracle 771597312 11月 14 08:40 20061114fsi2c0cq_2_1
2540 -rw-rw---- 1 oracle 2596864 11月 14 08:40 snapcf_sms.f
2580 -rw-rw---- 1 oracle 2637824 11月 14 08:41 c-629320509-20061114-01
3 man异机恢复
3.1 恢复分析,方案确定
如果现在机器A已经瘫痪了,os都倒了不能工作,现在面临一个很严重的问题,如果快速?安全的恢复数据库到机器B上?还有你会面临来自公司,老板很大的压力?
怎么办? 怎么解决?
首先要冷静,消除杂念,仔细分析现在的环境,不要急于动手.
现在环境如下:
(1) 有一个有效rman的全备份,包括控制文件,参数文件
(2) 还有备份日志保存完好.
(3) 机器B 的数据库软件已经安装完毕.
机器A上的全备份时间较长,期间会有log切换,会产生归档日志,备份片scn会不一致.
首先找出备份片里面最大的scn,来确定我们可以恢复的时间点.
采用:利用rman的全备份不完全恢复,可以基于scn,也可以基于seq.
3.2 确定时间点问题
最大数据文件的scn:
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
435 Full 348M DISK 00:01:53 14-NOV-06
BP Key: 746 Status: AVAILABLE Tag: DB151FULL
Piece Name: /backup2/rman_back/20061114fqi2c080_1_1
Controlfile Included: Ckp SCN: 2779791398 Ckp time: 14-NOV-06
List of Datafiles in backup set 435
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2779791412 14-NOV-06 /ora9/oradata/oradata/sms/system01.dbf
6 Full 2779791412 14-NOV-06 /ora9/oradata/oradata/sms/indx01.dbf
8 Full 2779791412 14-NOV-06 /ora9/oradata/oradata/sms/tools01.dbf
9 Full 2779791412 14-NOV-06 /ora9/oradata/oradata/sms/users01.dbf
最小数据文件scn:
2 Full 2779668804 14-NOV-06 /ora9/oradata/oradata/sms/undotbs01.dbf
3 Full 2779668804 14-NOV-06 /backup/ora_data/SPP_DB1.dbf
4 Full 2779668804 14-NOV-06 /backup/ora_data/perstat.dbf
5 Full 2779668804 14-NOV-06 /backup/ora_data/SMS_DB5.dbf
13 Full 2779668804 14-NOV-06 /backup/ora_data/SMP_DB3.dbf
14 Full 2779668804 14-NOV-06 /backup/ora_data/SMP_DB4.dbf
17 Full 2779668804 14-NOV-06 /backup/ora_data/SMS_DB3.dbf
19 Full 2779668804 14-NOV-06 /backup/ora_data/SMP_DB5.dbf
20 Full 2779668804 14-NOV-06 /backup/ora_data/SMS_DB4.dbf
22 Full 2779668804 14-NOV-06 /tmp/TEMP_DB.dbf
23 Full 2779668804 14-NOV-06 /backup/ora_data/CHAT_DB.dbf
归档日志scn:
1 15036 2779038309 13-NOV-06 2779095845 13-NOV-06
1 15037 2779095845 13-NOV-06 2779179637 13-NOV-06
1 15038 2779179637 13-NOV-06 2779208527 13-NOV-06
1 15039 2779208527 13-NOV-06 2779265891 13-NOV-06
1 15040 2779265891 13-NOV-06 2779341934 14-NOV-06
1 15041 2779341934 14-NOV-06 2779407093 14-NOV-06
1 15042 2779407093 14-NOV-06 2779464082 14-NOV-06
1 15043 2779464082 14-NOV-06 2779519525 14-NOV-06
1 15044 2779519525 14-NOV-06 2779563468 14-NOV-06
1 15045 2779563468 14-NOV-06 2779591580 14-NOV-06
1 15046 2779591580 14-NOV-06 2779648660 14-NOV-06
1 15047 2779648660 14-NOV-06 2779712963 14-NOV-06
1 15048 2779712963 14-NOV-06 2779768813 14-NOV-06
1 15049 2779768813 14-NOV-06 2779793829 14-NOV-06
1 15050 2779793829 14-NOV-06 2779794400 14-NOV-06
1 15051 2779794400 14-NOV-06 2779794410 14-NOV-06
控制文件scn:
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
438 Full 2M DISK 00:00:04 14-NOV-06
BP Key: 750 Status: AVAILABLE Tag:
Piece Name: /backup2/rman_back/c-629320509-20061114-01
SPFILE Included: Modification time: 04-SEP-06
要恢复的时间点需要大于数据文件的恢复点,并且要保证需要的arc都存在.
数据文件scn: 2779668804 --2779791412
最后确定恢复时间点为: scn=2779794400 seq=15050
数据文件scn是2779668804 --2779791412 然后利用arc15047 -15050 向前恢复到 2779794400 .
3.3 恢复控制文件
set oracle_sid=sms
rman target=sys/pass@152
set DBID=629320509
startup nomount;
run{
set controlfile autobackup format for device type disk to '/ora10/rman_back/%F';
restore controlfile from autobackup;
}
或者利用dbms_backup_restore
declare
devtype varchar2(256);
done boolean;
begin
devtype:=dbms_backup_restore.deviceallocate(NULL);
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('/ora10/oracle/oradata/testdb1/control01.ctl');
dbms_backup_restore.restorebackuppiece('/ora10/rman_back/2006111304i2a4u8_1_1',DONE=>done);
end;
3.4 恢复归档日志文件
利用dbms_backup_restore 这个包恢复归档日志:
declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/ora9/oracle/product/dbs/arch');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15046);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15047);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15048);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15049);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15050);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15051);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/backup2/rman_back/20061114fsi2c0cq_1_1',params=>null);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/backup2/rman_back/20061114fsi2c0cq_2_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
3.5 恢复数据文件
控制文件恢复成功后.修改数据库为mount状态,加载ctl文件
alter database mount;
restore database until scn 2779794400
recover database until scn 2779794400
或者
restore database until sequence 15050 thread 1
recover database until sequence 15050 thread 1
恢复日志如下:
RMAN> restore database until scn 2779794400;
Starting restore at 14-NOV-06
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /ora9/oradata/oradata/sms/undotbs01.dbf
restoring datafile 00003 to /backup/ora_data/SPP_DB1.dbf
restoring datafile 00004 to /backup/ora_data/perstat.dbf
restoring datafile 00005 to /backup/ora_data/SMS_DB5.dbf
restoring datafile 00013 to /backup/ora_data/SMP_DB3.dbf
restoring datafile 00014 to /backup/ora_data/SMP_DB4.dbf
restoring datafile 00017 to /backup/ora_data/SMS_DB3.dbf
restoring datafile 00019 to /backup/ora_data/SMP_DB5.dbf
restoring datafile 00020 to /backup/ora_data/SMS_DB4.dbf
restoring datafile 00022 to /tmp/TEMP_DB.dbf
restoring datafile 00023 to /backup/ora_data/CHAT_DB.dbf
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /ora9/oradata/oradata/sms/SMP_DB1.dbf
channel ORA_DISK_2: restored backup piece 1
piece handle=/backup2/rman_back/20061114fli2bti9_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=/backup2/rman_back/20061114fki2bp8u_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00012 to /ora9/oradata/oradata/sms/SMP_DB2.dbf
channel ORA_DISK_1: restored backup piece 2
piece handle=/backup2/rman_back/20061114fki2bp8u_2_1 tag=DB151FULL params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=/backup2/rman_back/20061114fki2bp8u_3_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restored backup piece 1
piece handle=/backup2/rman_back/20061114fmi2bu1p_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 4
piece handle=/backup2/rman_back/20061114fki2bp8u_4_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00015 to /ora9/oradata/oradata/sms/SMS_DB1.dbf
channel ORA_DISK_1: restored backup piece 5
piece handle=/backup2/rman_back/20061114fki2bp8u_5_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restored backup piece 1
piece handle=/backup2/rman_back/20061114fni2bugv_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00016 to /ora9/oradata/oradata/sms/SMS_DB2.dbf
channel ORA_DISK_1: restored backup piece 6
piece handle=/backup2/rman_back/20061114fki2bp8u_6_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restored backup piece 1
piece handle=/backup2/rman_back/20061114foi2bv55_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00018 to /ora9/oradata/oradata/sms/SPP_DB.dbf
channel ORA_DISK_1: restored backup piece 7
piece handle=/backup2/rman_back/20061114fki2bp8u_7_1 tag=DB151FULL params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_2: restored backup piece 1
piece handle=/backup2/rman_back/20061114fpi2bvp3_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /ora9/oradata/oradata/sms/system01.dbf
restoring datafile 00006 to /ora9/oradata/oradata/sms/indx01.dbf
restoring datafile 00008 to /ora9/oradata/oradata/sms/tools01.dbf
restoring datafile 00009 to /ora9/oradata/oradata/sms/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/backup2/rman_back/20061114fqi2c080_1_1 tag=DB151FULL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 14-NOV-06
RMAN> recover database until scn 2779794400 ;
Starting recover at 14-NOV-06
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 15047 is already on disk as file /ora9/oracle/product/dbs/arch/1_15047.dbf
archive log thread 1 sequence 15048 is already on disk as file /ora9/oracle/product/dbs/arch/1_15048.dbf
archive log thread 1 sequence 15049 is already on disk as file /ora9/oracle/product/dbs/arch/1_15049.dbf
archive log thread 1 sequence 15050 is already on disk as file /ora9/oracle/product/dbs/arch/1_15050.dbf
archive log filename=/ora9/oracle/product/dbs/arch/1_15047.dbf thread=1 sequence=15047
archive log filename=/ora9/oracle/product/dbs/arch/1_15048.dbf thread=1 sequence=15048
archive log filename=/ora9/oracle/product/dbs/arch/1_15049.dbf thread=1 sequence=15049
archive log filename=/ora9/oracle/product/dbs/arch/1_15050.dbf thread=1 sequence=15050
media recovery complete
Finished recover at 14-NOV-06
3.6 一致性恢复,打开数据库重建log
RMAN> alter database open resetlogs;
database opened
RMAN>
SQL> select status from v$instance;
STATUS
------------
OPEN
后台日志:
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Tue Nov 14 16:15:18 2006
SMON: enabling tx recovery
Tue Nov 14 16:15:18 2006
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting control autobackup
Control autobackup written to DISK device
handle '/backup2/rman_back/c-629320509-20061114-01'
Completed: alter database open resetlogs
3.7 重新作一个rman 的全备份
执行备份脚本即可.
4 总结
1 做完rman全备份后最好执行一下有效性检查
restore database check logical validate
目的:一定要保证有一个有效,可恢复的全备份
2 主服务器和备份服务器目录结构最好一样,这个方便一些,否则还需要作文件目录转换.
3 一定不要忘记备份rman的备份日志,这样可以给恢复带来很到的作用.
4 rman的备份集存放目录最好机器A 和机器B 一致,否则在机器B 没有任何文件的时候
数据文件不能自动恢复,需要使用sys.dbms_backup恢复数据文件.
由于机器A 数据文件几十个手工恢复麻烦.
5 由于rman备份时间较长,在备份期间log会有很多arc产生.
为了不完全恢复一定要备份arc文件
先备份数据文件,强制切换log后备份arc.
6 在恢复前冷静的分析和确定可恢复的时间点.
成功恢复=rman全备份+最小数据文件scn到恢复时间点的arc文件
恢复的时间点需要大于最大数据文件的scn时间点
以上是经过反复数十次恢复测试的结果.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8109090/viewspace-879434/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    60
  • 访问量
    580369