Jimmy He – 第 18 页

配置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 […]

..experience, Working case

instance不随机启动

2015-01-01 没有评论

客户有个机器上的instance，在机器重启后，总是不随机启动，每次都要手工的srvctl的去start一次。这其实是crs的配置缘故：

[root@rac1 ~]# crsctl stat res ora.ora11g.db -p 
NAME=ora.ora11g.db 
TYPE=ora.database.type 
ACL=owner:oracle:rwx,pgrp:oinstall:r--,other::r--,group:dba:r-x,group:oinstall:r-x,user:oracle:r-x 
ACTION_FAILURE_TEMPLATE= 
ACTION_SCRIPT= 
ACTIVE_PLACEMENT=1 
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX% 
AUTO_START=restore <<======这里配置的是restore，即上次是手工关闭过，等server重启后，instance也是启动到关机前关闭状态。

1

2

3

4

5

6

7

8

9

[root@rac1 ~]# crsctl stat res ora.ora11g.db -p

NAME=ora.ora11g.db

TYPE=ora.database.type

ACL=owner:oracle:rwx,pgrp:oinstall:r--,other::r--,group:dba:r-x,group:oinstall:r-x,user:oracle:r-x

ACTION_FAILURE_TEMPLATE=

ACTION_SCRIPT=

ACTIVE_PLACEMENT=1

AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%

AUTO_START=restore <<======这里配置的是restore，即上次是手工关闭过，等server重启后，instance也是启动到关机前关闭状态。

将auto_start修改成always就可以了。修改方式： [crayon-68079a64 […]

..unix shell, Useful script

批量取statspack的脚本

2015-01-01 一条评论

做了一个脚本sprpt_batch.sh:

read line
snap_i_id=$1
end_snap=$2

sqlplus -s /nolog<<EOF
conn /as sysdba;
define begin_snap=${snap_i_id};
define end_snap=${end_snap};
define report_name=sprpt_batch_${snap_i_id}_${end_snap}.txt
set echo off
set feedback off
@myspreport
exit
EOF

1

2

3

4

5

6

7

8

9

10

11

12

13

14

read line

snap_i_id=$1

end_snap=$2

sqlplus -s /nolog<<EOF

conn /as sysdba;

define begin_snap=${snap_i_id};

define end_snap=${end_snap};

define report_name=sprpt_batch_${snap_i_id}_${end_snap}.txt

set echo off

set feedback off

@myspreport

exit

EOF

将$ORACLE_HOME/rdbms/admin/spreport.sql和$ORACLE_HOME/rdbms/admin/sprepins.sql拷贝到工作目录下，重命名成myspre […]

Study note

切出的归档日志小于设置的redo大小

2015-01-01 没有评论

数据库产生大量的小日志，原因和log_buffer的大小和redo log file size有关。为了说明这个问题，我们先来看看从log buffer开始写redo log file的过程：（1）写redo log buffer 在pga中产生redo entry（即change record， […]

..experience, Working case

RMAN-00600 [13200]的处理

2015-01-01 没有评论

在dataguard上，使用rman连接catalog数据库进行备份。备份的时候，rman的日志报错如下：

Starting backup at 2014/04/23-10:02:02
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc
RMAN-06061: WARNING: skipping archived log compromises recoverability
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/23/2014 10:02:23
RMAN-00600: internal error, arguments [13200] [] [] [] []

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Starting backup at 2014/04/23-10:02:02

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc

RMAN-06061: WARNING: skipping archived log compromises recoverability

released channel: ch00

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

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

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

RMAN-03002: failure of backup command at 04/23/2014 10:02:23

RMAN-00600: internal error, arguments [13200] [] [] [] []

一开始，根据文档：341337.1，以为造成这个报错的原因是，注册在catalog库中的信息，在备份时被调用，由于备份是在da […]

..experience, Working case

打补丁的时候一定要注意opatch版本

2015-01-01 一条评论

在打patch的时候，特别是有些新申请的merge patch，我们需要注意在opatch的readme文档说明，因为有的时候，使用自带版本的opatch，会发生一些千奇百怪的问题。如下面的这个log中，我们看到，这是一个for 10.2.0.3.3的做db replay的patch，由于db r […]

..experience, Working case

11g库shutdown导致10g库的crsd进程重启

2014-12-05 没有评论

客户有个环境是10g的RAC，由于一次偶尔的需求，需要将一个11g的数据库临时在上面启动，当我们mount了11g的软件卷和datafile 卷之后，11g的数据库能正常启动，但是当11g的数据库shutdown时，导致了10g的crsd进程重启。在10g的crsd的log中，可以看到： [cra […]

..experience, Working case

ORA-06553: PLS-801: internal error [56327]

2014-12-05 没有评论

今天收到一个客户的问题，数据库中的alertlog中报错ORA-06553: PLS-801: internal error [56327]，

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Mon Nov 17 14:11:04 2014
Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]

1

2

3

4

5

6

7

8

9

10

11

12

13

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Mon Nov 17 14:11:04 2014

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

测试export用户或者表，甚至查询dba_segments都会报错ORA-06553: […]

..experience, Working case

rman set newname时报错RMAN-06015

2014-12-05 一条评论

某数据库restore之后，尝试set newname但是报错RMAN-06015。我们可以先手工catalog进去，在set newname。可以看如下的测试案例：

--1.纯粹的set newname是可以的
RMAN> run{ 
2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 
3> } 

executing command: SET NEWNAME 

RMAN> 

RMAN> 

RMAN> 

RMAN> 

RMAN> 


--2. 但是switch之后，就不可以。这是因为在你当前控制文件中，找不到该文件（asm上的文件）的信息
RMAN> run{ 
2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 
3> switch datafile 6; 
4> } 

executing command: SET NEWNAME 

RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03002: failure of switch command at 11/12/2014 10:36:09 
RMAN-20230: datafile copy not found in the repository 
RMAN-06015: error while looking up datafile copy name: /u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 

RMAN> 

RMAN> 

RMAN> -- 3.将该文件作为datafilecopy，catalog到控制文件中：

RMAN> catalog datafilecopy '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 

cataloged datafile copy 
datafile copy file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 RECID=1 STAMP=863433432 

RMAN> --4. 然后就可以顺利set newname后switch了。

RMAN> run{ 
2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 
3> switch datafile 6; 
4> } 

executing command: SET NEWNAME 

datafile 6 switched to datafile copy 
input datafile copy RECID=1 STAMP=863433432 file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 

RMAN> 

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

--1.纯粹的set newname是可以的

RMAN> run{

2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

3> }

executing command: SET NEWNAME

RMAN>

--2. 但是switch之后，就不可以。这是因为在你当前控制文件中，找不到该文件（asm上的文件）的信息

RMAN> run{

2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

3> switch datafile 6;

4> }

executing command: SET NEWNAME

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

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

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

RMAN-03002: failure of switch command at 11/12/2014 10:36:09

RMAN-20230: datafile copy not found in the repository

RMAN-06015: error while looking up datafile copy name: /u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222

RMAN>

RMAN> -- 3.将该文件作为datafilecopy，catalog到控制文件中：

RMAN> catalog datafilecopy '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

cataloged datafile copy

datafile copy file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 RECID=1 STAMP=863433432

RMAN> --4. 然后就可以顺利set newname后switch了。

RMAN> run{

2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

3> switch datafile 6;

4> }

executing command: SET NEWNAME

datafile 6 switched to datafile copy

input datafile copy RECID=1 STAMP=863433432 file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222

RMAN>

..experience, It's my life, Working case

基于Solaris sparc的Oracle调优

2014-09-28 没有评论

一、RAC 中cluster wait time高问题 1.设置LMS进程为FX 60，不要过多调整lms进程的数量注：在Solaris 10 Update 10之后，以及Solaris 11，才可以设置进程的优先级。可以通过看/etc/release看其版本。如Oracle Solaris 10 […]

Study note

v$archived_log中显示creator是rman

2014-09-28 没有评论

Q：$archived_log中显示creator有多种，如果是FGRD，这可能是alter system archive log current，那么还有看到是RMAN，这是怎么产生的？ A：可能是这些archive之前被rman catalog过。见下面的testcase： [crayon-68 […]

..experience, Working case

SQL执行时反复一慢两快的问题

2014-09-14 没有评论

SQL执行的时间，在正常情况下应该是稳定的。如果第一次快，第二次慢，那么可能就是由于cardinality feedback的缘故，我们可以设置”_OPTIMIZER_USE_FEEDBACK”= false来规避。但是这次遇到的问题却是执行过程两快一慢，执行过程是慢->快- […]