Action Plan
1. Execute srdc_db_undo_ora-30013.sql as sysdba and provide the spool output
--srdc_db_undo_ora-30013.sql
REM srdc_db_undo_ora-30013.sql
REM collect Undo parameters and segment details for troubleshooting ORA-30013 issues.
define SRDCNAME='DB_Undo_ORA-30013'
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 Tablespace Details**********************
show parameter undo
Select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE')
/
--***********************Active or Dead Transaction Details***********************
select KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA, KTUXECFL, KTUXESIZ
from x$ktuxe
where KTUXESTA='ACTIVE' and KTUXECFL='DEAD'
/
SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING
/
SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors
/
show parameter undo
set echo off
set sqlprompt "SQL> " term on
set verify on
spool off
set markup html off spool off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set echo on
2. Provide the command which encountered the ORA-30013 error
3. Upload the alert log file along with the tracefile generated (if any).