Cambios en los parámetros de la base de datos Oracle, cuándo tienen lugar

Existe la creencia un tanto generalizada de que cuando realizamos un cambio en los parámetros de la base de datos Oracle (utilizando el comando SQL ALTER SYSTEM SET ...) mientras la instancia de la base de datos está operativa y si los cambios los realizamos utilizando el fichero de parámetros almacenado (SPFILE), estos cambios no tienen lugar hasta que la base de datos se reinicia. Esta idea es falsa y equivocada, ya que el comando SQL ALTER SYSTEM dispone del parámetro SCOPE para especificar en que momento deben tener efecto los cambios.

El parámetro SCOPE puede tomar los valores MEMORY, SPFILE, or BOTH. Si le asignamos el valor MEMORY el cambio aplicará de forma inmediata siempre que estemos cambiando un parámetro que no requiera el reinicio de la base de datos Oracle para que el cambio sea efectivo. Si le asignamos el valor SPFILE entonces únicamente estaremos modificando el fichero de parámetros almacenado y el cambio tendrá lugar en el siguiente reinicio de la base de datos Oracle. Pero si utilizamos el valor BOTH, entonces el cambio será inmediato y, además, cambiaremos el valor en el fichero de parámetros almacenado (SPFILE).

En este punto concreto, desde programación PLSQL queremos indicar que, dependiendo de si la base de datos Oracle está o no online, podemos encontrarnos con tres tipos diferentes de parámetros:

Parámetros que no pueden cambiarse online

La guía de referencia de bases de datos Oracle (Oracle Database Reference) describe claramente los parámetros de inicialización y sus propiedades, señalando si un parámetro es modificable o no, si un parámetro es no modificable, entonces no podremos cambiarlo mientras la base de datos está online. Por ejemplo es el caso del parámetro AUDIT_TRAIL.

Parámetros que pueden cambiarse online pero sólo para sesiones futuras

En este caso el cambio realizado no afectará a ninguna de las sesiones que estén conectadas a la base de datos en el momento que realicemos en cambio, sino que afectará sólo a las sesiones que se conecten posteriormente a la ejecución del comando ALTER SYSTEM. Es el caso de, por ejemplo, el parámetro SORT_AREA_SIZE:

SQL> ALTER SYSTEM
2    SET sort_area_size = 12345
3    DEFERRED SCOPE=memory;
System altered.

SQL> SHOW PARAMETER sort_area_size

NAME           TYPE    VALUE
-------------- ------- -----
sort_area_size integer 65536

SQL> connect /
Connected.

SQL> SHOW PARAMETER sort_area_size

NAME           TYPE    VALUE
-------------- ------- -----
sort_area_size integer 12345

Parámetros que pueden cambiarse online de forma inmediata

En este caso los cambios se reflejan inmediatamente, propagándose a todas las sesiones que, en ese momento, estén conectadas a la base de datos Oracle. Así ocurre, por ejemplo, con el parámetro USER_DUMP_DEST.

SQL> SHOW PARAMETER user_dump_dest

NAME           TYPE    VALUE
-------------- ------- -----
user_dump_dest string  /tmp

SQL> ALTER SYSTEM
2    SET user_dump_dest = '/u02/tmp';
System altered.

SQL> SHOW PARAMETER user_dump_dest

NAME           TYPE    VALUE
-------------- ------- --------
user_dump_dest string  /u02/tmp

Por otro lado, si lo que queremos es eliminar un parámetro de configuración de la SPFILE, puesto que se trata de un fichero que no se puede editar utilizando un editor de textos, también deberemos utilizar el comando SQL ALTER SYSTEM, pero en este caso utilizando la cláusula RESET. Así, por ejemplo, si lo que queremos es eliminar el parámetro USER_DUMP_DEST y que tome el valor por defecto que hemos sobre escrito anteriormente, deberemos proceder como sigue:

SQL> ALTER SYSTEM
2    RESET user_dump_dest
3    SCOPE=spfile SID='*';
System altered.

En este momento habremos eliminado el parámetro USER_DUMP_DEST del fichero SPFILE cosa que podremos verificar ejecutando el comando SQL:

SQL> CREATE pfile='/tmp/pfile.txt'
2    FROM spfile;
File created.

Ahora, si revisamos el fichero /tmp/pfile.txt veremos que el parámetro USER_DUMP_DEST no aparece en el mismo.