概述

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

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

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

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

环境：

数据库版本11.2.0.3

OS:linux redhat6.4

删除dual表重启

[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重启。

即可顺利完成。

创建pfile，修改参数

[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

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

通过pfile重启数据库

[oracle@Oracle11g ~]$ sqlplus / as sysdba
SQL> startup pfile='/tmp/pfile'

--顺利启动，查看dual

SQL> select * from sys.dual;

创建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方面的内容，感兴趣的朋友可以关注一下~

玖叶教程网

前端编程开发入门

【Oracle实验】数据库删除DUAL表并恢复

概述

环境：