Category Archives: Oracle

ARCn: Media recovery disabled原因分析

Posted on by

1.数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

2.alert日志出现类此记录

Fri Feb 10 05:16:55 2012
ARC1: Media recovery disabled
Fri Feb 10 05:17:23 2012
ARC0: Media recovery disabled
Fri Feb 10 05:17:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:18:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:19:12 2012
Thread 1 advanced to log sequence 24
  Current log# 1 seq# 24 mem# 0: /tmp/recover/redo01.log
Fri Feb 10 05:19:12 2012
ARC0: Media recovery disabled
Fri Feb 10 05:19:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:20:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:21:23 2012
ARC1: Media recovery disabled

3.数据库归档信息

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /tmp/recover
Oldest online log sequence     25
Current log sequence           27

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
log_archive_start                    boolean     TRUE

从这里可以看出，数据库处于非归档模式，但是log_archive_start=true，这样会导致归档进程自动启动(ARCn),因为数据库设置为非归档模式，从而导致该进程启动后，又自己关闭

4.解决方法

SQL> alter system set log_archive_start=false scope=spfile;

System altered.

SQL> startup force      
ORACLE instance started.

Total System Global Area  202445176 bytes
Fixed Size                   450936 bytes
Variable Size             167772160 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
log_archive_start                    boolean     FALSE
SQL> ARCHIVE LOG LIST;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /tmp/recover
Oldest online log sequence     26
Current log sequence           28

ALERT中无类此记录出现

手动提交分布式事务一例

Posted on by

一.alert文件中出现了很多类此记录

Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name
Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name

这里可以看出来两个信息：
1.出错的进程是rec0进程，而该进程的作用是解决分布式事务失败后遗留问题(事务提交或者回滚)
2.错误信息是tns不能被解析

二.查看trace文件

/tmp/recover/ahcx216_reco_7956.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.9-89.0.0.0.1.ELhugemem
Version:        #1 SMP Tue May 19 04:38:38 EDT 2009
Machine:        i686
Instance name: ahcx216
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 7956, image: oracle@localhost.localdomain (RECO)


*** SESSION ID:(6.1) 2012-02-10 04:58:24.886
*** 2012-02-10 04:58:24.886
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
………………
*** 2012-02-10 05:25:01.580
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
*** 2012-02-10 05:25:01.627
ERROR, tran=12.19.99059, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name

通过这里我们可以看出事务id分别为12.19.99059和6.1.712757有问题

三.查看dba_2pc_pending视图

SQL> select local_tran_id,state,fail_time,retry_time from dba_2pc_pending;

LOCAL_TRAN_ID     STATE            FAIL_TIME           RETRY_TIME
-------------- ---------------- --------------       --------------
6.1.712757	collecting	2010/12/1 13:39:03   2012/2/10 5:38:52
12.19.99059	collecting	2010/12/1 15:56:26   2012/2/10 5:38:52

可以看出，果然有两个分布式事务在2010年12月1日出了问题(本库是一个问题库，在把库拉起来后发现该问题)
因为是异机恢复，而且间隔时间较长，很多tns的信息都已经不存在，所以需要手工提交分布式事务

四.手动提交事务

SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 2

SQL> alter session set "_smu_debug_mode"=4;

Session altered.

-------------------------------------------------------------
--设置UNDO_SUPPRESS_ERRORS=true也可以解决此问题
alter system set UNDO_SUPPRESS_ERRORS = TRUE;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<事务ID>');
commit;
alter system set UNDO_SUPPRESS_ERRORS = false;
--------------------------------------------------------------

SQL> commit;

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');

PL/SQL procedure successfully completed.


SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059'); END;

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
--第一个分布式事务处理后，未提交导致

SQL> commit;

Commit complete.

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

五.补充说明
开始在另一个会话中，执行失败原因

SQL> alter session set "_smu_debug_mode"=4;

会话已更改。

SQL> commit;

提交完成。

SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 3

因为这里的begin end中包含了两个事务的清理，在清理完第一个事务之后，需要提交才能够清理第二个，这里因为没有提交，导致ORA-01453错误。

通过sql语句获取awr/statspack逻辑读/物理读

Posted on by

在日常的性能监控中，我们经常需要需要通过一段时间内数据库的逻辑读/物理读来大致反映数据库的性能情况，这里列出通过awr和statspack来获取相关数据（查询最近30天数据，除掉第一条和最后一条数据）

awr逻辑读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM DBA_HIST_SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
          AND b.INSTANCE_NUMBER = &INST_NUM
     AND B.STAT_NAME IN ('session logical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
    FROM DBA_HIST_SNAPSHOT B
   WHERE  B.DBID = (SELECT dbid FROM v$database)
     AND B.INSTANCE_NUMBER = &INST_NUM) B
     WHERE A.SNAP_ID=B.snap_id
     AND END_INTERVAL_TIME>=SYSDATE-30;

awr物理读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM DBA_HIST_SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
          AND b.INSTANCE_NUMBER = &INST_NUM
     AND B.STAT_NAME IN ('physical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
    FROM DBA_HIST_SNAPSHOT B
   WHERE  B.DBID = (SELECT dbid FROM v$database)
     AND B.INSTANCE_NUMBER = &INST_NUM) B
     WHERE A.SNAP_ID=B.snap_id
     AND END_INTERVAL_TIME>=SYSDATE-30;

statspack逻辑读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM STATS$SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
     AND B.INSTANCE_NUMBER = &INST_NUM
     AND B.NAME IN ('session logical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,
       (SELECT SNAP_TIME, SNAP_ID
          FROM STATS$SNAPSHOT B
         WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = &INST_NUM) B
 WHERE A.SNAP_ID = B.SNAP_ID
  AND snap_time>=SYSDATE-30;

statspack物理读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM STATS$SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
     AND B.INSTANCE_NUMBER = &INST_NUM
     AND B.NAME IN ('physical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,
       (SELECT SNAP_TIME, SNAP_ID
          FROM STATS$SNAPSHOT B
         WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = &INST_NUM) B
 WHERE A.SNAP_ID = B.SNAP_ID
 AND snap_time>=SYSDATE-30;

ODU恢复PACKAGE/VIEW/DBLINK等数据脚本操作

Posted on by

当数据库不能拉起来时候，我们可以通过odu找回丢失的数据，今天客户出要求，需要我帮忙找回不能起来库中的包，过程，函数，视图，触发器等之类东西。这些东西都是存在system空间中，存在一些系统的基表中，我们如果能够拿到这些基表的数据然后进行处理，原则上就可以得到客户需要的东西
一、PROCEDURE/FUNCTION/PACKAGE/PACKAGE BODY/TRIGGER/TYPE/TYPE BODY之类恢复
1. 查看这些数据存储在什么基表中

select u.name, o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
               11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
               'UNDEFINED'),
s.line, s.source
from sys.obj$ o, sys.source$ s, sys.user$ u
where o.obj# = s.obj#
  and o.owner# = u.user#
  and ( o.type# in (7, 8, 9, 11, 12, 14) OR
       ( o.type# = 13 AND o.subname is null))
union all
select u.name, o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
from sys.obj$ o, x$joxfs s, sys.user$ u
where o.obj# = s.joxftobn
  and o.owner# = u.user#
  and o.type# = 28;

通过这个sql知道主要存储在sys.obj$ o, sys.source$ s, sys.user$中（至于x$joxfs表，我暂时在数据库中未找到，不能使用odu提取数据，也就是说我这里不能恢复出来JAVA SOURCE）

2. 使用odu导出数据（注意output_format设置为dmp，防止出现意外）
使用类此unload table sys.obj$ 下面语句导出数据

3. 加载这些数据到新库中(切记不要使用sys schema)
使用类此imp chf/xifenfei file=d:/SYS_OBJ$.dmp fromuser=sys touser=chf导入数据

4. 在查询出需要对象

   SELECT u.name USER_NAME,
         o.name OBJECT_nAME,
         DECODE (o.type#,
                 7, 'PROCEDURE',
                 8, 'FUNCTION',
                 9, 'PACKAGE',
                 11, 'PACKAGE BODY',
                 12, 'TRIGGER',
                 13, 'TYPE',
                 14, 'TYPE BODY',
                 'UNDEFINED')
            OBJECT_tYPE,
         s.line,
         s.source
    FROM chf.obj$ o, chf.source$ s, chf.user$ u
   WHERE o.obj# = s.obj# AND o.owner# = u.user#
         AND (o.type# IN (7, 8, 9, 11, 12, 14)
              OR (o.type# = 13 AND o.subname IS NULL))
--过来条件选择出需要对象
-- AND U.name='BSSADMIN'
ORDER BY U.NAME,
         O.NAME,
         O.TYPE#,
         S.LINE;

5. 使用说明
在line=1的source前面加上create or replace ,使用合适的用户创建需要对象

二、VIEW恢复
1. 找出视图存储基表
sys.obj$，sys.view$，sys.user$
2. 使用odu导出需要表
3. 使用imp导入数据库
4. 查询语句

    SELECT u.name username, o.name viewname, v.text
  FROM chf.obj$ o, chf.view$ v, chf.user$ u
 WHERE o.obj# = v.obj# AND o.owner# = u.user# 
 --过滤条件，挑选需要视图
 AND u.name = 'MAS_ADMIN';

5. 使用说明
create VIEW OR REPLACE username.viewname AS+TEXT中内容

三、dblink恢复
1. 找出关联基表
Sys.link$和sys.user$
2. odu导出相关表
3. imp导入数据
4. 查询语句

SELECT U.NAME   USERNAME,
       L.NAME   DBLINK_NAME,
       L.USERID LOGIN_USER,
       L.HOST   TNS,
       L.CTIME
  FROM CHF.LINK$ L, CHF.USER$ U
 WHERE L.OWNER# = U.USER#
  --过滤条件
   AND U.NAME = 'PUBLIC'

5. 使用说明
根据查询出来信息，自己创建DBLINK

FAST_START_PARALLEL_ROLLBACK与回滚恢复

Posted on by

1.模拟产生大事务需回滚

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 12:47:08 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show sga;

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             406848896 bytes
Database Buffers          205520896 bytes
Redo Buffers                7548928 bytes

SQL> create table chf.xifenfei tablespace users 
  2  as
  3  select * from dba_objects;

--下面两句多次执行
SQL> insert into chf.xifenfei 
  2  select * from chf.xifenfei;

73831 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from  chf.xifenfei;

  COUNT(*)
----------
  18900736

SQL> select bytes/1024/1024 from dba_segments where segment_name='XIFENFEI';

BYTES/1024/1024
---------------
           2103
--删除数据不提交
SQL> delete from chf.xifenfei;

18900736 rows deleted.

--直接kill掉ora_dbw进程

2.FAST_START_PARALLEL_ROLLBACK=LOW(默认值)

SQL> select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
  2   "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 
  3   / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 
  4  "Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 
  5  from v$fast_start_transactions;

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    545624     103020     442604 2012-02-16 13:57:38 2012-02-16 13:47:02


SQL> /

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    545624     122614     423010 2012-02-16 13:57:42 2012-02-16 13:47:31

--每秒钟回滚undo数据块数量
SQL> select (122614-103020)/29 from dual;

(122614-103020)/29
------------------
        675.655172

--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle   24901     1  4 13:44 ?        00:00:15 ora_p000_chf
oracle   24903     1  3 13:44 ?        00:00:12 ora_p001_chf
oracle   24905     1  3 13:44 ?        00:00:12 ora_p002_chf
oracle   24907     1  3 13:44 ?        00:00:12 ora_p003_chf
oracle   24909     1  3 13:44 ?        00:00:12 ora_p004_chf
oracle   24911     1  3 13:44 ?        00:00:12 ora_p005_chf
oracle   24913     1  3 13:44 ?        00:00:12 ora_p006_chf
oracle   24915     1  3 13:44 ?        00:00:12 ora_p007_chf
oracle   24917     1  3 13:44 ?        00:00:12 ora_p008_chf
oracle   24919     1  3 13:44 ?        00:00:12 ora_p009_chf
oracle   24921     1  3 13:44 ?        00:00:12 ora_p010_chf
oracle   24923     1  3 13:44 ?        00:00:12 ora_p011_chf
oracle   24925     1  3 13:44 ?        00:00:12 ora_p012_chf
oracle   24927     1  3 13:44 ?        00:00:12 ora_p013_chf
oracle   24929     1  3 13:44 ?        00:00:12 ora_p014_chf
oracle   24931     1  3 13:44 ?        00:00:12 ora_p015_chf

说明：该机器操作系统是8个CPU

并发数=CPU*2

3.FAST_START_PARALLEL_ROLLBACK =HIGH

SQL> alter system set FAST_START_PARALLEL_ROLLBACK =HIGH;

System altered.

SQL> select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
  2   "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 
  3   / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 
  4  "Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 
  5  from v$fast_start_transactions;

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    380434      25351     355083 2012-02-16 14:03:53 2012-02-16 13:49:39


SQL> /

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    380434      39592     340842 2012-02-16 14:03:33 2012-02-16 13:50:12

--每秒钟回滚undo数据块数量
SQL> select (39592-25351)/33 from dual;

(39592-25351)/33
----------------
      431.545455

--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle   24901     1  4 13:44 ?        00:00:15 ora_p000_chf
oracle   24903     1  3 13:44 ?        00:00:12 ora_p001_chf
oracle   24905     1  3 13:44 ?        00:00:12 ora_p002_chf
oracle   24907     1  3 13:44 ?        00:00:12 ora_p003_chf
oracle   24909     1  3 13:44 ?        00:00:12 ora_p004_chf
oracle   24911     1  3 13:44 ?        00:00:12 ora_p005_chf
oracle   24913     1  3 13:44 ?        00:00:12 ora_p006_chf
oracle   24915     1  3 13:44 ?        00:00:12 ora_p007_chf
oracle   24917     1  3 13:44 ?        00:00:12 ora_p008_chf
oracle   24919     1  3 13:44 ?        00:00:12 ora_p009_chf
oracle   24921     1  3 13:44 ?        00:00:12 ora_p010_chf
oracle   24923     1  3 13:44 ?        00:00:12 ora_p011_chf
oracle   24925     1  3 13:44 ?        00:00:12 ora_p012_chf
oracle   24927     1  3 13:44 ?        00:00:12 ora_p013_chf
oracle   24929     1  3 13:44 ?        00:00:12 ora_p014_chf
oracle   24931     1  3 13:44 ?        00:00:12 ora_p015_chf
oracle   25072     1  0 13:48 ?        00:00:01 ora_p016_chf
oracle   25074     1  0 13:48 ?        00:00:01 ora_p017_chf
oracle   25076     1  0 13:48 ?        00:00:01 ora_p018_chf
oracle   25078     1  0 13:48 ?        00:00:01 ora_p019_chf
oracle   25080     1  0 13:48 ?        00:00:01 ora_p020_chf
oracle   25082     1  0 13:48 ?        00:00:01 ora_p021_chf
oracle   25084     1  0 13:48 ?        00:00:01 ora_p022_chf
oracle   25086     1  0 13:48 ?        00:00:01 ora_p023_chf
oracle   25088     1  0 13:48 ?        00:00:01 ora_p024_chf
oracle   25090     1  0 13:48 ?        00:00:01 ora_p025_chf
oracle   25092     1  0 13:48 ?        00:00:01 ora_p026_chf
oracle   25094     1  0 13:48 ?        00:00:01 ora_p027_chf
oracle   25096     1  0 13:48 ?        00:00:01 ora_p028_chf
oracle   25098     1  0 13:48 ?        00:00:01 ora_p029_chf
oracle   25100     1  0 13:48 ?        00:00:01 ora_p030_chf
oracle   25102     1  0 13:48 ?        00:00:01 ora_p031_chf

1.说明问题：直接修改FAST_START_PARALLEL_ROLLBACK =HIGH后，
  数据库在原来并发进程基础上，又重新启动额外进程

2.修改FAST_START_PARALLEL_ROLLBACK后，以前回滚过的数据块是成功的
  v$fast_start_transactions视图重新开始计算

3.并发数=CPU*4
[/shell]

<strong>4.FAST_START_PARALLEL_ROLLBACK=FALSE</strong>
1
SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE;

System altered.

--直接修改为FALSE后，观察到数据库的并发等资源都没有释放，重启数据库释放资源继续试验
SQL> startup force;
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             406848896 bytes
Database Buffers          205520896 bytes
Redo Buffers                7548928 bytes
Database mounted.
Database opened.
SQL> select  undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
  2   "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 
  3   / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 
  4  "Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 
  5  from v$fast_start_transactions;

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    306828      15735     291093 2012-02-16 14:04:34 2012-02-16 13:52:33


SQL> /

     Total       Done       ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
    306828      65861     240967 2012-02-16 14:05:15 2012-02-16 13:54:46

--每秒钟回滚undo数据块数量
SQL> select (65861-15735)/133 from dual;

(65861-15735)/133
-----------------
       376.887218

--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle   25252     1  1 13:51 ?        00:00:00 ora_p000_chf
oracle   25254     1  0 13:51 ?        00:00:00 ora_p001_chf
oracle   25256     1  1 13:51 ?        00:00:00 ora_p002_chf
oracle   25258     1  1 13:51 ?        00:00:00 ora_p003_chf
oracle   25260     1  1 13:51 ?        00:00:00 ora_p004_chf
oracle   25262     1  1 13:51 ?        00:00:00 ora_p005_chf
oracle   25264     1  1 13:51 ?        00:00:00 ora_p006_chf

1.数据库重启后，以前的回滚依然生效(v$fast_start_transactions.undoblockstotal变小)

2.FAST_START_PARALLEL_ROLLBACK=false还是有并发，而非官方文档描述(Parallel rollback is disabled)

5.总结

通过这三种情况下的每秒钟回滚undo数据块数量比较可以知道在LOW状态下最快，HIGH状态下次之，FALSE最慢。其实这个实验没有任何实际说明力，只是想说明几个问题：
1)Oracle大事物回滚，是没有办法取消，但是可以通过FAST_START_PARALLEL_ROLLBACK干预回滚速度
2)数据库的并发效率高于低，取决于系统的资源情况(如果你系统的cpu非常强大，那么可能设置HIGH速度最快)
3)回滚的数据类型，在回滚表中数据时可能设置并发比FALSE快，
但是如果是要回滚串行数据(如:index),那么可能串行方法方式速度更快
4)根据你的系统的使用状况，比如你想让系统的业务受到的影响最小，那么设置FALSE可能是个不错的选择。

6.补充官方说明

FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. 
Terminated transactions are transactions that are active before a system failure. 
If a system fails when there are uncommitted parallel DML or DDL transactions, 
then you can speed up transaction recovery during startup by using this parameter.

Values:

FALSE
Parallel rollback is disabled

LOW
Limits the maximum degree of parallelism to 2 * CPU_COUNT

HIGH
Limits the maximum degree of parallelism to 4 * CPU_COUNT

Note:If you change the value of this parameter, 
then transaction recovery will be stopped and restarted with the new implied degree of parallelism.