Logical Standby Database Switchover Using DGMGRL

Primary Database    - proddb
Logical Standby      -  stddb
DGMGRL config    - dg_logical

Primary Database:
SQL> select name,db_unique_name,open_mode,database_role,flashback_on from v$database;

NAME      DB_UNIQUE_NAME         OPEN_MODE          DATABASE_ROLE    FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
proddb      proddb              READ WRITE          PRIMARY           YES

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /oradata2/proddb/archives
Oldest online log sequence     55
Next log sequence to archive   57
Current log sequence           57

Logical Standby Database:

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

NAME      DB_UNIQUE_NAME         OPEN_MODE          DATABASE_ROLE    FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
stddb   stddb             READ WRITE          LOGICAL STANDBY  YES

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /oradata3/stddb/archives
Oldest online log sequence     38
Next log sequence to archive   40
Current log sequence           40

Replication Testing Before Switchover

On Primary Database:
SQL> select * from scott.t1;

    NO
----------
     1
     2

2 rows selected.

SQL> insert into scott.t1 values(3);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from scott.t1;

    NO
----------
     3
     1
     2

3 rows selected.

On Standby Database:
SQL> select * from scott.t1;

    NO
----------
     2
     1
     3

3 rows selected.

Switchover using DGMGRL
Primary Database    - proddb
Logical Standby      - stddb
DGMGRL config    - dg_logical

1. Login to the DGMGRL prompt.
[oracle@nagpur bin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@proddb
Connected.

DGMGRL> show configuration
Configuration - dg_logical

  Protection Mode: MaxPerformance
  Databases:
    proddb   - Primary database
    stddb - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Since for switchover there is no requirement for Fast-Start Failover  it has been disabled.

2. To switchover primary database to Logical Standby database issue the below command.

switchover to stddb;

DGMGRL> switchover to stddb;
Performing switchover NOW, please wait...
Switchover succeeded, new primary is "stddb"

From the above spool we can clearly understand that the primary database has been switched over to Logical Standby.

3. Check the show configuration after switchover.

DGMGRL> show configuration
Configuration - dg_logical

  Protection Mode: MaxPerformance
  Databases:
    stddb - Primary database
    proddb   - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

4. Check the role of the primary and logical standby database.
On Primary Database:
SQL> select name,db_unique_name,open_mode,database_role,flashback_on from v$database;

NAME      DB_UNIQUE_NAME         OPEN_MODE          DATABASE_ROLE    FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
proddb      proddb              READ WRITE          LOGICAL STANDBY  YES

On Logical Standby Database:
SQL> select name,db_unique_name,open_mode,database_role,flashback_on from v$database;

NAME      DB_UNIQUE_NAME         OPEN_MODE          DATABASE_ROLE    FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
stddb   stddb             READ WRITE          PRIMARY           YES

Replication Testing After SwitchoverOn Logical Standby Database (Now Primary):
SQL> select * from scott.t1;

    NO
----------
     2
     1
     3

3 rows selected.

SQL> insert into scott.t1 values(4);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from scott.t1;

    NO
----------
     2
     1
     3
     4

4 rows selected.

On Primary Database (Now Logical Standby):
SQL> select * from scott.t1;

    NO
----------
     2
     1
     3
     4

4 rows selected.

Switchback using DGMGRL
Primary Database    - stddb
Logical Standby     - proddb
DGMGRL config          - dg_logical

1. Login to the DGMGRL prompt.
[oracle@nagpur bin]$ export ORACLE_SID=proddb
[oracle@nagpur bin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@proddb
Connected.

DGMGRL> show configuration
Configuration - dg_logical

  Protection Mode: MaxPerformance
  Databases:
    stddb - Primary database
    proddb   - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Since for switchback there is no requirement for Fast-Start Failover  it has been disabled.

2. To switchover primary database to Logical Standby database issue the below command.
switchover to proddb;

DGMGRL> switchover to proddb;
Performing switchover NOW, please wait...
Switchover succeeded, new primary is "proddb"

From the above spool we can clearly understand that the primary database has been switched over to Logical Standby.

3. Check the show configuration After switchback:
DGMGRL> show configuration
Configuration - dg_logical

  Protection Mode: MaxPerformance
  Databases:
    proddb   - Primary database
    stddb - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

4. Check the role of the primary and logical standby database.
On Primary Database:
SQL> select name,db_unique_name,open_mode,database_role,flashback_on from v$database;

NAME      DB_UNIQUE_NAME         OPEN_MODE          DATABASE_ROLE    FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
proddb      proddb              READ WRITE          PRIMARY           YES

On Logical Standby Database:
SQL> select name,db_unique_name,open_mode,database_role,flashback_on from v$database;

NAME      DB_UNIQUE_NAME         OPEN_MODE          DATABASE_ROLE    FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
stddb   stddb             READ WRITE          LOGICAL STANDBY  YES

Replication Testing After Switchover
On Primary Database:
SQL> select * from scott.t1;

    NO
----------
     4
     3
     1
     2

4 rows selected.

SQL> insert into scott.t1 values(5);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from scott.t1;

    NO
----------
     4
     3
     1
     2
     5

5 rows selected

On Logical Standby Database:
SQL> select * from scott.t1;

    NO
----------
     2
     5
     1
     3
     4

5 rows selected.