Switchover and Switchback with 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.

• 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

SQL> alter database prepare to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

—————————-

TO PRIMARY

• 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.

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;

SQL>

• 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.

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;

• Start the Logical Standby apply process.

SQL> alter database start logical standby apply immediate;

Database altered.

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

• 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

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

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

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

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