about partiton column with date or varchar2？

最近有一个需求需要了解关于分区列字段的选择的问题，下面进行了简单的测试： [crayon-67d91a0833 […]

Tags: date, partition, varchar
Posted in mangement on 7月 13, 2012 | There are currently No Comments

About recreate controlfile ?

上午群里网友讨论了一下关于controlfile重建的问题，曾经的一次恢复(记一次非归档RAC恢复(大量的OR […]

Tags: recreate controlfile, x$kcvfh
Posted in backup & rcovery on 7月 11, 2012 | There are currently No Comments

dml with lmode=6 ?

今天跟09年的一个同事聊天，他去年去原厂了，问他在干吗，他说在处理一个客户的故障，说是dml操作产生TM锁， […]

Tags: tm lmode=6
Posted in mangement on 7月 7, 2012 | There are currently No Comments

sql execution plan in heap 6?

今天在看Jonathan Lewis大师的oracle core第194页时，Jonathan Lewis提到 […]

Posted in mangement on 7月 4, 2012 | There are currently No Comments

soft parse 和 library cache lock

同事遇到一个library cache lock lath等待的问题，导致cpu使用非常高，关于 librar […]

Tags: library cache lock, library cache pin
Posted in oracle internal on 6月 30, 2012 | There are currently 1 Comment

10g中distinct加强以及anti jion,semi jion

SQL> conn roger/roger
Connected.

SQL> create table t1 as select * from dba_objects where object_id < 30001; 

Table created. 

SQL> create table t2 as select * from dba_objects where object_id < 20001; 

Table created. 
SQL>
SQL> set lines 160
SQL> set pagesize 50
SQL> set pagesize 50
SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set autot traceonly
SQL> select distinct a.owner from t2 a where not exists
  2  (select b.owner from t1 b where b.owner=a.owner and b.owner='SYS');

7 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4203366459

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     8 |   193   (2)| 00:00:03 |
|   1 |  HASH UNIQUE          |      |     1 |     8 |   193   (2)| 00:00:03 |
|*  2 |   HASH JOIN RIGHT ANTI|      | 17022 |   132K|   192   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL  | T1   |  3682 | 14728 |   115   (0)| 00:00:02 |
|   4 |    TABLE ACCESS FULL  | T2   | 19454 | 77816 |    76   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - access("B"."OWNER"="A"."OWNER")
   3 - filter("B"."OWNER"='SYS')

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

SQL> conn roger/roger

Connected.

SQL> create table t1 as select * from dba_objects where object_id < 30001;

Table created.

SQL> create table t2 as select * from dba_objects where object_id < 20001;

Table created.

SQL>

SQL> set lines 160

SQL> set pagesize 50

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set autot traceonly

SQL> select distinct a.owner from t2 a where not exists

2 (select b.owner from t1 b where b.owner=a.owner and b.owner='SYS');

7 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4203366459

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 8 | 193 (2)| 00:00:03 |

| 1 | HASH UNIQUE | | 1 | 8 | 193 (2)| 00:00:03 |

|* 2 | HASH JOIN RIGHT ANTI| | 17022 | 132K| 192 (1)| 00:00:03 |

|* 3 | TABLE ACCESS FULL | T1 | 3682 | 14728 | 115 (0)| 00:00:02 |

| 4 | TABLE ACCESS FULL | T2 | 19454 | 77816 | 76 (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("B"."OWNER"="A"."OWNER")

3 - filter("B"."OWNER"='SYS')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

670 consistent gets

0 physical reads

0 redo size

516 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

7 rows processed

我们可以看到此时逻辑读之和为671，没有排序 […]

Tags: anti jion semi jion
Posted in tunning on 6月 20, 2012 | There are currently 2 Comments

redo wastage

今天在看oracle core 第6章时，里面提到了redo wastage的概念，针对该概念，以前也有所了 […]

Tags: redo wastage
Posted in backup & rcovery on 6月 17, 2012 | There are currently 2 Comments

Where is the backup of ASM disk header block? –补充

Kamus在他的博客提到，从Oracle 10.2.0.5开始，ASM磁盘已经开始自动将头块进行备份，备份块 […]

Tags: asm disk header backup
Posted in ASM, backup & rcovery on 6月 13, 2012 | There are currently 1 Comment

About ora-00600 [4400] [48]

上午同事让帮忙分析一个ora-600错误，用UE打开trace，看到如下错误： [crayon-67d91a0 […]

Tags: ora-00600 [4400] [48]
Posted in mangement on 6月 13, 2012 | There are currently No Comments

一次3 node Rac tunning

帮忙简单调整之后，性能提升非常明显，当然其实还有优化的余地，不多说了，直接上前后对比awr数据：总的来说，R […]

Tags: gc buffer busy, gc cr multi block request, gc current block busy
Posted in rac & dg, tunning on 6月 13, 2012 | There are currently 1 Comment

asm(aix) 小记

一、asm(aix) 环境下disk操作注意事项对于asm for aix环境，我们知道，通常是不建议保留p […]

Tags: add/delete ocr, add/delete votedisk, asm pvid
Posted in ASM, rac & dg on 5月 22, 2012 | There are currently No Comments

记一次非归档RAC恢复(大量的ORA-600 错误)

帮客户恢复了一个10205 rac(for linux),非归档，掉电（听说频繁掉电几十次),下面是处理过程：

1	帮客户恢复了一个10205 rac(for linux),非归档，掉电（听说频繁掉电几十次),下面是处理过程：

、 [crayon-67d91a084dac […]

Tags: ORA-00214, ORA-01189, [13013], [4194], [6002], [kcbnew_3], [kccpb_sanity_check_2], [kddummy_blkchk], [kdsgrp1], [qertbFetchByRowID]
Posted in backup & rcovery on 5月 14, 2012 | There are currently 3 Comments

Puppet以及Dashboard安装使用

由于项目需要，最近研究了puppet以及dashboard，如下是写的2个简单的文档，分享出来。由于客户的机 […]

Tags: linux, 自动化运维
Posted in Unix/linux on 5月 9, 2012 | There are currently 2 Comments

10.2.0.4+版本PSU以及相关bundle patch列表-（2012/4/17 update)

oracle 昨天发布了2012年第2季度cpu和psu，第1季度的详细信息请参考：
<a href="//www.killdb.com/2012/01/19/10-2-0-4%E7%89%88%E6%9C%ACpsu%E4%BB%A5%E5%8F%8A%E7%9B%B8%E5%85%B3bundle-patch%E5%88%97%E8%A1%A8-%EF%BC%882012119-update.html&lt;/a"><span style="color: #ff0000"> 10.2.0.4+版本PSU以及相关bundle patch列表–（2012/1/19 update)

</span></a>

1

2

3

4

oracle 昨天发布了2012年第2季度cpu和psu，第1季度的详细信息请参考：

<a href="//www.killdb.com/2012/01/19/10-2-0-4%E7%89%88%E6%9C%ACpsu%E4%BB%A5%E5%8F%8A%E7%9B%B8%E5%85%B3bundle-patch%E5%88%97%E8%A1%A8-%EF%BC%882012119-update.html</a"><span style="color: #ff0000"> 10.2.0.4+版本PSU以及相关bundle patch列表–（2012/1/19 update)

</span></a>

[crayon-67d91a0851bbc2 […]

Tags: 10.2.0.5.7, 11.2.0.2.6, 11.2.0.3.2
Posted in mangement, Oracle rdbms on 4月 18, 2012 | There are currently 1 Comment

使用bbed跳过丢失的归档进行recover datafile

这篇文字写于2010年，详见原文链接使用bbed 跳过丢失的归档进行recover datafile [cr […]

Posted in oracle internal, troubleshooting on 4月 14, 2012 | There are currently 1 Comment

深入解析参数 OPTIMIZER_DYNAMIC_SAMPLING

下午看同事的博客提到了参数 OPTIMIZER_DYNAMIC_SAMPLING, 
                            
原帖地址:
<a href="http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/</a>


没有提到关于该参数的详细说明，下面我这里就在研究一下，跟大家分享。

10g 的官方文档有如下的简单描述：

1

2

3

4

5

6

7

8

9

下午看同事的博客提到了参数 OPTIMIZER_DYNAMIC_SAMPLING,

原帖地址:

<a href="http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/</a>

没有提到关于该参数的详细说明，下面我这里就在研究一下，跟大家分享。

10g 的官方文档有如下的简单描述：

[crayon-67d91a0859ead4 […]

Posted in Oracle rdbms, tunning on 4月 12, 2012 | There are currently 4 Comments

非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记

群中一位网友遇到的问题，硬件故障导致undo损坏，且是非归档，通过dbv检测发现大量坏块。

1	群中一位网友遇到的问题，硬件故障导致undo损坏，且是非归档，通过dbv检测发现大量坏块。

[crayon-67d91a085e4ad6 […]

Tags: ORA-01110, ORA-01190, [krhpfh_03-1202]
Posted in backup & rcovery on 3月 24, 2012 | There are currently 7 Comments

database crash with ora-00494

database crash with ora-00494 [crayon-67d91a086266d8745 […]

Posted in Oracle rdbms, troubleshooting on 3月 17, 2012 | There are currently 3 Comments

dd复制ASM中的datafile

SQL> select file_id,file_name,AUTOEXTENSIBLE 
  2    from dba_data_files 
  3   order by 1;
                                                                            
   FILE_ID FILE_NAME                                                    AUT 
---------- ------------------------------------------------------------ --- 
         1 +DATA01/10gasm/datafile/system.256.776886753                 YES 
         2 +DATA01/10gasm/datafile/undotbs1.258.776886753               YES 
         3 +DATA01/10gasm/datafile/sysaux.257.776886753                 YES 
         4 +DATA01/10gasm/datafile/users.259.776886755                  YES 
                                                                            
SQL> select GROUP_NUMBER, FILE_NUMBER, NAME 
  2    from v$asm_alias
  3   group by GROUP_NUMBER, FILE_NUMBER, NAME;

                   File
GROUP_NUMBER     Number NAME
------------ ---------- ------------------------------------------------
           1        256 SYSTEM.256.776886753
           1        257 SYSAUX.257.776886753
           1        258 UNDOTBS1.258.776886753
           1        259 USERS.259.776886755
           1        260 Current.260.776886827
           1        261 group_1.261.776886835
           1        262 group_2.262.776886835
           1        263 group_3.263.776886837
           1        264 TEMP.264.776886851
           1        265 spfile.265.777691577
           1 4294967295 10GASM
           1 4294967295 DATAFILE
           1 4294967295 TEMPFILE
           1 4294967295 ONLINELOG
           1 4294967295 CONTROLFILE
           1 4294967295 PARAMETERFILE
           2        256 ROGER.256.777429425
           2 4294967295 10GASM
           2 4294967295 DATAFILE

19 rows selected.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

SQL> select file_id,file_name,AUTOEXTENSIBLE

2 from dba_data_files

3 order by 1;

FILE_ID FILE_NAME AUT

---------- ------------------------------------------------------------ ---

1 +DATA01/10gasm/datafile/system.256.776886753 YES

2 +DATA01/10gasm/datafile/undotbs1.258.776886753 YES

3 +DATA01/10gasm/datafile/sysaux.257.776886753 YES

4 +DATA01/10gasm/datafile/users.259.776886755 YES

SQL> select GROUP_NUMBER, FILE_NUMBER, NAME

2 from v$asm_alias

3 group by GROUP_NUMBER, FILE_NUMBER, NAME;

File

GROUP_NUMBER Number NAME

------------ ---------- ------------------------------------------------

1 256 SYSTEM.256.776886753

1 257 SYSAUX.257.776886753

1 258 UNDOTBS1.258.776886753

1 259 USERS.259.776886755

1 260 Current.260.776886827

1 261 group_1.261.776886835

1 262 group_2.262.776886835

1 263 group_3.263.776886837

1 264 TEMP.264.776886851

1 265 spfile.265.777691577

1 4294967295 10GASM

1 4294967295 DATAFILE

1 4294967295 TEMPFILE

1 4294967295 ONLINELOG

1 4294967295 CONTROLFILE

1 4294967295 PARAMETERFILE

2 256 ROGER.256.777429425

2 4294967295 10GASM

2 4294967295 DATAFILE

19 rows selected.

[crayon-67d91a08669a83 […]

Posted in ASM, oracle internal, Oracle rdbms on 3月 13, 2012 | There are currently 6 Comments

手工构造逻辑坏块一例

以前同事问我，能否构造一个逻辑坏块？我们知道坏块通常分为物理坏块和逻辑坏块两种。

物理坏块：通常是由于硬件损坏如磁盘异常导致；

逻辑坏块：通常是由于oracle bug导致，比如data block和index block数据不一致等；

那么如何构造一个逻辑坏块呢？我们这里就来构造一个index block内容跟其对应的data block
内容不一致的情况，其实非常简单，我们随便改下index entry就行。

1

2

3

4

5

6

7

8

以前同事问我，能否构造一个逻辑坏块？我们知道坏块通常分为物理坏块和逻辑坏块两种。

物理坏块：通常是由于硬件损坏如磁盘异常导致；

逻辑坏块：通常是由于oracle bug导致，比如data block和index block数据不一致等；

那么如何构造一个逻辑坏块呢？我们这里就来构造一个index block内容跟其对应的data block

内容不一致的情况，其实非常简单，我们随便改下index entry就行。

[crayon-67d91a086bbd11 […]

Posted in Oracle rdbms, troubleshooting on 3月 8, 2012 | There are currently 2 Comments

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

about partiton column with date or varchar2？

About recreate controlfile ?

dml with lmode=6 ?

sql execution plan in heap 6?

soft parse 和 library cache lock

10g中distinct加强以及anti jion,semi jion

redo wastage

Where is the backup of ASM disk header block? –补充

About ora-00600 [4400] [48]

一次3 node Rac tunning

asm(aix) 小记

记一次非归档RAC恢复(大量的ORA-600 错误)

Puppet以及Dashboard安装使用

10.2.0.4+版本PSU以及相关bundle patch列表-（2012/4/17 update)

使用bbed跳过丢失的归档进行recover datafile

深入解析参数 OPTIMIZER_DYNAMIC_SAMPLING

非归档恢复遭遇ORA-01190 和 ORA-600 [krhpfh_03-1202]–恢复小记

database crash with ora-00494

dd复制ASM中的datafile

手工构造逻辑坏块一例