Determine and Set Sizing Parameters for Database Structures

1. Documentación en Tahiti -> Masters Book List -> Administrator’s Guide -> 2 Creating and Configuring an Oracle Database -> Specifying Initialization Parameters

2. Lo primero de todo, vamos a ver cómo localizar y ver los diferentes parámetros de la BD. Aunque todos los parámetros que vamos a ver no afectan a estructuras de la BD, resulta interesante dar un repaso general

# Todos estos ejercicios los hacemos en la Máquina Virtual OCM con el usuario Oracle
[oracle@ocm ~]$ sqlplus / as sysdba

-- Podemos ver los parámetros que hay en efecto en la sesión actual
show parameters

-- También podemos ver el valor de uno o varios parámetros específicos
show parameters db_recovery

-- Este comando utiliza por debajo la vista V$PARAMETER
-- Es equivalente a la siguiente consulta
COL NAME FORMAT A35
COL VALUE FORMAT A30
SELECT NAME, VALUE FROM V$PARAMETER WHERE LOWER(NAME) LIKE '%db_recovery%';

-- Podemos comprobar si hemos arrancando la instancia con SPFILE o PFILE
-- Este parámetro nos da la ubicación del SPFILE o estará vacio si levantamos la BD con PFILE
show parameter spfile

-- Como estamos utilizando SPFILE, podemos ver los valores de la siguiente forma
show spparameters db_recovery

-- La consulta anterior utiliza V$SPPARAMETER, por lo que podemos usar la siguiente SQL
SELECT NAME, VALUE FROM V$SPPARAMETER WHERE LOWER(NAME) LIKE '%db_recovery%';

3. Parámetros DB_NAME y DB_DOMAIN. El nombre de la BD (DB_NAME) no puede ser mayor de 8 caracteres. Está definido en el CONTROLFILE y si no coincide con el valor del parámetro la INSTANCIA no levantará. El parámetro DB_DOMAIN es opcional y puede ser útil en entornos distribuidos donde existen DATABASE LINKS. El nombre global de la BD (GLOBAL DATABASE NAME) estará formado por ambos parámetros.

-- Vemos el valor de ambos parámetros y el nombre global de la BD
show parameter db_name
show parameter db_domain
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'GLOBAL_DB_NAME';

4. Configuración FAST RECOVERY AREA (anteriormente conocida como Flash Recovery Area)

-- Los parámetros clave son db_recovery_file_dest y db_recovery_file_Dest_size
show parameter db_recovery

-- Son modificables sin reiniciar la instancia
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G SCOPE=BOTH;
-- Validamos el cambio
show parameter db_recovery_file_dest_size

-- Oracle se encarga automáticamente de gestionar el espacio en esta ubicación
-- Podemos ver la ocupación en porcentaje de los tipos de ficheros que contiene
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

5. En el parámetro CONTROL_FILES se define la ubicación de los CONTROLFILE.

-- Vemos el valor actual de CONTROL_FILES
show parameter control_files
-- También podemos obtener la ubicación usando la vista V$CONTROLFILE
SELECT NAME FROM V$CONTROLFILE;

Supongamos que queremos añadir una tercera copia del CONTROLFILE

-- Paramos la BD
SHUTDOWN IMMEDIATE

 

# Copiamos uno de los CONTROLFILE a la ubicación donde queremos el nuevo
cp -p /u01/app/oracle/oradata/OCM/control01.ctl /u01/app/oracle/product/11.2.0/dbhome_1/dbs/control03.ctl

 

-- Ponemos la BD en modo NOMOUNT y modificamos el parámetro CONTROL_FILES
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/u01/app/oracle/oradata/OCM/control01.ctl',
'/u01/app/oracle/flash_recovery_area/OCM/control02.ctl',
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/control03.ctl' SCOPE=SPFILE;

-- Paramos, arrancamos la BD y validamos el cambio
SHUTDOWN IMMEDIATE
STARTUP
show parameter control_files

-- Deshacemos los cambios
SHUTDOWN IMMEDIATE
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/u01/app/oracle/oradata/OCM/control01.ctl',
'/u01/app/oracle/flash_recovery_area/OCM/control02.ctl' SCOPE=SPFILE;
STARTUP FORCE

-- Un par de CONSEJOS que te pueden librar de un gran susto e incluso de un gran despido
-- 1. NO SOBRESCRIBAS JAMÁS un CONTROLFILE sin haber comprobado dos veces que tienes un backup alternativo
-- 2. NO SOBRESCRIBAS JAMÁS un backup de un CONTROLFILE (REPITO, NUNCA, NEVER EVER)

6. Veamos la importancia del tamaño del bloque de la BD (DB_BLOCK_SIZE). Por defecto la BD fija el parametro en 8k (DB_BLOCK_SIZE = 8192)

-- Veamos la configuración del tamaño del bloque de BD
show parameter db_block_size

-- Sin embargo, podemos crear TABLESPACES que utilicen otro tamaño
-- Esto puede ser útil si queremos mover TBS entre BBDD con diferente tamaño de bloque (TBS TRANSPORTABLES)
-- También es útil para FACT TABLES (lo veremos en la sección de Data Warehouse Management)

-- Vamos a crear un TBS de ejemplo de 32k
-- Necesitamos habilitar primero la BUFFER CACHE de 32k
ALTER SYSTEM SET DB_32K_CACHE_SIZE=100M SCOPE=BOTH;

-- Ahora añadimos un TABLESPACE de prueba y validamos que se ha creado con un tamaño de 32K
CREATE TABLESPACE TESTDW DATAFILE '/u01/app/oracle/oradata/OCM/testdw01.dbf' SIZE 100M BLOCKSIZE 32K;
SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;

-- Limpiamos el entorno
DROP TABLESPACE TESTDW INCLUDING CONTENTS AND DATAFILES;
ALTER SYSTEM SET DB_32K_CACHE_SIZE=0 SCOPE=MEMORY;
ALTER SYSTEM RESET DB_32_CACHE_SIZE SCOPE=SPFILE;

-- Si tenéis curiosidad, podéis ver cómo Oracle mueve la memoria consultando la vista V$SGA_RESIZE_OPS
-- Se puede ver como se mueven los 100M entre la BUFFER POOL (db_cache_size) y la BUFFER de 32k (db_32k_cache_size)
-- SELECT * FROM V$SGA_RESIZE_OPS ORDER BY END_TIME;

7. Máximo número de procesos o lo mismo, parámetro PROCESSES. Por defecto tiene un valor de 100. Este valor suele ser recomendable aumentarlo, ya que suele ser insuficiente

-- Por defecto PROCESSES = 100 aunque como hemos creado la BD con DBCA, pone el valor por defecto de 150
show parameter processes

-- Vamos a subirlo a 300 y reiniciar la BD ya que es un parámetro estático
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

8. En la versión 11g han introducido un nuevo parámetro, DDL_LOCK_TIMEOUT, para permitir que las sentencias DDL esperen por un LOCK.

-- Veamos el valor en efecto de DDL_LOCK_TIMEOUT para nuestra sesión
-- Aunque también podemos fijar el valor a nivel de instancia
show parameter ddl_lock_timeout

-- Podemos fijar el número de segundos que queramos (Por ej. 10 segundos)
ALTER SESSION SET DDL_LOCK_TIMEOUT=10;

-- Desde otra sesión podemos generar un bloqueo en alguna tabla
CREATE TABLE TEST (COL1 VARCHAR2(10));
INSERT INTO TEST VALUES ('X');

-- Volvemos a la primera sesión e intentamos eliminar la tabla
-- Veremos como espera 10 segundos antes de soltar el error ORA-00054
SET TIMING ON
DROP TABLE TEST;

-- Limpiamos el entorno en la segunda sesión
ROLLBACK;
DROP TABLE TEST;
-- Fijamos el valor a 0 en la primera sesión
ALTER SESSION SET DDL_LOCK_TIMEOUT=0;

9. Es posible que alguna vez metáis algún parámetro estático en el SPFILE y luego intentáis reiniciar la instancia y falle por algún motivo. Vamos a practicar la recuperación de este escenario, que es más frecuente de lo que nos imaginamos.

-- Para forzar un fallo en el reinicio vamos a subir el parámetro MEMORY_TARGET. Por Ej. 200G
ALTER SYSTEM SET MEMORY_TARGET = 200G SCOPE=SPFILE;

-- Paramos e intentamos levantar la instancia
-- Debe aparecer el error "ORA-00845: MEMORY_TARGET not supported on this system", a no ser que tengáis más de 200G libres
SHUTDOWN IMMEDIATE
STARTUP

-- Como la instancia ni siquiera ha llegado al estado NOMOUNT, no podemos modificar el SPFILE con SQL*Plus
-- Pero si podemos generar un PFILE a partir del SPFILE sin que hayamos abierto la BD
CREATE PFILE='/tmp/initOCM.ora' FROM SPFILE;

-- Ahora tenemos que editar el fichero /tmp/initOCM.ora y modificar la línea de MEMORY_TARGET para dejarlo como estaba
-- vi /tmp/initOCM.ora
-- Modificamos MEMORY_TARGET=500M

-- Ahora ponemos levantamos la instancia en modo MOUNT usando el PFILE modificado
STARTUP NOMOUNT PFILE='/tmp/initOCM.ora'

-- Restauramos el SPFILE a partir del PFILE modificado y reiniciamos la instancia
CREATE SPFILE FROM PFILE='/tmp/initOCM.ora'
SHUTDOWN IMMEDIATE
STARTUP

10. Existe una forma más rápida para ver el contenido de un fichero binario SPFILE y es utilizar el comando “string”

# El comando string extrae las cadenas de texto plano de al menos 4 caracteres que hay en un fichero binario
strings /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCM.ora

# Esto también nos sirve para generar un PFILE
# Aunque es posible que tengáis que corregir las lineas largas (En el ejemplo de abajo hay que concatenar el parámetro control_files)
strings /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCM.ora > /tmp/initOCM.ora

11. Echemos un vistazo a los parámetros que tenemos en nuestra BD OCM con un pequeño comentario a cada uno

*.audit_file_dest='/u01/app/oracle/admin/OCM/adump'         # Ruta en sistema de auditorias. Si se llena no entras a la BD
*.audit_trail='db'                                          # Específica que las auditorías se escriban solo en BD
*.compatible='11.2.0.0.0'                                   # Valor por defecto para poder utilizar las funcionalidades de la 11gR2
*.control_files='/u01/app/oracle/oradata/OCM/control01.ctl',  # Utilizad mínimo dos copias, recomendable 3
                '/u01/app/oracle/flash_recovery_area/OCM/control02.ctl'
*.db_block_size=8192                                        # 8192 = 8k es el tamaño estándar para BBDD OLTP
*.db_domain=''                                              # Opcional, utilizado en entornos distribuidos
*.db_name='OCM'                                             # Este es el único parámetro obligatorio para levantar una BD
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' # Ubicación de la FRA. Se recomienda tenerlo en otro disco al de la BD
*.db_recovery_file_dest_size=5368709120                     # Tamaño min. recomendado = TAMAÑO DATAFILES + ARCH. GENERADOS EN UN DÍA
*.diagnostic_dest='/u01/app/oracle'                         # Ubicación ADR "Automatic Diagnostic Recovery"
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OCMXDB)'             # Configuración Dispatcher por defecto para el servicio XML DB
*.log_archive_format='%t_%s_%r.dbf'                         # Formato por defecto de ARCHIVELOGS (%thread_%sequence_%resetlogsID)
*.memory_target=500M                                        # Utilizamos AMM = ASMM más movimiento de memoria entre PGA y SGA
*.open_cursors=300                                          # Máximo número de cursos abiertos
*.processes=300                                             # Hemos aumentado este valor a 300. Este valor es muy variable.
*.remote_login_passwordfile='EXCLUSIVE'                     # EL fichero de password (orapwOCM) sólo puede ser usado por una BD
*.undo_tablespace='UNDOTBS1'                                # Tablespace de UNDO (Si lo omitimos, la BD usaría el primer TBS de UNDO)