關於dual表的破壞性測試

關於dual表的破壞性測試

關於dual表的破壞性測試，既然是破壞性測試，就需要確定這個測試僅限於測試或者個人學習所用，可能有些sql看似極為簡單，但是一旦運行就會導致整個業務系統崩潰。
 比如說我們拿dual表開刀，這個表是一個dummy表，裡面的內容沒有特定的意義，就是為了存在而存在。但是一旦這個表出現問題，所有相關的基礎操作都會受到影響，後果不敢想象。
 來簡單類比一下，在個人的機器上開始做下面的嘗試，drop 表dual

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

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

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

SQL> commit;
 Commit complete.

SQL> select *from dual; --簡單的驗證，會發現問題似乎解決了。
D
 -
 X

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

OBJECT_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 BODY

OBJECT_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 synonyms

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.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01775: looping chain of synonyms
Process ID: 434
Session ID: 237 Serial number: 5

alert日誌中的相關內容如下：
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的預設值TRUE

SQL> show parameter track

NAME                                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 modified

SQL>  alter system set replication_dependency_tracking=false scope=spfile;
 System altered.

SQL> shutdown immediate
 ORA-01507: database not mounted

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>

SQL> select sysdate from dual;  --啟動起來之後嘗試，會發現問題沒有修複，我們需要重建表dual
 select sysdate from dual
                    *
 ERROR at line 1:
 ORA-01775: looping chain of synonyms

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

SQL> show parameter track
 NAME                                TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 db_unrecoverable_scn_tracking        boolean    TRUE
replication_dependency_tracking      boolean    FALSE

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

Oracle 11gR2 在VMWare虛擬機器中安裝步驟