分类目录归档:Oracle

建立合适的索引优化查询

对大数据量查询时,合理使用索引能极大的提高查询效率,以下几则案例是前一段时间优化系统时做的,简单记录一下。

案例一,优化前的查询执行计划:

SQL> set autotrace traceonly
SQL> set timing on
SQL> set linesize 200
SQL>
SQL> select count(*) from busilink
2  where (wpcode=’074′ or wpcode=’078′  or  wpcode=’099′ ) and wcode =’0795′
3  or (wcode in (select  rwcode  from ratifyauth where wcodeto = ’0795′) and (wpcode=’074′  or wpcode=’078′   or wpcode=’099′ ) )
4  /

Elapsed: 00:00:00.05

Execution Plan
———————————————————-
Plan hash value: 4220767725

————————————————————————————————
| Id  | Operation              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————
|   0 | SELECT STATEMENT       |                       |     1 |     9 |   131   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE        |                       |     1 |     9 |            |          |
|*  2 |   FILTER               |                       |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| BUSILINK_PRIMARYKEY   |  1824 | 16416 |   131   (3)| 00:00:02 |
|*  4 |    INDEX RANGE SCAN    | RATIFYAUTH_PRIMARYKEY |     1 |    11 |     2   (0)| 00:00:01 |
————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“WCODE”=’0795′ OR  EXISTS (SELECT 0 FROM “RATIFYAUTH” “RATIFYAUTH” WHERE
“WCODETO”=’0795′ AND “RWCODE”=:B1))
3 – filter(“WPCODE”=’074′ OR “WPCODE”=’078′ OR “WPCODE”=’099′)
4 – access(“RWCODE”=:B1 AND “WCODETO”=’0795′)

Statistics
———————————————————-
1  recursive calls
0  db block gets
818  consistent gets
0  physical reads
0  redo size
514  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

经分析后在wpcode,wcode两列上建立复合索引:

SQL> create index idx_busilink_wpcode_wcode on busilink(wpcode,wcode) tablespace erp_index;

Index created.

Elapsed: 00:00:00.36
SQL>
SQL> select count(*) from busilink
2  where (wpcode=’074′ or wpcode=’078′  or  wpcode=’099′ ) and wcode =’0795′
3  or (wcode in (select  rwcode  from ratifyauth where wcodeto = ’0795′) and (wpcode=’074′  or wpcode=’078′   or wpcode=’099′ ) )
4  /

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 2874463383

————————————————————————————————-
| Id  | Operation           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————-
|   0 | SELECT STATEMENT    |                           |     1 |     9 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                           |     1 |     9 |            |          |
|*  2 |   FILTER            |                           |       |       |            |          |
|   3 |    INLIST ITERATOR  |                           |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| IDX_BUSILINK_WPCODE_WCODE |  1824 | 16416 |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN | RATIFYAUTH_PRIMARYKEY     |     1 |    11 |     2   (0)| 00:00:01 |
————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“WCODE”=’0795′ OR  EXISTS (SELECT 0 FROM “RATIFYAUTH” “RATIFYAUTH” WHERE
“WCODETO”=’0795′ AND “RWCODE”=:B1))
4 – access(“WPCODE”=’074′ OR “WPCODE”=’078′ OR “WPCODE”=’099′)
5 – access(“RWCODE”=:B1 AND “WCODETO”=’0795′)

Statistics
———————————————————-
1  recursive calls
0  db block gets
244  consistent gets
7  physical reads
0  redo size
514  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

逻辑读有大幅度的减少,效果明显。

案例二,单列索引,建立索引前走全表扫描:

SQL> select count(*)
2  from
3   ccode where groupccode=’9010000000′
4  /

Elapsed: 00:00:00.09

Execution Plan
———————————————————-
Plan hash value: 2879317460

—————————————————————————-
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————-
|   0 | SELECT STATEMENT   |       |     1 |    11 |   901   (2)| 00:00:11 |
|   1 |  SORT AGGREGATE    |       |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| CCODE |     1 |    11 |   901   (2)| 00:00:11 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“GROUPCCODE”=’9010000000′)

Statistics
———————————————————-
1  recursive calls
0  db block gets
4036  consistent gets
0  physical reads
0  redo size
515  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

根据查询条件建立索引:

SQL> create index idx_ccode_groupccode on ccode(groupccode) tablespace erp_index;

Index created.

Elapsed: 00:00:00.45
SQL>
SQL> alter session set events = ‘immediate trace name flush_cache’;

Session altered.

Elapsed: 00:00:01.40
SQL> select count(*) from ccode where groupccode=’9010000000′;

Elapsed: 00:00:00.01

Execution Plan
———————————————————-
Plan hash value: 2074245817

——————————————————————————————
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————
|   0 | SELECT STATEMENT  |                      |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                      |     1 |    11 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_CCODE_GROUPCCODE |     1 |    11 |     1   (0)| 00:00:01 |
——————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – access(“GROUPCCODE”=’9010000000′)

Statistics
———————————————————-
1  recursive calls
0  db block gets
2  consistent gets
2  physical reads
0  redo size
515  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

案例三,在不同列上建立复合索引的效果比对:

优化之前:

SQL> alter session set events = ‘immediate trace name flush_cache’;

Session altered.

Elapsed: 00:00:00.15
SQL>
SQL> SELECT IHCODE, HCODE, ETRDCODE, HSTATUS, SUBSTR(BCODE, 1, 5) AS BCODE1, BCODE,
2     RMB, CCODE, GCODELIST, QTYLIST, QTYUNITLIST, BUSIMODE1, BUSIMODE2,
3    INOUTMODE, ECODE, VPREPARE, EDITION, predate,’*', BCODE, HSTATUS, HTYPE,
4    BUSIMODE1, INOUTMODE, ECODE,CONTRACTTBL.predate
5  FROM
6   CONTRACTTBL WHERE  SBCODE <> ’200994′ AND  (IHCODE LIKE ‘________________’
7    OR  IHCODE LIKE ‘________________xx’)    AND (BCODE LIKE ’01%’ OR BCODE
8    LIKE ’02%’ OR BCODE LIKE ’03%’ OR BCODE LIKE ’04%’ OR BCODE LIKE ’05%’ OR
9    BCODE LIKE ’06%’ OR BCODE LIKE ’07%’ OR BCODE LIKE ’08%’ OR BCODE LIKE
10    ’09%’ OR BCODE LIKE ’10%’ OR BCODE LIKE ’11%’ OR BCODE LIKE ’90%’ OR BCODE
11    LIKE ’91%’ OR BCODE LIKE ’01%’ OR BCODE LIKE ’02%’ OR BCODE LIKE ’03%’ OR
12    BCODE LIKE ’04%’ OR BCODE LIKE ’05%’ OR BCODE LIKE ’06%’ OR BCODE LIKE
13    ’07%’ OR BCODE LIKE ’08%’ OR BCODE LIKE ’09%’ OR BCODE LIKE ’10%’ OR BCODE
14    LIKE ’11%’ OR BCODE LIKE ’90%’ OR BCODE LIKE ’91%’ OR BCODE LIKE ’02%’ OR
15    BCODE LIKE ’03%’ OR BCODE LIKE ’05%’ OR BCODE LIKE ’07%’ OR BCODE =
16    ’01100040049′) AND HTYPE =’190301′ AND INOUTMODE =’01′ AND PREDATE>=
17    TO_DATE(’2009-01-01′, ‘yyyy-mm-dd’) ORDER BY IHCODE DESC
18  /

15815 rows selected.

Elapsed: 00:00:37.96

Execution Plan
———————————————————-
Plan hash value: 693144140

——————————————————————————————————————
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————
|   0 | SELECT STATEMENT                  |                              |     2 |   280 |  3640   (1)| 00:00:44 |
|   1 |  SORT ORDER BY                    |                              |     2 |   280 |  3640   (1)| 00:00:44 |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | CONTRACTTBL                  |     2 |   280 |  3639   (1)| 00:00:44 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                              |       |       |            |          |
|   4 |     BITMAP OR                     |                              |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|   6 |       SORT ORDER BY               |                              |       |       |            |          |
|*  7 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|   9 |       SORT ORDER BY               |                              |       |       |            |          |
|* 10 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  11 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  12 |       SORT ORDER BY               |                              |       |       |            |          |
|* 13 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  14 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  15 |       SORT ORDER BY               |                              |       |       |            |          |
|* 16 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  17 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  18 |       SORT ORDER BY               |                              |       |       |            |          |
|* 19 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  20 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  21 |       SORT ORDER BY               |                              |       |       |            |          |
|* 22 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  23 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  24 |       SORT ORDER BY               |                              |       |       |            |          |
|* 25 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  26 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  27 |       SORT ORDER BY               |                              |       |       |            |          |
|* 28 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  29 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  30 |       SORT ORDER BY               |                              |       |       |            |          |
|* 31 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  32 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  33 |       SORT ORDER BY               |                              |       |       |            |          |
|* 34 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  35 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  36 |       SORT ORDER BY               |                              |       |       |            |          |
|* 37 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  38 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  39 |       SORT ORDER BY               |                              |       |       |            |          |
|* 40 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  41 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  42 |       SORT ORDER BY               |                              |       |       |            |          |
|* 43 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
|  44 |      BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  45 |       SORT ORDER BY               |                              |       |       |            |          |
|* 46 |        INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |     5   (0)| 00:00:01 |
——————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“HTYPE”=’190301′ AND “PREDATE”>=TO_DATE(’2009-01-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’) AND
“INOUTMODE”=’01′ AND “SBCODE”<>’200994′ AND (“IHCODE” LIKE ‘________________’ OR “IHCODE” LIKE
‘________________xx’))
7 – access(“BCODE”=’01100040049′)
filter(“BCODE”=’01100040049′)
10 – access(“BCODE” LIKE ’03%’)
filter(“BCODE” LIKE ’03%’ AND “BCODE” LIKE ’03%’)
13 – access(“BCODE” LIKE ’06%’)
filter(“BCODE” LIKE ’06%’ AND “BCODE” LIKE ’06%’)
16 – access(“BCODE” LIKE ’09%’)
filter(“BCODE” LIKE ’09%’ AND “BCODE” LIKE ’09%’)
19 – access(“BCODE” LIKE ’02%’)
filter(“BCODE” LIKE ’02%’ AND “BCODE” LIKE ’02%’)
22 – access(“BCODE” LIKE ’10%’)
filter(“BCODE” LIKE ’10%’ AND “BCODE” LIKE ’10%’)
25 – access(“BCODE” LIKE ’08%’)
filter(“BCODE” LIKE ’08%’ AND “BCODE” LIKE ’08%’)
28 – access(“BCODE” LIKE ’05%’)
filter(“BCODE” LIKE ’05%’ AND “BCODE” LIKE ’05%’)
31 – access(“BCODE” LIKE ’11%’)
filter(“BCODE” LIKE ’11%’ AND “BCODE” LIKE ’11%’)
34 – access(“BCODE” LIKE ’07%’)
filter(“BCODE” LIKE ’07%’ AND “BCODE” LIKE ’07%’)
37 – access(“BCODE” LIKE ’04%’)
filter(“BCODE” LIKE ’04%’ AND “BCODE” LIKE ’04%’)
40 – access(“BCODE” LIKE ’01%’)
filter(“BCODE” LIKE ’01%’ AND “BCODE” LIKE ’01%’)
43 – access(“BCODE” LIKE ’90%’)
filter(“BCODE” LIKE ’90%’ AND “BCODE” LIKE ’90%’)
46 – access(“BCODE” LIKE ’91%’)
filter(“BCODE” LIKE ’91%’ AND “BCODE” LIKE ’91%’)

Statistics
———————————————————-
1  recursive calls
0  db block gets
92614  consistent gets
92574  physical reads
0  redo size
2269387  bytes sent via SQL*Net to client
12063  bytes received via SQL*Net from client
1056  SQL*Net roundtrips to/from client
15  sorts (memory)
0  sorts (disk)
15815  rows processed

建立一个单列索引:

SQL> create index idx_contracttbl_predate on CONTRACTTBL(PREDATE) tablespace erp_index;

Index created.

Elapsed: 00:00:13.51
SQL> alter session set events = ‘immediate trace name flush_cache’;

Session altered.

Elapsed: 00:00:00.14
SQL> SELECT IHCODE, HCODE, ETRDCODE, HSTATUS, SUBSTR(BCODE, 1, 5) AS BCODE1, BCODE,
2     RMB, CCODE, GCODELIST, QTYLIST, QTYUNITLIST, BUSIMODE1, BUSIMODE2,
3    INOUTMODE, ECODE, VPREPARE, EDITION, predate,’*', BCODE, HSTATUS, HTYPE,
4    BUSIMODE1, INOUTMODE, ECODE,CONTRACTTBL.predate
5  FROM
6   CONTRACTTBL WHERE  SBCODE <> ’200994′ AND  (IHCODE LIKE ‘________________’
7    OR  IHCODE LIKE ‘________________xx’)    AND (BCODE LIKE ’01%’ OR BCODE
8    LIKE ’02%’ OR BCODE LIKE ’03%’ OR BCODE LIKE ’04%’ OR BCODE LIKE ’05%’ OR
9    BCODE LIKE ’06%’ OR BCODE LIKE ’07%’ OR BCODE LIKE ’08%’ OR BCODE LIKE
10    ’09%’ OR BCODE LIKE ’10%’ OR BCODE LIKE ’11%’ OR BCODE LIKE ’90%’ OR BCODE
11    LIKE ’91%’ OR BCODE LIKE ’01%’ OR BCODE LIKE ’02%’ OR BCODE LIKE ’03%’ OR
12    BCODE LIKE ’04%’ OR BCODE LIKE ’05%’ OR BCODE LIKE ’06%’ OR BCODE LIKE
13    ’07%’ OR BCODE LIKE ’08%’ OR BCODE LIKE ’09%’ OR BCODE LIKE ’10%’ OR BCODE
14    LIKE ’11%’ OR BCODE LIKE ’90%’ OR BCODE LIKE ’91%’ OR BCODE LIKE ’02%’ OR
15    BCODE LIKE ’03%’ OR BCODE LIKE ’05%’ OR BCODE LIKE ’07%’ OR BCODE =
16    ’01100040049′) AND HTYPE =’190301′ AND INOUTMODE =’01′ AND PREDATE>=
17    TO_DATE(’2009-01-01′, ‘yyyy-mm-dd’) ORDER BY IHCODE DESC
18  /

15815 rows selected.

Elapsed: 00:00:14.21

Execution Plan
———————————————————-
Plan hash value: 567454617

—————————————————————————————————————————
| Id  | Operation                          | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
—————————————————————————————————————————
|   0 | SELECT STATEMENT                   |                              |     2 |   280 |       |  2277   (2)| 00:00:28 |
|   1 |  SORT ORDER BY                     |                              |     2 |   280 |       |  2277   (2)| 00:00:28 |
|*  2 |   TABLE ACCESS BY INDEX ROWID      | CONTRACTTBL                  |     2 |   280 |       |  2276   (2)| 00:00:28 |
|   3 |    BITMAP CONVERSION TO ROWIDS     |                              |       |       |       |            |          |
|   4 |     BITMAP AND                     |                              |       |       |       |            |          |
|   5 |      BITMAP OR                     |                              |       |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|   7 |        SORT ORDER BY               |                              |       |       |       |            |          |
|*  8 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  10 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 11 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  12 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  13 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 14 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  15 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  16 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 17 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  18 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  19 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 20 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  21 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  22 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 23 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  24 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  25 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 26 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  27 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  28 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 29 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  30 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  31 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 32 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  33 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  34 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 35 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  36 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  37 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 38 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  39 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  40 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 41 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  42 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  43 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 44 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  45 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |       |            |          |
|  46 |        SORT ORDER BY               |                              |       |       |       |            |          |
|* 47 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |       |       |       |     5   (0)| 00:00:01 |
|  48 |      BITMAP CONVERSION FROM ROWIDS |                              |       |       |       |            |          |
|  49 |       SORT ORDER BY                |                              |       |       |  5240K|            |          |
|* 50 |        INDEX RANGE SCAN            | IDX_CONTRACTTBL_PREDATE      |       |       |       |   448   (1)| 00:00:06 |
—————————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“HTYPE”=’190301′ AND “INOUTMODE”=’01′ AND “SBCODE”<>’200994′ AND (“IHCODE” LIKE ‘________________’
OR “IHCODE” LIKE ‘________________xx’))
8 – access(“BCODE”=’01100040049′)
filter(“BCODE”=’01100040049′)
11 – access(“BCODE” LIKE ’03%’)
filter(“BCODE” LIKE ’03%’ AND “BCODE” LIKE ’03%’)
14 – access(“BCODE” LIKE ’06%’)
filter(“BCODE” LIKE ’06%’ AND “BCODE” LIKE ’06%’)
17 – access(“BCODE” LIKE ’09%’)
filter(“BCODE” LIKE ’09%’ AND “BCODE” LIKE ’09%’)
20 – access(“BCODE” LIKE ’02%’)
filter(“BCODE” LIKE ’02%’ AND “BCODE” LIKE ’02%’)
23 – access(“BCODE” LIKE ’10%’)
filter(“BCODE” LIKE ’10%’ AND “BCODE” LIKE ’10%’)
26 – access(“BCODE” LIKE ’08%’)
filter(“BCODE” LIKE ’08%’ AND “BCODE” LIKE ’08%’)
29 – access(“BCODE” LIKE ’05%’)
filter(“BCODE” LIKE ’05%’ AND “BCODE” LIKE ’05%’)
32 – access(“BCODE” LIKE ’11%’)
filter(“BCODE” LIKE ’11%’ AND “BCODE” LIKE ’11%’)
35 – access(“BCODE” LIKE ’07%’)
filter(“BCODE” LIKE ’07%’ AND “BCODE” LIKE ’07%’)
38 – access(“BCODE” LIKE ’04%’)
filter(“BCODE” LIKE ’04%’ AND “BCODE” LIKE ’04%’)
41 – access(“BCODE” LIKE ’01%’)
filter(“BCODE” LIKE ’01%’ AND “BCODE” LIKE ’01%’)
44 – access(“BCODE” LIKE ’90%’)
filter(“BCODE” LIKE ’90%’ AND “BCODE” LIKE ’90%’)
47 – access(“BCODE” LIKE ’91%’)
filter(“BCODE” LIKE ’91%’ AND “BCODE” LIKE ’91%’)
50 – access(“PREDATE”>=TO_DATE(’2009-01-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’))
filter(“PREDATE”>=TO_DATE(’2009-01-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’))

Statistics
———————————————————-
1  recursive calls
0  db block gets
25260  consistent gets
25225  physical reads
0  redo size
2269387  bytes sent via SQL*Net to client
12063  bytes received via SQL*Net from client
1056  SQL*Net roundtrips to/from client
16  sorts (memory)
0  sorts (disk)
15815  rows processed

建立复合索引:

SQL> drop index idx_contracttbl_predate
2  /

Index dropped.

Elapsed: 00:00:00.83
SQL>
SQL> create index idx_contracttbl_htype_inout_predate on CONTRACTTBL(HTYPE, INOUTMODE,PREDATE) tablespace erp_index;
create index idx_contracttbl_htype_inout_predate on CONTRACTTBL(HTYPE, INOUTMODE,PREDATE) tablespace erp_index
*
ERROR at line 1:
ORA-00972: identifier is too long

Elapsed: 00:00:00.00
SQL> create index idx_contract_htiopre on CONTRACTTBL(HTYPE, INOUTMODE,PREDATE) tablespace erp_index;

Index created.

Elapsed: 00:00:13.32
SQL> alter session set events = ‘immediate trace name flush_cache’;

Session altered.

Elapsed: 00:00:38.90
SQL>
SQL> SELECT IHCODE, HCODE, ETRDCODE, HSTATUS, SUBSTR(BCODE, 1, 5) AS BCODE1, BCODE,
2     RMB, CCODE, GCODELIST, QTYLIST, QTYUNITLIST, BUSIMODE1, BUSIMODE2,
3    INOUTMODE, ECODE, VPREPARE, EDITION, predate,’*', BCODE, HSTATUS, HTYPE,
4    BUSIMODE1, INOUTMODE, ECODE,CONTRACTTBL.predate
5  FROM
6   CONTRACTTBL WHERE  SBCODE <> ’200994′ AND  (IHCODE LIKE ‘________________’
7    OR  IHCODE LIKE ‘________________xx’)    AND (BCODE LIKE ’01%’ OR BCODE
8    LIKE ’02%’ OR BCODE LIKE ’03%’ OR BCODE LIKE ’04%’ OR BCODE LIKE ’05%’ OR
9    BCODE LIKE ’06%’ OR BCODE LIKE ’07%’ OR BCODE LIKE ’08%’ OR BCODE LIKE
10    ’09%’ OR BCODE LIKE ’10%’ OR BCODE LIKE ’11%’ OR BCODE LIKE ’90%’ OR BCODE
11    LIKE ’91%’ OR BCODE LIKE ’01%’ OR BCODE LIKE ’02%’ OR BCODE LIKE ’03%’ OR
12    BCODE LIKE ’04%’ OR BCODE LIKE ’05%’ OR BCODE LIKE ’06%’ OR BCODE LIKE
13    ’07%’ OR BCODE LIKE ’08%’ OR BCODE LIKE ’09%’ OR BCODE LIKE ’10%’ OR BCODE
14    LIKE ’11%’ OR BCODE LIKE ’90%’ OR BCODE LIKE ’91%’ OR BCODE LIKE ’02%’ OR
15    BCODE LIKE ’03%’ OR BCODE LIKE ’05%’ OR BCODE LIKE ’07%’ OR BCODE =
16    ’01100040049′) AND HTYPE =’190301′ AND INOUTMODE =’01′ AND PREDATE>=
17    TO_DATE(’2009-01-01′, ‘yyyy-mm-dd’) ORDER BY IHCODE DESC
18  /

15815 rows selected.

Elapsed: 00:00:05.90

Execution Plan
———————————————————-
Plan hash value: 2443244798

——————————————————————————————————————-
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————-
|   0 | SELECT STATEMENT                   |                              |     2 |   280 |   117  (15)| 00:00:02 |
|   1 |  SORT ORDER BY                     |                              |     2 |   280 |   117  (15)| 00:00:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID      | CONTRACTTBL                  |     2 |   280 |   116  (14)| 00:00:02 |
|   3 |    BITMAP CONVERSION TO ROWIDS     |                              |       |       |            |          |
|   4 |     BITMAP AND                     |                              |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS |                              |       |       |            |          |
|   6 |       SORT ORDER BY                |                              |       |       |            |          |
|*  7 |        INDEX RANGE SCAN            | IDX_CONTRACT_HTIOPRE         |  3294 |       |    16   (0)| 00:00:01 |
|   8 |      BITMAP OR                     |                              |       |       |            |          |
|   9 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  10 |        SORT ORDER BY               |                              |       |       |            |          |
|* 11 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  12 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  13 |        SORT ORDER BY               |                              |       |       |            |          |
|* 14 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  15 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  16 |        SORT ORDER BY               |                              |       |       |            |          |
|* 17 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  18 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  19 |        SORT ORDER BY               |                              |       |       |            |          |
|* 20 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  21 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  22 |        SORT ORDER BY               |                              |       |       |            |          |
|* 23 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  24 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  25 |        SORT ORDER BY               |                              |       |       |            |          |
|* 26 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  27 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  28 |        SORT ORDER BY               |                              |       |       |            |          |
|* 29 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  30 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  31 |        SORT ORDER BY               |                              |       |       |            |          |
|* 32 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  33 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  34 |        SORT ORDER BY               |                              |       |       |            |          |
|* 35 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  36 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  37 |        SORT ORDER BY               |                              |       |       |            |          |
|* 38 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  39 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  40 |        SORT ORDER BY               |                              |       |       |            |          |
|* 41 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  42 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  43 |        SORT ORDER BY               |                              |       |       |            |          |
|* 44 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  45 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  46 |        SORT ORDER BY               |                              |       |       |            |          |
|* 47 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
|  48 |       BITMAP CONVERSION FROM ROWIDS|                              |       |       |            |          |
|  49 |        SORT ORDER BY               |                              |       |       |            |          |
|* 50 |         INDEX RANGE SCAN           | CONTRACTTBL_BCODE_RATIFYDATE |  3294 |       |     5   (0)| 00:00:01 |
——————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“SBCODE”<>’200994′ AND (“IHCODE” LIKE ‘________________’ OR “IHCODE” LIKE
‘________________xx’))
7 – access(“HTYPE”=’190301′ AND “INOUTMODE”=’01′ AND “PREDATE”>=TO_DATE(’2009-01-01 00:00:00′,
‘yyyy-mm-dd hh24:mi:ss’))
filter(“PREDATE”>=TO_DATE(’2009-01-01 00:00:00′, ‘yyyy-mm-dd hh24:mi:ss’) AND “INOUTMODE”=’01′ AND
“HTYPE”=’190301′)
11 – access(“BCODE”=’01100040049′)
filter(“BCODE”=’01100040049′)
14 – access(“BCODE” LIKE ’03%’)
filter(“BCODE” LIKE ’03%’ AND “BCODE” LIKE ’03%’)
17 – access(“BCODE” LIKE ’06%’)
filter(“BCODE” LIKE ’06%’ AND “BCODE” LIKE ’06%’)
20 – access(“BCODE” LIKE ’09%’)
filter(“BCODE” LIKE ’09%’ AND “BCODE” LIKE ’09%’)
23 – access(“BCODE” LIKE ’02%’)
filter(“BCODE” LIKE ’02%’ AND “BCODE” LIKE ’02%’)
26 – access(“BCODE” LIKE ’10%’)
filter(“BCODE” LIKE ’10%’ AND “BCODE” LIKE ’10%’)
29 – access(“BCODE” LIKE ’08%’)
filter(“BCODE” LIKE ’08%’ AND “BCODE” LIKE ’08%’)
32 – access(“BCODE” LIKE ’05%’)
filter(“BCODE” LIKE ’05%’ AND “BCODE” LIKE ’05%’)
35 – access(“BCODE” LIKE ’11%’)
filter(“BCODE” LIKE ’11%’ AND “BCODE” LIKE ’11%’)
38 – access(“BCODE” LIKE ’07%’)
filter(“BCODE” LIKE ’07%’ AND “BCODE” LIKE ’07%’)
41 – access(“BCODE” LIKE ’04%’)
filter(“BCODE” LIKE ’04%’ AND “BCODE” LIKE ’04%’)
44 – access(“BCODE” LIKE ’01%’)
filter(“BCODE” LIKE ’01%’ AND “BCODE” LIKE ’01%’)
47 – access(“BCODE” LIKE ’90%’)
filter(“BCODE” LIKE ’90%’ AND “BCODE” LIKE ’90%’)
50 – access(“BCODE” LIKE ’91%’)
filter(“BCODE” LIKE ’91%’ AND “BCODE” LIKE ’91%’)

Statistics
———————————————————-
1  recursive calls
0  db block gets
15474  consistent gets
15439  physical reads
0  redo size
2269387  bytes sent via SQL*Net to client
12063  bytes received via SQL*Net from client
1056  SQL*Net roundtrips to/from client
16  sorts (memory)
0  sorts (disk)
15815  rows processed

除了改写明显有问题的SQL语句,针对索引的优化是提高查询性能的最快最简单的办法。

不可挽回的数据损失——警惕可怕的应用程序错误

最近接触到的一个系统,出现了莫名其妙的数据丢失,怀疑是应用程序的问题,可是如何定位出问题的程序位置很难,最后根据大概的数据丢失时间,结合Logminer工具找出了有问题的程序,下面记录定位程序错误的过程:

一、根据大概的时间定位需要哪些归档文件

ERPDB1@/orabak>rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Tue Apr 6 09:26:15 2010

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

connected to target database: CNDERPDB (DBID=2400249746)

RMAN> list backup of archivelog time between “to_date(’2010-04-01 22:00:00′,’yyyy-mm-dd hh24:mi:ss’)” and “to_date(’2010-04-02 23:00′,’yyyy-mm-dd hh24:mi:ss’)”;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18012   75.15M     DISK        00:00:22     02-APR-10
BP Key: 21234   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T130015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715265583_18079_1

List of Archived Logs in backup set 18012
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    130816  12413552772 01-APR-10 12413640444 01-APR-10
1    130817  12413640444 01-APR-10 12413642594 01-APR-10
1    130818  12413642594 01-APR-10 12413649352 01-APR-10
1    130819  12413649352 01-APR-10 12413650311 01-APR-10
1    130820  12413650311 01-APR-10 12413651164 01-APR-10
1    130821  12413651164 01-APR-10 12413651934 01-APR-10
1    130822  12413651934 01-APR-10 12413658859 01-APR-10
1    130823  12413658859 01-APR-10 12413662103 01-APR-10
1    130824  12413662103 01-APR-10 12413665559 01-APR-10
1    130825  12413665559 01-APR-10 12413668607 01-APR-10
1    130826  12413668607 01-APR-10 12413671510 01-APR-10
1    130827  12413671510 01-APR-10 12413674568 01-APR-10
1    130828  12413674568 01-APR-10 12413679025 02-APR-10
1    130829  12413679025 02-APR-10 12413681948 02-APR-10
1    130830  12413681948 02-APR-10 12413682794 02-APR-10
1    130831  12413682794 02-APR-10 12413683591 02-APR-10
1    130832  12413683591 02-APR-10 12413699490 02-APR-10
1    130833  12413699490 02-APR-10 12413700261 02-APR-10
1    130834  12413700261 02-APR-10 12413703183 02-APR-10
1    130835  12413703183 02-APR-10 12413704994 02-APR-10
1    130836  12413704994 02-APR-10 12413706561 02-APR-10
1    130837  12413706561 02-APR-10 12413708150 02-APR-10
1    130838  12413708150 02-APR-10 12413719850 02-APR-10
1    130839  12413719850 02-APR-10 12413739975 02-APR-10
1    130840  12413739975 02-APR-10 12413765112 02-APR-10
1    130841  12413765112 02-APR-10 12413792162 02-APR-10
1    130842  12413792162 02-APR-10 12413822087 02-APR-10
1    130843  12413822087 02-APR-10 12413876089 02-APR-10
1    130844  12413876089 02-APR-10 12413958554 02-APR-10
1    130845  12413958554 02-APR-10 12414035599 02-APR-10
1    130846  12414035599 02-APR-10 12414123518 02-APR-10
1    130847  12414123518 02-APR-10 12414165097 02-APR-10
1    130848  12414165097 02-APR-10 12414200120 02-APR-10
1    130849  12414200120 02-APR-10 12414234183 02-APR-10
1    130850  12414234183 02-APR-10 12414266300 02-APR-10
1    130851  12414266300 02-APR-10 12414367031 02-APR-10
1    130852  12414367031 02-APR-10 12414450980 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18013   28.81M     DISK        00:00:08     02-APR-10
BP Key: 21235   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T130015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715265609_18081_1

List of Archived Logs in backup set 18013
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    130853  12414450980 02-APR-10 12414552623 02-APR-10
1    130854  12414552623 02-APR-10 12414605521 02-APR-10
1    130855  12414605521 02-APR-10 12414688546 02-APR-10
1    130856  12414688546 02-APR-10 12414768204 02-APR-10
1    130857  12414768204 02-APR-10 12414850576 02-APR-10
1    130858  12414850576 02-APR-10 12415578138 02-APR-10
1    130859  12415578138 02-APR-10 12416563016 02-APR-10
1    130860  12416563016 02-APR-10 12417650008 02-APR-10
1    130861  12417650008 02-APR-10 12418713043 02-APR-10
1    130862  12418713043 02-APR-10 12419850520 02-APR-10
1    130863  12419850520 02-APR-10 12420094921 02-APR-10
1    130864  12420094921 02-APR-10 12420151035 02-APR-10
1    130865  12420151035 02-APR-10 12420215791 02-APR-10
1    130866  12420215791 02-APR-10 12420240304 02-APR-10
1    130867  12420240304 02-APR-10 12420251373 02-APR-10
1    130868  12420251373 02-APR-10 12420258130 02-APR-10
1    130869  12420258130 02-APR-10 12420263794 02-APR-10
1    130870  12420263794 02-APR-10 12420268456 02-APR-10
1    130871  12420268456 02-APR-10 12420270403 02-APR-10
1    130872  12420270403 02-APR-10 12420271525 02-APR-10
1    130873  12420271525 02-APR-10 12420272893 02-APR-10
1    130874  12420272893 02-APR-10 12420274107 02-APR-10
1    130875  12420274107 02-APR-10 12420275039 02-APR-10
1    130876  12420275039 02-APR-10 12420275961 02-APR-10
1    130877  12420275961 02-APR-10 12420283221 02-APR-10
1    130878  12420283221 02-APR-10 12420289525 02-APR-10
1    130879  12420289525 02-APR-10 12420308743 02-APR-10
1    130880  12420308743 02-APR-10 12420353689 02-APR-10
1    130881  12420353689 02-APR-10 12420422037 02-APR-10
1    130882  12420422037 02-APR-10 12420538802 02-APR-10
1    130883  12420538802 02-APR-10 12420674578 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18014   1.35G      DISK        00:05:34     02-APR-10
BP Key: 21236   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T130015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715266018_18080_1

List of Archived Logs in backup set 18014
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
2    139146  12413544512 01-APR-10 12413551394 01-APR-10
2    139147  12413551394 01-APR-10 12413640367 01-APR-10
2    139148  12413640367 01-APR-10 12413642584 01-APR-10
2    139149  12413642584 01-APR-10 12413649329 01-APR-10
2    139150  12413649329 01-APR-10 12413650295 01-APR-10
2    139151  12413650295 01-APR-10 12413651154 01-APR-10
2    139152  12413651154 01-APR-10 12413651918 01-APR-10
2    139153  12413651918 01-APR-10 12413658803 01-APR-10
2    139154  12413658803 01-APR-10 12413662056 01-APR-10
2    139155  12413662056 01-APR-10 12413665502 01-APR-10
2    139156  12413665502 01-APR-10 12413668540 01-APR-10
2    139157  12413668540 01-APR-10 12413671451 01-APR-10
2    139158  12413671451 01-APR-10 12413674508 01-APR-10
2    139159  12413674508 01-APR-10 12413678971 02-APR-10
2    139160  12413678971 02-APR-10 12413681936 02-APR-10
2    139161  12413681936 02-APR-10 12413682783 02-APR-10
2    139162  12413682783 02-APR-10 12413683580 02-APR-10
2    139163  12413683580 02-APR-10 12413699494 02-APR-10
2    139164  12413699494 02-APR-10 12413700259 02-APR-10
2    139165  12413700259 02-APR-10 12413703174 02-APR-10
2    139166  12413703174 02-APR-10 12413704987 02-APR-10
2    139167  12413704987 02-APR-10 12413706566 02-APR-10
2    139168  12413706566 02-APR-10 12413708145 02-APR-10
2    139169  12413708145 02-APR-10 12413719931 02-APR-10
2    139170  12413719931 02-APR-10 12413739973 02-APR-10
2    139171  12413739973 02-APR-10 12413765108 02-APR-10
2    139172  12413765108 02-APR-10 12413792160 02-APR-10
2    139173  12413792160 02-APR-10 12413822085 02-APR-10
2    139174  12413822085 02-APR-10 12413839475 02-APR-10
2    139175  12413839475 02-APR-10 12413916931 02-APR-10
2    139176  12413916931 02-APR-10 12413996350 02-APR-10
2    139177  12413996350 02-APR-10 12414058643 02-APR-10
2    139178  12414058643 02-APR-10 12414132707 02-APR-10
2    139179  12414132707 02-APR-10 12414171482 02-APR-10
2    139180  12414171482 02-APR-10 12414204122 02-APR-10
2    139181  12414204122 02-APR-10 12414237819 02-APR-10
2    139182  12414237819 02-APR-10 12414268323 02-APR-10
2    139183  12414268323 02-APR-10 12414338270 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18015   1.08G      DISK        00:04:48     02-APR-10
BP Key: 21237   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T130015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715266355_18082_1

List of Archived Logs in backup set 18015
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
2    139193  12415576435 02-APR-10 12415918972 02-APR-10
2    139194  12415918972 02-APR-10 12416263996 02-APR-10
2    139195  12416263996 02-APR-10 12416562635 02-APR-10
2    139196  12416562635 02-APR-10 12416910144 02-APR-10
2    139197  12416910144 02-APR-10 12417269808 02-APR-10
2    139198  12417269808 02-APR-10 12417649399 02-APR-10
2    139199  12417649399 02-APR-10 12417949379 02-APR-10
2    139200  12417949379 02-APR-10 12418319357 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18016   1.18G      DISK        00:05:01     02-APR-10
BP Key: 21238   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T130015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715266651_18083_1

List of Archived Logs in backup set 18016
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
2    139184  12414338270 02-APR-10 12414423130 02-APR-10
2    139185  12414423130 02-APR-10 12414500563 02-APR-10
2    139186  12414500563 02-APR-10 12414570455 02-APR-10
2    139187  12414570455 02-APR-10 12414642681 02-APR-10
2    139188  12414642681 02-APR-10 12414698647 02-APR-10
2    139189  12414698647 02-APR-10 12414771473 02-APR-10
2    139190  12414771473 02-APR-10 12414850611 02-APR-10
2    139191  12414850611 02-APR-10 12415217365 02-APR-10
2    139192  12415217365 02-APR-10 12415576435 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18017   888.53M    DISK        00:03:52     02-APR-10
BP Key: 21239   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T130015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715266957_18084_1

List of Archived Logs in backup set 18017
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
2    139201  12418319357 02-APR-10 12418704868 02-APR-10
2    139202  12418704868 02-APR-10 12419088247 02-APR-10
2    139203  12419088247 02-APR-10 12419463210 02-APR-10
2    139204  12419463210 02-APR-10 12419844874 02-APR-10
2    139205  12419844874 02-APR-10 12420094882 02-APR-10
2    139206  12420094882 02-APR-10 12420150868 02-APR-10
2    139207  12420150868 02-APR-10 12420214323 02-APR-10
2    139208  12420214323 02-APR-10 12420238252 02-APR-10
2    139209  12420238252 02-APR-10 12420250587 02-APR-10
2    139210  12420250587 02-APR-10 12420257180 02-APR-10
2    139211  12420257180 02-APR-10 12420263066 02-APR-10
2    139212  12420263066 02-APR-10 12420268384 02-APR-10
2    139213  12420268384 02-APR-10 12420270333 02-APR-10
2    139214  12420270333 02-APR-10 12420271382 02-APR-10
2    139215  12420271382 02-APR-10 12420272765 02-APR-10
2    139216  12420272765 02-APR-10 12420273996 02-APR-10
2    139217  12420273996 02-APR-10 12420274953 02-APR-10
2    139218  12420274953 02-APR-10 12420275837 02-APR-10
2    139219  12420275837 02-APR-10 12420283017 02-APR-10
2    139220  12420283017 02-APR-10 12420289397 02-APR-10
2    139221  12420289397 02-APR-10 12420308029 02-APR-10
2    139222  12420308029 02-APR-10 12420352897 02-APR-10
2    139223  12420352897 02-APR-10 12420409200 02-APR-10
2    139224  12420409200 02-APR-10 12420536370 02-APR-10
2    139225  12420536370 02-APR-10 12420673365 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18019   368.43M    DISK        00:01:33     02-APR-10
BP Key: 21241   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T213015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715296617_18087_1

List of Archived Logs in backup set 18019
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
2    139226  12420673365 02-APR-10 12420743967 02-APR-10
2    139227  12420743967 02-APR-10 12420869457 02-APR-10
2    139228  12420869457 02-APR-10 12421033197 02-APR-10
2    139229  12421033197 02-APR-10 12421227660 02-APR-10
2    139230  12421227660 02-APR-10 12421333583 02-APR-10
2    139231  12421333583 02-APR-10 12421496532 02-APR-10
2    139232  12421496532 02-APR-10 12421613786 02-APR-10
2    139233  12421613786 02-APR-10 12421745380 02-APR-10
2    139234  12421745380 02-APR-10 12421894114 02-APR-10
2    139235  12421894114 02-APR-10 12421998582 02-APR-10
2    139236  12421998582 02-APR-10 12422163198 02-APR-10
2    139237  12422163198 02-APR-10 12422276738 02-APR-10
2    139238  12422276738 02-APR-10 12422408879 02-APR-10
2    139239  12422408879 02-APR-10 12422531754 02-APR-10
2    139240  12422531754 02-APR-10 12422630287 02-APR-10
2    139241  12422630287 02-APR-10 12422733683 02-APR-10
2    139242  12422733683 02-APR-10 12422830935 02-APR-10
2    139243  12422830935 02-APR-10 12422841318 02-APR-10
2    139244  12422841318 02-APR-10 12422845794 02-APR-10
2    139245  12422845794 02-APR-10 12422848359 02-APR-10
2    139246  12422848359 02-APR-10 12422852191 02-APR-10
2    139247  12422852191 02-APR-10 12422855696 02-APR-10
2    139248  12422855696 02-APR-10 12422860249 02-APR-10
2    139249  12422860249 02-APR-10 12422861531 02-APR-10
2    139250  12422861531 02-APR-10 12422868202 02-APR-10
2    139251  12422868202 02-APR-10 12422875058 02-APR-10
2    139252  12422875058 02-APR-10 12422892330 02-APR-10
2    139253  12422892330 02-APR-10 12422906852 02-APR-10
2    139254  12422906852 02-APR-10 12422922052 02-APR-10
2    139255  12422922052 02-APR-10 12423017940 02-APR-10
2    139256  12423017940 02-APR-10 12423167539 02-APR-10
2    139257  12423167539 02-APR-10 12423419386 02-APR-10
2    139258  12423419386 02-APR-10 12423572891 02-APR-10
2    139259  12423572891 02-APR-10 12423679063 02-APR-10
2    139260  12423679063 02-APR-10 12423804204 02-APR-10
2    139261  12423804204 02-APR-10 12423965935 02-APR-10
2    139262  12423965935 02-APR-10 12424067553 02-APR-10
2    139263  12424067553 02-APR-10 12424181177 02-APR-10
2    139264  12424181177 02-APR-10 12424276388 02-APR-10
2    139265  12424276388 02-APR-10 12424460831 02-APR-10
2    139266  12424460831 02-APR-10 12424607422 02-APR-10
2    139267  12424607422 02-APR-10 12424743466 02-APR-10
2    139268  12424743466 02-APR-10 12424914672 02-APR-10
2    139269  12424914672 02-APR-10 12425085812 02-APR-10
2    139270  12425085812 02-APR-10 12425218972 02-APR-10
2    139271  12425218972 02-APR-10 12425344844 02-APR-10
2    139272  12425344844 02-APR-10 12425480047 02-APR-10
2    139273  12425480047 02-APR-10 12425643050 02-APR-10
2    139274  12425643050 02-APR-10 12425758729 02-APR-10
2    139275  12425758729 02-APR-10 12425909760 02-APR-10
2    139276  12425909760 02-APR-10 12426102834 02-APR-10
2    139277  12426102834 02-APR-10 12426236664 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18020   421.06M    DISK        00:01:44     02-APR-10
BP Key: 21242   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T213015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715296183_18086_1

List of Archived Logs in backup set 18020
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    130884  12420674578 02-APR-10 12420751514 02-APR-10
1    130885  12420751514 02-APR-10 12420869668 02-APR-10
1    130886  12420869668 02-APR-10 12421033097 02-APR-10
1    130887  12421033097 02-APR-10 12421227343 02-APR-10
1    130888  12421227343 02-APR-10 12421332908 02-APR-10
1    130889  12421332908 02-APR-10 12421485220 02-APR-10
1    130890  12421485220 02-APR-10 12421612693 02-APR-10
1    130891  12421612693 02-APR-10 12421729960 02-APR-10
1    130892  12421729960 02-APR-10 12421883068 02-APR-10
1    130893  12421883068 02-APR-10 12421996639 02-APR-10
1    130894  12421996639 02-APR-10 12422142061 02-APR-10
1    130895  12422142061 02-APR-10 12422259990 02-APR-10
1    130896  12422259990 02-APR-10 12422380732 02-APR-10
1    130897  12422380732 02-APR-10 12422512955 02-APR-10
1    130898  12422512955 02-APR-10 12422622031 02-APR-10
1    130899  12422622031 02-APR-10 12422731142 02-APR-10
1    130900  12422731142 02-APR-10 12422829112 02-APR-10
1    130901  12422829112 02-APR-10 12422840605 02-APR-10
1    130902  12422840605 02-APR-10 12422845469 02-APR-10
1    130903  12422845469 02-APR-10 12422847714 02-APR-10
1    130904  12422847714 02-APR-10 12422851559 02-APR-10
1    130905  12422851559 02-APR-10 12422854834 02-APR-10
1    130906  12422854834 02-APR-10 12422859713 02-APR-10
1    130907  12422859713 02-APR-10 12422861547 02-APR-10
1    130908  12422861547 02-APR-10 12422868247 02-APR-10
1    130909  12422868247 02-APR-10 12422875082 02-APR-10
1    130910  12422875082 02-APR-10 12422892562 02-APR-10
1    130911  12422892562 02-APR-10 12422906910 02-APR-10
1    130912  12422906910 02-APR-10 12422922078 02-APR-10
1    130913  12422922078 02-APR-10 12423017956 02-APR-10
1    130914  12423017956 02-APR-10 12423167377 02-APR-10
1    130915  12423167377 02-APR-10 12423419566 02-APR-10
1    130916  12423419566 02-APR-10 12423572829 02-APR-10
1    130917  12423572829 02-APR-10 12423677866 02-APR-10
1    130918  12423677866 02-APR-10 12423798772 02-APR-10
1    130919  12423798772 02-APR-10 12423964636 02-APR-10
1    130920  12423964636 02-APR-10 12424056421 02-APR-10
1    130921  12424056421 02-APR-10 12424169818 02-APR-10
1    130922  12424169818 02-APR-10 12424259646 02-APR-10
1    130923  12424259646 02-APR-10 12424456980 02-APR-10
1    130924  12424456980 02-APR-10 12424577229 02-APR-10
1    130925  12424577229 02-APR-10 12424729139 02-APR-10
1    130926  12424729139 02-APR-10 12424892515 02-APR-10
1    130927  12424892515 02-APR-10 12425058920 02-APR-10
1    130928  12425058920 02-APR-10 12425183453 02-APR-10
1    130929  12425183453 02-APR-10 12425302024 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18021   104.97M    DISK        00:00:25     02-APR-10
BP Key: 21243   Status: AVAILABLE  Compressed: YES  Tag: TAG20100402T213015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715296294_18088_1

List of Archived Logs in backup set 18021
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    130930  12425302024 02-APR-10 12425464067 02-APR-10
1    130931  12425464067 02-APR-10 12425612482 02-APR-10
1    130932  12425612482 02-APR-10 12425741312 02-APR-10
1    130933  12425741312 02-APR-10 12425903002 02-APR-10
1    130934  12425903002 02-APR-10 12426033120 02-APR-10
1    130935  12426033120 02-APR-10 12426231614 02-APR-10
1    130936  12426231614 02-APR-10 12426258334 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18034   66.82M     DISK        00:00:20     03-APR-10
BP Key: 21292   Status: AVAILABLE  Compressed: YES  Tag: TAG20100403T130014
Piece Name: /orabak/arch/CNDERPDB_arch_20100403_715351984_18101_1

List of Archived Logs in backup set 18034
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
1    130937  12426258334 02-APR-10 12426281568 02-APR-10
1    130938  12426281568 02-APR-10 12426378051 02-APR-10
1    130939  12426378051 02-APR-10 12426497713 02-APR-10
1    130940  12426497713 02-APR-10 12426508246 02-APR-10
1    130941  12426508246 02-APR-10 12426518759 02-APR-10
1    130942  12426518759 02-APR-10 12426561156 02-APR-10
1    130943  12426561156 02-APR-10 12426575448 02-APR-10
1    130944  12426575448 02-APR-10 12426581961 02-APR-10
1    130945  12426581961 02-APR-10 12426604391 02-APR-10
1    130946  12426604391 02-APR-10 12426607860 02-APR-10
1    130947  12426607860 02-APR-10 12426611851 02-APR-10
1    130948  12426611851 02-APR-10 12426614176 02-APR-10
1    130949  12426614176 02-APR-10 12426625355 02-APR-10
1    130950  12426625355 02-APR-10 12426629997 02-APR-10
1    130951  12426629997 02-APR-10 12426632292 02-APR-10
1    130952  12426632292 02-APR-10 12426638597 02-APR-10
1    130953  12426638597 02-APR-10 12426645006 02-APR-10
1    130954  12426645006 02-APR-10 12426646673 02-APR-10
1    130955  12426646673 02-APR-10 12426648522 02-APR-10
1    130956  12426648522 02-APR-10 12426652032 02-APR-10
1    130957  12426652032 02-APR-10 12426667628 02-APR-10
1    130958  12426667628 02-APR-10 12426669877 02-APR-10
1    130959  12426669877 02-APR-10 12426670663 02-APR-10
1    130960  12426670663 02-APR-10 12426671857 02-APR-10
1    130961  12426671857 02-APR-10 12426673180 02-APR-10
1    130962  12426673180 02-APR-10 12426674259 02-APR-10
1    130963  12426674259 02-APR-10 12426719108 02-APR-10
1    130964  12426719108 02-APR-10 12426751352 02-APR-10
1    130965  12426751352 02-APR-10 12426752399 02-APR-10
1    130966  12426752399 02-APR-10 12426753211 02-APR-10
1    130967  12426753211 02-APR-10 12426754028 02-APR-10
1    130968  12426754028 02-APR-10 12426754879 02-APR-10
1    130969  12426754879 02-APR-10 12426761703 02-APR-10

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
18038   1.32G      DISK        00:05:25     03-APR-10
BP Key: 21296   Status: AVAILABLE  Compressed: YES  Tag: TAG20100403T130014
Piece Name: /orabak/arch/CNDERPDB_arch_20100403_715353019_18105_1

List of Archived Logs in backup set 18038
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
—- ——- ———- ——— ———- ———
2    139278  12426236664 02-APR-10 12426270726 02-APR-10
2    139279  12426270726 02-APR-10 12426285750 02-APR-10
2    139280  12426285750 02-APR-10 12426409113 02-APR-10
2    139281  12426409113 02-APR-10 12426501941 02-APR-10
2    139282  12426501941 02-APR-10 12426510767 02-APR-10
2    139283  12426510767 02-APR-10 12426521032 02-APR-10
2    139284  12426521032 02-APR-10 12426563858 02-APR-10
2    139285  12426563858 02-APR-10 12426576699 02-APR-10
2    139286  12426576699 02-APR-10 12426584470 02-APR-10
2    139287  12426584470 02-APR-10 12426605256 02-APR-10
2    139288  12426605256 02-APR-10 12426608982 02-APR-10
2    139289  12426608982 02-APR-10 12426612473 02-APR-10
2    139290  12426612473 02-APR-10 12426624363 02-APR-10
2    139291  12426624363 02-APR-10 12426625804 02-APR-10
2    139292  12426625804 02-APR-10 12426630539 02-APR-10
2    139293  12426630539 02-APR-10 12426632904 02-APR-10
2    139294  12426632904 02-APR-10 12426639011 02-APR-10
2    139295  12426639011 02-APR-10 12426645620 02-APR-10
2    139296  12426645620 02-APR-10 12426647268 02-APR-10
2    139297  12426647268 02-APR-10 12426649060 02-APR-10
2    139298  12426649060 02-APR-10 12426653216 02-APR-10
2    139299  12426653216 02-APR-10 12426668724 02-APR-10
2    139300  12426668724 02-APR-10 12426670578 02-APR-10
2    139301  12426670578 02-APR-10 12426670660 02-APR-10
2    139302  12426670660 02-APR-10 12426671859 02-APR-10
2    139303  12426671859 02-APR-10 12426673157 02-APR-10
2    139304  12426673157 02-APR-10 12426674262 02-APR-10
2    139305  12426674262 02-APR-10 12426719104 02-APR-10
2    139306  12426719104 02-APR-10 12426751344 02-APR-10
2    139307  12426751344 02-APR-10 12426752392 02-APR-10
2    139308  12426752392 02-APR-10 12426753203 02-APR-10
2    139309  12426753203 02-APR-10 12426754019 02-APR-10
2    139310  12426754019 02-APR-10 12426754870 02-APR-10
2    139311  12426754870 02-APR-10 12426761660 02-APR-10

RMAN>

RMAN> exit

Recovery Manager complete.

二、拷贝需要的rman备份片文件回来,放到默认的rman备份路径

ERPDB1@/orabak/arch>ls -l
total 14338968
-rw-r—–   1 oracle   oinstall   78802432 Apr 06 09:35 CNDERPDB_arch_20100402_715265583_18079_1
-rw-r—–   1 oracle   oinstall   30210560 Apr 06 09:36 CNDERPDB_arch_20100402_715265609_18081_1
-rw-r—–   1 oracle   oinstall 1447344128 Apr 06 09:52 CNDERPDB_arch_20100402_715266018_18080_1
-rw-r—–   1 oracle   oinstall 1154966528 Apr 06 09:51 CNDERPDB_arch_20100402_715266355_18082_1
-rw-r—–   1 oracle   oinstall 1264911360 Apr 06 09:51 CNDERPDB_arch_20100402_715266651_18083_1
-rw-r—–   1 oracle   oinstall  931689984 Apr 06 09:50 CNDERPDB_arch_20100402_715266957_18084_1
-rw-r—–   1 oracle   oinstall  441509888 Apr 06 09:41 CNDERPDB_arch_20100402_715296183_18086_1
-rw-r—–   1 oracle   oinstall  110066176 Apr 06 09:42 CNDERPDB_arch_20100402_715296294_18088_1
-rw-r—–   1 oracle   oinstall  386329600 Apr 06 09:53 CNDERPDB_arch_20100402_715296617_18087_1
-rw-r—–   1 oracle   oinstall   70063104 Apr 06 09:36 CNDERPDB_arch_20100403_715351984_18101_1
-rw-r—–   1 oracle   oinstall 1420035584 Apr 06 09:55 CNDERPDB_arch_20100403_715353019_18105_1

三、从Rman备份片中解析出归档日志文件

ERPDB1@/orabak/arch>rman target /

Recovery Manager: Release 10.2.0.3.0 – Production on Tue Apr 6 10:01:54 2010

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

connected to target database: CNDERPDB (DBID=2400249746)

RMAN> run {
2> set archivelog destination to ‘/orabak/testarch’;
3> SQL ‘ALTER SESSION SET NLS_DATE_FORMAT=”YYYY-MM-DD:HH24:MI:SS”‘;
restore archivelog time between ’2010-04-01 22:00:00′ and ’2010-04-02 23:00:00′;4>
5> }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

sql statement: ALTER SESSION SET NLS_DATE_FORMAT=”YYYY-MM-DD:HH24:MI:SS”

Starting restore at 06-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=676 instance=cnderpdb1 devtype=DISK

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130816
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130817
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130818
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130819
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130820
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130821
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130822
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130823
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130824
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130825
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130826
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130827
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130828
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130829
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130830
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130831
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130832
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130833
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130834
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130835
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130836
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130837
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130838
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130839
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130840
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130841
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130842
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130843
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130844
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130845
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130846
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130847
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130848
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130849
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130850
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130851
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130852
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715265583_18079_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715265583_18079_1 tag=TAG20100402T130015
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130853
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130854
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130855
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130856
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130857
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130858
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130859
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130860
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130861
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130862
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130863
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130864
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130865
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130866
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130867
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130868
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130869
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130870
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130871
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130872
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130873
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130874
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130875
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130876
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130877
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130878
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130879
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130880
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130881
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130882
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130883
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715265609_18081_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715265609_18081_1 tag=TAG20100402T130015
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139146
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139147
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139148
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139149
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139150
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139151
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139152
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139153
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139154
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139155
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139156
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139157
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139158
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139159
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139160
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139161
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139162
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139163
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139164
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139165
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139166
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139167
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139168
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139169
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139170
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139171
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139172
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139173
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139174
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139175
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139176
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139177
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139178
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139179
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139180
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139181
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139182
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139183
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715266018_18080_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715266018_18080_1 tag=TAG20100402T130015
channel ORA_DISK_1: restore complete, elapsed time: 00:08:55
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139193
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139194
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139195
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139196
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139197
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139198
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139199
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139200
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715266355_18082_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715266355_18082_1 tag=TAG20100402T130015
channel ORA_DISK_1: restore complete, elapsed time: 00:08:35
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139184
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139185
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139186
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139187
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139188
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139189
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139190
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139191
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139192
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715266651_18083_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715266651_18083_1 tag=TAG20100402T130015
channel ORA_DISK_1: restore complete, elapsed time: 00:08:35
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139201
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139202
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139203
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139204
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139205
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139206
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139207
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139208
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139209
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139210
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139211
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139212
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139213
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139214
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139215
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139216
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139217
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139218
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139219
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139220
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139221
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139222
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139223
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139224
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139225
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715266957_18084_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715266957_18084_1 tag=TAG20100402T130015
channel ORA_DISK_1: restore complete, elapsed time: 00:06:40
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130884
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130885
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130886
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130887
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130888
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130889
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130890
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130891
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130892
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130893
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130894
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130895
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130896
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130897
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130898
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130899
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130900
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130901
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130902
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130903
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130904
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130905
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130906
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130907
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130908
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130909
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130910
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130911
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130912
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130913
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130914
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130915
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130916
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130917
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130918
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130919
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130920
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130921
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130922
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130923
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130924
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130925
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130926
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130927
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130928
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130929
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715296183_18086_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715296183_18086_1 tag=TAG20100402T213015
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130930
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130931
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130932
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130933
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130934
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130935
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130936
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715296294_18088_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715296294_18088_1 tag=TAG20100402T213015
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139226
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139227
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139228
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139229
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139230
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139231
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139232
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139233
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139234
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139235
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139236
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139237
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139238
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139239
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139240
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139241
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139242
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139243
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139244
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139245
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139246
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139247
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139248
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139249
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139250
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139251
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139252
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139253
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139254
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139255
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139256
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139257
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139258
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139259
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139260
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139261
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139262
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139263
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139264
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139265
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139266
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139267
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139268
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139269
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139270
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139271
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139272
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139273
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139274
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139275
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139276
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139277
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100402_715296617_18087_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100402_715296617_18087_1 tag=TAG20100402T213015
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130937
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130938
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130939
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130940
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130941
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130942
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130943
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130944
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130945
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130946
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130947
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130948
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130949
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130950
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130951
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130952
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130953
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130954
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130955
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130956
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130957
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130958
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130959
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130960
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130961
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130962
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130963
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130964
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130965
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130966
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130967
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130968
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=130969
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100403_715351984_18101_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100403_715351984_18101_1 tag=TAG20100403T130014
channel ORA_DISK_1: restore complete, elapsed time: 00:00:38
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/orabak/testarch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139278
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139279
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139280
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139281
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139282
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139283
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139284
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139285
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139286
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139287
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139288
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139289
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139290
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139291
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139292
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139293
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139294
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139295
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139296
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139297
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139298
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139299
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139300
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139301
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139302
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139303
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139304
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139305
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139306
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139307
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139308
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139309
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139310
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=139311
channel ORA_DISK_1: reading from backup piece /orabak/arch/CNDERPDB_arch_20100403_715353019_18105_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/orabak/arch/CNDERPDB_arch_20100403_715353019_18105_1 tag=TAG20100403T130014
channel ORA_DISK_1: restore complete, elapsed time: 00:06:56
Finished restore at 06-APR-10

RMAN>

RMAN> exit

Recovery Manager complete.

四、把归档日志文件转移到测试环境以便进行日志挖掘

p5b2@/orabak/testarch$ rcp p5a1:/orabak/testarch/* /orabak/testarch/
p5b2@/orabak/testarch$ ls -l
total 36555528
-rw-r—–   1 oracle   oinstall   10272768 Apr 06 11:50 1_130816_640266118.dbf
-rw-r—–   1 oracle   oinstall     756224 Apr 06 11:50 1_130817_640266118.dbf
-rw-r—–   1 oracle   oinstall     308736 Apr 06 11:50 1_130818_640266118.dbf
-rw-r—–   1 oracle   oinstall     160256 Apr 06 11:50 1_130819_640266118.dbf
-rw-r—–   1 oracle   oinstall     113664 Apr 06 11:50 1_130820_640266118.dbf
-rw-r—–   1 oracle   oinstall      59904 Apr 06 11:50 1_130821_640266118.dbf
……
-rw-r—–   1 oracle   oinstall   10095104 Apr 06 12:20 2_139311_640266118.dbf

五、在测试环境上开始用Logminer工具进行挖掘:

p5b2@/orabak/testarch$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Apr 6 14:01:09 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘erp’);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>’/orabak/testarch/1_130816_640266118.dbf’,Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>’/orabak/testarch/1_130817_640266118.dbf’,Options=>dbms_logmnr.addfile);
……
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>’/orabak/testarch/1_130821_640266118.dbf’,Options=>dbms_logmnr.addfile);

SQL>
PL/SQL procedure successfully completed.

SQL>

PL/SQL procedure successfully completed.
SQL>
SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> create table erp.log_201004_1 as select * from v$logmnr_contents;

Table created.

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

查询结果集,根据表名和DML操作类型定位SQL语句:

SQL> select count(*)
2  from v$logmnr_contents where seg_owner=’ERP’
3  and seg_name=’FA_CARD’ and operation=’DELETE’
4  /

COUNT(*)
———-
29

SQL> select timestamp,username,session#,sql_redo,operation,session_info
2 from log_201004_1 where seg_owner=’ERP’
3 and seg_name=’FA_CARD’ and operation=’DELETE’
4  /

根据SQL语句的内容和系统日志,最终确定了具体的案发时间、地点和人物,但是这种数据丢失已经不可挽回了,因为这个数据丢失并不是简单的删除一个表的记录,而是程序代码的问题导致每当用户做一个操作的时侯,就会删除掉一点数据,可能从系统上线的时侯开始,数据就已经不准确了。应用程序的错误导致的数据损失,这是多么可怕的事情。

解决dba_outstanding_alerts误报表空间使用率的问题

前段时间通过dba_outstanding_alerts发现表空间增长超过了85%,然后加了个裸设备做为数据文件,虽然这个数据文件可用,但dba_outstanding_alerts中的警告信息并没有消失:

SQL> col reason for a50
SQL> SELECT REASON
2  , METRIC_VALUE
3  , TO_CHAR(CREATION_TIME,’DD-MON-YYYY HH24:MI:SS’) cdate
4  FROM SYS.DBA_OUTSTANDING_ALERTS;

REASON                                             METRIC_VALUE CDATE
————————————————– ———— ———————–
Tablespace [ERP_INDEX] is [94 percent] full          94.5023148 26-MAR-2010 19:45:05

可见Oracle在这里认为这个表空间已经使用了94%以上,查看一下预警阀值的设定:

SQL> col METRICS_NAME for a50

SQL> col WARN_VAL for a10
SQL> col CRIT_VAL for a10
SQL> col OBJ_TYPE for a20
SQL> SELECT METRICS_NAME
2  , WARNING_VALUE WARN_VAL
3  , CRITICAL_VALUE CRIT_VAL
4  , OBJECT_TYPE OBJ_TYPE
5  FROM SYS.DBA_THRESHOLDS
6  WHERE metrics_name LIKE ‘%Tablespace%’;

METRICS_NAME                   WARN_VAL   CRIT_VAL   OBJ_TYPE
—————————— ———- ———- ——————–
Tablespace Bytes Space Usage   0          0          TABLESPACE
Tablespace Space Usage         85         97         TABLESPACE

超过85%就提示,似乎也是顺利成章的,可是实际表空间到底占用了多少呢:

SQL> select sum(bytes/1024/1024) from dba_data_files
2  where TABLESPACE_NAME=’ERP_INDEX’;

SUM(BYTES/1024/1024)
——————–
31744

SQL> select sum(bytes/1024/1024) from sys.dba_free_space where tablespace_name=’ERP_INDEX’
2  /

SUM(BYTES/1024/1024)
——————–
3936

SQL>
SQL> select (31744-3936)/31744 from dual;

(31744-3936)/31744
——————
.876008065

可见表空间实际的使用率是87%左右,那么为什么dba_outstanding_alerts中会提示是使用了94%呢?从DBA_TABLESPACE_USAGE_METRICS这个oracle 10g新增的未publish的视图来看,使用率也是94%:

SQL> SELECT TABLESPACE_NAME TBSP_NAME
2  , USED_SPACE*8/1024
3  , TABLESPACE_SIZE*8/1024 TBSP_SIZE
4  , USED_PERCENT
5  FROM SYS.DBA_TABLESPACE_USAGE_METRICS where TABLESPACE_NAME=’ERP_INDEX’;

TBSP_NAME                      USED_SPACE*8/1024  TBSP_SIZE USED_PERCENT
—————————— —————– ———- ————
ERP_INDEX                                  30016      31744   94.5564516

而这里显示为94%的原因是因为它统计了recyclebin里占用的空间:

SQL> connect banping/banping
Connected.
SQL> select sum(space*8/1024) from user_recyclebin where ts_name=’ERP_INDEX’;

SUM(SPACE*8/1024)
—————–
2240

SQL> select (31744-3936+2240)/31744 from dual;

(31744-3936+2240)/31744
———————–
.946572581

可见,实际使用空间加上recyclebin里的这个表空间的对象大小正好是94%左右。那么purge了recyclebin后,是不是就解决了问题呢?

SQL> purge recyclebin;

Done

SQL> SELECT TABLESPACE_NAME TBSP_NAME
2  , USED_SPACE*8/1024
3  , TABLESPACE_SIZE*8/1024 TBSP_SIZE
4  , USED_PERCENT
5  FROM SYS.DBA_TABLESPACE_USAGE_METRICS where TABLESPACE_NAME=’ERP_INDEX’;

TBSP_NAME                      USED_SPACE*8/1024  TBSP_SIZE USED_PERCENT
—————————— —————– ———- ————
ERP_INDEX                                  27776      31744   87.5

SQL> col reason for a50
SQL> SELECT REASON
2  , METRIC_VALUE
3  , sequence_id,reason_id
4  FROM SYS.DBA_OUTSTANDING_ALERTS;

REASON                                             METRIC_VALUE  sequence_id reason_id
————————————————– ———— ————— ———-
Tablespace [ERP_INDEX] is [94 percent] full          94.5023148  566360 9

可见这样操作能够消除DBA_TABLESPACE_USAGE_METRICS视图的错误统计,而dba_outstanding_alerts中的警告却依然如故。尝试看一下dba_outstanding_alerts的基表:

CREATE OR REPLACE VIEW SYS.DBA_OUTSTANDING_ALERTS AS
SELECT sequence_id,
reason_id,
owner,
object_name,
subobject_name,
typnam_keltosd AS object_type,
dbms_server_alert.expand_message(userenv(‘LANGUAGE’),
mid_keltsd,
reason_argument_1,
reason_argument_2,
reason_argument_3,
reason_argument_4,
reason_argument_5) AS reason,
time_suggested,
creation_time,
dbms_server_alert.expand_message(userenv(‘LANGUAGE’),
amid_keltsd,
action_argument_1,
action_argument_2,
action_argument_3,
action_argument_4,
action_argument_5)
AS suggested_action,
advisor_name,
metric_value,
decode(message_level, 32, ‘Notification’, ‘Warning’)
AS message_type,
nam_keltgsd AS message_group,
message_level,
hosting_client_id,
mdid_keltsd AS module_id,
process_id,
host_id,
host_nw_addr,
instance_name,
instance_number,
user_id,
execution_context_id,
error_instance_id
FROM wri$_alert_outstanding, X$KELTSD, X$KELTOSD, X$KELTGSD,
dba_advisor_definitions
WHERE reason_id = rid_keltsd
AND otyp_keltsd = typid_keltosd
AND grp_keltsd = id_keltgsd
AND aid_keltsd = advisor_id(+)

信息来自wri$_alert_outstanding表,和X$KELTSD, X$KELTOSD, X$KELTGSD等底层表都有关联,担心直接删掉的话会出问题,于是尝试修改下阀值:

SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id=>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value=>95,
critical_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value=>97,
observation_period=>1,
consecutive_occurrences=>1,
instance_name=>NULL,
object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name=>NULL);

PL/SQL procedure successfully completed

因为告警信息消除后,会从dba_outstanding_alerts转移到DBA_ALERT_HISTORY视图,查看这两个视图,果然发现了信息已经转移到了DBA_ALERT_HISTORY,而且提示的空间使用率已经变成了正确的87%,看来在这个过程中oracle会去某个地方重新取一次这个信息:

select * from dba_alert_history where sequence_id=566360 and reason_id=9

SQL> SELECT REASON
2  , METRIC_VALUE
3  FROM SYS.DBA_ALERT_HISTORY where sequence_id=566360 and reason_id=9;

REASON                                             METRIC_VALUE
————————————————– ————
Tablespace [ERP_INDEX] is [87 percent] full          87.5

再把告警阀值更改回原来的85%,发现告警信息又会出现在dba_outstanding_alerts中,不过已经是正确的87%了,而DBA_ALERT_HISTORY中的历史信息还是存在的,至此曲线解决了这个问题。

SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id=>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value=>85,
critical_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value=>97,
observation_period=>1,
consecutive_occurrences=>1,
instance_name=>NULL,
object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name=>NULL);

PL/SQL procedure successfully completed

SQL> col reason for a50
SQL> SELECT REASON
2  , METRIC_VALUE
3  , TO_CHAR(CREATION_TIME,’DD-MON-YYYY HH24:MI:SS’) cdate
4  FROM SYS.DBA_OUTSTANDING_ALERTS;

REASON                                             METRIC_VALUE CDATE
————————————————– ———— ———————–
Tablespace [ERP_INDEX] is [87 percent] full          87.5  8-JUN-2010 16:45:05

metalink上有个文档列举了一些解决表空间使用率提示的问题,不过都不适用于我这个案例:

Troubleshooting a Database Tablespace Used(%) Alert problem [ID 403264.1]

解决一次Listener挂掉的问题

在一个大型系统开发环境,有人反映无法连接到数据库,经检查判断是listener的问题,找到对应的进程杀掉后重启listener 就解决了。

开始怀疑是只是单个session的问题:

[oracle@erpdevdb bdump]$ sqlplus banping/banping

SQL*Plus: Release 10.2.0.1.0 – Production on Mon May 17 10:23:07 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> select machine from v$session;

MACHINE
—————————————————————-
servicedesk
localhost.localdomain
x-9ee5775925
erpdevdb
erp-app
WORKGROUPDEV00
WORKGROUPERP_DEV003

WORKGROUPDEV00
erp-app
servicedesk
servicedesk
localhost.localdomain
erpdevdb

……

39 rows selected.

SQL> select sql_id from v$session where machine=’x-9ee5775925′;

SQL_ID
————-
2vwg9dh3v6rkg

SQL> select sql_text from v$sql where sql_id=’2vwg9dh3v6rkg’;

no rows selected

SQL> select distinct user from v$session;

USER
——————————
banping

后来意识到是监听的问题,查看监听果然没有反应:

[oracle@erpdevdb bdump]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 17-MAY-2010 10:31:40

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

查找到listener 的进程并杀死进程:

[oracle@erpdevdb admin]$ ps -ef|grep ora

……

oracle   13720 28474  0 May15 ?        00:00:00 /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

[oracle@erpdevdb admin]$ kill -9 13720

重新启动监听:

[oracle@erpdevdb bdump]$ lsnrctl stat

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 17-MAY-2010 10:49:37

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erpdevdb)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@erpdevdb bdump]$
[oracle@erpdevdb bdump]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 17-MAY-2010 10:49:44

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erpdevdb)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
LSNRCTL> start
Starting /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdevdb.banping.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date                17-MAY-2010 10:49:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdevdb.banping.com)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>
LSNRCTL> exit

[oracle@erpdevdb admin]$ ps -ef|grep tnslsnr
oracle    5021     1  0 10:49 ?        00:00:00 /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

查看sqlnet.log记录:

Fatal NI connect error 12537, connecting to:
(LOCAL=NO)

VERSION INFORMATION:
TNS for Linux: Version 10.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 – Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 – Production
Time: 17-MAY-2010 10:49:23
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

查看listener.log 记录

17-MAY-2010 10:49:23 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45772)) * establish * +ASM * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
17-MAY-2010 10:49:23 * service_update * erpoptm * 0
17-MAY-2010 10:49:23 * service_update * erpwh * 0
17-MAY-2010 10:49:23 * service_update * devdb * 0
17-MAY-2010 10:49:23 * service_update * erptest * 0
17-MAY-2010 10:49:23 * service_update * papererpdb * 0
17-MAY-2010 10:49:23 * service_update * erpstudydb * 0
17-MAY-2010 10:49:23 * service_update * +ASM * 0
17-MAY-2010 10:49:23 * service_update * erpoptm * 0
17-MAY-2010 10:49:23 * service_update * erpwh * 0
17-MAY-2010 10:49:23 * service_update * devdb * 0
17-MAY-2010 10:49:23 * service_update * papererpdb * 0
17-MAY-2010 10:49:23 * service_update * erpstudydb * 0
17-MAY-2010 10:49:23 * ping * 0
17-MAY-2010 10:49:23 * service_update * erptest * 0
17-MAY-2010 10:49:23 * service_update * +ASM * 0
17-MAY-2010 10:49:23 * ping * 0
17-MAY-2010 10:49:23 * ping * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SID=erptest)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45907)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45905)) * establish * +ASM * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=erptest)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45863)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SID=erptest)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=45774)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46043)) * establish * +ASM * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=erptest)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46005)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=erptest)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46084)) * establish * erptest * 0
17-MAY-2010 10:49:23 * (CONNECT_DATA=(SID=erptest)(CID=(PROGRAM=perl@erpdevdb)(HOST=erpdevdb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.88)(PORT=46045)) * establish * erptest * 0
17-MAY-2010 10:49:23 * service_update * +ASM * 0

TNSLSNR for Linux: Version 10.2.0.1.0 – Production on 17-MAY-2010 10:49:52

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Trace information written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/trace/li

SQL语句的日期格式和nls_date_format参数

前段时间把一个在Linux 平台运行的应用程序部署一套到windows平台上,是java开发的基于oracle10g的程序,部署完成后,发现有个写入操作会报错,而后台跟踪到的SQL是插入日期时侯出现的问题。

insert into t ( fromtime ) values ( ’2009-4-15′ )

其中的fromtime是date 类型,而SQL中并没有对字符串做to_date转化。程序不大好修改,只能在环境变量等参数配置上下手。而奇怪的是这样的SQL在原来的Linux 平台上能运行的很好,说明是和环境问题是有关的。

首先修改了oracle的nls_date_format参数,原来是空的,改为yyyy-mm-dd hh24:mi:Ss,用sqlplus连到服务器测试SQL能执行,但是java程序还是报错,看来得改客户端的nls_date_format设置,老熊说有些jdbc驱动可以采用设置数据库的nls_data_format方式来解决,因为这种驱动连接上后自动将会话的这个设为跟数据库一样的。修改windows系统的环境变量仍然不行,看来java程序可以不从操作系统来读变量。以下是测试过程:

SQL> create table t1 (mydate varchar2(20));

Table created.

SQL> insert into t1 mydate values (2010-10-10);

1 row created.

SQL> alter table t1 modify mydate date;

Table altered.

SQL>
SQL> desc t1;
Name                                      Null?    Type
—————————————– ——– —————————-
MYDATE                                             DATE

SQL> insert into t1 mydate values (’2010-10-10′);
insert into t1 mydate values (’2010-10-10′)
*
ERROR at line 1:
ORA-01861: literal does not match format string

SQL> show parameter nls_date

NAME                                 TYPE        VALUE
———————————— ———– ——————————
nls_date_format                      string
nls_date_language                    string

SQL> alter system set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
alter system set nls_date_format=’yyyy-mm-dd hh24:mi:ss’
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

SQL> alter system set nls_date_format=’yyyy-mm-dd hh24:mi:ss’ scope=spfile;

System altered.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2022144 bytes
Variable Size             285213952 bytes
Database Buffers         1845493760 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
———————————— ———– ——————————
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss

SQL> insert into t1 mydate values (’2010-10-10′);

1 row created.

SQL> commit;

Commit complete.

最后尝试在程序使用的oracle用户登录的时侯修改当前session的nls_date_format,通过触发器来实现

create or replace trigger tri_logon_nlsformat
after logon
ON DATABASE
when (USER=’BANPING’)
begin
execute immediate ‘alter session set nls_date_format = ”YYYY-MM-DD” ‘;
end;

这样用户通过应用程序每次连接到数据库的时侯,就修改当前session的nls_date_format,应用程序不再报错,不过这只是一个丑陋的解决办法,根本的还需要从修改程序入手。

客户端异常退出导致的僵死进程

今天在数据库的v$locked_object视图 里发现一个923号session,用户是SYS,一直持有对 PLAN_TABLE表的锁不释放,PLAN_TABLE是存储SQL执行计划的,很奇怪。

select sid,serial#,paddr,username,server,schemaname,osuser,terminal,program from v$session where sid=923

SID    SERIAL#    PADDR    USERNAME    SERVER    SCHEMANAME    OSUSER    TERMINAL    PROGRAM
923    42779    0700000C8F3D9588    SYS    PSEUDO    SYS    oracle    pts/2    sqlplus@p5b1 (TNS V1-V3)

查看进程信息:

select * from v$process where addr=’0700000C8F3D9588′

发现spid的值为234178,这个也就是操作系统的进程id:

DB2@/ora_arch>ps -ef|grep 234178
oracle 413964 807630   0 15:00:19  pts/1  0:00 grep 234178
oracle 234178 623538   0   May 04      -  0:39 oraclebanpingdb2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

通过检查一些操作日志发现5月4号,青年节这天确实有人登录执行过一个SQL:

SQL> create or replace procedure show_space(…

然后异常退出了,那么可以断定就是这个session一直僵死在这里了。

这时在数据库端杀session:

alter system kill session ’923,42779′

从v$session看oracle只是把这个session标记为killed了,v$process虽然也查不到了,但是os层面的进程还在,杀之:

DB2@/ora_arch>kill -9 234178

然后再看,这个session已经被清理掉消失了:

select * from v$session where status=’KILLED’

这种进程在oracle实例关闭的时侯会在alert日志里记录:

Process OS id : 271052 alive after kill
Errors in file /u01/admin/banpingdb/udump/banpingdb1_ora_865116.trc

模拟一个死锁的状态

锁是数据库里一个重要的概念,可以自己来模拟一个Oracle中的死锁状态。

首先session A登录到系统,更新一条记录:

[oracle@devdb ~]$ export ORACLE_SID=optm
[oracle@devdb ~]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 11 23:14:09 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> connect banping/mypassword
Connected.

SQL> create table banpingtest (a varchar2(10),b varchar2(10));

Table created.

SQL> insert into banpingtest(a,b) values (’10′,’aaa’);

1 row created.

SQL> insert into banpingtest(a,b) values (’20′,’bbb’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from banpingtest;

A          B
———- ———-
10         aaa
20         bbb

SQL> update banpingtest set b=’ccc’ where a=’10′;

1 row updated.

这里不提交事务,然后再开一个session B来更新另外一条记录:

SQL> update banpingtest set b=’ddd’ where a=’20′;

1 row updated.

这里同样不提交这个事务,这样这两行分别被持有,其他session无法更新了,然后再用session A去更新B持有的这个记录:

SQL> update banpingtest set b=’eee’ where a=’20′;

这时A的这个操作会处于等待状态,因为B那边没有提交,没有释放这行上的锁。同样,用B去更新A持有的记录,同样会处于等待状态:

SQL> update banpingtest set b=’fff’ where a=’10′;

这时,B等待A提交事务,而A也等待B提交事务,两个session都需要对方的资源,一个死锁就产生了。

Oracle会自动检测死锁,A session很快就会产生报错:

update banpingtest set b=’eee’ where a=’20′
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

而这时B session仍然在等待,这时A session如果提交事务,则B session会结束等待,收到成功更新的提示信息。

ORA-12705给我带来的折腾

从windows 平台迁移一个简单的Java系统到Linux 平台,应用服务器是Tomcat,在Linux 上安装好JDK后,直接把整个Tomcat复制到了Linux 上,然后启动都正常。但是在软件打开登录的时侯一直提示无法连接到数据库,坚持Tomcat的日志记录的信息如下:

ORA-00604: error occurred at recursive SQL level 1

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

Google 了很多资料,大多说是NLS_LANG设置的问题:

ORA-12705: “invalid or unknown NLS parameter value specified”

Cause: There are two possible causes:

- An attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value.

- The NLS_LANG environment variable contains an invalid language, territory, or character set.

Action: Check the syntax of the ALTER SESSION command and the NLS parameter, correct the syntax and retry the statement, or specify correct values in the NLS_LANG environment variable.

可是我尝试修改这些环境变量都没用,更换JDBC的驱动版本,没用,仍然是报同样的错误,IT系统就是这样,出错的时侯一定有确定的原因,可是有时侯你就是不知道原因在哪里,只能忍受这种折磨。

后来我打算重新安装一个Tomcat版本,可是就在计划把原来的应用程序文件复制到新的Tomcat环境的时侯,发现了在原来的Catalina.sh里的一行配置:

JAVA_OPTS=”-server -Dfile.encoding=GB18030 -Xms512m -Xmx1400m -Duser.language=zh_CN -Duser.timezone=Asia/Shanghai -Duser.country=CN

很明显是这里设置的时区和语言参数造成的ORA-12705错误,去掉这些参数,改成这样:

JAVA_OPTS=’-Xms256m -Xmx1400m’

重启登录,一切正常。但是Windows平台上的应用也有这样的配置为什么就能正常连接同一台数据库呢?看来不只是和数据库端的设置有关,和应用服务器所在的OS也是有关的。

如何使用在线重定义功能建立分区表

oracle 提供了在线重定义功能,可以把普通表转化为分区表,记录一下操作过程:

首先扩展必要的表空间,然后查看要操作的表是否可以进行分区:

[oracle@erpdevdb admin]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Mar 26 18:50:12 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database datafile 7 resize 12288m;

Database altered.

SQL> connect erp/erp
Connected.
SQL>
SQL> set timing on
SQL>
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘ERP’, ‘BALANCE’,dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

如果不能分区,这一步会报错,然后建立中间表,这里采取按year进行range分区,按month进行list子分区的方式:

SQL> create table P_BALANCE
2  (
3    ID         INTEGER not null,
4    YEAR       INTEGER not null,
5    MONTH      INTEGER not null,
6    ACODE      VARCHAR2(16) not null,
7    BCODE      VARCHAR2(16) not null,
8    FCODE      VARCHAR2(4) not null,
9    CCODE      VARCHAR2(16) not null,
10    GCODE      VARCHAR2(20) not null,
11    SCODE      VARCHAR2(8) not null,
12    NCODE      VARCHAR2(8) not null,
13    HCODE      VARCHAR2(75) not null,
14    ICODE      VARCHAR2(35) not null,
15    XCODE      VARCHAR2(35) not null,
16    DCODE      VARCHAR2(32) not null,
17    ECODE      VARCHAR2(16) not null,
18    RMBDEBIT   NUMBER(19,4),
19    RMBCREDIT  NUMBER(19,4),
20    RMBBALANCE NUMBER(19,4),
21    FCYDEBIT   NUMBER(19,4),
22    FCYCREDIT  NUMBER(19,4),
23    FCYBALANCE NUMBER(19,4),
24    USDDEBIT   NUMBER(19,4),
25    USDCREDIT  NUMBER(19,4),
26    USDBALANCE NUMBER(19,4),
27    QTYDEBIT   NUMBER(19,8),
28    QTYCREDIT  NUMBER(19,8),
29    QTYBALANCE NUMBER(19,8),
30    QTXDEBIT   NUMBER(19,4),
31    QTXCREDIT  NUMBER(19,4),
32    QTXBALANCE NUMBER(19,4),
33    GATTR1     VARCHAR2(8) not null,
34    GATTR2     VARCHAR2(8) not null,
35    GATTR3     VARCHAR2(8) not null,
36    GATTR4     VARCHAR2(8) not null,
37    GATTR5     VARCHAR2(8) not null,
38    GATTR6     VARCHAR2(8) not null,
39    GATTR7     VARCHAR2(8) not null,
40    GATTR8     VARCHAR2(8) not null,
41    GATTR9     VARCHAR2(8) not null
42  )
43  PARTITION BY RANGE (YEAR)
44  SUBPARTITION BY LIST (MONTH)
45  SUBPARTITION TEMPLATE
46  (SUBPARTITION B0 VALUES (0),
47  SUBPARTITION B1 VALUES (1),
48  SUBPARTITION B2 VALUES (2),
49  SUBPARTITION B3 VALUES (3),
50  SUBPARTITION B4 VALUES (4),
51  SUBPARTITION B5 VALUES (5),
52  SUBPARTITION B6 VALUES (6),
53  SUBPARTITION B7 VALUES (7),
54  SUBPARTITION B8 VALUES (8),
55  SUBPARTITION B9 VALUES (9),
56  SUBPARTITION B10 VALUES (10),
57  SUBPARTITION B11 VALUES (11),
58  SUBPARTITION B12 VALUES (12))
59  (PARTITION B2005 VALUES LESS THAN (2005),
60  PARTITION B2006 VALUES LESS THAN (2006),
61  PARTITION B2007 VALUES LESS THAN (2007),
62  PARTITION B2008 VALUES LESS THAN (2008),
63  PARTITION B2009 VALUES LESS THAN (2009),
64  PARTITION B2010 VALUES LESS THAN (2010),
65  PARTITION B2011 VALUES LESS THAN (MAXVALUE))
66  tablespace ERP_CW
67  ;

Table created.

Elapsed: 00:00:00.57

为中间表建立主键:

SQL> alter table P_BALANCE add constraint P_BALANCE_PRIMARYKEY primary key (ID);

Table altered.

Elapsed: 00:00:00.11

开始用重定义的方式进行分区:

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(‘CNDERP’, ‘BALANCE’, ‘P_BALANCE’);

PL/SQL procedure successfully completed.

Elapsed: 00:02:17.53

根据源表上的索引规则建立分区后的local 索引:

SQL> CREATE INDEX P_BALANCE_ID ON P_BALANCE(ID)
2  local
3  (
4  PARTITION B2005 TABLESPACE ERP_INDEX,
5  PARTITION B2006 TABLESPACE ERP_INDEX,
6  PARTITION B2007 TABLESPACE ERP_INDEX,
7  PARTITION B2008 TABLESPACE ERP_INDEX,
8  PARTITION B2009 TABLESPACE ERP_INDEX,
9  PARTITION B2010 TABLESPACE ERP_INDEX,
10  PARTITION B2011 TABLESPACE ERP_INDEX
11  )
12  ;
CREATE INDEX P_BALANCE_ID ON P_BALANCE(ID)
*
ERROR at line 1:
ORA-01408: such column list already indexed

Elapsed: 00:00:00.00
SQL> create index P_BALANCE_CCODE_ACODE_NY on P_BALANCE (CCODE, ACODE, YEAR, MONTH)
2  local
3  (
4  partition B2005 TABLESPACE ERP_INDEX,
5  partition B2006 TABLESPACE ERP_INDEX,
6  partition B2007 TABLESPACE ERP_INDEX,
7  partition B2008 TABLESPACE ERP_INDEX,
8  partition B2009 TABLESPACE ERP_INDEX,
9  partition B2010 TABLESPACE ERP_INDEX,
10  partition B2011 TABLESPACE ERP_INDEX
11  )
12  ;

Index created.

Elapsed: 00:00:55.11
SQL> create index P_BALANCE_DCODE on P_BALANCE (DCODE)
2  local
3  (
4  partition B2005 TABLESPACE ERP_INDEX,
5  partition B2006 TABLESPACE ERP_INDEX,
6  partition B2007 TABLESPACE ERP_INDEX,
7  partition B2008 TABLESPACE ERP_INDEX,
8  partition B2009 TABLESPACE ERP_INDEX,
9  partition B2010 TABLESPACE ERP_INDEX,
10  partition B2011 TABLESPACE ERP_INDEX
11  )
12  ;

Index created.

Elapsed: 00:00:41.85
SQL> create index P_BALANCE_YEAR_MONTH_BCODE on P_BALANCE (BCODE, ACODE, YEAR, MONTH)
2  local
3  (
4  partition B2005 TABLESPACE ERP_INDEX,
5  partition B2006 TABLESPACE ERP_INDEX,
6  partition B2007 TABLESPACE ERP_INDEX,
7  partition B2008 TABLESPACE ERP_INDEX,
8  partition B2009 TABLESPACE ERP_INDEX,
9  partition B2010 TABLESPACE ERP_INDEX,
10  partition B2011 TABLESPACE ERP_INDEX
11  )
12  ;

Index created.

Elapsed: 00:00:57.70
SQL> create index P_BALANCE_bcode_acode on P_BALANCE (BCODE, ACODE)
2  local
3  (
4  partition B2005 TABLESPACE ERP_INDEX,
5  partition B2006 TABLESPACE ERP_INDEX,
6  partition B2007 TABLESPACE ERP_INDEX,
7  partition B2008 TABLESPACE ERP_INDEX,
8  partition B2009 TABLESPACE ERP_INDEX,
9  partition B2010 TABLESPACE ERP_INDEX,
10  partition B2011 TABLESPACE ERP_INDEX
11  )
12  ;

Index created.

Elapsed: 00:00:49.30

如果有外键约束等其他条件也要在这里一并建立,分区完成后还要注意表上的序列也需要rebuild。

然后就可以完成在线分区了,要注意的是,这一步才完成实际的切换:

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘ERP’, ‘BALANCE’, ‘P_BALANCE’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.66

如果在分区动作过程中业务DML操作很多,可以先同步一次再进行实际切换,这样会减少停滞的时间:

dbms_redefinition.sync_interim_table(‘ERP’, ‘BALANCE’, ‘P_BALANCE’);

分区完成后,要重新收集统计信息,并行度根据cpu数量自己确定:

SQL>exec dbms_stats.gather_table_stats(ownname => ‘erp’,tabname => ‘balance’,cascade => TRUE,degree => 4);

PL/SQL procedure successfully completed.

这里也可以根据需要指定granularity参数,然后就可以删除中间表了:

SQL> drop table p_balance;

Table dropped.

分区常用的一些视图:user_tab_partitions,user_ind_partitions,dba_part_tables,dba_part_indexes等。

GES:Potential blocker on resource TX问题的处理

有时候会在Oracle alert日志中看到如下信息:

GES: Potential blocker (pid=348868) on resource TX-0013000E-0010D004;

这就是rac中的死锁,一般Oracle会自己处理,有时候需要手工干预。要找到这个引起死锁的session也很简单,通过v$process和v$session视图就能查到:

select * from v$session where paddr= (select addr from v$process where spid=’348868′)

可以根据这个session的情况来决定如何处理。比如这个session的program是oracle@p5b1 (J000),这应该 是oracle自身的job,然后检查dba_jobs_running,发现昨晚的一个job没有跑完,而这个job阻塞了其他的session。

可以查看v$session_wait视图查看该session的等待事件:

select * from v$session_wait where event<>’SQL*Net message from client’
and event<>’rdbms ipc message’

发现等待事件在db file sequential read和gc cr request间不断切换,这在rac中是很常见的,说明这个job需要的很多block要从别的节点上作一致读,而state是WAITED KNOWN TIME表示等待已经结束了。

那么如何找到被阻塞的session是什么呢?可以通过v$lock来查看,block=1的是blocker,block=0的是waiter,另外更直观的做法是查看DBA_WAITERS视图,该视图可以通过运行 $ORACLE_HOME/rdbms/admin/catblock.sql这个脚本来创建。DBA_WAITERS里的lock_id1和lock_id2分别对应v$lock中的id1和id2,不同的lock有不同的定义, 比如TM的话,lock1就是object id。

如果严重影响了系统的运行,可以杀死引起死锁的session:

alter system kill session ’833,33751′

oracle扩展表空间的常用方法

监控表空间的使用是DBA的日常工作之一,Oracle扩展表空间很简单,一般有扩展某个数据文件大小或增加新的数据文件两种办法。举例说明如下。

首先我们要获取某个表空间的数据文件信息:

select file_name,file_id,tablespace_name,bytes/1024/1024 from dba_data_files
order by file_name

根据file_id扩展某个数据文件大小:

alter database datafile 12 resize 13312m;

要注意的是,如果使用裸设备,一般要先查看LV是否足够,否则以上扩展命令可能出错,查看方式如下:

查看VG信息:

ERPDB1@/home/oracle>lsvg
rootvg
oraclevg

查看VG的LV:

ERPDB1@/home/oracle>lsvg -l oraclevg
oraclevg:
LV NAME             TYPE       LPs   PPs   PVs  LV STATE      MOUNT POINT
db_oravote          raw        1     1     1    closed/syncd  N/A
db_oraocr           raw        1     1     1    closed/syncd  N/A
db_system           raw        3     3     1    open/syncd    N/A
db_sysaux           raw        5     5     1    open/syncd    N/A
db_undotbs1         raw        3     3     1    open/syncd    N/A
db_undotbs2         raw        3     3     1    open/syncd    N/A
db_temp             raw        46    46    1    closed/syncd  N/A

db_erp_ht2          raw        56    56    1    open/syncd    N/A
db_erp2             raw        72    72    1    open/syncd    N/A

查看VG的详细信息:

ERPDB1@/home/oracle>lsvg oraclevg
VOLUME GROUP:       oraclevg                 VG IDENTIFIER:  00c65fbf00004c000000011697e0f5f9
VG STATE:           active                   PP SIZE:        256 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      2328 (595968 megabytes)
MAX LVs:            512                      FREE PPs:       1146 (293376 megabytes)
LVs:                57                       USED PPs:       1182 (302592 megabytes)
OPEN LVs:           39                       QUORUM:         5
TOTAL PVs:          8                        VG DESCRIPTORS: 8
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         8                        AUTO ON:        no
Concurrent:         Enhanced-Capable         Auto-Concurrent: Disabled
VG Mode:            Concurrent
Node ID:        1                        Active Nodes:
MAX PPs per VG:     130048
MAX PPs per PV:     1016                     MAX PVs:        128
LTG size (Dynamic): 1024 kilobyte(s)         AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable

这里可见还有1146个可用的pp ,根据pp size能计算出VG的容量。

查看某个LV的详细信息:

ERPDB1@/home/oracle>lslv db_erp2
LOGICAL VOLUME:     db_erp2                VOLUME GROUP:   oraclevg
LV IDENTIFIER:      00c65fbf00004c000000011697e0f5f9.44 PERMISSION:     read/write
VG STATE:           active/complete        LV STATE:       opened/syncd
TYPE:               raw                    WRITE VERIFY:   off
MAX LPs:            512                    PP SIZE:        256 megabyte(s)
COPIES:             1                      SCHED POLICY:   parallel
LPs:                72                     PPs:            72
STALE PPs:          0                      BB POLICY:      relocatable
INTER-POLICY:       minimum                RELOCATABLE:    no
INTRA-POLICY:       middle                 UPPER BOUND:    128
MOUNT POINT:        N/A                    LABEL:          None
MIRROR WRITE CONSISTENCY: off
EACH LP COPY ON A SEPARATE PV ?: no
Serialize IO ?:     NO
DEVICESUBTYPE : DS_LVZ

注意这里可以根据pp 的数量和大小计算能提供的容量。

扩展某个LV,增加32个pp :

p5a1@/orabak/arch/transmited#extendlv db_erp_cw 32
p5a1@/orabak/arch/transmited#ls -l /dev/rdb_erp_cw
crw-rw—-   1 oracle   dba          53, 35 Dec 02 2007  /dev/rdb_erp_cw

LV增加了以后,就可以用上面的命令扩展对应的数据文件了。

如果要新增LV,则一般这样操作:

p5a1@/#mklv -y db_erp_index2 -T O -w n -t raw -s n -r n oraclevg 156

p5a1@/#chown oracle.dba /dev/rdb_erp_index2
p5a1@/#ls -l /dev/rdb_erp_index2
crw-rw—-   1 oracle   dba          53, 58 Mar 26 20:02 /dev/rdb_erp_index2

然后增加数据文件:

SQL> ALTER TABLESPACE erp_index ADD DATAFILE ‘/dev/rdb_erp_index2′ SIZE 4096m;

Tablespace altered.

如果是ASM环境,查看可用空间和为表空间增加数据文件的脚本如下:

select group_number,name,total_mb,free_mb from v$asm_diskgroup;

ALTER TABLESPACE erp_index ADD DATAFILE ‘+DATA’ SIZE 4096m;

单个数据文件的大小对性能基本没什么影响,单个数据文件过大就是可能有管理维护方面的潜在问题,比如数据文件损坏带来的数据损失。

如何监控oracle的索引是否使用

很多软件开发过程中,没有注意合理规划索引,造成一个表上有N多个索引,为后续的维护和优化带来麻烦。因此有时候需要监控已有的索引是否在使用,oracle提供了监控索引是否使用的工具,很简单,简要介绍一下。

首先,我们如果是监控一个表上的所有索引,可以这样先生成监控的命令:

SQL> select ‘alter index ‘||index_name||’ monitoring usage;’ from user_indexes where table_name=upper(‘mpaymentappl’)
2  /

‘ALTERINDEX’||INDEX_NAME||’MONITORINGUSAGE;’
————————————————————
alter index IDX_MPAYMENTAPPL_BCODE monitoring usage;
alter index MPAYMENTAPPL_FLAGS monitoring usage;
alter index MPAYMENTAPPL_PAICODE monitoring usage;

然后执行这些脚本就开始监控了,监控信息可通过V$OBJECT_USAGE查看,通过used列可知道这个索引是否被使用:

SQL> select * from V$OBJECT_USAGE
2  /

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
—————————— —————————— — — ——————- ——————-
IDX_MPAYMENTAPPL_BCODE         MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
MPAYMENTAPPL_FLAGS             MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
MPAYMENTAPPL_PAICODE           MPAYMENTAPPL                   YES NO  03/24/2010 10:55:28

取消监控某个索引:

SQL> ALTER INDEX MPAYMENTAPPL_FLAGS NOMONITORING USAGE;

Index altered.

SQL> select * from V$OBJECT_USAGE
2  /

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
—————————— —————————— — — ——————- ——————-
IDX_MPAYMENTAPPL_BCODE         MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
MPAYMENTAPPL_FLAGS             MPAYMENTAPPL                   NO  NO  03/24/2010 10:55:27 03/24/2010 10:57:19
MPAYMENTAPPL_PAICODE           MPAYMENTAPPL                   YES NO  03/24/2010 10:55:28

那些持续关注一定时间没有使用的索引就可以删除了,以提高DML操作效率。

统计信息被锁定引发的血案

在一个测试环境进行统计信息收集,结果提示被锁定:

SQL> exec dbms_stats.gather_table_stats(‘erp’,'balance’);
BEGIN dbms_stats.gather_table_stats(‘cnderp’,'balance’); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 13056
ORA-06512: at “SYS.DBMS_STATS”, line 13076
ORA-06512: at line 1
Elapsed: 00:00:00.87

可以用以下命令解除锁定:

SQL> exec dbms_stats.unlock_table_stats(‘erp’,'balance’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.47
SQL> exec dbms_stats.gather_table_stats(‘erp’,'balance’);

PL/SQL procedure successfully completed.

Elapsed: 00:04:37.31

解除都整个schema对象的锁定

SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname => ‘erp’);

PL/SQL procedure successfully completed.

统计信息被锁定的原因有多种:

Symptoms
———
Either of the following two error messages are signaled:
1. ORA-38029: object statistics are locked
2. ORA-20005: object statistics are locked (stattype = ALL)

Cause
———
Possible Cause 1:
DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table.

Possible Cause 2:
Using import (imp) or data pump import (impdp) to import a table without data results in the table’s statistics being locked in 10gR2.

Possible Cause 3:
After an IMPORT is finished for which ROWS=N, the statistics for all tables imported will be locked.
Part Number B14233-04 Database Readme 10g Release 2 (10.2) (39.5 Original Export/Import)

Possible Cause 4: If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table’s volatility. During an upgrade to 10gR2 statistics on queue tables are deleted and then locked. In 10gR2 when a queue table is created statistics are locked while still empty.

Solution
———
If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables. If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter.

To prevent import (imp) from locking the table’s statistics when importing a table without therows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table’s statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

我这里统计信息被锁定的原因是:这些数据文件是从一个测试库copy过来的,而这些数据最初imp到测试库的时候没有导入约束,然后我在当前环境通过imp加rows=n参数导入了约束信息。

但是奇怪的事情是,在完成了统计信息收集后,通过autotrace查看sql语句的统计信息时,统计信息显示的都是0:

SQL> connect sys/sys as sysdba
Connected.
SQL>
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Database opened.
SQL> connect erp/erp
Connected.

SQL> show user
USER is “ERP”

SQL> set timing on
SQL> set autotrace on statistics
SQL> select count(*) from balance;

COUNT(*)
———-
19548622

Elapsed: 00:00:01.31

Statistics
———————————————————-
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
0  bytes sent via SQL*Net to client
0  bytes received via SQL*Net from client
0  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

尝试了很多种办法也没用:

SQL> drop table plan_table
2  /

Table dropped.

Elapsed: 00:00:02.83
SQL> @?/rdbms/admin/utlxplan.sql

Table created.

Elapsed: 00:00:00.26

SQL> analyze table balance estimate statistics;

Table analyzed.

开来这个问题有待进一步研究:

SQL> show user
USER is “SYS”

SQL> connect erp/erp@erpoptm
Connected.

SQL> set autotrace on statistics
SQL> set timing on
SQL> select count(*) from balance;

COUNT(*)
———-
19548622

Elapsed: 00:00:00.99

Statistics
———————————————————-
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
0  bytes sent via SQL*Net to client
0  bytes received via SQL*Net from client
0  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

SQL> exit

[oracle@erpdevdb install]$ sqlplus “cnderp/cnderp@erpoptm”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 22 11:38:34 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on statistics
SQL> select count(*) from balance;

COUNT(*)
———-
19548622

Statistics
———————————————————-
0  recursive calls
0  db block gets
43615  consistent gets
0  physical reads
0  redo size
518  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@erpdevdb install]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 22 11:39:13 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> connect erp/erp@erpoptm
Connected.
SQL> set autotrace on statistics
SQL> select count(*) from balance;

COUNT(*)
———-
19548622

Statistics
———————————————————-
0  recursive calls
0  db block gets
43615  consistent gets
0  physical reads
0  redo size
518  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@erpdevdb install]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 22 11:40:28 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on statistics
SQL> select count(*) from erp.balance;

COUNT(*)
———-
19548622

Statistics
———————————————————-
1  recursive calls
0  db block gets
43615  consistent gets
0  physical reads
0  redo size
518  bytes sent via SQL*Net to client
469  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

SQL> show user
USER is “SYS”

看来用非sysdba用户通过网络登录一次sqlplus就可以了,这个问题应该还是和以前流传的sys用户看不到统计信息的bug有关。

在64位Linux下安装OWB

Oracle Warehouse Builder (OWB) 是 Oracle 的一个综合工具,它提供了易用的图形环境,从而可以快速设计、部署和管理业务智能系统。

一台已经安装了Oracle 10g的64位Linux下安装10.0.1版本OWB软件时,碰到了两个错误,后来查了些资料解决了,记录一下。

首先是OWB需要有自己的ORACLE_HOME,建议建立一个单独的路径来安装OWB,在安装到一半的时候会报调用isqlldr的错,日志信息如下:

INFO: /u01/app/oracle/oracle/product/10.2.0/db_1/owb/rdbms/lib/ins_rdbms.mk:2: /u01/app/oracle/oracle/product/10.2.0/db_1/owb:/u01/app/oracle/oracle/product/10.2.0/db_1:/rdbms/lib/env_rdbms.mk: No such file or directory

INFO: /u01/app/oracle/oracle/product/10.2.0/db_1/owb/rdbms/lib/ins_rdbms.mk:23: target `ksms.s’ given more than once in the same rule.

INFO: make: *** No rule to make target `/u01/app/oracle/oracle/product/10.2.0/db_1/owb:/u01/app/oracle/oracle/product/10.2.0/db_1:/rdbms/lib/env_rdbms.mk’.  Stop.

INFO: End output from spawned process.
INFO: ———————————-
INFO: Exception thrown from action: make

Exception Name: MakefileException
Exception String: Error in invoking target ‘isqlldr’ of makefile ‘/u01/app/oracle/oracle/product/10.2.0/db_1/owb/rdbms/lib/ins_rdbms.mk’. See ‘/u01/app/oracle/oraInventory/logs/installActions2010-03-12_02-44-34PM.log’ for details.
Exception Severity: 1

这个错误的解决办法就是编辑ins_rdbms.mk文件,增加一行指定OWB自身的ORACLE_HOME的配置:

# Entering /ade/aime_rdbms_9819/oracle/rdbms/install/cus_rdbms.mk
ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1/owb
include $(ORACLE_HOME)/rdbms/lib/env_rdbms.mk

安装完成后,要建立资料库,可以调用安装目录下owb/unix/reposinst.sh脚本建立资料库和用户,但是在创建过程中会报ora-01919: role ‘JAVAIDPRIV’ does not exist错误,这时需要在目标数据库中重建JVM环境,操作步骤如下:

SQL> @?/javavm/install/rmjvm
SQL> shutdown immediate
SQL> startup
SQL> @?/javavm/install/initjvm
SQL> @?/xdk/admin/initxml
SQL> @?/xdk/admin/xmlja
SQL> @?/rdbms/admin/catjava

虽然前面创建资料库失败,但是用户和一些角色已经创建在数据库中了,我们可以删除这些用户和OWB开头的角色,类似如下SQL:

select * from dba_users

drop user BIUSER cascade

select * from dba_roles where role like ‘OWB%’

drop roles OWB_BIUSER

然后再重新创建即可成功。

Oracle手动建库的步骤

Oracle除了用DBCA可以建库外,也可以手动来建立数据库,手动建库能够更了解数据库的一些启动过程和运作机制,对深入学习oracle 很有帮助,本文演示了10g下手动建库的流程和可能碰到的问题。

第一步:建立参数文件,这个文件可以从其他地方已有的数据库复制过来修改,也可以从init.ora的基础上修改,主要内容如下:

*.audit_file_dest=’/u01/app/oracle/admin/erpwh/adump’
*.background_dump_dest=’/u01/app/oracle/admin/erpwh/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’+DGWH/erpwh/control01.ctl’,'+DGWH/erpwh/control02.ctl’,'+DGWH/erpwh/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/erpwh/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’erpwh’
*.db_recovery_file_dest=’+DGWH’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=erpoptmXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2147483648
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/erpwh/udump’

可以看到参数文件主要是指定了数据库名、跟踪文件的位置、控制文件的位置、数据块大小、内存参数等。Linux 系统中,该文件位置应在$ORACLE_HOME/dbs目录下。

第二步:建立密码文件,该文件用于sys用户以sysdba身份远程管理登录,这里的密码可以和建立数据库脚本理的sys密码不同,如果是远程以sysdba身份登录则需要使用这里的密码。密码文件和参数文件在相同的目录下:

[oracle@erpdevdb dbs]$ orapwd file=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/orapwerpwh password=erpwh entries=5

第三步:建立跟踪、日志等文件的路径:

[oracle@erpdevdb dbs]$ cd /u01/app/oracle/admin/

[oracle@erpdevdb admin]$ mkdir erpwh
[oracle@erpdevdb admin]$ cd erpwh
[oracle@erpdevdb erpwh]$ ll
total 0
[oracle@erpdevdb erpwh]$ mkdir adump bdump udump cdump
[oracle@erpdevdb erpwh]$ ll
total 32
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 adump
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 bdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 cdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 udump

第四步:启动数据库到nomount状态:

[oracle@erpdevdb dbs]$ export ORACLE_SID=erpwh
[oracle@erpdevdb dbs]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Mar 10 15:18:09 2010

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

Connected to an idle instance.

SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

如果出现以上错误,是操作系统参数设置的内存不够,参见这篇文章修改即可:

[root@erpdevdb ~]# vi /etc/sysctl.conf

kernel.shmall = 4718592

或者调小参数文件里的内存参数也可以,取决于系统资源情况。

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2022144 bytes
Variable Size 486540544 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14753792 bytes

第五步,运行建立数据库的脚本

CREATE DATABASE erpwh
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY sys
LOGFILE GROUP 1 (‘+DGWH/erpwh/redo01.log’) SIZE 100M,
GROUP 2 (‘+DGWH/erpwh/redo02.log’) SIZE 100M,
GROUP 3 (‘+DGWH/erpwh/redo03.log’) SIZE 100M
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘+DGWH/erpwh/system01.dbf’ SIZE 2048M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘+DGWH/erpwh/sysaux01.dbf’ SIZE 1024M REUSE
DEFAULT TEMPORARY TABLESPACE temptbs1
TEMPFILE ‘+DGWH/erpwh/temp01.dbf’
SIZE 2048M REUSE
UNDO TABLESPACE undotbs1
DATAFILE ‘+DGWH/erpwh/undotbs01.dbf’
SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

脚本主要是指定sys和system的密码,redo文件,字符集,数据文件,临时表空间和回滚段等信息。

但有时候在建库脚本里指定默认表空间DEFAULT TABLESPACE erp会报错:

SQL> CREATE DATABASE erpwh
2 USER SYS IDENTIFIED BY sys
3 USER SYSTEM IDENTIFIED BY sys
4 LOGFILE GROUP 1 (‘+DGWH/erpwh/redo01.log’) SIZE 100M,
5 GROUP 2 (‘+DGWH/erpwh/redo02.log’) SIZE 100M,
6 GROUP 3 (‘+DGWH/erpwh/redo03.log’) SIZE 100M
7 MAXLOGFILES 16
8 MAXLOGMEMBERS 5
9 MAXDATAFILES 100
10 MAXINSTANCES 8
11 MAXLOGHISTORY 29200
12 CHARACTER SET ZHS16GBK
13 NATIONAL CHARACTER SET AL16UTF16
14 DATAFILE ‘+DGWH/erpwh/system01.dbf’ SIZE 2048M REUSE
15 EXTENT MANAGEMENT LOCAL
16 SYSAUX DATAFILE ‘+DGWH/erpwh/sysaux01.dbf’ SIZE 1024M REUSE
17 DEFAULT TABLESPACE erp
18 DEFAULT TEMPORARY TABLESPACE temptbs1
19 TEMPFILE ‘+DGWH/erpwh/temp01.dbf’
20 SIZE 2048M REUSE
21 UNDO TABLESPACE undotbs1
22 DATAFILE ‘+DGWH/erpwh/undotbs01.dbf’
23 SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE DATABASE erpwh
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

这种情况一般是建库脚本哪里写的不对,需要检查。从asm存储来看各种数据文件都建立了,但是数据库并没有创建成功,这时把已经建立的数据文件删掉,并删除建库脚本理的默认表空间这行就可以了:

SQL> CREATE DATABASE erpwh
2 USER SYS IDENTIFIED BY sys
3 USER SYSTEM IDENTIFIED BY sys
4 LOGFILE GROUP 1 (‘+DGWH/erpwh/redo01.log’) SIZE 100M,
5 GROUP 2 (‘+DGWH/erpwh/redo02.log’) SIZE 100M,
6 GROUP 3 (‘+DGWH/erpwh/redo03.log’) SIZE 100M
7 MAXLOGFILES 16
8 MAXLOGMEMBERS 5
9 MAXDATAFILES 100
10 MAXINSTANCES 8
11 MAXLOGHISTORY 2
12 CHARACTER SET ZHS16GBK
13 NATIONAL CHARACTER SET AL16UTF16
14 DATAFILE ‘+DGWH/erpwh/system01.dbf’ SIZE 2048M REUSE
15 EXTENT MANAGEMENT LOCAL
16 SYSAUX DATAFILE ‘+DGWH/erpwh/sysaux01.dbf’ SIZE 1024M REUSE
17 DEFAULT TEMPORARY TABLESPACE temptbs1
18 TEMPFILE ‘+DGWH/erpwh/temp01.dbf’
19 SIZE 2048M REUSE
20 UNDO TABLESPACE undotbs1
21 DATAFILE ‘+DGWH/erpwh/undotbs01.dbf’
22 SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

第六步,建立其他表空间和数据字典:

SQL> create tablespace erp datafile ‘+DGWH’ size 30720m;

Tablespace created.

SQL> @?/rdbms/admin/catalog.sql

……

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc.sql

……

PL/SQL procedure successfully completed.

第七步,建立spfile:

SQL> create spfile=’+DGWH/ERPWH/spfileerpwh.ora’ from pfile;

File created.

要注意的是,Oracle下次启动的时候不不会默认使用这种方式建立的spfile,

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string

不指定spfile路径的时候就可以了:

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2022144 bytes
Variable Size 486540544 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/oracle/product
/10.2.0/db_1/dbs/spfileerpwh.o
ra

第八步,建立用户

SQL> create user erp identified by erp default tablespace ERP temporary tablespace temptbs1 profile DEFAULT;

User created.

SQL> grant dba to erp;

Grant succeeded.

SQL> connect erp/erp
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

出现这个错误提示并不会影响数据库的使用,手动建库一般会有这个警告出现。 PRODUCT_USER_PROFILE是SYSTEM用户的一个表,存储客户端程序执行命令方面的限制信息,可以根据提示用system用户执行脚本来消除:

SQL> connect system/sys
Connected.
SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

…….

SQL> connect erp/erp
Connected.
SQL>
SQL>
SQL> show user
USER is “ERP”

第九步,配置网络服务:

[oracle@erpdevdb admin]$ vi tnsnames.ora

ERPWH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = erpwh)
)
)

至此手动建立数据库完成,客户端可以连接上来使用了。

如何快速克隆一个Oracle数据库

有时候为了测试方便,需要建立一个和源库一样的数据库来操作,这时候一个简单的做法是直接克隆,通过配置参数文件和直接复制数据文件来快速建立一个新的库,本文演示了基于10g版本ASM存储下的一个操作过程。

源库SID:erptest    目标库SID:erpoptm

第一步,首先获取源库的参数文件

SQL> CREATE PFILE=’/tmp/init_temp.ora’ from spfile;

File created.

参照以上文件建立一个目标库的参数文件initerpoptm.ora如下:

erptest.__db_cache_size=1828716544
erptest.__java_pool_size=16777216
erptest.__large_pool_size=16777216
erptest.__shared_pool_size=268435456
erptest.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/erpoptm/adump’
*.background_dump_dest=’/u01/app/oracle/admin/erpoptm/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’+DGOPTM/erpoptm/control01.ctl’,'+DGOPTM/erpoptm/control02.ctl’,'+DGOPTM/erpoptm/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/erpoptm/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’erptest’
*.db_recovery_file_dest=’+DGOPTM’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=erpoptmXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2147483648
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS2′
*.user_dump_dest=’/u01/app/oracle/admin/erpoptm/udump’

改动的内容都是各种文件的路径,注意这里的db_name不要修改,否则无法mount数据库,因为db_name要和数据文件头记录的db_name一致。

第二步,建立对应的日志、跟踪等文件的路径

[oracle@erpdevdb tmp]$ cd /u01/app/oracle/admin/

[oracle@erpdevdb admin]$ mkdir erpoptm

[oracle@erpdevdb admin]$ cd erpoptm
[oracle@erpdevdb erpoptm]$ ll
total 0
[oracle@erpdevdb erpoptm]$ mkdir adump
[oracle@erpdevdb erpoptm]$ mkdir bdump
[oracle@erpdevdb erpoptm]$ mkdir udump
[oracle@erpdevdb erpoptm]$ mkdir cdump
[oracle@erpdevdb erpoptm]$ ll
total 32
drwxr-xr-x  2 oracle oinstall 4096 Mar  8 11:45 adump
drwxr-xr-x  2 oracle oinstall 4096 Mar  8 11:45 bdump
drwxr-xr-x  2 oracle oinstall 4096 Mar  8 11:45 cdump
drwxr-xr-x  2 oracle oinstall 4096 Mar  8 11:45 udump

第三步,获取源库的控制文件内容并修改

SQL> alter database backup controlfile to trace;

Database altered.

在udump目录下查看生成的trace文件,找到建立控制文件的部分脚本并修改如下:

CREATE CONTROLFILE REUSE DATABASE “ERPTEST” NORESETLOGS  NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 29200
LOGFILE
GROUP 1 ‘+DGOPTM/erpoptm/redo01.log’  SIZE 50M,
GROUP 2 ‘+DGOPTM/erpoptm/redo02.log’  SIZE 50M,
GROUP 3 ‘+DGOPTM/erpoptm/redo03.log’  SIZE 50M
– STANDBY LOGFILE
DATAFILE
‘+DGOPTM/erpoptm/system01.dbf’,
‘+DGOPTM/erpoptm/undotbs01.dbf’,
‘+DGOPTM/erpoptm/sysaux01.dbf’,
‘+DGOPTM/erpoptm/users01.dbf’,
‘+DGOPTM/erpoptm/datafile/erp_ht.268′,
‘+DGOPTM/erpoptm/datafile/erp_ht.269′,
‘+DGOPTM/erpoptm/datafile/erp_index.270′,
‘+DGOPTM/erpoptm/datafile/erp_wl.271′,
‘+DGOPTM/erpoptm/datafile/erp.272′,
‘+DGOPTM/erpoptm/datafile/erp_sp.273′,
‘+DGOPTM/erpoptm/datafile/erp_cw.274′,
‘+DGOPTM/erpoptm/datafile/erp_zj.275′,
‘+DGOPTM/erpoptm/datafile/erp_xm.276′,
‘+DGOPTM/erpoptm/datafile/erp_ht.277′
CHARACTER SET ZHS16GBK

第四步,拷贝源库的数据文件到目标库的对应路径:

先要为目标库建立好ASM磁盘组,建立的详细步骤可参考这篇文章

[root@erpdevdb ~]# oracleasm createdisk VOL5 /dev/sda10
Writing disk header: done
Instantiating disk: done

[oracle@erpdevdb ~]$ export ORACLE_SID=+ASM

SQL> create diskgroup DGOPTM external redundancy disk ‘ORCL:VOL5′;

Diskgroup created.

[oracle@erpdevdb ~]$ asmcmd
ASMCMD> ls
DGDEV/
DGOPTM/
DGPAPER/
DGSTUDY/
DGTEST/
ASMCMD> cd DGOPTM/

ASMCMD> mkdir ERPOPTM/

然后在源库的实例中用DBMS_FILE_TRANSFER.COPY_FILE包拷贝文件:

SQL> create directory DGFROM as ‘+DGTEST/ERPTEST/DATAFILE’;

Directory created.

SQL> create directory DGTO as ‘+DGOPTM/ERPOPTM/DATAFILE’;

Directory created.

执行以下命令拷贝数据文件:

exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_ht.268.711907283′,’DGTO’,'erp_ht.268′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_ht.269.711907397′,’DGTO’,'erp_ht.269′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_ht.277.711972029′,’DGTO’,'erp_ht.277′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_index.270.711907487′,’DGTO’,'erp_index.270′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_wl.271.711907545′,’DGTO’,'erp_wl.271′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp.272.711907565′,’DGTO’,'erp.272′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_sp.273.711907695′,’DGTO’,'erp_sp.273′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_cw.274.711907757′,’DGTO’,'erp_cw.274′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_xm.276.711907831′,’DGTO’,'erp_xm.276′);
exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'erp_zj.275.711907801′,’DGTO’,'erp_zj.275′);

要注意的是,这里的目标文件名不能带有ASM自身生成的那一串数字,否则会报错如下:

ORA-19504: failed to create file
“+DGOPTM/ERPOPTM/DATAFILE/erp_zj.275.711907801″
ORA-17502: ksfdcre:4 Failed to create file
+DGOPTM/ERPOPTM/DATAFILE/erp_zj.275.711907801
ORA-15046: ASM file name ‘+DGOPTM/ERPOPTM/DATAFILE/erp_zj.275.711907801′ is not
in single-file creation form
ORA-06512: at “SYS.DBMS_FILE_TRANSFER”, line 84
ORA-06512: at “SYS.DBMS_FILE_TRANSFER”, line 193
ORA-06512: at line 1

这是因为这串数字是ASM用来进行标识的信息,文档Doc ID: Note:452158.1中有说明。老杨的一篇文章描述了类似的问题。

然后再拷贝system等表空间和redo等文件:

SQL> drop directory DGFROM;

Directory dropped.

SQL> create directory DGFROM as ‘+DGTEST/ERPTEST’;

Directory created.

SQL> drop directory DGTO;

Directory dropped.

SQL> create directory DGTO as ‘+DGOPTM/ERPOPTM’;

Directory created.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'redo01.log’,'DGTO’,'redo01.log’);

PL/SQL procedure successfully completed.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'redo02.log’,'DGTO’,'redo02.log’);

PL/SQL procedure successfully completed.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'redo03.log’,'DGTO’,'redo03.log’);

PL/SQL procedure successfully completed.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'system01.dbf’,'DGTO’,'system01.dbf’);

PL/SQL procedure successfully completed.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'undotbs01.dbf’,'DGTO’,'undotbs01.dbf’);

PL/SQL procedure successfully completed.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'sysaux01.dbf’,'DGTO’,'sysaux01.dbf’);

PL/SQL procedure successfully completed.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE(‘DGFROM’,'users01.dbf’,'DGTO’,'users01.dbf’);

PL/SQL procedure successfully completed.

需要注意的是,copy过来的文件并不是文件存储的实际路径,而是按原有的路径组织的,新的文件名只是一个alias而已:

ASMCMD> cd ERPOPTM/
ASMCMD> ls -l
Type         Redund  Striped  Time             Sys  Name
N    DATAFILE/
N    control01.ctl => +DGOPTM/ERPTEST/CONTROLFILE/Current.273.713177855
N    control02.ctl => +DGOPTM/ERPTEST/CONTROLFILE/Current.274.713177855
N    control03.ctl => +DGOPTM/ERPTEST/CONTROLFILE/Current.275.713177855
N    redo01.log => +DGOPTM/ERPTEST/ONLINELOG/group_0.266.713120427
N    redo02.log => +DGOPTM/ERPTEST/ONLINELOG/group_0.267.713120435
N    redo03.log => +DGOPTM/ERPTEST/ONLINELOG/group_0.268.713120441
N    sysaux01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.271.713120497
N    system01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.269.713120449
N    undotbs01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.270.713120467
N    users01.dbf => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.272.713120509

ASMCMD> cd DATAFILE/
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
N    erp.272 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.261.713119325
N    erp_cw.274 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.263.713119613
N    erp_ht.268 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.257.713118643
N    erp_ht.269 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.256.713118843
N    erp_ht.277 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.258.713119001
N    erp_index.270 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.259.713119135
N    erp_sp.273 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.262.713119553
N    erp_wl.271 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.260.713119275
N    erp_xm.276 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.264.713119665
N    erp_zj.275 => +DGOPTM/ERPTEST/DATAFILE/COPY_FILE.265.713119693

至此文件拷贝完成。

第五步,建立密码文件

[oracle@erpdevdb dbs]$ orapwd file=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/orapwerpoptm password=erpoptm entries=5

第六步,启动数据库

先启动到nomount状态,这时会用到参数文件:

[oracle@erpdevdb dbs]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 8 14:01:08 2010

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2022144 bytes
Variable Size             486540544 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14753792 bytes
SQL>
SQL> alter database mount
2  /
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

oracle会先找spfile,然后找pfile,找若没有参数文件,则会报错:

[oracle@erpdevdb dbs]$ export ORACLE_SID=erpoptm
[oracle@erpdevdb dbs]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Mar 8 14:00:10 2010

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

Connected to an idle instance.

SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/initerpoptm.ora’

mount时会找控制文件,没有则会出现如上所示的错误,可以在nomount阶段来建立控制文件,先关闭源库,然后在目标库启动到nomount阶段来建立控制文件:

SQL> CREATE CONTROLFILE REUSE DATABASE “ERPTEST” NORESETLOGS  NOARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 29200
7  LOGFILE
8    GROUP 1 ‘+DGOPTM/erpoptm/redo01.log’  SIZE 50M,
9    GROUP 2 ‘+DGOPTM/erpoptm/redo02.log’  SIZE 50M,
10    GROUP 3 ‘+DGOPTM/erpoptm/redo03.log’  SIZE 50M
11  — STANDBY LOGFILE
12  DATAFILE
13    ‘+DGOPTM/erpoptm/system01.dbf’,
14    ‘+DGOPTM/erpoptm/undotbs01.dbf’,
15    ‘+DGOPTM/erpoptm/sysaux01.dbf’,
16    ‘+DGOPTM/erpoptm/users01.dbf’,
17    ‘+DGOPTM/erpoptm/datafile/erp_ht.268′,
18    ‘+DGOPTM/erpoptm/datafile/erp_ht.269′,
19    ‘+DGOPTM/erpoptm/datafile/erp_index.270′,
20    ‘+DGOPTM/erpoptm/datafile/erp_wl.271′,
21    ‘+DGOPTM/erpoptm/datafile/erp.272′,
22    ‘+DGOPTM/erpoptm/datafile/erp_sp.273′,
23    ‘+DGOPTM/erpoptm/datafile/erp_cw.274′,
24    ‘+DGOPTM/erpoptm/datafile/erp_zj.275′,
25    ‘+DGOPTM/erpoptm/datafile/erp_xm.276′,
26    ‘+DGOPTM/erpoptm/datafile/erp_ht.277′
27  CHARACTER SET ZHS16GBK
28  /

Control file created.

为什么要先关闭源库呢,这是因为二者有相同的db_name,oracle通过一个lk开头的文件来在运行阶段lock某个库,如果不关闭会在alert日志中看到以下错误:

Tue Mar  9 08:53:20 2010
sculkget: failed to lock /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/lkERPTEST exclusive
sculkget: lock held by PID: 29747
Tue Mar  9 08:53:21 2010
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 29747
Tue Mar  9 08:53:21 2010
Errors in file /u01/app/oracle/admin/erpoptm/udump/erpoptm_ora_26143.trc:
ORA-01158: database  already mounted

由于现在在同一个主机上有两个相同db_name的数据库,那么如何能同时启动呢,这需要指定参数db_unique_ name来区分开来,这个参数在standby环境也是必须设置的。

修改源库的db_unique_name如下:

SQL> alter system set db_unique_name=erptest scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

回到正题,在创建控制文件的过程中会验证数据文件是否存在,如果不存在则报以下错误:

ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file ‘+DGOPTM/erpoptm/system01.dbf’
ORA-17503: ksfdopn:2 Failed to open file +DGOPTM/erpoptm/system01.dbf
ORA-15173: entry ‘system01.dbf’ does not exist in directory ‘erpoptm’

也会检查参数文件中的db_name和数据文件头的db_name是否相同,否则报以下错误:

ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ERPTEST in file header does not match given name of
ERPOPTM
ORA-01110: data file 1: ‘+DGOPTM/erpoptm/system01.dbf’

接着打开数据库:

SQL> alter database open
2  /
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘+DGOPTM/erpoptm/system01.dbf’

这说明需要介质恢复,可以一个一个数据文件的来recover,也可以直接recover database:

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      erptest
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
instance_name                        string      erpoptm

注意这种情况下启动的数据库会做实例恢复,因为redo和数据文件等都是copy过来的。这样就建立了一个db_name为erptest,而instance_name和SID为erpoptm的数据库。

然后要马上建立临时表空间:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGOPTM/erpoptm/temp01.dbf’ SIZE 8192M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

但是事情并没有结束,很快就发些这个新搭建的库会很快死掉,alert日志记录了如下的信息:

Tue Mar  9 10:11:24 2010
Errors in file /u01/app/oracle/admin/erpoptm/bdump/erpoptm_mmon_30564.trc:
ORA-00600: internal error code, arguments: [504], [0x06000AD18], [1], [0], [ksv instance], [0], [0], [0x0DCFCE590]
Tue Mar  9 10:11:26 2010
Errors in file /u01/app/oracle/admin/erpoptm/bdump/erpoptm_mmon_30564.trc:
ORA-00600: internal error code, arguments: [504], [0x06000AD18], [1], [0], [ksv instance], [0], [0], [0x0DCFCE590]
Tue Mar  9 10:11:26 2010
Errors in file /u01/app/oracle/admin/erpoptm/bdump/erpoptm_mmon_30564.trc:
ORA-00600: internal error code, arguments: [4193], [2887], [2890], [], [], [], [], []

碰到很多4193的600错误,这种一般和undo有关,重建一下undo即可:

SQL> show parameter  undo

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace undotbs2 datafile ‘+DGOPTM/ERPOPTM/undotbs2.dbf’ size 1024m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

再修改一下参数文件initerpoptm.ora,修改这里的undo:

*.undo_tablespace=’UNDOTBS2′

重新启动数据库,设置db_unique_name和spfile:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL>
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate

SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /u01/app/oracle/oracle/product
/10.2.0/db_1/dbs/spfileerpoptm
.ora
SQL> alter system set db_unique_name=erpoptm scope=spfile;

System altered.

SQL> shutdown immediate
SQL> startup
SQL>
SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      erptest
SQL>
SQL>
SQL>
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
instance_name                        string      erpoptm
SQL>
SQL>
SQL>
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      ERPOPTM

可以看到spfile默认会放到dbs目录下。至此这个数据库应该是可用了,当然还要配置一下网络监听等服务:

[oracle@erpdevdb admin]$ vi tnsnames.ora

ERPOPTM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = erpoptm)
)
)

[oracle@erpdevdb admin]$ lsnrctl

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               spawn
change_password     quit                exit
set*                show*

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

[oracle@erpdevdb admin]$ vi /etc/oratab

erpoptm:/u01/app/oracle/oracle/product/10.2.0/db_1:Y

Oracle中如何建立触发器

Oracle 中建立触发器的语法如下,图片引自Oracle网站:

其中DML Event Clause为:

其中referencing_clause为:

一般来说建立after的触发器效率比较高,为了提高效率,我们应该细化限制触发的条件,比如指定字段和值的范围才触发:

CREATE TRIGGER hr.salary_check

BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees

FOR EACH ROW

WHEN (new.job_id <> ‘AD_VP’)

pl/sql_block ……

触发器和引起触发的SQL是同一个事务,除非用PRAGMA AUTONOMOUS_TRANSACTION参数指定自治事务,但可能会出现数据结果不一致的问题。

rac环境下禁用oracle实例对vip的依赖

为了保证RAC环境下如果一个节点出问题,客户端能漂移到其他节点,Oracle引入了VIP的配置。但在个别版本中,实例对VIP的依赖却可能因为VIP出现问题导致实例崩溃。从10203开始可以手动取消实例对VIP的依赖,而10204已经取消了这个依赖关系。

以下信息是数据库实例crash的记录:

Sun Feb 14 20:07:18 2010
Shutting down instance (abort)
License high water mark = 381
Instance terminated by USER, pid = 619164

取消实例依赖VIP的方法是导出实例部分的资源配置,修改后再注册回去。步骤如下:

DB1@/home/oracle>crs_stat -p ora.racdb.rac1.inst >> /tmp/ora.racdb.rac1.inst.cap

然后编辑这个文件,去掉对vip的依赖:

DB1@/home/oracle>vi /tmp/ora.racdb.rac1.inst.cap

REQUIRED_RESOURCES= 这里设为空

然后再注册回去:

DB1@/home/oracle>crs_register -u ora.racdb.rac1.inst -dir /tmp

所有的节点都需要修改,再用crs_stat -p命令查看已经生效了。如果RAC环境中断网引起的实例崩溃,很可能就是这个问题。

在Linux上创建ASM存储分区

本文描述了在Linux 服务器上创建分区、建立oracle ASM存储和创建数据库实例的过程。

查看当前分区:

[root@erpdevdb ~]# fdisk -l

Disk /dev/sda: 1048.5 GB, 1048576000000 bytes
255 heads, 63 sectors/track, 127482 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          65      522081   83  Linux
/dev/sda2              66        2676    20972857+  8e  Linux LVM
/dev/sda3            2677        5287    20972857+  8e  Linux LVM
/dev/sda4            5288      127482   981531337+   5  Extended
/dev/sda5            5288        7898    20972826   8e  Linux LVM
/dev/sda6            7899       20400   100422283+  8e  Linux LVM
/dev/sda7           20401       32850   100004593+  8e  Linux LVM
/dev/sda8           32851       57750   200009218+  8e  Linux LVM

Disk /dev/sdb: 1198.8 GB, 1198899855360 bytes
255 heads, 63 sectors/track, 145757 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1       37349   300005811   83  Linux
/dev/sdb2           37350       74698   300005842+  83  Linux
/dev/sdb3           74699      136946   500007060   83  Linux

在/dev/sda设备上创建一个新的分区:

[root@erpdevdb ~]# fdisk /dev/sda

The number of cylinders for this disk is set to 127482.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition’s system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): p

Disk /dev/sda: 1048.5 GB, 1048576000000 bytes
255 heads, 63 sectors/track, 127482 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          65      522081   83  Linux
/dev/sda2              66        2676    20972857+  8e  Linux LVM
/dev/sda3            2677        5287    20972857+  8e  Linux LVM
/dev/sda4            5288      127482   981531337+   5  Extended
/dev/sda5            5288        7898    20972826   8e  Linux LVM
/dev/sda6            7899       20400   100422283+  8e  Linux LVM
/dev/sda7           20401       32850   100004593+  8e  Linux LVM
/dev/sda8           32851       57750   200009218+  8e  Linux LVM

Command (m for help): n
First cylinder (57751-127482, default 57751):
Using default value 57751
Last cylinder or +size or +sizeM or +sizeK (57751-127482, default 127482): +300000M

Command (m for help): p

Disk /dev/sda: 1048.5 GB, 1048576000000 bytes
255 heads, 63 sectors/track, 127482 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          65      522081   83  Linux
/dev/sda2              66        2676    20972857+  8e  Linux LVM
/dev/sda3            2677        5287    20972857+  8e  Linux LVM
/dev/sda4            5288      127482   981531337+   5  Extended
/dev/sda5            5288        7898    20972826   8e  Linux LVM
/dev/sda6            7899       20400   100422283+  8e  Linux LVM
/dev/sda7           20401       32850   100004593+  8e  Linux LVM
/dev/sda8           32851       57750   200009218+  8e  Linux LVM
/dev/sda9           57751       94224   292977373+  83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@erpdevdb ~]#

这时能看到新的分区:

[root@erpdevdb ~]# fdisk -l

Disk /dev/sda: 1048.5 GB, 1048576000000 bytes
255 heads, 63 sectors/track, 127482 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          65      522081   83  Linux
/dev/sda2              66        2676    20972857+  8e  Linux LVM
/dev/sda3            2677        5287    20972857+  8e  Linux LVM
/dev/sda4            5288      127482   981531337+   5  Extended
/dev/sda5            5288        7898    20972826   8e  Linux LVM
/dev/sda6            7899       20400   100422283+  8e  Linux LVM
/dev/sda7           20401       32850   100004593+  8e  Linux LVM
/dev/sda8           32851       57750   200009218+  8e  Linux LVM
/dev/sda9           57751       94224   292977373+  83  Linux
/dev/sda10          94225      127482   267144853+  83  Linux

Disk /dev/sdb: 1198.8 GB, 1198899855360 bytes
255 heads, 63 sectors/track, 145757 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1       37349   300005811   83  Linux
/dev/sdb2           37350       74698   300005842+  83  Linux
/dev/sdb3           74699      136946   500007060   83  Linux
/dev/sdb4          136947      145757    70774357+  83  Linux

查看当前的ASM磁盘组:

[root@erpdevdb ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3

这时创建新的ASM磁盘组会报错,正如前面创建分区保存时提示的,需要重启后才生效。

[root@erpdevdb ~]# /etc/init.d/oracleasm createdisk VOL4 /dev/sda9
Marking disk “VOL4″ as an ASM disk: [FAILED]

关闭已有的oracle 实例和ASM实例:

[oracle@erpdevdb ~]$ export ORACLE_SID=devdb
[oracle@erpdevdb ~]$ sqlplus ” / as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Feb 25 12:52:48 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

[oracle@erpdevdb ~]$ export ORACLE_SID=+ASM
[oracle@erpdevdb ~]$ sqlplus ” /  as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Feb 25 12:57:24 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

关闭listener :

[oracle@erpdevdb ~]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-FEB-2010 12:58:32

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> stat   
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date                29-NOV-2009 13:13:31
Uptime                    87 days 23 hr. 45 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdevdb.chinacnd.com)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
LSNRCTL> exit

重启服务器:

[root@erpdevdb ~]# reboot

Broadcast message from root (pts/1) (Thu Feb 25 12:59:17 2010):

The system is going down for reboot NOW!

重启后启动Listerner:

[root@erpdevdb ~]# su – oracle
[oracle@erpdevdb ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-FEB-2010 13:03:58

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdevdb.chinacnd.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date                25-FEB-2010 13:03:58
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdevdb.chinacnd.com)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@erpdevdb ~]$

启动Asm实例:

[oracle@erpdevdb ~]$ export ORACLE_SID=+ASM
[oracle@erpdevdb ~]$ sqlplus  “/  as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Feb 25 13:04:17 2010

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

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  130023424 bytes
Fixed Size                  2019032 bytes
Variable Size             102838568 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

启动其他数据库实例:

[oracle@erpdevdb ~]$ export ORACLE_SID=devdb
[oracle@erpdevdb ~]$ sqlplus  “/  as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Feb 25 13:04:40 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2026296 bytes
Variable Size             285213896 bytes
Database Buffers         3992977408 bytes
Redo Buffers               14749696 bytes
Database mounted.
Database opened.
SQL> exit

创建Asm设备:

[root@erpdevdb ~]# /etc/init.d/oracleasm createdisk VOL4 /dev/sda9
Marking disk “VOL4″ as an ASM disk: [  OK  ]
[root@erpdevdb ~]#
[root@erpdevdb ~]#
[root@erpdevdb ~]#
[root@erpdevdb ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4

创建ASM磁盘组:

[root@erpdevdb ~]# su – oracle
[oracle@erpdevdb ~]$ export ORACLE_SID=+ASM
[oracle@erpdevdb ~]$ sqlplus  “/ as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Feb 25 13:52:44 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL>
SQL> create diskgroup DGTEST external redundancy disk ‘ORCL:VOL4′;

Diskgroup created.

SQL>
SQL> select name,group_number,disk_number,mode_status from v$asm_disk;

NAME                           GROUP_NUMBER DISK_NUMBER MODE_ST
—————————— ———— ———– ——-
VOL1                                      3           0 ONLINE
VOL2                                      1           0 ONLINE
VOL3                                      2           0 ONLINE
VOL4                                      4           0 ONLINE

SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB
———— —————————— ———- ———-
           1 DGDEV                              292974     151815
           2 DGPAPER                            488288     328379
           3 DGSTUDY                            292974     168839
           4 DGTEST                             286110     286058

SQL> exit

然后通过dbca工具创建新的实例,在创建过程中存储选择这里新建的DGTEST磁盘组即可:

asm

年底事故多发-ORA-00600之kcbz_check_objd_typ_3

最近给小型机加了内存,调整了SGA参数,以应付明年到来的重压,昨天在alert日志发现了600错误:

Mon Feb  8 10:00:54 2010
Errors in file /u01/admin/erpdb/bdump/erpdb2_m001_598774.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Mon Feb  8 10:00:58 2010
Trace dumping is performing id=[cdmp_20100208100058]

检查trace文件,发现了执行的SQL语句如下:

*** 2010-02-08 10:00:54.825
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Current SQL statement for this session:

SELECT count(*) over() as total_count,
sd_xe_ash_nm.event_name,
sd_xe_ash_nm.event_id,
sd_xe_ash_nm.parameter1 as p1text,
(CASE
WHEN (sd_xe_ash_nm.parameter1 is NULL OR
sd_xe_ash_nm.parameter1 = ’0′) THEN
0
ELSE
1
END) as p1valid,
sd_xe_ash_nm.parameter2 as p2text,
(CASE
WHEN (sd_xe_ash_nm.parameter2 is NULL OR
sd_xe_ash_nm.parameter2 = ’0′) THEN
0
ELSE
1
END) as p2valid,
sd_xe_ash_nm.parameter3 as p3text,
(CASE
WHEN (sd_xe_ash_nm.parameter3 is NULL OR
sd_xe_ash_nm.parameter3 = ’0′) THEN
0
ELSE
1
END) as p3valid,
sd_xe_ash_nm.keh_evt_id,
nvl(xc.class#, 0) as class_num,
sd_xe_ash_nm.wait_class_id,
nvl(xc.keh_id, 0) as keh_ecl_id,
sd_xe_ash_nm.ash_cnt,
sd_xe_ash_nm.tot_wts_diff,
sd_xe_ash_nm.tot_tmo_diff,
sd_xe_ash_nm.tim_wait_diff
FROM (SELECT sd_xe_ash.*,
evtname.event_name,
evtname.wait_class_id,
evtname.parameter1,
evtname.parameter2,
evtname.parameter3
FROM (SELECT sd_xe.*, nvl(ash.cnt, 0) as ash_cnt
FROM (SELECT nvl(xe.keh_id, 0) as keh_evt_id,
nvl(sd.event_id, xe.event_hash) as event_id,
nvl(sd.tot_wts_diff, 0) as tot_wts_diff,
nvl(sd.tot_tmo_diff, 0) as tot_tmo_diff,
nvl(sd.tim_wait_diff, 0) as tim_wait_diff
FROM (SELECT endsn.event_id as event_id,
(endsn.total_waits -
nvl(begsn.total_waits, 0)) as tot_wts_diff,
(endsn.total_timeouts -
nvl(begsn.total_timeouts, 0)) as tot_tmo_diff,
(endsn.time_waited_micro -
nvl(begsn.time_waited_micro, 0)) as tim_wait_diff
FROM (SELECT end_snap.*
FROM (SELECT t1.*
FROM WRH$_SYSTEM_EVENT t1,
WRM$_SNAPSHOT     s1
WHERE t1.dbid = s1.dbid
AND t1.instance_number =
s1.instance_number
AND t1.snap_id = s1.snap_id
AND s1.bl_moved = 0
UNION ALL
SELECT t2.*
FROM WRH$_SYSTEM_EVENT_BL t2,
WRM$_SNAPSHOT        s2
WHERE t2.dbid = s2.dbid
AND t2.instance_number =
s2.instance_number
AND t2.snap_id = s2.snap_id
AND s2.bl_moved <> 0) end_snap
WHERE end_snap.dbid = :dbid
and end_snap.instance_number =
:instance_number
and end_snap.snap_id = :end_snap) endsn
LEFT OUTER JOIN (SELECT beg_snap.*
FROM (SELECT t1.*
FROM WRH$_SYSTEM_EVENT t1,
WRM$_SNAPSHOT     s1
WHERE t1.dbid =
s1.dbid
AND t1.instance_number =
s1.instance_number
AND t1.snap_id =
s1.snap_id
AND s1.bl_moved = 0
UNION ALL
SELECT t2.*
FROM WRH$_SYSTEM_EVENT_BL t2,
WRM$_SNAPSHOT        s2
WHERE t2.dbid =
s2.dbid
AND t2.instance_number =
s2.instance_number
AND t2.snap_id =
s2.snap_id
AND s2.bl_moved <> 0) beg_snap
WHERE beg_snap.dbid = :dbid
and beg_snap.instance_number =
:instance_number
and beg_snap.snap_id =
:beg_snap) begsn ON endsn.event_id =
begsn.event_id) sd
FULL OUTER JOIN X$KEHEVTMAP xe ON sd.event_id =
xe.event_hash) sd_xe
LEFT OUTER JOIN (SELECT a.event_id, count(*) as cnt
FROM (SELECT t1.*
FROM WRH$_ACTIVE_SESSION_HISTORY t1,
WRM$_SNAPSHOT               s1
WHERE t1.dbid = s1.dbid
AND t1.instance_number =
s1.instance_number
AND t1.snap_id = s1.snap_id
AND s1.bl_moved = 0
UNION ALL
SELECT t2.*
FROM WRH$_ACTIVE_SESSION_HISTORY_BL t2,
WRM$_SNAPSHOT                  s2
WHERE t2.dbid = s2.dbid
AND t2.instance_number =
s2.instance_number
AND t2.snap_id = s2.snap_id
AND s2.bl_moved <> 0) a
WHERE a.dbid = :dbid
and a.instance_number = :instance_number
and a.snap_id > :beg_snap
and a.snap_id <= :end_snap
and a.wait_time = 0
GROUP BY a.event_id) ash ON sd_xe.event_id =
ash.event_id) sd_xe_ash,
WRH$_EVENT_NAME evtname
WHERE evtname.event_id = sd_xe_ash.event_id
and evtname.event_id > 0
and evtname.dbid = :dbid) sd_xe_ash_nm,
X$KEHECLMAP xc
WHERE sd_xe_ash_nm.wait_class_id = xc.class_hash
ORDER BY sd_xe_ash_nm.wait_class_id,
sd_xe_ash_nm.tim_wait_diff DESC,
sd_xe_ash_nm.event_id

到了晚上,收集统计信息的时候日志再次出现提示:

Mon Feb  8 22:05:29 2010
Errors in file /u01/admin/erpdb/bdump/erpdb2_j000_270930.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [2], [0], [7], [], [], [], []
Mon Feb  8 22:05:32 2010
Trace dumping is performing id=[cdmp_20100208220532]
Mon Feb  8 22:05:36 2010
Errors in file /u01/admin/erpdb/bdump/erpdb2_j000_270930.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [2], [0], [7], [], [], [], []
ORA-06512: at “SYS.DBMS_STATS”, line 19089
], [], [], [], [], [], [], []

而从trace文件查看这次是执行了以下SQL语句:

insert /*+ append */
into sys.ora_temp_2_ds_1073343
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
“PLSQL_ENTRY_OBJECT_ID”,
“PLSQL_ENTRY_SUBPROGRAM_ID”,
“PLSQL_OBJECT_ID”,
“PLSQL_SUBPROGRAM_ID”,
“SNAP_ID”,
“DBID”,
“INSTANCE_NUMBER”,
“SAMPLE_ID”,
“SAMPLE_TIME”,
“SESSION_ID”,
“SESSION_SERIAL#”,
“USER_ID”,
“SQL_ID”,
“SQL_CHILD_NUMBER”,
“SQL_PLAN_HASH_VALUE”,
“SERVICE_HASH”,
“SESSION_TYPE”,
“SQL_OPCODE”,
“QC_SESSION_ID”,
“QC_INSTANCE_ID”,
“CURRENT_OBJ#”,
“CURRENT_FILE#”,
“CURRENT_BLOCK#”,
“SEQ#”,
“EVENT_ID”,
“P1″,
“P2″,
“P3″,
“WAIT_TIME”,
“TIME_WAITED”,
“PROGRAM”,
“MODULE”,
“ACTION”,
“CLIENT_ID”,
“FORCE_MATCHING_SIGNATURE”,
“BLOCKING_SESSION”,
“BLOCKING_SESSION_SERIAL#”,
“XID”
from “SYS”.”WRH$_ACTIVE_SESSION_HISTORY” sample(6.1241078288) t

从metalink查看这个错误可能是以下原因:

一、并行查询导致的,解决办法就是改用串行查询

Bug# 6405339   See Note:6405339.8
Corruption / OERI[kcbz_check_objd_typ_3] / OERI:5400 / OERI:5357 from PQ
Fixed: 10.2.0.5, 11.1.0.7, 11.2

Bug 6405339  Corruption / OERI[kcbz_check_objd_typ_3] / OERI:5400 / OERI:5357 from PQ
A parallel query which uses TEMP segments can suffer corruption
within temporary segments between the slaves leading to various ORA-600
errors like:

ORA-600 [kcbz_check_objd_typ_3]
ORA-600 [5400]
ORA-600 [5357]

Workaround
Run the query serially

二、基于SCN的恢复之后

Bug# 5689412   See Note:5689412.8
ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery
Fixed: 10.2.0.5

Bug 5689412  ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery

ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery to SCN just before
a TRUNCATE operation.

eg:
On primary
create table y (d date, v varchar2(30));
insert into y values (SYSDATE, ‘test1′);
insert into y values (SYSDATE, ‘test2′);
truncate table y;

Recover the standby to a point just prior to the
truncate to recover this specific table. (Detemine
the SCN by mining the redo)

recover standby database until change <SCN>;
-> media recovery completes successfully

alter database open read only;
select * from y;
^
ORA-600 [kcbz_check_objd_typ_3]

三、using a multi-table insert SQL with direct load operations

Bug# 4592596   See Note:4592596.8
Corruption (ORA-1410 / ORA-8103) from multi-table insert with direct load
Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.2.P01, 10.2.0.2.P08, 10.2.0.3.P03
Bug 4592596  Corruption (ORA-1410 / ORA-8103) from multi-table insert with direct load

Corruption can occur using a multi-table insert SQL with
direct load operations. Eg: If the SQL goes parallel.

This can result in subsequent ORA-1410 / ORA-8103 type errors on selects
from the target table/s.

In the case of an internal error like ORA-600 [kcbz_check_objd_typ_3] the
trace file may contain a redo dump with OP:19.1 for the affected object:

OP:19.1 Direct Loader block redo entry

It may affect AWR tables as some of them are populated with Direct Load.

四、Segment advisor带来的buffer坏块,这应该也是本案例的原因所在,可以禁用AUTO_SPACE_ADVISOR_JOB并清空buffer来解决。

Bug# 4430244 +  See Note:4430244.8
Segment advisor can load blocks of dropped objects into buffer cache (KCB OERI errors)
Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.2.P11, 10.2.0.3.P11
Bug 4430244  Segment advisor can load blocks of dropped objects into buffer cache (KCB OERI errors)

Segment advisor code (eg: DBMS_SPACE.OBJECT_GROWTH_TREND)
can load blocks into the cache for DROPped objects as
CURRENT leading to subsequent operations seeing an incorrect
(old) version of a block.
This can lead to various internal buffer cache related
errors such as ORA-600 [kcbnew_3] / ORA-600 [kcbz_check_objd_typ_3].
The exact error depends on which code sees the block.

eg:
If the segment advisor decides to scan table USER1.TAB1
then this table is concurrently dropped then the advisor
may read blocks for the dropped object into the cache as
“CURRENT” block images.
A seperate session may do a direct load of a block from
the dropped object so the block now belongs to USER2.TAB2.
A subsequent select from USER2.TAB2 can see the old
USER1.TAB1 block in the cache so fails.

Workaround
Do not use segment advisor to prevent the problem occurring.
eg:  connect / as sysdba;
exec dbms_scheduler.disable(‘AUTO_SPACE_ADVISOR_JOB’);

Use “alter system flush buffer_cache” to clear bad blocks
from the cache (In RAC the block may have sourced from a
different instance so you need to flush all buffer caches).

另外一个Bug 和这个类似,解决办法相同:5218905  OERI[kcbnew_3] when segment advisor has been used。