Switchover In Logical Standby Database Without Using DGMGRL

On Primary Database:

Check the switchover status and of the  Primary Database.


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

-----------------------------

TO STANDBY


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


NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE

--------- ------------------------------ ---------------- --------------------

primdb      primdb              PRIMARY      READ WRITE


Issue the below command to prepare the Primary Database to switchover to logical standby.

--> alter database prepare to switchover to logical standby;


SQL> alter database prepare to switchover to logical standby;

Database altered.

SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

-----------------------------

PREPARING SWITCHOVER


Here the switchover status is PREPARING SWITCHOVER. Change the switchover status of Logical standby database and Wait for some time so that the status of primary database will change as TO LOGICAL STANDBY.


On Logical Standby Database
primdb

Check the switchover status and database role of the database.


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

----------------------------

NOT ALLOWED


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


NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE

---------    ------------------------------ ---------------- --------------------

stddby   stddby             LOGICAL STANDBY  READ WRITE


Issue the command to prepare the Logical standby database to switchover to Primary Database.


--> alter database prepare to switchover to logical standby;


SQL> alter database prepare to switchover to primary;

Database altered.


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS
----------------------------

TO PRIMARY


On Primary Database

Now the check the switchover status of primary database. The status will be changed as TO LOGICAL STANDBY.


SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------

TO LOGICAL STANDBY

Now switchover primary database to Logical Standby using the  below command.


--> alter database commit to switchover to logical standby;


SQL> alter database commit to switchover to logical standby;

Database altered.


Now check the database role of the primary database. It will be changed to Logical Standby.


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

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE

--------- ------------------------------ ---------------- --------------------

primdb      primdb              LOGICAL STANDBY  READ WRITE


On Logical Standby Database

Now check the switchover status of Logical Standby Database.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

----------------------------

TO PRIMARY


Switchover the  Logical standby database to Primary using the below command.


--> alter database commit to switchover to primary;


SQL> alter database commit to switchover to primary;

Database altered.

Check the database role of the Logical Standby database. It will be changed as Primary.


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


NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE

--------- ------------------------------ ---------------- --------------------

stddby   stddby             PRIMARY      READ WRITE


On Primary Database

Start the Logical Standby apply process.


SQL> alter database start logical standby apply immediate;

Database altered.


On Logical Standby Database (Current Primary Database)

Insert a row in a table and check whether it has been replicated to Primary Database(Currently Logical Standby).


SQL> select * from scott.t1;

    NO
----------

     2

     1

     3

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

1 row created.


SQL> commit;

Commit complete.

SQL> select * from scott.t1;


    NO

----------

     2

     1

     3

     4


On Primary Database (Current Logical Standby Database)

Check the replication to Primary Database(Current Logical Standby).


SQL> select * from scott.t1;

    NO

----------

     2

     1

     3


SQL> select * from scott.t1;


    NO

----------

     2

     1

     3

     4

The same thing has to be followed for Switchback.


SWITCHBACK TO OLD ROLE


On Logical Standby Database(Current Primary Database)

SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

-----------------------------

TO STANDBY


SQL> alter database prepare to switchover to logical standby;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------

PREPARING SWITCHOVER


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

----------------------------
TO LOGICAL STANDBY


SQL> alter database commit to switchover to logical standby;

Database altered.


SQL> select switchover_status from v$database;


SWITCHOVER_STATUS

----------------------------

NOT ALLOWED


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

OPEN_MODE         DATABASE_ROLE    NAME

-------------------- ---------------- ---------

READ WRITE         LOGICAL STANDBY  stddby


SQL> alter database start logical standby apply immediate;

Database altered.


SQL> archive log list

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /oradata3/stddby/archives

Oldest online log sequence     17

Next log sequence to archive   19

Current log sequence           19


SQL> select * from scott.t1;

    NO

----------

     2

     1

     3

     4


SQL> select * from scott.t1;

    NO

----------

     2

     5

     1

     3

     4



On primary Database (Current Logical Standby Database)

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

----------------------------

NOT ALLOWED


SQL> archive log list

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /oradata2/primdb/archives

Oldest online log sequence     33

Next log sequence to archive   35

Current log sequence           35

SQL> alter database prepare to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------

PREPARING SWITCHOVER


SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

----------------------------

TO PRIMARY


SQL> alter database commit to switchover to primary;

Database altered.


SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

----------------------------

LOG SWITCH GAP


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

OPEN_MODE         DATABASE_ROLE    NAME

-------------------- ---------------- ---------

READ WRITE         PRIMARY          primdb

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------

LOG SWITCH GAP

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /oradata2/primdb/archives

Oldest online log sequence     40

Next log sequence to archive   42

Current log sequence           42

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

----------------------------

TO STANDBY


SQL> select * from scott.t1;

    NO
----------

     4

     3

     1

     2

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