SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
SELECT current_scn FROM v$database;
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;
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;
Configuración actual:
SHOW SGA;
SELECT * FROM v$sgainfo;
COL COMPONENT FORMAT A30 SELECT * FROM v$sga_dynamic_components;
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' ;
SELECT LOG_MODE FROM SYS.V$DATABASE;
SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;
ARCHIVE LOG LIST
SHOW parameter spfile ;
col VALUE format a200 SELECT name, VALUE FROM v$parameter WHERE name = 'spfile' ;
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';
COL DIRECTORY_NAME FORMAT A40; COL DIRECTORY_PATH FORMAT A180; SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM all_directories ;
SHOW PARAMETER BACKGROUND_DUMP_DEST ;
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
”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
SHOW parameter db_name ;
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
SELECT * FROM v$version ;
COL VALUE FORMAT A50 COL PARAMETER FORMAT A50 SELECT * FROM nls_database_parameters ;
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# ;
SELECT * FROM v$log;
col member format a100 SELECT * FROM v$logfile ;
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 ;
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')
ALTER SYSTEM RESET <PARAMETER_NAME> ;
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# /