使用RMAN將生產庫資料恢復到另外環境追溯先前的資料

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=============================================