误删dual表后的恢复过程

1. 概要说明

主要说明dual表损坏或被误删情况下的处理过程

2. 9iR2下dual表误删重启后的处理过程

drop sys.dual表后重启数据库ALERT文件里的内容:

Wed Dec 29 05:31:59 2010
Errors in file /u01/app/admin/dbtest/udump/dbtest_ora_5929.trc:
ORA-00980: synonym translation is no longer valid
Error 980 happened during db open, shutting down database
USER: terminating instance due to error 980
Instance terminated by USER, pid = 5929
ORA-1092 signalled during: ALTER DATABASE OPEN...

在初始化参数中设置:

replication_dependency_tracking = FALSE

处理过程如下:

SQL> startup pfile='d:\pfile.bak'
SQL> create table sys.dual (dummy varchar2(1)) storage (initial 1);
SQL> insert into dual values('X');
SQL> commit;
SQL> grant select on dual to public with grant option;

3. 10gR2下dual表损坏或删除后的处理过程

3.1 删除dual表后未重启

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select object_type,owner from dba_objects where object_name='DUAL';
OBJECT_TYPE         OWNER
------------------- ------------------------------
TABLE               SYS
SYNONYM             PUBLIC

SQL> drop table sys.dual;
Table dropped.

SQL> select object_type from dba_objects where object_name='DUAL';
OBJECT_TYPE
-------------------
SYNONYM

SQL> SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual
                    *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> CREATE TABLE t1 AS SELECT * from dba_objects;
Table created.

SQL> drop table t1 purge; 
drop table t1 purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

设置10046跟踪会话发现,在每次删除表操作的时候发现如下错误

select dummy from dual where  ora_dict_obj_type = 'TABLE'

由于数据库没有停,重建dual表

SQL> CREATE TABLE "SYS"."DUAL"
        (       "DUMMY" VARCHAR2(1)
        ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
       STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
       PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
       TABLESPACE "SYSTEM" ;

Table created.

SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;
Grant succeeded.

SQL> insert into dual values('X');
 1 row created.

SQL> COMMIT;
Commit complete.

--编译对象
SQL> @?/rdbms/admin/utlrp.sql

3.2 删除dual表后重启数据库

SQL> startup
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2095672 bytes
Variable Size             130024904 bytes
Database Buffers          142606336 bytes
Redo Buffers                6291456 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

查看alert日志发现:

Thu Feb 26 03:16:49 CST 2015
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
Thu Feb 26 03:16:55 CST 2015
Errors in file /oracle/app/oracle/admin/tea/udump/tea_ora_16013.trc:
ORA-01775: looping chain of synonyms
Error 1775 happened during db open, shutting down database
USER: terminating instance due to error 1775
Instance terminated by USER, pid = 16013
ORA-1092 signalled during: ALTER DATABASE OPEN...

查看trace文件发现:

*** ACTION NAME:() 2015-02-26 03:16:49.458
*** MODULE NAME:(sqlplus@oel5 (TNS V1-V3)) 2015-02-26 03:16:49.458
*** SERVICE NAME:(SYS$USERS) 2015-02-26 03:16:49.458
*** SESSION ID:(159.3) 2015-02-26 03:16:49.458
DBRM(kskinitrm) cpu_count (fudge enabled): old(0) -> new(1)
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SYS.DUAL

数据库在启动的时候,因为无dual表导致死锁,然后启动失败。
可以设置replication_dependency_tracking解决问题,在pfile中增加如下参数:

replication_dependency_tracking = FALSE

处理过程如下:

SQL> startup pfile='/home/oracle/init.ora';
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2095672 bytes
Variable Size             130024904 bytes
Database Buffers          142606336 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.

SQL> CREATE TABLE "SYS"."DUAL"
        (       "DUMMY" VARCHAR2(1)
        ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
       STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
       PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
       TABLESPACE "SYSTEM" ;

Table created.

SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;
Grant succeeded.

SQL> insert into dual values('X');
 1 row created.

SQL> COMMIT;
Commit complete.

--编译对象
SQL> @?/rdbms/admin/utlrp.sql

关于紫砂壶

感悟技术人生
此条目发表在故障处理分类目录,贴了标签。将固定链接加入收藏夹。