Netsoftmate

Tag: same directory structure

  • RMAN Duplicate With Same Database Name and Same Directory Structure

    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.
    Environment Details
    Target (Source) details
    Database Name
    prod
    Hostname
    Snsm-linux02
    Ip Address
    172.16.110.18
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/prod
    Backup Location
    /backup/rman_backup
    Tns details:
    PROD =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = prod)
        )
      )
    Destination (Auxiliary) details:
    Database Name
    Prod
    Hostname
    Nsm-linux01
    Ip Address
    172.16.110.16
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/prod
    Backup Location
    /backup/rman_backup
    Tns details:
    DUP =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dup)
        )
      )

    Pre-requisites

    • Oracle net configuration
    • Both target and destination server should have tns entries
    • Static listener registration on auxiliary site
    • Service should be register on aux listener
    • Password files from target database
    • Sqlnet.ora should have correct parameters
    • Target database should be running through spfile
    • Check connectivity between target and destination server
    Steps on target (source) server

    • Put tns entry of auxiliary database into target $ORACLE_HOME/network/admin directory
    [oracle@Snsm-linux02 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
    [oracle@Snsm-linux02 admin]$ vi tnsnames.ora
    DUP =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dup)
        )
      )
    [oracle@Snsm-linux02 admin]$ tnsping dup
    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:34:19
    Copyright (c) 1997, 2009, Oracle.  All rights reserved.
    Used parameter files:
    /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
    (SERVICE_NAME = dup)))
    OK (0 msec)
    •  Check database whether it is running in archive log mode.
    SQL> select name, open_mode, log_mode from v$database;
    NAME       OPEN_MODE            LOG_MODE
    ———- ——————– —————
    PROD       READ WRITE           ARCHIVELOG
    • Check database is running from spfile.
    SQL> select value from v$parameter where name=’spfile’;
    VALUE
    —————————————————————–
    /data2/app/oracle/product/11.2.0/dbs/spfileprod.ora
    or
    SQL> show parameter pfile
    NAME                                 TYPE                              VALUE
    ———————————— ——————————— ——————————
    spfile                               string                            /data2/app/oracle/product/11.2
                                                                           .0/dbs/spfileprod.ora
    • Create pfile from spfile.
    SQL> create pfile=’/backup/rman_backup/initprod.ora’ from spfile;
    File created.
    • Create password file.
    [oracle@Snsm-linux02 dbs]$ pwd
    /data2/app/oracle/product/11.2.0/dbs
    [oracle@Snsm-linux02 dbs]$orapwd file=orapwprod password=oracle
    • Copy the password file and init file to destination $ORACLE_HOME/dbs directory using scp or ftp
    scp orapwprod oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/
    scp /backup/rman_backup/initprod.ora oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/
    Steps on destination server
    • Put static listener entry into listener.ora file in $ORACLE_HOME/network/admin directory.
    [oracle@Nsm-linux01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
    [oracle@Nsm-linux01 admin]$
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = dup)
          (ORACLE_HOME = /data2/app/oracle/product/11.2.0)
          (SID_NAME = prod)
        )
      )
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
        )
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    Here global_name is nothing but service name and sid_name is instance name
    Now reload the listener and check the services
    [oracle@Nsm-linux01 admin]$ lsnrctl
    LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:46:41
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    Welcome to LSNRCTL, type “help” for information.
    LSNRCTL> reload
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))
    The command completed successfully
    LSNRCTL> services
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))
    Services Summary…
    Service “dup” has 1 instance(s).
      Instance “prod”, status UNKNOWN, has 1 handler(s) for this service…
        Handler(s):
          “DEDICATED” established:0 refused:0
             LOCAL SERVER
    Service “prod” has 1 instance(s).
          “DEDICATED” established:0 refused:0 state:ready
             LOCAL SERVER
    The command completed successfully
    • Put Tns entry for target (source) database in $ORACLE_HOME/network/admin directory
    [oracle@Nsm-linux01 admin]$ vi tnsnames.ora
    PROD =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = prod)
        )
      )
    [oracle@Nsm-linux01 admin]$ tnsping prod
    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:49:28
    Copyright (c) 1997, 2009, Oracle.  All rights reserved.
    Used parameter files:
    /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
    (SERVICE_NAME = prod)))
    OK (10 msec)
    • Create the directory structure same as target(source) server.
    [oracle@Nsm-linux01 admin]$ mkdir -p /data2/prod
    [oracle@Nsm-linux01 admin]$ mkdir -p /backup/archive
    • Set the oracle Sid and start the instance in no mount stage.
    [oracle@Nsm-linux01 admin]$ export ORACLE_SID=prod
    [oracle@Nsm-linux01 admin]$ sqlplus
    SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 12:54:37 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             335547560 bytes
    Database Buffers          805306368 bytes
    Redo Buffers                9383936 bytes
    SQL>
    • Now connect to Rman
    [oracle@Nsm-linux01 admin]$ rman target sys/oracle@prod auxiliary sys/oracle@dup
    Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 1 12:56:39 2015
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    connected to target database: PROD (DBID=284539893)
    connected to auxiliary database: PROD (not mounted)
    RMAN>

    • Issue the command to duplicate the database

    RMAN> DUPLICATE TARGET DATABASE TO prod FROM ACTIVE DATABASE NOFILENAMECHECK;
    Starting Duplicate Db at 01-APR-15
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=63 device type=DISK
    Following is the output.
    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 =
    ”PROD” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
       shutdown clone immediate;
       startup clone force nomount
       backup as copy current controlfile auxiliary format  ‘/data2/prod/control01.ctl’;
       restore clone controlfile to  ‘/data2/prod/control02.ctl’ from
    ‘/data2/prod/control01.ctl’;
       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 =  ”PROD” 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 backup at 01-APR-15
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=76 device type=DISK
    channel ORA_DISK_1: starting datafile copy
    copying current control file
    output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f tag=TAG20150401T124940 RECID=4 STAMP=875882981
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 01-APR-15
    Starting restore at 01-APR-15
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=63 device type=DISK
    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 01-APR-15
    database mounted
    contents of Memory Script:
    {
       set newname for datafile  1 to
    “/data2/prod/system01.dbf”;
       set newname for datafile  2 to
    “/data2/prod/sysaux01.dbf”;
       set newname for datafile  3 to
    “/data2/prod/undotbs01.dbf”;
       set newname for datafile  4 to
    “/data2/prod/users01.dbf”;
       set newname for datafile  5 to
    “/data2/prod/example01.dbf”;
       backup as copy reuse
       datafile  1 auxiliary format
    “/data2/prod/system01.dbf”   datafile
    2 auxiliary format
    “/data2/prod/sysaux01.dbf”   datafile
    3 auxiliary format
    “/data2/prod/undotbs01.dbf”   datafile
    4 auxiliary format
    “/data2/prod/users01.dbf”   datafile
    5 auxiliary format
    “/data2/prod/example01.dbf”   ;
       sql ‘alter system archive log current’;
    }
    executing Memory Script
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    Starting backup at 01-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00001 name=/data2/prod/system01.dbf
    output file name=/data2/prod/system01.dbf tag=TAG20150401T124955
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00002 name=/data2/prod/sysaux01.dbf
    output file name=/data2/prod/sysaux01.dbf tag=TAG20150401T124955
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00005 name=/data2/prod/example01.dbf
    output file name=/data2/prod/example01.dbf tag=TAG20150401T124955
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00003 name=/data2/prod/undotbs01.dbf
    output file name=/data2/prod/undotbs01.dbf tag=TAG20150401T124955
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00004 name=/data2/prod/users01.dbf
    output file name=/data2/prod/users01.dbf tag=TAG20150401T124955
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 01-APR-15
    sql statement: alter system archive log current
    contents of Memory Script:
    {
       backup as copy reuse
       archivelog like  “/backup/archive/1_116_872701561.dbf” auxiliary format
    “/backup/archive/1_116_872701561.dbf”   ;
       catalog clone archivelog  “/backup/archive/1_116_872701561.dbf”;
       switch clone datafile all;
    }
    executing Memory Script
    Starting backup at 01-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=116 RECID=112 STAMP=875883160
    output file name=/backup/archive/1_116_872701561.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
    Finished backup at 01-APR-15
    cataloged archived log
    archived log file name=/backup/archive/1_116_872701561.dbf RECID=112 STAMP=875883644
    datafile 1 switched to datafile copy
    input datafile copy RECID=4 STAMP=875883644 file name=/data2/prod/system01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=5 STAMP=875883644 file name=/data2/prod/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=6 STAMP=875883645 file name=/data2/prod/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=7 STAMP=875883645 file name=/data2/prod/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=8 STAMP=875883645 file name=/data2/prod/example01.dbf
    contents of Memory Script:
    {
       set until scn  4529390;
       recover
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    executing command: SET until clause
    Starting recover at 01-APR-15
    using channel ORA_AUX_DISK_1
    starting media recovery
    archived log for thread 1 with sequence 116 is already on disk as file /backup/archive/1_116_872701561.dbf
    archived log file name=/backup/archive/1_116_872701561.dbf thread=1 sequence=116
    media recovery complete, elapsed time: 00:00:02
    Finished recover at 01-APR-15
    contents of Memory Script:
    {
       shutdown clone immediate;
       startup clone nomount;
       sql clone “alter system set  db_name =
    ”PROD” 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 =  ”PROD” 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 “PROD” RESETLOGS ARCHIVELOG
      MAXLOGFILES     16
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     8
      MAXLOGHISTORY      292
    LOGFILE
      GROUP  1 ( ‘/data2/prod/redo01.log’ ) SIZE 50 M  REUSE,
      GROUP  2 ( ‘/data2/prod/redo02.log’ ) SIZE 50 M  REUSE,
      GROUP  3 ( ‘/data2/prod/redo03.log’ ) SIZE 50 M  REUSE
    DATAFILE
      ‘/data2/prod/system01.dbf’
    CHARACTER SET WE8MSWIN1252
    contents of Memory Script:
    {
       set newname for tempfile  1 to
    “/data2/prod/temp01.dbf”;
       switch clone tempfile all;
       catalog clone datafilecopy  “/data2/prod/sysaux01.dbf”,
    “/data2/prod/undotbs01.dbf”,
    “/data2/prod/users01.dbf”,
    “/data2/prod/example01.dbf”;
       switch clone datafile all;
    }
    executing Memory Script
    executing command: SET NEWNAME
    renamed tempfile 1 to /data2/prod/temp01.dbf in control file
    cataloged datafile copy
    datafile copy file name=/data2/prod/sysaux01.dbf RECID=1 STAMP=875883671
    cataloged datafile copy
    datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=875883671
    cataloged datafile copy
    datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=875883671
    cataloged datafile copy
    datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=875883672
    datafile 2 switched to datafile copy
    input datafile copy RECID=1 STAMP=875883671 file name=/data2/prod/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=875883671 file name=/data2/prod/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=3 STAMP=875883671 file name=/data2/prod/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=4 STAMP=875883672 file name=/data2/prod/example01.dbf
    contents of Memory Script:
    {
       Alter clone database open resetlogs;
    }
    executing Memory Script
    database opened
    Finished Duplicate Db at 01-APR-15
     Issues which we have faced are as follows              

    Everything has went well listener services were also worked fine, RMAN connectivity from auxiliary server also fine using net service name but  When issue the duplicate database command from RMAN> prompt it has thrown following error.
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:01:01
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:01:01
    ORA-17629: Cannot connect to the remote database server
    ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
    ORA-17629: Cannot connect to the remote database server
    Then we have searched for solutions, we get one MOS note 1144273.1
    This note gives some idea like causes and solutions from that we have checked in our databases on destination server there are wrong parameters in sqlnet.ora then we have corrected the sqlnet.ora file as follows
    [oracle@Nsm-linux01 admin]$ cat sqlnet.ora
    # sqlnet.ora Network Configuration File: /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.
    SQLNET.AUTHENTICATION_SERVICES = (NTS,NONE)
    #NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
    ADR_BASE = /data2/app/oracle
    Now we have executed again, this time we get different error, check below.
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:23:02
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:23:02
    ORA-17628: Oracle error 19505 returned by remote Oracle server
    After dig out the error we get to know that there is no directory structure is available on destination server to accommodate data files and control  Files, we have created the directory structure and execute the duplicate command again, this time has successful.
     Additionally there is one more MOS note for known issues while duplicating RMAN. 1366290.1





    Conclusion

    In the above article, we have learned that Active Duplication using Rman utility with same database name and same directory structure, and we have faced some errors and their work around.

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

    Avast logo
    This email has been checked for viruses by Avast antivirus software.
    www.avast.com

  • Rman backup based duplication with same directory structure and different DB name

    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.
    Here we have maintained same directory structure but different database name.



    Environment Details

    Target (source) details:

    Database Name
    prod
    Hostname
    Nsmsrc01
    Ip Address
    172.16.110.18
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/prod
    Backup Location
    /backup/rman_backup

    Destination details:

    Database Name
    dup
    Hostname
    Nsmtrg
    Ip Address
    172.16.110.16
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data2/prod
    Backup Location
    /backup/rman_backup

     

    Pre-requisites
    Password file from target database.
    Sqlnet.ora should have correct parameters.
    Target database should be running through spfile.

    Steps on target(source) server
    1.    Set Oracle sid 

    [oracle@Nsmsrc01 dbs]$ hostname


    Nsmsrc01


    [oracle@Nsmsrc01 dbs]$ export ORACLE_SID=prod

     

    2.    Connect to RMAN.

    [oracle@Nsmsrc01 dbs]$ rman target sys/oracle



    Recovery Manager: Release 11.2.0.1.0 – Production on
    Thu Apr 9 10:47:41 2015
    Copyright (c) 1982, 2009, Oracle and/or its
    affiliates.  All rights reserved.
    connected to target database: PROD (DBID=284539893)
    RMAN>

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

    RMAN> show all;



    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> backup database plus archivelog;


    Starting backup at 09-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=87 RECID=83
    STAMP=875118622
    input archived log thread=1 sequence=88 RECID=84
    STAMP=875138912
    input archived log thread=1 sequence=89 RECID=85
    STAMP=875169392
    input archived log thread=1 sequence=90 RECID=86
    STAMP=875202890
    input archived log thread=1 sequence=91 RECID=87
    STAMP=875225400
    input archived log thread=1 sequence=92 RECID=88
    STAMP=875253619
    input archived log thread=1 sequence=93 RECID=89
    STAMP=875287426
    input archived log thread=1 sequence=94 RECID=90
    STAMP=875311887
    input archived log thread=1 sequence=95 RECID=91
    STAMP=875338210
    input archived log thread=1 sequence=96 RECID=92
    STAMP=875371700
    input archived log thread=1 sequence=97 RECID=93
    STAMP=875398375
    input archived log thread=1 sequence=98 RECID=94
    STAMP=875422534
    input archived log thread=1 sequence=99 RECID=95
    STAMP=875455916
    input archived log thread=1 sequence=100 RECID=96
    STAMP=875489442
    input archived log thread=1 sequence=101 RECID=97
    STAMP=875511892
    input archived log thread=1 sequence=102 RECID=98
    STAMP=875532973
    input archived log thread=1 sequence=103 RECID=99
    STAMP=875566589
    input archived log thread=1 sequence=104 RECID=100
    STAMP=875598381
    input archived log thread=1 sequence=105 RECID=101
    STAMP=875617253
    input archived log thread=1 sequence=106 RECID=102
    STAMP=875650222
    input archived log thread=1 sequence=107 RECID=103
    STAMP=875683804
    input archived log thread=1 sequence=108 RECID=104
    STAMP=875692844
    input archived log thread=1 sequence=109 RECID=105
    STAMP=875726521
    input archived log thread=1 sequence=110 RECID=106
    STAMP=875759423
    input archived log thread=1 sequence=111 RECID=107
    STAMP=875771364
    input archived log thread=1 sequence=112 RECID=108
    STAMP=875804400
    input archived log thread=1 sequence=113 RECID=109
    STAMP=875809288
    input archived log thread=1 sequence=114 RECID=110
    STAMP=875842252
    input archived log thread=1 sequence=115 RECID=111 STAMP=875857844
    input archived log thread=1 sequence=116 RECID=112
    STAMP=875883160
    input archived log thread=1 sequence=117 RECID=113
    STAMP=875939543
    input archived log thread=1 sequence=118 RECID=114
    STAMP=875955632
    input archived log thread=1 sequence=119 RECID=115
    STAMP=875960595
    input archived log thread=1 sequence=120 RECID=116
    STAMP=876018651
    input archived log thread=1 sequence=121 RECID=117
    STAMP=876036137
    input archived log thread=1 sequence=122 RECID=118
    STAMP=876094207
    input archived log thread=1 sequence=123 RECID=119
    STAMP=876117455
    input archived log thread=1 sequence=124 RECID=120
    STAMP=876173429
    input archived log thread=1 sequence=125 RECID=121
    STAMP=876203937
    input archived log thread=1 sequence=126 RECID=122
    STAMP=876259857
    input archived log thread=1 sequence=127 RECID=123
    STAMP=876288622
    input archived log thread=1 sequence=128 RECID=124
    STAMP=876308432
    input archived log thread=1 sequence=129 RECID=125
    STAMP=876364250
    input archived log thread=1 sequence=130 RECID=126
    STAMP=876378605
    input archived log thread=1 sequence=131 RECID=127
    STAMP=876398550
    input archived log thread=1 sequence=132 RECID=128
    STAMP=876399532
    input archived log thread=1 sequence=133 RECID=129
    STAMP=876419413
    input archived log thread=1 sequence=134 RECID=130
    STAMP=876421079
    input archived log thread=1 sequence=135 RECID=131
    STAMP=876461598
    input archived log thread=1 sequence=136 RECID=132
    STAMP=876504610
    input archived log thread=1 sequence=137 RECID=133
    STAMP=876548085
    input archived log thread=1 sequence=138 RECID=134
    STAMP=876567056
    input archived log thread=1 sequence=139 RECID=135
    STAMP=876567230
    input archived log thread=1 sequence=140 RECID=136
    STAMP=876583581
    input archived log thread=1 sequence=141 RECID=137
    STAMP=876583754
    input archived log thread=1 sequence=142 RECID=138
    STAMP=876586722
    channel ORA_DISK_1: starting piece 1 at 09-APR-15
    channel ORA_DISK_1: finished piece 1 at 09-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2mq3v9n2_1_1.bkp tag=TAG20150409T161842
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:01:35
    Finished backup at 09-APR-15

    Starting backup at 09-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 09-APR-15
    channel ORA_DISK_1: finished piece 1 at 09-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2nq3v9q2_1_1.bkp tag=TAG20150409T162018
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:56
    Finished backup at 09-APR-15

    Starting backup at 09-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=143 RECID=139
    STAMP=876586874
    channel ORA_DISK_1: starting piece 1 at 09-APR-15
    channel ORA_DISK_1: finished piece 1 at 09-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2oq3v9rr_1_1.bkp tag=TAG20150409T162115
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:01
    Finished backup at 09-APR-15

    Starting Control File and SPFILE Autobackup at
    09-APR-15
    piece
    handle=/backup/rman_backup/controlfile_c-284539893-20150409-02 comment=NONE
    Finished Control File and SPFILE Autobackup at
    09-APR-15

    RMAN>


    4.    Create pfile and copy to destination server.

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



    File created.
    [oracle@Nsmsrc01 rman_backup]$ scp initprod.ora 
    oracle@172.16.110.16:/data1/oracle11g/product/dbs/
    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@Nsmtrg prod]$ mkdir -p /backup/rman_backup/


    5.    Copy rman backup from target to destination server.

    [oracle@Nsmsrc01 rman_backup]$ scp db*
    oracle@172.16.110.16:/backup/rman_backup/


    oracle@172.16.110.16’s password:

    db_arch_2mq3v9n2_1_1.bkp                                                                                                                                          
    100% 1729MB  11.2MB/s   02:34
    db_arch_2nq3v9q2_1_1.bkp                                                                                                                                           100%
    1133MB  11.2MB/s   01:41
    db_arch_2oq3v9rr_1_1.bkp                                                                              
                                                                100%  104KB 104.0KB/s   00:00
    [oracle@Nsmsrc01 rman_backup]$ scp
    controlfile_c-284539893-20150409-02 
    oracle@172.16.110.16:/backup/rman_backup/
    oracle@172.16.110.16’s password:
    controlfile_c-284539893-20150409-02                                                                                                                               
    100% 9632KB   9.4MB/s   00:01

    Steps on destination server
    6.    Create directory structure same as target(source) database.

    [oracle@Nsmtrg prod]$ mkdir -p /data2/prod


    [oracle@Nsmtrg prod]$ mkdir -p /backup/archive/


    7.    Start the new instance in nomount

    [oracle@Nsmtrg rman_backup]$ export ORACLE_SID=dup


    [oracle@Nsmtrg dbs]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9
    16:24:32 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> 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

    8.    Connect to rman

    [oracle@Nsmtrg rman_backup]$ rman auxiliary
    sys/oracle



    Recovery Manager: Release 11.2.0.1.0 – Production on
    Thu Apr 9 16:27:47 2015
    Copyright (c) 1982, 2009, Oracle and/or its
    affiliates.  All rights reserved.
    connected to auxiliary database: DUP (not mounted)
    RMAN>

    9.    Issue the following command to duplicate database with different name and same directory structure.

    RMAN> duplicate
    database to dup backup location ‘/backup/rman_backup’ nofilenamecheck;




    Starting Duplicate Db at 09-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 =

     ”DUP”
    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-20150409-02’;

       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 =  ”DUP” 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 09-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:01

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

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

    Finished restore at 09-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  4972796;

       set newname
    for datafile  1 to

     “/data2/prod/system01.dbf”;

       set newname
    for datafile  2 to

     “/data2/prod/sysaux01.dbf”;

       set newname
    for datafile  3 to

     “/data2/prod/undotbs01.dbf”;

       set newname
    for datafile  4 to

     “/data2/prod/users01.dbf”;

       set newname
    for datafile  5 to

     “/data2/prod/example01.dbf”;

       set newname
    for datafile  6 to

     “/data2/prod/data01.dbf”;

       set newname
    for datafile  7 to

     “/data2/prod/test01.dbf”;

       set newname
    for datafile  8 to

     “/data2/prod/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 09-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/prod/system01.dbf

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

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

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

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

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

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

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

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

    channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2nq3v9q2_1_1.bkp
    tag=TAG20150409T162018

    channel ORA_AUX_DISK_1: restored backup piece 1

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

    Finished restore at 09-APR-15

    contents of Memory Script:

    {

       switch clone
    datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

    input datafile copy RECID=1 STAMP=876589349 file
    name=/data2/prod/system01.dbf

    datafile 2 switched to datafile copy

    input datafile copy RECID=2 STAMP=876589350 file
    name=/data2/prod/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=3 STAMP=876589350 file
    name=/data2/prod/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=4 STAMP=876589350 file
    name=/data2/prod/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=5 STAMP=876589351 file
    name=/data2/prod/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=6 STAMP=876589351 file
    name=/data2/prod/data01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=7 STAMP=876589351 file
    name=/data2/prod/test01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=8 STAMP=876589352 file
    name=/data2/prod/tools01.dbf

    contents of Memory Script:

    {

       set until
    scn  4972796;

       recover

       clone
    database

        delete
    archivelog

       ;

    }

    executing Memory Script

    executing command: SET until clause

    Starting recover at 09-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=143

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

    channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2oq3v9rr_1_1.bkp
    tag=TAG20150409T162115

    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_143_872701561.dbf thread=1 sequence=143

    channel clone_default: deleting archived log(s)

    archived log file
    name=/backup/archive/1_143_872701561.dbf RECID=1 STAMP=876589358

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

    Finished recover at 09-APR-15

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup
    clone nomount;

       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

    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 = 
    ”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    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
    “DUP” RESETLOGS ARCHIVELOG

     
    MAXLOGFILES     16

     
    MAXLOGMEMBERS      3

     
    MAXDATAFILES      100

     
    MAXINSTANCES     8

     
    MAXLOGHISTORY      292

     LOGFILE

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

      GROUP  2 ( ‘/data2/prod/redo02.log’ ) SIZE 50 M  REUSE,

      GROUP  3 ( ‘/data2/prod/redo03.log’ ) SIZE 50 M  REUSE

     DATAFILE

     
    ‘/data2/prod/system01.dbf’

     CHARACTER SET
    WE8MSWIN1252

    contents of Memory Script:

    {

       set newname
    for tempfile  1 to

     “/data2/prod/temp01.dbf”;

       switch clone
    tempfile all;

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

     “/data2/prod/undotbs01.dbf”,

     “/data2/prod/users01.dbf”,

     “/data2/prod/example01.dbf”,

     “/data2/prod/data01.dbf”,

     “/data2/prod/test01.dbf”,

     “/data2/prod/tools01.dbf”;

       switch clone
    datafile all;

    }

    executing Memory Script

    executing command: SET NEWNAME

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

    cataloged datafile copy

    datafile copy file name=/data2/prod/sysaux01.dbf
    RECID=1 STAMP=876589383

    cataloged datafile copy

    datafile copy file name=/data2/prod/undotbs01.dbf
    RECID=2 STAMP=876589383

    cataloged datafile copy

    datafile copy file name=/data2/prod/users01.dbf
    RECID=3 STAMP=876589383

    cataloged datafile copy

    datafile copy file name=/data2/prod/example01.dbf
    RECID=4 STAMP=876589383

    cataloged datafile copy

    datafile copy file name=/data2/prod/data01.dbf
    RECID=5 STAMP=876589384

    cataloged datafile copy

    datafile copy file name=/data2/prod/test01.dbf
    RECID=6 STAMP=876589384

    cataloged datafile copy

    datafile copy file name=/data2/prod/tools01.dbf
    RECID=7 STAMP=876589385

    datafile 2 switched to datafile copy

    input datafile copy RECID=1 STAMP=876589383 file
    name=/data2/prod/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=2 STAMP=876589383 file
    name=/data2/prod/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=3 STAMP=876589383 file
    name=/data2/prod/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=4 STAMP=876589383 file
    name=/data2/prod/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=5 STAMP=876589384 file
    name=/data2/prod/data01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=6 STAMP=876589384 file
    name=/data2/prod/test01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=7 STAMP=876589385 file
    name=/data2/prod/tools01.dbf

    contents of Memory Script:

    {

       Alter clone
    database open resetlogs;

    }

    executing Memory Script

    database opened

    Finished Duplicate Db at 09-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 same directory structure.

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