Oracle Multitenant

Oracle has announced that, from 19c onward, you can have 3 user-defined PDBs, without having to license the multitenant option.

The non-CDB architecture will be desupported from Oracle 21c on.

Check

Current Container

SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','CON_NAME') AS CON_NAME FROM dual;

SHOW CON_ID

SELECT SYS_CONTEXT('USERENV','CON_ID') AS CON_ID FROM dual;

CON_NAME------------------------------CDB$ROOT
CON_ID------------------------------1

Services

Each pluggable database should automatically register a service with the listener...

lsnrctl service

NAME                                                         PDB------------------------------------------------------------ ------------------------------MYDB_CLONE2.testsubnetfdata.vcnmydb.oraclevcn.com CDB$ROOTOnePlaDXDB                                                   CDB$ROOTSYS$BACKGROUND                                               CDB$ROOTSYS$USERS                                                    CDB$ROOTmydb_pdb1                                                    MYDB_PDB1

 CON_ID NAME                           OPEN_MODE  RESTRICT OPEN_TIME------- ------------------------------ ---------- -------- -----------------------------------      2 PDB$SEED                       READ ONLY  NO       13-JAN-24 11.27.55.799 PM +00:00      3 MYDB_PDB1                      READ WRITE NO       15-JAN-24 02.27.54.843 PM +00:00

SELECT * FROM dba_pdbs ORDER BY pdb_id

Plug-In Violations

SELECT name,

       cause,

       type,

       message,

       status

  FROM pdb_plug_in_violations

 WHERE type = 'ERROR'

   AND status !='RESOLVED';

PDB1 Sync Failure ERROR "Sync PDB failed with ORA-65177 during 'alter user "xxx" ...' " PENDING 

Possible reasons (1):
  • Delete a common user
  • Having two set of local user ,one in CDB and other in PDB
  • Create / duplicate a PDB

COLUMN name    FORMAT A30

COLUMN sqlstmt FORMAT A100

SELECT ctime,

       sqlstmt,

       name,

       flags,

       opcode,

       replay#

  FROM pdb_sync$; 

To fix...

ALTER SESSION SET CONTAINER=CDB$ROOT;CREATE TABLE bkppdb_sync$ AS SELECT * FROM pdb_sync$;DELETE FROM pdb_sync$ WHERE sqlstmt LIKE ('%alter user "xxx"%');COMMIT;ALTER SESSION SET CONTAINER=PDB;CREATE TABLE bkppdb_sync$ AS SELECT * FROM pdb_sync$;DELETE FROM pdb_sync$ WHERE sqlstmt LIKE ('%alter user "xxx"%');COMMIT;ALTER SESSION SET CONTAINER=CDB$ROOT;ALTER PLUGGABLE database PDB CLOSE IMMEDIATE;ALTER PLUGGABLE database PDB OPEN; 

Switching

When logged into the CDB you can switch between PDBs/CDB like this...

ALTER SESSION SET CONTAINER=mypdb;

ALTER SESSION SET CONTAINER=cdb$root;

sqlplus / as sysdba

This works in 18.8 and 19.x. It does not work in 12.2 or earlier.

See Also

These pages also include Multi-Tenant specific content...

Bibliography & References