RMAN duplicate之基于活动数据库，修改SID

简单介绍一下，RMAN duplicate 数据库可以使用活动的数据库，也可以使用备份，基于备份的又分为具有目标连接和没有目标连接，具体如下图所示。本列中是基于active 数据库的。基于备份的duplicate可以查看我的博客http://blog.itpub.net/27771627/viewspace-1265919/





做之前请先明确概念，目标数据库和辅助数据库，按照常规想法目标数据库是duplicate结果的数据库，而这里目标数据库是指被duplicate的数据库，这可能和oracle的官方文档翻译过来的习惯有关系。

 

目标数据库，需要被duplicate的数据库 IP为192.168.221.201

实例名为：sap

 

辅助数据库，duplicate生成的数据库192.168.221.204

实例名为：new

 

在new数据库上面设置环境变量

[oracle@sap2 sap]$ . oraenv

ORACLE_SID = [sap] ? new

ORACLE_HOME = [/home/oracle] ? /home/oracle/app/oracle/product/11.2.0/dbhome_1

The Oracle base for ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1 is /home/oracle/app/oracle

 

1、创建密码文件，在new机器上

[oracle@sap2 sap]$ cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@sap2 dbs]$ orapwd file=orapwnew password=sys entries=5;

 

2、建立网络连接，配置listener.ora文件，加粗部分为新添加部分的

SID_LIST_LISTENER

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = new)

(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = new)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)

ADR_BASE_LISTENER = /home/oracle/app/oracle

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

 

 

3、创建初始化参数文件，在201机器上使用 create pfile from spfile命令创建pfile 然后scp到new机器对应目录，修改黑体部分其他可以不变。

 

sap.__db_cache_size=314572800

sap.__java_pool_size=4194304

sap.__large_pool_size=12582912

sap.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment

sap.__pga_aggregate_target=289406976

sap.__sga_target=545259520

sap.__shared_io_pool_size=0

sap.__shared_pool_size=201326592

sap.__streams_pool_size=0

*.audit_file_dest='/home/oracle/app/oracle/admin/new/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/home/oracle/app/oracle/oradata/new/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='new'

*.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=10485760000

*.diagnostic_dest='/home/oracle/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sapXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=831520768

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

4、创建目录，如果已经存在的无需创建

mkdir -p /home/oracle/app/oracle/flash_recovery_area

mkdir -p /home/oracle/app/oracle/admin/new/adump

mkdir -p /home/oracle/app/oracle/oradata/new/

mkdir -p /home/oracle/app/oracle/flash_recovery_area/new/

 

5、启动到nomount状态

[oracle@sap2 ~]$ export ORACLE_SID=new

[oracle@sap2 ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1

SQL> startup nomount pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora'

 

 

6、将目标数据库201启动到mount 或open状态

在目标数据库上执行

connect target sys/sys@192.168.221.201:1521/sap

connect auxiliary sys/sys@192.168.221.204:1521/new

 

 

7、执行duplicate命令

RMAN> duplicate target database to "new" from active database nofilenamecheck;

Starting Duplicate Db at 06-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=129 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 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 490735688 bytes

Database Buffers 331350016 bytes

Redo Buffers 6627328 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''SAP'' comment=

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

sql clone "alter system set db_unique_name =

''NEW'' comment=

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

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '/home/oracle/app/oracle/oradata/new/control01.ctl';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''SAP'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''NEW'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 490735688 bytes

Database Buffers 331350016 bytes

Redo Buffers 6627328 bytes

Starting backup at 06-MAR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=69 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_sap.f tag=TAG20140306T001438 RECID=27 STAMP=841450478

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 06-MAR-14

database mounted

contents of Memory Script:

{

set newname for datafile 1 to

"/home/oracle/app/oracle/oradata/sap/system01.dbf";

set newname for datafile 2 to

"/home/oracle/app/oracle/oradata/sap/sysaux01.dbf";

set newname for datafile 3 to

"/home/oracle/app/oracle/oradata/sap/undotbs01.dbf";

set newname for datafile 4 to

"/home/oracle/app/oracle/oradata/sap/users01.dbf";

set newname for datafile 5 to

"/home/oracle/app/oracle/oradata/sap/example01.dbf";

set newname for datafile 6 to

"/home/oracle/app/oracle/oradata/sap/lztest.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/home/oracle/app/oracle/oradata/sap/system01.dbf" datafile

2 auxiliary format

"/home/oracle/app/oracle/oradata/sap/sysaux01.dbf" datafile

3 auxiliary format

"/home/oracle/app/oracle/oradata/sap/undotbs01.dbf" datafile

4 auxiliary format

"/home/oracle/app/oracle/oradata/sap/users01.dbf" datafile

5 auxiliary format

"/home/oracle/app/oracle/oradata/sap/example01.dbf" datafile

6 auxiliary format

"/home/oracle/app/oracle/oradata/sap/lztest.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

executing command: SET NEWNAME

Starting backup at 06-MAR-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/home/oracle/app/oracle/oradata/sap/system01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/system01.dbf tag=TAG20140306T001444

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf tag=TAG20140306T001444

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf tag=TAG20140306T001444

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/home/oracle/app/oracle/oradata/sap/example01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/example01.dbf tag=TAG20140306T001444

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

output file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf tag=TAG20140306T001444

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=/home/oracle/app/oracle/oradata/sap/users01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/users01.dbf tag=TAG20140306T001444

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 06-MAR-14

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "/home/oracle/app/oracle/flash_recovery_area/SAP/archivelog/2014_03_06/o1_mf_1_31_9kjcpb24_.arc" auxiliary format

"/home/oracle/app/oracle/flash_recovery_area/NEW/archivelog/2014_03_06/o1_mf_1_31_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 06-MAR-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=31 RECID=34 STAMP=841450571

output file name=/home/oracle/app/oracle/flash_recovery_area/NEW/archivelog/2014_03_06/o1_mf_1_31_2rp2f12b_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:04

Finished backup at 06-MAR-14

searching for all files in the recovery area

List of Files Unknown to the Database

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

File Name: /home/oracle/app/oracle/flash_recovery_area/NEW/archivelog/2014_03_06/o1_mf_1_31_2rp2f12b_.arc

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /home/oracle/app/oracle/flash_recovery_area/NEW/archivelog/2014_03_06/o1_mf_1_31_2rp2f12b_.arc

datafile 1 switched to datafile copy

input datafile copy RECID=27 STAMP=841450576 file name=/home/oracle/app/oracle/oradata/sap/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=28 STAMP=841450576 file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=29 STAMP=841450576 file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=30 STAMP=841450576 file name=/home/oracle/app/oracle/oradata/sap/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=31 STAMP=841450576 file name=/home/oracle/app/oracle/oradata/sap/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=32 STAMP=841450576 file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

contents of Memory Script:

{

set until scn 1259337;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 06-MAR-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=63 device type=DISK

starting media recovery

archived log for thread 1 with sequence 31 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/NEW/archivelog/2014_03_06/o1_mf_1_31_2rp2f12b_.arc

archived log file name=/home/oracle/app/oracle/flash_recovery_area/NEW/archivelog/2014_03_06/o1_mf_1_31_2rp2f12b_.arc thread=1 sequence=31

media recovery complete, elapsed time: 00:00:02

Finished recover at 06-MAR-14

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''NEW'' 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 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 490735688 bytes

Database Buffers 331350016 bytes

Redo Buffers 6627328 bytes

sql statement: alter system set db_name = ''NEW'' 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 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 490735688 bytes

Database Buffers 331350016 bytes

Redo Buffers 6627328 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NEW" 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

'/home/oracle/app/oracle/oradata/sap/system01.dbf'

CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

set newname for tempfile 1 to

"/home/oracle/app/oracle/oradata/sap/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/home/oracle/app/oracle/oradata/sap/sysaux01.dbf",

"/home/oracle/app/oracle/oradata/sap/undotbs01.dbf",

"/home/oracle/app/oracle/oradata/sap/users01.dbf",

"/home/oracle/app/oracle/oradata/sap/example01.dbf",

"/home/oracle/app/oracle/oradata/sap/lztest.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/app/oracle/oradata/sap/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf RECID=1 STAMP=841450594

cataloged datafile copy

datafile copy file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf RECID=2 STAMP=841450594

cataloged datafile copy

datafile copy file name=/home/oracle/app/oracle/oradata/sap/users01.dbf RECID=3 STAMP=841450595

cataloged datafile copy

datafile copy file name=/home/oracle/app/oracle/oradata/sap/example01.dbf RECID=4 STAMP=841450595

cataloged datafile copy

datafile copy file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf RECID=5 STAMP=841450595

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=841450594 file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=841450594 file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=841450595 file name=/home/oracle/app/oracle/oradata/sap/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=841450595 file name=/home/oracle/app/oracle/oradata/sap/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=841450595 file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 06-MAR-14

 

duplicate执行完成。