No Same Machine Create a Duplicate Database with NOCATALOG

  This document describes setup of a duplicate database on same node
  using RMAN without a catalog. This allows creation of a duplicate database
from the backups of a target database (either backup sets or image copies).

  Duplicate creates a new database by restoring the backup. The duplicate
  database has a different DBID  so that it can also be registered with the
  recovery catalog.


SCOPE & APPLICATION
-------------------
  In this document you find:
  1) prerequisites
  2) init.ora configuration duplicate and passwordfile
  3) Setup sqlnet configuration
  4) Making a backup with RMAN
  5) duplicate the primary database
  6) tips and hints

1) Prerequisites
----------------
   - Primary database is in archive mode and must be at least mounted.
   - Rman has been setup
   - Remote login as SYSDBA has been setup
       - add to the init.ora : remote_login_passwordfile=EXCLUSIVE

2) init.ora configuration duplicate and passwordfile
----------------------------------------------------
  Copy init.ora primary to inittdup.ora and change at least
  the following parameters for tdup instance:

  - to avoid the original primary datafile being overwritten 
     db_file_name_convert=('/tar/server/sroonl/sroonl','/tar/server/sroonl/sroonl/dup')

  - to avoid the original primary logfiles being overwritten
      log_file_name_convert=('/tar/server/sroonl/sroonl','/tar/server/sroonl/sroonl/dup')
  - to avoid the original primary control files being overwritten
      control_files = /tar/server/sroonl/sroonl/dup/control01.ctl
  - to change database name
     db_name = tdup
  - to change instance name
     instance_name = tdup

  Set the environment variable ORACLE_SID to duplicate
  (and ORACLE_HOME and other environment variables remain the same)
  and create the passwordfile.

  Example for UNIX (and from dos box you can use SET):
    $ export NLS_LANG=AMERICAN_AMERICA.us7ascii
    $ export ORACLE_SID=tdup
    $ orapwd file=$ORACLE_HOME/dbs/orapwtdup password=secret entries=10

  Startup the duplicate instance in nomount:
    SQL> startup nomount pfile=<location_of_init.ora of duplicate>
 
  Example:
    SQL> startup nomount pfile=/tar/server/sroonl/sroonl/dup/inittdup.ora

3) Setup sqlnet configuration
-----------------------------
  If not done already, setup servicename for primary database in listener.ora
  and tnsnames.ora (Check Note:126074.1 how to setup a SQL*Net configuration)

  Connect to primary (assuming service is tests_nlsu22) as:
    $ sqlplus <username>/<password>@tests_nlsu22

  Test with RMAN :
    $ rman target internal/secret@tests_nlsu22 nocatalog

  should result in:
     RMAN-06005: connected to target database: TESTS (DBID=493757597)
     RMAN-06009: using target database controlfile instead of recovery catalog


4) Making a backup with RMAN
----------------------------
  Set the environment (ORACLE_SID) to primary database TESTS

  $ rman target / nocatalog

  result:
    RMAN-06005: connected to target database: TESTS (DBID=493757597)
    RMAN-06009: using target database controlfile instead of recovery catalog


  ====start example backup script ======

  RMAN> run { allocate channel C1 type disk;
        backup (database format '/tar/server/sroonl/sroonl/back/b_%d_%t_%s.bkp2');
        backup archivelog all delete input;
}
  ====end   example backup script ======


  /* after connecting to target nocatalog */

  RMAN> list backupset of database;

  ======part of output==============
  ....

    List of Datafiles Included
    File Name                                  LV Type Ckp SCN    Ckp Time
    ---- ------------------------------------- -- ---- ---------- -------------
    1    /tar/server/sroonl/sroonl/system.dbf  0  Full 249328     15-JUL-03
    2    /tar/server/sroonl/sroonl/rbs01.dbf   0  Full 249328     15-JUL-03
    3    /tar/server/sroonl/sroonl/users01.dbf 0  Full 249328     15-JUL-03
    4    /tar/server/sroonl/sroonl/tempT817_01.dbf 0  Full 249328     15-JUL-03
    5    /tar/server/sroonl/sroonl/indx01.dbf  0  Full 249328     15-JUL-03

   List of Backup Sets
   Key     Recid      Stamp      LV Set Stamp  Set Count  Completion Time
   ------- ---------- ---------- -- ---------- ---------- ----------------------
   5       5          499446145  0  499446100  9          15-JUL-03

  ..........
  ====== end part of output==============

5) Duplicate the primary database
---------------------------------
  To duplicate the primary database, set all environment variables to
  the duplicate database in this case TDUP

  Double check if you are connected to the duplicate instance with :
     $ sqlplus /nolog
     SQL> connect / as sysdba
     SQL> select * from v$instance;
            => This should report the instance name TDUP


  $ rman target internal/secret@tests_nlsu22 nocatalog
       RMAN-06005: connected to target database: TESTS (DBID=493757597)
       RMAN-06009: using target database controlfile instead of recovery catalog

    RMAN> connect auxiliary /
       RMAN-06020: connected to auxiliary database 

   Use the following RMAN commands for the duplication :
    RMAN> run {
        set until scn 249328;   # The SCN is derived from the output of
                                # step (4) : List backupset of database
        allocate channel ch1 type disk;
        allocate auxiliary channel ch2 type disk;
        duplicate target database to tdup;  
       }

  Output results :

    RMAN-03027: printing stored script: Memory Script
    {
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/system.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/rbs01.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/users01.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/tempT817_01.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/indx01.dbf";
       switch clone datafile  1 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/system.dbf";
       switch clone datafile  2 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/rbs01.dbf";
       switch clone datafile  3 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/users01.dbf";
       switch clone datafile  4 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/tempT817_01.dbf";
       switch clone datafile  5 to datafilecopy
             "/tar/server/sroonl/sroonl/dup/indx01.dbf";
       Alter clone database open resetlogs;
    }
    RMAN-03021: executing script: Memory Script
    RMAN-03022: compiling command: catalog
    RMAN-03023: executing command: catalog
    RMAN-08050: cataloged datafile copy
    RMAN-08513: datafile copy filename=/tar/server/sroonl/sroonl/dup/system.dbf rec7
     .....
     .....
    RMAN-03022: compiling command: catalog
    RMAN-03023: executing command: catalog
    RMAN-08050: cataloged datafile copy
    RMAN-08513: datafile copy filename=/tar/server/sroonl/sroonl/dup/indx01.dbf rec1

    RMAN-03022: compiling command: switch
    RMAN-03023: executing command: switch
    RMAN-08015: datafile 1 switched to datafile copy
    RMAN-08507: input datafilecopy recid=1 stamp=499522637 filename=/tar/server/srof
     ....
     ....
    RMAN-03022: compiling command: switch
    RMAN-03023: executing command: switch
    RMAN-08015: datafile 5 switched to datafile copy
    RMAN-08507: input datafilecopy recid=5 stamp=499522641 filename=/tar/server/srof

    RMAN-03022: compiling command: alter db
    RMAN-06400: database opened
    RMAN-08031: released channel: ch1
    RMAN-08031: released channel: ch2 


   You now have a database tdup and tests on same node that
   are equivalent at the moment the backup at scn used was taken.


6) Tips and hints
-----------------

  The relocation of primary database files to duplicate datafiles is done
  with init.ora DB_FILE_NAME_CONVERT for all the datafiles. If you wish to
  relocate datafile(s) to another location then use the RMAN command:
       SET NEWNAME FOR DATAFILE <file#> TO <new path and name>;

  Example:
    RMAN> set newname for datafile 4 to '/tar/server/sroonl/sroonl/dup/indx01.dbf';