Oracle – 第25页 – Database SOS

Oracle Database常用补丁集Patch号

Patchset / PSU Patch Number

11.2.0.4	13390677      11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.3.8	16902043      DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013)
11.2.0.3.7	16619892      DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013)
11.2.0.3.6	16056266      DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013)
11.2.0.3.5	14727310      DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013):
11.2.0.3.4	14275605      DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
11.2.0.3.3	13923374      DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012):
11.2.0.3.2	13696216      DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012)
11.2.0.3.1	13343438      DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012)
11.2.0.3	10404530      11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.2.11	16619893      DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013)
11.2.0.2.10	16056267      DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013)
11.2.0.2.9	14727315      DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013):
11.2.0.2.8	14275621      DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012)
11.2.0.2.7	13923804      DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012)
11.2.0.2.6	13696224      DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012)
11.2.0.2.5	13343424      DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012)
11.2.0.2.4	12827726      DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011)
11.2.0.2.3	12419331      DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)
11.2.0.2.2	11724916      DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
11.2.0.2.1	10248523      DATABASE PSU 11.2.0.2.1
11.2.0.2	10098816      11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
11.2.0.1.6	12419378       DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)
11.2.0.1.5	11724930      DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011)
11.2.0.1.4	10248516      DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011)
11.2.0.1.3	9952216      DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)
11.2.0.1.2	9654983      DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
11.2.0.1.1	9352237      DATABASE PSU 11.2.0.1.1
11.1.0.7.16	16619896 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013)
11.1.0.7.15	16056268  [*]      DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013)
11.1.0.7.14	14739378 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013)
11.1.0.7.13	14275623 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012)
11.1.0.7.12	13923474      DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012)
11.1.0.7.11	13621679      DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)
11.1.0.7.10	13343461      DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012)
11.1.0.7.9	12827740      DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011)
11.1.0.7.8	12419384      DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011)
11.1.0.7.7	11724936      DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011)
11.1.0.7.6	10248531      DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011)
11.1.0.7.5	9952228      DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010)
11.1.0.7.4	9654987      DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010)
11.1.0.7.3	9352179      DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
11.1.0.7.2	9209238      DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
11.1.0.7.1	8833297      DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
11.1.0.7	6890831      11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.5.12	16619894 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013):
10.2.0.5.11	16056270  [*]      DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013)
10.2.0.5.10	14727319 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013):
10.2.0.5.9	14275629 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012)
10.2.0.5.8	13923855 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012)
10.2.0.5.7	13632743 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012)
10.2.0.5.6	13343471 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012)
10.2.0.5.5	12827745 [*]      DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
10.2.0.5.4	12419392      DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
10.2.0.5.3	11724962      DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
10.2.0.5.2	10248542      DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
10.2.0.5.1	9952230      DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10.2.0.5	8202632      10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.4.17	16619897 [*]      DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013):
10.2.0.4.16	16056269 [*]      DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013)
10.2.0.4.15	14736542 [*]      DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013):
10.2.0.4.14	14275630 [**]      DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012)
0.2.0.4.13	13923851 [*]      DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012)
10.2.0.4.12	12879933 [*]
DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012)
10.2.0.4.11	12879929 [*]      DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012)
10.2.0.4.10	12827778      DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011)
10.2.0.4.9	12419397      DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011)
10.2.0.4.8	11724977      DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011)
10.2.0.4.7	10248636      DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011)
10.2.0.4.6	9952234      DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010)
10.2.0.4.5	9654991      DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010)    [overlay PSU]
10.2.0.4.4	9352164      DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
10.2.0.4.3	9119284      DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
10.2.0.4.2	8833280      DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
10.2.0.4.1	8576156      DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
10.2.0.4	6810189      10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.3	5337014      10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.2	4547817      10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.5	4505133      10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.4	4163362      10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.3	3761843      10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.8	4547809      9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.7	4163445      9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.6	3948480      9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.5	3501955      ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0
9.2.0.4	3095277      9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.3	2761332      9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.2	2632931      9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.5	3301544      9.0.1.5 PATCHSET
9.0.1.4	2517300      9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.3	2271678      9.0.1.3. PATCH SET FOR ORACLE DATA SERVER
8.1.7.4	2376472      8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
8.1.7.3	2189751      8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
8.1.7.2	1909158      8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER

NOTE:
[*] 10.2.0.4 and 10.2.0.5 are now in extended support mode and PSU’s released after Aug 01,2011 will need ES License to download them.
参考:Quick Reference to Patchset Patch Numbers [ID 753736.1]

坏块分类与检测

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：坏块分类与检测

很多人都对oracle都知道oracle坏块有逻辑坏块和物理坏块之分，其实根据物理和逻辑坏块的分类，还可以继续分下去；对于坏块的检查，很多人知道物理和逻辑坏块使用dbv和rman检测，那其他种类的坏块该怎么办呢？这篇文章整理自MOS，给大家整理个分析坏块的思路，在后续文章中，会给出各种坏块的解决思路
http://www.xifenfei.com/wp-content/uploads/2012/03/Corruption_block_type_and_analysis.pdf
附件:hout和hcheck

通过修改col$.col#改变列展示顺序

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：通过修改col$.col#改变列展示顺序

有网友提出在表中新增加一列，然后让这个列在中间(大家都知道默认情况下，增加一列，这列会在其他列之后)，通过修改col$基表可以实现改变列的显示顺序(增加一个新列是小儿科的事情不再研究)。我这里通过建立一个表，有id和name列，现在要改变这两列的默认展示顺序
1.创建模拟表

SQL> create table chf.t_xff (id number,name varchar2(100));
Table created.
SQL> insert into chf.t_xff values(1,'xifenfei');
1 row created.
SQL> insert into chf.t_xff values(2,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> desc chf.t_xff;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(100)
SQL> col name for a30
SQL> select * from chf.t_xff;
        ID NAME
---------- ------------------------------
         1 xifenfei
         2 www.xifenfei.com

2.修改col$.col#

SQL> select COLUMN_ID,COLUMN_NAME from dba_tab_cols
2    where table_name='T_XFF' AND OWNER='CHF';
 COLUMN_ID COLUMN_NAME
---------- ------------------------------------------------------------
         2 NAME
         1 ID
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T_XFF';
 OBJECT_ID
----------
     75598
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    2
ID                                      1
SQL> UPDATE COL$ SET COL#=2 WHERE OBJ#=75598 AND NAME='ID';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    2
ID                                      2
SQL> UPDATE COL$ SET COL#=1 WHERE OBJ#=75598 AND NAME='NAME';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME                                 COL#
------------------------------ ----------
NAME                                    1
ID                                      2
SQL> COMMIT;
Commit complete.

3.验证结果

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xff;
NAME                                   ID
------------------------------ ----------
xifenfei                                1
www.xifenfei.com                        2
SQL> desc chf.t_xff
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 NAME                                               VARCHAR2(100)
 ID                                                 NUMBER

误删除dual表恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：误删除dual表恢复

1.10G中删除dual表恢复

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select object_type,owner from dba_objects where object_name='DUAL';
OBJECT_TYPE         OWNER
------------------- ------------------------------
TABLE               SYS
SYNONYM             PUBLIC
SQL> drop table sys.dual;
Table dropped.
SQL> select object_type from dba_objects where object_name='DUAL';
OBJECT_TYPE
-------------------
SYNONYM
SQL> SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual
                    *
ERROR at line 1:
ORA-01775: looping chain of synonyms
SQL> CREATE TABLE XFF AS SELECT * from dba_objects;
Table created.
SQL> drop table xff purge;
drop table xff purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
设置10046跟踪会话发现，在每次删除表操作的时候发现如下错误
select dummy from dual where  ora_dict_obj_type = 'TABLE'

其实这里错误都很明显，是因为dual表不存在了，表对应的同义词还存在，当查询dual的时候，会去查询同义词，然后该同义词去找表，而表不存在，所以出现上述的ORA-01775: looping chain of synonyms错误

2.解决方法

SQL> CREATE TABLE "SYS"."DUAL"
  2     (       "DUMMY" VARCHAR2(1)
  3     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  4    STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  5    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  6    TABLESPACE "SYSTEM" ;
Table created.
SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;
Grant succeeded.
SQL> insert into dual values('X');
1 row created.
SQL> COMMIT;
Commit complete.
--编译对象
SQL> @?/rdbms/admin/utlrp.sql

3.测试结果

SQL> select sysdate from dual;
SYSDATE
------------
13-MAR-12
SQL> drop table xff purge;
Table dropped.

tempfile真正文件号

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：tempfile真正文件号

1.发现问题
这里看到文件号为201，但是查询了v$datafile和v$tempfile视图都没有文件号为201

SQL> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;
USERN SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
----- --------- ---------- ---------- ---------- ----------
SYS   SORT             201     260745        650          1
SEGFILE#  NUMBER  File number of initial extent
SEGRFNO#   NUMBER  Relative file number of initial extent
SQL> SELECT FILE#,RFILE# FROM V$DATAFILE;
     FILE#     RFILE#
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11
     FILE#     RFILE#
---------- ----------
        13         13
        14         14
13 rows selected.
SQL> SELECT FILE#,RFILE# FROM V$TEMPFILE;
     FILE#     RFILE#
---------- ----------
         1          1

2.查看v$sort_usage的原始表

SELECT USERNAME,
       USERNAME,
       KTSSOSES,
       KTSSOSNO,
       PREV_SQL_ADDR,
       PREV_HASH_VALUE,
       PREV_SQL_ID,
       KTSSOTSN,
       DECODE(KTSSOCNT, 0, 'PERMANENT', 1, 'TEMPORARY'),
       DECODE(KTSSOSEGT,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              'UNDEFINED'),
       KTSSOFNO,
       KTSSOBNO,
       KTSSOEXTS,
       KTSSOBLKS,
       KTSSORFNO
  FROM X$KTSSO, V$SESSION
 WHERE KTSSOSES = V$SESSION.SADDR
   AND KTSSOSNO = V$SESSION.SERIAL#
   and inst_id = USERENV('Instance')

这里没有发现有用信息，只是知道X$KTSSO.KTSSOFNO是v$sort_usage.SEGFILE#，通过v$sort_usage视图是查询临时表空用来排序的数据文件使用情况。所以把问题定位在v$tempfile视图中，检查它为什么没有显示文件号为201的文件

3.查看v$tempfile视图

SELECT TF.TFNUM,
       TO_NUMBER(TF.TFCRC_SCN),
       TO_DATE(TF.TFCRC_TIM,
               'MM/DD/RR HH24:MI:SS',
               'NLS_CALENDAR=Gregorian'),
       TF.TFTSN,
       TF.TFRFN,
       DECODE(BITAND(TF.TFSTA, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       DECODE(BITAND(TF.TFSTA, 12),
              0,
              'DISABLED',
              4,
              'READ ONLY',
              12,
              'READ WRITE',
              'UNKNOWN'),
       FH.FHTMPFSZ * TF.TFBSZ,
       FH.FHTMPFSZ,
       TF.TFCSZ * TF.TFBSZ,
       TF.TFBSZ,
       FN.FNNAM
  FROM X$KCCTF TF, X$KCCFN FN, X$KCVFHTMP FH
 WHERE FN.FNFNO = TF.TFNUM
   AND FN.FNFNO = FH.HTMPXFIL
   AND TF.TFFNH = FN.FNNUM
   AND TF.TFDUP != 0
   AND BITAND(TF.TFSTA, 32) <> 32
   AND FN.FNTYP = 7
   AND FN.FNNAM IS NOT NULL
   and inst_id = USERENV('Instance')

从这里可以看出v$tempfile.file#出自X$KCCTF.TFNUM

4.继续查看X$KCCTF表

SQL> desc X$KCCTF
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 TFNUM                                              NUMBER
 TFAFN                                              NUMBER
 TFCSZ                                              NUMBER
 TFBSZ                                              NUMBER
 TFSTA                                              NUMBER
 TFCRC_SCN                                          VARCHAR2(16)
 TFCRC_TIM                                          VARCHAR2(20)
 TFFNH                                              NUMBER
 TFFNT                                              NUMBER
 TFDUP                                              NUMBER
 TFTSN                                              NUMBER
 TFTSI                                              NUMBER
 TFRFN                                              NUMBER
 TFPFT                                              NUMBER
 TFMSZ                                              NUMBER
 TFNSZ                                              NUMBER

这里发现一列TFAFN，初步怀疑这个才是真正的绝对文件号。

5.证明X$KCCTF.TFAFN是绝对文件号

SQL> select TFAFN,TFNUM from X$KCCTF;
     TFAFN      TFNUM
---------- ----------
       201          1

如果证明X$KCCTF.TFAFN才是真正的文件号，而TFNUM是临时文件的文件号

6.temp file绝对文件号结论
再进一步可以知道，实际上，为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files+file#。

SQL> show parameter db_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
db_files                             integer     200

利用oradebug释放被删除文件空间

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：利用oradebug释放被删除文件空间

在很多时候，检查系统时候发现，由于某个Oracle的trace文件导致磁盘空间告警，因为业务需要不能让数据库down下来。这个时候你想到的方法可能是直接删除掉这个trace文件，如果是win系统，那恭喜你这样做可以解决问题；如果是linux/unix系统，那就等着事故的发生吧。在linux/unix中，如果直接rm掉oracle进程的某个文件(该进程还存在)，文件句柄不会释放，即磁盘使用空间不会释放。可以通过df命名看到磁盘的空间释放释放。下面通过对lgwr进程的一系列操作，使用oradebug来释放oracle进程句柄，从而达到释放oracle某个被删除的trace文件的磁盘空间
一、查找lgwr进程的trace文件

[oracle@localhost /]$ cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
[oracle@localhost bdump]$ pwd
/opt/oracle/admin/mcrm/bdump
[oracle@localhost bdump]$ ls -l|grep lgwr
-rw-r----- 1 oracle oinstall 32133 Dec 22 21:00 mcrm_lgwr_3485.trc
-rw-r----- 1 oracle oinstall  3713 Oct  8 07:13 mcrm_lgwr_3489.trc
-rw-r----- 1 oracle oinstall 22507 Mar  3 06:00 mcrm_lgwr_3598.trc
-rw-r----- 1 oracle oinstall  8441 Sep 15 10:29 mcrm_lgwr_4963.trc
[oracle@localhost bdump]$ ps -ef|grep lgwr
oracle    1056 30718  0 21:10 pts/3    00:00:00 grep lgwr
oracle    3598     1  0  2011 ?        00:04:10 ora_lgwr_mcrm
[oracle@localhost bdump]$ df |grep /opt
/dev/sda6             37798668  33312588   2534988  93% /opt
[oracle@localhost bdump]$ du -s .
948     .

从这里得出几点结论：
1.当前lgwr进程的spid为：3598
2.当前lgwr进程产生的trace文件大小为：22507B
3.包含该trace文件的分区大小使用大小为：33312588KB
4.bdump目录大小为：948KB

二、删除lgwr进程对应trace文件

[oracle@localhost bdump]$ rm mcrm_lgwr_3598.trc
[oracle@localhost bdump]$ du -s .
924     .
[oracle@localhost bdump]$ df |grep /opt
/dev/sda6             37798668  33312588   2534988  93% /opt
[oracle@localhost bdump]$ ls -l /proc/3598/fd|grep lgwr
l-wx------ 1 oracle oinstall 64 Mar  3 20:54 2 -> /opt/oracle/admin/mcrm/bdump/mcrm_lgwr_3598.trc (deleted)

从这里得出结论：
1.bdump目录当前大小变为：924KB(大约等于948KB-22507B)
2.包含该trace文件的分区大小使用大小依然为：33312588KB(没有因为删除trace文件而释放空间)

三、释放被删除trace文件空间

[oracle@localhost bdump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 3 21:12:41 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> !ls -l /proc/3598/fd|grep lgwr
l-wx------ 1 oracle oinstall 64 Mar  3 20:54 2 -> /opt/oracle/admin/mcrm/bdump/mcrm_lgwr_3598.trc (deleted)
SQL>  oradebug setospid 3598
Oracle pid: 6, Unix process pid: 3598, image: oracle@localhost.localdomain (LGWR)
SQL> oradebug flush;
Statement processed.
SQL> oradebug close_trace;
Statement processed.
SQL> !ls -l /proc/3598/fd|grep lgwr
SQL> !df |grep /opt
/dev/sda6             37798668  33312564   2535012  93% /opt
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

从这里可以得出结论：
1.包含该trace文件的分区大小使用大小为：33312564KB(大约等于948KB-22507B)
2./proc/spid/fd下面的句柄已经释放
3.总这里可以看出使用oradebug可以真正释放oracle进程磁盘使用空间

statspack报告中逻辑读为负值

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：statspack报告中逻辑读为负值

最近遇到两次在sp报告中，显示逻辑读为负数。进行分析情况如下：
一台是运营商的crm库（aix 5.3+9.2.0.8+rac）

--系统版本
[zwq_crm2:/home/crm_oraeye]oslevel -s
5300-08-07-0920
--数据库版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
  2   FROM gv$instance a;
   INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
         2 2011-02-19 03:33:49
         1 2011-02-19 03:56:17
--异常的逻辑读统计数据
SQL>   SELECT b.snap_id,
  2           TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (A.VALUE, '999,999,999,999,999')
  4      FROM stats$sysstat a, stats$snapshot b
  5     WHERE     a.NAME = 'session logical reads'
  6           AND a.instance_number = 2
  7           AND a.snap_id = b.snap_id
  8           AND A.SNAP_ID >=47913
  9           AND A.SNAP_ID <=47920
 10  ORDER BY a.snap_id;
   SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
   47913	2012-02-10 20:00	4,764,705,272,783
   47914	2012-02-10 21:00	4,761,539,910,574
   47915	2012-02-10 22:00	4,749,529,436,021
   47916	2012-02-10 23:00	4,745,952,040,146
   47917	2012-02-11 00:00	4,738,052,256,634
   47918	2012-02-11 01:00	4,738,894,245,521
   47919	2012-02-11 02:00	4,739,587,095,184
   47920	2012-02-11 03:00	4,740,409,262,259

另一台是运营商的开停机库（aix 5.3+9.2.0.8+rac）

--系统版本
[zwq_offon2:/home/oraeye]oslevel -s
5300-08-07-0920
--数据库版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
  2    FROM gv$instance a;
   INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
         2 2010-01-23 19:16:46
         1 2010-01-23 19:15:09
--异常的逻辑读统计数据
SQL>   SELECT b.snap_id,
  2           TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (A.VALUE, '999,999,999,999,999')
  4      FROM stats$sysstat a, stats$snapshot b
  5     WHERE     a.NAME = 'session logical reads'
  6           AND a.instance_number = 2
  7           AND a.snap_id = b.snap_id
  8           AND A.SNAP_ID IN ('38271', '38272', '38339', '38340')
  9  ORDER BY a.snap_id;
   SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
     38271 2012-01-31 19:00:05    4,269,858,122,434
     38272 2012-01-31 20:00:02    4,266,001,522,867
     38339 2012-02-02 09:00:02    4,275,651,080,526
     38340 2012-02-02 10:00:02    4,250,263,107,466

这两个数据库中都出现了在数据库没有重启的情况下stats$sysstat.value的值出现波动情况，而且都是在40万亿以上的时候。不知道是特点的版本巧合(特定的数据库版本，特定的操作系统版本)，还是Oracle未公布bug。出现这样的情况，目前只能通过重启实例来使得statspack捕获到的逻辑读值变小，从而避免这样的波动，从而解决sp中出现逻辑读为负数的情况。

ARCn: Media recovery disabled原因分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ARCn: Media recovery disabled原因分析

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中无类此记录出现

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

联系：手机/微信(+86 17813235971) QQ(107644445)

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

在日常的性能监控中，我们经常需要需要通过一段时间内数据库的逻辑读/物理读来大致反映数据库的性能情况，这里列出通过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;

FAST_START_PARALLEL_ROLLBACK与回滚恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：FAST_START_PARALLEL_ROLLBACK与回滚恢复

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>

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.

分类目录归档：Oracle