This is a weird case we had for a Customer where DB/PDB’s were running fine and after a restart, one of the PDB’s went to restricted YES.
We don’t know why it went to restricted mode because there hasn’t been a patching activity for a while and DB had few restarts in the past with no issues.
We did not manage to know how this happened, but this is how we fixed.
INST HOST INST_NAME STATUS DB_ROLE OPEN_MODE STARTUP_TIME
---- ------------------ ---------- ---------- ----------------- --------------------- -------------------------
*1 DB_NODE PROD_DB_1 OPEN PRIMARY READ WRITE 11-FEVR.-2021 23:47:01
INST_ID NAME OPEN_MODE RES OPEN_TIME
---------- -------------------- --------------------- --- ------------------------------
1 PDB$SEED READ ONLY NO 11-FEVR.-2021 23:47:48
1 PDB_1 READ WRITE NO 11-FEVR.-2021 23:47:49
1 PDB_2 READ WRITE YES 11-FEVR.-2021 23:47:49 <<-------------
SQL> select name, cause, type, message, status, action from PDB_PLUG_IN_VIOLATIONS where type like 'ERROR%' and status like 'PENDING' ;
NAME CAUSE TYPE MESSAGE STATUS ACTION
------- -------------------- --------- ---------------------------------------------------------------------- ---------- ------------------------------
PDB_2 SQL patch error ERROR DBRU bundle patch 200714 (DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.20 PENDING Call datapatch to reinstall
0714): APPLY with status WITH ERRORS (RETRYABLE) in the PDB.
PDB_2 SQL Patch ERROR SQL patch ID/UID 30133625/23095898 (OJVM RELEASE UPDATE: 12.2.0.1.1910 PENDING Call datapatch to install in t
15 (30133625)): Installed in the PDB but not in the CDB. he PDB or the CDB
PDB_2 SQL Patch ERROR DBRU bundle patch 200714 (DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.20 PENDING Call datapatch to install in t
0714): Installed in the CDB but not in the PDB. he PDB or the CDB
PDB_2 SQL Patch ERROR SQL patch ID/UID 30133625/23095898 (OJVM RELEASE UPDATE: 12.2.0.1.1910 PENDING Call datapatch to install in t
15 (30133625)): Installed in the CDB but not in the PDB. he PDB or the CDB
SQL> alter session set container=PDB_2;
Session modifiee.
SQL> show con_name
CON_NAME
------------------------------
PDB_2
SQL>
select BUNDLE_SERIES,PATCH_UID,PATCH_ID,VERSION,ACTION,STATUS,ACTION_TIME ,DESCRIPTION from dba_registry_sqlpatch;
BUNDLE_SERIES PATCH_UID PATCH_ID VERSION ACTION STATUS ACTION_TIME DESCRIPTION
--------------- ---------- ---------- ---------- --------- ------------------------ ------------------------ --------------------------------------------------
...
22597359 28790651 12.2.0.1 ROLLBACK SUCCESS 20-01-25 04:31:54,018927 OJVM RELEASE UPDATE: 12.2.0.1.190115 (28790651)
23095898 30133625 12.2.0.1 APPLY SUCCESS 20-01-25 04:31:54,025800 OJVM RELEASE UPDATE: 12.2.0.1.191015 (30133625)
DBRU 23136381 30138470 12.2.0.1 APPLY SUCCESS 20-01-25 04:31:55,895024 DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015
DBRU 23691705 31312468 12.2.0.1 APPLY WITH ERRORS (RETRYABLE) 20-11-22 05:54:37,738658 DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.200714
DBRU 23691705 31312468 12.2.0.1 APPLY WITH ERRORS (RETRYABLE) 20-11-22 07:20:32,085943 DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.200714 <<-----
13 lignes selectionnees.
SQL>
We tried the “datapatch -verbose” but this did not help:
SQL> alter pluggable database PDB_2 close immediate;
Base de donnees pluggable modifiee.
SQL>
SQL> alter pluggable database PDB_2 open upgrade;
Base de donnees pluggable modifiee.
SQL>
oracle@DB_NODE:~$ /u01/app/oracle/product/12.2.0.1/dbhome_1/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Fri Feb 12 08:43:48 2021
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18174_2021_02_12_08_43_48/sqlpatch_invocation.log
Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Patch 28440725 (OJVM RELEASE UPDATE: 12.2.0.1.181016 (28440725)):
Not installed in the binary or the SQL registry
Patch 28790651 (OJVM RELEASE UPDATE: 12.2.0.1.190115 (28790651)):
Not installed in the binary or the SQL registry
Patch 30133625 (OJVM RELEASE UPDATE: 12.2.0.1.191015 (30133625)):
Installed in the binary registry and CDB$ROOT PDB$SEED PDB_1 PDB_2
Bundle series DBRU:
ID 200714 in the binary registry and ID 200714 in PDB CDB$ROOT, ID 200714 in PDB PDB$SEED, ID 200714 in PDB PDB_1, ID 200714 with errors in PDB PDB_2
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB_1 PDB_2
Nothing to roll back
Nothing to apply
SQL Patching tool complete on Fri Feb 12 08:45:51 2021
oracle@DB_NODE:~$ This is how we fixed, we forced the patch “31312468/23691705”:
oracle@DB_NODE:~$ /u01/app/oracle/product/12.2.0.1/dbhome_1/OPatch/datapatch -verbose -apply 31312468/23691705 -force -pdbs PDB_2
SQL Patching tool version 12.2.0.1.0 Production on Fri Feb 12 11:39:37 2021
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_17882_2021_02_12_11_39_37/sqlpatch_invocation.log
Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Patch 28440725 (OJVM RELEASE UPDATE: 12.2.0.1.181016 (28440725)):
Not installed in the binary or the SQL registry
Patch 28790651 (OJVM RELEASE UPDATE: 12.2.0.1.190115 (28790651)):
Not installed in the binary or the SQL registry
Patch 30133625 (OJVM RELEASE UPDATE: 12.2.0.1.191015 (30133625)):
Installed in PDB_2 only
Patch 31312468 ():
Not installed in the binary or the SQL registry
Bundle series DBRU:
ID 200714 in the binary registry and ID 200714 with errors in PDB PDB_2
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: PDB_2
Nothing to roll back
The following patches will be applied:
31312468 (DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.200714)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 31312468 apply (pdb PDB_2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31312468/23691705/31312468_apply_PROD_DB_PDB_2_2021Feb12_11_39_41.log (no errors)
SQL Patching tool complete on Fri Feb 12 11:47:55 2021
oracle@DB_NODE:~$
SQL> alter pluggable database PDB_2 close immediate;
Base de donnees pluggable modifiee.
SQL> alter pluggable database PDB_2 open ;
Base de donnees pluggable modifiee.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB_1 READ WRITE NO
5 PDB_2 READ WRITE NO
SQL> alter session set container=PDB_2;
Session modifiee.
SQL> select BUNDLE_SERIES,PATCH_UID,PATCH_ID,VERSION,ACTION,STATUS,ACTION_TIME ,DESCRIPTION from dba_registry_sqlpatch;
BUNDLE_SERIES PATCH_UID PATCH_ID VERSION ACTION STATUS ACTION_TIME DESCRIPTION
--------------- ---------- ---------- ---------- -------- ------------------------ ------------------------ --------------------------------------------------
22459205 28440725 12.2.0.1 APPLY SUCCESS 19-05-31 09:41:34,584326 OJVM RELEASE UPDATE: 12.2.0.1.181016 (28440725)
DBRU 22500316 28662603 12.2.0.1 APPLY SUCCESS 19-05-31 09:41:35,168888 DATABASE OCT 2018 RELEASE UPDATE 12.2.0.1.181016
22459205 28440725 12.2.0.1 ROLLBACK WITH ERRORS 19-08-19 10:21:20,829716 OJVM RELEASE UPDATE: 12.2.0.1.181016 (28440725)
22597359 28790651 12.2.0.1 APPLY WITH ERRORS 19-08-19 10:21:20,831092 OJVM RELEASE UPDATE: 12.2.0.1.190115 (28790651)
DBRU 22643228 28822515 12.2.0.1 APPLY WITH ERRORS 19-08-19 10:21:20,832200 DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
22459205 28440725 12.2.0.1 ROLLBACK SUCCESS 19-08-19 10:36:48,221427 OJVM RELEASE UPDATE: 12.2.0.1.181016 (28440725)
22597359 28790651 12.2.0.1 APPLY SUCCESS 19-08-19 10:36:48,227307 OJVM RELEASE UPDATE: 12.2.0.1.190115 (28790651)
DBRU 22643228 28822515 12.2.0.1 APPLY SUCCESS 19-08-19 10:36:48,271799 DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
22597359 28790651 12.2.0.1 ROLLBACK SUCCESS 20-01-25 04:31:54,018927 OJVM RELEASE UPDATE: 12.2.0.1.190115 (28790651)
23095898 30133625 12.2.0.1 APPLY SUCCESS 20-01-25 04:31:54,025800 OJVM RELEASE UPDATE: 12.2.0.1.191015 (30133625)
DBRU 23136381 30138470 12.2.0.1 APPLY SUCCESS 20-01-25 04:31:55,895024 DATABASE OCT 2019 RELEASE UPDATE 12.2.0.1.191015
DBRU 23691705 31312468 12.2.0.1 APPLY WITH ERRORS (RETRYABLE) 20-11-22 05:54:37,738658 DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.200714
DBRU 23691705 31312468 12.2.0.1 APPLY WITH ERRORS (RETRYABLE) 20-11-22 07:20:32,085943 DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.200714
DBRU 23691705 31312468 12.2.0.1 APPLY SUCCESS 21-02-12 11:47:54,738592 DATABASE JUL 2020 RELEASE UPDATE 12.2.0.1.200714 <<----
14 lignes selectionnees.
SQL>
Comments
Thank you/Merci Alex. helped me solve my problem.
Thanks for checkign this page, glad it fixed your problem!
Thanks for the information. Helped me a lot!