程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 【BBED】bbed常用命令,bbed常用命令

【BBED】bbed常用命令,bbed常用命令

編輯:Oracle教程

【BBED】bbed常用命令,bbed常用命令


【BBED】bbed常用命令

 

 

 

 

一.1  相關知識點掃盲

BBED(Oracle Block Browerand EDitor Tool),用來直接查看和修改數據文件數據的一個工具,是Oracle一款內部工具,可以直接修改Oracle數據文件塊的內容,在一些極端恢復場景下比較有用。該工具不受Oracle支持,所以默認是沒有生成可執行文件的,在使用前需要重新連接。

 

一.1.1  我的編譯代碼

ls -l  $ORACLE_HOME/rdbms/lib/*sbbd*

ls -l  $ORACLE_HOME/rdbms/mesg/bbed*

chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/*sbbd*

chown oracle:dba /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/bbed*

 

--cd $ORACLE_HOME/rdbms/lib

--make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

 

--make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

 

一.1.2  我的使用代碼

vi /home/oracle/file.txt

set line 9999 pagesize 9999

col name format a80

select file#||' '||name||' '||bytes name from v$datafile;

 

vi /home/oracle/bbed.par

blocksize=8192

listfile=/home/oracle/file.txt

mode=edit

 

bbed parfile=/home/oracle/bbed.par

bbed PASSWORD=blockedit  mode=edit blocksize=8192 listfile=/home/oracle/file.txt

 

一.1.3  注意事項

① 若使用bbed的過程中,數據庫有重啟的過程,最好是退出BBED重新進入bbed的環境

② windows下BBED軟件和其他系統下BBED不太一樣,操作的時候塊號比其他系統下多一個

 

一.2  bbed日志記錄 log.bbd

bbed啟動和運行的過程會將運行過的所有命令記錄到當前的目錄下log.bbd文件中,所以bbed要求oracle用戶在當前目錄具有創建文件的權限。

一.3  報錯

一.3.1  BBED-00303: unable to open file 'log.bbd'

oracle用戶需要對當前操作目錄有權限,否則報錯:

[ZFZHLHRDB2:oracle]:/oracle>bbed

BBED-00303: unable to open file 'log.bbd'

[ZFZHLHRDB2:oracle]:/oracle>touch log.bbd

touch: 0652-046 Cannot create log.bbd.

[ZFZHLHRDB2:oracle]:/oracle>ls -ld

drwxr-xr-x    5 root     dba             256 Mar 14 15:41 .

[ZFZHLHRDB2:oracle]:/oracle>cd /home/oracle

[ZFZHLHRDB2:oracle]:/home/oracle>ls -ld

drwxr-xr-x    3 oracle   dba             256 Apr 05 17:01 .

[ZFZHLHRDB2:oracle]:/home/oracle>bbed

Password:

BBED-00113: Invalid password. Please rerun utility with the correct password.

 

[ZFZHLHRDB2:oracle]:/home/oracle>bbed parfile=/home/oracle/bbed.par

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Tue Apr 5 17:28:35 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> info

File#  Name                                                        Size(blks)

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

     1  /oracle/app/oracle/oralhr/system01.dbf                           96000

     2  /oracle/app/oracle/oralhr/sysaux01.dbf                           62720

     3  /oracle/app/oracle/oralhr/undotbs01.dbf                          11520

     4  /oracle/app/oracle/oralhr/users01.dbf                              640

     5  /oracle/app/oracle/oralhr/example01.dbf                          40080

 

 

 

第二章 BBED基本命令

先看幫助的說明:

BBED> help all

SET DBA [ dba | file#, block# ]

SET FILENAME 'filename'

SET FILE file#

SET BLOCK [+/-]block#

SET OFFSET [ [+/-]byte offset | symbol |*symbol ]

SET BLOCKSIZE bytes

SET LIST[FILE] 'filename'

SET WIDTH character_count

SET COUNT bytes_to_display

SET IBASE [ HEX | OCT | DEC ]

SET OBASE [ HEX | OCT | DEC ]

SET MODE [ BROWSE | EDIT ]

SET SPOOL [ Y | N ]

SHOW [ <SET parameter> | ALL ]

INFO

MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]

DUMP[/v] [ DBA | FILENAME | FILE | BLOCK |OFFSET | COUNT ]

PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME |BLOCK | OFFSET | symbol | *symbol ]

EXAMINE[/Nuf] [ DBA | FILE | FILENAME |BLOCK | OFFSET | symbol | *symbol ]

</Nuf>:

N - a number which specifies a repeatcount.

u - a letter which specifies a unit size:

  b -b1, ub1 (byte)

  h -b2, ub2 (half-word)

  w -b4, ub4(word)

  r -Oracle table/index row

f - a letter which specifies a displayformat:

  x -hexadecimal

  d -decimal

  u -unsigned decimal

  o -octal

  c -character (native)

  n -Oracle number

  t -Oracle date

  i -Oracle rowid

FIND[/x|d|u|o|c] numeric/character string [TOP | CURR ]

COPY [ DBA | FILE | FILENAME | BLOCK ] TO [DBA | FILE | FILENAME | BLOCK ]

MODIFY[/x|d|u|o|c] numeric/character string

     [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]

ASSIGN[/x|d|u|o] <targetspec>=<source spec>

<target spec> : [ DBA | FILE |FILENAME | BLOCK | OFFSET | symbol | *symbol ]

<source spec> : [ value | <targetspec options> ]

SUM [ DBA | FILE | FILENAME | BLOCK ] [APPLY ]

PUSH [ DBA | FILE | FILENAME | BLOCK |OFFSET ]

POP [ALL]

REVERT [ DBA | FILE | FILENAME | BLOCK ]

UNDO

HELP [ <bbed command> | ALL ]

VERIFY [ DBA | FILE | FILENAME | BLOCK ]

CORRUPT [ DBA | FILE | FILENAME | BLOCK ]

 

下面是幾個常用的:

set 設定當前的環境

show 查看當前的環境參數,跟sqlplus的同名命令類似。

dump 列出指定block的內容

find 在指定的block中查找指定的字符串,結果是顯示出字符串,及其偏移量--offset,偏移量就是在block中的字節數

modify 修改指定block的指定偏移量的值,可以在線修改。

copy 把一個block的內容copy到另一個block中

verify 檢查當前環境是否有壞塊

sum 計算block的checksum,modify之後block就被標識為壞塊,current checksum與reqired checksum不一致,sum命令可以計算出新的checksum並應用到當前塊。

undo 回滾當前的修改操作,如果手誤做錯了,undo一下就ok了,回到原來的狀態。

revert 回滾所有之前的修改操作,意思就是 undo all

 

 

二.1  SET 命令

二.1.1  set dba

Set the current data block using the standard Oracle DBA (Data Block Address)format. This is entered as file_id, block.

       關於DBA 說明,參考:Oracle rdba和 dba 說明 http://blog.csdn.net/tianlesoftware/article/details/6529346

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.emp;

 

set dba 4,145

 

 如果設置成功,會返回該block的RDBA (Relative Data Block Address),rdba就是rowid中的rfile#+block#。括號裡面的是DBA值和block 和 file id。 我們驗證一下:

sqlplus執行

variable dba varchar2(30);

exec :dba :=dbms_utility.make_data_block_address(4,145);

print dba

 

 

二.1.2  set filename

Sets the current file to the one specified. It must be a valid Oracle data file andit must be enclosed in single quotes. If the file is not in the current path itmust also be fully qualified. If successful, bbed will respond showing the filenow being accessed.

 

BBED> set filename '/u01/app/oracle/oradata/dave2/users01.dbf'

       FILENAME       /u01/app/oracle/oradata/dave2/users01.dbf

--必須是一個有效的datafile,並且用單引號括起來

 

二.1.3  set file

Sets the current file to the number specified. The number specified must be one ofthe file ids supplied in the filelist referenced at startup. If successful,bbed will respond showing the file id now being accessed.

BBED> set file 4

       FILE#           4

--注意這裡的number,是我們之前配置的filelist裡的number。它可以和我們db 裡的file id 不一樣。 不過最好是配置一樣的。

 

二.1.4  set block

Sets the current block. The block is relative to the filename or file already set.The absolute block can be specified, or an offset to the current block can bespecified using the plus (+) or (-) symbols. If successful, bbed will respondshowing the current block.

--注意這裡的block 是一個相對的位置,我們需要先指定一個file,然後在指定block。 即對應file裡的block。可以對當前block的位置進行+和-操作。

 

BBED> set file 4

       FILE#           4

BBED> set block 60882

BBED-00309: out of range block number(60882)

BBED> set file 1

       FILE#           1

BBED> set block 60882

       BLOCK#          60882

BBED> set bock +10

BBED-00202: invalid parameter (bock)

BBED> set block +10

       BLOCK#          60892

BBED> set block -10

       BLOCK#          60882

 

BBED> set file 4 block 520

        FILE#           4

        BLOCK#          520

 

BBED> set dba 4,520

        DBA             0x01000208 (16777736 4,520)

 

 

二.1.5  set offset

Sets the current offset. The offset is relative to the block already set. Theabsolute offset can be specified, or an offset to the current offset can bespecified using the plus (+) or minus (-) symbols. If successful, bbed willrespond showing the current offset.

      --偏移量是相對某個block裡的偏移量,可以用+和-進行操作

BBED> set offset 20

       OFFSET          20

BBED> set offset +2

       OFFSET          22

BBED> set offset -2

       OFFSET          20

 

二.1.6  set blocksize

 Sets the blocksize of the current file. The blocksize must match the file selectedor an error will be reported. If successful, bbed will respond showing thecurrent blocksize.

       設置當前datafile 的blocksize 大小,該大小必須和datafile 的實際block 匹配,否則會報錯。

BBED> set blocksize 8192

       BLOCKSIZE       8192

 

二.1.7  set listfile

Sets the listfile to the specified file. This option can be used if the listfile wasnot specified on the command line. The listfile must be enclosed in singlequotes. If successful, bbed will respond showing the current listfile.

--在前面講過,可以通過parameter file 來指定bbed的屬性,當然也可以通過set 來指定這些信息。對於listfile的文件,必須用單引號括起來。

 

BBED> set listfile '/u01/filelist.txt'

       LISTFILE        /u01/filelist.txt

 

二.1.8  set width

 Sets the current screen width. If not specified bbed will assume an 80-characterdisplay.

       設定當前屏幕的寬度,默認是80.

BBED> set width 200

       WIDTH           200

 

二.1.9  set count

 Sets the number of bytes of the data block to display from the dump command. Thedefault is 512.

Tosee an entire 8Kb block therefore you would need to dump the block eight timesat offsets 0, 512, 1024, 1536, 2048, 2560, 3092 and 3604.

By setting the count higher bbed will dump more of the block each time. Byreducing it a smaller dump can be achieved.

       設置dump 命令顯示bytes的數量。默認是512 bytes。

 

BBED> set count 512

       COUNT           512

 

二.1.10  set ibase

 Sets the internal number base. The default is decimal. However it can also be set tohexadecimal or octal. This allows the set file, set block and set offsetcommands to use an alternate base to decimal. If successful, bbed will respondshowing the current base:

--設置內部的數字格式,默認是十進制。 也可以設置為十六進制或者八進制。設置完數字格式之後,可是使用該格式來設置blcok,offset等。

BBED> set ibase hex

       IBASE           Hex

BBED> set block +D

       BLOCK#          14

BBED> set ibase decimal

       IBASE           Dec

 

二.1.11  set mode

 Sets the bbed mode. The options are browse or edit. In browse mode no changes can bemade. This is the suggested mode for first-time users, or if you are intendingto use the tool only to inspect data blocks.

--設置bbed 的模式,該默認有2種:browse edit。 browse 模式不允許進行修改。 如果要修改,就選擇edit模式。 這個在我們的之前的配置文件裡,我們選擇了edit。

BBED> set mode browse

       MODE            Browse

BBED> set mode edit

       MODE            Edit

 

二.2  show 命令

   顯示當前的配置選項。

show

 

BBED> show

       FILE#           1

       BLOCK#          14

       OFFSET          0

       DBA             0x0040000e(4194318 1,14)

       --注意這裡的block 變成了14. 是我們剛才設置的。

       FILENAME       /u01/app/oracle/oradata/dave2/system01.dbf

       BIFILE          bifile.bbd

       LISTFILE        /u01/filelist.txt

       BLOCKSIZE       8192

       MODE            Edit

       EDIT            Unrecoverable

       IBASE           Dec

       OBASE           Dec

       WIDTH           200

       COUNT           512

       LOGFILE         log.bbd

       SPOOL           No

 

二.3  info

顯示當前可以進行browse 或者edit 的file。即我們filelist 裡指定的datafile信息

Info

 

 

二.4  map

The map command shows a map of the current block. It can be combined with the /voption to produce a more verbose output. The map shows the offsets throughout the block where certain information can be found such as the block header, the data block header or the row directory.

If the set commands have not been used to set a current block, or it the user simply wishes to examine another block while keeping the current block their focus,the file name, file id, block or DBA can be specified with the command.

Map會通過偏移量來顯示block裡的詳細信息,如block header,data block header 和row directory。 使用/v 選項,可以查看更詳細的信息

在不指定block的情況下,會顯示當前block的信息,如果想顯示其他block的信息,可以使用file name,file id,block 和DBA 來指定要顯示的block。

 

--通過dba 來指定某個block

map /v dba 4,145

 

 

Map 顯示的具體信息解釋如下:

 

struct kcbh, 20 bytes

Block Header Structure

  ub1 type_kcbh

Block type (see Header Block Types below)

  ub1 frmt_kcbh

Block format 1=Oracle 7, 2=Oracle 8+

  ub1 spare1_kcbh

Not used

  ub1 spare2_kcbh

Not used

  ub4 rdba_kcbh

RDBA -Relative Data Block Address

  ub4 bas_kcbh

SCN Base

  ub2 wrp_kcbh

SCN Wrap

  ub1 seq_kcbh

Sequence number, incremented for every change made to the block at the same SCN

  ub1 flg_kcbh

Flag:

0x01 New Block

0x02 Delayed Logging Change advanced SCN/seq 0x04 Check value saved - block XOR‘s to zero

0x08 Temporary block

  ub2 chkval_kcbh

Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)

  ub2 spare3_kcbh

Not used

struct ktbbh, 72 bytes

Transaction Fixed Header Structure

  ub1 ktbbhtyp

Block type (1=DATA, 2=INDEX)

  union ktbbhsid, 4 bytes

Segment/Object ID

  struct ktbbhcsc, 8 bytes

SCN at last block cleanout

  b2 ktbbhict

Number of ITL slots

  ub1 ktbbhflg

0=on the freelist

  ub1 ktbbhfsl

ITL TX freelist slot

  ub4 ktbbhfnx

DBA of next block on the freelist

  struct ktbbhitl[2], 48 bytes

ITL list index

struct kdbh, 14 bytes

Data Header Structure

  ub1 kdbhflag

N=pctfree hit(clusters); F=do not put on freelist; K=flushable cluster keys

  b1 kdbhntab

Number of tables (>1 in clusters)

  b2 kdbhnrow

Number of rows

  sb2 kdbhfrre

First free row entry index; -1 = you have to add one

  sb2 kdbhfsbo

Freespace begin offset

  sb2 kdbhfseo

Freespace end offset

  b2 kdbhavsp

Available space in the block

  b2 kdbhtosp

Total available space when all TXs commit

struct kdbt[1], 4 bytes

Table Directory Entry Structure

  b2 kdbtoffs

 

  b2 kdbtnrow

 

sb2 kdbr[1]

Row Directory

ub1 freespace[8030]

Free Space

ub1 rowdata[38]

Row Data

ub4 tailchk

(See Tailchecks below)

 

 

       Different block types are designated by the first byte of the block. The following tableshows how to decode the block type:

       不同的block 可以第一個byte的值是不一樣的。 具體值對應block 類型如下。

Header Block Types

ID

Type

01

Undo segment header

02

Undo data block

03

Save undo header

04

Save undo data block

05

Data segment header (temp, index, data and so on)

06

KTB managed data block (with ITL)

07

Temp table data block (no ITL)

08

Sort Key

09

Sort Run

10

Segment free list block

11

Data file header

 

可以通過dump block來查看對應的具體的值。 下文講dump時會有相關的示例。

 

       oracleblocks 的最後4個bytes 是tail check。 下面看一下oracle 9i block的tail check 組成。

 

Tailchecks

       Thetail of an Oracle 8+ block is a concatenation of the lower order two bytes ofthe SCN base, the block type and the SCN sequence number.

      Oracleblock tail 由4個bytes組成,但實際上只用了低2個bytes來存放。 2個bytes的tail 由scn base,block type 和 scn sequence 組成。

 

E.g, if the SCN base number is 0x00029728,the block type is 06 and the SCN sequence number is 0x02, the tail check wouldbe 0x97280602:

 

SCN base     Type   SCN seq

9728           06        02

 

       Althoughthis tail check value is generated from three components, Oracle treats thefinal value as a single unsigned integer stored as a word (4-bytes). Onlittle-endian architecture machines, which include Intel, the value will bestores as low-order byte first.

       雖然tail check 由3個部分組成,但是oracle 把這3部分作為一個整體來存儲,並且占用4個bytes。

       對於little-endian(低端)架構的機器,包括Intel, 他們會先存放low-order byte,即低位字節。

     

       Thereforeif the tail check is examined in the block using a standard block editor, orthe dump command which will be explained in the next section, the byte ordermay look different. A tail check of 0x97280602 stored on an Intel machine wouldbe written to disk as "02062897".

       可以通過標准block editor 或者dump 來查看tail check。 對於不同的機器,他們存儲的順序是不一樣的。比如tail check 0x97280602 在Intel Machine 就被存儲為02062897,因為它會先保存low-order bytes。

 

二.5  dumpd

The dump command dumps the content of the block to the screen. It can be combinedwith the /v option to produce a more verbose output. TheDBA, Filename, File, Block and/or Offset to dump can be specified with thecommand. If these are not specified the current file, block and offsetas established with the set command will be dumped. The size of the dump islimited by the set count option and defaults to 512 bytes or alternatively thesize of the dump can be specified with the command.

dump命令可以將block 的內容顯示到屏幕。 每次顯示的bytes由count 控制,默認是512 bytes。 使用 /v 選項,可以顯示更多詳細信息。

dump /v dba 4,145 offset 0 count 128

 

 

二.6  examine(x)

       The examine command is used to display data from the block in raw or formattedoutput. The DBA, Filename, File, Block and/or Offset to examine can bespecified with the command. If these are not specified the current file, blockand offset as established with the set command will be examined. If the examinecommand is issued with just the block and offset to examine, bbed will displaythe data structure at that offset.

       --examine命令也是用來顯示datablock的內容的

       Unlikethe print command it cannot interpret data structures, but it can be used todisplay row information. Combined with knowledge of the data type of the row,it can be used to retrieve complete rows from the block:

      --print命令不能對data structures 進行一個解釋說明。

 

       The examine command will interpret the data in the block according to the followingswitches:

Switch

Display Format

/b

b1, ub1 (byte)

/h

b2, ub2 (half-word)

/w

b4, ub4 (word)

/l

b8, ub8 (long) (was b4/ub4 in Oracle7).

/r

Oracle table/index row

 

       Theexamine command allows switches from the print command to be combined withthese specific switches to interpret data.

      --examine可以根據switch的方式和print 命令進行一個結合來對data 進行解釋說明

 

       Forexample if we wanted to interpret data as an Oracle table row with the firstcolumn character and the second and third columns numeric, we would execute thecommand as follows:

       BBED>x /rcnn

 

       Thefollowing example shows the print and examine commands being used to step throughthe first and second rows of a block, with the data interpreted as a row in theformat: character, number, number:

 

x /rcnn

 

select * from scott.emp;

 

二.7  findf

       The find command is used to locate data within a block. The command allows hex,string or numeric data to be searched for. The pattern can be searched for fromthe top of the block (offset 0) using the TOP directive, or from the currentposition using the CURR directive.

       find命令可以用來搜索關鍵字。 可以從offset 0 搜索到top 或者從當前的offset 搜索到top。

 

       Switchesare used to determine the data type of the pattern to search for. These areshown below:

Switch

Datatype

/x

Hexadecimal

/d

Decimal

/u

unsigned decimal

/o

Octal

/c

character (native)

Note: Number and Dates are not supported bythe find command.

       find 命令支持的switch 類型如上表,注意,find 不支持number和Date 。

 

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.emp;

 

set file 4

set block 145

set offset 0

find /c SCOTT top

 

d /v dba 4,145 offset 7864 count 128

 

SCOTT

 

 

二.8  copy

       The copy command is used to copy blocks from one location to another. As with other commands, the file or filename and offset can be specified, or the DBA can be specified instead.

 可以把一個塊的內容拷貝到另一個塊中。

命令格式如下:

BBED> copy dba 1,115362 to dba 1,115363

copy 是個危險的命令,慎用。

 

二.9  modifym

BBED> help modify

MODIFY[/x|d|u|o|c] numeric/character string

      [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]

 

BBED>

 

       The modify command is used to change data inside a block. The DBA, Filename, File,Block and/or Offset to modify can be specified with the command. If these are not specified the current file, block and offset as established with the set command will be modified. Alternatively a symbol or symbol pointer can bespecified for modification.

       The pattern of bytes used to overwrite the original can be specified inhexadecimal, decimal, unsigned decimal, octal or character data using the sameswitches as the find command.

 可以修改中的內容,該步驟不演示,下面實驗有。

       在file 1,block 115362 有我們的Dave,我們這裡把Dave 改成dmm。

modify /c HAHAH dba 4,31 offset 7864  該步驟不演示,下面實驗有。

 

 

d /v dba 4,31 offset 7864 count 128

 

select * from scott.emp;

 

現在無改變。

sum apply

 

重啟庫才能生效。

shutdown immediate;

startup

select * from scott.emp;

 

 

二.10  assign

       The assign command does symbolic assignment, with type and range checking. Either target or source can be omitted for the current offset.

       For example, the following command assigns structure at current offset to file 4,block 2 ”s first ITL entry

BBED> assign dba 4, 145 ktbbhitl[0]

 指定結構 ,演示

二.11  sum

       Thesum command is used to check and set the block checksum. The DBA, Filename,File, Block and/or Offset to check can be specified with the command. If theseare not specified the current file, block and offset as established with theset command will be checked.

       Theapply directive can be used to update the checksum.

       我們可以使用bbed 對block 進行修改。 要使這些修改生效,就要使用sum命令

該步驟不演示,下面實驗有。

sum dba 4,31 apply  該步驟不演示,下面實驗有。

 

重啟庫或清除緩沖區才能生效。

 

二.12  push / pop

       Thepush and pop commands are used to push a file, block and offset location onto amemory backed stack and then pop them back. This allows a current locationbeing edited to be temporarily saved while another location is examined ormodified.

Note that the stack only stores the locationœ it does notsave the contents.

       --push命令將對象放到內存的stackpop 將對象從內存寫回磁盤

 

       Thefollowing example shows file 7, block 16, offset 8163 being examined. Thelocation is saved with the push command. We then move to file 6, block 1 beforereturning to DBA 7,16 with the pop command.

 

BBED> push dba 7,16

DBA 0x01c00010 (29360144 7,16)

OFFSET 8163

 

BBED> set dba 6,1

DBA 0x01800001 (25165825 6,1)

 

BBED> pop

DBA 0x01c00010 (29360144 7,16)

OFFSET 8163

       The command pop all can be used to remove all push‘d entries from the stack. Thecommand show all can be used to show all saved locations.

 

 

二.13  revert

       Therevert command is used to restore a file, filename, block or DBA to it‘soriginal state when bbed was started.

       revert是恢復自bbed 啟動以來的所有修改

 revert dba 4,31該步驟不演示,下面實驗有。

 

sum dba 4,31 apply

 

重啟庫才能看到變化。

shutdown immediate

startup

 

或者:

alter system flush buffer_cache;

 

select * from scott.emp;

 

 

還是沒有改變,flush 一下buffer cache之後就更改回來了。我們剛才在之前的測試時,是重啟了DB。 看來也是啟了flush buffer cache的作用。

 

二.14  undo

       Theundo command rolls back the last modify or assign command. If the undo commandis issued again the modification is re-done.

       undo命令是回滾最後一次的操作

該步驟不演示,下面實驗有。

modify /c HAHAH dba 4,31 offset 7864該步驟不演示,下面實驗有。

 

d /v dba 4,31 offset 7864 count 128

 

sum apply

 

一定要提交。

如果不提交之前可以

undo  該步驟不演示,下面實驗有。

 

alter system flush buffer_cache;

 

select * from scott.emp;

 

 

 

 

二.15  verify

       Theverify command is used to verify the integrity of the block. It performs asimilar function to the dbverify utility.

      verify命令用來驗block的完整性

verify dba 4,145

 

查看有沒有壞塊 

 

二.16  corrupt

       The corrupt command is used to mark blocks as media corrupt.

       corrupt命令將一個block 標記為corrupt,這樣db 在操作時就會跳過該block,從而避免錯誤

verify dba 4,31 該步驟不演示,下面實驗有。

 

corrupt dba 4,31該步驟不演示,下面實驗有。

 

verify dba 4,31

 

注意: undo 命令不能undo 一個corruption,但是revert 命令卻可以。

revert dba 4,31

 

sum apply

 

verify dba 4,31

 

 

二.17  修改數據塊中的內容

本例綜合運用上面的各種命令,修塊的內容,並撤銷修改。

在sqlplus中操作

select * from scott.emp;

 

現在無改變。

 

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.emp;

 

bbed操作

set dba 4,145 offset 0

find /c SCOTT top

 

dump /v dba 4,145 offset 7864 count 128

 

注意這裡面的Offsets:7864 to 7991, 它指的是這一行的一個地址。其中

S 的offset 是7864

C 的offset 是7865

O 的offset 是7866

T 的offset 是7867

空格也算offset。

modify /c HAHAH dba 4,145 offset 7864

 

sum apply

 

或者

sum dba 4,145 apply

 

dump /v dba 4,145 offset 7864 count 128

 

在sqlplus中操作

alter system flush buffer_cache;

select * from scott.emp;

 

二.17.1  下面撤銷修改:

bbed操作

revert dba 4,145

 

sum dba 4,145 apply

 

在sqlplus中操作

alter system flush buffer_cache;

select * from scott.emp;

 

二.18  恢復delete的rows

When rows are deleted in Oracle the data is not actually removed. The row is simply marked as deleted and the free space counters and pointers adjusted accordingly. The status of a row is stored in the Row Header which occupies the first few bytes of each row.

當row 被delete 的時候,實際上data 並沒有被remove,只是將該row 標記為delete,然後其對應的空間被統計為free space。 row 的status 存在每個row的row header裡。

The Row Header consists of the Row Flag, Lock Byte (ITL entry) and Column Count.The first of these - the Row Flag - is a single byte that holds a bitmask thatshows the status of the row. The bitmask is decoded as follows:

 RowHeader 包含Row Flag,Lock Byte(ITL)和column Count。其中Row Flag占用1個byte,並且以bitmask 來保存。bitmask 的解釋如下:

 

 

Cluster Key

Cluster Table Member

Head of row piece

Deleted

First data piece

Last data piece

1st Column continues from previous piece

Last column continues in next piece

128

64

32

16

8

4

2

1

二.18.1  刪除前

在sqlplus中操作

select * from scott.emp;

 

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.emp;

 

col segment_name for a10

select extent_id,segment_name,bytes/1024 k,file_id,block_id from dba_extents where owner='SCOTT';

 

 

alter system checkpoint;

 

 

alter system dump datafile 4 block 145;

 

查詢dump到哪個trace文件中:

oradebug setmypid

oradebug tracefile_name

 

more /u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/PROD_ora_6241.trc

可以看到這個表有14條記錄。

 

 

注意這裡的fb: --H-FL--。 其有8個選項,每個值分別與bitmask 對應。

       Therefore,columns that fit within a single block, are not chained, migrated or part of aclustered table and are not deleted will have the following attributes:

       (1)Head of Row Piece

       (2)First Data Piece

       (3)Last Data Piece

如果一個row 沒有被刪除,那麼它就具有上面的3個屬性,即Flag 表示為:--H-FL--. 這裡的字母分別代表屬性的首字母。其對應的值:32 + 8 + 4 =44 or 0x2c.

如果一個row 被delete了,那麼row flag 就會更新,bitmask 裡的deleted 被設置為16. 此時row flag 為: 32 + 16 + 8 + 4 = 60 or 0x3c.

delete from scott.emp where empno=7900;

commit;

select * from scott.emp;

 

 

二.18.2  刪除

alter system checkpoint;

alter system dump datafile 4 block 145;

 

查詢dump到哪個trace文件中:

oradebug setmypid

oradebug tracefile_name

 

more /u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/PROD_ora_6241.trc

 

 

注意上面的標簽被刪除的數據是HDFL,一般是H-FL

現在我們用bbed 將刪除的內容找回來。

bbed操作

set dba 4,145 offset 0

find /c JAMES top

 

 

dump /v dba 4,145 offset 7706 count 128

 

d /v dba 4,145 offset 7705 count 128

 

剛剛多了兩個字符。

8個字符才是一個完整的信息,所以要改變4個偏移才能展示完整的信息。

7706-8=7698再加1=7699.

尋找原則row directory核對,尋找前面最接近的值

d /v dba 4,145 offset 7699

 

這裡已經出現了我們3c(deleted)標志,但是注意這裡的位置的根據我們的查找的字符串來分的,實際在block裡的分割方式不一樣按照我們的offset 來進行。 我們可以通過row directory 來進行一個確認。

驗證一下

p kdbr

 

p *kdbr[9]

p *kdbr[10]

p *kdbr[11]

p *kdbr[12]

 

確定7699就是3c的

modify /x 2c offset 7699

 

sum apply

 

在sqlplus中操作

alter system flush buffer_cache;

select * from scott.emp;

 

之前delete 的數據已經恢復出來。

但是

系統認為已經刪掉了。

select count(*) from scott.emp;

 

alter table scott.emp move;

 

 

 

二.19  使用copy命令從舊數據文件中恢復delete的rows

將表scott.emp移動到我們的單獨的datafile裡(目的是查找清晰,並恢復上面的破壞)

col tablespace_name for a15

select tablespace_name,contents,status from dba_tablespaces;

 

col name for a50

select file#,name from v$datafile;

 

create tablespace lxtbs datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf' size 50m;

 

alter table scott.emp move tablespace lxtbs;

alter index scott.pk_emp rebuild tablespace lxtbs;

 

col table_name for a10

select table_name,tablespace_name,blocks from dba_tables where owner='SCOTT';

 

select * from scott.emp;

 

select file#||' '||name||' '||bytes from v$datafile;

 

二.19.1  創建一個舊的數據文件

shutdown immediate;

 

關庫後再操作:

cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle

cd /home/oracle

ls

 

將copy 的bak datafile 添加到bbed 的parfile裡面

vi file.txt

添加

6 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf 52428800

7 /home/oracle/lxtbs01.dbf 52428800

 

最後的7號文件是我們添加的。

 

startup

 

 

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.emp;

 

col owner for a10

col segment_name for a10

select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='SCOTT';

 

從這個查詢結果,我們可以看到,對象保存在datafile 6裡,從130 的block 開始存儲,占用8個blocks。

       這裡要注意的一點是:dba_segments 視圖裡的block 是從0開始的統計的,而bbed 裡是從1. 所以我們在bbed中指定block時,需要加1(130會報錯),也就是131

quit

bbed parfile=/home/oracle/bbed.par

blockedit

 

下面能查出東西來就行。

set dba 6,130 offset 0

p ktbbh

 

set dba 6,131 offset 0

p ktbbh

 

 

做一個誤刪除操作:

delete from scott.emp;

commit;

select * from scott.emp;

 

二.19.2  使用copy 從舊的datafile裡恢復

set width 70

info

 

其中7我們的舊的數據文件。

copy dba 7,131 to dba 6,131

 

copy剩下的7個塊:

copy dba 7,132 to dba 6,132

copy dba 7,133 to dba 6,133

copy dba 7,134 to dba 6,134

copy dba 7,135 to dba 6,135

copy dba 7,136 to dba 6,136

copy dba 7,137 to dba 6,137

copy dba 7,138 to dba 6,138

sum apply

 

alter system flush buffer_cache;

select * from scott.emp;

 

如果出不來就再做一遍copy,數據就回來了,或者重啟db數據也能回來。

但是

select count(*) from scott.emp;

 

需要再move一次。

alter table scott.emp move tablespace users;

alter index scott.pk_emp rebuild tablespace users;

alter table scott.emp move tablespace lxtbs;

alter index scott.pk_emp rebuild tablespace lxtbs;

 

select count(*) from scott.emp;

 

 

 

二.20  壞塊恢復(Rman Blockrecover)

有些人喜歡用bbed干一些弄簡為繁的事情,比如控制文件丟失,或者介質故障開不了庫,手工修改scn號開庫,或壞塊修復等。如果不是高手,最好不要用bbed進行高風險的工作,其實Oracle Rman提供了修復壞塊的工具block recover。但前提條件是你得有一個可用的備份存在。

最好重新做一次備份:

rman target /

delete noprompt backup;

delete noprompt copy;

list backup;

list copy;

 

 

run{

shutdown immediate;

startup mount;

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format '/home/oracle/rman_bak/cold_bak/%d_%s_%p.bak';

alter database open;

}

 

run{

sql 'alter system switch logfile';

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format '/home/oracle/rman_bak/hot_bak/%d_%s_%p_hot.bak' filesperset 3;

sql 'alter system switch logfile';

}

 

/************************重新登錄*************************/

sqlplus重新登錄:

quit

sqlplus '/as sysdba'

bbed重新登錄

quit

bbed parfile=/home/oracle/bbed.par

blockedit

/************************重新登錄*************************/

 

 

 

 

二.20.1  制作壞塊

bbed搞壞數據塊。

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.dept;

 

col segment_name for a10

select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='SCOTT';

 

加一變成129.

set dba 4,128 offset 0

p ktbbh

 

set dba 4,129 offset 0

p ktbbh

 

map

 

d /v offset 0 count 128

 

modify /x 12345678 offset 0     單步執行,或copy命令制作壞塊

sum apply

 

d /v offset 0 count 128

 

alter system flush buffer_cache;

select * from scott.dept;

 

--當Oracle 認為一個block 是corrupt時,會將該block的sequence number 標記為0xff.  該值可以通過seq_kcbh 屬性查看。

 

set dba 4,129

p kcbh

 

verify dba 4,129

 

二.20.2  恢復壞塊

不建議使用bbed來操作,rman有工具可以很好的處理bbed做的話就是revert回去就好了

BBED> revert

All changes made in this session will be rolled back. Proceed? (Y/N) Y

Reverted file '/oracle/app/oracle/oralhr/users01.dbf', block 520

Reverted file '/oracle/app/oracle/oralhr/users01.dbf', block 523

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

 

下邊來嘗試rman恢復:

rman target /

 

不要執行:只適用一兩個壞塊 blockrecover datafile 4 block 129,130;

如果有多個壞塊最好先校驗:

backup validate datafile 4

 

select * from v$database_block_corruption;

 

修復全部壞塊。

blockrecover corruption list;

 

alter system flush buffer_cache;

select * from scott.dept;

 

用rman的備份,修復了這個

select * from v$database_block_corruption; 

 

set dba 4,129

d /v offset 0 count 128

 

 

二.20.3  壞塊的影響

再次搞壞數據塊:

set dba 4,129

d /v offset 0 count 128

modify /x 12345678 offset 0

sum apply

 

 

alter system flush buffer_cache;

select * from scott.dept;

 

rman內操作:

backup datafile 4;

 

RMAN> backup datafile 4;

 

Starting backup at 2016-04-06 14:25:42

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/oracle/app/oracle/oralhr/users01.dbf

channel ORA_DISK_1: starting piece 1 at 2016-04-06 14:25:42

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2016 14:25:45

ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/app/oracle/oralhr/users01.dbf

 

col tablespace_name for a10

col owner for a10

col owner for a10

col PARTITION_NAME for a10

SELECT tablespace_name,segment_type,owner,segment_name,partition_name

FROM dba_extents

WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id+blocks-1;

輸入

4

129

 

run{

  set maxcorrupt for datafile 4 to 2;

  backup datafile 4 tag='2corruptblock';

}

 

這樣才能備份,但是推薦先修復壞塊:

backup validate datafile 4; 

 

blockrecover corruption list;

 

backup datafile 4;

 

 

 

 

About Me

..........................................................................................................................................................................................................                        

● 本文作者:小麥苗,只專注於數據庫的技術,更注重技術的運用

● 本文在ITpub(http://blog.itpub.net/26736162)、博客園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新

● QQ群:230161599 微信群:私聊

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2123465/ 本文博客園地址:http://www.cnblogs.com/lhrbest/p/5771282.html

● 本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取碼:ed9b)

● 小麥苗分享的其它資料:http://blog.itpub.net/26736162/viewspace-1624453/

● 聯系我請加QQ好友(642808185),注明添加緣由

● 於 2016-08-01 15:00~ 2016-08-01 19:00 在中行完成

● 【版權所有,文章允許轉載,但須以鏈接方式注明源地址,否則追究法律責任】

..........................................................................................................................................................................................................

長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號:xiaomaimiaolhr,學習最實用的數據庫技術。

 

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved