溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關oracle中dump logfile方法有哪些，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
dump logfile
有時工作需要可能需要dump redo logfile，幫助解決問題。
這個命令需要alter system系統權限。
數據庫可以是在nomount,mount,open狀態。
可以dump在線redo log，也可以dump歸檔文件。
甚至可以dump其他數據的文件，但是操作系統要一致。
1. To dump records based in DBA (Data Block Address)
2. To dump records based on RBA (Redo Block Address)
3. To dump records based on SCN
4. To dump records based on time
5. To dump records based on layer and opcode
6. Dump the file header information
7. Dump an entire log file:
ALTER SYSTEM DUMP LOGFILE 'filename'
DBA MIN fileno . blockno
DBA MAX fileno . blockno;
dump出指定范圍數據塊的redo記錄。
數據庫版本： SQL> !sqlplus -V
SQL*Plus: Release 11.2.0.1.0 Production
SQL> sho user USER is "MING" SQL> set line 300 SQL> col b for a30 SQL> select
dbms_rowid.rowid_relative_fno(rowid)
file_id,
FILE_ID BLOCK_ID ROWID A B ---------- ---------- ------------------ ---------- ------------------------------ 7 1683 AAASZ2AAHAAAAaTAAA 1 ming 更新一行： SQL> update test set b='mingshuomingshuo' where a=1;
1 row updated.
SQL> select
lg.group#,lg.sequence#,lg.bytes/1024/1024/1024 G,
GROUP# SEQUENCE# G MEMBERS MEMBER ARC STATUS ---- ---------- ---------- ---------- ------------------------- --- --------- 3 36 .048828125 1 /tpdata/oradata/ogg1/redo03.log NO CURRENT 2 35 .048828125 1 /tpdata/oradata/ogg1/redo02.log YES INACTIVE 1 34 .048828125 1 /tpdata/oradata/ogg1/redo01.log YES INACTIVE
SQL> alter system dump logfile '/tpdata/oradata/ogg1/redo03.log' dba min 7 1683 dba max 7 1683;
System altered.
SQL> select tracefile from v$process
where addr in (
TRACEFILE --------------------------------------------------------------------------------------------- /tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_2647.trc
[oracle@oggtest1 ~]$ more /tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_2647.trc Trace file /tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_2647.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /tpsys/app/oracle/product/11.2.0/db_1 System name: Linux Node name: oggtest1 Release: 2.6.32-358.el6.x86_64 Version: #1 SMP Tue Jan 29 11:47:41 EST 2013 Machine: x86_64 Instance name: ogg1 Redo thread mounted by this instance: 1 Oracle process number: 29 Unix process pid: 2647, image: oracle@oggtest1 (TNS V1-V3)
*** 2018-05-31 23:35:34.677 *** SESSION ID:(38.36) 2018-05-31 23:35:34.677 *** CLIENT ID:() 2018-05-31 23:35:34.677 *** SERVICE NAME:(SYS$USERS) 2018-05-31 23:35:34.677 *** MODULE NAME:(sqlplus@oggtest1 (TNS V1-V3)) 2018-05-31 23:35:34.677 *** ACTION NAME:() 2018-05-31 23:35:34.677
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DUMP OF REDO FROM FILE '/tpdata/oradata/ogg1/redo03.log' Opcodes *.* DBAs: (file # 7, block # 1683) thru (file # 7, block # 1683) data塊的起點和終點（thru），這里我只dump了1683號一個塊。
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=4132820558=0xf655d64e, Db Name='OGG1' Activation ID=4132850254=0xf6564a4e Control Seq=3130=0xc3a, File size=102400=0x19000 File Number=3, Blksiz=512, File Type=2 LOG descrip:"Thread 0001, Seq# 0000000036, SCN 0x000000158f45-0xffffffffffff" thread: 1 nab: 0xffffffff seq: 0x00000024 hws: 0x1 eot: 1 dis: 0 resetlogs count: 0x38d85a12 scn: 0x0000.000e6c20 (945184) prev resetlogs count: 0x296b946b scn: 0x0000.00000001 (1) Low scn: 0x0000.00158f45 (1412933) 05/31/2018 23:05:04 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 Enabled scn: 0x0000.000e6c20 (945184) 09/03/2017 05:28:50 Thread closed scn: 0x0000.00158f45 (1412933) 05/31/2018 23:05:04 Disk cksum: 0x471d Calc cksum: 0x471d Terminal recovery stop scn: 0x0000.00000000 Terminal recovery 01/01/1988 00:00:00 Most recent redo scn: 0x0000.00000000 Largest LWN: 0 blocks End-of-redo stream : No Unprotected mode Miscellaneous flags: 0x800000 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
REDO RECORD - Thread:1 RBA: 0x000024.00000ad6.0010 LEN: 0x01f4 VLD: 0x05 SQL> SELECT TO_NUMBER('24','XXXXXXXXXXXXXXXX') FROM DUAL; TO_NUMBER('23','XXXXXXXXXXXXXXXX')---------------------------------- 36 SQL> SELECT TO_NUMBER('00000ad6','XXXXXXXXXXXXXXXX') FROM DUAL; TO_NUMBER('1E3A','XXXXXXXXXXXXXXXX')------------------------------------ 277436號日志，redo塊number是2774，就是我們dump的redo log
SCN: 0x0000.0015939d SUBSCN: 1 05/31/2018 23:33:53 CHANGE #1 TYP:0 CLS:17 AFN:3
DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.0015932b ktudh redo: slt: 0x000e sqn: 0x00000355 flg: 0x0012 siz: 160 fbi: 0 uba: 0x00c00211.010e.03 pxid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:18 AFN:3
DBA:0x00c00211 OBJ:4294967295 SCN:0x0000.0015932a ktudb redo: siz: 160 spc: 7794 flg: 0x0012 seq: 0x010e rec: 0x03 xid: 0x0001.00e.00000355 ktubl redo: slt: 14 rci: 0 opc: 11.1 [objn: 75382 objd: 75382 tsn: 8] Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00c00211.010e.02 prev ctl max cmt scn: 0x0000.00158bd7 prev tx cmt scn: 0x0000.00158be6 txn start scn: 0xffff.ffffffff logon user: 95 prev brb: 12583426 prev bcl: 0 BuExt idx: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: URP row dependencies Disabled ##下面這部分是修改前的數據,上面和下面這一部分其實是undo的信息 xtype: XA flags: 0x00000000 bdba: 0x01c00693 hdba: 0x01c00692 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: -12 col 1: [ 4] 6d 69 6e 67 ##4是長度，6d 69 6e 67是修改前的值 修改前的值:SQL> select utl_raw.cast_to_varchar2(replace('6d,69,6e,67',',')) value from dual; VALUE------------------------------------------------------------------------------------------------------ming
CHANGE #3 TYP:0 CLS:1 AFN:7 DBA:0x01c00693 OBJ:75382 SCN:0x0000.0015673a SEQ:1 OP:11.5 ENC:0 RBL:0 SQL> SELECT TO_NUMBER('01c00693','XXXXXXXXXXXXXXXX') FROM DUAL; TO_NUMBER('01C00693','XXXXXXXXXXXXXXXX')---------------------------------------- 29361811 SQL> select2 dbms_utility.data_block_address_block(29361811) "BLOCK",3 dbms_utility.data_block_address_file(29361811) "FILE"4 from dual; BLOCK FILE---------- ---------- 1683 77號數據文件的1693塊就是我dump的塊。 75382就是我們操作的test表的object_id OP code是11.5，在DML的操作代碼中，11.5代表update row piece
KTB Redo op: 0x01 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: F xid: 0x0001.00e.00000355 uba: 0x00c00211.010e.03 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01c00693 hdba: 0x01c00692 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0 ncol: 2 nnew: 1 size: 12 col 1: [16] 6d 69 6e 67 73 68 75 6f 6d 69 6e 67 73 68 75 6f Bdba是更新的塊的地址，所以跟DBA:0x01c00693一樣也就是顯而易見的了；Hdba是更新的塊所在的段的地址。SQL> SELECT TO_NUMBER('01c00692','XXXXXXXXXXXXXXXX') FROM DUAL; TO_NUMBER('01C00692','XXXXXXXXXXXXXXXX')---------------------------------------- 29361810SQL> select 2 dbms_utility.data_block_address_block(29361810) "BLOCK", 3 dbms_utility.data_block_address_file(29361810) "FILE" 4 from dual; BLOCK FILE---------- ---------- 1682 7TEST表所在的段的信息：SQL> select owner,segment_name,segment_type,header_file,header_block from dba_segments where segment_name='TEST' and tablespace_name='TEST';OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK------- -------------- ------------ ----------- ------------MING TEST TABLE 7 1682兩者是一致的 itli: 2 該事務正在使用第二個事務槽。tabn: 0 該塊上第一個表。Flag是0x2c，代表沒有出現行遷移，包括first data piece,last data piecehe head piece of row.ncol: 2 該記錄有2行nnew：1 修改了1列（b那一列）size: 12 修改列長度增加了12（從4變成16）修改后的值：SQL> select utl_raw.cast_to_varchar2(replace('6d,69,6e,67,73,68,75,6f,6d,69,6e,67,73,68,75,6f',',')) valuefrom dual; VALUE-----------------------------------------------------------------------------------------------------------------------------mingshuomingshuo
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0 session number = 1 serial number = 15 執行事務的會話信息：SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID=( select userenv('SID') FROM DUAL); SID SERIAL#---------- ---------- 1 15
transaction name = version 186646784 audit sessionid 151083 Client Id = END OF REDO DUMP ----- Redo read statistics for thread 1 ----- Read rate (SYNC): 1419Kb in 0.01s => 138.57 Mb/sec Total redo bytes: 2047Kb Longest record: 13Kb, moves: 1/2688 moved: 0Mb (0%) Longest LWN: 504Kb, reads: 240 Last redo scn: 0x0000.001593c8 (1414088) Change vector header moves = 261/4848 (5%) ---------------------------------------------- |
ALTER SYSTEM DUMP LOGFILE 'filename'
RBA MIN seqno . blockno
RBA MAX seqno . blockno;
RBA實際就是塊的變化在redo log中的記錄位置。所以這里是dump出指定范圍的redo塊的redo記錄。
ALTER SYSTEM DUMP LOGFILE 'filename'
SCN MIN minscn
SCN MAX maxscn;
Dump出指定范圍SCN的redo記錄
ALTER SYSTEM DUMP LOGFILE 'filename'
TIME MIN value
TIME MAX value;
這里的value實際是時間范圍,確切說是redo dump time。
下面給出一個時間轉變為redo dump time的腳本：
/* Formatted on2011/8/8 23:00:53 (QP5 v5.163.1008.3004) */ SET ECHO OFF REM NAME: TFSTM2RD.SQL REMUSAGE:"@path/tfstm2rd" REM------------------------------------------------------------------------ REM REQUIREMENTS: REM None. REM------------------------------------------------------------------------ REM AUTHOR: REM Anonymous REM Copyright 1996, Orqacle Corporation REM------------------------------------------------------------------------ REM PURPOSE: REM Converts a standard date into redo dump timeformat. REM------------------------------------------------------------------------ REM EXAMPLE: REM Enter day (DD/MM/YYYY) ?08/07/1996 REM Enter time (HH24:MI:SS) ? 12:05:05 REM REM REDO_YEAR REDO_MONTH REDO_DAY REDO_HOURREDO_MIN REDO_SEC REM --------- ---------- -------- ----------------- -------- REM 1996 7 8 12 5 5 REM REM EDO_TIME REM ---------- REM 273845105 REM REM------------------------------------------------------------------------ REM DISCLAIMER: REM This script. is provided for educational purposesonly. It is NOT REM supported by Oracle World Wide TechnicalSupport. REM The script. has been tested and appears towork as intended. REM You should always run new scripts on a testinstance initially. REM------------------------------------------------------------------------ REM Main text ofscript. follows:
UNDEFINE redo_day UNDEFINE redo_hhmiss
ACCEPT redo_day PROMPT "Enter day (DD/MM/YYYY) ? " ACCEPT redo_hhmiss PROMPT "Enter time (HH24:MI:SS) ? "
COLUMN redo_year NEW_VALUE redo_year FORMAT 9999 COLUMN redo_month NEW_VALUE redo_month FORMAT 9999 COLUMN redo_day NEW_VALUE redo_day FORMAT9999 COLUMN redo_hour NEW_VALUE redo_hour FORMAT 9999 COLUMN redo_min NEW_VALUE redo_min FORMAT9999 COLUMN redo_sec NEW_VALUE redo_sec FORMAT9999 COLUMN redo_time NEW_VALUE redo_time
SETVERIFY OFF
SELECT TO_NUMBER ( TO_CHAR ( TO_DATE ('&redo_day&redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'), 'YYYY')) redo_year, TO_NUMBER ( TO_CHAR ( TO_DATE ('&redo_day&redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'), 'MM')) redo_month, TO_NUMBER ( TO_CHAR ( TO_DATE ('&redo_day&redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'), 'DD')) redo_day, TO_NUMBER ( TO_CHAR ( TO_DATE ('&redo_day&redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'), 'HH24')) redo_hour, TO_NUMBER ( TO_CHAR ( TO_DATE ('&redo_day&redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'), 'MI')) redo_min, TO_NUMBER ( TO_CHAR ( TO_DATE ('&redo_day&redo_hhmiss', 'DD/MM/YYYY HH24:MI:SS'), 'SS')) redo_sec FROM DUAL;
SELECT ( ( ( ( ( (&redo_year - 1988)) * 12 + (&redo_month - 1)) * 31 + (&redo_day - 1)) * 24 + (&redo_hour)) * 60 + (&redo_min)) * 60 + (&redo_sec) redo_time FROM DUAL; |
ALTER SYSTEM DUMP LOGFILE 'filename'
LAYER value
OPCODE value;
Layer和opcode是用來dump出特定類型的redo記錄的。
Dump出所有在線日志的header信息：
alter session set events 'immediate trace name redohdr level 10';
dump出歸檔日志的header信息：
ALTER SYSTEM DUMP LOGFILE 'filename' RBA MIN 1 1 RBA MAX 1 1;
在線日志：
SQL> alter session set events 'immediate trace name redohdr level 10'; Session altered.
[oracle@oggtest1 trace]$ more ogg1_ora_3048.trc Trace file /tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_3048.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /tpsys/app/oracle/product/11.2.0/db_1 System name: Linux Node name: oggtest1 Release: 2.6.32-358.el6.x86_64 Version: #1 SMP Tue Jan 29 11:47:41 EST 2013 Machine: x86_64 Instance name: ogg1 Redo thread mounted by this instance: 1 Oracle process number: 29 Unix process pid: 3048, image: oracle@oggtest1 (TNS V1-V3)
*** 2018-06-01 01:31:40.458 *** SESSION ID:(38.38) 2018-06-01 01:31:40.458 *** CLIENT ID:() 2018-06-01 01:31:40.458 *** SERVICE NAME:(SYS$USERS) 2018-06-01 01:31:40.458 *** MODULE NAME:(sqlplus@oggtest1 (TNS V1-V3)) 2018-06-01 01:31:40.458 *** ACTION NAME:() 2018-06-01 01:31:40.458
DUMP OF LOG FILES: 3 logs in database LOG FILE #1: name #3: /tpdata/oradata/ogg1/redo01.log Thread 1 redo log links: forward: 2 backward: 0 siz: 0x19000 seq: 0x00000022 hws: 0x3 bsz: 512 nab: 0x12c7e flg: 0x1 dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00150d87 Low scn: 0x0000.00151fff 05/30/2018 21:31:05 Next scn: 0x0000.00154ded 05/30/2018 22:54:33 FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=4132820558=0xf655d64e, Db Name='OGG1' Activation ID=4132850254=0xf6564a4e Control Seq=3105=0xc21, File size=102400=0x19000 File Number=1, Blksiz=512, File Type=2 LOG redo log key is 35a83c3bcc89f58a38e79d8babc0cd redo log key flag is 5 descrip:"Thread 0001, Seq# 0000000034, SCN 0x000000151fff-0x000000154ded" thread: 1 nab: 0x12c7e seq: 0x00000022 hws: 0x3 eot: 0 dis: 0 reset logs count: 0x38d85a12 scn: 0x0000.000e6c20 Low scn: 0x0000.00151fff 05/30/2018 21:31:05 Next scn: 0x0000.00154ded 05/30/2018 22:54:33 Enabled scn: 0x0000.000e6c20 09/03/2017 05:28:50 Thread closed scn: 0x0000.00151fff 05/30/2018 21:31:05 Disk cksum: 0xdeda Calc cksum: 0xdeda Terminal Recovery Stop scn: 0x0000.00000000 Terminal Recovery Stamp 01/01/1988 00:00:00 Most recent redo scn: 0x0000.00000000 Largest LWN: 2048 blocks Miscellaneous flags: 0x800000 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000 LOG FILE #2: name #2: /tpdata/oradata/ogg1/redo02.log Thread 1 redo log links: forward: 3 backward: 1 siz: 0x19000 seq: 0x00000023 hws: 0x5 bsz: 512 nab: 0x13ea0 flg: 0x1 dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00151fff Low scn: 0x0000.00154ded 05/30/2018 22:54:33 Next scn: 0x0000.00158f45 05/31/2018 23:05:04 FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=4132820558=0xf655d64e, Db Name='OGG1' Activation ID=4132850254=0xf6564a4e Control Seq=3130=0xc3a, File size=102400=0x19000 File Number=2, Blksiz=512, File Type=2 LOG redo log key is 65a21627df0616da4e0eab04481274 redo log key flag is 5 descrip:"Thread 0001, Seq# 0000000035, SCN 0x000000154ded-0x000000158f45" thread: 1 nab: 0x13ea0 seq: 0x00000023 hws: 0x5 eot: 0 dis: 0 reset logs count: 0x38d85a12 scn: 0x0000.000e6c20 Low scn: 0x0000.00154ded 05/30/2018 22:54:33 Next scn: 0x0000.00158f45 05/31/2018 23:05:04 Enabled scn: 0x0000.000e6c20 09/03/2017 05:28:50 Thread closed scn: 0x0000.0015680b 05/31/2018 02:12:09 Disk cksum: 0xb8e7 Calc cksum: 0xb8e7 Terminal Recovery Stop scn: 0x0000.00000000 Terminal Recovery Stamp 01/01/1988 00:00:00 Most recent redo scn: 0x0000.00000000 Largest LWN: 2048 blocks Miscellaneous flags: 0x800000 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000 LOG FILE #3: name #1: /tpdata/oradata/ogg1/redo03.log Thread 1 redo log links: forward: 0 backward: 2 siz: 0x19000 seq: 0x00000024 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00154ded Low scn: 0x0000.00158f45 05/31/2018 23:05:04 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 FILE HEADER: Compatibility Vsn = 186646528=0xb200000 Db ID=4132820558=0xf655d64e, Db Name='OGG1' Activation ID=4132850254=0xf6564a4e Control Seq=3130=0xc3a, File size=102400=0x19000 File Number=3, Blksiz=512, File Type=2 LOG redo log key is 822c416515657eb0b48bc9b93c403cc8 redo log key flag is 5 descrip:"Thread 0001, Seq# 0000000036, SCN 0x000000158f45-0xffffffffffff" thread: 1 nab: 0xffffffff seq: 0x00000024 hws: 0x1 eot: 1 dis: 0 reset logs count: 0x38d85a12 scn: 0x0000.000e6c20 Low scn: 0x0000.00158f45 05/31/2018 23:05:04 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 Enabled scn: 0x0000.000e6c20 09/03/2017 05:28:50 Thread closed scn: 0x0000.00158f45 05/31/2018 23:05:04 Disk cksum: 0x471d Calc cksum: 0x471d Terminal Recovery Stop scn: 0x0000.00000000 Terminal Recovery Stamp 01/01/1988 00:00:00 Most recent redo scn: 0x0000.00000000 Largest LWN: 0 blocks Miscellaneous flags: 0x800000 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000 |
ALTER SYSTEM DUMP LOGFILE 'filename';
Dump出指定日志的所有內容。
關于“oracle中dump logfile方法有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
