-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2015 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : rac_instances.sql | -- | CLASS : Real Application Clusters | -- | PURPOSE : Provide a summary report of all configured instances for the | -- | current clustered database. | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Oracle RAC Instances | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN instance_name FORMAT a13 HEAD 'Instance|Name / Number' COLUMN thread# FORMAT 99999999 HEAD 'Thread #' COLUMN host_name FORMAT a28 HEAD 'Host|Name' COLUMN status FORMAT a6 HEAD 'Status' COLUMN startup_time FORMAT a20 HEAD 'Startup|Time' COLUMN database_status FORMAT a8 HEAD 'Database|Status' COLUMN archiver FORMAT a8 HEAD 'Archiver' COLUMN logins FORMAT a10 HEAD 'Logins?' COLUMN shutdown_pending FORMAT a8 HEAD 'Shutdown|Pending?' COLUMN active_state FORMAT a6 HEAD 'Active|State' COLUMN version HEAD 'Version' SELECT instance_name || ' (' || instance_number || ')' instance_name , thread# , host_name , status , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time , database_status , archiver , logins , shutdown_pending , active_state , version FROM gv$instance ORDER BY instance_number; ### SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : RMAN Registered Databases | PROMPT | Instance : ¤t_instance | PROMPT | Note : Listing of all databases in the RMAN recovery catalog. | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN db_key FORMAT 999999 HEADING 'DB|Key' COLUMN dbinc_key FORMAT 999999 HEADING 'DB Inc|Key' COLUMN dbid HEADING 'DBID' COLUMN name FORMAT a12 HEADING 'Database|Name' COLUMN resetlogs_change_num HEADING 'Resetlogs|Change Num' COLUMN resetlogs FORMAT a21 HEADING 'Reset Logs|Date/Time' SELECT rd.db_key , rd.dbinc_key , rd.dbid , rd.name , rd.resetlogs_change# resetlogs_change_num , TO_CHAR(rd.resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs FROM rc_database rd ORDER BY rd.name /