通过onlog来分析逻辑日志的操作

在数据库审计选项没有开启的情况下,有客户提出需要对数据库的某些dml或ddl操作进行事后追踪和审计,此时可以通过onlog工具对逻辑日志的分析来实现。

下面我们将举一个通过逻辑日志分析alter table的例子:

假设怀疑数据库testdb中的表t_user,在2023年9月7日早上9:00-10:00被实施alter操作了,我们需要查出谁在什么时间发起了这个alter操作。

1、找到对应时间的日志

在online.log 中,通过Checkpoint完成的时间来确认相关的logical logs:


通过以上9点-10点的日志信息,我们可以大致确认修改操作发生在逻辑日志 388。

2、确认逻辑日志状态

确认这些被选中的逻辑日志的状态和位置

通过onstat -l我们发现逻辑日志388还没有被覆盖

3、找到partnum

找到t_user表的partnum

$ dbaccess testdb -

select hex(partnum) from systables where tabname=‘t_user’;
image

4、查找相关操作记录

4.1、使用onlog命令找出关于表state在相应时间段的所有记录

$ onlog -n 388 -l -t 0x00300205 > onlog-388.out

4.2、在相应的记录输出中,查找相应的ALTER关键字

$ grep ALT onlog-388.out

4.3、分析整个记录确认所需事务信息

5、事务的详细信息

使用onlog和BEGIN关键字得到关于事务30的详细信息

逻辑日志显示:有用户在09/07/2023 09:25:50发起了对表t_user相应的alter table操作。

此外,onlog在对数据库其他操作如insert,delete,update等都可以进行分析。

附:数据库逻辑日志记录类型对应的操作。

Record Type Action Additional Columns and Format
ADDCHK Add chunk. chunk number - Decimal
chunk name - ASCII
ADDDBS Add dbspace. dbspace name - ASCII
ADDITEM Add item to index. tblspace ID - Hexadecimal
rowid - Hexadecimal
logical page - Decimal
key number - Decimal
key length - Decimal
ADDLOG Add log. log number - Decimal
log size (pages) - Decimal
pageno - Hexadecimal
ALLOCGENPG Allocate a generic page. tblspace ID - Decimal
rowid - Decimal
slot flags and length - Decimal
page version if delete - Decimal
flags, vimage record - Decimal
rowid for previous - Decimal
data - ASCII
ALTERDONE Alter of fragment complete. tblspace ID - Hexadecimal
physical page number previous page - Hexadecimal
logical page number - Decimal
version of alter - Decimal
ALTSPCOLSNEW Changed columns in an alter table. number of columns - Decimal
special column list - array
ALTSPCOLSOLD Changed columns in an alter table. number of columns - Decimal
special column list - array
BADIDX Bad index tblspace ID - Hexadecimal
BEGCOM Begin commit. (None) - (None)
BEGIN Begin work. date - Decimal
time - Decimal
SID - Decimal
user - ASCII
BEGPREP Written by the coordinator database server to record the start of the two-phase commit protocol. flags - Decimal (Value is 0 in a distributed transaction.)
number of participants - Decimal
BEGWORK Begin a transaction. begin transaction time - Decimal
user ID - Decimal
process ID - Decimal
BFRMAP Simple-large-object free-map change. tblspace ID - Hexadecimal
bpageno - Hexadecimal
status USED/FREE log ID - Decimal
prev page - Hexadecimal
BLDCL Build tblspace. tblspace ID - Hexadecimal
fextsize - Decimal
nextsize - Decimal
row size - Decimal
ncolumns - Decimal
table name - ASCII
BMAPFULL Bitmap modified to prepare for alter. tblspace ID - Hexadecimal
bitmap page num - Decimal
BMAP2TO4 2-bit bitmap altered to two 4-bit bitmaps. tblspace ID - Hexadecimal
2-bit bitmap page number - Decimal
flags - Decimal
BSPADD Add blobspace. blobspace name - ASCII
BTCPYBCK Copy back child key to parent. tblspace ID - Hexadecimal
parent logical page - Decimal
child logical page - Decimal
slot - Decimal
rowoff - Decimal
key number - Decimal
BTMERGE Merge B-tree nodes. tblspace ID - Hexadecimal
parent logical page - Decimal
left logical page - Decimal
right logical page - Decimal
left slot - Decimal
left rowoff - Decimal
right slot - Decimal
right rowoff - Decimal
key number - Decimal
BTSHUFFL Shuffle B-tree nodes. tblspace ID - Hexadecimal
parent logical page - Decimal
left logical page - Decimal
right logical page - Decimal
left slot - Decimal
left rowoff - Decimal
key number - Decimal
flags - Hexadecimal
BTSPLIT Split B-tree node. tblspace ID - Hexadecimal
rowid - Hexadecimal
parent logical page - Decimal
left logical page - Decimal
right logical page - Decimal
infinity logical page - Decimal
rootleft logical page - Decimal
midsplit - Decimal
key number - Decimal
key length - Decimal
CDINDEX Create detached index. database name - ASCII
owner - ASCII
table name - ASCII
index name - ASCII
CDR Captures the set of table columns modified by an update statement such as a bitvector. This log record allows Enterprise Replication to capture only the changed data to avoid transmitting the unchanged columns to a target site.
In the example, the first six columns of the table are unchanged (6 leftmost bits in the bitvector are 0), the seventh and eighth columns have been updated (seventh and eighth bits are 1), and so on. The onlog output displays as many bits of bitvector as fit in a single line of the output. To see the entire bitvector displayed in hexadecimal, use the onlog -l command. name of CDR record - ASCII
partition number - Hexadecimal
bitvector - Binary
Sample onlog output for CDR log record:
adr len type xid id link
40 36 CDR 14 0 18
name partno bitvector
UPDCOLS 10009a 000000110100110100
CHALLOC Chunk extent allocation. pageno - Hexadecimal
size - Hexadecimal
CHCOMBINE Chunk extent combine. pageno - Hexadecimal
CHFREE Chunk extent free. pageno - Hexadecimal
size - Hexadecimal
CHKADJUP Update chunk adjunct on disk. The database server writes this record when it moves space from the reserved area to the metadata or user-data area or when the user adds an sbspace chunk. chunk number - Integer
ud1_start_page - Integer
ud1_size - Integer
md_start_page - Integer
md_size - Integer
ud2_start_page - Integer
ud2_size - Integer
flags - Hexadecimal
CHPHYLOG Change physical-log location. pageno - Hexadecimal
size in kilobytes - Hexadecimal
dbspace name - ASCII
CHRESERV Reserve extent for metadata stealing. This record is written when you add an sbspace chunk. chunk number - Integer
page number - Integer
length - Integer
CHSPLIT Chunk extent split. pageno - Hexadecimal
CINDEX Create index. tblspace ID - Hexadecimal
low rowid - Decimal
high rowid - Decimal
index descriptor - ASCII
COARSELOCK Coarse-grain locking tblspace ID - Hexadecimal
old coarse-locking flag value - Decimal
new coarse-locking flag value - Decimal
CKPOINT Checkpoint. max users - Decimal
number of active transactions - Decimal
CLR Compensation-log record; created during a rollback. (None) - (None)
CLUSIDX Create clustered index. tblspace ID - Hexadecimal
key number - Decimal
COLREPAI Adjust BYTE, TEXT, or VARCHAR column. tblspace ID - Hexadecimal
number of columns adjusted - Decimal
COMMIT Commit work. date - Decimal
time - Decimal
COMTAB Compact slot table on a page. logical page number - Decimal
number slots moved - Decimal
compressed slot pairs - ASCII
COMWORK End a transaction and commit work. end transaction time - Decimal
begin transaction time - Decimal
DELETE Delete before-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
DELITEM Delete item from index. tblspace ID - Hexadecimal
rowid - Hexadecimal
logical page - Decimal
key number - Decimal
key length - Decimal
DERASE Drop tblspace in down dbspace. tblspace number - Hexadecimal
table lock number - Decimal
DFADDEXT New extent is added. partnum - Hexadecimal
offset of extent entry in list - Hexadecimal
extent size in pages - Decimal
physical address of extent - Offset and chunk no-hex
DFDRPEXT Drop the original extent. partnum - Hexadecimal
offset of extent entry in list - Hexadecimal
original size of this extent - Decimal
physical address - offset and chunk no-hex
DFEND End of defragment operation. partnum - Hexadecimal
DFMVPG Move page from old extent to new extent. partnum - Hexadecimal
offset of new extent - Hexadecimal
logical page number of source - Hexadecimal
physical address of destination - Offset and chunk no-hex
physical address of source - Offset and chunk no-hex
DFREMDUM Remove the dummy entries. partnum - Hexadecimal
DFSTART Start of defragment operation. partnum - Hexadecimal
DINDEX Drop index. tblspace ID - Hexadecimal
key number - Decimal
DRPBSP Drop blobspace. blobspace name - ASCII
DRPCHK Drop chunk. chunk number - Decimal
chunk name - ASCII
DRPDBS Drop dbspace. dbspace name ASCII
DRPLOG Drop log. log number - Decimal
log size (pages) - Decimal
pageno - Hexadecimal
ENDTRANS Written by both the coordinator and participant database servers to record the end of the transaction. ENDTRANS instructs the database server to remove the transaction entry from its shared-memory transaction table and close the transaction.
In the coordinator logical log, each BEGPREP that results in a committed transaction is paired with an ENDTRANS record. If the final decision of the coordinator is to roll back the transaction, no ENDTRANS record is written.
In the participant logical log, each ENDTRANS record is paired with a corresponding HEURTX record. (None) - (None)
ERASE Drop tblspace. tblspace ID -Hexadecimal
FREE_RE Allocate extent from reserve extent to metadata or user-data area of an sbspace chunk. chunk number - Integer
page number - Integer
length - Integer
flag - Hexadecimal
HDELETE Delete home row. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
HEURTX Written by a participant database server to record a heuristic decision to roll back the transaction. It should be associated with a standard ROLLBACK record indicating that the transaction was rolled back. flag - Hexadecimal (Value is always 1.)
HINSERT Home row insert. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
HUPAFT Home row update, after-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
HUPBEF Home row update, before-image.
In addition, the flag field of the HUPBEF header may include the following values:
LM_PREVLSN
Confirms that an LSN exists.
LM_FIRSTUPD
Confirms that this is the first update for this rowID by this transaction. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
LSN (optional) - Decimal
HUPDATE If the home row update before-images and after-images can both fit into a single page, the database server writes a single HUPDATE record.
In addition, the flag field of the HUPDATE log may include the following values:
LM_PREVLSN
Confirms that an LSN exists.
LM_FIRSTUPD
Confirms that this is the first update for this rowID by this transaction. tblspace ID - Hexadecimal
rowid - Hexadecimal
forward ptr rowid - Hexadecimal
old slotlen - Decimal
new slotlen - Decimal
number of pieces - Decimal
LSN (optional) - Decimal
IDXFLAGS Index flags. tblspace ID - Hexadecimal
key number - Hexadecimal
INSERT Insert after-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
ISOSPCOMMIT Log an isolated save-point commit. end transaction time - Decimal
begin transaction time - Decimal
LCKLVL Locking mode (page or row). tblspace ID - Hexadecimal
old lockmode - Hexadecimal
new lockmode - Hexadecimal
LG_ADDBPOOL Add a buffer pool online. page size in bytes - Decimal
number of buffers in the pool - Decimal
number of lru queues - Decimal
percent of lru_max_dirty - Decimal
percent of lru_min_dirty - Decimal
PTRUNCATE Identifies an intention to truncate a table. The partitions are marked to be dropped or reused, according to the specified command option. tblspace ID - Hexadecimal
TRUNCATE TRUNCATE has freed the extents and the transaction will be committed. tblspace ID - Hexadecimal
MVIDXND Index node moved to allow for 2-bit to 4-bit bitmap conversion. tblspace ID - Hexadecimal
old page number - Decimal
new page number - Decimal
parent page number - Decimal
parent slot number - Decimal
parent slot offset - Decimal
key number - Decimal
PBDELETE Delete tblspace blobpage. bpageno - Hexadecimal
status USED/FREE unique ID - Decimal
PBINSERT Insert tblspace blobpage. bpageno - Hexadecimal
tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
pbrowid - Hexadecimal
PDINDEX Predrop index. tblspace ID - Hexadecimal
PGALTER Page altered in place. tblspace ID - Hexadecimal
physical page number - Hexadecimal
PGMODE Page mode modified in bitmap. tblspace ID - Hexadecimal
logical page number - Decimal
old mode - Hexadecimal
new mode - Hexadecimal
PERASE Preerase old file. Mark a table that is to be dropped. The database server frees the space on the commit. tblspace ID - Hexadecimal
PNGPALIGN8 Use the pages in this tblspace as generic pages. None
PNLOCKID Change tblspaces lockid. tblspace ID - Hexadecimal
old lock ID - Hexadecimal
new lock ID - Hexadecimal
PNSIZES Set tblspace extent sizes. tblspace ID - Hexadecimal
fextsize - Decimal
nextsize - Decimal
PREPARE Written by a participant database server to record the ability of the participant to commit the transaction, if so instructed. DBSERVERNAME of coordinator - ASCII
PTADESC Add alter description information. tblspace ID - Hexadecimal
physical page number of previous page - Hexadecimal
logical page number - Decimal
number of columns added - Decimal
PTALTER Alter of fragment begun. tblspace ID - Hexadecimal
physical page number previous page - Hexadecimal
logical page number - Decimal
alter desc page number - Decimal
num columns added - Decimal
version of alter - Decimal
added rowsize - Decimal
PTALTNEWKEYD Update key descriptors in a tblspace header after an alter table command. bytes in key descriptor - Decimal
data in key descriptor - ASCII
PTALTOLDKEYD Update key descriptors after an alter table command. bytes in key descriptor - Decimal
data in key descriptor - ASCII
PTCOLUMN Add special columns to fragment. tblspace ID - Hexadecimal
number of columns - Decimal
PTEXTEND Tblspace extend. tblspace ID - Hexadecimal
last logical page - Decimal
first physical page - Hexadecimal
PTRENAME Rename table. tblspace ID - Hexadecimal
old table name - ASCII
new table name - ASCII
RDELETE Remainder page delete. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
hrowid (optional) - Decimal
poffset (optional) - Decimal
RENDBS Rename dbspace. new dbspace name - ASCII
REVERT Logs the reversion of a database space to a database space of an earlier version. type of reversion event - Decimal
arg1 - Decimal
arg2 - Decimal
arg3 - Decimal
RINSERT Remainder page insert. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
hrowid (optional) - Decimal
poffset (optional) - Decimal
ROLLBACK Rollback work. date - Decimal
time - Decimal
ROLWORK End a transaction and roll back work. end transaction time - Decimal
begin transaction time - Decimal
RSVEXTEND Logs the extension to the reserved pages. number of pages - Decimal
physical page number of extent - Hexadecimal
RTREE Logs inserts and deletions for R-tree index pages. (Other operations on R-tree indexes are physically logged.) The record subtypes are:
LEAFINS - insert item in a leaf page
LEAFDEL - delete item from leaf page record subtype - ASCII
index page rowid - Hexadecimal
tuple length - Decimal
base table rowid - Decimal
base table fragid - Decimal
delete flag - Decimal
RUPAFT Remainder page update, after-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
RUPBEF Remainder page update, before-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
slotlen - Decimal
hrowid (optional) - Decimal
poffset (optional) - Decimal
RUPDATE If the remainder page update before-images and after-images can both fit into a single page, the database server writes a single RUPDATE record. tblspace ID - Hexadecimal
rowid - Hexadecimal
forward ptr rowid - Hexadecimal
old slotlen - Decimal
new slotlen - Decimal
number of pieces - Decimal
hrowid (optional) - Decimal
poffset (optional) - Decimal
SBLOB Indicates a subsystem log record for a smart large object.
The various record subtypes are:
CHALLOC
CHCOMBINE
CHFREE
CHSPLIT
CREATE
DELETES
EXTEND
HDRUPD
PDELETE
PTRUNC
REFCOUNT
UDINSERT
UDINSERT_LT
UDUPAFT
UDUPAFT_LT
UDUPAFT
UDUPAFT_LT
UDWRITE
UDWRITE_LT Varies
For more information, see Log Record Types for Smart Large Objects.
Varies
SYNC Written to a logical-log file if that log file is empty and administrator instructs the database server to switch to next log file. (None) - (None)
TABLOCKS Written by either a coordinator or a participant database server. It is associated with either a BEGPREP or a PREPARE record and contains a list of the locked tblspaces (by tblspace number) held by the transaction. (In a distributed transaction, transactions are shown as the owners of locks.) number of locks - Decimal
tblspace number - Hexadecimal
UDINSERT Append new user data. number of locks - Decimal
tblspace number - Hexadecimal
UDUPAFT Update user data after-image if a UDWRITE is too expensive. chunk - Decimal
page within chunk - Hexadecimal
offset within page - Hexadecimal
data length - Hexadecimal
UDUPBEF Update user-data before-image if a UDWRITE is too expensive. chunk - Decimal
page within chunk- Hexadecimal
offset within page - Hexadecimal
data length - Hexadecimal
UDWRITE Update user data (difference image). chunk - Decimal
page within chunk - Hexadecimal
offset within chunk - Hexadecimal
length before write - Hexadecimal
length after write - Hexadecimal
UNDO Header record to a series of transactions to be rolled back. count - Decimal
UNDOBLDC This record is written if a CREATE TABLE statement should be rolled back but cannot be because the relevant chunk is down. When the log file is replayed, the table will be dropped. tblspace number - Hexadecimal
UNIQID Logged when a new SERIAL value is assigned to a row. tblspace ID - Hexadecimal
unique ID - Decimal
UNIQ8ID Logged when a new SERIAL8 value is assigned to a row. tblspace ID - Hexadecimal
unique ID - Decimal
UPDAFT Update after-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
UPDBEF Update before-image. tblspace ID - Hexadecimal
rowid - Hexadecimal
XAPREPARE Participant can commit this XA transaction. (None) - (None)
1 个赞