Manual Switchover Standby Database In Oracle 10g

Manual Switchover Standby Database



switchover is a process of dataguard whether we can switch roles of a primary database to standby and a standby database to primary at runtime without loss of data and vice-versa can also be done after performing another switchover.

Manual switch over of dataguard configuration without dataguard broker:

PRE-CHECKS:
First verify that Data Guard is working properly or not using following steps:

–Check the redo log current status on primary and standby both:
SQL> select SEQUENCE#, ARCHIVED, APPLIED, FIRST_TIME, NEXT_TIME from V$ARCHIVED_LOG order by SEQUENCE#;

–Switch log file on primary database:
SQL>ALTER SYSTEM SWITCH LOGFILE;

— Now check new redo logs created on primary are applied on standby or not by using following command on standby:
SQL> select SEQUENCE#, ARCHIVED, APPLIED, FIRST_TIME, NEXT_TIME from V$ARCHIVED_LOG order by SEQUENCE#;

–Check whether Managed Recovery Process (MRP) is running on the standby and Cancel apply delay for the target standby using SQL:
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,DELAY_MINS FROM V$MANAGED_STANDBY;

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;

–Check gaps on primary and standby using following commands if found then fix the gap:

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#;

–And there should not be any active users connected with database.

SWITCHOVER STEPS:

Monitor alert log for both primary and standby:
–Convert Primary database to standby running following command on primary:

ON PRIMARY:
Connect DB as sysdba:

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
TO STANDBY

Since switchover_status is TO STANDBY then run below to switchover:
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

If switchover_status is SESSION ACTIVE the run below command to switchover:

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

–Now shutdown the primary database:
SQL>Shu immediate;
SQL>startup nomount;
SQL>alter database mount standby database;

–Convert Standby database to Primary running following command on standby:

ON STANDBY:
Connect DB as sysdba:

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
——————–
TO PRIMARY

Since switchover_status is TO PRIMARY then run below to switchover:
SQL>alter database commit to switchover to primary;
 
If switchover_status is SESSION ACTIVE then run below to switchover:
SQL>alter database commit to switchover to primary with session shutdown;

And then bounce the database in open mode:
SQL>shu immediate;
SQL>startup;

–Now run following command on former primary DB to get the database in managed recovery mode:

ON PRIMARY:
Connect DB as sysdba:

SQL>select switchover_status from v$database;
result of this query should be to primary:

SQL>alter database recover managed standby database disconnect from session;

NOTE: Now verify that after switchover, primary and standby are working fine or not using PRE-CHECK commands  as mentioned start of the post and switchover status from v$database as well.

You can skip to the end and leave a response. Pinging is currently not allowed.

2 Responses to “Manual Switchover Standby Database In Oracle 10g”

  1. Mujeebur DBA says:

    when i issue this command SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
    i am getting this error
    ERROR at line 1:
    ORA-00344: unable to re-create online log
    '/u01/app/oracle/oradata/rahman/redo01.log'
    ORA-27040: file create error, unable to create file
    Linux Error: 2: No such file or directory

  2. Please check following in your test environment:

    1.If the paths are not the same on the standby system as they
    are on the primary system then you need to set LOG_FILE_NAME_CONVERT.

    2.check path for log file is set same on both servers or not.
    if not then set LOG_FILE_NAME_CONVERT.

    3.also check set path physically correct in OS or not?

    crosscheck with below command
    select * from v$logfile;

    if not matched then rename it

    4.check ownership and permissions on that directory or path is fine or not?

    5.if above solution does not work then recreate logfiles at correct location.

Leave a Reply