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
