undo的管理方式

[@more@]

-- ==============第8章調整UNDO段================= --

-- ==========8.3.1 人工管理undo================--

-- 修改init.ora檔案,設定
undo_management=MANUAL

-- 重啟資料庫,使用pfile
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=c:oracleadminorcl2pfileinit.ora
ORACLE instance started.

Total System Global Area 101785428 bytes
Fixed Size 454484 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-01991: invalid password file 'C:oracleora92DATABASEPWDorcl2.ORA'
E:>set ORACLE_SID=orcl2
E:>orapwd.exe file=C:oracleora92databasePWDorcl2.ora password=change_on_install

OPW-00005: File with same name exists - please delete or rename
E:> del C:oracleora92databasePWDorcl2.ora
E:> orapwd.exe file=C:oracleora92databasePWDorcl2.ora password=change_on_install
E:> sqlplus /nolog
SQL> conn / as sysdba
SQL> startup pfile=c:oracleadminorcl2pfileinit.ora

-- 檢查undo管理方式
SQL> show parameter undo_management

NAME TYPE VALUE
------------------------------------ ----------- -------
undo_management string MANUAL

-- 是否有自動帶起的undo表空間?
SQL> show parameter rollback_segments

NAME TYPE VALUE
------------------------------------ ----------- -------
max_rollback_segments integer 37
rollback_segments string

-- 檢視現在的undo表空間回滾段
select owner,segment_name,status
from dba_rollback_segs;
OWNER SEGMENT_NAME STATUS
------ ------------------------------ --------
SYS SYSTEM ONLINE
PUBLIC _SYSSMU1$ OFFLINE
PUBLIC _SYSSMU2$ OFFLINE
PUBLIC _SYSSMU3$ OFFLINE
PUBLIC _SYSSMU4$ OFFLINE
PUBLIC _SYSSMU5$ OFFLINE
PUBLIC _SYSSMU6$ OFFLINE
PUBLIC _SYSSMU7$ OFFLINE
PUBLIC _SYSSMU8$ OFFLINE
PUBLIC _SYSSMU9$ OFFLINE
PUBLIC _SYSSMU10$ OFFLINE

-- 建立一個undo 表空間
create undo tablespace rbs_htjs01
datafile 'c:oracleoradataorcl2rbs_htjs01.dbf' size 60m ;
-- 在這個undo表空間上建立rollback segment
create public rollback segment rbs01
tablespace rbs_htjs01
storage( initial 2m next 2m optimal 5m minextents 2 maxextents unlimited)
/
-- 同樣的方法建立rbs02 rbs03 rbs04 rbs05

-- 建立另外一個undo 表空間
create undo tablespace rbs_htjs02
datafile 'c:oracleoradataorcl2rbs_htjs02.dbf' size 50m ;
create public rollback segment rbs_batch
tablespace rbs_htjs02
storage( initial 10m next 2m optimal 20m minextents 2 maxextents unlimited)
/

-- 檢視現在的undo表空間驗證上面建立的回滾段
select owner,segment_name,status
from dba_rollback_segs;
OWNER SEGMENT_NAME STATUS
------ ------------------------------ ----------
SYS SYSTEM ONLINE
PUBLIC _SYSSMU1$ OFFLINE
PUBLIC _SYSSMU2$ OFFLINE
PUBLIC _SYSSMU3$ OFFLINE
PUBLIC _SYSSMU4$ OFFLINE
PUBLIC _SYSSMU5$ OFFLINE
PUBLIC _SYSSMU6$ OFFLINE
PUBLIC _SYSSMU7$ OFFLINE
PUBLIC _SYSSMU8$ OFFLINE
PUBLIC _SYSSMU9$ OFFLINE
PUBLIC _SYSSMU10$ OFFLINE
PUBLIC _SYSSMU11$ OFFLINE
PUBLIC _SYSSMU12$ OFFLINE
PUBLIC _SYSSMU13$ OFFLINE
PUBLIC _SYSSMU14$ OFFLINE
PUBLIC _SYSSMU15$ OFFLINE
PUBLIC _SYSSMU16$ OFFLINE
PUBLIC _SYSSMU17$ OFFLINE
PUBLIC _SYSSMU18$ OFFLINE
PUBLIC _SYSSMU19$ OFFLINE
PUBLIC _SYSSMU20$ OFFLINE
PUBLIC RBS01 OFFLINE
PUBLIC RBS02 OFFLINE
PUBLIC RBS03 OFFLINE
PUBLIC RBS04 OFFLINE
PUBLIC RBS05 OFFLINE
PUBLIC _SYSSMU26$ OFFLINE
PUBLIC _SYSSMU27$ OFFLINE
PUBLIC _SYSSMU28$ OFFLINE
PUBLIC _SYSSMU29$ OFFLINE
PUBLIC _SYSSMU30$ OFFLINE
PUBLIC _SYSSMU31$ OFFLINE
PUBLIC _SYSSMU32$ OFFLINE
PUBLIC _SYSSMU33$ OFFLINE
PUBLIC _SYSSMU34$ OFFLINE
PUBLIC _SYSSMU35$ OFFLINE
PUBLIC RBS_BATCH OFFLINE

--修改init.ora 設定rollback_segments引數加上rbs01 rbs02...rbs_batch
-- 重啟資料庫,檢視這幾個回滾段的線上狀態應為online
SQL> show parameter rollback_segments

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_rollback_segments integer 37
rollback_segments string rbs01, rbs02, rbs03, rbs04, rb
s05, rbs_batch

select owner,segment_name,status
from dba_rollback_segs
where status = 'ONLINE'
/

OWNER SEGMENT_NAME STATUS
------ ------------------------------ ----------------
SYS SYSTEM ONLINE
PUBLIC RBS01 ONLINE
PUBLIC RBS02 ONLINE
PUBLIC RBS03 ONLINE
PUBLIC RBS04 ONLINE
PUBLIC RBS05 ONLINE
PUBLIC RBS_BATCH ONLINE

7 rows selected.

-- 驗證目前資料庫中的undo表空間
select tablespace_name,status,contents
from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
INDX ONLINE PERMANENT
TOOLS ONLINE PERMANENT
USERS ONLINE PERMANENT
RBS_HTJS01 ONLINE UNDO
RBS_HTJS02 ONLINE UNDO

-- ==========8.3.2 自動管理undo================--
-- 首先清除在手工管理下建立的undo表空間
alter rollback segment rbs01 offline;
alter rollback segment rbs02 offline;
alter rollback segment rbs03 offline;
alter rollback segment rbs04 offline;
alter rollback segment rbs05 offline;

drop tablespace rbs_htjs01
including contents and datafiles ;

alter rollback segment rbs_batch offline;

drop tablespace rbs_htjs02
including contents and datafiles ;

--============= undo大小設定的指導原則 =============--
create public rollback segment rbs_batch
tablespace rbs_htjs02
storage( initial 10m next 2m optimal 20m minextents 2 maxextents unlimited)

-- 在建立回滾段時,有以下幾個儲存引數, 及建議值
initial -- 平均事務大小 * 併發事務數量
next -- 要等於initial
optimal -- 要大於initial + next
minextents -- 2 ~ 20
maxextents -- unlimited

-- 如何計算平均事務大小? 如何計算併發事務數量?
-- V$TRANSACTION
select count(*) as concurrent_trans,
avg(used_ublk) as average_transaction_size
from v$transaction ;
例如:上述查詢結果為 3個併發事務 6408個平均使用塊數. 則:
initial = 6408 blocks * 8 個併發
next = initial

假定大事務正消耗10000~15000個塊, optimal 應設定為125000這個中間數

-- 按照當前的事務數量, 需要的undo有多大?
select ceil(undo_retention * (block_per_second * block_size) * 1.01) as undo_size
from (select value undo_retention
from v$parameter
where name = 'undo_retention') retention,
(select value block_size
from v$parameter
where name = 'db_block_size') block,
(select sum(undoblks) / sum((end_time - begin_time) * 24 * 60 * 60) block_per_second
from v$undostat) undo_blocks

-- 得到每一個回滾段的當前資訊
select t.USN,t.RSSIZE,t.WRITES,t.SHRINKS,t.EXTENDS from v$rollstat t;

USN RSSIZE WRITES SHRINKS EXTENDS
------- --------- -------- --------- ---------
0 507904 5640 0 0
1 1163264 69059038 1022 4403
2 1163264 3719095802 401 2061
3 1163264 31929720 320 1646
4 1163264 1595824632 362 1954
5 1163264 677031086 359 1900
6 1163264 167228412 377 2025
7 1163264 60612486 374 1975
8 1163264 3201194346 359 1711
9 8503296 3866071994 128 798
20 1163264 4083546036 176 1087

-- 回滾段標題爭用上限1%
select name rbs_name,
gets,
waits,
round((waits / gets) * 100, 2) header_ratio
from v$rollname r, v$rollstat s
where s.USN = r.usn

--
-- 所有回滾段的塊標題比率 5%的上限
select sum(gets),
sum(waits),
round(sum(waits) / sum(gets) * 100, 2) header_ratio
from v$rollname r, v$rollstat s
where s.USN = r.usn

-- 查詢段標題爭用等待事件
select event, total_waits, time_waited
from v$system_event
where event like '%undo%'

-- 回滾段緩衝器的爭用 1%的上限
select ((w1.COUNT + w2.COUNT + w3.COUNT + w4.COUNT) /
(st1.VALUE + st2.VALUE)) * 100 ratio
from v$waitstat w1,
v$waitstat w2,
v$waitstat w3,
v$waitstat w4,
v$sysstat st1,
v$sysstat st2
where w1.CLASS = 'system undo header'
and w2.CLASS = 'system undo block'
and w3.CLASS = 'undo header'
and w4.CLASS = 'undo block'
and st1.NAME = 'db block gets'
and st2.NAME = 'db block changes'

-- 當前事務使用的回滾段
select r.name segment_name,
s.USERNAME,
s.OSUSER,
s.SID,
s.SERIAL#,
t.USED_UBLK trans_used,
rs.AVEACTIVE,
rs.EXTENDS,
rs.WAITS,
rs.SHRINKS,
rs.WRAPS
from v$rollstat rs, v$rollname r, v$session s, v$transaction t
where rs.USN(+) = r.usn
and t.XIDUSN(+) = r.usn
and t.ADDR = s.TADDR(+)

-- 活動的事務使用的回滾段情況
select r.name segment_name,
s.USERNAME,
s.OSUSER,
s.SID,
s.SERIAL#,
t.USED_UBLK trans_used,
t.STATUS,
t.CR_GET,
t.PHY_IO,
t.USED_UBLK,
t.NOUNDO
from v$rollname r, v$session s, v$transaction t
where t.XIDUSN = r.usn
and t.ADDR = s.TADDR

-- 回滾段以及相關的程式
select r.name segment,
p.pid,
p.spid,
nvl(p.username, 'No tra') username,
p.terminal
from v$lock l, v$process p, v$rollname r
where l.SID = p.PID(+)
and trunc(l.ID1(+) / 65536) = r.usn
and l.TYPE(+) = 'TX'
and l.LMODE(+) = 6
order by 1

-- 回滾段的持續時間查詢

select r.name,
round(24 *
((sysdate - startup_time) - trunc(sysdate - startup_time)) /
(s.writes / s.rssize),
1) hours
from v$instance i, v$rollname r, v$rollstat s
where r.usn = s.usn
and s.STATUS = 'ONLINE'

-- 有多少提交和回滾
select a.value commits, b.value rollbacks, a.value + b.value total
from v$sysstat a, v$sysstat b
where a.name = 'USER COMMITS'
and b.name = 'USER ROLLBACKS'

------===================== 如何診斷UNDO? ==========================--
-- 1 由於UNDO段空間不足失敗的次數,接近0正常.
select sum(nospaceerrcnt) nospace_cnt from v$undostat

-- 2 snapshot too old的錯誤次數
select sum(ssolderrcnt) from v$undostat

-- 3 偷竊(未過期的盤區從其他事務)的次數
select sum(unxpstealcnt) from v$undostat

-- 4 事務的高峰時間,undo段的用量
select to_char(begin_time, 'dd-mon-yyyy hh24:mi:ss') begin_time,
to_char(end_time, 'dd-mon-yyyy hh24:mi:ss') end_time,
undoblks,
txncount,
maxconcurrency
from v$undostat
where rownum < 20
order by 3 desc

-- 5 最長的事務有哪些?
select to_char(begin_time, 'yyyymmdd hh24:mi:ss') begin_time,
to_char(end_time, 'yyyymmdd hh24:mi:ss') end_time,
undoblks,
txncount,
maxconcurrency
from v$undostat
where undoblks = (select max(undoblks) from v$undostat)

-- 如何進行閃回查詢?
一查當前的SCN號
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
94701049

二嘗試找到合適的恢復點,並備份資料
create table temp_lxr as
select khid,bgdd,lxr,dh from c_khxx_zb as of scn 94625937

-- 如何處理長事務使用過多的回滾段. 避免ORA-01650錯誤
set transaction use rollback segment rbs01;
insert into ...
commit;
-- 以上必須在手動管理回滾段的模式下進行.

9i筆記-undo

相關文章