ITPub博客

首页 > 数据库 > Oracle > [20141218]误操作删除dual表的恢复.txt

[20141218]误操作删除dual表的恢复.txt

原创 Oracle 作者:lfree 时间:2014-12-18 09:31:46 0 删除 编辑

[20141218]误操作删除dual表的恢复.txt

--没事,做一个误操作删除dual表的恢复,没想到不能按照网上介绍的方法恢复,做一个记录。

1.建立测试数据库:

mkdir -p /mnt/ramdisk
mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk

$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName test -sid test -sysPassword oracle \
-systemPassword oracle -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -listeners LISTENER -sampleSchema  true --memoryPercentage 2 \
-databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk

--以上是10g静态建立数据库的脚本,与11g有一些不同。

SYS@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--删除dual表,会导致应用出错,因为许多应用要执行select  sysdate from dual的命令,如果重启,在open阶段就要访问
--dual ,导致无法打开数据库。注意,千万不要在生产系统做这样的测试!!


1.首先抽取dual的定义:
SYS@test> @ &r/ddl sys.dual
C100
--------------------------------------------------------------------------
  CREATE TABLE "SYS"."DUAL"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;

---
CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;
GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;

--Insert into SYS.DUAL (DUMMY) Values ('X');
--COMMIT;

2.开始测试:
SYS@test> drop table sys.dual purge ;
Table dropped.


CREATE TABLE "SYS"."DUAL"
(    "DUMMY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;

--报如下错误:
CREATE TABLE "SYS"."DUAL"
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

--按照网上的介绍这样应该可以的,难道要删除同义词吗?
SYS@test> drop PUBLIC SYNONYM DUAL;
drop PUBLIC SYNONYM DUAL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

--问题依旧!做一个跟踪看看:

SYS@test> @ &r/10046on 12
Session altered.

=====================
PARSING IN CURSOR #15 len=275 dep=0 uid=0 oct=1 lid=0 tim=1385609730197333 hv=9179637 ad='76a61078'
CREATE TABLE "SYS"."DUAL"
(    "DUMMY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
END OF STMT
PARSE #15:c=0,e=1549,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1385609730197329
BINDS #15:
=====================
PARSE ERROR #24:len=94 dep=1 uid=47 oct=3 lid=47 tim=1385609730197782 err=1775
select dummy from dual where  ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'
EXEC #15:c=1000,e=461,p=0,cr=0,cu=3,mis=0,r=0,dep=0,og=1,tim=1385609730197891
ERROR #15:err=604 tim=1191228612
WAIT #15: nam='SQL*Net break/reset to client' ela= 2 driver id=1650815232 break?=1 p3=0 obj#=49815 tim=1385609730198202
WAIT #15: nam='SQL*Net break/reset to client' ela= 54 driver id=1650815232 break?=0 p3=0 obj#=49815 tim=1385609730198280
WAIT #15: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=49815 tim=1385609730198306
WAIT #15: nam='SQL*Net message from client' ela= 9676395 driver id=1650815232 #bytes=1 p3=0 obj#=49815 tim=1385609739874747
=====================

--可以发现在建立过程中就要访问dual表。select dummy from dual where  ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'。


3.开始按照网上的介绍开始恢复。
建立pfile,加入参数replication_dependency_tracking = FALSE。

SYS@test> create pfile='/tmp/test001.ora' from spfile ;
File created.

SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@test> startup pfile=/tmp/test001.ora
ORACLE instance started.

Total System Global Area    473956352 bytes
Fixed Size                    2084776 bytes
Variable Size               176160856 bytes
Database Buffers            285212672 bytes
Redo Buffers                 10498048 bytes
Database mounted.
Database opened.

SYS@test> show parameter track
NAME                                 TYPE     VALUE
------------------------------------ -------- -------
replication_dependency_tracking      boolean  FALSE

CREATE TABLE "SYS"."DUAL"
(    "DUMMY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;

CREATE TABLE "SYS"."DUAL"
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

--依旧报错!跟踪发现依旧要访问select dummy from dual where  ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC';

4.不行,采用升级方式:
SYS@test> startup upgrade pfile=/tmp/test001.ora
ORACLE instance started.
Total System Global Area    473956352 bytes
Fixed Size                    2084776 bytes
Variable Size               176160856 bytes
Database Buffers            285212672 bytes
Redo Buffers                 10498048 bytes
Database mounted.
Database opened.

CREATE TABLE "SYS"."DUAL"
(    "DUMMY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;

Table created.
---
Insert into SYS.DUAL (DUMMY) Values ('X');
COMMIT;

SYS@test> select object_type,owner from dba_objects where object_name='DUAL';
OBJECT_TYPE         OWNER
------------------- ------
TABLE               SYS
SYNONYM             PUBLIC

--同义次没有删除,无需建立
--CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;
GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;

SYS@test> GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;
Grant succeeded.

--ok,恢复完成。使用spfile参数启动数据库。
--我google许多blog,都没有使用startup upgrade pfile=/tmp/test001.ora来解决的,难道我的测试数据库按照了什么特殊组件吗?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1371760/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6465965