Archives for: August 2009

August 2009
Mon	Tue	Wed	Thu	Fri	Sat	Sun
<< <	Current	> >>
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

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

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, 13291 views. Categories: Base de datos, Tuning / Performance, Sql*Plus , 1 comment » • Send a trackback »

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, 7710 views. Categories: Oracle 11g, Tuning / Performance , Leave a comment » • Send a trackback »

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=copiaXD'
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=copiaXD'' 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=copiaXD'' 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, 18593 views. Categories: Oracle 11g , 4 comments » • Send a trackback »

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, 3980 views. Categories: Oracle 10g, Tuning / Performance , Leave a comment » • Send a trackback »

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, 4021 views. Categories: Oracle 10g, Tuning / Performance , Leave a comment » • Send a trackback »

1 2 >>

Oracle y yo

Categories

Oracle y yo

Search

XML Feeds