关于dual表的破坏性测试，既然是破坏性测试，就需要确定这个测试仅限于测试或者个人学习所用，可能有些sql看似极为简单，但是一旦运行就会导致整个业务系统崩溃。 比如说我们拿dual表开刀，这个表是一个dummy表，里面的内容没有特定的意义，就是为了存在而存在。但是一旦这个表出现问题，所有相关的基础操作都会受到影响，后果不敢想象。 来简单模拟一下，在个人的机器上开始做下面的尝试，drop 表dualSQL> show user USER is "SYS" SQL> SQL> show parameter insta NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ active_instance_count integer cluster_database_instances integer 1 instance_groups string instance_name string TEST01 instance_number integer 0 instance_type string RDBMS open_links_per_instance integer 4 parallel_instance_group string parallel_server_instances integer 1 SQL> drop table dual; --运行这个命令也没有出现任何的问题 Table dropped.SQL> select count（*）from dual; --但是一旦开始尝试查询操作就会报出1775的错误。 select count（*）from dual * ERROR at line 1: ORA-01775: looping chain of synonymsSQL> select *from dual; --再次尝试，问题依旧，可以想象在线业务系统出现这个问题是致命的影响。 select *from dual * ERROR at line 1: ORA-01775: looping chain of synonyms --如果没有尝试重启数据库的情况下，完全可以采用如下的方式来解决，我们可以完全重建表dual,插入dummy的数据SQL> CREATE TABLE "SYS"."DUAL" （ "DUMMY" VARCHAR2（1） ） TABLESPACE "SYSTEM" ; Table created.SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION; Grant succeeded.SQL> select *from dual; no rows selectedSQL> insert into dual values（"X"）; 1 row created.SQL> commit; Commit complete.SQL> select *from dual; --简单的验证，会发现问题似乎解决了。 D - XSQL> select sysdate from dual; --尝试使用dual来查询时间，也没有发现问题。SYSDATE ------------------ 20-NOV-14 这个时候可以查看相关的sys下的失效对象，会发现受到影响的还不少，这个时候可以重新编译。SQL> select object_name,owner ,object_type from dba_objects where status="INVALID" OBJECT_NAME OWNER OBJECT_TYPE ------------------------------ ------------------------------ ------------------- AQ$_ALERT_QT_V SYS EVALUATION CONTEXT ALERT_QUE_R SYS RULE SET ALERT_QUE_N SYS RULE SET DBMS_RCVMAN SYS PACKAGE BODY DBMS_BACKUP_RESTORE SYS PACKAGE BODY DBMS_CDC_UTILITY SYS PACKAGE BODY DBMS_CDC_ISUBSCRIBE SYS PACKAGE BODY AQ$_AQ$_MEM_MC_V SYS EVALUATION CONTEXT AQ$_AQ_PROP_TABLE_V SYS EVALUATION CONTEXT AQ_PROP_NOTIFY_R SYS RULE SET AQ_PROP_NOTIFY_N SYS RULE SETOBJECT_NAME OWNER OBJECT_TYPE ------------------------------ ------------------------------ ------------------- ALERT_QUE$1 SYS RULE SET ALERT_QUE$1 SYS RULE AQ$_KUPC$DATAPUMP_QUETAB_V SYS EVALUATION CONTEXT DBMS_AW_EXP SYS PACKAGE BODY DBMS_AW_STATS SYS PACKAGE BODY STREAMS$_EVALUATION_CONTEXT SYS EVALUATION CONTEXT AQ$_SYS$SERVICE_METRICS_TAB_V SYS EVALUATION CONTEXT SYS$SERVICE_METRICS_R SYS RULE SET SYS$SERVICE_METRICS_N SYS RULE SET MGMT_RESPONSE DBSNMP PACKAGE BODY DBMS_JDM_INTERNAL SYS PACKAGE BODYOBJECT_NAME OWNER OBJECT_TYPE ------------------------------ ------------------------------ ------------------- AQ$_KUPC$DATAPUMP_QUETAB_1_V SYS EVALUATION CONTEXT SQL>@？/rdbms/admin/utlrp.sql如果不幸的是我们在出现问题的时候尝试重启数据库，就会发现数据库就起不来了。 SQL> drop table dual; Table dropped.SQL> select sysdate from dual; select sysdate from dual * ERROR at line 1: ORA-01775: looping chain of synonymsSQL> shutdown immediate Database closed. Database dismounted. Oracle instance shut down. SQL> startup ORACLE instance started.Total System Global Area 313159680 bytes Fixed Size 2227944 bytes Variable Size 255852824 bytes Database Buffers 50331648 bytes Redo Buffers 4747264 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-01775: looping chain of synonyms Process ID: 434 Session ID: 237 Serial number: 5alert日志中的相关内容如下： Undo initialization finished serial:0 start:236214754 end:236215144 diff:390 （3 seconds） Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active Errors in file /u03/ora11g/diag/rdbms/test01/TEST01/trace/TEST01_ora_434.trc: ORA-01775: looping chain of synonyms Errors in file /u03/ora11g/diag/rdbms/test01/TEST01/trace/TEST01_ora_434.trc: ORA-01775: looping chain of synonyms Error 1775 happened during db open, shutting down database USER （ospid: 434）: terminating the instance due to error 1775 Instance terminated by USER, pid = 434 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid （434） as a result of ORA-1092 Thu Nov 20 06:31:13 2014 ORA-1092 : opitsk aborting process Thu Nov 20 06:32:02 2014日志中???到的trace 文件的内容如下：*** 2014-11-20 06:31:11.920 *** SESSION ID:（237.5） 2014-11-20 06:31:11.920 *** CLIENT ID:（） 2014-11-20 06:31:11.920 *** SERVICE NAME:（SYS$USERS） 2014-11-20 06:31:11.920 *** MODULE NAME:（sqlplus@rac1 （TNS V1-V3）） 2014-11-20 06:31:11.920 *** ACTION NAME:（） 2014-11-20 06:31:11.920
ORA-01775: looping chain of synonyms ORA-01775: looping chain of synonyms*** 2014-11-20 06:31:11.947 USER （ospid: 434）: terminating the instance due to error 1775如果确实知道问题的原因就轻车熟路的解决了，要不还需要费一番周折，开启一些更为详尽的trace来排查。 这个错误和数据库参数replication_dependency_tracking有关,默认是TRUE,我们需要暂时绕过这个校验，先把库启动起来，然后重建表dual就可以了。在修复以后，重启数据库恢复replication_dependency_tracking的默认值TRUESQL> show parameter trackNAME TYPE ------------------------------------ --------------------------------- VALUE ------------------------------ db_unrecoverable_scn_tracking boolean TRUE replication_dependency_tracking boolean TRUE SQL> alter system set replication_dependency_tracking=false; alter system set replication_dependency_tracking=false * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modifiedSQL> alter system set replication_dependency_tracking=false scope=spfile; System altered.SQL> shutdown immediate ORA-01507: database not mountedORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 313159680 bytes Fixed Size 2227944 bytes Variable Size 255852824 bytes Database Buffers 50331648 bytes Redo Buffers 4747264 bytes Database mounted. Database opened. SQL>SQL> select sysdate from dual; --启动起来之后尝试，会发现问题没有修复，我们需要重建表dual select sysdate from dual * ERROR at line 1: ORA-01775: looping chain of synonymsSQL> CREATE TABLE "SYS"."DUAL" 2 （ "DUMMY" VARCHAR2（1） 3 ） 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> select sysdate from dual; --修复以后问题似乎就解决了。 SYSDATE ------------------ 20-NOV-14SQL> show parameter track NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unrecoverable_scn_tracking boolean TRUE replication_dependency_tracking boolean FALSESQL> alter system set replication_dependency_tracking=TRUE scope=spfile; System altered.SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 313159680 bytes Fixed Size 2227944 bytes Variable Size 255852824 bytes Database Buffers 50331648 bytes Redo Buffers 4747264 bytes Database mounted. Database opened. SQL> select sysdate from dual; SYSDATE ------------------ 20-NOV-14 然后可以重新编译失效对象，这个问题的解决就告一段落了。在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址
易网时代-编程资源站