A failover is typically used only when the Oracle primary database becomes unavailable

A failover is typically used only when the primary database becomes unavailable, and there is no possibility of restoring it to service within a reasonable period of time

Performing a Failover to a Physical Standby Database –

Step 01- Flush any unsent redo from the primary database to the target standby database

If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode. Mount, but do not open primary database. If primary database cannot be mounted go to step 2. On Primary database ( if accessible during real disaster )

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

For target_db_name, specify the DB_UNIQUE_NAME of the standby database that is to receive the redo flushed from the primary database. This statement flushes any unsent redo from the primary database to the standby database, and waits for that redo to be applied to the standby database. If this statement completes without any errors,

go to Step 3. If the statement completes with any error, or

 if it must be stopped because you cannot wait any longer for the statement to complete, continue with Step 2.


Step 2 – Identify and resolve any archived redo log gaps On Standby database

SQL> SELECT DATABASE_MODE,RECOVERY_MODE,GAP_STATUS FROM V$ARCHIVE_DEST_STATUS;

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

Wait for logs indicates it’s not applied to DR. 

If possible, copy any missing archived redo log files to the target standby database from the primary database. 
If primary database is not accessible then there will be data loss.

We must run the following query to learn last applied archive log sequence number.
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
 If you can access archive logs which are not copied to standby then copy archives to standby. After copy archive log files we must register them to standby database. 
This operation must be done for every thread.

SQL> alter database register physical logfile '/b01/fra/tstssby_991834413_1_102.arc ';
 Check the standby database for redo gap. If there is a gap then we must copy archive log files and register.

As a result of the above query until it returns to zero.
stop the redo apply process in standby database.

 3 
 Step 3 – Stop Redo Apply


 4 
 Step 4 – Finish applying all received Redo Data

 Verify that the target standby database is ready to become a primary database

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 

SWITCHOVER_STATUS 

TO PRIMARY 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.
SQL>
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         TSTSBY           PRIMARY

 5   On Standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

 If this statement completes with  error

If the error condition cannot be resolved, a failover can still be performed (with some data loss) by issuing the following SQL statement On Standby database

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

 Open the new primary database On Standby database

SQL> ALTER DATABASE OPEN;

   Convert the database to a physical standby database 
1   
On Old Primary database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

This statement will dismount the database after successfully converting the control file to a standby control file

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

 2  
On New Primary database

query to see the current state of the archive destinations

 SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

 If necessary, enable the destination

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

Perform a log switch to ensure the standby database begins receiving redo data from the new primary database

 SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
  
Start Redo Apply on the new physical standby database

 On New Physical standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

performed Switchover and Switchback in Oracle 12c

12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1

Performed Switchover and Switchback  in Oracle 12c

1. 2 instances Oracle 12c RAC primary database ( TSTDB)
2. Single instance Oracle 12c RAC Standby database ( TSTSBY)
3. Noted – all the issues – documented steps

Switchover and Switchback in 12c

1.       Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases
2.       Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database
3.       Create STANDBY REDO logs on PRIMARY, if not exists
4.       FAL_CLIENT & FAL_SERVER parameters set on both databases

  

Pre-Switchover in 12c

oracle@raj1 (SID:TSTDB_1) /h01/app/oracle/db/12.1.0.2_03/dbs $ srvctl status database -d TSTDB

Instance TSTDB_1 is running on node raj1
Instance TSTDB_2 is running on node raj2


DGMGRL> connect /

Connected as SYSDG.

DGMGRL> show configuration

 Configuration - TSTDBCONF

  Protection Mode: MaxPerformance

  Members:

  TSTDB  - Primary database

    TSTSBY - Physical standby database


Fast-Start Failover: DISABLED



Configuration Status:

SUCCESS   (status updated 59 seconds ago)


Let’s run the command on the primary database to validate if the environments are ready for the role transition

SQL> select status , error from v$archive_dest where dest_id=2;

STATUS    ERROR

--------- -----------------------------------------------------------------

INACTIVE


SQL> alter database switchover to TSTSBY verify;

alter database switchover to TSTSBY verify

*ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details


SQL> alter database switchover to TSTSBY;

Database altered.


SQL>

 oracle@raj1 (SID:TSTDB_1) /h01/app/oracle/db/12.1.0.2_03/dbs $ srvctl status database -d TSTDB

Instance TSTDB_1 is not running on node raj1

Instance TSTDB_2 is not running on node raj2

oracle@(SID:TSTDB_1) /h01/app/oracle/db/12.1.0.2_03/dbs

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


STATUS       INSTANCE_NAME    DATABASE_ROLE

------------ ---------------- ----------------

MOUNTED      TSTSBY           PRIMARY


SQL> alter database open;


Database altered.


SQL>


SQL> select status , error from v$archive_dest where dest_id=2;


STATUS    ERROR

--------- -----------------------------------------------------------------

VALID


SQL> alter system switch logfile;

System altered.

SQL>  alter system switch logfile;

 System altered.


ALTER SYSTEM SET log_archive_dest_2='service="TSTDB"','ASYNC NOAFFIRM delay=1 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTDB" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;


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


STATUS       INSTANCE_NAME    DATABASE_ROLE

------------ ---------------- ----------------

MOUNTED      TSTDB_1          PHYSICAL STANDBY



SQL> alter database recover managed standby database using archived logfile disconnect;

 Database altered.



2019-04-15 14:58:17.261000 +10:00

Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_1_seq_97.1086.1005663391

Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_2_seq_69.2336.1005662909

Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_2_seq_70.4376.1005663391

Resetting standby activation ID 0 (0x0)

2019-04-15 14:58:18.367000 +10:00

Media Recovery End-Of-Redo indicator encountered

Media Recovery Continuing

Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_2_seq_71.3281.1005663441

Media Recovery Delayed for 1 minute(s) (thread 1 sequence 98)

2019-04-15 14:58:19.586000 +10:00

Media Recovery Log +FLASH/TSTDB/ARCHIVELOG/2019_04_15/thread_1_seq_98.3285.1005663439

Media Recovery Waiting for thread 1 sequence 99 (in transit)

2019-04-15 15:00:57.038000 +10:00

RFS[1]: Selected log 6 for thread 1 sequence 100 dbid 3526761822 branch 1005127022

Archived Log entry 777 added for thread 1 sequence 99 ID 0xd2f7d7c3 dest 1:

ARC6: Archive log thread 1 sequence 99 available in 1 minute(s)

Media Recovery Delayed for 1 minute(s) (thread 1 sequence 99)

2019-04-15 15:01:01.067000 +10:00

RFS[1]: No standby redo logfiles available for T-1

RFS[1]: Opened log for thread 1 sequence 101 dbid 3526761822 branch 1005127022

Archived Log entry 778 added for thread 1 sequence 100 ID 0xd2f7d7c3 dest 1:

ARC7: Archive log thread 1 sequence 100 available in 1 minute(s)

To revert (switch back) to the previous situation, perform the same action. Remember, now, your primary is your previous STANDBY and standby is previous PRIMARY. 



TSTDB_1 - up and running

TSTDB_2 - down



TSTSBY is primary


SQL> alter database switchover to TSTDB verify;

alter database switchover to TSTDB verify

*

ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details

Online log +FLASH/TSTDB/ONLINELOG/group_23.4033.1004695265: Thread 2 Group 23 was previously cleared

Online log +DATA/TSTDB/ONLINELOG/group_24.525.1004695265: Thread 2 Group 24 was previously cleared

Online log +FLASH/TSTDB/ONLINELOG/group_24.1681.1004695265: Thread 2 Group 24 was previously cleared

Standby became primary SCN: 14048950

Switchover: Complete - Database mounted as primary

SWITCHOVER: completed request from primary database


SQL> alter database switchover to TSTDB;


Database altered.


SQL>


TSTDB


SQL> alter database open;


Database altered. 

ALTER SYSTEM SET log_archive_dest_2='service="TSTSBY"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSBY" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH ;



stop


srvctl stop database -d TSTDB


start



srvctl start database -d TSTDB


DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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



Welcome to DGMGRL, type "help" for information.

DGMGRL> connect /

Connected as SYSDG.

DGMGRL> show configuration



Configuration - TSTDBCONF



  Protection Mode: MaxPerformance

  Members:

  TSTDB  - Primary database

    TSTSBY - Physical standby database



Fast-Start Failover: DISABLED



Configuration Status:

SUCCESS   (status updated 59 seconds ago)



DGMGRL>

OEM 13c Blackout Stuck in 'Stop Pending' Status

issue was started due to two blackout were created DR databases 

stopped the Blackout job manually

OEM Blackout was hug state 

Workaround fix the problem 

Solution 

create  balackout for the DR database again for 10 minutes - wait to end blackout automatically 

 

all good now 

SWITCHOVER ERROR: Switchover target couldn't convert to primary database due to error 1275

alter database switchover to TSTSBY

Mon Apr 08 12:04:40 2019

Starting switchover [Process ID: 19008]

Mon Apr 08 12:04:40 2019

SWITCHOVER ERROR: Switchover target couldn't convert to  primary database due to error 1275.

Check switchover target's alert log for detailed error.

USER (ospid: 19008): terminating the instance

Mon Apr 08 12:05:53 2019

Instance terminated by USER, pid = 19008

ORA-16471 signalled during: alter database switchover to TSTSBY.

SQL> SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID

---------------- --------- ---------- ------------------------------ -----------

OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE

-------------------- ------------ ------------------ --------------------

PROTECTION_LEVEL

--------------------

PHYSICAL STANDBY TSTDB     3526761822 TSTDB                                   13

READ ONLY            ARCHIVELOG   YES                MAXIMUM PERFORMANCE

MAXIMUM PERFORMANCE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

ROLE             NAME            DBID DB_UNIQUE_NAME                 PLATFORM_ID

---------------- --------- ---------- ------------------------------ -----------

OPEN_MODE            LOG_MODE     FLASHBACK_ON       PROTECTION_MODE

-------------------- ------------ ------------------ --------------------

PROTECTION_LEVEL

--------------------

PHYSICAL STANDBY TSTDB     3526761822 TSTDB                                   13

READ ONLY            ARCHIVELOG   YES                MAXIMUM PERFORMANCE

MAXIMUM PERFORMANCE

SQL> select db_unique_name,open_mode,database_role from gv$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

TSTDB                          READ ONLY            PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.0737E+10 bytes

Fixed Size                  6081440 bytes

Variable Size            7818183776 bytes

Database Buffers         2885681152 bytes

Redo Buffers               27471872 bytes

Database mounted.

SQL> select db_unique_name,open_mode,database_role from gv$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

TSTDB                          MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

SQL> select db_unique_name,open_mode,database_role from gv$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

TSTDB                          READ WRITE           PRIMARY

SQL>

Oracle Standby database

Shutting Down a Physical Standby Database

Issue the following query to find out if the standby database is performing Redo Apply or real-time apply. 

If the MRP0 or MRP process exists, then the standby database is applying redo.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

If Redo Apply is running, cancel it as shown in the following example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shut down the standby database.

SQL> SHUTDOWN IMMEDIATE;

Start and mount the physical standby database:

SQL> STARTUP MOUNT;

Start Redo Apply or real-time apply:

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION;

To start real-time apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE;

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

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

SELECT   name, db_unique_name, log_mode,   protection_mode, database_role FROM v$database;

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;

SELECT client_process, process, thread#, sequence#, status FROM v$managed_standby  WHERE client_process='LGWR' or process='MRP0' ORDER BY process;

Before getting into the real action, validate the following to avoid any failures during the course of role transition:

Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases

Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database

Create STANDBY REDO logs on PRIMARY, if not exists

FAL_CLIENT & FAL_SERVER parameters set on both databases

In Primary site:

FAL_SERVER=STANDBY

FAL_CLIENT=PRIMARY

In Standby site:

FAL_SERVER=PRIMARY

FAL_CLIENT=STANDBY

ALTER SYSTEM SET FAL_SERVER=TSTSB SCOPE=BOTH SID='*';

ALTER SYSTEM SET FAL_CLIENT=TSTSBY SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TSTDB,TSTSBY)' SCOPE=BOTH SID='*';

alter system set log_archive_config='dg_config=(TSTDB,TSTSBY)' scope=both

FAL_SERVER specifies the FAL (fetch archive log) server for a standby database.The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.

FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER parameter, to refer to the FAL client. 

Verify TEMP tablespaces on STANDBY, add them if required, as TEMPFFILES created after STANDBY creation won’t be propagated to STANDBY site.

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

SQL> -- query on standby database

SQL> select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

SQL> -- query on physical database

SQL> select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

select status , error from v$archive_dest where dest_id=2;

alter database switchover to TSTSBY verify; 

alter system set log_archive_dest_state_2=enable scope=both;

start apply

alter database recover managed standby database disconnect from session;

  alter system switch logfile;

 select dest_id,error,status from v$archive_dest where dest_id=2;

 select max(sequence#),thread# from v$log_history group by thread#;

 select max(sequence#)  from v$archived_log where applied='YES' and dest_id=2

select group#, thread#, bytes/1024/1024 "Size in MB" from v$Log;

select group#, type from v$logfile;

ALTER SYSTEM SET log_archive_dest_2='service="tstsby"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSBY" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TSTSBY ASYNC NOAFFIRM VALID_FOR=(online_logfile,all_roles) DB_UNIQUE_NAME=TSTSBY' scope=both;

alter system set log_archive_config='dg_config=(TSTDB,TSTSBY)' scope=both

SWITCHOVER VERIFY: Send VERIFY request to switchover target GGSTBY

SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.

ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO GGSTBY VERIFY…

2017-09-03T08:16:47.465995-04:00

# this fixed the warning by doing this on the PRIMARY -TSTSB

alter system set log_archive_dest_2='service="TSTSBY"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSBY" net_timeout=30','valid_for=(online_logfile,all_roles)';

# for reference the standby must be defined as a DEST_2 as follows: -standby

alter system set log_archive_dest_2='service="TSTSB"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="TSTSB" net_timeout=30','valid_for=(online_logfile,all_roles)';

explan plan

select * from table(dbms_xplan.display_cursor('2hbdag3dfzj9u'));
	select * from table(dbms_xplan.display_cursor('sqlid'));
	-- For more details and generally excellent report (11g only)
	set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
	select DBMS_SQLTUNE.REPORT_SQL_MONITOR('sqlid') from dual;