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#
/