SRDC - ORA-1555 during Export: Checklist of Evidence to Supply (Doc ID 1682706.1)
SRDC - ORA-1555 during Export: Checklist of Evidence to Supply (Doc ID 1682706.1)
Action Plan
1. Execute srdc_db_undo_1555-1.sql as sysdba and collect the spool output. 以sysdba身份执行
--srdc_db_undo_1555-1.sql
REM srdc_db_undo_1555-1.sql
REM collect collect Undo parameters and Segments details.
define SRDCNAME='DB_Undo_1555-1'
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 Extents breakdown information***********************
select status, count(*) cnt from dba_rollback_segs
group by status
/
col segment_name format a30 head "Segment Name"
col "ACT BYTES" format 999,999,999,999 head "Active Bytes"
col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"
col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"
select segment_name, nvl(sum(act),0) "ACT BYTES",
nvl(sum(unexp),0) "UNEXP BYTES",
nvl(sum(exp),0) "EXP BYTES"
from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp
from dba_undo_extents where status='ACTIVE' group by segment_name
union
select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp
from dba_undo_extents where status='UNEXPIRED' group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp
from dba_undo_extents where status='EXPIRED' group by segment_name)
group by segment_name
order by 1
/
select distinct status st, count(*) "HOW MANY", sum(bytes) "SIZE"
from dba_undo_extents
group by status
/
select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID,
FILE_ID, BLOCK_ID, BYTES, BLOCKS, STATUS
from dba_undo_extents
order by 1,3,4,5
/
---***********************Undo Extents Contention breakdown***********************
-- Take out column TUNED_UNDORETENTION if customer
-- prior to 10.2.x
--
-- The time frame can be adjusted with this query
-- By default using around 4 hour window of time
--
-- Ex.
-- Using sysdate-.04 looking at the last hour
-- Using sysdate-.1 looking at the last 4 hours
-- Using sysdate-.32 looking at the last 8 hours
-- Using sysdate-1 looking at the last 24 hours
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN,
TUNED_UNDORETENTION, TUNED_UNDORETENTION/60/60 hours
from gv$undostat
where begin_time between (sysdate-.1)
and sysdate
order by inst_id, begin_time
/
set echo off
set sqlprompt "SQL> " term on
set verify on
spool off
set markup html off spool off
set echo on
2. Check the Undo Configuration recommendation using script srdc_undo_recommendation_10g.sql (version 10g onwards), or srdc_undo_recommendation_9i.sql (till version 9i). Collect the spool output.
2. 使用脚本srdc_undo_recommendation_10g.sql （版本10g起）或 srdc_undo_recommendation_9i.sql （版本9i）检查“Undo配置”建议
--srdc_undo_recommendation_10g.sql
REM srdc_undo_recommendation_10g.sql
REM check the current undo configuration and provides recommendation based on the previous workload..
define SRDCNAME='DB_Undo_Recommendation_10g'
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 SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
set echo off
set sqlprompt "SQL> " term on
set verify on
set markup html off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set verify on echo on
--srdc_undo_recommendation_9i.sql
REM srdc_undo_recommendation_9i.sql
REM check the current undo configuration and provides recommendation based on the previous workload..
define SRDCNAME='DB_Undo_Recommendation_9i'
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 SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_begin_time varchar2(30);
v_end_time varchar2(30);
v_max_qry_len number;
v_unto_tbs varchar2(100);
v_cur_undo_ret varchar2(100);
v_cur_undo_mb number;
v_undo_autoext varchar2(5);
v_cur_dt date;
v_recommended_undo_mb number;
v_analyse_start_time date;
v_analyse_end_time date;
BEGIN
select sysdate
into v_cur_dt
from dual;
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
select min(BEGIN_TIME), max(end_time) into v_begin_time, v_end_time from v$undostat;
select max(MAXQUERYLEN) into v_max_qry_len from v$undostat;
select value into v_cur_undo_ret from v$parameter where name = 'undo_retention';
select value into v_unto_tbs from v$parameter where name = 'undo_tablespace';
SELECT ROUND((UR * (UPS * DBS))/1024/1024)
INTO v_recommended_undo_mb
FROM
( select max(MAXQUERYLEN) UR
from v$undostat) ,
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks =
(SELECT MAX(undoblks)
FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'undo_tablespace'));
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_unto_tbs;
SELECT CASE(select count(*) from dba_data_files
where tablespace_name = v_unto_tbs
and autoextensible = 'YES')
WHEN 0 THEN 'OFF'
ELSE 'ON' END
into v_undo_autoext
from dual;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('----');
DBMS_OUTPUT.PUT_LINE(RPAD('Longest running query ran for : ',40) || ' : ' || v_max_qry_len || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention is ',40) || ' : ' || v_cur_undo_ret || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size is ',40) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace size is ',40) || ' : ' || v_undo_autoext);
DBMS_OUTPUT.PUT_LINE('----');
DBMS_OUTPUT.PUT_LINE(RPAD('Recommended undo retention is ',40) || ' : ' || TO_CHAR(v_max_qry_len+1) || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('Recommended undo tablespace size is ',40) || ' : ' || v_recommended_undo_mb || 'M');
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('- Undo Analysis completed at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
END;
/
set echo off
set sqlprompt "SQL> " term on
set verify on
set markup html off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set verify on echo on
3. If the failing table has LOB columns, check for LOB Corruption using srdc_corrupt_LOB.sql script. Collect the spool output.
3. 如果失败的表具有LOB列，请使用srdc_corrupt_LOB.sql脚本检查LOB损坏
--srdc_corrupt_LOB.sql
REM srdc_corrupt_LOB.sql
REM To identify corrupted row(s) in a LOB segment
define SRDCNAME='DB_Corrupt_LOB'
set pagesize 200 verify off 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
drop table corrupted_lob_data
/
create table corrupted_lob_data (corrupted_rowid rowid)
/
set term on
ACCEPT table_with_lob CHAR PROMPT 'Table Name :'
ACCEPT table_owner CHAR PROMPT 'Table Owner :'
ACCEPT lob_column CHAR PROMPT 'LOB Column Name :'
set term off
set serveroutput on
set concat off
declare
error_1555 exception;
pragma exception_init(error_1555,-1555);
num number;
begin
for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r);
commit;
end;
end loop;
end;
/
--********************Corrupt Data*****************
select *from corrupted_lob_data
/
drop table corrupted_lob_data
/
spool off
set markup html off spool off
set term on
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set echo off
set sqlprompt "SQL> " term on
set verify on
set echo on
4. Attach all of the above evidence to the Service Request at SR creation time. The evidence can all be placed into a single ZIP or similar compressed archive or can be attached as separate files.
