強制OPEN資料庫後遭遇ORA-08102故障的處理方法

強制OPEN資料庫後遭遇ORA-08102故障的處理方法

用隱含參數強制OPEN資料庫後會有很多遺留問題，如：需重建UNDO資料表空間，此外還會伴隨有ORA-08102錯誤

剛才做破壞online日誌實驗的時候採用加隱含參數強制開啟過資料庫，之後alert日誌就一直會報ORA-08102
Wed Jun 24 13:56:24 2015
Errors in file /u01/app/Oracle/admin/ora10g/bdump/ora10g_j000_4737.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)
ORA-12012: error on auto execute of job 1
ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)

--查看obj# 239的對象
SYS@ora10g> set line 130 pages 130
SYS@ora10g> col object_name form a15
SYS@ora10g> col owner for a10
SYS@ora10g> select owner,object_name,object_id,object_type from dba_objects where object_id=239;

OWNER  OBJECT_NAME    OBJECT_ID OBJECT_TYPE
---------- --------------- ---------- -------------------
SYS  I_JOB_NEXT  239 INDEX

--查看I_JOB_NEXT這個索引屬於哪個表
SYS@ora10g> select owner,index_name,table_name from dba_indexes where index_name='I_JOB_NEXT';

OWNER  INDEX_NAME  TABLE_NAME
---------- ------------------------------ ------------------------------
SYS  I_JOB_NEXT  JOB$

--查看I_JOB_NEXT這個索引建立再JOB$表的哪個列上
SYS@ora10g> col index_name for a10;
SYS@ora10g> col index_owner for a10
SYS@ora10g> col table_name for a10
SYS@ora10g> col column_name for a15
SYS@ora10g> select INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME from dba_ind_columns where INDEX_NAME='I_JOB_NEXT';

INDEX_OWNE INDEX_NAME TABLE_NAME COLUMN_NAME
---------- ---------- ---------- ---------------
SYS  I_JOB_NEXT JOB$ NEXT_DATE

根據metalink上的文章：

ORA-08102: TRYING TO MANIPULATE A JOB IN DBA_JOBS [ID 1036858.6]

Solution Description:
=====================

You need to recreate the inex I_JOB_NEXT.

Script "$ORACLE_HOME/rdbms/admin/cat7103.sql" creates the I_JOB_NEXT:

Drop and recreate this index.

connect sys/<password>
drop index i_job_next;
create index i_job_next on job$ (next_date)

Note: alter index I_JOB_NEXT rebuild;

      Will not fix the problem.

重建索引是沒有用的，必須先刪除，再重新建立

1.先刪除索引i_job_next

SYS@ora10g> drop index i_job_next;

Index dropped.

2重建索引i_job_next;

SYS@ora10g> create index i_job_next on job$ (next_date);

Index created.

重建索引後，問題解決。所以，在沒有必要的情況下，不要輕易去用隱含參數_allow_resetlogs_corruption=true去強制開啟資料庫，有隱患，如果不知道處理方法，仍然會對資料庫產生影響。