Oracle net switchover

Oracle net switchover

The core thing of making users automaticaly switch to new primary database after switchover
operation is dbms_service package. With it we can create new service, delete it, start and stop it.
The second thing is db_role_change trigger, which fire after role transition occured. And the last
thing is configuration of clients tnsnames.

1. Let's create service on the primary database to wich applications are connected.
In our example I give it name 'T4U'.

prod2 $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Пт Авг 19 15:54:18 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec DBMS_SERVICE.CREATE_SERVICE('T4U','T4U');

PL/SQL procedure successfully completed.

2. Now we have to create after db_role_change trigger

SQL> create or replace trigger role_changed_service after db_role_change on database
  2  declare
  3    dbrole varchar2(16);
  4  begin
  5    select database_role into dbrole from v$database;
  6    if dbrole = 'PRIMARY' then dbms_service.start_service('T4U');
  7    else dbms_service.stop_service('T4U');
  8    end if;
  9  end;
 10  /

Trigger created.

SQL> exit

3. Configure oracle net on the client's side
tnsnames.ora:

T =

  (DESCRIPTION =

    (FAILOVER = ON)

    (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = TCP)(HOST = prod2.mmk.local)(PORT = 1521))

       (ADDRESS = (PROTOCOL = TCP)(HOST = ilstal.mmk.local)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = T4U.MMK.LOCAL)

    )

    (FAILOVER_MODE =

      (TYPE = SELECT)

      (METHOD = BASIC)

      (RETRIES = 100)

      (DELAY = 1)

    )

  )

4. Now let's switchover

prod2 $ dgmgrl sys/password@T
DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> switchover to 'TSTB';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "T" on database "T"
Shutting down instance "T"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "TSTB" on database "TSTB"
Shutting down instance "TSTB"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "T" on database "T"
Starting instance "T"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "TSTB" on database "TSTB"
Starting instance "TSTB"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "TSTB"
DGMGRL> show configuration;

Configuration
  Name:                DG_T
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    T    - Physical standby database
    TSTB - Primary database

Current status for "DG_T":
SUCCESS

5. Check if standby host aware of 'T4U' service

prod2 $ lsnrctl services | grep T4U
Service "T4U.MMK.LOCAL" has 1 instance(s).
prod2 $

6. Well it's the time to try connect from client's side

C:\Users\bas>sqlplus system/pasword@T

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 19 16:10:45 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


GLOBAL_NAME
--------------------------------------------------------------------------------
system@T

system@T>

That's all.