88 lines
3.1 KiB
MySQL
88 lines
3.1 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display instance startup details from AWR repository
|
|
* Parameters : 1 - Instance Number ( Use % to query for all instances)
|
|
* 2 - Number of days to query ( Default 30)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 04-Nov-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
VARIABLE INST_ID VARCHAR2(100)
|
|
VARIABLE DAYS VARCHAR2(100)
|
|
|
|
BEGIN
|
|
:INST_ID := '&&1';
|
|
:DAYS := '&&2';
|
|
:INST_ID := CASE WHEN NVL(:INST_ID,'x') = 'x' THEN '%' ELSE :INST_ID END;
|
|
:DAYS := CASE WHEN NVL(:DAYS,'x') = 'x' THEN '30' ELSE :DAYS END;
|
|
END;
|
|
/
|
|
|
|
|
|
set head off
|
|
SELECT '**********************************************' || chr(10) ||
|
|
'* Instance History' || chr(10) ||
|
|
'* (From AWR Repository)' || chr(10) ||
|
|
'*' || chr(10) ||
|
|
'* Input Parameters' || chr(10) ||
|
|
'* - Instance# = "' || :INST_ID || '"' || chr(10) ||
|
|
'* - Days = "' || :DAYS || '"' || chr(10) ||
|
|
'**********************************************' || chr(10) ||
|
|
''
|
|
FROM DUAL;
|
|
set head on
|
|
|
|
|
|
COLUMN instance_number HEADING "I#" FORMAT 99
|
|
COLUMN db_name HEADING "DBName" FORMAT a10
|
|
COLUMN instance_name HEADING "Instance|Name" FORMAT a10
|
|
COLUMN startup_time HEADING "StartupTime" FORMAT a18
|
|
COLUMN last_uptime HEADING "Last Uptime" FORMAT a16
|
|
COLUMN version HEADING "Version" FORMAT a10
|
|
COLUMN parallel HEADING "Parallel" FORMAT a10
|
|
COLUMN host_name HEADING "Host Name" FORMAT a10 TRUNCATE
|
|
COLUMN platform_name HEADING "Platform Name" FORMAT a18 TRUNCATE
|
|
|
|
SELECT i.db_name
|
|
, i.instance_number
|
|
, i.host_name
|
|
, i.instance_name
|
|
, to_char(i.startup_time,'DD-MON-YY HH24:MI:SS') startup_time
|
|
, NVL2(i.last_uptime
|
|
, LPAD( EXTRACT(DAY FROM i.last_uptime) || 'd '
|
|
|| LPAD(EXTRACT(HOUR FROM i.last_uptime),2) || 'h '
|
|
|| LPAD(EXTRACT(MINUTE FROM i.last_uptime),2) || 'm '
|
|
|| LPAD(EXTRACT(SECOND FROM i.last_uptime),2) || 's'
|
|
, 16)
|
|
, '')
|
|
last_uptime
|
|
, i.version
|
|
, i.parallel
|
|
, i.platform_name
|
|
FROM
|
|
( select a.*
|
|
, a.startup_time
|
|
- LAG(a.startup_time) OVER (PARTITION BY a.dbid, a.instance_number ORDER BY a.dbid, a.instance_number, a.startup_time) last_uptime
|
|
from dba_hist_database_instance a
|
|
, v$database d
|
|
WHERE a.dbid = d.dbid
|
|
AND a.instance_number like :INST_ID
|
|
) i
|
|
WHERE i.startup_time > systimestamp - :DAYS
|
|
ORDER BY i.startup_time
|
|
;
|
|
|
|
@@footer |