ORACLE Fail over – Standby Becomes Primary
# Failover – Standby Becomes Primary
End all activities on the standby database.
May need to resolve redo log gaps (not shown here).
On the standby: SQL> alter database recover managed standby database finish;
SQL> alter database commit to switch over to primary;
SQL> shutdown immediate
Change tns names.ora entry on all servers to point the primary connect string to the standby database.
New standby needs to be created. Old primary is no longer functional.
Monitoring Standby Database
select count(*) from v$archive_gap;
This query detects gaps in the logs that have been received. If any rows are returned by this query then there is a gap in the sequence numbers of the logs that have been received.
This gap must be resolved before logs can be applied.
SELECT decode(count(*),0,0,1) FROM v$managed_standby WHERE (PROCESS=’ARCH’ AND STATUS NOT
IN (‘CONNECTED’)) OR (PROCESS=’MRP0′ AND STATUS NOT IN (‘WAIT_FOR_LOG’,’APPLYING_LOG’))
OR (PROCESS=’RES’ AND STATUS NOT IN (‘IDLE’,’RECEIVING’));
This query detects bad statuses. When a bad status is present this query will return a “1″.
The ‘ARCH’ process should always be ‘CONNECTED’. The ‘MRP0′ process should always be waiting for a log or applying a log, and when this is not true it will report the error in the status. The ‘RFS’ process exists when the Primary is connected to the Standby and should always be ‘IDLE’ or ‘RECEIVING’.
SELECT DECODE(COUNT(DISTINCT PROCESS),3,0,1)FROM v$managed_standby;
This query detects missing processes. If we do not have exactly 3 distinct processes then there is a problem, and this query will return a “1″.
The most likely process to be missing is the ‘RFS’ which is the connection to the Primary database. You must resolve the problem preventing the Primary from connecting to the Standby before this process will start running again.
# Verify all STANDBY PROCESSES are running normally on the STANDBY database.
SELECT PROCESS,STATUS,RESET LOG_ID,SEQUENCE#,ACTIVE_A GENTS FROM V$MANAGED_STANDBY ;
A query with good results follows proving all processes are connected with normal statuses.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG WHERE FIRST_TIME >TRUNC (SYS DATE)ORDER BY SEQUENCE#;