Doc ID: Note:280928.1 Type: PROBLEM
Last Revision Date: 19-JUN-2005 Status: PUBLISHED

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2 to 9.2
This problem can occur on any platform.

Symptoms

1 Database startup crashes with ORA-1092 and ORA-980
2 Database was up and running before the last clean shutdown

Changes

Dual table was dropped which led to this problem

Cause

1 ) Drop the Table Dual while connected as sys user
2 ) Shutdown the database
3 ) Startup the Database
4 ) Startup fails with the ORA-1092 and ORA-980 as below

================================================
SQL> startup;
ORACLE instance started.

Total System Global Area 110173996 bytes
Fixed Size 454444 bytes
Variable Size 92274688 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
================================================

6 ) Set the Errorstack for the ORA-980 in the init.ora to get the cause of the problem

7 ) Erorstack for the 980 shows the problematic SQL which is crashing the DB

================================================
*** 2004-08-18 17:32:55.837
ksedmp: internal or fatal error
ORA-00980: synonym translation is no longer valid
Current SQL statement for this session:
select 1 from dual where exists (select 1 from system.repcat$_repprop prop
where prop.type in (-1,2,9,-4) and prop.how in (1,3))
================================================

Solution

1 ) Error 980 during db open is causing the shut down of the database

2 ) Need to create the Dual Table to resolve the issue (which was not possible in the mount stage)

Workaround
=========
3 ) Set the following parameter in the init.ora

replication_dependency_tracking = FALSE

4 ) Startup the database

5 ) Create the Dual Table and Dual Public Synonym

CREATE TABLE "SYS"."DUAL"
  (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 4;
Insert Into Dual Values ('X');
Commit;
Grant Select On Dual To Public;

6 ) Remove the above parameter and restart the database

Note :- Dual table should not be dropped in any case , as it can lead to serious problems

Errors

ORA-1092 Oracle instance terminated.ORA-980 synonym translation is no longer valid

ITPub博客

DB crash with ORA-1092 and ORA-980 While startup