误删除dual表恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:误删除dual表恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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 条评论

  1. 惜分飞 说:

    删除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
    
  2. 惜分飞 说:

    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