oracle 也推出来了数据库的top命令，叫做oratap,是使用c语言写的，目前只支持linux x86与x64的11gR2与12cR1
使用说明

[oracle@xifenfei tmp]$ ./oratap  -h
oratop: Release 13.2.4
Usage:
         oratop [ [Options] [Logon] ]
         Logon:
                {username[/password][@connect_identifier] | / }
                [AS {SYSDBA|SYSOPER}]
         Options:
             -i : Interval Delay (requires value in seconds, default: 3s)
             -f : Long format for header & section 4 (default: 80 column)
             -r : IORL mode for Section 2 (default is IOPS)
             -d : Real-Time Top 5 Wait Events (default: Cumulative)
             -m : MODULE/ACTION mode for Section 4 (default: USER/PROGRAM_NAME)
             -b : Batch mode
             -n : maximum number of iterations (requires number)
             -h : Help

使用说明
1. Log in to the system as the Oracle RDBMS software installation owner
2. Stage the oratop executable on the server on which the tool will be executed. On a RAC system it is only necessary to stage the executable on one node as it is RAC aware.
3. Change the name of the executable, eg.

$ mv oratop* oratop

4. Validate the permissions for oratop are 755 (-rwxr-xr-x). If the permissions are not currently set to 755, set the permissions on oratop as follows:

$ chmod 755 oratop

5. Configure the execution environment as follows (if not already set accordingly)

$ export TERM=xterm #or vt100
$ export ORACLE_HOME=<11.2 database home>
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=<local 11.2 database SID to be monitored> #only needed if connecting to a local database

6. Invoke the tool to monitor a local database as follows (interval can be any interval in seconds):

$ ./oratop -i 10 / as sysdba

7. Non-dba privileged user requires specific grants to use the tool. See User Guide for grants required.
8. Databases can be monitored remotely using a tns alias as described in the oratop Users Guide .

$ ./oratop -i 10 username/password@tns_alias
$ ./oratop -i 10 system/manager@tns_alias

9. To exit the program simply press the keyboard key “q”. To abort, CTRL-C.
Troubleshooting:
If you receive an error similar to
./oratop: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory that is an indication that libclntsh.so.11.1 was not found in the path defined in LD_LIBRARY_PATH similar to

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib (ie., the location of libclntsh.so.11.1)

使用效果

[oracle@xifenfei tmp]$ ./oratap  -i 10 chf/xifenfei@ora11g_p
oratop: Release 13.2.4 Production on Mon Aug 26 03:10:17 2013
Copyright (c) 2011, Oracle.  All rights reserved.
Processing ...
oratop 1:  148 ora1 03:10:13 up  3.3m,  1 ins, 301M mt,   3 sn,  1 us,   6% db
ID %CU HLD MBPS IOPS %FR PGAU ASC ASI ASW ASP   AAS USN  TPS UCPS SSRT DBC DBW
 1   0   0    0    1   5 133M   1   0   0   0   0.1   3    0    2   0u   0   0
EVENT     (Cumulative)           TOT WAITS  TIME(s)  AVG_MS PCT  WAIT_CLASS
DB CPU                                          218          68
control file parallel write           5535       30     5.5   9  System I/O
db file sequential read              10080       27     2.7   8  User I/O
os thread startup                      198       25   124.9   8  Concurrency
db file async I/O submit              2575       21     8.0   7  System I/O
ID   SID  SPID USR PROG  PGA OPN SQLID/BLOCKER  E/T STATUS STE WAIT_EVENT  W/T
 1   148  8254 CHF DEDI   2M SEL 4agz3g5aajkdc    0 ACTIVE CPU wa for cpu   0u

参考文档:
oratop – utility for near real-time monitoring of databases, RAC and Single Instance (Doc ID 1500864.1)

在11.2.0.3(从11.2.0.2开始)创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而设置的一个参数,默认为true，即分区表的每个extent为8M,这里对于_partition_large_extents为true和false的情况进行了测试
_partition_large_extents=true

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter deferred_segment_creation
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> show parameter _partition_large_extents;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents             string      FALSE
SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  subpartition by range (bbbbb)
 11  subpartition template
 12  (
 13  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 14  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 15  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 16  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 17  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 18  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 19  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 20  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 21  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 22  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 23  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 24  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 25  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 26  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 27  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 28  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 29  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 30  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 31  subpartition sp_max values less than (maxvalue) tablespace sp_max
 32  )
 33  (partition part_init values less than (1))
 34  enable row movement;
Table created.
--数据库延迟对象创建
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2 like 'SP%' group by TABLESPACE_NAME;
no rows selected
--只插入一个分区1,2013
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),1,'2013',
   2 rpad('aaafdfafd',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
--所有分区全部都创建了segment
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name like 'SP%'
   2 group by TABLESPACE_NAME;
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                           8
SP_2022                                           8
SP_2021                                           8
SP_2025                                           8
SP_2011                                           8
SP_2008                                           8
SP_MAX                                            8
SP_2020                                           8
SP_2012                                           8
SP_2010                                           8
SP_2024                                           8
SP_2019                                           8
SP_2015                                           8
SP_2014                                           8
SP_2013                                           8
SP_2023                                           8
SP_2017                                           8
SP_2016                                           8
SP_2009                                           8
19 rows selected.
SQL> begin
  2  for i in 3 .. 200 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),mod(i,5),
     '2013',rpad('xifenfei',4000,'www.xifenfei.com'));
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
--只是在2013的分区(1,子分区2013)中插入了对象,但是其他分区也都创建了segment(extent)
SQL>  select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2  like 'SP%' group by TABLESPACE_NAME;
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                          32
SP_2021                                          32
SP_2022                                          32
SP_2008                                          32
SP_2011                                          32
SP_2025                                          32
SP_2010                                          32
SP_2012                                          32
SP_2020                                          32
SP_MAX                                           32
SP_2015                                          32
SP_2019                                          32
SP_2024                                          32
SP_2013                                          40
SP_2014                                          32
SP_2023                                          32
SP_2009                                          32
SP_2016                                          32
SP_2017                                          32
19 rows selected.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SYS_SUBP128                    SP_2015
SYS_SUBP148                    SP_2015
SYS_SUBP168                    SP_2015
SYS_SUBP188                    SP_2015
--因为在创建表语句中有partition part_init values less than (1)，隐藏之类对于小于1的分区没有子分区，只有PART_INIT_SP_2013
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
PART_INIT_SP_2013              SP_2013
SYS_SUBP126                    SP_2013
SYS_SUBP146                    SP_2013
SYS_SUBP166                    SP_2013
SYS_SUBP186                    SP_2013

_partition_large_extents=false

SQL> alter system set "_partition_large_extents"=false;
System altered.
SQL> show parameter _partition_large_extents
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents             string      FALSE
SQL> drop table test_com_partition_1 purge;
Table dropped.
SQL> alter system set deferred_segment_creation=true;
System altered.
SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  subpartition by range (bbbbb)
 11  subpartition template
 12  (
 13  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 14  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 15  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 16  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 17  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 18  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 19  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 20  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 21  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 22  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 23  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 24  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 25  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 26  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 27  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 28  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 29  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 30  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 31  subpartition sp_max values less than (maxvalue) tablespace sp_max
 32  )
 33  (partition part_init values less than (1))
 34  enable row movement;
Table created.
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments
   2 where tablespace_name like 'SP%' group by TABLESPACE_NAME;
no rows selected
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),
   2 1,'2013',rpad('aaafdfafd',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
SQL>  select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2 like 'SP%' group by TABLESPACE_NAME;
Tablespace           SUM(BYTES)/1024/1024
-------------------- --------------------
SP_2018                             .0625
SP_2021                             .0625
SP_2022                             .0625
SP_2008                             .0625
SP_2011                             .0625
SP_2025                             .0625
SP_2010                             .0625
SP_2012                             .0625
SP_2020                             .0625
SP_MAX                              .0625
SP_2015                             .0625
SP_2019                             .0625
SP_2024                             .0625
SP_2013                             .0625
SP_2014                             .0625
SP_2023                             .0625
SP_2009                             .0625
SP_2016                             .0625
SP_2017                             .0625
19 rows selected.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP328          SP_2015
SQL>  select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP326          SP_2013
SQL> begin
  2  for i in 3 .. 2000 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),
     mod(i,5),'2013',rpad('xifenfei',4000,'www.xifenfei.com'));
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP328          SP_2015
SYS_SUBP348          SP_2015
SYS_SUBP368          SP_2015
SYS_SUBP388          SP_2015
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
Partition Name       Tablespace
-------------------- --------------------
PART_INIT_SP_2013    SP_2013
SYS_SUBP326          SP_2013
SYS_SUBP346          SP_2013
SYS_SUBP366          SP_2013
SYS_SUBP386          SP_2013

通过测试证明,设置_partition_large_extents参数确实是能够控制分区表的extent大小,而且对于分区表,deferred_segment_creation虽然为true，但是在一个分区表中如果有一个子分区插入了记录,那么其他子分区会同时创建segment.对于数据量不多，而且数据大量集中在某几个分区,那强烈建议设置_partition_large_extents为false,节约空间.如果数据量较大,而且数据分布较为均匀,建议设置_partition_large_extents为true.另外对于分区的index也有同样的参数为_index_partition_large_extents

数据库中记录时间和现实中时间相互转换（如同文件头的kcvfhcrt和v$datafile_header.CREATION_TIME相互转换）
以前写过类似文章，这里提供具体的sql转换语句数据文件的CREATION_TIME来源和算法

--十进制转换为时间
set serveroutput on
declare
v_yyyy number;
v_mm number;
v_dd number;
v_hh number;
v_mi number;
v_ss number;
begin
select floor(&&crt_num/32140800) into v_yyyy from dual;
select floor((&&crt_num-v_yyyy*32140800)/2678400) into v_mm from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400)/86400) into v_dd from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400)/3600) into v_hh from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400-v_hh*3600)/60) into v_mi from dual;
select (&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400-v_hh*3600-v_mi*60) into v_ss from dual;
dbms_output.put_line((1988+v_yyyy)||'-'||(1+v_mm)||'-'||(1+v_dd)||' '||v_hh||':'||v_mi||':'||v_ss);
end;
/
--时间转换为十进制
select
((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'YYYY'))-1988)*12*31*24*60*60) +
    ((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'MM'))-1)*31*24*60*60) +
    (((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'DD'))-1))*24*60*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'HH24'))*60*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'MI'))*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'SS')))
from dual;

接到同事请求,说客户的linux redhat 5.8平台部署的11.2.0.3 RAC 节点2挂掉了,报磁盘IO异常,数据库hang住

Fri Jun 14 12:01:22 2013
Thread 2 advanced to log sequence 369 (LGWR switch)
  Current log# 49 seq# 369 mem# 0: +DATA/q9db/onlinelog/group_49.861.817830099
Fri Jun 14 12:01:22 2013
Archived Log entry 89300 added for thread 2 sequence 368 ID 0x35324053 dest 1:
Fri Jun 14 14:26:18 2013
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_11788.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_11788.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: failed to read mirror side 1 of virtual extent 441 logical extent 0 of file 625
  in group [2.3857217523] from disk DATA_0001
  allocation unit 377890 reason error; if possible, will try another mirror side
Fri Jun 14 14:31:17 2013
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_13767.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_13767.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: failed to read mirror side 1 of virtual extent 441 logical extent 0 of file 625
  in group [2.3857217523] from disk DATA_0001
  allocation unit 377890 reason error; if possible, will try another mirror side

在12点钟数据库运行正常,无任何错误,突然到了14多出现ORA-15025/ORA-27041,并且重启ORACLE 数据库恢复正常。该错误很明显是数据库无权限访问ASM DISK,检查ASM实例日志

Thu Jun 13 19:01:21 2013
ASMB started with pid=25, OS id=25066
Thu Jun 13 19:01:22 2013
NOTE: client +ASM2:+ASM registered, osid 25068, mbr 0x0
WARNING: failed to online diskgroup resource ora.DATA.dg (unable to communicate with CRSD/OHASD)
Thu Jun 13 19:01:24 2013
WARNING: failed to online diskgroup resource ora.OCR_VOTE.dg (unable to communicate with CRSD/OHASD)
Thu Jun 13 19:01:57 2013
NOTE: client q9db2:q9db registered, osid 25732, mbr 0x1
Thu Jun 13 19:02:31 2013
ALTER SYSTEM SET local_listener=' (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.8.33)
 (PORT=1521))))' SCOPE=MEMORY SID='+ASM2';
Fri Jun 14 14:53:09 2013
SQL> ALTER DISKGROUP OCR_VOTE DISMOUNT  /* asm agent *//* {2:61929:97} */
Fri Jun 14 14:53:10 2013
SQL> ALTER DISKGROUP ARCH DISMOUNT  /* asm agent *//* {2:61929:97} */
Fri Jun 14 14:53:10 2013
SQL> ALTER DISKGROUP DATA DISMOUNT  /* asm agent *//* {2:61929:97} */

这里可以明显的看到,ASM实例在该时间点无任何错误,证明一切运行正常,查看系统日志，在该故障点,message中无任何记录,查看asm disk权限

[oracle@q9db02 trace]$ ll /dev/mapper/
total 0
crw------- 1 root root    10, 60 Jun  9 11:08 control
brw-rw---- 1 grid asmdba 253, 15 Jun 14 16:20 q9datalun1
brw-rw---- 1 grid asmdba 253, 16 Jun 14 16:20 q9datalun2
brw-rw---- 1 grid asmdba 253, 17 Jun 14 16:20 q9datalun3
brw-rw---- 1 grid asmdba 253, 18 Jun 14 16:19 q9datalun4
brw-rw---- 1 grid asmdba 253, 19 Jun 14 16:20 q9datalun5
brw-rw---- 1 grid asmdba 253, 20 Jun 14 16:20 q9datalun6
brw-rw---- 1 grid asmdba 253, 21 Jun 14 16:19 q9datalun7
brw-rw---- 1 grid asmdba 253,  4 Jun 14 16:20 q9datalun8
brw-rw---- 1 grid asmdba 253,  5 Jun 14 16:20 q9votelun1

所有文件权限没有任何问题,和当初部署之时完全相同而且运行了一段时间都正常,部署之时权限

[oracle@q9db02 trace]$ more /etc/rc.local
chown grid:asmdba /dev/mapper/q9votelun1
chmod 660 /dev/mapper/q9votelun1
chown grid:asmdba /dev/mapper/q9datalun1
chmod 660 /dev/mapper/q9datalun1
chown grid:asmdba /dev/mapper/q9datalun2
chmod 660 /dev/mapper/q9datalun2
chown grid:asmdba /dev/mapper/q9datalun3
chmod 660 /dev/mapper/q9datalun3
chown grid:asmdba /dev/mapper/q9datalun4
chmod 660 /dev/mapper/q9datalun4
chown grid:asmdba /dev/mapper/q9datalun5
chmod 660 /dev/mapper/q9datalun5
chown grid:asmdba /dev/mapper/q9datalun6
chmod 660 /dev/mapper/q9datalun6
chown grid:asmdba /dev/mapper/q9datalun7
chmod 660 /dev/mapper/q9datalun7
chown grid:asmdba /dev/mapper/q9datalun8
chmod 660 /dev/mapper/q9datalun8
chown grid:asmdba /dev/mapper/q9datalun8
chmod 660 /dev/mapper/q9datalun8

因为这里权限没有任何改变,而且asm disk权限正确,系统日志无任何日志,证明该问题不是因为ASM DISK权限改变导致,那我怀疑是人做了不该做的操作,比喻临时性修改了ASM DISK权限,然后有修改回来了,或者是不正常的用户操作了数据库,而这些操作更加可能是root用户操作,分析root用户操作记录

--history部分记录
  803  su  oracle
  804  exit
  805  cd /tmp
  806  ls
  807  cd sysbench/
  808  cd bin/
  809  ls
  810  ORACLE_SID=q9db2
  811  export ORACLE_BASE
  812  export ORACLE_HOME
  813  ./sysbench --test=oltp --oltp-table-name=sysbench --oltp-table-size=1 --oracle-db=Q9DB
       --oracle-user=sysbench --oracle-password=sysbench --db-driver=oracle  prepare
  814  syssql
  815  sqlplus system/sysbench@q9db02
  816  sqlplus system/q9db@q9db02
  817  echo $ORACLE_HOME
  818  cd $ORACLE_HOME/network/
  819  vi admin/tnsnames.ora
  820  sqlplus system/NEWQ9DB
  821   echo $ORACLE_HOME
  822  vi ~/.bash_profile
  823   echo $ORACLE_SID
  824  ps -ef | grep smon
  825  sqlplus system/NEWQ9DB
  826  exit

这里很明显的看到,由于SA想使用sysbench做系统基线测试,使用了root用户登录数据库并进行了相关操作,从而出现了该问题,因为ASM DISK 所有者是grid:asmdba,权限是660,root用户无法对ASM DISK进行读写操作,从而出现了上述错误。让同事协助SA重现上述操作,果然出现完全相同的错误,而且退出root session,数据库恢复正常

Fri Jun 14 15:44:24 2013
Archived Log entry 89330 added for thread 2 sequence 389 ID 0x35324053 dest 1:
Fri Jun 14 15:50:42 2013
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: failed to read mirror side 1 of virtual extent 473 logical extent 0 of file 625
  in group [2.3857045540] from disk DATA_0001
  allocation unit 377894 reason error; if possible, will try another mirror side
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun4"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/q9db/datafile/system.625.817825255'
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun4"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/q9db/datafile/system.625.817825255'
ORA-15081: failed to submit an I/O operation to a disk
WARNING: failed to read mirror side 1 of virtual extent 652 logical extent 0 of file 625
  in group [2.3857045540] from disk DATA_0003
  allocation unit 377939 reason error; if possible, will try another mirror side
Fri Jun 14 15:55:58 2013
Thread 2 advanced to log sequence 391 (LGWR switch)
  Current log# 41 seq# 391 mem# 0: +DATA/q9db/onlinelog/group_41.853.817830085
Fri Jun 14 15:55:58 2013
Archived Log entry 89331 added for thread 2 sequence 390 ID 0x35324053 dest 1:
Thread 2 advanced to log sequence 392 (LGWR switch)
  Current log# 42 seq# 392 mem# 0: +DATA/q9db/onlinelog/group_42.854.817830087

在ASM ORACLE RAC环境中,使用root操作oracle 数据库导致该错误,强烈建议:操作oracle数据库,请使用oracle数据库安装用户(最少也是同一个所属组用户)运行,超级用户root对于oracle来说也不是万能的

从11.1开始密码大小写敏感了，同时可以设置sec_case_sensitive_logon参数可以忽略大小写敏感。
通过朋友咨询的不设置sec_case_sensitive_logon参数让密码大小写不敏感的实验这篇文章疑惑,同时也感谢朋友让我学习到了新知识,我通过测试证明了如下结论：
1.password_versions的优先级大于sec_case_sensitive_logon
2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感

10g创建用户导出数据

SQL> create user ora10g identified by xifenfei;
User created.
SQL> grant connect to ora10g;
Grant succeeded.
C:\Documents and Settings\Administrator>expdp \"/ as sysdba \" DIRECTORY=exp_dp
DUMPFILE=chf.dmp schemas=ora10g
Export: Release 10.2.0.1.0 - Production on 星期三, 15 5月, 2013 22:59:45
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=exp_dp DUMPF
ILE=chf.dmp schemas=ora10g
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 0 KB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
SYS.SYS_EXPORT_SCHEMA_01 的转储文件集为:
  C:\CHF.DMP
作业 "SYS"."SYS_EXPORT_SCHEMA_01" 已于 23:00:19 成功完成

11g创建用户

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter logon
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> create user ora11g identified by xifenfei;
User created.
SQL> grant connect to ora11g;
Grant succeeded.
SQL> conn ora11g/xifenfei
Connected.
SQL> conn ora11g/XIFENFEI
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

这里证明,在sec_case_sensitive_logon=true的情况下,数据库密码是大小写敏感

导入10g创建用户dmp文件

[oracle@localhost ~]$ impdp '"/ as sysdba"' directory=exp_dp dumpfile=CHF.DMP
Import: Release 11.2.0.3.0 - Production on Wed May 15 23:07:20 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=exp_dp dumpfile=CHF.DMP
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 23:07:21

查询ora10g和ora11g用户区别

SQL> conn ora10g/xifenfei
Connected.
SQL> conn ora10g/XIFENFEI
Connected.
SQL> conn / as sysdba
Connected.
SQL>  select t.username,t.account_status,t.password_versions from dba_users t where t.username in ('ORA11G','ORA10G');
USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- --------
ORA11G                         OPEN                             10G 11G
ORA10G                         OPEN                             10G
SQL> select name,password,spare4 from SYS.USER$ t where name in ('ORA11G','ORA10G');
NAME                           PASSWORD                       SPARE4
------------------------------ ------------------------------ ----------------------------------------------------------------
ORA10G                         F3CF2F0CB35CB6CA
ORA11G                         559D84354181EB8E               S:BFE2625310D9382E9AEA6EE0AA2988E82C17B3EA23E3DAC23800490C2621

这里可以发现我们从低版本(10g)导入到11g中的用户登录是不区分大小写,而11g本身创建的用户是区分大小写,而他们的区别仅仅是在dba_users.password_versions中有不一样,跟踪到基表发现就是USER$.SPARE4列不一样(10g的该列为空).
猜想:password_versions的优先级大于sec_case_sensitive_logon这个参数

验证猜想
如果是password_versions的优先级大于sec_case_sensitive_logon那么，如果我修改了USER$.SPARE4,使得dba_users.password_versions变成和10g导入的库一样,是否就可以实现不区分密码大小写的问题,如果不缺乏证明：password_versions的优先级大于sec_case_sensitive_logon这个参数,反之失败.

SQL> update SYS.USER$ t set SPARE4='' where name='ORA11G';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> conn ora11g/XIFENFEI
Connected.
SQL> conn ora11g/xifenfei
Connected.

由此得出两个结论:
1.password_versions的优先级大于sec_case_sensitive_logon
2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感

32-Bit Patches :

64-Bit x64 Patches :

64-Bit Itanium Patches :