1

I have a Red Hat Enterprise Linux server with Oracle 10.2.0.5 and a database on it (SID is TNATEST). I have a remote CentOS server with Oracle 10.2.0.5 on it that I want to duplicate TNATEST to. The new database will have the SID TNAPRODB.

I'm fairly sure I have everything set up except for making the backup files available. I took backups using RMAN and transferred them over to my CentOS server, I placed them in the db_recovery_file_dest location.

Here's how I created my backup on the source.

sqlplus / as sysdba
sqlplus> shutdown immediate;
sqlplus> startup mount;
sqlplus> exit;

rman target /
rman> backup database;
rman> list backup;
rman> exit;

Here's the output of list backup:

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    4.10G      DISK        00:00:49     11-APR-14
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1ks60_.bkp
  List of Datafiles in backup set 12
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 26213664896 10-APR-14 /u01/oradata/TNATEST/system01.dbf
  9       Full 26213664896 10-APR-14 /u02/oradata/TNATEST/ac_report02.dbf
  28      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/ifsapp_lob01.dbf
  29      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/ifsapp_lob02.dbf
  42      Full 26213664896 10-APR-14 /u03/oradata/TNATEST/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    2.52G      DISK        00:00:27     11-APR-14
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1mjbd_.bkp
  List of Datafiles in backup set 13
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 26213664896 10-APR-14 /u02/oradata/TNATEST/undotbs01.dbf
  6       Full 26213664896 10-APR-14 /u01/oradata/TNATEST/ac_index01.dbf
  22      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/fnd_data01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    2.10G      DISK        00:00:24     11-APR-14
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1nmgj_.bkp
  List of Datafiles in backup set 14
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 26213664896 10-APR-14 /u01/oradata/TNATEST/sysaux01.dbf
  23      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/fnd_data02.dbf
  24      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/fnd_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15      Full    806.70M    DISK        00:00:11     11-APR-14
        BP Key: 15   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1odmh_.bkp
  List of Datafiles in backup set 15
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 26213664896 10-APR-14 /u01/oradata/TNATEST/ac_data01.dbf
  16      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/dist_data01.dbf
  18      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/dist_index01.dbf
  25      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/fnd_index02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Full    106.88M    DISK        00:00:04     11-APR-14
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1ovtj_.bkp
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 26213664896 10-APR-14 /u02/oradata/TNATEST/ac_data02.dbf
  8       Full 26213664896 10-APR-14 /u01/oradata/TNATEST/ac_report01.dbf
  10      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/cost_data01.dbf
  11      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/cost_data02.dbf
  12      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/cost_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Full    62.44M     DISK        00:00:04     11-APR-14
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1p2vq_.bkp
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 26213664896 10-APR-14 /u02/oradata/TNATEST/ac_index02.dbf
  13      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/cost_index02.dbf
  14      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/cost_report01.dbf
  20      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/dist_report01.dbf
  26      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/fnd_report01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    232.38M    DISK        00:00:05     11-APR-14
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1pb0l_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  15      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/cost_report02.dbf
  17      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/dist_data02.dbf
  30      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/pdm_data01.dbf
  32      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/pdm_index01.dbf
  34      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/pdm_report01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19      Full    58.99M     DISK        00:00:03     11-APR-14
        BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1pk51_.bkp
  List of Datafiles in backup set 19
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/dist_index02.dbf
  21      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/dist_report02.dbf
  27      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/fnd_report02.dbf
  36      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/prod_data01.dbf
  38      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/prod_index01.dbf
  40      Full 26213664896 10-APR-14 /u01/oradata/TNATEST/prod_report01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20      Full    116.58M    DISK        00:00:02     11-APR-14
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1pr9b_.bkp
  List of Datafiles in backup set 20
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  31      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/pdm_data02.dbf
  33      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/pdm_index02.dbf
  35      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/pdm_report02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    21.72M     DISK        00:00:02     11-APR-14
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_nnndf_TAG20140411T111152_9nj1pvdw_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  37      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/prod_data02.dbf
  39      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/prod_index02.dbf
  41      Full 26213664896 10-APR-14 /u02/oradata/TNATEST/prod_report02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    7.20M      DISK        00:00:01     11-APR-14
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20140411T111152
        Piece Name: /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/o1_mf_ncsnf_TAG20140411T111152_9nj1pz4n_.bkp
  Control File Included: Ckp SCN: 26213664896   Ckp time: 10-APR-14
  SPFILE Included: Modification time: 11-APR-14

Then I copy from source to destination server.

scp /u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/*.bkp tnaprodbserver:/u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11/

On the source server, I run the duplicate command...

rman target /
rman> connect auxiliary sys@TNAPRODB
rman> duplicate target database to TNAPRODB DB_FILE_NAME_CONVERT= ('/u01/oradata/TNATEST/','/u01/oradata/TNAPRODB/','/u02/oradata/TNATEST/', '/u02/oradata/TNAPRODB/','/u03/oradata/TNATEST/','/u03/oradata/TNAPRODB/') LOGFILE '/u01/oradata/TNAPRODB/redo011.log' SIZE 60M, '/u01/oradata/TNAPRODB/redo012.log' SIZE 60M, '/u01/oradata/TNAPRODB/redo013.log' SIZE 60M, '/u02/oradata/TNAPRODB/redo021.log' SIZE 60M, '/u02/oradata/TNAPRODB/redo022.log' SIZE 60M, '/u02/oradata/TNAPRODB/redo023.log' SIZE 60M, '/u03/oradata/TNAPRODB/redo031.log' SIZE 60M, '/u03/oradata/TNAPRODB/redo032.log' SIZE 60M, '/u03/oradata/TNAPRODB/redo033.log' SIZE 60M;

But I get errors:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/11/2014 10:02:02
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 42 found to restore
RMAN-06023: no backup or copy of datafile 41 found to restore
RMAN-06023: no backup or copy of datafile 40 found to restore
RMAN-06023: no backup or copy of datafile 39 found to restore
/*all the way to datafile 1 */

How can I make it see the backups which contain the datafiles?

  • How do you start RMAN before duplicating (target, auxiliary parameters)? What output do you get in RMAN from e.g. 'list backup of datafile 1' on the source database? – Balazs Papp Apr 11 '14 at 18:15
  • @BalazsPapp I updated my question to include that information. – mason Apr 11 '14 at 18:43
  • May be you changed the db_unique_name of your database, too and not only the instance name. I experienced a similar behavior on 11.2. This is described here docs.oracle.com/cd/E11882_01/server.112/e41134/…. I wouldn't expect it on 10.2. Maybe Oracle ignores the backups in the recovery area of another database. Nevertheless you can try to overcome the problem by cataloging the backup again by your prod database. So connect to RMAN catalog and TNAPROD with rman and do a CATALOG START WITH '/u03/oracle/flash_recovery_area/TNATEST/backupset/2014_04_11'. – miracle173 Nov 9 '15 at 8:05
1

Ok, so here is how you duplicate a database from backup:

  • make backup (datafiles, archivelogs, controlfile)
  • transfer backup to destination host, to exactly same path, not FRA or anything else (creating backup to a shared location also works)
  • create parameterfile and start the destination instance in NOMOUNT mode
  • rman target sys@source auxiliary sys@destination
  • RMAN> duplicate target database to ...

You don't catalog backups at destination site, because you don't mount the database (controlfile), you don't even have the controlfile there. The controlfile will be restored from the backup, and that controlfile will contain the backup entries, that were transferred to the destination host to the same path as on source.

  • I tried copying to the same path. Did not work. I got the same errors as when I had tried copying to the db_recovery_file_dest location that I showed in my question. Using a shared location isn't an option in my situation. – mason Apr 11 '14 at 18:00
1

you can use rman active duplicate database

RMAN DUPLICATE TARGET DATABASE TO xxxx FROM ACTIVE DATABASE;

but you have two big disadvantages: 1. Negative performance impact on the source database. This impact applies to the whole duplication time. 2. High network traffic on the connection between the source and target databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.