undo的管理方式

-- ==============第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;
-- 以上必须在手动管理回滚段的模式下进行.