元旦放假，本来是一件很愉快的事，但是灰度环境测试人员(权限很大的sys）因为误操作， 把dual 混杂在 正常的测试表中 给批量删除了。 辛亏不是大事。。。比较淡定，这么多年以来。
sql> show user;
sql> DROP TABLE DUAL;
sql> SELECT SYSDATE FROM DUAL ;
--此时会报ora-01775错。
( dummy varchar2(1)) tablespace system;
sql> grant select on sys.dual to public with grant option;
sql> select sysdate from dual; --验证
但是当验证 sys 下一些无效对象 时，还是有很多存在：
sql > select object_name,owner,object_type,status from dba_objects where status='INVALID';
--解决办法：
[oracle@martin01 ~]$ cd /dba/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/
[oracle@martin01 admin]$ sqlplus / as sysdba
sql*Plus: Release 11.2.0.4.0 Production on Mon Jan 2 00:14:47 2017
Copyright (c) 1982,2013,Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Automatic Storage Management,OLAP,Data Mining
and Real Application Testing options
--再次验证，没有了。
sql> select object_name,status from dba_objects where status='INVALID';
no rows selected
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 613797888 bytes
Fixed Size 2255712 bytes
Variable Size 436208800 bytes
Database Buffers 171966464 bytes
Redo Buffers 3366912 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01775: looping chain of synonyms
Process ID: 6017
Session ID: 1 Serial number: 5
------alter trace 日志 告警 ------
[6017] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:13807764 end:13807824 diff:60 (0 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 /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_ora_6017.trc:
ORA-01775: looping chain of synonyms
Errors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_ora_6017.trc:
ORA-01775: looping chain of synonyms
Error 1775 happened during db open,shutting down database
USER (ospid: 6017): terminating the instance due to error 1775
Instance terminated by USER,pid = 6017
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (6017) as a result of ORA-1092
Mon Jan 02 00:22:41 2017
ORA-1092 : opitsk aborting process
-----------------------------------------------------------
解决办法：
sql> startup mount
ORACLE instance started.
Total System Global Area 613797888 bytes
Fixed Size 2255712 bytes
Variable Size 436208800 bytes
Database Buffers 171966464 bytes
Redo Buffers 3366912 bytes
Database mounted.
sql> show parameter dependency_tracking
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE
-
-dual 与参数replication_dependency_tracking 有关，因为在open 库的时候，需对其检测 ，这里通过mount 后设置参数 使其 false 状态（绕过检测）
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;
--重启库后，通过检测dual表，发现还是报错：
sql> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sql> startup
ORACLE instance started.
Total System Global Area 613797888 bytes
Fixed Size 2255712 bytes
Variable Size 436208800 bytes
Database Buffers 171966464 bytes
Redo Buffers 3366912 bytes
Database mounted.
Database opened.
sql> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
同时对应的alter 日志：
----
[6260] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:14188194 end:14188244 diff:50 (0 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
Starting background process QMNC
Mon Jan 02 00:29:01 2017
QMNC started with pid=31,OS id=6282
Mon Jan 02 00:29:02 2017
Errors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_mmon_6222.trc:
ORA-04063: package body "SYS.STANDARD" has errors
ORA-06508: PL/sql: could not find program unit being called: "SYS.STANDARD"
ORA-06512: at "SYS.DBMS_HA_ALERTS_PRVT",line 548
ORA-06512: at line 1
Errors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_mmon_6222.trc:
ORA-04063: package body "SYS.STANDARD" has errors
ORA-06508: PL/sql: could not find program unit being called: "SYS.STANDARD"
ORA-06512: at "SYS.DBMS_PRVT_TRACE",line 305
ORA-06512: at "SYS.DBMS_PRVT_TRACE",line 149
ORA-06512: at "SYS.DBMS_HA_ALERTS_PRVT",line 309
ORA-04063: package body "SYS.STANDARD" has errors
ORA-06508: PL/sql: could not find program unit being called: "SYS.STANDARD"
ORA-06512: at line 1
ARC3: Archival started
--------
这里重复 第一种类型，创建后，在修改参数即可：
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 sysdate from dual;
SYSDATE
---------
02-JAN-17
sql> alter system set replication_dependency_tracking=true scope=spfile;
System altered.
sql> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
1026
sql> host
[oracle@martin01 ~]$ cd /dba/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/
[oracle@martin01 admin]$ sqlplus / as sysdba
sql*Plus: Release 11.2.0.4.0 Production on Mon Jan 2 00:34:26 2017
Copyright (c) 1982,Data Mining
and Real Application Testing options
。。。。。。。。。
Function created.
PL/sql procedure successfully completed.
Function dropped.
PL/sql procedure successfully completed.
sql> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
现在重启数据库，即可。
sql> shutdown immmediate
SP2-0717: illegal SHUTDOWN option
sql> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup
ORACLE instance started.
Total System Global Area 613797888 bytes
Fixed Size 2255712 bytes
Variable Size 457180320 bytes
Database Buffers 150994944 bytes
Redo Buffers 3366912 bytes
Database mounted.
Database opened.
sql> select sysdate from dual;
SYSDATE
---------
02-JAN-17
I'M Martin.Lee
原文链接：https://www.f2er.com/oracle/211054.html