Netsoftmate

Tag: spfile

  • Multiplex Oracle Database Controlfile in ASM

    Introduction:
    In Oracle databases, it is recommended to multiplex you controlfile to safeguard against different failures like corruption, accidentally removing control file and so on.

    In this article I will demonstrate how to multiplex/duplicating a controlfile into Automatic Storage Management (ASM).

    Current Setup

    Exadata 8-node RAC using ASM.
    Current controlfile is stored in ASM.
    Database is using SPFILE.
    There are diffferent ASM Disk Groups available such as DATA, RECO, DBFS_DG, ACFS_DG.
     
    dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
    Instance orcldb1 is running on node dm01db01
    Instance orcldb2 is running on node dm01db02
    Instance orcldb3 is running on node dm01db04
    Instance orcldb4 is running on node dm01db05
    Instance orcldb5 is running on node dm01db07
    Instance orcldb6 is running on node dm01db06
    Instance orcldb7 is running on node dm01db03
    Instance orcldb8 is running on node dm01db08

    SQL> show parameter spfile

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    spfile                               string      +DATA/ORCLDB/PARAMETERFILE/spfile.431.939367673

    SQL> select name from v$controlfile;

    NAME
    ——————————————————————————–
    +DATA/ORCLDB/CONTROLFILE/current.384.939367517

    dm01db01-+ASM1 {/home/oracle}:asmcmd lsdg
    State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
    MOUNTED  NORMAL  Y         512             512   4096  4194304  10092544       424           315392         -157484              1             N  ACFS_DG/
    MOUNTED  NORMAL  Y         512             512   4096  4194304   7208960       532           225280         -112374              1             N  DATA/
    MOUNTED  HIGH    N         512             512   4096  4194304  12390400  12012736           450560         3854058              0             N  RECO/
    MOUNTED  NORMAL  N         512             512   4096  4194304   2106432   2104640            30528         1037056              0             Y  DBFS_DG/

    Steps to multiplex controlfile in ASM When Database is using SPFILE

    • Update the control_files to include the location for second control file.  The second controlfile is going to be created on different diskgroup RECO.

    SQL> alter system set control_files=’+DATA/ORCLDB/CONTROLFILE/current.384.939367517′,‘+RECO’ scope=spfile sid=’*’;

    System altered.

    • Stop and start the instance on node 1 in NOMOUNT state.

    dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
    Instance orcldb1 is running on node dm01db01
    Instance orcldb2 is running on node dm01db02
    Instance orcldb3 is running on node dm01db04
    Instance orcldb4 is running on node dm01db05
    Instance orcldb5 is running on node dm01db07
    Instance orcldb6 is running on node dm01db06
    Instance orcldb7 is running on node dm01db03
    Instance orcldb8 is running on node dm01db08

    dm01db01-orcldb1 {/home/oracle}:srvctl stop database -d orcldb

    dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
    Instance orcldb1 is not running on node dm01db01
    Instance orcldb2 is not running on node dm01db02
    Instance orcldb3 is not running on node dm01db04
    Instance orcldb4 is not running on node dm01db05
    Instance orcldb5 is not running on node dm01db07
    Instance orcldb6 is not running on node dm01db06
    Instance orcldb7 is not running on node dm01db03
    Instance orcldb8 is not running on node dm01db08

    dm01db01-orcldb1 {/home/oracle}:srvctl start instance -d orcldb -i orcldb1 -o nomount

    SQL> set lines 200
    SQL> select * from v$instance;

    INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS     SHU
    ————— —————- —————————————————————- —————– ——— ———— — ———- ——- ————— ———- —
    DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO     CON_ID INSTANCE_MO EDITION FAMILY                                                                           DATABASE_TYPE
    —————– —————— ——— — ———- ———– ——- ——————————————————————————– —————
                  1 orcldb1          dm01db01                                  12.2.0.1.0        09-MAY-17 STARTED      YES          0 STOPPED                 ALLOWED    NO
    ACTIVE            UNKNOWN            NORMAL    NO           0 REGULAR     EE                                                                                       RAC

    • Connect to RMAN and duplicate the controlfile

    dm01db01-orcldb1 {/home/oracle}:rman target /

    Recovery Manager: Release 12.2.0.1.0 – Production on Tue May 9 05:07:45 2017

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

    connected to target database: ORCLDB (not mounted)

    RMAN> restore controlfile from ‘+DATA/ORCLDB/CONTROLFILE/current.384.939367517’;

    Starting restore at 09-MAY-17
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=372 instance=orcldb1 device type=DISK

    channel ORA_DISK_1: copied control file copy
    output file name=+DATA/ORCLDB/CONTROLFILE/current.384.939367517
    output file name=+RECO/ORCLDB/CONTROLFILE/current.1003.943506471
    Finished restore at 09-MAY-17

    RMAN> exit

    Recovery Manager complete.

    • update the control_file parameter with the full path and name.

    SQL> alter system set control_files=’+DATA/ORCLDB/CONTROLFILE/current.384.939367517′,’+RECO/ORCLDB/CONTROLFILE/current.1003.943506471′ scope=spfile sid=’*’;

    System altered.

    • Shutdown and start database

    SQL> shutdown immediate;
    ORA-01507: database not mounted

    ORACLE instance shut down.
    SQL> exit

    dm01db01-orcldb1 {/home/oracle}:srvctl start database -d orcldb

    dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
    Instance orcldb1 is running on node dm01db01
    Instance orcldb2 is running on node dm01db02
    Instance orcldb3 is running on node dm01db04
    Instance orcldb4 is running on node dm01db05
    Instance orcldb5 is running on node dm01db07
    Instance orcldb6 is running on node dm01db06
    Instance orcldb7 is running on node dm01db03
    Instance orcldb8 is running on node dm01db08

    • verify that both controlfiles are in ASM now.

    SQL> select name from v$controlfile;

    NAME
    ——————————————————————————–
    +DATA/ORCLDB/CONTROLFILE/current.384.939367517
    +RECO/ORCLDB/CONTROLFILE/current.1003.943506471

    SQL> show parameter control_files

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    control_files                        string      +DATA1/ORCLDB/CONTROLFILE/current.384.939367517, +RECO/ORCLDB/CONTROLFILE/current.1003.943506471

    Conclusion
    In this article we have learned how to duplicate a control file in ASM. Multiplexing control file is recommended to safeguard against controlfil failures.

  • Rman backup based duplication with same DB name and directory structure and target db have Read only tablespaces

    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