اوراکل 23ai– بررسی وجود inconsistency در Data Dictionary

نازسازگاری در دیتادیکشنری امر متداولی نیست و به ندرت اتفاق می افتد اما در صورت رخ دادن می تواند بسیار چالش ساز باشد نمونه ای از این دست ناسازگاری که شاید شما هم با آن روبرو شده باشید، invalid reference است مثلا segmentای که اطلاعاتش در dba_objects موجود نیست.

 تا قبل از نسخه 23c، این قبیل ناسازگاری ها را می توانستیم با اسکریپت hcheck.sql شناسایی کنیم. اما در اوراکل نسخه 23c، پکیجی به نام DBMS_DICTIONARY_CHECK(البته در 23.3) ارائه شد که با کمک آن می توانیم از consistency در دیتادیکشنری اطمینان حاصل کنیم. این پکیج پروسیجرهای متعددی دارد:

SQL> desc DBMS_DICTIONARY_CHECK

PROCEDURE CRITICAL
PROCEDURE FULL
PROCEDURE DUPLICATEDATAOBJ
PROCEDURE IDNSEQOBJ
PROCEDURE IDNSEQSEQ
PROCEDURE INDEXPARTITIONSEG
PROCEDURE INDEXSEG
PROCEDURE INDINDPARMISMATCH
PROCEDURE INDPARTOBJ
PROCEDURE LOBSEG
PROCEDURE MVIEW
PROCEDURE NEXTOBJECT
PROCEDURE NOSEGMENTINDEX
PROCEDURE OBJECTNAMES
PROCEDURE OBJERROR
….

در این بین، پروسیجر FULL می تواند اطلاعات کاملی را در زمینه درستی Data Dictionary ارائه دهد که در ادامه نمونه ای از خروجی آن را مشاهده می کنید:

SQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.FULL;

dbms_dictionary_check on 06-DEC-2023 18:25:16
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 12/06 18:25:16 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ValidSeg                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 12/06 18:25:17 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 12/06 18:25:17 PASS
.- ObjError                    ... 2300000000 >  1102000000 12/06 18:25:17 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 12/06 18:25:17 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 12/06 18:25:17 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 12/06 18:25:17 PASS
---------------------------------------
06-DEC-2023 18:25:17  Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc

PL/SQL procedure successfully completed.

در ادامه قصد داریم ناسازگاری ای را در دیتادیکشنری ایجاد کنیم و سپس این پکیج را اجرا کنیم بدین منظور، رکوردی را از جدول seq$  حذف می کنیم(اعمال تغییر در جداول دیتادیکشنری ممکن است منجر به نابودی دیتابیس شود!):

SQL> select obj# from seq$ where rownum=1;
      OBJ#
----------
       130

SQL> delete seq$ where OBJ#=130;
1 row deleted.

SQL> commit;
Commit complete.

DBMS_DICTIONARY_CHECK را مجددا اجرا می کنیم:

SQL> exec DBMS_DICTIONARY_CHECK.full;

dbms_dictionary_check on 06-DEC-2023 18:31:40
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 12/06 18:31:40 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 12/06 18:31:41 WARN

HCKW-0006: SEQ$ entry missing for OBJ$ type#=6 (Doc ID 1360524.1)
OBJ#=130 Name=SYS.UGROUP_SEQUENCE

.- UndoSeg                     ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ValidSeg                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 12/06 18:31:41 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 12/06 18:31:41 PASS
.- ObjError                    ... 2300000000 >  1102000000 12/06 18:31:41 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 12/06 18:31:41 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 12/06 18:31:41 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
.- LobSeg                      ... 2300000000 <=  *All Rel* 12/06 18:31:41 PASS
---------------------------------------
06-DEC-2023 18:31:41  Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc

PL/SQL procedure successfully completed.

همانطور که می بینید خطای مربوط به object شماره 130 توسط این پکیج نمایش داده شده است.

HCKW-0006: SEQ$ entry missing for OBJ$ type#=6 (Doc ID 1360524.1)
OBJ#=130 Name=SYS.UGROUP_SEQUENCE

یکی دیگر از پروسیجرهای مهم این پکیج، CRITICAL است:

SQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.CRITICAL;

dbms_dictionary_check on 06-DEC-2023 18:36:01
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg                     ... 2300000000 <=  *All Rel* 12/06 18:36:01 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 12/06 18:36:01 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 12/06 18:36:01 PASS
---------------------------------------
06-DEC-2023 18:36:01  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *