Oracle 11gR2 使用 RMAN duplicate from active database 复制数据库

本文链接：https://blog.youkuaiyun.com/fffygapl/article/details/8593547

1、概要

在Oracle 10g下可以使用RMAN duplicate 命令创建一个具有不同DBID 的复制数据库。而Oracle 11G_R2 RMAN 的duplicate 有2种方法实现：

1），Activedatabase duplication

2），Backup-basedduplication

Activedatabase duplication 是通过网络直接copy target 库到auxiliary库，并生成auxiliary数据库。就好像使用数据泵的network_link参数一样，通过网络传输数据，而不需要保存到存储设备上面，类似点对点的数据复制。它的优点是：

1），不需要备份到磁盘从而不占用存储空间。

2），通过网络直接复制生成auxiliary库，速度快。

3），对于T级别的数据库效率更高。

2、Active Database Duplicate的步骤如下：

2.1 创建Auxiliary 库的InitializationParameter：

如果使用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.2 在Auxiliary库创建PasswordFile 文件

对于Backup-basedduplication，PasswordFile 不是必须的，但是对于ActiveDatabase Duplication，Password File是必须的。因为Active DatabaseDuplication 使用相同的SYSDBA密码直接连接到auxiliary库。所以，确保target和Auxiliary库的SYSDBA密码一样很重要。

当然，我们也可以在duplicate命令中加上PASSWORDFILE 选项（也是默认值），这样RMAN 在copy 的时候也会从target库把密码文件copy过来，如果auxiliary库上已经存在了Passwordfile，那么该操作会重写那个文件。

如：

RMAN>DUPLICATE TARGET DATABASE TO ora11g

2>FROM ACTIVE DATABASE

3>NOFILENAMECHECK

4>PASSWORD FILE

5>SPFILE;

2.3 如果是windows 平台，还需要创建Databaseservice：

set ORACLE_SID=ora11g

set ORACLE_HOME=E:/oracle/product/11.1.0/db_1

oradim-NEW -SID ora11g

2.4 配置oracle net，修改listener.ora和 tnsnames.ora 文件：

注意：一定需要使用静态监听注册，不然会“ORA-12528”错误。

在Target库和Auxiliary分别都要修改。当然也可以使用netca|netmgr命令配置。

2.5 创建的pfile文件，将Auxiliary启动到nomout 状态，执行”createspfile from pfile;” ,重新启动到nomount状态，

2.6 进行Active Database duplicate。

[oracle@dba2~]$

rman target sys/oracle@ora11g_dba1 auxiliary sys/oracle@ora11g_dba2

RMAN>

DUPLICATE TARGET DATABASE TO ora11g FROM ACTIVE DATABASENOFILENAMECHECK PASSWORD FILE SPFILE;

注意以下几点：

1，一定需要使用静态监听注册，不然会“ORA-12528”错误。

2，确保target和Auxiliary库的SYSDBA密码一样很重要。

3，target 和Auxiliary库的目录一致，否则Auxiliary需要用以下两个参数启库

DB_FILE_NAME_CONVERT\LOG_FILE_NAME_CONVERT

3、示例如下：

3.1 环境描述

TargetDB：

RHEL5U7+ORACLE11G_R2(11.2.0.1.0)

IP:192.168.2.111/24

Hostname:dba1.test.com

SID：ora11g

AuxiliaryDB：

RHEL5U7+ORACLE11G_R2(11.2.0.1.0)

IP：192.168.2.112/24

Hostname:dba2.test.com

SID:ora11g

这个实验数据库和日志目录相同，否则需要使用db_file_name_convert和log_file_name_convert参数。

3.2 在Auxiliary 创建pfile 参数文件：

[oracle@dba2 dbs]$ cat initora11g.ora

*.DB_NAME=ora11g

只有一个参数：DB_NAME

3.3 在Target和Auxiliary库上创建口令文件

[oracle@dba1dbs]$ orapwd file=?/dbs/orapwora11g password=oracle force=y

[oracle@dba2dbs]$ orapwd file=?/dbs/orapwora11g password=oracle

3.4 在Auxiliary库创建相应目录结构：

[oracle@dba2dbs]$ mkdir -p /oradata/ora11g/

[oracle@dba2dbs]$ mkdir -p /orachivelog

3.5 启动Auxiliary 到nomout 状态：

[oracle@dba2dbs]$ sqlplus / as sysdba;

SYS@ora11g> startup nomount pfile=?/dbs/initora11g.ora

ORACLEinstance started.

TotalSystem Global Area 368263168 bytes

FixedSize 1336596 bytes

VariableSize 281021164 bytes

DatabaseBuffers 79691776 bytes

RedoBuffers 6213632 bytes

3.6 配置Target 和Auxiliary Oracle Net（Listener.ora and tnsnames.ora）并启动监听：

注意：一定需要使用静态监听注册，不然会“ORA-12528”错误。

在Target库和Auxiliary分别都要修改。当然也可以使用netca|netmgr命令配置。

Target库

Listener.ora

[oracle@dba1 admin]$ cat listener.ora

#listener.ora Network Configuration File: /u01/oracle/product/11.2.0/network/admin/listener.ora

#Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/oracle/product/11.2.0)
(SID_NAME = ora11g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.111)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/oracle

Tnsname.ora

[oracle@dba1admin]$ cat tnsnames.ora

#tnsnames.ora Network Configuration File: /u01/oracle/product/11.2.0/network/admin/tnsnames.ora

#Generated by Oracle configuration tools.

ORA11G_DBA1=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.111)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

ORA11G_DBA2=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.112)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora11g)

)

Auxiliary库

Listener.ora

[oracle@dba2admin]$ cat listener.ora

#listener.ora Network Configuration File:/u01/oracle/product/11.2.0/network/admin/listener.ora

#Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/oracle/product/11.2.0)
(SID_NAME = ora11g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.112)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/oracle

Tnsname.ora

[oracle@dba2admin]$ cat tnsnames.ora

#tnsnames.ora Network Configuration File:/u01/oracle/product/11.2.0/network/admin/tnsnames.ora

#Generated by Oracle configuration tools.

ORA11G_DBA2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g)
)
)

ORA11G_DBA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.111)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

[oracle@dba2 admin]$ tnsping ORA11G_DBA1
[oracle@dba2 admin]$ tnsping ORA11G_DBA2

3.7 开始RMAN duplicatefrom active database：

如果目录不同，在pfile里加如下2个参数进行转换：

db_file_name_convert

log_file_name_convert.

[oracle@dba2~]$

rman target sys/oracle@ora11g_dba1 auxiliary sys/oracle@ora11g_dba2

RecoveryManager: Release 11.2.0.1.0 - Production on Tue Feb 19 16:04:55 2013

connectedto target database: ORA11G (DBID=4173278619)

connectedto auxiliary database: ORA11G (not mounted)

RMAN>

DUPLICATE TARGET DATABASE TO ora11g FROM ACTIVE DATABASENOFILENAMECHECK PASSWORD FILE SPFILE;

StartingDuplicate Db at 19-FEB-13

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA_AUX_DISK_1

channelORA_AUX_DISK_1: SID=20 device type=DISK

contentsof Memory Script:

{

backup as copy reuse

targetfile '/u01/oracle/product/11.2.0/dbs/orapwora11g' auxiliary format

'/u01/oracle/product/11.2.0/dbs/orapwora11g' targetfile

'/u01/oracle/product/11.2.0/dbs/spfileora11g.ora'auxiliary format

'/u01/oracle/product/11.2.0/dbs/spfileora11g.ora' ;

sql clone "alter system set spfile=''/u01/oracle/product/11.2.0/dbs/spfileora11g.ora''";

}

executingMemory Script

Startingbackup at 19-FEB-13

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=36 device type=DISK

Finishedbackup at 19-FEB-13

sqlstatement: alter system set spfile=''/u01/oracle/product/11.2.0/dbs/spfileora11g.ora''

contentsof Memory Script:

{

sql clone "alter system set db_name =

''ORA11G'' comment=

''duplicate'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executingMemory Script

sqlstatement: alter system set db_name= ''ORA11G'' comment= ''duplicate''scope=spfile

Oracleinstance shut down

connectedto auxiliary database (not started)

Oracleinstance started

TotalSystem Global Area 368263168 bytes

FixedSize 1336596 bytes

VariableSize 281021164 bytes

DatabaseBuffers 79691776 bytes

RedoBuffers 6213632 bytes

contentsof Memory Script:

{

sql clone "alter system set db_name =

''ORA11G'' comment=

''Modified by RMAN duplicate''scope=spfile";

sql clone "alter system set db_unique_name =

''ORA11G'' comment=

''Modified by RMAN duplicate''scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliaryformat '/oradata/ora11g/control01.ctl';

restore clone controlfile to '/u01/oracle/flash_recovery_area/ora11g/control02.ctl'from

'/oradata/ora11g/control01.ctl';

alter clone database mount;

}

executingMemory Script

sqlstatement: alter system set db_name= ''ORA11G'' comment= ''Modified by RMANduplicate'' scope=spfile

sqlstatement: alter system set db_unique_name = ''ORA11G''comment= ''Modified by RMAN duplicate'' scope=spfile

Oracleinstance shut down

Oracleinstance started

TotalSystem Global Area 368263168 bytes

FixedSize 1336596 bytes

VariableSize 281021164 bytes

DatabaseBuffers 79691776 bytes

RedoBuffers 6213632 bytes

Startingbackup at 19-FEB-13

usingchannel ORA_DISK_1

channelORA_DISK_1: starting datafile copy

copyingcurrent control file

outputfile name=/u01/oracle/product/11.2.0/dbs/snapcf_ora11g.f tag=TAG20130219T160747RECID=2 STAMP=807811668

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finishedbackup at 19-FEB-13

Startingrestore at 19-FEB-13

allocatedchannel: ORA_AUX_DISK_1

channelORA_AUX_DISK_1: SID=19 device type=DISK

channelORA_AUX_DISK_1: copied control file copy

Finishedrestore at 19-FEB-13

databasemounted

RMAN-05538:WARNING: implicitly using DB_FILE_NAME_CONVERT

contentsof Memory Script:

{

set newname for datafile 1 to

"/oradata/ora11g/system01.dbf";

set newname for datafile 2 to

"/oradata/ora11g/sysaux01.dbf";

set newname for datafile 3 to

"/oradata/ora11g/undotbs01.dbf";

set newname for datafile 4 to

"/oradata/ora11g/users01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/oradata/ora11g/system01.dbf" datafile

2 auxiliary format

"/oradata/ora11g/sysaux01.dbf" datafile

3 auxiliary format

"/oradata/ora11g/undotbs01.dbf" datafile

4 auxiliary format

"/oradata/ora11g/users01.dbf" ;

sql 'alter system archive log current';

}

executingMemory Script

executingcommand: SET NEWNAME

Startingbackup at 19-FEB-13

usingchannel ORA_DISK_1

channelORA_DISK_1: starting datafile copy

inputdatafile file number=00001 name=/oradata/ora11g/system01.dbf

outputfile name=/oradata/ora11g/system01.dbf tag=TAG20130219T160803

channelORA_DISK_1: datafile copy complete, elapsed time: 00:01:36

channelORA_DISK_1: starting datafile copy

inputdatafile file number=00002 name=/oradata/ora11g/sysaux01.dbf

outputfile name=/oradata/ora11g/sysaux01.dbf tag=TAG20130219T160803

channelORA_DISK_1: datafile copy complete, elapsed time: 00:01:26

channelORA_DISK_1: starting datafile copy

inputdatafile file number=00003 name=/oradata/ora11g/undotbs01.dbf

outputfile name=/oradata/ora11g/undotbs01.dbf tag=TAG20130219T160803

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channelORA_DISK_1: starting datafile copy

inputdatafile file number=00004 name=/oradata/ora11g/users01.dbf

outputfile name=/oradata/ora11g/users01.dbf tag=TAG20130219T160803

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finishedbackup at 19-FEB-13

sqlstatement: alter system archive log current

contentsof Memory Script:

{

backup as copy reuse

archivelog like "/orachivelog/1_10_777078497.dbf"auxiliary format

"/orachivelog/1_10_777078497.dbf" ;

catalog clone archivelog "/orachivelog/1_10_777078497.dbf";

switch clone datafile all;

}

executingMemory Script

Startingbackup at 19-FEB-13

usingchannel ORA_DISK_1

channelORA_DISK_1: starting archived log copy

inputarchived log thread=1 sequence=10 RECID=6 STAMP=807811887

outputfile name=/orachivelog/1_10_777078497.dbf RECID=0 STAMP=0

channelORA_DISK_1: archived log copy complete, elapsed time: 00:00:03

Finishedbackup at 19-FEB-13

catalogedarchived log

archivedlog file name=/orachivelog/1_10_777078497.dbf RECID=6 STAMP=807811893

datafile1 switched to datafile copy

inputdatafile copy RECID=2 STAMP=807811893 file name=/oradata/ora11g/system01.dbf

datafile2 switched to datafile copy

inputdatafile copy RECID=3 STAMP=807811893 file name=/oradata/ora11g/sysaux01.dbf

datafile3 switched to datafile copy

inputdatafile copy RECID=4 STAMP=807811893 file name=/oradata/ora11g/undotbs01.dbf

datafile4 switched to datafile copy

inputdatafile copy RECID=5 STAMP=807811893 file name=/oradata/ora11g/users01.dbf

contentsof Memory Script:

{

set until scn 868868;

recover

clone database

delete archivelog

;

}

executingMemory Script

executingcommand: SET until clause

Startingrecover at 19-FEB-13

usingchannel ORA_AUX_DISK_1

startingmedia recovery

archivedlog for thread 1 with sequence 10 is already on disk as file/orachivelog/1_10_777078497.dbf

archivedlog file name=/orachivelog/1_10_777078497.dbf thread=1 sequence=10

mediarecovery complete, elapsed time: 00:00:01

Finishedrecover at 19-FEB-13

contentsof Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''ORA11G'' 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;

}

executingMemory Script

databasedismounted

Oracleinstance shut down

connectedto auxiliary database (not started)

Oracleinstance started

TotalSystem Global Area 368263168 bytes

FixedSize 1336596 bytes

VariableSize 281021164 bytes

DatabaseBuffers 79691776 bytes

RedoBuffers 6213632 bytes

sqlstatement: alter system set db_name= ''ORA11G'' comment= ''Reset tooriginal value by RMAN'' scope=spfile

sqlstatement: alter system reset db_unique_name scope=spfile

Oracleinstance shut down

connectedto auxiliary database (not started)

Oracleinstance started

TotalSystem Global Area 368263168 bytes

FixedSize 1336596 bytes

VariableSize 281021164 bytes

DatabaseBuffers 79691776 bytes

RedoBuffers 6213632 bytes

--创建控制文件

sqlstatement: CREATE CONTROLFILE REUSE SET DATABASE "ORA11G" RESETLOGSARCHIVELOG

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

'/oradata/ora11g/system01.dbf'

CHARACTER SET AL32UTF8

contentsof Memory Script:

{

set newname for tempfile 1 to

"/oradata/ora11g/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/oradata/ora11g/sysaux01.dbf",

"/oradata/ora11g/undotbs01.dbf",

"/oradata/ora11g/users01.dbf";

switch clone datafile all;

}

executingMemory Script

executingcommand: SET NEWNAME

renamedtempfile 1 to /oradata/ora11g/temp01.dbf in control file

catalogeddatafile copy

datafilecopy file name=/oradata/ora11g/sysaux01.dbf RECID=1 STAMP=807811953

catalogeddatafile copy

datafilecopy file name=/oradata/ora11g/undotbs01.dbf RECID=2 STAMP=807811953

catalogeddatafile copy

datafilecopy file name=/oradata/ora11g/users01.dbf RECID=3 STAMP=807811953

datafile2 switched to datafile copy

inputdatafile copy RECID=1 STAMP=807811953 file name=/oradata/ora11g/sysaux01.dbf

datafile3 switched to datafile copy

inputdatafile copy RECID=2 STAMP=807811953 file name=/oradata/ora11g/undotbs01.dbf

datafile4 switched to datafile copy

inputdatafile copy RECID=3 STAMP=807811953 file name=/oradata/ora11g/users01.dbf

contentsof Memory Script:

{

Alter clone database open resetlogs;

}

executingMemory Script

databaseopened

--到这里完成

Finished Duplicate Db at 19-FEB-13

RMAN>

Target库

SYS@ora11g> select name,dbid from v$database;

NAME DBID

------------------------------------------------------------

ORA11G 4173278619

Auxiliary库

SYS@ora11g>select name,dbid from v$database;

NAME DBID

------------------------------------------------------------

ORA11G 4204015026

参考来自：http://blog.youkuaiyun.com/tianlesoftware/article/category/697504

=======================================================================

QQ: 164798858@qq.com
Sina: weibo.com/kaijunfeng
Yahoo: fffygapl@yahoo.com.cn