FAQ of ORACLE 1

1.1.1     Oracle 撤销public权限导致系统全部中断

现场反馈使用oem修改部分用户的权限后，很多存储过程变成无效，所有的查询执行出错，应用系统全部中断。即使是sys用户查询tab视图，也会得到ORA-06553的错误

ORA-06553: PLS-213: package STANDARD not accessible

PL/SQL编译器不能在数据库中找到standard包。要编译一个程序或者执行一个dml语句，PL/SQL都需要standard包

进一步了解情况，现场修改了perfstat和public的权限。将perfstat除了select以外的权限全部收回，但无法描述public收回的具体权限。
1、要求现场执行$ORACLE_HOME/rdbms/admin/spcusr.sql中所有的grant语句，恢复perfstat的所有权限
2、根据错误提示，让现场首先编译STANDARD包
       ALTER PACKAGE STANDARD COMPILE;
3、将STANDARD包的权限授予public，即所有用户
      grant execute on STANDARD to public;
4、grant执行成功后，所有的业务恢复

perfstat和public都是系统用户，不可以更改它们的权限。如果需要根据SOX法案加固数据库，一定要根据已经验证的方案和语句来做，切不可随意在数据库中更改或者撤销权限，尤其是在不清楚这些用户和权限的含义的情况下。

1.1.2     ORACLE9I中用EXP工具导数据时候出现EXP-00003错误

在ORACLE9I中用EXP做按用户导数据的动作的时候，部分用户表会出现‘EXP-00003: no storage definition found for segment ...’错误，表数据导出失败。

在EXP日志表中出现EXP-00003: no storage definition found for segment .....错误，出现该错误的表数据导出失败。

该问题是ORACLE的BUG造成的，需要修改视图exu9tne

执行以下语句：
CREATE OR REPLACE VIEW exu9tne 
(tsno, fileno, blockno, length) 
AS  SELECT ts#, segfile#, segblock#, lengthFROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB/ 
然后通过EXP工具导出数据可以成功
然后执行
CREATE OR REPLACE VIEW exu9tne 
(tsno, fileno, blockno, length) 
AS  SELECT ts#, segfile#, segblock#, lengthFROM sys.uet$
WHERE ext# = 1
将视图恢复回去。

1.1.3     Oracle 用exp导出大表报ora-01555错导致备份失败的处理案例

某局点用oracle的exp工具每天凌晨导出数据，但随着数据库数据量的增加，导出的时间越来越长。而且经常执行exp几小时之后失败，报以下错误：

EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 48 with name "RBS1_1" too small
EXP-00000: Export terminated unsuccessfully

Oracle 在做exp导出时会保证每张表的读一致性，即每张表开始导出，到这张表导出结束，这期间对该表的任何改动对exp都不可见，也就是exp会保证读出的数据是该表开始导出时的数据。如果这期间该表内容被修改，exp会从回滚段中读出修改前的数据。但有时因为被导出的表很大，需要导很长时间。这期间如果不断有业务程序修改该表并提交修改，oracle可能会把部分回滚段的内容清空重用（因为事务提交后该事务所用的回滚段就可以重用），这时exp就读不到该表修改前的数据，因此会报ORA-01555: snapshot too old: rollback segment number 48 with name "RBS1_1" too small。
解决这种问题靠增加回滚段或undo tablespace空间是没用的，关键是要保证exp导出大表时该表不能被修改

将exp的执行时间改在半夜业务量很少的时间段，并在exp命令中加入direct=y参数加快导出速度后，导出成功。
 

1.1.4     Oracle 数据库磁盘IO利用率为百分之百问题性能调整

某局数据库系统主机通过top命令查看发现磁盘的全天的利用率为100%。
tty:      tin         tout   avg-cpu:  % user    % sys     % idle    % iowait
          0.0        160.2              18.2      2.6       45.1      34.1
 
Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
hdisk0           4.3      46.6       9.7          0       140
hdisk1           4.3      46.6       9.7          0       140
hdisk2          99.9     18222.2     749.9      54344       353

 

磁盘IO利用率过高会导致数据库的性能问题，正常系统的IO的利用率在50%以下，以保证系统有足够的IO冗余应对突发业务的变化。
导致IO过高的因素往往是大量的磁盘读些操作，在数据库中可能是没有正确使用索引或表碎片，需要通过statspack报告进一步分析。

 

第一步、对数据库作两个快照，时间间隔为15分钟。
sql> execute statspack.snap
第二步、生成统计报告。
sql>@spreport.sql
第三步、读性能报告的reads  for DB 部分。
发现如下语句读磁盘操作的块最多：
SELECT SERIALNO,DWCODEPROTOCOL,DWDRIVERPROTOCOL,PDRIVERPARAM,SYS
TEMID,PASSWORD,ORGADDR,DESTADDR,USERDATA1,USERDATA2,USERDATA3,US
ERDATA4,USERDATA5 FROM T_SCEAUTOSM WHERE SENDTIME < SYSDATE AND
ORDERTIME < SYSDATE - 1/1440 AND SMTRYNUM < :B1 AND SMFLAG >0 AN
D ROWNUM <2 FOR UPDATE NOWAIT
第四步、分析该语句的执行计划，发现是全表扫描。
sql> explain plan for 
SELECT SERIALNO,DWCODEPROTOCOL,DWDRIVERPROTOCOL,PDRIVERPARAM,SYS
TEMID,PASSWORD,ORGADDR,DESTADDR,USERDATA1,USERDATA2,USERDATA3,US
ERDATA4,USERDATA5 FROM T_SCEAUTOSM WHERE SENDTIME < SYSDATE AND
ORDERTIME < SYSDATE - 1/1440 AND SMTRYNUM < :B1 AND SMFLAG >0 AN
D ROWNUM <2 FOR UPDATE NOWAIT;
sql> select *  from table(dbms_xplan.display)
第五步、为该表建立索引。
create index IDX_T_SCEAUTOSM ON T_SCEAUTOSM(SENDTIME,ORDERTIME,SMTRYNUM) TABLESPACE  SERVICE_LOG_IDX;
第六步、10分钟后检查系统，发现磁盘IO利用率下降到10%， 重做statspack快照生成性能报告，在 reads for DB 部分没发现该语句。

 

处理一般数据库性能问题的简易办法是通过statspack生成性能报告，然后根据问题的类型，如IO问题找reads for  DB部分的SQ最前面的几条语句，然后对该语句作执行计划分析，可以找出问题根本原因。

 

 

1.1.5     Oracle 如何定位数据库进程开销CPU过大的问题

现网系统当业务系统出现数据库响应问题，检查系统发现某一个或几个ORACLE进程占有很多CPU资源异常。

统可能没有告警信息，但数据库响应很慢，有的情况从alert日志中可以看出日志切换异常频繁

造成用户进程占用CPU 资源多的原因可能是大量的数据读取或写，与此联系最紧密的是SQL语句的执行计划出现异常或业务逻辑设计不合理，因此建议直接与导致该问题的SQL 语句入手，分析问题。

第一步、使用top 命令，找出占CPU资源最多的进程ID。
如进程id为14356
第二步、根据进程ID，需要通过如下操作方法获取问题sql语句。
select sid,serial# from v$session where paddr in ( select addr from v$process where spid=14356
)；
37 3462
select sql_text from v$sqltext where hash_value in (select SQL_HASH_VALUE from v$session where sid=37  order by piece; 
SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
第三步、找到导致问题的语句后，对该语句做执行计划分析。
SQL> explain plan for
 SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]
第四步、由此可以确认是由于该SQL语句的全表扫描操作导致CPU资源利用过多

这种问题很常见，通常在系统数据量发生某些变化或某些操作导致系统的某些程序突然变的很慢，通过如上步骤,可以很快定位问题。

1.1.6     Oracle-ORA-29807错误解决办法

在ORACLE建库过程中，安装到80%左右，跳出ORA-29807: specified operator does not exist。

这是ORACLE的一个bug，可以忽略掉。
此错误解释：
ORA-29807: specified operator does not exist 
This is a known issue (bug 2925665). You can click on the "Ignore" button to continue. 
Once DBCA has completed database creation, remember to run the 'prvtxml.plb' script from $ORACLE_HOME/rdbms/admin independently, as the user SYS. It is also advised to run the 'utlrp.sql' script to ensure that there are no invalid objects in the database at this time.

安装完成后，在$ORACLE_HOME/rdbms/admin 目录下运行utlrp.sql ，用于修补ORA-29807 错误

1.1.7     ORACLE 数据库查询语句性能优化案例

某局近期话务员反映工作流系统反映较慢，有时候打开工单页面需要等待一段时间。经检查网络方面正常，初步判断是数据库执行存储过程时较慢所致。

系统反应较慢，需要延迟1~2秒钟的时间

通过调试工作流页面所调用存储过程，发现在执行一个SQL语句时需要较长时间，SQL语句如下：
SELECT COUNT(1)
  INTO v_Flag
FROM t_wf_processhis t
WHERE t.serialno = v_SerialNo
  AND t.serviceclassid = v_ServiceClassID，
把该语句单独拿出来调试，每次执行时间为2秒左右，在PL/SQL Developer上用F5键查看其执行计划，用到的是全索引扫描

由于表t_wf_processhis是按照旬进行分区的分区表，即每个月的数据存放在3个分区中，如果对该表的查询使用全索引扫描的话，势必造成查询性能的下降，把SQL语句进行更改，使用分区查询，修改后的SQL语句如下：
SELECT COUNT(1)
  INTO v_Flag
FROM t_wf_processhis t
WHERE t.serialno = v_SerialNo
  AND t.serviceclassid = v_ServiceClassID
  AND t.monthday = substr(v_SerialNo,5,4);
经过优化后，该SQL语句性能得到极大提高，查询过程只需要0.1秒左右，提升了将近20倍。写SQL语句时，要多注意一下SQL语句的执行效率，有时一个小小的改进，就能带来很大的性能提升。

 

1.1.8     oracle数据库安装出现no shell的问题

我先建立一个oracle用户shell 指定为/usr/bin/bash，然后用su - oracle进入安装完成oracle时，提示用root用户执行root.sh。执行root.sh后，退回到#再执行su – oracle显示no shell

系统显示no shell

执行root.sh后更改了/usr/bin/下的bash文件

分析得出是因为在oracle的HOME目录下编辑.profile文件时候，环境变量设置出现错误，改变了bash文件，且发现bash文件被修改成字符文件，不是那种密文格式。从另外一台系统相同的小型机上copy一个bash文件，然后安装成功。 

 

1.1.9      ora-01102错误的处理方法

 

[ZJT63783A]/home/oracle> sqlplus "/ as sysdba"

 

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Dec 18 15:19:07 2010

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startupORACLE instance started.

 

Total System Global Area 2137886720 bytesFixed Size                  2139912 bytesVariable Size            1191184632 bytesDatabase Buffers          939524096 bytesRedo Buffers                5038080 bytesO

RA-01102: cannot mount database in EXCLUSIVE mode

 

ORA-01102问题的处理方法

现象描述：当启动数据库时，得到以下错误，启动失败：

ORA-01102 cannot mount database in EXCLUSIVE mode

 

原因分析：当启动数据库遇到1102报错时，之前的数据库的down操作一般都不是正常完成的，或由于一些异常使Oracle在操作系统中残留一些内存结构，Pmon等一几个进程依然存在等原因使Oracle误认

为Instance依然在运行着，所以库就没有启动，具体说来大体原因有如下几个：

1、pmon、smon、lwgw及dbwr这些后台进程依然存在着

2、Oracle开辟的共享内存没有释放掉

3、"lk<sid>" and "sgadef<sid>.dbf"这两个用于锁内存的文件存在着。

 

 

处理过程：  1、看一下"lk<sid>" and "sgadef<sid>.dbf"这两个文件是不是存在着，如果存在将其删掉。

$cd $ORACLE_HOME/dbs

$ls -l sgadef<sid>.dbf

如果存在删掉它

$rm sgadef<sid>.dbf

$ls -l lk<sid>

如果存在删掉它

$rm lk<sid>

2、看是不是有后台进程存在了

$ps -ef | grep ora_ | grep $ORACLE_SID

如果有pmon这些后台进程的残留，kill -9掉它

$kill -9 pid

3、看一下oracle的共享内存段及信号集(semaphores)是不是还存在着

1)清共享内存段

$ipcs -m --显示一下,看owner是Oracle用户的

$ipcrm -m <Shared_Memory_ID>

2)清信号集

$ipcs -s --显示一下,看owner是Oracle用户的

$ipcrm -s <Semaphore_ID>