DBARoad：我的DBA之路

SNIPED状态会话处理

OoNiceDream — Wed, 11 Jan 2012 10:08:45 +0800

数据库profile中设置IDLE_TIME后，如果会话超过这个限制，PMON进程会：

 * Mark the V$SESSION as SNIPED
 * Clean up the database resources for the session
 * Remove the V$SESSION entry

在一个9208的数据库中，经常会有SNIPED状态的会话，这些会话不活动了，但却占用了用户的会话数：

SYS@DB2> select sid,serial#,paddr,username,status from v$session where status = 'SNIPED';
 
    SID    SERIAL# PADDR            USERNAME        STATUS
------- ---------- ---------------- --------------- --------
    116       4063 C000000826067820 USER_79        SNIPED
    236      31393 C00000082634BEA0 USER_79        SNIPED
    319      59368 C00000082606D8E0 USER_79        SNIPED
    552      21495 C000000826128AC0 USER_79        SNIPED
    769       3890 C00000082615C680 USER_79        SNIPED
    838      21427 C000000826164220 USER_79        SNIPED
   1009      29340 C000000826087C20 USER_70        SNIPED
   1023      17707 C000000826062CE0 USER_71        SNIPED
   1148       8085 C0000008263A03C0 USER_73        SNIPED
   1360      45212 C00000082621D3C0 USER_79        SNIPED
   1676       4466 C00000082615D140 USER_79        SNIPED
   1959       5966 C00000082639C8A0 USER_72        SNIPED
   2187       7000 C000000826355A80 USER_78        SNIPED
   2251      65047 C0000008263A4440 USER_72        SNIPED
   2462      14367 C00000082634AE80 USER_71        SNIPED
   2561      21989 C0000008262733C0 USER_74        SNIPED
   2639      10214 C0000008263972A0 USER_78        SNIPED
   2683      30762 C000000826071EC0 USER_76        SNIPED
 
18 rows selected.

这里我们看到的现象是v$session中，会话没有被清理。最好的情况是，当用户再次尝试连接该会话时，会收到ORA-02396错误，然后会话被清理。

[/home/oracle]$ oerr ora 2396
02396, 00000, "exceeded maximum idle time, please connect again"
//  *Cause:  as stated
//  *Action:

尝试在sqlnet.ora中设置SQLNET.EXPIRE_TIME，没什么用。

尝试用alter system kill session，v$session中标记为KILLED，v$process、os process都是不清理的。

    SID    SERIAL# PADDR            USERNAME        STATUS
------- ---------- ---------------- --------------- --------
    116       4063 C000000826067820 USER_79        SNIPED
 
SYS@DB2> select pid from v$process where addr='C000000826067820';
 
       PID
----------
        64
 
SYS@DB2> select spid from v$process where addr='C000000826067820';
 
SPID
------------
22907
 
[/home/oracle]$ ps -ef | grep 22907
  oracle 23300 23244  1 11:04:23 pts/ta    0:00 grep 22907
  oracle 22907     1  0 14:23:51 ?         0:03 oracleDB2 (LOCAL=NO)
 
 
SYS@DB2> alter system kill session '116,4063';
 
System altered.
 
SYS@DB2> select sid,serial#,paddr,username,status from v$session where sid=116;
 
       SID    SERIAL# PADDR            USERNAME                       STATUS
---------- ---------- ---------------- ------------------------------ --------
       116       4063 C0000008239C3A88 USER_79                       KILLED
 
SYS@DB2> select spid from v$process where addr='C000000826067820';
 
SPID
------------
22907  
 
[/home/oracle]$ ps -ef | grep 22907
  oracle 22907     1  0 14:23:51 ?         0:03 oracleDB2 (LOCAL=NO)
  oracle 25859 23244  0 11:07:56 pts/ta    0:00 grep 22907

最后，写个脚本，OS上kill -9，会话被清理。

— The End —

使用restore_table_stats还原统计信息

OoNiceDream — Thu, 22 Dec 2011 16:10:17 +0800

10G中可以使用dbms_stats.restore_table_stats恢复表老的统计信息，这功能还是挺不错的。因为统计信息的变化有时会引起某些语句执行计划变差，这时恢复原有统计信息，常常会有效果。当然分析前备份老的统计信息，始终是个好习惯。

查询统计信息保存时间，当然这个时间不一定能保证：

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

也可以通过execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45) 来修改这个保存时间。

查询统计信息能恢复到哪个时间点：

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------------
20-NOV-11 10.08.13.843271000 PM +08:00

小测试：
当前统计信息：

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               108046         100 HEIGHT BAL .000015395 C3020104        C30C512D        2011-12-22 14:53
                                    ANCED
 
NAME               7775         100 HEIGHT BAL .001328021 2F3130303065386 73756E2F7574696 2011-12-22 14:53
                                    ANCED                 4315F4C696E6B65 C2F427564646869
                                                          64486173684D617 737443616C656E6
                                                          056616C75654974 46172

恢复到分析之前：

SQL> select to_timestamp ('2011-12-22 14:52','yyyy-mm-dd hh24:mi') from dual;
 
TO_TIMESTAMP('2011-12-2214:52','YYYY-MM-DDHH24:MI')
---------------------------------------------------------------------------
22-DEC-11 02.52.00.000000000 PM
 
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  ownname => 'SYS',
  4  tabname => 'T1',
  5  as_of_timestamp => '22-DEC-11 02.52.00.000000000 PM'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               118045         100 HEIGHT BAL .000013915 C102            C30C512E        2011-12-22 14:45
                                    ANCED
 
NAME              11656         100 HEIGHT BAL  .00104712 2F3130343866633 73756E2F746F6F6 2011-12-22 14:45
                                    ANCED                 9355F5772617054 C732F747265652F
                                                          6F6B656E5772617 5768696C6553746
                                                          0546F6B656E496E 174656D656E74

再次恢复到2011-12-22 14:45之前的统计信息：

SQL> select to_timestamp ('2011-12-22 14:44','yyyy-mm-dd hh24:mi') from dual;
 
TO_TIMESTAMP('2011-12-2214:44','YYYY-MM-DDHH24:MI')
---------------------------------------------------------------------------
22-DEC-11 02.44.00.000000000 PM
 
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  ownname => 'SYS',
  4  tabname => 'T1',
  5  as_of_timestamp => '22-DEC-11 02.44.00.000000000 PM'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               117773           1 NONE       8.4909E-06 C11B            C30C5108        2011-12-21 16:15
NAME               2617           1 NONE       .000382117 2F3130303562643 73756E2F746F6F6 2011-12-21 16:15
                                                          3305F4C6E6B6443 C732F747265652F
                                                          6F6E7374616E74  4F7245787072657
                                                                          373696F6E

— The End —

PARALLEL_INDEX与索引并发

OoNiceDream — Wed, 21 Dec 2011 17:27:09 +0800

使用PARALLEL_INDEX可以在索引上开并发，先来看看文档中的描述：
The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.

这里说的是PARALLEL_INDEX可以用在分区索引上开并发，其实还有一种就情况是，当查询走index fast full scan时，也可以开并发。

1、分区索引：

SQL> select index_name, index_type, status, partitioned, degree
  2    from dba_indexes
  3   where table_name = 'T1'
  4     and owner = 'SYS';
 
INDEX_NAME      INDEX_TYPE      STATUS     PARTIT DEGREE
--------------- --------------- ---------- ------ -------
IND_T1_ID       NORMAL          N/A        YES    1
IND_T1_NAME     NORMAL          VALID      NO     1
 
SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1 where id between 1 and 3000;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2134138182
 
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Pstart| Pstop |    TQ  |IN-OUT| PQ Di
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |  2500 |       |       |        |      |
|   1 |  PX COORDINATOR          |           |       |       |       |        |      |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000  |  2500 |       |       |  Q1,00 | P->S | QC (R
|   3 |    PX PARTITION RANGE ALL|           |  2500 |     1 |     4 |  Q1,00 | PCWC |
|*  4 |     INDEX RANGE SCAN     | IND_T1_ID |  2500 |     1 |     4 |  Q1,00 | PCWP |
-------------------------------------------------------------------------------------------
 
SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2841388588
 
--------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Pstart| Pstop |    TQ  |IN-OUT| PQ Dis
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |   936K|       |       |        |      |
|   1 |  PX COORDINATOR         |           |       |       |       |        |      |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000  |   936K|       |       |  Q1,00 | P->S | QC (RA
|   3 |    PX BLOCK ITERATOR    |           |   936K|     1 |     4 |  Q1,00 | PCWC |
|   4 |     INDEX FAST FULL SCAN| IND_T1_ID |   936K|     1 |     4 |  Q1,00 | PCWP |
--------------------------------------------------------------------------------------------

2、非分区索引，需要走index fast full scan才能开并发:

SQL> SELECT /*+ INDEX_FFS(T1,IND_T1_NAME) PARALLEL_INDEX(T1, ind_t1_name, 2) */
  2  count(name) from T1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1205896683
 
--------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |     1 |        |      |         |
|   1 |  SORT AGGREGATE           |             |     1 |        |      |         |
|   2 |   PX COORDINATOR          |             |       |        |      |         |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000    |     1 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |             |     1 |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR    |             |   936K|  Q1,00 | PCWC |         |
|   6 |       INDEX FAST FULL SCAN| IND_T1_NAME |   936K|  Q1,00 | PCWP |         |
--------------------------------------------------------------------------------------
 
以下T2表为非分区表：
SQL> select /*+ PARALLEL_INDEX(T2, ind_t2_id, 2) */count(id) from T2;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 155381254
 
------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |     1 |        |      |         |
|   1 |  SORT AGGREGATE           |           |     1 |        |      |         |
|   2 |   PX COORDINATOR          |           |       |        |      |         |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000  |     1 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |           |     1 |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR    |           |   949K|  Q1,00 | PCWC |         |
|   6 |       INDEX FAST FULL SCAN| IND_T2_ID |   949K|  Q1,00 | PCWP |         |
------------------------------------------------------------------------------------

另外还找到一个与PARALLEL_INDEX相关的BUG，使HINT无法在DBLINK中使用，该BUG在10.2.0.5中修复：
Bug 6621937 – [NO]PARALLEL_INDEX hint not sent to remote site for SQL over database link [ID 6621937.8]:

A SQL statement containing [NO_]PARALLEL_INDEX hints
which is sent to a remote site (over a database link)
does not send those hints with the remote SQL. This can
affect query performance.
 
eg:
  select /*+ driving_site(a) parallel_index(a,4) */
    a.*,b.* from test_aaa@test_link a,test_bbb b
  where a.col1 = b.col1 and a.col3=5;

— The End —

比较删除柱状图的三种方法

OoNiceDream — Tue, 20 Dec 2011 18:12:38 +0800

比较下常用的三种删除柱状图信息的方法。
测试环境：

SQL> select * from v$version;
 
BANNER
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

1、使用dbms_stats.delete_column_stats 册除柱状图信息：
删除前统计信息：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T1              111757        200           7 2011-12-20 14:54
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T1'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T1_ID           111111          6         249   200          1         41 2011-12-20 14:54
IND_T1_ID_NAME      111111          6         295   249          1         49 2011-12-20 14:54
IND_T1_NAME         111111          5         218   200          1         43 2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     5           5 FREQUENCY .000004474 C164       C3646464   2011-12-20 14:54
NAME                   4           4 FREQUENCY .000004474 5232       5235       2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         1             99
ID                         5            999
ID                        49           9999
ID                       534          99999
ID                      5532         999999
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      49     4.2682E+35
NAME                    5532     4.2684E+35

这里加一步删除所有数据，验证下删除柱状图后，表及索引的统计信息有没有变更：

SQL> delete from t1;
 
111111 rows deleted.
 
SQL> commit;
 
Commit complete.

删除柱状图后统计信息：

SQL> exec dbms_stats.delete_column_stats('SYS','T1','id');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T1              111757        200           7 2011-12-20 14:54
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T1'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T1_ID           111111          6         249   200          1         41 2011-12-20 14:54
IND_T1_ID_NAME      111111          6         295   249          1         49 2011-12-20 14:54
IND_T1_NAME         111111          5         218   200          1         43 2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
NAME                   4           4 FREQUENCY .000004474 5232       5235       2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      49     4.2682E+35
NAME                    5532     4.2684E+35

小结：可以看出dbms_stats.delete_column_stats不仅删除了柱状图，且列上的基本统计信息如最大值最小值、唯一值等都删了，这对CBO计算成本还是有影响的，特别是谓词中带有这个条件时。表及索引、其它列上的统计信息，是不受影响的。

2、使用dbms_stats.gather_table_stats 重新收集：
删除前统计信息：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T2              109636        200           7 2011-12-20 15:49
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T2'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T2_ID           111111          6         384   203          1         64 2011-12-20 15:49
IND_T2_ID_NAME      111111          6         408   427          2         68 2011-12-20 15:49
IND_T2_NAME         111111          5         220   202          1         44 2011-12-20 15:49
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T2'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     5           5 FREQUENCY 4.5605E-06 C164       C3646464   2011-12-20 15:49
NAME                   4           4 FREQUENCY 4.5605E-06 5232       5235       2011-12-20 15:49
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         1             99
ID                         5            999
ID                        51           9999
ID                       566          99999
ID                      5427         999999
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      51     4.2682E+35
NAME                    5427     4.2684E+35

更改数据，增加唯一值个数，最大值等，观察分析时，会不会对ID、NAME列进行分析：

SQL> begin
  2  for i in 1..1000 loop
  3  insert into t2 values(8888888,'R8');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t2 values(88888888,'R9');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t2 values(999999999,'R10');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

删除柱状图后统计信息：

SQL> exec dbms_stats.gather_table_stats('SYS','T2',cascade=>true,method_opt=>'for columns ID size 1');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T2              113094        210           8 2011-12-20 15:57
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T2'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T2_ID           114111          9         391   211          1         43 2011-12-20 15:57
IND_T2_ID_NAME      114111          9         418   439          2         46 2011-12-20 15:57
IND_T2_NAME         114111          8         228   212          1         28 2011-12-20 15:57
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T2'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     8           1 NONE            .125 C164       C50A646464 2011-12-20 15:57
                                                                     64
 
NAME                   4           4 FREQUENCY 4.5605E-06 5232       5235       2011-12-20 15:49
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         0             99
ID                         1      999999999
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      51     4.2682E+35
NAME                    5427     4.2684E+35

小结：可以看出这种方法，代价较大，重新收集了表和索引上的统计信息，只是收集时，不再收集ID列上的住状图。但这里需要注意的是，其它列上的统计信息，也没有重新收集的，因为method_opt 只指定了ID列。如果是method_opt=>’for all columns size 1′，那么NAME列上的柱状图也将不再收集。

3、使用dbms_stats.set_column_stats 手工删除柱状图：
删除前统计信息：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T3              109252        200           7 2011-12-20 15:50
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T3'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T3_ID           111111          6         373   203          1         62 2011-12-20 15:50
IND_T3_ID_NAME      111111          6         408   422          2         68 2011-12-20 15:50
IND_T3_NAME         111111          5         219   202          1         43 2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T3'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     4           4 FREQUENCY 4.5766E-06 C20A64     C3646464   2011-12-20 15:50
NAME                   3           3 FREQUENCY 4.5766E-06 5233       5235       2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         5            999
ID                        58           9999
ID                       541          99999
ID                      5408         999999
NAME                       5     4.2680E+35
NAME                      58     4.2682E+35
NAME                    5408     4.2684E+35

更改数据，增加唯一值个数，最大值等，观察分析时，会不会对ID列进行分析：

SQL> begin
  2  for i in 1..1000 loop
  3  insert into t3 values(8888888,'R8');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t3 values(88888888,'R9');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t3 values(999999999,'R10');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

删除后统计信息:

SQL> exec dbms_stats.set_column_stats('SYS','T3','ID',DISTCNT=>4);
 
PL/SQL procedure successfully completed.
 
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T3              109252        200           7 2011-12-20 15:50
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T3'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T3_ID           111111          6         373   203          1         62 2011-12-20 15:50
IND_T3_ID_NAME      111111          6         408   422          2         68 2011-12-20 15:50
IND_T3_NAME         111111          5         219   202          1         43 2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T3'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     4           1 NONE             .25 C20A64     C3646464   2011-12-20 16:02
NAME                   3           3 FREQUENCY 4.5766E-06 5233       5235       2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         0            999
ID                         1         999999
NAME                       5     4.2680E+35
NAME                      58     4.2682E+35
NAME                    5408     4.2684E+35

小结：dbms_stats.set_column_stats 只对列进行了更新，而对表、索引，表上其它列信息，均无影响。
通过SQL TRACE观察dbms_stats.set_column_stats操作，发现以下两步：
1、更新列的统计信息：
update hist_head$ set bucket_cnt=:3, row_cnt=:4, cache_cnt=:5,null_cnt=:6, timestamp#=:7, sample_size=:8, minimum=:9, maximum=:10,distcnt=:11, lowval=:12, hival=:13, density=:14, spare1=:15, spare2=:16, avgcln=:17, col#=:18 where obj#=:1 and intcol#=:2
2、删除柱状图：
delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3

注意，这里只是更新列的统计信息，并没有对列的统计信息进行重新分析（最大、最小值无变化），虽然列的LAST_ANALYZED更新到了最新。

从上面的测试可以看出，正常情况下，最好还是使用dbms_stats.set_column_stats删除柱状图信息，代价小，副作用小。

— The End —

what is fuzzy file?

OoNiceDream — Sat, 17 Sep 2011 14:49:31 +0800

在一次恢复时，alter log中出现以下信息：

Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file

fuzzy file，什么意思呢？

首先想到的是v$datafile_header中的fuzzy字段。关于fuzzy列有一些小结：

9iR2之前,FUZZY可以用于标识数据文件是不是处于hot backup状态。
当一个数据文件begin backup 时,fuzzy即为yes,当end backup时为null
在9iR2之后的，当数据库打开后，fuzzy这一列便为yes了
当数据文件为read write状态且为online时，则fuzzy为yes
当数据文件为read only，则fuzzy为no
当数据文件为offline时：
alter database datafile 4 offline;  则fuzzy为YES
alter tablespace USERS offline;     则fuzzy为NULL

根据文章，Fuzzy File Warning When Recovering From Cold Backup [ID 103100.1]：

You are doing a restore from a cold backup using a backup 
controlfile.  On every file you receive the following message:
 
WARNING! Recovering data file <fno> from a fuzzy file. 
If not the current file it might be an online backup taken without
entering the begin backup command.
 
This message does not make sense as this is a cold backup.
 
This message is issued when you are performing recovery after restoring from a 
fuzzy backup due to the online fuzzy bit is set in the file headers.
Although this can be set when you do an online backup without
issuing begin/end backup, it can also be set when the system crashes.
This is the case here; a shutdown abort had been performed prior to
the cold backup.

除了begin/end backup外，system crashes时，Recover时alter log也会出现提上提示信息。所以可以看看shutdown abort后，mount状态下，fuzzy列的状态（为YES）：

sys@PROD>shutdown abort;
ORACLE instance shut down.
sys@PROD>startup mount;
ORACLE instance started.
 
Total System Global Area  184549376 bytes
Fixed Size                  1266488 bytes
Variable Size              62917832 bytes
Database Buffers          117440512 bytes
Redo Buffers                2924544 bytes
Database mounted.
sys@PROD>select file#,name,tablespace_name,status,recover,fuzzy from v$datafile_header;
 
 FILE# NAME                                          TABLESPACE_NAME STATUS  REC FUZ
------ --------------------------------------------- --------------- ------- --- ---
     1 /u01/app/oracle/oradata/PROD/system01.dbf     SYSTEM          ONLINE  NO  YES
     2 /u01/app/oracle/oradata/PROD/undotbs01.dbf    UNDOTBS1        ONLINE  NO  YES
     3 /u01/app/oracle/oradata/PROD/sysaux01.dbf     SYSAUX          ONLINE  NO  YES
     4 /u01/app/oracle/oradata/PROD/users01.dbf      USERS           ONLINE  NO  YES
 
sys@PROD>recover database;
Media recovery complete.
sys@PROD>select file#,name,tablespace_name,status,recover,fuzzy from v$datafile_header;
 
 FILE# NAME                                          TABLESPACE_NAME STATUS  REC FUZ
------ --------------------------------------------- --------------- ------- --- ---
     1 /u01/app/oracle/oradata/PROD/system01.dbf     SYSTEM          ONLINE  NO  NO
     2 /u01/app/oracle/oradata/PROD/undotbs01.dbf    UNDOTBS1        ONLINE  NO  NO
     3 /u01/app/oracle/oradata/PROD/sysaux01.dbf     SYSAUX          ONLINE  NO  NO
     4 /u01/app/oracle/oradata/PROD/users01.dbf      USERS           ONLINE  NO  NO

对比shutdown immediate后，mount状态，fuzzy列（为NO）：

sys@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@PROD>startup mount;
ORACLE instance started.
 
Total System Global Area  184549376 bytes
Fixed Size                  1266488 bytes
Variable Size              62917832 bytes
Database Buffers          117440512 bytes
Redo Buffers                2924544 bytes
Database mounted.
sys@PROD>select file#,name,tablespace_name,status,recover,fuzzy from v$datafile_header;
 
 FILE# NAME                                          TABLESPACE_NAME STATUS  REC FUZ
------ --------------------------------------------- --------------- ------- --- ---
     1 /u01/app/oracle/oradata/PROD/system01.dbf     SYSTEM          ONLINE  NO  NO
     2 /u01/app/oracle/oradata/PROD/undotbs01.dbf    UNDOTBS1        ONLINE  NO  NO
     3 /u01/app/oracle/oradata/PROD/sysaux01.dbf     SYSAUX          ONLINE  NO  NO
     4 /u01/app/oracle/oradata/PROD/users01.dbf      USERS           ONLINE  NO  NO

所以，在恢复时，参考fuzzy列除了可以看出数据文件的状态外，还可以看出数据库是不是一个clean shutdown，需不需要recover。

回到本文的开始，alter log中的提示信息，可以通过以下方式模拟:

源库shutdown abort
冷备一个到B目录
startup nomount
重建控制文件指向B目录，此时fuzzy列为YES
recover database using backup controlfile until cancel;
 
alter log 提示：
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.

— The End —

坏块恢复与归档注册

OoNiceDream — Wed, 07 Sep 2011 17:58:46 +0800

环境AIX 6100/Oracle 10.2.0.4，备份软件：Netbackup
数据库遇到了不少坏块，在用RMAN进行坏块恢复时，遇到了报错：

RMAN> run{
2> allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=host-bf-1,NSR_CLIENT=HOST_SJK_1)';
3> allocate channel t2 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=host-bf-1,NSR_CLIENT=HOST_SJK_2)';
4> set archivelog destination to '/home/oracle/rmanbak/arch2';
5> BLOCKRECOVER DATAFILE 116 BLOCK 893583;
6> release channel t1;
7> release channel t2;
8> }
报错：
Starting blockrecover at 30-AUG-11
 
channel t2: restoring block(s)
channel t2: specifying block(s) to restore from backup set
restoring blocks of datafile 00098
channel t2: reading from backup piece /FULL_YDPT_qjmkjr7n_1_1
channel t2: restored block(s) from backup piece 1
piece handle=/FULL_YDPT_qjmkjr7n_1_1 tag=TAG20110821T030841
channel t2: block restore complete, elapsed time: 00:20:36
 
starting media recovery
 
..................
..................
 
archive log destination=/home/oracle/rmanbak/arch2
channel t2: restoring archive log
archive log thread=2 sequence=4923
channel t2: restoring archive log
archive log thread=2 sequence=4926
channel t2: restoring archive log
archive log thread=2 sequence=4927
channel t2: reading from backup piece arch_YDPT_t759895790_s8040_p1?
ORA-19870: error reading backup piece arch_YDPT_t759895790_s8040_p1
ORA-19507: failed to retrieve sequential file, handle="arch_YDPT_t759895790_s8040_p1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   Could not locate the LNM save file 'arch_YDPT_t759895790_s8040_p1' on server 'host-bf-1'. (2:9:2)
channel t2: starting archive log restore to user-specified destination

以上报错的这个备份的piece，只有3个归档日志：

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7396    528.00M    SBT_TAPE    00:00:08     23-AUG-11      
        BP Key: 7396   Status: AVAILABLE  Compressed: NO  Tag: TAG20110823T020229
        Handle: arch_YDPT_t759895790_s8040_p1   Media: 
 
  List of Archived Logs in backup set 7396
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    4923    116717054828 23-AUG-11 116721862245 23-AUG-11
  2    4926    116721872332 23-AUG-11 116722011507 23-AUG-11
  2    4927    116722011507 23-AUG-11 116722019629 23-AUG-11

但三个归档，我们已经恢复（也是由于这个报错，我们选择了手工恢复这些报错的归档）：

HOST_SJK_2:[/home/oracle/rmanbak/arch2]$ls -l *492*
-rw-r-----    1 oracle   dba          121344 Aug 30 02:47 2_4920_714614581.dbf
-rw-r-----    1 oracle   dba      2090072064 Aug 30 02:47 2_4921_714614581.dbf
-rw-r-----    1 oracle   dba      2090100224 Aug 28 21:05 2_4922_714614581.dbf
-rw-r-----    1 oracle   dba       539039232 Aug 30 02:50 2_4923_714614581.dbf
-rw-r-----    1 oracle   dba          656896 Aug 28 20:58 2_4924_714614581.dbf
-rw-r-----    1 oracle   dba          100352 Aug 28 21:00 2_4925_714614581.dbf
-rw-r-----    1 oracle   dba        13673984 Aug 30 02:50 2_4926_714614581.dbf
-rw-r-----    1 oracle   dba          864256 Aug 30 02:50 2_4927_714614581.dbf
-rw-r-----    1 oracle   dba      2094528512 Aug 30 04:39 2_4928_714614581.dbf
-rw-r-----    1 oracle   dba      2095246848 Aug 30 04:39 2_4929_714614581.dbf

报错的原因，可能是归档备份集(arch_YDPT_t759895790_s8040_p1)所在的NSR_CLIENT变量，与块坏resotre时所在备份集(/FULL_DB_qmmkjst3_1_1)的NSR_CLIENT变量不同。
也可以说是恢复时所在的channel是NSR_CLIENT变量是A，而这些归档的NSR_CLIENT变量是B。
NSR_CLIENT这些信息，当时是在NBU的软件界面上查询出来的。
这是netbackup工程师给出的解释，我也一直觉得软件配置中哪设置设置、修改修改就可以解决的，可惜，换了很多工程师都没找到办法。

最后找到的解决办法是，将所有报错的归档手工注册，在RMAN中用：

RMAN> catalog archivelog '/home/oracle/rmanbak/arch2/1_5771_714614581.dbf';
 
cataloged archive log
archive log filename=/home/oracle/rmanbak/arch2/1_5771_714614581.dbf recid=12929 stamp=760770659

这些备份信息是以copy形式存放的：

RMAN> list copy of archivelog sequence 5771;
 
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
12929   1    5771    A 31-AUG-11 /home/oracle/rmanbak/arch2/1_5771_714614581.dbf

手工将所有的归档恢复出来后，全都注册一把，再次blockrecover，终于都认到归档信息了：

RMAN> run{
2> allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=host-bf-1,NSR_CLIENT=HOST_SJK_1)';
3> allocate channel t2 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=host-bf-1,NSR_CLIENT=HOST_SJK_2)';
4> set archivelog destination to '/home/oracle/rmanbak/arch2';
5> BLOCKRECOVER DATAFILE 116 BLOCK 893583;
6> release channel t1;
7> release channel t2;
8> }
 
allocated channel: t1
channel t1: sid=2424 instance=DB1 devtype=SBT_TAPE
channel t1: NMDA Oracle v1.0.0
 
allocated channel: t2
channel t2: sid=1905 instance=DB1 devtype=SBT_TAPE
channel t2: NMDA Oracle v1.0.0
 
executing command: SET ARCHIVELOG DESTINATION
 
Starting blockrecover at 02-SEP-11
 
channel t1: restoring block(s)
channel t1: specifying block(s) to restore from backup set
restoring blocks of datafile 00116
channel t1: reading from backup piece /FULL_DB_qmmkjst3_1_1
channel t1: restored block(s) from backup piece 1
piece handle=/FULL_DB_qmmkjst3_1_1 tag=TAG20110822T052656
channel t1: block restore complete, elapsed time: 00:25:36
 
starting media recovery
 
archive log thread 1 sequence 5609 is already on disk as file /home/oracle/rmanbak/arch2/1_5609_714614581.dbf
archive log thread 1 sequence 5610 is already on disk as file /home/oracle/rmanbak/arch2/1_5610_714614581.dbf
archive log thread 1 sequence 5611 is already on disk as file /home/oracle/rmanbak/arch2/1_5611_714614581.dbf
archive log thread 1 sequence 5612 is already on disk as file /home/oracle/rmanbak/arch2/1_5612_714614581.dbf
archive log thread 1 sequence 5613 is already on disk as file /home/oracle/rmanbak/arch2/1_5613_714614581.dbf
archive log thread 1 sequence 5614 is already on disk as file /home/oracle/rmanbak/arch2/1_5614_714614581.dbf
archive log thread 1 sequence 5615 is already on disk as file /home/oracle/rmanbak/arch2/1_5615_714614581.dbf
archive log thread 1 sequence 5616 is already on disk as file /home/oracle/rmanbak/arch2/1_5616_714614581.dbf
archive log thread 1 sequence 5617 is already on disk as file /home/oracle/rmanbak/arch2/1_5617_714614581.dbf
 
....................
....................
....................
 
archive log thread 2 sequence 5103 is already on disk as file /home/oracle/rmanbak/arch2/2_5103_714614581.dbf
archive log thread 2 sequence 5104 is already on disk as file /home/oracle/rmanbak/arch2/2_5104_714614581.dbf
archive log thread 2 sequence 5105 is already on disk as file /home/oracle/rmanbak/arch2/2_5105_714614581.dbf
archive log thread 2 sequence 5106 is already on disk as file /home/oracle/rmanbak/arch2/2_5106_714614581.dbf
media recovery complete, elapsed time: 02:08:41
Finished blockrecover at 02-SEP-11
 
released channel: t1
 
released channel: t2

参考文档：
HOW TO PERFORM BLOCK MEDIA RECOVERY (BMR) WHEN BACKUPS ARE NOT TAKEN BY RMAN. [ID 342972.1]

— The End —

NFS mount问题

OoNiceDream — Wed, 07 Sep 2011 14:45:55 +0800

环境AIX 6100/Oracle 10.2.0.4
在做恢复时，将归档恢复到挂载过来的NFS，报错：

rman target lgto/lgto@DB2
run{
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=sjyd-bf-1,NSR_CLIENT=SJYD_SJK_1)';
allocate channel t2 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=sjyd-bf-1,NSR_CLIENT=SJYD_SJK_2)';
set archivelog destination to '/home/oracle/rmanbak/arch2';
restore archivelog sequence 5026 thread 2;
switch datafile all;
release channel t1;
release channel t2;
}
 
 
allocated channel: t1
channel t1: sid=1808 instance=DB1 devtype=SBT_TAPE
channel t1: NMDA Oracle v1.0.0
 
allocated channel: t2
channel t2: sid=1358 instance=DB1 devtype=SBT_TAPE
channel t2: NMDA Oracle v1.0.0
 
executing command: SET ARCHIVELOG DESTINATION
 
Starting restore at 30-AUG-11
 
channel t2: starting archive log restore to user-specified destination
archive log destination=/home/oracle/rmanbak/arch1
channel t2: restoring archive log
archive log thread=2 sequence=5026
channel t2: reading from backup piece arch_DB_t760337546_s8137_p1
ORA-19870: error reading backup piece arch_DB_t760337546_s8137_p1
ORA-19504: failed to create file "/home/oracle/rmanbak/arch1/2_5026_714614581.dbf"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6
failover to previous backup
 
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/30/2011 12:53:41
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 2 seq 5026 lowscn 117046553883 found to restore

找到几篇文章，有说NFS mout时，需要用hard模式，重新挂载下：

umount /home/oracle/rmanbak
 
mount -v nfs -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,proto=tcp 192.168.10.49:/storage/rmanbak /home/oracle/rmanbak

恢复仍报错。将mount信息写到/etc/filesystems，可正常恢复了：

/home/oracle/rmanbak:
        dev             = /storage/rmanbak
        vfs             = nfs
        nodename        = 192.168.10.49
        mount           = true
        options         = rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,vers=3,timeo=600
        account         = false

几个文档中都提到设置10298事件可以解决该报错，未测试：
event=”10298 trace name context forever, level 32″

参考文档：
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options [ID 781349.1]
WRONG MOUNT OPTIONS AIX [ID 420582.1]

— The End —

transaction等待与僵死事物回滚

OoNiceDream — Fri, 26 Aug 2011 17:44:20 +0800

检查数据库发现有不少transaction等待事件，查看文档，发现该等待事件是会话在等待事物回滚引起的：

Wait for a blocking transaction to be rolled back. Continue waiting until the transaction has been rolled back.
 
Wait Time: 1 second
Parameter 	Description
undo seg# 	The rollback segment ID
slot# 	The slot ID inside the rollback segment
wrap# 	The sequence number that is incremented for each transaction
count 	The number of times that the session has waited on this transaction

根据解释，试着分解下p1raw:

SQL> select sid,
  2         seq#,
  3         event,
  4         p1raw,
  5         to_number(substr(p1raw, 1, 12), 'xxxxxxxxxxxxxx') undo_seg#,
  6         to_number(substr(p1raw, 13, 4), 'xxxxxxxxxxxxxx') slot#,
  7         p2 wrap#
  8    from v$session_wait
  9   where event = 'transaction';
 
    SID       SEQ# EVENT          P1RAW             UNDO_SEG#      SLOT#      WRAP#
------- ---------- -------------- ---------------- ---------- ---------- ----------
    126      34574 transaction    0000000005C6002A       1478         42    1160985
    254      54675 transaction    0000000005C6002A       1478         42    1160985
    463      43888 transaction    0000000005C6002A       1478         42    1160985
   1032       1514 transaction    0000000005C6002A       1478         42    1160985
   1009      10184 transaction    0000000005C6002A       1478         42    1160985
    835       3244 transaction    0000000005C6002A       1478         42    1160985
    783      56173 transaction    0000000005C6002A       1478         42    1160985
    780       9823 transaction    0000000005C6002A       1478         42    1160985
    760        302 transaction    0000000005C6002A       1478         42    1160985
    687      21500 transaction    0000000005C6002A       1478         42    1160985
    622      51064 transaction    0000000005C6002A       1478         42    1160985
    589      23390 transaction    0000000005C6002A       1478         42    1160985
   2565       3288 transaction    0000000005C6002A       1478         42    1160985
   2452      36087 transaction    0000000005C6002A       1478         42    1160985
   1952      14522 transaction    0000000005C6002A       1478         42    1160985
   1934      42843 transaction    0000000005C6002A       1478         42    1160985
   1867      47919 transaction    0000000005C6002A       1478         42    1160985
   1683      56912 transaction    0000000005C6002A       1478         42    1160985
   1609      57399 transaction    0000000005C6002A       1478         42    1160985
   1549      49748 transaction    0000000005C6002A       1478         42    1160985
   1386      17543 transaction    0000000005C6002A       1478         42    1160985
   1368      12885 transaction    0000000005C6002A       1478         42    1160985
   1359      45603 transaction    0000000005C6002A       1478         42    1160985
   1222      25543 transaction    0000000005C6002A       1478         42    1160985
   1089      43488 transaction    0000000005C6002A       1478         42    1160985
   1065      40328 transaction    0000000005C6002A       1478         42    1160985
   1061      10693 transaction    0000000005C6002A       1478         42    1160985
    186      14309 transaction    0000000005C6002A       1478         42    1160985
    333      13982 transaction    0000000005C6002A       1478         42    1160985
    279      26949 transaction    0000000005C6002A       1478         42    1160985

与僵死事物的回滚信息还是一致的：

SQL> select ktuxeusn, ktuxesiz, ktuxesta, 
  2         KTUXEUSN, KTUXESLT, KTUXESQN
  3    from x$ktuxe
  4   where ktuxecfl = 'DEAD'; 
 
  KTUXEUSN   KTUXESIZ KTUXESTA           KTUXEUSN   KTUXESLT   KTUXESQN
---------- ---------- ---------------- ---------- ---------- ----------
        15          0 INACTIVE                 15         11   11240607
        39          0 INACTIVE                 39          8    5434064
        50          0 INACTIVE                 50          3    3088316
      1478    1065934 ACTIVE                 1478         42    1160985

由于应用人员杀了不少会话，事物需要回滚，但悲剧的是KTUXESIZ一直不变，说明事物压根没在回滚。
查找数据库中KILLED状态的会话，将其后台进程都kill -9，问题依旧。

僵死事物回滚一般由SMON进行，查看两个节点的SMON进程，都在等待一些异常的等待事件，不停地在enqueue、DFS lock handle间变化：

节点1：

SQL> select sid,sql_hash_value,program from v$session where sid=43;
 
    SID SQL_HASH_VALUE PROGRAM
------- -------------- --------------------
     43     3587907089 oracle@db1 (SMON)
 
 
SQL> r
  1  select s.sid,
  2         s.sql_hash_value,
  3         sw.event,
  4         sw.p2,
  5         sw.p3,
  6         chr(bitand(sw.P1, -16777216) / 16777215) ||
  7         chr(bitand(sw.P1, 16711680) / 65535) type,
  8         mod(sw.P1, 16) "MODE",
  9         sw.SECONDS_IN_WAIT ctime
 10  from v$session_wait sw, v$session s
 11   where sw.sid=43
 12     and sw.sid = s.sid
 13*  order by p2,p3
 
       SID SQL_HASH_VALUE EVENT                   P2         P3 TYPE       MODE      CTIME
---------- -------------- --------------- ---------- ---------- ---- ---------- ----------
        43     3587907089 enqueue                  1          0 RO            6          8
 
SQL> r
  1  select s.sid,
  2         s.sql_hash_value,
  3         sw.event,
  4         sw.p2,
  5         sw.p3,
  6         chr(bitand(sw.P1, -16777216) / 16777215) ||
  7         chr(bitand(sw.P1, 16711680) / 65535) type,
  8         mod(sw.P1, 16) "MODE",
  9         sw.SECONDS_IN_WAIT ctime
 10  from v$session_wait sw, v$session s
 11   where sw.sid=43
 12     and sw.sid = s.sid
 13*  order by p2,p3
 
       SID SQL_HASH_VALUE EVENT                   P2         P3 TYPE       MODE      CTIME
---------- -------------- --------------- ---------- ---------- ---- ---------- ----------
        43     3587907089 DFS lock handle         18          2 CI            5          2

节点2：

SQL> r
  1  select s.sid,
  2         s.sql_hash_value,
  3         sw.event,
  4         sw.p2,
  5         sw.p3,
  6         chr(bitand(sw.P1, -16777216) / 16777215) ||
  7         chr(bitand(sw.P1, 16711680) / 65535) type,
  8         mod(sw.P1, 16) "MODE",
  9         sw.SECONDS_IN_WAIT ctime
 10  from v$session_wait sw, v$session s
 11   where sw.sid=47
 12     and sw.sid = s.sid
 13*  order by p2,p3
 
       SID SQL_HASH_VALUE EVENT                   P2         P3 TYPE       MODE      CTIME
---------- -------------- --------------- ---------- ---------- ---- ---------- ----------
        47     3587907089 enqueue                  1          0 RO            6          3
 
SQL> r
  1  select s.sid,
  2         s.sql_hash_value,
  3         sw.event,
  4         sw.p2,
  5         sw.p3,
  6         chr(bitand(sw.P1, -16777216) / 16777215) ||
  7         chr(bitand(sw.P1, 16711680) / 65535) type,
  8         mod(sw.P1, 16) "MODE",
  9         sw.SECONDS_IN_WAIT ctime
 10  from v$session_wait sw, v$session s
 11   where sw.sid=47
 12     and sw.sid = s.sid
 13*  order by p2,p3
 
       SID SQL_HASH_VALUE EVENT                   P2         P3 TYPE       MODE      CTIME
---------- -------------- --------------- ---------- ---------- ---- ---------- ----------
        47     3587907089 DFS lock handle         18          2 CI            5          0

语句都一样：

[SQL Text]
--------------------------------------------------------------------------------
 
select file#, block#, ts# from seg$ where type# = 3
 
[Execution Plan Information]
 
----------------------------------------------------------------------------------------------------
| Operation                                | PHV/Object Name               |  Rows | Bytes| Cost   |
----------------------------------------------------------------------------------------------------
|000[000]SELECT STATEMENT                  |---- 3587907089.0 ----         |       |      |        |
|001[001]TABLE ACCESS FULL                 |SEG$                           |       |      |        |
----------------------------------------------------------------------------------
 
[Predicate Information]
 
------------------------------------------------------------
 
         1 filter:"TYPE#"=3

等待事件enqueue的类型的RO，从v$lock中观察，两个SMON进程像是在互锁。

与事物回滚相关的最重要的参数就是fast_start_parallel_rollback了，当前为LOW。
FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.
Values:
FALSE indicates that parallel rollback is disabled
LOW (default value)limits the number of rollback processes to 2 * CPU_COUNT
HIGH limits the number of rollback processes to 4 * CPU_COUN

尝试修改成HIGH后，发现事物开始回滚了：

SQL> select ktuxeusn, ktuxesiz, ktuxesta, 
  2         KTUXEUSN, KTUXESLT, KTUXESQN
  3    from x$ktuxe
  4   where ktuxecfl = 'DEAD'; 
 
  KTUXEUSN   KTUXESIZ KTUXESTA           KTUXEUSN   KTUXESLT   KTUXESQN
---------- ---------- ---------------- ---------- ---------- ----------
        15          0 INACTIVE                 15         11   11240607
        39          0 INACTIVE                 39          8    5434064
        50          0 INACTIVE                 50          3    3088316
      1478    1063268 ACTIVE                 1478         42    1160985
 
SQL> r
  1  select ktuxeusn, ktuxesiz, ktuxesta,
  2         KTUXEUSN, KTUXESLT, KTUXESQN
  3    from x$ktuxe
  4*  where ktuxecfl = 'DEAD'
 
  KTUXEUSN   KTUXESIZ KTUXESTA           KTUXEUSN   KTUXESLT   KTUXESQN
---------- ---------- ---------------- ---------- ---------- ----------
        15          0 INACTIVE                 15         11   11240607
        39          0 INACTIVE                 39          8    5434064
        50          0 INACTIVE                 50          3    3088316
      1478    1063176 ACTIVE                 1478         42    1160985
 
SQL> select ktuxeusn, ktuxesiz, ktuxesta, 
  2         KTUXEUSN, KTUXESLT, KTUXESQN
  3    from x$ktuxe
  4   where ktuxecfl = 'DEAD'; 
 
  KTUXEUSN   KTUXESIZ KTUXESTA           KTUXEUSN   KTUXESLT   KTUXESQN
---------- ---------- ---------------- ---------- ---------- ----------
        15          0 INACTIVE                 15         11   11240607
        39          0 INACTIVE                 39          8    5434064
        50          0 INACTIVE                 50          3    3088316
      1478    1058504 ACTIVE                 1478         42    1160985

没几分钟，又挂住了，现象一样，又是enqueue、DFS lock handle。之后再尝试修改fast_start_parallel_rollback，改来改去都挂那不动了。
查询select * from v$fast_start_servers; 也无进程在工作了。

查找到一些关于DFS lock handle的文档，发现基本与BUG有关。
The DFS lock Handle:

The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.

后尝试重启数据库了，停1节点后，事物开始回滚，没多久，又挂住，等待事件The DFS lock Handle。(1478这个回滚段在2节点上)。
重启2节点，事物开始正常回滚了，等待事件基本上是 db file sequential read，再启1节点，正常。

PERFSTAT@custom2> select * from v$fast_start_servers;
 
STATE       UNDOBLOCKSDONE        PID
----------- -------------- ----------
RECOVERING             100         67
RECOVERING               0         68
RECOVERING               0         69
RECOVERING               0         70
RECOVERING               0         71
RECOVERING               0         72
RECOVERING               0         73
RECOVERING               0         74
RECOVERING               0         75
RECOVERING               0         76
RECOVERING               0         77
RECOVERING               0         78
RECOVERING               0         79
RECOVERING               0         80
RECOVERING               0         81
RECOVERING               0         82
RECOVERING               0         83
RECOVERING             100         84
RECOVERING               0         86
RECOVERING               0         87
RECOVERING               0         88
RECOVERING               0         89
RECOVERING               0         90
RECOVERING             100         91
 
24 rows selected.

重启前，还修改了以下参数，调成了400，据说是可以加快回滚速度:
_cleanup_rollback_entries ：undo entries to apply per transaction cleanup

另外，发现FAST_START_PARALLEL_ROLLBACK为FALSE和HIGH时，回滚速度差距好大，粗算下FALSE要6天多，HIGH为4小时，虽然最后1小时不到便回滚结束。

— The End —

RAC补丁回退引起的问题

OoNiceDream — Fri, 29 Jul 2011 18:58:35 +0800

环境 HP IA RAC，1节点打完所有小补丁后，继续在2节点打，第一个补丁便报错：

[/app/oracle/soft/3.patchset]$ cd 11737047
[/app/oracle/soft/3.patchset/11737047]$ opatch apply -local
Invoking OPatch 10.2.0.5.1
 
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
 
 
Oracle Home       : /app/oracle/product/10.2.0/db
Central Inventory : /app/oracle/product/10.2.0/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /app/oracle/product/10.2.0/db/oui
Log file location : /app/oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2011-07-29_12-57-50PM.log
 
Patch history file: /app/oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch_history.txt
 
ApplySession applying interim patch '11737047' to OH '/app/oracle/product/10.2.0/db'
OPatchSession cannot load inventory for the given Oracle Home /app/oracle/product/10.2.0/db. Possible causes are:
   No read or write permission to ORACLE_HOME/.patch_storage
   Central Inventory is locked by another OUI instance
   No read permission to Central Inventory
   The lock file exists in ORACLE_HOME/.patch_storage
   The Oracle Home does not exist in Central Inventory
 
ApplySession failed: ApplySession failed to prepare the system. Unable to create patchObject
System intact, OPatch will not attempt to restore the system
 
OPatch failed with error code 73

同样opatch lsinventory 也报错，但奇怪的是刚刚打PSU补丁时还是正常的：

[/app/oracle/soft/3.patchset/11737047]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1
 
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
 
 
Oracle Home       : /app/oracle/product/10.2.0/db
Central Inventory : /app/oracle/product/10.2.0/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /app/oracle/product/10.2.0/db/oui
Log file location : /app/oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2011-07-29_13-00-09PM.log
 
Patch history file: /app/oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch_history.txt
 
 
 
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
 
LsInventorySession failed: Unable to create patchObject
 
OPatch failed with error code 73

做个trace：

[/app/oracle/product/10.2.0/db]$ export OPATCH_DEBUG=TRUE
[/app/oracle/product/10.2.0/db]$ opatch lsinventory
_osArch is IA64N
/app/oracle/product/10.2.0/db/jdk/bin/java -cp /app/oracle/product/10.2.0/db/OPatch/ocm/lib/emocmutl.jar:/app/oracle/product/10.2.0/db/OPatch/ocm/lib/emocmclnt.jar:/app/oracle/product/10.2.0/db/oui/jlib/OraInstaller.jar:/app/oracle/product/10.2.0/db/oui/jlib/OraPrereq.jar:/app/oracle/product/10.2.0/db/oui/jlib/xmlparserv2.jar:/app/oracle/product/10.2.0/db/oui/jlib/orai18n-mapping.jar:/app/oracle/product/10.2.0/db/oui/jlib/share.jar:/app/oracle/product/10.2.0/db/oui/jlib/srvm.jar:/app/oracle/product/10.2.0/db/OPatch/jlib/opatch.jar:/app/oracle/product/10.2.0/db/OPatch/jlib/opatchutil.jar:/app/oracle/product/10.2.0/db/OPatch/jlib/opatchprereq.jar:/app/oracle/product/10.2.0/db/OPatch/jlib/opatchactions.jar:/app/oracle/product/10.2.0/db/OPatch/jlib/opatchfmw.jar:/app/oracle/product/10.2.0/db/OPatch/jlib/opatchext.jar -DOPatch.ORACLE_HOME=/app/oracle/product/10.2.0/db -DOPatch.DEBUG=true -DOPatch.RUNNING_DIR=/app/oracle/product/10.2.0/db/OPatch   oracle/opatch/OPatch lsinventory
Invoking OPatch 10.2.0.5.1
 
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.
 
OPatchSession::parse() on "lsinventory",
 
.............................
.............................
.............................
 
PatchObject(oracleHomePath,patchID)
PatchObject::PatchObject looks for the actions file /app/oracle/product/10.2.0/db/inventory/oneoffs/11737047/etc/config/actions.xml
PatchObject::PatchObject looks for the inventory file /app/oracle/product/10.2.0/db/inventory/oneoffs/11737047/etc/config/inventory.xml
PatchObject::PatchObject now looks for the actions file /app/oracle/product/10.2.0/db/inventory/oneoffs/11737047/etc/config/actions
PatchObject::PatchObject now looks for the inventory file /app/oracle/product/10.2.0/db/inventory/oneoffs/11737047/etc/config/inventory
Loading the information from inventory/filemap.
 
 
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
 
Locker::release()
OUISessionManager::unRegister()
Un-Registering the caller : OPatch
LsInventory::getInstance() returns
LsInventorySession failed: Unable to create patchObject
Cleaning up the directory : "/app/oracle/product/10.2.0/db/.patch_storage/patch_unzip"...
 
OPatch failed with error code 73

这里比较关键的是，报错前去读取了11737047补丁的信息，但事实上，我还没把11737047补丁打上去，oneoffs下也没这个补丁：
（囧的是我一开始把11737047看到了11724962，还尝试回退了11724962，不过也是报错信息跟打补丁时报错一样)

[/app/oracle/product/10.2.0/db/inventory/oneoffs]$ ls -lrt
total 0
drwxrwxrwx   4 oracle     dba             96 Jul 29 10:03 9952245
drwxrwxrwx   4 oracle     dba             96 Jul 29 12:27 11724962

发现没有安装补丁后，找到配置文件，发现配置文件中确实多了几个补丁 11737047、10214450、9981011：

/app/oracle/product/10.2.0/db/inventory/ContentsXML/comps.xml：
 
<ONEOFF REF_ID="11737047" ROLLBACK="T" XML_INV_LOC="oneoffs/11737047/" ACT_INST_VER="10.2.0.5.0" INSTALL_TIME="2011.Jul.29 12:27:00 GMT+08:00">
   <DESC></DESC>
   <REF_LIST>
      <REF NAME="oracle.rdbms" VER="10.2.0.5.0" HOME_IDX="2"/>
   </REF_LIST>
   <BUG_LIST>
      <BUG>11737047</BUG>
   </BUG_LIST>
   <PREREQID_LIST/>
   <FILE_LIST/>
</ONEOFF>
<ONEOFF REF_ID="10214450" ROLLBACK="T" XML_INV_LOC="oneoffs/10214450/" ACT_INST_VER="10.2.0.5.0" INSTALL_TIME="2011.Jul.29 12:29:22 GMT+08:00">
   <DESC></DESC>
   <REF_LIST>
      <REF NAME="oracle.rdbms" VER="10.2.0.5.0" HOME_IDX="2"/>
      <REF NAME="oracle.rdbms.rsf" VER="10.2.0.5.0" HOME_IDX="2"/>
   </REF_LIST>
   <BUG_LIST>
      <BUG>10214450</BUG>
   </BUG_LIST>
   <PREREQID_LIST/>
   <FILE_LIST/>
</ONEOFF>
<ONEOFF REF_ID="9981011" ROLLBACK="T" XML_INV_LOC="oneoffs/9981011/" ACT_INST_VER="10.2.0.5.0" INSTALL_TIME="2011.Jul.29 12:34:06 GMT+08:00">
   <DESC></DESC>
   <REF_LIST>
      <REF NAME="oracle.rdbms" VER="10.2.0.5.0" HOME_IDX="2"/>
      <REF NAME="oracle.rdbms.rsf" VER="10.2.0.5.0" HOME_IDX="2"/>
   </REF_LIST>
   <BUG_LIST>
      <BUG>9981011</BUG>
   </BUG_LIST>
   <PREREQID_LIST/>
   <FILE_LIST/>
</ONEOFF>
</ONEOFF_LIST>

而这三个补丁压根没打过，对比1节点，打补丁的时间居然是一模一样的:

INSTALL_TIME="2011.Jul.29 12:27:00 GMT+08:00" 
INSTALL_TIME="2011.Jul.29 12:29:22 GMT+08:00"
INSTALL_TIME="2011.Jul.29 12:34:06 GMT+08:00"

查找一节点日志，发现打完这三个补丁后，打第四个补丁时，遇到些错误，回退过一次：

[/app/oracle/soft10g/3.patchset/9980062]$ opatch rollback -id 9980062

两个节点均打补丁时间变成一样，说明回退补丁时，可能将1节点的补丁信息更新到了2节点上了

根据文档，可以看出，如果打补丁是全局的操作，则comps.xml会被复制到另一个节点：
Running Opatch Lsinventory Failed With Error Code 73 on Remote Node [ID 602497.1]

When a patch is applied to the cluster, the files of the new patch and the modifications of the
inventory are copied to the remote node. One of the files updated and copied is the comps.xml
file, therefore the references for patch applied locally included on the comps.xml file are copied to the node 2… but really the patch is not applied to the node 2, therefore the inventory is corrupted on the node 2.

而回退时opatch rollback -id 9980062 时未使用 local选项，导致了这一问题。

文档上建议是回退补丁，再重打，这里我简单的修改comps.xml文件，删除前面找到的补丁信息。

— The End —

HP 异步IO报错

OoNiceDream — Wed, 27 Jul 2011 14:18:49 +0800

数据库APP目录使用率激增至95%，查看dump目录，发现今天生成近3W个trace文件，查看报错信息为：

[/app/oracle/admin/DB/udump]$ ls -lrt |wc -l
29714
 
/app/oracle/admin/DB/udump/DB1_ora_865.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/10.2.0/db
System name:    HP-UX
Node name:      db-p01
Release:        B.11.31
Version:        U
Machine:        ia64
Instance name: DB1
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 865, image: oracle@db-p01
 
Ioctl ASYNC_CONFIG error, errno = 1

从ASYNC_CONFIG报错可以看出，是跟异步IO相关的报错，一般通过给DBA组授MLOCK权限，可以避免Ioctl ASYNC_CONFIG的报错

(1) # /usr/sbin/setprivgrp dba MLOCK
(2) # vi /etc/privgroup
-- This should contain dba MLOCK RTSCHED RTPRIO
(3) # cat /etc/privgroup
dba MLOCK RTSCHED RTPRIO

参考文档：
How to Disable Asynch_io on HP to Avoid Ioctl Async_config Error Errno = 1 [ID 302801.1]

— The End —