Oracle Standby Site Plan Switch Over...10g/11g

REM: Prepared By Hayat Mohammad Khan
REM: Senior DBA PTCL, Pakistan
REM: hayathk@hotmail.com
REM: 06-Feb-2012
REM: Use it by your own risk, Author and Oracle is not responsible for any command failure or mal function

Tip: Alert Log file reading is the Key to successful DR Site Switch Over Activity
Tip: Keep Calm, and Wait for the successful Execution of Command, Avoid Hurry Ness.
Tip: Verify the Listener from both sites, tnsping pr-site, tnsping dr-site
Tip: Always take Backup Before Start of Switch Over Activity.
Tip: Keep Your documents in DR-DRILL Folder
Tip: Oracle Erros Details Document especially for Data Guard
Tip: Keep List of Oracle V$ views Related to Data Guard


===================================
SPFILE Verification:::
===================================

PR-SITE::
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=????

SPFILE Verification::
DR-SITE
FAL_SERVER=PRIM
FAL_CLIENT=STAN
STANDBY_FILE_MANAGEMENT=????

Read Below Website for Parameters For Fal_Client & Fal_Server:
http://myoracledba.blogspot.com/2008/08/falserver-falclientdataguard-falclient.html


===================================
GAP Verification:::
===================================

ON-PR-SITE Apply Log Switch::
alter system switch logfile;

ON-DR-SITE Verify::
SQL>select sequence#, applied from v$archived_log order by sequence#;



############################################################################################################################
{{{{{{{ Pl only do this exercise if above things are sure}}}}}}}
############################################################################################################################

===================================
SWITCH OVER:::
===================================

You Must Verify:

1. PR Site Instance in Open, DR Site Instance in Mount Stage
2. NO Active users connected to the databases
3. Make sure the last redo data transmitted from the Primary database was applied
on the standby database
select sequence#, applied from v$archvied_log;
Perform Log Switch from Priamry to verify at DR Site
4. Execute before and after and Document Below Query Results>>
 select database_role, open_mode, protection_mode, db_unique_name from v$database;
5. Always cancel recovery at DR Site




Please Verify You have Off Other DB Instances (If ORACLE RAC is there), Stop them

Example
[DB-Name: R3P, Instance Name R3P002..... pl replace for your environment]

$ srvctl stop instance -d R3P -i R3P002


Switchover Steps

********************
SPRSite-1.
********************

Connect to PR Site

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
VEIRFY STATUS - IF FAILED DESTINATIONS returned DON'T PROCEED  [very very important], VERIFY CONNECTIIVTY TO DR ........ and GAP....

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Note: Pl see Alert Log Entry in teminal sessions, tail -f aler*

********************
SDRSITE-2.
********************

If step# SPRSite-1 Completed successfully


----------------------------------------------------------------------------------
Connect to DR Site
----------------------------------------------------------------------------------
cancel any recovery if active

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

Note: Pl see Alert Log Entry in teminal sessions,  tail -f aler*

----------------------------------------------------------------------------------
Once Enter SQL Command on DR Site for becoming Priamry,
Enter immediately Below Command on PR SITE
----------------------------------------------------------------------------------
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;


********************
SPRSITE-3.   [new primary site]
********************

Recommended to Perform this action on NEW Priamery (Karachi Site)

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

oR

SQL>ALTER DATABASE OPEN;

********************
SPRDRSITE-3.
********************

vERIFY THE DB Mode

-----------
PR SITE
-----------

SQL> select open_mode from v$database;
select database_role, open_mode, protection_mode, db_unique_name from v$database;

OUTPUT>>>READ WRITE

-----------
DR SITE
-----------

SQL> select open_mode from v$database;
select database_role, open_mode, protection_mode, db_unique_name from v$database;

OUTPUT>>>MOUNT


********************
SPRSITE-4.
********************

SQL>ALTER SYSTEM SWITCH LOGFILE;

VERIFY GAP, and Applying Status.


********************
SPRSITE-5.
********************

Auto Redo Apply at New DR Site

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








===============================
Appendix A
==============================

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;
SELECT * from v$dataguard_status order by timestamp desc
SELECT status, pid, sequence# from v$managed_standby where process like 'MRP%';
SELECT * FROM V$ARCHIVE_GAP;
SELECT * FROM $DATABASE
SELECT * FROM V$LOG_HISTORY

--Determining Which Log Files Were Not Received by the Standby Site
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
 THREAD# = LOCAL.THREAD#);

alter system set log_archive_dest_state_2='defer' scope=both sid='*';

--- Determine the most recent archived redo log file at each destination
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
 FROM V$ARCHIVE_DEST_STATUS
 WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

alter database register or replace physical logfile '/ccsarch/1_339657_720905336.dbf';


===============================
Appendix B
==============================

TNS ENTRIES AT PR SITE

TNS ENTRIES AT DR SITE


V$SERVICES OUTPUT ATTACHED

RECOVER STANDBY DATABASE;

ALTER SYSTEM ARCHIVE LOG Current

alter database recover managed standby database disconnect from session;

recover managed standby database disconnect;

alter database recover managed standby database cancel;

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Thread 2:: select sequence#, applied from v$archived_log where thread#=2 and sequence#='xxxxxx';

select sequence#, applied from v$archived_log where applied='NO' order by sequence#

select sequence#, applied from v$archived_log where applied='YES' order by sequence#

select * from v$dataguard_status;

select process,status,THREAD#,SEQUENCE# from v$managed_standby;

select THREAD#,SEQUENCE#,APPLIED from v$archived_log where THREAD#=2 and SEQUENCE# BETWEEN 342340 AND 342450 ORDER BY 3;

select 'APPLIED', max(sequence#), thread# from v$archived_log where APPLIED='YES'group by thread#
union all
select 'ARCHIVED', max(sequence#),thread# from v$archived_log where archived='YES'group by thread# order by 3

select timestamp , facility, dest_id, message_num, error_code, message from v$dataguard_status order by timestamp;

Select process, status, thread#, sequence#, blocks, delay_mins from
v$managed_standby
where process = 'MRP0';

select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';


-- At PR
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;


-- At DR
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

--- At DR

select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES'
group by thread# order by 1;