误删dual表，启动数据库失败

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

 

--------------------------------------------------------------------------------
table dual;

Table dropped.

force;
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

@>startup mount;
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
@>create pfile from spfile;

File created.

-- 修改參數文件加上replication_dependency_tracking = FALSE
……

-- 啟動數据庫
immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

pfile=G:A9Idatabaseinitantiper.ora;
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
-- 創建dual 表
table sys.dual
  2  (
  3   dummy varchar2(1)
  4  )
  5  pctfree 10 pctused 4;

Table created.

into dual values('X');

1 row created.

;

Commit complete.

select on dual to public;

Grant succeeded.

force;
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

sysdate from dual;

SYSDATE
------------------
07-Mar-07 10:26:15