误删除dual表恢复

发表于 2012 年 3 月 13 日由惜分飞

1.10G中删除dual表恢复

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.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 XFF AS SELECT * from dba_objects;

Table created.

SQL> drop table xff purge;
drop table xff 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表不存在了，表对应的同义词还存在，当查询dual的时候，会去查询同义词，然后该同义词去找表，而表不存在，所以出现上述的ORA-01775: looping chain of synonyms错误

2.解决方法

SQL> CREATE TABLE "SYS"."DUAL" 
  2     (       "DUMMY" VARCHAR2(1)
  3     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  4    STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  5    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  6    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.测试结果


SQL> select sysdate from dual;

SYSDATE
------------
13-MAR-12

SQL> drop table xff purge;

Table dropped.

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

《误删除dual表恢复》有 2 条评论

惜分飞说：

2012 年 3 月 14 日10:52

删除dual重启数据库后恢复
1.模拟删除dual表重启数据库

[oracle@xifenfei product]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 13 23:52:24 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> col owner for a20
SQL> col object_type for a30
SQL> select owner,object_type from dba_objects where object_name='DUAL';

OWNER                OBJECT_TYPE
-------------------- ------------------------------
SYS                  TABLE
PUBLIC               SYNONYM

SQL> DROP TABLE SYS.DUAL PURGE;

Table dropped.

SQL> select owner,object_type from dba_objects where object_name='DUAL';

OWNER                OBJECT_TYPE
-------------------- ------------------------------
PUBLIC               SYNONYM

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size             113247656 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

2.查看alert文件

Errors in file /u01/oracle/admin/XFF/udump/xff_ora_11051.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 = 11051
ORA-1092 signalled during: ALTER DATABASE OPEN...

3.查看trace文件

*** SESSION ID:(159.3) 2012-03-13 23:55:37.997
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
--------------------------------------------------------
  object   waiting  waiting       blocking blocking
  handle   session     lock mode   session     lock mode
--------  -------- -------- ----  -------- -------- ----
0x32ac8264  0x32f0b488 0x30737168    X  0x32f0b488 0x307247b0    S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x30737168, type: 53, owner: 0x31a2b84c, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=30737168 handle=32ac8264 request=X
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x307371b4[0x307247fc,0x30716860] htb=0x30716860 ssga=0x30716094
user=32f0b488 session=32f0a1d4 count=0 flags=[0000] savepoint=0x4f9a
LIBRARY OBJECT HANDLE: handle=32ac8264 mutex=0x32ac8318(0)
name=SYS.DUAL 
hash=608f3914e8f4f4eb2ce8093ef467d0e3 timestamp=NULL
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=2 hpc=0002 hlc=0002
lwt=0x32ac82c0[0x30737184,0x30737184] ltm=0x32ac82c8[0x32ac82c8,0x32ac82c8]
pwt=0x32ac82a4[0x32ac82a4,0x32ac82a4] ptm=0x32ac82ac[0x32ac82ac,0x32ac82ac]
ref=0x32ac82e0[0x32ac82e0,0x32ac82e0] lnd=0x32ac82ec[0x32ac6e74,0x32ac8e98]
  LIBRARY OBJECT: object=2ff176d0
  flags=NEX[0002] pflags=[0000] status=VALD load=0
  DATA BLOCKS:
  data#     heap  pointer    status pins change whr
  ----- -------- -------- --------- ---- ------ ---
      0 32ac81f4 2ff17768 I/P/A/-/-    0 NONE   00 
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x307247b0, type: 53, owner: 0x32f2867c, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=307247b0 handle=32ac8264 mode=S
call pin=0x3071849c session pin=(nil) hpc=0000 hlc=0000
htl=0x307247fc[0x30716860,0x307371b4] htb=0x30716860 ssga=0x30716094
user=32f0b488 session=32f0a1d4 count=1 flags=PNC/[0400] savepoint=0x4e74
LIBRARY OBJECT HANDLE: handle=32ac8264 mutex=0x32ac8318(0)
name=SYS.DUAL 
hash=608f3914e8f4f4eb2ce8093ef467d0e3 timestamp=NULL
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=2 hpc=0002 hlc=0002
lwt=0x32ac82c0[0x30737184,0x30737184] ltm=0x32ac82c8[0x32ac82c8,0x32ac82c8]
pwt=0x32ac82a4[0x32ac82a4,0x32ac82a4] ptm=0x32ac82ac[0x32ac82ac,0x32ac82ac]
ref=0x32ac82e0[0x32ac82e0,0x32ac82e0] lnd=0x32ac82ec[0x32ac6e74,0x32ac8e98]
  LIBRARY OBJECT: object=2ff176d0
  flags=NEX[0002] pflags=[0000] status=VALD load=0
  DATA BLOCKS:
  data#     heap  pointer    status pins change whr
  ----- -------- -------- --------- ---- ------ ---
      0 32ac81f4 2ff17768 I/P/A/-/-    0 NONE   00 
--------------------------------------------------------
This lock request was aborted.
kksfbc : Clear parse Err=4045 xsc=0xb694e5e0 chd=(nil) clk=(nil) 400020 40000000 22000
ORA-01775: looping chain of synonyms

从这里可以看出，数据库在启动的时候，因为无dual表导致死锁，然后启动失败

4.设置replication_dependency_tracking解决问题

SQL> create pfile='/tmp/pfile' from spfile;

File created.

SQL> !vi /tmp/pfile

--增加下面参数
replication_dependency_tracking = FALSE

SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size             113247656 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> CREATE TABLE "SYS"."DUAL"
  2     (       "DUMMY" VARCHAR2(1)
  3      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  4     STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  6     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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size             113247656 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL>  @?/rdbms/admin/utlrp.sql

惜分飞说：

2012 年 3 月 13 日21:57

11G中删除dual表恢复

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select object_type,owner from dba_objects where object_name='DUAL';

OBJECT_TYPE                    OWNER
------------------------------ ------------------------------
TABLE                          SYS
SYNONYM                        PUBLIC

SQL> select object_type,owner from dba_objects where object_name='DUAL';

OBJECT_TYPE                    OWNER
------------------------------ ------------------------------
TABLE                          SYS
SYNONYM                        PUBLIC

SQL> select sysdate from daul;
select sysdate from daul
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select sysdate from dual;

SYSDATE
------------
13-MAR-12

SQL> drop table sys.dual;

Table dropped.

SQL> create table t_xifenfei
  2  as 
  3  select * from dba_objects;

Table created.

SQL> select sysdate from dual;
select sysdate from dual
                    *
ERROR at line 1:
ORA-01775: looping chain of synonyms

--11g中无dual表可以删除表
SQL> drop table t_xifenfei;

Table dropped.

SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> drop table t_xifenfei purge;

Table dropped.


SQL>  select object_type,owner from dba_objects where object_name='DUAL';

OBJECT_TYPE                    OWNER
------------------------------ ------------------------------
SYNONYM                        PUBLIC

SQL> 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.

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

Grant succeeded.

SQL> @?/rdbms/admin/utlrp.sql

SQL> select sysdate from dual;

SYSDATE
------------
13-MAR-12

17813235971

QQ 咨询

误删除dual表恢复

《误删除dual表恢复》有 2 条评论

文章分类

最近发表

误删除dual表恢复

《误删除dual表恢复》有 2 条评论

标签云

文章分类

最近发表