The Power Of Oracle Services With Standby Databases

One of the first tasks most Database Administrators (DBA) focus on when looking at implementing a Disaster Recovery (DR) solution, is getting a physical standby database implemented.

With products such as Standby™️ this can be done quickly and the DBA will be able to have a standby database ready within a short period of time. But then the question; what about the application/client connections? How will you switch them from pointing at the current Primary server to the Standby server following a graceful switchover (GS)?

Let’s say you have a primary database PROD with a standby database instance named PRODDR, and the database is used to store financial information. You want user sessions to connect to the PRODDR database instance with as little intervention as possible when you perform a graceful switchover or activation.  One way to implement such a solution is to make use of Oracle Services.

In the above scenario we can create a service named FINPROD that will be configured for connectivity to the primary database, which will be open read/write. From an end user point of view they are connecting to a service called FINPROD, and they are not aware that they are connecting to either the PROD or the PRODDR instance. To go one step further we also create a service called PRODRO that can be used for reporting/read-only queries on the standby database; if it is opened “Read Only”. This would be especially useful when having multiple standby databases, where one is open read-only during the day for reporting and only gets updated at night. The read-only (PRODRO) Service can then be activated by this instance to allow users to connect and execute queries.

The process to implement the use of services can be summarized as follows:

• Create two new services:

  • FINPROD – used for Read/Write day to day operations

  • PRODRO – used for reporting when standby database is opened read-only

• Create an “after startup” database trigger:

  • This trigger will be used to activate the required service depending on the database role

• Update the client network configuration file:

  • Update client tnsnames.ora configuration file to allow for failover connections between the primary and standby server

I always prefer to use examples, as it is just the best way to explain a topic.  Please note, the example below is on a single instance non-clustered environment. Two Linux servers being used; dbvlin101 and dbvlin102, with Oracle Database Standard Edition (11gR2) installed on both systems. The primary database instance(PROD) is running on dbvlin101 and the standby database (PRODDR) on the dbvlin102 server. This example will also be making use of the latest version of Standby™️ for log transfer, apply, graceful switchover and activation. For more details on Standby™️ please see here

Step 1: Standby Database Creation

A new standby database called PRODDR was created using the Standby™️ CSD feature. Following the creation, schedules were configured to automate log transfer and apply at set intervals.

Step 2: Create Oracle Services on Primary

In this example I am creating two services FINPROD and PRODRO. The FINPROD service will be the primary service used by client connections for day-to-day (read/write) operations where the PRODRO service will be used for reporting (read-only) purpose when the standby database is opened in read-only mode. The commands below are executed on the primary database instance as the SYS database user account:

begin
  dbms_service.create_service (service_name => 'FINPROD', network_name => 'FINPROD');
  dbms_service.create_service (service_name => 'PRODRO', network_name => 'PRODRO');
end;
/

To confirm service is created you can review dba_services:

SQL> select name, network_name, creation_date from dba_services where name like '%PROD%';

NAME         NETWORK NAME          CREATION_DATE
------------ -------------------   -------------------
FINPROD      FINPROD               18/06/2012:20:22:13
PRODRO       PRODRO                18/06/2012:20:22:13

As the services are not active yet, you will not see them in v$active_services;

Step 3: Create “after startup” trigger

The next step to be performed is the creation of the “after startup” trigger.  This step will be executed on the primary and standby databases when startup procedures are being performed. If the database is opened as a primary database in read/write mode, the FINPROD service will be activated and enabled on that instance. If the standby database is opened read-only, the PRODRO service will be activated and enabled.

create or replace trigger stby_service_check
  after startup on database
declare
  db_role varchar2(16);
  db_mode varchar2(20);
begin
  select database_role, open_mode into db_role, db_mode from v$database;
  if db_role = 'PRIMARY' then
    dbms_service.start_service('FINPROD');
    dbms_service.stop_service('PRODRO');
  elsif (db_role = 'PHYSICAL STANDBY') and (db_mode = 'READ ONLY') then
    dbms_service.start_service('PRODRO');
    dbms_service.stop_service('FINPROD');
  else
    dbms_service.stop_service('FINPROD');
    dbms_service.stop_service('PRODRO');
  end if;
end;
/

During this process these services will be registered with the listener. It is important to note that if your listener is running on a different port than the default 1521, you will have to configure the local_listener database parameter to point to your listener. To do this, add an entry to the tnsnames.ora file followed by updating the database parameter. For example, if the listener is using port 1525, you can add the following entry to your tnsnames.ora (on the database server):

LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin101)(PORT = 1525))

Then update the database parameter:

Sql> alter system set local_listener=’LISTENER’ scope=both;

This should be done on both databases and is only required if you are not using the default port 1521 for the listener.

Step 4: Update client network configuration

The next step is to enable the clients to use the new service. To do this the client tnsnames.ora file should be updated to include the new service name.  You should add both the primary and standby server details under the address_list section and you need to include the option “failover=on” and make sure the option “load_balance=off” is set to off.  Below are the two service entries used in our example:

FINPROD =
  (description =
    (address_list =
      (address = (protocol=tcp)( host=dbvlin101)(port=1525))
      (address = (protocol=tcp)( host=dbvlin102)(port=1525))
      (failover = on)
      (load_balance = off)
    )
  (connect_data=
    (service_name = FINPROD)
  )
)

PRODRO =
  (description =
    (address_list =
      (address = (protocol=tcp)( host=dbvlin102)(port=1525))
      (address = (protocol=tcp)( host=dbvlin101)(port=1525))
      (failover = on)
      (load_balance = off)
    )
  (connect_data=
    (service_name = PRODRO)
  )
)

Step 5: Enable the services

In most cases you will be doing this on a production environment where you cannot just restart the production database, so to enable the FINPROD service you can run the following command on the primary node:

begin
  dbms_service.start_service(‘FINPROD’);
end;

Once this is done, a quick way to confirm the service is active is to review v$active_services:

select service_id, name, network_name, creation_date from v$active_services
SERVICE_ID         NAME              NETWORK NAME   CREATION_DATE
----------         ---------------- --------------- -------------------
3                  FINPROD          FINPROD         18/06/2012:20:22:13
5                  prodXDB          prodXDB         18/06/2012:15:45:28
6                  prod             prod            18/06/2012:15:45:28
1                  SYS$BACKGROUND                   17/09/2011:09:46:22
2                  SYS$USERS                        17/09/2011:09:46:22

You can also review the status of the listener, and you should see an entry similar to the following (line 4 below):

# lsnrctl status
...
Services Summary...
Service "FINPROD" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
...

Step 6: Ensure standby is up to date

It is important that you make sure the standby is up to date. This is to ensure the services and trigger creation changes were applied to the standby.  This was easily done, by using Standby™️. We just executed the following command on both the primary and the standby which made the standby database up to date with the primary:

cd /usr/local/dbvisit/standby
./dbvisit prod

Step 7: Testing

The first step of the test will be to connect to the primary service FINPROD from a client. Below is an example showing the connection details for a database user named “aels”:

SQL> connect aels@FINPROD
Enter password:
Connected.

As a dba account, we did a quick select to show the session (added a few columns from v$instance and v$database to show you more detail):

Sql> select a.username, a.service_name, b.instance_name, c.name db_name, c.database_role, c.open_mode from v$session, v$instance, v$database
where username=’AELS’;

USERNAME   SERVICE_NAME   INSTANCE_NAME   DB_NAME   DATABASE_ROLE    OPEN_MODE
---------- -------------- --------------- --------- ---------------- --------------------
AELS       FINPROD        prod            PROD      PRIMARY          READ WRITE

As you can see, the user is now connected using the service FINPROD to the primary database that is open read/write.

Now to test that we can connect to the standby when it is opened read-only. We first open the standby database read-only and then attempt another connection via the PRODRO service name:

SQL> connect aels@PRODRO
Enter password:
Connected.
SQL> select a.username, a.service_name, b.instance_name, c.name db_name, c.database_role, c.open_mode from v$session a, v$instance b, v$database c where a.username='AELS';

USERNAME    SERVICE NAME   INSTANCE NAME DB_NAME   DATABASE_ROLE      OPEN_MODE
----------- -------------- ------------- --------- ----------------   --------------------
AELS        PRODRO         proddr        PROD      PHYSICAL STANDBY   READ ONLY

As you can see above we were able to connect via the PRODRO service to the standby database, which is open read-only.

Now we perform a switchover between the two databases. Again this was done using Standby™️.

Following the switchover which means that the PRODDR database instance is now the primary and the PROD instance is the standby, we attempt the connection to the FINPROD service and as you can see below, my connection is now directed to the PRODDR database instance:

SQL> connect aels@FINPROD
Enter password:
Connected.
SQL> select a.username, a.service_name, b.instance_name, c.name db_name, c.database_role, c.open_mode from v$session a, v$instance b, v$database c where a.username='AELS';

USERNAME    SERVICE NAME   INSTANCE NAME DB_NAME   DATABASE_ROLE      OPEN_MODE
----------- -------------- ------------- --------- ----------------   --------------------
AELS        FINPROD        proddr        PROD      PRIMARY            READ WRITE

In summary, Oracle Services are powerful and together with Standby™️, managing a Disaster Recovery environment becomes an easier task.

Download a free Standby™️ 10 Day Trial today.