How to Resolve "Warning: PDB altered with errors."

Warning: PDB altered with errors

Tried to open a newly cloned or created PDB, but it failed with "Warning: PDB altered with errors".

SQL> alter pluggable database ORCLPDB open;

Warning: PDB altered with errors.

"Warning: PDB altered with errors" means that the PDB has errors while opening it, you should check PDB_PLUG_IN_VIOLATIONS in alert log for more information.

Let's see its status.

SQL> show pdbs

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

It seems that we cannot open the newly cloned PDB normally, it fell into restricted mode. Furthermore, we saw something in alert log.

PDB_PLUG_IN_VIOLATIONS

Let's see what we found in alert log.

[oracle@primary01 ~]$ vi $ORACLE_BASE/diag/rdbms/<db_unique_name_in_lower_case>/$ORACLE_SID/trace/alert_$ORACLE_SID.log
...
ORCLPDB(3):***************************************************************
ORCLPDB(3):WARNING: Pluggable Database ORCLPDB with pdb id - 3 is
ORCLPDB(3):         altered with errors or warnings. Please look into
ORCLPDB(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
ORCLPDB(3):***************************************************************

Want to know where Oracle alert log is? You may take a look.

Our goal is to make the PDB get rid of restricted mode, but how can we remove the restricted mode? Apparently, we can't get out by normal ways, we have to solve errors behind first.

In this post, we have two error patterns that throw Warning: PDB altered with errors or ORA-65144.

1. SQL Patch Level
2. Common Users

SQL Patch Level

Such message in alert log indicates us to check errors in PDB_PLUG_IN_VIOLATIONS. So next, let's see what we can find in PDB_PLUG_IN_VIOLATIONS.

Errors in PDB_PLUG_IN_VIOLATIONS

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> set linesize 200;
SQL> column name format a10;
SQL> column cause format a10;
SQL> column type format a10;
SQL> column message format a30;
SQL> column action format a30; SQL> select name, cause, type, message, action from PDB_PLUG_IN_VIOLATIONS where status = 'PENDING' and con_id = 3;

NAME       CAUSE      TYPE       MESSAGE                        ACTION
---------- ---------- ---------- ------------------------------ ------------------------------
ORCLPDB    SQL Patch  ERROR      Interim patch 33192694/2444113 Call datapatch to install in t
                                 4 (OJVM RELEASE UPDATE: 19.13. he PDB or the CDB
                                 0.0.211019 (33192694)): Instal
                                 led in the CDB but not in the
                                 PDB

In the view, it provides some valuable information including cause and solution for you.

Solutions

In most cases, it's an unmatched SQL patch level problem, more specifically, the cloned PDB does not match the SQL patch level of the container.

Here we perfom a SQL patching (datapatch -verbose) to solve ORA-65144.

[oracle@primary01 ~]$ $ORACLE_HOME/OPatch/datapatch -verbose
...

Then we check the patching level of the PDB after SQL patching. This time, we query CDB_REGISTRY_SQLPATCH which is also a data dictioanry view derived from DBA_REGISTRY_SQLPATCH.

SQL> set linesize 200;
SQL> column action format a10;
SQL> column status format a10;
SQL> column action_time format a30;
SQL> column description format a30;
SQL> select patch_id, action, status, action_time, description from cdb_registry_sqlpatch where con_id = 3;

  PATCH_ID ACTION     STATUS     ACTION_TIME                    DESCRIPTION
---------- ---------- ---------- ------------------------------ ------------------------------
  29517242 APPLY      SUCCESS    26-MAR-21 09.54.18.281421 PM   Database Release Update : 19.3
                                                                .0.0.190416 (29517242)

  33192793 APPLY      SUCCESS    25-DEC-21 08.49.32.417971 PM   Database Release Update : 19.1
                                                                3.0.0.211019 (33192793)

  33192694 APPLY      SUCCESS    01-FEB-22 08.33.21.673815 PM   OJVM RELEASE UPDATE: 19.13.0.0
                                                                .211019 (33192694)

Please note that, using ALTER SYSTEM DISABLE RESTRICTED SESSION is NOT the right way to leave the restricted mode in such case, restart is.

You may normally restart the PDB now.

SQL> alter pluggable database ORCLPDB close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB open instances=all;

Pluggable database altered.

Common Users

Common users in a multitenant environment must be synchronized to the newly cloned PDB while opening the PDB for the first time, but DDL of common users may not sucessfully be executed.

Errors in PDB_PLUG_IN_VIOLATIONS

As usual, we should check PDB_PLUG_IN_VIOLATIONS.

SQL> set linesize 200;
SQL> column name format a10;
SQL> column cause format a10;
SQL> column type format a10;
SQL> column message format a60;
SQL> select name, cause, type, message from PDB_PLUG_IN_VIOLATIONS where status = 'PENDING' and con_id = 3;

NAME       CAUSE           TYPE       MESSAGE
---------- --------------- ---------- ------------------------------------------------------------
ORCLPDB    Sync Failure    ERROR      Sync PDB failed with ORA-959 during 'alter user c##admin quo
                                      ta 10g on erp_tbs01'

In the above, a DDL failed with ORA-00959 while synchronizing common users to the newly cloned PDB.

ORA-00959 in the result means that the tablespace you specified in the statement cannot be found in the database, you should check your syntax or create the right one for it.

Solution

Obviously, the newly cloned PDB does not have tablespace ERP_TBS01, so the solution is to create one for it in this PDB.

SQL> alter session set container=ORCLPDB;

Session altered.

SQL> create tablespace erp_tbs01 datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/erp_tbs01_01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

You don't have to retry the failed DDL, it will be synchronized from the container when the PDB is open again.

After solving all errors in PDB_PLUG_IN_VIOLATIONS, we can restart the PDB.

SQL> alter pluggable database ORCLPDB close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB open instances=all;

Pluggable database altered.

Let's see the PDB status.

SQL> show pdbs

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

The PDB is normally opened, no more restricted mode.