Oracle DUAL误删 恢复测试

2017-01-02 08:24:01来源:CSDN作者:lmocm人点击

第七城市
元旦放假,本来是一件很愉快的事,但是灰度环境测试人员(权限很大的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 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> @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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.
Total System Global Area 613797888 bytesFixed Size 2255712 bytesVariable Size 436208800 bytesDatabase Buffers 171966464 bytesRedo Buffers 3366912 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-01775: looping chain of synonymsProcess ID: 6017Session 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 completedSMON: enabling tx recoveryDatabase Characterset is AL32UTF8No Resource Manager plan activeErrors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_ora_6017.trc:ORA-01775: looping chain of synonymsErrors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_ora_6017.trc:ORA-01775: looping chain of synonymsError 1775 happened during db open, shutting down databaseUSER (ospid: 6017): terminating the instance due to error 1775Instance terminated by USER, pid = 6017ORA-1092 signalled during: ALTER DATABASE OPEN...opiodr aborting process unknown ospid (6017) as a result of ORA-1092Mon Jan 02 00:22:41 2017ORA-1092 : opitsk aborting process-----------------------------------------------------------解决办法:SQL> startup mountORACLE instance started.
Total System Global Area 613797888 bytesFixed Size 2255712 bytesVariable Size 436208800 bytesDatabase Buffers 171966464 bytesRedo Buffers 3366912 bytesDatabase 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 modifiedSQL> alter system set replication_dependency_tracking=false scope=spfile;
--重启库后,通过检测dual表,发现还是报错:SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.
Total System Global Area 613797888 bytesFixed Size 2255712 bytesVariable Size 436208800 bytesDatabase Buffers 171966464 bytesRedo Buffers 3366912 bytesDatabase 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 completedSMON: enabling tx recoveryDatabase Characterset is AL32UTF8No Resource Manager plan activeStarting background process QMNCMon Jan 02 00:29:01 2017QMNC started with pid=31, OS id=6282Mon Jan 02 00:29:02 2017Errors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_mmon_6222.trc:ORA-04063: package body "SYS.STANDARD" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.STANDARD"ORA-06512: at "SYS.DBMS_HA_ALERTS_PRVT", line 548ORA-06512: at line 1Errors in file /dba/app/oracle/diag/rdbms/martin01/martin01/trace/martin01_mmon_6222.trc:ORA-04063: package body "SYS.STANDARD" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.STANDARD"ORA-06512: at "SYS.DBMS_PRVT_TRACE", line 305ORA-06512: at "SYS.DBMS_PRVT_TRACE", line 149ORA-06512: at "SYS.DBMS_HA_ALERTS_PRVT", line 309ORA-04063: package body "SYS.STANDARD" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.STANDARD"ORA-06512: at line 1ARC3: 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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 immmediateSP2-0717: illegal SHUTDOWN optionSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 613797888 bytesFixed Size 2255712 bytesVariable Size 457180320 bytesDatabase Buffers 150994944 bytesRedo Buffers 3366912 bytesDatabase mounted.Database opened.
SQL> select sysdate from dual;SYSDATE---------02-JAN-17


I'M Martin.Lee

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台