oracle資料庫redo檔案的blocksize

charsi發表於2016-07-31
Oracle資料庫BloC
Oracle資料庫中，redo檔案的block大小（blocksize）是跟平臺相關的，因此redo檔案的block size是無法改變的

至於如何檢視redo檔案的blocksize，有兩種方法：
1.直接查詢v$log檢視
在Oracle 11g之後，可以直接通過查詢v$log.blocksize就可以查到
如下：

點選(此處)摺疊或開啟

  1. SQL> select * from v$log;

  3.     GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
  4. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
  5.          1 1 289 52428800 512 1 YES INACTIVE 2686107 30-JUL-16 2706826 30-JUL-16
  6.          2 1 290 52428800 512 1 YES INACTIVE 2706826 30-JUL-16 2727425 30-JUL-16
  7.          3 1 291 52428800 512 1 NO CURRENT 2727425 30-JUL-16 2.8147E+14
可以看到Linux平臺下的BLOCKSIZE為512

2.dump logfile檢視blocksize大小
方法如下：
a. ALTER SESSION SET EVENTS 'immediate trace name redohdr level 10';
b.在udump目錄下檢視相應的trc檔案
c.在trc檔案中找“bsz=”內容，即是redolog檔案的blocksize

點選(此處)摺疊或開啟

  1. *** 2016-07-31 07:35:36.164
  2. DUMP OF LOG FILES: 3 logs in database
  3. LOG FILE #1:
  4.   name #3: /home/oracle/app/oracle/oradata/orcl/redo01.log
  5.  Thread 1 redo log links: forward: 2 backward: 0
  6.  siz: 0x19000 seq: 0x00000121 hws: 0x7 bsz: 512 nab: 0x1d0 flg: 0x1 dup: 1
  7.  Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0028ac04
  8.  Low scn: 0x0000.0028fc9b 07/30/2016 20:46:26
  9.  Next scn: 0x0000.00294d8a 07/30/2016 20:49:40
  10.  FILE HEADER:
  11.         Compatibility Vsn = 186647552=0xb200400
  12.         Db ID=1434730535=0x55844027, Db Name='ORCL'
  13.         Activation ID=1434687783=0x55839927
  14.         Control Seq=6323=0x18b3, File size=102400=0x19000
  15.         File Number=1, Blksiz=512, File Type=2 LOG
  16.  Format ID is 2
  17.  redo log key is 214e32a54a14bea8f0de67f8c53a9f
  18.  redo log key flag is 5
  19.  descrip:"Thread 0001, Seq# 0000000289, SCN 0x00000028fc9b-0x000000294d8a"
  20.  thread: 1 nab: 0x1d0 seq: 0x00000121 hws: 0x7 eot: 0 dis: 0
  21.  reset logs count: 0x360ef4a8 scn: 0x0000.000e2006
  22.  Low scn: 0x0000.0028fc9b 07/30/2016 20:46:26
  23.  Next scn: 0x0000.00294d8a 07/30/2016 20:49:40
  24.  Enabled scn: 0x0000.000e2006 03/20/2016 02:30:00
  25.  Thread closed scn: 0x0000.00294d88 07/30/2016 20:49:25
  26.  Disk cksum: 0x2d4 Calc cksum: 0x2d4
  27.  Terminal Recovery Stop scn: 0x0000.00000000
  28.  Terminal Recovery Stamp 01/01/1988 00:00:00
  29.  Most recent redo scn: 0x0000.00000000
  30.  Largest LWN: 0 blocks
  31.  Miscellaneous flags: 0x800000
  32.  Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
  33.  Zero blocks: 8
  34.  Enabled redo threads: 1
  35. LOG FILE #2:
  36.   name #2: /home/oracle/app/oracle/oradata/orcl/redo02.log
如上所示，blocksize為512

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-2122778/，如需轉載，請註明出處，否則將追究法律責任。

相關文章