Oracle Database Details, Logs and more..
-- General Details
SELECT name, db_unique_name, open_mode, switchover_status, current_scn, 
 control_file_type,
 fs_failover_status, 
 fs_failover_observer_host,
 fs_failover_current_target
FROM v$database;

SELECT * FROM v$dataguard_config;

SELECT * FROM v$instance;

-- Number of sessions

SELECT name, value FROM v$parameter
WHERE INSTR(name,'session') > 0;

-- Database Start up time, role - Primary/DR

SELECT host_name, instane_name,
  instance_role,   -- Primary or DR
  TO_CHAR(startup_time,'mm/dd/yyyy hh24:mi:ss') startup_time,
  status
FROM v$instance;

SELECT * FROM v$active_instances;

SELECT property_name, property_value, description
FROM database_properties
ORDER BY property_name;

SELECT * FROM v$license;

SELECT * FROM v$services ORDER BY service_id;

SELECT * FROM v$spparameter;

SELECT * FROM v$class_ping;

SELECT * FROM v$client_stats;

--Key values: file#, rfile#, name, status
SELECT * FROM v$datafile;

--Key values: comp_id, comp_name, version, other_schemas
SELECT * FROM dba_registry;

=> DBA_Registry Output

SELECT status,  sequence#, first_change#,
 TO_CHAR(first_time, 'mm/dd/yyyy hh24:mi:ss') first_time, bytes
FROM v$log
ORDER BY sequence#
;

=> Typical v$log Output

-- Prevention of Oracle error (ORA-00257).  When the available value 
-- reaches zero due to lack of server space, the database archiver
-- fails with ORA-00257 error.

SHOW parameter db_recovery_file_dest;

SELECT  t.*,
 100*(t.space_limit-t.space_used)/t.space_limit available_pct
FROM v$recovery_file_dest t
;

=> Typical v$recovery_file_dest Output


General Database Information

NLS and DB Properties

Logon, cursors, and others

Oracle registered trademark of Oracle Corporation.

Last Revised On: August 17, 2015

  88