这篇文章将为大家详细讲解有关oracle中dump logfile方法有哪些,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
dump logfile
有时工作需要可能需要dump redo logfile,帮助解决问题。
这个命令需要alter system系统权限。
数据库可以是在nomount,mount,open状态。
可以dump在线redo log,也可以dump归档文件。
甚至可以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:
1.To dump records based in DBA (Data Block Address)
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
FILE_ID BLOCK_ID ROWID A B ———- ———- —————— 7 1683 更新一行: SQL> update test set
1 row updated.
SQL> select
GROUP# SEQUENCE# G MEMBERS MEMBER ARC STATUS —- ———- ———- ———- 3 36 .048828125 1 2 35 .048828125 1 1 34 .048828125 1 /tpdata/oradata/ogg1/redo01.log YES INACTIVE
SQL> alter system dump logfile
System altered.
SQL> select tracefile from v$process
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 Oracle Database 11g Enterprise Edition With the Partitioning, OLAP, Data Mining ORACLE_HOME = 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:
*** 2018-05-31 23:35:34.677 *** SESSION ID:(38.36) 2018-05-31 *** CLIENT ID:() 2018-05-31 23:35:34.677 *** SERVICE NAME:(SYS$USERS) 2018-05-31 *** MODULE NAME:(sqlplus@oggtest1 (TNS *** ACTION NAME:() 2018-05-31
Log read is SYNCHRONOUS though
DUMP OF REDO FROM FILE Opcodes *.* DBAs: (file # 7, block # 1683) thru (file # data块的起点和终点(thru),这里我只dump了1683号一个块。
RBAs: 0x000000.00000000.0000 thru SCNs: scn: 0x0000.00000000 thru scn: 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, thread: 1 nab: 0xffffffff seq: 0x00000024 resetlogs count: 0x38d85a12 scn: prev resetlogs count: 0x296b946b scn: 0x0000.00000001 Low scn: 0x0000.00158f45 (1412933) 05/31/2018 23:05:04 Next scn: 0xffff.ffffffff 01/01/1988 Enabled scn: 0x0000.000e6c20 (945184) Thread closed scn: 0x0000.00158f45 (1412933) 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,
REDO RECORD – Thread:1 RBA: 0x000024.00000ad6.0010 LEN: 0x01f4 VLD: 0x05 SQL>
SCN: 0x0000.0015939d SUBSCN: 1 05/31/2018 23:33:53 CHANGE #1 TYP:0 CLS:17 AFN:3 ktudh redo: slt: 0x000e sqn: 0x00000355 uba: 0x00c00211.010e.03 pxid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:18 AFN:3 ktudb redo: siz: 160 spc: 7794 flg: xid: 0x0001.00e.00000355 ktubl redo: slt: 14 rci: 0 opc: 11.1 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 ##下面这部分是修改前的数据,上面和下面这一部分其实是undo的信息 xtype: XA flags: 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 修改前的值:SQL> select
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>
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 xtype: XA flags: 0x00000000 bdba: 0x01c00693 hdba: 0x01c00692 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ncol: 2 nnew: 1 size: 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
CHANGE #4 MEDIA RECOVERY MARKER session number = 1 serial number = 15 执行事务的会话信息:SQL>
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 => Total redo bytes: 2047Kb Longest record: Longest LWN: 504Kb, reads: 240 Last redo scn: 0x0000.001593c8 (1414088) Change vector header moves = 261/4848 ———————————————- |
2. To dump records based on RBA (Redo Block Address)
ALTER SYSTEM DUMP LOGFILE 'filename'
RBA MIN seqno . blockno
RBA MAX seqno . blockno;
RBA实际就是块的变化在redo log中的记录位置。所以这里是dump出指定范围的redo块的redo记录。
3. To dump records based on SCN
ALTER SYSTEM DUMP LOGFILE 'filename'
SCN MIN minscn
SCN MAX maxscn;
Dump出指定范围SCN的redo记录
4. To dump records based on time
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 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 ACCEPT redo_hhmiss PROMPT "Enter time
COLUMN redo_year NEW_VALUE redo_year COLUMN redo_month NEW_VALUE redo_month COLUMN redo_day NEW_VALUE redo_day FORMAT9999 COLUMN redo_hour NEW_VALUE redo_hour 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 'YYYY')) redo_year, TO_NUMBER ( TO_CHAR ( TO_DATE 'MM')) redo_month, TO_NUMBER ( TO_CHAR ( TO_DATE 'DD')) redo_day, TO_NUMBER ( TO_CHAR ( TO_DATE 'HH24')) redo_hour, TO_NUMBER ( TO_CHAR ( TO_DATE 'MI')) redo_min, TO_NUMBER ( TO_CHAR ( TO_DATE 'SS')) redo_sec FROM DUAL;
SELECT ( ( ( ( ( (&redo_year – 1988)) + (&redo_day – 1)) * 24 + (&redo_hour)) * 60 + (&redo_min)) * 60 + (&redo_sec) redo_time FROM |
5. To dump records based on layer and opcode
ALTER SYSTEM DUMP LOGFILE 'filename'
LAYER value
OPCODE value;
Layer和opcode是用来dump出特定类型的redo记录的。
6. Dump the file header information
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 Session altered.
[oracle@oggtest1 trace]$ more Trace file Oracle Database 11g Enterprise Edition With the Partitioning, OLAP, Data Mining ORACLE_HOME = 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:
*** 2018-06-01 01:31:40.458 *** SESSION ID:(38.38) 2018-06-01 *** CLIENT ID:() 2018-06-01 01:31:40.458 *** SERVICE NAME:(SYS$USERS) 2018-06-01 *** MODULE NAME:(sqlplus@oggtest1 (TNS *** ACTION NAME:() 2018-06-01
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 siz: 0x19000 seq: 0x00000022 hws: 0x3 bsz: Archive links: fwrd: 0 back: 0 Prev scn: 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 redo log key flag is 5 descrip:"Thread 0001, Seq# 0000000034, thread: 1 nab: 0x12c7e seq: 0x00000022 hws: reset logs count: 0x38d85a12 scn: Low scn: 0x0000.00151fff 05/30/2018 21:31:05 Next scn: 0x0000.00154ded 05/30/2018 Enabled scn: 0x0000.000e6c20 09/03/2017 Thread closed scn: 0x0000.00151fff Disk cksum: 0xdeda Calc cksum: 0xdeda Terminal 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, LOG FILE #2: name #2: /tpdata/oradata/ogg1/redo02.log Thread 1 redo log links: forward: 3 siz: 0x19000 seq: 0x00000023 hws: 0x5 bsz: Archive links: fwrd: 0 back: 0 Prev scn: Low scn: 0x0000.00154ded 05/30/2018 22:54:33 Next scn: 0x0000.00158f45 05/31/2018 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 redo log key flag is 5 descrip:"Thread 0001, Seq# 0000000035, thread: 1 nab: 0x13ea0 seq: 0x00000023 hws: reset logs count: 0x38d85a12 scn: Low scn: 0x0000.00154ded 05/30/2018 22:54:33 Next scn: 0x0000.00158f45 05/31/2018 Enabled scn: 0x0000.000e6c20 09/03/2017 Thread closed scn: 0x0000.0015680b 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, LOG FILE #3: name #1: /tpdata/oradata/ogg1/redo03.log Thread 1 redo log links: forward: 0 siz: 0x19000 seq: 0x00000024 hws: 0x1 bsz: Archive links: fwrd: 0 back: 0 Prev scn: Low scn: 0x0000.00158f45 05/31/2018 23:05:04 Next scn: 0xffff.ffffffff 01/01/1988 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 redo log key flag is 5 descrip:"Thread 0001, Seq# 0000000036, thread: 1 nab: 0xffffffff seq: 0x00000024 reset logs count: 0x38d85a12 scn: Low scn: 0x0000.00158f45 05/31/2018 23:05:04 Next scn: 0xffff.ffffffff 01/01/1988 Enabled scn: 0x0000.000e6c20 09/03/2017 Thread closed scn: 0x0000.00158f45 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, |
7. Dump an entire log file:
ALTER SYSTEM DUMP LOGFILE 'filename';
Dump出指定日志的所有内容。
关于“oracle中dump logfile方法有哪些”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。
原创文章,作者:306829225,如若转载,请注明出处:https://blog.ytso.com/205122.html