..experience – 第 10 页

rename asm diskgroup

2016-02-17 没有评论

在11.2之后，已经可以rename asm diskgroup了，而不必再新建diskgroup之后，再做image copy。在11.2.中的gi home中，自带了一个命令renamedg，看下面的例子:

renamedg phase=<both|one|two> dgname=<old_diskgroup_name>  newdgname=<new_diskgroup_name> [asm_diskstring='<path_name>' ] [verbose=true]

1	renamedg phase=<both\|one\|two> dgname=<old_diskgroup_name> newdgname=<new_diskgroup_name> [asm_diskstring='<path_name>' ] [verbose=true]

其步骤可以分成2个p […]

..experience, Working case

在同一主机内复制一个数据库

2015-10-16 没有评论

到了这年头，这个任务已经变得非常简单。我们可以用rman的duplicate来做。 1. 准备初始化文件，注意加上加db_file_name_convert和log_file_name_convert。另外，如果数据库显式指定了db_unique_name，复制的库的db_unique_name和原 […]

..experience, Working case

物化视图刷新报错ora-920（或ora-904）

2015-09-30 没有评论

某物化视图在增量刷新的时候报错ora-00920，尝试全量刷新的时候，报错ora-00904。

BEGIN
DBMS_MVIEW.REFRESH('MV_ABCD_K_PPLLCJ','F');
END;

ORA-12018:在创建"MYJUKKA"."MV_ABCD_K_PPLLCJ"的代码时出现以下错误
ORA-00920:无效的关系运算符
ORA-06512:在"SYS.SNAPSHOT", line 2809
ORA-06512:在"SYS.SNAPSHOT", line 3025
ORA-06512:在"SYS.SNAPSHOT", line 2994
ORA-06512:在line 1

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

BEGIN

DBMS_MVIEW.REFRESH('MV_ABCD_K_PPLLCJ','F');

END;

ORA-12018:在创建"MYJUKKA"."MV_ABCD_K_PPLLCJ"的代码时出现以下错误

ORA-00920:无效的关系运算符

ORA-06512:在"SYS.SNAPSHOT", line 2809

ORA-06512:在"SYS.SNAPSHOT", line 3025

ORA-06512:在"SYS.SNAPSHOT", line 2994

ORA-06512:在line 1

SQL>

BEGIN
DBMS_MVIEW.REFRESH('MV_ABCD_K_PPLLCJ','C',ATOMIC_REFRESH=>false);
END;

ORA-12018:在创建"MYJUKKA"."MV_ABCD_K_PPLLCJ"的代码时出现以下错误
ORA-00904:标识符无效
ORA-06512:在"SYS.SNAPSHOT", line 2809
ORA-06512:在"SYS.SNAPSHOT", line 3025
ORA-06512:在"SYS.SNAPSHOT", line 2994
ORA-06512:在line 1

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

BEGIN

DBMS_MVIEW.REFRESH('MV_ABCD_K_PPLLCJ','C',ATOMIC_REFRESH=>false);

END;

ORA-12018:在创建"MYJUKKA"."MV_ABCD_K_PPLLCJ"的代码时出现以下错误

ORA-00904:标识符无效

ORA-06512:在"SYS.SNAPSHOT", line 2809

ORA-06512:在"SYS.SNAPSHOT", line 3025

ORA-06512:在"SYS.SNAPSHOT", line 2994

ORA-06512:在line 1

SQL>

查了mview的定义，直接select是没有问题，再查了mos，也没有查 […]

..experience, Working case

恢复数据文件到文件系统却到了asm

2015-09-30 没有评论

有一套系统，是dataguard，primary是使用asm，standby是使用文件系统，通过db_file_name_convert来转换数据文件的路径，平时的备份是放在standby主机上做，即备份standby上的文件，备份信息是放在catalog库。有一次做恢复，在恢复主机上，运行了rm […]

..experience, Working case

检查字段中不可见字符

2015-09-12 没有评论

在一个mysql导入到oracle的库中，某字段显示为’a’，但是用where column=’a’ 返回0行，只有where column like ‘a%’ 才有返回结果。那么，在该字段的末尾，应该是有些不可见字符了。我们可 […]

..experience, Working case

SQL> ALTER SYSTEM SET SERVICE_NAMES='oracleblog.org, jimmy.oracleblog.org, jimmy.he.oracleblog.org, hejianmin.oracleblog.org, he.jian.min.oracleblog.org, jimmy_he_oracleblog.org, jimmy_he_he.jian_min.oracleblog.org, jian_min_he.jimmy.oracleblog.org, jimmy.he.he_jianmin.oracleblog.org, jimmyhe_hejianmin.oracleblog.org, jimmy.he.he.jianmin.oracleblog.org' scope=both;

ALTER SYSTEM SET SERVICE_NAMES='oracleblog.org, jimmy.oracleblog.org, jimmy.he.oracleblog.org, hejianmin.oracleblog.org, he.jian.min.oracleblog.org, jimmy_he_oracleblog.org, jimmy_he_he.jian_min.oracleblog.org, jian_min_he.jimmy.oracleblog.org, jimmy.he.he_jianmin.oracleblog.org, jimmyhe_hejianmin.oracleblog.org, jimmy.he.he.jianmin.oracleblog.org' scope=both

*

ERROR at line 1:

ORA-32021: parameter value longer than 255 characters

SQL>

虽然可以通过dbms_service包来创建和修改service […]

..experience, Working case

停10g的css进程

2015-07-30 没有评论

在10g中，如果使用asm存储，且不使用clusterware，那么在安装asm的时候，就会要求启动css服务，启动css服务是以root运行运行$ORACLE_HOME/bin下的localconfig add。然后，css服务就开始运行了，你也可以建立asm实例，在asm上建单实例的数据库了。 […]

..experience, Working case

另一种找asm disk dir的方法

2015-07-24 一条评论

其实还有另一种找asm disk dir的方法，这种方法利用了官方提供的amdu的工具，找起来更简单。先利用amdu得到map文件，命令为：

amdu -dump <diskgroup_name|ALL> -diskstring '<diskstring_path>'

1	amdu -dump <diskgroup_name\|ALL> -diskstring '<diskstring_path>'

如，查到所有的diskgroup： [crayon-69044f361a800085414 […]

..experience, Working case

HP平台的asm要求maxfiles_lim参数为32767

2015-07-03 2条评论

最近在客户遇到一个案例，和大家分享一下，大家如果客户那里也有HP平台，那么要注意在11gR2中kernal参数maxfile_lim要设置成32767，而在11gR1以及之前，这个参数是65536的。（在169706.1文档上，11gR1之前是设置65536的，在11gR2上要求参考install […]

..experience, Working case

asm alertlog大量报错Voting file relocation is required in diskgroup

2015-07-02 没有评论

客户将asm的实例配置错了主机，asm1运行在节点2，asm2运行在节点1，在进行de-config和re-config之后，asm的alertlog大量报错： SYMPTOMS:

NOTE: Attempting voting file refresh on diskgroup GRID
NOTE: Voting file relocation is required in diskgroup GRID
NOTE: Attempting voting file relocation on diskgroup GRID
NOTE: Attempting voting file refresh on diskgroup GRID
NOTE: Voting file relocation is required in diskgroup GRID
NOTE: Attempting voting file relocation on diskgroup GRID
NOTE: Attempting voting file refresh on diskgroup GRID

1

2

3

4

5

6

7

NOTE: Attempting voting file refresh on diskgroup GRID

NOTE: Voting file relocation is required in diskgroup GRID

NOTE: Attempting voting file relocation on diskgroup GRID

NOTE: Attempting voting file refresh on diskgroup GRID

NOTE: Voting file relocation is required in diskgroup GRID

NOTE: Attempting voting file relocation on diskgroup GRID

NOTE: Attempting voting file refresh on diskgroup GRID

CAUSE: [crayon-69044f361bc […]

..experience, Working case

高IO下的library cache lock 和cursor pin S wait on X

2015-07-02 没有评论

今天，客户端某数据库遭遇大量library cache lock 和cursor pin S wait on X的问题，经诊断发现是由于他们底层的ASM存储是多个库共享的，故障时间段别的库占用大量的IO，导致paitsm库的IO响应变慢，db file sequential read从平时的20多毫 […]

..experience, Working case

配置odbc透明网关实现oracle访问postgres DB

2015-07-02 没有评论

最近帮客户配置了一下通过odbc透明网关，实现在oracle内通过db link访问postgres DB。简单记录一下：（1）listener.ora和tnsnames.ora的配置：

[wsj81@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /wsj/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_ORCL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = pgdb)
      (ORACLE_HOME = /wsj/oracle/app/product/11.2.0/dbhome_1)
      (ENV="LD_LIBRARY_PATH=/usr/lib64:/wsj/oracle/app/product/11.2.0/dbhome_1/lib:usr/local/lib")
      (PROGRAM=dg4odbc)
    )
  )

ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.89.64.111)(PORT = 1531))
      )
    )
  ) 


[wsj81@localhost admin]$ 
[wsj81@localhost admin]$ cat  tnsnames.ora
pgdb =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=19.89.64.111)(PORT=1521))
    (CONNECT_DATA=(SID=pgdb))
    (HS=OK)
  )

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

[wsj81@localhost admin]$ cat listener.ora

# listener.ora Network Configuration File: /wsj/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_ORCL =

(SID_LIST =

(SID_DESC =

(SID_NAME = pgdb)

(ORACLE_HOME = /wsj/oracle/app/product/11.2.0/dbhome_1)

(ENV="LD_LIBRARY_PATH=/usr/lib64:/wsj/oracle/app/product/11.2.0/dbhome_1/lib:usr/local/lib")

(PROGRAM=dg4odbc)

)

ORCL =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 19.89.64.111)(PORT = 1531))

)

[wsj81@localhost admin]$

[wsj81@localhost admin]$ cat tnsnames.ora

pgdb =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=19.89.64.111)(PORT=1521))

(CONNECT_DATA=(SID=pgdb))

(HS=OK)

)

（2）透明网关的配置，这2个文件在$ORA […]