What are the dump logfile methods in oracle 01/18 Update SLTechnology News&Howtos

What are the dump logfile methods in oracle

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the dump logfile methods in oracle. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Dump logfile

Sometimes the job may require dump redo logfile to help solve the problem.

This command requires alter system system permissions.

The database can be in the nomount,mount,open state.

You can dump online redo log, or you can dump archive files.

You can even dump files for other data, but the operating system should be consistent.

Several methods of 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 gives the redo record of the specified range of data blocks.

Database version:

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

Update one line:

SQL > update test set breadth shutting down where shutting 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 23 35 purl 34.677

* SESSION ID: (38.36) 2018-05-31 2315 351 35 purl 34.677

* CLIENT ID: () 2018-05-31 23mm 3534.677

* SERVICE NAME: (SYS$USERS) 2018-05-31 23mm 3534.677

* MODULE NAME: (sqlplus@oggtest1 (TNS V1-V3)) 2018-05-31 23 purl 35 purl 34.677

* ACTION NAME: () 2018-05-31 23mm 3534.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)

The start and end of the data block (thru). Here I only dump the 1683 block.

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) 05Universe 31 Universe 23:05:04, 2018

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

Enabled scn: 0x0000.000e6c20 (945184) 09Universe 03Universe 05:28:50

Thread closed scn: 0x0000.00158f45 (1412933) 05Universe 31 Universe 23:05:04, 2018

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 ('24xxxxxxxxxxxxxxxxxxxx) FROM DUAL; TO_NUMBER (' 23xxxxxxxxxxxxxxxxx)-- 36 SQL > SELECT TO_NUMBER ('00000ad6)) FROM DUAL TO_NUMBER ('1E3A')-- log number 277436. Redo block number is 2774, which is the redo log of our dump.

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

# # the following part is the data before modification, and the above and lower parts are actually the information of 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 is the length, 6d 69 6e 67 is the value before modification

The value before modification: SQL > select utl_raw.cast_to_varchar2 (replace ('6d, 69, 6e, 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)) FROM DUAL; TO_NUMBER (' 01C00693)) FROM DUAL; TO_NUMBER ('01C00693))-- 29361811 SQL > select2 dbms_utility.data_block_address_block (29361811) "BLOCK", 3 dbms_utility.data_block_address_file (29361811) "FILE" 4 from dual The 1693 pieces of BLOCK FILE--1683 77 data file are the blocks of my dump. 75382 is the object_id OP code of the test table we operate. In the operation code of DML, 11.5 represents 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 is the address of the updated block, so it is as obvious as DBA:0x01c00693; Hdba is the address of the segment in which the updated block is located. SQL > SELECT TO_NUMBER ('01c00692)) FROM DUAL; TO_NUMBER (' 01C00692))-- 29361810SQL > select 2 dbms_utility.data_block_address_block (29361810) "BLOCK", 3 dbms_utility.data_block_address_file (29361810) "FILE" 4 from dual Information about the segment in which the BLOCK FILE--1682 7TEST table is located: 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 the two are consistent itli: 2 the transaction is using a second transaction slot. Tabn: 0 the first table on the block. Flag is 0x2c, which means that there is no row migration, including first data piece,last data piecehe head piece of row.ncol: 2 the record has 2 rows, nnew:1 modified 1 column (b column) size: 12 modified column length increased by 12 (from 4 to 16) modified value: SQL > select utl_raw.cast_to_varchar2 ('6d penguin 69e meme 67m 73m 6m 67m 75m 6e 6d 6e 6e 6e 77e 77e 6e 6e 6e 77e 6e 6e 6e 77e 6e 6e)) 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

Session information for executing transactions: 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: 1Accord 2688 moved: 0Mb (0%)

Longest LWN: 504Kb, reads: 240

Last redo scn: 0x0000.001593c8 (1414088)

Change vector header moves = 261ram 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 is actually the location where changes in blocks are recorded in redo log. So here is the redo record of the redo block in which dump goes out of the specified range.

3. To dump records based on SCNALTER SYSTEM DUMP LOGFILE 'filename' SCN MIN minscn SCN MAX maxscn

Dump the redo record of the specified range of SCN

4. To dump records based on timeALTER SYSTEM DUMP LOGFILE 'filename' TIME MIN value TIME MAX value

The value here is actually a time range, or redo dump time to be exact.

Here is a script that converts the time to 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 / 08 / 07 / 06 / 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 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 (