脚本: 检查目标数据库是否因为Bug:970640 损坏

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

摘要 
检查目标数据库是否因为Bug:970640 损坏
 
产品名称, 产品版本 RDBMS, 8.1.5 8.1.6
平台  独立平台
创建日期  27-NOV-1999
 
说明
执行环境:     <SQL, SQL*Plus, iSQL*Plus> 访问权限:    要求运行连接为INTERNAL或一个 SYSDBA 用户 使用:     ex. sqlplus internal      SQL> START CHECKTARGET.SQL 说明:  使用前进行校对! 由于文本编辑器,电子邮件包和操作系统处理文本格式(空间es, 、制表符和回车)方式的不同,当你第一次收到它时,该脚本可能不处于可执行状态this 状态,检查该脚本确保已经纠正这种类型的错误,该脚本会产生一个名为[outputfile]的输出文件,这个文件可以在浏览器中查看或者上传用于支持分析。
 
描述 
传输表空间元数据的导出可能包含损坏的下一个对象ID这可能会损坏目标数据库的字典。该损坏可能会长时间沉默。该脚本用来检查目标数据库是否因为Bug:970640 损坏,当击中错误时,会收到错误ORA-600 [15260].@ 它可能与错误 ORA-600 [kkdlson2]有关,参考bug:1094031
 
参考文献
Note:76670.1 Bug:970640 Bug:1073721@ Bug:1094031
 
脚本
 

 

REM – – – – – – – – – – – – – cut here – – – – – – – – – – – – – – – – – –
REM CHECKTARGET.SQL
REM
REM This script should be used in conjunction with Note:76670.1
REM to check a target database for the side effects of BUG:970640
REM
REM For Oracle 8.1 ONLY
REM To be run connected INTERNAL or as a SYSDBA user
REM
REM SPOOL THE OUTPUT OF THIS SCRIPT
REM
SET SERVEROUTPUT ON
REM
DECLARE

LIMIT number:=2147483648; /* Highest sensible object id */
MAXOBJ number:=4294950911; /* Max ever object id */
next_id number; /* Current NEXT object_id */
high_id number; /* Current Highest object_id */
best_id number; /* Current Highest Object_id below LIMIT */
badcnt number:=0; /* Number of objects with ID above LIMIT */
dups boolean:=false; /* True if duplicate dataobj# */

— Cursor to get information on OBJECTs with ID above LIMIT

CURSOR bad_objects IS
SELECT greatest(object_id, data_object_id) ID,
OBJECT_TYPE,OWNER, OBJECT_NAME, SUBOBJECT_NAME
FROM dba_objects
WHERE object_id>=LIMIT OR data_object_id>=LIMIT
ORDER BY 3,4,5
;

— Cursor to check for DUPLICATE dataobj# which are not in a cluster

CURSOR Duplicates IS
select obj# from obj$ o
where o.dataobj# in
( select dataobj# from obj$ g
group by dataobj# having count(*) >1)
and not exists
(select 1 from clu$ c where c.dataobj#=o.dataobj#)
;
CURSOR ObjDesc(ID number) IS
select * from dba_objects where object_id=id;

BEGIN
dbms_output.enable(100000);

dbms_output.put_line(‘Checking target database for effects of Bug:970640’);

— First check for DUPLICATE dataobj#

FOR D in Duplicates
LOOP
IF not dups THEN
dbms_output.put_line(‘.’);
dbms_output.put_line(‘** Warning: Objects exist with duplicate DATAOBJ#’);
dbms_output.put_line(‘** These objects should be checked’||
‘ to ensure they are NOT in the same tablespace’);
dbms_output.put_line(‘.’);
dups:=true;
END IF;
FOR O in ObjDesc(D.obj#)
LOOP
dbms_output.put(‘. ‘||O.data_object_id||’ ‘);
dbms_output.put(O.owner||’.’||O.object_name||’ ‘||O.subobject_name);
dbms_output.put_line(‘ (‘||O.object_type||’)’);
END LOOP;
END LOOP;

— Get the NEXT OBJECT ID from the dictionary

SELECT dataobj# INTO next_id FROM obj$ WHERE name=’_NEXT_OBJECT’;

— Get the highest OBJECT ID that looks sensible (below 2Gb)

SELECT max(id) INTO best_id FROM
(SELECT max(dataobj#) ID FROM obj$
WHERE dataobj#<LIMIT and name!=’_NEXT_OBJECT’
UNION ALL
SELECT max(obj#) ID FROM obj$
WHERE obj#<LIMIT and name!=’_NEXT_OBJECT’
)
;

— Get the actual highest object ID, whether good or bad

SELECT max(greatest(obj#,dataobj#)) INTO high_id FROM obj$
WHERE name!=’_NEXT_OBJECT’;

— Output findings

dbms_output.put_line(‘.’);
dbms_output.put_line(‘Base data:’);
dbms_output.put_line(‘. NEXT OBJECT Id: ‘||next_id);
dbms_output.put_line(‘. HIGHEST OBJECT Id: ‘||high_id);
dbms_output.put_line(‘. BEST OBJECT Id: ‘||best_id);

— Comment on findings

IF next_id>=LIMIT THEN
dbms_output.put_line(‘.’);
dbms_output.put_line(‘NEXT OBJECT Id much too high’);
END IF;

IF high_id>=LIMIT THEN
dbms_output.put_line(‘.’);
dbms_output.put_line(‘The objects listed below have very high object ids.’);
dbms_output.put_line(‘Determine if they can be dropped/rebuilt:’);
FOR R in bad_objects
LOOP
dbms_output.put(‘. ‘||R.owner||’.’||R.object_name||’ ‘||R.subobject_name);
dbms_output.put_line(‘ (‘||R.object_type||’)’);
badcnt:=badcnt+1;
END LOOP;
END IF;

IF high_id>=next_id THEN
dbms_output.put_line(‘.’);
dbms_output.put_line(‘Some objects have IDs above “NEXT OBJECT”‘);
END IF;

IF high_id>=LIMIT OR high_id>=next_id OR next_id>=LIMIT THEN
dbms_output.put_line(‘.’);
dbms_output.put_line(‘** Corrective action is required by Oracle Support’);
dbms_output.put_line(‘** Information below for SUPPORT only’);
dbms_output.put_line(‘** Best option:’);
dbms_output.put(‘. ‘);
if badcnt>0 then
dbms_output.put(‘DROP the above ‘||badcnt||’ object/s and ‘);
end if;
dbms_output.put_line(‘RESET to ‘||(best_id+1));
if high_id>=LIMIT THEN
dbms_output.put_line(‘** Second option:’);
dbms_output.put_line(‘. RESET to ‘||(high_id+1)||’ leaving only ‘||
(maxobj-high_id-2)||’ objects before DB is in trouble’);
end if;
dbms_output.put_line(‘.’);
dbms_output.put(‘** Remember to RE-RUN this script IMMEDIATELY ‘);
dbms_output.put_line(‘prior to any corrective action’);
ELSE
IF not dups THEN
dbms_output.put_line(‘** Database object IDs look OK’);
END IF;
END IF;
IF dups THEN
dbms_output.put_line(‘.’);
dbms_output.put_line(‘** Remember to check the DUPLICATES above’);
END IF;
END;
/

REM
REM – – – – – – – – – – – – – cut here – – – – – – – – – – – – – – – – – –
REM

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569