Oracle DUAL误删 恢复测试

元旦放假，本来是一件很愉快的事，但是灰度环境测试人员(权限很大的sys）因为误操作， 把dual 混杂在 正常的测试表中 给批量删除了。 辛亏不是大事。。。比较淡定，这么多年以来。

1 关于dual 表删除后恢复：(未重启数据库）

SQL> show user;

SQL> DROP TABLE DUAL;

SQL> SELECT SYSDATE FROM DUAL ;

--此时会报ora-01775错。

解决办法： 创建 SQL> create table sys.dual

( 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> @utlrp.sql

--再次验证，没有了。

SQL> select object_name,owner,object_type,status from dba_objects where status='INVALID';

no rows selected

2， 如果drop dual , 同时关闭了数据库，解决办法：

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, 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> @utlrp.sql --等待 5分钟左右。。

。。。。。。。。。

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