SCN暴增问题导致的DBLINK access遭遇ora19706.
后续更新日志在笔记本上.
使用MDATA恢复drop的对象
创建测试表,同时drop对应测试表，模拟drop table的案例
SQL> create table mdata_test ( a number,b varchar2(10),c nvarchar2(30),d varchar2(20),e date);
Table created.
SQL> insert into mdata_test select rownum,lpad('x',10),'NC测试' || rownum, 'ZHS测试'|| rownum,sysdate+dbms_random.value(0,100) from dba_objects where rownum< =20000;
20000 rows created.
SQL> commit;
Commit complete.
SQL> select object_id,object_name from dba_objects where object_name='MDATA_TEST';
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
51997
MDATA_TEST
SQL> drop table mdata_test_bak purge;
Table dropped.
SQL> create table mdata_test_bak as select * from mdata_test;
Table created.
SQL> drop table mdata_test purge;
Table dropped.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
加载mdata
olm@hc10 /home/oracle/MDATA$ ./MDATA.sh MDATA for oracle 9I,10G,11G, release 3.0.3 (@)copyright LUDATOU,HC all rights reserved. Web:www.ludatou.com,www.hcdba.com Email:feigigi@qq.com,564439763@qq.com QQ group:66809572 loading default config....... load config file 'config.txt' successful loading default asm disk file ...... start loading default control file ...... load control file 'control.txt' successful load BOOTSTRAP$ success. load TAB$ success. load COL$ success. load OBJ$ success. load USER$ success. load PROPS$ success. load TABPART$ success. load TABSUBPART$ success. load IND$ success.
先对被drop的表所在tablespace进行scan做操
MDATA>scan extent tablespace 4 scan extent start: Tue Jan 20 22:23:10 CST 2015 scanning extent... scanning extent finished. scan extent completed: Tue Jan 20 22:23:12 CST 2015
接着通过drop恢复的语法对drop的表进行恢复
MDATA>unload object 51994 column number varchar2 nvarchar2 varchar2 date Unloading Object,object ID: 51994, Cluster: 0 -1 rows unloaded MDATA>unload object 51997 column number varchar2 nvarchar2 varchar2 date Unloading Object,object ID: 51997, Cluster: 0 20000 rows unloaded MDATA>exit
恢复结束后，切换到mdata的ddl目录，通过生成的ddl语句在instance中重建该table
olm@hc10 /home/oracle/MDATA$ cd ddl olm@hc10 /home/oracle/MDATA/ddl$ sqlplus hc/hc SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 20 22:23:56 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> host ls appclean.sql MDATA_0000051979.sql MDATA_0000051994.sql SYS.IND.sql SYS.TABPART.sql SYS.USER.sql app.sql MDATA_0000051981.sql MDATA_0000051997.sql SYS.PROPS.sql SYS.TABSUBPART.sql SQL> host ls -lrt total 44 -rw-r--r-- 1 oracle oinstall 281 Oct 6 16:57 appclean.sql -rw-r--r-- 1 oracle oinstall 1909 Oct 6 17:44 app.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 16:22 MDATA_0000051979.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 16:53 MDATA_0000051981.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 22:20 MDATA_0000051994.sql -rw-r--r-- 1 oracle oinstall 1328 Jan 20 22:23 SYS.USER.sql -rw-r--r-- 1 oracle oinstall 193 Jan 20 22:23 SYS.PROPS.sql -rw-r--r-- 1 oracle oinstall 1367 Jan 20 22:23 SYS.TABSUBPART.sql -rw-r--r-- 1 oracle oinstall 1277 Jan 20 22:23 SYS.TABPART.sql -rw-r--r-- 1 oracle oinstall 1761 Jan 20 22:23 SYS.IND.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 22:23 MDATA_0000051997.sql SQL> @MDATA_0000051997.sql Table created. SQL> quit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
接着切换到mdata目录下的data目录，通过sqlldr工具，对导出来的数据恢复到被刚创建的表中去
olm@hc10 /home/oracle/MDATA/ddl$ cd ../data olm@hc10 /home/oracle/MDATA/data$ sqlldr "'hc/hc' control=MDATA_0000051997.ctl" SQL*Loader: Release 10.2.0.5.0 - Production on Tue Jan 20 22:24:51 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 6502 Commit point reached - logical record count 13004 Commit point reached - logical record count 19506 Commit point reached - logical record count 20000
导入完成后，登入数据库验证恢复
olm@hc10 /home/oracle/MDATA/data$ sqlplus / as sysdba; SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 20 22:25:08 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn hc/hc Connected. SQL> select * from mdata_test_bak minus select * from MDATA_0000051997; no rows selected
至此drop的恢复完成。
使用mdata恢复drop的表需要知道该对象的object id，同时需要知道该表的表结构（字段，字段属性）
使用MDATA恢复Oracle中truncate的数据
创建测试表并模拟truncate的场景：
SQL> create table trutab as select * from t1;
Table created.
SQL> select count(*) from trutab;
COUNT(*)
----------
10000
SQL> commit
2 ;
Commit complete.
SQL>
SQL>
SQL> truncate table trutab;
Table truncated.
SQL> select count(*) from trutab;
COUNT(*)
----------
0
SQL> select segment_name,tablespace_name from dba_segments where segment_name='TRUTAB' and owner='LUDA';
SEGMENT_NAME TABLESPACE_NAME
---------------------------------------- ----------------------------------------
TRUTAB USERS
加载mdata
MDATA>reload dict Start reload dict,Mon Jan 12 13:46:45 CST 2015 load BOOTSTRAP$ success. load TAB$ success. load COL$ success. load OBJ$ success. load USER$ success. load PROPS$ success. load TABPART$ success. load TABSUBPART$ success. load IND$ success. End reload dict,Mon Jan 12 13:46:51 CST 2015,reload success. MDATA>list table luda OWNER TABLE --------------- -------------- LUDA T2 LUDA T1 LUDA TRUTAB owner:LUDA has 3 rows selected.
通过scan的方式扫描被truncate的表所在的tablespace
MDATA>unload table luda.trutab unload schema:LUDA;tab:TRUTAB tabName:LUDA.TRUTAB,dic_obj:com.olm.b.H@f0ca71,getFileid:4,getBlockid:195 0 rows unloaded MDATA>scan extent tablespace 4 datafile 4 scan extent start: Mon Jan 12 13:48:14 CST 2015 scanning extent... scanning extent finished. scan extent completed: Mon Jan 12 13:48:15 CST 2015 MDATA>unload table luda.trutab unload schema:LUDA;tab:TRUTAB tabName:LUDA.TRUTAB,dic_obj:com.olm.b.H@26e5d8,getFileid:4,getBlockid:195 0 rows unloaded MDATA>scan extent tablespace 4 datafile 4 auto scan extent [tablespace <ts #> [datafile <rfile #>] ] [object <data_object_data>] parallel [parallel_degree]
scan做操完成后，对该表执行unload操作
MDATA>unload table luda.trutab object auto Auto mode truncated table. unload schema:LUDA;tab:TRUTAB tabName:LUDA.TRUTAB,dic_obj:com.olm.b.H@cd1365,getFileid:4,getBlockid:195 10000 rows unloaded MDATA>
导出恢复的数据后，通过sqlload工具将导出的数据恢复到trutab表中：
[oracle@DB01 data]$ sqlldr luda/luda control=LUDA.TRUTAB.ctl SQL*Loader: Release 10.2.0.4.0 - Production on Mon Jan 12 13:52:44 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 2501 Commit point reached - logical record count 5002 Commit point reached - logical record count 7503 Commit point reached - logical record count 10000
恢复完成后验证恢复：
[oracle@DB01 data]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 12 13:52:54 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from luda.trutab;
COUNT(*)
----------
10000
至此truncate的恢复已经完成。提示一个小技巧，mdata的scan功能支持并行模式
使用MDATA恢复Oracle误删除(deleted)的数据
构建测试的环境并模拟删除数据：
SQL> create user luda identified by luda default tablespace users;
User created.
SQL> grant dba to luda;
Grant succeeded.
SQL> alter system checkpoint;
System altered.
SQL>
SQL> conn luda/luda
Connected.
SQL> create table t1 as select * from dba_objects where rownum < 10001;
Table created.
SQL> create table t2 as select object_id,object_name from dba_objects where rownum < 100;
Table created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
#rows before delete from luda.t2
SQL> select count(*) from t2;
COUNT(*)
----------
99
#delete 90rows from luda
SQL> delete from luda.t2 where rownum < 91;
90 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from luda.t2;
COUNT(*)
----------
9
SQL> alter system checkpoint;
System altered.
加载mdata
#using mdata unload data that have deleted from luda.t2 MDATA>reload dict Start reload dict,Sun Jan 11 00:01:50 CST 2015 SQLException: State: X0X95 Severity: 30000 Operation 'TRUNCATE TABLE' cannot be performed on object 'IDUL_OBJ' because there is an open ResultSet dependent on that object. load BOOTSTRAP$ success. load TAB$ success. load COL$ success. load OBJ$ success. load USER$ success. load PROPS$ success. load TABPART$ success. load TABSUBPART$ success. load IND$ success. End reload dict,Sun Jan 11 00:01:56 CST 2015,reload success.
通过unload的功能恢复出delete数据
MDATA>unload table luda.t2 only deleted data load only deleted data,schema:LUDA;tab:T2 tabName:LUDA.T2,dic_obj:com.olm.b.H@2d6636,getFileid:4,getBlockid:187 90 rows unloaded
退出mdata切换到恢复出数据的目录，这时候将恢复出的数据使用sqlloadl恢复到表中
#then u can find the unload data at /oracle/mdata/data [root@DB01 data]# ls -lFrt -rw-r--r-- 1 oracle oinstall 1042 Jan 11 00:02 LUDA.T2.dat -rw-r--r-- 1 oracle oinstall 204 Jan 11 00:02 LUDA.T2.ctl ...
这里介绍2个delete的功能
第一种是unload表数据时候包含被删除数据
using mdata unload luda.t2's data contrain deleted data MDATA>unload table luda.t2 contain deleted data unload schema:LUDA;tab:T2 contain deleted data tabName:LUDA.T2,dic_obj:com.olm.b.H@a0b1e1,getFileid:4,getBlockid:187 99 rows unloaded
第二种是unload表数据库时候不包含被删除数据
# using mdata unload luda.t2's data is not contrain deleted data MDATA>unload table luda.t2 unload schema:LUDA;tab:T2 tabName:LUDA.T2,dic_obj:com.olm.b.H@1c76b8d,getFileid:4,getBlockid:187 9 rows unloaded
使用MDATA恢复Oracle丢失的lob数据(DELETE以及TRUNCATE通用)
lob data recover
构建包含lob数据的表
SQL> desc lobtab Name Null? Type ----------------------------------------- -------- ---------------------------- NUMBER_CONTENT NUMBER(10) VARCHAR2_CONTENT VARCHAR2(100) DATE_CONTENT DATE CLOB_CONTENT CLOB BLOB_CONTENT BLOB [oracle@DB01 ~]$ ls blob_01.lob blob_02.lob clob_01.lob clob_02.lob lob_data_test.txt lobtab.sql [oracle@DB01 ~]$ cat blob_01.lob luda's blob data 01 [oracle@DB01 ~]$ cat blob_02.lob luda's blob data 02 [oracle@DB01 ~]$ cat clob_02.lob luda's clob data 02 [oracle@DB01 ~]$ cat clob_01.lob luda's clob data 01 [oracle@DB01 ~]$ cat lobtab.ctl LOAD DATA INFILE 'lob_data_test.txt' INTO TABLE lobtab FIELDS TERMINATED BY ',' (number_content CHAR(10), varchar2_content CHAR(100), date_content DATE "DD-MON-YYYY" ":date_content", clob_filename FILLER CHAR(100), clob_content LOBFILE(clob_filename) TERMINATED BY EOF, blob_filename FILLER CHAR(100), blob_content LOBFILE(blob_filename) TERMINATED BY EOF) --将lob数据导入到表中 [oracle@DB01 ~]$ sqlldr luda/luda control=lobtab.ctl log=lob_tab.log bad=lob_tab.bad SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jan 15 00:39:40 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 2 [oracle@DB01 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 15 00:39:48 2015 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options --查询lob数据 SQL> select * from luda.lobtab 2 ; SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> select count(*) from luda.lobtab; COUNT(*) ---------- 2 SQL> COLUMN varchar2_content FORMAT A16 SQL> COLUMN date_content FORMAT A12 SQL> COLUMN clob_content FORMAT A20 SQL> SELECT number_content,varchar2_content,TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,clob_content,DBMS_LOB.getlength(blob_content) AS blob_length 2 FROM luda.lobtab; NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH -------------- ---------------- ------------ -------------------- ----------- 1 one 01-JAN-2006 luda's clob data 01 20 2 two 02-JAN-2006 luda's clob data 02 20
模拟truncate lob数据
SQL> truncate table luda.lobtab
加载mdata
loading default config....... load config file 'config.txt' successful loading default asm disk file ...... start loading default control file ...... load control file 'control.txt' successful load BOOTSTRAP$ success. load TAB$ success. load COL$ success. load OBJ$ success. load USER$ success. load PROPS$ success. load TABPART$ success. load TABSUBPART$ success. load IND$ success.
通过scan的方式扫描该表所在tablespace
MDATA>scan extent tablespace 4 datafile 4 scan extent start: Thu Jan 15 00:54:43 CST 2015 scanning extent... scanning extent finished. scan extent completed: Thu Jan 15 00:54:43 CST 2015
通过unload恢复出被truncate的lob数据
MDATA>unload table luda.lobtab object auto Auto mode truncated table. unload schema:LUDA;tab:LOBTAB . unloading (index organized) table LOB010001e3 Preparing lob metadata from lob index Reading LOB010001e3.dat 0 entries loaded and sorted 0 entries . unloading (index organized) table LOB010001d3 Preparing lob metadata from lob index Reading LOB010001d3.dat 0 entries loaded and sorted 0 entries tabName:LUDA.LOBTAB,dic_obj:com.olm.b.H@4e94a4,getFileid:4,getBlockid:451 2 rows unloaded
切换到恢复数据的存放目录
[oracle@DB01 data]$ pwd /oracle/mdata/data
通过sqlload加载恢复出的数据
[oracle@DB01 data]$ sqlldr luda/luda control=LUDA.LOBTAB.ctl log=LUDA.LOBTAB.log SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jan 15 00:57:15 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 2
加载完成后验证恢复后的lob数据
SQL> select count(*) from luda.lobtab; COUNT(*) ---------- 2 SQL> COLUMN varchar2_content FORMAT A16 SQL> COLUMN date_content FORMAT A12 SQL> COLUMN clob_content FORMAT A20 SQL> SELECT number_content,varchar2_content,TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,clob_content,DBMS_LOB.getlength(blob_content) AS blob_length 2 FROM luda.lobtab; NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH -------------- ---------------- ------------ -------------------- ----------- 1 one 01-JAN-2006 luda's clob data 01 20 2 two 02-JAN-2006 luda's clob data 02 20
恢复lob的关键在于lob index所在的块的保存，如果lob index有坏块则会造成恢复失败，需要额外处理。
