误delete tab$后使用DUL类工具恢复数据(有抽取不出来的可能-原因未知)

1、实验环境
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

2、在会话1中delete tab$表
SQL> select count(*) from tab$;
  COUNT(*)
----------
      4496

SQL> delete from tab$;
4496 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(*) from tab$;
  COUNT(*)
----------
         0

3、在会话2中尝试登陆数据库报错如下
[oracle@sourcedb ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 7 15:24:16 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> conn / as sysdba
ERROR:
ORA-01075: you are currently logged on


SQL>  conn / as sysdba
ERROR:
ORA-01075: you are currently logged on


此时alert报错:
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2153.trc  (incident=19211):
ORA-00600: internal error code, arguments: [18061], [1403], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_19211/orcl_ora_2153_i19211.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun May 07 15:25:34 2017
Dumping diagnostic data in directory=[cdmp_20170507152534], requested by (instance=1, osid=2153), summary=[incident=19210].
Sun May 07 15:25:41 2017
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_2159.trc  (incident=18228):
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_18228/orcl_m000_2159_i18228.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_2159.trc:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], [], [], [], [], []
Dumping diagnostic data in directory=[cdmp_20170507152543], requested by (instance=1, osid=2159 (M000)), summary=[incident=18228].

4、在会话1中重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  413372416 bytes
Fixed Size                  2253784 bytes
Variable Size             327158824 bytes
Database Buffers           79691776 bytes
Redo Buffers                4268032 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [],
[], [], [], [], [], []
Process ID: 2208
Session ID: 1 Serial number: 5

alert报错如下:
Completed: ALTER DATABASE   MOUNT
Sun May 07 15:26:32 2017
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Sun May 07 15:26:32 2017
ARC0 started with pid=20, OS id=2210 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 opened at log sequence 30
  Current log# 3 seq# 30 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Sun May 07 15:26:33 2017
ARC1 started with pid=21, OS id=2212 
Sun May 07 15:26:33 2017
ARC2 started with pid=22, OS id=2214 
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Sun May 07 15:26:34 2017
ARC3 started with pid=23, OS id=2216 
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2208.trc  (incident=19372):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_19372/orcl_ora_2208_i19372.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2208.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2208.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 2208): terminating the instance due to error 704
Instance terminated by USER, pid = 2208
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (2208) as a result of ORA-1092
Sun May 07 15:26:34 2017
ORA-1092 : opitsk aborting process

此时数据库已被shutdown,并未启动在mount状态。

5、在会话1中将数据库启动至mount
SQL> startup mount
ORACLE instance started.
Total System Global Area  413372416 bytes
Fixed Size                  2253784 bytes
Variable Size             327158824 bytes
Database Buffers           79691776 bytes
Redo Buffers                4268032 bytes
Database mounted.

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/backup/TEST01.DBF


6、使用类DUL工具抽取业务数据

某DUL> unload table tab$
 2017-05-07 15:56:31 unloading table "SYS"."TAB$"...
 2017-05-07 15:56:31 unloaded 0 rows.                 《------------TAB$表数据为0行
 
某DUL> unload table obj$
 2017-05-07 15:59:57 unloading table "SYS"."OBJ$"...
 2017-05-07 15:59:57 unloaded 86355 rows.

某DUL> show user
USER is "SYS"
某DUL> list user
ID         NAME                 TABLE_CNT 
---------- -------------------- -----------
0          SYS                  1033      
5          SYSTEM               167       
9          OUTLN                3         
14         DIP                  0         
21         ORACLE_OCM           0         
30         DBSNMP               20        
31         APPQOSSYS            4         
32         WMSYS                46        
42         EXFSYS               47        
43         CTXSYS               50        
45         XDB                  80        
46         ANONYMOUS            0         
53         ORDSYS               5         
54         ORDDATA              73        
55         ORDPLUGINS           0         
56         SI_INFORMTN_SCHEMA   0         
57         MDSYS                151       
60         OLAPSYS              126       
64         MDDATA               0         
66         SPATIAL_WFS_ADMIN_USR 0         
69         SPATIAL_CSW_ADMIN_USR 0         
71         SYSMAN               729       
73         MGMT_VIEW            0         
74         FLOWS_FILES          1         
75         APEX_PUBLIC_USER     0         
77         APEX_030200          360       
78         OWBSYS               1         
79         OWBSYS_AUDIT         0         
83         SCOTT                4         
88         SOURCE_TEST          0         
90         TESTER               1         

2147483638 XS$NULL              0      


某DUL> list table 
ID         NAME                           DICT_ROWS  DICT_BLOCKS
---------- ------------------------------ ---------- ----------
......
87398      AQ$_KUPC$DATAPUMP_QUETAB_1_L   0          0         
87399      AQ$_KUPC$DATAPUMP_QUETAB_1_G   0          0         
87402      AQ$_KUPC$DATAPUMP_QUETAB_1_I   0          0         
87415      AQ$_KUPC$DATAPUMP_QUETAB_1_P   0          0         
87416      SYSNTSzhv1CZ4DFvgUwoGqMAE2A==  0          0         
87418      SYSNTSzhv1CZ6DFvgUwoGqMAE2A==  0          0         
87420      SYSNTSzhv1CZ8DFvgUwoGqMAE2A==  0          0         
87444      AQ$_KUPC$DATAPUMP_QUETAB_1_D   0          0         
87606      CUSTOMER                       13         1         
87612      TTT                            13         1        


抽取业务数据表TTT:
某DUL> unload table TTT
 2017-05-07 15:58:47 unloading table "SYS"."TTT"...
 2017-05-07 15:58:47 unloaded 13 rows.

查看抽取文件:
[oracle@sourcedb dump]$ ll
total 7560
-rw-r--r-- 1 oracle oinstall 7630848 Mar 07 15:52 SYS_OBJ$.dmp
-rw-r--r-- 1 oracle oinstall     420 Mar 07 15:52 SYS_OBJ$.sql
-rw-r--r-- 1 oracle oinstall   53248 Mar 07 15:56 SYS_TAB$.dmp
-rw-r--r-- 1 oracle oinstall     698 Mar 07 15:56 SYS_TAB$.sql
-rw-r--r-- 1 oracle oinstall   45056 Mar 07 15:58 SYS_TTT.dmp
-rw-r--r-- 1 oracle oinstall      90 Mar 07 15:58 SYS_TTT.sql
[oracle@sourcedb dump]$ cat SYS_TTT.sql 
CREATE TABLE "SYS"."TTT" ("ID_CUSTOMER" VARCHAR2(32), "NAME" VARCHAR2(100), "PHOTO" BLOB);
重建新的数据库,将以上抽取产生的文件数据采用imp/impdp恢复至新库即可。

使用undelete恢复TAB$表:
某DUL> undelete table TAB$
 2017-05-07 16:03:19 undeleting table "SYS"."TAB$"...
 2017-05-07 16:03:19 unloaded 2902 rows.


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
当多次重启数据库后,再次进入某DUL则无法查看user及table信息,原因未知:
SQL> startup force
ORACLE instance started.
Total System Global Area  413372416 bytes
Fixed Size                  2253784 bytes
Variable Size             327158824 bytes
Database Buffers           79691776 bytes
Redo Buffers                4268032 bytes
Database mounted.
conn / ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [],
[], [], [], [], [], []
Process ID: 2307
Session ID: 1 Serial number: 5
SQL> startup mount
ORACLE instance started.
Total System Global Area  413372416 bytes
Fixed Size                  2253784 bytes
Variable Size             327158824 bytes
Database Buffers           79691776 bytes
Redo Buffers                4268032 bytes
Database mounted.


某DUL> info

FILE#      TS#        RFILE#     BIGFILE    SIZE(GB)   NAME                
---------- ---------- ---------- ---------- ---------- ------------------------------
1          0          1          FALSE      0.73       /u01/app/oracle/oradata/orcl/system01.dbf
2          1          2          FALSE      0.52       /u01/app/oracle/oradata/orcl/sysaux01.dbf
3          2          3          FALSE      0.07       /u01/app/oracle/oradata/orcl/undotbs01.dbf
4          4          4          FALSE      0.00       /u01/app/oracle/oradata/orcl/users01.dbf
5          7          5          FALSE      0.01       /u01/backup/TEST01.DBF
某DUL> list user
ID         NAME                 TABLE_CNT 
---------- -------------------- -----------

某DUL> list table
ID         NAME                           DICT_ROWS  DICT_BLOCKS
---------- ------------------------------ ---------- ----------

某DUL> show user
某DUL> 
某DUL> set user sys
User SYS not exists
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/lk_db/article/details/78204823
个人分类: Oracle
上一篇记一次修改系统时间后,Linux重启磁盘superblock不一致故障fsck恢复
下一篇PG(HGDB)中查看用户对表的操作权限
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭