Download Principios de Administración

ADMINISTRACIÓN
El presente trabajo pretende explicar la administración de una base de datos ORACLE, lo
que considero más importante para poder desarrollar las tareas administrativas, es el
conocimiento de la estructura interna del motor, mas que los comandos en sí, por esta razón
se comienza con una descripción de la organización interna del motor y posteriormente se
verán los comandos y las tablas que se deben consultar.
Responsabilidades del DBA
•
Instalar y actualizar el servidor y las herramientas de desarrollo.
•
Creación de bases de datos, estructuras de almacenamiento y objetos primarios.
•
Localizar el espacio físico y planear los requerimientos futuros para las bases de datos.
•
Modificar la estructura de la base de datos
•
Administración de usuarios
•
Controlar y monitorear los accesos a la base de datos
•
Backup y recuperación
•
Mantenimiento de la seguridad del sistema
•
Monitorear y optimizar el desempeño de la base de datos.
I.
Componentes de la Arquitectura Oracle
1. Servidor Oracle
El servidor Oracle es un sistema para manejo de bases de datos objeto-relacionales que
proporciona una aproximación abierta e integrada para el manejo de información.
2. Conexión a la base de datos
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 1
El usuario que necesite interactuar con el servidor Oracle necesita primero establecer una
conexión a la Base de Datos. Los pasos siguientes se llevan a cabo para conectarse a una
Base de datos:
•
El usuario ejecuta una herramienta como SQL*PLUS, o corre una aplicación,
originando un proceso usuario.
•
Cuando un usuario se conecta al servidor Oracle especificando un usuario, password,
y una Base de datos, se crea un proceso en la máquina que esta ejecutando el
Servidor Oracle. Este proceso se conoce como proceso servidor.
PL/SQL Es una extensión del lenguaje que ofrece la estructura procedimental de bloques
combinado con las capacidades no procedimentales de SQL.
Las herramientas de desarrollo de 4ª generación, presentan la interface con los datos, estas
herramientas son parte de la estrategia de arquitectura abierta de Oracle. Estas herramientas
soportan bases de datos Oracle y no Oracle (Como muchas herramientas lo hacen).
3. Proceso usuario
•
Corre en la máquina cliente
•
Se crea cuando se invoca una herramienta o aplicación
•
Corre la herramienta o aplicación (SQL*PLUS, Server Manager, OEM,
Developer/2000)
•
Genera llamados al Servidor Oracle
4. Proceso Servidor
•
Corre en la máquina servidor (host)
•
Atiende a un sólo proceso usuario (servidor dedicado)
•
Procesa los llamados generados por el cliente
•
Devuelve resultados al cliente
5. Instancia Oracle
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 2
El servidor Oracle consiste de una instancia Oracle y una Base de Datos Oracle. Una
instancia Oracle consiste de una estructura de memoria llamada la System Global Area
(SGA) y procesos de background usados por un servidor Oracle para manejar una Base
de Datos.
Al iniciar el servidor ORACLE, en memoria se localiza un área específica llamada
sistema de área global (System global area SGA). A la combinación de buffers de
memoria y procesos de fondo se le denomina una instancia de Oracle. La colección de
archivos físicos se le denomina base de datos ORACLE.
La SGA es un grupo de buffers de memoria localizados por el servidor Oracle para una
instancia.
Los procesos de background, son las distintas tareas de todos los usuarios de la base de
datos (Consultas, inserciones, borrados, etc).
Una instancia es la combinación de la SGA y los procesos de background, la información
contenida en los archivos de parámetros determinan las características de una instancia,
como son: El tamaño del SGA y número de procesos entre otros.
6. Base de Datos Oracle
Una Base de datos Oracle representa las estructuras físicas y está compuesta por
archivos del Sistema operativo. Una Base de Datos Oracle consiste de los siguientes
tipos de archivos:
•
Data Files
•
Redo Log Files
•
Control Files
Término
Significado
Database
Colección lógica de datos almacenados en tablespaces.
File
Archivo de datos físicos ubicados en un solo tablespace.
Tablespace
Repositorio lógico para agrupar datos fisicamente.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 3
Segment
Conjunto de uno o mas extents, que contiene todos los datos para una
estructura específica contenida en un tablespace.
Block
Bloque físico que localiza los datos existentes en un archivo ( Es un
componente de los archivos físicos, un archivo físico se compone de
varios bloques).
7. Otras estructuras físicas
Aparte de los database files, el servidor Oracle usa otros archivos. Algunos de los
cuales son :
•
Parameter file
•
Password file
•
Archived redo log files
8. Procesamiento de un query
Las siguientes son las etapas principales en el procesamiento de un query :
1. Parse
2. Execute
3. Fetch
9. El Shared Pool
•
Se usa durante la fase de Parse
•
En el Library Cache se encuentra el texto de la instrucción, el código parseado, y el
plan de ejecución
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 4
•
El Data Dictionary Cache contiene las definiciones y privilegios de tablas y
columnas
10. Database Buffer Cache
•
Número de bloques definidos por DB_BLOCK_BUFFERS
•
Tamaño del buffer basado en DB_BLOCK_SIZE
•
Almacena los bloques más recientemente usados
11. Program Global Area (PGA)
•
No compartida y no escribible.
•
Contiene :
•
Sort area
•
Información de la sesión
•
Estado de los cursores
•
Espacio de pila
12. Segmento de Rollback
Antes de efectuar una modificación, el proceso servidor almacena el valor previo en un
segmento de rollback.
13. Redo Log Buffer
•
Tamnaño definido por LOG_BUFFER
•
Registra las modificaciones hechas a través de la instancia
•
Usado secuencialmente
•
Buffer circular
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 5
14. Database Writer (DBWR)
El proceso Database Writer (DBWR) escribe los buffers dirty desde el database buffer
cache a los data files. Asegura que esté disponible un número suficiente de free buffers
en el database buffer cache.
15. Log Writer (LGWR)
El proceso Log Writer (LGWR) es un proceso de background que escribe entradas desde
el redo log buffer a los redo log files.
16. Procesamiento de un COMMIT
Cuando se ejecuta un commit, ocurren los siguientes pasos:
1. El proceso servidor coloca un registro de commit en el redo log buffer
2. LGWR efectúa una escritura contigua de todas las entradas del redo log buffer
3. Al usuario se le informa que el commit está completo
4. El proceso servidor registra la información para indicar que la transacción está
completa y que han sido liberados los locks en los recursos.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 6
II.
Uso de las herramientas administativas
1. Ejemplos
Herramienta
Descripción
Server Manager
Usada para subir, bajar o recuperar una base
de datos
Oracle Enterprise Manager
Herramienta
gráfica
para
administrar,
monitorear, y afinar múltiples bases de datos
SQL*Loader
Utilidad para cargar datos desde archivos
externos a tablas Oracle
Export o Import
Utilidad para exportar/importar datos en
formato Oracle
Password File
Utilidad para crear el archivo de password
de la base de datos
2. Ejecutando el Server Manager
En Unix
En NT
svrmgrl
svrmgr30
Iniciando y ejecutando un script
En Unix
En NT
svrmgrl [email protected]
svrmgr30 [email protected]
3. Oracle Enterprise Manager (OEM)
Oracle Enterprise Manager (OEM) consiste de una consola centralizada, agentes
inteligentes, y un paquete de aplicaciones estándar que proporcionan a los
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 7
administradores de la base de datos la funcionalidad que necesitan para manejar sus
bases de datos.
También existe un paquete de aplicaciones integradas conociadas como el OEM
Performance Pack, que proporcionan monitoreo, diagnóstico y afinamiento experto y
avanzado
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 8
III.
Manejo de una instancia Oracle
1. Vistazo
Una base de datos Oracle no está disponible a los usuarios hasta que el administrador de
la base de datos haya iniciado la instancia y abierto la base de datos.
Durante el arranque de la base de datos ocurren los siguientes eventos :
1. Subir una instancia
2. Montar la base de datos
3. Abrir la base de datos
Se pueden ejecutar los siguientes pasos para bajar la base de datos :
1. Cerrar la base de datos
2. Desmontar la base de datos
3. Bajar la instancia
2. Usuarios administradores de la base de datos
Los dos usuarios administradores de la base de datos SYS y SYSTEM son :
3.
•
Creados automáticamente
•
Privilegiados con el role DBA
SYS Y SYSTEM
•
SYS
Password : change_on_install
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 9
Dueño del diccionario de datos de la base de datos
•
SYSTEM
Password : manager
Dueño de tablas internas adicionales usadas por herramientas Oracle
4. Autenticación por sistema operativo
•
Establecer el usuario a ser autenticado por el sistema operativo
•
Establecer REMOTE_LOGIN_PASSWORD_FILE a NONE
•
Utilizar los siguientes comandos para conectarse a una base de datos
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
5. Autenticación usando archivo de password
•
Crear el archivo de password usando la utilidad de password
orapwd file=$ORACLE_HOME/dbs/orapwU15 password=admin
•
Establecer REMOTE_LOGIN_PASSWORD_FILE a EXCLUSIVE o SHARED
•
Usar el siguiente comando para conectarse a la base de datos :
CONNECT INTERNAL/ADMIN
6. El archivo de parámetros
El archivo de parámetros, comúnmente conocido como el initSID.ora, es un archivo de
texto que puede mantenerse usando un editor estándar del sistema operativo.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 10
El archivo de parámetros se lee únicamente durante el inicio de la instancia. Si el
archivo es modificado, es necesario bajar e iniciar la instancia para que los nuevos
parámetros surtan efecto.
Los parámetros en el archivo initSID.ora tienen un efecto significativo en el desempeño
de la base de datos :
•
Dimensionan el tamaño de los componentes de la SGA
•
Establecen defaults para la instancia y la base de datos
•
Establecen límites en la base de datos
•
Definen (en creación de la base de datos) varios atributos físicos de la base de datos,
como el tamaño del bloque
•
Especifiación de los constrol files, archived log files, y ubicaciones de los archivos
de trace
7. Parámetros que deberían especificarse
BACKGROUND_DUMP_DEST
COMPATIBLE
CONTROL_FILES
DB_BLOCK_BUFFERS
DB_NAME
SHARED_POOL_SIZE
USER_DUMP_DEST
8. Parámetros comúnmente modificados
IFILE
LOG_BUFFER
MAX_DUMP_FILE_SIZE
PROCESSES
SQL_TRACE
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 11
TIMED_STATISTICS
9. Startup y Shutdown en etapas
STARTUP
•
NOMOUNT
•
MOUNT
•
OPEN
SHUTDOWN
•
NORMAL
•
TRANSACTIONAL
•
IMMEDIATE
•
ABORT
10. Vistas dinámicas de desempeño
•
Mantenidas y continuamente actualizadas por el servidor Oracle
•
Contienen datos de disco y de estructuras de memoria
•
Contienen datos que son útiles para afinamiento
•
Tienen sinónimos públicos con el prefijo V$
V$PARAMETER
V$SGA
V$OPTION
V$PROCESS
V$SESSION
V$VERSION
V$INSTANCE
V$CONTROLFILE
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 12
V$DATABASE
V$DATAFILE
V$LOGFILE
11. Obteniendo los valores actuales de los parámetros
•
Server Manager : SHOW PARAMETER control
•
SELECT name FROM v$parameter WHERE name LIKE ‘%control%’;
12. Sesiones restringidas
Usar el comando STARTUP para restringir el acceso a la base de datos :
STARTUP RESTRICT
Usar el comando ALTER SYSTEM para colocar una instancia en modo restringido :
ALTER SYSTEM ENABLE RESTRICTED SESSION;
13. Matando sesiones
1. Identificar la sesión a terminar usando la vista V$SESSION :
SELECT sid, serial# FROM v$session WHERE username=’SCOTT’;
2. Ejecutar el comando ALTER SYSTEM ;
ALTER SYSTEM KILL SESSION ‘7,15’;
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 13
IV.
Creación de una Base de Datos
1. Prerrequisitos
•
Una cuenta privilegiada se autentica por una de las siguientes formas:
Sistema Operativo
Archivo de password
•
Memoria para iniciar la instancia
•
Espacio en disco suficiente
2. Planeación de la ubicación de los archivos de la Base de Datos
•
Mantener al menos dos copias activas del control file en al menos dos dispositivos
diferentes
•
Multiplexar los redo log files y colocar los miembros de cada grupo en discos diferentes
•
Separar los data files cuyos datos:
- Participaran en contención por recursos en disco
- Tienen distinta duración
- Tienen distintas características administrativas
3. Organización del sofware de Oracle
/u01/app/oracle
/u02/app/applmgr
/u02/oradata
/u03/oradata
4. Creación de una Base de Datos: Consideraciones
•
En Unix
- Creada automáticamente durante una instalación
- Creada manualmente después de la instalación
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 14
•
En NT
- Creada usando el Oracle Database Assistant
- Creada manualmente
5. Creación manual de una Base de Datos
1. Decidir el nombre de la instancia y de la Base de Datos
2. Establecer las variables del Sistema Operativo
3. Preparar el archivo de parámetros
4. Crear el archivo password
5. Subir la instancia
6. Crear la Base de Datos
7. Correr los Scripts para generar el diccionario de datos y ejecutar las tareas posteriores
a la creación
6. Ambiente del Sistema Operativo
En Unix establecer las siguientes variables de ambiente:
- ORACLE_HOME
- ORACLE_SID
- ORACLE_BASE
- ORA_NLS33
- PATH
En NT
- Establecer la variable ORACLE_SID para usar SVRMGR30
- Crear el servicio y el archivo de password con ORADIM80
Ejemplo
C:\ORADIM80 -NEW -SID u16 -INTPWD
PASSWORD -STARTMODE auto -PFILE
ORACLE_HOME\DATABASE\initu16.ora
7. Preparación del archivo de parámetros
1. Crear el nuevo init<SID>.ora
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 15
2. Modificar el initu16.ora editando los parámetros
8. Subir la instancia
1. Conectarse como SYSDBA
2. Subir la instancia en estado NOMOUNT
SVRMGR> startup nomount pfile=initu16.ora
9. Creación de la Base de Datos
Comando CREATE DATABASE
Ejemplo:
CREATE DATABASE U16
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXLOGHISTORY 100
LOGFILE
GROUP 1 ('/DISK3/log1a.rdo', '/disk4/log1b.rdo') SIZE 1M
GROUP 2 ('/DISK3/log2a.rdo,'/DISK4/log2b.rdo') SIZE 1M
DATAFILE
'/DISK1/System01.dbf' SIZE 50M AUTOEXTEND ON
CHARACTER SET WE81S08859P1;
10. TROUBLESHOOTING
La creación de la Base de Datos falla si:
* Hay errores de sintaxis en el script SQL
* Los archivos a ser creados ya existen.
* Errores por permisos en el sistema operativo o por espacio insuficiente en disco
11. Después de la creación de la Base de Datos
La Base de Datos contiene:
* Datafiles que pertenecen al tablespace SYSTEM
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 16
* Control Files y redo log files
* Usuario SYS/Change_on_install
* Usuario SYSTEM/manager
* Segmento de rollback SYSTEM
* Tablas internas (Pero no las vistas del diccionario de datos)
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 17
V. Creación de las Vistas del Diccionario de Datos y Paquetes Estándar
1. Uso del Diccionario de Datos
El Diccionario de Datos proporciona información a cerca de:
* Estructura lógica y física de la base de datos
* Nombres, definiciones y localización de espacio de algunos objetos
* Constraints de integridad
* Usuarios y privilegios de la base de datos
* Auditoría
2. Tablas base y vistas del diccionario de datos
Vistas del diccionario de datos:
* Simplifican la información de la tabla base
* Creados con el script catalog.sql
Tablas base:
* Normalizadas
* Creadas con el script sql.bsq
3. Vistas del diccionario de datos
DBA_XXX
: Objetos de toda la base de datos
ALL_XXX
: Objetos a los que el usuario puede acceder
USER_XXX : Objetos propiedad del usuario
4. Diccionario de Datos: Ejemplos de Vistas
DICTIONARY
DICT_COLUMNS
DBA_TABLES
DBA_OBJECTS
DBA_LOBS
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 18
DBA_TAB_COLUMNS
DBA_CONSTRAINTS
DBA_USERS
DBA_SYS_PRIVS
DBA_ROLES
DBA_EXTENTS
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_ROLLBACK_SEGS
DBA_DATA_FILES
DBA_TABLESPACES
DBA_AUDIT_TRAIL
DBA_AUDIT_OBJECTS
DBA_AUDIT_OBJ_OPTS
5. Creación de Vistas del Diccionario de Datos
Script
Propósito
Catalog.sql
Crea las vistas más comúnmente usadas del diccionario de datos
Catproc.sql
Corre todos los scripts requeridos por PL/SQL en el servidor
6. Scripts Administrativos
Existen las siguientes convenciones para los scripts sql:
Cat*.sql
Información del catálogo y del diccionario de datos
dbms*.sql Especificaciones de paquetes de la base de datos
prvt*.plb
Código encriptado de la base de datos
utl*.sql
Vistas y tablas para utilidades
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 19
7.
Procedimientos y paquetes almacenados
Son objetos de la Base de Datos que permiten almacenar unidades de programación
PL/SQL y se ejecutan usando herramientas Oracle como SQL*Plus, Server Manager o
Enterprise Manager, o ejecutándolas desde una aplicación Oracle.
8. Qué son los Procedimientos Almacenados?
•
Son procedimientos o funciones
•
Se almacenan en el Diccionario de Datos
•
Pueden ser usados por muchos usuarios
•
Pueden aceptar y retornar parámetros
•
Pueden usarse en funciones SQL
9. Qué son los Paquetes?
•
Agrupación de tipos, items y subprogramas PL/SQL lógicamente relacionados
•
Tienen dos partes:
•
-
Una especificación
-
Un cuerpo
Permite que Oracle lea multiples objetos a memoria a la vez.
10. Paquetes proporcionados por Oracle
•
DBMS_LOB
•
DBMS_SESSION
•
DBMS_UTILITY
•
DBMS_SPACE
•
DBMS_ROWID
•
DBMS_SHARED_POOL
11. Información delos objetos almacenados
•
Vista DBA_OBJECTS del diccionario de datos
-
OWNER
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 20
•
-
OBJECT_NAME
-
OBJECT_TYPE
-
STATUS (VALID, INVALID)
Comando DESCRIBE
Ejemplo: DESCRIBE dbms_session.set_role
12. Trobleshooting
El status de los objetos puede ser INVALID:
-
Si se ejecutan comandos DDL sobre objetos referenciados
-
Después de crear los objetos con la utilidad IMPORT
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 21
VI.
Control File
1. Uso del Control File
•
Es un archivo binario necesario para que la Base de Datos inicie y opere
exitosamente
•
Cada vez que una instancia monta una Base de Datos Oracle, lee el Control File para
localizar los Data Files y los Online Redo Log Files.
•
El Control File se utiliza continuamente durante el uso de la Base de Datos y debe
estar disponible siempre que la Base de Datos de monte o se abra.
•
El Control Files proporciona información acerca de la consistencia de la Base de
Datos usada durante la recuperación.
•
Si alguno de los Control Files activos de la Base de Datos ya no está disponible,
entonces la Base de Datos no puede funcionar apropiadamente.
2. Contenido del Control File
•
Nombre de la Base de Datos
•
Ubicación de los data files y de los redo log files
•
Nombres de los tablespaces
•
Fecha de la ceación de la Base de Datos
•
Número actual de secuencia del log
•
Información de checkpoint
•
Historia del log
3. Parámetros que afectan el tamaño del Control File
•
MAXLOGFILES
•
MAXLOGMEMBERS
•
MAXLOGHISTORY
•
MAXDATAFILES
•
MAXINSTANCES
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 22
4. Obteniendo información
V$CONTROLFILE
V$PARAMETER
Ejemplo: SELECT name FROM V$controlfile;
SELECT value FROM V$parameter
WHERE name=control_files;
5. Multiplexando el Control File
Control_Files=(/DISK1/control01.con,/DISK2/control02.con)
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 23
VII.
1.
Mantenimiento de los Redo Log Files
Uso de los Redo Log Files
•
El servidor Oracle mantiene online redo log files para minimizar la pérdida de datos
en la Base de Datos. Los redo log files registran todas las modificaciones hechas a
los datos en el database buffer cache con algunas excepciones.
•
Se usan únicamente para recuperación.
2. Grupos y miembros de Redo Log
Grupos
•
Un conjunto de copias idénticas de los Online redo log files se llama un Online redo
log group
•
El proceso LGWR escribe simultáneamente la misma infromación a todos los Online
redo log files en un grupo
•
Oracle requiere almenos de dos Online redo log groups para la operación normal de
la Base de Datos
Miembros
•
Cada Online redo log file en un grupo se llama un miembro
•
Cada miembro en un grupo tiene “log sequence members” idénticos y el mismo
tamaño. El log sequence member se asigna cada vez que Oracle inicia la escritura de
un log group para identificar cada redo log file de manera única. El log sequence
number se almacena en el control file y en el encabezado de todos los data files.
3. LGWR, Log Switches y Checkpoints
LGWR
Oracle registra secuencialmente todas las modificaciones hechas a la Base de Datos en el
redo log buffer. Las entradas se escriben a uno de los online redo log groups, llamado el
“current”, bajo las siguientes situaciones:
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 24
•
Ocurre un commit
•
El redo log buffer se llen en un tercio
•
Ocurre un timeout del LGWR
Log Switches
LGWR escribe secuencialmente a los online redo log files un “log switch”, es el evento
durante el cual
LGWR finaliza la escritura a un online redo log group y empieza a escribir otro.
Checkpoints
Durante un checkpoint:
•
Todos los buffer dirty de la Base de Datos se esriben a los data files por DBWR
•
El proceso CKPT actualiza los encabezados de todos los data files y control files
para reflejar que ha finalizado exitosamente.
Un checkpoint ocurre en las siguientes situaciones:
•
Cada log switch
•
Cuando una instancia se baja con la opción NORMAL O IMMEDIATE
•
Parámetros LOG_CHECKPOINT_INTERVAL y LOG_CHECKPOINT_TIMEOUT.
4. Información acerca de los grupos
V$THREAD
Ejemplo: SELECT groups, current_group#, sequence#
FROM V$thread;
5. Información acerca de grupos y miembros
V$LOG
Ejemplo: SELECT group#, sequence#, bytes, members, status
FROM v$log;
V$LOGFILE
Ejemplo: SELECT *
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 25
FROM v$logfile;
6. Log Switches y Checkpoints
•
Los log switches se pueden forzar con al comando:
ALTER SYSTEM SWITCH LOGFILE;
•
Controlar la frecuencia de los checkpoints con los parámetros:
LOG_CHECKPOINT_INTERNAL
LOG_CHECKPOINT_TIMEOUT
7. Adición de Online Redo Log Groups
ALTER DATABASE
ADD LOGFILE (‘/DISK3/log3a.rdo’,’/DISK4/log3b.rdo’)
SIZE 1M;
8. Adición de Online Redo Log Members
ALTER DATABASE
ADD LOGFILE MEMBER
‘/DISK4/log1b.rdo’ TO GROUP 1,
‘/DISK4/log2b.rdo’ TO GROUP 2;
9. Cómo relocalizar Online Redo log Files
1- Bajar la Base de Datos
2- Copiar los online redo log files a la nueva ubicación
3- Montar la Bade de Datos
4- Ejecutar el comando ALTER DATABASE RENAME FILE
5- Abrir la Base de datos
10. Eliminación de Online Redo Log Groups
ALTER DATABASE DROP LOGFILE GROUP 3;
11. Eliminación de Online Redo Log Members
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 26
ALTER DATABASE DROP LOGFILE MEMBER
‘/DISK4/log2b.rdo’;
12. Posibles errores del LGWR
•
No está disponible un miembro de un grupo de dos o más miembros
•
No están disponibles todos los miembros del siguiente grupo
•
No están disponibles todos los miembros del grupo actual
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 27
VIII.
Manejo de Tablespaces y Data Files
1. Vistazo
La arquitectura de la Base de Datos incluye las estructuras lógicas y físicas que la
conforman.
La estructura física consiste de control files, online redo log files y data files.
La estructura lógica incluye tablespaces, segmentos, extents y data blocks.
2. Estructura de la Base de Datos
Una Base de Datos Oracle puede dividirse en áreas lógicas de espacios más pequeños
conocidas como “Tablespaces”
Tablespaces
•
Un tablespace puede pertenecer a sólo una Base de Datos
•
Cada tablespace consiste de uno o más archivos del Sistema Operativo
•
Pueden colocarse Online mientras la Base de Datos está corriendo
•
Pueden colocarse offline (excepto SYSTEM u otro tablespace con un segmento de
rollback activo), permitiendo que la Base de Datos siga corriendo
•
Pueden cambiar entre estado read-write y read-only
Usos de los Tablespaces
•
Controlan la localización de espacio y asignación de espacio a usuario
•
Controlan la disponibilidad de los datos colocando tablespaces individuales online u
offline
•
Distribuyen el almacenamiento de datos entre dispositivos para mejorar el
desempeño y reducir la contención
•
Ejecución de operaciones de backup o recuperación parcial
•
Mantener gran cantidad de datos estáticos en dispositivos read-only
Data Files
Cada tablespace consiste de uno o más archivos llamados “data files”
•
Un data file puede pertenecer a solo un tablespace
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 28
•
Oracle crea un datafile para un tablespace ubicando la cantidad especificada de
espacio en disco
•
El DBA puede modificar el tamaño de un data file posterior a su creación o puede
especificar que un data file puede crear dinámicamente a medida que crecen los
objetos en el tablespace
Segmentos
Un segmento es el espacio localizado para un tipo específico de estructura lógica de
almacenamiento dentro de un tablespace.
Ejemplos: Tablas, indices, temporales, rollback
Un segmento puede estar ubicado en varios data files siempre que éstos pertenezcan al
mismo tablespace
Extents
El siguiente nivel de espacio lógico de la Base de Datos se conoce como un extent. Un
extent es un conjunto de bloques contigüos. Cada tipo de segmento está conformado por
uno o más extents. Un extent debe existir en un data file.
Data blocks
Un data Block corresponde a uno o más bloques físicos localizados para un data file
existente.
Se especifica un tiempo de creación de la Base de Datos por el parámetro de
inicialización DB_BLOCK_SIZE. Un data block es la unidad mínima de entrada –
salida.
3. Tablespaces SYSTEM y NO-SYSTEM
El tablespaces SYSTEM contiene:
•
Información del diccionario de datos
•
Segmento de rollback SYSTEM
Los tablespaces NO-SYSTEM contienen:
•
Segmentos de rollback
•
Segmentos temporales
•
Datos de la aplicación
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 29
•
Indices de la aplicación
4. Creación de tablespaces
Comando CREATE TABLESPACE
Ejemplo: CREATE TABLESPACE app_data
DATAFILE ‘/DISK4/app01.dbf’ SIZE 100M,
‘/DISK3/app02.dbf’ SIZE 100M
MINIMUN EXTENT 500K
DEFAULT STDRAGE (INITIAL 500K NEXT 500K MAXEXTENTS 500
PCTINCREASE 0);
5. Parámetros de almacenamiento
•
INITIAL
•
NEXT
•
MAXEXTENTS
•
MINEXTENTS
•
PCTINCREASE
6. Tablespace temporal
•
Usado por operaciones de ordenamiento (sort)
•
No puede contener objetos permanentes
Ejemplo:
CREATE TABLESPACE sort
DATAFILE ‘/DISK2/sort01.dbf’ SIZE 50M
DEFAULT STDRAGE (INITIAL 2M NEXT 2M
MAXEXTENTS 500 PCTINCREASE 0)
TEMPORARY;
7. Adición de Data Files a un tablespace
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 30
Se puede aumentar el tamaño de dos formas:
•
Agregar un data file a un tablespace
•
Cambiar el tamaño de un data file
Ejemplo:
ALTER TABLESPACE app_data
ADD DATAFILE ‘/DISK3/app03.dbf’ SIZE 200M;
8. Crecimiento automatico de data files
Se puede alterar el tamaño de un data file de dos formas:
•
Automáticamente usando la opción AUTOEXTEND
•
Manualmente usando el comando ALTER DATABASE
Ejemplo: ALTER TABLESPACE app_data
ADD DATAFILE ‘/DISK6/app04.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 10M
MAXSIZE 500M;
9. Modificación manual del tamaño de los data files
ALTER DATABASE DATAFILE
‘/DISK/app02.dbf’ RESIZE 200M;
10. Modificación de los parámetros de storage
Ejemplo: ALTER TABLESPACE app_data
DEFAULT STORAGE
(INITIAL 2M NEXT 2M MAXEXTENTS 999);
11. Status OFFLINE
•
El tablespace que esté OFFLINE no está disponible para acceder a sus datos
•
El tablespace SYSTEM y cualquier tablespace con segmentos de rollback activos no
pueden colocarse OFFLINE;
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 31
12. El status READ-ONLY
Ejemplo: ALTER TABLESPACE app_data READ ONLY;
El tablespace APP_DATA está disponible únicamente para operaciones de lectura
13. Estableciendo un tablespace como READ-ONLY
•
El tablespace debe estar online
•
No se permiten transacciones activas
•
El tablespace no debe contener segmentos de rollback activos
•
El tablespace no debe estar involucrado en un backup online
14. Eliminación de tablespaces
La siguiente instrucción elimina el tablespace APP_DATA y todo su contenido
Ejemplo: DROP TABLESPACE app_data
INCLUDING CONTENTS;
15. Información de los tablespaces
DBA_TABLESPACES
Ejemplo: SELECT tablespace_name, initial_extent, next_extent,
max_extents, pct_increase.
FROM dba_tablespaces;
16. Información de los data files
DBA_DATA_FILES
Ejemplo: SELECT file_name, tablespace_name, bytes, maxbytes
FROM dba_data_files;
17. Recomendaciones
•
Usar varios tablespaces
•
Especificar los parámetros de almacenamiento
•
Asignar espacio en los tablespace a los usuarios
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 32
IX.
Estructuras de almacenamiento
1. Jerarquía de almacenamiento en la Base de Datos
•
Una base de datos está agrupada lógicamente en tablespaces
•
Un tablespace puede consistir de uno o más segmentos
•
Cuando se crea un segmento, consiste de al menos un extent el cual es un conjunto de
bloques contigüos. A medida que crece el segmento se adicionan extents al segmento
•
Un bloque, es la unidad más pequeña usada para operaciones read-write
2. Tipos de segmentos
Tabla: Es el medio más común de almacenar datos dentro de una base de datos. Los
datos dentro de una tabla no se almacenan con un orden particular.
Cluster: Las filas en un cluster se almacenan basadas en los valores de ciertas columnas.
Un cluster puede contener una o más tablas y es un tipo de segmento de datos.
Indice: Todas las entradas para un índice particular se almacenan dentro de un segmento
índice. Si una tabla tiene tres índices, se usan tres segmentos de índices. El propósito de
este índice es buscar la ubicación de las filas en la tabla basado en un valor específico.
Rollback: Es usado por una transacción que esté efectuando cambios en una base de
datos. Antes de modificar los bloques de datos o de índices, el valor anterior se
almacena en el segmento de rollback. Esto permite que el usuario pueda deshacer los
cambios realizados.
Temporal: Usando una instrucción SQL requiere de un ordenamiento que requiere mucho
espacio, se crean en disco segmentos temporales.
Ejemplos:
CREATE
INDEX,
SELECT DISTINCT, y SELECT GROUP BY.
LOB: Permite almacenar documentos de texto, imágenes, o videos.
3. Procedencia de la cláusula STORAGE
•
Cualquier parámetro de storage especificado a nivel de segmento ignora la opción
correspondiente establecida a nivel de tablespace
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 33
•
Cuando no se establecen explícitamente parámetros de storage a nivel de segmento,
toman el valor de aquellos establecidos a nivel de tablespace
•
Cuando no se establecen explícitamente parámetros de storage a nivel de tablespace,
aplican los defaults de Oracle.
4. Localización y De–Localización de extents
•
•
Localizados cuando el segmento se:
-
Crea
-
Extiende
-
Altera
De-Localizados cuando el segmento se:
-
Elimina
-
Altera
-
Trunca
-
Contrae automáticamente (únicamente segmentos de rollback)
5. Extents libres y usado
Cuando se crea un tablespace, los data files en el tablespace contienen los siguientes
elementos:
•
Un bloque encabezado, el cual es el primer bloque en el archivo
•
Un extent libre, compuesto por el resto del data file
A medida que se crean segmentos, localizan espacio a partir de los extents libres en el
tablespace. El espacio contigüo usado por un segmento se conoce como un extent usado.
Cuando los segmentos liberan espacio, los extents liberados se adicionan al conjunto de
extents libres disponibles en el tablespace.
6. Bloque Oracle
•
Unidad mínima de I/O
•
Consiste de uno o más bloques del Sistema Operativo
•
Establecido por DB_BLOCK_SIZE
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 34
•
Establecido cuando se crea la base de datos
Cada data block contiene:
-
Header
-
Espacio libre
-
Datos
7. Parámetos de utilización del espacio en un bloque
INITRANS
MAXTRANS
PCTFREE
PCTUSED
8. Información acerca de las estructuras
DBA_SEGMENTS
Ejemplo: SELECT segment_name, tablespace_name, extents, blocks
FROM dba_segments;
DBA_EXTENTS
Ejemplo: SELECT extent_id, file_id, block_id, block
FROM dba_extents
WHERE owner =’SCOTT’;
9. Espacio libre
SELECT tablespace_name, count (*), max (blocks), sum (blocks)
FROM dba_free_space
GROUP BY tablespace_name;
10. Resumen Almacenamiento de la base de datos
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 35
Parámetros de almacenamiento.
•
INITIAL
•
NEXT
•
MAXEXTENTS
•
MINEXTENTS
•
PCTINCREASE
•
OPTIMAL
•
FREELISTS
Parámetros de utilización de espacio
PCTFREE
PCTUSED
INITRANS
MAXTRANS
BLOQUES DE LA BASE DE DATOS
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 36
Propiedades
•
Un bloque corresponde a uno o mas bloques físicos en disco.
•
El tamaño se determina al crear la base de datos, y en el parámetro DB_BLOCK_SIZE y
es constante en todos los datafiles.
•
El DB_BLOCK_SIZE también determina el tamaño de cada buffer en el SGA.
•
Los bloques de base de datos también son conocidos como bloques lógicos y bloques
ORACLE.
•
Una vez creada la base de datos, el parámetro DB_BLOCK_SIZE no puede ser
cambiado.
•
El tamaño típico del bloque es 2K o 4K. El valor por defecto depende del sistema
operativo.
•
En algunas plataformas el tamaño del bloque puede ser muy grande (Por ejemplo 32 K).
Este bloque es conocido como BIG ORACLE BLOCKS.
•
Todas las operaciones de I/O, están implementadas para trabajar a nivel de bloques.
•
El servidor de Oracle mantiene bloqueos a nivel de fila.
Partes de un Database Block
Parte
Descripción
Header
Contiene la información general del bloque, como la dirección y el tipo de segmento.
Table directory
Almacena la información acerca de las tablas presentes en el bloque.
Row directory
Contiene información de las filas presentes en el bloque.
Free Space
Consiste en un conjunto de bytes en el bloque, disponibles para realizar inserciones o
actualizaciones de filas.
Row Data
Almacena los datos de la tabla o indice.
Para el caso de Oracle 8, se observa una diferencia en cuanto a los componentes de un
Database block, el Header contiene la información del directorio de tablas y el directorio de
filas.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 37
Control del espacio usado
El control del uso del espacio libre para inserciones, actualizaciones y borrado de filas en
bloques de la base de datos, se especifica en valores apropiados en los siguientes
parámetros.
♦ PCTFREE
♦ PCTUSED
♦ INITRANS
♦ MAXTRANS
PCTFREE y PCTUSED poseen el control del uso de espacio libre para inserciones o
actualizaciones de filas en bloques de la base de datos. Ambos parámetros aplican a tablas,
clusters, y vistas usando los comandos CREATE y ALTER. PCTFREE puede ser
especificado para indices.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 38
Los valores por defecto son para PCTFREE 10% y para PCTUSED 40%, la suma de
PCTFREE y PCTUSED debe ser menor o igual al 100.
INITRANS indica el número de transacciones localizadas inicialmente en el encabezado del
bloque.
MAXTRANS indica el máximo número de transacciones que pueden acceder al bloque
concurrentemente.
En el caso 1, se pueden insertar filas en el bloque ya que la utilización del mismo está por
debajo del 80%.
En el caso 2, no se pueden insertar filas, el espacio sobrante es utilizado en caso de
modificaciones, ejemplo cuando una columna tiene un valor nulo y cambia de valor.
En el caso 3, si se borran filas o se reduce el espacio ocupado con modificaciones, la
utilización del bloque puede estar por debajo del 80%, sin embargo el bloque no es usado
para inserciones hasta que la utilización del bloque caiga por debajo de PCTUSED.
En el caso 4, cuando el nivel baja de PCTUSED el bloque queda disponible para inserción.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 39
Asignar un valor bajo al parámetro PCTFREE
♦ Permite inserciones para llenar bloques más completamente.
♦ Puede requerir menos bloques para almacenar datos.
♦ Puede incrementar los costos de proceso si se requiere de reorganización de bloques
frecuentemente.
♦ Puede causar migración de filas.
Asignar un valor alto al parámetro PCTFREE
♦ Reserva mas espacio para actualizaciones.
♦ Puede requerir mas bloques para almacenar datos.
♦ Bajos costos de proceso porque los bloques pueden requerir reorganización con poca
frecuencia.
♦ Reduce la necesidad de encadenar filas.
Asignar un valor bajo al parámetro PCTUSED
♦ Reduce el costo de procesamiento porque los bloques no son liberados frecuentemente.
♦ Incrementa el espacio sin utilizar.
Asignar un valor alto al parámetro PCTUSED
♦ Incrementa los costos de proceso porque los bloques son liberados frecuentemente.
♦ Mejora el uso de espacio ya que los bloques se utilizan con mas capacidad.
INITRANS
Es el número inicial de entradas de transacciones, para transacciones concurrentes, estas son
localizadas en cada encabezado de bloque, cuando un bloque es localizado (Por defecto 1,
mínimo 1, máximo 255). Cada entrada de transacción es aproximadamente de 23 byter de
longitud (Depende del sistema operativo).
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 40
MAXTRANS
Es el máximo número de transacciones concurrentes que puede soportar un bloque (Máximo
255).
El valor por defecto para MAXTRANS depende del sistema operativo, pero usualmente es
de 255.
Cada transacción puede necesitar aproximadamente 23 bytes de espacio libre en el bloque.
Si el espacio libre del bloque está siendo usado, las transacciones pueden tener que esperar
para acceder al bloque.
EXTENTS
Un extent es un conjunto de bloques de datos contigüos. Cuando el objeto de la base de datos
crece, se localiza espacio para el mismo.
Los extents son localizados cuando:
♦ El segmento es creado (INITIAL EXTENT)
♦ El segmento crece (NEXT EXTENT)
♦ La tabla o cluster es alterado y requiere extents.
Los extents son liberados cuando:
♦ El segmento o cluster es borrado.
♦ El segmento o cluster es truncado.
♦ El segmento es más grande que el óptimo y contiene extents libres (Unicamente para
segmentos de rollback).
Características de los extents
♦ Cada segmento en una base de datos es creado con el menor número de extents para
almacenar los datos.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 41
♦ El primer extent es llamado el extent inicial.
♦ Los extents siguientes son llamados extents incrementales.
♦ Un objeto puede localizar un nuevo extent solamente si los extent localizados actualmente
se encuentran llenos.
♦ La frecuente liberación de extents puede ocasionar una defragmentación del tablespace.
♦ El extent inicial es un pedazo de espacio reservado en la base de datos. Cuando el extent
inicial se llena, se localiza un NEXT EXTENT.
♦ El servidor de oracle considera los bloques como bloques consecutivos, pero esto no
significa que deban ser contígüos físicamente en disco.
Control de la localización de los extents
Parámetro
Descripción
INITIAL
Tamaño en bytes del primer extent localizado en el segmento, por
defecto equivale a cinco data blocks.
NEXT
Tamaño en bytes de los extents siguientes, por defecto equivale a
cinco data blocks.
MAXEXTENTS
Número total de extents que pueden ser localizados en el
segmento, el tamaño máximo depende del tamaño de los bloques
de Oracle, por ejemplo 121 para bloques de 2K.
MINEXTENTS
Número total de extents que pueden ser localizados al momento de
ser creado el segmento, por defecto es 1 excepto para los
segmentos de rollback que requieren 2.
PCTINCREASE
Porcentaje en el cual se puede incrementar los extents siguientes.
OPTIMAL
Especifica el tamaño optimo en bytes para los segmentos de
rollback.
FREELIST
Número de listas de bloques libres para inserción en na tabla. Por
defecto es uno.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 42
FREELIST GROUPS
Número de freelist para ser usado en diferentes instancias de un
Parallel Server.
Representación del almacenamiento en Oracle.
En la gráfica anterior se presentan las tablas de las cuales se saca la información necesaria
para presentar en la aplicación.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 43
X.
Segmentos de rollback
1. Vistazo
Un segmento de rollback se usa para salvar el valor anterior cuando un proceso está
efectuando modificaciones a los datos en una base de datos, almacena información como
el código del archivo, código del bloque y también datos como existían antes de ser
modificados.
El encabezado de un segmento de rollback contiene una tabla donde se almacena
información acerca de las transacciones que está usando el segmento.
Una transacción puede usar únicamente un segmento de rollback para almacenar todos
los registros de rollback. Muchas transacciones pueden escribir a un segmento de
rollback.
2. Segmentos de rollback: Propósito
•
Rollback de la transacción
•
Recuperación de la transacción
•
Consistencia en la lectura
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 44
XI.
Auditoría de la base de datos.
Privilegios de operación de la auditoría
-
Auditoría siempre
-
Iniciar la base, bajar la base y conexiones de SYSDBA
Auditoría de la base de datos
-
Habilitada por DBA
-
Incapaz de grabar registros en la base de datos
Valores base de auditoría en aplicaciones
-
Implementación mediante código
-
Poder grabar registros en la base.
-
Usar pistas de cambios en las tablas
El servidor de Oracle puede auditar acciones en la base de datos como son :
-
Iniciar una instancia, la auditoría registra el usuario del sistema operativo quien inicia la
instancia, identificación de la terminal y la fecha y la hora y cuando la auditoría es
habilitada o inhabilitada.
-
Bajar una instancia, de forma similar se registra la información del usuario del sistema
operativo, la terminal y la fecha y hora.
-
Conexiones a la base de datos con privilegios de administrador, se graba el registro con
la información del usuario que ingresa como SYSOPER o SYSDBA.
1. Auditoría de la base de datos
Auditoría es el monitoreo y grabación de acciones seleccionadas de los usuarios. La
información de los eventos es almacenada en la pista de auditoria.
La pista de auditoria puede ser usada para investigar actividades sospechosas. Por ejemplo,
si un usuario sin autorización borra datos de las tablas, el administrador de la base de datos
puede decidir auditar todas las conexiones a la base de datos en conjunto con borrados
exitosos o fallidos de registros.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 45
Puede por medio de la auditoría registrar estadísticas de las tablas mas actualizadas y/o
consultadas, para afinar las operaciones de Entrada/Salida.
2. Auditoría de valores base
La auditoría no puede registrar los valores de las columnas que son cambiados, si requiere
almacenar los datos anteriores de las columnas que son modificadas, se debe usar auditoría
de aplicaciones. La auditoría de aplicaciones se realiza mediante código en la herramienta
cliente, procedimientos almacenados, o triggers de la base de datos.
Para verificar la auditoría básica sobre conexiones, se puede examinar el visor de sucesos
de Windows NT, en la parte de Registro de aplicación.
3. Demostración de auditoria de valores base
Se posee una tabla de empleados, donde tenemos los siguientes campos:
Codigo
Nombre
Direccion
Teléfono
Por alguna razón se desea conservar la historia de los valores contenidos en esta tabla, en
caso de que se cambie la dirección o el teléfono del empleado, se desea mantener un registro
de los datos anteriores. Para esto se puede crear un trigger a la tabla que posea el siguiente
código.
CREATE TRIGGER auditoria_empleados
AFTER INSERT OR DELETE OR UPDATE
ON empleados
FOR EACH ROW
BEGIN
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 46
INSERT INTO aud_empleados
VALUES (:OLD.codigo, :OLD.nombre, :OLD.direccion, :OLD.telefono,
:NEW.codigo, :NEW.nombre, :NEW.direccion, :NEW.telefono,
USER, SYSDATE);
END;
Con el trigger anterior, cada vez que se inserte, modifique o borre un registro de la tabla
empleados, se guardará la información en la tabla aud_empleados (La tabla aud_empleados
debe ser creada por el administrador o alguna persona encargada de la auditoría).
4. Usando auditoría de la base de datos
El administrador de la base de datos debe definir claramente el propósito de la auditoría, de
lo contrario puede que la información generada por la auditoría pueda crecer
desmesuradamente, sin ofrecer información valiosa, por otro lado puede llegar a degradar el
desempeño de la base de datos.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 47
Habilitar la auditoría de la base de datos
Una vez que se decide que auditar, se debe asignar el parámetro de inicialización
AUDIT_TRAIL, para que habilite la auditoría para la instancia. Este parámetro indica si la
pista de auditoría se escribe a una tabla de la base de datos o a la pista de auditoría del
sistema operativo.
5. Especificación de las opciones de auditoría
El siguiente paso es especificar las opciones de auditoría usando el comando AUDIT, con
este comando se indica cuales comandos, usuarios, objetos o privilegios auditar. Se puede
indicar si un registro de auditoría debe ser generado por cada ocurrencia o una vez por
sesión. Si una opción de auditoría no es necesaria, se puede desabilitar la opción con el
comando NOAUDIT.
6. Ejecución de instrucciones
Cuando un usuario ejecuta una instrucción SQL o PL/SQL, el servidor procede a examinar
las opciones de auditoría, para determinar si la ejecución del mismo debe generar algún
registro de auditoría. Si el comando SQL se encuentra dentro de un programa PL/SQL, es
necesario que sea ejecutado dicho comando para que se registre la auditoría, el bloque de
instrucciones debe pasar por dicho comando y puede llegar a generar varios registros de
auditoría.
7. Generación de datos de auditoría
La generación e inserción de un registro de auditoría, es independiente de la transacción del
usuario; esto es, si la transacción del usuario realiza rollback, el registro de auditoria
permanece intacto. Sin embargo el registro de auditoría es generado durante la fase de
ejecución, lo cual significa que si el comando posee un error de sintaxis no se genera un
registro de auditoría.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 48
8. Revisión de la información de auditoría
La información de la auditoría se revisa, mediante selecciones a la pista de auditoría de
vistas del diccionario de datos o usando la utilidad del sistema operativo. Esta información
es utilizada para investigar actividades sospechosas y monitorear la actividad de la base de
datos.
9. Habilitando la auditoría de la base de datos
El administrador de la base de datos asigna al parámetro de inicialización AUDIT_TRAIL
habilitar la auditoría para la instancia.
Sintaxis
AUDIT_TRAIL = valor
Donde valor puede tomar uno de los siguientes valores:
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 49
VALOR
DESCRIPCION
DB
Habilita la auditoría y direcciona todos los registros de auditoría a la pista de
auditoría de la base de datos (sys.aud$)
OS
Habilita la auditoría y direcciona todos los registros de auditoría a la pista de
auditoría del sistema operativo. (Si es permitido por este)
NONE
Deshabilita la auditoría (Este es el valor por defecto).
Nota curiosamente al buscar el parámetro en el init.ora, se encontró que el parámetro estaba
comentariado y poseía el siguiente valor.
#audit_trail = true
Este valor no se menciona como un posible valor para ser asignado al parámetro.
Buscando en la documentación se encuentra que se puede asignar el valor TRUE al
parámetro y se comporta como si fuera DB, anotan que es por compatibilidad con versiones
anteriores.
10. Habilitando las opciones de auditoría
♦ Auditar instrucciones
AUDIT user;
♦ Auditar privilegios
AUDIT select any table BY scott BY ACCESS;
♦ Auditar objetos
AUDIT LOCK ON scott.emp BY ACCESS WHENEVER SUCCESFUL;
Se utiliza el comando AUDIT para especificar las opciones de auditoría. Estos registros de
auditoría nunca son generados por sesiones del usuario SYS o conexiones como
INTERNAL.
Instrucciones de auditoría
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 50
Se pueden ingresar los comandos de auditoría con instrucciones de SQL para un tipo de
objeto, las instrucciones que se pueden auditar por ejemplo son CREATE, ALTER, y DROP
USER para todos los usuarios.
Auditoría de privilegios
La auditoría de privilegios se usa para verificar el uso de los privilegios del sistema. Por
ejemplo siempre que el usuario Scott realice un SELECT sobre cualquier tabla, se genere un
registro de auditoría, cuando el usuario realiza una consulta sobre una tabla en la cual no
tiene privilegios no se genera el registro de auditoría.
Sintaxis de los comandos de auditoría
Privilegios o auditoría de instrucciones
AUDIT {statement|system_priv}
[,{statement|system_priv}]....
[BY user [, user]...]
[BY {SESSION|ACCESS} ]
[WHENEVER [NOT] SUCCESSFUL]
Auditoría de objetos
AUDIT statement [,statement]...
ON {[schema.]objeto|DEFAULT}
[BY {SESSION|ACCESS}]
[WHENEVER [NOT] SUCCESSFUL]
Donde:
Statement
Especifica la instrucción SQL o el esquema-objeto a auditar.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 51
System_priv
Especifica el privilegio del sistema a auditar.
Schema.schema-object
Especifica el objeto seleccionado para auditoría.
Default
Conjunto de objetos específicos como objetos por defecto.
User
Indica el usuario sobre el cual realizar la auditoría, si se omite se
realizará la auditoria para todos los usuarios.
BY SESSION
Origina que Oracle inserte únicamente un registro por objeto de la
base de datos en el audit trail por cada sesión, esta es la opción
por defecto, excepto para instrucciones DDL.
BY ACCESS
Origina que Oracle inserte un registro en el audit trail, cada vez
que una instrucción auditada es enviada.
WHENEVER
Especifica que la auditoría sea realizada únicamente para
instrucciones satisfactorias o fallidas. El valor por defecto es
ambas.
Es importante anotar que en caso de realizarse la auditoría a instrucciones fallidas, no tiene
en cuenta una consulta de una tabla que no exista, el registro de auditoría es realizado
durante la fase de ejecución.
La auditoría de privilegios e instrucciones especificadas por el comando AUDIT, solo
aplican para las sesiones siguientes, no para las sesiones activas, en cambio la auditoría
sobre objetos se hace efectiva inmediatamente.
11. Auditoría de esquema de objetos
IV.
Opción
Objeto
XII.
TABLA
VISTA
SECUENCIA
PROCEDIMIENTO
SNAPSHOT
ALMACENADO
ALTER
X
X
AUDIT
X
X
COMMENT
X
X
X
DELETE
X
X
X
EXECUTE
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
X
X
X
X
X
Página 52
GRANT
X
X
X
X
X
INDEX
X
INSERT
X
X
X
LOCK
X
X
X
RENAME
X
X
SELECT
X
X
UPDATE
X
X
X
READ
X
X
X
X
X
Observar las opciones de auditoría
Vista del diccionario de datos
Descripción
ALL_DEF_AUDIT_OPTS
Opciones de auditoría por defecto
DBA_STMT_AUDIT_OPTS
Opciones de auditoría de instrucciones
DBA_PRIV_AUDIT_OPTS
Opciones de auditoría de privilegios
DBA_OBJ_AUDIT_OPTS
Opciones de auditoría de objetos
12. Desabilitar las opciones de auditoría
♦ NOAUDIT user WHENEVER SUCCESSFUL;
♦ NOAUDIT create table BY scott;
♦ NOAUDIT LOCK ON emp;
La instrucción NOAUDIT para la auditoría especificada en el comando.
NOAUDIT {statement|system_priv}
[, {statement|system_priv} ] . . .
[BY user [, user ] . . . ]
[WHENEVER [NOT] SUCCESFULL]
NOAUDIT statement [,statement ] . . .
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 53
ON {[schema.]object | DEFAULT}
[WHENEVER [NOT] SUCCESSFUL ]
La instrucción NOAUDIT reversa el efecto de una instrucción AUDIT previa.
La instrucción NOAUDIT puede tener la misma sintaxis de una instrucción AUDIT previa y
con ello desabilitar la auditoría que se había establecido, sin embargo, si una instrucción
AUDIT (Instrucción 1) habilita la auditoría para un usuario específico, y una segunda
instrucción AUDIT (Instrucción 2) habilita la auditoría para todos los usuarios, al ejecutar
una instrucción NOAUDIT que desabilite la auditoría para todos los usuarios, ésta reversa la
Instrucción 2, pero permite que la auditoría asignada con la Instrucción 1 continúe.
13. Viendo los resultados de la auditoría
La pista de auditoría:
♦ Almacena los registros generados por instrucciones, privilegios y objetos auditados.
♦ El registro de auditoría está almacenado en la tabla del diccionario de datos SYS.AUD$
o en el registro de auditoría del sistema operativo.
♦ Cada registro en la pista de auditoría incluye:
§
El usuario quien ejecutó la instrucción
§
El comando emitido
§
Cualquier privilegio de objeto usado
§
Los objetos referenciados en la instrucción
§
La fecha y hora de ejecución de la instrucción
Localización de la pista de auditoría
Los registros de auditoría pueden ser almacenados en una tabla del diccionario de datos,
llamada la base de datos de pistas de auditoría (Database Audit Trail) o en la pista de
auditoría del sistema operativo.
Contenido de la pista de auditoría
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 54
Cada registro de la pista de auditoría incluye:
♦ El nombre del usuario
♦ La identificación de la sesión
♦ La identificación de la terminal
♦ El nombre del objeto accesado
♦ La operación desempeñada
♦ El código completo de la operación
♦ La fecha y la hora
♦ Los privilegios del sistema utilizados
Vista de la pista de auditoría
Descripción
DBA_AUDIT_TRAIL
Todas las entradas de la pista de auditoría
DBA_AUDIT_EXISTS
Registros para AUDIT EXISTS/NOT EXISTS
DBA_AUDIT_OBJECT
Registros concernientes al ambiente de los objetos
DBA_AUDIT_SESSION
Todas las conexiones y desc onexiones
DBA_AUDIT_STATEMENT
Registros de auditoría de instrucciones
Recomendación mover la pista de auditoría fuera del tablespace system, ya que la pista de
auditoría puede ser muy extensa.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 55
XIII.
Comunicación con un servidor remoto
1. Resolución del esquema de nombres de objetos
Para que una aplicación resuelva las referencias del esquema de objetos (Un proceso
llamado name resolution), Oracle utilizar una jerarquía de nombres de objetos. Por ejemplo,
en una base de datos sola, se garantiza que un esquema tiene un nombre único, y dentro de
este cada objeto tiene un nombre único, de esta manera se garantiza la unicidad de un nombre
de esquema-objeto.
En una base de datos distribuida, un esquema puede encontrarse repetido en dos bases de
datos diferentes, o puede suceder con un nombre de objeto, la solución es agregando a la
jerarquía de nombres el nombre global de la base de datos.
2. Conexión entre versiones diferentes de Oracle
Un sistema distribuido de bases de datos, puede incorporar bases de datos Oracle de
diferentes versiones, todas las versiones soportadas de Oracle pueden participar en un
sistema distribuido de bases de datos. Es importante anotar que se deben conocer los objetos
en ambas versiones para que se puedan comprender las dos bases de datos. Por ejemplo, si
en un motor Oracle 8i se poseen tablas de objetos, estas no pueden ser comprendidas por una
base de datos basada en la versión 7.
3. Bases de datos distribuidas y procesos distribuidos
Los términos base de datos distribuida y procesos distribuidos se encuentran relacionados
pero tienen grandes diferencias entre sí.
Base de datos distribuida: Es un conjunto de bases de datos que pueden estar almacenadas en
diferentes computadores y se comportan en la aplicación como una sola base de datos.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 56
Proceso distribuido: Ocurre cuando una aplicación distribuye tareas en diferentes
computadores en una red.
4. Comunicación entre servidores
La forma de establecer la comunicación entre dos bases de datos ORACLE es mediante un
database link, por lo menos fue una forma de comunicación que encontré. Cada base de datos
es conocida por un global database name, este se encuentra definido por el dominio de la
máquina y por el nombre de la base de datos, de esta forma se puede lograr la comunicación
a través de una red, intranet o internet.
En la figura se describe una base de datos distribuida.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 57
5. Database Links
Para facilitar los requerimientos de un sistema distribuido de bases de datos, ORACLE
utiliza los database links, un database link define una via de comunicación desde una base de
datos a otra.
Un database link es escencialmente transparente para los usuarios de un sistema de base de
datos distribuido, a causa que el nombre del database link es el nombre global de la base de
datos.
Por ejemplo, basado en la gráfica anterior, se va a crear un database link a la base remota
SALES.US.AMERICAS.ACME_AUTO.COM
CREATE DATABASE LINK sales.us.americas.acme_auto.com ... ;
Después de la creación del database link, las aplicaciones conectadas a la base de datos
local pueden acceder a la información de la base de datos remota, de la siguiente manera:
SELECT * from scott.emp @sales.us.americas.acme_auto.com
Para realizar la práctica se instaló otra base de datos en la misma máquina, para poder
realizar el database link, la base de datos fue creada mediante la utilización del asistente
para la creación de bases de datos, de esta forma quedaron instaladas en la máquina las
bases de datos DBCAREM y DBCAREM2, para crear el database link es necesario que el
listener conozca la ruta de las dos bases de datos, por encontrarse en la misma máquina no
fue necesario realizar cambios.
Al crear el database link no presentó ningún inconveniente, pero al ejecutar una instrucción
donde se utilizaba si, se presentaba un error, el cuál significa que no puede localizar el
nombre de la base de datos.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 58
En la oficina con ayuda por parte de mi jefe se realizaron pruebas en dos bases de datos
montadas en el mismo servidor sobre ambiente Unix, las cuales fueron satisfactorias,
después de realizar varios ensayos, se comentario una línea del init.ora de cada una de las
bases de datos, las cuales poseen la siguiente información:
Global_names = true
Estas mismas líneas se encuentras en el archivo de configuración de las bases de datos sobre
Unix y no impedian el funcionamiento del database link, pero en el caso de NT no permitian
que este funcionara.
Tipos de database links
Para soportar que la aplicación acceda a los datos de bases de datos diferentes en un
ambiente distribuido, los administradores deben crear todos los database link necesarios,
existen diferentes tipos de database link como son:
♦ PRIVATE
♦ PUBLIC
♦ GLOBAL DATABASE LINK
Private database link
Se puede crear un database link privado en un esquema específico de la base de datos, de
forma que únicamente el dueño del database link privado, o subprograma PL/SQL en el
esquema pueda usar el database link para acceder a datos y objetos en la correspondiente
base de datos remota.
Public database link
Es el caso contrario donde todos los usuarios pueden acceder a los datos y objetos en la
correspondiente base de datos remota.
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 59
Global database link
Cuando una red ORACLE utiliza Oracle names, los nombres de los servidores
automáticamente crean y manejan global database links por cada base de datos Oracle en la
red, de esta forma todos los usuarios y subprogramas en una base de datos pueden acceder a
la información y objetos de las demás bases de datos en la red.
Un private database link es más seguro que un public database link y que un global database
link, ya que el dueño es el único que puede acceder a la base de datos distribuida.
Cuando se requiere que muchos usuarios accedan a la información de otra base de datos, lo
más recomendable es la creación de un public database link.
Cuando se trabaja en un ambiente Oracle Network que utilice Oracle names, la
administración de los database links es centralizada y simple.
Opciones de seguridad para database link
Un database link define un camino de comunicación entre dos bases de datos. Cuando una
aplicación usa un database link para acceder a la base de datos remota, Oracle establece una
sesión en la base de datos remota.
Cuando se crea el database link público o privado, se puede establecer el esquema de
trabajo en el base de datos remota, mediante la asignación del usuario con el cual va a
ingresar a la base de datos remota.
Sintaxis de CREATE DATABASE LINK
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 60
Parámetros:
Shared
: Utiliza una conexión de red para crear un database link que pueda ser
compartido por múltiples usuarios.
Public
: Crea un database link público disponible para todos los usuarios, si se
omite esta palabra el database link es privado y solo es disponible para el usuario que lo
crea.
Dblink
: Es el nombre completo o parcial del database link, las restricciones
corresponden a que no se puede crear otro database link con el mismo nombre en otro
esquema.
Connect to
: Habilita una conexión a la base de datos remota.
Current_user : Crea el link con el usuario actual, de modo que el usuario debe poseer una
cuenta válida en el servidor remoto.
User identified by : Es el nombre y password del usuario con el que se conecta a la base de
datos remota.
Ejemplos
CREATE DATABASE LINK sales.hq.acme.com
CONNECT TO CURRENT_USER
USING ‘sales’;
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 61
CREATE DATABASE LINK sales.hq.acme.com
CONNECT TO scott IDENTIFIED BY tiger
USING ‘sales’;
Oracle – Administración de Bases de Datos
Presentado por Carlos Mayorga al Ing. Ismael Castañeda F.
Página 62