使用dbms_backup_restore和RMAN的duplicate差不多,也是需要一个完整的备份集,但是路径可以完全不一样。
创建相应的目录
[oracle@zhong ~]$ mkdir -p $ORACLE_BASE/admin/zwc/adump
[oracle@zhong ~]$ mkdir -p $ORACLE_BASE/admin/zwc/bdump
[oracle@zhong ~]$ mkdir -p $ORACLE_BASE/admin/zwc/cdump
[oracle@zhong ~]$ mkdir -p $ORACLE_BASE/admin/zwc/udump
[oracle@zhong ~]$ mkdir -p $ORACLE_BASE/oradata/zwc
[oracle@zhong ~]$ mkdir -p /arch/
创建pfile
[oracle@zhong oradata]$ cp $ORACLE_HOME/dbs/initbackupdb.ora $ORACLE_HOME/dbs/initzwc.ora
[oracle@zhong ~]$ cat /u01/app/oracle/product/10.2.0/db_1/dbs/initzwc.ora
zwc.__db_cache_size=184549376
zwc.__java_pool_size=4194304
zwc.__large_pool_size=4194304
zwc.__shared_pool_size=88080384
zwc.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/zwc/adump'
*.background_dump_dest='/u01/app/oracle/admin/zwc/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/zwc/control01.ctl','/u01/app/oracle/oradata/zwc/control02.ctl','/u01/app/oracle/oradata/zwc/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/zwc/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zwc'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zwcXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/zwc/udump'
[oracle@zhong ~]$
创建口令文件
[oracle@zhong ~]$ orapwd file=orapwzwc password=oracle entries=5
将zwc实例启动到nomount状态
[oracle@zhong ~]$ export ORACLE_SID=zwc
[oracle@zhong ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 28 16:32:33 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS@zwc:~>startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
SYS@zwc:~>
编写dbms_backup_restore数据文件还原脚本
SYS@backupdb:~>select name,file# from v$datafile;
NAME FILE#
-------------------------------------------------- ----------
/u01/app/oracle/oradata/backupdb/system01.dbf 1
/u01/app/oracle/oradata/backupdb/undotbs01.dbf 2
/u01/app/oracle/oradata/backupdb/sysaux01.dbf 3
/u01/app/oracle/oradata/backupdb/users01.dbf 4
/u01/app/oracle/oradata/backupdb/example01.dbf 5
/u01/app/oracle/oradata/backupdb/catalog01.dbf 6
6 rows selected.
dbmsbkrs_datafile.sql
declare
devtype varchar2(300);
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/app/oracle/oradata/zwc/system.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u01/app/oracle/oradata/zwc/undotbs1.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u01/app/oracle/oradata/zwc/sysaux.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u01/app/oracle/oradata/zwc/users.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u01/app/oracle/oradata/zwc/example.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/u01/app/oracle/oradata/zwc/catalog.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/orabackup/inc0_BACKUPDB_02n70rvn_1_1',params=>null');
sys.dbms_backup_restore.deviceDeallocate;
end;
/
执行脚本dbmsbkrs_datafile.sql
SYS@zwc:~>@dbmsbkrs_datafile
PL/SQL procedure successfully completed.
SYS@zwc:~>
可以看到数据文件全部还原过来了
[oracle@zhong zwc]$ ll -trh
total 1.4G
-rw-r----- 1 oracle oinstall 5.1M Mar 28 18:25 users.dbf
-rw-r----- 1 oracle oinstall 41M Mar 28 18:26 undotbs1.dbf
-rw-r----- 1 oracle oinstall 101M Mar 28 18:26 example.dbf
-rw-r----- 1 oracle oinstall 251M Mar 28 18:26 sysaux.dbf
-rw-r----- 1 oracle oinstall 491M Mar 28 18:26 system.dbf
-rw-r----- 1 oracle oinstall 501M Mar 28 18:26 catalog.dbf
[oracle@zhong zwc]$
编写提取archivelog的脚本dbmsbkrs_arch.sql
declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoreSetArchivedlog(destination=>'/arch/');
sys.dbms_backup_restore.restoreArchivedlogRange;
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/orabackup/inc0_BACKUPDB_04n70s1g_1_1',params=>null);
sys.dbms_backup_restore.deviceDeallocate;
end;
/
执行dbmsbkrs_arch.sql
SYS@zwc:~>@dbmsbkrs_arch;
PL/SQL procedure successfully completed.
SYS@zwc:~>
可以看到archivelog被提取出来
[oracle@zhong zwc]$ ll /arch/
total 4
-rw-r----- 1 oracle oinstall 1536 Mar 28 18:44 1_7_778974218.dbf
[oracle@zhong zwc]$
重建控制文件
CREATE CONTROLFILE REUSE SET DATABASE "ZWC" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/zwc/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/zwc/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/zwc/redo03.log' SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/zwc/system.dbf',
'/u01/app/oracle/oradata/zwc/undotbs1.dbf',
'/u01/app/oracle/oradata/zwc/sysaux.dbf',
'/u01/app/oracle/oradata/zwc/users.dbf',
'/u01/app/oracle/oradata/zwc/example.dbf',
'/u01/app/oracle/oradata/zwc/catalog.dbf'
CHARACTER SET ZHS16GBK
;
[oracle@zhong ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 28 18:56:52 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS@zwc:~>@create_ctl
Control file created.
SYS@zwc:~>
应用archivelog
SYS@zwc:~>select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SYS@zwc:~>set logsource '/arch';
SYS@zwc:~>alter database open resetlogs;
Database altered.
SYS@zwc:~>
[oracle@zhong zwc]$ ll -trh
total 1.6G
-rw-r----- 1 oracle oinstall 5.1M Mar 28 18:59 users.dbf
-rw-r----- 1 oracle oinstall 41M Mar 28 18:59 undotbs1.dbf
-rw-r----- 1 oracle oinstall 491M Mar 28 18:59 system.dbf
-rw-r----- 1 oracle oinstall 251M Mar 28 18:59 sysaux.dbf
-rw-r----- 1 oracle oinstall 51M Mar 28 18:59 redo02.log
-rw-r----- 1 oracle oinstall 51M Mar 28 18:59 redo01.log
-rw-r----- 1 oracle oinstall 101M Mar 28 18:59 example.dbf
-rw-r----- 1 oracle oinstall 501M Mar 28 18:59 catalog.dbf
-rw-r----- 1 oracle oinstall 51M Mar 28 19:01 redo03.log
-rw-r----- 1 oracle oinstall 7.1M Mar 28 19:02 control03.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 28 19:02 control02.ctl
-rw-r----- 1 oracle oinstall 7.1M Mar 28 19:02 control01.ctl
[oracle@zhong zwc]$
至此使用dbms_backup_restore异地恢复的整个过程结束,验证数据库
SYS@zwc:~>set logsource '/arch';
SYS@zwc:~>alter database open resetlogs;
Database altered.
SYS@zwc:~>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@zhong ~]$ sqlplus hr/hr@zwc
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 28 19:01:14 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
HR@zwc:~>select last_name from employees where rownum=1;
LAST_NAME
-------------------------
Abel
HR@zwc:~>conn / as sysdba
Connected.
SYS@zwc:~>select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/zwc/catalog.dbf
/u01/app/oracle/oradata/zwc/example.dbf
/u01/app/oracle/oradata/zwc/users.dbf
/u01/app/oracle/oradata/zwc/sysaux.dbf
/u01/app/oracle/oradata/zwc/undotbs1.dbf
/u01/app/oracle/oradata/zwc/system.dbf
6 rows selected.
SYS@zwc:~>