oracle 10g 物理备库转换逻辑备库ORA-19953故障解决方法

操作环境是Red hat Linux 5.4 x86-64 Oracle 10.2.0.5 在将物理备库转换为逻辑备库出现ORA-19953

SQL> alter database recover to logical standby test;
alter database recover to logical standby test
*
ERROR at line 1:
ORA-19953: database should not be open

alert.log文件内容如下:

Incomplete Recovery applied until change 720500
Sun Jun 28 19:50:45 CST 2015
Media Recovery Complete (test_ldg)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 720500
Resetting resetlogs activation ID 2174774786 (0x81a06e02)
Online log /u03/app/oracle/oradata/test_ldg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 720498
Sun Jun 28 19:50:48 CST 2015
Setting recovery target incarnation to 3
Sun Jun 28 19:50:48 CST 2015
ACTIVATE STANDBY: Complete - Database shutdown required (test_ldg)
Sun Jun 28 19:50:48 CST 2015
ORA-19953 signalled during: alter database recover to logical standby test...

MOS上有一关于这个问题的BUG(Bug ID 9207121)内容如下:

Type	B - Defect	Fixed in Product Version
Severity	2 - Severe Loss of Service	Product Version	10.2.0.4
Status	33 - Suspended, Req'd Info not Avail	Platform	226 - Linux x86-64
Created	11-Dec-2009	Platform Version	RED HAT ENTERPRISE LINUX 5
Updated	05-Feb-2015	Base Bug	N/A
Database Version	10.2.0.4	Affects Platforms	Generic
Product Source	Oracle	Knowledge, Patches and Bugs related to this bug


Related Products

Line	Oracle Database Products	Family	Oracle Database Suite
Area	Oracle Database	Product	5 - Oracle Database - Enterprise Edition

Hdr: 9207121 10.2.0.4 RDBMS 10.2.0.4 DATAGUARD_LSBY PRODID-5 PORTID-226 ORA-19953
Abstract: ORA-19953 CREATING LOGICAL STANDBY

*** 12/11/09 12:35 pm ***

PROBLEM:
--------
ct has a 3-node RAC primary(db_name=TCIP, unique_name=TCIP)
and a single node physical standby db_name=TCIP,unique_name=TCIPvl) using
spfile.

Converting this physical standby to logical standby failed.
When executing on the standby side
SQL> alter database recover to logical standby TCIPvl;
the db_name in the spfile is not changed to TCIPvl.




DIAGNOSTIC ANALYSIS:
--------------------
The following outlines the steps:
- Verified that primary and physical standby are in sync. (around 2009 12/11
12:30)
- stopped recovery at physical standby (Fri Dec 11 12:35:10 2009)
- build dictionary on primary  (Fri Dec 11 12:55:29 2009 log seq 9976)
   SQL> DBMS_LOGSTDBY.BUILD;
- switched logs on primary (all instances 3 times)
- verified on the standby side that the logs containing dictionary
information were archived and arrived (but not applied) on the standby
- executed "alter database recover to logical standby TCIPvl" on standby (Fri
Dec 11 13:05:35 2009)
- the above SQL did not show any errors on the screen. However I noticed the
following:
. the db_name was not changed in spfile.  (verified using pfile create
pfile='/tmp/whatever.ora" from spfile)
. the standby's alert log shows ORA-19953.
. did not see the following message in the alert log.
    *** DBNEWID utility started ***
     DBID will be changed from 3890508598 to new DBID of 70593532 for
database ORCL10
     DBNAME will be changed from ORCL10 to new DBNAME of ORCL10S
     Starting datafile conversion
    ...
- verified that spfile is writable as the changes to archive_dest_3 was
effective in spfile.
- performed "alter system set db_name='TCIPvl' scope=spfile sid='*' ' on
standby
- shutdown standby, then startup mount
  got ORA-1103 "database name '%s' in control file is not '%s' on the command
line.

WORKAROUND:
-----------

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
at ct site.

TEST CASE:
----------

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------
- alert logs from primary and standby, as well as the pfile from the standby
after "recover to logical standy.." was excuted.
- The converting physical-> logical work was done between 2009 12/11 12:30 -
13:10

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 12/11/09 12:58 pm ***
*** 12/11/09 12:58 pm *** (CHG: Sta->16)
*** 12/11/09 01:00 pm *** (CHG: Sta->10)
*** 01/08/10 12:44 pm ***
*** 01/12/10 10:55 am *** (CHG: Sta->33)
*** 02/04/15 11:54 pm ***
*** 02/04/15 11:54 pm ***
*** 02/04/15 11:54 pm ***

描述是Linux x86-64位的10.2.0.4,但我这是10.2.0.5,与现象与这个BUG相同。上面给出的论断步骤如下:

The following outlines the steps:
- Verified that primary and physical standby are in sync. (around 2009 12/11
12:30)
- stopped recovery at physical standby (Fri Dec 11 12:35:10 2009)
- build dictionary on primary  (Fri Dec 11 12:55:29 2009 log seq 9976)
   SQL> DBMS_LOGSTDBY.BUILD;
- switched logs on primary (all instances 3 times)

在主库中执行DBMS_LOGSTDBY.BUILD创建数据字典后,在主库执行日志切换三次(因为缺省有三组重做日志组,如果是RAC,每个实例都要执行三次)以确保创建的数据字典传输同物理备库。

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database recover to logical standby test;

Database altered.

转换成功,alert.log内容如下:

alter database recover to logical standby test
Sun Jun 28 20:12:29 CST 2015
Media Recovery Start: Managed Standby Recovery (test_ldg)
Sun Jun 28 20:12:29 CST 2015
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u03/app/oracle/archive/test_ldg/1_71_876665479.dbf
Media Recovery Log /u03/app/oracle/archive/test_ldg/1_72_876665479.dbf
Media Recovery Log /u03/app/oracle/archive/test_ldg/1_73_876665479.dbf
Sun Jun 28 20:12:31 CST 2015
Incomplete Recovery applied until change 722225
Sun Jun 28 20:12:31 CST 2015
Media Recovery Complete (test_ldg)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 722225
Resetting resetlogs activation ID 2174774786 (0x81a06e02)
Online log /u03/app/oracle/oradata/test_ldg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 722223
Sun Jun 28 20:12:34 CST 2015
Setting recovery target incarnation to 3
Sun Jun 28 20:12:34 CST 2015
Converting standby mount to primary mount.
Sun Jun 28 20:12:34 CST 2015
ACTIVATE STANDBY: Complete - Database mounted as primary (test_ldg)
*** DBNEWID utility started ***
DBID will be changed from 2174811906 to new DBID of 2181762994 for database TEST
DBNAME will be changed from TEST to new DBNAME of TEST
Starting datafile conversion
kcv_lh_or_upgrade: 10.2 upgrading 1 incarnations
Setting recovery target incarnation to 1
Datafile conversion complete
Failed to find temporary file: /u03/app/oracle/oradata/test_ldg/temp01.dbf
Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2181762994.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Completed: alter database recover to logical standby test
Sun Jun 28 20:12:44 CST 2015
destination database instance is 'started' not 'mounted'

从上面的Completed: alter database recover to logical standby test可以确认将test数据库从物理备为转换为了逻辑备库。

oracle 11g duplicate database基于备份复制数据库(六)

不使用目标数据库和恢复目录基于备份的复制,下面测试将原数据库使用备份复制到远程主机不同目录
1.对原数据库生成备份

RMAN> backup as compressed backupset database plus archivelog;


Starting backup at 2015-06-05 16:44:19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:22
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:29
piece handle=/u02/backup/29q8o8v6_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:29
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:30
piece handle=/u02/backup/2aq8o8vd_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:30
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:31
piece handle=/u02/backup/2bq8o8ve_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:31
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:34
piece handle=/u02/backup/2cq8o8vf_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:34
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:35
piece handle=/u02/backup/2dq8o8vi_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
input archived log thread=1 sequence=10 RECID=29 STAMP=881274168
input archived log thread=1 sequence=11 RECID=30 STAMP=881309835
input archived log thread=1 sequence=12 RECID=31 STAMP=881310058
input archived log thread=1 sequence=13 RECID=32 STAMP=881310212
input archived log thread=1 sequence=14 RECID=33 STAMP=881396520
input archived log thread=1 sequence=15 RECID=34 STAMP=881534563
input archived log thread=1 sequence=16 RECID=35 STAMP=881570186
input archived log thread=1 sequence=17 RECID=36 STAMP=881577860
input archived log thread=1 sequence=18 RECID=37 STAMP=881578662
input archived log thread=1 sequence=19 RECID=38 STAMP=881579588
input archived log thread=1 sequence=20 RECID=39 STAMP=881582326
input archived log thread=1 sequence=21 RECID=40 STAMP=881594537
input archived log thread=1 sequence=22 RECID=41 STAMP=881599459
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:36
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:51
piece handle=/u02/backup/2eq8o8vk_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-06-05 16:44:51

Starting backup at 2015-06-05 16:44:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:52
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:37
piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 2015-06-05 16:46:37

Starting backup at 2015-06-05 16:46:37
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=42 STAMP=881599598
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:46:39
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:40
piece handle=/u02/backup/2gq8o93f_1_1 tag=TAG20150605T164639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 16:46:40

Starting Control File and SPFILE Autobackup at 2015-06-05 16:46:40
piece handle=/u02/backup/c-1644809111-20150605-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-06-05 16:46:44

RMAN> list backup;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16127   946.50K    DISK        00:00:00     2015-06-05 16:44:29
        BP Key: 16133   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2aq8o8vd_1_1

  List of Archived Logs in backup set 16127
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1       703711     2015-06-01 10:49:47 704659     2015-06-01 11:05:07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16129   5.37M      DISK        00:00:01     2015-06-05 16:44:32
        BP Key: 16135   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2cq8o8vf_1_1

  List of Archived Logs in backup set 16129
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    2       704659     2015-06-01 11:05:07 705159     2015-06-01 11:07:43
  1    3       705159     2015-06-01 11:07:43 705443     2015-06-01 11:12:21
  1    4       705443     2015-06-01 11:12:21 707134     2015-06-01 11:23:07
  1    5       707134     2015-06-01 11:23:07 707879     2015-06-01 11:30:45
  1    6       707879     2015-06-01 11:30:45 707956     2015-06-01 11:33:00
  1    7       707956     2015-06-01 11:33:00 739261     2015-06-01 21:55:54

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16131   25.64M     DISK        00:00:08     2015-06-05 16:44:44
        BP Key: 16137   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2eq8o8vk_1_1

  List of Archived Logs in backup set 16131
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    8       739261     2015-06-01 21:55:54 746801     2015-06-01 22:05:12
  1    9       746801     2015-06-01 22:05:12 750015     2015-06-01 22:09:21
  1    10      750015     2015-06-01 22:09:21 751675     2015-06-01 22:22:48
  1    11      751675     2015-06-01 22:22:48 773357     2015-06-02 08:17:13
  1    12      773357     2015-06-02 08:17:13 773904     2015-06-02 08:20:58
  1    13      773904     2015-06-02 08:20:58 773991     2015-06-02 08:23:32
  1    14      773991     2015-06-02 08:23:32 811036     2015-06-03 08:21:54
  1    15      811036     2015-06-03 08:21:54 848751     2015-06-04 22:42:38
  1    16      848751     2015-06-04 22:42:38 878559     2015-06-05 08:36:20
  1    17      878559     2015-06-05 08:36:20 882947     2015-06-05 10:44:20
  1    18      882947     2015-06-05 10:44:20 883808     2015-06-05 10:57:41
  1    19      883808     2015-06-05 10:57:41 884702     2015-06-05 11:13:08
  1    20      884702     2015-06-05 11:13:08 886070     2015-06-05 11:58:46
  1    21      886070     2015-06-05 11:58:46 892378     2015-06-05 15:22:17
  1    22      892378     2015-06-05 15:22:17 894752     2015-06-05 16:44:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16184   Full    271.73M    DISK        00:01:43     2015-06-05 16:46:35
        BP Key: 16200   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164451
        Piece Name: /u02/backup/2fq8o904_1_1
  List of Datafiles in backup set 16184
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/system01.dbf
  2       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/sysaux01.dbf
  3       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/undotbs01.dbf
  4       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/users01.dbf
  5       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/test01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16213   7.50K      DISK        00:00:00     2015-06-05 16:46:39
        BP Key: 16219   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164639
        Piece Name: /u02/backup/2gq8o93f_1_1

  List of Archived Logs in backup set 16213
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    23      894752     2015-06-05 16:44:19 894834     2015-06-05 16:46:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16228   Full    9.48M      DISK        00:00:02     2015-06-05 16:46:43
        BP Key: 16230   Status: AVAILABLE  Compressed: NO  Tag: TAG20150605T164641
        Piece Name: /u02/backup/c-1644809111-20150605-00
  SPFILE Included: Modification time: 2015-06-05 16:01:17
  SPFILE db_unique_name: DB
  Control File Included: Ckp SCN: 894861       Ckp time: 2015-06-05 16:46:41

2.将备份文件传输到目的主机上

[oracle11@jingyong1 backup]$ scp oracle11@192.168.56.2:/u02/backup/* /u02/backup/
oracle11@192.168.56.2's password:
29q8o8v6_1_1                                                                                                                         100%   17MB  16.8MB/s   00:01
2aq8o8vd_1_1                                                                                                                         100%  947KB 947.0KB/s   00:00
2bq8o8ve_1_1                                                                                                                         100% 2916KB   2.9MB/s   00:00
2cq8o8vf_1_1                                                                                                                         100% 5504KB   5.4MB/s   00:01
2dq8o8vi_1_1                                                                                                                         100% 1992KB   1.9MB/s   00:00
2eq8o8vk_1_1                                                                                                                         100%   26MB  12.8MB/s   00:02
2fq8o904_1_1                                                                                                                         100%  272MB   8.5MB/s   00:32
2gq8o93f_1_1                                                                                                                         100% 8192     8.0KB/s   00:00
c-1644809111-20150605-00                                                                                                             100% 9728KB   9.5MB/s   00:00
[oracle11@jingyong1 backup]$ ls -lrt
total 343140
-rw-r----- 1 oracle11 oinstall   2985472 Jun  5 17:07 2bq8o8ve_1_1
-rw-r----- 1 oracle11 oinstall    969728 Jun  5 17:07 2aq8o8vd_1_1
-rw-r----- 1 oracle11 oinstall  17567232 Jun  5 17:07 29q8o8v6_1_1
-rw-r----- 1 oracle11 oinstall   2039296 Jun  5 17:07 2dq8o8vi_1_1
-rw-r----- 1 oracle11 oinstall   5636096 Jun  5 17:07 2cq8o8vf_1_1
-rw-r----- 1 oracle11 oinstall  26888192 Jun  5 17:07 2eq8o8vk_1_1
-rw-r----- 1 oracle11 oinstall   9961472 Jun  5 17:07 c-1644809111-20150605-00
-rw-r----- 1 oracle11 oinstall      8192 Jun  5 17:07 2gq8o93f_1_1
-rw-r----- 1 oracle11 oinstall 284934144 Jun  5 17:07 2fq8o904_1_1

3.创建辅助实例的密码文件(这里辅助实例名为dup) ,在目的主机上为辅助实例创建密码文件可以有以下选项:
.手动创建密码文件,对于duplicate … from active database有额外的要求。必须使用SYS用户ID并且密码必须与原数据库的密码相匹配。当想要使用单独的密码来创建密码文件时因此可以启动辅助实例并使用它来连接原数据库。

.在执行duplicate … from active database命令时指定password file选项,在这种情况下,RMAN将原数据库的密码文件复制到目的主机上并且覆盖辅助实例已经存在的密码文件。如果原数据库密码文件有多个密码且你想让它们在副本数据库中使用时这种技术是非常有用的。

[oracle11@jingyong1 dbs]$ orapwd file=/u03/app/oracle/11.2.0/db/dbs/orapwdup password=system entries=10;

[oracle11@jingyong1 dbs]$ ls -lrt orapwdup
-rw-r----- 1 oracle11 oinstall 2560 Jun  5 09:40 orapwdup

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

[oracle11@jingyong1 admin]$ vi listener.ora


# listener.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u03/app/oracle/11.2.0/db)
    )
  )

[oracle11@jingyong1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUN-2015 11:25:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u03/app/oracle/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Log messages written to /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                05-JUN-2015 11:25:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Listener Log File         /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cs" has 1 instance(s).
  Instance "cs", status UNKNOWN, has 1 handler(s) for this service...
Service "dup" has 1 instance(s).
  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

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

[oracle11@jingyong1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

5.创建辅助实例的参数文件,初始化参数文件的目录和内容依赖于给复制文件命名选择的策略。可以选择建议的技术来对原主机和目的主机使用相同的命名策略。因此对于Oracle RAC环境,对于原主机和目的主机可以使用相同的ORACLE_SID。在参数文件中将db_name设置为任意值,db_name是唯一必须设置的参数。创建参数文件有以下选项:
.为辅助实例来创建文本参数文件,初始化参数文件的存储目录是在主机上操作系统特定的缺省目录。例如,在Linux和UNIX中缺省参数文件名是ORACLE_HOME/dbs/initORACLE_SID.ora,在Windows平台上参数文件名是ORACLE_HOME\database\initORACLE_SID.ora

.在执行duplicate命令时指定spfile子句。duplicate … spfile技术最简单,因为在执行复制时RMAN自动将原数据库的spfile文件复制到辅助实例或从备份中还原spfile文件。如果在辅助实例上存在spfile文件,那么RMAN就会覆盖它。

[oracle11@jingyong1 dbs]$ vi initdup.ora
db_name=dup
db_unique_name=dup
control_files= /u03/app/oracle/oradata/dup/control01.ctl
remote_login_passwordfile=exclusive
compatible = 11.2.0.4.0
db_block_size=8192
sga_target=300M
sga_max_size=300M
pga_aggregate_target=32M
db_file_name_convert=('/u03/app/oracle/oradata/db/','/u03/app/oracle/oradata/dup/')
log_file_name_convert=('/u03/app/oracle/oradata/db/','/u03/app/oracle/oradata/dup/')

6.启动辅助实例,启动SQL*Plus并使用sysdba权限连接到辅助实例。将辅助实例启动到nomount状态(如果参数文件在缺省目录中startup命令不需要pfile参数)。注意:确保辅助实例使用文本参数文件来启动而不是SPFILE参数文件。不要创建控制文件或试图mount或open辅助实例。

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

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 09:58:16 2015

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1364340 bytes
Variable Size             104861324 bytes
Database Buffers          201326592 bytes
Redo Buffers                6307840 bytes

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

[oracle11@oracle11g 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)
    )
  )

[oracle11@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 10:05:14 2015

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

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

8.加载或打开目标数据库,如果RMAN连接到原数据库作为目标数据库,那么原数据库为了执行复制必须设置为合适的状态。如果原数据库实例没有mount或open,那么将原数据库mount或open。如果执行active database复制,那么确保满足下面额外的条件:
-如果原数据库open,那么必须启用归档
-如果原数据库没有open,那么数据库不需要执行实例恢复
由于原数据库启用了归档所以可以将原数据库启动到open状态


[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 08:35:45 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1365068 bytes
Variable Size             310381492 bytes
Database Buffers          104857600 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.

9.执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例。如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。复制命令没有指定数据库名。使用database关键字而不指定数据库名,duplicate将从备份中获得数据库名和DBID。如果在backup location目录中一个数据库有多个备份将会显示错误。使用backup子句来识别没有目标数据库和恢复目录的复制类型。until time选项用来指定恢复目标时间,它是唯一能与backup location子句一起使用的选项。因为副本数据库和原数据库文件的文件名相同所以要指定nofilenamecheck选项:

[oracle11@oracle11g ~]$ rman auxiliary sys/system@dup

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 20:02:39 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database to dup until time '2015-06-05 16:46:37'backup location '/u02/backup'  nofilenamecheck;

Starting Duplicate Db at 2015-06-05 20:03:01

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u02/backup/snapcf_db.f';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

Starting restore at 2015-06-05 20:03:14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
output file name=/u03/app/oracle/oradata/dup/control01.ctl
Finished restore at 2015-06-05 20:03:16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   set until scn  894791;
   set newname for datafile  1 to
 "/u03/app/oracle/oradata/dup/system01.dbf";
   set newname for datafile  2 to
 "/u03/app/oracle/oradata/dup/sysaux01.dbf";
   set newname for datafile  3 to
 "/u03/app/oracle/oradata/dup/undotbs01.dbf";
   set newname for datafile  4 to
 "/u03/app/oracle/oradata/dup/users01.dbf";
   set newname for datafile  5 to
 "/u03/app/oracle/oradata/dup/test01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2015-06-05 20:03:21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/dup/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/dup/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/dup/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/dup/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/dup/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/2fq8o904_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 2015-06-05 20:05:59

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

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=881611559 file name=/u03/app/oracle/oradata/dup/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=881611559 file name=/u03/app/oracle/oradata/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=881611559 file name=/u03/app/oracle/oradata/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=881611559 file name=/u03/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=881611559 file name=/u03/app/oracle/oradata/dup/test01.dbf
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

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


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

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/dup/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/sysaux01.dbf RECID=1 STAMP=881611575
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/undotbs01.dbf RECID=2 STAMP=881611575
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/users01.dbf RECID=3 STAMP=881611575
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/test01.dbf RECID=4 STAMP=881611575

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881611575 file name=/u03/app/oracle/oradata/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881611575 file name=/u03/app/oracle/oradata/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881611575 file name=/u03/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=881611575 file name=/u03/app/oracle/oradata/dup/test01.dbf

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

database opened
Finished Duplicate Db at 2015-06-05 20:06:41

oracle 11g duplicate database基于备份复制数据库(五)

不使用目标数据库和恢复目录基于备份的复制,下面测试将原数据库使用备份复制到远程主机相同目录
1.对原数据库生成备份

RMAN> backup as compressed backupset database plus archivelog;


Starting backup at 2015-06-05 16:44:19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:22
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:29
piece handle=/u02/backup/29q8o8v6_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:29
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:30
piece handle=/u02/backup/2aq8o8vd_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:30
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:31
piece handle=/u02/backup/2bq8o8ve_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:31
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:34
piece handle=/u02/backup/2cq8o8vf_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:34
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:35
piece handle=/u02/backup/2dq8o8vi_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
input archived log thread=1 sequence=10 RECID=29 STAMP=881274168
input archived log thread=1 sequence=11 RECID=30 STAMP=881309835
input archived log thread=1 sequence=12 RECID=31 STAMP=881310058
input archived log thread=1 sequence=13 RECID=32 STAMP=881310212
input archived log thread=1 sequence=14 RECID=33 STAMP=881396520
input archived log thread=1 sequence=15 RECID=34 STAMP=881534563
input archived log thread=1 sequence=16 RECID=35 STAMP=881570186
input archived log thread=1 sequence=17 RECID=36 STAMP=881577860
input archived log thread=1 sequence=18 RECID=37 STAMP=881578662
input archived log thread=1 sequence=19 RECID=38 STAMP=881579588
input archived log thread=1 sequence=20 RECID=39 STAMP=881582326
input archived log thread=1 sequence=21 RECID=40 STAMP=881594537
input archived log thread=1 sequence=22 RECID=41 STAMP=881599459
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:36
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:51
piece handle=/u02/backup/2eq8o8vk_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-06-05 16:44:51

Starting backup at 2015-06-05 16:44:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:52
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:37
piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 2015-06-05 16:46:37

Starting backup at 2015-06-05 16:46:37
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=42 STAMP=881599598
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:46:39
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:40
piece handle=/u02/backup/2gq8o93f_1_1 tag=TAG20150605T164639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 16:46:40

Starting Control File and SPFILE Autobackup at 2015-06-05 16:46:40
piece handle=/u02/backup/c-1644809111-20150605-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-06-05 16:46:44

RMAN> list backup;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16127   946.50K    DISK        00:00:00     2015-06-05 16:44:29
        BP Key: 16133   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2aq8o8vd_1_1

  List of Archived Logs in backup set 16127
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1       703711     2015-06-01 10:49:47 704659     2015-06-01 11:05:07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16129   5.37M      DISK        00:00:01     2015-06-05 16:44:32
        BP Key: 16135   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2cq8o8vf_1_1

  List of Archived Logs in backup set 16129
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    2       704659     2015-06-01 11:05:07 705159     2015-06-01 11:07:43
  1    3       705159     2015-06-01 11:07:43 705443     2015-06-01 11:12:21
  1    4       705443     2015-06-01 11:12:21 707134     2015-06-01 11:23:07
  1    5       707134     2015-06-01 11:23:07 707879     2015-06-01 11:30:45
  1    6       707879     2015-06-01 11:30:45 707956     2015-06-01 11:33:00
  1    7       707956     2015-06-01 11:33:00 739261     2015-06-01 21:55:54

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16131   25.64M     DISK        00:00:08     2015-06-05 16:44:44
        BP Key: 16137   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2eq8o8vk_1_1

  List of Archived Logs in backup set 16131
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    8       739261     2015-06-01 21:55:54 746801     2015-06-01 22:05:12
  1    9       746801     2015-06-01 22:05:12 750015     2015-06-01 22:09:21
  1    10      750015     2015-06-01 22:09:21 751675     2015-06-01 22:22:48
  1    11      751675     2015-06-01 22:22:48 773357     2015-06-02 08:17:13
  1    12      773357     2015-06-02 08:17:13 773904     2015-06-02 08:20:58
  1    13      773904     2015-06-02 08:20:58 773991     2015-06-02 08:23:32
  1    14      773991     2015-06-02 08:23:32 811036     2015-06-03 08:21:54
  1    15      811036     2015-06-03 08:21:54 848751     2015-06-04 22:42:38
  1    16      848751     2015-06-04 22:42:38 878559     2015-06-05 08:36:20
  1    17      878559     2015-06-05 08:36:20 882947     2015-06-05 10:44:20
  1    18      882947     2015-06-05 10:44:20 883808     2015-06-05 10:57:41
  1    19      883808     2015-06-05 10:57:41 884702     2015-06-05 11:13:08
  1    20      884702     2015-06-05 11:13:08 886070     2015-06-05 11:58:46
  1    21      886070     2015-06-05 11:58:46 892378     2015-06-05 15:22:17
  1    22      892378     2015-06-05 15:22:17 894752     2015-06-05 16:44:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16184   Full    271.73M    DISK        00:01:43     2015-06-05 16:46:35
        BP Key: 16200   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164451
        Piece Name: /u02/backup/2fq8o904_1_1
  List of Datafiles in backup set 16184
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/system01.dbf
  2       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/sysaux01.dbf
  3       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/undotbs01.dbf
  4       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/users01.dbf
  5       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/test01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16213   7.50K      DISK        00:00:00     2015-06-05 16:46:39
        BP Key: 16219   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164639
        Piece Name: /u02/backup/2gq8o93f_1_1

  List of Archived Logs in backup set 16213
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    23      894752     2015-06-05 16:44:19 894834     2015-06-05 16:46:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16228   Full    9.48M      DISK        00:00:02     2015-06-05 16:46:43
        BP Key: 16230   Status: AVAILABLE  Compressed: NO  Tag: TAG20150605T164641
        Piece Name: /u02/backup/c-1644809111-20150605-00
  SPFILE Included: Modification time: 2015-06-05 16:01:17
  SPFILE db_unique_name: DB
  Control File Included: Ckp SCN: 894861       Ckp time: 2015-06-05 16:46:41

2.将备份文件传输到目的主机上

[oracle11@jingyong1 backup]$ scp oracle11@192.168.56.2:/u02/backup/* /u02/backup/
oracle11@192.168.56.2's password:
29q8o8v6_1_1                                                                                                                         100%   17MB  16.8MB/s   00:01
2aq8o8vd_1_1                                                                                                                         100%  947KB 947.0KB/s   00:00
2bq8o8ve_1_1                                                                                                                         100% 2916KB   2.9MB/s   00:00
2cq8o8vf_1_1                                                                                                                         100% 5504KB   5.4MB/s   00:01
2dq8o8vi_1_1                                                                                                                         100% 1992KB   1.9MB/s   00:00
2eq8o8vk_1_1                                                                                                                         100%   26MB  12.8MB/s   00:02
2fq8o904_1_1                                                                                                                         100%  272MB   8.5MB/s   00:32
2gq8o93f_1_1                                                                                                                         100% 8192     8.0KB/s   00:00
c-1644809111-20150605-00                                                                                                             100% 9728KB   9.5MB/s   00:00
[oracle11@jingyong1 backup]$ ls -lrt
total 343140
-rw-r----- 1 oracle11 oinstall   2985472 Jun  5 17:07 2bq8o8ve_1_1
-rw-r----- 1 oracle11 oinstall    969728 Jun  5 17:07 2aq8o8vd_1_1
-rw-r----- 1 oracle11 oinstall  17567232 Jun  5 17:07 29q8o8v6_1_1
-rw-r----- 1 oracle11 oinstall   2039296 Jun  5 17:07 2dq8o8vi_1_1
-rw-r----- 1 oracle11 oinstall   5636096 Jun  5 17:07 2cq8o8vf_1_1
-rw-r----- 1 oracle11 oinstall  26888192 Jun  5 17:07 2eq8o8vk_1_1
-rw-r----- 1 oracle11 oinstall   9961472 Jun  5 17:07 c-1644809111-20150605-00
-rw-r----- 1 oracle11 oinstall      8192 Jun  5 17:07 2gq8o93f_1_1
-rw-r----- 1 oracle11 oinstall 284934144 Jun  5 17:07 2fq8o904_1_1

3.创建辅助实例的密码文件(这里辅助实例名为dup) ,在目的主机上为辅助实例创建密码文件可以有以下选项:
.手动创建密码文件,对于duplicate … from active database有额外的要求。必须使用SYS用户ID并且密码必须与原数据库的密码相匹配。当想要使用单独的密码来创建密码文件时因此可以启动辅助实例并使用它来连接原数据库。

.在执行duplicate … from active database命令时指定password file选项,在这种情况下,RMAN将原数据库的密码文件复制到目的主机上并且覆盖辅助实例已经存在的密码文件。如果原数据库密码文件有多个密码且你想让它们在副本数据库中使用时这种技术是非常有用的。

[oracle11@jingyong1 dbs]$ orapwd file=/u03/app/oracle/11.2.0/db/dbs/orapwdup password=system entries=10;

[oracle11@jingyong1 dbs]$ ls -lrt orapwdup
-rw-r----- 1 oracle11 oinstall 2560 Jun  5 09:40 orapwdup

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

[oracle11@jingyong1 admin]$ vi listener.ora


# listener.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u03/app/oracle/11.2.0/db)
    )
  )

[oracle11@jingyong1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUN-2015 11:25:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u03/app/oracle/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Log messages written to /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                05-JUN-2015 11:25:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Listener Log File         /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cs" has 1 instance(s).
  Instance "cs", status UNKNOWN, has 1 handler(s) for this service...
Service "dup" has 1 instance(s).
  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

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

[oracle11@jingyong1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

5.创建辅助实例的参数文件,初始化参数文件的目录和内容依赖于给复制文件命名选择的策略。可以选择建议的技术来对原主机和目的主机使用相同的命名策略。因此对于Oracle RAC环境,对于原主机和目的主机可以使用相同的ORACLE_SID。在参数文件中将db_name设置为任意值,db_name是唯一必须设置的参数。创建参数文件有以下选项:
.为辅助实例来创建文本参数文件,初始化参数文件的存储目录是在主机上操作系统特定的缺省目录。例如,在Linux和UNIX中缺省参数文件名是ORACLE_HOME/dbs/initORACLE_SID.ora,在Windows平台上参数文件名是ORACLE_HOME\database\initORACLE_SID.ora

.在执行duplicate命令时指定spfile子句。duplicate … spfile技术最简单,因为在执行复制时RMAN自动将原数据库的spfile文件复制到辅助实例或从备份中还原spfile文件。如果在辅助实例上存在spfile文件,那么RMAN就会覆盖它。

[oracle11@jingyong1 dbs]$ vi initdup.ora
db_name=dup
db_unique_name=dup
control_files= /u03/app/oracle/oradata/db/control01.ctl
remote_login_passwordfile=exclusive
compatible = 11.2.0.4.0
db_block_size=8192
sga_target=300M
sga_max_size=300M
pga_aggregate_target=32M

6.启动辅助实例,启动SQL*Plus并使用sysdba权限连接到辅助实例。将辅助实例启动到nomount状态(如果参数文件在缺省目录中startup命令不需要pfile参数)。注意:确保辅助实例使用文本参数文件来启动而不是SPFILE参数文件。不要创建控制文件或试图mount或open辅助实例。

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

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 09:58:16 2015

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1364340 bytes
Variable Size             104861324 bytes
Database Buffers          201326592 bytes
Redo Buffers                6307840 bytes

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

[oracle11@oracle11g 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)
    )
  )

[oracle11@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 10:05:14 2015

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

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

8.加载或打开目标数据库,如果RMAN连接到原数据库作为目标数据库,那么原数据库为了执行复制必须设置为合适的状态。如果原数据库实例没有mount或open,那么将原数据库mount或open。如果执行active database复制,那么确保满足下面额外的条件:
-如果原数据库open,那么必须启用归档
-如果原数据库没有open,那么数据库不需要执行实例恢复
由于原数据库启用了归档所以可以将原数据库启动到open状态


[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 08:35:45 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1365068 bytes
Variable Size             310381492 bytes
Database Buffers          104857600 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.

9.执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例。如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。复制命令没有指定数据库名。使用database关键字而不指定数据库名,duplicate将从备份中获得数据库名和DBID。如果在backup location目录中一个数据库有多个备份将会显示错误。使用backup子句来识别没有目标数据库和恢复目录的复制类型。until time选项用来指定恢复目标时间,它是唯一能与backup location子句一起使用的选项。因为副本数据库和原数据库文件的文件名相同所以要指定nofilenamecheck选项:

[oracle11@oracle11g ~]$ rman auxiliary sys/system@dup

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 19:44:22 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database to dup until time '2015-06-05 16:46:37'backup location '/u02/backup'  nofilenamecheck;

Starting Duplicate Db at 2015-06-05 19:44:29

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u02/backup/snapcf_db.f';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

Starting restore at 2015-06-05 19:44:45
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
output file name=/u03/app/oracle/oradata/db/control01.ctl
Finished restore at 2015-06-05 19:44:47

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   set until scn  894791;
   set newname for datafile  1 to
 "/u03/app/oracle/oradata/db/system01.dbf";
   set newname for datafile  2 to
 "/u03/app/oracle/oradata/db/sysaux01.dbf";
   set newname for datafile  3 to
 "/u03/app/oracle/oradata/db/undotbs01.dbf";
   set newname for datafile  4 to
 "/u03/app/oracle/oradata/db/users01.dbf";
   set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2015-06-05 19:44:52
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/db/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/db/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/db/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/db/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/2fq8o904_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 2015-06-05 19:47:09

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

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=881610429 file name=/u03/app/oracle/oradata/db/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=881610429 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=881610429 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=881610429 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=881610429 file name=/u03/app/oracle/oradata/db/test01.dbf
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

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


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

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/db/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/sysaux01.dbf RECID=1 STAMP=881610446
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/undotbs01.dbf RECID=2 STAMP=881610446
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/users01.dbf RECID=3 STAMP=881610446
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/test01.dbf RECID=4 STAMP=881610446

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881610446 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881610446 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881610446 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=881610446 file name=/u03/app/oracle/oradata/db/test01.dbf

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

database opened
Finished Duplicate Db at 2015-06-05 19:47:49

oracle 11g duplicate database基于备份复制数据库(四)

不使用目标数据库的基于备份的复制,下面测试将原数据库使用备份复制到远程主机不同目录
1.对原数据库生成备份

RMAN> backup as compressed backupset database plus archivelog;


Starting backup at 2015-06-05 16:44:19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:22
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:29
piece handle=/u02/backup/29q8o8v6_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:29
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:30
piece handle=/u02/backup/2aq8o8vd_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:30
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:31
piece handle=/u02/backup/2bq8o8ve_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:31
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:34
piece handle=/u02/backup/2cq8o8vf_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:34
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:35
piece handle=/u02/backup/2dq8o8vi_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
input archived log thread=1 sequence=10 RECID=29 STAMP=881274168
input archived log thread=1 sequence=11 RECID=30 STAMP=881309835
input archived log thread=1 sequence=12 RECID=31 STAMP=881310058
input archived log thread=1 sequence=13 RECID=32 STAMP=881310212
input archived log thread=1 sequence=14 RECID=33 STAMP=881396520
input archived log thread=1 sequence=15 RECID=34 STAMP=881534563
input archived log thread=1 sequence=16 RECID=35 STAMP=881570186
input archived log thread=1 sequence=17 RECID=36 STAMP=881577860
input archived log thread=1 sequence=18 RECID=37 STAMP=881578662
input archived log thread=1 sequence=19 RECID=38 STAMP=881579588
input archived log thread=1 sequence=20 RECID=39 STAMP=881582326
input archived log thread=1 sequence=21 RECID=40 STAMP=881594537
input archived log thread=1 sequence=22 RECID=41 STAMP=881599459
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:36
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:51
piece handle=/u02/backup/2eq8o8vk_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-06-05 16:44:51

Starting backup at 2015-06-05 16:44:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:52
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:37
piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 2015-06-05 16:46:37

Starting backup at 2015-06-05 16:46:37
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=42 STAMP=881599598
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:46:39
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:40
piece handle=/u02/backup/2gq8o93f_1_1 tag=TAG20150605T164639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 16:46:40

Starting Control File and SPFILE Autobackup at 2015-06-05 16:46:40
piece handle=/u02/backup/c-1644809111-20150605-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-06-05 16:46:44

RMAN> list backup;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16127   946.50K    DISK        00:00:00     2015-06-05 16:44:29
        BP Key: 16133   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2aq8o8vd_1_1

  List of Archived Logs in backup set 16127
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1       703711     2015-06-01 10:49:47 704659     2015-06-01 11:05:07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16129   5.37M      DISK        00:00:01     2015-06-05 16:44:32
        BP Key: 16135   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2cq8o8vf_1_1

  List of Archived Logs in backup set 16129
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    2       704659     2015-06-01 11:05:07 705159     2015-06-01 11:07:43
  1    3       705159     2015-06-01 11:07:43 705443     2015-06-01 11:12:21
  1    4       705443     2015-06-01 11:12:21 707134     2015-06-01 11:23:07
  1    5       707134     2015-06-01 11:23:07 707879     2015-06-01 11:30:45
  1    6       707879     2015-06-01 11:30:45 707956     2015-06-01 11:33:00
  1    7       707956     2015-06-01 11:33:00 739261     2015-06-01 21:55:54

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16131   25.64M     DISK        00:00:08     2015-06-05 16:44:44
        BP Key: 16137   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2eq8o8vk_1_1

  List of Archived Logs in backup set 16131
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    8       739261     2015-06-01 21:55:54 746801     2015-06-01 22:05:12
  1    9       746801     2015-06-01 22:05:12 750015     2015-06-01 22:09:21
  1    10      750015     2015-06-01 22:09:21 751675     2015-06-01 22:22:48
  1    11      751675     2015-06-01 22:22:48 773357     2015-06-02 08:17:13
  1    12      773357     2015-06-02 08:17:13 773904     2015-06-02 08:20:58
  1    13      773904     2015-06-02 08:20:58 773991     2015-06-02 08:23:32
  1    14      773991     2015-06-02 08:23:32 811036     2015-06-03 08:21:54
  1    15      811036     2015-06-03 08:21:54 848751     2015-06-04 22:42:38
  1    16      848751     2015-06-04 22:42:38 878559     2015-06-05 08:36:20
  1    17      878559     2015-06-05 08:36:20 882947     2015-06-05 10:44:20
  1    18      882947     2015-06-05 10:44:20 883808     2015-06-05 10:57:41
  1    19      883808     2015-06-05 10:57:41 884702     2015-06-05 11:13:08
  1    20      884702     2015-06-05 11:13:08 886070     2015-06-05 11:58:46
  1    21      886070     2015-06-05 11:58:46 892378     2015-06-05 15:22:17
  1    22      892378     2015-06-05 15:22:17 894752     2015-06-05 16:44:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16184   Full    271.73M    DISK        00:01:43     2015-06-05 16:46:35
        BP Key: 16200   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164451
        Piece Name: /u02/backup/2fq8o904_1_1
  List of Datafiles in backup set 16184
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/system01.dbf
  2       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/sysaux01.dbf
  3       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/undotbs01.dbf
  4       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/users01.dbf
  5       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/test01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16213   7.50K      DISK        00:00:00     2015-06-05 16:46:39
        BP Key: 16219   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164639
        Piece Name: /u02/backup/2gq8o93f_1_1

  List of Archived Logs in backup set 16213
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    23      894752     2015-06-05 16:44:19 894834     2015-06-05 16:46:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16228   Full    9.48M      DISK        00:00:02     2015-06-05 16:46:43
        BP Key: 16230   Status: AVAILABLE  Compressed: NO  Tag: TAG20150605T164641
        Piece Name: /u02/backup/c-1644809111-20150605-00
  SPFILE Included: Modification time: 2015-06-05 16:01:17
  SPFILE db_unique_name: DB
  Control File Included: Ckp SCN: 894861       Ckp time: 2015-06-05 16:46:41

2.将备份文件传输到目的主机上

[oracle11@jingyong1 backup]$ scp oracle11@192.168.56.2:/u02/backup/* /u02/backup/
oracle11@192.168.56.2's password:
29q8o8v6_1_1                                                                                                                         100%   17MB  16.8MB/s   00:01
2aq8o8vd_1_1                                                                                                                         100%  947KB 947.0KB/s   00:00
2bq8o8ve_1_1                                                                                                                         100% 2916KB   2.9MB/s   00:00
2cq8o8vf_1_1                                                                                                                         100% 5504KB   5.4MB/s   00:01
2dq8o8vi_1_1                                                                                                                         100% 1992KB   1.9MB/s   00:00
2eq8o8vk_1_1                                                                                                                         100%   26MB  12.8MB/s   00:02
2fq8o904_1_1                                                                                                                         100%  272MB   8.5MB/s   00:32
2gq8o93f_1_1                                                                                                                         100% 8192     8.0KB/s   00:00
c-1644809111-20150605-00                                                                                                             100% 9728KB   9.5MB/s   00:00
[oracle11@jingyong1 backup]$ ls -lrt
total 343140
-rw-r----- 1 oracle11 oinstall   2985472 Jun  5 17:07 2bq8o8ve_1_1
-rw-r----- 1 oracle11 oinstall    969728 Jun  5 17:07 2aq8o8vd_1_1
-rw-r----- 1 oracle11 oinstall  17567232 Jun  5 17:07 29q8o8v6_1_1
-rw-r----- 1 oracle11 oinstall   2039296 Jun  5 17:07 2dq8o8vi_1_1
-rw-r----- 1 oracle11 oinstall   5636096 Jun  5 17:07 2cq8o8vf_1_1
-rw-r----- 1 oracle11 oinstall  26888192 Jun  5 17:07 2eq8o8vk_1_1
-rw-r----- 1 oracle11 oinstall   9961472 Jun  5 17:07 c-1644809111-20150605-00
-rw-r----- 1 oracle11 oinstall      8192 Jun  5 17:07 2gq8o93f_1_1
-rw-r----- 1 oracle11 oinstall 284934144 Jun  5 17:07 2fq8o904_1_1

3.创建辅助实例的密码文件(这里辅助实例名为dup) ,在目的主机上为辅助实例创建密码文件可以有以下选项:
.手动创建密码文件,对于duplicate … from active database有额外的要求。必须使用SYS用户ID并且密码必须与原数据库的密码相匹配。当想要使用单独的密码来创建密码文件时因此可以启动辅助实例并使用它来连接原数据库。

.在执行duplicate … from active database命令时指定password file选项,在这种情况下,RMAN将原数据库的密码文件复制到目的主机上并且覆盖辅助实例已经存在的密码文件。如果原数据库密码文件有多个密码且你想让它们在副本数据库中使用时这种技术是非常有用的。

[oracle11@jingyong1 dbs]$ orapwd file=/u03/app/oracle/11.2.0/db/dbs/orapwdup password=system entries=10;

[oracle11@jingyong1 dbs]$ ls -lrt orapwdup
-rw-r----- 1 oracle11 oinstall 2560 Jun  5 09:40 orapwdup

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

[oracle11@jingyong1 admin]$ vi listener.ora


# listener.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u03/app/oracle/11.2.0/db)
    )
  )

[oracle11@jingyong1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUN-2015 11:25:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u03/app/oracle/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Log messages written to /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                05-JUN-2015 11:25:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Listener Log File         /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cs" has 1 instance(s).
  Instance "cs", status UNKNOWN, has 1 handler(s) for this service...
Service "dup" has 1 instance(s).
  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

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

[oracle11@jingyong1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

5.创建辅助实例的参数文件,初始化参数文件的目录和内容依赖于给复制文件命名选择的策略。可以选择建议的技术来对原主机和目的主机使用相同的命名策略。因此对于Oracle RAC环境,对于原主机和目的主机可以使用相同的ORACLE_SID。在参数文件中将db_name设置为任意值,db_name是唯一必须设置的参数。创建参数文件有以下选项:
.为辅助实例来创建文本参数文件,初始化参数文件的存储目录是在主机上操作系统特定的缺省目录。例如,在Linux和UNIX中缺省参数文件名是ORACLE_HOME/dbs/initORACLE_SID.ora,在Windows平台上参数文件名是ORACLE_HOME\database\initORACLE_SID.ora

.在执行duplicate命令时指定spfile子句。duplicate … spfile技术最简单,因为在执行复制时RMAN自动将原数据库的spfile文件复制到辅助实例或从备份中还原spfile文件。如果在辅助实例上存在spfile文件,那么RMAN就会覆盖它。

[oracle11@jingyong1 dbs]$ vi initdup.ora
db_name=dup
db_unique_name=dup
control_files= /u03/app/oracle/oradata/dup/control01.ctl
remote_login_passwordfile=exclusive
compatible = 11.2.0.4.0
db_block_size=8192
sga_target=300M
sga_max_size=300M
pga_aggregate_target=32M
db_file_name_convert=('/u03/app/oracle/oradata/db/','/u03/app/oracle/oradata/dup/')
log_file_name_convert=('/u03/app/oracle/oradata/db/','/u03/app/oracle/oradata/dup/')

6.启动辅助实例,启动SQL*Plus并使用sysdba权限连接到辅助实例。将辅助实例启动到nomount状态(如果参数文件在缺省目录中startup命令不需要pfile参数)。注意:确保辅助实例使用文本参数文件来启动而不是SPFILE参数文件。不要创建控制文件或试图mount或open辅助实例。

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

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 09:58:16 2015

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1364340 bytes
Variable Size             104861324 bytes
Database Buffers          201326592 bytes
Redo Buffers                6307840 bytes

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

[oracle11@oracle11g 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)
    )
  )

[oracle11@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 10:05:14 2015

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

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

8.加载或打开目标数据库,如果RMAN连接到原数据库作为目标数据库,那么原数据库为了执行复制必须设置为合适的状态。如果原数据库实例没有mount或open,那么将原数据库mount或open。如果执行active database复制,那么确保满足下面额外的条件:
-如果原数据库open,那么必须启用归档
-如果原数据库没有open,那么数据库不需要执行实例恢复
由于原数据库启用了归档所以可以将原数据库启动到open状态


[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 08:35:45 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1365068 bytes
Variable Size             310381492 bytes
Database Buffers          104857600 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.

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

[oracle11@oracle11g ~]$ rman  catalog rman/rman@cs auxiliary sys/system@dup

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 19:29:07 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database db to dup   until time '2015-06-05 16:46:37'  nofilenamecheck;

Starting Duplicate Db at 2015-06-05 19:29:21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   set until scn  894834;
   sql clone "alter system set  db_name =
 ''DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

Starting restore at 2015-06-05 19:29:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input file name=/u03/app/oracle/11.2.0/db/dbs/snapcf_db.f
output file name=/u03/app/oracle/oradata/dup/control01.ctl
Finished restore at 2015-06-05 19:29:39

database mounted

contents of Memory Script:
{
   set until scn  894834;
   set newname for datafile  1 to
 "/u03/app/oracle/oradata/dup/system01.dbf";
   set newname for datafile  2 to
 "/u03/app/oracle/oradata/dup/sysaux01.dbf";
   set newname for datafile  3 to
 "/u03/app/oracle/oradata/dup/undotbs01.dbf";
   set newname for datafile  4 to
 "/u03/app/oracle/oradata/dup/users01.dbf";
   set newname for datafile  5 to
 "/u03/app/oracle/oradata/dup/test01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2015-06-05 19:29:43
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/dup/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/dup/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/dup/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/dup/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/dup/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/2fq8o904_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at 2015-06-05 19:32:19

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

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=881609539 file name=/u03/app/oracle/oradata/dup/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=881609539 file name=/u03/app/oracle/oradata/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=881609539 file name=/u03/app/oracle/oradata/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=881609539 file name=/u03/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=881609540 file name=/u03/app/oracle/oradata/dup/test01.dbf

contents of Memory Script:
{
   set until time  "to_date('JUN 05 2015 16:46:37', 'MON DD YYYY HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-06-05 19:32:21
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=23
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/2gq8o93f_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/2gq8o93f_1_1 tag=TAG20150605T164639
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/app/oracle/11.2.0/db/dbs/arch1_23_881232587.dbf thread=1 sequence=23
channel clone_default: deleting archived log(s)
archived log file name=/u03/app/oracle/11.2.0/db/dbs/arch1_23_881232587.dbf RECID=43 STAMP=881609544
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-06-05 19:32:27
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

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


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

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/dup/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/sysaux01.dbf RECID=1 STAMP=881609566
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/undotbs01.dbf RECID=2 STAMP=881609566
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/users01.dbf RECID=3 STAMP=881609566
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/dup/test01.dbf RECID=4 STAMP=881609566

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881609566 file name=/u03/app/oracle/oradata/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881609566 file name=/u03/app/oracle/oradata/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881609566 file name=/u03/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=881609566 file name=/u03/app/oracle/oradata/dup/test01.dbf

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

database opened
Finished Duplicate Db at 2015-06-05 19:33:11

oracle 11g duplicate database基于备份复制数据库(三)

不使用目标数据库的基于备份的复制,下面测试将原数据库使用备份复制到远程主机相同目录
1.对原数据库生成备份

RMAN> backup as compressed backupset database plus archivelog;


Starting backup at 2015-06-05 16:44:19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:22
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:29
piece handle=/u02/backup/29q8o8v6_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:29
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:30
piece handle=/u02/backup/2aq8o8vd_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:30
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:31
piece handle=/u02/backup/2bq8o8ve_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:31
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:34
piece handle=/u02/backup/2cq8o8vf_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:34
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:35
piece handle=/u02/backup/2dq8o8vi_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
input archived log thread=1 sequence=10 RECID=29 STAMP=881274168
input archived log thread=1 sequence=11 RECID=30 STAMP=881309835
input archived log thread=1 sequence=12 RECID=31 STAMP=881310058
input archived log thread=1 sequence=13 RECID=32 STAMP=881310212
input archived log thread=1 sequence=14 RECID=33 STAMP=881396520
input archived log thread=1 sequence=15 RECID=34 STAMP=881534563
input archived log thread=1 sequence=16 RECID=35 STAMP=881570186
input archived log thread=1 sequence=17 RECID=36 STAMP=881577860
input archived log thread=1 sequence=18 RECID=37 STAMP=881578662
input archived log thread=1 sequence=19 RECID=38 STAMP=881579588
input archived log thread=1 sequence=20 RECID=39 STAMP=881582326
input archived log thread=1 sequence=21 RECID=40 STAMP=881594537
input archived log thread=1 sequence=22 RECID=41 STAMP=881599459
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:36
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:44:51
piece handle=/u02/backup/2eq8o8vk_1_1 tag=TAG20150605T164421 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2015-06-05 16:44:51

Starting backup at 2015-06-05 16:44:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:44:52
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:37
piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 2015-06-05 16:46:37

Starting backup at 2015-06-05 16:46:37
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=42 STAMP=881599598
channel ORA_DISK_1: starting piece 1 at 2015-06-05 16:46:39
channel ORA_DISK_1: finished piece 1 at 2015-06-05 16:46:40
piece handle=/u02/backup/2gq8o93f_1_1 tag=TAG20150605T164639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 16:46:40

Starting Control File and SPFILE Autobackup at 2015-06-05 16:46:40
piece handle=/u02/backup/c-1644809111-20150605-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-06-05 16:46:44

RMAN> list backup;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16127   946.50K    DISK        00:00:00     2015-06-05 16:44:29
        BP Key: 16133   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2aq8o8vd_1_1

  List of Archived Logs in backup set 16127
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1       703711     2015-06-01 10:49:47 704659     2015-06-01 11:05:07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16129   5.37M      DISK        00:00:01     2015-06-05 16:44:32
        BP Key: 16135   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2cq8o8vf_1_1

  List of Archived Logs in backup set 16129
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    2       704659     2015-06-01 11:05:07 705159     2015-06-01 11:07:43
  1    3       705159     2015-06-01 11:07:43 705443     2015-06-01 11:12:21
  1    4       705443     2015-06-01 11:12:21 707134     2015-06-01 11:23:07
  1    5       707134     2015-06-01 11:23:07 707879     2015-06-01 11:30:45
  1    6       707879     2015-06-01 11:30:45 707956     2015-06-01 11:33:00
  1    7       707956     2015-06-01 11:33:00 739261     2015-06-01 21:55:54

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16131   25.64M     DISK        00:00:08     2015-06-05 16:44:44
        BP Key: 16137   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164421
        Piece Name: /u02/backup/2eq8o8vk_1_1

  List of Archived Logs in backup set 16131
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    8       739261     2015-06-01 21:55:54 746801     2015-06-01 22:05:12
  1    9       746801     2015-06-01 22:05:12 750015     2015-06-01 22:09:21
  1    10      750015     2015-06-01 22:09:21 751675     2015-06-01 22:22:48
  1    11      751675     2015-06-01 22:22:48 773357     2015-06-02 08:17:13
  1    12      773357     2015-06-02 08:17:13 773904     2015-06-02 08:20:58
  1    13      773904     2015-06-02 08:20:58 773991     2015-06-02 08:23:32
  1    14      773991     2015-06-02 08:23:32 811036     2015-06-03 08:21:54
  1    15      811036     2015-06-03 08:21:54 848751     2015-06-04 22:42:38
  1    16      848751     2015-06-04 22:42:38 878559     2015-06-05 08:36:20
  1    17      878559     2015-06-05 08:36:20 882947     2015-06-05 10:44:20
  1    18      882947     2015-06-05 10:44:20 883808     2015-06-05 10:57:41
  1    19      883808     2015-06-05 10:57:41 884702     2015-06-05 11:13:08
  1    20      884702     2015-06-05 11:13:08 886070     2015-06-05 11:58:46
  1    21      886070     2015-06-05 11:58:46 892378     2015-06-05 15:22:17
  1    22      892378     2015-06-05 15:22:17 894752     2015-06-05 16:44:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16184   Full    271.73M    DISK        00:01:43     2015-06-05 16:46:35
        BP Key: 16200   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164451
        Piece Name: /u02/backup/2fq8o904_1_1
  List of Datafiles in backup set 16184
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/system01.dbf
  2       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/sysaux01.dbf
  3       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/undotbs01.dbf
  4       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/users01.dbf
  5       Full 894791     2015-06-05 16:44:52 /u03/app/oracle/oradata/db/test01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16213   7.50K      DISK        00:00:00     2015-06-05 16:46:39
        BP Key: 16219   Status: AVAILABLE  Compressed: YES  Tag: TAG20150605T164639
        Piece Name: /u02/backup/2gq8o93f_1_1

  List of Archived Logs in backup set 16213
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    23      894752     2015-06-05 16:44:19 894834     2015-06-05 16:46:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16228   Full    9.48M      DISK        00:00:02     2015-06-05 16:46:43
        BP Key: 16230   Status: AVAILABLE  Compressed: NO  Tag: TAG20150605T164641
        Piece Name: /u02/backup/c-1644809111-20150605-00
  SPFILE Included: Modification time: 2015-06-05 16:01:17
  SPFILE db_unique_name: DB
  Control File Included: Ckp SCN: 894861       Ckp time: 2015-06-05 16:46:41

2.将备份文件传输到目的主机上

[oracle11@jingyong1 backup]$ scp oracle11@192.168.56.2:/u02/backup/* /u02/backup/
oracle11@192.168.56.2's password:
29q8o8v6_1_1                                                                                                                         100%   17MB  16.8MB/s   00:01
2aq8o8vd_1_1                                                                                                                         100%  947KB 947.0KB/s   00:00
2bq8o8ve_1_1                                                                                                                         100% 2916KB   2.9MB/s   00:00
2cq8o8vf_1_1                                                                                                                         100% 5504KB   5.4MB/s   00:01
2dq8o8vi_1_1                                                                                                                         100% 1992KB   1.9MB/s   00:00
2eq8o8vk_1_1                                                                                                                         100%   26MB  12.8MB/s   00:02
2fq8o904_1_1                                                                                                                         100%  272MB   8.5MB/s   00:32
2gq8o93f_1_1                                                                                                                         100% 8192     8.0KB/s   00:00
c-1644809111-20150605-00                                                                                                             100% 9728KB   9.5MB/s   00:00
[oracle11@jingyong1 backup]$ ls -lrt
total 343140
-rw-r----- 1 oracle11 oinstall   2985472 Jun  5 17:07 2bq8o8ve_1_1
-rw-r----- 1 oracle11 oinstall    969728 Jun  5 17:07 2aq8o8vd_1_1
-rw-r----- 1 oracle11 oinstall  17567232 Jun  5 17:07 29q8o8v6_1_1
-rw-r----- 1 oracle11 oinstall   2039296 Jun  5 17:07 2dq8o8vi_1_1
-rw-r----- 1 oracle11 oinstall   5636096 Jun  5 17:07 2cq8o8vf_1_1
-rw-r----- 1 oracle11 oinstall  26888192 Jun  5 17:07 2eq8o8vk_1_1
-rw-r----- 1 oracle11 oinstall   9961472 Jun  5 17:07 c-1644809111-20150605-00
-rw-r----- 1 oracle11 oinstall      8192 Jun  5 17:07 2gq8o93f_1_1
-rw-r----- 1 oracle11 oinstall 284934144 Jun  5 17:07 2fq8o904_1_1

3.创建辅助实例的密码文件(这里辅助实例名为dup) ,在目的主机上为辅助实例创建密码文件可以有以下选项:
.手动创建密码文件,对于duplicate … from active database有额外的要求。必须使用SYS用户ID并且密码必须与原数据库的密码相匹配。当想要使用单独的密码来创建密码文件时因此可以启动辅助实例并使用它来连接原数据库。

.在执行duplicate … from active database命令时指定password file选项,在这种情况下,RMAN将原数据库的密码文件复制到目的主机上并且覆盖辅助实例已经存在的密码文件。如果原数据库密码文件有多个密码且你想让它们在副本数据库中使用时这种技术是非常有用的。

[oracle11@jingyong1 dbs]$ orapwd file=/u03/app/oracle/11.2.0/db/dbs/orapwdup password=system entries=10;

[oracle11@jingyong1 dbs]$ ls -lrt orapwdup
-rw-r----- 1 oracle11 oinstall 2560 Jun  5 09:40 orapwdup

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

[oracle11@jingyong1 admin]$ vi listener.ora


# listener.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u03/app/oracle/11.2.0/db)
    )
  )

[oracle11@jingyong1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUN-2015 11:25:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u03/app/oracle/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Log messages written to /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                05-JUN-2015 11:25:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Listener Log File         /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cs" has 1 instance(s).
  Instance "cs", status UNKNOWN, has 1 handler(s) for this service...
Service "dup" has 1 instance(s).
  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

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

[oracle11@jingyong1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

5.创建辅助实例的参数文件,初始化参数文件的目录和内容依赖于给复制文件命名选择的策略。可以选择建议的技术来对原主机和目的主机使用相同的命名策略。因此对于Oracle RAC环境,对于原主机和目的主机可以使用相同的ORACLE_SID。在参数文件中将db_name设置为任意值,db_name是唯一必须设置的参数。创建参数文件有以下选项:
.为辅助实例来创建文本参数文件,初始化参数文件的存储目录是在主机上操作系统特定的缺省目录。例如,在Linux和UNIX中缺省参数文件名是ORACLE_HOME/dbs/initORACLE_SID.ora,在Windows平台上参数文件名是ORACLE_HOME\database\initORACLE_SID.ora

.在执行duplicate命令时指定spfile子句。duplicate … spfile技术最简单,因为在执行复制时RMAN自动将原数据库的spfile文件复制到辅助实例或从备份中还原spfile文件。如果在辅助实例上存在spfile文件,那么RMAN就会覆盖它。

[oracle11@jingyong1 dbs]$ vi initdup.ora
db_name=dup
db_unique_name=dup
control_files= /u03/app/oracle/oradata/db/control01.ctl
remote_login_passwordfile=exclusive
compatible = 11.2.0.4.0
db_block_size=8192
sga_target=300M
sga_max_size=300M
pga_aggregate_target=32M

6.启动辅助实例,启动SQL*Plus并使用sysdba权限连接到辅助实例。将辅助实例启动到nomount状态(如果参数文件在缺省目录中startup命令不需要pfile参数)。注意:确保辅助实例使用文本参数文件来启动而不是SPFILE参数文件。不要创建控制文件或试图mount或open辅助实例。

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

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 09:58:16 2015

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1364340 bytes
Variable Size             104861324 bytes
Database Buffers          201326592 bytes
Redo Buffers                6307840 bytes

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

[oracle11@oracle11g 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)
    )
  )

[oracle11@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 10:05:14 2015

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

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

8.加载或打开目标数据库,如果RMAN连接到原数据库作为目标数据库,那么原数据库为了执行复制必须设置为合适的状态。如果原数据库实例没有mount或open,那么将原数据库mount或open。如果执行active database复制,那么确保满足下面额外的条件:
-如果原数据库open,那么必须启用归档
-如果原数据库没有open,那么数据库不需要执行实例恢复
由于原数据库启用了归档所以可以将原数据库启动到open状态

[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 08:35:45 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1365068 bytes
Variable Size             310381492 bytes
Database Buffers          104857600 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.

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

[oracle11@oracle11g ~]$ rman  catalog rman/rman@cs auxiliary sys/system@dup

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 19:13:12 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database db to dup   until time '2015-06-05 16:46:37'  nofilenamecheck;

Starting Duplicate Db at 2015-06-05 19:13:16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   set until scn  894834;
   sql clone "alter system set  db_name =
 ''DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

Starting restore at 2015-06-05 19:13:29
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input file name=/u03/app/oracle/11.2.0/db/dbs/snapcf_db.f
output file name=/u03/app/oracle/oradata/db/control01.ctl
Finished restore at 2015-06-05 19:13:33

database mounted

contents of Memory Script:
{
   set until scn  894834;
   set newname for datafile  1 to
 "/u03/app/oracle/oradata/db/system01.dbf";
   set newname for datafile  2 to
 "/u03/app/oracle/oradata/db/sysaux01.dbf";
   set newname for datafile  3 to
 "/u03/app/oracle/oradata/db/undotbs01.dbf";
   set newname for datafile  4 to
 "/u03/app/oracle/oradata/db/users01.dbf";
   set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2015-06-05 19:13:37
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/db/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/db/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/db/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/db/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/2fq8o904_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/2fq8o904_1_1 tag=TAG20150605T164451
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 2015-06-05 19:15:54

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

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=881608554 file name=/u03/app/oracle/oradata/db/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=881608554 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=881608554 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=881608554 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=881608554 file name=/u03/app/oracle/oradata/db/test01.dbf

contents of Memory Script:
{
   set until time  "to_date('JUN 05 2015 16:46:37', 'MON DD YYYY HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-06-05 19:15:55
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=23
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/2gq8o93f_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/2gq8o93f_1_1 tag=TAG20150605T164639
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/app/oracle/11.2.0/db/dbs/arch1_23_881232587.dbf thread=1 sequence=23
channel clone_default: deleting archived log(s)
archived log file name=/u03/app/oracle/11.2.0/db/dbs/arch1_23_881232587.dbf RECID=43 STAMP=881608557
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-06-05 19:15:59
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     313860096 bytes

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


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

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/db/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/sysaux01.dbf RECID=1 STAMP=881608574
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/undotbs01.dbf RECID=2 STAMP=881608574
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/users01.dbf RECID=3 STAMP=881608574
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/test01.dbf RECID=4 STAMP=881608574

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881608574 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881608574 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881608574 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=881608574 file name=/u03/app/oracle/oradata/db/test01.dbf

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

database opened
Finished Duplicate Db at 2015-06-05 19:16:35
Proudly powered by WordPress | Indrajeet by Sus Hill.