Category Archives: Dataguard

Failover with No Broker (No Flashback)

Failover with No DG Broker (No Flashback) / Failover using Manual Method.

Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Enviroment
1. Simulate failover
2. Initiate Failover


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

On Primary

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005pr          OPEN         PRIMARY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> 

On Standby

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005prg         OPEN         PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> 


1. Simulate failover: Bring down the primary database w5005pr

DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


2. Steps to perform Failover

On Standby (w5005prg)

SQL> alter database recover managed standby database cancel;
 
Database altered.

SQL> alter database recover managed standby database finish force;

Database altered.

SQL>

Note: If Any error from above step then only follow below command.

alter database activate physical standby database;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL>

On Old Primary (W5005PR)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
SQL>

From New Primary Database

Rebuild Standby database for new primary

[oracle@rac2 ~]$ rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 12 00:22:53 2016

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

connected to target database: W5005PR (DBID=4158252169)
connected to auxiliary database: W5005PR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 12-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' ;
}
executing Memory Script

Starting backup at 12-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK
Finished backup at 12-JAN-16

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/w5005pr/controlfile/current.268.900456457';
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005prg.f tag=TAG20160112T002322 RECID=5 STAMP=900894203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes

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 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;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/w5005prg/datafile/system.264.900546429
output file name=+DATA/w5005pr/datafile/system.274.900894219 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/w5005prg/datafile/sysaux.265.900546465
output file name=+DATA/w5005pr/datafile/sysaux.275.900894265 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/w5005prg/datafile/undotbs1.266.900546499
output file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/w5005prg/datafile/users.267.900546507
output file name=+DATA/w5005pr/datafile/users.277.900894303 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=900894305 file name=+DATA/w5005pr/datafile/system.274.900894219
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=900894305 file name=+DATA/w5005pr/datafile/sysaux.275.900894265
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=900894305 file name=+DATA/w5005pr/datafile/undotbs1.276.900894299
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=900894305 file name=+DATA/w5005pr/datafile/users.277.900894303
Finished Duplicate Db at 12-JAN-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Failover with No DG Broker using Flashback

Failover with No DG Broker using Flashback Database

Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Environment
1. Pre-requisites
2. Simulate failover
3. Steps to perform Failover with Physical Standby database


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

On Primary

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005pr          OPEN         PRIMARY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>

On Standby

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005prg         OPEN         PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>


1. Pre-quesites

- Configre Physical Standby database. Please click below for step by step configuration.

Please click here

- Make sure that flashback database is enabled on both primary and standby database.


2. Simulate failover: Bring down the primary database w5005pr

DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Steps to perform Failover with Physical Standby database

On Standby (w5005prg)

SQL> alter database recover managed standby database cancel;
 
Database altered.

SQL> alter database recover managed standby database finish force;

Database altered.

SQL>

Note: If Any error from above step then only follow below command.

alter database activate physical standby database;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL>

Find SCN on the new primary database (w5005prg) 

SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
                   1618441

SQL>


On OLD Primary (W5005PR)

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> 

SQL> select name,INSTANCE_NAME from v$database,v$instance;

NAME       INSTANCE_NAME
--------- ----------------
W5005PR     w5005pr

SQL> flashback database to scn 1618441;  <---

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>

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

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING               4
ARCH      CONNECTED             0
ARCH      CLOSING               5
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING               6
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
MRP0      APPLYING_LOG          7 <--
RFS       IDLE                  7
RFS       IDLE                  0

37 rows selected.

SQL> 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Failover with DG Broker using Flashback

Reinstate a failed over Data Guard using Flashback Database

Overview:

In a Data Guard environment, the operations of any Oracle DBA wants to avoid is a Data Guard infrastructure failover.
Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database
However, in a MAA environments, unplanned failures can occur and they forces to do a database failover
However, if this failure is repaired in a few hours and the lost data center , is operational again.

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Enviroment
1. Pre-quesites
2. Primary Database Crashed
3. Steps to perform Failover with Physical Standby database
4. Reinstate database old primary (w5005pr)


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Pre-quesites: We need to set up Flashback Database

On Primary

Mount the database, configure flashback retention, start flashback database and open the database.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;
SQL> alter system set db_recovery_file_dest='+DATA' scope=both;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240; # Set up for 4 hour retention 
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

On STANDBY

Stop redo apply, configure flashback retention, start flashback database, open the database and start redo apply (Is active DG).

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;
SQL> alter system set db_recovery_file_dest='+DATA' scope=both;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


2. Primary Database Crashed

DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


[oracle@rac1 trace]$ ps -ef | grep pmon | grep -v grep
oracle    3589     1  0 19:53 ?        00:00:00 asm_pmon_+ASM
[oracle@rac1 trace]$

USER (ospid: 6254): terminating the instance
Instance terminated by USER, pid = 6254 <-- For some reason primary instance crashed


3. Steps to perform Failover with Physical Standby database

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "w5005pr"
DGM-17017: unable to determine configuration status

DGMGRL>
DGMGRL> failover to w5005prg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "w5005prg" <----
DGMGRL>

Database alert log (w5005prg)
/*
Thu Jan 21 21:33:25 2016
Data Guard Broker: Beginning failover <---
Thu Jan 21 21:33:26 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jan 21 21:33:26 2016
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/w5005prg/w5005prg/trace/w5005prg_mrp0_4750.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (w5005prg)
Managed Standby Recovery Canceled (w5005prg)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Attempt to do a Terminal Recovery (w5005prg)
Media Recovery Start: Managed Standby Recovery (w5005prg)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '01/21/2016 21:33:27'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 25 redo required
Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 5 Seq 25 Reading mem 0
  Mem# 0: +DATA/w5005prg/onlinelog/group_5.273.900719769
Identified End-Of-Redo (failover) for thread 1 sequence 25 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 1582299 time 01/21/2016 21:28:42
Media Recovery Complete (w5005prg)
Terminal Recovery: successful completion
Forcing ARSCN to IRSCN for TR 0:1582299
Attempt to set limbo arscn 0:1582299 irscn 0:1582299
Resetting standby activation ID 4158708098 (0xf7e0d982)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (w5005prg)
Maximum wait for role transition is 15 minutes.
Thu Jan 21 21:33:27 2016
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance w5005prg - Archival Error
ORA-16014: log 5 sequence# 25 not archived, no available destinations
ORA-00312: online log 5 thread 1: '+DATA/w5005prg/onlinelog/group_5.273.900719769'
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Jan 21 21:33:27 2016
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/w5005prg/w5005prg/trace/w5005prg_rsm0_4660.trc
Standby terminal recovery start SCN: 1582276
RESETLOGS after incomplete recovery UNTIL CHANGE 1582299
Online logfile pre-clearing operation disabled by switchover
Online log +DATA/w5005prg/onlinelog/group_1.268.900546507: Thread 1 Group 1 was previously cleared
Online log +DATA/w5005prg/onlinelog/group_2.269.900546509: Thread 1 Group 2 was previously cleared
Online log +DATA/w5005prg/onlinelog/group_3.270.900546509: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1582275
Thu Jan 21 21:33:27 2016
Setting recovery target incarnation to 4
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE OPEN
Data Guard Broker initializing...
Thu Jan 21 21:33:27 2016
Assigning activation ID 4159548412 (0xf7edabfc)
Thu Jan 21 21:33:27 2016
ARCc: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thu Jan 21 21:33:27 2016
ARCd: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: +DATA/w5005prg/onlinelog/group_2.269.900546509
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 21 21:33:27 2016
ARCe: Becoming the 'no SRL' ARCH
SMON: enabling cache recovery
Thu Jan 21 21:33:27 2016
.....
.....
No Resource Manager plan active
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: +DATA/w5005prg/onlinelog/group_3.270.900546509
Thu Jan 21 21:33:28 2016
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 225 added for thread 1 sequence 2 ID 0xf7edabfc dest 1:
Starting background process QMNC
Thu Jan 21 21:33:29 2016
QMNC started with pid=63, OS id=5812
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='w5005prg';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='w5005prg';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=30 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Failover succeeded. Primary database is now w5005prg.
Thu Jan 21 21:33:31 2016
idle dispatcher 'D000' terminated, pid = (20, 1)
Starting background process CJQ0
Thu Jan 21 21:33:31 2016
CJQ0 started with pid=69, OS id=5871
Thu Jan 21 21:33:32 2016
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 2097152 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;
*/

DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005prg - Primary database
    w5005pr  - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


4. Reinstate database (w5005pr)

On New Primary

DGMGRL> reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

/*
Failing it would have needed to configure flashback database to be able to do so automatically…

If you try to start w5005pr database in MOUNT state and issue again a request to reinstate you will now get clear error message:
*/

On old Primary (W5005PR)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL>

On New Primary
DGMGRL> reinstate database w5005pr;  <---
Reinstating database "w5005pr", please wait...
Operation requires shutdown of instance "w5005pr" on database "w5005pr"
Shutting down instance "w5005pr"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "w5005pr" on database "w5005pr"
Starting instance "w5005pr"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "w5005pr" ...
Reinstatement of database "w5005pr" succeeded  <----
DGMGRL>

Database alert log. (w5005pr)
/*
Starting Data Guard Broker (DMON)
Thu Jan 21 21:43:59 2016
INSV started with pid=27, OS id=6547
Thu Jan 21 21:44:02 2016
NSV1 started with pid=28, OS id=6559
Thu Jan 21 21:44:07 2016
NSV1 started with pid=28, OS id=6567
Thu Jan 21 21:44:10 2016
RSM0 started with pid=30, OS id=6571
Thu Jan 21 21:44:10 2016
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA
RFS[1]: Assigned to RFS process 6575
RFS[1]: Database mount ID mismatch [0xf7edabfc:0xf7ede817] (4159548412:4159563799)
RFS[1]: Not using real application clusters
Thu Jan 21 21:44:12 2016
RFS[2]: Assigned to RFS process 6579
RFS[2]: Database mount ID mismatch [0xf7edabfc:0xf7ede817] (4159548412:4159563799)
RFS[2]: Not using real application clusters
FLASHBACK DATABASE TO SCN 1582275
SUCCESS: diskgroup FRA was mounted
Flashback Restore Start
Thu Jan 21 21:44:13 2016
NOTE: dependency between database w5005pr and diskgroup resource ora.FRA.dg is established
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Flashback Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_21.421.901747617
Flashback Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_22.422.901747623
Recovery of Online Redo Log: Thread 1 Group 1 Seq 23 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_1.278.900894305
Recovery of Online Redo Log: Thread 1 Group 2 Seq 24 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_2.279.900894307
Incomplete Recovery applied until change 1582276 time 01/21/2016 21:28:19
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 1582275
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (w5005pr)
Flush standby redo logfile failed:1649
Clearing standby activation ID 4158708098 (0xf7e0d982)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/w5005pr/onlinelog/group_1.278.900894305
Clearing online log 1 of thread 1 sequence number 23
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/w5005pr/onlinelog/group_2.279.900894307
Clearing online log 2 of thread 1 sequence number 24
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/w5005pr/onlinelog/group_3.280.900894309
Clearing online log 3 of thread 1 sequence number 25
Clearing online redo logfile 3 complete
Completed: alter database convert to physical standby
Thu Jan 21 21:44:16 2016
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 3
All dispatchers and shared servers shutdown
alter database CLOSE NORMAL
ORA-1109 signalled during: alter database CLOSE NORMAL...
alter database DISMOUNT
Shutting down archive processes
Archiving is disabled
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 4
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 77
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 2
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
Completed: alter database DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Thu Jan 21 21:44:19 2016
Completed: Data Guard Broker shutdown
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Thu Jan 21 21:44:21 2016
Stopping background process VKTM
Thu Jan 21 21:44:21 2016
NOTE: Shutting down MARK background process
Thu Jan 21 21:44:23 2016
Instance shutdown complete
Thu Jan 21 21:44:23 2016
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
WARNING: db_recovery_file_dest is same as db_create_file_dest
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      rac1.rajasekhar.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Using parameter settings in server-side pfile /u01/app/oracle/product/11.2.0/db_1/dbs/initw5005pr.ora
System parameters with non-default values:
  processes                = 150
  spfile                   = "+DATA/w5005pr/spfilew5005pr.ora"
  memory_target            = 1216M
  control_files            = "+DATA/w5005pr/controlfile/current.268.900456457"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  log_archive_dest_1       = "LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=w5005pr"
  log_archive_dest_2       = "service="w5005prg""
  log_archive_dest_2       = "LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="w5005prg" net_timeout=30"
  log_archive_dest_2       = "valid_for=(all_logfiles,primary_role)"
  log_archive_dest_state_1 = "ENABLE"
  log_archive_dest_state_2 = "ENABLE"
  log_archive_min_succeed_dest= 1
  fal_client               = "W5005PR"
  fal_server               = "w5005prg"
  log_archive_trace        = 0
  log_archive_config       = "DG_CONFIG=(w5005pr,w5005prg)"
  log_archive_format       = "%t_%s_%r.dbf"
  log_archive_max_processes= 30
  archive_lag_target       = 0
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+DATA"
  db_recovery_file_dest_size= 2G
  standby_file_management  = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=w5005prXDB)"
  job_queue_processes      = 0
  audit_file_dest          = "/u01/app/oracle/admin/w5005pr/adump"
  audit_trail              = "DB"
  db_name                  = "w5005pr"
  db_unique_name           = "w5005pr"
  open_cursors             = 300
  dg_broker_start          = TRUE
  diagnostic_dest          = "/u01/app/oracle"
Thu Jan 21 21:44:24 2016
PMON started with pid=2, OS id=6641
Thu Jan 21 21:44:24 2016
PSP0 started with pid=3, OS id=6645
Thu Jan 21 21:44:25 2016
VKTM started with pid=4, OS id=6649 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Thu Jan 21 21:44:25 2016
...
...
NOTE: initiating MARK startup
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Starting background process MARK
Thu Jan 21 21:44:25 2016
MMNL started with pid=18, OS id=6711
Thu Jan 21 21:44:25 2016
MARK started with pid=19, OS id=6715
NOTE: MARK has subscribed
starting up 1 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle
Thu Jan 21 21:44:25 2016
DMON started with pid=23, OS id=6736
Thu Jan 21 21:44:25 2016
alter database  mount
Thu Jan 21 21:44:25 2016
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521))))' SCOPE=MEMORY SID='w5005pr';
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database w5005pr and diskgroup resource ora.DATA.dg is established
ARCH: STARTING ARCH PROCESSES
Thu Jan 21 21:44:32 2016
ARC0 started with pid=26, OS id=6786
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Jan 21 21:44:33 2016
ARC1 started with pid=27, OS id=6790
Successful mount of redo thread 1, with mount id 4159536441
Allocated 4194304 bytes in shared pool for flashback generation buffer
Starting background process RVWR
..
..
ARC0: STARTING ARCH PROCESSES COMPLETE
Thu Jan 21 21:44:38 2016
Starting Data Guard Broker (DMON)
Thu Jan 21 21:44:38 2016
INSV started with pid=64, OS id=6983
Thu Jan 21 21:44:43 2016
NSV1 started with pid=65, OS id=6991
Thu Jan 21 21:44:46 2016
RSM0 started with pid=66, OS id=7001
Thu Jan 21 21:44:46 2016
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA
Thu Jan 21 21:44:46 2016
SUCCESS: diskgroup FRA was mounted
RFS[1]: Assigned to RFS process 7007
RFS[1]: Opened log for thread 1 sequence 25 dbid -136715127 branch 900893050
Archived Log entry 39 added for thread 1 sequence 25 rlc 900893050 ID 0xf7e0d982 dest 2:
Thu Jan 21 21:44:46 2016
NOTE: dependency between database w5005pr and diskgroup resource ora.FRA.dg is established
Thu Jan 21 21:44:46 2016
RFS[2]: Assigned to RFS process 7011
RFS[2]: Opened log for thread 1 sequence 1 dbid -136715127 branch 901748007
RFS[2]: New Archival REDO Branch(resetlogs_id): 901748007  Prior: 900893050
RFS[2]: Archival Activation ID: 0xf7edabfc Current: 0x0
RFS[2]: Effect of primary database OPEN RESETLOGS
RFS[2]: Incarnation entry added for Branch(resetlogs_id): 901748007 (w5005pr)
Thu Jan 21 21:44:46 2016
RFS[3]: Assigned to RFS process 7013
RFS[3]: Opened log for thread 1 sequence 2 dbid -136715127 branch 901748007
Thu Jan 21 21:44:46 2016
Setting recovery target incarnation to 4
Archived Log entry 40 added for thread 1 sequence 1 rlc 901748007 ID 0xf7edabfc dest 2:
Thu Jan 21 21:44:46 2016
RFS[4]: Assigned to RFS process 7015
RFS[4]: Opened log for thread 1 sequence 3 dbid -136715127 branch 901748007
Archived Log entry 41 added for thread 1 sequence 2 rlc 901748007 ID 0xf7edabfc dest 2:
Archived Log entry 42 added for thread 1 sequence 3 rlc 901748007 ID 0xf7edabfc dest 2:
RFS[2]: Selected log 4 for thread 1 sequence 4 dbid -136715127 branch 901748007
Thu Jan 21 21:44:47 2016
Archived Log entry 43 added for thread 1 sequence 4 ID 0xf7edabfc dest 1:
Thu Jan 21 21:44:47 2016
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process 7023
RFS[5]: Selected log 4 for thread 1 sequence 5 dbid -136715127 branch 901748007
Data Guard: Failover target was Real Time Query standby; attempting to open this standby after reinstatement ...
ALTER DATABASE OPEN READ ONLY
Data Guard Broker initializing...
Data Guard Broker initialization complete
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Thu Jan 21 21:44:49 2016
SMON: enabling cache recovery
Dictionary check beginning
Dictionary check complete
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: ALTER DATABASE OPEN READ ONLY
Thu Jan 21 21:44:50 2016
db_recovery_file_dest_size of 2048 MB is 5.52% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='w5005pr';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='w5005pr';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=30 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET fal_server='w5005prg' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (w5005pr)
Thu Jan 21 21:44:51 2016
MRP0 started with pid=73, OS id=7050
MRP0: Background Managed Standby Recovery process started (w5005pr)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 1582299
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_25.425.901748687
Identified End-Of-Redo (failover) for thread 1 sequence 25 at SCN 0x0.1824db
Resetting standby activation ID 4158708098 (0xf7e0d982)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_1.426.901748687
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_2.427.901748687
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_3.428.901748687
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_4.429.901748687
Media Recovery Waiting for thread 1 sequence 5 (in transit)
Thu Jan 21 21:44:57 2016
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_4.281.900894309
Thu Jan 21 21:45:00 2016
Archived Log entry 44 added for thread 1 sequence 5 ID 0xf7edabfc dest 1:
Media Recovery Waiting for thread 1 sequence 6
Thu Jan 21 21:45:00 2016
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Assigned to RFS process 7054
RFS[6]: Selected log 4 for thread 1 sequence 6 dbid -136715127 branch 901748007
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_4.281.900894309
  
*/


DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005prg - Primary database
    w5005pr  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Failover with DG Broker

Failover with DG Broker (No-Flashback)

Overview:

Failover is an irreversible transition of a standby database to the primary role.
This is only done in the event of a catastrophic failure of the primary database

0. Enviroment
1. Primary Database Crashed
2. Steps to perform Failover with Physical Standby database
3. Reinstate database old primary (w5005pr)
4. Rebuild Standby for new primary
5. Enable old primary database (w5005pr)

Note: Flashback database is not enabled on both primary and standby (in our case)

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.


0. Enviroment

Primary:

Platform: Linuxx86_64
Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version: Oracle 11.2.0.3, File system: ASM
DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
Flashback: Disabled
Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

Standby:

Platform: Linuxx86_64
Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version: Oracle 11.2.0.3, , File system: ASM
DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
Flashback: Disabled
Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Primary Database Crashed

DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005pr - Primary database
w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


[oracle@rac1 ~]$ ps -ef | grep pmon | grep -v grep
oracle 3540 1 0 Jan11 ? 00:00:02 asm_pmon_+ASM
[oracle@rac1 ~]$


Tue Jan 12 00:00:22 2016
CKPT (ospid: 2499): terminating the instance due to error 472
Tue Jan 12 00:00:23 2016
License high water mark = 5
Instance terminated by CKPT, pid = 2499
USER (ospid: 3029): terminating the instance
Instance terminated by USER, pid = 3029 <---- Instance terminated 


2. Steps to perform Failover with Physical Standby database

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005pr - Primary database
w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "w5005pr"
DGM-17017: unable to determine configuration status

DGMGRL>
DGMGRL> failover to w5005prg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "w5005prg" <-- Succeeded
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


3. Reinstate database old primary

DGMGRL> reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

/*
Failing it would have needed to configure flashback database to be able to do so automatically…

If you try to start w5005pr database in MOUNT state and issue again a request to reinstate you will now get clear error message
*/


[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
Database mounted.
SQL>


DGMGRL> reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16827: Flashback Database is disabled <-- Actual error

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
SQL>


4. Rebuild Standby database for new primary (w5005prg)

[oracle@rac2 ~]$ rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 12 00:22:53 2016

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

connected to target database: W5005PR (DBID=4158252169)
connected to auxiliary database: W5005PR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 12-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' ;
}
executing Memory Script

Starting backup at 12-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK
Finished backup at 12-JAN-16

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/w5005pr/controlfile/current.268.900456457';
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005prg.f tag=TAG20160112T002322 RECID=5 STAMP=900894203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes

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 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;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/w5005prg/datafile/system.264.900546429
output file name=+DATA/w5005pr/datafile/system.274.900894219 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/w5005prg/datafile/sysaux.265.900546465
output file name=+DATA/w5005pr/datafile/sysaux.275.900894265 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/w5005prg/datafile/undotbs1.266.900546499
output file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/w5005prg/datafile/users.267.900546507
output file name=+DATA/w5005pr/datafile/users.277.900894303 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=900894305 file name=+DATA/w5005pr/datafile/system.274.900894219
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=900894305 file name=+DATA/w5005pr/datafile/sysaux.275.900894265
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=900894305 file name=+DATA/w5005pr/datafile/undotbs1.276.900894299
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=900894305 file name=+DATA/w5005pr/datafile/users.277.900894303
Finished Duplicate Db at 12-JAN-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$


5. Enable old primary database

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database (disabled)
ORA-16795: the standby database needs to be re-created

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> enable database w5005pr;
Enabled.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database <-- Old Primary

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Switchover with Broker

Performing Switchover with DG Broker

Overview:

A switchover is a role reversal between the primary database and one of its standby  databases.  A switchover operation guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to recreate either database.

0. Enviroment
1. Pre-Switchover tasks
2. Verify TNS Entries
3. Steps to perform switchover with Physical Standby databases
4. Switch back to old primary


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Pre-Switchover tasks

- Verify that there is network connectivity between the primary and standby locations. 
- Always recommened test the switchover in your testing system befre working on production.
- Verify primary database READ WRITE and standby instance mounted.
- Verify there are no active users connected to database.
- Make sure last redo data transmitted from primary to standby and applied.
- Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
	alter system set job_queue_processes=0 ;
	alter system set aq_tm_processes = 0 ;
	alter system set dbwr_io_slaves = 0 ; 


2. Vefify TNS Entries

Primary

[oracle@rac1 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005pr_dgmgrl)
                (SID_NAME = w5005pr)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac1 ~]$

[oracle@rac1 ~]$ /u01/app/11.2.0/grid/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2016 00:37:10

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-JAN-2016 17:25:42
Uptime                    0 days 7 hr. 11 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005pr" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005prXDB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005pr_DGB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005pr_dgmgrl" has 1 instance(s).
  Instance "w5005pr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$

Standby

[oracle@rac2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005prg_dgmgrl)
                (SID_NAME = w5005prg)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

[oracle@rac2 ~]$


[oracle@rac2 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac2 ~]$

[oracle@rac2 ~]$ /u01/app/11.2.0/grid/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2016 00:37:27

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-JAN-2016 17:25:53
Uptime                    0 days 7 hr. 11 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005prg" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prgXDB" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prg_DGB" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prg_dgmgrl" has 1 instance(s).
  Instance "w5005prg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$


3. Steps to perform switchover with Physical Standby databases

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005pr
Connected.
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL> show database verbose w5005pr

Database - w5005pr

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    w5005pr

  Properties:
    DGConnectIdentifier             = 'w5005pr'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'w5005pr'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=w5005pr_DGMGRL)(INSTANCE_NAME=w5005pr)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> 



DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to w5005prg;
Performing switchover NOW, please wait...
New primary database "w5005prg" is opening...
Operation requires shutdown of instance "w5005pr" on database "w5005pr"
Shutting down instance "w5005pr"...
ORACLE instance shut down.
Operation requires startup of instance "w5005pr" on database "w5005pr"
Starting instance "w5005pr"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "w5005prg"
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005prg - Primary database
    w5005pr  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


4. Switch back to old primary (If required)

DGMGRL> switchover to w5005pr;
Performing switchover NOW, please wait...
New primary database "w5005pr" is opening...
Operation requires shutdown of instance "w5005prg" on database "w5005prg"
Shutting down instance "w5005prg"...
ORACLE instance shut down.
Operation requires startup of instance "w5005prg" on database "w5005prg"
Starting instance "w5005prg"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "w5005pr"
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Switchover with No Broker

Performing Switchover with No Broker (Manual)

Overview:

A switchover is a role reversal between the primary database and one of its standby databases. A switchover operation guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to recreate either database.

0. Enviroment
1. Pre-Switchover tasks
2. Steps to perform switchover with Physical Standby databases
3. Additional Information


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Pre-Switchover tasks

– Verify that there is network connectivity between the primary and standby locations.
– Always recommened test the switchover in your testing system befre working on production.
– Verify primary database READ WRITE and standby instance mounted.
– Verify there are no active users connected to database.
– Make sure last redo data transmitted from primary to standby and applied.
– Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
alter system set job_queue_processes=0 ;
alter system set aq_tm_processes = 0 ;
alter system set dbwr_io_slaves = 0 ;

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            20

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL> select * from dba_jobs_running;

no rows selected

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL>

Note: Block further job submission by setting the job_queue_processes parameter to 0 so that 
there would be no jobs running during switchover.

SQL> alter system set job_queue_processes=0 scope=spfile;

System altered.

SQL>

On Standby

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            20

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL> 


2. Steps to perform switchover with Physical Standby databases

On PRIMARY

Note: Always perform the switchover of the primary database to standby database first and then 
switchover the standby database to primary. 

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL>

Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover
using the below query

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL>
SQL> shut immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 10 01:15:12 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            22

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
W5005PR   MOUNTED

SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL> SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            22

SQL>


3. Additional Information

The SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED - Either this is a standby database and the primary database 
              has not been switched first, or this is a prstimary 
              database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached
                  to the primary or standby database that need to be 
                  disconnected before the switchover operation is 
                  permitted.

SWITCHOVER PENDING - This is a standby database and the primary database 
                     switchover request has been received but not 
                     processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not 
                    complete and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is 
             allowed to switch over to a primary database. 

TO STANDBY - This is a primary database, with no active sessions, that is 
             allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the 
                  switchover request. 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

DG Broker Configuration

How to setup Data Guard Broker Configuration

Pre-requisites: Configre Physical Standby database. Please click here

0. Enviroment
1. Check the current state of DG Broker on Primary Database
2. Check the current state of DG Broker on Standby Database
3. Vefiry GAP
4. Enable DG broker
5. Modify Listener.ora
6. Create the Dataguard Broker configuration on primary


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Check the current state of DG Broker on Primary Database

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>


2. Check the current state of DG Broker on Standby Database

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>


3. Vefiry GAP

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         w5005pr          PRIMARY          MAXIMUM PERFORMANCE

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            35

SQL>


Standby Side

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         w5005prg         PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            35

SQL>


4. Enable DG broker

On Primary

SQL> alter system set dg_broker_start=true;

System altered.


SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr1w5005pr.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr2w5005pr.dat
SQL>

On Standby

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr1w5005prg.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr2w5005prg.dat
SQL>


5. Modify Listener.ora

Add a static entry for the DGMGRL in the listener.ora on both the primary and standby servers.

On Primary

(SID_DESC =
 (GLOBAL_DBNAME=w5005pr_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005pr)
)

On Standby 

(SID_DESC =
 (GLOBAL_DBNAME=w5005prg_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005prg)
)

Listener.ora file contents

Primary

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005pr)
                (SID_NAME = w5005pr)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

(SID_DESC =
 (GLOBAL_DBNAME=w5005pr_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005pr)
)
[oracle@rac1 admin]$

On Standby

[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005prg)
                (SID_NAME = w5005prg)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

(SID_DESC =
 (GLOBAL_DBNAME=w5005prg_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005prg)
)
[oracle@rac2 admin]$


6. Create the Dataguard Broker configuration. On the primary

[oracle@rac1 ~]$ which dgmgrl
/u01/app/oracle/product/11.2.0/db_1/bin/dgmgrl
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005pr
Connected.
DGMGRL> create configuration 'w5005pr' as primary database is 'w5005pr' connect identifier is w5005pr;
Configuration "w5005pr" created with primary database "w5005pr"
DGMGRL>
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database 'w5005prg' as connect identifier is w5005prg maintained as physical;
Database "w5005prg" added
DGMGRL>
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Drop SRL

How to drop the standby redo logs

0. Enviroment
1. SET STANDBY_FILE_MANAGEMENT=’MANUAL’
2. Drop SRL On Standby
3. SET STANDBY_FILE_MANAGEMENT=’AUTO’
4. Enable MRP


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: APAC, DB_UNIQUE_NAME: APAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: APAC, DB_UNIQUE_NAME: EMEA
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. SET STANDBY_FILE_MANAGEMENT=’MANUAL’

On Standby

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/emea/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/emea/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/emea/redo01.log                      NO
         4         STANDBY /u01/app/oracle/oradata/emea/redo04.log                      NO
         5         STANDBY /u01/app/oracle/oradata/emea/redo05.log                      NO
         6         STANDBY /u01/app/oracle/oradata/emea/redo06.log                      NO
         7         STANDBY /u01/app/oracle/oradata/emea/redo07.log                      NO

7 rows selected.

SQL>

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


SQL> alter database recover managed standby database cancel;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY            PHYSICAL STANDBY

SQL> 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

System altered.

SQL>


2. Drop SRL On Standby

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/emea/redo04.log'

SQL> 

Note: If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as below

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/emea/redo03.log                      NO
         2         ONLINE  /u01/app/oracle/oradata/emea/redo02.log                      NO
         1         ONLINE  /u01/app/oracle/oradata/emea/redo01.log                      NO

SQL> select * from v$standby_log;

no rows selected

SQL>


3. SET STANDBY_FILE_MANAGEMENT=’AUTO’

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

System altered.

SQL>


4. Enable MRP

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Active Dataguard on ASM

Creating Active Data Guard Physical Standby Environment on ASM

AIM:Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command (No need to take backup of primary database)

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition.

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.

0. Enviroment
1. Enable Force Logging
2. Create Password File on Primary
3. Configure a Standby Redo Log on Primary
4. Verify Archive Mode Enabled on Primary
5. Set Primary Database Initialization Parameters
6. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE
7. Set Standby Database Initialization Parameters
8. Crete required directories on Standby
9. Add below entry in ORATAB on Standby
10. Create spfile from pfile on Standby
11. Configure LISTENER Entries on Primary
12. Configure LISTENER Entries on Standby
13. Configure TNS Entries on Primary
14. Configure LISTENER Entries on Standby
15. Verify connection ‘AS SYSDBA’ from Primary
16. Verify connection ‘AS SYSDBA’ from Standby
17. Run the duplicate from active database command from primary
18. Enable MRP on Standby
19. Verify the Physical Standby Database Is Performing Properly
20. Add the standby database w5005prg to the local OCR.


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Enable Force Logging

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL>


SQL> select force_logging from v$database;

FOR
---
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

SQL>


2. Create Password File on Primary

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ rm hc_apac.dat
[oracle@rac1 dbs]$ ls -ltr
total 20
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall   24 Jan  6 22:46 lkW5005PR
-rw-r----- 1 oracle oinstall 1536 Jan  6 22:47 orapww5005pr
-rw-r----- 1 oracle oinstall   41 Jan  6 22:48 initw5005pr.ora
-rw-rw---- 1 oracle oinstall 1544 Jan  6 22:48 hc_w5005pr.dat
[oracle@rac1 dbs]$


3. Configure a Standby Redo Log on Primary (Not Mandatory)

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 +DATA/apac/onlinelog/group_3.263.900283687                     52428800
         1          2 +DATA/apac/onlinelog/group_2.262.900283687                     52428800
         1          1 +DATA/apac/onlinelog/group_1.261.900283685                     52428800

SQL>

SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m;

Database altered.

SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m;

Database altered.

SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m;

Database altered.

SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m;

Database altered.

SQL>

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 +DATA/w5005pr/onlinelog/group_3.263.900456463                  52428800
         1          2 +DATA/w5005pr/onlinelog/group_2.266.900456463                  52428800
         1          1 +DATA/w5005pr/onlinelog/group_1.267.900456461                  52428800

SQL>


4. Verify Archive Mode Enabled on Primary

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>


5. Set Primary Database Initialization Parameters

SQL> show parameter pfile;

NAME       TYPE         VALUE
---------- ----------- --------------------------------
spfile     string       +DATA/w5005pr/spfilew5005pr.ora

SQL> create pfile='/home/oracle/initw5005pr.ora.bkp' from spfile;

File created.

SQL> alter system set db_unique_name='w5005pr' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(w5005pr,w5005prg)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=w5005pr' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=w5005prg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=w5005prg' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET fal_client=w5005pr scope=both;

System altered.

SQL> ALTER SYSTEM SET fal_server=w5005prg scope=both;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> create pfile='/home/oracle/initw5005pr.ora' from spfile;

File created.

SQL>


[oracle@rac1 ~]$ cat initw5005pr.ora
w5005pr.__db_cache_size=503316480
w5005pr.__java_pool_size=16777216
w5005pr.__large_pool_size=16777216
w5005pr.__pga_aggregate_target=520093696
w5005pr.__sga_target=754974720
w5005pr.__shared_io_pool_size=0
w5005pr.__shared_pool_size=201326592
w5005pr.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/w5005pr/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/w5005pr/controlfile/current.268.900456457'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='w5005pr'
*.db_unique_name='w5005pr'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=w5005prXDB)'
*.fal_client='W5005PR'
*.fal_server='W5005PRG'
*.log_archive_config='DG_CONFIG=(w5005pr,w5005prg)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=w5005pr'
*.log_archive_dest_2='SERVICE=w5005prg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=w5005prg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac1 ~]$


6. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE

Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@rac1 ~]$ scp initw5005pr.ora oracle@rac2:/home/oracle/initw5005prg.ora
initw5005pr.ora                                                                            100% 1255     1.2KB/s   00:00
[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@rac1 dbs]$ scp orapww5005pr oracle@rac2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg
orapww5005pr                                                                               100% 1536     1.5KB/s   00:00
[oracle@rac1 dbs]$

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@rac2 dbs]$ ll
total 36
-rw-r--r-- 1 oracle oinstall  2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall  1536 Jan  7 21:35 orapww5005prg
[oracle@rac2 dbs]$


7. Set Standby Database Initialization Parameters

Edit shown highlighted to set it up for the standby role

[oracle@rac2 ~]$ cat initw5005prg.ora
w5005prg.__db_cache_size=503316480
w5005prg.__java_pool_size=16777216
w5005prg.__large_pool_size=16777216
w5005prg.__pga_aggregate_target=520093696
w5005prg.__sga_target=754974720
w5005prg.__shared_io_pool_size=0
w5005prg.__shared_pool_size=201326592
w5005prg.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/w5005prg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#*.control_files='+DATA/w5005prg/controlfile/current.268.900456457'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='w5005pr'
*.db_unique_name='w5005prg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=w5005prgXDB)'
*.fal_client='W5005PRG'
*.fal_server='W5005PR'
*.log_archive_config='DG_CONFIG=(w5005prg,w5005pr)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=w5005prg'
*.log_archive_dest_2='SERVICE=w5005pr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=w5005pr'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac2 ~]$


8. Crete required directories on Standby

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/w5005prg/adump
[oracle@rac2 ~]$


9. Add below entry in ORATAB on Standby

w5005prg:/u01/app/oracle/product/11.2.0/db_1:N


10. Create spfile from pfile on Standby

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? w5005prg
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 7 21:41:19 2016

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initw5005prg.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
SQL> create spfile='+DATA/w5005prg/spfilew5005prg.ora' from pfile='/home/oracle/initw5005prg.ora';

File created.

SQL> startup nomount force;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 bytes
Redo Buffers                8847360 bytes
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/w5005prg/spfilew5005prg.
                                                 ora
SQL>


[oracle@rac2 dbs]$ cat initw5005prg.ora
SPFILE='+DATA/w5005prg/spfilew5005prg.ora'
[oracle@rac2 dbs]$


11. Configure LISTENER Entries on Primary

[oracle@rac1 admin]$ pwd
/u01/app/11.2.0/grid/network/admin
[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005pr)
                (SID_NAME = w5005pr)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)
[oracle@rac1 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:29:12

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:29:23

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                07-JAN-2016 23:05:32
Uptime                    0 days 0 hr. 23 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005pr" has 2 instance(s).
  Instance "w5005pr", status UNKNOWN, has 1 handler(s) for this service...
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005prXDB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$


12. Configure LISTENER Entries on Standby

[oracle@rac2 admin]$ pwd
/u01/app/11.2.0/grid/network/admin
[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005prg)
                (SID_NAME = w5005prg)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)
[oracle@rac2 admin]$

[oracle@rac2 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:27:54

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@rac2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:28:05

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                07-JAN-2016 23:05:36
Uptime                    0 days 0 hr. 22 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005prg" has 1 instance(s).
  Instance "w5005prg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$


13. Configure TNS Entries on Primary

[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac1 admin]$

[oracle@rac1 ~]$ tnsping w5005pr

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:38:47

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = w5005pr)))
OK (0 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping w5005prg

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:38:56

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = w5005prg)))
OK (0 msec)
[oracle@rac1 ~]$


14. Configure LISTENER Entries on Standby

[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac1 admin]$

[oracle@rac2 ~]$ tnsping w5005pr

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:40:05

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = w5005pr)))
OK (10 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping w5005prg

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-JAN-2016 23:40:08

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = w5005prg)))
OK (0 msec)
[oracle@rac2 ~]$


15. Verify connection ‘AS SYSDBA’ from Primary

[oracle@rac1 ~]$ sqlplus sys/sys@w5005pr as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 7 23:40:49 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[oracle@rac1 ~]$ sqlplus sys/sys@w5005prg as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 7 23:41:29 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>


16. Verify connection ‘AS SYSDBA’ from Standby

[oracle@rac2 ~]$ sqlplus sys/sys@w5005pr as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 7 23:43:03 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus sys/sys@w5005prg as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 7 23:43:11 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@rac2 ~]$


17. Run the duplicate from active database command from primary

[oracle@rac1 ~]$ rman target sys/sys@w5005pr auxiliary sys/sys@w5005prg

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 7 23:45:10 2016

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

connected to target database: W5005PR (DBID=4158252169)
connected to auxiliary database: W5005PR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 07-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg'   ;
}
executing Memory Script

Starting backup at 07-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
Finished backup at 07-JAN-16

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/w5005prg/controlfile/current.262.900546411'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DATA/w5005prg/controlfile/current.263.900546411';
   sql clone "alter system set  control_files =
  ''+DATA/w5005prg/controlfile/current.263.900546411'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/w5005prg/controlfile/current.262.900546411'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 07-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005pr.f tag=TAG20160107T234652 RECID=1 STAMP=900546413
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-JAN-16

sql statement: alter system set  control_files =   ''+DATA/w5005prg/controlfile/current.263.900546411'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2227984 bytes
Variable Size                754974960 bytes
Database Buffers             503316480 bytes
Redo Buffers                   8847360 bytes

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 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;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 07-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/w5005pr/datafile/system.260.900456399
output file name=+DATA/w5005prg/datafile/system.264.900546429 tag=TAG20160107T234709
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/w5005pr/datafile/sysaux.265.900456401
output file name=+DATA/w5005prg/datafile/sysaux.265.900546465 tag=TAG20160107T234709
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/w5005pr/datafile/undotbs1.264.900456401
output file name=+DATA/w5005prg/datafile/undotbs1.266.900546499 tag=TAG20160107T234709
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/w5005pr/datafile/users.269.900456401
output file name=+DATA/w5005prg/datafile/users.267.900546507 tag=TAG20160107T234709
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-JAN-16

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=900546507 file name=+DATA/w5005prg/datafile/system.264.900546429
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=900546507 file name=+DATA/w5005prg/datafile/sysaux.265.900546465
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=900546507 file name=+DATA/w5005prg/datafile/undotbs1.266.900546499
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=900546507 file name=+DATA/w5005prg/datafile/users.267.900546507
Finished Duplicate Db at 07-JAN-16

RMAN> exit


Recovery Manager complete.
[oracle@rac1 ~]$
[oracle@rac1 ~]$


18. Enable MRP on Standby

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   MOUNTED              PHYSICAL STANDBY w5005prg

SQL> !ps -ef | grep mrp
oracle    4696  4666  0 23:52 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle    4698  4696  0 23:52 pts/1    00:00:00 grep mrp

SQL> alter database open;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY            PHYSICAL STANDBY w5005prg

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL> !ps -ef | grep mrp
oracle    4718     1  2 23:53 ?        00:00:00 ora_mrp0_w5005prg
oracle    4765  4666  0 23:53 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle    4767  4765  0 23:53 pts/1    00:00:00 grep mrp

SQL>


19. Verify the Physical Standby Database Is Performing Properly

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         2 06-JAN-16 07-JAN-16
         3 07-JAN-16 07-JAN-16
         4 07-JAN-16 07-JAN-16
         5 07-JAN-16 07-JAN-16
         6 07-JAN-16 07-JAN-16
         7 07-JAN-16 07-JAN-16
         8 07-JAN-16 07-JAN-16

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            10

SQL>

On Standby

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         6 07-JAN-16 07-JAN-16
         7 07-JAN-16 07-JAN-16
         8 07-JAN-16 07-JAN-16
         9 07-JAN-16 07-JAN-16
        10 07-JAN-16 07-JAN-16

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            10

SQL>

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
MRP0      WAIT_FOR_LOG         10          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                 10          1

35 rows selected.

SQL>


20. Add the standby database w5005prg to the local OCR.

[oracle@rac2 ~]$ which srvctl
/u01/app/oracle/product/11.2.0/db_1/bin/srvctl
[oracle@rac2 ~]$ srvctl add database -d w5005prg -o /u01/app/oracle/product/11.2.0/db_1/ -r physical_standby -s 'READ ONLY'
[oracle@rac2 ~]$ srvctl start database -d w5005prg

[oracle@rac2 ~]$ /u01/app/11.2.0/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac2
ora.FRA.dg
               ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac2
ora.asm
               ONLINE  ONLINE       rac2                     Started
ora.ons
               OFFLINE OFFLINE      rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       rac2
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       rac2
ora.w5005prg.db
      1        ONLINE  ONLINE       rac2                     Open,Readonly
[oracle@rac2 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Enable Real Time Apply

How to Enable Real Time Apply

Real-time apply : When real-time apply is enabled, the log apply services can apply redo data as it is received,
                                     without waiting for the current standby redo log file to be archived.

Pre-requisites: Configure Physical Standby database. Click here

0. Enviroment
1. Set up log_archive_dest_2 on Primary
2. Verify Real-Tme Apply Status on Standby
3. Try to enable real-time apply
4. Add standby redo logs on primary and standby
5. Enable Real-Time Apply
6. Quick Testing


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=w5005prg LGWR ASYNC VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=w5005p
                                                 rg


2. Verify Real-Tme Apply Status on Standby

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_1             VALID     LOCAL          NO  MANAGED

SQL>

Note: If it is not in Real-Time apply mode then SRL (Standby Redo Logs) will not be in use.

SRL=NO
RECOVERY_MODE = Managed : It means Redo-Apply only, Not Real Time Aapply.


3. Try to enable real-time apply

SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

SQL>

/*
standby redo logs are populated with redo information as fast as the primary redo logs, 
rather than waiting for the redo log to be archived and shipped to the standby.  
This results in faster switchover and failover times because the standby redo log files have been applied 
already to the standby database by the time the failover or switchover begins.
Oracle recommends the below formula to calculate the number of Standby redo logs file as 
(maximum number of logfiles for each thread + 1) * maximum number of threads
*/


4. Add standby redo logs

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> col member for a50
SQL> set lines 180
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA/w5005pr/onlinelog/group_3.263.900456463      NO
         2         ONLINE  +DATA/w5005pr/onlinelog/group_2.266.900456463      NO
         1         ONLINE  +DATA/w5005pr/onlinelog/group_1.267.900456461      NO

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

no rows selected

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1         50
         2         50
         3         50

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

no rows selected

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 +DATA/w5005pr/onlinelog/group_4.259.900718771        52428800
         5 +DATA/w5005pr/onlinelog/group_5.258.900718779        52428800
         6 +DATA/w5005pr/onlinelog/group_6.257.900718785        52428800
         7 +DATA/w5005pr/onlinelog/group_7.256.900718791        52428800

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED

SQL>

On Standby

SQL> alter database add standby logfile group 4 size 50M;
alter database add standby logfile group 4 size 50M
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL>


5. Enable Real-Time Apply on Standby

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>
SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_1             VALID     LOCAL          NO  MANAGED REAL TIME APPLY

SQL>

On Primary

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2             VALID     PHYSICAL       YES MANAGED REAL TIME APPLY

SQL>


6. Quick Testing

On Primary

SQL> set time on
00:03:15 SQL> create table test (name varchar2(30));

Table created.


00:03:24 SQL> insert into test values ('Rajasekhar Amudala');

1 row created.

00:05:22 SQL>  COMMIT;

Commit complete.

00:05:23 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:24 SQL>

On Standby

SQL> set time on
00:05:30 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:21 SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.