How to prevent a user from login into a postgres database

alter role scott with nologin;
ALTER ROLE

Remember that in postgres, "users" and "roles" are used interchangably, so this would also work:

alter user scott with nologin;
ALTER ROLE

In either case, the postgres server will echo "ALTER ROLE" back to the administrator.

To see the result of such an operation:

echo "\du" | psql

Example output:

                                               List of roles
            Role name            |                         Attributes                         |      Member of
---------------------------------+------------------------------------------------------------+---------------------
 scott                           | Cannot login                                              +| {business_users}
 

You can also query the postgres data dictionary for the answer, like this:

postgres=# select rolcanlogin from pg_roles where rolname='jim';
 rolcanlogin
-------------
 t
(1 row)

postgres=# select rolcanlogin from pg_roles where rolname='scott';
 rolcanlogin
-------------
 f
(1 row)

where t and f indicates true if the user can login and false if the user cannot, respectively.

How to solve "Service name or network name of ... in the PDB is invalid or conflicts with an existing service name or network name in the CDB."

From the alert log during startup of the container database called "cdb":

pdb1(3):***************************************************************
pdb1(3):WARNING: Pluggable Database saes with pdb id - 3 is
pdb1(3):         altered with errors or warnings. Please look into
pdb1(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
pdb1(3):***************************************************************

When I check the PDB_PLUG_IN_VIOLATIONS I see this:

select name,cause,type,message,status,action 
from pdb_plug_in_violations;

NAME	CAUSE	TYPE	MESSAGE	STATUS	ACTION
sales	Service Name Conflict	WARNING	Service name or network name of service salesXDB in the PDB is invalid or conflicts with an existing service name or network name in the CDB.	PENDING	Drop the service and recreate it with an appropriate name.

Check the CDB_SERVICES view:

SELECT name,network_name,creation_date,pdb,enabled
FROM   cdb_services
where con_id=3
and name='salesXDB'
ORDER BY 1;

NAME	NETWORK_NAME	CREATION_DATE	PDB	ENABLED
salesXDB	salesXDB	03/03/2022 11:28:56	sales	NO

There is indeed a service called salesXDB, which is a reminisce from an earlier point in time where "sales" was a non-CDB database.
It has now been replaced with the container database XDB service, in my case called "cdbXDB".

How to address the warnings

Log onto the sales pluggable database:

sqlplus / as sysdba

alter session set container=sales;

Session altered.

Remove the service from the PDB:

exec dbms_service.delete_service('salesXDB');

PL/SQL procedure successfully completed.

If you check the CDB_SERVICES again, it will now be gone.

Restart the pluggable database:

sqlplus / as sysdba

alter session set container=sales;

Session altered.

shutdown immediate
startup

If you query the PDB_PLUG_IN_VIOLATIONS again, you will see that the value for status in the error is now set to RESOLVED:

time	NAME	CAUSE	TYPE	MESSAGE	STATUS	ACTION
11.10.2022 12:49	sales	Service Name Conflict	WARNING	Service name or network name of service salesXDB in the PDB is invalid or conflicts with an existing service name or network name in the CDB.	RESOLVED	Drop the service and recreate it with an appropriate name.

Source: PDB Name Conflicts With Existing Service Name In The CDB Or The PDB (Doc ID 2247291.1) from Oracle Support

How do I make sure that services are automatically brought online in an Oracle 19c Pluggable Database?

In an Oracle 19c pluggable database, the service_names parameter has no bearing on whether or not the services are brought up automatically at instance or server restart.

If the service was started in the PDB and you then saved as the pluggable database's state, it willl be brought online together with the pluggable database automatically.

First, create and start your desired service from the PDBS:

SQL> alter session set container=sales;

Session altered.

SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('online_users');

PL/SQL procedure successfully completed.

Switch to your root container:

SYS@cdb>SQL>alter session set container=CDB$ROOT;

Session altered.

Then, save the state of your PDB:

SYS@cdb>SQL>alter pluggable database sales save state;

Pluggable database altered.

Or, alternatively, save all current states of your PDBs:

SYS@cdb>SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

Pluggable database altered.

The service_names parameter is actually deprecated in Oracle 19c. The documentation states
To manage your services, Oracle recommends that you instead use the SRVCTL command-line utility, the GDSCTL command-line utility, or the DBMS_SERVICE PL/SQL package.

How to check the code level currently in use in an EBS database

To find the E-Business Suite and AD/TXK patch levels, log into the EBS container and use the following two queries:

col abbreviation format a20
col name format a40
col codelevel format a20
select  ABBREVIATION, NAME, codelevel FROM apps.AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad')

ABBREVIATION         NAME                                     CODELEVEL
-------------------- ---------------------------------------- --------------------
ad                   Applications DBA                         C.11
txk                  Oracle Applications Technology Stack     C.11

select release_name from apps.FND_PRODUCT_GROUPS;

RELEASE_NAME
------------
12.2.9

What exactly is the LOCAL_LISTENER parameter, and what does it do?

What is the local_listener parameter?

It is a parameter that points to the listener running on the local server.

What is the purpose of the local_listener parameter?

It is used for dynamic listener registration, which is the process of the database contacting the listener and registrering the services it offers automatically.

How is the database registering its services with the listener?

From Oracle 12c and onwards, this registration process is handled by the LREG process.
The lreg process is easy to spot from the operating system:

ps -fu oracle |grep lreg
oracle     22023       1  0 Sep19 ?        00:00:30 ora_lreg_cdb

How is dynamic listener registration implemented?

Start the listener process with or without a parameter file. If you do not have a listener.ora parameter file, the listener will run using default values.
In either case, simply start the listener process by typing

lsnrctl start

After a little while (usually within a minute) the database has registered its services with the listener.
You you do not want to wait, you can manually force a registration by logging onto the database as a dba and issue:

alter system register;

How will the database find the listener process?

This is where the local_listener parameter comes in. It tells the database where to find it, and which port to use.
The value of my local_listener parameter is:

SYS@cdb>SQL>show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_CDB

My $TNS_ADMIN/tnsnames.ora file has an entry that matches the value LISTENER_CDB:

LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = testserver1.oric.no)(PORT = 1521))

This makes it possible for the database to register its services with the listener.

Even if the local_listener is not set at all, dynamic registeration would still work if your listener runs with the default values, which is

(ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.

How does the database know which services that should be registered with the listener?

It will start to listen for services listed in the v$active_services view.

How does the local_listener parameter work under the multitenant architecture?

The local_listener works identically in both non-CDB and a multitenant database.
However, in a multitenant setup, remember that each pdb will have its own corresponding service.
This service you cannot stop unless you unplugg or shutdown your pdb.

If you attempt to stop the service of a running pdb you will receive

ORA-44793: cannot stop internal services

Any other services created after this point can be stopped and started at will, and the listener will follow suit.


Let's see how it works:

First, list the services currently supported by the listener:

Services Summary...
Service "cdb.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "sales.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully

The listener supports the services for the root container ("cdb") and the pdb ("sales").

Let's try to create and start a new service in my pdb called "sales":

SQL> alter session set container=sales;

Session altered.

SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('online_users');

PL/SQL procedure successfully completed.

List the services supported by the listener now:

Services Summary...
Service "cdb.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "online_users.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "sales.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...

For automatic restart of Pluggable databases and their services, please see this post.

Documentation:

local_listener

service_names

dbms_service

A great blog post by Mr. Ed Stevens on the same topic

How to use the html markup option in sqlplus

Use the following directives in sqlplus:

set timing on
set trimspool on
set markup html on spool on head MYHEADLINEHERE table "width='50%' border='1'"
spool myfile.html

What is the difference between dba_services and v$services?

Why do we see differences in output between dba_services and v$services view ?

SQL>select name from v$services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
ebs_accnt
accnt
cdbXDB
cdb
accnt_ebs_patch

7 rows selected.



SQL>  select name from dba_services;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
accntXDB
cdb
cdbXDB
cdb.skead.no
ebs_PATCH

7 rows selected.

New services were added and few services were stopped recently.

Answer:

The difference is due to the following:

* DBA_SERVICES - This view lists all the services that are in the database. This includes both started and stopped services.
* V$SERVICES - This view lists only the services that were started some time since the instance startup. When you stop a running service, that service will still be listed in this view until the next instance restart.

Oracle states that "The difference between these outputs does not pose any issue."

Source: Differences in outputs between DBA_SERVICES and V$SERVICES view (Doc ID 1496074.1) found at support.oracle.com