dba:oracle:oracle_sql_querys:status_and_configuration
This is an old revision of the document!
Table of Contents
ORACLE SQL QUERIES : Status & Config
SECCIÓN : STATUS
Hora de la bbdd
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
SCN
get scn
SELECT current_scn FROM v$database;
convert de/a timestamp
SELECT timestamp_to_scn(to_timestamp('24/09/2012 14:24:54','DD/MM/YYYY HH24:MI:SS')) AS scn FROM dual;
SELECT scn_to_timestamp(7705798324) AS TIMESTAMP FROM dual;
Status genérico
SET LINES 110 col strtd hea 'STARTED' col instance_name FOR a8 hea 'INSTANCE' col host_name FOR a15 hea 'HOSTNAME' col version FOR a10 SELECT instance_name, version, host_name, STATUS, database_status, to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd FROM v$instance;
col instnace format a35 SELECT instance, STATUS, enabled, open_time, checkpoint_time FROM v$thread;
Memoria
SGA
Configuración actual:
SHOW SGA;
SELECT * FROM v$sgainfo;
COL COMPONENT FORMAT A30 SELECT * FROM v$sga_dynamic_components;
PGA
SHOW parameter PGA;
or
SET pages 999; COLUMN pga_size format 999,999,999 SELECT 1048576+a.value+b.value pga_size FROM v$parameter a, v$parameter b WHERE a.name = 'sort_area_size' AND b.name = 'hash_area_size' ;
Archivelog Mode
SELECT LOG_MODE FROM SYS.V$DATABASE;
SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;
ARCHIVE LOG LIST
Ficheros/Directorios
Localizar spfile
SHOW parameter spfile ;
col VALUE format a200 SELECT name, VALUE FROM v$parameter WHERE name = 'spfile' ;
Listar controlfiles
COL NAME FORMAT A200; SELECT NAME FROM V$CONTROLFILE ;
o en RAC:
COL NAME FORMAT A200; SELECT NAME FROM GV$CONTROLFILE ;
Y también:
COL VALUE FORMAT A200; SELECT VALUE FROM gv$parameter WHERE name = 'control_files';
Directorios
Listar
COL DIRECTORY_NAME FORMAT A40; COL DIRECTORY_PATH FORMAT A180; SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM all_directories ;
Hubicación de trazas y alert
SHOW PARAMETER BACKGROUND_DUMP_DEST ;
Debugging
ASM alert y trazas
No he conseguido query para sacarlo, solo mediante variables de sistema:
$ORACLE_BASE/diag/$ASMDB/$DB1/$ORACLE_SID/trace/
Hay que cargar las variables de +ASM
, por supuesto:
$ASMDB
: Normalmente se corresponde con:tolower(“+ASM”) | sed 's,+,,g
'$DB1
: Normalmente se corresponde con:tolower(“+ASM”)
$ORACLE_SID
: Normalmente se corresponde con “+ASM
”
Debug de otra sesión
Se puede debugar otra sesión a la nuestra mediante:
oradebug setospid 3885 oradebug unlimit oradebug event 10046 trace name context forever,level 12
El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro
SECCIÓN : Configuración
Nombre de la bd
SHOW parameter db_name ;
DBID de la bd
SELECT DBID FROM V$DATABASE;
En caso de que la bbdd esté down, podemos verlo en los logs de RMAN
$rman TARGET / Recovery Manager: Release 10.2.0.1.0 - Production ON Tue May 6 01:25:48 2008 Copyright (c) 1982, 2005, Oracle. ALL rights reserved. connected TO target DATABASE: ARJU (DBID=2869417476)
Mas info: http://arjudba.blogspot.com/2008/05/how-to-discover-find-dbid.html
Versión
SELECT * FROM v$version ;
charset
COL VALUE FORMAT A50 COL PARAMETER FORMAT A50 SELECT * FROM nls_database_parameters ;
REDO's
Status de los REDO (all in one query)
col members format a150 col STATUS format a20 SELECT vl.group#, vl.thread#, vl.sequence#, vl.bytes/1024/1024 SIZE_MB, vl.archived, vl.status, gm.type STATUS, gm.members FROM v$log vl, (SELECT GROUP#, TYPE, listagg(vlf.member, ' ; ') WITHIN GROUP (ORDER BY member) AS members FROM v$logfile vlf GROUP BY vlf.group#, TYPE) gm WHERE vl.group#=gm.group# ;
Status de los REDO
SELECT * FROM v$log;
Localización de los REDO
col member format a100 SELECT * FROM v$logfile ;
Destino de los redos
Dónde se envían los redos:
col DEST_NAME FORMAT A20; COL STATUS FORMAT A15; COL NAME_SPACE FORMAT A15; COL SCHEDULE FORMAT A15; COL DESTINATION FORMAT A30; SELECT DEST_NAME, STATUS, NAME_SPACE, SCHEDULE, DESTINATION FROM V$ARCHIVE_DEST WHERE ROWNUM < 10 ;
Parametros
Por regla general:
SHOW parameter <param_name>
in sql*plus
SELECT VALUE FROM v$parameter WHERE name = LOWER('param_name')
SELECT VALUE FROM gv$parameter WHERE name = LOWER('param_name')
Resetear un parámetro
ALTER SYSTEM RESET <PARAMETER_NAME> ;
Standby redo logs
This query will show ORL
for ONLINE redo logs and SRL
for STANDBY redo logs
COL member format 150 SELECT lf.group#,l_type.log_type AS TYPE, lf.member FROM v$logfile lf JOIN ( SELECT GROUP#,'ORL' AS log_type FROM v$log UNION SELECT GROUP#,'SRL' AS log_type FROM v$standby_log) l_type ON lf.group#=l_type.group# ORDER BY lf.group# /
dba/oracle/oracle_sql_querys/status_and_configuration.1644579407.txt.gz · Last modified: 2022/02/11 11:36 by 127.0.0.1