oracle rollback 觀察時間

標籤：speed   script   進程   put   number   where   async   如何   mini   

###########issue 0: db alert 有如下提示， thread 1 cannot allocatete new log, sequenec 1111

通過檢查v$log ,發現10組日誌，每組1G, 一個小時產生10G 日誌，應該是夠用的。

懷疑是沒有提交的巨量事物導致這個報錯：

檢查
SELECT a.used_ublk
FROM v$transaction a, v$session b
WHERE a.addr = b.taddr

 

####issue 1 檢查復原的時間，使用alter system kill session 方法，觀察方法如下：
pmon Monitor the Rollback progress ,after alter system kill session "pid,serial", the ospid is still exsist after kill

after kill , the ospid 14001 still exsit;
lsof -p 14001 can check process

Problem Description
-------------------

You did not commit your transactions and the session were accidentally
killed. Your transactions are rolling back and it is taking a long time.
Rollback started hours ago and is still in progress.

You want to know if there is any way to speed up the process such as using
cleanup_rollback_entries in the init.ora and then restarting the database.

You also want to know what will happen if you shutdown the database after 12
hours of rollback. Will the rollback pick up where it left off?

SQL> SELECT a.used_ublk
FROM v$transaction a, v$session b
WHERE a.addr = b.taddr AND b.sid = 206;

For example:

If used_ublk showed 29,900 12 hours ago and is now 22,900, it has
taken 12 hours to rollback 7,000 entries. It will take approximately
another 36 hours to complete depending on the types of transactions
that are rolling back.

SELECT USED_UBLK FROM V$TRANSACTION;

select ses.username,ses.sid,substr(ses.program, 1, 19) command,tra.used_ublk from v$session ses, v$transaction tra where ses.saddr = tra.ses_addr;

select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v_$transaction t,
sys.v_$session s
where
x.inst_id = userenv(‘Instance‘) and
x.ktuxesta = ‘ACTIVE‘ and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;

 

或者使用自動化指令碼（取自網路）

set serveroutput on

declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v_$transaction t,
sys.v_$session s
where
x.inst_id = userenv(‘Instance‘) and
x.ktuxesta = ‘ACTIVE‘ and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usn number;
xid_slot number;
xid_sqn number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv(‘Instance‘) and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = ‘ACTIVE‘;
if used_ublk2 < used_ublk1
then
sys.dbms_output.put_line(
user_name ||
‘‘‘s transaction ‘ ||
xid_usn || ‘.‘ ||
xid_slot || ‘.‘ ||
xid_sqn ||
‘ will finish rolling back at approximately ‘ ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
‘HH24:MI:SS DD-MON-YYYY‘
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line(‘No transactions appear to be rolling back.‘);
end if;
end;

 

/db/aa/oradata對應的是不是這塊盤 VxVM27001

 

 

Solution Description
--------------------
沒有辦法加快rollback 的速度。

There is no way to speed up the rollback process and there is no formula for
determining how long it will take to complete. It depends on what type of
undo the application has generated. Some undo may take little space in an
undo block, but may take awhile to apply.

You can look at used_ublk in V$transaction to estimate how long it is going
to take to complete the rollback.

SQL> SELECT a.used_ublk
FROM v$transaction a, v$session b
WHERE a.addr = b.taddr AND b.sid = <SID>;

For example:

If used_ublk showed 29,900 12 hours ago and is now 22,900, it has
taken 12 hours to rollback 7,000 entries. It will take approximately
another 36 hours to complete depending on the types of transactions
that are rolling back.

CLEANUP_ROLLBACK_ENTRIES determines how long SMON will be holding onto one
transaction‘s resources. It only affects recovery of transactions in the
background such as after an instance crash. It doesn‘t affect rollback
by the transaction itself.

Rollback will pick up where it left off if you do shutdown after 12 hours
of rollback.

Solution Explanation
--------------------

You can use V$transaction used_ublk to estimate how long the rollback is
going to take but there is no formula for this. If you shutdown the
database after rollback has started, it will begin where it left off.

For Oracle 9i and onwards ,check :
SQL> SELECT DISTINCT ktuxesiz
FROM x$ktuxe
WHERE ktuxecfl=‘DEAD‘;

########### 檢查復原的時間，kill -9 的方法殺進程，觀察方法如下：
issue 2 smon Transaction recovery by SMON , the ospid is not exsist.

###monitor

SMON process takes over the recovery when

->Server process is dead / crashed. the ospid is not exsist.
->Instance itself is crashed

3. Speed up SMON transaction recovery
SMON transaction recovery can be controlled using the FAST_START_PARALLEL_ROLLBACK parameter

a. Parallel Transaction Recovery:

To enable Parallel recovery mode, set the parameter FAST_START_PARALLEL_ROLLBACK to LOW / HIGH.

ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH
OR
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = LOW
b. If the parallel recovery is hanging, enable serial recovery:

To enable serial recovery mode, set the parameter FAST_START_PARALLEL_ROLLBACK to FALSE.

PS: The parameter FAST_START_PARALLEL_ROLLBACK will be effective only when SMON does the transaction recovery (generally after a instance crash).

 

Monitor the transaction recovery by SMON
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

-------- ---------------- -------- -------- -------- ---------------------------------

->none

####solution:

You may notice that UNDOBLOCKSDONE is not increasing or increases very slowly.

1、停止並行復原，減少IO請求，快速提升系統響應能力
如果你沒時間等待復原進程完成復原操作，可根據如下提示進行操作。
最後在google上根據ora_p001, wait for a undo record 的關鍵字，找到了一些資訊，以下資訊引起了我的注意：
Oracle工程師首先懷疑是暫存資料表空間空間不足導致，經檢查暫存資料表空間沒有空間不足的情況，仔細觀察日誌發現重做記錄檔不斷切換，分析應該是有較多的事務沒有完成提交或者有較多沒有提交的事務完成復原。現在面臨的問題是我們沒有很多時間去等待所有的事務去完成復原或提交。解決問題的思路就是如何儘快結束這些事務的復原或提交。
1) 查看spfile檔案中是否有fast_start_parallel_rollback參數的設定，檢查結果G網資料庫沒有設定該參數。如果沒有顯式設定，則該參數的預設值為low。修改該參數值為false
　　2) 將資料庫啟動到nomount狀態：startup nomount
　　3) 修改改參數值：alter system set fast_start_parallel_rollback = FALSE scope=spfile
　　4) shutdown immediate關閉資料庫
　　5) startup啟動
　　6) 查看該參數是否生效：show parameter fast_start_parallel_rollback
　　7) 等待一段時間
8) shutdown immediate資料庫可以關閉
2、加快復原速度
提高並行復原進程的數量，設定為HIGH時復原進程=4*cpu數。在sql命令列模式下執行
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH

refer http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=116213&id=147411

 

#####issue 3:

set lines 200
set pagesize 2000
col name for a65
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
WHERE F.FILE#=I.FILE_NO
AND FILETYPE_NAME=‘Data File‘;

 

oracle rollback 觀察時間