Impact of doing a flashback on data guard environment

In very rare case, We may end up in situation like some logical mistake has been happened to our production database and as a last resort to recover the data we may need to do a flashback operation at primary database. When we do a flashback operation on primary database subsequently redo apply will get stopped out on all standby databases.In this demonstration I will show how to bring our standby back in sync with our production database. 

Here SRCDB is my primary database and TRGDB is my standby database. Flashback should be enabled on both primary and standby database.

C:\Users\Mahi>set ORACLE_SID=SRCDB
C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:38:05 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>select name ,open_mode, database_role,flashback_on from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
--------- -------------------- ---------------- ------------------
SRCDB     READ WRITE           PRIMARY          YES


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
16-FEB-18 07.38.37.868000 PM +05:30


SQL> select count(*) from hr.JOBS;

  COUNT(*)
----------
        19

C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:41:04 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name ,open_mode, database_role,flashback_on from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
--------- -------------------- ---------------- ------------------
SRCDB     READ ONLY WITH APPLY PHYSICAL STANDBY YES


SQL> select count(*) from hr.JOBS;

  COUNT(*)
----------
        19
SQL>

Let's do some logical mistake-

C:\Users\Mahi>sqlplus sys@srcdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:45:20 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> drop user hr cascade ;

User dropped.

SQL>

As I have configured "Real Time Apply" the logical mistake that I have done on my primary database should have applied to my standby database as well.I will verify that on my standby.

C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 19:46:18 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from hr.JOBS;
select count(*) from hr.JOBS
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING              36       4096       1522
ARCH      CONNECTED             0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      APPLYING_LOG         37       3982     102400
RFS       IDLE                 37       3982          1

9 rows selected.

SQL>

Now shutdown the primary database and do the flashback operation to recover the dropped schema. 
C:\Users\Mahi>sqlplus sys@srcdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:31:23 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

C:\Users\Mahi>set ORACLE_SID=SRCDB
C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:38:20 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  534462464 bytes
Fixed Size                  2404704 bytes
Variable Size             440405664 bytes
Database Buffers           83886080 bytes
Redo Buffers                7766016 bytes
Database mounted.
SQL>
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('16-02-18 19:38:37', 'DD-MM-YY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select count(*) from hr.JOBS;

  COUNT(*)
----------
        19

SQL>

Once you do the flashback operation on the primary database, subsequently all standby databases will no longer able to apply the redo because my primary database has gone through a new incarnation.I Verified MRP process is no longer running in standby.

C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:48:51 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               1          1          1
ARCH      CLOSING              37      10240       1726
RFS       IDLE                  2       1218          1
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

8 rows selected.

SQL>

Output from standby alertlog file-

RFS[6]: New Archival REDO Branch(resetlogs_id): 968273102  Prior: 967810818
RFS[6]: Archival Activation ID: 0x23e32516 Current: 0x23dcca7c
RFS[6]: Effect of primary database OPEN RESETLOGS
RFS[6]: Managed Standby Recovery process is active
2018-02-16 20:46:25.742000 +05:30
Setting recovery target incarnation to 3
Archived Log entry 27 added for thread 1 sequence 37 ID 0x23dcca7c dest 1:
2018-02-16 20:46:27.820000 +05:30
Archived Log entry 28 added for thread 1 sequence 1 ID 0x23e32516 dest 1:
Media Recovery Waiting for thread 1 sequence 38
MRP0: Incarnation has changed! Retry recovery...
Errors in file E:\APP\MAHI\diag\rdbms\trgdb\trgdb\trace\trgdb_pr00_7600.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2018-02-16 20:46:30.078000 +05:30
 Started logmerger process
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 2718127) is orphaned on incarnation#=2
MRP0: Detected orphaned datafiles!
2018-02-16 20:46:31.113000 +05:30
Recovery will possibly be retried after flashback...
Errors in file E:\APP\MAHI\diag\rdbms\trgdb\trgdb\trace\trgdb_pr00_7416.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'E:\ORADATA\TRGDB\DATAFILE\O1_MF_SYSTEM_F80M3JN4_.DBF'
Managed Standby Recovery not using Real Time Apply
2018-02-16 20:46:32.375000 +05:30
Recovery Slave PR00 previously exited with exception 19909

Now flashback the standby database to the present time of the primary database. 

C:\Users\Mahi>sqlplus sys@srcdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 20:59:52 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
          2709581

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\Mahi>sqlplus sys@trgdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 16 21:00:22 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  flashback database to scn 2709581;

Flashback complete.

SQL>

Note:- It is better to use a SCN substracted by 1 or 2 from the resetlogs_change# that we noted from the primary database.So we can
make sure that we get the Standby close before the present time of the Primary.

Output from standby alertlog file -

flashback database to scn 2709581
Stopping Emon pool
2018-02-16 21:01:16.646000 +05:30
Waiting for shared server 'S000' to die
All dispatchers and shared servers shutdown
2018-02-16 21:01:17.740000 +05:30
CLOSE: killing server sessions.
2018-02-16 21:01:34.966000 +05:30
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
2018-02-16 21:01:36.006000 +05:30
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active process 2644 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
ARC1: Waiting for instance close to complete
SMON: disabling cache recovery
2018-02-16 21:01:39.226000 +05:30
ARC1: Wait for instance close completed
2018-02-16 21:01:41.779000 +05:30
Flashback Restore Start
2018-02-16 21:01:46.660000 +05:30
Flashback Restore Complete
Flashback Media Recovery Start
2018-02-16 21:01:47.718000 +05:30
 Started logmerger process
2018-02-16 21:01:48.983000 +05:30
Parallel Media Recovery started with 4 slaves
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 2709580
2018-02-16 21:01:50.465000 +05:30
Flashback Media Recovery Log E:\ARCHIVE\TRGDB\TRGDB_1_36_967810818.ARC
2018-02-16 21:01:52.447000 +05:30
Flashback Media Recovery Log E:\ARCHIVE\TRGDB\TRGDB_1_37_967810818.ARC
2018-02-16 21:01:54.697000 +05:30
Flashback Media Recovery Log E:\ARCHIVE\TRGDB\TRGDB_1_1_968273102.ARC
Incomplete Recovery applied until change 2709582 time 02/16/2018 20:45:28
Flashback Media Recovery Complete
2018-02-16 21:01:58.676000 +05:30
Completed:  flashback database to scn 2709581

Now we need to start managed recovery process-

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH      CONNECTED             0          0          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               2       6144         47
ARCH      CLOSING              37      10240       1726
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG          3          0          0
RFS       IDLE                  0          0          0

8 rows selected.

SQL> select count(*) from hr.JOBS;
select count(*) from hr.JOBS
                        *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL> recover managed standby database cancel ;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,status,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ----------
ARCH      CLOSING               3          1        139
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               4          1          5
ARCH      CLOSING              37      10240       1726
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      APPLYING_LOG          5       1379     102400
RFS       IDLE                  5       1379          1
RFS       IDLE                  0          0          0

9 rows selected.

SQL> select count(*) from hr.JOBS;

  COUNT(*)
----------
        19
SQL>

My tables is back in stanby and is in sync with primary database too.

Ref:- https://uhesse.com/2010/08/06/using-flashback-in-a-data-guard-environment/

Why we need standby redo log file?

If you configure your standby for Maximum Protection, then Standby Redo Logs are required. Most implementations are configured for Maximum Performance because they do not want the performance hit Max Protect may impart on their application. Even if you are using Max Performance, you still want to implement SRLs. To understand why, we first need to start by examining how redo transport works when SRLs do not exist.  We’ll start by exploring the diagram below. 

In the system above, SRLs are not configured on the standby database. The arrows show how redo transport flows through the system. Redo travels along this path:

1. A transaction writes redo records into the Log Buffer in the System Global Area (SGA).
2. The Log Writer process (LGWR) writes redo records from the Log Buffer to the Online Redo Logs (ORLs).
3. When the ORL switches to the next log sequence (normally when the ORL fills up), the Archiver process (ARC0) will copy the ORL to the Archived Redo Log.
4. Because a standby database exists, a second Archiver process (ARC1) will read from a completed Archived Redo Log and transmit the redo over the network to the Remote File Server (RFS) process running for the standby instance.
5. RFS sends the redo stream to the local Archiver process (ARCn).
6. ARCn then writes the redo to the archived redo log location on the standby server.
7. Once the archived redo log is completed, the Managed Recovery Process (MRP0) sends the redo to the standby instance for applying the transaction.

The picture is more complex when we have Standby Redo Logs in place, as can be seen below. 

With SRLs, not only do we have more items in the picture, we also have different choices, i.e. different paths to get from the primary to the standby. The first choice is to decide if we are configured for Max Protect or Max Performance as I will discuss its impact below.

1. Just like without SRLs, a transaction generates redo in the Log Buffer in the SGA.
2. The LGWR process writes the redo to the ORL.
3. Are we in Max Protect/Max availability or Max Performance mode?
   1. If Max Protect/availability, then we are performing SYNC redo transport. The Network Server SYNC process (NSSn) is a slave process to LGWR. It ships redo to the RFS process on the standby server.
   2. If Max Performance mode, then we are performing ASYNC redo transport. The Network Server ASYNC process (NSAn) reads from the ORL and transports the redo to the RFS process on the standby server.
4. RFS on the standby server simply writes the redo stream directly to the SRLs.
5. How the redo gets applied depends if we are using Real Time Apply or not.
   1. If we are using Real Time Apply, MRP0 will read directly from the SRLs and apply the redo to the standby database.
   2. If we are not using Real Time Apply, MRP0 will wait for the SRL’s contents to be archived and then once archived and once the defined delay has elapsed, MRP0 will apply the redo to the standby database.

Step 3 above is the entire reason we want to use Standby Redo Logs. If we are in Max Protect (SYNC) mode, then SRLs are required otherwise this process will not work. If we are in Max Performance mode, will still want SRLs. Why? We want SRLs to be configured, even in Max Performance mode because they reduce data loss to seconds, rather than minutes or hours. Max Performance mode with SRLs often achieves a near-zero data loss solution. Take a minute to go back and re-read the underlined portions again. The underlined passage above is why you want to configure SRLs if you are in Max Performance mode.  The other big benefit to SRLs is when Real Time Apply is being performed. As soon as the redo is in the SRL, it can be replayed on the standby database. We do not have to wait for a log switch to occur. Real Time Apply, only possible with SRLs, means the recovery time to open the stnadby database in a failover operation is as low as it can be.

It is often find that people are operating under the misconception that if you configure for ASYNC, configure for Max Performance, then only ARCn can transport redo from the primary to the standby. This used to be true in much older versions, but in 10g (maybe 9i), ARCn is only used to transport redo only if SRLs are not configured.Also sometimes if the communication between primary and standby are interrupted(standby down for a while)ARCH process will send the archived redo logs to RFS process directly to resolve the archive log gap. If SRLs are configured, then for ASYNC, NSAn is used to transport redo. Furthermore, NSAn does this in near real time. 

Note: The NSSn and NSAn processes are new to Oracle 12c. In prior versions, a singular process, LNS performed this job.

Without SRLs, we must wait for a log switch to occur on the primary before the redo can be transported. If it takes one hour for the log switch to occur, then we can have one hour’s worth of data loss. If it takes six hours for that log switch to occur, then I can have six hour’s worth of data loss. This behavior was improved by implementing the ARCHIVE_LAG_TARGET initialization parameter in their primary configuration. If the DBA set this parameter to 3600 seconds, then a log switch would occur at most once per hour. Even with this parameter, one hour of data loss may seem like a lot to most companies, especially when you do better. 

Soource:- https://community.oracle.com/docs/DOC-1007036

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