Destructive Testing of dual tables

Destructive Testing of dual tables

With regard to dual table destructive testing, since it is a destructive test, it needs to be determined that this test is limited to testing or personal learning, and some SQL statements may seem extremely simple, however, once running, the entire business system will crash.
For example, we can open a table named dual, which is a dummy table. The content in the table has no specific meaning and exists for the sake of existence. However, once a problem occurs in this table, all related basic operations will be affected, and the consequences cannot be imagined.
In a simple simulation, start the following attempt on a personal machine, drop the table 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; -- No problem occurred when running this command.
Table dropped.

SQL> select count (*) from dual; -- the error 1775 is returned once the query is started.
Select count (*) from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> select * from dual; -- try again and the problem persists. It can be imagined that the problem in the online business system is fatal.
Select * from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
-- If you do not try to restart the database, you can use the following method. We can completely recreate the table dual and insert dummy data.

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; -- simple verification, you will find that the problem seems to be resolved.
D
-
X

SQL> select sysdate from dual; -- try to use dual to query the time, and no problem is found.

SYSDATE
------------------
20-NOV-14
At this time, you can view the invalid objects in the related sys and find that there are still many affected objects. At this time, you can re-compile them.

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 $ _ MEM_MC_V SYS EVALUATION CONTEXT
AQ $ _ AQ_PROP_TABLE_V SYS EVALUATION CONTEXT
Aq_prop_policy_r SYS RULE SET
Aq_prop_policy_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

If we try to restart the database when a problem occurs, we will find that the database will not be able to get up.
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
The Session ID is 237 Serial number: 5.

The relevant content in the alert Log is as follows:
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

The trace file mentioned in the log is as follows:

* ** 06:31:11. 920
* ** Session id: (237.5) 06:31:11. 920
* ** Client id: () 06:31:11. 920
* ** Service name :( SYS $ USERS) 06:31:11. 920
* ** Module name :( sqlplus @ rac1 (TNS V1-V3) 06:31:11. 920
* ** Action name: () 06:31:11. 920

ORA-01775: looping chain of synonyms
ORA-01775: looping chain of synonyms

* ** 06:31:11. 947
USER (ospid: 434): terminating the instance due to error 1775

If you know the cause of the problem, you can easily solve it. Otherwise, you need to take a long time to start some more detailed trace for troubleshooting.
This error is related to the database parameter replication_dependency_tracking. The default value is TRUE. We need to temporarily bypass this check. First, start the database and recreate the table dual. After the restoration, restart the database to restore replication_dependency_tracking. The default value is 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; -- try it after it is started. The problem is not fixed. We need to recreate the table 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; -- the problem appears to be solved after the fix.
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
Then you can re-compile the invalid object, and the solution to this problem will come to an end.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm