Tareas administrativas de un SGBD

Documento escrito por: Fernando Lucendo García

Puedes ver todos los documentos de este autor aquí.

Conceptos previos:

SGBD: Sistema gestor de base de datos

BD: Base de datos

Instancia: Una instancia de base de datos puede contener varias bases de datos creadas por el usuario y se puede obtener acceso a ella utilizando las mismas herramientas y aplicaciones cliente que podría usar para obtener acceso a una instancia de base de datos individual.

Vista: Una vista de base de datos es un subconjunto de una base de datos y se basa en una consulta que se ejecuta en una o más tablas de base de datos.

Tablespace: Un tablespace es un almacén lógico de los ficheros de la base de datos. Cada tablespace posee uno o varios ficheros (datafiles) donde almacena toda la información.

PGA: El Program Global Area es un área de memoria utilizada por un proceso Oracle.

SGA: El Área Global de Sistema SGA por sus siglas en inglés (System Global Area) es un grupo de estructuras de memoria compartida, conocidas como componentes SGA las cuales contienen datos e información de control para una instancia de Oracle Database

---

ARRANQUE Y PARADA DE LA BD

Para arrancar la instancia el servidor lee el fichero de parámetros de inicialización de la BD. Está ubicado en $ORACLE_HOME/dbs y puede ser:

• initSID.ora: Fichero de texto, editable.

  • No puede ser incluido en backups con RMAN.
  • Los init deben estar en el sistema para realizar el arranque
  • Al realizar cambio de parámetro debemos escribirlo en el archivo manualmente para conservar el cambio en el nuevo arranque
  • Aunque usemos el spfile, no podemos olvidar el init.ora, si la BD no arranca, se puede iniciar desde el init.ora.

• spfile.ora: Fichero binario, no editable pero visualizable.

  • Su utilización es más rápida que en el archivo init.
  • Puede ser incluido en backups con RMAN.
  • Los spfile puede hacer un arranque de instancia en remoto
  • Cuando se cambia un parámetro con ALTER SYSTEM se guarda el cambio en el spfile

Editar algunos parámetros requiere la parada de la BD.

Estados del arranque de la BD

1. Nomount. Conexión a los archivos de control, lectura de fichero de parámetros, creación de SGA, arranque de procesos background y apertura de fichero alertSID.log (siendo SID el nombre de nuestra BD)
2. Mount. Procesos y recursos se asocian a los archivos de la BD. Se abre el fichero de control, obtienen los nombre de ficheros: datafiles y redo log
3. Open. Abre los ficheros de datos y redo log. El Oracle server comprueba la consistencia de la BD y si es necesario, el proceso SMON inicia la recuperación de la instancia.

Desde que se arranca la BD pasa por estos estados anteriores hasta ser accesible para todos.

Operaciones de arranque

---

Comando arranque	Operaciones a realizar
startup nomount	Crea la BD. Cambia modo de archivado de la BD. Renombra los archivos redolog o del tablespace asociado SYSTEM. Crea, modifica o suprime nuevos redolog o grupos de estos
startup mount	Cambia el nombre de los datafiles. Activa el modo ArchiveLOG. Recupera la BD. Tareas sobre los archivos de la BD ya que no están abiertos sus datos.
startup archivelog	Inicia una recuperación completa de la BD
startup restrict	Permite la conexión de la BD a usuarios con el privilegio Restricted Sesion para hacer tareas de administración.
startup force	Hace shutdown abort y arranca la BD
startup pfile='ruta/archivo.ora'	Arranca con un determinado archivo de parámetros.

---

Arranque

Habiendo iniciado la sesión en Oracle Server como usuario Oracle.

Conectamos a sqlplus como administrador con el comando: sqlplus / as sysdba

Una vez estemos dentro de SQLPLUS ejecutamos startup

Paso de un estado a otro

Cambios de estado:

SQLPLUS> ALTER DATABASE MOUNT;

SQLPLUS> ALTER DATABASE OPEN;

Trabajar en modo restringido:

SQLPLUS> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQLPLUS> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Tipos de parada

SHUTDOWN

• No admite nuevas conexiones a la BD pero espera a que terminen las transacciones en curso y sesiones

  (PASOS COMUNES)

• Fuerza checkpoint y graba los datos en buffer

• Cierra todos los ficheros

• Para la instancia

SHUTDOWN TRANSACTIONAL

• Corta conexiones que no haya empezado una transacción y espera a que terminen transacciones en curso.
• PASOS COMUNES

SHUTDOWN IMMEDIATE

• No acepta nuevas conexiones y cierra las actuales
• Hace rollback de las transacciones en curso
• PASOS COMUNES

SHUTDOWN ABORT

• Corta conexiones de golpe
• Cierra la instancia (destruye procesos background y SGA), no espera ni desmonta BD.
• No hace checkpoint ni cierra ficheros.
• No guarda nada en disco
• Requiere recovery de la instancia al arrancar (lo hace automáticamente SMON)

Para detener una BD

Conectamos a SQLPLUS como administrador desde el usuario Oracle con el comando sqlplus / as sysdba

E introducimos el comando:

SQLPLUS> SHUTDOWN

USUARIOS ADMINISTRADORES

En la instalación de Oracle se crean dos usuarios administradores por excelencia:

SYS

• Clave por defecto: change_on_install
• Propietario del diccionario de datos
• Se suele utilizar para:
  • Arrancar y parar la BD
  • Modificar componentes de la BD

SYSTEM

• Clave por defecto: manager
• DBA por excelencia (DBA – Data Base Administrator)
• Se suele usar para:
  • Tareas administrativas habituales: alta de users, creación de tablespaces, etc.

Cambio de contraseña

Como usuario system ejecutamos los comandos:

SQLPLUS> ALTER USER SYS IDENTIFIED BY tu_contraseña;

SQLPLUS> ALTER USER SYSTEM IDENFIFIED BY tu_contraseña;

CONFIGURACIÓN DE RED

Funcionamiento

El esquema de funcionamiento de red es así:

Imagen de Javier García Retamero

Fichero LISTENER.ORA

Los clientes se conectan a través del LISTENER que está ubicado únicamente en el SERVIDOR.

Archivos de configuración: listener.ora

Tiene un fichero de configuración que está en: $ORACLE_HOME\network\admin\listener.ora

Este programa atiende las peticiones de clientes y las redirige a la BD. En este fichero tenemos el nombre del listener, nombre de la máquina y puerto de escucha

Podemos visualizar un ejemplo de este fichero aquí:

Fichero TNSNAMES.ORA

Este fichero está en la misma carpeta que el listener.

$ORACLE_HOME\network\admin\tnsnames.ora

Contiene las cadenas de conexión con las diferentes BBDD a las que puede redirigir el listener a los usuarios:

Arranque y parada del LISTENER

Con el listener parado las peticiones remotas a la BD no serán atendidas. Pero las conexiones en local seguirán funcionando. Entrar al programa de control del listener con el comando: LSNRCTL Una vez estemos dentro de la aplicación del LISTENER podremos ejecutar una serie de comandos adicionales:

• Con este comando haremos una parada del listener indicado: stop nombrelistener
• Con este comando iniciaremos el listener indicado: start nombrelistener
• Con este comando obtenemos información del listener: status

CONFIGURAR HERRAMIENTAS CLIENTE

Para que cualquier programa pueda conectar con la BD necesita:

• Información sobre la cadena de conexión
• Driver específico de conexión con la BD

Cadena de conexión

Una cadena de conexión está formada por:

• Dirección IP donde está la BD
• Puerto por el que escucha
• SID de la BD (nombre)

Esta información está en los archivos listener.ora y tnsnames.ora del SGBD.

Cliente Oracle

Oracle cuenta con software que permite utilizar ciertas herramientas para trabajar directamente con la base de datos: SQL Plus, SQL Developer, etc.

SQL DEVELOPER

Si hemos descargado ya SQL Developer, no requiere instalación, tenemos que dar de alta la cadena de conexión en el programa.

CONEXIÓN DESDE JAVA

Importando y descargando la librería correspondiente se puede conectar también a la BD usando Java.

PARÁMETROS DE LA BD

El archivo pfile nos sirve para iniciar la BD en caso que no pueda arrancar, ya que no podemos modificar el archivo spfile

Copia entre archivos de configuración

Normalmente se usa un archivo binario SPFILE que contiene los parámetros, pero es necesario tener una copia en formato PFILE para si el sistema no arranca y tenemos que modificar los parámetros en el archivo.

CREAR PFILE

Comando	Operación
CREATE PFILE FROM SPFILE	Crea archivo PFILE desde el SPFILE actual. Coloca el PFILE en su ubicación por defecto
CREATE PFILE=’ruta’ FROM SPFILE	Hace lo mismo, pero coloca el PFILE en la ruta que se especifique
CREATE PFILE=’ruta’ FROM SPFILE=’ruta’	Crea el PFILE desde el SPFILE, se especifican las rutas de ambos archivos
CREATE PFILE FROM MEMORY	Crea el PFILE desde los parámetros actuales que estén en memoria
CREATE PFILE=’ruta’ FROM MEMORY	Hace lo mismo, pero indicando la ruta para el PFILE

CREAR SPFILE

Comando	Operación
CREATE SPFILE FROM PFILE	Crea archivo SPFILE desde el PFILE actual. Coloca el SPFILE en su ubicación por defecto
CREATE SPFILE=’ruta’ FROM PFILE	Hace lo mismo, pero coloca el SPFILE en la ruta que se especifique
CREATE SPFILE=’ruta’ FROM PFILE=’ruta’	Crea el SPFILE desde el PFILE, se especifican las rutas de ambos archivos
CREATE SPFILE FROM MEMORY	Crea el SPFILE desde los parámetros actuales que estén en memoria
CREATE SPFILE=’ruta’ FROM MEMORY	Hace lo mismo, pero indicando la ruta para el SPFILE

Carga de parámetros en arranque

Oracle busca los archivos de parámetros en la ruta habitual de estos archivos:

• Linux: ORACLE_HOME/dbs
• Windows: ORACLE_HOME\database

El orden de búsqueda en los archivos es:

1. Archivo SPFILE de nombre spfileSID.ora
2. Archivo SPFILE de nombre spfile.ora
3. Archivo PFILE de nombre initSID.ora
4. Archivo PFILE de nombre init.ora

También se puede forzar a que se cargue un archivo PFILE que nosotros queramos. Para ello usaremos el comando: STARTUP PFILE='ruta+nombre y extensión archivo'

Tipos de parámetros

• Derivados: su valor depende de otros parámetros.
• Dependientes del SO: su valor depende del SO donde esté instalado el servidor de BD. Por ejemplo, el tamaño de bloque en disco.
• Independientes: Su valor no depende del SO ni otros parámetros.
• Estáticos: Su valor se modifica en el fichero de parámetros y se aplica al reiniciar la instancia.
• Dinámicos: Su valor se puede cambiar con la BD abierta y se aplica al instante.
  • A nivel de sesión: Solo afectan a la sesión actual
  • A nivel de sistema: Afectan a todas las sesiones.

A NIVEL DE SESIÓN

ALTER SESSION SET PARÁMETRO=VALOR;

A NIVEL DE SISTEMA

ALTER SYSTEM SET PARÁMETRO=VALOR;

[COMMENTS = comentarios] [DEFERRED]
[SCOPE={SPFILE | MEMORY | BOTH}];

Las líneas entre corchetes [ ] y entre { } significan que esa parte del comando es opcional ponerla.

Scope indica cuándo se deben aplicar los cambios y puede tomar los siguientes valores:

• SPFILE. Modifica el archivo SPFILE y sus efectos estarán en el siguiente arranque
• MEMORY. Cambio guardado en memoria, se produce al momento. No se toca el SPFILE, en el próximo reinicio tendrá el valor anterior.
• BOTH. Hace ambas cosas.
• DEFERRED. El valor asignado funciona en el siguiente reinicio.

VISUALIZACIÓN

Imagen de Javier García Retamero.

ALGUNOS PARÁMETROS

Imágenes de Javier García Retamero.

DICCIONARIO DE DATOS

Compuesto por un conjunto de tablas y vistas donde se guarda toda la información de los objetos de la BD, así como la estructura lógica y física. Guardan información como:

• Definición objetos BD
• Espacio ocupado por cada objeto  Importante para estimar el espacio necesario.
• Condiciones de integridad
• Usuarios
• Privilegios
• Roles
• Segmentos de auditoría, etc…

ESTRUCTURA

Incluye dos tipos de objetos:

• Tablas base:
  • Creadas automáticamente con la BD
  • Las que contienen la información del Diccionario de Datos
• Las vistas:
  • Creadas con el script catalog.sql
  • Permiten acceder a la información de las tablas base

Para mostrar la información Oracle usa unas vistas que comienzan por estos prefijos:

Esta lista está ordenada de más permisos a menos permisos.

• USER_: Muestra objetos del usuario que hace la consulta
• ALL_: Muestra objetos a los que puede acceder el usuario de la consulta, sean suyos o no
• DBA_: Muestra objetos de la BD. Solo accesibles desde usuarios con permisos de administración.
• V$: Son tablas dinámicas, creadas al arrancar la instancia y alojadas en memoria. Su información cambia durante la ejecución. Cuando las cerramos la BD, desaparecen.

Estas vistas se consultan añadiendo USER_, ALL_, DBA_ delante del nombre indicado para cada una.

Imágenes de Javier García Retamero.

danger

A partir de esta, las siguientes vistas no se visualizan escribiendo USER_, ALL_, DBA_ delante, solo con el nombre indicado para cada vista.

Imágenes de Javier García Retamero.

Algunos ejemplos más

• Visualizar los campos de una vista o tabla: DESC NOMBRE_VISTA;
• Mostrar información de la BD activa: SELECT * FROM V$DATABASE;
• Visualizar tablas de un usuario: SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=’SCOTT’;
• Visualizar índices de un usuario: SELECT TABLE_NAME, INDEX_NAME FROM DBA_INDEXES WHERE OWNER=’SCOTT’;
• Visualizar secuencias de un usuario: SELECT SEQUENCE_NAME FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER=’SCOTT’;
• Visualizar las tablas y vistas del usuario actual SELECT * FROM USER_CATALOG;
• Visualizar objetos: SELECT * FROM USER_OBJECTS;

FICHEROS DE LOG

LOGS en procesos background.

1. Proceso en segundo plano.
2. Archivo de traza.
3. Detectar fallo, información sobre error.

Como administradores debemos revisar estos archivos a menudo para comprobar errores, para visualizar estos archivos usaremos el comando:

SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

El nombre del archivo de rastreo se forma por: NombreProceso 2º plano que lo creó y .trc

Su ubicación estará en el directorio indicado por: El parámetro BACKGROUND_DUMP_DEST que está en init.ora El parámetro DIAGNOSTIC_DEST en caso que se use el repositorio de diagnóstico ALERT_”SID”.LOG

Registra de forma cronológica operaciones y errores en la BD como:

• Errores internos, bloqueos, datos corruptos…
• Operaciones de administración (STARTUP,CREATE,ALTER…)
• Modificaciones en parámetros principales

Ubicación del archivo ALERT: SELECT * FROM V$DIAG_INFO WHERE NAME LIKE UPPER(‘%ALERT%’);

Ubicación de los archivos de traza: SELECT * FROM V$DIAG_INFO WHERE NAME=’Diag Trace’;

El parámetro MAX_DUMP_FILE_SIZE: Tamaño en bloques del SO máximo de los Log y TRAZA. Por defecto es unlimited es decir, sin límite.

Vistas útiles:

Imagen de Javier García Retamero.

LOG EN SESIONES DE USUARIO

Para activar la traza en sesión de usuario > genera un archivo con información SQL ejecutada > Analiza problemas en sentencias y estadísticas.

Un fichero de traza se puede analizar mediante: tkprof SQLdeveloper

Desde el propio usuario

Podemos ayudar a identificar nuestro fichero de traza añadiendo al nombre el atributo:

ALTER SESSION SET TRACEFILE_IDENTIFIER=’identificador’;

Para saber el nombre del fichero de traza de nuestra sesión podemos hacer:

SELECT VALUE FROM V$DIAG_INFO WHERE NAME=’Default Trace File’;

Seguidamente, en SYS le damos permisos al usuario que quiere activar su traza:

grant execute on dbms_monitor to USUARIO;

Desde el usuario que quiere lanzar la traza lo activamos con:

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=> TRUE, binds=> FALSE);

Desactivación:

SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE;

Desde el propio ADMIN

Por ejemplo un usuario DBA

1. Debemos averigurar el PADDR, el ID y el serial de la sesión del usuario a monitorizar, es necesario que el usuario tenga su sesión abierta. Para esto usamos el comando:

   SELECT PADDR, SID, SERIAL#, USERNAME FROM V$SESSION;

2. Para saber el nombre de fichero de traza del usuario a monitorizar ejecutamos la siguiente consulta: SELECT TRACEFILE FROM V$PROCESS WHERE ADDR=PADDR;

3. Finalmente, para activar la monitorización de este fichero ejecutamos este comando: EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id=> SID, serial_num=> SERIAL#, waits => TRUE, binds => FALSE);

   Si queremos desactivar la monitorización usaremos el comando:

   EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id=> SID, serial_num=> SERIAL#);

LECTURA DE FICHEROS DE TRAZA

Desde una terminal como usuario ORACLE debemos ir a al carpeta donde se haya generado el fichero de traza y ejecutamos el comando:

tkprof fichero_traza.trc salida_traza.txt explain=usuario/contraseña sys=no