使用dbms包結合RMAN來複制資料庫

2010年9月16日 星期四 使用dbms包結合RMAN來複制資料庫
第一步:配置各種環境,包括口令檔案,PFILE檔案,SPFILE檔案,各種DUMP檔案(bdump ,cdump ,dpdump,pfile ,udump)
       同時設定ORACLE_SID=saijuan 並且啟動到NOMOUNT的狀態
第二步：使用RMAN備份源資料庫，包括備份資料檔案，控制檔案，歸檔日誌檔案
run{
allocate channel d1 device type disk;
backup as compressed backupset
incremental level=0
format='/u01/bak_qilin/rmanbak/inc0_%d_%U'
tag='inc0'
channel=d1
database plus archivelog delete input;
backup
format='/u01/bak_qilin/rmanbak/bkctl.ctl'
tag='bkctl'
channel=d1
current controlfile;
}
第三步：複製源資料庫使用RMAN備份的備份檔案(包括備份的資料檔案，控制檔案，歸檔日誌檔案)到目標資料庫的指定目            錄（/u01/bak_qilin/rmanbak）
第四步：通過DBMS包從備份檔案中提取資料檔案dbmsbkrs_datafile.sql
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u01/oradata/qilin/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u01/oradata/qilin/undotbs01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u01/oradata/qilin/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u01/oradata/qilin/users01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u01/oradata/qilin/example01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/u01/oradata/qilin/undotbs02.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/u01/bak_qilin/rmanbak/      inc0_QILIN_1ilnutj5_1_1',params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
執行該指令碼 SQL> @/u01/bak_qilin/script/dbmsbkrs_datafile.sql

第五步：提取歸檔日誌檔案dbmsbkrs_arch.sql
以下是將這個備份檔案(inc0_QILIN_1klnutmm_1_1)中歸檔日誌全部恢復出來
DECLARE
devtype varchar2(256);
done boolean;
Begin
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetArchivedLog(DESTINATION=>'/u01/bak_qilin/archive/');
sys.dbms_backup_restore.restoreArchivedLogRange;
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/u01/bak_qilin/rmanbak/inc0_QILIN_1klnutmm_1_1',params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
執行該指令碼 SQL> @/u01/bak_qilin/script/dbmsbkrs_arch.sql
以下是將這個備份檔案(o1_mf_annnn_TAG20100916T060546_692jswgk_.bkp)中歸檔日誌部分恢復出來
備份歸檔日誌
RMAN> backup archivelog all delete input;
Starting backup at 16-SEP-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=729841459
input archive log thread=1 sequence=2 recid=2 stamp=729842746
channel ORA_DISK_1: starting piece 1 at 16-SEP-10
channel ORA_DISK_1: finished piece 1 at 16-SEP-10
piece handle=/u01/flash_recovery_area/SAIJUAN/backupset/2010_09_16/o1_mf_annnn_TAG20100916T060546_692jswgk_.bkp tag=TAG20100916T060546 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
--以下指的是歸檔日誌所放置的物理位置
archive log filename=/u01/flash_recovery_area/SAIJUAN/archivelog/2010_09_16/o1_mf_1_1_692hkm56_.arc recid=1 stamp=729841459
archive log filename=/u01/flash_recovery_area/SAIJUAN/archivelog/2010_09_16/o1_mf_1_2_692jst37_.arc recid=2 stamp=729842746
Finished backup at 16-SEP-10

使用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=>'/u01/bak_qilin/script/archive');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>1);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>2);
-- 備份歸檔日誌所在的路徑
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/flash_recovery_area/SAIJUAN/backupset/2010_09_16/o1_mf_annnn_TAG20100916T060546_692jswgk_.bkp',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
執行該指令碼 SQL> @/u01/bak_qilin/script/dbmsbk_partarch.sql

第六步：在源資料庫上建立控制檔案
 alter database backup controlfile to trace;
ps -ef (
avahi     2288     1  0 12:15 ?   00:00:00 avahi-daemon: running [oracle10g.local])
在源資料庫中/u01/admin/qilin/udump下從 qilin_ora_2288 .trc 中提取到以下的建立控制檔案的內容做修改後房子在目標資料庫下create_ctl.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE set DATABASE "SAIJUAN" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/qilin/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oradata/qilin/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oradata/qilin/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/qilin/system01.dbf',
  '/u01/oradata/qilin/undotbs01.dbf',
  '/u01/oradata/qilin/sysaux01.dbf',
  '/u01/oradata/qilin/users01.dbf',
  '/u01/oradata/qilin/example01.dbf',
  '/u01/oradata/qilin/undotbs02.dbf'
CHARACTER SET WE8ISO8859P1;
執行該指令碼 SQL> @/u01/bak_qilin/script/create_ctl.sql 
或者 使用dbms_backup_restore包恢復提取控制檔案dbmsbkrs_controlfile.sql
1 提取控制檔案   
declare                                                                                                 
devtype varchar2(256);                                                                                  
done boolean;                                                                                           
begin                                                                                                   
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');                                  
sys.dbms_backup_restore.restoresetdatafile;                                                             
sys.dbms_backup_restore.restorecontrolfileto(cfname=>'/u01/bak_qilin/script/controlfile.ctl');          
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/bak_qilin/rmanbak/bkctl.ctl',params=>null);
sys.dbms_backup_restore.devicedeallocate;                                                               
end;                                                                                                    
/
  如果使用backup database 這樣的命令備份資料庫，通過使用RMAN> list backup of controlfile;檢視控制檔案的備份片的具體名稱                                                                                             執行該指令碼 SQL> @/u01/bak_qilin/script/dbmsbkrs_controlfile.sql

2 把控制檔案cp到原路徑
  [oracle@oracle10g_aux script]$ pwd
/u01/bak_qilin/script
[oracle@oracle10g_aux script]$ ls
bakl0  bakl2            create_ctl.sql     dbmsbkrs_controlfile.sql  report_new.sql  report.txt
bakl1  controlfile.ctl  dbmsbkrs_arch.sql  dbmsbkrs_datafile.sql     report.sql                 
[oracle@oracle10g_aux script]$cp controlfile.ctl /u01/oradata/qilin/control01.ctl
[oracle@oracle10g_aux script]$cp controlfile.ctl /u01/oradata/qilin/control02.ctl
[oracle@oracle10g_aux script]$cp controlfile.ctl /u01/oradata/qilin/control03.ctl
 
第七步：設定歸檔日誌的檔案 SQL> set logsource '/u01/bak_qilin/archive';輸入cancel
第八步: 檢視狀態SQL> select open_mode from v$database; 然後啟動到MOUNT狀態
第九步：啟動到OPENT狀態 SQL> alter database open resetlogs; 

13、使用controlfile恢復資料庫，resetlogs方式開啟資料庫

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 65670562 generated at 04/19/2006 13:24:05 needed for thread 1
ORA-00289: suggestion : /oradata/rmanbackup/archive/hb130000_1_27.dbf
ORA-00280: change 65670562 for thread 1 is in sequence #27

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 65671036 generated at 04/19/2006 15:29:16 needed for thread 1
ORA-00289: suggestion : /oradata/rmanbackup/archive/hb130000_1_28.dbf
ORA-00280: change 65671036 for thread 1 is in sequence #28
ORA-00278: log file '/oradata/rmanbackup/archive/hb130000_1_27.dbf' no longer
needed for this recovery

ORA-00308: cannot open archived log
'/oradata/rmanbackup/archive/hb130000_1_28.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;

Database altered.
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20976446/viewspace-674319/，如需轉載，請註明出處，否則將追究法律責任。