PDB Read Write con RESTRICTED YES

El otro día haciendo tareas de mantenimiento en una base de datos me encontré con el siguiente caso a nivel de PDB, estaba en modo read write pero con restricciones.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 XXXXXX                         READ WRITE YES

¿Qué podemos hacer en estos casos? Lo primero que tenemos que hacer es mirar la vista PDB_PLUG_IN_VIOLATIONS. La funcionalidad de la vista es decirnos posibles errores o advertencias que podamos tener a nivel de PDB.

Así que vamos a consultar la vista por si nos da una pista.

SQL> set linesize 1000 pages 40
SQL> column name format a10
SQL> column message format a60
SQL> column message format a100
SQL> Select Name, Cause, Type, Message, Status From Pdb_Plug_In_Violations Where Name = 'XXXXXX' And Status != 'RESOLVED' And Type='ERROR' Order By Name;

NAME       CAUSE                     TYPE      MESSAGE                                                                                              STATUS
---------- ------------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
XXXXXX     SQL patch error           ERROR     Interim patch 36414915/25700138 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)): APPLY with statu PENDING
                                               s WITH ERRORS (PREV PATCH) in the PDB.

XXXXXX     SQL Patch                 ERROR     Interim patch 36414915/25700138 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)): Installed in the PENDING
                                                CDB but not in the PDB

XXXXXX     SQL Patch                 ERROR     19.24.0.0.0 Release_Update 2406272351: APPLY with status WITH ERRORS in the PDB                      PENDING

Parece que cuando se aplicó el parche de la 19.24 algo falló o se hizo de manera incompleta.

En un primer vistazo, vemos una nota en soporte que puede encajar: Datapatch precheck failed : Release update is BEING installed to PDB but is not installed in CDB$ROOT.This will cause a patch mismatch between this PDB and CDB$ROOT (Doc ID 2604940.1).

El sr indica lo siguiente:

Datapatch precheck fails with message like below :
Error: prereq checks failed!
Release update 18.7.0.0.0 Release_Update 1906241920 is being installed to PDB but is not installed in CDB$ROOT.
This will cause a patch mismatch between this PDB and CDB$ROOT and prevent the PDB from opening. Please use the allow_pdb_mismatch option if this is really intended.

*Is successfully applied in CDB$ROOT, but is to be rolled back from PDB.
This will cause a patch mismatch between PDB and CDB$ROOT and prevent the PDB from opening unrestricted. Please use the allow_pdb_mismatch option if this is really intended.

OR*

Is not successfully installed in CDB$ROOT but is to be applied to PDB.
This will cause a patch mismatch between PDB and CDB$ROOT and prevent the PDB from opening unrestricted. Please use the allow_pdb_mismatch option if this is really intended.

Pues vamos a ello, vamos a ejecutar el datapatch a nivel de CDB$Root y a nivel de PDB.

A nivel de CDB$Root

[oracle@~]$ $ORACLE_HOME/OPatch/datapatch -verbose -pdbs CDB\$ROOT
SQL Patching tool version 19.24.0.0.0 Production on Tue Dec  3 13:16:07 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4525_2024_12_03_13_16_07/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
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 interim SQL patches:
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 26-AUG-24 09.34.59.323391 AM
Interim patch 36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 26-AUG-24 09.34.59.399290 AM
Current state of release update SQL patches:
  Binary registry:
    19.24.0.0.0 Release_Update 240627235157: Installed
  PDB CDB$ROOT:
    Applied 19.24.0.0.0 Release_Update 240627235157 successfully on 26-AUG-24 09.36.43.282831 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied
SQL Patching tool complete on Tue Dec  3 13:16:22 2024

A nivel de PDB

[oracle@~]$ $ORACLE_HOME/OPatch/datapatch -verbose -pdbs XXXXXX
SQL Patching tool version 19.24.0.0.0 Production on Tue Dec  3 13:38:59 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27885_2024_12_03_13_38_59/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
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 interim SQL patches:
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
  Binary registry: Not installed
  PDB XXXXXX: Rolled back successfully on 26-AUG-24 09.37.16.051518 AM
Interim patch 36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)):
  Binary registry: Installed
  PDB XXXXX: Applied with errors on 03-DEC-24 01.23.52.810962 PM
Current state of release update SQL patches:
  Binary registry:
    19.24.0.0.0 Release_Update 240627235157: Installed
  PDB XXXXX:
    Applied 19.24.0.0.0 Release_Update 240627235157 with errors on 03-DEC-24 01.29.56.096997 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: XXXXXX
    No interim patches need to be rolled back
    Patch 36582781 (Database Release Update : 19.24.0.0.240716 (36582781)):
      Apply from 19.18.0.0.0 Release_Update 230127005551 to 19.24.0.0.0 Release_Update 240627235157
    The following interim patches will be applied:
      36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915))
Installing patches...
Patch installation complete.  Total patches installed: 2
Validating logfiles...done
Patch 36582781 apply (pdb XXXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_XXXXX_XXXX_2024Dec03_13_39_15.log (no errors)
Patch 36414915 apply (pdb XXXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36414915/25700138/36414915_apply_XXXXX_XXXXX_2024Dec03_13_39_15.log (no errors)
SQL Patching tool complete on Tue Dec  3 13:40:31 2024

Perfecto, se han ejecutado a nivel de CDB$Root y PDB sin errores. Vamos con el siguiente paso de la nota en el que tendremos que modificar a nivel de sesión el parámetro _oracle_script.

¿Para qué sirve el parámetro _oracle_script? indica a Oracle que lo que va a ejecutarse a continuación es un script oficial y, por lo tanto, puede "saltarse" un poco las reglas de nomenclatura para objetos, usuarios, etc. así como ciertas comprobaciones en nombres, etc. Otra cosa importante de este parámetro, es que empieza con “_”, con lo que nos indica que es un parámetro reservado.

[oracle@~]$ sqlplus /nolog
SQL> conn / as sysdba
SQL> alter session set container=XXXXXX;
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> alter pluggable database XXXXX close immediate instances=all;
Pluggable database altered.
SQL>  alter pluggable database XXXXX OPEN READ WRITE;
Pluggable database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> exit

Ejecutamos de nuevo el datapatch, pero esta vez solo a nivel de PDB.

[oracle@~]$ $ORACLE_HOME/OPatch/datapatch -verbose -pdbs XXXXX
SQL Patching tool version 19.24.0.0.0 Production on Tue Dec  3 13:44:29 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_33988_2024_12_03_13_44_29/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
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 interim SQL patches:
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
  Binary registry: Not installed
  PDB XXXXXX: Rolled back successfully on 26-AUG-24 09.37.16.051518 AM
Interim patch 36414915 (OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)):
  Binary registry: Installed
  PDB XXXXXX: Applied successfully on 03-DEC-24 01.39.15.876612 PM
Current state of release update SQL patches:
  Binary registry:
    19.24.0.0.0 Release_Update 240627235157: Installed
  PDB XXXXXX:
    Applied 19.24.0.0.0 Release_Update 240627235157 successfully on 03-DEC-24 01.40.17.570953 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: XXXXXX
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied

Como último paso modificamos el parámetro _oracle_script a false y chequeamos si se ha quitado la restricción a nivel de PDB

SQL>  alter session set "_oracle_script"=FALSE;
Session altered.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 XXXXXX                         READ WRITE NO

Genial, se ha abierto la PDB en modo read write sin restricciones.

Revisamos de nuevo la vista PDB_PLUG_IN_VIOLATIONS para verificar que está todo correcto a nivel de PDB.

SQL> set linesize 1000 pages 40
SQL> column name format a10
SQL> column message format a60
SQL> column message format a100
SQL> Select Name, Cause, Type, Message, Status From Pdb_Plug_In_Violations Where Name = 'XXXXXXX' And Status != 'RESOLVED' And Type='ERROR' Order By Name;

   no rows selected

Perfecto, problema resuelto. ¡Espero que os sirva!

PDB Read Write con RESTRICTED YES

Comments

OCI

[OCI] housekeeping /u01

More from this blog

[26ai] SQL: Calendar functions/Aggregation Filters "CALENDAR"

[OCI] Recreación PDB$SEED

[OCI] Clone PDB

[OCI] Check Jobs DBCli

[26ai] JOIN_TO_ME

Command Palette

Comments

OCI

[OCI] housekeeping /u01

More from this blog