Netsoftmate

Category: Uncategorized

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

    Overview
    Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
    Here we have maintained same directory structure but different database name.



    Environment Details

    Target (source) details:

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

    Destination details:

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

     

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

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

    [oracle@Nsmsrc01 dbs]$ hostname


    Nsmsrc01


    [oracle@Nsmsrc01 dbs]$ export ORACLE_SID=prod

     

    2.    Connect to RMAN.

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



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

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

    RMAN> show all;



    RMAN configuration parameters for database with
    db_unique_name PROD are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
    TYPE DISK TO ‘/backup/rman_backup/controlfile_%F’;
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
    TO BACKUPSET; # default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK
    TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
    DISK TO 1; # default
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘/backup/rman_backup/db_arch_%U.bkp’;
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
    CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE
    ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
    default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO
    ‘/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f’; # default

    RMAN> backup database plus archivelog;


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

    Starting backup at 09-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup
    set
    input datafile file number=00001
    name=/data2/prod/system01.dbf
    input datafile file number=00002
    name=/data2/prod/sysaux01.dbf
    input datafile file number=00005
    name=/data2/prod/example01.dbf
    input datafile file number=00003
    name=/data2/prod/undotbs01.dbf
    input datafile file number=00006
    name=/data2/prod/data01.dbf
    input datafile file number=00004
    name=/data2/prod/users01.dbf
    input datafile file number=00007
    name=/data2/prod/test01.dbf
    input datafile file number=00008
    name=/data2/prod/tools01.dbf
    channel ORA_DISK_1: starting piece 1 at 09-APR-15
    channel ORA_DISK_1: finished piece 1 at 09-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2nq3v9q2_1_1.bkp tag=TAG20150409T162018
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:56
    Finished backup at 09-APR-15

    Starting backup at 09-APR-15
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in
    backup set
    input archived log thread=1 sequence=143 RECID=139
    STAMP=876586874
    channel ORA_DISK_1: starting piece 1 at 09-APR-15
    channel ORA_DISK_1: finished piece 1 at 09-APR-15
    piece
    handle=/backup/rman_backup/db_arch_2oq3v9rr_1_1.bkp tag=TAG20150409T162115
    comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:01
    Finished backup at 09-APR-15

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

    RMAN>


    4.    Create pfile and copy to destination server.

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



    File created.
    [oracle@Nsmsrc01 rman_backup]$ scp initprod.ora 
    oracle@172.16.110.16:/data1/oracle11g/product/dbs/
    oracle@172.16.110.16’s password:
    initprod.ora                                                                                                               
                                           100%
    1169     1.1KB/s   00:00

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

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


    5.    Copy rman backup from target to destination server.

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


    oracle@172.16.110.16’s password:

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

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

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


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


    7.    Start the new instance in nomount

    [oracle@Nsmtrg rman_backup]$ export ORACLE_SID=dup


    [oracle@Nsmtrg dbs]$ sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9
    16:24:32 2015
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Enter user-name: sys as sysdba
    Enter password:
    Connected to an idle instance.
    SQL> startup nomount
    ORACLE instance started.
    Total System Global Area 1152450560 bytes
    Fixed Size                  2212696 bytes
    Variable Size             352324776 bytes
    Database Buffers          788529152 bytes
    Redo Buffers                9383936 bytes
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise
    Edition Release 11.2.0.1.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real
    Application Testing options

    8.    Connect to rman

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



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

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

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




    Starting Duplicate Db at 09-APR-15

    contents of Memory Script:

    {

       sql clone
    “create spfile from memory”;

    }

    executing Memory Script

    sql statement: create spfile from memory

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup
    clone nomount;

    }

    executing Memory Script

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    contents of Memory Script:

    {

       sql clone
    “alter system set  db_name =

     ”PROD”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       sql clone
    “alter system set  db_unique_name =

     ”DUP”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone force nomount

       restore
    clone primary controlfile from 
    ‘/backup/rman_backup/controlfile_c-284539893-20150409-02’;

       alter clone
    database mount;

    }

    executing Memory Script

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

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

    Oracle instance shut down

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    Starting restore at 09-APR-15

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    channel ORA_AUX_DISK_1: restoring control file

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

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

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

    Finished restore at 09-APR-15

    database mounted

    released channel: ORA_AUX_DISK_1

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    contents of Memory Script:

    {

       set until
    scn  4972796;

       set newname
    for datafile  1 to

     “/data2/prod/system01.dbf”;

       set newname
    for datafile  2 to

     “/data2/prod/sysaux01.dbf”;

       set newname
    for datafile  3 to

     “/data2/prod/undotbs01.dbf”;

       set newname
    for datafile  4 to

     “/data2/prod/users01.dbf”;

       set newname
    for datafile  5 to

     “/data2/prod/example01.dbf”;

       set newname
    for datafile  6 to

     “/data2/prod/data01.dbf”;

       set newname
    for datafile  7 to

     “/data2/prod/test01.dbf”;

       set newname
    for datafile  8 to

     “/data2/prod/tools01.dbf”;

       restore

       clone
    database

       ;

    }

    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 09-APR-15

    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting datafile backup set
    restore

    channel ORA_AUX_DISK_1: specifying datafile(s) to
    restore from backup set

    channel ORA_AUX_DISK_1: restoring datafile 00001 to
    /data2/prod/system01.dbf

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

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

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

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

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

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

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

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

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

    channel ORA_AUX_DISK_1: restored backup piece 1

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

    Finished restore at 09-APR-15

    contents of Memory Script:

    {

       switch clone
    datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

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

    datafile 2 switched to datafile copy

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

    datafile 3 switched to datafile copy

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

    datafile 4 switched to datafile copy

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

    datafile 5 switched to datafile copy

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

    datafile 6 switched to datafile copy

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

    datafile 7 switched to datafile copy

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

    datafile 8 switched to datafile copy

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

    contents of Memory Script:

    {

       set until
    scn  4972796;

       recover

       clone
    database

        delete
    archivelog

       ;

    }

    executing Memory Script

    executing command: SET until clause

    Starting recover at 09-APR-15

    using channel ORA_AUX_DISK_1

    starting media recovery

    channel ORA_AUX_DISK_1: starting archived log restore
    to default destination

    channel ORA_AUX_DISK_1: restoring archived log

    archived log thread=1 sequence=143

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

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

    channel ORA_AUX_DISK_1: restored backup piece 1

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

    archived log file
    name=/backup/archive/1_143_872701561.dbf thread=1 sequence=143

    channel clone_default: deleting archived log(s)

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

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

    Finished recover at 09-APR-15

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup
    clone nomount;

       sql clone
    “alter system set  db_name =

     ”DUP”
    comment=

     ”Reset to
    original value by RMAN” scope=spfile”;

       sql clone
    “alter system reset  db_unique_name
    scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone nomount;

    }

    executing Memory Script

    database dismounted

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    sql statement: alter system set  db_name = 
    ”DUP” comment= ”Reset to original value by RMAN” scope=spfile

    sql statement: alter system reset  db_unique_name scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

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

     
    MAXLOGFILES     16

     
    MAXLOGMEMBERS      3

     
    MAXDATAFILES      100

     
    MAXINSTANCES     8

     
    MAXLOGHISTORY      292

     LOGFILE

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

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

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

     DATAFILE

     
    ‘/data2/prod/system01.dbf’

     CHARACTER SET
    WE8MSWIN1252

    contents of Memory Script:

    {

       set newname
    for tempfile  1 to

     “/data2/prod/temp01.dbf”;

       switch clone
    tempfile all;

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

     “/data2/prod/undotbs01.dbf”,

     “/data2/prod/users01.dbf”,

     “/data2/prod/example01.dbf”,

     “/data2/prod/data01.dbf”,

     “/data2/prod/test01.dbf”,

     “/data2/prod/tools01.dbf”;

       switch clone
    datafile all;

    }

    executing Memory Script

    executing command: SET NEWNAME

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    datafile 2 switched to datafile copy

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

    datafile 3 switched to datafile copy

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

    datafile 4 switched to datafile copy

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

    datafile 5 switched to datafile copy

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

    datafile 6 switched to datafile copy

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

    datafile 7 switched to datafile copy

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

    datafile 8 switched to datafile copy

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

    contents of Memory Script:

    {

       Alter clone
    database open resetlogs;

    }

    executing Memory Script

    database opened

    Finished Duplicate Db at 09-APR-15

    RMAN>

     

    Conclusion
    In the preceding scenario based article, we have learned that backup based duplication using Rman utility with different database name and same directory structure.

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

  • Rman backup based duplication with ReadOnly tablespaces, with different DB name and same directory structure.

    Overview
    Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
    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.

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

    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

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

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

    Target
    (source) details:

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



    Pre-requisites

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

    Steps on source server:

    1. Set Oracle sid on which backup will perform.

    [oracle@NSM-SRC dbs]$ hostname


    NSM-SRC

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




    2.    Connect to RMAN.

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

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

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

    connected to target database: PROD (DBID=284539893)

    RMAN>

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


    RMAN> report schema;

    using target database control file instead of
    recovery catalog

    Report of database schema for database with
    db_unique_name PROD

    List of Permanent Datafiles

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

    File Size(MB) Tablespace           RB segs Datafile Name

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

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

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

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

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

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

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

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

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

    List of Temporary Files

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

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

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

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


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

         FILE# NAME

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

             1
    /data2/prod/system01.dbf

             2
    /data2/prod/sysaux01.dbf

             3
    /data2/prod/undotbs01.dbf

             4
    /data2/prod/users01.dbf

             5
    /data2/prod/example01.dbf

             6
    /data2/prod/data01.dbf

             7
    /data2/prod/test01.dbf

             8
    /data2/prod/tools01.dbf

    8 rows selected.

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

         FILE# NAME

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

             1
    /data2/prod/temp01.dbf


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

    RMAN> show all;

    using target database control file instead of
    recovery catalog

    RMAN configuration parameters for database with
    db_unique_name PROD are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    CONFIGURE CONTROLFILE AUTOBACKUP ON;

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

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

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

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

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

    CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

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

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
    default

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

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

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

    RMAN> list backup;

    List of Backup Sets

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

    BS Key 
    Size       Device Type Elapsed
    Time Completion Time

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

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

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

            Piece
    Name: /backup/rman_backup/db_arch_2uq495u9_1_1.bkp

      List of
    Archived Logs in backup set 32

      Thrd Seq     Low SCN   
    Low Time  Next SCN   Next Time

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

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

    BS Key  Type LV
    Size       Device Type Elapsed Time
    Completion Time

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

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

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

            Piece
    Name: /backup/rman_backup/db_arch_2vq495ua_1_1.bkp

      List of
    Datafiles in backup set 33

      File LV Type
    Ckp SCN    Ckp Time  Name

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

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

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

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

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

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

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

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

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

    BS Key 
    Size       Device Type Elapsed
    Time Completion Time

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

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

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

            Piece
    Name: /backup/rman_backup/db_arch_30q49603_1_1.bkp

      List of
    Archived Logs in backup set 34

      Thrd Seq     Low SCN   
    Low Time  Next SCN   Next Time

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

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

    BS Key  Type LV
    Size       Device Type Elapsed Time
    Completion Time

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

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

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

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

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

    RMAN>



    5.    Create pfile and copy to destination server.

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

    File created.

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


    oracle@172.16.110.16’s password:

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

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

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



    6.    Copy rman backup from target to destination server.

    RMAN> exit

    Recovery Manager complete.

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

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

    total 1176020

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

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

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

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

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




    Steps on destination server:

    7.    Create new directory structure.

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



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

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

    *.db_name=’snname’

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

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

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

    10.    Start the instance in no mount stage.

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

    [oracle@NSM-TRG dbs]$ sqlplus

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

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

    Enter user-name: sys as sysdba

    Enter password:

    Connected to an idle instance.

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 1152450560 bytes

    Fixed Size                  2212696 bytes

    Variable Size             352324776 bytes

    Database Buffers          788529152 bytes

    Redo Buffers                9383936 bytes

    SQL>


    11.    Connect to auxiliary instance through RMAN.

    SQL> exit

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

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

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

    snname

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

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

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

    connected to auxiliary
    database: SNNAME (not mounted)

    RMAN>

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

    RMAN> RUN


    2> {

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

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

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

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

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

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

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

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

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

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

    13> LOGFILE

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

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

    16> }

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting Duplicate Db at 13-APR-15

    contents of Memory Script:

    {

       sql clone
    “create spfile from memory”;

    }

    executing Memory Script

    sql statement: create spfile from memory

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup
    clone nomount;

    }

    executing Memory Script

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    contents of Memory Script:

    {

       sql clone
    “alter system set  db_name =

     ”PROD”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       sql clone
    “alter system set  db_unique_name =

     ”SNNAME”
    comment=

     ”Modified by
    RMAN duplicate” scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone force nomount

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

       alter clone
    database mount;

    }

    executing Memory Script

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

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

    Oracle instance shut down

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    Starting restore at 13-APR-15

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    channel ORA_AUX_DISK_1: restoring control file

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

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

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

    Finished restore at 13-APR-15

    database mounted

    released channel: ORA_AUX_DISK_1

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: SID=63 device type=DISK

    contents of Memory Script:

    {

       set until
    scn  5176531;

       set newname
    for datafile  1 to

     “/data2/snname/system01.dbf”;

       set newname
    for datafile  2 to

     “/data2/snname/sysaux01.dbf”;

       set newname
    for datafile  3 to

     “/data2/snname/undotbs01.dbf”;

       set newname
    for datafile  4 to

     “/data2/snname/users01.dbf”;

       set newname
    for datafile  5 to

     “/data2/snname/example01.dbf”;

       set newname
    for datafile  6 to

     “/data2/snname/data01.dbf”;

       set newname
    for datafile  7 to

     “/data2/snname/test01.dbf”;

       set newname
    for datafile  8 to

     “/data2/snname/tools01.dbf”;

       restore

       clone
    database

       ;

    }

    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 13-APR-15

    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting datafile backup set
    restore

    channel ORA_AUX_DISK_1: specifying datafile(s) to
    restore from backup set

    channel ORA_AUX_DISK_1: restoring datafile 00001 to
    /data2/snname/system01.dbf

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

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

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

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

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

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

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

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

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

    channel ORA_AUX_DISK_1: restored backup piece 1

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

    Finished restore at 13-APR-15

    contents of Memory Script:

    {

       switch clone
    datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

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

    datafile 2 switched to datafile copy

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

    datafile 3 switched to datafile copy

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

    datafile 4 switched to datafile copy

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

    datafile 5 switched to datafile copy

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

    datafile 6 switched to datafile copy

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

    datafile 7 switched to datafile copy

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

    datafile 8 switched to datafile copy

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

    contents of Memory Script:

    {

       set until
    scn  5176531;

       recover

       clone database

        delete
    archivelog

       ;

    }

    executing Memory Script

    executing command: SET until clause

    Starting recover at 13-APR-15

    using channel ORA_AUX_DISK_1

    starting media recovery

    channel ORA_AUX_DISK_1: starting archived log restore
    to default destination

    channel ORA_AUX_DISK_1: restoring archived log

    archived log thread=1 sequence=154

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

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

    channel ORA_AUX_DISK_1: restored backup piece 1

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

    archived log file
    name=/backup/archive/1_154_872701561.dbf thread=1 sequence=154

    channel clone_default: deleting archived log(s)

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

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

    Finished recover at 13-APR-15

    contents of Memory Script:

    {

       shutdown
    clone immediate;

       startup clone nomount;

       sql clone
    “alter system set  db_name =

     ”SNNAME”
    comment=

     ”Reset to
    original value by RMAN” scope=spfile”;

       sql clone
    “alter system reset  db_unique_name
    scope=spfile”;

       shutdown
    clone immediate;

       startup
    clone nomount;

    }

    executing Memory Script

    database dismounted

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size   
                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

    sql statement: alter system set  db_name = 
    ”SNNAME” comment= ”Reset to original value by RMAN” scope=spfile

    sql statement: alter system reset  db_unique_name scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)

    Oracle instance started

    Total System Global Area    1152450560 bytes

    Fixed Size                     2212696 bytes

    Variable Size                335547560 bytes

    Database Buffers             805306368 bytes

    Redo Buffers                   9383936 bytes

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

     
    MAXLOGFILES     16

     
    MAXLOGMEMBERS      3

     
    MAXDATAFILES      100

     
    MAXINSTANCES     8

     
    MAXLOGHISTORY      292

     LOGFILE

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

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

     DATAFILE

     
    ‘/data2/snname/system01.dbf’

     CHARACTER SET
    WE8MSWIN1252

    contents of Memory Script:

    {

       set newname
    for tempfile  1 to

     “/data2/snname/temp01.dbf”;

       switch clone
    tempfile all;

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

     “/data2/snname/undotbs01.dbf”,

     “/data2/snname/users01.dbf”,

     “/data2/snname/example01.dbf”,

     “/data2/snname/data01.dbf”,

     “/data2/snname/test01.dbf”,

     “/data2/snname/tools01.dbf”;

       switch clone
    datafile all;

    }

    executing Memory Script

    executing command: SET NEWNAME

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    cataloged datafile copy

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

    datafile 2 switched to datafile copy

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

    datafile 3 switched to datafile copy

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

    datafile 4 switched to datafile copy

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

    datafile 5 switched to datafile copy

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

    datafile 6 switched to datafile copy

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

    datafile 7 switched to datafile copy

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

    datafile 8 switched to datafile copy

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

    contents of Memory Script:

    {

       Alter clone
    database open resetlogs;

    }

    executing Memory Script

    database opened

    Finished Duplicate Db at 13-APR-15

    RMAN>

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

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

  • De-Register Database Vault on RAC DB

    Overview:
    In this article we explain the process of  how to De-Register DBV on RAC DB.

    Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs. Application data can be protected with policies that control access by setting up the rules.
    Environment Details

    Hostname
    Node1 : db01

    Node 2: db02
    Database version
    11.2.0.4
    Database Environment
    OMFDB



    Disable DB vault 



    Login with DBVOWNER to DB and disable the Rules



    $sqlplus -s / as sysdba
    SQL>set lines 200
    SQL>set pages 0
    SQL>set feed off
    SQL>select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

    STATUS
    ———–
    VALID

    SQL>conn dbvowner/******
    Connected.

    SQL>BEGIN
    DBMS_MACADM.UPDATE_COMMAND_RULE(
      command         => ‘CONNECT’,
      rule_set_name   =>  ‘LIMIT_DIP_MONITOR_USER’,
      object_owner => NULL,
      object_name => NULL,
      enabled         => DBMS_MACUTL.G_NO);    —-> it will disable the DBV rule and it will allow to application to connect db
    END;
    /

    PL/SQL procedure successfully completed.

    SQL>BEGIN
      DBMS_MACADM.UPDATE_RULE_SET (
             rule_set_name => ‘LIMIT_DIP_MONITOR_USER’,
             description => ‘Limit DIP_MONITOR_USER Access to certain IP’,
             enabled => DVSYS.DBMS_MACUTL.G_NO,
             eval_options  => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ANY,
             audit_options => DVSYS.DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
             fail_options => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,
             fail_message => ‘Rule Set Limit DIP_MONITOR_USER Access failed.’,
             fail_code => -20001,
             handler_options  => DVSYS.DBMS_MACUTL.G_RULESET_HANDLER_OFF,
             handler         => NULL);
    END;
    /

    PL/SQL procedure successfully completed.

    SQL> conn dbvowner/*******
    Connected.

    SQL>  select ENABLED from dba_dv_rule_set where RULE_SET_NAME=’LIMIT_DIP_MONITOR_USER’;

    E
    –
    N



    Run following scripts to remove Vault.


    SQL> set line 200
    SQL> SELECT HOST_NAME,name,INSTANCE_NAME,open_mode,to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) “DB Startup Time” FROM gv$database,sys.gv_$instance;

    HOST_NAME               NAME      INSTANCE_NAME    OPEN_MODE      DB Startup Time
    —————————————————————- ——— —————- 
    dm01.tfn.com           OMFDB  omfdb01        READ WRITE           14-JAN-2017 
    dm01.tfn.com           OMFDB  omfdb02        READ WRITE           14-JAN-2017 


    SQL> update dvsys.config$ set status=0;

    1 row updated.

    SQL> commit;

    Commit complete.

    SQL> @?/rdbms/admin/dvremov.sql

    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    Commit complete.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    1 row deleted.


    1 row deleted.


    PL/SQL procedure successfully completed.


    Drop DBV users and grant back the roles and privs to DBA

    SQL> drop user dbvowner cascade;

    User dropped.

    SQL> drop user dbvacctmgr cascade;

    User dropped.

    SQL> grant BECOME USER to DBA;

    Grant succeeded.

    SQL> grant SELECT ANY TRANSACTION to DBA;

    Grant succeeded.

    SQL> grant CREATE ANY JOB to DBA;

    Grant succeeded.

    SQL> grant CREATE EXTERNAL JOB to DBA;

    Grant succeeded.

    SQL> grant EXECUTE ANY PROGRAM to DBA;

    Grant succeeded.

    SQL> grant EXECUTE ANY CLASS to DBA;

    Grant succeeded.

    SQL> grant MANAGE SCHEDULER to DBA;

    Grant succeeded.

    SQL> grant DEQUEUE ANY QUEUE to DBA;

    Grant succeeded.

    SQL> grant ENQUEUE ANY QUEUE to DBA;

    Grant succeeded.

    SQL> grant MANAGE ANY QUEUE to DBA;

    Grant succeeded.

    SQL> grant BECOME USER to IMP_FULL_DATABASE;

    Grant succeeded.

    SQL> grant MANAGE ANY QUEUE to IMP_FULL_DATABASE;

    Grant succeeded.

    SQL> grant DBA to INFA_ADMIN;

    Grant succeeded.

    SQL> grant EXECUTE ON DBMS_LOGMNR to EXECUTE_CATALOG_ROLE;

    Grant succeeded.

    SQL> grant EXECUTE ON DBMS_LOGMNR_D to EXECUTE_CATALOG_ROLE;

    Grant succeeded.

    SQL> grant EXECUTE ON DBMS_LOGMNR_LOGREP_DICT to EXECUTE_CATALOG_ROLE;

    Grant succeeded.

    SQL> grant EXECUTE ON DBMS_LOGMNR_SESSION to EXECUTE_CATALOG_ROLE;

    Grant succeeded.

    SQL> grant EXECUTE ON DBMS_FILE_TRANSFER to EXECUTE_CATALOG_ROLE;

    Grant succeeded.

    SQL> grant EXECUTE ON UTL_FILE to PUBLIC;

    Grant succeeded.

    SQL> grant CREATE ANY JOB to SCHEDULER_ADMIN;

    Grant succeeded.

    SQL> grant CREATE EXTERNAL JOB to SCHEDULER_ADMIN;

    Grant succeeded.

    SQL> grant EXECUTE ANY PROGRAM to SCHEDULER_ADMIN;

    Grant succeeded.

    SQL> grant EXECUTE ANY CLASS to SCHEDULER_ADMIN;

    Grant succeeded.

    SQL> grant MANAGE SCHEDULER to SCHEDULER_ADMIN;

    Grant succeeded.

    SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=FALSE SCOPE=SPFILE sid=’*’;

    System altered.

    SQL> ALTER SYSTEM SET RECYCLEBIN=’ON’ SCOPE=SPFILE sid=’*’;

    System altered.

    SQL> ALTER SYSTEM SET SQL92_SECURITY=FALSE SCOPE=SPFILE sid=’*’;

    System altered.

    Check the status of DBV 

    SQL> select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

    no rows selected

    exit

    Bounce the DB 

    $ srvctl status database -d omfdb
    Instance omfdb1 is running on node dm01
    Instance omfdb2 is running on node dm02

    $ srvctl stop database -d omfdb

    $ srvctl status database -d omfdb
    Instance omfdb1 is not running on node dm01
    Instance omfdb2 is not running on node dm02

    $ srvctl start database -d omfdb

    $ srvctl status database -d omfdb
    Instance omfdb1 is running on node dm01
    Instance omfdb2 is running on node dm02

    Verify DBV removed completely.


    SQL> select status from dba_registry where COMP_NAME=’Oracle Database Vault’;


    no rows selected


    exit

    Conculsion:
    In this article we learnt the process of  how to De-Register DBV on RAC DB.

  • Update Existing Database Vault settings.

    Overview:
    In this article we explain the process of  how to Update the existing Database Vault settings.

    Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs.Application data can be protected with policies that control access by setting up the rules.

    Environment Details
    Hostname
    Node1 : db01

    Node 2: db02
    Database version
    11.2.0.4
    Database Environment
    OMFDB
    Login to Database and check the current DBV Rule
    $sqlplus / as sysdba
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
    OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

    SQL> set line 200
    SQL> set pages 00
    SQL> set pages 200
    SQL> select name,open_mode,database_role from v$database;

    NAME      OPEN_MODE            DATABASE_ROLE
    ——— ——————– —————-
    OMFDB     READ WRITE           PRIMARY

    SQL> SELECT RULE_SET_NAME, HANDLER_OPTIONS, HANDLER FROM DVSYS.DBA_DV_RULE_SET
     WHERE RULE_SET_NAME = ‘Allow MONITOR_USER Access’;  2

    no rows selected

    SQL>
    SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

    NAME                          RULE_EXPR
    ——————————————————————————————
    Allow MONITOR_USER Access    DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’


    SQL> SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE WHERE RULE_NAME = ‘Allow MONITOR_USER Access’;

    RULE_SET_NAME                    RULE_NAME                         RULE_EXPR
    —————————————————————————————— ——————————————————————————————
    LIMIT_MONITOR_USER  Allow MONITOR_USER Access       DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

    Login to DBVOWNER and Update the DBV rules with additional application IPs

    SQL> conn dbvowner/*********
    Connected.
    SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;


    NAME                        RULE_EXPR
    ——————————————————————————————

    Allow MONITOR_USER Access DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’



    SQL> begin
    DVSYS.DBMS_MACADM.UPDATE_RULE
      2    3  (rule_name => ‘Allow MONITOR_USER Access’,
      4  rule_expr => ‘DVF.F$CLIENT_IP in (”10.10.1.1”,”172.10.1.1”,”192.168.1.1”, ”10.10.10.1”,”10.10.10.120”) and DVF.F$SESSION_USER = ”MONITOR_USER”’);
      5  end;
      6  /



    PL/SQL procedure successfully completed.


    Verify the changes



    SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;


    NAME                                 RULE_EXPR
    ——————————————————————————————
    Allow MONITOR_USER Access      DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1′,10.10.10.1′,’10.10.10.120’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

    Overview:

    In this article we learnt how to Update the existing Database Vault settings.

  • Enable Database Vault on RAC Database

    Overview:
    In this article we explain the process of  how to register RAC DB in Database Vault

    Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs. 
    Application data can be protected with policies that control access by setting up the rules.
    Environment Details
    Hostname
    Node1 : db01

    Node 2: db02
    Database version
    11.2.0.4
    Database Environment
    OMFDB

    Register Database in Database Vault.
    $export PATH=$PATH:$ORACLE_HOME/bin
    $dbca -silent -configureDatabase -sourceDB omfdb -sysDBAUserName sys -sysDBAPassword ******* -addDBOption OMS,DV -dvUserName dbvowner -dvUserPassword ******* -dvAccountManagerName dbvacctmgr -dvAccountManagerPassword ********

    Preparing to Configure Database
    1% complete
    3% complete
    18% complete
    Adding Oracle Label Security
    19% complete
    20% complete
    21% complete
    22% complete
    23% complete
    24% complete
    42% complete
    54% complete
    Adding Oracle Database Vault
    65% complete
    90% complete
    Completing Database Configuration
    100% complete
    Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/omfdb/omfdb.log” for further details.

    Login to database and change the below parameter to take effect database vault.

    $sqlplus / as sysdba
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
    OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

    SQL> set line 200
    SQL> set line 200
    SQL> set pages 200
    SQL> show parameter AUDIT_SYS_OPERATIONS

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    audit_sys_operations                 boolean     FALSE
    SQL> show parameter AUDIT_SYS_OPERATIONS

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    audit_sys_operations                 boolean     FALSE
    SQL> show parameter os_roles

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    os_roles                             boolean     FALSE
    remote_os_roles                      boolean     FALSE
    SQL> show parameter RECYCLEBIN

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    recyclebin                           string      on
    SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    remote_login_passwordfile            string      EXCLUSIVE
    SQL> show parameter SQL92_SECURITY

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    sql92_security                       boolean     FALSE
    SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE sid=’*’;

    System altered.

    sql>ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE sid=’*’;

    System altered.

    SQL>ALTER SYSTEM SET RECYCLEBIN=’OFF’ SCOPE=SPFILE sid=’*’;

    System altered.

    SQL>ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’ SCOPE=SPFILE sid=’*’;

    System altered.

    SQL>ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE sid=’*’;

    System altered.

    Check the changes done and DBV Enabled.

    SQL> show parameter AUDIT_SYS_OPERATIONS

    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    audit_sys_operations                 boolean     FALSE

    SQL> set pages 200
    SQL>  select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

    STATUS
    ——————————————–
    VALID

    Bouce database

    $ srvctl status database -d omfdb
    Instance omfdb1 is running on node dm01
    Instance omfdb2 is running on node dm02

    $ srvctl stop database -d omfdb

    $ srvctl status database -d omfdb
    Instance omfdb1 is not running on node dm01
    Instance omfdb2 is not running on node dm02

    $ srvctl start database -d omfdb

    $ srvctl status database -d omfdb
    Instance omfdb1 is running on node dm01
    Instance omfdb2 is running on node dm02

    Re-grant for app specific

    SQL> connect / as sysdba
    SQL> grant become user to IMP_FULL_DATABASE;

    SQL> grant become user to IMP_FULL_DATABASE;

    Grant succeeded.
    Setting up DBV rules for app

    Here in this example we are setting up the DBV rule to allow apps users to access the Database.

    $sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 2017

    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
    OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

    SQL> set line 200
    SQL> set pages 00
    SQL> set pages 200
    SQL> select name from v$database;

    NAME
    ———
    OMFDB

    SQL>  select status from dba_registry where COMP_NAME=’Oracle Database Vault’;

    STATUS
    ——————————————–
    VALID


    Login to DBVOWNER to enable the DBV rules.

    SQL> conn dbvowner/*******
    Connected.


    SQL>BEGIN
      DBMS_MACADM.CREATE_RULE (
              rule_name => ‘Allow MONITOR_USER Access’,
              rule_expr => ‘DVF.F$CLIENT_IP in (”10.10.1.1”,”172.10.1.1”,”192.168.1.1”) and DVF.F$SESSION_USER = ”MONITOR_USER”’);
    END;
    /

    PL/SQL procedure successfully completed.


    SQL>BEGIN
      DBMS_MACADM.CREATE_RULE (
              rule_name => ‘Allow non MONITOR_USER Access’,
              rule_expr => ‘UPPER(DVF.F$CLIENT_IP) NOT in (”10.10.1.1”,”172.10.1.1”,”192.168.1.1”) OR DVF.F$CLIENT_IP is NULL’ );
    END;
    /

    PL/SQL procedure successfully completed.

    Now Create the Rule set 

    SQL>BEGIN
      DBMS_MACADM.CREATE_RULE_SET (
             rule_set_name => ‘LIMIT_MONITOR_USER’,
             description => ‘Limit MONITOR_USER Access to certain IP’,
             enabled => DVSYS.DBMS_MACUTL.G_YES,
             eval_options  => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ANY,
             audit_options => DVSYS.DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
             fail_options => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,
             fail_message => ‘Rule Set Limit MONITOR_USER Access failed.’,
             fail_code => -20001,
             handler_options  => DVSYS.DBMS_MACUTL.G_RULESET_HANDLER_OFF,
             handler         => NULL);
    END;
    /

    PL/SQL procedure successfully completed.


    SQL>BEGIN
      DBMS_MACADM.ADD_RULE_TO_RULE_SET (
            rule_set_name => ‘LIMIT_MONITOR_USER’,
            rule_name => ‘Allow non MONITOR_USER Access’,
            rule_order => 1);
      DBMS_MACADM.ADD_RULE_TO_RULE_SET (
            rule_set_name => ‘LIMIT_MONITOR_USER’,
            rule_name => ‘Allow MONITOR_USER Access’,
            rule_order => 1);
    END;
    /

    PL/SQL procedure successfully completed.


    SQL>BEGIN
      DBMS_MACADM.CREATE_COMMAND_RULE (
         command => ‘CONNECT’,
         rule_set_name => ‘LIMIT_MONITOR_USER’,
         object_owner => NULL,
         object_name => NULL,
         enabled => DBMS_MACUTL.G_YES);
    END;
    /

    PL/SQL procedure successfully completed.


    Verify the DBV rule creation.

    SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

    NAME
    ——————————————————————————————
    RULE_EXPR
    ——————————————————————————————————————————————————————————————————–
    Allow MONITOR_USER Access
    DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’


    SQL> SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE
     WHERE RULE_NAME = ‘Allow MONITOR_USER Access’;  2

    RULE_SET_NAME                                                                              RULE_NAME
    —————————————————————————————— ——————————————————————————————
    RULE_EXPR
    ——————————————————————————————————————————————————————————————————–
    LIMIT_MONITOR_USER_ACCESS                                                              Allow MONITOR_USER Access
    DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’


    SQL> conn dbvowner/*********
    Connected.
    SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = ‘Allow MONITOR_USER Access’;

    NAME
    ——————————————————————————————
    RULE_EXPR
    ——————————————————————————————————————————————————————————————————–
    Allow MONITOR_USER Access
    DVF.F$CLIENT_IP in (‘10.10.1.1′,’172.10.1.1′,’192.168.1.1’) and DVF.F$SESSION_USER = ‘MONITOR_USER’

    Conclusion:
    In this article we explain the process of  how to register RAC DB in Database Vault

  • RAC DB creation on Exadata Machine using DBCA.

    Overview:
    In this article we explain the process of  how to create RAC DB on Exadata Machine using DBCA


    DBCA is the oracle utility which provide the easy and fast way to create the DB.
    Environment Details

    Hostname
    Node1 : db01

    Node 2: db02
    Database version
    11.2.0.4
    Database Environment
    OMFDB 

    Login to Exadata Machine node1 and initate DB creation using DBCA.

    $export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
    $dbca




    DBCA launched db creation window.
    select oracle Real appliction clusters(RAC) database option than next







    Select create database option then Next





    Select General purpose and click next
    Select Admin-Managed and give the DB Name and SID and select nodes in Exadata Machine.Then Next

    Un-check Register with Grid crontrol and slect configuration Database control for Local Management.Then Next
    Provide the SYS and SYS and all the other password.Then Next
    Provide the Database files location.Then Next
    Click on Multiplex Redo logs and Control Files if you want.Then Next
     Provide ASMSNMP Password.Then Next
    Provide the Flash Recovery Area Diskgroup. Then Next
     Provide the Archive log format.Then Next
    Next
    Provide SGA and PGA and all the sizing and character set ,Then Next
    Provide the Redo log size and Next.
     Create Database and Finish.
    Read Summary and Click OK
    Progress
    Database Creation completion Summary, Click Exit.



    Check the Database status it should run on both nodes.

    $ srvctl status database -d omfdb
    Instance omfdb1 is running on node dm01
    Instance omfdb2 is running on node dm02

    Conclusion:
    We learnt the process of  how to create RAC DB on Exadata Machine using DBCA

  • Data guard Broker setup

    Overview:
    In this
    article we explain the process of  Data guard Broker setup
    DG Broker maintains,
    manage, and monitor one or more standby databases to enable production Oracle
    databases to survive disasters and data corruptions



    Environment Details

    Hostname
    Primary
    server :–Primary
    Standby
    Server :– Standby
    Operating
    system
    Linux
    Database
    Environment
    NSMPRD
    and NSMDR



    Ensure
    that both the primary and standby databases are up and running as shown in the
    following query

    NSMPRD@PRIMARY

    SQL> select
    db_unique_name,open_mode,database_role from v$database;
    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
    ——————————
    ——————– —————-
    nsmprd                        READ WRITE           PRIMARY
    NSMDR@STANDBY
    SQL> select
    db_unique_name,open_mode,database_role from v$database;
    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
    ——————————
    ——————– —————-
    nsmdr                        READ WRITE           PRIMARY

    Ensure
    that both the primary and standby databases are using server parameter files,
    so that the broker can form a healthy relationship between the broker
    properties and parameter values as follows
    NSMPRD@PRIMARY

    SQL> show
    parameter spfile
    NAME                                 TYPE                             VALUE
    ————————————
    ——————————-
    spfile                               string                           /u01/app/oracle/product/11.2.0
                                      .4/db/dbs/spfilensmprd.ora
    NSMDR@STANDBY

    SQL> show
    parameter spfile
    NAME                                 TYPE                             VALUE
    ————————————
    ——————————-
    spfile                               string                                       /u01/app/oracle/product/11.2.0.4/db/dbs/spfilensmdr.ora
    Set
    configuration file parameter locations on both primary and standby

    NSMPRD@PRIMARY
    SQL> ALTER
    SYSTEM SET dg_broker_config_file1 = ‘/u01/oraadmin1/nsmprd/dbs/dr1nsmprd.dat’
    scope=both;
    System altered.
    SQL> ALTER
    SYSTEM SET dg_broker_config_file2 = ‘/u01/oraadmin1/nsmprd/dbs/dr2nsmprd.dat’
    scope=both;
    System altered.
    NSMDR@STANDBY
    SQL> ALTER
    SYSTEM SET dg_broker_config_file1 = ‘/u01/oraadmin1/nsmdr/dbs/dr1nsmdr.dat’
    scope=both;
    System altered.
    SQL> ALTER
    SYSTEM SET dg_broker_config_file2 = ‘/u01/oraadmin1/nsmdr/dbs/dr2nsmdr.dat’
    scope=both;
    System altered.

    Start
    the DMON process on both the primary and standby databases by setting the
    DG_BROKER_START parameter as follows

    NSMPRD@PRIMARY
    SQL> alter
    system set dg_broker_start=TRUE scope=both;
    System altered.
    NSMDR@STANDBY
    SQL> alter
    system set dg_broker_start=TRUE scope=both;
    System altered.

    The configuration files will be created under the
    specified location or in the default directory automatically. The Data Guard
    broker will maintain two copies of its configuration files as follows: Primary
    and standby
    NSMPRD@PRIMARY
    SQL> show
    parameter DG_BROKER_CONFIG_FILE
    NAME                                 TYPE                             VALUE
    ————————————
    ——————————-
    dg_broker_config_file1               string                           
                               /u01/oraadmin1/nsmprd/dbs/dr1 nsmprd.dat
    dg_broker_config_file2           string                   /u01/oraadmin1/nsmprd/dbs/dr2nsmprd.dat
    NSMDR@STANDBY
    SQL> show
    parameter DG_BROKER_CONFIG_FILE
    NAME                                 TYPE                             VALUE
    ————————————
    ——————————-
    dg_broker_config_file1               string                                                    /u01/oraadmin1/nsmdr/dbs/dr1nsmdr.dat
    dg_broker_config_file2               string                                                    /u01/oraadmin1/nsmdr/dbs/dr2nsmdr.dat
    NSMPRD@PRIMARY

    Connect DGMGRL on the primary system and create
    the configuration as follows

    Note: Make sure all the
    required directories are created before proceeding with enable configuration
    $dgmgrl  /
    DGMGRL for Linux:
    Version 11.2.0.4.0 – 64bit Production
    Copyright (c) 2000,
    2009, Oracle. All rights reserved.
    Welcome to
    DGMGRL, type “help” for information.
    Connected.
    DGMGRL>
    DGMGRL> CREATE CONFIGURATION ‘nsmprd’ AS
    PRIMARY DATABASE IS ‘nsmprd’ CONNECT IDENTIFIER IS nsmprd;
    Configuration “nsmprd
    created with primary database “nsmprd”
    DGMGRL>  ADD DATABASE ‘nsmdr’ AS CONNECT IDENTIFIER IS
    ‘nsmdr’ maintained as physical;
    Dataase “nsmdr”
    added

    Enable
    DG broker config
    $dgmgrl  /
    DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
    Copyright (c) 2000, 2009, Oracle. All rights reserved.
    Welcome to DGMGRL, type “help” for information.
    Connected.
    DGMGRL>
    DGMGRL> show
    configuration
    Configuration – nsmprd
      Protection Mode: MaxPerformance
      Databases:
        nsmprd – Primary database
        nsmdr 
    – Physical standby database
    Fast-Start
    Failover: DISABLED
    Configuration
    Status:
    DISABLED
    DGMGRL> enable
    configuration
    Enabled.
    DGMGRL> show
    configuration
    Configuration – nsmprd
      Protection Mode: MaxPerformance
      Databases:
        nsmprd – Primary database
        nsmdr 
    – Physical standby database
    Fast-Start
    Failover: DISABLED
    Configuration
    Status:
    SUCCESS
    Update Listener entries on both primary and standby for
    DG broker
    On Standby :
    LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
    ADR_BASE_LISTENER=/u01/oraadmin1
    SID_LIST_LISTENER
    =
      (SID_LIST=
       
    (SID_DESC=
          (GLOBAL_DBNAME=nsmdr_DGMGRL)
          (ORACLE_HOME=
    /u01/app/oracle/product/11.2.0.4/db)
          (SID_NAME=nsmdr))
       )
    On Primary:
    LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
    ADR_BASE_LISTENER=/u01/oraadmin1
    SID_LIST_LISTENER
    =
      (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=nsmprd_DGMGRL)
          (ORACLE_HOME=
    /u01/app/oracle/product/11.2.0.4/db)
          (SID_NAME=nsmprd))
       )
    $dgmgrl /
    DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
    Copyright (c) 2000, 2009, Oracle. All rights reserved.
    Welcome to DGMGRL, type “help” for information.
    Connected.
    DGMGRL> enable
    configuration
    Enabled.
    DGMGRL> show
    configuration
    Configuration – nsmprd
      Protection Mode: MaxPerformance
      Databases:
        nsmprd – Primary database
        nsmdr 
    – Physical standby database
    Fast-Start
    Failover: DISABLED
    Configuration
    Status:
    SUCCESS
    DGMGRL>
     Conclusion
    In this article we have learnt the steps to the data guard broker.

  • Physical Standby Build using RMAN Active Duplicate

    Overview:
    In this article we explain the process of  build a physical standby database using RMAN active duplicate.


    Standby database is exact copy of production in read only mode with apply. In case if the production goes down for any outage. Standby can failover and make as primary. 

    Environment Details

    Hostname
    Primary server :–Primary

    Standby Server :– Standby
    Operating system
    Linux
    Database Environment
    NSMPRD and NSMDR
    NSMPRD@PRIMARY
    Determine
    if the FORCE LOGGIN is enabled.
    SQL> select
    force_logging from v$database;
    For
    ——
    YES
    If the output of the query is YES, then proceed with the
    next step. If the output of the above query is NO then enable the FORCE LOGGING
    at the database level.
    SQL> alter database force logging;
     Set
    the primary database initialization parameters
    SQL> SHOW
    PARAMETER DB_NAME
    NAME                       TYPE                        VALUE
    —————————————————————-
    db_name                    string                    nsmprd
    SQL> ALTER
    SYSTEM SET log_archive_config=’dg_config=(NSMPRD,NSMDR)’;
       
    System altered
    SQL> show
    parameter log_archive_config
    NAME                    TYPE                        VALUE
    —————————————————————-
    log_archive_config
         string            ‘dg_config=(NSMPRD,NSMDR)
      
    Set the archive loctions on primary 
    SQL> alter
    system set log_archive_dest_1=’LOCATION=/u01/oraarch1/nsmprd
    valid_for=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nsmprd REOPEN=30
    MAX_FAILURE=3 scope=both;
    SYSTEM ALTERED
    SQL> alter
    system set log_archive_dest_2=’service=”nsmdr”‘,’LGWR SYNC AFFIRM
    delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=10
    db_unique_name=”nsmdr” net_timeout=30′,’template=”/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC”
    valid_for=(all_logfiles,primary_role)’ scope=both;
    System altered
    SQL> alter
    system set log_archive_dest_state_3=’ENABLE’;
    System altered
    SQL> alter
    system fal_client= NSMPRD;
    System altered
    SQL> alter
    system fal_server= NSMDR;
    System altered
    SQL> Show
    parameter fal
    NAME                    TYPE                        VALUE
    —————————————————————-
    fal_client           string                         NSMPRD
    fal_server           sting                         NSMDR
    SQL> ALTER
    SYSTEM SET log_archive_dest_state_2=defer;
    System altered
    These parameters are dynamic and will take effect
    immediately. Check the parameter to make sure it points to the correct
    locations as specified.
    SQL> SHOW
    PARAMETER LOG_ARCHIVE_DEST_2
    NAME                    TYPE                        VALUE
    —————————————————————-
    LOG_ARCHIVE_DEST_2    string                 service=”nsmdr”‘,’LGWR
     
                                                 SYNC AFFIRM
    delay=0
                                                 Optional 
                                                 compression=disable
                                                max_failure=0
                                                max_connections=1
                                                reopen=10                                             db_unique_name=”nsmdr”                                            
    net_timeout=30′,’template=”/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC”
    valid_for=(all_logfiles,primary_role)’
    SQL> SHOW
    PARAMETER REMOTE_LOGIN_PASSWORDFILE  (
    this must be set to “EXCLUSIVE” (default))
    NAME                        TYPE                  VALUE
    —————————————————————-
    REMOTE_LOGIN_PASSWORDFILE  string                EXCLUSIVE            
    Create
    standby redo log files on the primary database.
    SQL>column
    member format a50
    SQL>SELECT
    a.group#, a.member, b.bytes/1024/1024 bytes_MB
    FROM v$logfile a,
    v$log b WHERE a.group# = b.group#
    Union select
    a.group#, a.member, b.bytes/1024/1024 bytes_MB
    from v$logfile a,
    v$standby_log b  WHERE a.group# =
    b.group#;
    GROUP#                     MEMBER   
                BYTES_MB
    —————————————————————-
    1     /u01/oradata1/nsmprd/nsmprd_redo01.log    50MB
    2     /u01/oradata1/nsmprd/nsmprd_redo02.log    50MB
    3     /u01/oradata1/nsmprd/nsmprd_redo03.log    50MB
    4     /u01/oradata1/nsmprd/nsmprd_redo04.log    50MB
    SQL>ALTER DATABASE
    ADD STANDBY LOGFILE GROUP 5  ‘/u01/oradata1/nsmprd/nsmprd_sb_redo05.log’
    SIZE 50M;
    Database altered
    SQL> ALTER DATABASE
    ADD STANDBY LOGFILE GROUP 6  ‘/u01/oradata1/nsmprd/nsmprd_sb_redo06.log’
    SIZE 50M;
    Database altered
    SQL> ALTER DATABASE
    ADD STANDBY LOGFILE GROUP 7  ‘/u01/oradata1/nsmprd/nsmprd_sb_redo07.log’
    SIZE 50M;
    Database altered
    SQL> ALTER DATABASE
    ADD STANDBY LOGFILE GROUP 8  ‘/u01/oradata1/nsmprd/nsmprd_sb_redo08.log’
    SIZE 50M;
    SQL> ALTER DATABASE
    ADD STANDBY LOGFILE GROUP 9  ‘/u01/oradata1/nsmprd/nsmprd_sb_redo09.log’
    SIZE 50M;
    Database altered
    SQL>column
    member format a50
    SQL>SELECT
    a.group#, a.member, b.bytes/1024/1024 bytes_MB
    FROM v$logfile a,
    v$log b WHERE a.group# = b.group#
    Union select
    a.group#, a.member, b.bytes/1024/1024 bytes_MB
    from v$logfile a,
    v$standby_log b  WHERE a.group# =
    b.group#;
    GROUP#                     MEMBER   
                BYTES_MB
    —————————————————————-
    1     /u01/oradata1/nsmprd/nsmprd_redo01.log       50MB
    2     /u01/oradata1/nsmprd/nsmprd_redo02.log       50MB
    3     /u01/oradata1/nsmprd/nsmprd_redo03.log       50MB
    4     /u01/oradata1/nsmprd/nsmprd_redo04.log       50MB
    5     /u01/oradata1/nsmprd/nsmprd_sb_redo05.log    50MB
    6     /u01/oradata1/nsmprd/nsmprd_sb_redo06.log    50MB
    7     /u01/oradata1/nsmprd/nsmprd_sb_redo07.log    50MB
    8     /u01/oradata1/nsmprd/nsmprd_sb_redo08.log    50MB
    9     /u01/oradata1/nsmprd/nsmprd_sb_redo09.log    50MB
    Verify the standby redo log file groups were created
    (do this after the creation of standby database)
    SQL> SELECT
    GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
        GROUP#   
    THREAD#  SEQUENCE# ARC STATUS
    ———-
    ———- ———- — ———-
             5          0          0 YES UNASSIGNED
             6          0          0 YES UNASSIGNED
             7          0          0 YES UNASSIGNED
             8          0          0 YES UNASSIGNED
             9          0          0 YES UNASSIGNED
    Configuring TNS for primary and physical standby
    database:
    NSMPRD@PRIMARY
    NSMDR =
      (DESCRIPTION =
        (ADDRESS =
    (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT
    = 1521))
        (CONNECT_DATA =
          (SERVER =
    DEDICATED)
          (SERVICE_NAME
    = nsmdr)
        )
      )
    NSMDR@STANDBY
    NSMPRD =
      (DESCRIPTION =
        (ADDRESS =
    (PROTOCOL = TCP)(HOST = 192.168.1.1 )(PORT = 1521))
        (CONNECT_DATA =
          (SERVER =
    DEDICATED)
          (SERVICE_NAME
    = nsmprd)
        )
      )
    NSMDR =
      (DESCRIPTION =
        (ADDRESS =
    (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER =
    DEDICATED)
          (SERVICE_NAME
    = nsmdr)
        )
      )
    LISTENER_NSMDR=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521))
    Creating the physical standby database:
    NSMDR@STANDBY
    Configure
    listener entry for the standby database in the Listener.ora file on standby
    server.
    $cat listener.ora
    SID_LIST_LISTENER
    =
    (SID_LIST=
    (SID_DESC=
    (GLOBAL_DBNAME=nsmdr)
    (ORACLE_HOME=
    /u01/app/oracle/product/11.2.0.4/db)
    (SID_NAME=nsmdr))
     )
    $tnsping nsmprd
    TNS Ping Utility
    for Linux: Version 11.2.0.4.0 – Production on 05-FEB-2017 09:42:43
    Copyright (c)
    1997, 2013, Oracle.  All rights reserved.
    Used parameter
    files:
    /u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora
    Used TNSNAMES
    adapter to resolve the alias
    Attempting to
    contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT =
    1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))
    OK (0 msec)
    $tnsping nsmdr
    TNS Ping Utility
    for Linux: Version 11.2.0.4.0 – Production on 05-FEB-2017 09:42:51
    Copyright (c)
    1997, 2013, Oracle.  All rights reserved.
    Used parameter
    files:
    /u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora
    Used TNSNAMES
    adapter to resolve the alias
    Attempting to
    contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT =
    1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmdr)))
    OK (0 msec)
    NSMPRD@PRIMARY
    create password file on Primary 

    $orapwd file=orapwnsmprd password=******  force=y entries=20
    Copy
    the password file from the primary database server to the standby server.
    $scp $ORACLE_HOME/dbs/ orapwnsmprd oracle@STANDBY: $ORACLE_HOME/dbs
     NSMDR@STANDBY
    In the
    $ORACLE_HOME/dbs directory of the standby database server create an initialization
    parameter file named initnsmdr.ora containing only one parameter DB_NAME.
    $cd
    $ORACLE_HOME/dbs
    $vi initnsmdr.ora
    DB_NAME= NSMPRD
    :wq (save and
    exit the file)
    On the standby database server go to ORACLE_BASE/admin
    and create a directory as your DB_UNIQUE_NAME.
    $ cd
    $ORACLE_BASE/admin
    $mkdir NSMDR
    On the standby server se the ORACLE_SID for the
    standby database.

    $vi /etc/oratab       –>file and entry as below
    nsmdr:/u01/app/oracle/product/11.2.0.4/db:N
    $ . oraenv
    ORACLE_SID = [oracle]
    ? nsmdr
    The Oracle base
    remains unchanged with value /u01/app/oracle
    $ echo
    $ORACLE_SID=nsmdr
    nsmdr
    Connect
    as sysdba and start the database in NOMOUNT state.
    SQL> STARTUP
    NOMOUNT PFILE=’/u01/app/oracle/product/11.2.0.4/db/dbs/ initnsmdr.ora’;
    Total System
    Global Area 225509376 bytes
    Fixed Size                 2252896 bytes
    Variable
    Size              167773064 bytes
    Redo Buffers                
    5152768 bytes
    SQL> EXIT
    NSMPRD@PRIMARY
    On the
    primary database server set the ORACLE_SID for primary database.
    $ echo
    $ORACLE_SID
    nsmprd
    On the
    primary database server CHECK RMAN CONNECTION and connect to the primary
    database and auxiliary database as sys.
    $ rman
    RMAN>connect
    target sys
    target database
    Password:
    connected to
    target database : NSMPRD (DBID=139468621)
    RMAN>connect
    auxiliary sys@nsmdr  ( Make sure listener
    is running on standby)
    auxiliary database
    Password:
    connected to auxiliary
    database: NSMDR(not mounted)
    RMAN>
     Now
    execute the below script from RMAN on the primary database server. When this
    script finishes you will have a new standby database that was created over the
    network without any interim storage.
    RMAN>duplicate target database for standby from active
    database
    spfile
    SET audit_file_dest=’ /u01/oraadmin1/nsmdr/adump’
    SET DB_UNIQUE_NAME ‘NSMDR’
    SET log_archive_dest_2 ”
    SET log_archive_dest_3 ”
    SET dg_broker_config_file1 ”
    SET dg_broker_config_file2 ”
    SET dg_broker_start ‘FALSE’
    nofilenamecheck;
    After DR restore please set the standby parameters.
    NSMDR@STANDBY
     Update
    standby database parameter.
    SQL> ALTER
    SYSTEM SET log_archive_config=’dg_config=(NSMPRD,NSMDR)’;
    System altered
    SQL> ALTER
    SYSTEM SET log_archive_dest_state_2=defer;
    System altered
    ALTER SYSTEM SET
    log_archive_dest_2=’service=”nsmprd”‘,’LGWR ASYNC NOAFFIRM delay=0
    optional compression=disable max_failure=0 max_connections=1 reopen=300
    db_unique_name=”nsmprd” net_timeout=30′,’template=”/u01/oraarch2/nsmprd_standby/LOG_%r_%s_%t.ARC”
    valid_for=(all_logfiles,primary_role)’ scope=both;
    System altered
    SQL> alter
    system set fal_client= NSMDR;
    System altered
    SQL> alter
    system set fal_server= NSMPRD;
    System altered
    Enable
    the log_archive_dest_state_2 on primary
    SQL> ALTER SYSTEM SET
    log_archive_dest_state_2=ENABLE;
    System altered
    Perform
    a log switch on the primary database and redo will start being sent to the
    standby.
    SQL> ALTER
    SYSTEM SWITCH LOGFILE;
    System altered
    SQL>/
    System altered
    On the
    standby database set the database SID and start the manage recovery process.
    SQL> ALTER
    DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
    Database altered
    Verify that the standby database is performing correctly:
    On the primary database switch the logfile.
    SQL> ALTER
    SYSTEM SWITCH LOGFILE;
    System altered
    Check the log sequence on the primary database.
    SQL>  SELECT THREAD#, MAX(SEQUENCE#) FROM
    V$LOG_HISTORY GROUP BY THREAD#;
    THREAD#   MAX(SEQUENCE#)
    ————————
         1   
    199
      On the standby database execute the above SQL
    statement to check the log sequence.
    SQL>  SELECT THREAD#, MAX(SEQUENCE#) FROM
    V$LOG_HISTORY GROUP BY THREAD#;
    THREAD#   MAX(SEQUENCE#)
    ————————
         1   
    198
    SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS
    from v$managed_standby where process=’MRP0′;
    PROCESS                        THREAD#  SEQUENCE# STATUS
    —————————
    ———- ———- ————————————
    MRP0                                 1    199     WAIT_FOR_LOG

    Conclusion

    In this article we have learnt the steps to build a physical standby database using RMAN active duplicate.