今天项目中定时任务报错，影响报表数据；日志表看到错误
ORA-01839: date not valid for month specified
起初怀疑是Oracle的bug；……省略一千字，最后发现是在表中掏出两条’2009-02-29′这样的字符型日期数据……
出于应用可以跨数据库的角度，表中日期型数据都使用varchar2存储，留下了隐患。
这是一个教训：日期型数据就要用日期类型字段存储，记得在tom的书中有类似的描述！
否则可能导致的后果：
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
$ cat /proc/sys/kernel/shmmax
1073741824
sysctl -w kernel.shmmax=1073741824
echo “kernel.shmmax=1073741824″ >> /etc/sysctl.conf
SHMMNI
# cat /proc/sys/kernel/shmmni
4096
# sysctl -w kernel.shmmni=4096
# echo “kernel.shmmni=4096″ >> /etc/sysctl.conf
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
SEMMNI
SEMMNS
SEMOPM
查看信号量设置：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管理。
表T
索引T(id)
select * from (select * from t order by id desc) where rownum<=10;
搜集统计信息，不走索引；
用hint，不走索引；
在zh提醒下，终于想起来是not null约束搞鬼，以前也遇到过，笨死。
问题来源于这样一个案例：需要把数据库从裸设备中迁移到文件系统上，裸设备大小为10000M一个，但实际占用非常小；
考虑到上面的情况，想到找到裸设备的高水位，然后把高水位下的块dd到文件系统中，从而缩短迁移时间。
如下：
SQL> l 1 select file_id,sum(blocks),min(block_id),max(block_id) from dba_extents 2 where tablespace_name='USERS' 3* group by file_id SQL> / FILE_ID SUM(BLOCKS) MIN(BLOCK_ID) MAX(BLOCK_ID) ---------- ----------- ------------- ------------- 4 7080 9 7689
users表空间只有file#为4的一个文件，最大的block_id是7689
SQL> select 7689*8/1024 Mb from dual; MB ---------- 60.0703125 SQL> alter database datafile '/dev/raw/raw4' resize 60m; alter database datafile '/dev/raw/raw4' resize 60m * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value SQL> alter database datafile '/dev/raw/raw4' resize 62m; Database altered.
经过上面确认，可以dd拷贝裸设备的数据到块7689即可。
环境：ubuntu,10.2.0.1 oracle db
现象：客户端连接数据库时长时间无响应
处理：
以oracle用户登录ubuntu主机，ps -ef | grep pmon查看后台进程，没有问题；
尝试用sqlplus / as sysdba方式登录数据库，失败，同样无响应；
top查看系统资源占用情况，cpu user20%,sys70%，oracle用户进程平均占4%左右cpu；
怀疑由于cpu繁忙导致无响应；尝试杀掉所有oracle进程，重启数据库；
在杀掉oracle进程后；cpu资源得到释放，恢复正常，再次尝试sqlplus / as sysdba，仍然无响应；
strace sqlplus后，发现进入死循环：
times(NULL) = -2144584792
times(NULL) = -2144584792
……
跟bug：338461.1非常相像
$ strace /oracle/home/bin/sqlplus -V 2>&1 |less
……
old_mmap(NULL, 385024, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0×41794000
gettimeofday({1122996561, 411035}, NULL) = 0
access(“/usr/local/UD/conf/sqlnet.ora”, F_OK) = -1 ENOENT (No such file or directory)
access(“/usr/local/UD/lib/oracle/network/admin/sqlnet.ora”, F_OK) = -1 ENOENT (No such file or directory)
access(“/usr/local/UD/conf/sqlnet.ora”, F_OK) = -1 ENOENT (No such file or directory)
access(“/usr/local/UD/lib/oracle/network/admin/sqlnet.ora”, F_OK) = -1 ENOENT (No such file or directory)
fcntl64(-1218313656, F_SETFD, FD_CLOEXEC) = -1 EBADF (Bad file descriptor)
times(NULL) = -1825782405
times(NULL) = -1825782405
times(NULL) = -1825782405
times(NULL) = -1825782405
times(NULL) = -1825782405
times(NULL) = -1825782405
times(NULL) = -1825782405It is looping on the times() function.
解决：1. 重启主机 or 2. 打10.2.0.2补丁。
Alert日志中出现少量如下错误：
Thu Apr 9 14:07:26 2009
Errors in file /oracle/admin/hollyc5/udump/hollyc5_ora_14616.trc:
ORA-07445: exception encountered: core dump [opidsa()+386] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
……
查看了所有trc文件：
[oracle@db-01-001 ~]$ head -20 /oracle/admin/hollyc5/udump/hollyc5_ora_14616.trc
/oracle/admin/hollyc5/udump/hollyc5_ora_14616.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name: Linux
Node name: db-01-001.hollycrm.com
Release: 2.6.9-67.ELlargesmp
Version: #1 SMP Wed Nov 7 14:07:22 EST 2007
Machine: x86_64
Instance name: hollyc5
Redo thread mounted by this instance: 1
Oracle process number: 51
Unix process pid: 14616, image: oraclehollyc5@db-01-001.hollycrm.com*** 2009-04-09 14:07:26.294
*** ACTION NAME:(Main session) 2009-04-09 14:07:26.257
*** MODULE NAME:(PL/SQL Developer) 2009-04-09 14:07:26.257
*** SERVICE NAME:(hollyc5) 2009-04-09 14:07:26.257
*** SESSION ID:(486.41443) 2009-04-09 14:07:26.257
发现问题有一个共同点：MODULE NAME:(PL/SQL Developer) ；似乎对jdbc并无影响；
建议如果再遇到类似问题，考虑尝试升级PL/SQL到最新版本。
Metalink Alert:418531.1记录了这一问题：
Description
After having applied the 10.2.0.3 patch set on any platform, processes from user sessions can core dump (ORA-7445) in opidsa().
The problem is also known to affect 10.2.0.2, but only for Windows platforms when running with 10.2.0.2 Patch 6 upwards.Workaround or Resolution
No real workaround exists for the problem, but flushing the shared pool after receiving the error may clear the situation to prevent further occurrences for a while. Restarting the instance will also clear the problem for a period. Implementing regular flushes of the shared pool can prevent the problem from occurring (this could eg. be executed via DBMS_JOB outside business hours to minimize the impact).The problem is fixed in 11g via non-published bug:5648872 and the fix is included in 10.2.0.4.
LTOM 是Oracle官方提供的嵌入式在线的系统数据采集和诊断工具，有三个功能：
官方介绍：
The Lite Onboard Monitor (LTOM) is a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM differs from other support tools, as it is proactive rather than reactive. LTOM provides real-time automatic problem detection and data collection. LTOM runs on the customer’s UNIX server, is tightly integrated with the host operating system and provides an integrated solution for detecting and collecting trace files for system performance issues. The ability to detect problems and collect data in real-time will hopefully reduce the amount of time it takes to solve problems and reduce customer downtime.
可以从metalink下载ltom412.tar
tar xvf ltom412.tar
参考README进行安装
oracle@wayne-laptop:~/tools/ltom/tom_base/install$ ./autoinstall.sh
TOM_HOME=/home/oracle/tools/ltom/tom_base/install/../tom
rm: cannot remove `autoinstall.log’: No such file or directoryPreparing to install LTOM v4.0.0…
Checking your Oracle Environment…
ORACLE_HOME=/home/oracle/app/10.2.0/db_1
ORACLE_SID=ora10g
TNS_ADMIN=
TOM_HOME=/home/oracle/tools/ltom/tom_base/install/../tomIs this the correct Oracle environment you wish to use for installing LTOM?
Hit Return to Accept Default:
============================> \cContinuing the install with this environment
Checking for Java…
./autoinstall.sh: line 176: /tmp/jstout.txt: Permission denied
Java found.Checking for top utility…
Found top utility.Checking for vmstat utility…
Found vmstat utility.Need to run some sql now to create TOM user
Hit Return to allow setup to do this for you, enter other to do this manually:
============================> \cTom user created successfully.
Warning…Please change tom password immediately!Grant user Tom successful.
Create package dbmssupp successful.
Grant execute on dbmssupp successful.Checking for statspack…
Found statspack.Enter your email id to get notification of hangs via email…
Hit Return to ignore hang notification via email:
============================> \cConfiguring tom_deploy.properties file…
TOM/LTOM successfully installed.
Please review any messages you received above.
REMEMBER: You must have TOM_HOME defined and java in your PATH
before you can run TOM/LTOM.
安装后需要设置TOM_HOME，然后就可以启动了
oracle@wayne-laptop:~/tools/ltom/tom_base/tom$ export TOM_HOME=`pwd`
oracle@wayne-laptop:~/tools/ltom/tom_base/tom$ echo $TOM_HOME
/home/oracle/tools/ltom/tom_base/tom
oracle@wayne-laptop:~/tools/ltom/tom_base/tom$ ./startltom.sh
Enter username:tom
Enter your password:****Starting LTOM V4.1.2
LTOM written by Oracle Center of Expertise
Copyright (c) 2008 by Oracle CorporationEnter 1 to Start Auto Hang Detection
Enter 2 to Stop Auto Hang Detection
Enter 3 to Start System Profiling
Enter 4 to Stop System Profiling
Enter 7 to Start Session Tracing
Enter 71 to Display Sessions Traced
Enter 72 to Dump All Trace Buffers
Enter 73 to Dump Specific Trace Buffer
Enter 74 to Stop Specific Session Tracing
Enter 8 to Stop All Session TracingEnter S to Update status
Enter Q to End Program
CURRENT STATUS: HangDetection=OFF Profiling=OFF AutoRec=OFF SessionRec=OFF
下面，使用1进入Auto Hang Detection，在数据库中模拟library cache pin事件；下面就可以看到相应的挂起信息
Please Select an Option:1
Enter polling frequency in seconds
(Hit return to accept default value of 20 seconds): 5Enter 1 to Start Auto Hang Detection
Enter 2 to Stop Auto Hang Detection
Enter 3 to Start System Profiling
Enter 4 to Stop System Profiling
Enter 7 to Start Session Tracing
Enter 71 to Display Sessions Traced
Enter 72 to Dump All Trace Buffers
Enter 73 to Dump Specific Trace Buffer
Enter 74 to Stop Specific Session Tracing
Enter 8 to Stop All Session TracingEnter S to Update status
Enter Q to End Program
CURRENT STATUS: HangDetection=ON Profiling=OFF AutoRec=OFF SessionRec=OFF
Please Select an Option:
>>> LTOM has detected a hang at Tue Jun 2 18:36:31 CST 2009
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/ltomholdfilename.out
HANGANALYZE Level 3 started Tue Jun 2 18:36:31 CST 2009
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/hangmail.tmp
exit value was 127
cmd was /home/oracle/tools/ltom/tom_base/tom/src/ltommail.sh
HANGANALYZE completed Tue Jun 2 18:36:31 CST 2009
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/fname.out
exit value was 1
cmd was cat UNKNOWN
>ERROR PROCESSING FILE. MOST LIKELY CAUSE IS A DELETED FILE: UNKNOWN
This cmd failed: cat UNKNOWN
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/holdfilename.out
SYSTEMSTATE DUMP Level 266 started Tue Jun 2 18:36:31 CST 2009
SYSTEMSTATE DUMP completed Tue Jun 2 18:36:33 CST 2009
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/fname.out
SYSTEMSTATE DUMP in file UNKNOWN
HANGANALYZE Level 3 started Tue Jun 2 18:37:03 CST 2009
HANGANALYZE completed Tue Jun 2 18:37:03 CST 2009
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/fname.out
exit value was 1
cmd was cat UNKNOWN
>ERROR PROCESSING FILE. MOST LIKELY CAUSE IS A DELETED FILE: UNKNOWN
This cmd failed: cat UNKNOWN
SYSTEMSTATE DUMP Level 266 started Tue Jun 2 18:37:03 CST 2009
SYSTEMSTATE DUMP completed Tue Jun 2 18:37:04 CST 2009
exit value was 1
cmd was rm /home/oracle/tools/ltom/tom_base/tom/tmp/fname.out
SYSTEMSTATE DUMP in file UNKNOWN>>> Exiting autohangdetect thread…
……
CURRENT STATUS: HangDetection=OFF Profiling=OFF AutoRec=OFF SessionRec=OFF
Please Select an Option:q>>> Beginning shutdown. Waiting for all threads to be notified…
>>> Thread 0 is main still waiting to be notified of shutdown…
>>> LTOM successfully shut down.
The following output was produced from running LTOM…
Auto Hang Detection Logs located in $TOM_HOME/hanglog
Other hang trace files may be located in the udump/bdump
进入$TOM_HOME/hanglog查看
>>>WARNING. Hang detected!
>>>Hang Detected while running query. SID= 144
Event= library cache pin Secs_In_Wait= 48
Tue Jun 2 18:36:31 CST 2009
>>>Info Only…
Total sessions waiting on significant events = 1
Total sessions waiting on rule specified event = 1
进一步可以通过hanganalyze log 和 ubump目录下的system state dump文件获取详细信息。
其他两项功能也比较实用……
首先安装rlwrap包：
apt-get install rlwrap
然后在/etc/bash.bashrc中设置别名
alias sqlplus=’rlwrap sqlplus’。
在设置别名的过程中有一个小插曲：
用/etc/profile定义别名，在gnome中进入终端后，不能正常使用别名，进入命令行，可以正常读取别名设置；
应该是由于gnome中调用了gnome-terminal导致了问题，只好修改/etc/bash.bashrc来实现。
Oracle数据库的内存管理技术从9i到最新的版本11g每一个版本都有着显著的变化：
9i：
10g：进一步把SGA也收编在了一起，有一个新名词Automatic Shared Memory Management(ASMM)，加入了新参数SGA_TARGET来设置SGA的大小。
11g：再把PGA也收编进来，新名词叫Automatic Memory Management(AMM)，
参数：
新还增加了RESULT CACHE用于保存sql及pl/sql结果，相应的参数：
视图：
可以看出，Oracle内存管理的趋势通过不断的整合实现了自动化，傻瓜化；
同时，对于特殊的需求仍然可以通过手动设置来满足；
Oracle最近就是不停的整合，自己产品内部要整合；其他产品，其他公司也统统要整合……
可以幻想一下，在将来真的会有fast=true这样的设置也不一定吧！
记录一下ubuntu的回收站位置，今天遇到了回收站不能清空；记录一下。
~/.local/share/Trash/