Creación de directorios en Oracle

Amog@s,

En este post se enseñara a crear un directorio para realizar los export e import en diferentes rutas de la predefinida por Oracle

Comencemos.

Hoy en día  los export  no se guardan en el mismo servidor, y si lo tienen es mejor ponerse como meta sacarlos, ya que por daño de servidor físico o fallo de disco podría perder su información y para una empresa cualesquiera que sea el costo es elevado.

Indicando esto comencemos

Oracle en su configuración crea una ruta por defecto y la registra con el nombre de DATA_PUMP_DIR  la ruta es:

OWNER                  DIRECTORY_NAME              DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------

SYS                            DATA_PUMP_DIR           $ORACLE_BASE/admin/SID/dpdump/

Vamos a crear  un directorio siguiendo los siguientes pasos:

1. Nos conectarnos a SQLPlus*  como sysdba

[oracle@local ~]$ cd

[oracle@local ~]$ . ./.bash_profile 

[oracle@local ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 30 11:40:43 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Verificamos los directorios existentes

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME          DIRECTORY_PATH

---------  ------------------------   -----------------------------

SYS       IMPORT1                                              /u01/import/

SYS       IMPORTAR                                           /u01/app/oracle/admin/sid_prod

SYS       EXP_DIR                                               /home/oracle

SYS       XMLDIR                                                /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS       IMPORT                                                /u01/import

SYS       DATA_PUMP_DIR                               /u01/app/oracle/admin/sid_prod/dpdump/

SYS       ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

7 rows selected.

3. Sentencia para crear un directorio

Formato:

CREATE DIRECTORY <  Nombre del directorio > AS < Ruta del directorio> ;

Ejemplo:

SQL> CREATE DIRECTORY RESPALDOS_EXP AS '/bck_prod';     

Directory created.

Nota: la carpeta /bck_prod es un punto de montaje fuera del servidor

4. Permisos de lectura y escritura sobre el directorio para realizar import y export

SQL> GRANT READ,WRITE ON DIRECTORY RESPALDOS_EXP TO IMP_FULL_DATABASE;

Grant succeeded.

SQL> GRANT READ,WRITE ON DIRECTORY RESPALDOS_EXP TO EXP_FULL_DATABASE;

Grant succeeded.

Esto es todo una vez creado esto ya pueden lanzar la sentencia de export o import apuntando al directorio RESPALDOS_EXP.

Ejemplo: 

[oracle@local ~]$ expdp directory:RESPALDOS_EXP dumpfile:exp_diag.dmp logfile:imp_diag.log schemas:diag exclude:statistics;

RMAN Database 12c: Recuperación de tablas por Joel Pérez

“Jamás serás capaz de plantear una excelente estrategia de respaldo sino eres experto recuperando…” 

<<Joel Pérez ORACLE ACE DIRECTOR'S>>

http://ocmacedjoelperez.blogspot.com/2013_09_01_archive.html

Vista Materializada (VM) Oracle

FORMATO

CREATE MATERIALIZED VIEW mi_vista_materializada
 [TABLESPACE mi_tablespace]
 [BUILD {IMMEDIATE | DEFERRED}]
 [REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicio] NEXT fecha_intervalo } |
          {COMPLETE | FAST | FORCE} ]
 [{ENABLE|DISABLE} QUERY REWRITE] AS
     SELECT t1.campo1, t2.campo2
     FROM mi_tabla1 t1 , mi_tabla2 t2
     WHERE t1.campo_fk = t2.campo_pk AND …

OPCIONES DE SENTENCIA

Carga de datos en la vista

BUILD IMMEDIATE: Los datos de la vista se cargan en el mismo momento de la creación
BUILD DEFERRED: Sólo se crea la definición, los datos se cargarán más adelante.

Para realizar esta carga se puede utilizar la función REFRESH del package DBMS_MVIEW:

      begin
         dbms_mview.refresh('mi_vista_materializada');
      end;

Cada cuánto tiempo se refrescarán:

REFRESH ON COMMIT: Cada vez que se haga un commit en los objetos origin definidos en la select

REFRESH ON DEMAND: Como con la opción DEFERRED del BUILD, se utilizarán los procedures
REFRESH, REFRESH_ALL_MVIEWS o REFRESH_DEPENDENT del package DBMS_MVIEW
REFRESH [START WITH fecha_inicio] NEXT fecha_intervalo:

          START WITH: indica la fecha del primer refresco (fecha_inicio suele ser un SYSDATE)

          NEXT: indica cada cuánto tiempo se actualizará (fecha_intervalo podría ser SYSDATE +1 para       realizar el refresco una vez al día)

De qué manera se refrescarán

REFRESH COMPLETE:

El refresco se hará de todos los datos de la vista materializada, la recreará completamente cada vez que se lance el refresco.
Utiliza todo el recurso necesario para completar la actualización en la hora indicada.

REFRESH FAST:

El refresco será incremental, es la opción más recomendable.
Este tipo de refresco tiene bastantes restricciones según el tipo de vista que se esté creando.
Se realiza ocupando el mínimo de recurso de memoria para completar la actualización. Terminara dependiendo del número de registros a actualizar como también la memoria que tiene disponible

Una de las cosas importantes a tener en cuenta es que para poder utilizar este método casi siempre es necesario haber creado antes un LOG de la Vista materializada, indicando los campos clave en los que se basará el mantenimiento de la vista.

Se utiliza la instrucción  "CREATE MATERIALIZED VIEW LOG ON":
 
CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen    
   WITH PRIMARY KEY    
   INCLUDING NEW VALUES;
REFRESH FORCE:

Con esta opción se indica que si es posible se utilice el metodo FAST, y si no el COMPLETE.

Para saber si una vista materializada puede utilizar el método FAST, el package DBMS_MVIEW proporciona el procedure EXPLAIN_MVIEW

Activación de la reescritura de consultas para optimizar el Data warehouse

ENABLE QUERY REWRITE: Se permite a la base de datos la reescritura de consultas
DISABLE QUERY REWRITE: Se desactiva la reescritura de consultas

QUERY REWRITE es la que más vamos a utilizar si queremos las vistas materializadas para optimizar nuestro Data warehouse.

Esta opción permite crear tablas agregadas en forma de vistas materializadas, y que cuando se lance una SELECT la base de datos pueda reescribirla para consultar la tabla o vista que vaya a devolver los datos solicitados en menos tiempo, todo de manera totalmente transparente al usuario.

Lo único que hay que hacer es crear las tablas agregadas como vistas materializadas con QUERY REWRITE habilitado.

LINUX: Pasos para bajar una base de datos ORACLE en forma segura

Cuando se generen inconvenientes en su empresa y por A o B razón deben bajar las bases de datos, la forma segura de realizar esto es siguiendo los siguientes pasos:

1.-Ingresar al servidor 

ssh o por telnet para ingresar al SQLPlus* ya sea por putty , SSH-Security o cualquier herramienta que maneje su negocio para administrar servidores

2.-Cargar las variables de ambiente

Nota:Este paso es necesario cuando el ambiente de oracle no se encuentra cargado en la sesión actual de sistema operativo

[oracle@docec admin]$ cd

[oracle@docec ~]$ . ./.bash_profile

[oracle@docec ~]$ . oraenv
ORACLE_SID = [nueva] ? nueva
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

3.-Detener el listener

Esta acción permite que usuarios nuevos no puedan conectarse

[oracle@docec ~]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 21-MAY-2014 14:24:08

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
The command completed successfully

4.-Apagar la base de forma segura

[oracle@docec ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed May 21 14:25:16 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

5.- Apagar el sistema operativo

[oracle@docec ~]$ poweroff

Nota: Realizar los pasos 2 y 4 por cada Instancia (SID) que se tenga en el servidor

Conexión de Base de datos DBLink

DATABASE LINK

Para que sirve un DBLink?

Un conexión de base de datos externa o simplemente DBLink, sirve para conectarte con otra base de datos y así poder extraer información de esa base.

Creación del DBLink

Para crear un dblink se deben tomar en cuenta los siguientes puntos


- Agregar la entrada de conexión en el tnsname.ora

[host@local ~]$ vim $ORACLE_HOME/networck/admin/tnsname.ora

Descripción de la nueva entrada:

NUEVA_ENTRADA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nueva_entrada.rfcg.com)
    )
  )

Nota: Para validar que todo se encuentre funcionando, realizamos una prueba ejecutando

 [host@local ~]$ tnsping NUEVA_ENTRADA

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 21-MAY-2014 12:09:23

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nuva.rfcg.com)))
OK (10 msec)


-En SQLPlus*, Toad , SqlDeveloper o cualquier ambiente que se tenga para poder ejecutar el scrip ejecutamos.

Formato:

CREATE [PUBLIC] DATABASE LINK <NOMBRE DEL DBLINK>
CONNECT TO <NOMBRE DEL USUARIO>
IDENTIFIED BY <CLAVE DEL USUARIO>
USING <NOMBRE DE LA ENTRADA EN EL TNSNAME>

Ejemplo:

SQL> CREATE PUBLIC DATABASE LINK "DBL_CONEXION_PROD.RGCG.COM"
CONNECT TO JFABRE
IDENTIFIED PassWOrD
USING 'NUVA_ENTRADA';

Añadir un Datafile en 11g

AÑADIR DATAFILE

VERSION 11G

La creación de  datafile's para un DBA es muy común. y mas común aun es, no tener ambiente gráfico para crearlo por lo que en este blog se enseña  como crear un datafile por sqlplus*


Como saber cuando es necesario el incremento de un datafile, es cuando:

- El o los datafiles que componen el tablespace no se pueden extender más porque hemos llegado a la máxima capacidad del disco donde este ubicado
- Hemos llegado el máximo permitido por Oracle.

La recomendación para ampliar el datafile automáticamente va de la mano con la  transacción que posee su base de datos, si es una base que no posee mucha transacción con 10MB esta bien.

Se recomienda ampliar y crear un nuevo datafile si todos los que posee el tablespace estan al 100% y el ultimo esta a un 90%

Problema: Tenemos el tabelspace DATA de la base de datos TEST que esta por encima del 90% de ocupación y necesitamos ampliarlo, desde sqlplus*

 1.-Revisión de los datafile llenos.

SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='DATA';


FILE_NAME                                                                     Tamaño en MB
--------------------------------------------------------------                     -----------------
/u02/oradata/TEST/dat/TEST_DATA_01.dbf               32768
/u02/oradata/TEST/dat/TEST_DATA_02.dbf               32768

El tablespace DATA tiene ya 2 datafiles de 32GB que no podemos extender por loque vamos a añadirle un tercer datafile, vamos a añadir el datafile TEST_DATA_03.dbf de 5GB, nos aseguramos que tenemos el espacio libre el filesytem y realizamos la siguiente operación en sqlplus:

2.- Agregar datafile al tablesapce

SQL> ALTER TABLESPACE DATA ADD DATAFILE '/u02/oradata/TEST/dat/TEST_DATA_03.dbf’ SIZE 5120M;

3.- Revisión del datafile nuevo.

SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME  ='DATA';


FILE_NAME                                                                     Tamaño en MB
--------------------------------------------------------------                     -----------------
/u02/oradata/TEST/dat/TEST_DATA_01.dbf               32768
/u02/oradata/TEST/dat/TEST_DATA_02.dbf               32768
/u02/oradata/TEST/dat/TEST_DATA_03.dbf                 5120

Se puede? - export y import 10g a 11g

Hoy tuve la oportunidad de realizar  un ambiente de pruebas,  fue pasar un export de 10g a un servidor donde se tiene instalado 11g.

Nota: Se deben realizar las debidas pruebas en el servidor de 11g ya que paquetes propios de Oracle cambiaron sus nombres en esta versión así como también algunas funcionalidades.

 Esto si es posible siguiendo los siguientes pasos

1: Crea los  tablespaces, usuarios, roles y  perfiles en la base  11g.
2: Export full del SID la base en 10g utilizando el parámetro versión=10.<Release>
3: Copiar los archivos dumpfile del servidor con 10g al de 11g en la ruta por defecto DATA_PUMP_DIR o cualquier otra ruta.

         Nota: Si se envía a otra ruta se debe crear el directorio. Aquí explicamos como : http://oracle-             facil.blogspot.com/2014/05/creacion-de-directorios-oracle.html
4: Ejecutar el comando Import full en la base 11g.

<<Relato de un DBA>>

Consultas de Administración (DBA Query's) Oracle 11g

CONSULTAS DBA

VERSIÓN 11g

1. Introducción

2. Consultas SQL

1. Introducción

En estos días eh  tenido muchas experiencias como dba sin un entorno gráfico solo sqlplus*  y eh realizado un pequeño diccionario de query's que permiten la rápida administración de una base de datos, cada uno de estos pueden ser optimizado a las necesidades requeridas.

2. Consultas SQL

Nota: Para poder visualizar correctamente se puede dar formato a las columnas de salida en el sqlplus* con

SQL> SET LINESIZE 200

SQL> SET PAGESIZE 200

• Instancia: Instancia a la que se esta conectado.

SQL> select host_name,instance_name,status from v$instance;

HOST_NAME INSTANCE_NAME  STATUS

----------------------------------------------------------------

rfcg1.oracle.com prod  OPEN

• Directorios registrados:  Directorios registrados en la base de datos

SQL> select * from dba_directories;

OWNER       DIRECTORY_NAME                    DIRECTORY_PATH

------------------------------ ------------------------------  ----------------------       

SYS ORACLE_OCM_CONFIG_DIR      /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SYS       DATA_PUMP_DIR      /u01/app/oracle/admin/prod/dpdump/

SYS       XMLDIR               /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml 

• Registro de usuarios: Listado de usuarios que se conectaron con su numero de veces por día

SQL > SELECT USERNAME,LOGON_TIME,COUNT(*) AS NUMERO 

 FROM V$SESSION 

 WHERE USERNAME IS NOT NULL

--AND LOGON_TIME=SYSDATE

 GROUP BY LOGON_TIME,USERNAME 

 ORDER BY 3 DESC;

USERNAME        LOGON_TIM     NUMERO

------------------------------ --------- ----------

SYSMAN        30-APR-14   3

SYSMAN        01-MAY-14   1

DBSNMP        29-APR-14   1

DBSNMP        29-APR-14   1

SYS                07-MAY-14   1

SYSMAN        30-APR-14   1

SYSMAN        30-APR-14   1

SYSMAN        07-MAY-14   1

DBSNMP        29-APR-14   1

• Tablespace: 

- Espacio disponible en tablespace

SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) as "MB free" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME  MB free

------------------------------ ----------

SYSAUX       32

UNDOTBS1       22

USERS 3

SYSTEM 3

- Espacio total de tablespace

SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) as "MB free" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME  MB free

------------------------------ ----------

UNDOTBS1       30

SYSAUX      530

USERS 5

SYSTEM      700

• Datafile

SQL> SELECT FILE_NAME,BYTES/1024/1024  AS MB FROM DBA_DATA_FILES;

FILE_NAME                                                             MB  

-----------------------------                                     ----------

/u01/app/oracle/oradata/prod/users01.dbf                  5

/u01/app/oracle/oradata/prod/undotbs01.dbf             30

/u01/app/oracle/oradata/prod/sysaux01.dbf              530

/u01/app/oracle/oradata/prod/system01.dbf              700

• Modo de la base de datos: Identificar si la base esta corriendo sobre archivelog o noarchivelog

SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;

NAME  LOG_MODE

--------- ------------

PROD  ARCHIVELOG

• Auditoria: Identificar si la base esta activada la auditoria

SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME LIKE 'audit_trail';

NAME                          VALUE

----------------------    ----------------------------

audit_trail                       DB

• Controlfile: Identificar la ruta donde se encuentran nuestros controlfile

SQL> show parameter control_files;

NAME       TYPE          VALUE

----------------    -----------    ------------------

control_files         string            /u01/app/oracle/oradata/prod/control01.ctl, 

                                               /u01/app/oracle/fast_recovery_area/prod/control02.ctl

• Fast recovery Area (FRA): Tamaño de la FRA

SQL>SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

NAME        TYPE            VALUE

----------------------------    -----------      ----------------------

db_recovery_file_dest_size big integer     4122M

• Tamaño actual de la base de datos

SQL> SELECT SUM(BYTES)/1024/1024 MB FROM DBA_SEGMENTS;

MB

----------

    1201.5

• Esquemas creados

SQL> SELECT USERNAME FROM DBA_USERS;

USERNAME

------------------------------

SYS

SYSTEM

DBVISIT7

OUTLN

MGMT_VIEW

FLOWS_FILES

MDSYS

ORDSYS

EXFSYS

DBSNMP

WMSYS

APPQOSSYS

APEX_030200

OWBSYS_AUDIT

ORDDATA

CTXSYS

ANONYMOUS

SYSMAN

XDB

ORDPLUGINS

OWBSYS

SI_INFORMTN_SCHEMA

OLAPSYS

SCOTT

ORACLE_OCM

XS$NULL

MDDATA

DIP

APEX_PUBLIC_USER

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

• Tablas con propietario especifico

SQL> SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM < 11;

OWNER                                             OBJECT_NAME
------------------------------      --------------------------------------------------
SYS                                          ICOL$
SYS                                          I_USER1
SYS                                          CON$
SYS                                          UNDO$
SYS                                          C_COBJ#
SYS                                          I_OBJ#
SYS                                          PROXY_ROLE_DATA$
SYS                                          I_IND1
SYS                                          I_CDEF2
SYS                                          I_OBJ5

Auditoria en Oracle 11g

AUDITORIA EN ORACLE

VERSIÓN: 11G

1. Auditoria informática
2. Auditoria en Oracle
3. Tablas y Vistas
4. Comprobar activación de auditoria
5. Comandos audit y noaudit
6. Consultas de auditoria
7. Descripción de tablas de auditoria

Auditoria informática

Consiste en recoger, agrupar y evaluar evidencias para determinar si un sistema de información salvaguarda el activo empresarial, mantiene la integridad de los datos, lleva a cabo eficazmente los fines de la organización, utiliza eficientemente los recursos, y cumple con las leyes y regulaciones establecidas.

Auditoria para Oracle

En el caso de Oracle, la auditoría es un conjunto de características que permite al administrador de la base de datos y a los usuarios hacer un seguimiento del uso de la base de datos. El administrador de base de datos puede definir la actividad de auditoría predeterminada. La información de las auditorías se almacena en el diccionario de datos, en la tabla SYS.AUD$ o en la pista de auditoría del sistema operativo (si lo permite). Lo anterior viene definido en el parámetro audit_trail
.

Se pueden auditar tres tipos de acciones: intentos de inicio de sesión, accesos a objetos y acciones de la base de datos. Cuando se realizan auditorías, la funcionalidad de la base de datos es dejar constancia de los comandos correctos e incorrectos. Esto puede modificarse cuando se configura cada tipo de auditoría.

 Tablas y Vistas

Oracle almacena en la tabla SYS.AUD$ o en la pista de auditoría del sistema operativo (si lo permite).

Existen varias vistas que se usan para ayudar a la extracción de los datos  deseado en una auditoria en esta tabla (SYS.AUD$).

• ALL_AUDIT_POLICIES
• ALL_AUDIT_POLICY_COLUMNS
• ALL_DEF_AUDIT_OPTS
• ALL_REPAUDIT_ATTRIBUTE
• ALL_REPAUDIT_COLUMN
• APEX_DEVELOPER_AUDIT_LOG
• DBA_AUDIT_EXISTS
• DBA_AUDIT_OBJECT
• DBA_AUDIT_POLICIES
• DBA_AUDIT_POLICY_COLUMNS
• DBA_AUDIT_SESSION
• DBA_AUDIT_STATEMENT
• DBA_AUDIT_TRAIL
• DBA_COMMON_AUDIT_TRAIL
• DBA_FGA_AUDIT_TRAIL
• DBA_OBJ_AUDIT_OPTS
• DBA_PRIV_AUDIT_OPTS
• DBA_REPAUDIT_ATTRIBUTE
• DBA_REPAUDIT_COLUMN
• DBA_STMT_AUDIT_OPTS
• GV_$XML_AUDIT_TRAIL
• KU$_AUDIT_DEFAULT_VIEW
• KU$_AUDIT_OBJ_BASE_VIEW
• KU$_AUDIT_OBJ_VIEW
• KU$_AUDIT_VIEW
• KU$_PROC_AUDIT_VIEW
• KU$_PROCDEPOBJ_AUDIT_VIEW
• KU$_PROCOBJ_AUDIT_VIEW
• KU$_10_1_AUDIT_VIEW
• MGMT$AUDIT_LOG
• MGMT$ESA_AUDIT_SYSTEM_REPORT
• SM$AUDIT_CONFIG
• USER_AUDIT_OBJECT
• USER_AUDIT_POLICIES
• USER_AUDIT_POLICY_COLUMNS
• USER_AUDIT_SESSION
• USER_AUDIT_STATEMENT
• USER_AUDIT_TRAIL
• USER_OBJ_AUDIT_OPTS
• USER_REPAUDIT_ATTRIBUTE
• USER_REPAUDIT_COLUMN
• V_$XML_AUDIT_TRAIL

Estas vistas se pueden ver ejecutando la consulta SQL:

SELECT view_name
FROM dba_views
WHERE view_name LIKE '%AUDIT%'
ORDER BY view_name

Las principales son:

- DBA_AUDIT_OBJECT: guarda la información relativa a la auditoría de

- DBA_AUDIT_SESSION: guarda la información relativa a la auditoría de los inicios de sesión de los usuarios.

- DBA_AUDIT_TRAIL: muestra la auditoría estándar (de la tabla AUD$)

- USER_AUDIT_TRAIL: muestra la auditoría estándar (de la tabla AUD$) relativa al usuario actual

- DBA_FGA_AUDIT_TRAIL: muestra información de auditoría de grano fino (obtenida de FGA_LOG$). La auditoría de grano fino (FGA) extiende la auditoría estándar y, además, captura la sentencia SQL que ha sido ejecutada.

Nota: Todo lo anterior estará condicionado al tipo de auditoría que se haya establecido para la base de datos Oracle,

Comprobar activación de auditoría

La activación de la auditoría en Oracle  viene definida por el valor del parámetro: audit_trail.

Para comprobar si la auditoría de la base de datos está activa ejecutamos el siguiente query :

select name, value
from v$parameter
where name like 'audit_trail'

Valores:

- none: desactiva la auditoría de la base de datos.

- os: activa la auditoría de la base de datos. Los sucesos auditados se escribirán en la pista de auditoría del sistema operativo, no se auditará en Oracle sino en el sistema operativo anfitrión. Esta opción funcionará dependiendo del sistema operativo.

- db: activa la auditoría y los datos se almacenarán en la taba SYS.AUD$ de Oracle.

- db, extended: activa la auditoría y los datos se almacenarán en la taba SYS.AUD$ de Oracle. Además se escribirán los valores correspondientes en las columnas SQLBIND y SQLTEXT de la tabla SYS.AUD$.

- xml: activa la auditoría de la base de datos, los sucesos será escritos en ficheros XML del sistema operativo.

- xml, extended: activa la auditoría de la base de datos, los sucesos será escritos en el formato XML del sistema operativo, además se incluirán los valores de SqlText y SqlBind.

Activa la auditoria

ALTER SYSTEM SET audit_trail = "DB" SCOPE=SPFILE;

Desactivar la auditoria

ALTER SYSTEM SET audit_trail = "NONE" SCOPE=SPFILE;

Nota: En Oracle 11g la auditoria viene activada por defecto, el valor del parámetro "audit_trail" está a "DB".

Comandos audit y noaudit

Audit

Este comando puede funcionar aunque no esté activada la auditoría de la base de datos. Pero no dejara registro alguno guardado.

Auditorías de inicio de sesión: 

audit session;

Nota: Auditará tanto los intentos fallidos como los aciertos.

             Sólo los intentos fallidos : audit session whenever not successful;
             Sólo los intentos fallidos : audit session whenever  successful;

Auditorías de acción: 

Nota: Cualquier acción que afecte a un objeto de la base de datos (tabla, enlace de base de datos, espacio de tablas, sinónimo, segmento de anulación, usuario, índice, etc.) puede auditarse

audit role; 

Nota: Este comando activará la auditoría de las acciones: create role, alter role, drop role y set role.

Auditar a un usuario al realizar la acción "update" :

audit update table by nombre_usuario;

Auditorías de objeto: 

Auditar las acciones de manipulación de datos sobre objetos.

Por ejemplo, para auditar los "insert" realizados sobre una  tabla:

audit insert on <TABLA> by access;

Nota: al indicar "by access" hay que tener cuidado pues registrará un suceso de auditoría por cada insert, esto puede afectar al rendimiento. De ser así siempre será mejor optar por "by session" que sólo registrará un suceso de auditoría por sesión, aunque es menos exaustivo.

Sintaxis

AUDIT { sql_statement_clause | schema_object_clause | NETWORK } [ BY { SESSION | ACCESS } ] [ WHENEVER [ NOT ] SUCCESSFUL ] ; 

• sql_statement_clause: activa la auditoría para una sentencia SQL concreta.
• schema_object_clause: activa la auditoría para un objeto concreto de la base de datos.
• WHENEVER SUCCESSFUL: activa la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que se completen con éxito.
• WHENEVER NOT SUCCESSFUL: activa la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que originen error.

Noaudit

La instrucción noaudit se utiliza para detener la actividad de auditoría que se había activado previamente con la instrucción audit.

La instrucción noaudit debe tener la misma sintaxis que la instrucción audit que queramos detener.

Por ejemplo, si hemos auditado un usuario con:

audit session by alonso;

Auditará los inicios de sesión para el usuario de Oracle "alonso", tanto los fallidos como los correctos. Para desactivar esta auditoría ejecutaremos el comando:

noaudit session by alonso;

Sintaxis

NOAUDIT { sql_statement_clause | schema_object_clause | NETWORK} [ WHENEVER [ NOT ] SUCCESSFUL ] ;  

• sql_statement_clause: detiene la auditoria de una sentencia SQL concreta.
• schema_object_clause: detiene la auditoría para un objeto concreto de la base de datos.
• WHENEVER SUCCESSFUL: detiene la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que se completen con éxito.
• WHENEVER NOT SUCCESSFUL: detiene la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que originen error.

Consultas de auditoria

• Auditoria en inicio de sesión :

  select OS_Username Usuario_SO, Username Usuario_Oracle, Terminal ID_Terminal, DECODE (Returncode, '0', 'Conectado', '1005', 'Fallo - Null', 1017, 'Fallo', Returncode) Tipo_Suceso, TO_CHAR(Timestamp, 'DD-MM-YY HH24:MI:SS') Hora_Inicio_Sesion, TO_CHAR(Logoff_Time, 'DD-MM-YY HH24:MI:SS') Hora_Fin_Sesion from DBA_AUDIT_SESSION;

• Auditoria por acción :

  select OS_Username Usuario_SO, 
    Username Usuario_Oracle, Terminal ID_Terminal,
    Owner Propietario_Objeto,
    Obj_Name Nombre_Objeto, 
    Action_Name Accion,
    DECODE (Returncode, '0', 'Realizado', 'Returncode') Tipo_Suceso,
    TO_CHAR (Timestamp,  'DD-MM-YY HH24:MI:SS') Hora
  from DBA_AUDIT_OBJECT;

  Descripción de tablas de auditoria

  Estructura de la tabla SYS.AUD$:

Campo	Tipo de datos	Tamaño	Permite nulos
SESSIONID	NUMBER	22	N
ENTRYID	NUMBER	22	N
STATEMENT	NUMBER	22	N
TIMESTAMP#	DATE	7	Y
USERID	VARCHAR2	30	Y
USERHOST	VARCHAR2	128	Y
TERMINAL	VARCHAR2	255	Y
ACTION#	NUMBER	22	N
RETURNCODE	NUMBER	22	N
OBJ$CREATOR	VARCHAR2	30	Y
OBJ$NAME	VARCHAR2	128	Y
AUTH$PRIVILEGES	VARCHAR2	16	Y
AUTH$GRANTEE	VARCHAR2	30	Y
NEW$OWNER	VARCHAR2	30	Y
NEW$NAME	VARCHAR2	128	Y
SES$ACTIONS	VARCHAR2	19	Y
SES$TID	NUMBER	22	Y
LOGOFF$LREAD	NUMBER	22	Y
LOGOFF$PREAD	NUMBER	22	Y
LOGOFF$LWRITE	NUMBER	22	Y
LOGOFF$DEAD	NUMBER	22	Y
LOGOFF$TIME	DATE	7	Y
COMMENT$TEXT	VARCHAR2	4000	Y
CLIENTID	VARCHAR2	64	Y
SPARE1	VARCHAR2	255	Y
SPARE2	NUMBER	22	Y
OBJ$LABEL	RAW	255	Y
SES$LABEL	RAW	255	Y
PRIV$USED	NUMBER	22	Y
SESSIONCPU	NUMBER	22	Y
NTIMESTAMP#	TIMESTAMP(6)	11	Y
PROXY$SID	NUMBER	22	Y
USER$GUID	VARCHAR2	32	Y
INSTANCE#	NUMBER	22	Y
PROCESS#	VARCHAR2	16	Y
XID	RAW	8	Y
AUDITID	VARCHAR2	64	Y
SCN	NUMBER	22	Y
DBID	NUMBER	22	Y
SQLBIND	CLOB	4000	Y
SQLTEXT	CLOB	4000	Y
OBJ$EDITION	VARCHAR2	30	Y