Dropdual
程序员文章站
2022-06-11 13:12:03
...
有网友删除dual表出现了问题： 删除dual表的时候hang住，然后直接shutdown abort。再重新启动数据库的时候，发现open的时候一直hang住，但是打开另外一个窗口数据库确实已经打开的。 下面进行模拟： SYS@orcl11gdrop table dual; 一直hang在这不动 在另外一个
有网友删除dual表出现了问题：删除dual表的时候hang住，然后直接shutdown abort。再重新启动数据库的时候，发现open的时候一直hang住，但是打开另外一个窗口数据库确实已经打开的。
下面进行模拟：
SYS@orcl11g>drop table dual;
一直hang在这不动
在另外一个窗口：
SYS@orcl11g>shutdown abort ORACLE instance shut down.
重启数据库：
SYS@orcl11g>startup mount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted.
SYS@orcl11g>alter database open;
一直hang这个不动，打开另外一个窗口：
SYS@orcl11g>select open_mode from v$database; OPEN_MODE ---------------------------------------- READ WRITE SYS@orcl11g>发现数据库已经打开
这是因为系统触发器造成，在初始化参数中加入：
_system_trig_enabled=flase
再次启动数据库：
SYS@orcl11g>startup mount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted. SYS@orcl11g>show parameter _sys NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ _system_trig_enabled boolean FALSE audit_sys_operations boolean FALSE audit_syslog_level string filesystemio_options string none ldap_directory_sysauth string no SYS@orcl11g>alter database open; Database altered.最开始删除dual表的时候也是因为系统触发器的存在。
现在我们禁用了系统触发器将dual删除看是什么效果：
SYS@orcl11g>drop table dual; Table dropped.
重启数据库：
SYS@orcl11g>startup nomount pfile=initorcl11g.ora ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes SYS@orcl11g>show parameter rep NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ replication_dependency_tracking boolean TRUE SYS@orcl11g> SYS@orcl11g> SYS@orcl11g> SYS@orcl11g> SYS@orcl11g>alter database mount; Database altered. SYS@orcl11g>alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-01775: looping chain of synonyms Process ID: 2485 Session ID: 1 Serial number: 3
这里我们不用trace就知道是因为dual表的缘故，所以dual也是很重要的。不要乱玩
这时候我们需要将REPLICATION_DEPENDENCY_TRACKING参数设置为False才能打开数据库：
官方文档给出的解释：
REPLICATION_DEPENDENCY_TRACKING enables or disables dependency tracking for
read/write operations to the database. Dependency tracking is essential for
propagating changes in a replicated environment in parallel
加入参数打开数据库：
SYS@orcl11g>startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 327157712 bytes
Database Buffers 83886080 bytes
Redo Buffers 4288512 bytes
Database mounted.
SYS@orcl11g>show parameter rep
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
replication_dependency_tracking boolean FALSE
SYS@orcl11g>ak^H
SP2-0042: unknown command "a" - rest of line ignored.
SYS@orcl11g>a;ter^H
SYS@orcl11g>alter database open;
Database altered.
SYS@orcl11g>select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms这时候我们呢需要重建dual表：
SYS@orcl11g>CREATE TABLE "SYS"."DUAL" 2 ( "DUMMY" VARCHAR2(1) 3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 4 NOCOMPRESS LOGGING 5 STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 6 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 7 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 8 TABLESPACE "SYSTEM" ; Table created. SYS@orcl11g>SYS@orcl11g>GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION; Grant succeeded. SYS@orcl11g>SYS@orcl11g>@?/rdbms/admin/utlrp.sql
至此我们dual已经恢复完成
SYS@orcl11g>select 'www.zbdba.com' from dual; 'WWW.ZBDBA.COM' -------------------------- www.zbdba.com