关于dual表的破坏性测试

关于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本文永久更新链接地址