Configuring Oracle Database Services Through Role Based Services

You can automatically control the startup of Oracle database services on both the primary and standby Oracle databases by assigning a database role to each service.

An Oracle database service automatically starts when the Oracle database starts if the Oracle database policy is set to AUTOMATIC and if the service role matches the current role of the database. In this case, the role for the Oracle database is either in the primary or standby role as part of the Active Data Guard configuration.

Configure services with the srvctl utility identically on all Oracle databases in the Data Guard configuration. The following example shows two services created identically on both the primary and the standby Oracle databases. See srvctl add service in the Oracle Database Administrator's Guide.

The following steps add the primaryrole and standbyrole database services to both the primary and standby Oracle databases when the primary Oracle database is located in Austin and the standby Oracle database is located in Houston.

  1. On the primary Oracle database, add the primaryrole database service. While this Oracle database acts as the primary, this service is started.
    srvctl add service -d Austin -s primaryrole -r ssa1,ssa2,ssa3,
     ssa4 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
  2. On the primary Oracle database, add the standbyrole database service. This service starts only if this Oracle database switches to the standby role and then provides real-time reporting on the standby Oracle database.
    srvctl add service -d Austin -s standbyrole -r ssa1,ssa2,ssa3,
     ssa4 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
  3. On the standby Oracle database, add the primaryrole database service. This service starts only if this Oracle database switches to the primary role.
    srvctl add service -d Houston -s primaryrole -r ssb1,ssb2,ssb3,
     ssb4 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
  4. On the standby Oracle database, add the standbyrole database service. While this Oracle database acts as the standby, this service is started and then provides real-time reporting on the standby Oracle database.
    srvctl add service -d Houston -s standbyrole -r ssb1,ssb2,ssb3,
     ssb4 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
  5. Run the following SQL statement on the primary Oracle database so that the service definitions are transmitted and applied to the physical standby Oracle database.
    EXECUTE DBMS_SERVICE.CREATE_SERVICE('standbyrole', 'standbyrole', NULL, 
     NULL, TRUE, 'BASIC', 'SESSION', 150, 10, NULL);
  6. 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))))
  7. On the primary Oracle database, start the primaryrole database service.
    srvctl start service -d Austin -s primaryrole 
  8. On the standby Oracle database, start the standbyrole database service.
    srvctl start service -d Houston -s standbyrole