概述

今天主要分享之前在學(xué)Oracle時(shí)做的一個(gè)實(shí)驗(yàn)，刪除dual表并恢復(fù)。

dual表是系統(tǒng)的一個(gè)虛表，用來(lái)構(gòu)成select的語(yǔ)法規(guī)則。

如果不小心刪除了的話，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)起不來(lái)，報(bào)錯(cuò)ORA-01092: ORACLE instance terminated. Disconnection forced。

下面介紹一下實(shí)驗(yàn)的過(guò)程。

---

環(huán)境：

數(shù)據(jù)庫(kù)版本11.2.0.3

OS:linux redhat6.4

---

刪除dual表重啟

[oracle@Oracle11g ~]$ sqlplus / as sysdbaSQL> select * from dual;D-X

--刪除DUAL表

SQL> drop table dual;

--報(bào)錯(cuò)，已無(wú)法啟動(dòng)

Alert日志報(bào)錯(cuò)：

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

---

解決思路：

1.創(chuàng)建一個(gè)pfile,在pfile中加入?yún)?shù)replication_dependency_tracking = FALSE 。

2.使用這個(gè)加參數(shù)的pfile啟動(dòng)數(shù)據(jù)庫(kù)。

3.創(chuàng)建dual表。

4.去掉參數(shù)，用pfile重啟或者直接默認(rèn)spfile重啟。

即可順利完成。

---

創(chuàng)建pfile，修改參數(shù)

[oracle@Oracle11g ~]$ sqlplus / as sysdba;SQL> startup mount (只能用startup mount啟動(dòng)，startup nomount會(huì)報(bào)錯(cuò))SQL> create pfile='/tmp/pfile' from spfile;SQL> shutdown immediate

在/tmp/pfile文件中最后加入?yún)?shù)：replication_dependency_tracking = FALSE

[oracle@Oracle11g dbs]$ cat /tmp/pfile orcl.__db_cache_size=704643072orcl.__java_pool_size=16777216orcl.__large_pool_size=16777216orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=687865856orcl.__sga_target=1023410176orcl.__shared_io_pool_size=0orcl.__shared_pool_size=268435456orcl.__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

這個(gè)參數(shù)指定數(shù)據(jù)庫(kù)在啟動(dòng)的時(shí)候是否啟用讀/寫(xiě)相關(guān)性跟蹤。

---

通過(guò)pfile重啟數(shù)據(jù)庫(kù)

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

--順利啟動(dòng)，查看dual

SQL> select * from sys.dual;

---

創(chuàng)建dual表

官網(wǎng)：

'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.創(chuàng)建dual表是系統(tǒng)觸發(fā)器的問(wèn)題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 ( initial 16K next 1M minextents 1 maxextents unlimited);Table created.

--創(chuàng)建成功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-XSQL> select owner,object_name,object_type from dba_objects where object_name='DUAL';OWNER OBJECT_NAME OBJECT_TYPE---------- ------------------------------ -------------------SYS DUAL TABLEPUBLIC DUAL SYNONYM

---

重啟數(shù)據(jù)庫(kù)

關(guān)閉數(shù)據(jù)庫(kù)重啟即可。

SQL> shutdown immediate;SQL> startup (通過(guò)spfile啟動(dòng))SQL> select * from dual;D-X

---

大家有空也可以做一下，這個(gè)也是朋友一次問(wèn)到dual表刪除后能不能恢復(fù)，然后去做的一個(gè)實(shí)驗(yàn)。

后面會(huì)分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的oracle .ctl 是什么文件_Oracle误删dual表怎么办？这里教你怎么恢复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。