Category: CDB/PDB

How to Check PDB is compatible with a new CDB

To test if a PDB is compatible with a new CDB before plugging it in, use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function in the target CDB. This function checks for incompatibilities like patch levels, character sets, database options, and the COMPATIBLE parameter setting.

Generate a PDB Description File (XML): Before you can run the check, you must have an XML metadata file that describes the PDB. This is usually created during the unplugging process from the source CDB or by using DBMS_PDB.DESCRIBE.
Access to Target CDB: You need SYSDBA privileges on the target CDB.

— On Source CDB

BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/tmp/pdb_description.xml',
    pdb_name       => 'YOUR_PDB_NAME');
END;
/
 $ grep -v "<parameter>" /tmp/pdb_description.xml > /tmp/pdb_description.xml_no_param.xml

— On Target CDB

SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
      pdb_descr_file => '/tmp/pdb_description.xml',
      pdb_name       => 'NEW_PDB_NAME')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
    END;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible? ==> ' || compatible);
END;
/

Check for Violations

COLUMN MESSAGE FORMAT A60
COLUMN STATUS FORMAT A10
SELECT NAME, TYPE, MESSAGE, STATUS FROM PDB_PLUG_IN_VIOLATIONS 
ORDER BY TIME;

How to Switch or Recreate Undo Tablespace at PDB Level

From 12.2.0.1 onwards, local undo tablespace is introduced in PDB level. In many cases, the automatically created undo tablespace in PDB is not complying with naming standard. For example:

SQL>  select inst_id,name, con_id, value 
from gv$system_parameter 
where name='undo_tablespace' and con_id=3 
order by 1;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               3 UNDO_3
SQL> SELECT property_name, property_value 
FROM database_properties 
WHERE property_name = 'LOCAL_UNDO_ENABLED';  

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

RESOLUTION

Rename undo tablespace 

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL>  select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
UNDO_3

SQL> alter tablespace undo_3 rename to UNDOTBS3;

Tablespace altered.

SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
UNDOTBS3

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO

SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
undo_tablespace  
                    string      UNDO_3

SQL> ALTER SYSTEM set  UNDO_TABLESPACE='UNDOTBS3' scope=both;

System altered.

SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
undo_tablespace                      string      UNDOTBS3

Close PDB1 and start PDB1 to check further whether UNDOTBS3 assigned to instance 3.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
undo_tablespace                      string      UNDOTBS3

-- checked in spfile for PDB1??
SQL> create pfile='/tmp/x.x' from spfile;

File created.

SQL> ! cat /tmp/x.x
*.undo_tablespace='UNDOTBS3'

Check parameter UNDO_TABLESPACE in CDB and PDB, all looks good.

SQL>  select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

Finally reset undo_tablespace parameter in PDB , and bounce PDB on instance 3:

SQL> alter session set container=pdb1;

Session altered.

SQL>  alter system reset undo_tablespace;

System altered.

SQL> create pfile='/tmp/x.x' from spfile;

File created.

SQL> ! cat /tmp/x.x

SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               3 UNDOTBS3

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 MOUNTED

6 rows selected.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.


SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS3
SQL>
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;


   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

Recreate a New Undo Tablespace

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDB1                               MOUNTED

SQL> alter session set container=PDB1;


SQL> alter pluggable database PDB1 open restricted;

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        3 PDB1                           READ WRITE YES


SQL> CREATE UNDO TABLESPACE UNDOTBS3 datafile '+DATA2'  SIZE 100M AUTOEXTEND ON ;

Tablespace created.

--
-- can NOT drop UNDO_3 now
--
SQL> DROP tablespace UNDO_3 including contents and datafiles;
DROP tablespace UNDO_3 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO_3' is currently in use

--
-- Here the following NO need scope=spfile 
--
SQL> ALTER SYSTEM set  UNDO_TABLESPACE='UNDOTBS3';

 System altered.

SQL> DROP tablespace UNDO_3 including contents and datafiles;

Tablespace dropped.


SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;


   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

Local and Shared Undo Tablespace Modes

Oracle introduced Local Undo since 12cR2. Before 12cR2, there is no local undo concept, the Undo tablespace used to be shared or global across all the PDBs in a container.

How to check the current Undo mode

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        TRUE       true if local undo is enabled

How to convert CDB from Local to Shared Undo Mode

When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

SQL> startup upgrade

SQL> sho con_name 

CON_NAME
-----------------
CDB$ROOT

SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.

 
SQL> shutdown immediate

SQL> startup

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        FALSE       true if local undo is enabled

-- Check current undo tablespaces for CDB and all PDBS.
--
SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

 
--
-- Drop the Undo tablespace from the PDBs
--
SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.

How to convert CDB from Shared to Local Undo Mode

When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

SQL> startup upgrade

SQL>  alter database local undo on;

SQL> startup force;

-- Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace
--
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

SQL> CREATE UNDO TABLESPACE seed_undo datafile '+DATA1'......

SQL> alter system set  UNDO_TABLESPACE='SEED_UNDO' scope=both;

SQL> show parameter undo_tablespace
undo_tablespace                      string      SEED_UNDO


SQL>  ALTER SESSION SET CONTAINER=CDB$ROOT; 

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

-- Open the PDB is read write mode

SQL> ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE;

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
-------------------------------------------------------------------
         3 UNDO_1

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf  << Undo tablespace is created automatically.

          1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

-- From the PDB
--

SQL> ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
---------------------------------------------------------------

         3 UNDO_1 << Undo tablespace is created automatically.

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

Datapatch: Unsupported named object type for bind parameter error

While running “datapatch -verbose”, the following errors occur:

$ ./datapatch -verbose
...
..
.


Unsupported named object type for bind parameter at /u01/app/oracle/product/19.0.0/dbhome_1/sqlpatch/sqlpatch.pm line 5827.


Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_52411_2025_01_01_10_37_20/sqlpatch_invocation.log
for information on how to resolve the above errors.

Check line 5827 of file “/u01/app/oracle/product/19.0.0/dbhome_1/sqlpatch/sqlpatch.pm”:

5827 $patch_metadata_stmt->bind_param(":patch_descriptor", $descriptor, {ora_type => ORA_XMLTYPE});

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe