Nuevos parámetros de memoria y volcado de Oracle 11g

Si quieres migrar una base de datos de Oracle 10g a Oracle 11g, en general puedes hacer un volcado de todos los parámetros de la base de datos con una sentencia CREATE PFILE e iniciar tu nueva instancia Oracle 11g con ese pfile, pero sería mejor sustituír algunos parámetros obsoletos por los nuevos parámetros de Oracle 11g; sólo tomaría un par de minutos y así no saldrían mensajes molestos sobre parámetros obsoletos al iniciar la instancia:

SQL> startup nomount pfile=initmydb.ora
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area  267227136 bytes                                       
Fixed Size                  2158832 bytes                                       
Variable Size             134221584 bytes                                       
Database Buffers          125829120 bytes                                       
Redo Buffers                5017600 bytes                                       

Primero que nada, el parámetro remote_os_authent es obsoleto en 11g por lo que puedes sólo quitarlo. Y en Oracle 11g, puedes sustituír los parámetros background_dump_dest, core_dump_dest and user_dump_dest con el nuevo parámetro diagnostic_dest, que por default es derivado del valor de la variable de entorno $ORACLE_BASE. Finalmente, puedes configurar un objetivo de uso de memoria para la instancia completa (PGA and SGA) con el nuevo parámetro memory_target, y también un límite de uso de memoria con el parámetro memory_max_target.

En este ejemplo, los seis parámetros están configurados para esta instancia de Oracle 10g:

SQL> select NAME, VALUE from v$parameter where name in ('pga_aggregate_target','sga_target',
'sga_max_size','user_dump_dest','background_dump_dest','core_dump_dest') order by NAME;

NAME                           VALUE
------------------------------ --------------------------------------------------
background_dump_dest           /oracle_10g/app/oracle/admin/mydb/bdump
core_dump_dest                 /oracle_10g/app/oracle/admin/mydb/cdump
pga_aggregate_target           100663296
sga_max_size                   268435456
sga_target                     268435456
user_dump_dest                 /oracle_10g/app/oracle/admin/mydb/udump

6 rows selected.

En esta instancia 11g, sólo fueron definidos los parámetros diagnostic_dest y memory_target, y los otros parámetros fueron configurados por Oracle y derivados de los dos parámetros anteriores:

SQL> select NAME, VALUE from v$parameter where name in ('pga_aggregate_target','sga_target',
'sga_max_size','memory_target','memory_max_target','user_dump_dest','background_dump_dest',
'diagnostic_dest','core_dump_dest') order by NAME;

NAME                           VALUE
------------------------------ ------------------------------------------------------------
background_dump_dest           /oracle_11g/product/diag/rdbms/mydb/mydb/trace
core_dump_dest                 /oracle_11g/product/diag/rdbms/mydb/mydb/cdump
diagnostic_dest                /oracle_11g/product
memory_max_target              369098752
memory_target                  369098752
pga_aggregate_target           0
sga_max_size                   369098752
sga_target                     0
user_dump_dest                 /oracle_11g/product/diag/rdbms/mydb/mydb/trace

9 rows selected.

Más información:

11g Automatic Diagnostic Repository (ADR)
REMOTE_OS_AUTHENT
SGA_MAX_SIZE
DIAGNOSTIC_DEST

Revisando el set de caracteres de la base de datos

Si quieres exportar una base de datos Oracle para importar los datos en otra base de datos (imp/exp), o quieres crear una base de datos Oracle similar a otra base de datos, entonces tienes que poner atención al set de caracteres de la base de datos y a la variable de ambiente NLS_LANG. Si no lo haces, en caso de importar/exportar podrías terminar con datos diferentes de la base de datos origen, y en el caso de creación de una base de datos tu aplicación podría no almacenar los datos como se espera, y ambos problemas son difíciles de detectar y resolver. Por lo tanto, gasta un minuto revisando el NLS_CHARACTERSET en la base de datos origen y configurando la variable de ambiente NLS_LANG al momento de importar o crear la base de datos, y no tendrás que reexportar o recrear una base de datos por problemas de set de caracteres.

Para revisar el NLS_CHARACTERSET y todos los parámetros de localización de tu base de datos origen, puedes revisar la tabla NLS_DATABASE_PARAMETERS:

SQL> select * from NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                dd-mon-yyyy
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0

20 rows selected.

Teniendo los valores de los parámetros NLS_LANGUAGE, NLS_TERRITORY y NLS_CHARACTERSET puedes configurar la variable de ambiente NLS_LANG, o puedes sólo configurarla en la base de datos destino de la misma manera que la base de datos origen siempre y cuando el valor de la variable NLS_LANG corresponda con los parámetros NLS de la base de datos:

oracle@myserver:~$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Y si estás creando una base de datos, no olvides declarar el set de caracteres apropiadamente:

CREATE DATABASE mydb
...
    CHARACTER SET WE8ISO8859P1
...

Más información:

Oracle Database Globalization Support Guide

Exports comprimidos

Usar RMAN para hacer respaldos de una base de datos Oracle es muy conveniente, pero algunas veces tienes que hacer exports de tu base de datos porque RMAN no es adecuado; por ejemplo, si necesitas copiar o respaldar sólo una tabla, o si no tienes activado el archivado de logs debido a restricciones de espacio y necesitas respaldar tu base de datos, y además no puedes dar de baja tu instancia mientras la respaldas, entonces exp es una buena opción.

Pero por alguna misteriosa razón exp (al menos hasta Oracle 10g) no tiene compresión y no puedes redirigir tu respaldo a la salida estándar por lo que podría ser difícil respaldar bases de datos grandes. Afortunadamente puedes usar tuberías nombradas para resolver esta situación; primero tienes que crear una tubería nombrada para conectar la salida de exp y la entrada de tu compresor:

[oracle]$ mknod mypipe p
[oracle]$ ls -la
total 8
drwxrwx---  2 oracle dba 4096 May 30 16:47 .
drwxrwx---  7 oracle dba 4096 May 30 16:45 ..
prw-r-----  1 oracle dba    0 May 30 16:47 mypipe

Entonces puedes lanzar tu compresor favorito para trabajar en segundo plano y finalmente ejecutar el comando exp, y tendrás un export comprimido al vuelo:

[oracle]$ compress < mypipe > mydb.dmp.Z &
[oracle]$ exp / parfile=myparfile.txt log=mylog.txt file=mypipe

Y recuerda, si quieres poner esto en un script agrega una sentencia wait después del comando exp.

PeopleSoft, Oracle y un DBA sin suerte

Si eres un administrador de bases de datos Oracle, es posible que tengas que manejar bases de datos PeopleSoft. Son bases de datos grandes, complejas, que de pronto se les acaba el espacio temporal o se incrementa varias veces el tiempo de procesamiento usual, volviendo loco al administrador de bases de datos Oracle en el proceso.

En resumen, para soportar otros motores de bases de datos PeopleSoft usa tablas normales como tablas temporales y las etiqueta en la tabla PSRECDEFN como rectype=7, por lo tanto no puedes simplemente recolectar estadísticas de objetos como usualmente se hace debido a que las tablas temporales crecen mucho y se quedan vacías bastante rápido. Primero que nada, tienes que usar el script del apéndice A del PeopleSoft Enterprise Performance on Oracle 10g Database Red Paper, y si lees el documento completo valdrá la pena el tiempo invertido.

Esto es lo que sé de primera mano. El problema es que algunas veces este procedimiento especial de recolectar estadísticas no es suficiente y de todas formas tienes los problemas arriba mencionados, pero un compañero administrador de bases de datos Oracle después de analizar un montón de reportes de AWR, concluyó que ayuda recolectar estadísticas de los siguientes objetos:

DBMS_STATS.GATHER_TABLE_STATS (
   OWNNAME            => 'SYSADM',
   TABNAME            => 'PS_GP_ITER_TRGR',
   estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt         => 'FOR ALL COLUMNS SIZE AUTO',
   DEGREE             => DBMS_STATS.AUTO_DEGREE,
   CASCADE            => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
   OWNNAME            => 'SYSADM',
   TABNAME            => 'PS_GP_RUNCTL',
   estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt         => 'FOR ALL COLUMNS SIZE AUTO',
   DEGREE             => DBMS_STATS.AUTO_DEGREE,
   CASCADE            => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
   OWNNAME            => 'SYSADM',
   TABNAME            => 'PS_GP_CAL_RUN',
   estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt         => 'FOR ALL COLUMNS SIZE AUTO',
   DEGREE             => DBMS_STATS.AUTO_DEGREE,
   CASCADE            => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
   OWNNAME            => 'SYSADM',
   TABNAME            => 'PS_GP_PYE_PRC_STAT',
   estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt         => 'FOR ALL COLUMNS SIZE AUTO',
   DEGREE             => DBMS_STATS.AUTO_DEGREE,
   CASCADE            => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
   OWNNAME            => 'SYSADM',
   TABNAME            => 'PS_GP_HST_WRK',
   estimate_percent   => DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt         => 'FOR ALL COLUMNS SIZE AUTO',
   DEGREE             => DBMS_STATS.AUTO_DEGREE,
   CASCADE            => TRUE);

analyze index SYSADM.PS_GP_PYE_SEG_STAT compute statistics;
analyze index SYSADM.PSAGP_PYE_PRC_STAT compute statistics;
analyze index SYSADM.PS_GP_PYE_PRC_STAT compute statistics;

No estoy seguro por qué este procedimiento resuelve problemas de desempeño y espacio temporal, pero funciona para mi y el administrador de bases de datos Oracle que escribió este procedimiento es muy bueno, por lo que podría funcionar para ti también.

Pusimos este procedimiento como un job diario en una base de datos PeopleSoft y en general trabaja bien, pero aún así hay problemas de vez en cuando con esta base de datos; esto me lleva a pensar que este procedimiento debe ejecutarse antes de programar una tarea de PeopleSoft grande o compleja ya que ejecutando este procedimiento no aparece ningún problema. Lo que quiero decir es que si tienen que correrse dos o más trabajos complejos de PeopleSoft en un día, sería mejor ejecutar este procedimiento antes de correr cada trabajo de PeopleSoft.

Revisando corrupción de bloques de datos

Si crees que una tabla podría tener bloques corruptos entonces puedes revisarla con el paquete DBMS_REPAIR. El procedimiento de revisión es muy simple: primero crea una tabla de reparación (en tu esquema) si no tienes una, después revisa la tabla con el procedimiento DBMS_REPAIR.CHECK_OBJECT, y finalmente revisa la tabla de reparación buscando registros de corrupción de bloques. Opcionalmente, puedes eliminar la tabla de reparación si no la vas a necesitar más.

myserver> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 20 08:29:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/

PL/SQL procedure successfully completed.

SQL> exit;

myserver> nohup sqlplus '/ as sysdba' @check.sql &
[1]     12263522
myserver> Sending nohup output to nohup.out.

myserver> tail -f nohup.out

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 20 08:29:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

number corrupt: 0

PL/SQL procedure successfully completed.

SQL> exit;

myserver> cat check.sql
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
     SCHEMA_NAME => 'MYSCHEMA',
     OBJECT_NAME => 'MYTABLE',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
exit;

Como puedes ver, de acuerdo con el mensaje del procedimiento no hay bloques corruptos (number corrupt: 0), y si creaste la tabla de reparación justo antes de correr el procedimiento DBMS_REPAIR.CHECK_OBJECT entonces estará vacía también. Quizás notaste que decidí crear un script SQL (check.sql) y correrlo con nohup; si tienes una tabla muy grande y una conexión no muy buena al servidor de bases de datos, podría ser buena idea ejecutar el procedimiento con el comando nohup.

myserver> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 20 08:29:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from REPAIR_TABLE;

no rows selected

SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.drop_action);
END;
/
 
PL/SQL procedure successfully completed.

SQL> exit;

Y si no tienes suerte y tienes una tabla con bloques corruptos, asegúrate de revisar la documentación de Oracle y entender qué significa reparar una tabla con corrupción de bloques.

Uso de índices en Oracle

Si quieres obtener estadísticas básicas de uso de índices usados recientemente en tu instancia de Oracle, puedes usar esta sentencia SQL:

SQL> select p.object_owner, p.object_name, sum(t.disk_reads_total) as disk_reads_sum,
sum(t.rows_processed_total) as rows_processed_sum from dba_hist_sql_plan p, dba_hist_sqlstat t
where p.sql_id = t.sql_id and p.object_type like '%INDEX%' and p.object_owner not in
('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','TSMSYS')
group by p.object_owner, p.object_name order by p.object_owner, 4 desc;

OBJECT_OWNER         OBJECT_NAME                     DISK_READS_SUM ROWS_PROCESSED_SUM
-------------------- ------------------------------- -------------- ------------------
MYSCHEMA01           IDX_MYTABLE01                           620641               6209
MYSCHEMA01           IDX_MYTABLE02                           569879               4965
MYSCHEMA01           IDX_MYTABLE03_PK                            20               4793
MYSCHEMA01           IDX_MYTABLE04_PK                            20               4793
MYSCHEMA02           IDX_MYTABLE05                           414609           19940082
MYSCHEMA02           IDX_MYTABLE06                           559721            3776187
MYSCHEMA02           IDX_MYTABLE07                          1165298             621298
MYSCHEMA02           IDX_MYTABLE08_PK                           795             107678
MYSCHEMA02           IDX_MYTABLE09_PK                        174079              78627

Alertas sin atender de Oracle

Desde Oracle 10g puedes configurar alertas para eventos de bases de datos como tamaño del tablespace (y muchas mas), y por default la instancia emite alertas sobre tamaño del tablespace, por lo cual puedes revisar todas las alertas y también corrupción de bloques de la base de datos con estas sentencias:

SQL> column REASON           format A35
SQL> column SUGGESTED_ACTION format A35
SQL> column TIME_SUGGESTED   format A35

SQL> select REASON, SUGGESTED_ACTION, TIME_SUGGESTED from dba_alert_history union
select REASON, SUGGESTED_ACTION, TIME_SUGGESTED from dba_outstanding_alerts
order by TIME_SUGGESTED;

REASON                              SUGGESTED_ACTION                    TIME_SUGGESTED
----------------------------------- ----------------------------------- -----------------------------------
Tablespace [UNDO] is [76 percent] f Add space to the tablespace         07-MAY-11 01.47.40.716220 AM -05:00
           ull

Tablespace [UNDO] is [73 percent] f Add space to the tablespace         07-MAY-11 03.07.46.785262 AM -05:00
           ull

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

no rows selected

No es necesario decirlo, pero si hay datos en la vista V$DATABASE_BLOCK_CORRUPTION podrías tener serios problemas con tu base de datos.