#JoelKallmanDay Parámetro oculto "_pdb_auto_save_state" en Oracle Container Database

Cuando implementamos una base de datos en Contenedor, es muy normal que vayamos aumentando la cantidad de bases de datos acopladas (pluggable database).

Cuando una PDB sea crea, de facto está queda en estado "MOUNT" y es necesario abrir la base de datos para poder trabajar con ella.

Sin embargo, si tenemos que reiniciar el contenedor las PDBs quedan en el estado original o en el último estado en la que la hemos guardado.

Hay una forma en como mantener el último estado registrado para todos los PDBs en el Contenedor, sin necesidad de preocuparnos de estar al tanto del estado de PDBs.

Esto se logra configurando el parámetro oculto "_pdb_auto_save_state" en el Contenedor de la base de datos, como se demuestra a continuación.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 PDB1_DR                        MOUNTED

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB1_DR                        MOUNTED
         
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 7868511032 bytes
Fixed Size                  9196344 bytes
Variable Size            1509949440 bytes
Database Buffers         6341787648 bytes
Redo Buffers                7577600 bytes
Database mounted.
Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 PDB1_DR                        MOUNTED

SQL> alter system set "_pdb_auto_save_state"=TRUE scope=both;
System altered.

SQL> alter pluggable database PDB2 open;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB1_DR                        MOUNTED

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 7868511032 bytes
Fixed Size                  9196344 bytes
Variable Size            1509949440 bytes
Database Buffers         6341787648 bytes
Redo Buffers                7577600 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           READ WRITE NO
         5 PDB1_DR                        MOUNTED

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB1_DR                        MOUNTED

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 7868511032 bytes
Fixed Size                  9196344 bytes
Variable Size            1509949440 bytes
Database Buffers         6341787648 bytes
Redo Buffers                7577600 bytes
Database mounted.
Database opened.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB1_DR                        MOUNTED
SQL>