Good Contents Are Everywhere, But Here, We Deliver The Best of The Best.Please Hold on!
About Flex ASM
  • In earlier Oracle database release, ASM will be running on each node in the cluster and ASM clients such as database and ACFS can only access ASM by using an ASM on the same host.
  • Oracle Database 12c introduced a new feature called Oracle Flex ASM which provides remote storage access.
  • Oracle Flex ASM is an ASM instance which will be running in the cluster based on cardinality defined.
  • The default cardinality is 3, but this can be modified using srvctl modify asm command.
  • ASM is not required to run on each node in the cluster.
  • With Oracle Flex ASM,the clients can connect to remote ASM using network connection (ie ASM network ).
  • If a server running an ASM instance fails, Oracle Clusterware will start a new ASM instance on a different server to maintain the cardinality. If a 12c database instance is using a particular ASM instance, and that instance is lost because of a server crash or ASM instance failure, then the Oracle 12c database instance will reconnect to an existing ASM instance on another node.These features are collectively called Oracle Flex ASM.

Steps to Convert Standard ASM to Flex ASM
  • You can convert an Oracle ASM configuration to an Oracle Flex ASM using ASMCA. This functionality is only available in an Oracle Grid Infrastructure 12c configuration.
Before you convert an Oracle ASM configuration to an Oracle Flex ASM, you must ensure the following:
  • Oracle Cluster Registry (OCR) is stored in a disk group
[root@dm01db01 ~]# /u01/app/12.1.0.2/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :      10684
         Available space (kbytes) :     398884
         ID                       : 1119121028
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded
  • The server parameter file (SPFILE) is stored in a disk group.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA/dm01-cluster/ASMPARAMETE
                                                 RFILE/registry.253.934096549

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA/orcl/spfileorcl.ora
  • The password file (ORAPWD file) is stored in a disk group.
dm01db01-orcldb1 {/home/oracle}:dcli -g ~/dbs_group -l oracle ‘ls -ltr
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl1
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl2
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl3
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl4
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl5
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl7
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl6
dm01db01: -rw-r—– 1 oracle oinstall 8192 Jan 17 03:45 /u01/app/oracle/product/12.1.0.2/dbhome/dbs/orapworcl8

dm01db01-orcl1 {/home/oracle}:orapwd file=’+DATA/orcl/orapworcl’ entries=10 dbuniquename=’orcl’ format=12

Enter password for SYS:

ASMCMD> ls -l +DATA/ORCL/orapworcl
Type      Redund  Striped  Time             Sys  Name
PASSWORD  HIGH    COARSE   JAN 26 06:00:00  N    orapworcl => +DATA/ORCL/PASSWORD/pwdorcl.272.934265865

dm01db01-+ASM1 {/home/oracle}:orapwd file=’+DATA/orapwASM’ entries=10 dbuniquename=’ASM’ format=12

Enter password for SYS:

OPW-00019: Failed to update the CRS resource with DB password file location

dm01db01-+ASM1 {/home/oracle}:srvctl modify asm -pwfile +DATA/orapwASM

dm01db01-+ASM1 {/home/oracle}:srvctl config asm -detail
ASM home: <CRS home>
Password file: +DATA/orapwASM
ASM listener: LISTENER
ASM is enabled.
ASM is individually enabled on nodes:
ASM is individually disabled on nodes:

ASMCMD [+DATA] > ls -l orapwasm
Type      Redund  Striped  Time             Sys  Name
PASSWORD  HIGH    COARSE   JAN 27 04:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.399.934344779
  • The disk group where the OCR, SPFILE, and ORAPWD files are stored has Oracle ASM compatibility (COMPATIBLE.ASM) set to 12.1 or higher.
SQL> col name for a40
SQL> col value for a40
SQL> set lines 200
SQL> set pages 200
SQL> select a.group_number,b.name dgname,a.name,a.value from v$asm_attribute a, v$asm_diskgroup b where a.name in (‘au_size’,’disk_repair_time’,’compatible.rdbms’,’compatible.asm’) and a.group_number=b.group_number order by b.name,a.name;

GROUP_NUMBER DGNAME                         NAME                                     VALUE
———— —————————— ————————–               ————————
           3 ACFS_DG                        au_size                                  4194304
           3 ACFS_DG                        compatible.asm                           12.1.0.0.0
           3 ACFS_DG                        compatible.rdbms                         12.1.0.0.0
           3 ACFS_DG                        disk_repair_time                         3.6h
           1 DATA                           au_size                                  4194304
           1 DATA                           compatible.asm                           12.1.0.2.0
           1 DATA                           compatible.rdbms                         11.2.0.4.0
           1 DATA                           disk_repair_time                         3.6h
           2 RECO                           au_size                                  4194304
           2 RECO                           compatible.asm                           12.1.0.2.0
           2 RECO                           compatible.rdbms                         11.2.0.4.0
           2 RECO                           disk_repair_time                         3.6h

dm01db01-+ASM1 {/home/oracle}:/u01/app/12.1.0.2/grid/bin/crsctl stat res -t
——————————————————————————–
Name           Target  State        Server                   State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ACFS_DG.ACFSVOL.advm
               ONLINE  ONLINE       dm01db01                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db02                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db03                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db04                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db05                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db06                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db07                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
               ONLINE  ONLINE       dm01db08                 Volume device /dev/a
                                                             sm/acfsvol-228 is online,STABLE
ora.ACFS_DG.dg
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
ora.DATA.dg
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
ora.RECO.dg
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
ora.acfs_dg.acfsvol.acfs
               ONLINE  ONLINE       dm01db01                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db02                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db03                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db04                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db05                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db06                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db07                 mounted on /acfs,STABLE
               ONLINE  ONLINE       dm01db08                 mounted on /acfs,STABLE
ora.asm
               ONLINE  ONLINE       dm01db01                 Started,STABLE
               ONLINE  ONLINE       dm01db02                 Started,STABLE
               ONLINE  ONLINE       dm01db03                 Started,STABLE
               ONLINE  ONLINE       dm01db04                 Started,STABLE
               ONLINE  ONLINE       dm01db05                 Started,STABLE
               ONLINE  ONLINE       dm01db06                 Started,STABLE
               ONLINE  ONLINE       dm01db07                 Started,STABLE
               ONLINE  ONLINE       dm01db08                 Started,STABLE
ora.net1.network
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
ora.ons
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       dm01db08                 STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       dm01db07                 STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       dm01db05                 STABLE
ora.dm01db01.vip
      1        ONLINE  ONLINE       dm01db01                 STABLE
ora.dm01db02.vip
      1        ONLINE  ONLINE       dm01db02                 STABLE
ora.dm01db03.vip
      1        ONLINE  ONLINE       dm01db03                 STABLE
ora.dm01db04.vip
      1        ONLINE  ONLINE       dm01db04                 STABLE
ora.dm01db05.vip
      1        ONLINE  ONLINE       dm01db05                 STABLE
ora.dm01db06.vip
      1        ONLINE  ONLINE       dm01db06                 STABLE
ora.dm01db07.vip
      1        ONLINE  ONLINE       dm01db07                 STABLE
ora.dm01db08.vip
      1        ONLINE  ONLINE       dm01db08                 STABLE
ora.cvu
      1        ONLINE  ONLINE       dm01db06                 STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.orcl.db
      1        ONLINE  ONLINE       dm01db03                 Open,STABLE
      2        ONLINE  ONLINE       dm01db04                 Open,STABLE
      3        ONLINE  ONLINE       dm01db01                 Open,STABLE
      4        ONLINE  ONLINE       dm01db05                 Open,STABLE
      5        ONLINE  ONLINE       dm01db06                 Open,STABLE
      6        ONLINE  ONLINE       dm01db02                 Open,STABLE
      7        ONLINE  ONLINE       dm01db07                 Open,STABLE
      8        ONLINE  ONLINE       dm01db08                 Open,STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       dm01db08                 STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       dm01db07                 STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       dm01db05                 STABLE


ASMCMD> showclustermode
ASM cluster : Flex mode disabled

dm01db01-+ASM1 {/home/oracle}:srvctl status asm -detail
ASM is running on dm01db01,dm01db03,dm01db02,dm01db05,dm01db04,dm01db07,dm01db06,dm01db08
ASM is enabled.

Conversion Steps

  • Set the display and Start ASMCA



  • Click on “ASM Instances” and then “Convert to Oracle Flex ASM”



  • On Convert to Oracle Flex ASM page, specify:
    • ASM Listener Port number: 1521
    • Select network to be used for ASM: bond0 (cluster_interconnect)
    • Check the box if you plan on using ASM for pre 12c databases.
    • Click Ok



  • Click Yes



  • The processing page will be displayed.





  • Open the script and view the script contents.

[root@dm01db01 ~]# cat /u01/app/oracle/cfgtoollogs/asmca/scripts/converttoFlexASM.sh  | more
#!/bin/sh

cd /u01/app/oracle/cfgtoollogs/asmca/scripts
su oracle -c “/u01/app/12.1.0.2/grid/bin/srvctl add asm -proxy”
if [ “$?” != “0” ]; then
        echo “Error adding ASM Proxy”
        exit 1
fi
/u01/app/12.1.0.2/grid/srvm/admin/srvmhelper -updateDepASM
if [ “$?” != “0” ]; then
        echo “Error updating ASM resource dependencies”
        exit 1
fi
for node in dm01db01 dm01db02 dm01db03 dm01db04 dm01db05 dm01db06 dm01db07 dm01db08
do
        /u01/app/12.1.0.2/grid/bin/crsctl stop cluster -n $node
        if [ “$?” != “0” ]; then
                echo “Error stopping Oracle Grid Infrastructure in node $node”
                exit 1
        fi
        /u01/app/12.1.0.2/grid/bin/crsctl start cluster -n $node

        stat=1
        cmd=”/u01/app/12.1.0.2/grid/bin/crsctl status server $node”
        while [ $stat = 1 ]
        do
                sleep 5
                op=`$cmd`
                op=`echo $op | /bin/cut -d’ ‘ -f2  | /bin/cut -d’=’ -f2`
                if [ “$op” = “ONLINE” ]
                then
                        stat=0
                        echo “Oracle Grid Infrastructure restarted in node $node”
                fi
        done
        if [ “$node” = dm01db01 ]; then
                for lsnr in ASMNET1LSNR_ASM
                do
                        /u01/app/12.1.0.2/grid/bin/srvctl start listener -listener $lsnr
                        lsnrret=$?
                        if [ “$lsnrret” = “2” ]; then
                                echo “ASM listener $lsnr running already”
                        elif [ “$lsnrret” != “0” ]; then
                                echo “Error starting ASM listener $lsnr”
                                exit 1
                        fi
                done
        fi
done


  • Execute the script on node 1 as root user as follows:

dm01db01-+ASM1 {/dev/asm}:df -k | grep asm
/dev/asm/acfsvol-228

Stop the ACFS file system before executing the script.

dm01db01-+ASM1 {/dev/asm}:srvctl status filesystem -device /dev/asm/acfsvol-228
ACFS file system /acfs is mounted on nodes

dm01db01,dm01db02,dm01db03,dm01db04,dm01db05,dm01db06,dm01db07,dm01db08

dm01db01-+ASM1 {/home/oracle}:srvctl stop filesystem -d /dev/asm/acfsvol-228 -f

dm01db01-+ASM1 {/home/oracle}:srvctl status filesystem -d /dev/asm/acfsvol-228
ACFS file system /acfs is not mounted

dm01db01-+ASM1 {/dev/asm}:df -k | grep asm

Finally execute the script

[root@dm01db01 ~]# /u01/app/oracle/cfgtoollogs/asmca/scripts/converttoFlexASM.sh
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘dm01db01’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.orcldb.db’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ttsrv_dm01db01’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.acfs_dg.acfsvol.acfs’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.RECO.dg’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.orcl.db’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.ACFS_DG.dg’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.ACFS_DG.dg’ on ‘dm01db01’ succeeded
CRS-2677: Stop of ‘ora.RECO.dg’ on ‘dm01db01’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘dm01db01’ succeeded
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.dm01db01.vip’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.dm01db01.vip’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.dm01db01.vip’ on ‘dm01db05’
CRS-2677: Stop of ‘ora.orcldb.db’ on ‘dm01db01’ succeeded
CRS-2677: Stop of ‘ora.orcl.db’ on ‘dm01db01’ succeeded
CRS-2676: Start of ‘ora.dm01db01.vip’ on ‘dm01db05’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.ons’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.net1.network’ on ‘dm01db01’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘dm01db01’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘dm01db01’
CRS-2673: Attempting to stop ‘ora.storage’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.storage’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.ctssd’ on ‘dm01db01’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘dm01db01’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.cssd’ on ‘dm01db01’ succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘dm01db01’
CRS-2677: Stop of ‘ora.diskmon’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.evmd’ on ‘dm01db01’
CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘dm01db01’
CRS-2676: Start of ‘ora.cssdmonitor’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.cssd’ on ‘dm01db01’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘dm01db01’
CRS-2676: Start of ‘ora.evmd’ on ‘dm01db01’ succeeded
CRS-2676: Start of ‘ora.diskmon’ on ‘dm01db01’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.ctssd’ on ‘dm01db01’
CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘dm01db01’
CRS-2676: Start of ‘ora.ctssd’ on ‘dm01db01’ succeeded
CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.asm’ on ‘dm01db01’
CRS-2676: Start of ‘ora.asm’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.storage’ on ‘dm01db01’
CRS-2676: Start of ‘ora.storage’ on ‘dm01db01’ succeeded
CRS-2672: Attempting to start ‘ora.crsd’ on ‘dm01db01’
CRS-2676: Start of ‘ora.crsd’ on ‘dm01db01’ succeeded
Oracle Grid Infrastructure restarted in node dm01db01
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘dm01db02’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘dm01db02’
CRS-2673: Attempting to stop ‘ora.acfs_dg.acfsvol.acfs’ on ‘dm01db02’
CRS-2673: Attempting to stop ‘ora.orcl.db’ on ‘dm01db02’
CRS-2673: Attempting to stop ‘ora.orcldb.db’ on ‘dm01db02’
CRS-2673: Attempting to stop ‘ora.RECO.dg’ on ‘dm01db02’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘dm01db02’
CRS-2677: Stop of ‘ora.RECO.dg’ on ‘dm01db02’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘dm01db02’ succeeded
CRS-2677: Stop of ‘ora.orcl.db’ on ‘dm01db02’ succeeded
CRS-2677: Stop of ‘ora.orcldb.db’ on ‘dm01db02’ succeeded



[root@dm01db01 ~]#

  • Go back to ASMCA and click close.

Verify the cluster mode now.

ASMCMD> showclustermode
ASM cluster : Flex mode enabled

Conclusion
In this article we have learned about Flex ASM in Oracle database 12c and how to convert a Standard ASM to Flex ASM to take advantage of 12c new feature.

0

About ACFS:

  • ACFS stands for Oracle Automatic Storage Management Cluster File System
  • ACFS extends ASM functionality to support all kind of files like database files, application files and general purpose files.
  • Oracle ACFS is a High Availability Cluster File system and it is tightly coupled with Oracle Clusterware technology.
  • It can manage large number of file systems and files.
  • One can manage ACFS/ADVM using: ASMCA, ASMCMD, SQL*PLUS, OEM and ACFS Plug-ins

In this article I will demostrate how to configure Oracle ACFS on Exadata Database machine. Before start with the actual configuration, let’s discuss the few important points about ACFS and configure it on Exadata.

Oracle Automatic Storage Management Cluster File System (ACFS) on Exadata Database Machine:

Starting with Oracle Grid Infrastructure version 12.1.0.2, Oracle ACFS supports all database files and general purpose files on Oracle Exadata Database Machine running Oracle Linux on database servers.

The following database versions are supported by Oracle ACFS on Exadata Database Machine:

  • Oracle Database 10g Rel. 2 (10.2.0.4 and 10.2.0.5)
  • Oracle Database 11g (11.2.0.4 and higher)
  • Oracle Database 12c (12.1.0.1 and higher)


Some important information about using Oracle ACFS with Exadata


  • Oracle ACFS supports all database file types and general purpose files
  • All Oracle ACFS advanced functionality is supported on Exadata including:
    • Snapshots, replication, tagging, security, encryption, audit and HANFS.


Patches required

  • Please refer to the MOS note for important fixed required for Oracle ACFS on Exadata Database Machine.
    • 2022172.1


Oracle ACFS restriction

  • Smart Scan, Storage Indexes, IO Resource Manager (IORM) are currently not supported.
  • Smart FC read operation are cached.
  • Smart FC write operations is not supported
  • Smart Flash Logging is not supported.
  • HCC is supported with fix for bug 19136936
  • Hardware Assisted Resilient Data (HARD) checks are not performed.
  • Oracle ACFS replication or security/encryption/audit is only supported with general purpose files.
  • Database files in ACFS doesn’t support replication or security/encryption/audit


Performance Optimization

  • When storing database data files on Oracle ACFS, Initialization parameter FILESYSTEMIO_OPTIONS must be set to ‘setall’. Other settings are not supported.
  • For each disk group that contains an Oracle ADVM volume that is intended to hold data files, set ASM and ADVM compatibility attributes to 12.1 or higher as this will ensure optimized performance.


Uses of ACFS on Exadata Database Machine

  • Since Oracle ACFS support database version 10.2.0.4 and 10.2.0.5, Customers can migrate their 10g database to Exadata. Later they upgrade their 10g database to 11g or higher database version.
  • Oracle ACFS can be used as a shared Oracle Home for RDBMS, Middleware, GoldenGate binaries.
  • Oracle ACFS can be used to store Oracle GoldenGate related files such as trail file, checkpoint files, parameter files and so on.


Steps to configure ACFS

It consists of 4 steps:

  1. Load ACFS/ADVM module on all the nodes
  2. Create ASM diskgroup for ACFS
  3. Create ASM Volume
  4. Create ASM Cluster File system


Current Environment Details:

  • Exadata Database Machine X5-2 Full Rack
  • Exadata Storage Software version 12.1.2.1.1
  • Oracle Grid/Database Version 12.1.0.2


I – Load ACFS/ADVM module on all the nodes

  • Login as Oracle Grid Infrastructure software owner and start asmca.

uid=1000(oracle) gid=1001(oinstall) groups=1001(oinstall),1002(dba),1003(oper),1004(asmdba)

  • Set the ORACLE HOME and SID to ASM

dm01db01-orcldb1 {/home/oracle}:. oraenv
ORACLE_SID = [orcldb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

dm01db01-+ASM1 {/home/oracle}:env | grep ORA
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/12.1.0.2/grid

  • Set DISPLAY

dm01db01-+ASM1 {/home/oracle}:export DISPLAY=10.30.201.137:0.0

  • Start asmca utility

dm01db01-+ASM1 {/home/oracle}:which asmca
/u01/app/12.1.0.2/grid/bin/asmca
dm01db01-+ASM1 {/home/oracle}:asmca





  • Make sure ACFS/ADVM modules loaded on every node in a standard cluster.


Here ACFS/ADVM module is not load on node 1.
Execute the following command to load acfs module.

[root@dm01db01 ~]# /u01/app/12.1.0.2/grid/bin/acfsload -h
ACFS-9228: usage: acfsload [-h] {start|stop} [-s]

[root@dm01db01 ~]# /u01/app/12.1.0.2/grid/bin/acfsload start
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device ‘/dev/asm/.asm_ctl_spec’.
ACFS-9156: Detecting control device ‘/dev/ofsctl’.
ACFS-9322: completed

  • Verify ACFS/ADVM module is loaded.

[root@dm01db01 ~]# lsmod | grep oracle
oracleacfs           3310765  0
oracleadvm            506070  0
oracleoks             508392  2 oracleacfs,oracleadvm

On all nodes:

[root@dm01db01 ~]# dcli -g dbs_group -l root ‘lsmod | grep oracle’
dm01db01: oracleacfs           3310765  0
dm01db01: oracleadvm            506070  0
dm01db01: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db02: oracleacfs           3310765  0
dm01db02: oracleadvm            506070  0
dm01db02: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db03: oracleacfs           3310765  0
dm01db03: oracleadvm            506070  0
dm01db03: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db04: oracleacfs           3310765  0
dm01db04: oracleadvm            506070  0
dm01db04: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db05: oracleacfs           3310765  0
dm01db05: oracleadvm            506070  0
dm01db05: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db06: oracleacfs           3310765  0
dm01db06: oracleadvm            506070  0
dm01db06: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db07: oracleacfs           3310765  0
dm01db07: oracleadvm            506070  0
dm01db07: oracleoks             508392  2 oracleacfs,oracleadvm
dm01db08: oracleacfs           3310765  0
dm01db08: oracleadvm            506070  0
dm01db08: oracleoks             508392  2 oracleacfs,oracleadvm

[root@dm01db01 ~]# vi acfs_validate_module.sh

[root@dm01db01 ~]# cat acfs_validate_module.sh
#################################################
###
### ACFS/ADVM modules validation
###
#################################################
###
### Author: Esteban D Bernal
###
#################################################

/u01/app/12.1.0.2/grid/bin/acfsdriverstate loaded
/u01/app/12.1.0.2/grid/bin/acfsdriverstate installed
/u01/app/12.1.0.2/grid/bin/acfsdriverstate supported
/u01/app/12.1.0.2/grid/bin/acfsdriverstate version
/u01/app/12.1.0.2/grid/bin/acfsroot version_check

lsmod | grep oracle

## Where: “/u01/app/12.1.0.2/grid” is the Grid Infrastructure Home directory,
## thus you need to replace this value with your Grid Infrastructure Home directory path.

[root@dm01db01 ~]# chmod +x acfs_validate_module.sh

[root@dm01db01 ~]# ls -l acfs_validate_module.sh
-rwxr-xr-x 1 root root 673 Jan 25 05:02 acfs_validate_module.sh

[root@dm01db01 ~]# sh -x acfs_validate_module.sh
+ /u01/app/12.1.0.2/grid/bin/acfsdriverstate loaded
ACFS-9203: true
+ /u01/app/12.1.0.2/grid/bin/acfsdriverstate installed
ACFS-9203: true
+ /u01/app/12.1.0.2/grid/bin/acfsdriverstate supported
ACFS-9200: Supported
+ /u01/app/12.1.0.2/grid/bin/acfsdriverstate version
ACFS-9325:     Driver OS kernel version = 2.6.39-400.3.0.el6uek.x86_64(x86_64).
ACFS-9326:     Driver Oracle version = 140611.5.
+ /u01/app/12.1.0.2/grid/bin/acfsroot version_check
ACFS-9316: Valid ADVM/ACFS distribution media detected at: ‘/u01/app/12.1.0.2/grid/usm/install/Oracle/EL6UEK/x86_64/2.6.39-400/2.6.39-400-x86_64/bin’
+ lsmod
+ grep oracle
oracleacfs           3310765  0
oracleadvm            506070  0
oracleoks             508392  2 oracleacfs,oracleadvm


  • Refresh or Exit and start ASMCA again.


I still see that ACFS/ADVM is “Not Installed” on node 1 though we have enabled/loaded it manually on node 1 and verified. Let’s move on and see if it is just a BUG. If there is no issue we should be able to configure ASM Volume and ACFS without any issues.

II – Create ASM diskgroup for ACFS

  • On Disk Groups page click on “Create”



  • On Create Disk Group page, specify:
    • Disk Group Name: ACFS_DG
    • Redundancy: Normal
    • Select Member Disk: Show Eligible
    • Select all candidate disks to be part of ACFS_DG disk group
    • In Exadata each Storage cell is a failure Group
    • Click on Show Advanced Options, specify:
    • Allocation Unit Size: 4 (MB)
    • Minimun software version for ASM/Database/ADVM: 12.1.0.0.0
    • Click Ok



  • Click Ok



  • We can see our ACFS_DG Disk Group created



  • Verify newly created ACFS_DG Disk Group as follows:

SQL> select inst_id, name, total_mb, group_number from gv$asm_diskgroup where name like ‘ACFS_DG’;

   INST_ID NAME                             TOTAL_MB GROUP_NUMBER
———- —————————— ———- ————
         1 ACFS_DG                           4145536            3
         3 ACFS_DG                           4145536            3
         6 ACFS_DG                           4145536            3
         5 ACFS_DG                           4145536            3
         7 ACFS_DG                           4145536            3
         4 ACFS_DG                           4145536            3
         2 ACFS_DG                           4145536            3
         8 ACFS_DG                           4145536            3

8 rows selected.

SQL> col value for a30
SQL> col name for a30
SQL> select name, value from v$asm_attribute where GROUP_NUMBER=3 and name like ‘compatible%’;

NAME                           VALUE
—————————— ——————————
compatible.asm                 12.1.0.0.0
compatible.rdbms               12.1.0.0.0
compatible.advm                12.1.0.0.0

dm01db01-+ASM1 {/home/oracle}:/u01/app/12.1.0.2/grid/bin/crsctl stat res ora.ACFS_DG.dg -t
——————————————————————————–
Name           Target  State        Server                   State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ACFS_DG.dg
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
——————————————————————————–

III – Create ASM Volume

  • Click on “Volumes” tab



  • On Volumes page click on “Create”
    • On Create Volume page, specify:
    • Volumn name: acfsvol
    • Disk Group name: ACFS_DG
    • Size: 2008.25 (it can be anything based on your requirement from usable space avaialble.)
    • Click Ok




  • Click Ok



  • We can see our Volume created



  • Verify newly created Volume as follows:

ASMCMD> volinfo –all
Diskgroup Name: ACFS_DG

         Volume Name: ACFSVOL
         Volume Device: /dev/asm/acfsvol-228
         State: ENABLED
         Size (MB): 2056448
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage:
         Mountpath:

dm01db01-+ASM1 {/home/oracle}:/u01/app/12.1.0.2/grid/bin/crsctl stat res ora.ACFS_DG.ACFSVOL.advm -t
——————————————————————————–
Name           Target  State        Server                   State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ACFS_DG.ACFSVOL.advm
               ONLINE  ONLINE       dm01db01                 STABLE
               ONLINE  ONLINE       dm01db02                 STABLE
               ONLINE  ONLINE       dm01db03                 STABLE
               ONLINE  ONLINE       dm01db04                 STABLE
               ONLINE  ONLINE       dm01db05                 STABLE
               ONLINE  ONLINE       dm01db06                 STABLE
               ONLINE  ONLINE       dm01db07                 STABLE
               ONLINE  ONLINE       dm01db08                 STABLE
——————————————————————————–

dm01db01-+ASM1 {/home/oracle}:dcli -g ~/dbs_group -l oracle ‘ls -l /dev/asm/*’
dm01db01: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db02: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db03: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db04: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db05: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db06: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db07: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228
dm01db08: brwxrwx— 1 root dba 251, 116737 Jan 25 06:14 /dev/asm/acfsvol-228

IV – Create ASM Cluster File system

  • Create ACFS mount point directory on all nodes

[root@dm01db01 ~]# dcli -g ~/dbs_group -l root ‘mkdir /acfs’

[root@dm01db01 ~]# dcli -g ~/dbs_group -l root ‘chown oracle:oinstall /acfs’

[root@dm01db01 ~]# dcli -g ~/dbs_group -l root ‘chmod 775 /acfs’

[root@dm01db01 ~]# dcli -g ~/dbs_group -l root ‘ls -ld /acfs’
dm01db01: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db02: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db03: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db04: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db05: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db06: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db07: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs
dm01db08: drwxrwxr-x 2 oracle oinstall 4096 Jan 25 06:28 /acfs


  • Click on “ASM Cluster File Systems” tab




  • On ASM Cluster File Systems page click on “Create”



  • On Create ASM Cluster File System page, specify:
    • Type of ACFS: Cluster File System
    • Mount point: /acfs
    • Auto Mount: check
    • User Name: oracle
    • Group Name: oinstall
    • Description: ACFS File System
    • Select Volume: ACFSVOL – /dev/asm/acfsvol-228 – 2008.25
    • Click Ok



  • Run the ACFS script on Node 1 only to register ACFS with Grid Infrastructure and to mount the ACFS file system.

  • Contents of ACFS script looks like this:

[root@dm01db01 ~]# cat /u01/app/oracle/cfgtoollogs/asmca/scripts/acfs_script.sh
#!/bin/sh

/u01/app/12.1.0.2/grid/bin/srvctl add filesystem -d /dev/asm/acfsvol-228 -m /acfs -u oracle -fstype ACFS -description ‘ACFS File System’ -autostart ALWAYS
if [ $? = “0” -o $? = “2” ]; then
   /u01/app/12.1.0.2/grid/bin/srvctl start filesystem -d /dev/asm/acfsvol-228
   if [ $? = “0” ]; then
      chown oracle:oinstall /acfs
      chmod 775 /acfs
      /u01/app/12.1.0.2/grid/bin/srvctl status filesystem -d /dev/asm/acfsvol-228
      exit 0
   fi
   /u01/app/12.1.0.2/grid/bin/srvctl status filesystem -d /dev/asm/acfsvol-228
fi


  • Execute the script

[root@dm01db01 ~]# /u01/app/oracle/cfgtoollogs/asmca/scripts/acfs_script.sh
ACFS file system /acfs is mounted on nodes dm01db01,dm01db02,dm01db03,dm01db04,dm01db05,dm01db06,dm01db07,dm01db08


  • Click Close



  • We can see our Volume created



  • Verify newly created Volume as follows:

dm01db01-+ASM1 {/home/oracle}:/u01/app/12.1.0.2/grid/bin/crsctl stat res ora.acfs_dg.acfsvol.acfs -t
——————————————————————————–
Name           Target  State        Server                   State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.acfs_dg.acfsvol.acfs
               ONLINE  ONLINE       dm01db01                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db02                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db03                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db04                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db05                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db06                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db07                 mounted on /acfs,STA
                                                             BLE
               ONLINE  ONLINE       dm01db08                 mounted on /acfs,STA
                                                             BLE
——————————————————————————–

V – Test ACFS Cluster file system

dm01db01-+ASM1 {/home/oracle}:cd /acfs

dm01db01-+ASM1 {/acfs}:ls -ltr
total 64
drwx—— 2 root root 65536 Jan 25 07:11 lost+found

dm01db01-+ASM1 {/acfs}:mkdir test

dm01db01-+ASM1 {/acfs}:cd test/

dm01db01-+ASM1 {/acfs/test}:vi myfile.txt

dm01db01-+ASM1 {/acfs/test}:cat myfile.txt
this is a test

dm01db01-+ASM1 {/home/oracle}:dcli -g ~/dbs_group -l oracle ‘cat /acfs/test/myfile.txt’
dm01db01: this is a test
dm01db02: this is a test
dm01db03: this is a test
dm01db04: this is a test
dm01db05: this is a test
dm01db06: this is a test
dm01db07: this is a test
dm01db08: this is a test

dm01db01-+ASM1 {/home/oracle}:dcli -g ~/dbs_group -l oracle ‘df -h /acfs’
dm01db01: Filesystem            Size  Used Avail Use% Mounted on
dm01db01: /dev/asm/acfsvol-228
dm01db01: 2.0T  4.3G  2.0T   1% /acfs
dm01db02: Filesystem            Size  Used Avail Use% Mounted on
dm01db02: /dev/asm/acfsvol-228
dm01db02: 2.0T  4.3G  2.0T   1% /acfs
dm01db03: Filesystem            Size  Used Avail Use% Mounted on
dm01db03: /dev/asm/acfsvol-228
dm01db03: 2.0T  4.3G  2.0T   1% /acfs
dm01db04: Filesystem            Size  Used Avail Use% Mounted on
dm01db04: /dev/asm/acfsvol-228
dm01db04: 2.0T  4.3G  2.0T   1% /acfs
dm01db05: Filesystem            Size  Used Avail Use% Mounted on
dm01db05: /dev/asm/acfsvol-228
dm01db05: 2.0T  4.3G  2.0T   1% /acfs
dm01db06: Filesystem            Size  Used Avail Use% Mounted on
dm01db06: /dev/asm/acfsvol-228
dm01db06: 2.0T  4.3G  2.0T   1% /acfs
dm01db07: Filesystem            Size  Used Avail Use% Mounted on
dm01db07: /dev/asm/acfsvol-228
dm01db07: 2.0T  4.3G  2.0T   1% /acfs
dm01db08: Filesystem            Size  Used Avail Use% Mounted on
dm01db08: /dev/asm/acfsvol-228
dm01db08: 2.0T  4.3G  2.0T   1% /acfs

dm01db01-+ASM1 {/home/oracle}:dcli -g ~/dbs_group -l oracle ‘ls -ld /acfs’
dm01db01: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db02: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db03: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db04: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db05: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db06: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db07: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs
dm01db08: drwxrwxr-x 4 oracle oinstall 4096 Jan 25 07:11 /acfs


Conclusion
In this article we have learned basic concepts about ACFS and it works on Exadata. We also seen a practicle demonstration on how to Load ACFS/ADVM module on all the nodes, Create ASM diskgroup for ACFS, Create ASM Volume and Create ASM Cluster File system.


2

Overview
  • The Exadata network grid consists of multiple Sun QDR InfiniBand switches.
  • IB Switches are used for the storage network as well as the Oracle RAC interconnect.
  • Exadata compute nodes and storage cells are configured with dual-port InfiniBand ports and connect to each of the two leaf switches.
  • You can access IB Switches using command line and Web ILOM
  • IB Switches run Linux operating system.

In this article I will demonstrate how to patch or upgrade Oracle Exadata IB Switches.

About Infiniband Switch Patching

  • Starting with release 11.2.3.3.0, the patchmgr utility is used to upgrade and downgrade the InfiniBand switches.
  • IB Switch patch is delievered with Exadata storage patch.
  • IB Switch patches are released semi annually to annually.
  • IB Switch can be patched in Rolling and Non-Rolling fashion.

Environment
  • Exadata Full Rack X2-2
  • 8 Compute nodes, 14 Storage cells and 3 IB Switches
  • Current IB Switch Version 2.1.5-1

Infiniband Switch Upgrade Steps

  • Identify the number of switches in clusters.
[root@dm01dbadm01 ~]# ibswitches
Switch  : 0x002128469b8aa0a0 ports 36 “SUN DCS 36P QDR dm01sw-ib1 10.209.41.246” enhanced port 0 lid 5 lmc 0
Switch  : 0x002128469b97a0a0 ports 36 “SUN DCS 36P QDR dm01sw-ib2 10.209.41.247” enhanced port 0 lid 4 lmc 0
Switch  : 0x002128469b82a0a0 ports 36 “SUN DCS 36P QDR dm01sw-ib3 10.209.41.248” enhanced port 0 lid 3 lmc 0

  • Identify the current IB switch software version on all the Switches
[root@dm01dbadm01 patch_12.1.2.2.0.150917]# ssh dm01sw-ib1 version
SUN DCS 36p version: 2.1.5-1
Build time: Oct  6 2014 10:35:15
SP board info:
Manufacturing Date: 2011.01.12
Serial Number: “NCD680421”
Hardware Revision: 0x0006
Firmware Revision: 0x0000
BIOS version: SUN0R100
BIOS date: 06/22/2010

  • Log in to Exadata Compute node 1 as root user and navigate the Exadata Storage Software staging area
[root@dm01dbadm01 ESS_121220]# cd /u01/app/oracle/software/ESS_121220/patch_12.1.2.2.0.150917/
[root@dm01dbadm01 patch_12.1.2.2.0.150917]# pwd
/u01/app/oracle/software/ESS_121220/patch_12.1.2.2.0.150917

  • Create a file named ibswitches.lst and enter IB switch names one per line as follows:
[root@dm01dbadm01 patch_12.1.2.2.0.150917]# vi ibswitches.lst
dm01sw-ib1
dm01sw-ib2
dm01sw-ib3

[root@dm01dbadm01 patch_12.1.2.2.0.150917]# cat ibswitches.lst
dm01sw-ib1
dm01sw-ib2
dm01sw-ib3

  • Execute the following to perform the IB Switch precheck
[root@dm01dbadm01 patch_12.1.2.2.0.150917]# ./patchmgr -ibswitches ibswitches.lst -upgrade -ibswitch_precheck -force

2015-11-04 11:31:44 -0600 1 of 1 :SUCCESS: DO: Initiate pre-upgrade validation check on InfiniBand switch(es).
 —– InfiniBand switch update process started 2015-11-04 11:31:44 -0600 —–
[NOTE     ] Log file at /u01/app/oracle/software/ESS_121220/patch_12.1.2.2.0.150917/upgradeIBSwitch.log

[INFO     ] List of InfiniBand switches for upgrade: ( dm01sw-ib1 dm01sw-ib2 dm01sw-ib3 )

[SUCCESS  ] Verifying Network connectivity to dm01sw-ib1
[SUCCESS  ] Verifying Network connectivity to dm01sw-ib2
[SUCCESS  ] Verifying Network connectivity to dm01sw-ib3
[FAIL     ] Validating verify-topology output
[INFO     ] Master Subnet Manager is set to “dm01sw-ib3” in all Switches

[INFO     ] ———- Starting with IBSwitch dm01sw-ib1
[WARNING  ] Infiniband switch meets minimal version requirements, but rollback is only available to 2.1.5-1 with the current package.
     To rollback to other versions:
     – Manually download the InfiniBand switch firmware package to the patch directory
     – Set export variable “EXADATA_IMAGE_IBSWITCH_ROLLBACK_VERSION” to the appropriate version
     – Run patchmgr command to initiate rollback.
[SUCCESS  ] Verifying that /tmp has 120M in dm01sw-ib1, found 137M
[SUCCESS  ] Verifying that / has 80M in dm01sw-ib1, found 197M
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib1
[SUCCESS  ] Verifying that dm01sw-ib1 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 17:41:20
[SUCCESS  ] Execute plugin check for Patch Check Prereq on dm01sw-ib1
[SUCCESS  ] Pre-update validation on dm01sw-ib1

[INFO     ] ———- Starting with InfiniBand Switch dm01sw-ib2
[WARNING  ] Infiniband switch meets minimal version requirements, but rollback is only available to 2.1.5-1 with the current package.
     To rollback to other versions:
     – Manually download the InfiniBand switch firmware package to the patch directory
     – Set export variable “EXADATA_IMAGE_IBSWITCH_ROLLBACK_VERSION” to the appropriate version
     – Run patchmgr command to initiate rollback.
[SUCCESS  ] Verifying that /tmp has 120M in dm01sw-ib2, found 249M
[SUCCESS  ] Verifying that / has 80M in dm01sw-ib2, found 199M
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib2
[SUCCESS  ] Verifying that dm01sw-ib2 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 17:41:33
[SUCCESS  ] Execute plugin check for Patch Check Prereq on dm01sw-ib2
[SUCCESS  ] Pre-update validation on dm01sw-ib2

[INFO     ] ———- Starting with InfiniBand Switch dm01sw-ib3
[WARNING  ] Infiniband switch meets minimal version requirements, but rollback is only available to 2.1.5-1 with the current package.
     To rollback to other versions:
     – Manually download the InfiniBand switch firmware package to the patch directory
     – Set export variable “EXADATA_IMAGE_IBSWITCH_ROLLBACK_VERSION” to the appropriate version
     – Run patchmgr command to initiate rollback.
[SUCCESS  ] Verifying that /tmp has 120M in dm01sw-ib3, found 248M
[SUCCESS  ] Verifying that / has 80M in dm01sw-ib3, found 196M
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib3
[SUCCESS  ] Verifying that dm01sw-ib3 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 17:41:42
[SUCCESS  ] Execute plugin check for Patch Check Prereq on dm01sw-ib3
[SUCCESS  ] Pre-update validation on dm01sw-ib3
[SUCCESS  ] Overall status

 —– InfiniBand switch update process ended 2015-11-04 11:41:42 -0600 —–
2015-11-04 11:41:42 -0600 1 of 1 :SUCCESS: DONE: Initiate pre-upgrade validation check on InfiniBand switch(es).

  • Upgrade the IB Switches using the following command:
[root@dm01dbadm01 patch_12.1.2.2.0.150917]# ./patchmgr -ibswitches ibswitches.lst -upgrade -force

2015-11-04 11:42:51 -0600 1 of 1 :SUCCESS: DO: Initiate upgrade of InfiniBand switches to 2.1.6-2. Expect up to 15 minutes for each switch
 —– InfiniBand switch update process started 2015-11-04 11:42:51 -0600 —–
[NOTE     ] Log file at /u01/app/oracle/software/ESS_121220/patch_12.1.2.2.0.150917/upgradeIBSwitch.log

[INFO     ] List of InfiniBand switches for upgrade: ( dm01sw-ib1 dm01sw-ib2 dm01sw-ib3 )

[SUCCESS  ] Verifying Network connectivity to dm01sw-ib1
[SUCCESS  ] Verifying Network connectivity to dm01sw-ib2
[SUCCESS  ] Verifying Network connectivity to dm01sw-ib3
[FAIL     ] Validating verify-topology output
[INFO     ] Proceeding with upgrade of InfiniBand switches to version 2.1.6_2
[INFO     ] Master Subnet Manager is set to “aibdalab1sw-ib3” in all Switches

[INFO     ] ———- Starting with IBSwitch dm01sw-ib1
[WARNING  ] Infiniband switch meets minimal version requirements, but rollback is only available to 2.1.5-1 with the current package.
     To rollback to other versions:
     – Manually download the InfiniBand switch firmware package to the patch directory
     – Set export variable “EXADATA_IMAGE_IBSWITCH_ROLLBACK_VERSION” to the appropriate version
     – Run patchmgr command to initiate rollback.
[SUCCESS  ] Copy firmware packages to dm01sw-ib1
[SUCCESS  ] Verifying that /tmp has 120M in dm01sw-ib1, found 137M
[SUCCESS  ] Verifying that / has 80M in dm01sw-ib1, found 197M
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib1
[SUCCESS  ] Service opensmd is running on InfiniBand Switch dm01sw-ib1
[SUCCESS  ] Verifying that dm01sw-ib1 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 17:44:09
[SUCCESS  ] Execute plugin check for Patch Check Prereq on dm01sw-ib1
[SUCCESS  ] Pre-update validation on dm01sw-ib1
[SUCCESS  ] Disable Subnet Manager on dm01sw-ib1
[INFO     ] Starting upgrade on dm01sw-ib1 to 2.1.6_2. Please give upto 10 mins for the process to complete. DO NOT INTERRUPT or HIT CTRL+C during the upgrade
[SUCCESS  ] Execute plugin check for Patching on dm01sw-ib1
[SUCCESS  ] Load firmware 2.1.6_2 onto dm01sw-ib1
[SUCCESS  ] Disable Subnet Manager on dm01sw-ib1
[SUCCESS  ] Set SMPriority to 8 on dm01sw-ib1
[INFO     ] Rebooting dm01sw-ib1. Wait for 240 secs before continuing
[SUCCESS  ] Reboot dm01sw-ib1
[SUCCESS  ] SUCCESS
[INFO     ] Starting post-update validation on dm01sw-ib1
[SUCCESS  ] Inifiniband switch dm01sw-ib1 is at target patching level
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib1
[SUCCESS  ] Service opensmd is running on InfiniBand Switch dm01sw-ib1
[SUCCESS  ] Verifying that dm01sw-ib1 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 18:01:21
[SUCCESS  ] Firmware verification on InfiniBand switch dm01sw-ib1
[SUCCESS  ] Execute plugin check for Post Patch on dm01sw-ib1
[SUCCESS  ] Post-check validation on IBSwitch dm01sw-ib1
[SUCCESS  ] Update switch dm01sw-ib1 to 2.1.6_2

[INFO     ] ———- Starting with InfiniBand Switch dm01sw-ib2
[WARNING  ] Infiniband switch meets minimal version requirements, but rollback is only available to 2.1.5-1 with the current package.
     To rollback to other versions:
     – Manually download the InfiniBand switch firmware package to the patch directory
     – Set export variable “EXADATA_IMAGE_IBSWITCH_ROLLBACK_VERSION” to the appropriate version
     – Run patchmgr command to initiate rollback.
[SUCCESS  ] Copy firmware packages to dm01sw-ib2
[SUCCESS  ] Verifying that /tmp has 120M in dm01sw-ib2, found 138M
[SUCCESS  ] Verifying that / has 80M in dm01sw-ib2, found 199M
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib2
[SUCCESS  ] Service opensmd is running on InfiniBand Switch dm01sw-ib2
[SUCCESS  ] Verifying that dm01sw-ib2 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 18:06:23
[SUCCESS  ] Execute plugin check for Patch Check Prereq on dm01sw-ib2
[SUCCESS  ] Pre-update validation on dm01sw-ib2
[SUCCESS  ] Disable Subnet Manager on dm01sw-ib2
[INFO     ] Starting upgrade on dm01sw-ib2 to 2.1.6_2. Please give upto 10 mins for the process to complete. DO NOT INTERRUPT or HIT CTRL+C during the upgrade
[SUCCESS  ] Execute plugin check for Patching on dm01sw-ib2
[SUCCESS  ] Load firmware 2.1.6_2 onto dm01sw-ib2
[SUCCESS  ] Disable Subnet Manager on dm01sw-ib2
[SUCCESS  ] Set SMPriority to 2 on dm01sw-ib2
[INFO     ] Rebooting dm01sw-ib2. Wait for 240 secs before continuing
[SUCCESS  ] Reboot dm01sw-ib2
[SUCCESS  ] SUCCESS
[INFO     ] Starting post-update validation on dm01sw-ib2
[SUCCESS  ] Inifiniband switch dm01sw-ib2 is at target patching level
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib2
[SUCCESS  ] Service opensmd is running on InfiniBand Switch dm01sw-ib2
[SUCCESS  ] Verifying that dm01sw-ib2 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 18:23:30
[SUCCESS  ] Firmware verification on InfiniBand switch dm01sw-ib2
[SUCCESS  ] Execute plugin check for Post Patch on dm01sw-ib2
[SUCCESS  ] Post-check validation on IBSwitch dm01sw-ib2
[SUCCESS  ] Update switch dm01sw-ib2 to 2.1.6_2

[INFO     ] ———- Starting with InfiniBand Switch dm01sw-ib3
[WARNING  ] Infiniband switch meets minimal version requirements, but rollback is only available to 2.1.5-1 with the current package.
     To rollback to other versions:
     – Manually download the InfiniBand switch firmware package to the patch directory
     – Set export variable “EXADATA_IMAGE_IBSWITCH_ROLLBACK_VERSION” to the appropriate version
     – Run patchmgr command to initiate rollback.
[SUCCESS  ] Copy firmware packages to dm01sw-ib3
[SUCCESS  ] Verifying that /tmp has 120M in dm01sw-ib3, found 137M
[SUCCESS  ] Verifying that / has 80M in dm01sw-ib3, found 196M
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib3
[SUCCESS  ] Service opensmd is running on InfiniBand Switch dm01sw-ib3
[SUCCESS  ] Verifying that dm01sw-ib3 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 18:28:26
[SUCCESS  ] Execute plugin check for Patch Check Prereq on dm01sw-ib3
[SUCCESS  ] Pre-update validation on dm01sw-ib3
[SUCCESS  ] Disable Subnet Manager on dm01sw-ib3
[INFO     ] Starting upgrade on dm01sw-ib3 to 2.1.6_2. Please give upto 10 mins for the process to complete. DO NOT INTERRUPT or HIT CTRL+C during the upgrade
[SUCCESS  ] Execute plugin check for Patching on dm01sw-ib3
[SUCCESS  ] Load firmware 2.1.6_2 onto dm01sw-ib3
[SUCCESS  ] Disable Subnet Manager on dm01sw-ib3
[SUCCESS  ] Set SMPriority to 2 on dm01sw-ib3
[INFO     ] Rebooting dm01sw-ib3. Wait for 240 secs before continuing
[SUCCESS  ] Reboot dm01sw-ib3
[SUCCESS  ] SUCCESS
[INFO     ] Starting post-update validation on dm01sw-ib3
[SUCCESS  ] Inifiniband switch dm01sw-ib3 is at target patching level
[SUCCESS  ] Service ntpd is running on InfiniBand Switch dm01sw-ib3
[SUCCESS  ] Service opensmd is running on InfiniBand Switch dm01sw-ib3
[SUCCESS  ] Verifying that dm01sw-ib3 has at least 1 NTP Server, found 1
[SUCCESS  ] opensm.conf passed all validations
[INFO     ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-11-04 Time:(HH:MM:SS) 18:46:04
[SUCCESS  ] Firmware verification on InfiniBand switch dm01sw-ib3
[SUCCESS  ] Execute plugin check for Post Patch on dm01sw-ib3
[SUCCESS  ] Post-check validation on IBSwitch dm01sw-ib3
[SUCCESS  ] Update switch dm01sw-ib3 to 2.1.6_2
[INFO     ] InfiniBand Switches ( dm01sw-ib1 dm01sw-ib2 dm01sw-ib3 ) updated to 2.1.6_2
[SUCCESS  ] Overall status

 —– InfiniBand switch update process ended 2015-11-04 12:50:22 -0600 —–
2015-11-04 12:50:22 -0600 1 of 1 :SUCCESS: DONE: Upgrade InfiniBand switch(es) to 2.1.6-2.


  • Verify that all the IB Switches are upgraded to latest version.
[root@dm01dbadm01 ~]# ssh dm01sw-ib1 version
SUN DCS 36p version: 2.1.6-2
Build time: Dec  8 2014 10:36:50
SP board info:
Manufacturing Date: 2011.01.12
Serial Number: “NCD680421”
Hardware Revision: 0x0006
Firmware Revision: 0x0000
BIOS version: SUN0R100
BIOS date: 06/22/2010
[root@dm01dbadm01 ~]#

 

Conclusion
In this article we have demonstrated how to patch Exadata IB Switches using patchmgr utility. Patching an Exadata IB switch is very straight forward and can be done in rolling and non-rolling fashion.
0

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 not using SPFILE.

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
nospfile
Hostname
NSM-TRG
Ip Address
172.16.110.16
OS
Linux
Version
x86_64
Datafile Location
/data2/nospfile/
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.
  • Auxiliary instance should be start with pfile in nomount stage. 
Steps on source server 
  • Set Oracle sid on which backup will perform.
[oracle@NSM-SRC dbs]$ hostname

NSM-SRC

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

  • Check database is running from pfile or spfile.
NAME       OPEN_MODE

———- ————–

PROD       READ WRITE



SQL> select value from v$parameter where name=’spfile’;



VALUE

——————–



or



SQL> show parameter pfile



NAME                                 TYPE                              VALUE

———————————— ——————————— ——————————

spfile                               string


  • Connect to target instance via RMAN.
[oracle@NSM-SRC dbs]$ rman target sys/oracle



Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 13 10:13:01 2015



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



connected to target database: PROD (DBID=284539893)



RMAN>

  • 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>

  • 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/initnospfile.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/

  •   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:
  • Create new directory structure.
[oracle@NSM-TRG dbs]$ mkdir -p /data2/nospfile/

  •   Edit the pfile which we have copied from source server.
/data1/oracle11g/product/dbs

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

Here we have modified new controlfile location and db_name parameters

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

*.db_name=’nospfile’


  • Create password file

[oracle@NSM-TRG dbs]$ orapwd file=orapwnospfile password=oracle
 
  • Start the instance in no mount stage.
[oracle@NSM-TRG dbs]$ export ORACLE_SID=nospfile

[oracle@NSM-TRG dbs]$ sqlplus



SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 10:42:46 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>



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]$

  • Connect to auxiliary instance via RMAN and issue the duplicate command.
[oracle@NSM-TRG dbs]$ rman auxiliary sys/oracle



Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 13 10:47:05 2015



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



connected to auxiliary database: NOSPFILE (not mounted)

RMAN>



RMAN> DUPLICATE TARGET DATABASE TO nospfile BACKUP LOCATION ‘/backup/rman_backup’

DB_FILE_NAME_CONVERT ‘/data2/prod’,’/data2/nospfile’

LOGFILE GROUP 1(‘/data2/nospfile/redo01.log’) SIZE 50m,

GROUP 2(‘/data2/nospfile/redo02.log’) SIZE 50m;



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 =

 ”NOSPFILE” 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 =  ”NOSPFILE” 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/nospfile/control01.ctl

output file name=/data2/nospfile/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/nospfile/system01.dbf”;

   set newname for datafile  2 to

 “/data2/nospfile/sysaux01.dbf”;

   set newname for datafile  3 to

 “/data2/nospfile/undotbs01.dbf”;

   set newname for datafile  4 to

 “/data2/nospfile/users01.dbf”;

   set newname for datafile  5 to

 “/data2/nospfile/example01.dbf”;

   set newname for datafile  6 to

 “/data2/nospfile/data01.dbf”;

   set newname for datafile  7 to

 “/data2/nospfile/test01.dbf”;

   set newname for datafile  8 to

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

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

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

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

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

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

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

channel ORA_AUX_DISK_1: restoring datafile 00008 to /data2/nospfile/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:00:45

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=876912854 file name=/data2/nospfile/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=10 STAMP=876912855 file name=/data2/nospfile/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=11 STAMP=876912856 file name=/data2/nospfile/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=876912857 file name=/data2/nospfile/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=13 STAMP=876912857 file name=/data2/nospfile/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=14 STAMP=876912858 file name=/data2/nospfile/data01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=15 STAMP=876912859 file name=/data2/nospfile/test01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=16 STAMP=876912860 file name=/data2/nospfile/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=876912867

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 =

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

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

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

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

 DATAFILE

  ‘/data2/nospfile/system01.dbf’

 CHARACTER SET WE8MSWIN1252





contents of Memory Script:

{

   set newname for tempfile  1 to

 “/data2/nospfile/temp01.dbf”;

   switch clone tempfile all;

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

 “/data2/nospfile/undotbs01.dbf”,

 “/data2/nospfile/users01.dbf”,

 “/data2/nospfile/example01.dbf”,

 “/data2/nospfile/data01.dbf”,

 “/data2/nospfile/test01.dbf”,

 “/data2/nospfile/tools01.dbf”;

   switch clone datafile all;

}

executing Memory Script



executing command: SET NEWNAME



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



cataloged datafile copy

datafile copy file name=/data2/nospfile/sysaux01.dbf RECID=1 STAMP=876912891

cataloged datafile copy

datafile copy file name=/data2/nospfile/undotbs01.dbf RECID=2 STAMP=876912892

cataloged datafile copy

datafile copy file name=/data2/nospfile/users01.dbf RECID=3 STAMP=876912892

cataloged datafile copy

datafile copy file name=/data2/nospfile/example01.dbf RECID=4 STAMP=876912892

cataloged datafile copy

datafile copy file name=/data2/nospfile/data01.dbf RECID=5 STAMP=876912893

cataloged datafile copy

datafile copy file name=/data2/nospfile/test01.dbf RECID=6 STAMP=876912893

cataloged datafile copy

datafile copy file name=/data2/nospfile/tools01.dbf RECID=7 STAMP=876912894



datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=876912891 file name=/data2/nospfile/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=876912892 file name=/data2/nospfile/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=876912892 file name=/data2/nospfile/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=876912892 file name=/data2/nospfile/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=876912893 file name=/data2/nospfile/data01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=6 STAMP=876912893 file name=/data2/nospfile/test01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=876912894 file name=/data2/nospfile/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, without using spfile

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

0

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 are performing active duplication and source database is in no archive log mode.
When you issue the command to duplicate database from target database where source Database is running in noarchivelog mode, you will receive following error.
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
 
 On this situation we have one solution put the target database in mount stage and perform the steps.
Target (source) details:
Database Name
dhstg
Hostname
Nsmdev01
Ip Address
172.16.110.18
OS
Linux
Version
x86_64
Datafile Location
/data1/dhstg
Backup Location
/backup/rman_backup

Tns details:
DHSTG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Nsmdev01.corp.netsoftmate.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dhstg.corp.netsoftmate.com)
    )
  )

Destination details:
Database Name
dhstg
Hostname
Nsmstg01
Ip Address
172.16.110.16
OS
Linux
Version
x86_64
Datafile Location
/data1/diff/dhstg
Backup Location
/backup/rman_backup

Tns details:
dhstg_dup =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dhstg_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.
  • Target database instance should be in mount mode.
Steps on target (source) server:

1. Put tns entry of auxiliary database into target $ORACLE_HOME/network/admin directory

[oracle@Nsmdev01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
[oracle@Nsmdev01 admin]$ vi tnsnames.ora

dhstg_dup =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dhstg_dup)
    )
  )


[[oracle@Nsmdev01 admin]$ tnsping dhstg_dup

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:34:39

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 = dhstg_dup)))
OK (0 msec)
2. 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
——— ——————– ————
DHSTG    READ WRITE           NOARCHIVELOG

3. Check database is running from spfile. While duplicating database with different directory structure target database must be running with spfile.

SQL> select value from v$parameter where name=’spfile’;

VALUE
——————————————————————————–
/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora


SQL> show parameter pfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /data2/app/oracle/product/11.2                                                 .0/dbs/spfiledhstg.ora

4. Create pfile from spfile

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

File created.

5. Create password file.

[oracle@Nsmdev01 dbs]$ orapwd file=orapwdhstg password=oracle

6. Copy the password file and init file to destination $ORACLE_HOME/dbs directory using scp or ftp

[oracle@Nsmdev01 dbs]$ scp orapwdhstg oracle@172.16.110.16:/data2/app/oracle/product/11.2.0/dbs/
oracle@172.16.110.16’s password:
orapwdhstg                                                                                                                                                        100% 1536     1.5KB/s   00:00

[oracle@Nsmdev01 rman_backup]$ pwd
/backup/rman_backup
[oracle@Nsmdev01 rman_backup]$ scp initdhstg.ora oracle@172.16.110.16:/data2/app/oracle/product/11.2.0/dbs/
oracle@172.16.110.16’s password:
initdhstg.ora                                                                                                                                                     100%  861     0.8KB/s   00:00

7. Shutdown and start the instance in mount stage.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
DHSTG    READ WRITE


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             838864728 bytes
Database Buffers          222298112 bytes
Redo Buffers                5554176 bytes
Database mounted.
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
DHSTG    MOUNTED

Steps on destination server:

8. Put static listener entry into listener.ora file in $ORACLE_HOME/netwrok/admin directory.

oracle@Nsmstg01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
[oracle@Nsmstg01 admin]$

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dhstg_dup)
      (ORACLE_HOME = /data2/app/oracle/product/11.2.0)
      (SID_NAME = dhstg)
    )
  )
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@Nsmstg01 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:13:54

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

Welcome to LSNRCTL, type “help” for information.

[oracle@Nsmstg01 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:43:43

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 “aux.corp.netsoftmate.com” has 1 instance(s).
  Instance “aux”, status READY, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0 state:ready
         LOCAL SERVER
Service “auxXDB.corp.netsoftmate.com” has 1 instance(s).
  Instance “aux”, status READY, has 1 handler(s) for this service…
    Handler(s):
      “D000” established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: Nsmstg01, pid: 4392>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Nsmstg01)(PORT=38403))
Service “dhstg.corp.netsoftmate.com” has 1 instance(s).
  Instance “dhstg”, status BLOCKED, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0 state:ready
         LOCAL SERVER
Service “dhstg_dup” has 1 instance(s).
  Instance “dhstg”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0
         LOCAL SERVER
Service “dhstg_dup.corp.netsoftmate.com” has 1 instance(s).
  Instance “dhstg”, status BLOCKED, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

9. put Tns entry for target(source) database in $ORACLE_HOME/network/admin directory

[oracle@Nsmstg01 admin]$ vi tnsnames.ora

DHSTG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dhstg.corp.netsoftmate.com)
    )
  )

[oracle@Nsmstg01 admin]$ tnsping DHSTG

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:15:52

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 = dhstg.corp.netsoftmate.com)))
OK (0 msec)

10. Create the directory structure different from target (source) server.

[oracle@Nsmstg01 dhstg]$ mkdir -p /data1/diff/dhstg

11. Set the oracle sid and start the instance in nomount stage with pfile, here start the instance with pfile is must or else you will get error.

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance
is started with spfile cannot use SPFILE clause

[oracle@Nsmstg01 dbs]$ export ORACLE_SID=DHSTG
[oracle@Nsmstg01 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 12:22:14 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 pfile=’/data2/app/oracle/product/11.2.0/dbs/initdhstg.ora’;

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             838864728 bytes
Database Buffers          222298112 bytes
Redo Buffers                5554176 bytes
SQL>

12. Now connect to Rman by issuing following command
[oracle@Nsmstg01 admin]$ rman target sys/oracle@dhstg auxiliary sys/oracle@dhstg_dup

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 6 12:58:08 2015

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

connected to target database: DHSTG (DBID=112149522, not open)
connected to auxiliary database: DHSTG (not mounted)

RMAN>

13. Issue the command to duplicate the database with same name but different directory structure from noarchivelog mode target DB.

RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE
2> spfile
3> parameter_value_convert
4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
5> set db_file_name_convert
6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
7> set log_file_name_convert
8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
9> nofilenamecheck;

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

contents of Memory Script:
{
   backup as copy reuse
   targetfile  ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ auxiliary format
‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’   ;
   sql clone “alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora””;
}
executing Memory Script

Starting backup at 06-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
Finished backup at 06-APR-15

sql statement: alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora”

contents of Memory Script:
{
   sql clone “alter system set  db_name =
”DHSTG” comment=
”duplicate” scope=spfile”;
   sql clone “alter system set  control_files =
”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment=
”” scope=spfile”;
   sql clone “alter system set  db_file_name_convert =
”/data1/dhstg”, ”/data1/diff/dhstg” comment=
”” scope=spfile”;
   sql clone “alter system set  log_file_name_convert =
”/data1/dhstg”, ”/data1/diff/dhstg” comment=
”” scope=spfile”;
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ”DHSTG” comment= ”duplicate” scope=spfile

sql statement: alter system set  control_files =  ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment= ”” scope=spfile

sql statement: alter system set  db_file_name_convert =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

sql statement: alter system set  log_file_name_convert =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2220200 bytes
Variable Size                838864728 bytes
Database Buffers             222298112 bytes
Redo Buffers                   5554176 bytes

contents of Memory Script:
{
   sql clone “alter system set  db_name =
”DHSTG” comment=
”Modified by RMAN duplicate” scope=spfile”;
   sql clone “alter system set  db_unique_name =
”DHSTG” comment=
”Modified by RMAN duplicate” scope=spfile”;
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  ‘/data1/diff/dhstg/control01.ctl’;
   restore clone controlfile to  ‘/data1/diff/dhstg/control02.ctl’ from
‘/data1/diff/dhstg/control01.ctl’;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2220200 bytes
Variable Size                838864728 bytes
Database Buffers             222298112 bytes
Redo Buffers                   5554176 bytes

Starting backup at 06-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_dhstg.f tag=TAG20150406T125217 RECID=2 STAMP=876315139
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 06-APR-15

Starting restore at 06-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 06-APR-15

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
“/data1/diff/dhstg/system01.dbf”;
   set newname for datafile  2 to
“/data1/diff/dhstg/sysaux01.dbf”;
   set newname for datafile  3 to
“/data1/diff/dhstg/undotbs01.dbf”;
   set newname for datafile  4 to
“/data1/diff/dhstg/users01.dbf”;
   set newname for datafile  5 to
“/data1/diff/dhstg/example01.dbf”;
   set newname for datafile  6 to
“/data1/diff/dhstg/data01.dbf”;
   backup as copy reuse
   datafile  1 auxiliary format
“/data1/diff/dhstg/system01.dbf”   datafile
2 auxiliary format
“/data1/diff/dhstg/sysaux01.dbf”   datafile
3 auxiliary format
“/data1/diff/dhstg/undotbs01.dbf”   datafile
4 auxiliary format
“/data1/diff/dhstg/users01.dbf”   datafile
5 auxiliary format
“/data1/diff/dhstg/example01.dbf”   datafile
6 auxiliary format
“/data1/diff/dhstg/data01.dbf”   ;
}
executing Memory Script

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 backup at 06-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/data1/dhstg/system01.dbf
output file name=/data1/diff/dhstg/system01.dbf tag=TAG20150406T125229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/data1/dhstg/sysaux01.dbf
output file name=/data1/diff/dhstg/sysaux01.dbf tag=TAG20150406T125229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/data1/dhstg/undotbs01.dbf
output file name=/data1/diff/dhstg/undotbs01.dbf tag=TAG20150406T125229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/data1/dhstg/data01.dbf
output file name=/data1/diff/dhstg/data01.dbf tag=TAG20150406T125229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/data1/dhstg/example01.dbf
output file name=/data1/diff/dhstg/example01.dbf tag=TAG20150406T125229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/data1/dhstg/users01.dbf
output file name=/data1/diff/dhstg/users01.dbf tag=TAG20150406T125229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 06-APR-15

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=876315893 file name=/data1/diff/dhstg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=876315894 file name=/data1/diff/dhstg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=876315894 file name=/data1/diff/dhstg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=876315894 file name=/data1/diff/dhstg/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=876315895 file name=/data1/diff/dhstg/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=876315895 file name=/data1/diff/dhstg/data01.dbf

contents of Memory Script:
{
   recover
   clone database
   noredo
    delete archivelog
   ;
}
executing Memory Script

Starting recover at 06-APR-15
using channel ORA_AUX_DISK_1

Finished recover at 06-APR-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone “alter system set  db_name =
”DHSTG” 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    1068937216 bytes

Fixed Size                     2220200 bytes
Variable Size                838864728 bytes
Database Buffers             222298112 bytes
Redo Buffers                   5554176 bytes

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

Fixed Size                     2220200 bytes
Variable Size                838864728 bytes
Database Buffers             222298112 bytes
Redo Buffers                   5554176 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DHSTG” RESETLOGS NOARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( ‘/data1/diff/dhstg/redo01.log’ ) SIZE 50 M  REUSE,
  GROUP  2 ( ‘/data1/diff/dhstg/redo02.log’ ) SIZE 50 M  REUSE,
  GROUP  3 ( ‘/data1/diff/dhstg/redo03.log’ ) SIZE 50 M  REUSE
DATAFILE
  ‘/data1/diff/dhstg/system01.dbf’
CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
“/data1/diff/dhstg/temp01.dbf”;
   switch clone tempfile all;
   catalog clone datafilecopy  “/data1/diff/dhstg/sysaux01.dbf”,
“/data1/diff/dhstg/undotbs01.dbf”,
“/data1/diff/dhstg/users01.dbf”,
“/data1/diff/dhstg/example01.dbf”,
“/data1/diff/dhstg/data01.dbf”;
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data1/diff/dhstg/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/data1/diff/dhstg/sysaux01.dbf RECID=1 STAMP=876315921
cataloged datafile copy
datafile copy file name=/data1/diff/dhstg/undotbs01.dbf RECID=2 STAMP=876315921
cataloged datafile copy
datafile copy file name=/data1/diff/dhstg/users01.dbf RECID=3 STAMP=876315922
cataloged datafile copy
datafile copy file name=/data1/diff/dhstg/example01.dbf RECID=4 STAMP=876315922
cataloged datafile copy
datafile copy file name=/data1/diff/dhstg/data01.dbf RECID=5 STAMP=876315922

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=876315921 file name=/data1/diff/dhstg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=876315921 file name=/data1/diff/dhstg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=876315922 file name=/data1/diff/dhstg/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=876315922 file name=/data1/diff/dhstg/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=876315922 file name=/data1/diff/dhstg/data01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 06-APR-15
14. Once database replicated open the target database.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
DHSTG    READ WRITE

Issues:
  • When you replicate database where target database is in noarchivelog mode. You will receive following error.
RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE
2> SPFILE
3> PARAMETER_VALUE_CONVERT
4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
5> SET DB_FILE_NAME_CONVERT
6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
7> SET LOG_FILE_NAME_CONVERT
8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
9> NOFILENAMECHECK;

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

contents of Memory Script:
{
   backup as copy reuse
   targetfile  ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ auxiliary format
‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’   ;
   sql clone “alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora””;
}
executing Memory Script

Starting backup at 06-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
Finished backup at 06-APR-15

sql statement: alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora”

contents of Memory Script:
{
   sql clone “alter system set  db_name =
”DHSTG” comment=
”duplicate” scope=spfile”;
   sql clone “alter system set  control_files =
”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment=
”” scope=spfile”;
   sql clone “alter system set  db_file_name_convert =
”/data1/dhstg”, ”/data1/diff/dhstg” comment=
”” scope=spfile”;
   sql clone “alter system set  LOG_FILE_NAME_CONVERT =
”/data1/dhstg”, ”/data1/diff/dhstg” comment=
”” scope=spfile”;
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ”DHSTG” comment= ”duplicate” scope=spfile

sql statement: alter system set  control_files =  ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment= ”” scope=spfile

sql statement: alter system set  db_file_name_convert =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2220200 bytes
Variable Size                838864728 bytes
Database Buffers             222298112 bytes
Redo Buffers                   5554176 bytes

contents of Memory Script:
{
   sql clone “alter system set  db_name =
”DHSTG” comment=
”Modified by RMAN duplicate” scope=spfile”;
   sql clone “alter system set  db_unique_name =
”DHSTG” comment=
”Modified by RMAN duplicate” scope=spfile”;
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  ‘/data1/diff/dhstg/control01.ctl’;
   restore clone controlfile to  ‘/data1/diff/dhstg/control02.ctl’ from
‘/data1/diff/dhstg/control01.ctl’;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2220200 bytes
Variable Size                838864728 bytes
Database Buffers             222298112 bytes
Redo Buffers                   5554176 bytes

Starting backup at 06-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_dhstg.f tag=TAG20150406T141312 RECID=3 STAMP=876319993
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 06-APR-15

Starting restore at 06-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 06-APR-15

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
“/data1/diff/dhstg/system01.dbf”;
   set newname for datafile  2 to
“/data1/diff/dhstg/sysaux01.dbf”;
   set newname for datafile  3 to
“/data1/diff/dhstg/undotbs01.dbf”;
   set newname for datafile  4 to
“/data1/diff/dhstg/users01.dbf”;
   set newname for datafile  5 to
“/data1/diff/dhstg/example01.dbf”;
   set newname for datafile  6 to
“/data1/diff/dhstg/data01.dbf”;
   backup as copy reuse
   datafile  1 auxiliary format
“/data1/diff/dhstg/system01.dbf”   datafile
2 auxiliary format
“/data1/diff/dhstg/sysaux01.dbf”   datafile
3 auxiliary format
“/data1/diff/dhstg/undotbs01.dbf”   datafile
4 auxiliary format
“/data1/diff/dhstg/users01.dbf”   datafile
5 auxiliary format
“/data1/diff/dhstg/example01.dbf”   datafile
6 auxiliary format
“/data1/diff/dhstg/data01.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

executing command: SET NEWNAME

Starting backup at 06-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/06/2015 14:21:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:34
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Solution: Work around for this is shutdown the target database and start in mount stage.
  • When you start the auxiliary instance with spfile.
[oracle@Nsmstg01 dbs]$ export ORACLE_SID=dhstg
[oracle@Nsmstg01 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 15:15:22 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 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             838864728 bytes
Database Buffers          222298112 bytes
Redo Buffers                5554176 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
[oracle@Nsmstg01 dbs]$ rman target sys/oracle@dhstg auxiliary sys/oracle@dhstg_dup

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 6 15:16:07 2015

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

connected to target database: DHSTG (DBID=112149522, not open)
connected to auxiliary database: DHSTG (not mounted)

RMAN>
RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE
2> SPFILE
3> PARAMETER_VALUE_CONVERT
4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
5> SET DB_FILE_NAME_CONVERT
6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
7> SET LOG_FILE_NAME_CONVERT
8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
9> NOFILENAMECHECK;

Starting Duplicate Db at 06-APR-15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/06/2015 15:47:03
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

solution: Start the auxiliary instance with pfile

Conclusion
In the above article, we have learned that Active Duplication using Rman utility with same database name and different directory structure, and our source Database is in noarchive log mode, and there are some vital things we need remember while performing active duplication of database running in no archive log mode, we have came a cross two errors and their work arounds.

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



0

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.

Backup-based duplication uses an RMAN backup of the target (source) database as its source to create the data files in the auxiliary (destination) environment.
No connection to the target database is required. This is referred to as targetless duplication. This technique only requires a connection to the auxiliary database. Targetless duplication is available in Oracle 11g release 2 and higher.
The big advantage to backup based duplication is that if you work in an environment where it’s not possible to have a simultaneous connection to both the target and the auxiliary database you can still duplicate a database provided you can copy an RMAN backup to the auxiliary database
Server (or provided the backup is on network-mounted storage readable from the auxiliary server). In many environments, due to security rules, there is no network connectivity allowed from test environments to the production server.

In this scenario we have same database name and same directory structure.

Environment Details

Target (source) details:
Database Name
prod
Hostname
Nsmprod
Ip Address
172.16.110.18
OS
Linux
Version
x86_64
Datafile Location
/data2/prod
Backup Location
/backup/rman_backup
 

Destination details:
Database Name
prod
Hostname
Nsmstg
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.    export Oracle sid. 


[oracle@Nsmprod dbs]$ hostname

Nsmprod

[oracle@Nsmprod dbs]$ export ORACLE_SID=prod

 2.    Connect to target database via RMAN.


[oracle@Nsmprod 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>
RMAN> list backup;

specification does not match any backup in the repository

RMAN> backup database plus archivelog;


Starting backup at 10-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=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
input archived log thread=1 sequence=143 RECID=139 STAMP=876586874
input archived log thread=1 sequence=144 RECID=140 STAMP=876641790
input archived log thread=1 sequence=145 RECID=141 STAMP=876670700
channel ORA_DISK_1: starting piece 1 at 10-APR-15
channel ORA_DISK_1: finished piece 1 at 10-APR-15
piece handle=/backup/rman_backup/db_arch_2qq41rnc_1_1.bkp tag=TAG20150410T153820 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 10-APR-15

Starting backup at 10-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 10-APR-15
channel ORA_DISK_1: finished piece 1 at 10-APR-15
piece handle=/backup/rman_backup/db_arch_2rq41rqd_1_1.bkp tag=TAG20150410T153956 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 10-APR-15

Starting backup at 10-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=146 RECID=142 STAMP=876670863
channel ORA_DISK_1: starting piece 1 at 10-APR-15
channel ORA_DISK_1: finished piece 1 at 10-APR-15
piece handle=/backup/rman_backup/db_arch_2sq41rsf_1_1.bkp tag=TAG20150410T154103 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-APR-15

Starting Control File and SPFILE Autobackup at 10-APR-15
piece handle=/backup/rman_backup/controlfile_c-284539893-20150410-00 comment=NONE
Finished Control File and SPFILE Autobackup at 10-APR-15

RMAN>
4.    Create pfile and copy to destination server.



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

File created.

[oracle@Nsmprod 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
create directory structure to accommodate rman backup pieces on destination server and start copy.


[oracle@Nsmstg prod]$ mkdir -p /backup/rman_backup/
5.    Copy rman backup from target to destination server.


RMAN> exit


Recovery Manager complete.

[oracle@Nsmprod rman_backup]$ ls -lrth
total 2.8G
-rw-r—–. 1 oracle dba 1.7G Apr 10 15:39 db_arch_2qq41rnc_1_1.bkp
-rw-r—–. 1 oracle dba 1.2G Apr 10 15:40 db_arch_2rq41rqd_1_1.bkp
-rw-r—–. 1 oracle dba  24K Apr 10 15:41 db_arch_2sq41rsf_1_1.bkp
-rw-r—–. 1 oracle dba 9.6M Apr 10 15:41 controlfile_c-284539893-20150410-00


[oracle@Nsmprod rman_backup]$ scp * oracle@172.16.110.16:/backup/rman_backup/
oracle@172.16.110.16’s password:
controlfile_c-284539893-20150410-00                                                                                                              
                 100% 9792KB   9.6MB/s   00:01
db_arch_2qq41rnc_1_1.bkp                                                                                                                         
                 100% 1668MB  11.2MB/s   02:29
db_arch_2rq41rqd_1_1.bkp                                                                                                                          
                 100% 1135MB  11.2MB/s   01:41
db_arch_2sq41rsf_1_1.bkp                                                                                                                          
                 100%   24KB  23.5KB/s   00:00
 

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



[oracle@Nsmstg prod]$ mkdir -p /data2/prod
[oracle@Nsmstg prod]$ mkdir -p /backup/archive/
7.    Start the instance in nomount stage.


[oracle@Nsmstg dbs]$ export ORACLE_SID=prod
[oracle@Nsmstg dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 15:58:10 2015

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL>
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
[oracle@Nsmstg dbs]$
8.    Connect to auxiliary instance via RMAN


[oracle@Nsmstg dbs]$ rman auxiliary sys/oracle

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Apr 10 15:59:22 2015

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

connected to auxiliary database: PROD (not mounted)

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



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

Starting Duplicate Db at 10-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 =
 ”PROD” 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-20150410-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 =  ”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 restore at 10-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/prod/control01.ctl
output file name=/data2/prod/control02.ctl
Finished restore at 10-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  5022379;
   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 10-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_2rq41rqd_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2rq41rqd_1_1.bkp tag=TAG20150410T153956
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 10-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=876672089 file name=/data2/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=876672089 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=876672090 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=876672090 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=876672090 file name=/data2/prod/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=876672091 file name=/data2/prod/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=876672091 file name=/data2/prod/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=876672091 file name=/data2/prod/tools01.dbf

contents of Memory Script:
{
   set until scn  5022379;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 10-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=146
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2sq41rsf_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2sq41rsf_1_1.bkp tag=TAG20150410T154103
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_146_872701561.dbf thread=1 sequence=146
channel clone_default: deleting archived log(s)
archived log file name=/backup/archive/1_146_872701561.dbf RECID=1 STAMP=876672098
media recovery complete, elapsed time: 00:00:04
Finished recover at 10-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”,
 “/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=876672123
cataloged datafile copy
datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=876672123
cataloged datafile copy
datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=876672124
cataloged datafile copy
datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=876672124
cataloged datafile copy
datafile copy file name=/data2/prod/data01.dbf RECID=5 STAMP=876672125
cataloged datafile copy
datafile copy file name=/data2/prod/test01.dbf RECID=6 STAMP=876672125
cataloged datafile copy
datafile copy file name=/data2/prod/tools01.dbf RECID=7 STAMP=876672125

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=876672123 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=876672123 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=876672124 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=876672124 file name=/data2/prod/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=876672125 file name=/data2/prod/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=876672125 file name=/data2/prod/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=876672125 file name=/data2/prod/tools01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 10-APR-15


RMAN>
RMAN> exit


Recovery Manager complete.
[oracle@Nsmstg dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 16:04:05 2015

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
PROD      READ WRITE

SQL> select host_name from v$instance;

HOST_NAME
—————————————————————-
Nsmstg

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
DATA                           ONLINE
TEST                           ONLINE
TOOLS                          ONLINE

9 rows selected.
 

Conclusion
In the above article, we have learned that backup based duplication using Rman utility with same database name and same directory structure, we can refer this as targetless duplication, where no connectivity between target and auxiliary instance is required.


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

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.

You can replicate a target (source) database using an RMAN backup when duplicating to an auxiliary (destination) database. The basic idea here is to copy an RMAN backup to an auxiliary server and create the auxiliary database directly from the backup. This is a simple and powerful technique for replicating a database. It is especially applicable where there’s no direct network connection between the target database and the auxiliary.

Here we are having same database name and same directory structure. And target database have read only tablespaces.


Environment details 
Target (source) details:
Database Name
prod
Hostname
Nsmdev
Ip Address
172.16.110.18
OS
Linux
Version
x86_64
Datafile Location
/data2/prod
Backup Location
/backup/rman_backup
 
Destination details:

Database Name
prod
Hostname
Nsmqty
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.    export Oracle sid.


[oracle@Nsmdev dbs]$ hostname

Nsmdev

[oracle@Nsmdev dbs]$ export ORACLE_SID=prod

 2.    Login to database and check tablespace details.


SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME      STATUS
——————– ———-
SYSTEM               ONLINE
SYSAUX               ONLINE
UNDOTBS1             ONLINE
TEMP                 ONLINE
USERS                ONLINE
EXAMPLE              ONLINE
DATA                 READ ONLY
TEST                 ONLINE
TOOLS                READ ONLY

9 rows selected.
  3.    Connect to RMAN.


[oracle@Nsmdev ~]$ rman target sys/oracle


Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 15 12:44:17 2015

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

connected to target database: PROD (DBID=284539893)

RMAN>
  4.    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 15-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=161 RECID=157 STAMP=877092331

channel ORA_DISK_1: starting piece 1 at 15-APR-15

channel ORA_DISK_1: finished piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3lq4enfb_1_1.bkp tag=TAG20150415T124531 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-APR-15



Starting backup at 15-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 15-APR-15

channel ORA_DISK_1: finished piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3mq4enfd_1_1.bkp tag=TAG20150415T124533 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished backup at 15-APR-15



Starting backup at 15-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=162 RECID=158 STAMP=877092389

channel ORA_DISK_1: starting piece 1 at 15-APR-15

channel ORA_DISK_1: finished piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3nq4enh6_1_1.bkp tag=TAG20150415T124629 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-APR-15



Starting Control File Autobackup at 15-APR-15

piece handle=/backup/rman_backup/controlfile_c-284539893-20150415-00 comment=NONE

Finished Control File Autobackup at 15-APR-15



RMAN>
  5.    Create pfile and copy to destination server.


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

File created.

[oracle@Nsmdev 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
create directory structure to accommodate rman backup on destination server and copy the backup pieces.


[oracle@Nsmqty prod]$ mkdir -p /backup/rman_backup/
6.    Copy rman backup to auxiliary database server


[oracle@Nsmdev rman_backup]$ scp *.bkp oracle@172.16.110.16://backup/rman_backup/

oracle@172.16.110.16’s password:
db_arch_2eq3umgh_1_1.bkp                                                                                                                         
                 100% 1860MB  11.2MB/s   02:46
db_arch_2fq3umjs_1_1.bkp                                                                                                                         
                 100% 1132MB  11.2MB/s   01:41
db_arch_2gq3umlu_1_1.bkp                                                                                                                          
                 100%   38KB  38.0KB/s   00:00
  7.    Create password file on destination server


/data1/oracle11g/product/dbs
[oracle@Nsmqty dbs]$ orapwd file=orapwprod password=ora123

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



[oracle@Nsmqty prod]$ mkdir -p /data2/prod
[oracle@Nsmqty prod]$ mkdir -p /backup/archive/
9.    Start the instance in nomount.


[oracle@Nsmqty dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 12:14:35 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
10.    Connect to rman


[oracle@Nsmqty dbs]$ rman auxiliary sys/oracle

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Apr 9 12:19:37 2015

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

connected to auxiliary database: PROD (not mounted)

RMAN>
  11.    Issue the following command to duplicate database.


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

Starting Duplicate Db at 15-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 =
 ”PROD” 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-20150415-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 =  ”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 restore at 15-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/prod/control01.ctl
output file name=/data2/prod/control02.ctl
Finished restore at 15-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  5291226;
   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 15-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_3mq4enfd_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_3mq4enfd_1_1.bkp tag=TAG20150415T124533
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 15-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=877102442 file name=/data2/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=877102443 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=877102443 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=877102444 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=877102444 file name=/data2/prod/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=877102444 file name=/data2/prod/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=877102445 file name=/data2/prod/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=877102445 file name=/data2/prod/tools01.dbf

contents of Memory Script:
{
   set until scn  5291226;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 15-APR-15
using channel ORA_AUX_DISK_1
datafile 6 not processed because file is read-only
datafile 8 not processed because file is read-only

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=162
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_3nq4enh6_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_3nq4enh6_1_1.bkp tag=TAG20150415T124629
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_162_872701561.dbf thread=1 sequence=162
channel clone_default: deleting archived log(s)
archived log file name=/backup/archive/1_162_872701561.dbf RECID=1 STAMP=877102457
media recovery complete, elapsed time: 00:00:03
Finished recover at 15-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”,
 “/data2/prod/test01.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=877102481
cataloged datafile copy
datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=877102482
cataloged datafile copy
datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=877102482
cataloged datafile copy
datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=877102483
cataloged datafile copy
datafile copy file name=/data2/prod/test01.dbf RECID=5 STAMP=877102483

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=877102481 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=877102482 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=877102482 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=877102483 file name=/data2/prod/example01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=877102483 file name=/data2/prod/test01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   catalog clone datafilecopy  “/data2/prod/data01.dbf”,
 “/data2/prod/tools01.dbf”;
   switch clone datafile  6 to datafilecopy
 “/data2/prod/data01.dbf”;
   switch clone datafile  8 to datafilecopy
 “/data2/prod/tools01.dbf”;
   #online the readonly tablespace
   sql clone “alter tablespace  TOOLS online”;
   #online the readonly tablespace
   sql clone “alter tablespace  DATA online”;
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/data2/prod/data01.dbf RECID=6 STAMP=877102546
cataloged datafile copy
datafile copy file name=/data2/prod/tools01.dbf RECID=7 STAMP=877102547

datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=877102546 file name=/data2/prod/data01.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=877102547 file name=/data2/prod/tools01.dbf

sql statement: alter tablespace  TOOLS online

sql statement: alter tablespace  DATA online
Finished Duplicate Db at 15-APR-15

RMAN>
12.     Check the tablespaces status once database replicated.


SQL>  select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME      STATUS
——————– ———-
SYSTEM               ONLINE
SYSAUX               ONLINE
UNDOTBS1             ONLINE
TEMP                 ONLINE
USERS                ONLINE
EXAMPLE              ONLINE
DATA                 READ ONLY
TEST                 ONLINE
TOOLS                READ ONLY

9 rows selected.

Conclusion
In the above article, we have learned that backup based duplication using Rman utility with same database name and same directory structure, and target(source) database have Read only tablespaces.
 

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

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 Readonly tablespace at target(Source) database. and same directory structure with different database name.




Target (source) details:

Database Name
prod
Hostname
Nsm-prod
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
Nsm-stg
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:
  • export ORACLE SID
[oracle@Nsm-prod dbs]$ hostname

Nsm-prod

[oracle@Nsm-prod dbs]$ export ORACLE_SID=prod

  •   Connect to RMAN.


[oracle@Nsm-prod 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>

  •   Issue the following command to initiate backup. Before issue the backup command verify the backup location.

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

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 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=85 RECID=81 STAMP=875052425

input archived log thread=1 sequence=86 RECID=82 STAMP=875086125

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

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_2iq3v6ku_1_1.bkp tag=TAG20150409T152621 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45

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_2jq3v6o8_1_1.bkp tag=TAG20150409T152807 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

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=141 RECID=137 STAMP=876583754

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_2kq3v6qa_1_1.bkp tag=TAG20150409T152914 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-01 comment=NONE

Finished Control File and SPFILE Autobackup at 09-APR-15



RMAN>

  •   Create pfile and copy to destination server.

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

File created.



[oracle@Nsm-prod 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@Nsm-stg prod]$ mkdir -p /backup/rman_backup/

  • Copy rman backup from target to destination server.

[oracle@Nsm-prod rman_backup]$ scp *.bkp oracle@172.16.110.16:/backup/rman_backup/
oracle@172.16.110.16’s password:
db_arch_2iq3v6ku_1_1.bkp                                                                                                                                           100% 1799MB  11.2MB/s   02:40
db_arch_2jq3v6o8_1_1.bkp                                                                                                                                           100% 1133MB  11.2MB/s   01:41
db_arch_2kq3v6qa_1_1.bkp                                                                                                                                           100%   23KB  22.5KB/s   00:00
initprod.ora.bkp                                                                                                                                                   100% 1250     1.2KB/s   00:00
[oracle@Nsm-prod rman_backup]$ scp controlfile_c-284539893-20150409-01 oracle@172.16.110.16:/backup/rman_backup/
oracle@172.16.110.16’s password:
controlfile_c-284539893-20150409-01                                                                                                                                100% 9632KB   9.4MB/s   00:01
 
Steps on destination server:
  • Create directory structure same as target(source) database.

[oracle@Nsm-stg prod]$ mkdir -p /data2/prod
[oracle@Nsm-stg prod]$ mkdir -p /backup/archive/

  •   Start the new instance in nomount.

[oracle@Nsm-stg rman_backup]$ export ORACLE_SID=dup
[oracle@Nsm-stg dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 12:14:35 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>
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

  •   Connect to rman.

[oracle@Nsm-stg dbs]$ rman auxiliary sys/oracle

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Apr 9 15:59:57 2015

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

connected to auxiliary database: DUP (not mounted)

RMAN>
  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-01’;
   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:03
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  4970799;
   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_2jq3v6o8_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2jq3v6o8_1_1.bkp tag=TAG20150409T152807
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
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=876585776 file name=/data2/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=876585777 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=876585777 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=876585777 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=876585778 file name=/data2/prod/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=876585778 file name=/data2/prod/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=876585778 file name=/data2/prod/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=876585779 file name=/data2/prod/tools01.dbf

contents of Memory Script:
{
   set until scn  4970799;
   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
datafile 6 not processed because file is read-only
datafile 8 not processed because file is read-only

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=141
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2kq3v6qa_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2kq3v6qa_1_1.bkp tag=TAG20150409T152914
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_141_872701561.dbf thread=1 sequence=141
channel clone_default: deleting archived log(s)
archived log file name=/backup/archive/1_141_872701561.dbf RECID=1 STAMP=876585786
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/test01.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=876585810
cataloged datafile copy
datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=876585810
cataloged datafile copy
datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=876585811
cataloged datafile copy
datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=876585811
cataloged datafile copy
datafile copy file name=/data2/prod/test01.dbf RECID=5 STAMP=876585812

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=876585810 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=876585810 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=876585811 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=876585811 file name=/data2/prod/example01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=876585812 file name=/data2/prod/test01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   catalog clone datafilecopy  “/data2/prod/data01.dbf”,
 “/data2/prod/tools01.dbf”;
   switch clone datafile  6 to datafilecopy
 “/data2/prod/data01.dbf”;
   switch clone datafile  8 to datafilecopy
 “/data2/prod/tools01.dbf”;
   #online the readonly tablespace
   sql clone “alter tablespace  TOOLS online”;
   #online the readonly tablespace
   sql clone “alter tablespace  DATA online”;
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/data2/prod/data01.dbf RECID=6 STAMP=876585838
cataloged datafile copy
datafile copy file name=/data2/prod/tools01.dbf RECID=7 STAMP=876585839

datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=876585838 file name=/data2/prod/data01.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=876585839 file name=/data2/prod/tools01.dbf

sql statement: alter tablespace  TOOLS online

sql statement: alter tablespace  DATA online
Finished Duplicate Db at 09-APR-15
 
 Observation:
One strange thing we have found, in this scenario at target side two of the tablespaces are in read only mode, we have taken rman full backup and when trying to duplicate the database it was successful but when check the rman log  it is showing that so and so datafile is not processed because it is in read only mode as follows


Starting recover at 09-APR-15

using channel ORA_AUX_DISK_1

datafile 6 not processed because file is read-only

datafile 8 not processed because file is read-only



starting media recovery



and after some execution it is trying to make those tablespaces online and this is also successful



contents of Memory Script:

{

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

 “/data2/prod/tools01.dbf”;

   switch clone datafile  6 to datafilecopy

 “/data2/prod/data01.dbf”;

   switch clone datafile  8 to datafilecopy

 “/data2/prod/tools01.dbf”;

   #online the readonly tablespace

   sql clone “alter tablespace  TOOLS online”;

   #online the readonly tablespace

   sql clone “alter tablespace  DATA online”;

}

  In Alert log file it is showing, bringing up the tablespaces online.


Switch of datafile 6 complete to datafile copy
  checkpoint is 4866414
Switch of datafile 8 complete to datafile copy
  checkpoint is 4866426
alter tablespace  TOOLS online
Completed: alter tablespace  TOOLS online
alter tablespace  DATA online
Completed: alter tablespace  DATA online
Thu Apr 09 16:04:05 2015
  Then replication has successful then i have cross checked in database and those tablespaces are there with Read only access.


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
DUP       READ WRITE

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
DATA                           READ ONLY
TEST                           ONLINE
TOOLS                          READ ONLY

9 rows selected.
  As per my understanding the tablespace bringing online is different thing, whereas status is different thing whenever we start up the database, it will bring up all the tablespaces online, to change tablespace mode we need to execute the command as follows.


SQL> alter tablespace DATA  Read write;

Tablespace altered.

SQL> alter tablespace TOOLS  Read write;

Tablespace altered.


SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
DATA                           ONLINE
TEST                           ONLINE
TOOLS                          ONLINE

9 rows selected.
 

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,
with Read only tablespace, if we have tablespaces are in read only mode same thing would replicate at target side.

BY
Name: Omer
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
0

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
0

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 using SPFILE clause in Rman duplication command.


Environment details:

Target (source) details:

Database Name
prod
Hostname
Nsm-source
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
Nsm-linux
Ip Address
172.16.110.16
OS
Linux
Version
x86_64
Datafile Location
/data2/prod/dup/
Backup Location
/backup/rman_backup

Prerequisites:
  • Password file from target database.
  • Sqlnet.ora should have correct parameters.
  • Target database should be running through spfile.
  • Auxiliary instance should be start with pfile in nomount stage.
Steps on target (source) server:

1.    Set Oracle sid on which backup will perform.
[oracle@Nsm-source dbs]$ hostname

Nsm-source

[oracle@Nsm-source dbs]$ export ORACLE_SID=prod



2.    Connect to RMAN.

[oracle@Nsm-source 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>

RMAN> list backup;

specification does not match any backup in the repository


RMAN> backup database plus archivelog;





Starting backup at 10-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=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

input archived log thread=1 sequence=143 RECID=139 STAMP=876586874

input archived log thread=1 sequence=144 RECID=140 STAMP=876641790

input archived log thread=1 sequence=145 RECID=141 STAMP=876670700

channel ORA_DISK_1: starting piece 1 at 10-APR-15

channel ORA_DISK_1: finished piece 1 at 10-APR-15

piece handle=/backup/rman_backup/db_arch_2qq41rnc_1_1.bkp tag=TAG20150410T153820 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

Finished backup at 10-APR-15



Starting backup at 10-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 10-APR-15

channel ORA_DISK_1: finished piece 1 at 10-APR-15

piece handle=/backup/rman_backup/db_arch_2rq41rqd_1_1.bkp tag=TAG20150410T153956 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Finished backup at 10-APR-15



Starting backup at 10-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=146 RECID=142 STAMP=876670863

channel ORA_DISK_1: starting piece 1 at 10-APR-15

channel ORA_DISK_1: finished piece 1 at 10-APR-15

piece handle=/backup/rman_backup/db_arch_2sq41rsf_1_1.bkp tag=TAG20150410T154103 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-APR-15



Starting Control File and SPFILE Autobackup at 10-APR-15

piece handle=/backup/rman_backup/controlfile_c-284539893-20150410-00 comment=NONE

Finished Control File and SPFILE Autobackup at 10-APR-15



RMAN>
  4.    Create pfile and copy to destination server.

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



File created.


[oracle@Nsm-source 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



prior to  start scp/ftp to destination server create directory structure to accommodate Rman backup on destination server.


[oracle@Nsm-linux prod]$ mkdir -p /backup/rman_backup/
 
 5.    Copy rman backup from target to destination server.
RMAN> exit





Recovery Manager complete.



[oracle@Nsm-source rman_backup]$ ls -lrth

total 2.8G

-rw-r—–. 1 oracle dba 1.7G Apr 10 15:39 db_arch_2qq41rnc_1_1.bkp

-rw-r—–. 1 oracle dba 1.2G Apr 10 15:40 db_arch_2rq41rqd_1_1.bkp

-rw-r—–. 1 oracle dba  24K Apr 10 15:41 db_arch_2sq41rsf_1_1.bkp

-rw-r—–. 1 oracle dba 9.6M Apr 10 15:41 controlfile_c-284539893-20150410-00

 


[oracle@Nsm-source rman_backup]$ scp * oracle@172.16.110.16:/backup/rman_backup/

oracle@172.16.110.16’s password:

controlfile_c-284539893-20150410-00                                                                                                                                100% 9792KB   9.6MB/s   00:01

db_arch_2qq41rnc_1_1.bkp                                                                                                                                           100% 1668MB  11.2MB/s   02:29

db_arch_2rq41rqd_1_1.bkp                                                                                                                                           100% 1135MB  11.2MB/s   01:41

db_arch_2sq41rsf_1_1.bkp                                                                                                                                           100%   24KB  23.5KB/s   00:00



Steps on destination server:
6.    Create new directory structure.


[oracle@Nsm-linux data2]$ mkdir -p /data2/prod/dup
 
7.    Start the instance in nomount stage using pfile.


[oracle@Nsm-linux dbs]$ export ORACLE_SID=dup

[oracle@Nsm-linux dbs]$ sqlplus


SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 16:52:53 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 pfile=’/data1/oracle11g/product/dbs/initdup.ora’;
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>

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-linux dbs]$
 
 8.    Connect to rman.


[oracle@Nsm-linux dbs]$ rman auxiliary sys/oracle



Recovery Manager: Release 11.2.0.1.0 – Production on Fri Apr 10 16:54:21 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 different directory structure.


RMAN> DUPLICATE TARGET DATABASE TO DUP

BACKUP LOCATION ‘/backup/rman_backup’

SPFILE
PARAMETER_VALUE_CONVERT
‘/data2/prod’, ‘/data2/prod/dup’
SET DB_FILE_NAME_CONVERT
‘/data2/prod’, ‘/data2/prod/dup’
SET LOG_FILE_NAME_CONVERT
‘/data2/prod’, ‘/data2/prod/dup’;2> 3> 4> 5> 6> 7> 8> 9>

Starting Duplicate Db at 10-APR-15

contents of Memory Script:
{
   restore clone spfile to  ‘/data1/oracle11g/product/dbs/spfiledup.ora’ from
 ‘/backup/rman_backup/controlfile_c-284539893-20150410-00’;
   sql clone “alter system set spfile= ”/data1/oracle11g/product/dbs/spfiledup.ora””;
}
executing Memory Script

Starting restore at 10-APR-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /backup/rman_backup/controlfile_c-284539893-20150410-00
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-APR-15

sql statement: alter system set spfile= ”/data1/oracle11g/product/dbs/spfiledup.ora”

contents of Memory Script:
{
   sql clone “alter system set  db_name =
 ”DUP” comment=
 ”duplicate” scope=spfile”;
   sql clone “alter system set  control_files =
 ”/data2/prod/dup/control01.ctl”, ”/data2/prod/dup/control02.ctl” comment=
 ”” scope=spfile”;
   sql clone “alter system set  db_file_name_convert =
 ”/data2/prod”, ”/data2/prod/dup” comment=
 ”” scope=spfile”;
   sql clone “alter system set  LOG_FILE_NAME_CONVERT =
 ”/data2/prod”, ”/data2/prod/dup” comment=
 ”” scope=spfile”;
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ”DUP” comment= ”duplicate” scope=spfile

sql statement: alter system set  control_files =  ”/data2/prod/dup/control01.ctl”, ”/data2/prod/dup/control02.ctl” comment= ”” scope=spfile

sql statement: alter system set  db_file_name_convert =  ”/data2/prod”, ”/data2/prod/dup” comment= ”” scope=spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ”/data2/prod”, ”/data2/prod/dup” comment= ”” 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                318770344 bytes
Database Buffers             822083584 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-20150410-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 =  ”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                318770344 bytes
Database Buffers             822083584 bytes
Redo Buffers                   9383936 bytes

Starting restore at 10-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/dup/control01.ctl
output file name=/data2/prod/dup/control02.ctl
Finished restore at 10-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  5022379;
   set newname for datafile  1 to
 “/data2/prod/dup/system01.dbf”;
   set newname for datafile  2 to
 “/data2/prod/dup/sysaux01.dbf”;
   set newname for datafile  3 to
 “/data2/prod/dup/undotbs01.dbf”;
   set newname for datafile  4 to
 “/data2/prod/dup/users01.dbf”;
   set newname for datafile  5 to
 “/data2/prod/dup/example01.dbf”;
   set newname for datafile  6 to
 “/data2/prod/dup/data01.dbf”;
   set newname for datafile  7 to
 “/data2/prod/dup/test01.dbf”;
   set newname for datafile  8 to
 “/data2/prod/dup/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 10-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/dup/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data2/prod/dup/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data2/prod/dup/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data2/prod/dup/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data2/prod/dup/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /data2/prod/dup/data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /data2/prod/dup/test01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /data2/prod/dup/tools01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2rq41rqd_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2rq41rqd_1_1.bkp tag=TAG20150410T153956
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 10-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=876675571 file name=/data2/prod/dup/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=876675572 file name=/data2/prod/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=876675573 file name=/data2/prod/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=876675574 file name=/data2/prod/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=876675575 file name=/data2/prod/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=876675575 file name=/data2/prod/dup/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=876675576 file name=/data2/prod/dup/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=876675577 file name=/data2/prod/dup/tools01.dbf

contents of Memory Script:
{
   set until scn  5022379;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 10-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=146
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2sq41rsf_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2sq41rsf_1_1.bkp tag=TAG20150410T154103
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=/data1/oracle11g/product/dbs/arch1_146_872701561.dbf thread=1 sequence=146
channel clone_default: deleting archived log(s)
archived log file name=/data1/oracle11g/product/dbs/arch1_146_872701561.dbf RECID=1 STAMP=876675584
media recovery complete, elapsed time: 00:00:03
Finished recover at 10-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                318770344 bytes
Database Buffers             822083584 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                318770344 bytes
Database Buffers             822083584 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/dup/redo01.log’ ) SIZE 50 M  REUSE,
  GROUP  2 ( ‘/data2/prod/dup/redo02.log’ ) SIZE 50 M  REUSE,
  GROUP  3 ( ‘/data2/prod/dup/redo03.log’ ) SIZE 50 M  REUSE
 DATAFILE
  ‘/data2/prod/dup/system01.dbf’
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 “/data2/prod/dup/temp01.dbf”;
   switch clone tempfile all;
   catalog clone datafilecopy  “/data2/prod/dup/sysaux01.dbf”,
 “/data2/prod/dup/undotbs01.dbf”,
 “/data2/prod/dup/users01.dbf”,
 “/data2/prod/dup/example01.dbf”,
 “/data2/prod/dup/data01.dbf”,
 “/data2/prod/dup/test01.dbf”,
 “/data2/prod/dup/tools01.dbf”;
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

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

cataloged datafile copy
datafile copy file name=/data2/prod/dup/sysaux01.dbf RECID=1 STAMP=876675609
cataloged datafile copy
datafile copy file name=/data2/prod/dup/undotbs01.dbf RECID=2 STAMP=876675609
cataloged datafile copy
datafile copy file name=/data2/prod/dup/users01.dbf RECID=3 STAMP=876675609
cataloged datafile copy
datafile copy file name=/data2/prod/dup/example01.dbf RECID=4 STAMP=876675609
cataloged datafile copy
datafile copy file name=/data2/prod/dup/data01.dbf RECID=5 STAMP=876675610
cataloged datafile copy
datafile copy file name=/data2/prod/dup/test01.dbf RECID=6 STAMP=876675610
cataloged datafile copy
datafile copy file name=/data2/prod/dup/tools01.dbf RECID=7 STAMP=876675611

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=876675609 file name=/data2/prod/dup/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=876675609 file name=/data2/prod/dup/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=876675609 file name=/data2/prod/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=876675609 file name=/data2/prod/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=876675610 file name=/data2/prod/dup/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=876675610 file name=/data2/prod/dup/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=876675611 file name=/data2/prod/dup/tools01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 10-APR-15

RMAN>
 
Conclusion
In the above article, we have learned that backup based duplication using Rman utility with different database name and different directory structure, using spfile

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

PREVIOUS POSTSPage 1 of 2NO NEW POSTS