【Oracle实验】数据库删除DUAL表并恢复
概述
今天主要分享之前在学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方面的内容,感兴趣的朋友可以关注一下~
相关推荐
Hody 2020-08-16
周嘉笙 2020-11-09
zhuzhufxz 2020-09-16
lklong 2020-11-22
oraclemch 2020-11-06
shilukun 2020-10-10
bfcady 2020-08-16
FightFourEggs 2020-08-16
数据库设计 2020-08-16
yanghuatong 2020-08-16
dbasunny 2020-08-16
罗罗 2020-08-16
ihuaqiang 2020-08-16
choice0 2020-07-30
娜娜 2020-07-28
solarspot 2020-07-28
踩风火轮的乌龟 2020-07-26
娜娜 2020-07-20
xwb 2020-07-19
娜娜 2020-07-18
流云追风 2020-07-04
dataminer 2020-06-25
娜娜 2020-06-22
zhangchaoming 2020-06-21