Oracle大事务回滚

包含如下：–>注意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$rollnamewhereusnin(selectuniquektuxeusnfromx$ktuxe
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$ktuxewhereKTUXEUSN=10andKTUXESLT=39;---------这里根据实际数字来填写dbm{s}_{l}ock.sleep(60);---------单位是秒，建议适当增大一些，评估出的时间更准确。selectktuxesizinto{l}_{e}ndfromx$ktuxe
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$ktuxewhereKTUXEUSN=10andKTUXESLT=39;dbm{s}_{l}ock.sleep(60);selectktuxesizinto{l}_{e}ndfromx$ktuxe
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}essionpx,gv$session 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$fas{t}_{s}tar{t}_{t}ransactions;或者select\ast fromx$ktuxe 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.收集事务恢复相关信息的脚本

1. Complete Database Alert log (at least logs from last 2sucessful startups)
2. SMON Trace file (if available)
3. 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$ktuxewherektuxesta<{>}^{\prime }INACTIV{E}^{\prime }andktuxecfllik{e}^{\prime }selectusn,state,undoblockstotal"Total",undoblocksdone"Done",undoblockstotal-undoblocksdone"ToDo",decode(cputime,0{,}^{\prime }unknow{n}^{\prime },sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400))"Estimatedtimetocomplete"fromv$fast_start_transactions;
   select * from v$fas{t}_{s}tar{t}_{s}ervers;selectktuxeusn,t{o}_{c}har(sysdate{,}^{\prime }DD-MON-YYYYHH24:MI:S{S}^{\prime })"Time",ktuxesiz,ktuxestafromx$ktuxe where ktuxecfl = ‘DEAD’;
   set markup html off
   spool off

---