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.