[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來解決的，難道我的測試資料庫按照了什麼特殊元件嗎？

[20141218]誤操作刪除dual表的恢復.txt

相關文章