Listener output showing multiple instances when only one exists

In one of my multitenant setups, I saw the following output from the listener:

lsnrctl status cdb

Service "sales" has 2 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...
  Instance "sales", status UNKNOWN, has 1 handler(s) for this service...

There is only one instance in my setup, the container database called "cdb".
What's causing the listener to view the pdb "sales" as a separate instance, then?

See this post where Uwe Hesse explains:

It is nothing to worry about when you see the same instance showing up twice, once with status READY and once with status UNKNOWN. READY means listener knows this because PMON of that instance told him so (dynamic registration) UNKNOWN means listener knows this because of SID_LIST entry in the listener.ora (static registration)

Quite correctly, I had a static entry in my listener.ora file:

SID_LIST_cdb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/orasoft/product/19c)
      (SID_NAME = sales)
    )
  )

If I remove the following part

(SID_NAME = sales)

and restart the listener, the extra instance will be removed from the listener output:

lsnrctl stop cdb
lsnrctl start cdb

Output:

Service "sales" has 1 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...

Workaround for error ERROR: OCI Error DPI (status = 7-DPI-007: invalid OCI handle or descriptor) when starting Golden Gate replicate process

When trying to login to the golden gate cli interface, I got the following error:

GGSCI (myserver1.oric.no) 7> dblogin useridalias mydb  domain admin
ERROR: OCI Error DPI (status = 7-DPI-007: invalid OCI handle or descriptor).

Cause:

Incorrectly specified network configuration:
sqlnet.ora

names.default_domain = world

tnsnames.ora

mydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

To correct the error, you need to do either of the following:

1. remove the names.default_domain from sqlnet.ora

or

2. add an alias to your tnsnames.ora file

mydb.world =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

How to clear resolved errors from PDB_PLUG_IN_VIOLATIONS

The query below shows 4 resolved messages in the view PDB_PLUG_IN_VIOLATIONS:

SELECT NAME,CAUSE,TYPE,MESSAGE,STATUS
FROM PDB_PLUG_IN_VIOLATIONS
WHERE NAME='PDB$SEED'

NAME	CAUSE	TYPE	MESSAGE	STATUS
PDB$SEED	SQL Patch	ERROR	Interim patch 31424070/24854845 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Installed in the CDB but not in the PDB	RESOLVED
PDB$SEED	SQL Patch	ERROR	Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): Not installed in the CDB but installed in the PDB	RESOLVED
PDB$SEED	SQL Patch	ERROR	'19.16.0.0.0 Release_Update 2207030222' is installed in the CDB but no release updates are installed in the PDB	RESOLVED
PDB$SEED	SQL patch error	ERROR	Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): ROLLBACK with status INITIALIZE in the CDB.	RESOLVED

The resolved messages can be easily removed with the procedure DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS:

SYS@CDB$ROOT SQL> exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PDB$SEED');

Useful resources:

Mike Dietrich on the same topic

The Oracle 19c Documentation for the PDB_PLUG_IN_VIOLATIONS view

Note that there is an Unpublished Bug 16192980 : NO SIMPLE WAY TO CLEAN ROWS FROM PDB_PLUG_IN_VIOLATIONS AFTER DBMS_PDB CALL that prevents you from discarding no-impact warnings.
According to Oracle there should be a fix for this in version 19.10, but I still strugle to remove messages like

  Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.
  

which can be ignored. Read more about Database option mismatch errors in Doc ID 2020172.1

Rename schema in PostgreSQL: syntax

\connect db01
alter schema schema1 rename to schema1_old;

Documented here

Rename database in PostgreSQL: syntax

alter database db01 rename to db01;

Documented here

See this post for how to create a new database in PostgreSQL

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