Data Guard Switchover Notes

old primary site

SQL>select switchover_status from v$database;

quick procedure for switchover.

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

SQL>shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL>alter system set log_archive_dest_state_2=defer;

old standby site

SQL>select switchover_status from v$database;

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate

SQL>startup

old primary site

SQL>recover managed standby database disconnect

How to check GAP? (run at both side (primary/standby)

 select status, gap_status

    from v$archive_dest_status

    where dest_id = 2;

how to check delay?

select delay_mins from v$archive_dest where dest_id = 2;

how to check switchover status from primary db?

select switchover_status from v$database;

how to verify active session at primary db?

select sid, process, program

    from v$session where type = 'USER';

pre-req for switchover.

1. check gap between both side

2. check tempfile name should be same at both side

3. check delay between both side.

4. verify primary db can be switch to standby (switchover_status should return to standby)

5. safe side verify the active session at primary db

alter database commit to switchover to standby; (on primary db)

6.command to switchover (perform on standby db)

 alter database commit to switchover to primary with session shutdown; ( acitve sesion is there)

 alter database commit to switchover to primary; (without active session)

Manually switch over to the standby database

Manually switch over of dataguard configuration without dataguard broker

1. Verify the Physical Standby Database Is Performing Properly
Step 1 Identify the existing archived redo log files on strandby

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

Step 2 Force a log switch to archive the current online redo log file on primary
SQL>ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 4 Verify that received redo has been applied on standby
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

The value of the APPLIED column for the most recently received log file will be
either IN-MEMORY or YES if that log file has been applied.

2. Pre-Switchover Checks
Verify Managed Recovery is Running (non-broker) on the standby
SQL>select process from v$managed_standby where process like 'MRP%';

Cancel apply delay for the target standby using SQL
SQL>select delay_mins from v$managed_standby where process = 'MRP0';

if delay_mins >0 then
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Verify there are no large GAPS.
On primary
SQL>SELECT THREAD#, SEQUENCE# FROM V$THREAD;
On the standby the following query should be no more than 1-2 less than the primary query result
SQL>SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG val, V$DATABASE vdb
WHERE APPLIED = 'YES' AND val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE#GROUP BY THREAD#;
If there is a gap .. fix the gap

Verify Primary and Standby TEMP Files Match
SQL>select tmp.name FileName, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;
If there is no match then you will correct the temp tbs problem after switchover

Verify that there is no issue with V$LOG_HISTORY on the Standby (bug 6010833)
1. Get the Primary RESETLOGS_CHANGE#. On the primary execute:
SQL>select RESETLOGS_CHANGE# from V$DATABASE;

2. On the standby get the maximum sequence numbers for each thread from V$LOG_HISTORY:
SQL>select thread#, max(sequence#) from V$LOG_HISTORY where resetlogs_change#=1 group by thread#;

3. Get current sequence numbers on standby:
SQL>select thread#, max(sequence#) from v$archived_log alog, v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;

4. The last sequence# for each thread# from V$LOG_HISTORY should be close (the difference in log sequences < 3) to the last sequence# for each thread# from V$ARCHIVED_LOG.

5. If there is an issue with V$LOG_HISTORY then recreate the standby controlfile

Verify no old partial Standby Redo Logs on the Standby (bug 7159505)
1. Identify any active standby redo logs (SRL’s)
SQL>SELECT GROUP#, THREAD#, SEQUENCE# FROM V$STANDBY_LOG WHERE STATUS = 'ACTIVE' ORDER BY THREAD#,SEQUENCE#;

2.Identify maximum applied sequence number(s).
SQL> select thread#, max(sequence#)
from V$LOG_HISTORY
where resetlogs_change#=<>
group by thread#;

3.If there are any active SRL's that have a thread#/sequence# less than the thread#/sequence# returned from the V$LOG_HISTORY (meaning the recovery has progressed beyond the active SRL) query then clear them.
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE CLEAR LOGFILE GROUP ;

3 Switchover
Clear Potential Blocking Parameters & Jobs
1.Capture current job state on the primary
SQL>select * from dba_jobs_running;
SQL>select owner,job_name, start_date, end_date, enabled from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS';
SQL>show parameter job_queue_processes -- and capture the value

Block further job submission
SQL>alter system set job_queue_processes=0 scope=both;
SQL>execute dbms_scheduler.disable(job_name);

Disable any cron jobs that may interfere , rman backups
Shutdown all mid-tiers (e.g db console)
$ emctl stop dbconsole

Monitor Switchover
Turn on Data Guard tracing on primary and standby
Tracing is turned on to have diagnostic information available in case any issues arise.
SQL>show parameter log_archive_trace -- capture the value
Set Data Guard trace level to 8191
SQL>alter system set log_archive_trace=8191;
Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.

Monitor alert log for both primary and standby

Finally Switchover
Verify that the primary database can be switched to the standby role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
A value of TO STANDBY or SESSIONS ACTIVE (requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role

Switchover the primary to a standby database
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
in primary alert log you would see

Switchover: Complete - Database shutdown required (sfs_stby1)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN

Verify that the standby database can be switched to the primary role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Check if the standby has ever been open read-only
SQL> SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME='standby has been open';

If the standby was open read-only then restart the standby
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

Switchover the standby database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Open the new primary database:
SQL> ALTER DATABASE OPEN;
Correct any tempfile mismatch

Restart the new standby
On the the new standby database (old production database), bring it to the mount state and start managed recovery.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Post-Switchover Steps
For each database:
SQL>alter system set log_archive_trace=;
SQL>alter system set job_queue_processes= scope=both;
SQL>execute dbms_scheduler.enable();
Enable any cron jobs that were diabled in 3.1

Check that they are syncronized
Perfom log switch on new primary
Create cron job to backup new primary !!!


Reference
Metalink Note id : 751600.1

Source: Google Search