sunchao

最近在看龙蛇演义，作者对国术的理解还是挺深的，纵览前半部分篇章，简直就是一个人修炼国术所写的日记。

系统中频繁的出现类似这样的错误

crrwkx: nothing to do (start)
*** 2009-03-09 20:05:39.510
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:10:39.513
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:15:39.516
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:20:39.519
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:25:39.521
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:30:39.524
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:35:39.527
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:40:39.530
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:45:39.532
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:50:39.535
kcrrwkx: nothing to do (start)
*** 2009-03-09 20:55:39.538
经查又是一个bug

Bug 4883174 ARCHIVE PRODUCES TO MANY LOG ENTRIES ''KCRRWKX: NOTHING TO DO (END)
Fixed in 11g and possible in 10.2.0.3

硬件环境：
IBM 3950级联
IBM DS4800机头 exp810 sata盘柜 exp810光纤盘柜

光纤交换机 IBM 2005-B16交换机先看系统：
[root@ykt ~]# uname -a
Linux ykt 2.6.18-8.el5xen #1 SMP Fri Jan 26 14:29:35 EST 2007 x86_64 x86_64 x86_64 GNU/Linux

为了避免安装过程中出现不必要的错误，参考网上资料把相关rpm安装包打上：
[root@ykt kernels]# rpm -qa|grep kernel
kernel-PAE-devel-2.6.18-53.el5
kernel-xen-2.6.18-8.el5
kernel-2.6.18-8.el5
kernel-headers-2.6.18-8.el5
kernel-xen-devel-2.6.18-53.el5
kernel-PAE-2.6.18-53.el5
kernel-devel-2.6.18-53.el5
kernel-PAE-devel-2.6.18-8.el5
[root@ykt kernels]# rpm -qa|grep compat
java-1.4.2-gcj-compat-devel-1.4.2.0-40jpp.112
java-1.4.2-gcj-compat-1.4.2.0-40jpp.112
compat-libf2c-34-3.4.6-4
compat-libstdc++-33-3.2.3-61
compat-db-4.2.52-5.1
java-1.4.2-gcj-compat-src-1.4.2.0-40jpp.112
compat-glibc-headers-2.3.4-2.26
compat-gcc-34-c++-3.4.6-4
compat-libstdc++-33-3.2.3-61
compat-glibc-2.3.4-2.26
compat-db-4.2.52-5.1
compat-libstdc++-296-2.96-138
compat-glibc-2.3.4-2.26
compat-gcc-34-g77-3.4.6-4
java-1.4.2-gcj-compat-devel-1.4.2.0-40jpp.112
compat-gcc-34-3.4.6-4
compat-libgcc-296-2.96-138
[root@ykt kernels]# rpm -qa|grep sysstat
sysstat-7.0.0-3.el5

将 rdac-LINUX-09.02.C5.13-source.tar.gz 拷贝到/tmp下
[root@ykt tmp]# ls -l rdac-LINUX-09.02.C5.13-source.tar.gz
-rwxr-xr-x 1 root root 329888 03-07 03:17 rdac-LINUX-09.02.C5.13-source.tar.gz

解压 rdac-LINUX-09.02.C5.13-source.tar.gz
[root@ykt tmp]# cd linuxrdac-09.02.C5.13/

该包中包含如下文件：
[root@ykt linuxrdac-09.02.C5.13]# ls
genfileattributes mppCmn_s2tos3.c mpp_linux_sys_headers mppLnx26p_vhbalib.c mppSupport
genuniqueid.c mppCmn_SysInterface.c mppLnx26p_spinlock_size.c mppLnx26p_vhbamisc.c Readme.txt
hbaCheck mpp.conf mppLnx26p_sysdep.c mppLnx26p_vhbaproc.c setupDriver.REDHAT
License.txt MPP_hba.c mppLnx26p_upper.c mppLnx26p_vhbatask.c setupDriver.SUSE
lsvdev mppiscsi_umountall mppLnx26p_vhba.c mpp_rcscript.REDHAT utility
Makefile mpp_linux_headers mppLnx26p_vhbaio.c mpp_rcscript.SUSE

系统中经常出现这样的错误：

/opt/app/oracle/admin/syntong/bdump/syntong_lgwr_10558.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2
System name: Linux
Node name: ykt
Release: 2.6.18-8.el5xen
Version: #1 SMP Fri Jan 26 14:29:35 EST 2007
Machine: x86_64
Instance name: syntong
Redo thread mounted by this instance: 1
Oracle process number: 9
Unix process pid: 10558, image: oracle@ykt (LGWR)

*** SERVICE NAME:() 2009-03-08 00:50:25.186
*** SESSION ID:(218.1) 2009-03-08 00:50:25.186
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150676 bytes
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)
*** 2009-03-08 01:22:31.437
tkcrrxmp: Stopping ARC1 to reduce ARCH processes from 2 to 0
tkcrrxmp: Stopping ARC0 to reduce ARCH processes from 1 to 0

经查是oracle10g的一个bug

Bug 4704334 - ''TKCRRSARC: (WARN) '

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Symptoms
- After starting the database receive the following errors in trace file:>
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
<<
- After manual performing log switches the following messages occur in the alert.log:>
Shutting down archive processes
Wed Oct 19 14:13:22 2005
ARCH shutting down
ARC2: Archival stopped
<<
Cause

Bug 4704334 - ''TKCRRSARC: (WARN) FAILED TO FIND ARCH FOR MESSAGE (MESSAGE:0X1)''

These messages are warning messages and are a consequence of the internal archive process spawning and releasing.


There can be different reasons for this message to appear, but it is in fact related to internal work.

Solution

These are only diagnostic messages and can be safely ignored.
However, there are two options:

Option 1

Ignore the messages.

Option 2

Increase the log_archive_max_processes equal to/or greater than 10

在安装完应用测试的时候，遭遇到这个问题

[root@synbak ~]# telnet 192.168.8.1 8812
Trying 192.168.8.1...
Connected to 192.168.8.1 (192.168.8.1).
Escape character is '^]'.
getdatad: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
Connection closed by foreign host.

发现libclntsh.so.10.1的文件都存在并且服务属主用户也有该文件的rx权限，

但是还是报上述错误。

后来发现$ORACLE_HOME/lib下文件的owner是 oracle：oinstall  将他所属的组改成DBA后，错误信息消失

 但是这样改后 会引发其他问题并且不是解决问题的终极办法

Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",
hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;

Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;

Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;

Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;

Top 10 by Sharable Memory:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;

Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10 查看全文

When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as Enterprise Manager or SQL*Plus), Oracle creates a user process to run the user's application.

Connections and Sessions
Connection and session are closely related to user process but are very different in meaning.

A connection is a communication pathway between a user process and an Oracle instance. A communication pathway is established using available interprocess communication mechanisms (on a computer that runs both the user process and Oracle) or network software (when different computers run the database application and Oracle, and communicate through a network).

A session is a specific connection of a user to an Oracle instance through a user process. For example, when a user starts SQL*Plus, the user must provide a valid user name and password, and then a session is established for that user. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

在一台服务器上运行两个程序，其中一个可以顺利的连接上oracle9i数据库，而同在一台机子上的一个网站，在进行调试的时候总是提示：无法加载oci.dll提示，而没有办法连接上数据库。 鉴于上面的这种情况可以推出oracle的net8配置好像没有问题，问题是出在应用程序上，让人郁闷了一把 最终解决办法如下： 开始 -> 程序 -> Oracle -> Configuration and Migration Tools -> Net Manager→本地→概要文件→Oracle高级安全性→验证→去掉所选方法中的 "NTS" 就可以了.

早两天在aix5.3l下安装oracle9201的时候，在进行所有的预配置之后，oracle安装程序开始copy文件和编译，在这过程中就出现了多个 ？？？？？.mk文件编译错误，然后就提示要么忽略，或重试，忽略之后安装的数据库，估计是有很多问题的， 查看全文

有时候我们会根据实际环境，实际情况来修改该参数以允许更多的session接入

以下是整个修改过程：

SQL> show parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL> create pfile from spfile;

File created.

是这样的 在每次查询的时候

要求查询结果不足6条的时候 对不足的数据补上null植

是不是要求有点变态

SQL> desc sun
Name Null? Type
----------------------------------------- -------- ---------------------

A NUMBER
B NUMBER
C CHAR(2)

SQL> desc sun1
Name Null? Type
----------------------------------------- -------- ---------------------

NO NUMBER

SQL> select * from sun;

A B C
---------- ---------- --
1 1 x
2 2 x
2 3 x
2 5 x
1 7 x
1 234 x

6 rows selected.

SQL> select * from sun1;

NO
----------
1
2
3
4
5
6

6 rows selected.

SQL> select * from sun where a=1;

A B C
---------- ---------- --
1 1 x
1 7 x
1 234 x

SQL>
1 select b.no,
2 case when a.rno<=(select count(*) from sun where a=1) then
3 a.b else null end b,
4 case when a.rno<=(select count(*) from sun where a=1) then
5 a.c else null end c
6 from sun1 b,(select rownum rno,b,c from sun where a=1) a
7* where a.rno(+)=b.no
SQL> /

NO B C
---------- ---------- --
1 1 x
2 7 x
3 234 x
6
5
4

6 rows selected.
当然还可以采用decode等来实现

在这里只能算是抛砖引玉了