Working case – 第 27 页

用分析函数取序列的间隔

2007-12-31 没有评论

假如我们有一个序列，是从400100到499999，但是其中有序列中有缺失，我们需要找在序列中有哪些间隔，我们应该怎么去找呢？我们来看看这个例子：

SQL> select * from test_table order by menuid;
……
    MENUID NAME
---------- ----------------------------------------
    408090 aa
    408091 aa
    408092 aa
    408093 aa
    408094 aa
    408095 aa
    408096 aa
    408097 aa
    408098 aa
    408099 aa<-----注意此处，序
    408200 aa<-----列不连续。
    408201 aa
    408202 aa
    408203 aa
    408204 aa
    408205 aa
    408206 aa
    408207 aa
    408208 aa
    408209 aa
……
    439379 aa
    439380 aa<----注意此处，序
    439382 aa<----列不连续。
    439383 aa
    439384 aa
    439385 aa
    439386 aa
    439387 aa
    439388 aa
……

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

SQL> select * from test_table order by menuid;

……

MENUID NAME

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

408090 aa

408091 aa

408092 aa

408093 aa

408094 aa

408095 aa

408096 aa

408097 aa

408098 aa

408099 aa<-----注意此处，序

408200 aa<-----列不连续。

408201 aa

408202 aa

408203 aa

408204 aa

408205 aa

408206 aa

408207 aa

408208 aa

408209 aa

……

439379 aa

439380 aa<----注意此处，序

439382 aa<----列不连续。

439383 aa

439384 aa

439385 aa

439386 aa

439387 aa

439388 aa

……

现有一个表myuser.test_table，其中有一列menuid，这个menui […]

..experience, Working case

挽救丢失的pfile

2007-12-22 一条评论

问题是这样出现的：按照平时的要求，spfile应该写入到一个裸设备中，由于裸设备所在的路径没有控制好权限，而create spfile的时候，弄错了大小写：裸设备为spfile_128m_01，而误操作成了create spfile=’spfile_128M_01′ from […]

..oracle service, Working case

oracle的官方维基

2007-12-14 没有评论

oracle也出了维基，地址为：http://wiki.oracle.com/ 目前该网站也被GFW封闭，请用代理软件登录该网站。

..troubleshooting, Working case

非归档下误删数据文件的处理

2007-12-08 4条评论

今天接到个电话，说某省的一个非归档数据库中，为某个表空间添加数据文件时误操作，加成了文件系统（数据库是使用裸设备做数据文件的），当时处理的方式是直接在os级rm掉了这个数据文件。结果在oem里面点击表空间的时候就报数据文件找不到了。然后，另一个同事将该数据文件执行offline drop后，在oem […]

..experience, Working case

关于数据文件头的一些问题

2007-11-14 没有评论

今天忽然想到一个问题，数据库使用裸设备，且一个主机上有2个instance，如果划分lv的时候没有注意名称上的区别，那么在使用的时候，是否会出现一个lv已经分配给了某一个instance，由于instance之间是独立了，裸设备也是不经过os层面，不会锁定的，因此可以把这个lv分配给另一个insta […]

..experience, Working case

侦听服务出现blocked状态

2007-11-14 一条评论

今天在用rman做standby数据库时，连接auxiliary数据库报错以下信息：

连接到目标数据库: ORALOCAL (DBID=3856983144)

RMAN> connect auxiliary sys/sys@standby

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12541: TNS: 没有监听器

1

2

3

4

5

6

7

8

连接到目标数据库: ORALOCAL (DBID=3856983144)

RMAN> connect auxiliary sys/sys@standby

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-04006: error from auxiliary database: ORA-12541: TNS: 没有监听器

去查了一下备机的侦听，由于是采用动态注册，先启动了侦听，再启动instance，当时pmon还没实现动态注册，需要等待1分钟左右的时间，发现oral […]

..experience, Working case

DG备机设置mandatory归档，主机无法切出日志

2007-10-18 没有评论

由于备机新升级了硬盘，一时还没把dg（data guard）装好，就断开了主机和备机的网络，今天在手工switch logfile的时候，当切出到低18个的时候，就挂住了，长时间没有相应。检查alertlog中，发现在不断写出如下的报错： [crayon-6903e4592449282717464 […]

..troubleshooting, Working case

创建视图时引用表的权限问题

2007-09-27 没有评论

在做imp的时候，发现log中有以下报错：

……
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "MYUESR"."V_VIEW01"                          ("ICPCOD"
 "E") AS "
 "select user02.IcpCode"
 "from user01.icp icp,user01.custcontact custcontact"
 "where user02.id = custcontact.id(+)"
……

1

2

3

4

5

6

7

8

……

IMP-00041: Warning: object created with compilation warnings

"CREATE FORCE VIEW "MYUESR"."V_VIEW01" ("ICPCOD"

"E") AS "

"select user02.IcpCode"

"from user01.icp icp,user01.custcontact custcontact"

"where user02.id = custcontact.id(+)"

……

通过直接在数据库查询，发现报错没有权限，但是select却是能够操作：

SQL> select * from V_VIEW01 where rownum<10;
select * from V_VIEW01 where rownum<10
              *
ERROR at line 1:
ORA-04063: view "MYUESR.V_VIEW01" has errors


SQL> show error view V_VIEW01
Errors for VIEW V_VIEW01:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-01031: insufficient privileges
SQL> 


SQL> select count(*) from user01.icp;

  COUNT(*)
----------
       976

SQL> select count(*) from user01.custcontact;

  COUNT(*)
----------
      1310

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

SQL> select * from V_VIEW01 where rownum<10;

select * from V_VIEW01 where rownum<10

*

ERROR at line 1:

ORA-04063: view "MYUESR.V_VIEW01" has errors

SQL> show error view V_VIEW01

Errors for VIEW V_VIEW01:

LINE/COL ERROR

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

0/0 ORA-01031: insufficient privileges

SQL>

SQL> select count(*) from user01.icp;

COUNT(*)

----------

976

SQL> select count(*) from user01.custcontact;

COUNT(*)

----------

1310

奇怪的是，MYUESR已经有dba权限，难道还不能建 […]

..troubleshooting, Working case

BUG:truncate分区后，segment大小没降低

2007-09-26 没有评论

今天遇到了一个的问题，一个分区表，当我truncate完一个分区，通过查看dba_segments.bytes竟然没有空间被释放：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
 
SQL> CREATE TABLE xxx
  2  (aa  varchar2(20),
  3  bb number ) tablespace tt
  4  PARTITION BY RANGE (bb)
  5  (PARTITION xxx_p1
  6  VALUES LESS THAN (10000),
  7  PARTITION xxx_p2
  8  VALUES LESS THAN (40000),
  9  PARTITION xxx_p3
 10  VALUES LESS THAN (80000),
 11  PARTITION xxx_p4
 12  VALUES LESS THAN (100000),
 13  PARTITION xxx_max
 14  values less than (maxvalue)
 15  );

Table created.


SQL> select TABLESPACE_NAME from user_tab_partitions where table_name='XXX';

TABLESPACE_NAME
------------------------------
TT
TT
TT
TT
TT

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          1      65536
         65536          1      65536
         65536          1      65536
         65536          1      65536
         65536          1      65536

SQL> insert into xxx select 'aa',rownum from dba_objects;

97390 rows created.

SQL> insert into xxx select * from xxx;

97390 rows created.

SQL> insert into xxx select aa,rownum  from xxx;

194780 rows created.

SQL> commit;

Commit complete.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          7     458752
         65536         17    2097152
         65536         17    2097152
         65536         13     851968
         65536         17    2097152

SQL> alter table xxx truncate partition xxx_p1;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          7     458752   ######truncate该分区，空间没释放
         65536         17    2097152
         65536         17    2097152
         65536         13     851968
         65536         17    2097152

SQL> alter table xxx truncate partition xxx_p2;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          7     458752
         65536         17    2097152    ######truncate该分区，空间没释放
         65536         17    2097152
         65536         13     851968
         65536         17    2097152

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

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

SQL> select * from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

PL/SQL Release 9.2.0.6.0 - Production

CORE 9.2.0.6.0 Production

TNS for HPUX: Version 9.2.0.6.0 - Production

NLSRTL Version 9.2.0.6.0 - Production

SQL> CREATE TABLE xxx

2 (aa varchar2(20),

3 bb number ) tablespace tt

4 PARTITION BY RANGE (bb)

5 (PARTITION xxx_p1

6 VALUES LESS THAN (10000),

7 PARTITION xxx_p2

8 VALUES LESS THAN (40000),

9 PARTITION xxx_p3

10 VALUES LESS THAN (80000),

11 PARTITION xxx_p4

12 VALUES LESS THAN (100000),

13 PARTITION xxx_max

14 values less than (maxvalue)

15 );

Table created.

SQL> select TABLESPACE_NAME from user_tab_partitions where table_name='XXX';

TABLESPACE_NAME

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

TT

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

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

65536 1 65536

SQL> insert into xxx select 'aa',rownum from dba_objects;

97390 rows created.

SQL> insert into xxx select * from xxx;

97390 rows created.

SQL> insert into xxx select aa,rownum from xxx;

194780 rows created.

SQL> commit;

Commit complete.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

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

65536 7 458752

65536 17 2097152

65536 13 851968

65536 17 2097152

SQL> alter table xxx truncate partition xxx_p1;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

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

65536 7 458752 ######truncate该分区，空间没释放

65536 17 2097152

65536 13 851968

65536 17 2097152

SQL> alter table xxx truncate partition xxx_p2;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

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

65536 7 458752

65536 17 2097152 ######truncate该分区，空间没释放

65536 17 2097152

65536 13 851968

65536 17 2097152

但是在我机器上的数据库中进行测试，却是有正常的结果： [crayon-6903e4592605c117 […]

..experience, Working case

谈谈容灾

2007-09-23 2条评论

今天来谈谈关于容灾的一些事情，其实网上很多关于容灾的方案、白皮书等等，各个厂家也有自己主推的容灾工具。在这里，我结合自己的工作经验，简单的谈谈我理解的数据库容灾。容灾，要考虑投资额度，能承受多少最大的支出；要考虑能接受的损失范围，保证我最小业务运行有哪些条件；要考虑容灾的距离，是本地容灾还是异地容 […]

..troubleshooting, Working case

因为hosts文件无法实现侦听动态注册

2007-09-06 3条评论

今天一个同事来求助sqlplus+sid登录不上的问题，测试登录，发现有ora-12514的报错:

oracle@my_testdb01:/oracle > sqlplus myuser/mypasswd@mysid

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Sep 5 15:36:06 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

1

2

3

4

5

6

7

8

9

oracle@my_testdb01:/oracle > sqlplus myuser/mypasswd@mysid

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Sep 5 15:36:06 2007

ERROR:

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

descriptor

检查了sqlnet.ora文件，发现NAMES.DIRECTORY_PATH= (TNSNAMES)，是使用tsnnames，继续检查 […]

..experience, Working case

配置dp备份报错fail to load MML

2007-09-03 一条评论

今天在配置dp时，dp中报错：用手工测试也有类似的报错：

RMAN> run{
2> allocate channel t1 type 'sbt_tape' ;                   
3> allocate channel t2 type 'sbt_tape' ;                   
4> backup datafile 3                              
5> format 'test_bk_%u_%p_%s.RMN'            
6> include current controlfile;
7> }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on t1 channel at 09/01/2007 04:25:21
ORA-19554: error allocating device, device type: SBT_TAPE, device name: 
ORA-27211: Failed to load Media Management Library
Additional information: 2

RMAN> 


################################
######  但是备份到disk却没有问题  
################################

RMAN> 

RMAN> 

RMAN> 

RMAN> run{
2> allocate channel t1 type disk ;                   
3> allocate channel t2 type disk ;                   
4> backup datafile 2                              
5> format '/iarch/test_bk_%u_%p_%s.RMN'            
6> include current controlfile;
7> }

allocated channel: t1
channel t1: sid=58 devtype=DISK

allocated channel: t2
channel t2: sid=70 devtype=DISK

Starting backup at 01-SEP-07
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
including current controlfile in backupset
channel t1: starting piece 1 at 01-SEP-07
channel t2: starting full datafile backupset
channel t2: specifying datafile(s) in backupset
input datafile fno=00002 name=/dev/vg_iora01/rdata_1g_001
channel t2: starting piece 1 at 01-SEP-07
channel t1: finished piece 1 at 01-SEP-07
piece handle=/iarch/test_bk_01iqqmc8_1_1.RMN comment=NONE
channel t1: backup set complete, elapsed time: 00:00:04
channel t2: finished piece 1 at 01-SEP-07
piece handle=/iarch/test_bk_02iqqmca_1_2.RMN comment=NONE
channel t2: backup set complete, elapsed time: 00:00:37
Finished backup at 01-SEP-07
released channel: t1
released channel: t2

RMAN>

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

62

63

RMAN> run{

2> allocate channel t1 type 'sbt_tape' ;

3> allocate channel t2 type 'sbt_tape' ;

4> backup datafile 3

5> format 'test_bk_%u_%p_%s.RMN'

6> include current controlfile;

7> }

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of allocate command on t1 channel at 09/01/2007 04:25:21

ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

Additional information: 2

RMAN>

################################

###### 但是备份到disk却没有问题

################################

RMAN>

RMAN> run{

2> allocate channel t1 type disk ;

3> allocate channel t2 type disk ;

4> backup datafile 2

5> format '/iarch/test_bk_%u_%p_%s.RMN'

6> include current controlfile;

7> }

allocated channel: t1

channel t1: sid=58 devtype=DISK

allocated channel: t2

channel t2: sid=70 devtype=DISK

Starting backup at 01-SEP-07

channel t1: starting full datafile backupset

channel t1: specifying datafile(s) in backupset

including current controlfile in backupset

channel t1: starting piece 1 at 01-SEP-07

channel t2: starting full datafile backupset

channel t2: specifying datafile(s) in backupset

input datafile fno=00002 name=/dev/vg_iora01/rdata_1g_001

channel t2: starting piece 1 at 01-SEP-07

channel t1: finished piece 1 at 01-SEP-07

piece handle=/iarch/test_bk_01iqqmc8_1_1.RMN comment=NONE

channel t1: backup set complete, elapsed time: 00:00:04

channel t2: finished piece 1 at 01-SEP-07

piece handle=/iarch/test_bk_02iqqmca_1_2.RMN comment=NONE

channel t2: backup set complete, elapsed time: 00:00:37

Finished backup at 01-SEP-07

released channel: t1

released channel: t2

RMAN>

检查了lib库中的link也是已经建立了：

gd_idb02:[/oracle/app/oracle/product/9.2.0/lib$]ll libobk.sl 
lrwxrwxrwx   1 oracle     dba             36 Sep  3 10:01 libobk.sl -> /opt/omni/lib/libob2oracle8ale_64bit.sl

1 2	gd_idb02:[/oracle/app/oracle/product/9.2.0/lib$]ll libobk.sl lrwxrwxrwx 1 oracle dba 36 Sep 3 10:01 libobk.sl -> /opt/omni/lib/libob2oracle8ale_64bit.sl

后来查了相关的文档，才发现那个link的微妙差别，原来在64位的 […]