加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle DUAL误删 恢复测试

发布时间：2020-12-12 15:30:26 所属栏目：百科 来源：网络整理
导读：元旦放假，本来是一件很愉快的事，但是灰度环境测试人员(权限很大的sys）因为误操作， 把dual 混杂在 正常的测试表中 给批量删除了。 辛亏不是大事。。。比较淡定，这么多年以来。 1 关于dual 表删除后恢复：(未重启数据库） SQL show user; SQL DROP TABLE
元旦放假，本来是一件很愉快的事，但是灰度环境测试人员(权限很大的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,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,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同