DATABASE MISMATCH /NOT ABLE TO OPEN

Database is mounted but not able to open

 

alert log output

ORA-00600: internal error code, arguments: [kksfbc-reparse-infinite-loop], [0x20000000023E75F0], [], [], [], [], [], []
Tue Sep 4 12:05:37 2012
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Tue Sep 4 12:05:37 2012
Errors in file /u01/app/oracle/product/10.2.0/db_2/admin/tchhis/bdump/tchhis1_pmon_23813.trc:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 23991
ORA-1092 signalled during: ALTER DATABASE OPEN…

 

METHOD-1

Lets perform the steps to update the timestamps :

1.Backup the database

!!!!!!!!!! THIS STEP IS VERY IMPORTANT !!!!!!!!!!!!!!

–> Shutdown the database (shutdown immediate)

–> Take a complete/consistent backup of the database [You can use RMAN]

2.Startup the database in restricted mode and connect as sysdba

SQL> Startup restrict

SQL> connect / as sysdba

3.Execute query to determine number of rows affected:

SQL>select o.name, o.stime, d.p_timestamp
from obj$ o, dependency$ d
where o.stime != d.p_timestamp and o.type#=13 and o.obj#=d.p_obj#;

<– Note the number of objects returned

4.Execute the following update statement to correct those rows <- !!! DO NOT COMMIT AFTER THE UPDATE !!! ->

<– This should update the same number of rows returned in step 3.

SQL> update dependency$ d set p_timestamp = (select stime from obj$ p where
d.p_obj#=p.obj#) where d.p_obj# in (select obj# from obj$ where type#=13)
and d.p_timestamp != (select stime from obj$ o where d.p_obj#=o.obj#);

5.If the updated row count is not same as step 3, then rollback and skip the rest of the steps. Else skip this step and go to step 6.

SQL> ROLLBACK;

6.To cross verify, re-execute query to confirm all rows corrected:

SQL> select distinct o.name, o.stime, d.p_timestamp
from obj$ o, dependency$ d
where o.stime != d.p_timestamp and o.type#=13 and o.obj#=d.p_obj#;

<– Check the number of objects returned. This must be 0.

If the row count is not 0, then rollback

SQL> ROLLBACK;

else

SQL> COMMIT;

7.Restart the database with a ‘SHUTDOWN ABORT;’

SQL> shutdown abort;

SQL> startup;

8.After restart run $ORACLE_HOME/rdbms/admin/utlrp.sql

SQL>select o.name, o.stime, d.p_timestamp
from obj$ o, dependency$ d
where o.stime != d.p_timestamp and o.type#=13 and o.obj#=d.p_obj#;

1* select owner, object_name, object_type from dba_objects where status = ‘INVALID’ and OWNER=’SYS’
SQL> /

OWNER OBJECT_NAME OBJECT_TYPE
SYS AQ$_SCHEDULER$_EVENT_QTAB_V EVALUATION CONTEXT
SYS SCHEDULER$_EVENT_QUEUE_R RULE SET
SYS SCHEDULER$_EVENT_QUEUE_N RULE SET
SYS ROW_TABLE PROCEDURE
SYS PR_REVENUEDETAILSWARDUPDATE_ PROCEDURE
SYS PR_INSERTMISSINGCOSTCENTER PROCEDURE
SYS PR_GENERATEREVENUE_ PROCEDURE
SYS SP_BILLING_VALIDATION PROCEDURE
SYS SP_BILLING_PKGVALIDATION_AUTO PROCEDURE
SYS FN_GET_PACKAGE_DURATION FUNCTION
SYS FN_EMR_NAVBAR_RADIOLOGOY FUNCTION
SYS FN_STATREVENUETEST FUNCTION

12 rows selected.

METHOD-2

 

Database timestamp mismatch, lets try recreating the objects by running catalog and catproc.

Steps :

– Shutdown immediate
– startup upgrade

– Run the below scripts as sysdba

spool catout.log

@ORACLE_HOME/rdbms/admin/catalog
@ORACLE_HOME/rdbms/admin/catproc
@ORACLE_HOME/rdbms/admin/utlrp

–You may have to give the full path of oracle home in the above command

spool off
shutdown immediate
startup

Run the query again to check the timestamp mismatch if any.

SQL> select distinct o.name, o.stime, d.p_timestamp
from obj$ o, dependency$ d
where o.stime != d.p_timestamp and o.obj#=d.p_obj#; 2 3
5662 rows selected

SQL> update dependency$ d
set p_timestamp = (select stime from obj$ p where d.p_obj#=p.obj#)
where d.p_timestamp != (select stime from obj$ o where d.p_obj#=o.obj#); 2 3

5662 rows updated.

SQL> select distinct o.name, o.stime, d.p_timestamp
from obj$ o, dependency$ d
where o.stime != d.p_timestamp and o.obj#=d.p_obj#; 2 3

no rows selected

SQL> spool off;

 

 

 

 

 

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle backup & recovery. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s