Configuring Oracle Database Services Through System Triggers

You can perform certain steps to create the primaryrole and standbyrole database services on the primary Oracle database using triggers. After creation, these are replicated to the standby Oracle database.

1. Create the primaryrole and standbyrole database services in the primary Oracle database.

   exec DBMS_SERVICE.CREATE_SERVICE( 
    service_name => 'primaryrole', 
    network_name => 'primaryrole',
    aq_ha_notifications => true, failover_method => 'BASIC', 
    failover_type => 'SELECT', failover_retries => 180, failover_delay => 1 );
   
   exec DBMS_SERVICE.CREATE_SERVICE( 
    service_name => 'standbyrole', 
    network_name => 'standbyrole',
    aq_ha_notifications => true, failover_method => 'BASIC', 
    failover_type => 'SELECT', failover_retries => 180, failover_delay => 1 );

2. Create the primaryrole and standbyrole triggers in the primary Oracle database for when the database starts.

   CREATE OR REPLACE TRIGGER manage_OCIService
   after startup on database
   DECLARE
     role VARCHAR(30);
   BEGIN
     SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
     IF role = 'PRIMARY' THEN
       BEGIN
         DBMS_SERVICE.START_SERVICE('primaryrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
       BEGIN
         DBMS_SERVICE.STOP_SERVICE('standbyrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
     ELSE
       BEGIN
         DBMS_SERVICE.STOP_SERVICE('primaryrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
       BEGIN
         DBMS_SERVICE.START_SERVICE('standbyrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
     END IF;
   END;

3. Create the following trigger on the primary Oracle database to run when the database changes roles:

   CREATE OR REPLACE TRIGGER manage_OCIService2
   AFTER DB_ROLE_CHANGE ON DATABASE
   DECLARE
     role VARCHAR(30);
   BEGIN
     SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
     IF role = 'PRIMARY' THEN
       BEGIN
         DBMS_SERVICE.START_SERVICE('primaryrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
       BEGIN
         DBMS_SERVICE.STOP_SERVICE('standbyrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
     ELSE
       BEGIN
         DBMS_SERVICE.STOP_SERVICE('primaryrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
       BEGIN
         DBMS_SERVICE.START_SERVICE('standbyrole');
       EXCEPTION
         WHEN OTHERS THEN
           NULL;
       END;
     END IF;
   END;

4. Add connection aliases in the appropriate tnsnames.ora files to identify the primary and standby Oracle databases and specify the database service names for each.

   primaryinstance=
     (DESCRIPTION_LIST=
       (LOAD_BALANCE=off)
       (FAILOVER=on)
       (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
              (ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521)))
                  (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primaryrole)))
    
       (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
              (ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521)))
                  (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primaryrole))))
    
   standbyinstance=
      (DESCRIPTION_LIST= 
        (LOAD_BALANCE=off)
        (FAILOVER=on)
        (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
               (ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521)))
                   (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standbyrole)))
    
        (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
               (ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521)))
                  (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standbyrole))))

5. Restart both of the Oracle databases to enable the trigger to start and stop the correct database services. Alternatively, if you do not want to restart both Oracle databases, you can start and stop the appropriate database services on each Oracle database as follows:

   On the primary Oracle database:

   exec DBMS_SERVICE.START_SERVICE('primaryrole');
   exec DBMS_SERVICE.STOP_SERVICE('standbyrole');

   On the standby Oracle database:

   exec DBMS_SERVICE.STOP_SERVICE('primaryrole');
   exec DBMS_SERVICE.START_SERVICE('standbyrole');