故事背景(本故事纯属虚构，如有雷同，纯属巧合)
当数据库需要非常规启动时，我们可能需要使用_corrupted_rollback_segments 参数来忽略有问题的回滚段,使数据库正常启动
之前常用strings从system文件提取所有的回滚段名称，进行全部忽略，有没有什么方式可以获取undo段的一些详细信息呢，也就是undo$这张表
后来我找到了bbed_warp.sh 这个脚本，这个脚本可以查看任何一个data类型的数据块的内容，这个脚本出处来自下面这篇mos，虽然我没看过，但还是要标明出处的
Oracle MOS上的一篇内部文档A QUICK WAY TO READ RECORDS FROM A DATA BLOCK USING BBED TOOL（Note:371546.1）
#!/bin/ksh
# $Id: bbed-wrap.sh,v1.1 2006/05/24 09:44:03 oracle Exp oracle $
# $Author: mmalvezz $
FILE=$1
BLOCK=$2
ORADATATYPE=${3:-"/rn2cntn"}
BBED=$ORACLE_HOME/bin/bbed
PARFILE=$(pwd)/bbed.par
DBA="file ${FILE} block ${BLOCK}"
export DBA ORADATATYPE
PORT=$(uname)
[ $PORT == "Linux" ] && AWK=awk
[ $PORT == "SunOS" ] && AWK=nawk
NUMROWS=$(echo p $DBA kdbh.kdbhnrow| $BBED parfile=bbed.par | grep kdbhnrow |${AWK} '{print $5}')
[ $NUMROWS -eq 0 ] && exit
echo "There are $NUMROWS rows in block $BLOCK on file $FILE"
Idx=0
while [ $Idx -lt $NUMROWS ]
do
echo x ${ORADATATYPE} $DBA *kdbr[$Idx] | $BBED parfile=bbed.par|${AWK} -F: 'BEGIN {
flag=0;
cnt=0;
}
{ if($1 ~ "cols") numcol=$2; }
# read only rows that are not chained, see kd3.h for details
{
if(($1 ~ "flag") && ($2 ~ "KDRHFL, KDRHFF, KDRHFH" ))
flag=1;
}
{
if(($1 ~ "col" ) && ($1 ~ "\\[") && (flag==1))
{
printf("\x22%s\x22", $2);
if(++cnt < numcol)
printf(",");
}
}
END { printf("\n"); }
'
((Idx+=1))
done
开始演示
这个脚本的使用方式很简单，3个参数，file#，block#，e(x)amine格式化
首先获取undo$的e(x)amine格式化，这个随便找个同版本的其它库就能得出
select '/r'||listagg(
case data_type
when 'NUMBER' then 'n'
when 'DATE' then 't'
else 'c'
end,
''
) within group (order by column_id) as data_type
from dba_tab_columns where table_name='UNDO$';
DATA_TYPE
--------------------------------------------------------------------------------
/rncnnnnnnnnnnnnnnnnncct
那么undo$的file# 和 block#在哪呢，file#为1，也就是system文件中，这个相信大家都想到了，那么block#呢，这个就不好猜了，那就不猜了，咱们从其它库也能得到
从bootstrap$看到UNDO$ 的从(FILE 1 BLOCK 224) 开始分配了一个区，8个块，现在file#，block#，e(x)amine格式化 咱们都有了，可以先试试bbed_warp.sh 这个脚本了
SQL> select obj#,sql_text from bootstrap$ where obj#=15;
OBJ# SQL_TEXT
---------- ------------------------------------------------------------------------------------------------------------------------
15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"B
LOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN" NUMBER,"INST#" NUMBER,"STATUS$" NUMBER
NOT NULL,"TS#" NUMBER,"UGRP#" NUMBER,"KEEP" NUMBER,"OPTIMAL" NUMBER,"FLAGS" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPA
RE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 15 EXTENTS (FILE 1 BLOCK 224))
SQL> select owner,file_id,segment_name,extent_id,block_id,round(bytes/1024/1024) m,blocks from dba_extents
where segment_name='UNDO$' order by file_id,block_id;
OWNER FILE_ID SEGMENT_NAME EXTENT_ID BLOCK_ID M BLOCKS
---------------------- ------- --------------------------------- --------- -------- -------- --------
SYS 1 UNDO$ 0 224 0 8
先准备bbed.par文件
blocksize=8192 #指定block size大小 listfile=/home/oracle/linux_64/bbedfile.txt #指定文件名 logfile=/home/oracle/linux_64/log.bbd #制定记录的日志文件及位置 mode=browse #指定操作模式(分edit和browse两种) spool=yes #是否记录bbed的操作日志 password=blockedit #bbed密码
查看file# 1 block# 225 的数据，至于为啥不看224，当然是因为它是段头块啊
[oracle@cesdb3 linux_64]$ sh bbed_warp.sh 1 225 /rncnnnnnnnnnnnnnnnnncct
There are 113 rows in block 225 on file 1
" 0 "," SYSTEM"," 0 "," 1 "," 128 "," 0 "," 0 "," 0 "," 0 "," 0 "," 3 "," 0 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 0 "
" 1 "," _SYSSMU1_2283764861$"," 1 "," 5 "," 216 "," 24829137 "," 0 "," 5013 "," 1080 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 2 "," _SYSSMU2_3856166232$"," 1 "," 5 "," 248 "," 24829647 "," 0 "," 4969 "," 1228 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 3 "," _SYSSMU3_795498773$"," 1 "," 5 "," 296 "," 24829120 "," 0 "," 5042 "," 1263 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 4 "," _SYSSMU4_2412018803$"," 1 "," 5 "," 320 "," 24830896 "," 0 "," 5040 "," 1245 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 5 "," _SYSSMU5_3510858360$"," 1 "," 5 "," 344 "," 24829176 "," 0 "," 4948 "," 1164 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 6 "," _SYSSMU6_2942906012$"," 1 "," 5 "," 360 "," 24829132 "," 0 "," 4986 "," 1124 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 7 "," _SYSSMU7_753594213$"," 1 "," 5 "," 376 "," 24829644 "," 0 "," 5007 "," 1084 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 8 "," _SYSSMU8_1259590054$"," 1 "," 5 "," 2056 "," 24829044 "," 0 "," 5010 "," 1104 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 9 "," _SYSSMU9_510728869$"," 1 "," 5 "," 2080 "," 24830884 "," 0 "," 5005 "," 1040 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 10 "," _SYSSMU10_4273839794$"," 1 "," 5 "," 2096 "," 24829173 "," 0 "," 4982 "," 1058 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 11 "," _SYSSMU11_1079567772$"," 1 "," 5 "," 128 "," 24829149 "," 0 "," 10864 "," 5342 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 12 "," _SYSSMU12_915735642$"," 1 "," 5 "," 144 "," 24829655 "," 0 "," 10929 "," 5776 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 13 "," _SYSSMU13_2784227331$"," 1 "," 5 "," 160 "," 24829123 "," 0 "," 11019 "," 5739 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
OK，到这里你已经可以读取出UNDO$的数据了，那么到这里结束了么，当然没有，还有个问题，如果undo段过多的情况，分配的这个数据区(file 1 block# 224-231)存不下，它分配的下一个数据区在哪呢？，从上面可以看到这个块存了113行记录，也就当undo段达到800左右的时候这个数据区就存不下了，需要分配下一个数据区了
undo$表是bootstrap$ 对象，数据库open时会进行扫描undo$所有的块，这个时候采用10046，可以找到undo$的所有块了
oradebug setmypid;
oradebug event 10046 trace name context forever ,level 12;
oradebug tracefile_name
alter session set db_file_multiblocK_read_count=1;
alter database open;
### 下面这些就是undo$所有的块了
[oracle@cesdb3 linux_64]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_70869.trc|grep file#=1|grep -w "obj#=15"
WAIT #140737232802400: nam='db file sequential read' ela= 4 file#=1 block#=224 blocks=1 obj#=15 tim=1720267800330
WAIT #140737232802400: nam='db file sequential read' ela= 6 file#=1 block#=225 blocks=1 obj#=15 tim=1720267800434
WAIT #140737232802400: nam='db file sequential read' ela= 20 file#=1 block#=226 blocks=1 obj#=15 tim=1720267800493
WAIT #140737232802400: nam='db file sequential read' ela= 20 file#=1 block#=227 blocks=1 obj#=15 tim=1720267800549
WAIT #140737232802400: nam='db file sequential read' ela= 20 file#=1 block#=228 blocks=1 obj#=15 tim=1720267800603
WAIT #140737232802400: nam='db file sequential read' ela= 6 file#=1 block#=229 blocks=1 obj#=15 tim=1720267800659
WAIT #140737232802400: nam='db file sequential read' ela= 21 file#=1 block#=230 blocks=1 obj#=15 tim=1720267800712
WAIT #140737232802400: nam='db file sequential read' ela= 19 file#=1 block#=231 blocks=1 obj#=15 tim=1720267800763
WAIT #140737232802400: nam='db file sequential read' ela= 5 file#=1 block#=247128 blocks=1 obj#=15 tim=1720267800802
WAIT #140737232802400: nam='db file sequential read' ela= 20 file#=1 block#=247129 blocks=1 obj#=15 tim=1720267800849
WAIT #140737232802400: nam='db file sequential read' ela= 112 file#=1 block#=247130 blocks=1 obj#=15 tim=1720267801007
WAIT #140737232802400: nam='db file sequential read' ela= 131 file#=1 block#=247131 blocks=1 obj#=15 tim=1720267801162
WAIT #140737232802400: nam='db file sequential read' ela= 3 file#=1 block#=247132 blocks=1 obj#=15 tim=1720267801184
### 生成查询块的语句
[oracle@cesdb3 linux_64]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_70869.trc|grep file#=1|grep -w "obj#=15"|grep -vw "block#=224"|\
awk '{match($0, /file#=([0-9]+).*block#=([0-9]+)/, arr); print "sh bbed_warp.sh", arr[1], arr[2], "/rncnnnnnnnnnnnnnnnnncct","|sed 1d"}'|tee undo.sh
sh bbed_warp.sh 1 225 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 226 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 227 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 228 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 229 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 230 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 231 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 247128 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 247129 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 247130 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 247131 /rncnnnnnnnnnnnnnnnnncct |sed 1d
sh bbed_warp.sh 1 247132 /rncnnnnnnnnnnnnnnnnncct |sed 1d
### 执行语句
[oracle@cesdb3 linux_64]$ sh undo.sh > undo.txt
[oracle@cesdb3 linux_64]$ wc -l undo.txt
1001 undo.txt
### 我这里就只展示一部分数据了
[oracle@cesdb3 linux_64]$ head -n 10 undo.txt ;tail -10 undo.txt
" 0 "," SYSTEM"," 0 "," 1 "," 128 "," 0 "," 0 "," 0 "," 0 "," 0 "," 3 "," 0 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 0 "
" 1 "," _SYSSMU1_2283764861$"," 1 "," 5 "," 216 "," 24946771 "," 0 "," 5013 "," 1080 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 2 "," _SYSSMU2_3856166232$"," 1 "," 5 "," 248 "," 24946262 "," 0 "," 4969 "," 1228 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 3 "," _SYSSMU3_795498773$"," 1 "," 5 "," 296 "," 24946236 "," 0 "," 5042 "," 1263 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 4 "," _SYSSMU4_2412018803$"," 1 "," 5 "," 320 "," 24946212 "," 0 "," 5040 "," 1245 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 5 "," _SYSSMU5_3510858360$"," 1 "," 5 "," 344 "," 24946371 "," 0 "," 4948 "," 1164 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 6 "," _SYSSMU6_2942906012$"," 1 "," 5 "," 360 "," 24946595 "," 0 "," 4986 "," 1124 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 7 "," _SYSSMU7_753594213$"," 1 "," 5 "," 376 "," 24946253 "," 0 "," 5007 "," 1084 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 8 "," _SYSSMU8_1259590054$"," 1 "," 5 "," 2056 "," 24946221 "," 0 "," 5010 "," 1104 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 9 "," _SYSSMU9_510728869$"," 1 "," 5 "," 2080 "," 24946203 "," 0 "," 5005 "," 1040 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 991 "," _SYSSMU991_3975575505$"," 1 "," 5 "," 19040 "," 24942370 "," 0 "," 2 "," 1 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 992 "," _SYSSMU992_4199057497$"," 1 "," 5 "," 19056 "," 24947390 "," 0 "," 2 "," 1 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 993 "," _SYSSMU993_1438511420$"," 1 "," 5 "," 19072 "," 24947089 "," 0 "," 3 "," 2 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 994 "," _SYSSMU994_96633996$"," 1 "," 5 "," 19088 "," 24947054 "," 0 "," 2 "," 2 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 995 "," _SYSSMU995_26033089$"," 1 "," 5 "," 19104 "," 24947125 "," 0 "," 2 "," 1 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 996 "," _SYSSMU996_2132268339$"," 1 "," 5 "," 19120 "," 24942364 "," 0 "," 2 "," 2 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 997 "," _SYSSMU997_3037379007$"," 1 "," 5 "," 19136 "," 24947474 "," 0 "," 2 "," 1 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 998 "," _SYSSMU998_1762494549$"," 1 "," 5 "," 19152 "," 24947093 "," 0 "," 2 "," 2 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 999 "," _SYSSMU999_200763971$"," 1 "," 5 "," 19168 "," 24946909 "," 0 "," 2 "," 1 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 1000 "," _SYSSMU1000_1799672153$"," 1 "," 5 "," 19184 "," 24947131 "," 0 "," 2 "," 3 "," 0 "," 3 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
undo$ 表结构
create table undo$ /* undo segment table */
( us# number not null, /* undo segment number */
name varchar2("M_IDEN_30") not null, /* name of this undo segment */
user# number not null, /* owner: 0 = SYS(PRIVATE), 1 = PUBLIC */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
scnbas number, /* highest commit time in rollback segment */
scnwrp number, /* scnbas - scn base, scnwrp - scn wrap */
xactsqn number, /* highest transaction sequence number */
undosqn number, /* highest undo block sequence number */
inst# number, /* parallel server instance that owns the segment */
status$ number not null, /* segment status (see KTS.H): */
/* 1 = INVALID, 2 = AVAILABLE, 3 = IN USE, 4 = OFFLINE, 5 = NEED RECOVERY,
* 6 = PARTLY AVAILABLE (contains in-doubt txs)
*/
ts# number, /* tablespace number */
ugrp# number, /* The undo group it belongs to */
keep number,
optimal number,
flags number,
spare1 number,
spare2 number,
spare3 number,
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date
)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
