Creating a standby database in the same host and disk group from rman backup location

Today I come up with a requirement to create a standy database within the same host where my primary database is up and running . If you do a RMAN manual restore, there might be a chance for overwriting the source database.

 

So I relied on RMAN duplicate – In rman duplicate we can duplicate a standby database either by connecting to primary  database (Active standby database duplication ) or by taking rman backup from the source. 

DIREC –>  primary database

DRREC –>  standby database to create 

Here I took RMAN backup from the primary  

[oracle@exdb01]:[DIREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 14 20:52:50 2016

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

connected to target database: DIREC (DBID=1030724485)

RMAN>run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

sql "alter system archive log current";

BACKUP AS COMPRESSED BACKUPSET FULL TAG 'DIREC_FULL' DATABASE format '/t4_nfs_2/DIREC_TEMP/Backup_%d_%T_%s' include current controlfile for standby;

sql "alter system archive log current";

backup archivelog all format '/t4_nfs_2/DIREC_TEMP/ARCHIVE_%d_%T_%s';

}

Create a init file for new stanby  

[oracle@exdb01]:[DRREC1] $ cat initDRREC1.ora
DRREC1.__data_transfer_cache_size=0
DRREC2.__db_cache_size=956301312
DRREC1.__db_cache_size=1207959552
DRREC2.__java_pool_size=67108864
DRREC1.__java_pool_size=67108864
DRREC2.__large_pool_size=83886080
DRREC1.__large_pool_size=520093696
DRREC2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DRREC1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DRREC2.__pga_aggregate_target=1493172224
DRREC1.__pga_aggregate_target=1493172224
DRREC2.__sga_target=2801795072
DRREC1.__sga_target=2801795072
DRREC2.__shared_io_pool_size=0
DRREC1.__shared_io_pool_size=117440512
DRREC2.__shared_pool_size=1660944384
DRREC1.__shared_pool_size=872415232
DRREC2.__streams_pool_size=0
DRREC1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DRREC/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='12.1.0.2'

*.control_files='+ORADATA/DRREC/control01.ctl','+ORAFRA/DRREC/control02.ctl'
*.db_block_size=8192

*.db_create_file_dest='+ORADATA'
*.db_create_online_log_dest_1='+ORADATA'
*.db_create_online_log_dest_2='+ORAFRA'

*.db_name='DIREC'
*.db_unique_name='DRREC'

*.db_recovery_file_dest='+ORAFRA'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DRRECXDB)'
DRREC1.instance_number=1
DRREC2.instance_number=2
*.memory_target=4G
*.nls_language='AMERICAN'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=500
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
DRREC1.thread=1
DRREC2.thread=2
DRREC1.undo_tablespace='UNDOTBS1'
DRREC2.undo_tablespace='UNDOTBS2'
[oracle@exdb01]:[DRREC1] $

Start up standby database in nomount stage 

[oracle@exdb01]:[DRREC1] $ . oraenv
ORACLE_SID = [DRREC1] ? DRREC1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@exdb01]:[DRREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 18:31:53 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
tConnected to an idle instance.
SQL> startup nomount pfile='initDRREC1.ora';
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size                  5291928 bytes
Variable Size            2717909096 bytes
Database Buffers         1560281088 bytes
Redo Buffers               11485184 bytes
SQL>
SQL> show parameter db_u
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_ultra_safe                        string      OFF
db_unique_name                       string      DRREC
db_unrecoverable_scn_tracking        boolean     TRUE
SQL> show parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DIREC
SQL> show parameter creeate
SQL> show parameter create
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +ORADATA
db_create_online_log_dest_1          string      +ORADATA
db_create_online_log_dest_2          string      +ORAFRA
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@exdb01]:[DRREC1] $

Connect to auxiliary instance and execute rman duplicate

[oracle@exdb01]:[DRREC1] $ rman auxiliary /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 14 18:33:01 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to auxiliary database: DIREC (not mounted)
RMAN> run
{
 allocate auxiliary channel stby1 type disk;
 allocate auxiliary channel stby2 type disk;
 allocate auxiliary channel stby3 type disk;
 DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER BACKUP LOCATION '/t4_nfs_2/DIREC_TEMP';
}2> 3> 4> 5> 6> 7>
allocated channel: stby1
channel stby1: SID=497 device type=DISK
allocated channel: stby2
channel stby2: SID=530 device type=DISK
allocated channel: stby3
channel stby3: SID=563 device type=DISK
Starting Duplicate Db at 14-JUN-16
contents of Memory Script:
{
   restore clone standby controlfile from  '/t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_19';
}
executing Memory Script
Starting restore at 14-JUN-16
channel stby2: skipped, AUTOBACKUP already found
channel stby3: skipped, AUTOBACKUP already found
channel stby1: restoring control file
channel stby1: restore complete, elapsed time: 00:00:08
output file name=+ORADATA/DRREC/control01.ctl
output file name=+ORAFRA/DRREC/control02.ctl
Finished restore at 14-JUN-16
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
   set until scn  130771368;
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   restore
   clone database
   ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed tempfile 1 to +ORADATA in control file
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 14-JUN-16
channel stby1: starting datafile backup set restore
channel stby1: specifying datafile(s) to restore from backup set
channel stby1: restoring datafile 00001 to +ORADATA
channel stby1: restoring datafile 00005 to +ORADATA
channel stby1: reading from backup piece /t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_18
channel stby2: starting datafile backup set restore
channel stby2: specifying datafile(s) to restore from backup set
channel stby2: restoring datafile 00002 to +ORADATA
channel stby2: restoring datafile 00006 to +ORADATA
channel stby2: reading from backup piece /t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_17
channel stby3: starting datafile backup set restore
channel stby3: specifying datafile(s) to restore from backup set
channel stby3: restoring datafile 00003 to +ORADATA
channel stby3: restoring datafile 00008 to +ORADATA
channel stby3: reading from backup piece /t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_16
channel stby3: piece handle=/t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_16 tag=DIREC_1406
channel stby3: restored backup piece 1
channel stby3: restore complete, elapsed time: 00:01:05
channel stby3: starting datafile backup set restore
channel stby3: specifying datafile(s) to restore from backup set
channel stby3: restoring datafile 00004 to +ORADATA
channel stby3: restoring datafile 00007 to +ORADATA
channel stby3: reading from backup piece /t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_15
channel stby3: piece handle=/t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_15 tag=DIREC_1406
channel stby3: restored backup piece 1
channel stby3: restore complete, elapsed time: 00:00:45
channel stby2: piece handle=/t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_17 tag=DIREC_1406
channel stby2: restored backup piece 1
channel stby2: restore complete, elapsed time: 00:02:00
channel stby1: piece handle=/t4_nfs_2/DIREC_TEMP/Backup_DIREC_20160614_18 tag=DIREC_1406
channel stby1: restored backup piece 1
channel stby1: restore complete, elapsed time: 00:02:10
Finished restore at 14-JUN-16
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=914524621 file name=+ORADATA/DRREC/DATAFILE/system.2663.914524493
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=914524621 file name=+ORADATA/DRREC/DATAFILE/sysaux.2668.914524493
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=914524621 file name=+ORADATA/DRREC/DATAFILE/undotbs1.2665.914524491
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=914524621 file name=+ORADATA/DRREC/DATAFILE/users.2670.914524557
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=914524621 file name=+ORADATA/DRREC/DATAFILE/orinon_ts.2667.914524493
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=914524621 file name=+ORADATA/DRREC/DATAFILE/oraion_ts.2669.914524493
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=914524622 file name=+ORADATA/DRREC/DATAFILE/tbs_test.2671.914524557
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=914524622 file name=+ORADATA/DRREC/DATAFILE/orion_data.2664.914524493
contents of Memory Script:
{
   set until scn  130771368;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 14-JUN-16
starting media recovery
channel stby1: starting archived log restore to default destination
channel stby1: restoring archived log
archived log thread=1 sequence=223
channel stby1: reading from backup piece /t4_nfs_2/DIREC_TEMP/ARCHIVE_DIREC_20160614_22
channel stby2: starting archived log restore to default destination
channel stby2: restoring archived log
archived log thread=1 sequence=224
channel stby2: reading from backup piece /t4_nfs_2/DIREC_TEMP/ARCHIVE_DIREC_20160614_23
channel stby1: piece handle=/t4_nfs_2/DIREC_TEMP/ARCHIVE_DIREC_20160614_22 tag=TAG20160614T155349
channel stby1: restored backup piece 1
channel stby1: restore complete, elapsed time: 00:00:01
archived log file name=+ORAFRA/DRREC/ARCHIVELOG/2016_06_14/thread_1_seq_223.702.914524623 thread=1 sequence=223
channel clone_default: deleting archived log(s)
archived log file name=+ORAFRA/DRREC/ARCHIVELOG/2016_06_14/thread_1_seq_223.702.914524623 RECID=1 STAMP=914524623
channel stby2: piece handle=/t4_nfs_2/DIREC_TEMP/ARCHIVE_DIREC_20160614_23 tag=TAG20160614T155349
channel stby2: restored backup piece 1
channel stby2: restore complete, elapsed time: 00:00:02
archived log file name=+ORAFRA/DRREC/ARCHIVELOG/2016_06_14/thread_1_seq_224.5048.914524623 thread=1 sequence=224
channel clone_default: deleting archived log(s)
archived log file name=+ORAFRA/DRREC/ARCHIVELOG/2016_06_14/thread_1_seq_224.5048.914524623 RECID=2 STAMP=914524623
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JUN-16
Finished Duplicate Db at 14-JUN-16
released channel: stby1
released channel: stby2
released channel: stby3
RMAN>

Here we go .We successfully restored our standby database and all  files are created according to the OMF location that we specified within the init file. we can verify it .

SQL> select name from v$datafile ;

NAME
--------------------------------------------------------------------------------
+ORADATA/DRREC/DATAFILE/system.2663.914524493
+ORADATA/DRREC/DATAFILE/sysaux.2668.914524493
+ORADATA/DRREC/DATAFILE/undotbs1.2665.914524491
+ORADATA/DRREC/DATAFILE/users.2670.914524557
+ORADATA/DRREC/DATAFILE/orinon_ts.2667.914524493
+ORADATA/DRREC/DATAFILE/oraion_ts.2669.914524493
+ORADATA/DRREC/DATAFILE/tbs_test.2671.914524557
+ORADATA/DRREC/DATAFILE/orion_data.2664.914524493

SQL> select member from v$logfile ;

MEMBER
--------------------------------------------------------------------------------
+ORADATA/DRREC/ONLINELOG/group_3.2674.914524625
+ORAFRA/DRREC/ONLINELOG/group_3.2475.914524627
+ORADATA/DRREC/ONLINELOG/group_2.2673.914524625
+ORAFRA/DRREC/ONLINELOG/group_2.702.914524625
+ORADATA/DRREC/ONLINELOG/group_1.2672.914524625
+ORAFRA/DRREC/ONLINELOG/group_1.5048.914524625
+ORADATA/DRREC/ONLINELOG/group_7.2675.914524627
+ORAFRA/DRREC/ONLINELOG/group_7.4010.914524627
+ORADATA/DRREC/ONLINELOG/group_8.2676.914524627
+ORAFRA/DRREC/ONLINELOG/group_8.7990.914524627
+ORADATA/DRREC/ONLINELOG/group_9.2677.914524627
+ORAFRA/DRREC/ONLINELOG/group_9.7813.914524629
+ORADATA/DRREC/ONLINELOG/group_10.2678.914524629
+ORAFRA/DRREC/ONLINELOG/group_10.7223.914524629

SQL> select name from v$controlfile ;

NAME
--------------------------------------------------------------------------------
+ORADATA/DRREC/control01.ctl
+ORAFRA/DRREC/control02.ctl