DUAL表的删除重启数据库会导致ORA-1092错误

如果删除SYS.DUAL表，关闭数据库后在重启数据库就会报ORA-1092错误，这个时候数据库打不开了。
可以通过设置REPLICATION_DEPENDENCY_TRACKING=FALSE 来打开数据库。然后重建DUAL表。
这个参数以前只在高级复制用过，没想到还可以有这个功能。
(感谢星星的指导http://www.itpub.net/viewthread.php?tid=1273693&pid=15327837&page=1&extra=#pid15327837）
[oracle@test ~]$ sqlplus "/ as sysdba "

 

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 29 05:28:59 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area  165119976 bytes
Fixed Size                   743400 bytes
Variable Size             113246208 bytes
Database Buffers           50331648 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

SQL> SELECT * FROM SYS.DUAL;

 

D
-
X

SQL> DROP TABLE SYS.DUAL;

 

Table dropped.

 

SQL> SHUTDOWN IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  165119976 bytes
Fixed Size                   743400 bytes
Variable Size             113246208 bytes
Database Buffers           50331648 bytes
Redo Buffers                 798720 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> SHOW PARAMETER REPLIC
ORA-03114: not connected to ORACLE

 

 

ALERT文件里的内容：

Wed Dec 29 05:31:59 2010
Errors in file /u01/app/admin/dbtest/udump/dbtest_ora_5929.trc:
ORA-00980: synonym translation is no longer valid
Error 980 happened during db open, shutting down database
USER: terminating instance due to error 980
Instance terminated by USER, pid = 5929
ORA-1092 signalled during: ALTER DATABASE OPEN...

[root@test ~]# cat /u01/app/admin/dbtest/udump/dbtest_ora_5929.trc
/u01/app/admin/dbtest/udump/dbtest_ora_5929.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /u01/app/oracle
System name:    Linux
Node name:      test
Release:        2.6.9-89.EL
Version:        #1 Mon Jun 22 12:18:19 EDT 2009
Machine:        x86_64
Instance name: dbtest
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 5929, image: (TNS V1-V3)

*** SESSION ID8.3) 2010-12-29 05:31:59.699
ORA-00980: synonym translation is no longer valid

SQL> CONN / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  165119976 bytes
Fixed Size                   743400 bytes
Variable Size             113246208 bytes
Database Buffers           50331648 bytes
Redo Buffers                 798720 bytes
SQL> SHOW PARAMETER REP

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking      boolean     TRUE
SQL> alter system set replication_dependency_tracking=false;
alter system set replication_dependency_tracking=false
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set replication_dependency_tracking=false scope=spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

Total System Global Area  165119976 bytes
Fixed Size                   743400 bytes
Variable Size             113246208 bytes
Database Buffers           50331648 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

SQL> select * from dual;
select * from dual
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

SQL> create table dual (dummy varchar2(1)) storage (initial 1);

 

Table created.

 

SQL> insert into dual values('X');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> create public synonym dual for dual
  2  /
create public synonym dual for dual
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> grant select on dual to public with grant option

  2  /

Grant succeeded.

 

SQL> alter system set replication_dependency_tracking=true scope=spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

Total System Global Area  165119976 bytes
Fixed Size                   743400 bytes
Variable Size             113246208 bytes
Database Buffers           50331648 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.

SQL> select * from dual;

 

D
-
X