脚本功能
在某些场景下，数据库启动失败，需要在mount状态下获取Undo段的名称，可以使用以下两种方法来获取。
脚本使用示例
1、直接从system数据文件中获取
直接从system数据文件中过滤，如果system数据文件较大，过滤时间会较长。方法如下：
[oracle@testdb-v2 tmp]$ strings /u01/app/oracle/oradata/testdb/system01.dbf | grep ^_SYSSMU | awk -F'$' '{print $1"$"}' | sort -u
_SYSSMU10_235314541$
_SYSSMU11_2013590764$
_SYSSMU12_2158721311$
_SYSSMU1_3336193855$
_SYSSMU13_3502728648$
_SYSSMU14_4065935650$
_SYSSMU15_2247773548$
_SYSSMU16_784885449$
_SYSSMU2_1849491058$
_SYSSMU3_2454584446$
_SYSSMU4_2145095103$
_SYSSMU5_1329709080$
_SYSSMU6_2221133880$
_SYSSMU7_3984710251$
_SYSSMU8_684059844$
_SYSSMU9_2039187731$2、使用BBED获取
2.1、获取undo$表所在数据块号
库在open状态可以通过undo$查询undo段的名称。但在mount状态下，该表无法查询。此时在相同版本的数据库中查询，确认undo$在哪个数据块。
SQL> select distinct DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) from undo$ order by 2 asc;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 2252.2、执行bbed脚本获取undo段名称
脚本如下：
#!/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执行脚本，命令如下：
[oracle@testdb-v2 bbed]$ ./bbed_wrap_tmp.sh 1 225 "/rncnnnnnnnnnnnnnnnnncct"
There are 17 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_3336193855$"," 1 "," 3 "," 128 "," 29830822 "," 1 "," 10534 "," 731 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 2 "," _SYSSMU2_1849491058$"," 1 "," 3 "," 144 "," 29830820 "," 1 "," 2761 "," 650 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 3 "," _SYSSMU3_2454584446$"," 1 "," 3 "," 160 "," 29830843 "," 1 "," 2644 "," 764 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 4 "," _SYSSMU4_2145095103$"," 1 "," 3 "," 176 "," 29830935 "," 1 "," 2702 "," 531 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 5 "," _SYSSMU5_1329709080$"," 1 "," 3 "," 192 "," 29830933 "," 1 "," 2729 "," 767 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 6 "," _SYSSMU6_2221133880$"," 1 "," 3 "," 208 "," 29831015 "," 1 "," 3136 "," 1074 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 7 "," _SYSSMU7_3984710251$"," 1 "," 3 "," 224 "," 29831019 "," 1 "," 2731 "," 632 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 8 "," _SYSSMU8_684059844$"," 1 "," 3 "," 240 "," 29830937 "," 1 "," 2762 "," 940 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 9 "," _SYSSMU9_2039187731$"," 1 "," 3 "," 256 "," 29831021 "," 1 "," 2729 "," 756 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 10 "," _SYSSMU10_235314541$"," 1 "," 3 "," 272 "," 29830824 "," 1 "," 2831 "," 618 "," 0 "," 3 "," 2 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 11 "," _SYSSMU11_2013590764$"," 1 "," 5 "," 8 "," 988316 "," 0 "," 31 "," 15 "," 0 "," 2 "," 5 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 12 "," _SYSSMU12_2158721311$"," 1 "," 5 "," 24 "," 988318 "," 0 "," 77 "," 69 "," 0 "," 2 "," 5 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 13 "," _SYSSMU13_3502728648$"," 1 "," 5 "," 40 "," 988310 "," 0 "," 17 "," 10 "," 0 "," 2 "," 5 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 14 "," _SYSSMU14_4065935650$"," 1 "," 6 "," 8 "," 1064730 "," 0 "," 81 "," 65 "," 0 "," 2 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 15 "," _SYSSMU15_2247773548$"," 1 "," 6 "," 24 "," 1064606 "," 0 "," 74 "," 80 "," 0 "," 2 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "
" 16 "," _SYSSMU16_784885449$"," 1 "," 6 "," 40 "," 1064752 "," 0 "," 104 "," 76 "," 0 "," 2 "," 6 "," *NULL*"," *NULL*"," *NULL*"," *NULL*"," 2 "脚本说明
./bbed_wrap_tmp.sh 1 225 "/rncnnnnnnnnnnnnnnnnncct"
# 1 225 数据文件号和块编号
# /rncnnnnnnnnnnnnnnnnncct 和undo$列的各个数据类型对应
注意:
如果只写成/rnc，执行该脚本输出内容不能换行，需注意2.3、批量列类型转换
转换SQL如下：
SQL> select '/r'||replace(wm_concat(ty),',','') from
2 (select case data_type when 'NUMBER' then 'n'
3 when 'DATE' then 't'
4 else 'c' END ty from dba_tab_columns where table_name='UNDO$' order by column_id);
'/R'||REPLACE(WM_CONCAT(TY),',','')
--------------------------------------------------------------------------------
/rncnnnnnnnnnnnnnnnnncct