Time for action – switchover with a logical standby database using SQL*Plus

Now we will see a step-by-step approach to perform a switchover between the primary and the logical standby database:

  1. Check the switchover status of the primary database. Ensure that the status is either 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

    Note

    In case of RESOLVABLE GAP, wait until SQL was applied on the logical standby database; for other statuses, troubleshoot and fix the synchronization for the switchover process to be successful.

  2. Prepare the primary database for switchover. Execute the following command from the primary database so that the current primary database will be accepted to perform a switchover to a logical standby database:
    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
  3. After issuing the previous command, the switchover status will be PREPARING SWITCHOVER.
  4. Prepare the logical standby database for switchover. After issuing the switchover initiation command from the primary database, you can execute the following code from the standby database:
    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)
  5. Perform the switchover from the primary database. After performing step 4, the switchover status in the current primary database will change from 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
  6. You must ensure that there are no active transactions during switchover. Therefore, clean up the transactions and proceed to switchover over. The 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
  7. Even after you perform the previous step, the session is still in the 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
  8. Perform the switchover from the logical standby database.
  9. We have completed the required steps on the primary database. Now check the status on the current logical standby database (INDIA) and issue the following switchover command:
    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
  10. During switchover, there will be zero data loss and the session will still be in the 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
  11. Check the status of new 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
--------------- ---------------- --------------------
india_un        PRIMARY          READ WRITE
  12. Start SQL Apply and monitor the logical standby database. Both the new primary database and the logical standby database are ready. Now start SQL Apply on the new logical standby database as follows:
    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

What just happened?

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.

Pop quiz

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?

Performing switchover with a logical standby database using broker

Managing any role transition or other administrative tasks of Data Guard with the broker is quite easy. Now we will see the step-by-step approach of a switchover between primary and logical standby databases using the DGMGRL utility.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.47.166