ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用RMAN将生产库数据恢复到另外环境追溯先前的数据

使用RMAN将生产库数据恢复到另外环境追溯先前的数据

原创 Linux操作系统 作者:ljm0211 时间:2012-07-02 19:55:29 0 删除 编辑

rman target /

RMAN>

startup nomount;

restore spfile from '/image/oraywzkr/tmp/xxx.bkp';

create pfile from spfile;

修改pfile内容

========================================================================

*._awr_flush_threshold_metrics=TRUE
*.audit_file_dest='/image/oraywzkr/admin/ywzkr/adump'
*.background_dump_dest='/image/oraywzkr/admin/ywzkr/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/image/oraywzkr/oradata/ywzkr/control01.ctl','/image/oraywzkr/oradata/ywzkr/control02.ctl','/image/oraywzkr/oradata
/ywzkr/control03.ctl'
*.core_dump_dest='/image/oraywzkr/admin/ywzkr/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ywzkr'
*.job_queue_processes=20
*.log_archive_dest_1='location=/image/oraywzkr/archive/'
*.log_archive_format='arch_%d_%t_%r_%s.log'
*.nls_date_format='yyyy-mm-dd hh24:mi:ss'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=200M
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=60
*.sessions=85
*.sga_max_size=1G
*.sga_target=600M
*.sort_area_size=655360
*.undo_management='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/image/oraywzkr/admin/ywzkr/udump'

========================================================================

startup nomount pfile='/image/oraywzkr/product/10.2.0/dbs/initywzkr.ora'

startup 成功

shutdown immediate

create spfile from pfile;

startup nomount;

restore controlfile from '/image/oraywzkr/tmp/dbbackup/rmanbackup/backupset/YW_2009_08_01/controlfile_YWZKR_693706484_979_1.bkp';

alter database mount;

通过pfile设置数据库参数 nls_date_format=yyyy-mm-dd hh24:mi:ss (没有引号, 否则出错无法进入RMAN)

cat > /image/sh/newname.sh

run {

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/example01.dbf'      TO '/image/oraywzkr/oradata/ywzkr/example01.dbf';

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs01.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs01.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs02.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs02.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs03.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs03.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs04.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs04.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs05.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs05.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs06.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs06.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs07.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs07.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs08.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs08.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs09.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs09.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_data_tbs10.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_data_tbs10.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_index_tbs01.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_index_tbs01.dbf';

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/hx_index_tbs02.dbf' TO '/image/oraywzkr/oradata/ywzkr/hx_index_tbs02.dbf';

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/jy_data_tbs01.dbf' TO '/image/oraywzkr/oradata/ywzkr/jy_data_tbs01.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/jy_data_tbs02.dbf' TO '/image/oraywzkr/oradata/ywzkr/jy_data_tbs02.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/jy_data_tbs03.dbf' TO '/image/oraywzkr/oradata/ywzkr/jy_data_tbs03.dbf' ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/jy_index_tbs01.dbf' TO '/image/oraywzkr/oradata/ywzkr/jy_index_tbs01.dbf';

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/redo01_1.log'       TO '/image/oraywzkr/oradata/ywzkr/redo01_1.log'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/redo02_1.log'       TO '/image/oraywzkr/oradata/ywzkr/redo02_1.log'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/redo03_1.log'       TO '/image/oraywzkr/oradata/ywzkr/redo03_1.log'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/redo04_1.log'       TO '/image/oraywzkr/oradata/ywzkr/redo04_1.log'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/redo05_1.log'       TO '/image/oraywzkr/oradata/ywzkr/redo05_1.log'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/sysaux01.dbf'       TO '/image/oraywzkr/oradata/ywzkr/sysaux01.dbf'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/system01.dbf'       TO '/image/oraywzkr/oradata/ywzkr/system01.dbf'      ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/temp01.dbf'         TO '/image/oraywzkr/oradata/ywzkr/temp01.dbf'        ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/undotbs01.dbf'      TO '/image/oraywzkr/oradata/ywzkr/undotbs01.dbf'     ;

SET NEWNAME FOR DATAFILE '/ywzkr_db/ywzkr/users01.dbf'        TO '/image/oraywzkr/oradata/ywzkr/users01.dbf'       ;

restore database;

switch datafile all;

}

/image/sh/newname.sh 这部分脚本需要根据实际情况将备份集中包含的所有数据文件都进行SET NEWNAME,当然有些没有包含在备份集中的数据文件可以从脚本中删除。

关于restore database的时候,如果备份集没有存放在原来的位置(control file中记录的),那么需要使用catalog backuppiece命令将新位置的备份集注册到control file中。

startup mount;

rman target / @/image/sh/newname.sh

ln -s /image/oraywzkr/tmp/ /ywzkr_arch/dbbackup/rmanbackup/yw_arch_work/

把archive log路径设置为原来备份时的一样:

copy all archive log to '/image/oraywzkr/tmp/ywzkr_arch/archive/'

cd /

ln -s /image/oraywzkr/tmp/ ywzkr_arch

rman target / catalog     (此处连接了生产库的恢复目录数据库,在不连接恢复目录的时候restore archivelog all;执行不成功,不过连接恢复目录后会对生产库的恢复目录有影响)

run {

set archivelog destination to '/image/oraywzkr/archive';

restore archivelog all;

}

生 产库的恢复目录被RMAN恢复出来的数据库库使用后出现的问题

此处建议不要使用生产库的恢复目录,以免对生产库的恢复目录造成破坏,影响生产库的备份。在不适用恢复目录的情况下,可以使用 catalog命令注册归档日志的备份集。然后直接执行recover database命令。

如果所有需要的archivelog已经存在于归档日志目录中,那么只需要使用catalog archivelog命令注册第一个需要的archivelog,然后执行recover database命令时,rman就会自动按照seq递增找到其后所有需要的archivelog。

如果只有archivelog的备份集存在于磁盘中,可以使用catalog backuppiece命令注册所有archivelog备份集,然后就可以执行recover database命令。

catalog命令例如:catalog backuppiece '/image/oraywzkr/tmp/dbbackup/rmanbackup/yw_arch_work/YW_ARCH_2009_08_01/arch_YWZKR_693706627_981_1.bkp';

此处如果有很多的归档日志备份集需要注册,编写所有归档日志备份集的注册脚本需要一定的工作量。可以考虑使用find命令来配合生成脚本 (find ./ -name *.bkp)。

recover database until time '2009-08-05 23:59:59';

alter database rename file '/ywzkr_db/ywzkr/redo01_1.log' to '/image/oraywzkr/oradata/ywzkr/redo01_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo02_1.log' to '/image/oraywzkr/oradata/ywzkr/redo02_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo03_1.log' to '/image/oraywzkr/oradata/ywzkr/redo03_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo04_1.log' to '/image/oraywzkr/oradata/ywzkr/redo04_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo05_1.log' to '/image/oraywzkr/oradata/ywzkr/redo05_1.log';

alter database open resetlogs;

CREATE temporary TABLESPACE temp1 TEMPFILE '/image/oraywzkr/oradata/ywzkr/temp1_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 640K MAXSIZE 2G;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;
CREATE temporary TABLESPACE temp TEMPFILE '/image/oraywzkr/oradata/ywzkr/temp_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 640K MAXSIZE 2G;
alter database default temporary tablespace temp;
drop tablespace temp1 including contents and datafiles;

=============下面重新恢复==========================

删除所有数据库文件(/image/oraywzkr/oradata/ywzkr下的文件)

startup nomount;

restore controlfile from '/image/oraywzkr/tmp/dbbackup/rmanbackup/backupset/YW_2009_08_01/controlfile_YWZKR_693706484_979_1.bkp';

alter database mount;

RMAN> @/image/newname.sh

run {

set archivelog destination to '/image/oraywzkr/archive';

restore archivelog all;

}

这一步还有一个作用就是将归档日志的信息注册到control file里。

$ cp -r ywzkr ywzkr_bak/   (冷备,/image/oraywzkr/oradata/ywzkr下的文件)

recover database until time '2009-08-06 22:00:00';

alter database rename file '/ywzkr_db/ywzkr/redo01_1.log' to '/image/oraywzkr/oradata/ywzkr/redo01_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo02_1.log' to '/image/oraywzkr/oradata/ywzkr/redo02_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo03_1.log' to '/image/oraywzkr/oradata/ywzkr/redo03_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo04_1.log' to '/image/oraywzkr/oradata/ywzkr/redo04_1.log';
alter database rename file '/ywzkr_db/ywzkr/redo05_1.log' to '/image/oraywzkr/oradata/ywzkr/redo05_1.log';

======================================================

$cp -r ywzkr ywzkr_0806

做一个8月6号的冷备,以便提取完8月6号的数据后,直接恢复8月6号冷备份,应用6号以后的归 档日志以取得其后的数据

======================================================

alter database open resetlogs;

CREATE temporary TABLESPACE temp1 TEMPFILE '/image/oraywzkr/oradata/ywzkr/temp1_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 640K MAXSIZE 2G;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;
CREATE temporary TABLESPACE temp TEMPFILE '/image/oraywzkr/oradata/ywzkr/temp_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 640K MAXSIZE 2G;
alter database default temporary tablespace temp;
drop tablespace temp1 including contents and datafiles;

exp business/zjywbus file=/image/exp_recover/wflog_20090806.dmp log=/image/exp_recover/wflog_20090806.log tables=wflog

测试环境zjhxcs中执行:

/* Formatted on 2009/10/21 15:14 (Formatter Plus v4.8.7) */
INSERT INTO business.wflog_recover
SELECT flowid, logno, modelno, nodeno, nodename, deptcode, deptname,
operatorcode, operatorname, flowintime, timelimit, handletime,
submittime, nodestatus, flowstatus, packageid, businesstype,
businessno, contractno, classcode, riskcode, makecom, comcode,
handlercode, handler1code, relateflowid, relatelogno, posx, posy,
flag, licenseno, relatecontractno, riskcategory, insuredcode,
insuredname, identifytype, identifynumber, reinsstatus, policyno,
claimno, entrustcomcode, entrustedcomcode, entrustflag,
'2009-08-06'
FROM ;

==========循环步骤 loop1===============

shutdown immediate

$rm -rf ywzkr

$mv ywzkr_0806 ywzkr

startup mount

recover database until time '2009-08-12 22:00:00';

shutdown immediate

$cp -r ywzkr ywzkr_0812

startup mount

alter database open resetlogs;

提取数据!

==========循环步骤 loop1===============

shutdown immediate

开始循环执行循环步骤 loop1,将其中的时间替换为其后需要恢复到的时间!

=======================================END=============================================

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    442407