Now we will see a step-by-step approach to perform a switchover between the primary and the logical standby database:
TO STANDBY
or SESSIONS ACTIVE
; if so, you are safe to perform a switchover as shown in the following code:SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
SQL> alter database prepare to switchover to logical standby; Database altered.
On the primary alert log, issue the following command:
Fri Oct 12 08:50:50 2012 alter database prepare to switchover to logical standby ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY (TURKEY) Completed: alter database prepare to switchover to logical standby
PREPARING SWITCHOVER
.SQL> alter database prepare to switchover to primary; Database altered.
On the standby alert log, issue the following command:
Fri Oct 12 08:51:55 2012 alter database prepare to switchover to primary ALTER DATABASE SWITCHOVER TO PRIMARY (INDIA) ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY (INDIA)
PREPARING SWITCHOVER
to TO LOGICAL STANDBY
. In this stage, both the databases wait for acknowledgment from each other. Now check the switchover status on the primary database as shown in the following lines:SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO LOGICAL STANDBY
SWITCHOVER
command waits until this transaction is complete, as shown in the following code:SQL> select addr,status,flag from v$transaction; ADDR STATUS FLAG ---------------- ---------------- ---------- 000000008EF5A950 ACTIVE 7683 SQL> select username,status from v$session where username is not null and username not in ('SYS','PUBLIC'), USERNAME STATUS ---------- -------- PACKT ACTIVE
ACTIVE
mode. Let's see what happens in the alert logfile when the switchover is issued, as follows:SQL> alter database commit to switchover to logical standby;
On the primary alert logfile you will see the following:
Fri Oct 12 14:51:12 2012 alter database commit to switchover to logical standby ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (TURKEY) ......... Fri Oct 12 14:52:25 2012 Waiting for transactions in flight at scn 0x0000.003337d6 to complete
Perform commit from the user session as follows:
SQL> show user USER is "PACKT" SQL> commit; Commit complete.
After performing commit from the user session, the switchover will be processed successfully and we'll see Database altered
as the output on the session in which we ran the switchover statement, as shown in the following code:
SQL> alter database commit to switchover to logical standby; Database altered.
On the primary alert logfile you can perform the following:
LOGSTDBY: Switchover complete (TURKEY) LOGSTDBY: enabling scheduler job queue processes. JOBQ: re-enabling CJQ0 Completed: alter database commit to switchover to logical standby
Note that during switchover, log apply services will be stopped on the logical standby database. Now check the latest status on the former primary database using the following code:
SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE --------------- ---------------- -------------------- turkey_un LOGICAL STANDBY READ WRITE
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> alter database commit to switchover to primary; Database altered.
The switchover from the logical standby to the primary was successful, as can be seen in the following command-line output:
Fri Oct 12 15:04:43 2012 alter database commit to switchover to primary ALTER DATABASE SWITCHOVER TO PRIMARY (INDIA) ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (INDIA) LOGSTDBY: Successful close of the current log stream: LOGSTDBY: primary: [1316772835] ............. Completed: alter database commit to switchover to primary
ACTIVE
mode. The following output shows that the session is still in the ACTIVE
mode on the former primary database:SQL> select sysdate from dual; SYSDATE -------------------- 12-OCT-2012 15:10:10 SQL> show user USER is "PACKT" SQL> select username,logon_time from v$session where username is not null and username not in ('SYS','PUBLIC'), USERNAME LOGON_TIME ---------- -------------------- PACKT 12-OCT-2012 14:44:06
SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE --------------- ---------------- -------------------- india_un PRIMARY READ WRITE
SQL> !ps -ef|grep lsp oracle 24824 8569 0 16:06 pts/1 00:00:00 /bin/bash -c ps -ef|grep lsp SQL> alter database start logical standby apply immediate; Database altered. SQL> !ps -ef|grep lsp oracle 24860 1 1 16:08 ? 00:00:01 ora_lsp0_TURKEY oracle 24914 8569 0 16:09 pts/1 00:00:00 /bin/bash -c ps -ef|grep lsp
On the standby alert logfile you will see the following:
Fri Oct 12 16:08:01 2012 alter database start logical standby apply immediate ALTER DATABASE START LOGICAL STANDBY APPLY (TURKEY) with optional part IMMEDIATE Attempt to start background Logical Standby process Fri Oct 12 16:08:01 2012 LSP0 started with pid=35, OS id=24860 Completed: alter database start logical standby apply immediate
We've seen the step-by-step approach to perform a switchover between the primary and logical standby database using SQL*Plus. We've also monitored switchover transactions by tracking the alert logfile on both databases.
Q1. You've prepared either the primary or the standby database to perform switchover and then you have decided not to perform switchover. Is it possible to cancel it?