ORA-16139 Physical standby with a switchover status of “NOT ALLOWED”

ORA-16139 Physical standby with a switchover status of “NOT ALLOWED”

The status on the standby (being switched to a primary) was as follows:

SQL> SELECT database_role, switchover_status FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
PHYSICAL STANDBY NOT ALLOWED

The alert log indicated that not all logs had been applied to this standby before it was converted to a primary:


Switchover: Media recovery required - standby not in limbo
ORA-16139 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN...
At this point we brought up the database as a standby with the below commands:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> alter database recover managed standby database finish force;

This caused the last archive logs from the primary to be applied and the following to be displayed in the alert log:

Attempt to do a Terminal Recovery
Media Recovery Start: Managed Standby Recovery

Attempt to set limbo arscn 0:115809474 irscn 0:115809474
Completed: alter database recover managed standby database finish force
After the above, we issued:

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

And the alert log indicated that it was successful:

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

SQL> ALTER DATABASE SWITCHOVER TO PRIMARY

Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with session shutdown
At this point the database was in the below mode and switchover status:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
MOUNTED              PRIMARY          NOT ALLOWED
We then enabled the log_archive_dest_state_2 as this was now the primary and issued the below commands:

SQL> shutdown immediate;

SQL>  startup;
The database was now in the below mode and switchover status:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
READ WRITE           PRIMARY          RESOLVABLE GAP
After a couple of minutes, the status changed to:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
READ WRITE           PRIMARY          SESSIONS ACTIVE
The standby status was as follows:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
MOUNTED              PHYSICAL STANDBY NOT ALLOWED

This is an expected status as per “Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1)”. After the command to switch roles is issued on the primary, the primary will generate a special marker called EOR (end-of-redo) that is placed in the header of online redo log sequence. So this online redo log sequence will be archived locally and sent to all standby databases. Only upon receiving and applying EOR (end-of-redo), v$database.switchover_status will change from “not allowed” to “to primary” or “sessions active”.

DR Dril

GRACEFUL SWITCH OVER:
++++++++++++++++++++++++

verify on Standby and Primary:
==============================

select name,database_role,switchover_status from v$database;
select process from v$managed_standby where process like 'MRP%';
select thread#, max(sequence#) from v$archived_log alog, 
 v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;
 select sequence#,thread#,process,status from gv$managed_standby

select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select sequence#,applied,completion_time from v$archived_log;


PRIMARY: (ORIGINAL PRIMARY)
======================


SWITCHOVER_STATUS (TO STANDBY)

1. Capture current job state on the primary:
select name,database_role,switchover_status from v$database;

SQL> select * from dba_jobs_running; [depending on what the running job is, be ready to terminate]
SQL> select owner,JOB_NAME,STATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION from dba_scheduler_jobs where enabled='TRUE';
SQL> select * from dba_scheduler_running_jobs;
SQL> show parameter job_queue_processes

Note: Cron job candidates to be disabled among others! 

2. Block further job submissions:

SQL> alter system set job_queue_processes=0 scope=both sid=’*’;
SQL> execute dbms_scheduler.disable(job_name);

3. Disable any cron jobs that may interfere if any
. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database, for example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

4. Shutdown all but one database instance.

 5. Switch the current primary over to the standby role.
alter database commit to switchover to physical standby with session shutdown;

6. Mount the database & put in Managed Mode.
shutdown immediate;

startup nomount;

alter database mount standby database;

recover managed standby database using current logifile disconnect;

SELECT SWITCHOVER_STATUS,database_role FROM V$DATABASE

SWITCHOVER_STATUS (TO PRIMARY)

Standby:
======================


7. Initiate the switchover:

alter database commit to switchover to primary with session shutdown;

8. Next, open all of the database instances:

ALTER DATABASE OPEN;

9. Enable database job submissions:

ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH SID='*';

Ensure that the new primary and standby are syncing properly by reviewing the alert logs and doing some log switches


============================================================================================================

GRACEFUL SWITCH BACKU:
++++++++++++++++++++++++

SWITCHOVER_STATUS (TO STANDBY)

verify:
========


1. Capture current job state on the Standby:
select max(sequence#) from v$archived_log;

select max(sequence#) from v$log_history; ----Primary

select sequence#,applied,completion_time from v$archived_log; -----> Standby

select name,database_role,switchover_status from v$database;
select process from gv$managed_standby where process like 'MRP%';
select thread#, max(sequence#) from v$archived_log alog, 
 v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;
 select sequence#,thread#,process,status from gv$managed_standby


PRIMARY: (OLD STANDBY)
======================


2. Capture current job state on the primary:
select name,database_role,switchover_status from v$database;

SQL> select * from dba_jobs_running; [depending on what the running job is, be ready to terminate]
SQL> select owner,JOB_NAME,STATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION from dba_scheduler_jobs where enabled='TRUE';
SQL> select * from dba_scheduler_running_jobs;
SQL> show parameter job_queue_processes

Note: Cron job candidates to be disabled among others! 

2. Block further job submissions:

SQL> alter system set job_queue_processes=0 scope=both sid=’*’;
SQL> execute dbms_scheduler.disable(job_name);

3. Disable any cron jobs that may interfere if any
. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database, for example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

4. Shutdown all but one database instance.

5. Switch the current primary over to the standby role.

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

6. Mount the database & put in Managed Mode.
shutdown immediate;

startup nomount;

alter database mount standby database;

recover managed standby database using current logifile disconnect;

SELECT SWITCHOVER_STATUS,database_role FROM V$DATABASE

SWITCHOVER_STATUS (TO PRIMARY)
Standby;( OLD PRIMRY)
======================


7. Initiate the switchover:
select name,database_role,switchover_status from v$database;
alter database commit to switchover to primary with session shutdown;

8. Next, open all of the database instances:

ALTER DATABASE OPEN;

9. Enable database job submissions:

ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH SID='*';

Ensure that the new primary and standby are syncing properly by reviewing the alert logs and doing some log switches

Datagaurd Archive gap

How to check archivelog gap?

-- On STANDBY, open_mode should be set to MOUNTED at least:

SELECT log_mode, open_mode, database_role from v$database;

-- On STANDBY, MRP0 process should show "Applying_log":

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

select process, client_process, sequence#, status from V$managed_standby;

-- On STANDBY, determine if there are any archived log gaps
-- Query should return no rows:

Select * From v$archive_gap;

--  On STANDBY, compare last applied log file against last received archived log
--  Difference should not be greater than 2.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


set echo off
conn /  as sysdba
set echo on
-- On PRIMARY, list the sequence#s of the logs that were last archived.
-- These should match "Last seq received" from previous query:

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

-- On PRIMARY, find out which logs where not shipped to standby.
-- The query should return no rows:

Select thread#,sequence#
From v$archived_log a
 where dest_id=1
and a.sequence# >
     (select min(b.sequence#)-1 from v$archived_log b
        where b.dest_id=2
          and b.thread#=a.thread#)
Minus
Select c.thread#,c.sequence#
From v$archived_log c where c.dest_id=2;

DR SYNC STEPS;;;

1. Restore/Copy the archives to the location on standby

If archive logs are in ASM copy to file system and then to standby

RMAN> copy archivelog '+FLASH_DG/PRD/ARCHIVELOG/2012_09_02/thread_3_seq_93970.9317.792907937' to '/u01/rman01/prd/incbkp/thread_3_seq_93970.9317.792907937';

2.Register the archives with the database(alter database register logfile '/u01/rman01/prd/incbkp/thread_3_seq_93970.9317.792907937';)

3.alter database recover managed standby database disconnect;----will apply archives

4.select max(sequence#) from v$archived_log where applied='YES';(the count should be increasing)