包含如下:–>注意RAC时其它实例的问题,或使用GVKaTeX parse error: Expected 'EOF', got '#' at position 152: ….收集事务恢复相关信息的脚本 #̲###############…ktuxe
where ktuxesta <> ‘INACTIVE’
and ktuxecfl like ‘%DEAD%’
order by ktuxesiz asc;
select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;
KTUXECFL COUNT(*)
------------------------ ----------
DEAD 1
NONE 2393
SCO|COL 8
===========
ktuxeusn – Undo Segment Number
ktuxeslt – Slot number
ktuxesqn – Sequence
ktuxesta – State
ktuxesiz – Undo Blocks Remaining
ktuxecfl – Flag
USN SLOT SEQ STATE UNDO
9 9 335 ACTIVE 10337
##############################################
2.查询包含死事务的UNDO段的信息:
select useg.segment_name, useg.segment_id, useg.tablespace_name, useg.status
from dba_rollback_segs useg
where useg.segment_id in (select unique ktuxeusn
from x$ktuxe
where ktuxesta <> ‘INACTIVE’
and ktuxecfl like ‘%DEAD%’);
SEGMENT_NAME SEGMENT_ID TABLESPACE_NAME STATUS
_SYSSMU9$ 9 UNDOTBS1 ONLINE
select usn,name
from v
r
o
l
l
n
a
m
e
w
h
e
r
e
u
s
n
i
n
(
s
e
l
e
c
t
u
n
i
q
u
e
k
t
u
x
e
u
s
n
f
r
o
m
x
rollname where usn in (select unique ktuxeusn from x
rollnamewhereusnin(selectuniquektuxeusnfromxktuxe
where ktuxesta <> ‘INACTIVE’
and ktuxecfl like ‘%DEAD%’);
##############################################
3.大事务恢复所需的时间
alter session set nls_date_format=‘yyyymmdd hh24:mi:ss’;
select usn, state, undoblockstotal “Total”, undoblocksdone “Done”, undoblockstotal-undoblocksdone “ToDo”,
decode(cputime,0,‘unknown’,sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Estimated time to complete”
from v$fast_start_transactions;
USN STATE Total Done ToDo Estimated time to Complete
5 RECOVERING 16207 14693 1514 11-may-2012 08:05:40
####################################################
4.对于死事务回滚所需时间的评估:
select distinct KTUXECFL,count() from x$ktuxe group by KTUXECFL;
KTUXECFL COUNT()
------------------------ ----------
DEAD 1
NONE 2393
SCO|COL 8
select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL=‘DEAD’;
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
00002B92FF5D5F68 15 12 314961 43611
–KTUXESIZ代表需要回滚的回滚块数。
declare
l_start number;
l_end number;
begin
select ktuxesiz
into l_start
from x
k
t
u
x
e
w
h
e
r
e
K
T
U
X
E
U
S
N
=
10
a
n
d
K
T
U
X
E
S
L
T
=
39
;
−
−
−
−
−
−
−
−
−
这里根据实际数字来填写
d
b
m
s
l
o
c
k
.
s
l
e
e
p
(
60
)
;
−
−
−
−
−
−
−
−
−
单位是秒,建议适当增大一些,评估出的时间更准确。
s
e
l
e
c
t
k
t
u
x
e
s
i
z
i
n
t
o
l
e
n
d
f
r
o
m
x
ktuxe where KTUXEUSN = 10 and KTUXESLT = 39; ---------这里根据实际数字来填写 dbms_lock.sleep(60); ---------单位是秒,建议适当增大一些,评估出的时间更准确。 select ktuxesiz into l_end from x
ktuxewhereKTUXEUSN=10andKTUXESLT=39;−−−−−−−−−这里根据实际数字来填写dbmslock.sleep(60);−−−−−−−−−单位是秒,建议适当增大一些,评估出的时间更准确。selectktuxesizintolendfromxktuxe
where KTUXEUSN = 10
and KTUXESLT = 39; ---------这里根据实际数字来填写
dbms_output.put_line(‘time cost Day:’ ||
round(l_end / (l_start - l_end) / 60, 2));
end;
/
(l_start - l_end) -->dbms_lock.sleep(60);指定的60秒间隔恢复的块数
l_end -->还需要恢复的块数
l_end / (l_start - l_end) /得出所需恢复的分钟数;
------------------------------------------得出所需小时
declare
l_start number;
l_end number;
begin
select ktuxesiz
into l_start
from x
k
t
u
x
e
w
h
e
r
e
K
T
U
X
E
U
S
N
=
10
a
n
d
K
T
U
X
E
S
L
T
=
39
;
d
b
m
s
l
o
c
k
.
s
l
e
e
p
(
60
)
;
s
e
l
e
c
t
k
t
u
x
e
s
i
z
i
n
t
o
l
e
n
d
f
r
o
m
x
ktuxe where KTUXEUSN = 10 and KTUXESLT = 39; dbms_lock.sleep(60); select ktuxesiz into l_end from x
ktuxewhereKTUXEUSN=10andKTUXESLT=39;dbmslock.sleep(60);selectktuxesizintolendfromxktuxe
where KTUXEUSN = 10
and KTUXESLT = 39;
dbms_output.put_line(‘time cost Day:’ ||
round(l_end / (l_start - l_end)/60, 2));
end;
/
round(l_end / (l_start - l_end)/60, 2)部分可以修改为:
TO_CHAR(sysdate+(l_end/(l_start-l_end)*60/86400),‘YYYY/MM/DD HH24:MI:SS’)
显示评估的到什么时间可以恢复完成。
##############################################
5.查询并行恢复时Slaves进程数量
select * from v$fast_start_servers;
STATE UNDOBLOCKSDONE PID XID
RECOVERING 54 18 001F005C00001BD6
RECOVERING 0 20 001F005C00001BD6
如果只有1个进程STATE 是RECOVERING ,其它都是IDLE; 建议关闭并行回滚–>FAST_START_PARALLEL_ROLLBACK=FALSE。
##############################################
6.查询事务恢复涉及的对象–>查字典表
查出SID:
select decode(px.qcinst_id,NULL,username,’ - '||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”,
decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
to_char( px.server_set) “Slave Set”,
to_char(s.sid) “SID”,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”,
px.req_degree “Requested DOP”,
px.degree “Actual DOP”
from gv
p
x
s
e
s
s
i
o
n
p
x
,
g
v
px_session px, gv
pxsessionpx,gvsession s
where px.sid=s.sid (+)
and px.serial#=s.serial#
order by 5 , 1 desc ;
根据SID查出对象ID:
select distinct current_obj#
from v$active_session_history
where SESSION_ID=145
and SESSION_SERIAL#=15;
–(This Query to be run multiple times and you should be able to see the Current Object id consistently)
根据object_id查 Object_name
select Object_name, object_type
from dba_objects
where object_id=77211;
##############################################
7.查询事务恢复涉及的对象–>DUMP UNDO段
通过下面的语句查到回滚的事务:
select * from v
f
a
s
t
s
t
a
r
t
t
r
a
n
s
a
c
t
i
o
n
s
;
或者
s
e
l
e
c
t
∗
f
r
o
m
x
fast_start_transactions; 或者 select * from x
faststarttransactions;或者select∗fromxktuxe where KTUXECFL=‘DEAD’ AND KTUXESTA!=‘INACTIVE’
根据上面的语句,我们可以查到事务的undo的segment id(USN或者KTUXEUSN),undo的slot(SLT或者KTUXESLT),和undo的sequence(SEQ或者KTUXESQN)。
根据USN,我们可以查到undo segment:
select * from v$rollname where usn=xxx
DUMP相应段:
alter system dump undo block “” XID ;
例如:alter system dump undo block “_SYSSMU33$” XID 33 56 7463;
dump出来的trace file,可以判断object number(objn)或者object id(objd)
cat xxx.trc |grep objn
根据object_id查 Object_name
select Object_name, object_type
from dba_objects
where object_id=77211;
################################
8.收集事务恢复相关信息的脚本
- Complete Database Alert log (at least logs from last 2sucessful startups)
- SMON Trace file (if available)
- Undo Block dump Trace file (if taken).
----------------------收集信息命令脚本:
spool /tmp/transaction_recovery.html
set markup html on
set time on
alter session set nls_date_format=‘dd-mon-yyyy hh24:mi:ss’;
select sysdate from dual;
show parameter fast_start_parallel_rollback
select ktuxeusn USN, ktuxeslt Slot, ktuxesqn Seq, ktuxesta State,ktuxesiz Undo from x k t u x e w h e r e k t u x e s t a < > ′ I N A C T I V E ′ a n d k t u x e c f l l i k e ′ s e l e c t u s n , s t a t e , u n d o b l o c k s t o t a l " T o t a l " , u n d o b l o c k s d o n e " D o n e " , u n d o b l o c k s t o t a l − u n d o b l o c k s d o n e " T o D o " , d e c o d e ( c p u t i m e , 0 , ′ u n k n o w n ′ , s y s d a t e + ( ( ( u n d o b l o c k s t o t a l − u n d o b l o c k s d o n e ) / ( u n d o b l o c k s d o n e / c p u t i m e ) ) / 86400 ) ) " E s t i m a t e d t i m e t o c o m p l e t e " f r o m v ktuxe where ktuxesta <> 'INACTIVE' and ktuxecfl like '%DEAD%' order by ktuxesiz asc; select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" from v ktuxewherektuxesta<>′INACTIVE′andktuxecfllike′selectusn,state,undoblockstotal"Total",undoblocksdone"Done",undoblockstotal−undoblocksdone"ToDo",decode(cputime,0,′unknown′,sysdate+(((undoblockstotal−undoblocksdone)/(undoblocksdone/cputime))/86400))"Estimatedtimetocomplete"fromvfast_start_transactions;
select * from v f a s t s t a r t s e r v e r s ; s e l e c t k t u x e u s n , t o c h a r ( s y s d a t e , ′ D D − M O N − Y Y Y Y H H 24 : M I : S S ′ ) " T i m e " , k t u x e s i z , k t u x e s t a f r o m x fast_start_servers; select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta from x faststartservers;selectktuxeusn,tochar(sysdate,′DD−MON−YYYYHH24:MI:SS′)"Time",ktuxesiz,ktuxestafromxktuxe where ktuxecfl = ‘DEAD’;
set markup html off
spool off