碧海蓝天

表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) = -1825782405

It 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官方提供的嵌入式在线的系统数据采集和诊断工具，有三个功能：

• Automatic Hang Detection
• System Profiler
• Automatic Session Tracing

官方介绍：

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 directory

Preparing 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/../tom

Is this the correct Oracle environment you wish to use for installing LTOM?
Hit Return to Accept Default:
============================> \c

Continuing 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:
============================> \c

Tom 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:
============================> \c

Configuring 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 Corporation

Enter 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 Tracing

Enter 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): 5

Enter 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 Tracing

Enter 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来实现。