Our Lab Environments
Source database Duplicate database
SYSTEM: IBM AIX 5L SYSTEM: IBM AIX 5L
IP ADDRESS:172.30.2.40 IP ADDRESS:172.30.2.56
HOST NAME:node_a HOST NAME:node_b
ORACLE SID: orcl ORACLE SID: orcl
TNSNAMES:beijing TNSNAMES:shanghai
实验中复制的目录相同,如果目录结构不同,则需要在pfile里面加入db_file_name_convert和log_file_name_convert参数。
1. 在Duplicate database创建pfile参数文件
$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ cat initorcl.ora --利用vi创建initorcl.ora文件,内容很简单只需包括db_name即可
db_name='orcl'
2. 在Duplicate database创建口令文件
$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ orapwd file=?/dbs/orapworcl password=oracle entries=8
3. 在Duplicate database创建相关的目录结构
$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/admin/orcl/dpdump
$ mkdir -p /u01/app/oracle/admin/orcl/pfile
$ mkdir -p /u01/app/oracle/admin/orcl/scripts
$ mkdir -p /u01/app/oracle/diag
$ mkdir -p /u01/app/oracle/flash_recovery_area/orcl
$ mkdir -p /u01/app/oracle/oradata/orcl
4. 启动Duplicate database到nomout状态
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 11 02:57:24 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=?/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2205360 bytes
Variable Size 159385936 bytes
Database Buffers 50331648 bytes
Redo Buffers 5234688 bytes
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
5. 在Source database和Duplicate database都配置Oracle Net(Listener.ora and tnsnames.ora)
Note:以下是Duplicate database的配置,在Source database上只需要把IP改成172.30.2.40即可。
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.56)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.saci.com.cn)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
$ vi tnsnames.ora
BEIJING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.40)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.saci.com.cn)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.40)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.saci.com.cn)
)
)
SHANGHAI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.56)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.saci.com.cn)
)
)
6. 开始利用RMAN duplicate from active database
NOTE:如果Source database和Duplicate database的目录结构相同,记得加上nofilenamecheck参数,不然会报如下错误:
RMAN-05001: auxiliary file name /u01/app/oracle/oradata/orcl/users01.dbf conflicts with a file used by the target database
如果目录不同,在pfile里添加如下2个参数进行转换:
db_file_name_convert
log_file_name_convert
$ rman target sys/oracle@beijing auxiliary sys/oracle@shanghai
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 11 03:08:25 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1275959622)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database to orcl from active database nofilenamecheck;
Starting Duplicate Db at 11-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=135 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2205360 bytes
Variable Size 159385936 bytes
Database Buffers 50331648 bytes
Redo Buffers 5234688 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlorcl.dbf';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2205360 bytes
Variable Size 159385936 bytes
Database Buffers 50331648 bytes
Redo Buffers 5234688 bytes
Starting backup at 11-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20110411T031002 RECID=2 STAMP=748149003
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-APR-11
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/u01/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orcl/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orcl/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orcl/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orcl/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orcl/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orcl/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orcl/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 11-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy --开始拷贝数据文件
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20110411T031009
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20110411T031009
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/oradata/orcl/example01.dbf tag=TAG20110411T031009
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20110411T031009
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20110411T031009
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-APR-11
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_11/o1_mf_1_8_6t5fvwtm_.arc" auxiliary format
"/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_8_748178889.dbf" ;
catalog clone archivelog "/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_8_748178889.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 11-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=3 STAMP=748149036
output file name=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_8_748178889.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 11-APR-11
cataloged archived log
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_8_748178889.dbf RECID=3 STAMP=748195838
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=748195838 file name=/u01/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=748195838 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=748195838 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=748195838 file name=/u01/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=748195838 file name=/u01/app/oracle/oradata/orcl/example01.dbf
contents of Memory Script:
{
set until scn 1071335;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-APR-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_8_748178889.dbf
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_8_748178889.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-APR-11
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''ORCL'' 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 217157632 bytes
Fixed Size 2205360 bytes
Variable Size 159385936 bytes
Database Buffers 50331648 bytes
Redo Buffers 5234688 bytes
sql statement: alter system set db_name = ''ORCL'' 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 217157632 bytes
Fixed Size 2205360 bytes
Variable Size 159385936 bytes
Database Buffers 50331648 bytes
Redo Buffers 5234688 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/orcl/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/orcl/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/orcl/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orcl/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/sysaux01.dbf",
"/u01/app/oracle/oradata/orcl/undotbs01.dbf",
"/u01/app/oracle/oradata/orcl/users01.dbf",
"/u01/app/oracle/oradata/orcl/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf RECID=1 STAMP=748195856
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf RECID=2 STAMP=748195856
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/users01.dbf RECID=3 STAMP=748195856
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/example01.dbf RECID=4 STAMP=748195856
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=748195856 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=748195856 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=748195856 file name=/u01/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=748195856 file name=/u01/app/oracle/oradata/orcl/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 11-APR-11
RMAN>
查询dbid
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
1275976591 ORCL
Source database:
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
1275959622 ORCL
发现通过duplicate创建的数据库会自动创建新的dbid。
总结:
在Oracle 10g下,我们可以利用RMAN duplicate 命令创建一个具有不同DBID 的复制库。 到了Oracle 11gR2,RMAN 的duplicate 有2种方法实现:
1. Active database duplication
2. Backup-based duplication
Active database duplication 通过网络,直接copy target 库到auxiliary 库,然后创建复制库。这种方法就不需要先用RMAN 备份数据库,然后将备份文件发送到auxiliary端。
这个功能的作用是非常大的。尤其是对T级别的库。因为对这样的库进行备份,然后将备份集发送到备库,在进行duplicate 的代价是非常大的。一备份要占用时间,二要占用备份空间,三在网络传送的时候,还需要占用带宽和时间。所以Active database duplicate 很好的解决了以上的问题。它对大库的迁移非常有用。
如果是从RAC duplicate 到单实例,操作是一样的。如果是从单实例duplicate 到RAC。那么先duplicate 到单实例。然后将单实例转换成RAC。
11gR2下,Active Database Duplicate的步骤如下:
1. 创建Auxiliary 库的Initialization Parameter:
如果使用spfile,那么在pfile文件里只需要设置一个DB_NAME参数,其他参数会在duplicate 命令中自己设置。
如果使用pfile,那么需要设置如下参数:
DB_NAME
CONTROL_FILES
DB_BLOCK_SIZE
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
DB_RECOVERY_FILE_DEST
2. 在Auxiliary库创建Password File 文件
对于Backup-based duplication,Password File 不是必须的,但是对于Active Database Duplication,Password File是必须的。因为Active Database Duplication 使用相同的SYSDBA 密码直接连接到auxiliary 库。所以,确保target 和Auxiliary库的SYSDBA 密码一样很重要。
当然,我们也可以在duplicate 命令中加上PASSWORD FILE 选项(也是默认值),这样RMAN 在copy 的时候也会从target库把密码文件copy过来,如果auxiliary库上已经存在了Password file,那么该操作会重写那个文件。
如:
RMAN> DUPLICATE TARGET DATABASE TO ORCL
2> FROM ACTIVE DATABASE
3> NOFILENAMECHECK
4> PASSWORD FILE
5> SPFILE;
3. 如果是windows 平台,还需要创建Database service
% set ORACLE_SID=ORCL
% set ORACLE_HOME=D:/oracle/product/11.2.0/db_1
% oradim -NEW -SID ORCL
4. 配置oracle net,修改listener.ora和tnsnames.ora 文件
在Target 库和Auxiliary 都要修改,这个也可以使用netca 和netmgr命令配置。
listener.ora
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.40)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
tnsname.ora
$ vi tnsnames.ora
BEIJING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.40)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.saci.com.cn)
)
)
SHANGHAI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.2.56)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
5. 用第一步创建的pfile文件,将Auxiliary库启动到nomout 状态。然后进行Active Database duplicate。