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

Sodakold · 2023 年9 月 7 日 10:33

在数据库审计选项没有开启的情况下，有客户提出需要对数据库的某些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’;

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)