88 lines
2.8 KiB
SQL
88 lines
2.8 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Online/Standby Redo log information
|
|
* Version :
|
|
* Parameters : None
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-AUG-15 Vishal Gupta Added group_size and total computation
|
|
* 14-JUL-14 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
Prompt
|
|
Prompt ************************
|
|
Prompt * Redologs Information
|
|
Prompt ************************
|
|
|
|
COLUMN type HEADING "Redo|Log|Type" FORMAT a7
|
|
COLUMN group# HEADING "Gr#" FORMAT 9999
|
|
COLUMN thread# HEADING "Th#" FORMAT 99
|
|
COLUMN status HEADING "Status" FORMAT a10
|
|
COLUMN archived HEADING "Archived" FORMAT a9
|
|
COLUMN members HEADING "Members" FORMAT 99999
|
|
COLUMN sequence# HEADING "Seq#" FORMAT 9999999
|
|
COLUMN Size_MB HEADING "RedoLog|Size|(MB)" FORMAT 99,999
|
|
COLUMN used_MB HEADING "Used|(MB)" FORMAT 99,999
|
|
COLUMN Group_Size_MB HEADING "Group|Size|(MB)" FORMAT 99,999,999
|
|
COLUMN first_time HEADING "FirstTime" FORMAT a20
|
|
COLUMN next_time HEADING "NextTime" FORMAT a20
|
|
COLUMN last_time HEADING "LastTime" FORMAT a20
|
|
|
|
BREAK ON type SKIP 1 ON thread#
|
|
COMPUTE SUM LABEL 'Total' OF Group_Size_MB FORMAT 999,999,999 ON type
|
|
COMPUTE SUM LABEL 'Total' OF Group_Size_MB ON REPORT
|
|
|
|
SELECT 'Online' type
|
|
, thread#
|
|
, group#
|
|
, status
|
|
, archived
|
|
, members
|
|
, sequence#
|
|
, (bytes/power(1024,2)) Size_MB
|
|
, NULL used_MB
|
|
, members * (bytes/power(1024,2)) Group_Size_MB
|
|
, TO_CHAR(first_time,'DD-MON-YY HH24:MI:SS') first_time
|
|
, TO_CHAR(next_time,'DD-MON-YY HH24:MI:SS') next_time
|
|
, NULL last_time
|
|
FROM v$log
|
|
UNION ALL
|
|
SELECT 'Standby' type
|
|
, thread#
|
|
, group#
|
|
, status
|
|
, archived
|
|
, (select count(1) from v$logfile f where f.group# = l.group#) members
|
|
, sequence#
|
|
, (bytes/power(1024,2)) Size_MB
|
|
, (used/power(1024,2)) used_MB
|
|
, (select count(1) from v$logfile f where f.group# = l.group#) * (bytes/power(1024,2)) Group_Size_MB
|
|
, TO_CHAR(first_time,'DD-MON-YY HH24:MI:SS') first_time
|
|
, TO_CHAR(next_time,'DD-MON-YY HH24:MI:SS') next_time
|
|
, TO_CHAR(last_time,'DD-MON-YY HH24:MI:SS') last_time
|
|
FROM v$standby_log l
|
|
ORDER BY type, thread# , group#
|
|
;
|
|
|
|
@@footer
|