碧海蓝天

话说从前有一个从excel导入到数据库中的功能，近来该功能“超级”缓慢，严重影响生产活动。

从告警日志中，可以找到一些线索：日志切换非常频繁，出现多处checkpoint not complete。

同时，在系统中查询产生redo最多的会话，正是上面提到的那个功能，导入不到1w条数据，竟然累计产生近10G的redo！！！

到这里，已经有理由怀疑是这个功能出现的问题，导致过多的redo，进一步产生checkpoint not complete等待；

OK，10046，终于找到了问题的根源：不合理使用的物化视图

在导入功能相关的数据库表上，建有一个物化视图，我们知道物化视图刷新可以on commit,on demand，而这里使用了on commit，而且每次都是全刷新；更糟糕的是，导入功能是每条记录提交一次！

也就是说：每导入一条数据，物化视图需要清空，重新刷新数据。

解决思路：

• 物化视图刷新模式修改为on demand
• 物化视图全刷新变为快速刷新
• 导入数据使用分批提交方式

昨天接到一个任务：一个报表相关sql语句需要优化，原来执行时间几分钟，现在半小时也出不来。

登录系统后，查看sql语句执行计划未发现问题；

会话A中执行语句，A会话的状态为inactive，相关等待事件为SQL*Net message from client；

上面的现象比较怪异，值得怀疑；

进一步查看sql语句，发现select后面有部分列使用了自定义函数，试图去掉自定义函数相关字段，查询，很快正常返回结果；

到这里可以断定是函数导致的问题，经过进一步针对自定义函数以及表中相关列数据的排查，最后确定问题的原因：

对于表中一条特殊数据，自定义函数进入死循环，故sql语句长时间未返回。

问题函数，看看死循环在哪呢：

create or replace function P_StringReplace(Src in varchar2) return varchar2 as
Result varchar2(2000) := '' ;
tempstr varchar2(2000) := '';
tempstr2 varchar2(2000) := '';
begin
tempstr := '';
tempstr2 := '';
if (Src is not null) then
begin
tempstr := substr(Src,instr(Src,'=')+1);
loop
if instr(tempstr,';')<=0 then
exit;
end if;
tempstr2 := substr(tempstr,instr(tempstr,';'),instr(tempstr,'=')-instr(tempstr,';')+1);
tempstr := replace(tempstr, tempstr2, '/');
end loop;
Result := tempstr;
end;
else
begin
Result := '无';
end;
end if;

return(Result);
end P_StringReplace;

apt-get install make gcc g++ libc6-dev libstdc++5 libaio1 unzip
mkdir /etc/rc.d
for i in 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done
ln -s /usr/bin/awk /bin/awk
ln -s /usr/bin/basename /bin/basename

安装过程中遇到不少问题，记录一下：

wayne@wayne-laptop:~/.ssh$ ssh -X holly@10.8.66.201

holly@10.8.66.201′s password:

Linux ubuntu108 2.6.24-19-server #1 SMP Wed Jun 18 14:44:47 UTC 2008 x86_64

The programs included with the Ubuntu system are free software;

the exact distribution terms for each program are described in the

individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by

applicable law.

To access official Ubuntu documentation, please visit:

http://help.ubuntu.com/

Last login: Mon Jul 20 09:34:34 2009 from 10.8.10.154

holly@ubuntu108:~$ sudo su -

root@ubuntu108:~#

root@ubuntu108:~# hostname

ubuntu108

root@ubuntu108:~# apt-get update

……

root@ubuntu108:~# apt-get install build-essential libaio1 gawk ksh libmotif3 alien libtool lsb-rpm

……

root@ubuntu108:~# cd /bin

root@ubuntu108:/bin# ls -l /bin/sh

lrwxrwxrwx 1 root root 4 Jul 17 22:33 /bin/sh -> dash

root@ubuntu108:/bin# ln -sf bash /bin/sh

root@ubuntu108:/bin# ls -l /bin/sh

lrwxrwxrwx 1 root root 4 Jul 20 09:45 /bin/sh -> bash

root@ubuntu108:/bin# addgroup oinstall

Adding group `oinstall’ (GID 1001) …

Done.

root@ubuntu108:/bin# addgroup dba

Adding group `dba’ (GID 1002) …

Done.

root@ubuntu108:/bin# addgroup nobody

Adding group `nobody’ (GID 1003) …

Done.

root@ubuntu108:/bin# usermod -g nobody nobody

root@ubuntu108:/bin# useradd -g oinstall -G dba -p oracle -d /home/oracle -s /bin/bash oracle

root@ubuntu108:/bin# passwd -l oracle

Password changed.

root@ubuntu108:/bin# mkdir /home/oracle

root@ubuntu108:/bin# chown -R oracle:dba /home/oracle

root@ubuntu108:/bin# ln -s /usr/bin/awk /bin/awk

root@ubuntu108:/bin# ln -s /usr/bin/rpm /bin/rpm

root@ubuntu108:/bin# ln -s /usr/bin/basename /bin/basename

root@ubuntu108:/bin# ln -s /lib/libgcc_s.so.1 /lib/libgcc_s.so

root@ubuntu108:/bin# mkdir /etc/rc.d

root@ubuntu108:/bin# for i in 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done

root@ubuntu108:/bin# mkdir -p /oracle

root@ubuntu108:/bin# chown -R oracle:dba /oracle

root@ubuntu108:/bin# echo “fs.file-max = 65536″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “kernel.shmall = 2097152″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “kernel.shmmax = 2147483648″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “kernel.shmmni = 4096″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “kernel.sem = 250 32000 100 128″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “net.ipv4.ip_local_port_range = 1024 65535″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “net.core.rmem_default = 262144″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “net.core.rmem_max = 262144″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “net.core.wmem_max = 262144″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “net.core.wmem_default = 262144″ >> /etc/sysctl.conf

root@ubuntu108:/bin# echo “oracle soft nproc 2047″ >> /etc/security/limits.conf

root@ubuntu108:/bin# echo “oracle hard nproc 16384″ >> /etc/security/limits.conf

root@ubuntu108:/bin# echo “oracle soft nfile 1024″ >> /etc/security/limits.conf

root@ubuntu108:/bin# echo “oracle hard nofile 65530″ >> /etc/security/limits.conf

root@ubuntu108:/bin# sysctl -p

kernel.printk = 4 4 1 7

kernel.maps_protect = 1

fs.inotify.max_user_watches = 524288

vm.mmap_min_addr = 65536

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.all.rp_filter = 1

fs.file-max = 65536

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 1024 65535

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_max = 262144

net.core.wmem_default = 262144

root@ubuntu108:/bin# su – oracle

Your account has expired; please contact your system administrator

su: User account has expired

(Ignored)

oracle@ubuntu108:~$ pwd

/home/oracle

oracle@ubuntu108:~$ wget ftp://usr:pwd@10.8.2.70/Oracle/10g/10201_database_linux_x86_64.cpio.gz–10:06:07– ftp://hollycrm:*password*@10.8.2.70/

……

oracle@ubuntu108:~$ ls

10201_database_linux_x86_64.cpio.gz

oracle@ubuntu108:~$ gunzip 10201_database_linux_x86_64.cpio.gz

oracle@ubuntu108:~$ ls

10201_database_linux_x86_64.cpio

oracle@ubuntu108:~$ cpio -idmv < 10201_database_linux_x86_64.cpio

……

oracle@ubuntu108:~$ ls

10201_database_linux_x86_64.cpio database

oracle@ubuntu108:~$ cd database/

oracle@ubuntu108:~/database$ ./runInstaller -help

./runInstaller: line 52: /home/oracle/database/install/.oui: No such file or directory

oracle@ubuntu108:~/database$ cd install

oracle@ubuntu108:~/database/install$ ls -al

total 364

drwxr-xr-x 5 oracle oinstall 4096 2009-07-20 10:11 .

drwxr-xr-x 6 oracle oinstall 4096 2009-07-20 10:11 ..

-rwxr-xr-x 1 oracle oinstall 28 2005-10-23 15:49 addLangs.sh

-rwxr-xr-x 1 oracle oinstall 76 2005-10-23 15:49 addNode.sh

drwxr-xr-x 2 oracle oinstall 4096 2009-07-20 10:11 images

-rwxr-xr-x 1 oracle oinstall 46448 2005-10-23 15:49 lsnodes

-rwxr-xr-x 1 oracle oinstall 2268 2005-10-23 15:49 oneclick.properties

-rwxr-xr-x 1 oracle oinstall 2387 2005-10-23 15:49 oraparam.ini

-rwxr-xr-x 1 oracle oinstall 6428 2005-10-23 15:49 oraparamsilent.ini

-rwxr-xr-x 1 oracle oinstall 163185 2005-10-23 15:49 .oui

drwxr-xr-x 2 oracle oinstall 4096 2009-07-20 10:11 resource

drwxr-xr-x 2 oracle oinstall 4096 2009-07-20 10:11 response

-rwxr-xr-x 1 oracle oinstall 102612 2005-10-23 15:49 unzip

–这里遇到这个问题，具体原因是由于Oracle数据库64位安装介质使用了32位的安装程序，而系统中没有32位包。

root@ubuntu108:/usr/X11R6# apt-get install ia32-libs

Reading package lists… Done

……

ldconfig deferred processing now taking place

oracle@ubuntu108:/oracle/database$ export DISPLAY=10.8.10.154:0.0

oracle@ubuntu108:/oracle/database$ export LANG=us_EN.UTF-8

oracle@ubuntu108:/oracle/database$ ./runInstaller -ignoreSysPrereqs -record -destinationFile /oracle/orainstall.rsp

……

oracle@ubuntu108:/oracle/database$ ./runInstaller -ignoreSysPrereqs -silent -responseFile /oracle/orainstall.rsp

……

WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.

To register the new inventory please run the script ‘/home/oracle/oraInventory/orainstRoot.sh’ with root privileges.

If you do not register the inventory, you may not be able to update or patch the products you installed.

The following configuration scripts

/home/oracle/oracle/product/10.2.0/db_1/root.sh

need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.

The installation of Oracle Database 10g was successful.

Please check ‘/home/oracle/oraInventory/logs/silentInstall2009-07-20_02-41-14PM.log‘ for more details.

–这里应该查看/home/oracle/oraInventory/logs/silentInstall2009-07-20_02-41-14PM.log，看是否有报错信息；我安装过程中没有注意，结果后面打补丁报出跟这里一样的错误。

****************************************************************************

root@ubuntu108:~# /home/oracle/oraInventory/orainstRoot.sh

Changing permissions of /home/oracle/oraInventory to 770.

Changing groupname of /home/oracle/oraInventory to oinstall.

The execution of the script is complete

root@ubuntu108:~# /home/oracle/oracle/product/10.2.0/db_1/root.sh

Running Oracle10 root.sh script…

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /home/oracle/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin …

Copying oraenv to /usr/local/bin …

Copying coraenv to /usr/local/bin …

Creating /etc/oratab file…

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

****************************************************************************

–打补丁：

–第一次图形安装报错，日志如下（查看日志的过程中，发现这个错误在之前的静默安装日志中已经报错）：

INFO: /usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-linux-gnu/4.2.4/../../../libpthread.so when searching for -lpthread

/usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-linux-gnu/4.2.4/../../../libpthread.a when searching for -lpthread

/usr/bin/ld: skipping incompatible /usr/bin/../lib/libpthread.so when searching for -lpthread

/usr/bin/ld: skipping incompatible /usr/bin/../lib/libpthread.a when searching for -lpthread

/usr/bin/ld: skipping incompatible /usr/lib/libpthread.so when searching for -lp

INFO: thread

/usr/bin/ld: skipping incompatible /usr/lib/libpthread.a when searching for -lpthread

/usr/bin/ld: cannot find -lpthread

collect2: ld returned 1 exit status

INFO: make[1]: *** [/home/oracle/oracle/product/10.2.0/db_1/sysman/lib/libnmemso.so] Error 1

INFO: make[1]: Leaving directory `/home/oracle/oracle/product/10.2.0/db_1/sysman/lib’

INFO: make: *** [libnmemso] Error 2

INFO: End output from spawned process.

INFO: ———————————-

INFO: Exception thrown from action: make

Exception Name: MakefileException

Exception String: Error in invoking target ‘agent’ of makefile ‘/home/oracle/oracle/product/10.2.0/db_1/sysman/lib/ins_sysman.mk’. See ‘/home/oracle/oraInventory/logs/installActions2009-07-20_02-54-28PM.log’ for details.

Exception Severity: 1

–继续安装包

root@ubuntu108:/home/oracle/oraInventory/logs# sudo apt-get install gcc libaio1 lesstif2 lesstif2-dev make libc6 libc6-i386 libc6-dev-i386 libstdc++5 lib32stdc++6 lib32z1 ia32-libs

……

–报错：

INFO: /usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-linux-gnu/4.2.4/libgcc.a when searching for -lgcc

/usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-linux-gnu/4.2.4/libgcc.a when searching for -lgcc

/usr/bin/ld: cannot find -lgcc

INFO: collect2: ld returned 1 exit status

make[1]: *** [/home/oracle/oracle/product/10.2.0/db_1/sysman/lib/libnmemso.so] Error 1

INFO: make[1]: Leaving directory `/home/oracle/oracle/product/10.2.0/db_1/sysman/lib’

INFO: make: *** [libnmemso] Error 2

INFO: End output from spawned process.

INFO: ———————————-

INFO: Exception thrown from action: make

Exception Name: MakefileException

Exception String: Error in invoking target ‘agent’ of makefile ‘/home/oracle/oracle/product/10.2.0/db_1/sysman/lib/ins_sysman.mk’. See ‘/home/oracle/oraInventory/logs/installActions2009-07-20_03-10-03PM.log’ for details.

Exception Severity: 1

–继续安装包

root@ubuntu108:/home/oracle/oraInventory/logs# apt-get install gcc-multilib

……

oracle@ubuntu108:/oracle/Disk1$ ./runInstaller -ignoreSysPrereqs -record -destinationFile /oracle/oraupdate.rsp

……

oracle@ubuntu108:/oracle/Disk1$ ./runInstaller -ignoreSysPrereqs -silent -responseFile /oracle/oraupdate.rsp

……

WARNING:
The following configuration scripts need to be executed as the “root” user.
#!/bin/sh
#Root script to run
/home/oracle/oracle/product/10.2.0/db_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as “root”
3. Run the scripts

The installation of Oracle Database 10g Release 2 Patch Set 3 was successful.
Please check ‘/home/oracle/oraInventory/logs/silentInstall2009-07-20_03-19-54PM.log’ for more details.

****************************************************************************
root@ubuntu108:/home/oracle/oraInventory/logs# /home/oracle/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script…

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /home/oracle/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin …
The file “oraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
****************************************************************************

vi .bash_profile

umask 022

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=hollyc5

export ORACLE_BASE=/home/oracle

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export NLS_LANG=AMERICAN_AMERICA.UTF8

export LANG=us_EN.UTF-8

dbca

……

netca

……

最后，ubuntu应该安装的包：

sudo apt-get install gcc libaio1 lesstif2 lesstif2-dev make libc6 libc6-i386 libc6-dev-i386 libstdc++5 lib32stdc++6 lib32z1 ia32-libs gcc-multilib

今天项目中定时任务报错，影响报表数据；日志表看到错误

ORA-01839: date not valid for month specified

起初怀疑是Oracle的bug；……省略一千字，最后发现是在表中掏出两条’2009-02-29′这样的字符型日期数据……

出于应用可以跨数据库的角度，表中日期型数据都使用varchar2存储，留下了隐患。

这是一个教训：日期型数据就要用日期类型字段存储，记得在tom的书中有类似的描述！
否则可能导致的后果：

• 可能进入脏数据
• 也可能导致CBO选择错误的执行计划

Oracle数据库在linux系统上的安装过程中，涉及到设置linux内核参数；

这些内核参数究竟代表什么含义，如何调整这些参数？

共享内存：

可以通过ipcs -lm命令查看目前系统共享内存的参数限制：

# ipcs -lm

—— Shared Memory Limits ——–
max number of segments = 4096
max seg size (kbytes) = 1048576
max total shared memory (pages) = 2097152
min seg size (bytes) = 1

这里涉及到3个于共享内存相关的参数：SHMMAX，SHMMNI，SHMALL

SHMMAX

• 含义：单个共享内存段最大字节数
• 设置：比SGA略大
• 查看：cat /proc/sys/kernel/shmmax

$ cat /proc/sys/kernel/shmmax
1073741824

• 修改：

sysctl -w kernel.shmmax=1073741824
echo “kernel.shmmax=1073741824″ >> /etc/sysctl.conf

SHMMNI

• 含义：共享内存段最大个数
• 设置：至少4096
• 查看：cat /proc/sys/kernel/shmmni

# cat /proc/sys/kernel/shmmni
4096

• 修改：

# sysctl -w kernel.shmmni=4096
# echo “kernel.shmmni=4096″ >> /etc/sysctl.conf

SHMALL

• 含义：系统中共享内存页总数
• 设置：至少ceil(shmmax/PAGE_SIZE)；ORACLE DOC 默认值：2097152*4096=8GB
• 查看：cat /proc/sys/kernel/shmall

$ getconf PAGE_SIZE
4096

# cat /proc/sys/kernel/shmall
2097152

• 修改：

# sysctl -w kernel.shmall=2097152
# echo “kernel.shmall=2097152″ >> /etc/sysctl.conf

信号量：

当前系统信号量限制：

oracle@lab-rd-01:~$ ipcs -ls

—— Semaphore Limits ——–
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100

SEMMSL

• 含义：每个信号量set中信号量最大个数
• 设置：最小250；对于processes参数设置较大的系统建议设置为processes+10
  

SEMMNI

• 含义：linux系统信号量set最大个数
• 设置：最少128

SEMMNS

• 含义：linux系统中信号量最大个数
• 设置：至少32000；SEMMSL * SEMMNI

SEMOPM

• 含义：semop系统调用允许的信号量最大个数
• 设置：至少100；或者等于SEMMSL

查看信号量设置：cat /proc/sys/kernel/sem

order：SEMMSL, SEMMNS, SEMOPM, SEMMNI

oracle@lab-rd-01:~$ cat /proc/sys/kernel/sem
250    32000    100    128

修改：

sysctl -w kernel.sem=”250 32000 100 128″
echo “kernel.sem=250 32000 100 128″ >> /etc/sysctl.conf

ORACLE提供了sysresv工具管理共享内存以及信号量，操作系统级别可以通过ipcs管理。