Good Contents Are Everywhere, But Here, We Deliver The Best of The Best.Please Hold on!
POSTS PORTFOLIO SEARCH SHOP
PORTFOLIO
SEARCH
SHOP
  • Your Cart Is Empty!
  • +966 56 585 6823
  • +971 52 849 3496
  • +1 512 808 5399
  • +91 965 246 5533
  • +61 42 076 8137

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

By on

February 12, 2017

- Uncategorized

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

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

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

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

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


[oracle@Nsmsrc01 dbs]$ hostname

Nsmsrc01
[oracle@Nsmsrc01 dbs]$ export ORACLE_SID=prod
  2.    Connect to RMAN.


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

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Apr 9 10:47:41 2015

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

connected to target database: PROD (DBID=284539893)

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


RMAN> show all;

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



RMAN> backup database plus archivelog;


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

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

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

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

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


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

File created.

[oracle@Nsmsrc01 rman_backup]$ scp initprod.ora  oracle@172.16.110.16:/data1/oracle11g/product/dbs/
oracle@172.16.110.16’s password:
initprod.ora                                                                                                                                                       100% 1169     1.1KB/s   00:00
Before start scp/ftp to destination server create directory structure to accommodate rman backup on destination server.


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

5.    Copy rman backup from target to destination server.



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

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



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


[oracle@Nsmtrg rman_backup]$ export ORACLE_SID=dup
[oracle@Nsmtrg dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 16:24:32 2015

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

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

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size                  2212696 bytes
Variable Size             352324776 bytes
Database Buffers          788529152 bytes
Redo Buffers                9383936 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
8.    Connect to rman


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

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Apr 9 16:27:47 2015

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

connected to auxiliary database: DUP (not mounted)

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


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


Starting Duplicate Db at 09-APR-15

contents of Memory Script:
{
   sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

contents of Memory Script:
{
   sql clone “alter system set  db_name =
 ”PROD” comment=
 ”Modified by RMAN duplicate” scope=spfile”;
   sql clone “alter system set  db_unique_name =
 ”DUP” comment=
 ”Modified by RMAN duplicate” scope=spfile”;
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  ‘/backup/rman_backup/controlfile_c-284539893-20150409-02’;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

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

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data2/prod/control01.ctl
output file name=/data2/prod/control02.ctl
Finished restore at 09-APR-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
   set until scn  4972796;
   set newname for datafile  1 to
 “/data2/prod/system01.dbf”;
   set newname for datafile  2 to
 “/data2/prod/sysaux01.dbf”;
   set newname for datafile  3 to
 “/data2/prod/undotbs01.dbf”;
   set newname for datafile  4 to
 “/data2/prod/users01.dbf”;
   set newname for datafile  5 to
 “/data2/prod/example01.dbf”;
   set newname for datafile  6 to
 “/data2/prod/data01.dbf”;
   set newname for datafile  7 to
 “/data2/prod/test01.dbf”;
   set newname for datafile  8 to
 “/data2/prod/tools01.dbf”;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-APR-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data2/prod/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data2/prod/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data2/prod/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data2/prod/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data2/prod/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /data2/prod/data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /data2/prod/test01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /data2/prod/tools01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2nq3v9q2_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2nq3v9q2_1_1.bkp tag=TAG20150409T162018
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 09-APR-15

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=876589349 file name=/data2/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=876589350 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=876589350 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=876589350 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=876589351 file name=/data2/prod/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=876589351 file name=/data2/prod/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=876589351 file name=/data2/prod/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=876589352 file name=/data2/prod/tools01.dbf

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

executing command: SET until clause

Starting recover at 09-APR-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=143
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2oq3v9rr_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2oq3v9rr_1_1.bkp tag=TAG20150409T162115
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/archive/1_143_872701561.dbf thread=1 sequence=143
channel clone_default: deleting archived log(s)
archived log file name=/backup/archive/1_143_872701561.dbf RECID=1 STAMP=876589358
media recovery complete, elapsed time: 00:00:04
Finished recover at 09-APR-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone “alter system set  db_name =
 ”DUP” comment=
 ”Reset to original value by RMAN” scope=spfile”;
   sql clone “alter system reset  db_unique_name scope=spfile”;
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DUP” RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( ‘/data2/prod/redo01.log’ ) SIZE 50 M  REUSE,
  GROUP  2 ( ‘/data2/prod/redo02.log’ ) SIZE 50 M  REUSE,
  GROUP  3 ( ‘/data2/prod/redo03.log’ ) SIZE 50 M  REUSE
 DATAFILE
  ‘/data2/prod/system01.dbf’
 CHARACTER SET WE8MSWIN1252


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

executing command: SET NEWNAME

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

cataloged datafile copy
datafile copy file name=/data2/prod/sysaux01.dbf RECID=1 STAMP=876589383
cataloged datafile copy
datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=876589383
cataloged datafile copy
datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=876589383
cataloged datafile copy
datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=876589383
cataloged datafile copy
datafile copy file name=/data2/prod/data01.dbf RECID=5 STAMP=876589384
cataloged datafile copy
datafile copy file name=/data2/prod/test01.dbf RECID=6 STAMP=876589384
cataloged datafile copy
datafile copy file name=/data2/prod/tools01.dbf RECID=7 STAMP=876589385

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

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

database opened
Finished Duplicate Db at 09-APR-15

RMAN>
 

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

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

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

By on

February 12, 2017

- Uncategorized

Overview
Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
In this scenario we have Readonly tablespace at target(Source) database. and same directory structure with different database name.



Target (source) details:

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

 Destination details:

Database Name
dup
Hostname
Nsm-stg
Ip Address
172.16.110.16
OS
Linux
Version
x86_64
Datafile Location
/data2/prod
Backup Location
/backup/rman_backup
  
 Pre-requisites:
  • Password file from target database.
  • Sqlnet.ora should have correct parameters.
  • Target database should be running through spfile.
 Steps on target (source) server:
  • export ORACLE SID
[oracle@Nsm-prod dbs]$ hostname

Nsm-prod

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

  •   Connect to RMAN.


[oracle@Nsm-prod dbs]$ rman target sys/oracle



Recovery Manager: Release 11.2.0.1.0 – Production on Thu Apr 9 10:47:41 2015



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



connected to target database: PROD (DBID=284539893)



RMAN>

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

RMAN> show all;

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



RMAN> backup database plus archivelog;





Starting backup at 09-APR-15

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=85 RECID=81 STAMP=875052425

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

input archived log thread=1 sequence=87 RECID=83 STAMP=875118622

input archived log thread=1 sequence=88 RECID=84 STAMP=875138912

input archived log thread=1 sequence=89 RECID=85 STAMP=875169392

input archived log thread=1 sequence=90 RECID=86 STAMP=875202890

input archived log thread=1 sequence=91 RECID=87 STAMP=875225400

input archived log thread=1 sequence=92 RECID=88 STAMP=875253619

input archived log thread=1 sequence=93 RECID=89 STAMP=875287426

input archived log thread=1 sequence=94 RECID=90 STAMP=875311887

input archived log thread=1 sequence=95 RECID=91 STAMP=875338210

input archived log thread=1 sequence=96 RECID=92 STAMP=875371700

input archived log thread=1 sequence=97 RECID=93 STAMP=875398375

input archived log thread=1 sequence=98 RECID=94 STAMP=875422534

input archived log thread=1 sequence=99 RECID=95 STAMP=875455916

input archived log thread=1 sequence=100 RECID=96 STAMP=875489442

input archived log thread=1 sequence=101 RECID=97 STAMP=875511892

input archived log thread=1 sequence=102 RECID=98 STAMP=875532973

input archived log thread=1 sequence=103 RECID=99 STAMP=875566589

input archived log thread=1 sequence=104 RECID=100 STAMP=875598381

input archived log thread=1 sequence=105 RECID=101 STAMP=875617253

input archived log thread=1 sequence=106 RECID=102 STAMP=875650222

input archived log thread=1 sequence=107 RECID=103 STAMP=875683804

input archived log thread=1 sequence=108 RECID=104 STAMP=875692844

input archived log thread=1 sequence=109 RECID=105 STAMP=875726521

input archived log thread=1 sequence=110 RECID=106 STAMP=875759423

input archived log thread=1 sequence=111 RECID=107 STAMP=875771364

input archived log thread=1 sequence=112 RECID=108 STAMP=875804400

input archived log thread=1 sequence=113 RECID=109 STAMP=875809288

input archived log thread=1 sequence=114 RECID=110 STAMP=875842252

input archived log thread=1 sequence=115 RECID=111 STAMP=875857844

input archived log thread=1 sequence=116 RECID=112 STAMP=875883160

input archived log thread=1 sequence=117 RECID=113 STAMP=875939543

input archived log thread=1 sequence=118 RECID=114 STAMP=875955632

input archived log thread=1 sequence=119 RECID=115 STAMP=875960595

input archived log thread=1 sequence=120 RECID=116 STAMP=876018651

input archived log thread=1 sequence=121 RECID=117 STAMP=876036137

input archived log thread=1 sequence=122 RECID=118 STAMP=876094207

input archived log thread=1 sequence=123 RECID=119 STAMP=876117455

input archived log thread=1 sequence=124 RECID=120 STAMP=876173429

input archived log thread=1 sequence=125 RECID=121 STAMP=876203937

input archived log thread=1 sequence=126 RECID=122 STAMP=876259857

input archived log thread=1 sequence=127 RECID=123 STAMP=876288622

input archived log thread=1 sequence=128 RECID=124 STAMP=876308432

input archived log thread=1 sequence=129 RECID=125 STAMP=876364250

input archived log thread=1 sequence=130 RECID=126 STAMP=876378605

input archived log thread=1 sequence=131 RECID=127 STAMP=876398550

input archived log thread=1 sequence=132 RECID=128 STAMP=876399532

input archived log thread=1 sequence=133 RECID=129 STAMP=876419413

input archived log thread=1 sequence=134 RECID=130 STAMP=876421079

input archived log thread=1 sequence=135 RECID=131 STAMP=876461598

input archived log thread=1 sequence=136 RECID=132 STAMP=876504610

input archived log thread=1 sequence=137 RECID=133 STAMP=876548085

input archived log thread=1 sequence=138 RECID=134 STAMP=876567056

input archived log thread=1 sequence=139 RECID=135 STAMP=876567230

input archived log thread=1 sequence=140 RECID=136 STAMP=876583581

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

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

piece handle=/backup/rman_backup/db_arch_2iq3v6ku_1_1.bkp tag=TAG20150409T152621 comment=NONE

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

Finished backup at 09-APR-15



Starting backup at 09-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/data2/prod/system01.dbf

input datafile file number=00002 name=/data2/prod/sysaux01.dbf

input datafile file number=00005 name=/data2/prod/example01.dbf

input datafile file number=00003 name=/data2/prod/undotbs01.dbf

input datafile file number=00006 name=/data2/prod/data01.dbf

input datafile file number=00004 name=/data2/prod/users01.dbf

input datafile file number=00007 name=/data2/prod/test01.dbf

input datafile file number=00008 name=/data2/prod/tools01.dbf

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

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

piece handle=/backup/rman_backup/db_arch_2jq3v6o8_1_1.bkp tag=TAG20150409T152807 comment=NONE

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

Finished backup at 09-APR-15



Starting backup at 09-APR-15

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=141 RECID=137 STAMP=876583754

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

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

piece handle=/backup/rman_backup/db_arch_2kq3v6qa_1_1.bkp tag=TAG20150409T152914 comment=NONE

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

Finished backup at 09-APR-15



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

piece handle=/backup/rman_backup/controlfile_c-284539893-20150409-01 comment=NONE

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



RMAN>

  •   Create pfile and copy to destination server.

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

File created.



[oracle@Nsm-prod rman_backup]$ scp initprod.ora  oracle@172.16.110.16:/data1/oracle11g/product/dbs/

oracle@172.16.110.16’s password:

initprod.ora                                                                                                                                                       100% 1169     1.1KB/s   00:00
  Before start scp/ftp to destination server create directory structure to accommodate rman backup on destination server.


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

  • Copy rman backup from target to destination server.

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

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

  •   Start the new instance in nomount.

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

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 12:14:35 2015

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

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

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size                  2212696 bytes
Variable Size             352324776 bytes
Database Buffers          788529152 bytes
Redo Buffers                9383936 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

  •   Connect to rman.

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

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

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

connected to auxiliary database: DUP (not mounted)

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


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

Starting Duplicate Db at 09-APR-15

contents of Memory Script:
{
   sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

contents of Memory Script:
{
   sql clone “alter system set  db_name =
 ”PROD” comment=
 ”Modified by RMAN duplicate” scope=spfile”;
   sql clone “alter system set  db_unique_name =
 ”DUP” comment=
 ”Modified by RMAN duplicate” scope=spfile”;
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  ‘/backup/rman_backup/controlfile_c-284539893-20150409-01’;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

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

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/data2/prod/control01.ctl
output file name=/data2/prod/control02.ctl
Finished restore at 09-APR-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
   set until scn  4970799;
   set newname for datafile  1 to
 “/data2/prod/system01.dbf”;
   set newname for datafile  2 to
 “/data2/prod/sysaux01.dbf”;
   set newname for datafile  3 to
 “/data2/prod/undotbs01.dbf”;
   set newname for datafile  4 to
 “/data2/prod/users01.dbf”;
   set newname for datafile  5 to
 “/data2/prod/example01.dbf”;
   set newname for datafile  6 to
 “/data2/prod/data01.dbf”;
   set newname for datafile  7 to
 “/data2/prod/test01.dbf”;
   set newname for datafile  8 to
 “/data2/prod/tools01.dbf”;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-APR-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data2/prod/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data2/prod/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data2/prod/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data2/prod/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data2/prod/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /data2/prod/data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /data2/prod/test01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /data2/prod/tools01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2jq3v6o8_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2jq3v6o8_1_1.bkp tag=TAG20150409T152807
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 09-APR-15

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=876585776 file name=/data2/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=876585777 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=876585777 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=876585777 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=876585778 file name=/data2/prod/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=876585778 file name=/data2/prod/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=876585778 file name=/data2/prod/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=876585779 file name=/data2/prod/tools01.dbf

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

executing command: SET until clause

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

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=141
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_2kq3v6qa_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2kq3v6qa_1_1.bkp tag=TAG20150409T152914
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/archive/1_141_872701561.dbf thread=1 sequence=141
channel clone_default: deleting archived log(s)
archived log file name=/backup/archive/1_141_872701561.dbf RECID=1 STAMP=876585786
media recovery complete, elapsed time: 00:00:04
Finished recover at 09-APR-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone “alter system set  db_name =
 ”DUP” comment=
 ”Reset to original value by RMAN” scope=spfile”;
   sql clone “alter system reset  db_unique_name scope=spfile”;
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DUP” RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( ‘/data2/prod/redo01.log’ ) SIZE 50 M  REUSE,
  GROUP  2 ( ‘/data2/prod/redo02.log’ ) SIZE 50 M  REUSE,
  GROUP  3 ( ‘/data2/prod/redo03.log’ ) SIZE 50 M  REUSE
 DATAFILE
  ‘/data2/prod/system01.dbf’
 CHARACTER SET WE8MSWIN1252


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

executing command: SET NEWNAME

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

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

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

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

database opened

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

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

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

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

sql statement: alter tablespace  TOOLS online

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


Starting recover at 09-APR-15

using channel ORA_AUX_DISK_1

datafile 6 not processed because file is read-only

datafile 8 not processed because file is read-only



starting media recovery



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



contents of Memory Script:

{

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

 “/data2/prod/tools01.dbf”;

   switch clone datafile  6 to datafilecopy

 “/data2/prod/data01.dbf”;

   switch clone datafile  8 to datafilecopy

 “/data2/prod/tools01.dbf”;

   #online the readonly tablespace

   sql clone “alter tablespace  TOOLS online”;

   #online the readonly tablespace

   sql clone “alter tablespace  DATA online”;

}

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


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


SQL> select name,open_mode from v$database;

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

SQL> select tablespace_name,status from dba_tablespaces;

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

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


SQL> alter tablespace DATA  Read write;

Tablespace altered.

SQL> alter tablespace TOOLS  Read write;

Tablespace altered.


SQL> select tablespace_name,status from dba_tablespaces;

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

9 rows selected.
 

Conclusion
In the preceding scenario based article, we have learned that backup based duplication using Rman utility with different database name and same directory structure,
with Read only tablespace, if we have tablespaces are in read only mode same thing would replicate at target side.

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

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

By on

February 12, 2017

- Uncategorized

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
1

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

By on

February 12, 2017

- Uncategorized



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
0

De-Register Database Vault on RAC DB

By on

February 11, 2017

- Uncategorized

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.
0

Update Existing Database Vault settings.

By on

February 11, 2017

- Uncategorized

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.

0

Enable Database Vault on RAC Database

By on

February 11, 2017

- Uncategorized

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



1

RAC DB creation on Exadata Machine using DBCA.

By on

February 11, 2017

- Uncategorized

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
0

Data guard Broker setup

By on

February 11, 2017

- Uncategorized

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.
0

Physical Standby Build using RMAN Active Duplicate

By on

February 11, 2017

- Uncategorized

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.


0
PREVIOUS POSTSPage 15 of 18NEXT POSTS

ABOUT US

Netsoftmate is an Oracle Gold Partner and an expert service provider for Oracle Engineered Systems, Databases and Cyber Security. We are headquartered in India with offices spread across North America, Middle-east and SEA.

Know More

Privacy Policy | Terms & Conditions

 

CONTACT US

Middle East
KSA Address: Prince Abdulaziz Ibn Musaid Ibn Jalawi St, Riyadh, Saudi Arabia
Phone: +966 56 585 6823

United States
Address: 228 Bagatelle Road, Melville, NY 11747
Phone: +1 512 808 5399

India
Address: Hyderabad, Telangana-500052
Phone: +91 965 246 5533
Phone: +91 988 534 4596
 
 

[contact-form-7 id=”4973″ title=”Lead”]