Rman duplicate of database using catalog and tape backup 11gr2 - no target connection

Been a while since my last post but here's a useful tip for when you want to duplicate a target database without actually having to connect to the target database and without having to do a disk backup of the database and copying it to the destination server which all the docs seem to imply:

The following script connects to the auxiliary database - the one you want to create as a copy of your other database and the rman catalog and then restores the database directly from the tape backup.

the oracle docs seem to be wrong (or at least very unclear)

rman rcvcat=rmanschema/password@catalogdb auxiliary=/

run{
    set dbid nnnnnnnnnnn;
    set until time "to_date('22-Jun-2011 08:00:00','dd-mon-yyyy hh24:mi:ss')";
    allocate auxiliary channel ch1 type'SBT_TAPE' parms'ENV=(TDPO_OPTFILE=/tsm/dbname/conf/tdpo.opt)';
    duplicate database 'LIVEDB' to TESTDB;
}

nnnnn is the dbid of the live 'target' you want to restore from

Comments

  1. Anonymous8 November 2011 at 14:21

    Hi
    I am getting the below error since the duplicate database is in nomount mode:


    RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted

    I don't know what i am missing, below is the script:
    rman catalog rmanadmin/xxxxxx@rmancatdb auxiliary /
    set parallelmediarestore off;
    run {
    set dbid 1582918119;
    set until time "to_date('Nov 07 2011 22:00:00','Mon DD YYYY HH24:MI:SS')";
    CONFIGURE auxiliary CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_POLICY=SNA_CRED_MSZ_UNX-ORACLE-HOT-WELLSDB_ANY,NB_ORA_SERV=pisgsna01bmst01.firstamdata.net,NB_ORA_CLIENT=credprboradb9.infosolco.com,NB_ORA_SCHED=weekly)';
    CONFIGURE auxiliary CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_POLICY=SNA_CRED_MSZ_UNX-ORACLE-HOT-WELLSDB_ANY,NB_ORA_SERV=pisgsna01bmst01.firstamdata.net,NB_ORA_CLIENT=credprboradb9.infosolco.com,NB_ORA_SCHED=weekly)';

    duplicate database 'WELLSDB' to 'welsrstr' ;
    }

    ReplyDelete
    Replies
    1. Anonymous3 February 2018 at 19:26

      Hi Rich,
      we are getting the following RMAN-05565: SPFILE backup created before TO_DATE
      when performing the following:
      RMAN> run {
      DUPLICATE DATABASE TO WPSTRN
      2> SPFILE
      3> 4> set control_files='/ora2/oradata/wpstrn/cntrl01.dbf', '/o ra3/oradata/wpstrn/cntrl02.dbf', '/ora2/oradata/wpstrn/cntrl03.dbf'
      set audit_file_dest='/ora1/oracle/wpstrn/12.1.0/rdbms/audit'
      5> 6> set local_listener='WPSTRN_LOCAL'
      7> set diagnostic_dest='/ora1/oracle/wpstrn/12.1.0/admin/WPSTRN _db5ebsdbtrn'
      8> set log_archive_dest_1='LOCATION=/ora3/oradata/wpstrn/archiv e'
      9> set db_file_name_convert='/ora3/oradata/wpsprd','/ora3/orada ta/wpstrn','/ora2/oradata/wpsprd', '/ora2/oradata/wpstrn'
      10> set log_file_name_convert='/ora3/oradata/wpsprd','/ora3/ora data/wpstrn','/ora2/oradata/wpsprd', '/ora2/oradata/wpstrn'
      11> until time "TO_DATE('03-FEB-2018 17:20:16','DD-MON-YYYY HH24: MI:SS')" BACKUP LOCATION '/ora2/oradata/full012818/' NOFILENAMECHECK NORESUME;
      }12>

      Starting Duplicate Db at 03-FEB-2018 20:45:39
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of Duplicate Db command at 02/03/2018 20:45:39
      RMAN-05501: aborting duplication of target database
      RMAN-05565: SPFILE backup created before TO_DATE('03-FEB-2018 17:20:16','DD-MON- YYYY HH24:MI:SS') not found in /ora2/oradata/full012818/

      Delete
    2. DBAHarrison4 February 2018 at 01:08

      Hi,
      Did the original backup contain the spfile (i.e. you added 'including spfile' to the rman command in the backup block?)

      Cheers,
      Rich

      Delete
  2. DBAHarrison9 November 2011 at 03:40

    Hi,
    You need to make sure your auxiliary databases is started up in nomount mode (i.e. you need an spfile) in place so that rman can connect to it.

    So basically:

    create a text pfile )in vi/notepad etc)

    startup nomount

    create spfile from pfile;

    shutdown;

    startup nomount

    then run the rman script

    Note - you must use an spfile on the auxiliary or you weill have problems

    ReplyDelete
  3. Anonymous9 November 2011 at 11:31

    Thanks Rich for responding.

    Per your suggestion, created a spfile and started the auxiliary database in nomount mode and ran the rman script but still get the same error:

    RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted

    Please advise.

    Thanks
    Padhu

    ReplyDelete
  4. DBAHarrison10 November 2011 at 00:05

    Hi Padhu,
    Ok I think i can see what the problem is now. Try replacing the configure command with the allocate command.

    Configure will try and save to the control file - which you don't have.

    rman catalog rmanadmin/xxxxxx@rmancatdb auxiliary /
    set parallelmediarestore off;
    run {
    set dbid 1582918119;
    set until time "to_date('Nov 07 2011 22:00:00','Mon DD YYYY HH24:MI:SS')";
    ALLOCATE auxiliary CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_POLICY=SNA_CRED_MSZ_UNX-ORACLE-HOT-WELLSDB_ANY,NB_ORA_SERV=pisgsna01bmst01.firstamdata.net,NB_ORA_CLIENT=credprboradb9.infosolco.com,NB_ORA_SCHED=weekly)';
    ALLOCATE auxiliary CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_POLICY=SNA_CRED_MSZ_UNX-ORACLE-HOT-WELLSDB_ANY,NB_ORA_SERV=pisgsna01bmst01.firstamdata.net,NB_ORA_CLIENT=credprboradb9.infosolco.com,NB_ORA_SCHED=weekly)';

    duplicate database 'WELLSDB' to 'welsrstr' ;
    }

    Cheers,
    Rich

    ReplyDelete
  5. Anonymous10 November 2011 at 14:29

    You rock Rich!!!.

    Thanks a lot. I am able to restore successfully.

    Thanks
    Padhu

    ReplyDelete
  6. Anonymous6 March 2013 at 12:28

    Is there a way we can do it without even connecting to catalog database?

    ReplyDelete
  7. DBAHarrison6 March 2013 at 23:36

    Hi,
    If you are happy to connect to the target database you can just use duplicate from active database since 11.2.

    Or are you talking about using duplicate with no target connection and no catalog connection?

    Regards,
    Rich

    ReplyDelete
    Replies
    1. Anonymous12 March 2013 at 08:58

      Yes, I was talking about no connection to either catalog database or target database.

      Delete
  8. DBAHarrison12 March 2013 at 09:52

    Hi,
    So you have a backupset on disk/tape somewhere and you want to restore the database from that directly?

    Regards,
    Rich

    ReplyDelete
  9. Anonymous18 March 2013 at 03:23

    HI Rich,

    I need to restore the oracle database 11gR2 to a new server with a new name (Windows platform).
    I try use backup-based location without Target connection and without Catalog connection.

    C:\> rman auxiliary SYS@testdb

    Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 22 20:01:43 2013
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    connected to auxiliary database: ectest (not mounted)

    RMAN> duplicate database to testdb
    until time "to_date('03/15/2013 11:00:00', 'mm/dd/yyyy
    hh24:mi:ss')"
    backup location 'H:\backup
    nofilenamecheck;

    And I get the following similar error:
    RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted

    Please help me !

    Regards, Mike

    ReplyDelete
    Replies
    1. DBAHarrison18 March 2013 at 03:36

      Hi Mike,
      I've never actually tried it without the catalog connection but other posts I've seen suggest that it should work.

      Two things to try/check:

      1) why are you using until_time? - is the backup not from a certain point in time anyway - is this step needed - it might be what is confusing oracle?
      2) is there a controlfile backup included in the h:\backup location?
      3) is there a typo in the h:\backup path - a missing quote ? or is that just an cut and paste error?

      Cheers,
      Rich

      Delete
    2. Anonymous19 March 2013 at 02:17

      Hi Rich,

      Thanks for the quick response.
      1)'until_time' my understanding is that it is possible to restore some of backup to the specified time. Or am I wrong?
      2)I think that the control file is included in backupset
      3)a quote trims when inserting

      I used another option, but also does not work:

      RMAN> duplicate database to testdb
      2> until time "to_date('03/15/2013 11:00:00', 'mm/dd/yyyy hh24:mi:ss')"
      3> spfile
      4> set control_files='E:\backup\CONTROL01.CTL'
      5> set db_file_name_convert='F:\ORACLE\ORADATA\PRODDB\','G:\oracle\oradata\testdb\'
      6> set log_file_name_convert='F:\ORACLE\ORADATA\PRODDB\','G:\oracle\oradata\testdb\'
      7> backup location 'E:\backup'
      8> nofilenamecheck
      9> ;

      Another error appears:
      RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
      Why TARGET ? This Backup-based location, without TARGET and CATALOG.

      Regards, Mike



      Delete
    3. Anonymous19 March 2013 at 03:19

      Hi Rich,
      I send another variant:

      C:\>rman auxiliary /

      Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 19 14:52:09 2013

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

      connected to auxiliary database: TESTDB (not mounted)

      RMAN> duplicate database to TESTDB
      2> until time "to_date('03/15/2013 11:00:00', 'mm/dd/yyyy hh24:mi:ss')"
      3> spfile
      4> set control_files='G:\oracle\oradata\ectest\control01.ctl','G:\oracle\oradata\ectest\control02.ctl'
      5> set db_file_name_convert='H:\ORACLE\ORADATA\PRODDB\','G:\oracle\oradata\testdb\'
      6> set log_file_name_convert='H:\ORACLE\ORADATA\PRODDB\','G:\oracle\oradata\testdb\'
      7> backup location 'E:\backup'
      8> nofilenamecheck
      9> ;

      Starting Duplicate Db at 19-MAR-13
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of Duplicate Db command at 03/19/2013 14:57:54
      RMAN-05501: aborting duplication of target database
      RMAN-05565: SPFILE backup created before to_date('03/15/2013 11:00:00', 'mm/dd/yyyy hh24:mi:ss') not found in 'E:\backup

      RMAN>

      thanks in advance
      Regards, Mike

      Delete
  10. DBAHarrison19 March 2013 at 12:00

    Hi Mike,
    i still think the until-time is the problem and i don't think it is necessary?

    have you tried without it?

    When you create the backup in the first place how are you doing that - is it just a single database backup located there?

    so something like

    backup database include archivelog format 'H:\backup\';

    so there is only actually one backup there and you can only go to one point in time anyway?


    Cheers,
    Rich

    ReplyDelete
  11. Anonymous21 March 2013 at 04:32

    Hi Rich,

    I tried without it but got another error:

    RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
    uence 1732 and starting SCN of 122054424

    Thanks
    Regards, Mike

    ReplyDelete
    Replies
    1. DBAHarrison22 March 2013 at 10:04

      Hi Mike,
      What backup command did you use in the first place to create the backup in H:\ ?

      How does archive log 1732 relate in time to that backup you took - is it afterwards - i.e. is the duplicate trying to roll forward to current time?

      Did you give the backup a tag - perhaps we can use that to restore to?

      Cheers,
      Rich

      Delete
    2. Anonymous23 March 2013 at 04:56

      Hi Rich,
      I have everything resolved, I forgot to put in the H:\Backup folder controlfile backup separately. First in the folder was only BACRUPSET and ARCHIVELOG, I thought backap of controlfile take from backupset, and it does not find the folder, so I decided to put it in a as separate file in the folder. After that, everything works.
      Thank you, very much !
      Regards, Mike

      Delete
  12. Anonymous21 May 2013 at 16:57

    Hi Rich, thanks for great article..any idea with this one ?
    RMAN> run {
    set until time= "to_date('21/05/2013 03:37:00','dd/mm/yyyy hh24:mi:ss')";
    DUPLICATE TARGET DATABASE TO "CLONE"
    BACKUP LOCATION '/u01/data/gdbtest2/'
    PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
    NOFILENAMECHECK;
    }
    2> 3> 4> 5> 6> 7>
    executing command: SET until clause

    Starting Duplicate Db at 22-05-2013 00:48
    allocated channel: ORA_AUX_SBT_TAPE_1
    channel ORA_AUX_SBT_TAPE_1: SID=686 device type=SBT_TAPE
    channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
    allocated channel: ORA_AUX_SBT_TAPE_2
    channel ORA_AUX_SBT_TAPE_2: SID=800 device type=SBT_TAPE
    channel ORA_AUX_SBT_TAPE_2: Data Protection for Oracle: version 6.3.0.0
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=914 device type=DISK
    allocated channel: ORA_AUX_DISK_2
    channel ORA_AUX_DISK_2: SID=1028 device type=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 05/22/2013 00:48:04
    RMAN-05501: aborting duplication of target database
    RMAN-06136: ORACLE error from auxiliary database: ORA-00907

    ReplyDelete
    Replies
    1. DBAHarrison22 May 2013 at 00:18

      Hi,
      I think the porblem might be you have an = sign where you don;t need it - can you remove the one in the set until time line?

      So change

      set until time= "to_date('21/05/2013 03:37:00','dd/mm/yyyy hh24:mi:ss')";

      to

      set until time "to_date('21/05/2013 03:37:00','dd/mm/yyyy hh24:mi:ss')";

      Cheers,
      Rich

      Delete
    2. Anonymous22 May 2013 at 03:21

      I did some testing already, but it's still failing - please see these. Thanks Jan

      ---- TESTING:


      export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'
      set |grep NLS

      NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI'
      _='NLS_DATE_FORMAT=DD-MM-YYYY HH24:MI:SS'

      run {
      set until time= "to_date('21-05-2013 03:37:15','DD-MM-YYYY HH24:MI:SS')";
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }
      >>> ORA-00907

      run {
      set until time "to_date('21-05-2013 03:37:15','DD-MM-YYYY HH24:MI:SS')";
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }
      >>> ORA-00907

      RMAN> run {
      set until time=to_date('21-05-2013 03:37:15');
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }
      2>
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00558: error encountered while parsing input commands
      RMAN-01009: syntax error: found "identifier": expecting one of: "double-quoted-string, single-quoted-string"
      RMAN-01008: the bad identifier was: to_date
      RMAN-01007: at line 3 column 16 file: standard input

      >>> but starts full recovery until the LAST backup! we need point in time recovery!

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

      unset NLS_DATE_FORMAT
      set |grep NLS


      run {
      set dbid=387769350
      set until time= "to_date('01/03/2013 17:00:00','dd/mm/yyyy hh24:mi:ss')";
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }

      -----sql "alter session set nls_date_format
      run {
      set dbid=387769350
      sql "alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS'";
      set until time= "to_date('21-05-2013 03:37:15','DD-MM-YYYY HH24:MI:SS')";
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }
      RMAN-03009: failure of sql command on unknown channel at 05/22/2013 01:35:08
      RMAN-06171: not connected to target database

      ------------ SET UNTIL SCN +1
      run {
      set dbid=387769350
      SET UNTIL SCN 8435873;
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }
      RMAN-06136: ORACLE error from auxiliary database: ORA-01194:


      set parallelmediarestore off;
      RMAN-06136

      Delete
    3. DBAHarrison22 May 2013 at 04:54

      Hi Jan,
      Have you tried:

      export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'

      run {
      set dbid=387769350
      set until '01-03-2013 17:00:00';
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }

      If that doesnt work try this inside rman before you execute the run block:

      sql "alter session set nls_date_format = 'DD-MM-YYYY:HH24:MI:SS'";
      run {
      set dbid=387769350
      set until '01-03-2013 17:00:00';
      DUPLICATE TARGET DATABASE TO "CLONE"
      BACKUP LOCATION '/u01/data/gdbtest2/'
      PFILE=/u01/app/product/11.2.0/db_1/dbs/initclone.ora
      NOFILENAMECHECK;
      }

      Regards,
      Rich

      Delete
  13. Unknown22 May 2013 at 12:51

    Set both NLS_LANG and NLS_DATE_FORMAT. NLS_LANG is not used, but it is required. I think it is a bug.
    I export to the environment before running my rman script:
    export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
    export NLS_LANG=AMERICAN.WE8ISO8859P1

    Then in the rman run block:
    set until time = '2013-05-01 08:01:01';

    Blake

    ReplyDelete
    Replies
    1. DBAHarrison23 May 2013 at 00:54

      Cheers Blake - it does explicitly say to set NLS_LANG in the rman docs but i never really thought it was used - lke you say maybe a bug (or a feature....)

      http://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmrecov.htm

      Cheers,
      Rich

      Delete
  14. Jan27 May 2013 at 07:45

    Blake +1! thanks mate! I wish you a great fish on your next trip :)

    export NLS_LANG=american was enough to overcome the silly error!

    ReplyDelete

Post a Comment