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方面的內容，感興趣的朋友可以關注一下~