Site Logo

IzzySoft


Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Buy at Amazon for 16,99 €
Das inoffizielle Android-SystemhandbuchDas inoffizielle Android-Systemhandbuch
Buy at Amazon for 7,41 €
Die besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und StudiumDie besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und Studium
Buy at Amazon for 1,52 €
As of 2020-08-12 21:08
prices & availability might be subject to change.

Configure & use Listeners & Services

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

Contents

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.1

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?

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 …

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:

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:

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

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.


  1. that is of course simplified, there are some other special roles who could – e.g. SYSDG ↩︎

2020-05-13