对于基于生产环境下的数据库的版本升级或者测试新的应用程序的性能及其影响,备份恢复等等,我们可以采取从生产环境以克隆的方式将其克隆到本地而不影响生产数据库的正常使用。实现这个功能我们可以借助rman duplicate方式以及其简单的方式来完成。duplicate方式不同于OS级别的备份,它会为辅助数据库(克隆出来的数据库)生成一个新的dbid,而能够同时将目标数据库(原数据库)与辅助数据库注册到同一个恢复目录。本文描述了使用rman duplicate实现异机数据库克隆。
1、RMAN支持的duplicate类型
支持基于备份的duplicate方式,其中该方式又分为连接到target DB与不连接target DB,不连接target DB又分为连接到catalog与不连接catalog
支持基于活动数据库的duplicate方式。更多关于duplicate的描述及duplicate步骤,请参考:基于RMAN的同机数据库克隆
这两种方式可以使用下面的图示简要描述。
From Active DB
--------------
| With recovery catalog connection
Duplicate | |--------------------------------
------------| Without target conenction |
| |----------------------------|
| | |
--------------| |-------------
From Backups | Without recovery catalog connection
|-----------------------------
With target connection
2、RMAN异机恢复示意图
下面的图示是没有连接到target DB,也没有连接到recover catalog方式。基于这种方式实现duplicate,应指定备份文件所在的位置。
本文下面所演示的试验也是基于该方式,在演示的过程中,磁盘路径,目录,数据文件使用了相同的位置与文件名。
对于不同位置的duplicate情形,需要对文件位置进行转换,可参考: RMAN 数据库克隆文件位置转换方法
3、实战RMAN异机克隆
-
-
-
-
-
-
-
-
-
[oracle@linux3 database]$ cat /etc/issue
-
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
-
Kernel \r on an \m
-
-
SQL> select * from v$version where rownum<2;
-
-
BANNER
-
-
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
-
-
-
a、备份及ftp目标数据库
-
-
SQL> select * from t;
-
-
NAME ACTION
-
-
Robinson Transfer DB
-
Jackson Transfer DB by rman
-
-
-
$ export ORACLE_SID=sybo3
-
$ rman target /
-
RMAN> backup database include current controlfile plus archivelog delete input;
-
$ scp -r fra 192.168.7.26:/u01/database/sybo3/
-
-
-
-
-
-
$ scp $ORACLE_HOME/dbs/initsybo3.ora 192.168.7.26:$ORACLE_HOME/dbs
-
-
-
b、创建相应的dump文件夹
-
[oracle@linux4 database]$ more sybo3.sh
-
#!/bin/sh
-
mkdir -p /u01/database
-
mkdir -p /u01/database/sybo3/adump
-
mkdir -p /u01/database/sybo3/controlf
-
mkdir -p /u01/database/sybo3/fra
-
mkdir -p /u01/database/sybo3/oradata
-
mkdir -p /u01/database/sybo3/redo
-
mkdir -p /u01/database/sybo3/dpdump
-
mkdir -p /u01/database/sybo3/pfile
-
[oracle@linux4 database]$ ./sybo3.sh
-
-
-
c、配置辅助实例参数文件
-
-
[oracle@linux4 ~]$ more /u01/oracle/db_1/dbs/initsybo3.ora
-
sybo3.__db_cache_size=113246208
-
sybo3.__java_pool_size=4194304
-
sybo3.__large_pool_size=4194304
-
sybo3.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
-
sybo3.__pga_aggregate_target=150994944
-
sybo3.__sga_target=226492416
-
sybo3.__shared_io_pool_size=0
-
sybo3.__shared_pool_size=96468992
-
sybo3.__streams_pool_size=0
-
*.audit_file_dest='/u01/database/sybo3/adump/'
-
*.audit_trail='db'
-
*.compatible='11.2.0.0.0'
-
*.control_files='/u01/database/sybo3/controlf/control01.ctl','/u01/database/sybo3/controlf/control02.ctl'
-
*.db_block_size=8192
-
*.db_domain='orasrv.com'
-
*.db_name='sybo3'
-
*.db_recovery_file_dest='/u01/database/sybo3/fra'
-
*.db_recovery_file_dest_size=4039114752
-
*.dg_broker_config_file1='/u01/database/sybo3/db_broker/dr1sybo3.dat'
-
*.dg_broker_config_file2='/u01/database/sybo3/db_broker/dr2sybo3.dat'
-
*.dg_broker_start=FALSE
-
*.diagnostic_dest='/u01/database/sybo3'
-
*.log_archive_dest_1=''
-
*.memory_target=374341632
-
*.open_cursors=300
-
*.processes=150
-
*.remote_login_passwordfile='EXCLUSIVE'
-
*.undo_tablespace='UNDOTBS1'
-
-
d、生成辅助实例密码文件
-
-
$ orapwd file=/u01/oracle/db_1/dbs/orapwsybo3 password=oracle entries=10
-
-
-
e、实施数据库克隆
-
-
[oracle@linux4 ~]$ export ORACLE_SID=sybo3
-
[oracle@linux4 ~]$ sqlplus / as sysdba
-
SQL> startup nomount;
-
[oracle@linux4 ~]$ rman auxiliary /
-
-
connected to auxiliary database: SYBO3 (not mounted)
-
-
RMAN> duplicate target database to sybo3 spfile
-
2> backup location '/u01/database/sybo3/fra/SYBO3'
-
3> nofilenamecheck;
-
-
Starting Duplicate Db at 31-JUL-13
-
-
contents of Memory Script:
-
{
-
restore clone spfile to '/u01/oracle/db_1/dbs/spfilesybo3.ora' from
-
'/u01/database/sybo3/fra/SYBO3/autobackup/2013_07_31/o1_mf_s_822220850_8zjy9lp8_.bkp';
-
sql clone "alter system set spfile= ''/u01/oracle/db_1/dbs/spfilesybo3.ora''";
-
}
-
executing Memory Script
-
-
Starting restore at 31-JUL-13
-
allocated channel: ORA_AUX_DISK_1
-
channel ORA_AUX_DISK_1: SID=134 device type=DISK
-
-
channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /u01/database/sybo3/fra/SYBO3/autobackup/2013_07_31/o1_mf_s_822220850_8zjy9lp8_.bkp
-
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
-
Finished restore at 31-JUL-13
-
-
sql statement: alter system set spfile= ''/u01/oracle/db_1/dbs/spfilesybo3.ora''
-
-
contents of Memory Script:
-
{
-
sql clone "alter system set db_name =
-
''SYBO3'' comment=
-
''duplicate'' scope=spfile";
-
shutdown clone immediate;
-
startup clone nomount;
-
}
-
executing Memory Script
-
-
sql statement: alter system set db_name = ''SYBO3'' comment= ''duplicate'' scope=spfile
-
-
Oracle instance shut down
-
-
connected to auxiliary database (not started)
-
Oracle instance started
-
-
Total System Global Area 376635392 bytes
-
-
Fixed Size 1336652 bytes
-
Variable Size 285215412 bytes
-
Database Buffers 83886080 bytes
-
Redo Buffers 6197248 bytes
-
-
contents of Memory Script:
-
{
-
sql clone "alter system set db_name =
-
''SYBO3'' comment=
-
''Modified by RMAN duplicate'' scope=spfile";
-
sql clone "alter system set db_unique_name =
-
''SYBO3'' comment=
-
''Modified by RMAN duplicate'' scope=spfile";
-
shutdown clone immediate;
-
startup clone force nomount
-
restore clone primary controlfile from '/u01/database/sybo3/fra/SYBO3/autobackup/2013_07_31/o1_mf_s_822220850_8zjy9lp8_.bkp';
-
alter clone database mount;
-
}
-
executing Memory Script
-
-
sql statement: alter system set db_name = ''SYBO3'' comment= ''Modified by RMAN duplicate'' scope=spfile
-
-
sql statement: alter system set db_unique_name = ''SYBO3'' comment= ''Modified by RMAN duplicate'' scope=spfile
-
-
Oracle instance shut down
-
-
Oracle instance started
-
-
Total System Global Area 376635392 bytes
-
-
Fixed Size 1336652 bytes
-
Variable Size 285215412 bytes
-
Database Buffers 83886080 bytes
-
Redo Buffers 6197248 bytes
-
-
Starting restore at 31-JUL-13
-
allocated channel: ORA_AUX_DISK_1
-
channel ORA_AUX_DISK_1: SID=133 device type=DISK
-
-
channel ORA_AUX_DISK_1: restoring control file
-
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
-
output file name=/u01/database/sybo3/controlf/control01.ctl
-
output file name=/u01/database/sybo3/controlf/control02.ctl
-
Finished restore at 31-JUL-13
-
-
database mounted
-
released channel: ORA_AUX_DISK_1
-
allocated channel: ORA_AUX_DISK_1
-
channel ORA_AUX_DISK_1: SID=133 device type=DISK
-
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
-
-
contents of Memory Script:
-
{
-
set until scn 1177881;
-
set newname for datafile 1 to
-
"/u01/database/sybo3/oradata/system01.dbf";
-
set newname for datafile 2 to
-
"/u01/database/sybo3/oradata/sysaux01.dbf";
-
set newname for datafile 3 to
-
"/u01/database/sybo3/oradata/undotbs01.dbf";
-
set newname for datafile 4 to
-
"/u01/database/sybo3/oradata/users01.dbf";
-
set newname for datafile 5 to
-
"/u01/database/sybo3/oradata/example01.dbf";
-
restore
-
clone database
-
;
-
}
-
executing Memory Script
-
-
executing command: SET until clause
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
Starting restore at 31-JUL-13
-
using channel ORA_AUX_DISK_1
-
-
channel ORA_AUX_DISK_1: starting datafile backup set restore
-
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
-
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/database/sybo3/oradata/system01.dbf
-
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/database/sybo3/oradata/sysaux01.dbf
-
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/database/sybo3/oradata/undotbs01.dbf
-
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/database/sybo3/oradata/users01.dbf
-
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/database/sybo3/oradata/example01.dbf
-
channel ORA_AUX_DISK_1: reading from backup piece /u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T103921_8zjy6s8p_.bkp
-
channel ORA_AUX_DISK_1: piece handle=/u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T103921_8zjy6s8p_.bkp
-
tag=TAG20130731T103921
-
channel ORA_AUX_DISK_1: restored backup piece 1
-
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
-
Finished restore at 31-JUL-13
-
-
contents of Memory Script:
-
{
-
switch clone datafile all;
-
}
-
executing Memory Script
-
-
datafile 1 switched to datafile copy
-
input datafile copy RECID=1 STAMP=822223969 file name=/u01/database/sybo3/oradata/system01.dbf
-
datafile 2 switched to datafile copy
-
input datafile copy RECID=2 STAMP=822223969 file name=/u01/database/sybo3/oradata/sysaux01.dbf
-
datafile 3 switched to datafile copy
-
input datafile copy RECID=3 STAMP=822223969 file name=/u01/database/sybo3/oradata/undotbs01.dbf
-
datafile 4 switched to datafile copy
-
input datafile copy RECID=4 STAMP=822223969 file name=/u01/database/sybo3/oradata/users01.dbf
-
datafile 5 switched to datafile copy
-
input datafile copy RECID=5 STAMP=822223969 file name=/u01/database/sybo3/oradata/example01.dbf
-
-
contents of Memory Script:
-
{
-
set until scn 1177881;
-
recover
-
clone database
-
delete archivelog
-
;
-
}
-
executing Memory Script
-
-
executing command: SET until clause
-
-
Starting recover at 31-JUL-13
-
using channel ORA_AUX_DISK_1
-
-
starting media recovery
-
-
channel ORA_AUX_DISK_1: starting archived log restore to default destination
-
channel ORA_AUX_DISK_1: restoring archived log
-
archived log thread=1 sequence=41
-
channel ORA_AUX_DISK_1: reading from backup piece /u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_annnn_TAG20130731T104049_8zjy9k8g_.bkp
-
channel ORA_AUX_DISK_1: piece handle=/u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_annnn_TAG20130731T104049_8zjy9k8g_.bkp
-
tag=TAG20130731T104049
-
channel ORA_AUX_DISK_1: restored backup piece 1
-
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
-
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_31/o1_mf_1_41_8zk1c4xq_.arc thread=1 sequence=41
-
channel clone_default: deleting archived log(s)
-
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_31/o1_mf_1_41_8zk1c4xq_.arc RECID=1 STAMP=822223972
-
media recovery complete, elapsed time: 00:00:01
-
Finished recover at 31-JUL-13
-
-
contents of Memory Script:
-
{
-
shutdown clone immediate;
-
startup clone nomount;
-
sql clone "alter system set db_name =
-
''SYBO3'' comment=
-
''Reset to original value by RMAN'' scope=spfile";
-
sql clone "alter system reset db_unique_name scope=spfile";
-
shutdown clone immediate;
-
startup clone nomount;
-
}
-
executing Memory Script
-
-
database dismounted
-
Oracle instance shut down
-
-
connected to auxiliary database (not started)
-
Oracle instance started
-
-
Total System Global Area 376635392 bytes
-
-
Fixed Size 1336652 bytes
-
Variable Size 285215412 bytes
-
Database Buffers 83886080 bytes
-
Redo Buffers 6197248 bytes
-
-
sql statement: alter system set db_name = ''SYBO3'' comment= ''Reset to original value by RMAN'' scope=spfile
-
-
sql statement: alter system reset db_unique_name scope=spfile
-
-
Oracle instance shut down
-
-
connected to auxiliary database (not started)
-
Oracle instance started
-
-
Total System Global Area 376635392 bytes
-
-
Fixed Size 1336652 bytes
-
Variable Size 285215412 bytes
-
Database Buffers 83886080 bytes
-
Redo Buffers 6197248 bytes
-
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SYBO3" RESETLOGS ARCHIVELOG
-
MAXLOGFILES 16
-
MAXLOGMEMBERS 3
-
MAXDATAFILES 100
-
MAXINSTANCES 8
-
MAXLOGHISTORY 292
-
LOGFILE
-
GROUP 1 SIZE 50 M ,
-
GROUP 2 SIZE 50 M ,
-
GROUP 3 SIZE 50 M
-
DATAFILE
-
'/u01/database/sybo3/oradata/system01.dbf'
-
CHARACTER SET AL32UTF8
-
-
-
contents of Memory Script:
-
{
-
set newname for tempfile 1 to
-
"/u01/database/sybo3/oradata/temp01.dbf";
-
switch clone tempfile all;
-
catalog clone datafilecopy "/u01/database/sybo3/oradata/sysaux01.dbf",
-
"/u01/database/sybo3/oradata/undotbs01.dbf",
-
"/u01/database/sybo3/oradata/users01.dbf",
-
"/u01/database/sybo3/oradata/example01.dbf";
-
switch clone datafile all;
-
}
-
executing Memory Script
-
-
executing command: SET NEWNAME
-
-
renamed tempfile 1 to /u01/database/sybo3/oradata/temp01.dbf in control file
-
-
cataloged datafile copy
-
datafile copy file name=/u01/database/sybo3/oradata/sysaux01.dbf RECID=1 STAMP=822223994
-
cataloged datafile copy
-
datafile copy file name=/u01/database/sybo3/oradata/undotbs01.dbf RECID=2 STAMP=822223994
-
cataloged datafile copy
-
datafile copy file name=/u01/database/sybo3/oradata/users01.dbf RECID=3 STAMP=822223994
-
cataloged datafile copy
-
datafile copy file name=/u01/database/sybo3/oradata/example01.dbf RECID=4 STAMP=822223994
-
-
datafile 2 switched to datafile copy
-
input datafile copy RECID=1 STAMP=822223994 file name=/u01/database/sybo3/oradata/sysaux01.dbf
-
datafile 3 switched to datafile copy
-
input datafile copy RECID=2 STAMP=822223994 file name=/u01/database/sybo3/oradata/undotbs01.dbf
-
datafile 4 switched to datafile copy
-
input datafile copy RECID=3 STAMP=822223994 file name=/u01/database/sybo3/oradata/users01.dbf
-
datafile 5 switched to datafile copy
-
input datafile copy RECID=4 STAMP=822223994 file name=/u01/database/sybo3/oradata/example01.dbf
-
-
contents of Memory Script:
-
{
-
Alter clone database open resetlogs;
-
}
-
executing Memory Script
-
-
database opened
-
Finished Duplicate Db at 31-JUL-13
-
-
-
-
-
-
-
f、验证结果(根据需要创建监听)
-
[oracle@linux4 ~]$ sqlplus / as sysdba
-
-
SQL> select instance_name,host_name from v$instance;
-
-
INSTANCE_NAME HOST_NAME
-
-
sybo3 linux4.orasrv.com
-
-
SQL> select * from t;
-
-
NAME ACTION
-
-
Robinson Transfer DB
-
Jackson Transfer DB by rman
4、后记
a、在异机克隆时,如果auxiliary DB使用了与target DB相同的磁盘配置,目录结构以及文件名时,必须指定NOFILENAMECHECK。
NOFILENAMECHECK可以阻止检查target DB的数据文件及联机日志文件是否处于正常使用的状态。而auxiliary DB与target DB的磁盘配置,
目录结构以及文件名任一不同时,应避免使用NOFILENAMECHECK。
b、对于没有连接到target DB或catalog的情形,应使用BACKUP LOCATION ''指定备份文件所在的位置。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/23490154/viewspace-1062383/
,如需转载,请注明出处,否则将追究法律责任。