RMAN复制数据库(八)

使用RMAN DUPLICATE DATABASE From ASM to ASM
测试环境如下:
1.使用了恢复目录数据库jy
2.目标数据库dupdb在主机jingyong1上,并且数据文件是存储在磁盘组+DG1中
3.将目标数据库dupdb复制到远程主机oracle11g上生成数据库dupcp。
4.主机oracle11g上有ASM磁盘组+DISK1
5.将数据库dupcp的数据文件存储在磁盘组+DISK1中
6.在磁盘组+DISK1中存储两份控制文件
7.在主机jingyong1上创建的备份与归档重做日志必须能被主机oracle11g访问

执行步骤如下:
1.创建辅助实例的密码文件(这里辅助实例名为dupcp)

[oracle@oracle11g dbs]$ orapwd file=/u01/app/oracle/10.2.0/db/dbs/orapwdupcp password=oracle entries=10
[oracle@oracle11g dbs]$ ls -lrt orapwdupcp
-rw-r----- 1 oracle oinstall 2560 Mar 25 22:05 orapwdupcp

2.创建辅助实例网络连接,修改监听文件,使用静态监听来监听辅助实例远程主机oracle11g

[oracle@oracle11g admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =
     (SID_NAME = dupcp)
      (ORACLE_HOME =/u01/app/oracle/10.2.0/db)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@oracle11g admin]$ vi tnsnames.ora
dupcp =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dupcp)
      (UR=A)
    )
  )

在目标主机上给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dupcp =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dupcp)
      (UR=A)
    )
  )

测试网络连接
远程主机oracle11g

[oracle@oracle11g admin]$ export ORACLE_SID=dupcp
[oracle@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 22:14:49 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dupcp as sysdba
Connected to an idle instance.

目标主机jingyong1

[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 22:16:10 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dupcp as sysdba
Connected to an idle instance.


3.创建辅助实例的参数文件

[oracle@oracle11g dbs]$ vi initdupcp.ora

db_name=dupcp
db_unique_name=dupcp
control_files=('+DISK1/control01.ctl','+DISK1/control02.ctl')
db_file_name_convert=('+DG1/','+DISK1/')
log_file_name_convert=('+DG1/','+DISK1/')
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M





[oracle@oracle11g dbs]$ export ORACLE_SID=dupcp
[oracle@oracle11g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 22:21:16 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/10.2.0/db/dbs/initdupcp.ora';

File created.

4.启动辅助实例

[oracle@oracle11g dbs]$ export ORACLE_SID=dupcp
[oracle@oracle11g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 22:22:12 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes
SQL> set long 300
SQL> set linesize 300
SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/10.2.0/db/dbs/
                                                            spfiledupcp.ora

断开连接

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误 :

executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation


5.加载或打开目标数据库

[oracle@jingyong1 u02]$ export ORACLE_SID=dupdb
[oracle@jingyong1 u02]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志,对目标数据库(被复制的数据库)进行备份(包含数据文件和归档重做日志)

[oracle@jingyong1 dbs]$ rman target sys/oracle@dupdb catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 21:47:53 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: DUPDB (DBID=600524259)
connected to recovery catalog database



RMAN> backup as backupset database plus archivelog;


Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=875310741
input archive log thread=1 sequence=2 recid=2 stamp=875310860
input archive log thread=1 sequence=3 recid=3 stamp=875310962
input archive log thread=1 sequence=4 recid=4 stamp=875311041
input archive log thread=1 sequence=5 recid=5 stamp=875311330
input archive log thread=1 sequence=6 recid=6 stamp=875311560
input archive log thread=1 sequence=7 recid=7 stamp=875312933
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_dupdb875312935_131 tag=TAG20150325T222854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DG1/system01.dbf
input datafile fno=00003 name=+DG1/sysaux01.dbf
input datafile fno=00005 name=+DG1/example01.dbf
input datafile fno=00006 name=+DG1/tspitr01.dbf
input datafile fno=00002 name=+DG1/undotbs01.dbf
input datafile fno=00004 name=+DG1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_dupdb875312941_141 tag=TAG20150325T222900 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:39
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=8 stamp=875313048
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_dupdb875313054_151 tag=TAG20150325T223053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAR-15

Starting Control File and SPFILE Autobackup at 25-MAR-15
piece handle=/u02/dupdb_c-600524259-20150325-02 comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-15

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5648    18.11M     DISK        00:00:04     25-MAR-15
        BP Key: 5649   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T222854
        Piece Name: /u02/ora_dupdb875312935_131

  List of Archived Logs in backup set 5648
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       1093435    25-MAR-15 1094797    25-MAR-15
  1    2       1094797    25-MAR-15 1094860    25-MAR-15
  1    3       1094860    25-MAR-15 1094957    25-MAR-15
  1    4       1094957    25-MAR-15 1095004    25-MAR-15
  1    5       1095004    25-MAR-15 1095738    25-MAR-15
  1    6       1095738    25-MAR-15 1098358    25-MAR-15
  1    7       1098358    25-MAR-15 1103796    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5683    Full    633.66M    DISK        00:01:31     25-MAR-15
        BP Key: 5691   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T222900
        Piece Name: /u02/ora_dupdb875312941_141
  List of Datafiles in backup set 5683
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1103818    25-MAR-15 +DG1/system01.dbf
  2       Full 1103818    25-MAR-15 +DG1/undotbs01.dbf
  3       Full 1103818    25-MAR-15 +DG1/sysaux01.dbf
  4       Full 1103818    25-MAR-15 +DG1/users01.dbf
  5       Full 1103818    25-MAR-15 +DG1/example01.dbf
  6       Full 1103818    25-MAR-15 +DG1/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5723    2.50K      DISK        00:00:01     25-MAR-15
        BP Key: 5730   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T223053
        Piece Name: /u02/ora_dupdb875313054_151

  List of Archived Logs in backup set 5723
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8       1103796    25-MAR-15 1103855    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5757    Full    7.11M      DISK        00:00:05     25-MAR-15
        BP Key: 5759   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T223057
        Piece Name: /u02/dupdb_c-600524259-20150325-02
  Control File Included: Ckp SCN: 1103879      Ckp time: 25-MAR-15
  SPFILE Included: Modification time: 25-MAR-15

将上面的备份传输到远程主机的相同目录中:

[oracle@oracle11g u02]$ scp -r oracle@192.168.56.11:/u02/ora_dupdb875312935_131 /u02
The authenticity of host '192.168.56.11 (192.168.56.11)' can't be established.
RSA key fingerprint is 8b:38:ef:bf:d4:1e:9e:2a:cb:31:67:6c:3b:83:3f:62.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.11' (RSA) to the list of known hosts.
oracle@192.168.56.11's password:
ora_dupdb875312935_131                                                                                                               100%   18MB   3.6MB/s   00:05
[oracle@oracle11g u02]$ scp -r oracle@192.168.56.11:/u02/ora_dupdb875312941_141 /u02
oracle@192.168.56.11's password:
ora_dupdb875312941_141                                                                                                               100%  634MB   6.7MB/s   01:35
[oracle@oracle11g u02]$ scp -r oracle@192.168.56.11:/u02/ora_dupdb875313054_151 /u02
oracle@192.168.56.11's password:
ora_dupdb875313054_151                                                                                                               100% 3072     3.0KB/s   00:00
[oracle@oracle11g u02]$ scp -r oracle@192.168.56.11:/u02/dupdb_c-600524259-20150325-02 /u02
oracle@192.168.56.11's password:
dupdb_c-600524259-20150325-02                                                                                                        100% 7296KB   7.1MB/s   00:01
[oracle@oracle11g u02]$

检查磁盘组+DISK1的空间使用情况,要有足够的空间存储复制数据库的所有文件

ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576      4096     4046                0            4046              0  DISK1/

7.执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动,并使用自动通道:

[oracle@jingyong1 dbs]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@jingyong1 dbs]$ rman target sys/oracle@dupdb catalog rman/rman@jy auxiliary sys/oracle@dupcp

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 22:47:55 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: DUPDB (DBID=600524259)
connected to recovery catalog database
connected to auxiliary database: DUPCP (not mounted)

RMAN> duplicate target database to dupcp;

Starting Duplicate Db at 2015-03-25 22:48:15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

contents of Memory Script:
{
   set until scn  1103855;
   set newname for datafile  1 to
 "+DISK1/system01.dbf";
   set newname for datafile  2 to
 "+DISK1/undotbs01.dbf";
   set newname for datafile  3 to
 "+DISK1/sysaux01.dbf";
   set newname for datafile  4 to
 "+DISK1/users01.dbf";
   set newname for datafile  5 to
 "+DISK1/example01.dbf";
   set newname for datafile  6 to
 "+DISK1/tspitr01.dbf";
   restore
   check readonly
   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

executing command: SET NEWNAME

Starting restore at 2015-03-25 22:48:18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DISK1/system01.dbf
restoring datafile 00002 to +DISK1/undotbs01.dbf
restoring datafile 00003 to +DISK1/sysaux01.dbf
restoring datafile 00004 to +DISK1/users01.dbf
restoring datafile 00005 to +DISK1/example01.dbf
restoring datafile 00006 to +DISK1/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_dupdb875312941_141
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_dupdb875312941_141 tag=TAG20150325T222900
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 2015-03-25 22:49:31
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPCP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '+DISK1/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '+DISK1/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '+DISK1/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DISK1/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875314184 filename=+DISK1/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875314184 filename=+DISK1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875314184 filename=+DISK1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875314184 filename=+DISK1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875314184 filename=+DISK1/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1103855;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-03-25 22:49:37
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_dupdb875313054_151
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_dupdb875313054_151 tag=TAG20150325T223053
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_8_875310182.dbf thread=1 sequence=8
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_8_875310182.dbf recid=1 stamp=875314186
media recovery complete, elapsed time: 00:00:02
Finished recover at 2015-03-25 22:49:43

contents of Memory Script:
{
   shutdown clone;
   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     167772160 bytes

Fixed Size                     1272624 bytes
Variable Size                 58721488 bytes
Database Buffers             104857600 bytes
Redo Buffers                   2920448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPCP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '+DISK1/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '+DISK1/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '+DISK1/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DISK1/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DISK1/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DISK1/undotbs01.dbf";
   catalog clone datafilecopy  "+DISK1/sysaux01.dbf";
   catalog clone datafilecopy  "+DISK1/users01.dbf";
   catalog clone datafilecopy  "+DISK1/example01.dbf";
   catalog clone datafilecopy  "+DISK1/tspitr01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +DISK1/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=+DISK1/undotbs01.dbf recid=1 stamp=875314211

cataloged datafile copy
datafile copy filename=+DISK1/sysaux01.dbf recid=2 stamp=875314211

cataloged datafile copy
datafile copy filename=+DISK1/users01.dbf recid=3 stamp=875314211

cataloged datafile copy
datafile copy filename=+DISK1/example01.dbf recid=4 stamp=875314212

cataloged datafile copy
datafile copy filename=+DISK1/tspitr01.dbf recid=5 stamp=875314212

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875314211 filename=+DISK1/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875314211 filename=+DISK1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875314211 filename=+DISK1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875314212 filename=+DISK1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875314212 filename=+DISK1/tspitr01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2015-03-25 22:50:24

RMAN复制数据库(七)

使用RMAN duplicate database from file System to ASM
测试环境如下:
1.使用了恢复目录数据库jy
2.目标数据库test在主机oracle11g上,并且数据文件是存储在文件系统中
3.将目标数据库test复制到远程主机jingyong1上生成数据库dupdb。
4.主机jingyong1上有ASM磁盘组+dg1
5.将数据库dupdb的数据文件存储在磁盘组+dg1中
6.在磁盘组+dg1中存储两份控制文件
7.在主机oracle11g上创建的备份与归档重做日志必须能被主机jingyong1访问

执行步骤如下:
1.创建辅助实例的密码文件(这里辅助实例名为dupdb)

[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdupdb password=oracle entries=10
[oracle@jingyong1 dbs]$ ls -lrt orapwdupdb
-rw-r----- 1 oracle oinstall 2560 Mar 25 20:49 orapwdupdb

2.创建辅助实例网络连接,修改监听文件,使用静态监听来监听辅助实例远程主机jingyong1

[oracle@jingyong1 admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dupdb)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dupdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dupdb)
      (UR=A)
    )
  )

在目标主机上给辅助实例增加网络服务名

[oracle@oracle11g admin]$ vi tnsnames.ora
dupdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dupdb)
      (UR=A)
    )
  )

测试网络连接
远程主机jingyong1

[oracle@jingyong1 admin]$ export ORACLE_SID=dupdb
[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 20:53:26 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dupdb as sysdba
Connected to an idle instance.

目标主机oracle11g

[oracle@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 21:00:13 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dupdb as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件

[oracle@jingyong1 dbs]$ vi initdupdb.ora

db_name=dupdb
db_unique_name=dupdb
control_files=('+DG1/control01.ctl','+DG1/control02.ctl')
db_file_name_convert=('/u01/app/oracle/oradata/test/','+DG1/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','+DG1/')
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M



[oracle@jingyong1 dbs]$ export ORACLE_SID=dupdb
[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 21:08:14 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdupdb.ora';

File created.

4.启动辅助实例

[oracle@jingyong1 dbs]$ export ORACLE_SID=dupdb
[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 21:10:09 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes


SQL> set long 300
SQL> set linesize 300
SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfiledupdb.ora

断开连接

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误 :

executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志,对目标数据库(被复制的数据库)进行备份(包含数据文件和归档重做日志)

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=65 recid=72 stamp=875262973
input archive log thread=1 sequence=66 recid=73 stamp=875266238
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266246_751 tag=TAG20150325T093040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_65_870806981.dbf recid=72 stamp=875262973
archive log filename=/u02/1_66_870806981.dbf recid=73 stamp=875266238
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:46
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=67 recid=74 stamp=875266422
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_67_870806981.dbf recid=74 stamp=875266422
Finished backup at 25-MAR-15

Starting Control File and SPFILE Autobackup at 25-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-15

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4782    5.72M      DISK        00:00:04     25-MAR-15
        BP Key: 4783   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093040
        Piece Name: /u02/ora_test875266246_751

  List of Archived Logs in backup set 4782
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      1009334    24-MAR-15 1038362    25-MAR-15
  1    66      1038362    25-MAR-15 1040219    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4794    Full    624.76M    DISK        00:02:40     25-MAR-15
        BP Key: 4797   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093052
        Piece Name: /u02/ora_test875266253_761
  List of Datafiles in backup set 4794
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4811    12.50K     DISK        00:00:01     25-MAR-15
        BP Key: 4818   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093344
        Piece Name: /u02/ora_test875266425_771

  List of Archived Logs in backup set 4811
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    67      1040219    25-MAR-15 1040304    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4827    Full    6.89M      DISK        00:00:06     25-MAR-15
        BP Key: 4829   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093348
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp
  Control File Included: Ckp SCN: 1040326      Ckp time: 25-MAR-15
  SPFILE Included: Modification time: 25-MAR-15

将上面的备份传输到远程主机的相同目录中:

[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266246_751 /u02
oracle@192.168.56.2's password:
ora_test875266246_751                                                                                                                100% 5857KB   5.7MB/s   00:00
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266253_761 /u02
oracle@192.168.56.2's password:
ora_test875266253_761                                                                                                                100%  625MB   8.1MB/s   01:17
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266425_771 /u02
oracle@192.168.56.2's password:
ora_test875266425_771                                                                                                                100%   13KB  13.0KB/s   00:00
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015-03-25
oracle@192.168.56.2's password:
o1_mf_s_875266428_bk4441pd_.bkp

检查磁盘组+DG1的空间使用情况,要有足够的空间存储复制数据库的所有文件

ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576      4096     4045                0            4045              0  DG1/

7.执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动,并使用自动通道:

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/oracle@dupdb catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 21:26:07 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: DUPDB (not mounted)

RMAN> duplicate target database to dupdb;

Starting Duplicate Db at 2015-03-25 21:26:22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=35 devtype=DISK

contents of Memory Script:
{
   set until scn  1093434;
   set newname for datafile  1 to
 "+DG1/system01.dbf";
   set newname for datafile  2 to
 "+DG1/undotbs01.dbf";
   set newname for datafile  3 to
 "+DG1/sysaux01.dbf";
   set newname for datafile  4 to
 "+DG1/users01.dbf";
   set newname for datafile  5 to
 "+DG1/example01.dbf";
   set newname for datafile  6 to
 "+DG1/tspitr01.dbf";
   restore
   check readonly
   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

executing command: SET NEWNAME

Starting restore at 2015-03-25 21:26:23
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DG1/system01.dbf
restoring datafile 00002 to +DG1/undotbs01.dbf
restoring datafile 00003 to +DG1/sysaux01.dbf
restoring datafile 00004 to +DG1/users01.dbf
restoring datafile 00005 to +DG1/example01.dbf
restoring datafile 00006 to +DG1/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266253_761
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2015-03-25 21:27:32
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '+DG1/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '+DG1/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '+DG1/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DG1/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875309253 filename=+DG1/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875309253 filename=+DG1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875309253 filename=+DG1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875309253 filename=+DG1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875309253 filename=+DG1/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1093434;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-03-25 21:27:37
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=67
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266425_771
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf thread=1 sequence=67
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf recid=1 stamp=875309258
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=68
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=69
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875292006_791
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875292006_791 tag=TAG20150325T164005
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_68_870806981.dbf thread=1 sequence=68
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_68_870806981.dbf recid=3 stamp=875309261
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_69_870806981.dbf thread=1 sequence=69
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_69_870806981.dbf recid=2 stamp=875309260
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=70
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=71
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875308493_811
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875308493_811 tag=TAG20150325T211453
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_70_870806981.dbf thread=1 sequence=70
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_70_870806981.dbf recid=4 stamp=875309268
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_71_870806981.dbf thread=1 sequence=71
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_71_870806981.dbf recid=5 stamp=875309268
media recovery complete, elapsed time: 00:00:04
Finished recover at 2015-03-25 21:27:57

contents of Memory Script:
{
   shutdown clone;
   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     167772160 bytes

Fixed Size                     1272624 bytes
Variable Size                 58721488 bytes
Database Buffers             104857600 bytes
Redo Buffers                   2920448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '+DG1/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '+DG1/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '+DG1/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DG1/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DG1/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DG1/undotbs01.dbf";
   catalog clone datafilecopy  "+DG1/sysaux01.dbf";
   catalog clone datafilecopy  "+DG1/users01.dbf";
   catalog clone datafilecopy  "+DG1/example01.dbf";
   catalog clone datafilecopy  "+DG1/tspitr01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +DG1/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=+DG1/undotbs01.dbf recid=1 stamp=875309291

cataloged datafile copy
datafile copy filename=+DG1/sysaux01.dbf recid=2 stamp=875309291

cataloged datafile copy
datafile copy filename=+DG1/users01.dbf recid=3 stamp=875309291

cataloged datafile copy
datafile copy filename=+DG1/example01.dbf recid=4 stamp=875309292

cataloged datafile copy
datafile copy filename=+DG1/tspitr01.dbf recid=5 stamp=875309292

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875309291 filename=+DG1/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875309291 filename=+DG1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875309291 filename=+DG1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875309292 filename=+DG1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875309292 filename=+DG1/tspitr01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2015-03-25 21:28:33

RMAN复制数据库(六)

在相同主机上创建复制数据库
当在与目标数据库相同主机上创建复制数据库就与在远程主机上使用不同目录结构复制数据库一样。可以在目标数据库的Oracle Home目录中创建复制数据库,但数据库名必须不同于目标数据库。

1.创建辅助实例的密码文件(这里辅助实例名为aux)

[oracle@oracle11g dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwaux password=oracle entries=10;
[oracle@oracle11g dbs]$ ls -lrt
-rw-r----- 1 oracle oinstall    2560 Mar 24 14:47 orapwdup

2.创建辅助实例网络连接,修改监听文件,使用静态监听来监听辅助实例

[oracle@oracle11g dbs]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = aux)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@oracle11g dbs]$ vi tnsnames.ora
aux =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =aux)
      (UR=A)
    )
  )

测试网络连接

[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 14:57:08 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件

[oracle@oracle11g dbs]$ vi initdup.ora

db_name=aux
db_unique_name=aux
control_files= /u01/app/oracle/oradata/aux/control01.ctl
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/aux/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/aux/')
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M



[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initaux.ora';

File created.

4.启动辅助实例

[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes

SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfileaux.ora

断开连接

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误 :

executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

配置辅助实例的网络服务名

[oracle@oracle11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jy)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )




aux =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =aux)
      (UR=A)
    )
  )

[oracle@oracle11g dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@aux as sysdba
Connected.

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志,对目标数据库(被复制的数据库)进行备份(包含数据文件和归档重做日志)

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=65 recid=72 stamp=875262973
input archive log thread=1 sequence=66 recid=73 stamp=875266238
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266246_751 tag=TAG20150325T093040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_65_870806981.dbf recid=72 stamp=875262973
archive log filename=/u02/1_66_870806981.dbf recid=73 stamp=875266238
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:46
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=67 recid=74 stamp=875266422
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_67_870806981.dbf recid=74 stamp=875266422
Finished backup at 25-MAR-15

Starting Control File and SPFILE Autobackup at 25-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-15

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4782    5.72M      DISK        00:00:04     25-MAR-15
        BP Key: 4783   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093040
        Piece Name: /u02/ora_test875266246_751

  List of Archived Logs in backup set 4782
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      1009334    24-MAR-15 1038362    25-MAR-15
  1    66      1038362    25-MAR-15 1040219    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4794    Full    624.76M    DISK        00:02:40     25-MAR-15
        BP Key: 4797   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093052
        Piece Name: /u02/ora_test875266253_761
  List of Datafiles in backup set 4794
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4811    12.50K     DISK        00:00:01     25-MAR-15
        BP Key: 4818   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093344
        Piece Name: /u02/ora_test875266425_771

  List of Archived Logs in backup set 4811
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    67      1040219    25-MAR-15 1040304    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4827    Full    6.89M      DISK        00:00:06     25-MAR-15
        BP Key: 4829   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093348
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp
  Control File Included: Ckp SCN: 1040326      Ckp time: 25-MAR-15
  SPFILE Included: Modification time: 25-MAR-15

7.执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动,并使用自动通道:

[oracle@oracle11g ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ rman target sys/zzh_2046@test auxiliary sys/system@aux catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 10:49:35 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: AUX (not mounted)

RMAN> duplicate target database to aux nofilenamecheck;

Starting Duplicate Db at 2015-03-25 10:50:29
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=38 devtype=DISK

contents of Memory Script:
{
   set until scn  1040304;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/aux/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/aux/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/aux/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/aux/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/aux/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/aux/tspitr01.dbf";
   restore
   check readonly
   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

executing command: SET NEWNAME

Starting restore at 2015-03-25 10:50:30
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/aux/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/aux/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/aux/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/aux/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/aux/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/aux/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266253_761
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 2015-03-25 10:52:28
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/aux/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/aux/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/aux/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/aux/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875271151 filename=/u01/app/oracle/oradata/aux/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875271151 filename=/u01/app/oracle/oradata/aux/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875271151 filename=/u01/app/oracle/oradata/aux/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875271151 filename=/u01/app/oracle/oradata/aux/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875271151 filename=/u01/app/oracle/oradata/aux/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1040304;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-03-25 10:52:30
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=67
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266425_771
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_67_870806981.dbf thread=1 sequence=67
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_67_870806981.dbf recid=1 stamp=875271154
media recovery complete, elapsed time: 00:00:03
Finished recover at 2015-03-25 10:52:38

contents of Memory Script:
{
   shutdown clone;
   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     167772160 bytes

Fixed Size                     1272624 bytes
Variable Size                 58721488 bytes
Database Buffers             104857600 bytes
Redo Buffers                   2920448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/aux/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/aux/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/aux/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/aux/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/aux/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/aux/undotbs01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/aux/sysaux01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/aux/users01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/aux/example01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/aux/tspitr01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/aux/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/aux/undotbs01.dbf recid=1 stamp=875271167

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/aux/sysaux01.dbf recid=2 stamp=875271167

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/aux/users01.dbf recid=3 stamp=875271167

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/aux/example01.dbf recid=4 stamp=875271167

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/aux/tspitr01.dbf recid=5 stamp=875271168

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875271167 filename=/u01/app/oracle/oradata/aux/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875271167 filename=/u01/app/oracle/oradata/aux/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875271167 filename=/u01/app/oracle/oradata/aux/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875271167 filename=/u01/app/oracle/oradata/aux/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875271168 filename=/u01/app/oracle/oradata/aux/tspitr01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2015-03-25 10:53:24


[oracle@oracle11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 10:53:30 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
aux

RMAN复制数据库(五)

使用configure auxname命令转换文件名
这种方法是使用configure auxname命令来对复制的数据文件进行重命名,使用logfile子句来指定联机重做日志的名称和大小。

1.创建辅助实例的密码文件(这里辅助实例名为dup)

[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdup password=oracle entries=10;
[oracle@jingyong1 dbs]$ ls -lrt
-rw-r----- 1 oracle oinstall    2560 Mar 24 14:47 orapwdup

2.创建辅助实例网络连接,修改监听文件,使用静态监听来监听辅助实例

[oracle@jingyong1 admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

测试网络连接

[oracle@jingyong1 admin]$ export ORACLE_SID=dup
[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 14:57:08 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件

[oracle@jingyong1 dbs]$ vi initdup.ora

db_name=dup
db_unique_name=dup
control_files= /u01/app/oracle/oradata/test/control01.ctl
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M



[oracle@jingyong1 dbs]$ export ORACLE_SID=dup
[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdup.ora';

File created.

4.启动辅助实例

[oracle@jingyong1 dbs]$ export ORACLE_SID=dup
[oracle@jingyong1 dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes

SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfiledup.ora

断开连接

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误 :

executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

在目标主机(运行被复制数据库的主机)配置辅助实例的网络服务名

[oracle@oracle11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jy)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )




dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

[oracle@oracle11g admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dup as sysdba
Connected.

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志,对目标数据库(被复制的数据库)进行备份(包含数据文件和归档重做日志)

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=65 recid=72 stamp=875262973
input archive log thread=1 sequence=66 recid=73 stamp=875266238
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266246_751 tag=TAG20150325T093040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_65_870806981.dbf recid=72 stamp=875262973
archive log filename=/u02/1_66_870806981.dbf recid=73 stamp=875266238
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:46
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=67 recid=74 stamp=875266422
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_67_870806981.dbf recid=74 stamp=875266422
Finished backup at 25-MAR-15

Starting Control File and SPFILE Autobackup at 25-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-15

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4782    5.72M      DISK        00:00:04     25-MAR-15
        BP Key: 4783   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093040
        Piece Name: /u02/ora_test875266246_751

  List of Archived Logs in backup set 4782
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      1009334    24-MAR-15 1038362    25-MAR-15
  1    66      1038362    25-MAR-15 1040219    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4794    Full    624.76M    DISK        00:02:40     25-MAR-15
        BP Key: 4797   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093052
        Piece Name: /u02/ora_test875266253_761
  List of Datafiles in backup set 4794
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4811    12.50K     DISK        00:00:01     25-MAR-15
        BP Key: 4818   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093344
        Piece Name: /u02/ora_test875266425_771

  List of Archived Logs in backup set 4811
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    67      1040219    25-MAR-15 1040304    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4827    Full    6.89M      DISK        00:00:06     25-MAR-15
        BP Key: 4829   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093348
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp
  Control File Included: Ckp SCN: 1040326      Ckp time: 25-MAR-15
  SPFILE Included: Modification time: 25-MAR-15

将上面的备份传输到远程主机的相同目录中:

[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266246_751 /u02
oracle@192.168.56.2's password:
ora_test875266246_751                                                                                                                100% 5857KB   5.7MB/s   00:00
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266253_761 /u02
oracle@192.168.56.2's password:
ora_test875266253_761                                                                                                                100%  625MB   8.1MB/s   01:17
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266425_771 /u02
oracle@192.168.56.2's password:
ora_test875266425_771                                                                                                                100%   13KB  13.0KB/s   00:00
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015-03-25
oracle@192.168.56.2's password:
o1_mf_s_875266428_bk4441pd_.bkp

7.执行duplicate命令,但在复制操作之前必须对所有数据文件执行configure auxname命令来生成新的文件名。如果没有配置自动通道,那么至少手动分配一个辅助实例。使用logfile子句将重做日志的名称与数量可以设置为与目标数据库一样。如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动,并使用自动通道:

[oracle@oracle11g ~]$ rman target sys/zzh_2046@test auxiliary sys/oracle@dup catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 10:19:22 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

RMAN> run
2> {
3> configure auxname for datafile '/u01/app/oracle/oradata/test/system01.dbf' to '/u01/app/oracle/oradata/dup/system01.dbf';
4> configure auxname for datafile '/u01/app/oracle/oradata/test/undotbs01.dbf' to '/u01/app/oracle/oradata/dup/undotbs01.dbf';
5> configure auxname for datafile '/u01/app/oracle/oradata/test/sysaux01.dbf' to '/u01/app/oracle/oradata/dup/sysaux01.dbf';
6> configure auxname for datafile '/u01/app/oracle/oradata/test/users01.dbf' to '/u01/app/oracle/oradata/dup/users01.dbf';
7> configure auxname for datafile '/u01/app/oracle/oradata/test/example01.dbf' to '/u01/app/oracle/oradata/dup/example01.dbf';
8> configure auxname for datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' to '/u01/app/oracle/oradata/dup/tspitr01.dbf';
9> duplicate target database to dup
10> db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/dup/')
11> logfile
12> group  1 ('/u01/app/oracle/oradata/dup/redo01.log') SIZE 50M,
13> group  2 ('/u01/app/oracle/oradata/dup/redo02.log') SIZE 50M,
14> group  3 ('/u01/app/oracle/oradata/dup/redo03.log') SIZE 50M;
15> }

auxiliary name for datafile 1 set to: /u01/app/oracle/oradata/dup/system01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

auxiliary name for datafile 2 set to: /u01/app/oracle/oradata/dup/undotbs01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

auxiliary name for datafile 3 set to: /u01/app/oracle/oradata/dup/sysaux01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

auxiliary name for datafile 4 set to: /u01/app/oracle/oradata/dup/users01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

auxiliary name for datafile 5 set to: /u01/app/oracle/oradata/dup/example01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

auxiliary name for datafile 6 set to: /u01/app/oracle/oradata/dup/tspitr01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting Duplicate Db at 25-MAR-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

contents of Memory Script:
{
   set until scn  1040304;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/dup/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/dup/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dup/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dup/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/dup/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dup/tspitr01.dbf";
   restore
   check readonly
   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

executing command: SET NEWNAME

Starting restore at 25-MAR-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/dup/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/dup/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/dup/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/dup/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/dup/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/dup/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266253_761
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 25-MAR-15
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/dup/redo01.log' ) SIZE 50 M ,
  GROUP  2 ( '/u01/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M ,
  GROUP  3 ( '/u01/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875269283 filename=/u01/app/oracle/oradata/dup/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875269283 filename=/u01/app/oracle/oradata/dup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875269283 filename=/u01/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875269283 filename=/u01/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875269283 filename=/u01/app/oracle/oradata/dup/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1040304;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-MAR-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=67
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266425_771
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf thread=1 sequence=67
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf recid=1 stamp=875269284
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-MAR-15

contents of Memory Script:
{
   shutdown clone;
   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     167772160 bytes

Fixed Size                     1272624 bytes
Variable Size                 58721488 bytes
Database Buffers             104857600 bytes
Redo Buffers                   2920448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/dup/redo01.log' ) SIZE 50 M ,
  GROUP  2 ( '/u01/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M ,
  GROUP  3 ( '/u01/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/dup/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/undotbs01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/sysaux01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/users01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/example01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/tspitr01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/dup/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/undotbs01.dbf recid=1 stamp=875269301

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/sysaux01.dbf recid=2 stamp=875269301

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/users01.dbf recid=3 stamp=875269301

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/example01.dbf recid=4 stamp=875269301

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/tspitr01.dbf recid=5 stamp=875269301

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875269301 filename=/u01/app/oracle/oradata/dup/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875269301 filename=/u01/app/oracle/oradata/dup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875269301 filename=/u01/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875269301 filename=/u01/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875269301 filename=/u01/app/oracle/oradata/dup/tspitr01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-MAR-15


[oracle@jingyong1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 10:22:40 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dup

RMAN复制数据库(四)

使用set newname命令转换文件名
这种方法是使用set newname命令来对复制的数据文件进行重命名,使用logfile子句来指定联机重做日志的名称和大小。

1.创建辅助实例的密码文件(这里辅助实例名为dup)

[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdup password=oracle entries=10;
[oracle@jingyong1 dbs]$ ls -lrt
-rw-r----- 1 oracle oinstall    2560 Mar 24 14:47 orapwdup

2.创建辅助实例网络连接,修改监听文件,使用静态监听来监听辅助实例

[oracle@jingyong1 admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

测试网络连接

[oracle@jingyong1 admin]$ export ORACLE_SID=dup
[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 14:57:08 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件

[oracle@jingyong1 dbs]$ vi initdup.ora

db_name=dup
db_unique_name=dup
control_files= /u01/app/oracle/oradata/test/control01.ctl
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M



[oracle@jingyong1 dbs]$ export ORACLE_SID=dup
[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdup.ora';

File created.

4.启动辅助实例

[oracle@jingyong1 dbs]$ export ORACLE_SID=dup
[oracle@jingyong1 dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes

SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfiledup.ora

断开连接

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误 :

executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

在目标主机(运行被复制数据库的主机)配置辅助实例的网络服务名

[oracle@oracle11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jy)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )




dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

[oracle@oracle11g admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@dup as sysdba
Connected.

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志对目标数据库(被复制的数据库)进行备份(包含数据文件和归档重做日志)

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=65 recid=72 stamp=875262973
input archive log thread=1 sequence=66 recid=73 stamp=875266238
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266246_751 tag=TAG20150325T093040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_65_870806981.dbf recid=72 stamp=875262973
archive log filename=/u02/1_66_870806981.dbf recid=73 stamp=875266238
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:46
Finished backup at 25-MAR-15

Starting backup at 25-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=67 recid=74 stamp=875266422
channel ORA_DISK_1: starting piece 1 at 25-MAR-15
channel ORA_DISK_1: finished piece 1 at 25-MAR-15
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_67_870806981.dbf recid=74 stamp=875266422
Finished backup at 25-MAR-15

Starting Control File and SPFILE Autobackup at 25-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-15

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4782    5.72M      DISK        00:00:04     25-MAR-15
        BP Key: 4783   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093040
        Piece Name: /u02/ora_test875266246_751

  List of Archived Logs in backup set 4782
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      1009334    24-MAR-15 1038362    25-MAR-15
  1    66      1038362    25-MAR-15 1040219    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4794    Full    624.76M    DISK        00:02:40     25-MAR-15
        BP Key: 4797   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093052
        Piece Name: /u02/ora_test875266253_761
  List of Datafiles in backup set 4794
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1040242    25-MAR-15 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4811    12.50K     DISK        00:00:01     25-MAR-15
        BP Key: 4818   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093344
        Piece Name: /u02/ora_test875266425_771

  List of Archived Logs in backup set 4811
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    67      1040219    25-MAR-15 1040304    25-MAR-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4827    Full    6.89M      DISK        00:00:06     25-MAR-15
        BP Key: 4829   Status: AVAILABLE  Compressed: NO  Tag: TAG20150325T093348
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp
  Control File Included: Ckp SCN: 1040326      Ckp time: 25-MAR-15
  SPFILE Included: Modification time: 25-MAR-15

将上面的备份传输到远程主机的相同目录中:

[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266246_751 /u02
oracle@192.168.56.2's password:
ora_test875266246_751                                                                                                                100% 5857KB   5.7MB/s   00:00
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266253_761 /u02
oracle@192.168.56.2's password:
ora_test875266253_761                                                                                                                100%  625MB   8.1MB/s   01:17
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266425_771 /u02
oracle@192.168.56.2's password:
ora_test875266425_771                                                                                                                100%   13KB  13.0KB/s   00:00
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015-03-25
oracle@192.168.56.2's password:
o1_mf_s_875266428_bk4441pd_.bkp

7.执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例。如果需要的话使用logfile子句将重做日志的名称与数量可以设置为与目标数据库一样。为复制数据库的数据文件指定新的文件名。如果是使用PFILE参数文件启动辅助实例需要指定file参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动,并使用自动通道:

[oracle@oracle11g ~]$ rman target sys/zzh_2046@test auxiliary sys/oracle@dup catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 10:00:22 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

RMAN> run
2> {
3> set newname for datafile '/u01/app/oracle/oradata/test/system01.dbf' to '/u01/app/oracle/oradata/dup/system01.dbf';
4> set newname for datafile '/u01/app/oracle/oradata/test/undotbs01.dbf' to '/u01/app/oracle/oradata/dup/undotbs01.dbf';
5> set newname for datafile '/u01/app/oracle/oradata/test/sysaux01.dbf' to '/u01/app/oracle/oradata/dup/sysaux01.dbf';
6> set newname for datafile '/u01/app/oracle/oradata/test/users01.dbf' to '/u01/app/oracle/oradata/dup/users01.dbf';
7> set newname for datafile '/u01/app/oracle/oradata/test/example01.dbf' to '/u01/app/oracle/oradata/dup/example01.dbf';
8> set newname for datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' to '/u01/app/oracle/oradata/dup/tspitr01.dbf';
9> duplicate target database to dup
10> db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/dup/')
11> logfile
12> group  1 ('/u01/app/oracle/oradata/dup/redo01.log') SIZE 50M,
13> group  2 ('/u01/app/oracle/oradata/dup/redo02.log') SIZE 50M,
14> group  3 ('/u01/app/oracle/oradata/dup/redo03.log') SIZE 50M;
15> }

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 Duplicate Db at 25-MAR-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=35 devtype=DISK

contents of Memory Script:
{
   set until scn  1040304;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/dup/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/dup/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dup/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dup/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/dup/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dup/tspitr01.dbf";
   restore
   check readonly
   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

executing command: SET NEWNAME

Starting restore at 25-MAR-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/dup/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/dup/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/dup/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/dup/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/dup/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/dup/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266253_761
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:47
Finished restore at 25-MAR-15
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/dup/redo01.log' ) SIZE 50 M ,
  GROUP  2 ( '/u01/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M ,
  GROUP  3 ( '/u01/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875268148 filename=/u01/app/oracle/oradata/dup/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875268149 filename=/u01/app/oracle/oradata/dup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875268149 filename=/u01/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875268149 filename=/u01/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875268149 filename=/u01/app/oracle/oradata/dup/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1040304;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-MAR-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=67
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266425_771
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf thread=1 sequence=67
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf recid=1 stamp=875268153
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-MAR-15

contents of Memory Script:
{
   shutdown clone;
   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     167772160 bytes

Fixed Size                     1272624 bytes
Variable Size                 58721488 bytes
Database Buffers             104857600 bytes
Redo Buffers                   2920448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/dup/redo01.log' ) SIZE 50 M ,
  GROUP  2 ( '/u01/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M ,
  GROUP  3 ( '/u01/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/dup/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/undotbs01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/sysaux01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/users01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/example01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dup/tspitr01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/dup/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/undotbs01.dbf recid=1 stamp=875268169

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/sysaux01.dbf recid=2 stamp=875268169

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/users01.dbf recid=3 stamp=875268169

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/example01.dbf recid=4 stamp=875268169

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/dup/tspitr01.dbf recid=5 stamp=875268169

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875268169 filename=/u01/app/oracle/oradata/dup/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875268169 filename=/u01/app/oracle/oradata/dup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875268169 filename=/u01/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875268169 filename=/u01/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875268169 filename=/u01/app/oracle/oradata/dup/tspitr01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-MAR-15

[oracle@jingyong1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 10:05:42 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dup

Proudly powered by WordPress | Indrajeet by Sus Hill.