oracle中dump logfile方法有哪些

74次阅读

没有评论

共计 15701 个字符，预计需要花费 40 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 oracle 中 dump logfile 方法有哪些，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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
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 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 (
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.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
SEQ:1 OP:5.2 ENC:0 RBL:0

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
SEQ:1 OP:5.1 ENC:0 RBL:0

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
flg2: 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%)

———————————————-

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
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————————————————————————

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 (