Skip to main content
Logical Standby

Switchover and Switchback with Logical Standby

By October 26, 2014September 12th, 2016No Comments

SWITCHOVER IN LOGICAL STANDBY

DATABASE – Oracle 11gR2 11.2.0.3.0

Operating System – Oracle Enterprise Linux

Primary Database  – PRODDB

Logical Standby Database – LOGSTDB

On Primary Database:

  • Check the switchover status and role 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

——— ——————— ——————-   ——————–

proddb  proddb  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

  • 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

——— ——————-    ——————–   —————- 

logstdb   logstdb         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

——— ——————-    ——————–   —————- 

proddb   proddb         LOGICAL STANDBY   READ WRITE

SQL>

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

——— ——————-    ——————–   —————- 

logstdb   logstdb         PRIMARY                READ WRITE

On Primary Database

  • Start the Logical Standby apply process.

alter database start logical standby apply immediate;

SQL> alter database start logical standby apply immediate;

Database altered.

  • Check the Replication between the New Primary and Switched Logical Standby Database.

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

SQL>

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

*****************************************

SAME 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

  • Prepare the Database for switchover.

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

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

—————————-

TO LOGICAL STANDBY

  • Now Switchover the role of Current primary database to logical standby.

alter database commit to switchover 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    logstdb

  • Start the Logical Standby apply process.

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/logstdb/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

On primary Database (Current Logical Standby Database)

  • Prepare the Database for switchover.

alter database prepare to switchover to primary;

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/proddb/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

  • Now Switchover the role of Logical Standby database to Primary Database.

alter database commit to switchover to primary;

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

—————————–

LOG SWITCH GAP

  • Check the role and switchover status of the Primary database.

select switchover_status from v$database;

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

OPEN_MODE     DATABASE_ROLE  NAME

—————   ——————  ——–

READ WRITE    PRIMARY       proddb

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/proddb/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

Note: logfile is switched to change the switchover status of the Primary Database.

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

On Logical Standby Database

SQL> select * from scott.t1;

NO

———-

 2

 5

 1

 3

 4

Leave a Reply