oracle中dump logfile方法有哪些

这篇文章将为大家详细讲解有关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 SCN4. To dump records based on time5. To dump records based on layer and
opcode6. Dump the file header information7. Dump an entire log file:ALTER SYSTEM DUMP LOGFILE ‘filename’DBA
MIN fileno . blockno DBA MAX fileno . blockno;dump出指定范围数据块的redo记录。数据库版本：SQL> !sqlplus -VSQL*Plus: Release 11.2.0.1.0 ProductionSQL> sho userUSER is “MING”SQL> set line 300SQL> col b for a30SQL> select
dbms_rowid.rowid_relative_fno(rowid)
file_id,
dbms_rowid.rowid_block_number(rowid) block_id,rowid,
test.* from
test; 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,
lg.members,lgf.member,lg.archived,
lg.status from v$log lg,v$logfile lgf where
lg.group#=lgf.group#;GROUP# SEQUENCE# G MEMBERS MEMBER ARC STATUS—- ———- ———- ———-
————————- — ———3 36 .048828125 1
/tpdata/oradata/ogg1/redo03.log NO CURRENT2 35 .048828125 1
/tpdata/oradata/ogg1/redo02.log YES INACTIVE1 34 .048828125 1 /tpdata/oradata/ogg1/redo01.log YES INACTIVESQL> 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 (
select paddr from v$session where sid in (select sid from
v$mystat));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.trcTrace file
/tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_2647.trcOracle Database 11g Enterprise Edition
Release 11.2.0.1.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining
and Real Application Testing optionsORACLE_HOME =
/tpsys/app/oracle/product/11.2.0/db_1System name: LinuxNode name: oggtest1Release: 2.6.32-358.el6.x86_64Version: #1 SMP Tue Jan 29 11:47:41 EST 2013Machine: x86_64Instance name: ogg1Redo thread mounted by this instance: 1Oracle process number: 29Unix 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.677Log 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)RBAs: 0x000000.00000000.0000 thru
0xffffffff.ffffffff.ffffSCNs: scn: 0x0000.00000000 thru scn:
0xffff.ffffffffTimes: creation thru eternityFILE 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 LOGdescrip:”Thread 0001, Seq# 0000000036,
SCN 0x000000158f45-0xffffffffffff”thread: 1 nab: 0xffffffff seq: 0x00000024
hws: 0x1 eot: 1 dis: 0resetlogs count: 0x38d85a12 scn:
0x0000.000e6c20 (945184)prev resetlogs count: 0x296b946b scn: 0x0000.00000001
(1)Low scn: 0x0000.00158f45 (1412933) 05/31/2018 23:05:04Next scn: 0xffff.ffffffff 01/01/1988
00:00:00Enabled scn: 0x0000.000e6c20 (945184)
09/03/2017 05:28:50Thread closed scn: 0x0000.00158f45 (1412933)
05/31/2018 23:05:04Disk cksum: 0x471d Calc cksum: 0x471dTerminal recovery stop scn: 0x0000.00000000Terminal recovery 01/01/1988 00:00:00Most recent redo scn: 0x0000.00000000Largest LWN: 0 blocksEnd-of-redo stream : NoUnprotected modeMiscellaneous flags: 0x800000Thread internal enable indicator: thr: 0,
seq: 0 scn: 0x0000.00000000REDO RECORD – Thread:1 RBA: 0x000024.00000ad6.0010 LEN: 0x01f4 VLD: 0x05SCN: 0x0000.0015939d SUBSCN: 1 05/31/2018 23:33:53CHANGE #1 TYP:0 CLS:17 AFN:3
DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.0015932b
SEQ:1 OP:5.2 ENC:0 RBL:0ktudh redo: slt: 0x000e sqn: 0x00000355
flg: 0x0012 siz: 160 fbi: 0 uba: 0x00c00211.010e.03 pxid: 0x0000.000.00000000CHANGE #2 TYP:0 CLS:18 AFN:3
DBA:0x00c00211 OBJ:4294967295 SCN:0x0000.0015932a
SEQ:1 OP:5.1 ENC:0 RBL:0ktudb redo: siz: 160 spc: 7794 flg:
0x0012 seq: 0x010e rec: 0x03 xid: 0x0001.00e.00000355ktubl redo: slt: 14 rci: 0 opc: 11.1
[objn: 75382 objd: 75382 tsn: 8]Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: No 0x00000000 prev ctl uba: 0x00c00211.010e.02prev ctl max cmt scn: 0x0000.00158bd7 prev tx cmt scn: 0x0000.00158be6txn start scn: 0xffff.ffffffff logon user: 95 prev brb: 12583426 prev bcl: 0 BuExt idx: 0
flg2: 0KDO undo record:KTB Redoop: 0x03 ver: 0x01compat bit: 4 (post-11) padding: 1op: ZKDO Op code: URP row dependencies
Disabled##下面这部分是修改前的数据,上面和下面这一部分其实是undo的信息 xtype: XA flags:
0x00000000 bdba: 0x01c00693 hdba: 0x01c00692itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: -12col 1: [ 4] 6d 69 6e 67 ##4是长度，6d 69 6e
67是修改前的值CHANGE #3 TYP:0 CLS:1 AFN:7 DBA:0x01c00693 OBJ:75382 SCN:0x0000.0015673a SEQ:1 OP:11.5 ENC:0 RBL:0KTB Redoop: 0x01 ver: 0x01compat bit: 4 (post-11) padding: 1op: F xid: 0x0001.00e.00000355 uba: 0x00c00211.010e.03KDO Op code: URP row dependencies
Disabled xtype: XA flags: 0x00000000 bdba: 0x01c00693 hdba: 0x01c00692itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2
ckix: 0ncol: 2 nnew: 1 size:
12col 1: [16] 免费主机域名6d 69 6e 67 73 68 75 6f 6d 69 6e 67 73 68 75 6fCHANGE #4 MEDIA RECOVERY MARKER
SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0session number = 1serial number = 15transaction name =version 186646784audit sessionid 151083Client Id =END OF REDO DUMP—– Redo read statistics for thread 1
—–Read rate (SYNC): 1419Kb in 0.01s =>
138.57 Mb/secTotal redo bytes: 2047Kb Longest record:
13Kb, moves: 1/2688 moved: 0Mb (0%)Longest LWN: 504Kb, reads: 240Last 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记录。Dump出指定范围SCN的redo记录这里的value实际是时间范围,确切说是redo dump time。下面给出一个时间转变为redo dump time的脚本：/* Formatted on2011/8/8 23:00:53 (QP5
v5.163.1008.3004) */SET ECHO OFFREM NAME: TFSTM2RD.SQLREMUSAGE:”@path/tfstm2rd”REM————————————————————————REM REQUIREMENTS:REM None.REM————————————————————————REM AUTHOR:REM AnonymousREM Copyright 1996, Orqacle CorporationREM————————————————————————REM PURPOSE:REM Converts a standard date into redo dump timeformat.REM————————————————————————REM EXAMPLE:REM Enter day (DD/MM/YYYY) ?08/07/1996REM Enter time (HH24:MI:SS) ? 12:05:05REMREM REDO_YEAR REDO_MONTH REDO_DAY REDO_HOURREDO_MIN REDO_SECREM ——— ———- ——– —————– ——–REM 1996 7 8 12 5 5REMREM EDO_TIMEREM ———-REM 273845105REMREM————————————————————————REM DISCLAIMER:REM This script. is provided for educational purposesonly. It is NOTREM 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_dayUNDEFINE redo_hhmissACCEPT 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 9999COLUMN redo_month NEW_VALUE redo_month
FORMAT 9999COLUMN redo_day NEW_VALUE redo_day FORMAT9999COLUMN redo_hour NEW_VALUE redo_hour
FORMAT 9999COLUMN redo_min NEW_VALUE redo_min FORMAT9999COLUMN redo_sec NEW_VALUE redo_sec FORMAT9999COLUMN redo_time NEW_VALUE redo_timeSETVERIFY OFFSELECT 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;Layer和opcode是用来dump出特定类型的redo记录的。SQL> alter session set events
‘immediate trace name redohdr level 10′;Session altered.[oracle@oggtest1 trace]$ more
ogg1_ora_3048.trcTrace file
/tpsys/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_3048.trcOracle Database 11g Enterprise Edition
Release 11.2.0.1.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining
and Real Application Testing optionsORACLE_HOME =
/tpsys/app/oracle/product/11.2.0/db_1System name: LinuxNode name: oggtest1Release: 2.6.32-358.el6.x86_64Version: #1 SMP Tue Jan 29 11:47:41 EST 2013Machine: x86_64Instance name: ogg1Redo thread mounted by this instance: 1Oracle process number: 29Unix 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.458DUMP OF LOG FILES: 3 logs in databaseLOG FILE #1: name #3: /tpdata/oradata/ogg1/redo01.logThread 1 redo log links: forward: 2
backward: 0siz: 0x19000 seq: 0x00000022 hws: 0x3 bsz:
512 nab: 0x12c7e flg: 0x1 dup: 1Archive links: fwrd: 0 back: 0 Prev scn:
0x0000.00150d87Low scn: 0x0000.00151fff 05/30/2018 21:31:05Next scn: 0x0000.00154ded 05/30/2018 22:54:33FILE 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 LOGredo log key is
35a83c3bcc89f58a38e79d8babc0cdredo log key flag is 5descrip:”Thread 0001, Seq# 0000000034,
SCN 0x000000151fff-0x000000154ded”thread: 1 nab: 0x12c7e seq: 0x00000022 hws:
0x3 eot: 0 dis: 0reset logs count: 0x38d85a12 scn:
0x0000.000e6c20Low scn: 0x0000.00151fff 05/30/2018 21:31:05Next scn: 0x0000.00154ded 05/30/2018
22:54:33Enabled scn: 0x0000.000e6c20 09/03/2017
05:28:50Thread closed scn: 0x0000.00151fff
05/30/2018 21:31:05Disk cksum: 0xdeda Calc cksum: 0xdedaTerminal
Recovery Stop scn: 0x0000.00000000Terminal Recovery Stamp 01/01/1988 00:00:00Most recent redo scn: 0x0000.00000000Largest LWN: 2048 blocksMiscellaneous flags: 0x800000Thread internal enable indicator: thr: 0,
seq: 0 scn: 0x0000.00000000LOG FILE #2: name #2: /tpdata/oradata/ogg1/redo02.logThread 1 redo log links: forward: 3
backward: 1siz: 0x19000 seq: 0x00000023 hws: 0x5 bsz:
512 nab: 0x13ea0 flg: 0x1 dup: 1Archive links: fwrd: 0 back: 0 Prev scn:
0x0000.00151fffLow scn: 0x0000.00154ded 05/30/2018 22:54:33Next scn: 0x0000.00158f45 05/31/2018
23:05:04FILE 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 LOGredo log key is
65a21627df0616da4e0eab04481274redo log key flag is 5descrip:”Thread 0001, Seq# 0000000035,
SCN 0x000000154ded-0x000000158f45″thread: 1 nab: 0x13ea0 seq: 0x00000023 hws:
0x5 eot: 0 dis: 0reset logs count: 0x38d85a12 scn:
0x0000.000e6c20Low scn: 0x0000.00154ded 05/30/2018 22:54:33Next scn: 0x0000.00158f45 05/31/2018
23:05:04Enabled scn: 0x0000.000e6c20 09/03/2017
05:28:50Thread closed scn: 0x0000.0015680b
05/31/2018 02:12:09Disk cksum: 0xb8e7 Calc cksum: 0xb8e7Terminal Recovery Stop scn: 0x0000.00000000Terminal Recovery Stamp 01/01/1988 00:00:00Most recent redo scn: 0x0000.00000000Largest LWN: 2048 blocksMiscellaneous flags: 0x800000Thr免费主机域名ead internal enable indicator: thr: 0,
seq: 0 scn: 0x0000.00000000LOG FILE #3: name #1: /tpdata/oradata/ogg1/redo03.logThread 1 redo log links: forward: 0
backward: 2siz: 0x19000 seq: 0x00000024 hws: 0x1 bsz:
512 nab: 0xffffffff flg: 0x8 dup: 1Archive links: fwrd: 0 back: 0 Prev scn:
0x0000.00154dedLow scn: 0x0000.00158f45 05/31/2018 23:05:04Next scn: 0xffff.ffffffff 01/01/1988
00:00:00FILE 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 LOGredo log key is
822c416515657eb0b48bc9b93c403cc8redo log key flag is 5descrip:”Thread 0001, Seq# 0000000036,
SCN 0x000000158f45-0xffffffffffff”thread: 1 nab: 0xffffffff seq: 0x00000024
hws: 0x1 eot: 1 dis: 0reset logs count: 0x38d85a12 scn:
0x0000.000e6c20Low scn: 0x0000.00158f45 05/31/2018 23:05:04Next scn: 0xffff.ffffffff 01/01/1988
00:00:00Enabled scn: 0x0000.000e6c20 09/03/2017
05:28:50Thread closed scn: 0x0000.00158f45
05/31/2018 23:05:04Disk cksum: 0x471d Calc cksum: 0x471dTerminal Recovery Stop scn: 0x0000.00000000Terminal Recovery Stamp 01/01/1988 00:00:00Most recent redo scn: 0x0000.00000000Largest LWN: 0 blocksMiscellaneous flags: 0x800000Thread internal enable indicator: thr: 0,
seq: 0 scn: 0x0000.00000000Dump出指定日志的所有内容。关于“oracle中dump logfile方法有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。

相关推荐: Oracle中如何是哪个ip造成的用户被锁

小编给大家分享一下Oracle中如何是哪个ip造成的用户被锁，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！在登陆时被告知test用户被锁1、用dba角色的用户登陆，进行解锁，先设置具体时间格式，以便查看具体时间 SQL> alter s…