User Tools

Site Tools


oracle:oracle_sql_querys:status_and_configuration

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

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> ;
oracle/oracle_sql_querys/status_and_configuration.txt · Last modified: 2019/07/18 09:17 (external edit)