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