Netsoftmate

Tag: set newname clause

  • Rman backup based duplication with different DB name and different directory structure with transforming directory structure using SET NEWNAME clause

    Overview
    Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
    In this scenario we have different database name and different directory structure. And we are transforming the directory structure using SET NEWNAME and this command should be used in Rman run blocks {}
    Transforming Directory Names using SET NEWNAME Another technique for instructing RMAN to transform directory names is with the SET NEWNAME command. This command must be encapsulated within an RMAN RUN {} block. Before performing this operation, first verify your target database data file numbers and corresponding names.

    Target
    (source) details:

    Database Name
    prod
    Hostname
    NSM-SRC
    Ip Address
    172.16.110.18
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/prod
    Backup Location
    /backup/rman_backup
      
    Destination details:
    Database Name
    snname
    Hostname
    NSM-TRG
    Ip Address
    172.16.110.16
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/snname/
    Backup Location
    /backup/rman_backup



    Pre-requisites

    • Password file from target database.
    • Sqlnet.ora should have correct parameters.
    • Target database should be running through pfile or spfile.
    • Auxiliary instance should be start with pfile in nomount stage.

    Steps on source server:

    1. Set Oracle sid on which backup will perform.

    [oracle@NSM-SRC dbs]$ hostname


    NSM-SRC

    [oracle@NSM-SRC dbs]$ export ORACLE_SID=prod




    2.    Connect to RMAN.

    [[oracle@NSM-SRC rman_backup]$ rman target sys/oracle

    Recovery Manager: Release 11.2.0.1.0 – Production on
    Mon Apr 13 12:14:30 2015

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

    connected to target database: PROD (DBID=284539893)

    RMAN>

    3.    Verify that you target database datafile number and names either from RMAN prompt or from SQL prompt.


    RMAN> report schema;

    using target database control file instead of
    recovery catalog

    Report of database schema for database with
    db_unique_name PROD

    List of Permanent Datafiles

    ===========================

    File Size(MB) Tablespace           RB segs Datafile Name

    —- ——– ——————– ——-
    ————————

    1    680      SYSTEM               ***     /data2/prod/system01.dbf

    2    610      SYSAUX               ***     /data2/prod/sysaux01.dbf

    3    40       UNDOTBS1             ***     /data2/prod/undotbs01.dbf

    4    5        USERS                ***     /data2/prod/users01.dbf

    5    100      EXAMPLE              ***     /data2/prod/example01.dbf

    6    10       DATA                 ***     /data2/prod/data01.dbf

    7    2        TEST                 ***     /data2/prod/test01.dbf

    8    2        TOOLS                ***     /data2/prod/tools01.dbf

    List of Temporary Files

    =======================

    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

    —- ——– ——————– ———–
    ——————–

    1    56       TEMP                 32767       /data2/prod/temp01.dbf


    SQL>  select
    file#,name from v$datafile;

         FILE# NAME

    ———-
    ———————————————

             1
    /data2/prod/system01.dbf

             2
    /data2/prod/sysaux01.dbf

             3
    /data2/prod/undotbs01.dbf

             4
    /data2/prod/users01.dbf

             5
    /data2/prod/example01.dbf

             6
    /data2/prod/data01.dbf

             7
    /data2/prod/test01.dbf

             8
    /data2/prod/tools01.dbf

    8 rows selected.

    SQL>  select
    file#,name from v$tempfile;

         FILE# NAME

    ———-
    ———————————————

             1
    /data2/prod/temp01.dbf


    4.    Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.

    RMAN> show all;

    using target database control file instead of
    recovery catalog

    RMAN configuration parameters for database with
    db_unique_name PROD are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    CONFIGURE CONTROLFILE AUTOBACKUP ON;

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
    TYPE DISK TO ‘/backup/rman_backup/controlfile_%F’;

    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
    TO BACKUPSET; # default

    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK
    TO 1; # default

    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
    DISK TO 1; # default

    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘/backup/rman_backup/db_arch_%U.bkp’;

    CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

    CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE
    ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
    default

    CONFIGURE SNAPSHOT CONTROLFILE NAME TO
    ‘/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f’; # default

    RMAN> list backup;
    specification does not match any backup in the
    repository

    RMAN> backup database plus archivelog;
    Starting backup at 13-APR-15
    current log archived
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=15 device type=DISK
    channel ORA_DISK_1: starting archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in
    backup set
    input archived log thread=1 sequence=153 RECID=149
    STAMP=876910536
    channel ORA_DISK_1: starting piece 1 at 13-APR-15
    channel ORA_DISK_1: finished piece 1 at 13-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2uq495u9_1_1.bkp tag=TAG20150413T101536
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:01
    Finished backup at 13-APR-15
    Starting backup at 13-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup
    set
    input datafile file number=00001
    name=/data2/prod/system01.dbf
    input datafile file number=00002
    name=/data2/prod/sysaux01.dbf
    input datafile file number=00005
    name=/data2/prod/example01.dbf
    input datafile file number=00003
    name=/data2/prod/undotbs01.dbf
    input datafile file number=00006
    name=/data2/prod/data01.dbf
    input datafile file number=00004
    name=/data2/prod/users01.dbf
    input datafile file number=00007
    name=/data2/prod/test01.dbf
    input datafile file number=00008
    name=/data2/prod/tools01.dbf
    channel ORA_DISK_1: starting piece 1 at 13-APR-15
    channel ORA_DISK_1: finished piece 1 at 13-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp tag=TAG20150413T101538
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:55
    Finished backup at 13-APR-15
    Starting backup at 13-APR-15
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in
    backup set
    input archived log thread=1 sequence=154 RECID=150
    STAMP=876910594
    channel ORA_DISK_1: starting piece 1 at 13-APR-15
    channel ORA_DISK_1: finished piece 1 at 13-APR-15
    piece
    handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp tag=TAG20150413T101635
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:01
    Finished backup at 13-APR-15
    Starting Control File Autobackup at 13-APR-15
    piece
    handle=/backup/rman_backup/controlfile_c-284539893-20150413-00 comment=NONE
    Finished Control File Autobackup at 13-APR-15

    RMAN> list backup;

    List of Backup Sets

    ===================

    BS Key 
    Size       Device Type Elapsed
    Time Completion Time

    ——- ———- ———– ————
    —————

    32     
    3.36M      DISK        00:00:00     13-APR-15

            BP Key:
    32   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101536

            Piece
    Name: /backup/rman_backup/db_arch_2uq495u9_1_1.bkp

      List of
    Archived Logs in backup set 32

      Thrd Seq     Low SCN   
    Low Time  Next SCN   Next Time

      —- ——-
    ———- ——— ———- ———

      1    153    
    5172938    13-APR-15 5176490    13-APR-15

    BS Key  Type LV
    Size       Device Type Elapsed Time
    Completion Time

    ——- —- — ———- ———– ————
    —————

    33      Full    1.11G     
    DISK        00:00:54     13-APR-15

            BP Key:
    33   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101538

            Piece
    Name: /backup/rman_backup/db_arch_2vq495ua_1_1.bkp

      List of
    Datafiles in backup set 33

      File LV Type
    Ckp SCN    Ckp Time  Name

      —- — —-
    ———- ——— —-

      1       Full 5176498    13-APR-15 /data2/prod/system01.dbf

      2       Full 5176498    13-APR-15 /data2/prod/sysaux01.dbf

      3       Full 5176498    13-APR-15 /data2/prod/undotbs01.dbf

      4       Full 5176498    13-APR-15 /data2/prod/users01.dbf

      5       Full 5176498    13-APR-15 /data2/prod/example01.dbf

      6       Full 5176498    13-APR-15 /data2/prod/data01.dbf

      7       Full 5176498    13-APR-15 /data2/prod/test01.dbf

      8       Full 5176498    13-APR-15 /data2/prod/tools01.dbf

    BS Key 
    Size       Device Type Elapsed
    Time Completion Time

    ——- ———- ———– ————
    —————

    34     
    9.00K      DISK        00:00:00     13-APR-15

            BP Key:
    34   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101635

            Piece
    Name: /backup/rman_backup/db_arch_30q49603_1_1.bkp

      List of
    Archived Logs in backup set 34

      Thrd Seq     Low SCN   
    Low Time  Next SCN   Next Time

      —- ——-
    ———- ——— ———- ———

      1    154    
    5176490    13-APR-15 5176531    13-APR-15

    BS Key  Type LV
    Size       Device Type Elapsed Time
    Completion Time

    ——- —- — ———- ———– ————
    —————

    35     
    Full    9.52M      DISK        00:00:02     13-APR-15

            BP Key:
    35   Status: AVAILABLE  Compressed: NO  Tag: TAG20150413T101636

            Piece
    Name: /backup/rman_backup/controlfile_c-284539893-20150413-00

      Control File
    Included: Ckp SCN: 5176543      Ckp time:
    13-APR-15

    RMAN>



    5.    Create pfile and copy to destination server.

    SQL> create
    pfile=’/backup/rman_backup/initprod.ora’ from spfile;

    File created.

    [oracle@NSM-SRC dbs]$ scp initprod.ora
    oracle@172.16.110.16:/data1/oracle11g/product/dbs/initcipfile.ora


    oracle@172.16.110.16’s password:

    initprod.ora                                                                                                                                                      
    100% 1169     1.1KB/s   00:00

     
    Before start scp/ftp to destination server create directory structure to accommodate rman backup on destination server.

    [oracle@NSM-TRG prod]$ mkdir -p /backup/rman_backup/



    6.    Copy rman backup from target to destination server.

    RMAN> exit

    Recovery Manager complete.

    [oracle@NSM-SRC dbs]$ cd /backup/rman_backup/

    [oracle@NSM-SRC rman_backup]$ ls -lrt

    total 1176020

    -rw-r—–. 1 oracle dba    3526656 Apr 13 10:15
    db_arch_2uq495u9_1_1.bkp

    -rw-r—–. 1 oracle dba 1190690816 Apr 13 10:16
    db_arch_2vq495ua_1_1.bkp

    -rw-r—–. 1 oracle dba       9728 Apr 13 10:16
    db_arch_30q49603_1_1.bkp

    -rw-r—–. 1 oracle dba    9994240 Apr 13 10:16
    controlfile_c-284539893-20150413-00

    [oracle@NSM-SRC rman_backup]$ scp *
    oracle@172.16.110.16:/backup/rman_backup/
    oracle@172.16.110.16’s password:
    controlfile_c-284539893-20150413-00                                                                                                                               
    100% 9760KB   9.5MB/s   00:01
    db_arch_2uq495u9_1_1.bkp                                                                                                                                           100%
    3444KB   3.4MB/s   00:00
    db_arch_2vq495ua_1_1.bkp                                                                                                           
                                   100% 1136MB  11.1MB/s  
    01:42
    db_arch_30q49603_1_1.bkp                                                                                                                                          
    100% 9728     9.5KB/s   00:00




    Steps on destination server:

    7.    Create new directory structure.

    [oracle@NSM-TRG dbs]$ mkdir -p /data2/snname/



    8.    Edit the pfile to reflect new db name and controlfile location.

    [oracle@NSM-TRG dbs]$ vi initsnname.ora

    *.db_name=’snname’

    *.control_files=’/data2/snname/control01.ctl’,’/data2/snname/control02.ctl’

    9.    Create password file in $ORACLE_HOME/dbs directory.

    [oracle@NSM-TRG dbs]$ orapwd file=orapwsnname
    password=oracle

    10.    Start the instance in no mount stage.

    [oracle@NSM-TRG dbs]$ export ORACLE_SID=snname

    [oracle@NSM-TRG dbs]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13
    12:32:50 2015

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

    Enter user-name: sys as sysdba

    Enter password:

    Connected to an idle instance.

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 1152450560 bytes

    Fixed Size                  2212696 bytes

    Variable Size             352324776 bytes

    Database Buffers          788529152 bytes

    Redo Buffers                9383936 bytes

    SQL>


    11.    Connect to auxiliary instance through RMAN.

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise
    Edition Release 11.2.0.1.0 – 64bit Production

    With the Partitioning, OLAP, Data Mining and Real
    Application Testing options

    [oracle@NSM-TRG dbs]$ echo $ORACLE_SID

    snname

    [oracle@NSM-TRG dbs]$ rman auxiliary sys/oracle

    Recovery Manager: Release 11.2.0.1.0 – Production on
    Mon Apr 13 12:36:27 2015

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

    connected to auxiliary
    database: SNNAME (not mounted)

    RMAN>

    12.    Issue the SET NEWNAME and duplicate command with a Run {} block.

    RMAN> RUN


    2> {

    3> SET NEWNAME FOR
    DATAFILE 1 TO ‘/data2/snname/system01.dbf’;

    4> SET NEWNAME FOR
    DATAFILE 2 TO ‘/data2/snname/sysaux01.dbf’;

    5> SET NEWNAME FOR
    DATAFILE 3 TO ‘/data2/snname/undotbs01.dbf’;

    6> SET NEWNAME FOR
    DATAFILE 4 TO ‘/data2/snname/users01.dbf’;

    7> SET NEWNAME FOR
    DATAFILE 5 TO ‘/data2/snname/example01.dbf’;

    8> SET NEWNAME FOR
    DATAFILE 6 TO ‘/data2/snname/data01.dbf’;

    9> SET NEWNAME FOR
    DATAFILE 7 TO ‘/data2/snname/test01.dbf’;

    10> SET NEWNAME FOR
    DATAFILE 8 TO ‘/data2/snname/tools01.dbf’;

    11> SET NEWNAME FOR
    TEMPFILE 1 TO ‘/data2/snname/temp01.dbf’;

    12> DUPLICATE TARGET
    DATABASE TO snname BACKUP LOCATION ‘/backup/rman_backup’

    13> LOGFILE

    14> GROUP 1
    (‘/data2/snname/redo01.log’) SIZE 50M,

    15> GROUP 2
    (‘/data2/snname/redo02.log’) SIZE 50M;

    16> }

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting Duplicate Db at 13-APR-15

    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    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    contents of Memory Script:

    {

       sql clone
    “alter system set  db_name =

     ”PROD”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       sql clone
    “alter system set  db_unique_name =

     ”SNNAME”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone force nomount

       restore
    clone primary controlfile from 
    ‘/backup/rman_backup/controlfile_c-284539893-20150413-00’;

       alter clone
    database mount;

    }

    executing Memory Script

    sql statement: alter system set  db_name = 
    ”PROD” comment= ”Modified by RMAN duplicate” scope=spfile

    sql statement: alter system set  db_unique_name =  ”SNNAME” comment= ”Modified by RMAN
    duplicate” scope=spfile

    Oracle instance shut down

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    Starting restore at 13-APR-15

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    channel ORA_AUX_DISK_1: restoring control file

    channel ORA_AUX_DISK_1: restore complete, elapsed
    time: 00:00:03

    output file name=/data2/snname/control01.ctl

    output file name=/data2/snname/control02.ctl

    Finished restore at 13-APR-15

    database mounted

    released channel: ORA_AUX_DISK_1

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    contents of Memory Script:

    {

       set until
    scn  5176531;

       set newname
    for datafile  1 to

     “/data2/snname/system01.dbf”;

       set newname
    for datafile  2 to

     “/data2/snname/sysaux01.dbf”;

       set newname
    for datafile  3 to

     “/data2/snname/undotbs01.dbf”;

       set newname
    for datafile  4 to

     “/data2/snname/users01.dbf”;

       set newname
    for datafile  5 to

     “/data2/snname/example01.dbf”;

       set newname
    for datafile  6 to

     “/data2/snname/data01.dbf”;

       set newname
    for datafile  7 to

     “/data2/snname/test01.dbf”;

       set newname
    for datafile  8 to

     “/data2/snname/tools01.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

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 13-APR-15

    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
    /data2/snname/system01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00002 to
    /data2/snname/sysaux01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00003 to
    /data2/snname/undotbs01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00004 to
    /data2/snname/users01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00005 to
    /data2/snname/example01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00006 to
    /data2/snname/data01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00007 to
    /data2/snname/test01.dbf

    channel ORA_AUX_DISK_1: restoring datafile 00008 to
    /data2/snname/tools01.dbf

    channel ORA_AUX_DISK_1: reading from backup piece
    /backup/rman_backup/db_arch_2vq495ua_1_1.bkp

    channel ORA_AUX_DISK_1: piece
    handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp tag=TAG20150413T101538

    channel ORA_AUX_DISK_1: restored backup piece 1

    channel ORA_AUX_DISK_1: restore complete, elapsed
    time: 00:01:05

    Finished restore at 13-APR-15

    contents of Memory Script:

    {

       switch clone
    datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

    input datafile copy RECID=9 STAMP=876919839 file
    name=/data2/snname/system01.dbf

    datafile 2 switched to datafile copy

    input datafile copy RECID=10 STAMP=876919840 file
    name=/data2/snname/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=11 STAMP=876919840 file
    name=/data2/snname/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=12 STAMP=876919841 file
    name=/data2/snname/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=13 STAMP=876919842 file
    name=/data2/snname/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=14 STAMP=876919843 file
    name=/data2/snname/data01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=15 STAMP=876919845 file
    name=/data2/snname/test01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=16 STAMP=876919845 file
    name=/data2/snname/tools01.dbf

    contents of Memory Script:

    {

       set until
    scn  5176531;

       recover

       clone database

        delete
    archivelog

       ;

    }

    executing Memory Script

    executing command: SET until clause

    Starting recover at 13-APR-15

    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=154

    channel ORA_AUX_DISK_1: reading from backup piece
    /backup/rman_backup/db_arch_30q49603_1_1.bkp

    channel ORA_AUX_DISK_1: piece
    handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp tag=TAG20150413T101635

    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=/backup/archive/1_154_872701561.dbf thread=1 sequence=154

    channel clone_default: deleting archived log(s)

    archived log file
    name=/backup/archive/1_154_872701561.dbf RECID=1 STAMP=876919854

    media recovery complete, elapsed time: 00:00:04

    Finished recover at 13-APR-15

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup clone nomount;

       sql clone
    “alter system set  db_name =

     ”SNNAME”
    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

    database dismounted

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size   
                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    sql statement: alter system set  db_name = 
    ”SNNAME” 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    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    sql statement: CREATE CONTROLFILE REUSE SET DATABASE
    “SNNAME” RESETLOGS ARCHIVELOG

     
    MAXLOGFILES     16

     
    MAXLOGMEMBERS      3

     
    MAXDATAFILES      100

     
    MAXINSTANCES     8

     
    MAXLOGHISTORY      292

     LOGFILE

      GROUP  1 ( ‘/data2/snname/redo01.log’ ) SIZE 50 M ,

      GROUP  2 ( ‘/data2/snname/redo02.log’ ) SIZE 50 M

     DATAFILE

     
    ‘/data2/snname/system01.dbf’

     CHARACTER SET
    WE8MSWIN1252

    contents of Memory Script:

    {

       set newname
    for tempfile  1 to

     “/data2/snname/temp01.dbf”;

       switch clone
    tempfile all;

       catalog
    clone datafilecopy 
    “/data2/snname/sysaux01.dbf”,

     “/data2/snname/undotbs01.dbf”,

     “/data2/snname/users01.dbf”,

     “/data2/snname/example01.dbf”,

     “/data2/snname/data01.dbf”,

     “/data2/snname/test01.dbf”,

     “/data2/snname/tools01.dbf”;

       switch clone
    datafile all;

    }

    executing Memory Script

    executing command: SET NEWNAME

    renamed tempfile 1 to /data2/snname/temp01.dbf in
    control file

    cataloged datafile copy

    datafile copy file name=/data2/snname/sysaux01.dbf
    RECID=1 STAMP=876919880

    cataloged datafile copy

    datafile copy file name=/data2/snname/undotbs01.dbf
    RECID=2 STAMP=876919880

    cataloged datafile copy

    datafile copy file name=/data2/snname/users01.dbf
    RECID=3 STAMP=876919881

    cataloged datafile copy

    datafile copy file name=/data2/snname/example01.dbf
    RECID=4 STAMP=876919881

    cataloged datafile copy

    datafile copy file name=/data2/snname/data01.dbf
    RECID=5 STAMP=876919882

    cataloged datafile copy

    datafile copy file name=/data2/snname/test01.dbf
    RECID=6 STAMP=876919882

    cataloged datafile copy

    datafile copy file name=/data2/snname/tools01.dbf
    RECID=7 STAMP=876919883

    datafile 2 switched to datafile copy

    input datafile copy RECID=1 STAMP=876919880 file
    name=/data2/snname/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=2 STAMP=876919880 file
    name=/data2/snname/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=3 STAMP=876919881 file
    name=/data2/snname/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=4 STAMP=876919881 file
    name=/data2/snname/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=5 STAMP=876919882 file
    name=/data2/snname/data01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=6 STAMP=876919882 file
    name=/data2/snname/test01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=7 STAMP=876919883 file
    name=/data2/snname/tools01.dbf

    contents of Memory Script:

    {

       Alter clone
    database open resetlogs;

    }

    executing Memory Script

    database opened

    Finished Duplicate Db at 13-APR-15

    RMAN>

     
    Conclusion
    In the preceding scenario based article, we have learned that backup based duplication using Rman utility with different database name and different directory structure, using SET NEWNAME feature, at the time of duplicating database.

    BY
    Name: Mirza Sarmadullah Baig
    Designation: Senior Database Engineer
    Organization: Netsoftmate IT Solutions.
    Email: info@netsoftmate.com