Differences

This shows you the differences between two versions of the page.

--- dba:oracle:oracle_sql_querys:status_and_configuration [2022/02/11 11:36] – external edit 127.0.0.1
+++ dba:oracle:oracle_sql_querys:status_and_configuration [2023/01/31 08:15] (current) – removed dodger
@@ Line 1: / Line 1: @@
-====== ORACLE SQL QUERIES : Status & Config ======
-======SECCIÓN : STATUS======
-===== Hora de la bbdd =====
-<code sql>SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;</code>
-===== SCN =====
-==== get scn ====
-<code sql>
-select current_scn from v$database;
-</code>
-==== convert de/a timestamp ====
-<code sql>
-select timestamp_to_scn(to_timestamp('24/09/2012 14:24:54','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
-</code>
-<code sql>
-select scn_to_timestamp(7705798324) as timestamp from dual;
-</code>
-=====Status genérico=====
-<code sql>
-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;
-</code>
-<code sql>
-col instnace format a35
-select instance, status, enabled, open_time, checkpoint_time from v$thread;
-</code>
-=====Memoria=====
-====SGA====
-Configuración actual:
-<code sql>SHOW SGA;</code>
-<code sql>select * from v$sgainfo;</code>
-<code sql>
-COL COMPONENT FORMAT A30
-select * from v$sga_dynamic_components;</code>
-====PGA====
-<code sql>
-show parameter PGA;
-</code>
-or
-<code sql>
-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'
-;
-</code>
-=====Archivelog Mode=====
-<code sql>
-SELECT LOG_MODE FROM SYS.V$DATABASE;
-</code>
-<code sql>
-SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;
-</code>
-<code sql>
-ARCHIVE LOG LIST
-</code>
-=====Ficheros/Directorios=====
-====Localizar spfile====
-<code sql>show parameter spfile ;</code>
-<code sql>
-col value format a200
-select name, value from v$parameter where name = 'spfile' ;
-</code>
-====Listar controlfiles====
-<code sql>
-COL NAME FORMAT A200;
-select NAME from V$CONTROLFILE ;
-</code>
-o en RAC:
-<code sql>
-COL NAME FORMAT A200;
-select NAME from GV$CONTROLFILE ;
-</code>
-Y también:
-<code sql>
-COL VALUE FORMAT A200;
-SELECT value
-FROM gv$parameter
-WHERE name = 'control_files';
-</code>
-====Directorios====
-=== Listar ===
-<code sql>
-COL DIRECTORY_NAME FORMAT A40;
-COL DIRECTORY_PATH FORMAT A180;
-select DIRECTORY_NAME, DIRECTORY_PATH FROM all_directories ;
-</code>
-=====Hubicación de trazas y alert=====
-<code sql>SHOW PARAMETER BACKGROUND_DUMP_DEST ; </code>
-=====Debugging=====
-====ASM alert y trazas====
-No he conseguido query para sacarlo, solo mediante variables de sistema:
-<code sql>$ORACLE_BASE/diag/$ASMDB/$DB1/$ORACLE_SID/trace/</code>
-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:
-<code sql>
-oradebug setospid 3885
-oradebug unlimit
-oradebug event 10046 trace name context forever,level 12
-</code>
-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=====
-<code sql>show parameter db_name ;</code>
-=====DBID de la bd=====
-<code sql>SELECT DBID FROM V$DATABASE;</code>
-En caso de que la bbdd esté down, podemos verlo en los logs de RMAN
-<code sql>
-$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)
-</code>
-Mas info: [[http://arjudba.blogspot.com/2008/05/how-to-discover-find-dbid.html]]
-=====Versión=====
-<code sql> select   * from v$version ;</code>
-==== charset ====
-<code sql>
-COL VALUE FORMAT A50
-COL PARAMETER FORMAT A50
-select * from nls_database_parameters ;
-</code>
-===== REDO's =====
-==== Status de los REDO (all in one query) ====
-<code sql>
-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#
-;
-</code>
-==== Status de los REDO ====
-<code sql>
-select * from v$log;
-</code>
-==== Localización de los REDO ====
-<code sql>
-col member format a100
-select * from v$logfile ;
-</code>
-==== Destino de los redos ====
-Dónde se envían los redos:
-<code sql>
-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 ;
-</code>
-=====Parametros=====
-Por regla general:
-  * <code sql>show parameter <param_name></code> in sql*plus
-  * <code sql>select value from v$parameter where name = lower('param_name') </code>
-  * <code sql>select value from gv$parameter where name = lower('param_name') </code>
-=====Resetear un parámetro=====
-<code sql>
-ALTER SYSTEM RESET <PARAMETER_NAME> ;
-</code>
-===== Standby redo logs =====
-This query  will show ''ORL'' for //ONLINE redo logs// and ''SRL'' for //STANDBY redo logs//
-<code sql>
-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#
-/
-</code>