Oracle误删dual表怎么办？这里教你怎么恢复

今天主要分享之前在学Oracle时做的一个实验，删除dual表并恢复。

dual表是系统的一个虚表，用来构成select的语法规则。

如果不小心删除了的话，会导致数据库起不来，报错ORA-01092: ORACLE instance terminated. Disconnection forced。

下面介绍一下实验的过程。

数据库版本11.2.0.3

OS:linux redhat6.4

[oracle@Oracle11g ~]$ sqlplus / as sysdba

SQL> select * from dual;

D

-

X

--删除DUAL表

SQL> drop table dual;

--报错，已无法启动

Alert日志报错：

[oracle@Oracle11g ~]$ tail -50f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

1.创建一个pfile,在pfile中加入参数

replication_dependency_tracking = FALSE 。

2.使用这个加参数的pfile启动数据库。

3.创建dual表。

东台数据恢复

4.去掉参数，用pfile重启或者直接默认spfile重启。

即可顺利完成。

[oracle@Oracle11g ~]$ sqlplus / as sysdba;

SQL> startup mount （只能用startup mount启动，startup nomount会报错）

SQL> create pfile='/tmp/pfile' from spfile;

SQL> shutdown immediate

在/tmp/pfile文件中最后加入参数：

replication_dependency_tracking = FALSE

[oracle@Oracle11g dbs]$ cat /tmp/pfile

orcl.__db_cache_size=704643072

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=687865856

orcl.__sga_target=1023410176

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=268435456

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.memory_target=1697644544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_retention=900

*.undo_tablespace='UNDOTBS1'

replication_dependency_tracking=FALSE

这个参数指定数据库在启动的时候是否启用读/写相关性跟踪。

[oracle@Oracle11g ~]$ sqlplus / as sysdba

SQL> startup pfile='/tmp/pfile'

--顺利启动，查看dual

SQL> select * from sys.dual;

官网：

'Dual' Synonym was Dropped by Mistake and Cannot Recreate it [ID 973260.1]

其中一段：

It appears a trigger is being fired prior to the create statement.

Solution

======================

-- To implement the solution, please execute the following steps::

It appears a before create trigger is firing before issuing the create synonym statement.

1- Issue:

SQL>ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;

SQL> create or replace public synonym dual for sys.dual;

SQL>ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=true SCOPE=MEMORY;

2- If that still fails, query dba_triggers to determine if you have a before create trigger enabled. If yes, disable it and then re-issue create synonym statement.

创建dual表是系统触发器的问题

SQL> ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;

System altered.

SQL> create table SYS.DUAL ( dummy VARCHAR2(1)) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initi

al 16K next 1M minextents 1 maxextents unlimited);

Table created.

--创建成功dual表

SQL> select * from dual;

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

SQL> commit;

SQL> grant select on DUAL to PUBLIC with grant option;

SQL> ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=true SCOPE=MEMORY;

SQL> select * from dual;

D

-

X

上海数据恢复

SQL> select owner,object_name,object_type from dba_objects where object_name='DUAL';

OWNER OBJECT_NAME OBJECT_TYPE

---------- ------------------------------ -------------------

SYS DUAL TABLE

PUBLIC DUAL SYNONYM

关闭数据库重启即可。

SQL> shutdown immediate;

SQL> startup （通过spfile启动）

SQL> select * from dual;

D

-

X

大家有空也可以做一下，这个也是朋友一次问到dual表删除后能不能恢复，然后去做的一个实验。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~