Skip to content

使用MDATA恢复drop的对象

olm@hc10 /home/oracle/MDATA$ sqlplus hc/hc

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Jan 20 22:21:20 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> 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
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.
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
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
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
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

使用MDATA恢复Oracle中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>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.
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 [datafile ] ] [object ] parallel [parallel_degree]
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>

[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]$
[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

使用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.

SQL>

#mdata reload dict to load luda’s objects
MDATA>reload dict
Start reload dict,Sat Jan 10 23:55:18 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,Sat Jan 10 23:55:23 CST 2015,reload success.
MDATA>list table luda
OWNER TABLE
————— ————–
LUDA T2
LUDA T1
owner:LUDA has 2 rows selected.
MDATA>list index luda
Not found INDEX: owner,luda
MDATA>

#unload data for luda as a before map
MDATA>unload table luda.t2
unload schema:LUDA;tab:T2
tabName:LUDA.T2,dic_obj:com.olm.b.H@11905e0,getFileid:4,getBlockid:187
99 rows unloaded

#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.

#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.
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

#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

#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
MDATA>

# 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

SQL> desc lobtab
Name Null? Type
—————————————– ——– —————————-
NUMBER_CONTENT NUMBER(10)
VARCHAR2_CONTENT VARCHAR2(100)
DATE_CONTENT DATE
CLOB_CONTENT CLOB
BLOB_CONTENT BLOB

SQL>

[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)

[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

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

SQL> truncate table luda.lobtab

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.

MDATA>unload table luda.lobtab
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@195bbec,getFileid:4,getBlockid:451
0 rows unloaded
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
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

[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

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