SRDC

SRDC - ORA-1628: Checklist of Evidence to Supply (Doc ID 1682729.1)
1. Execute srdc_db_undo_ora-1628.sql as sysdba and collect the spool output.
--srdc_db_undo_ora-1628.sql

REM srdc_db_undo_ora-1628.sql
REM collect Undo parameters,segment and transaction details for troubleshooting issues ORA-1628 errors.
define SRDCNAME='DB_Undo_ORA-1628'
set pagesize 200 verify off sqlprompt "" term off entmap off echo off
set markup html on spool on
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'|| to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
spool &&SRDCSPOOLNAME..htm
select 'Diagnostic-Name ' "Diagnostic-Name ", '&&SRDCNAME' "Report Info" from dual
union all
select 'Time ' , to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual
union all
select 'Machine ' , host_name from v$instance
union all
select 'Version ',version from v$instance
union all
select 'DBName ',name from v$database
union all
select 'Instance ',instance_name from v$instance
/
set echo on

--***********************Undo Parameters**********************

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm in ( '_undo_autotune' , '_smu_debug_mode' ,
'_highthreshold_undoretention' ,
'undo_tablespace' , 'undo_retention' , 'undo_management' )
order by 2
/
--***********************Tuned Undo Retention***********************

select max(maxquerylen),max(tuned_undoretention) from v$undostat
/
select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT
/
--***********************Undo Space Usage***********************

select distinct status,tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name
/
select sum(bytes) from dba_free_space where tablespace_name in (select value from v$parameter where name='undo_tablespace')
/
select tablespace_name, 
round(sum(case when status = 'UNEXPIRED' then bytes else 0 end) / 1048675,2) unexp_MB ,
round(sum(case when status = 'EXPIRED' then bytes else 0 end) / 1048576,2) exp_MB ,
round(sum(case when status = 'ACTIVE' then bytes else 0 end) / 1048576,2) act_MB 
from dba_undo_extents group by tablespace_name
/
--***********************Extent Size and Total Bytes***********************

SELECT segment_name, bytes "Extent_Size", count(extent_id) "Extent_Count", bytes * count(extent_id) "Extent_Bytes" FROM dba_undo_extents WHERE status = 'ACTIVE' group by segment_name, bytes order by 1, 3 desc
/
--***********************Transaction Details***********************

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr
/
set echo off 
set sqlprompt "SQL> " term on 
set verify on 
spool off
set markup html off spool off 
set echo on
　　2. Attach the above evidence to the Service Request at SR creation time.
SRDC

Action Plan
