[20240824]利用gdb抽取kglnaobj内容.txt
--//上午测试跟踪library cache lock library cache pin使用gdb,利用handle addreess+0x1c8偏移可以取出kglnaobj内容.
--//灵光一现,是否可以直接通过gdb抽取kglnaobj内容,新的gdb版本支持管道操作,在测试环境尝试一下.
--//千万不要在生产系统做这样的测试!!!!
1.环境:
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> select * from dept,emp where dept.deptno=emp.deptno;
...
--//输出略.
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1395733917 cxuafv59m2dcx 0 79261 844388907 5331359d 2024-08-24 15:05:27 16777216
SYS@book> @sharepool/shp4x cxuafv59m2dcx 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000066074120 0000000069ED8BB0 select * from dept,emp where dept.deptno 0 0 0 000000006CDABDA8 0000000066155518 8128 28320 3316 39764 39764 1395733917 cxuafv59m2dcx 0
parent handle address 0000000069ED8BB0 0000000069ED8BB0 select * from dept,emp where dept.deptno 0 0 0 0000000069470058 00 4064 0 0 4064 4064 1395733917 cxuafv59m2dcx 65535
--//parent handle address = 0000000069ED8BB0
$ echo x/s 0x0000000069ED8BB0+0x1c8 | gdb -q
(gdb) 0x69ed8d78: <Address 0x69ed8d78 out of bounds>
(gdb) quit
$ echo x/s 0x0000000069ED8BB0+0x1c8 | gdb -q $(which oracle)
Reading symbols from /u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle...done.
(gdb) 0x69ed8d78: <Address 0x69ed8d78 out of bounds>
(gdb) quit
--//以上两个方式都报错，<Address 0x69ed8d78 out of bounds>。以为gdb没有指定程序防蚊范围.
--//取出当前会话进程号看看。
SCOTT@book01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
403 52380 3539 DEDICATED 3541 95 3 alter system kill session '403,52380' immediate;
$ echo x/s 0x0000000069ED8BB0+0x1c8 | gdb -q -p 3541 | grep "^(gdb)"
(gdb) 0x69ed8d78: "select * from dept,emp where dept.deptno=emp.deptno"
(gdb) quit
--//OK,没有问题.
--//换1个进程ora_dbw0_book看看.
$ ps -ef | grep ora_dbw0_boo[k]
oracle 3066 1 0 15:03 ? 00:00:00 ora_dbw0_book
$ echo x/s 0x0000000069ED8BB0+0x1c8 | gdb -q -p 3066 | grep "^(gdb)"
(gdb) 0x69ed8d78: "select * from dept,emp where dept.deptno=emp.deptno"
(gdb) quit
--//OK没有问题.这样只要知道ora_dbw0_book进程就可以实现这个功能.
3.写脚本:
$ cat ./ext_kglobj.sh
#/bin/bash
echo x/s 0x${1}+0x1c8 | gdb -q -p $(pgrep ora_dbw0_${ORACLE_SID}) | grep '^(gdb)'
$ source ./ext_kglobj.sh 0000000069ED8BB0
(gdb) 0x69ed8d78: "select * from dept,emp where dept.deptno=emp.deptno"
(gdb) quit
4.继续尝试：
SCOTT@book01p> select * from dept
2 ,emp
3 where dept.deptno=emp.deptno;
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1207303074 b7fucgx3zbyx2 0 129954 844388907 47f5fba2 2024-08-24 15:21:20 16777216
SYS@book> @sharepool/shp4x b7fucgx3zbyx2 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000069F2E688 0000000069F2FDE8 select * from dept ,emp where dept.deptn 0 0 0 0000000069F2E4C8 0000000069F2F410 8080 28320 3317 39717 39717 1207303074 b7fucgx3zbyx2 0
parent handle address 0000000069F2FDE8 0000000069F2FDE8 select * from dept ,emp where dept.deptn 0 0 0 0000000069F2FC28 00 4064 0 0 4064 4064 1207303074 b7fucgx3zbyx2 65535
$ source ./ext_kglobj.sh 0000000069F2FDE8
(gdb) 0x69f2ffb0: "select * from dept\n,emp\nwhere dept.deptno=emp.deptno"
(gdb) quit
--//测试超长文本看看:
$ cat aa.txt
select /*+
012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890
....
012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890
*/ sysdate from dual;
SCOTT@book01p> @ aa.txt
SYSDATE
-------------------
2024-08-24 15:27:19
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1191379888 47ug7hp3h60xh 0 66480 1388734953 470303b0 2024-08-24 15:27:18 16777216
SYS@book> @sharepool/shp4x 47ug7hp3h60xh 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 00000000633E9680 000000006A5194D0 select /*+ 01234567890012345678900123456 0 0 0 0000000064E23A70 0000000069F7D948 4032 12128 8084 24244 24244 1191379888 47ug7hp3h60xh 0
parent handle address 000000006A5194D0 000000006A5194D0 select /*+ 01234567890012345678900123456 0 0 0 0000000063DBB9E0 00 4064 0 0 4064 4064 1191379888 47ug7hp3h60xh 65535
*/
$ source ./ext_kglobj.sh 000000006A5194D0
(gdb) 0x6a519698: "select /*+\n012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890\n01234567890012345678900123456789001234567890012345678900"...
(gdb) quit
--//超长sql语句分成多个chunk,仅仅取开头部分.
4.取一个表dept看看.
$ sql_idz.sh 'DEPT.SCOTT.BOOK01P\x1\0\0\0' 3
sql_text = DEPT.SCOTT.BOOK01P\x1\0\0\0
full_hash_value(16) = 05DB243908B3C797B99628590EDB820C or 05db243908b3c797b99628590edb820c
xxxxx_matching_signature(10) = -5073823567219817972 or 13372920506489733644
hash_value(10) = 249266700
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc
SYS@book> @ kglob 0 249266700
==============================
INST_ID : 1
OWNER : SCOTT
NAME : DEPT
DB_LINK :
NAMESPACE : TABLE/PROCEDURE
TYPE : TABLE
NAMESPACE_NUM : 1
NAMESPACE_HEX : 1
SHARABLE_MEM : 4064
LOADS : 4
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 98828
INVALIDATIONS : 0
HASH_VALUE : 249266700
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP : 2024-08-16/09:33:38
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 4
PINNED_TOTAL : 4
PROPERTY :
FULL_HASH_VALUE : 05db243908b3c797b99628590edb820c
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 0000000068FAC470
EDITION :
SQL_ID :
OBJECT_STR : DEPT.SCOTT.BOOK01P\x1\0\0\0
PL/SQL procedure successfully completed.
--//ADDR: 0000000068FAC470
$ source ./ext_kglobj.sh 0000000068FAC470
(gdb) 0x68fac638: "DEPTSCOTTBOOK01PPj9n"
(gdb) quit
--//后面不对，没有限制的长度，前面部分正确没有问题。
--//最后还是提示不要在生产系统做这类尝试。