Database Configuration - two service names on same port

Question

Can two SIDs/service names use same port number on a db server?

I don't have much knowledge on DB administration and so can you let me know the answer for my question?

Answer 1

There's a school of thought that you should only have a single database instance on a server, to optimise resource usage, but possibly with many schemas and applications running within that.

An instance has a single SID, but can have more than one service name. So with a single database you can have one SID and multiple service names; with multiple databases you will have multiple SIDs and service names. I'll assume you are running, or planning to run, multiple instances co-hosted on one box, and I'll leave the advisability of that as a separate discussion.

If you're talking about normal connections, rather than Apex etc., the database doesn't use a port number directly. The listener binds to a port and listens for connections, and hands those off to the database using an underlying protocol. This is explained at a high level in the concepts guide.

You can have multiple listeners, each on its own port; you may want to have a dedicated listener for each database, or in some circumstances for individual applications or schemas within a single database. But normally you'd have a single listener, probably on the default port 1521.

There are many ways to configure a listener, but again in a simple scenario, your listener.ora just has information about the listener itself - its address and port, logging details etc. - and nothing about which databases, SID or service name it supports. When the listener starts, lsnrctl services will show very little.

A database will register itself with a listener, on start-up and retrying at regular intervals if it can't find its listener. You can encourage that by issuing an alter system register command. Once a database has registered, lsnrctl services will show its SID and service name(s). If you have two databases then they will both happily register with the same listener, and lsnrctl services will show both.

When a connection is made the SID or service name is specified as part of that, e.g. @//hostname:port/service_name. The listener uses that to decide which instance to hand the connection off to. If no database has registered (yet) with the supplied SID or service name, the client will get an ORA-12505 or ORA-12514 error, TNS:listener does not currently know of....

There's a lot more to explore under the hood of course. You can define multiple service names, you can have the listener on a non-standard port, and if you have multiple listeners you can tell each database which to register against. If you're interested in that level of detail, dig into the documentation.