使用bbed恢复表数据

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
リリース: 2016-06-07 16:05:10
オリジナル
1329 人が閲覧しました

对于表级别的数据恢复,ORACLE提供了多种恢复方法:flashback query,logmnr等。本文通过示例演示使用bbed的copy命令恢复用户误删除或者损坏的表数据,当然我们也可以使用该方法来恢复其他数据。 实验过程: SQL select tablespace_name,file_name from dba_da

对于表级别的数据恢复,ORACLE提供了多种恢复方法:flashback query,logmnr等。本文通过示例演示使用bbed的copy命令恢复用户误删除或者损坏的表数据,当然我们也可以使用该方法来恢复其他数据。

实验过程:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

SQL> select tablespace_name,file_name from dba_data_files;

 

TABLESPACE_NAME FILE_NAME

--------------- --------------------------------------------------

USERS       /home/app/oraten/oradata/oraten/users01.dbf

SYSAUX      /home/app/oraten/oradata/oraten/sysaux01.dbf

UNDOTBS1    /home/app/oraten/oradata/oraten/undotbs01.dbf

SYSTEM      /home/app/oraten/oradata/oraten/system01.dbf

TBS1        /home/app/oraten/oradata/oraten/tbs101.dbf

 

SQL> conn scott/tiger

Connected.

SQL> create table tcopy tablespace tbs1 as select object_id,object_name from user_objects;

 

Table created.

 

SQL> select * from tcop; 

select * from tcop

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

SQL> select * from tcopy;

 

 OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

     51809 INVALID_ROWS

     52080 TCOPY

     51574 PK_DEPT

     51573 DEPT

     51575 EMP

     51576 PK_EMP

     51577 BONUS

     51578 SALGRADE

 

8 rows selected.

 

SQL> conn / as sysdba

Connected.

SQL> alter system checkpoint;

 

System altered.

 

SQL> alter system flush buffer_cache;

 

System altered.

 

SQL> host cp /home/app/oraten/oradata/oraten/tbs101.dbf /home/app/oraten/oradata/oraten/tbs101.copy.dbf

 

SQL> conn scott/tiger

Connected.

SQL> delete from tcopy;

 

8 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from tcopy;

 

no rows selected

ログイン後にコピー

用户误将表数据删除,下面通过bbed来进行恢复.

首先看看需要修复的数据块

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

SQL> desc dba_segments

 Name                              Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 OWNER                              VARCHAR2(30)

 SEGMENT_NAME                           VARCHAR2(81)

 PARTITION_NAME                         VARCHAR2(30)

 SEGMENT_TYPE                           VARCHAR2(18)

 TABLESPACE_NAME                        VARCHAR2(30)

 HEADER_FILE                            NUMBER

 HEADER_BLOCK                           NUMBER

 BYTES                              NUMBER

 BLOCKS                             NUMBER

 EXTENTS                            NUMBER

 INITIAL_EXTENT                         NUMBER

 NEXT_EXTENT                            NUMBER

 MIN_EXTENTS                            NUMBER

 MAX_EXTENTS                            NUMBER

 PCT_INCREASE                           NUMBER

 FREELISTS                          NUMBER

 FREELIST_GROUPS                        NUMBER

 RELATIVE_FNO                           NUMBER

 BUFFER_POOL                            VARCHAR2(7)

 

SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name='TCOPY';

 

SEGMENT_NAME                                      HEADER_FILE

--------------------------------------------------------------------------------- -----------

HEADER_BLOCK     BLOCKS

------------ ----------

TCOPY                                           5

     531          8

ログイン後にコピー
ログイン後にコピー

使用bbed的copy命令来恢复

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

SQL> desc dba_segments

 Name                              Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 OWNER                              VARCHAR2(30)

 SEGMENT_NAME                           VARCHAR2(81)

 PARTITION_NAME                         VARCHAR2(30)

 SEGMENT_TYPE                           VARCHAR2(18)

 TABLESPACE_NAME                        VARCHAR2(30)

 HEADER_FILE                            NUMBER

 HEADER_BLOCK                           NUMBER

 BYTES                              NUMBER

 BLOCKS                             NUMBER

 EXTENTS                            NUMBER

 INITIAL_EXTENT                         NUMBER

 NEXT_EXTENT                            NUMBER

 MIN_EXTENTS                            NUMBER

 MAX_EXTENTS                            NUMBER

 PCT_INCREASE                           NUMBER

 FREELISTS                          NUMBER

 FREELIST_GROUPS                        NUMBER

 RELATIVE_FNO                           NUMBER

 BUFFER_POOL                            VARCHAR2(7)

 

SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name='TCOPY';

 

SEGMENT_NAME                                      HEADER_FILE

--------------------------------------------------------------------------------- -----------

HEADER_BLOCK     BLOCKS

------------ ----------

TCOPY                                           5

     531          8

ログイン後にコピー
ログイン後にコピー

查看修复结果

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

SQL> conn / as sysdba

Connected.

SQL> alter system flush buffer_cache;

 

System altered.

 

SQL> conn scott/tiger

Connected.

SQL> select * from tcopy;

 

 OBJECT_ID

----------

OBJECT_NAME

----------------------------------------------------------------------------------------------------

     51809

INVALID_ROWS

 

     52080

TCOPY

 

     51574

PK_DEPT

 

 

 OBJECT_ID

----------

OBJECT_NAME

----------------------------------------------------------------------------------------------------

     51573

DEPT

 

     51575

EMP

 

     51576

PK_EMP

 

 

 OBJECT_ID

----------

OBJECT_NAME

----------------------------------------------------------------------------------------------------

     51577

BONUS

 

     51578

SALGRADE

 

8 rows selected.

ログイン後にコピー
このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
最新の問題
人気のチュートリアル
詳細>
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート