IzzySoft

• Person
• Services
• References
• Software
• Oracle
• Imprint

Configure & use Listeners & Services

Listeners and services make interesting combinations – let's have a look at that!

Contents

• Listeners
  • Basic listener
  • Static listener
  • Listener on specific ports
  • Further Readings
• Services
  • Differences
  • Use case: Standby DB

Listeners

No news even for the „Freshman DBA“: applications need listeners to connect to a database. I'll skip the basic part of what exactly they are and how they work, as probably all of you know, ad go straight to the (hopefully) more interesting stuff.

Basic listener

A very basic listener does not even need a configuration – you simply start the listener service (lsnrctl start), and there it is. The local database in its default configuration will find it within a minute and register to it (to speed things up, you could connect as sysdba and run alter system register;, so it happens instantly) – et voila, you can connect to the database over the network.

Pointer: What that database is already looking for is a listener running on a machine using the same IP the database was configured for, and listening on port 1521, which could basically be described by

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$HOSTNAME)(PORT=1521)))

A single listener can serve multiple databases. In a simple setup, you don't need a separate listener for each database – a single listener suffices. You don't even need a listener.ora configuration file.

Static listener

While with above „simple setup“ each database would dynamically register itself with the local listener – it would only do so when it's up and running. That's fine for your normal applications (they couldn't connect to a non-running database anyway) – but neither could you remotely connect to a database which is down to bring it up. Nor could the Data Guard Broker perform a switchover to a standby database which is only MOUNTED, or could an Data Guard Observer even monitor whether it is there at all. For this, you will need an listener.ora explicitly naming available databases. A basic listener.ora with a static database entry could look like this:

LISTENER = (
  DESCRIPTION_LIST = (
    DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)

SID_LIST_LISTENER= (
  SID_LIST= (
   (SID_DESC=
    (GLOBAL_DBNAME=MYDB)
    (SID_NAME=MYDB)
   )
)

In this case, provided you have a corresponding entry in your local machine's tnsnames.ora, you could connect to the MYDB database even if it were down:

$ sqlplus sys@mydb as sysdba

The important part here is the as sysdba. Without SYSDBA privileges, noone can connect to a database that is not open.¹

And what about the other databases on the same server, which before that were registering automatically? Well, they'll continue doing so. As long as you keep the listener's name to LISTENER and its port on 1521, that is.

A static entry for a database in the SID_LIST_* of your listener allows a SYSDBA to remotely connect to that database even if it is not open – more precisely, even if it was shut down completely. All other databases (and services) continue behaving as before, e.g. registering dynamically with the listener.

Listener on specific ports

Why would one use a different port? Isn't the default one fine? Short answer: It is – and unless you have good reason, you should leave it at that. Makes administraion much easier.

Now, what might those reasons be?

• Security: Known default port. Well, that would be „security by obscurity“ if it's the only reason (and measure). One can always use port scans.
• Security: DB Separation. That's a valid one: running multiple databases on the same server, restrict access per Firewall so each application can only access its own database.
• Convenience: Adjusting it to the DBAs dial extention. Well… A real-life example, so: No comment :)

Now, in case you want to do that, let's see how to set it up. Let's start with the listener.ora and establish our new special listener on port 1621:

APPLLIST = (
  DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.example.com)(PORT = 1621))
    )
)

Save the file, then start the new listener:

$ lsnrctl start appllist

Now we need to tell the database to register with this listener. This is done via the alter system set local_listener command. But other than you might think, you cannot simply pass that the name of the new listener (though it shouldn't be too hard for Oracle to figure the rest, with the listener being local, i.e. running on the very same machine the database is on. No: Oracle insists you make a tnsnames.ora entry for APPLLIST to resolve it. Alternatively, just pass the complete descriptor to the alter command:

ALTER SYSTEM SET LOCAL_LISTENER=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.example.com)(PORT=1621)))';

Shall your database register to both listeners – say, on port 1521 for all local work (completely blocked by the „outer firewall“) and on port 1621 for the application (enabled by a specific firewall rule)? That can be done, too:

ALTER SYSTEM SET LOCAL_LISTENER=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.example.com)(PORT=1521)))',
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.example.com)(PORT=1621)))';

Now you have the advantage to perform maintenance via one listener (port 1521) while ensuring no application user connects at the same time (because you stopped the listener on port 1621) – for example.

You can run your listener on any (free) port you like, and even make your database register with more than one listener. But then you either need to setup a tnsnames.ora entry for your listener, specify the full connection string to your database's local_listener setting – or use a static entry for your database in your listener.ora.

What's so bad with a static entry, as that would be the easiest to configure? Remember a static listener would even report knowing about the database when it's just MOUNTED or even when it's down. In a load-balanced or a high-availability environment, this might be a problem as not all applications can deal with that properly: some expect that „when it's there it should let me in“ – and get confused when receiving an answer like „it's there, but not open“. Another way of dealing with that would be …

Further Readings

• Registering the Oracle database with the listener (Blog)
• Configuring and Administering Oracle Net Listener (Oracle official documentation)
• Oracle’s Automatic Listener Registration (Blog)

Services

You can think about a service as an „alternative name“ for your database which you can control separately. And which an application can connect to – as services register with the listeners whenever the database registers with them. But you won't need to stop the entire database (or listener) just to disable those connections: you can simply „get rid of that service“. And more. To name some use cases:

• having a specific service for „manual users“ so you can control when they're able to connect and when not
• using Oracle's Resource Manager to control resource usage of e.g. big-power-eating batch processes, configuring them to use a specific „batch service“
• fine-tune your auditing with special measurements for a user/application group by assigning them to a service
• in a high-availability environment with Data Guard, make sure your applications only connect to the primary database (and not the standby when opened read-only)

There are certainly more, but this should give you a raw picture.

Differences

There are different types of services. I will concentrate on two here, based on how they are defined/set up:

ALTER SYSTEM SET services_names='MYDB_SERV1,MYDB_SERV2';

This way you've defined two services: MYDB_SERV1 and MYDB_SERV2. Your listener will show them both – regardless which open_mode your database is in. To make them invisible, you'd need to „unset“ them again. If you want to do that dynamically, you always need ALTER SYSTEM privileges (unless you write a wrapper procedure) – which implies that whomever you want to give permission to bring the service up or down, you'd also give a lot of extra power along. But granted, it's set up pretty easy.

begin
  dbms_service.create_service(
    'MYDB_SERV1',
    'MYDB_SERV1'
  );
  dbms_service.start_service('MYDB_SERV1');
  dbms_service.disconnect_session('MYDB_SERV1',1); -- 0: POST_TRANSACTION, 1: IMMEDIATE, 2: NO_REPLAY
  dbms_service.stop_service('MYDB_SERV1');
end;
/

This service wouldn't show up until the database is opened (up to MOUNT, you cannot call to dbms_service as it would not be found). Further, whomever you wanted to hand-over maintenance for that service, you wouldn't have to give any extra privileges along automatically: simply GRANT EXECUTE ON dbms_service suffices. Another plus is you can disconnect all sessions using the service without hunting them down manually (or restarting the database to get rid of them); so to kick out all users connected via MYDB_SERV1 you don't even need to bother about effects on other sessions coming via MYDB_SERV2. This comes in handy if you e.g. need to upgrade a specific schema for one application (for which nothing should be connected then), while keeping the service available for another.

exec dbms_service.disconnect_session('MYDB_SERV1',1)

Use case: Standby DB

Now let's sum this up with a nice use case. Say you have a primary and a standby database. Usually, your standby database is simply that: a standby database, status MOUNTED. Occasionally, you want to use it for reporting (while letting your primary perform the heavy processing). So you want to make sure that:

• while the standby is OPEN READ ONLY, only your reporting stuff connects to it
• at the same time, all processing stuff connects only to the primary
• that reporting stuff only connects to the standby (to not put extra load on the primary)
• and finally, when your standby is simply a standby (MOUNTED), nothing fails trying to connect there as the listener would simply shrug off such attempts

You might already have guessed what I'm aiming at:

• setting up a service for the productive work, say SERV_PROD
• setting up another service for the reporting stuff, say SERV_REPORT
• setting up a „dynamic listener“ on both machines, so the resp. services are made available when running
• and now the spices: creating a trigger taking care that the correct services are running on the correct database (even after a switchover)

The listener part is easy and was already described above. As our services will connect to it dynamically once running, nothing else is to do here. So let's define our services:

begin
  dbms_service.create_service('SERV_PROD','SERV_PROD');
  dbms_service.create_service('SERV_REPORT','SERV_REPORT');
end;
/

That was quick. But now, both services are running on both, primary and standby databases – which is not what we intended. So initially, manually stop SERV_REPORT on the primary – as well as SERV_PROD on the standby, should it have been running in Active Data Guard mode. Now we take care for the future:

create or replace trigger service_trigger
after startup on database
begin
  if sys_context('userenv','database_role') = 'PRIMARY'
  then
    dbms_service.start_service('SERV_PROD');
    dbms_service.stop_service('SERV_REPORT');
  else
    dbms_service.stop_service('SERV_PROD');
    dbms_service.start_service('SERV_REPORT');
  end if;
end;
/

That's it: once you startup your primary, the SERV_PROD service will be available but not SERV_REPORT; startup the standby as READ ONLY [WITH APPLY] and SERV_REPORT will be available but not SERV_PROD. You ask what will happen when starting up the standby in MOUNT mode with recovery? Didn't I forget something? Nope. While in MOUNT, the dbms_service package is not available, so none of the two services will be started. Thus, all of our four bullet points are met.