关于dual表的破坏性测试(3)

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.

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/119e963248e6ccbb3ca2b2aaca28b500.html