Sql*Plus : Como conectarse a una base de datos con problema (opción _prelim de Sql*Plus)



A veces queremos conectarnos a una instancia Oracle, y lo hacemos , como no, con el usuario más poderoso que existe, con SYS como SYSDBA, pero ni eso resulta y simplemente no podemos conectarnos :(

Pues bien , para esos momentos de desdicha, podemos hacer dos cosas

  • O reiniciar la instancia
  • O buscar los porque..
  • Y como somos DBA de peso, queremos saber el por que, cierto?? :yes:



    Pues bien, para realizar esa gran tarea de análisis, ocupamos un comando no documentado de Sql*Plus llamado la conección preliminar, con lo cual no nos conectamos a la instancia Oracle, pero... tenemos acceso a muchas vistas del diccionario de datos :>> , así tal cual , realizamos una conección pero sin conectarnos


    Acá va el ejemplo de ello

    Seteamos la opción oculta llamada "Conexión preliminar"

    [oracle@oracle10g oracle]$ sqlplus /nolog

    SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 26 04:20:56 2010

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

    SQL> set _prelim on


    Posterior a eso, realizamos la conexión a la base de datos, pero esto en realidad no realizará la conexión , de hecho si tratará de hacerlo se quedaría pegada.

    SQL> conn / as sysdba
    Prelim connection established


    Si quisieramos ejecutar alguna consulta , aparecería el siguiente mensaje

    SQL> select file_name from dba_data_files;
    select file_name from dba_data_files
    *
    ERROR at line 1:
    ORA-01012: not logged on

    SQL>


    Y esto es porque en realidad, no hemos hecho la conexión, pero si podemos obtener información estadística, por ejemplo para hacer un trace de la sesión y así poder detectar el error.

    Para realizar el comentado trace, podemos ocupar otro utilitario no documentado :>> parece cuento , este utilitario es el oradebug , para ello ocupamos los siguientes comandos.

    SQL>
    SQL> oradebug setmypid
    Statement processed.
    SQL> oradebug unlimit
    Statement processed.
    SQL> oradebug hanganalyze 3
    Hang Analysis in /u01/app/oracle/admin/orcl/udump/orcl_ora_5417.trc


    Ahora viene la explicación de los comandos utilizados

  • oradebug setmypid : Es un debug para el actual proceso que ejecuto el comando
  • oradebug hanganalyze 3 : Este comando sirve para generar un trace por HANG de la base de datos, de acuerdo al nivel que se le ha otorgado
  • Este nivel puede ser :

  • 1 y 2 : Sólo un análisis de HANG
  • 3 : Nivel 2 más los procesos con HANG
  • 4 : Nivel 3 más un vacíado de información de los bloqueadores
  • 5 : Nivel 4 más un vacíado de todos los procesos involucrados en el HANG
  • 10 : Un volcado de todos los procesos existentes (muy costoso)
  • Y claro , ahora viene la revisión del archivo de trace generado , la visualización del problema y el reinicio de la instancia Oracle ;)

    El tamaño del archivo de trace esta dado por el parámetro MAX_DUMP_FILE_SIZE , pero si queremos un tamaño mayor , ejecutamos el comando

    SQL> oradebug unlimit

    Antes de hacer el debug del trace

    Espero les sirva

    by Ligarius
    27.08.09. 18:19:56. 480 words, 30038 views. Categories: Base de datos, Tuning / Performance, Sql*Plus ,

    Oracle 11g : Analizando segmentos con DBVerify



    El famoso DBV (Database Verify) en Oracle11g sufrio una pequeña modificación, que de verdad se agradece mucho.

    No solamente es capaz de realizar análisis a datafiles de la base de datos, sino también puede realizar análisis de segmentos , esto proporciona una nueva herramienta cuando nos encontramos con cosas medias extrañas en nuestras consultas

    El como se utiliza



    Para poder ejecutar DBV sobre un segmento , necesitamos cierta información, toda proveniente desde la tabla SYS_USER_SEGS, para poder realizar la consulta debemos ser usuarios SYSDBA.

    dbv userid="pruebadbv/oracle" segment_id=0.1.88289 logfile=salida_dbv feedback=100


    El campo feedback nos provee un puntito por cada X valor que le hayamos dicho, esto esta expresado en cantidad de páginas procesadas por un puntito :)

    Segment_id = Está asociado a la información que necesitamos desde la SYS_USER_SEGS, esta información esta conformada por TABLESPACE_ID , HEADER_FILE y HEADER_BLOCK

    Un ejemplo de consulta para obetner toda está información.

    SQL> select tablespace_id , header_file , header_block from sys_user_segs where segment_name like 'TABLA_VALIDACIONES';

    TABLESPACE_ID HEADER_FILE HEADER_BLOCK
    ------------- ----------- ------------
    0 1 88289


    Este dato (0.1.88289) es el que utilizamos en nuestro dbv sobre el segmento

    Un ejemplo de salida del dbv en Oracle11g

    DBVERIFY - Verification starting : SEGMENT_ID = 0.1.88289

    DBVERIFY - Verification complete

    Total Pages Examined : 1025
    Total Pages Processed (Data) : 1016
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 0
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 0
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 9
    Total Pages Marked Corrupt : 0
    Total Pages Influx : 0
    Total Pages Encrypted : 0
    Highest block SCN : 1098130 (0.1098130)


    Información del DBV en Oracle11g y en Oracle10g

    Espero les sirva

    by Ligarius
    31.08.09. 07:23:48. 286 words, 11298 views. Categories: Oracle 11g, Tuning / Performance ,

    Oracle11g : Paso a paso como utilizar el comando DUPLICATE ACTIVE DATABASE de RMAN



    Esta es la situación :

    JEFE : Debes generar una copia de la base de datos productiva que esta en Oracle11g desde la máquina A a la máquina B
    DBA : Ok, sencillo , cuando comenzamos
    JEFE : Ahora, la base de datos sólo pesa 4TB
    DBA : Mmmm , que grande... bueno, donde dejamos el respaldo, en la máquina A o en la B?
    JEFE : En ninguna
    DBA : 0.o!!! de que me habla?
    JEFE : En ninguna máquina, pues no hay espacio
    DBA : ¿Nada de espacio?
    JEFE : Nada.... sólo 1GB
    DBA : Mmm , bueno, hacemos begin backup de los datafiles y los trasladamos de a poco
    JEFE : Los datafiles, pesan mínimo 20GB
    DBA : ¿Dónde firmo mi carta de renuncia?

    Para que no suceda lo anterior, simplemente utilizamos un comando genial de Oracle11g, llamado el DUPLICATE ACTIVE DATABASE, que permite realizar la copia de una base de datos y generarla en la máquina que deseemos, sin necesidad de pasar por un respaldo.

    Esto lo hace extremadamente poderoso y rápido.

    A continuación los pasos.




    1.- Se debe generar un listener, el cual contenga las entradas de la instancia nueva y claro, el origen de donde sacaremos los datos.

    LISTER11G =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11g.inmotion.cl)(PORT = 1521))
    )

    SID_LIST_LISTER11G =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    (SID_NAME = orcl)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = copia)
    (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    (SID_NAME = copia)
    )
    )

    2.- Al momento de levantar el listener, debe estar proporcionando disponibilidad a ambos servicios

    [oracle@oracle11g admin]$ lsnrctl start LISTER11G

    LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 21-AUG-2009 12:00:03

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

    Starting /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 11.1.0.6.0 - Production
    System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/oracle11g/lister11g/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g.inmotion.cl)(PORT=1521)))

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle11g.inmotion.cl)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTER11G
    Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
    Start Date 21-AUG-2009 12:00:03
    Uptime 0 days 0 hr. 0 min. 0 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/oracle11g/lister11g/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g.inmotion.cl)(PORT=1521)))
    Services Summary...
    Service "copia" has 1 instance(s).
    Instance "copia", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
    Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    [oracle@oracle11g admin]$

    3.- Se debe añadir la siguiente entrada al archivo tnsnames.ora

    copia =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11g.inmotion.cl)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = copia)

    )
    )

    4.- Necesitamos generar un archivo de inicialización pfile, el cual contenga sólo un parámetro, el db_name, el resto de los parámetros serán seteados con el DUPLICATE ACTIVE DATABASE

    [oracle@oracle11g dbs]$ ls -ltr initcopia.ora
    -rw-r--r-- 1 oracle oinstall 14 Aug 21 12:02 initcopia.ora
    [oracle@oracle11g dbs]$ more initcopia.ora
    db_name=copia

    5. -Se debe generar un archivo de password, dado que el DUPLICATE ACTIVE DATABASE se conecta mediante SYSDBA a la instancia remota. Como observación , la password debe ser exactamente la misma , entre la instancia de origen y la de destino.

    [oracle@oracle11g dbs]$ orapwd file=orapwcopia password=oracle entries=5
    [oracle@oracle11g dbs]$ ls -ltr *copia*
    -rw-r--r-- 1 oracle oinstall 14 Aug 21 12:02 initcopia.ora
    -rw-r----- 1 oracle oinstall 2048 Aug 21 12:06 orapwcopia
    [oracle@oracle11g dbs]$

    6.- Se debe iniciar la instancia auxiliar en estado NOMOUNT , en esta instancia es donde quedarán los datos de la primaria

    [oracle@oracle11g dbs]$ export ORACLE_SID=copia
    [oracle@oracle11g dbs]$ sqlplus /nolog

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 21 12:07:52 2009

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

    SQL> conn / as sysdba
    Connected to an idle instance.
    SQL> startup nomount
    ORACLE instance started.

    Total System Global Area 146472960 bytes
    Fixed Size 1298472 bytes
    Variable Size 92278744 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 2564096 bytes
    SQL>

    7.- Nos conectamos a RMAN , con la instancia primaria , la base de datos debe estar abierta

    [oracle@oracle11g dbs]$ rman target sys/oracle@orcl

    Recovery Manager: Release 11.1.0.6.0 - Production on Fri Aug 21 12:09:22 2009

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

    connected to target database: ORCL (DBID=1218194307)

    RMAN>

    8.- Nos conectamos a través de RMAN a la instancia auxiliar

    RMAN> connect auxiliary sys/oracle@copia

    connected to auxiliary database: COPIA (not mounted)

    RMAN>

    Se debe recordar que esta entrada (@copia) fue creada en el punto 3

    9.- Se deben generar todos los directorios nuevos que vamos a utilizar en nuestra nueva instancia

    10.- Ahora podemos ejecutar nuestro comando DUPLICATE DATABASE mediante RMAN , de la siguiente forma

    run {
    set newname for datafile '/u01/app/oracle/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata/copia1/users01.dbf';
    set newname for datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' to '/u01/app/oracle/oradata/copia2/undotbs01.dbf';
    set newname for datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/copia3/sysaux01.dbf';
    set newname for datafile '/u01/app/oracle/oradata/orcl/system01.dbf' to '/u01/app/oracle/oradata/copia4/system01.dbf';

    duplicate target database to copia
    from active database
    db_file_name_convert '/u01/app/oracle/oradata/orcl' , '/u01/app/oracle/oradata/copia'
    spfile parameter_value_convert = '/u01/app/oracle/admin/orcl' , '/u01/app/oracle/admin/copia'
    set log_file_name_convert = '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/copia'
    set audit_file_dest='/u01/app/oracle/admin/copia/adump'
    set log_archive_dest_1=''
    set memory_target='183001600'
    set dispatchers='(PROTOCOL=TCP) (SERVICE=copiaXDB)'
    set control_files='/u01/app/oracle/oradata/copia1/control01.ctl','/u01/app/oracle/oradata/copia2/control02.ctl','/u01/app/oracle/oradata/copia3/control03.ctl'
    set db_recovery_file_dest_size = '2294967296';
    }

    11.- Mediante el anterior script, se comienza a generar la copia de la base de datos.

    executing command: SET NEWNAME
    using target database control file instead of recovery catalog

    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME

    Starting Duplicate Db at 22-AUG-09
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=97 device type=DISK

    contents of Memory Script:
    {
    backup as copy reuse
    file '/u01/app/oracle/product/11.1.0/db_1/dbs/spfileorcl.ora' auxiliary format '/u01/app/oracle/product/11.1.0/db_1/dbs/spfilecopia.ora' ;
    sql clone "alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilecopia.ora''";
    }
    executing Memory Script

    Starting backup at 22-AUG-09
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=154 device type=DISK
    Finished backup at 22-AUG-09

    sql statement: alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilecopia.ora''

    contents of Memory Script:
    {
    sql clone "alter system set db_name = ''COPIA'' comment= ''duplicate'' scope=spfile";
    sql clone "alter system set log_file_name_convert = ''/u01/app/oracle/oradata/orcl'', ''/u01/app/oracle/oradata/copia'' comment= '''' scope=spfile";
    sql clone "alter system set audit_file_dest = ''/u01/app/oracle/admin/copia/adump'' comment= '''' scope=spfile";
    sql clone "alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile";
    sql clone "alter system set memory_target = 183001600 comment= '''' scope=spfile";
    sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=copiaXDB)'' comment= '''' scope=spfile";
    sql clone "alter system set control_files = ''/u01/app/oracle/oradata/copia1/control01.ctl'', ''/u01/app/oracle/oradata/copia2/control02.ctl'', ''/u01/app/oracle/oradata/copia3/control03.ctl'' comment= '''' scope=spfile";
    sql clone "alter system set db_recovery_file_dest_size = 2294967296 comment= '''' scope=spfile";
    shutdown clone immediate;
    startup clone nomount ;
    }
    executing Memory Script

    sql statement: alter system set db_name = ''COPIA'' comment= ''duplicate'' scope=spfile
    sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/orcl'', ''/u01/app/oracle/oradata/copia'' comment= '''' scope=spfile
    sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/copia/adump'' comment= '''' scope=spfile
    sql statement: alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile
    sql statement: alter system set memory_target = 183001600 comment= '''' scope=spfile
    sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=copiaXDB)'' comment= '''' scope=spfile
    sql statement: alter system set control_files = ''/u01/app/oracle/oradata/copia1/control01.ctl'', ''/u01/app/oracle/oradata/copia2/control02.ctl'', ''/u01/app/oracle/oradata/copia3/control03.ctl'' comment= '''' scope=spfile
    sql statement: alter system set db_recovery_file_dest_size = 2294967296 comment= '''' scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)
    Oracle instance started

    Total System Global Area 184127488 bytes

    Fixed Size 1298752 bytes
    Variable Size 142610112 bytes
    Database Buffers 37748736 bytes
    Redo Buffers 2469888 bytes

    contents of Memory Script:
    {
    set newname for datafile 1 to "/u01/app/oracle/oradata/copia4/system01.dbf";
    set newname for datafile 2 to "/u01/app/oracle/oradata/copia3/sysaux01.dbf";
    set newname for datafile 3 to "/u01/app/oracle/oradata/copia2/undotbs01.dbf";
    set newname for datafile 4 to "/u01/app/oracle/oradata/copia1/users01.dbf";
    backup as copy reuse
    datafile 1 auxiliary format
    "/u01/app/oracle/oradata/copia4/system01.dbf" datafile auxiliary format
    "/u01/app/oracle/oradata/copia3/sysaux01.dbf" datafile auxiliary format
    "/u01/app/oracle/oradata/copia2/undotbs01.dbf" datafile auxiliary format
    "/u01/app/oracle/oradata/copia1/users01.dbf" ;
    sql 'alter system archive log current';
    }
    executing Memory Script
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME

    Starting backup at 22-AUG-09
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
    output file name=/u01/app/oracle/oradata/copia4/system01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:07
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
    output file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
    output file name=/u01/app/oracle/oradata/copia1/users01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
    output file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf tag=TAG20090822T205622 RECID=0 STAMP=0
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
    Finished backup at 22-AUG-09

    sql statement: alter system archive log current
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "COPIA" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 ( '/u01/app/oracle/oradata/copia/redo01.log' ) SIZE 50 M REUSE,
    GROUP 2 ( '/u01/app/oracle/oradata/copia/redo02.log' ) SIZE 50 M REUSE,
    GROUP 3 ( '/u01/app/oracle/oradata/copia/redo03.log' ) SIZE 50 M REUSE
    DATAFILE
    '/u01/app/oracle/oradata/copia4/system01.dbf'
    CHARACTER SET WE8MSWIN1252

    contents of Memory Script:
    {
    backup as copy reuse
    archivelog like "/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_25_690413581.dbf" auxiliary format
    "/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_%u_.arc" archivelog like
    "/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_08_22/o1_mf_1_25_59154osg_.arc" auxiliary format
    "/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_%u_.arc" ;
    catalog clone recovery area;
    switch clone datafile all;
    }
    executing Memory Script

    Starting backup at 22-AUG-09
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=25 RECID=19 STAMP=695595606
    output file name=/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0lknbsj5_.arc RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=25 RECID=20 STAMP=695595606
    output file name=/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
    Finished backup at 22-AUG-09

    searching for all files in the recovery area

    List of Files Unknown to the Database
    =====================================
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0lknbsj5_.arc
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0fkn9m61_.arc
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0gkn9m6i_.arc
    cataloging files...
    cataloging done

    List of Cataloged Files
    =======================
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0lknbsj5_.arc
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0fkn9m61_.arc
    File Name: /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_23_0gkn9m6i_.arc

    datafile 4 switched to datafile copy
    input datafile copy RECID=1 STAMP=695595667 file name=/u01/app/oracle/oradata/copia1/users01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=695595668 file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=3 STAMP=695595668 file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf

    contents of Memory Script:
    {
    set until scn 1066933;
    recover clone database delete archivelog ;
    }
    executing Memory Script

    executing command: SET until clause

    Starting recover at 22-AUG-09
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=153 device type=DISK

    starting media recovery

    archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc
    archived log file name=/u01/app/oracle/flash_recovery_area/COPIA/archivelog/2009_08_22/o1_mf_1_25_0mknbsjs_.arc thread=1 sequence=25
    media recovery complete, elapsed time: 00:00:02
    Finished recover at 22-AUG-09

    contents of Memory Script:
    {
    shutdown clone immediate;
    startup clone nomount ;
    }
    executing Memory Script

    database dismounted
    Oracle instance shut down

    connected to auxiliary database (not started)
    Oracle instance started

    Total System Global Area 184127488 bytes

    Fixed Size 1298752 bytes
    Variable Size 146804416 bytes
    Database Buffers 33554432 bytes
    Redo Buffers 2469888 bytes
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "COPIA" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 ( '/u01/app/oracle/oradata/copia/redo01.log' ) SIZE 50 M REUSE,
    GROUP 2 ( '/u01/app/oracle/oradata/copia/redo02.log' ) SIZE 50 M REUSE,
    GROUP 3 ( '/u01/app/oracle/oradata/copia/redo03.log' ) SIZE 50 M REUSE
    DATAFILE
    '/u01/app/oracle/oradata/copia4/system01.dbf'
    CHARACTER SET WE8MSWIN1252

    contents of Memory Script:
    {
    set newname for tempfile 1 to "/u01/app/oracle/oradata/copia/temp01.dbf";
    switch clone tempfile all;
    catalog clone datafilecopy "/u01/app/oracle/oradata/copia3/sysaux01.dbf";
    catalog clone datafilecopy "/u01/app/oracle/oradata/copia2/undotbs01.dbf";
    catalog clone datafilecopy "/u01/app/oracle/oradata/copia1/users01.dbf";
    switch clone datafile all;
    }
    executing Memory Script

    executing command: SET NEWNAME

    renamed tempfile 1 to /u01/app/oracle/oradata/copia/temp01.dbf in control file

    cataloged datafile copy
    datafile copy file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf RECID=1 STAMP=695595722

    cataloged datafile copy
    datafile copy file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf RECID=2 STAMP=695595723

    cataloged datafile copy
    datafile copy file name=/u01/app/oracle/oradata/copia1/users01.dbf RECID=3 STAMP=695595724

    datafile 4 switched to datafile copy
    input datafile copy RECID=3 STAMP=695595724 file name=/u01/app/oracle/oradata/copia1/users01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=695595723 file name=/u01/app/oracle/oradata/copia2/undotbs01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=1 STAMP=695595722 file name=/u01/app/oracle/oradata/copia3/sysaux01.dbf

    contents of Memory Script:
    {
    Alter clone database open resetlogs;
    }
    executing Memory Script

    database opened
    Finished Duplicate Db at 22-AUG-09

    RMAN>

    Documentación sobre DUPLICATE ACTIVE DATABASE

    by Ligarius
    24.08.09. 11:31:00. 2640 words, 27248 views. Categories: Oracle 11g ,

    Monitorear o no monitorear , he allí el problema



    Antes nos decían que debiamos monitorear la tabla para que quedase registro de las DMLs en la tabla *_TAB_MODIFICATIONS, y claro, eso es real hasta Oracle10gr1 , en Oracle10gr2 cambia un poco el tema.

    Recordar un poco que el monitoring de una tabla, se realizaba con el comando


    ALTER TABLE nombre_tabla MONITORING;

    Y se puede apreciar si está o no monitoreada con el comando

    select owner , table_name , monitoring from dba_tables where table_name like '%NOMBRE_TABLA%';

    Cuando el parámetro STATISTIC_LEVEL está en TYPICAL, el monitoreo de todas las tablas es por defecto, lo que implica que siempre se almacenará su información de DMLs en la tabla *_TAB_MODIFICATIONS (Esto es sólo en Oracle10gr2)

    Ejemplo de la consulta :

    SQL> select owner , table_name , tablespace_name , monitoring from dba_tables where table_name like 'TABLA_FULL_%';

    OWNER TABLE_NAME TABLESPACE_NAME MON
    ------------------------------ ------------------------------ ------------------------------ ---
    SYS TABLA_FULL_7 SYSTEM YES
    SYS TABLA_FULL_6 SYSTEM YES
    SYS TABLA_FULL_5 SYSTEM YES
    SYS TABLA_FULL_4 SYSTEM YES
    SYS TABLA_FULL_3 SYSTEM YES
    SYS TABLA_FULL_2 SYSTEM YES
    SYS TABLA_FULL_1 SYSTEM YES

    Y ejemplo de consulta en la tabla que lleva registro de las DMLs en una tabla

    SQL> select table_name , inserts , updates , deletes from user_tab_modifications where table_name like '%TABLA_FULL_1%'

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 0

    Para llevar a cabo un pequeño ejemplo del registro de las DML , procedemos a borrar una cantidad de registros de una tabla

    SQL>delete tabla_full_6 where rownum < 1000 ;

    Ejecutamos el vaciado desde memoria de la información de DMLs sobre las tablas

    exec dbms_stats.flush_database_monitoring_info;

    Está última instrucción siempre registra las DMLs hacía una tabla, aunque se le haya realizado rollback, saca la información desde memoria y la lleva a tablas físicas, este comando se puede dejar de lado y esperar que SMON vacíe la información cada 15 minutos aproximadamente.

    Ejemplo de salida

    SQL> r
    1 select table_name , inserts , updates , deletes from user_tab_modifications
    2* where table_name like '%TABLA_FULL_1%'

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 1000

    Y quizás la pregunta es válida.. ¿Sirve saber la cantidad de delete a pesar de que haya rollback? , uno podría pensar hay fragmentación , una forma de analizar esto podría ser contando el número de bloques cuando :
    - No se ha realizado operación sobre la tabla
    - Se ha producido un borrado
    - Se ha realizado el rollback

    La verdad , se haga o no commit, siempre se registran los datos sobre la tabla

    Miremos el siguiente ejemplo

    Info original

    SQL> r
    1 select table_name , inserts , updates , deletes from user_tab_modifications where table_name like '%TABLA_FULL_1%'

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 1000

    Procedemos a eliminar registro de la tabla 1 (TABLA_FULL_1) , hacer rollback y vaciar la info desde memoria

    SQL> SQL> delete tabla_full_1 where rownum < 100;

    99 rows deleted.

    SQL> rollback;

    Rollback complete.

    SQL>
    SQL>
    SQL> exec dbms_stats.flush_database_monitoring_info;

    PL/SQL procedure successfully completed.

    SQL>

    Y consultamos nuevamente la tabla de modificaciones

    SQL> select table_name , inserts , updates , deletes from user_tab_modifications where table_name like '%TABLA_FULL_1%'
    2 ;

    TABLE_NAME INSERTS UPDATES DELETES
    ------------------------------ ---------- ---------- ----------
    TABLA_FULL_1 0 0 1099

    Como podemos ver, igual marca los datos a pesar de que haya un rollback

    ¿Y registrará las modificaciones en la *_tab_modifications pues hay un incremento o disminución de bloques?

    Podemos comprobarlo mediante la siguiente consulta

    SQL> col segment_name format a40
    SQL> r
    1* select blocks , bytes/1024/1024 MB , owner , segment_name from dba_segments where segment_name like 'TABLA_FULL_1'

    BLOCKS MB OWNER SEGMENT_NAME
    ---------- ---------- ------------------------------ ----------------------------------------
    768 6 SYS TABLA_FULL_1

    7 rows selected.

    Ahora procedemos a borrar , hacer rollback y realizar la misma consulta

    SQL> delete tabla_full_1;

    101220 rows deleted.

    SQL> SQL> rollback;

    Rollback complete.

    SQL>
    SQL>
    SQL> select blocks , bytes/1024/1024 MB , owner , segment_name from dba_segments where segment_name like 'TABLA_FULL_1';

    BLOCKS MB OWNER SEGMENT_NAME
    ---------- ---------- ------------------------------ ----------------------------------------
    768 6 SYS TABLA_FULL_1

    Y obtenemos exactamente la misma información

    ¿Y si desactivamos el monitoring de la tabla, sigue guardando registro en la *_tab_modification?
    La respuesta es si, ya que desactivar el monitoring, simplemente no se puede

    SQL> select owner , table_name , tablespace_name , monitoring from dba_tables where table_name like 'TABLA_FULL_1';

    OWNER TABLE_NAME TABLESPACE_NAME MON
    ------------------------------ ------------------------------ ------------------------------ ---
    SYS TABLA_FULL_1 SYSTEM YES

    1 rows selected.

    SQL> alter table tabla_full_1 nomonitoring;

    Table altered.

    SQL> select owner , table_name , tablespace_name , monitoring from dba_tables where table_name like 'TABLA_FULL_1';

    OWNER TABLE_NAME TABLESPACE_NAME MON
    ------------------------------ ------------------------------ ------------------------------ ---
    SYS TABLA_FULL_1 SYSTEM YES

    Con el parámetro STATISTICS_LEVEL en BASIC , nada de esto se generá de forma automática. Y tener muy en cuenta que Oracle toma en cuenta los datos de esta tabla para verificar que tablas están con más de un 10% de modificaciones (tablas stale) ante lo cual le toma estadísticas

    Una consulta bastante simple para saber a que tablas se les debiese tomar estadísticas de acuerdo a la cantidad de DMLs ejecutadas , es la siguiente

    col porcentaje format 9999D999

    select u.TIMESTAMP,
    t.last_analyzed,
    u.table_name,
    u.inserts,
    u.updates,
    u.deletes,
    d.num_rows,
    TO_CHAR(((U.inserts+u.deletes+u.updates)/decode(nvl(d.num_rows,0),0,1,d.num_rows)) * 100,'999D99') percent
    from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
    where u.table_name = t.table_name
    and d.table_name = t.table_name
    and d.owner = 'SYS'
    and (u.inserts is not null or u.updates is not null or u.deletes is not null)
    and ((U.inserts+u.deletes+u.updates)/decode(nvl(d.num_rows,0),0,1,d.num_rows)) * 100 > 10
    order by t.last_analyzed , ((U.inserts+u.deletes+u.updates)/decode(nvl(d.num_rows,0),0,1,d.num_rows)) * 100

    Puede tener variaciones y ciertas especulaciones, pero es una orientadora...

    Otro punto a tener en cuenta, es cuando se toman estadísticas, se elimina la información sobre la *_TAB_MODIFICATIONS

    Espero les sea de utilidad

    by Ligarius
    19.08.09. 09:46:32. 1052 words, 7862 views. Categories: Oracle 10g, Tuning / Performance ,

    Tuning SQL : Lo malo de usar UNION en los select :)



    Hace poco acabo de terminar un curso de Tuning de SQL ,el código es el D19165GC20 , en ese curso hay muchos detalles los cuales uno maneja a diario, pero de lo cual en mi caso nunca he escrito.

    He aquí un ejemplo, el porque siempre se debiese ocupar el UNION ALL , en vez del UNION , esto último debido a que el UNION provoca un ordenamiento implicito de los datos dentro de la PGA , a pesar de que como todos sabemos el UNION ALL entrega todas las filas a pesar de que estas se repitan , el UNION sólo entrega aquellas diferentes.

    He aquí el caso del porque es tan caro el simple, común y pequeño UNION ;)

    Para realizar los ejemplos, hemos creado 7 tablas las cuales tienen informacion desde la dba_objects, o sea, cada una de estas tablas posee como 55 mil registros, mas o menos.

    Vaciamos la memoria, de todo bloque para que la prueba sea más acertiva.

    alter system flush shared_pool ;
    alter system flush buffer_cache ;

    Primero utilizamos el simple UNION

    select * from tabla_full_1
    union
    select * from tabla_full_2
    union
    select * from tabla_full_3
    union
    select * from tabla_full_4
    union
    select * from tabla_full_5
    union
    select * from tabla_full_6
    union
    select * from tabla_full_7
    ;

    Cantidad de filas traídas en la consulta

    50610 rows selected.

    Y observamos el plan de ejecución

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3784107405

    --------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 708K| 28M| | 8800 (86)| 00:01:46 |
    | 1 | SORT UNIQUE | | 708K| 28M| 76M| 8800 (86)| 00:01:46 |
    | 2 | UNION-ALL | | | | | | |
    | 3 | TABLE ACCESS FULL| TABLA_FULL_1 | 101K| 4151K| | 151 (3)| 00:00:02 |
    | 4 | TABLE ACCESS FULL| TABLA_FULL_2 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 5 | TABLE ACCESS FULL| TABLA_FULL_3 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 6 | TABLE ACCESS FULL| TABLA_FULL_4 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 7 | TABLE ACCESS FULL| TABLA_FULL_5 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 8 | TABLE ACCESS FULL| TABLA_FULL_6 | 101K| 4151K| | 152 (3)| 00:00:02 |
    | 9 | TABLE ACCESS FULL| TABLA_FULL_7 | 101K| 4151K| | 152 (3)| 00:00:02 |
    --------------------------------------------------------------------------------------------

    Como lo aprecian , esta ordenando 28MB de información lo cual consume 76MB de espacio temporal (o sea , se esta utilizando disco) , lo cual produce I/O muy costoso.

    Statistics
    ----------------------------------------------------------
    1423 recursive calls
    0 db block gets
    4876 consistent gets
    4679 physical reads
    0 redo size
    2262291 bytes sent via SQL*Net to client
    37503 bytes received via SQL*Net from client
    3375 SQL*Net roundtrips to/from client
    22 sorts (memory)
    0 sorts (disk)
    50610 rows processed

    Nuevamente borramos todo vestigio desde memoria.

    alter system flush shared_pool ;
    alter system flush buffer_cache ;

    Y ejecutamos la misma consulta, pero esta vez con UNION ALL

    select * from tabla_full_1
    union all
    select * from tabla_full_2
    union all
    select * from tabla_full_3
    union all
    select * from tabla_full_4
    union all
    select * from tabla_full_5
    union all
    select * from tabla_full_6
    union all
    select * from tabla_full_7
    ;

    Trae efectivamente mas de 700 mil registros, los cuales debiesen ser procesados con un cursor, para así descartar los repetidos, es más barato y más rápido

    708540 rows selected.

    El plan de ejecución de la sentencia con UNION ALL

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2473866688

    -----------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 708K| 28M| 1061 (87)| 00:00:13 |
    | 1 | UNION-ALL | | | | | |
    | 2 | TABLE ACCESS FULL| TABLA_FULL_1 | 101K| 4151K| 151 (3)| 00:00:02 |
    | 3 | TABLE ACCESS FULL| TABLA_FULL_2 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 4 | TABLE ACCESS FULL| TABLA_FULL_3 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 5 | TABLE ACCESS FULL| TABLA_FULL_4 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 6 | TABLE ACCESS FULL| TABLA_FULL_5 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 7 | TABLE ACCESS FULL| TABLA_FULL_6 | 101K| 4151K| 152 (3)| 00:00:02 |
    | 8 | TABLE ACCESS FULL| TABLA_FULL_7 | 101K| 4151K| 152 (3)| 00:00:02 |
    -----------------------------------------------------------------------------------

    Y como se puede apreciar, no hay uso de espacio temporal , sólo se procesan los 28MB de datos.

    Statistics
    ----------------------------------------------------------
    1423 recursive calls
    0 db block gets
    51700 consistent gets
    4679 physical reads
    0 redo size
    27134781 bytes sent via SQL*Net to client
    519985 bytes received via SQL*Net from client
    47237 SQL*Net roundtrips to/from client
    21 sorts (memory)
    0 sorts (disk)
    708540 rows processed

    Para que se tenga en cuenta al momento de realizar un programa

    Espero les sirva

    by Ligarius
    17.08.09. 09:28:17. 626 words, 8513 views. Categories: Oracle 10g, Tuning / Performance ,

    << 1 ... 32 33 34 35 36 37 38 39 40 41 42 ... 44 >>