oracle数据库常见问题处理

1 归档日志文件系统满

 

现象：

1、服务器文件系统使用率到达100%；

2、应用程序连接数据库时出现ORA-00257: archiver error. Connect internal only, untilfreed 报错，数据库挂起无法访问。

 

分析：

1、执行sqlplus / as sysdba 命令连接数据库；

2、执行archive log list 命令确认oracle 的归档日志目录；

3、使用操作系统命令df –g，查看归档日志目录使用率是否已满。

 

处理：

因数据库归档日志信息记录在数据库控制文件中，直接删除归档日志虽能释放存储空间，但操作系统不会自动更新数据库控制文件中的相关信息，并且在查询相关动态视图(如v$archived_log)时，该部分已被删除的日志仍会标注为存在，这种情况将导致数据库在使用已被删除的归档日志进行恢复时出现错误。因此，即便归档日志目录已满，也不推荐直接从目录中删除归档日志。

可使用以下方法清理归档日志目录：

1、使用SYSDBA 角色（通常为oracle 用户）执行rman target/ 命令进入rman 工具操作界面；

2 、在rman 工具界面执行DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'；命令删除7 天以前的归档日志。具体需要删除多少天的日志，应根据归档日志目录使用率酌情确定，确定保留天数后，修改'SYSDATE-7'参数中的数字即可。

3 、为稳妥起见， 删除归档日志前， 可执行backup format '/backup/arch_%T_%s_%U' archivelog all delete input；命令将归档日志备份到其他文件系统中。

4、若在之前已直接将归档日志目录中的部分归档日志文件删除，可执行crosscheck archivelog all; 和delete expired archivelog all;命令清理掉已经删除的归档日志记录，并更新数据库控制文件。

 

 

2 数据库表空间使用率满

 

现象：

1、数据库表空间使用率到达100%；

2、应用程序连接数据库时出现ORA-01658: unable to create INITIAL extent for segment in tablespace **报错，数据库无法处理事务。

 

分析：

1、执行sqlplus / as sysdba 命令连接数据库；

2、执行如下SQL 语句，查看表空间使用率：

prompt 自扩展表空间使用率

select total.tablespace_name as 表空间名,

       round(total.MB, 2) as 总空间量,

       round(now.MB, 2) as 已分配量,

       round(now.MB - free.MB, 2) as 已使用配量,

       round(((now.MB - free.MB) / now.MB) * 100, 2) as 已分配使用率,

       round(((now.MB - free.MB) / total.MB) * 100, 2) as 总使用率

  from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

          from dba_free_space

         group by tablespace_name) free,

       (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

          from dba_data_files

         group by tablespace_name) now,

       (select tablespace_name, sum(maxbytes) / 1024 / 1024 as MB

          from dba_data_files

         where maxbytes > 0

           and autoextensible = 'YES'

         group by tablespace_name) total

 where free.tablespace_name = total.tablespace_name

   and now.tablespace_name = total.tablespace_name

 order by 总使用率 desc;

 

prompt 固定表空间使用率

select now.tablespace_name as 表空间名,

       round(now.MB, 2) as 总空间量,

       round(now.MB - free.MB, 2) as 已使用配量,

       round(((now.MB - free.MB) / now.MB) * 100, 2) as 总使用率

  from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

          from dba_free_space

         group by tablespace_name) free,

       (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

          from dba_data_files

         where autoextensible = 'NO'

         group by tablespace_name) now

 where free.tablespace_name = now.tablespace_name

 order by 总使用率 desc;

 

3、输出结果类似如下界面：

表空间名总空间量已分配量已使用配量已分配使用率总使用率

--------------------------- ---------- ---------- ------------ ----------

PPPP 259071.94 218964 218959.44 100 84.52

PPPP_IDX 163071.94 43248 43146.81 99.77 26.46

PATROL_DATA 300 200 6.63 3.31 2.21

SYSAUX 131071.94 700 667.31 95.33 .51

SYSTEM 131071.94 570 567.38 99.54 .43

UNDOTBS1 131071.94 14550 323.63 2.22 .25

USERS 131071.94 5 .13 2.5 0

7 rows selected.

4、由输出结果可知，PPPP表空间使用率已超过阀值，需要进行扩容。

5、使用操作系统命令df –g，查看数据库数据文件所在文件系统名（通常为/ora_data文件系统）；使用操作系统命令ps –ef|grep smon，查看数据库实例名（本例中数据库实例为pppp），由以上信息可确定待扩容数据文件存放的位置为/ora_data/pppp。

 

处理：

1、执行sqlplus / as sysdba 命令连接数据库；

2、执行ALTER TABLESPACE "PPPP" ADD ATAFILE '/ora_data/pppp/pppp03.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M;为PPPP 表空间增加一个名为pppp03.dbf 数据文件，初始大小SIZE 为1024M，自动扩展属性AUTOEXTEND 打开，每次自动扩展100M。

3、若扩展表空间之前发现数据文件所在文件系统空间不足，则需要先对文件系统进行扩容，使用root 用户执行chfs –a size=+30G /ora_data 可为/ora_data 文件系统增加30G 存储空间，然后再增加数据文件。

4、若该数据库是使用裸设备存储数据文件，则需要先使用root 用户执行mklv -y'pppp03dbf' sharedbvg 10 创建一个裸设备，然后执行ALTER TABLESPACE "PPPP" ADD ATAFILE '/dev/pppp03dbf' SIZE 10240M;为PPPP 表空间增加一个10G 的裸设备存储。

 

 

3 数据库数据被误删除

 

现象：

数据库中某个表的数据被误删除或者被错误更新，需要恢复。

 

分析：

1、与操作人员确认误删除数据的准确时间点及内容；

2、与操作人员确认误操作的类型是DML 操作（insert、delete、update）还是DDL 操作（drop）。

 

处理：

1、DML 误操作（insert、delete、update）

前提条件：重做日志信息（undo）信息没有被复写；误操作发生的时间点确定；数据库无需开启闪回（flash back）功能。

恢复操作如下：

创建一个新表，通过基于时间点的查询将数据恢复到新表上：

SQL> create table 表名_new as select (*) from 表名 as of timestamp to _timestamp('时间点','yyyy-mm-dd hh24:mi:ss');

将原表重命名为备份表：

SQL> alter table rename 表名 to 表名_bak；

将恢复后的新表重命名为原表：

SQL> alter table rename 表名_new to 表名；

检查重命名后的新表数据是否正确：

SQL> select count(*) from 表名_new;

2、DDL 操作（drop）

前提条件：重做日志信息（undo）信息没有被复写；误操作发生的时间点确定；未使用drop table purge 命令清空回收站；数据库无需开启闪回（flash back）功能。

恢复操作如下：

查询事发时间点对应的回收站记录：

SQL> select * from user_recyclebin;

通过记录在段中的回收站信息将drop 掉的表闪回：

SQL> flashback table "BIN$*******************" to before drop rename to 表名;

检查表中的数据是否正确：

SQL> select count(*) from 表名;

 

 

4 数据库监听程序宕

 

现象：

数据库监听出现异常，应用无法连接数据库。

 

分析：

使用oracle 用户执行lsnrctl status 命令查看监听的状态，若监听程序不存在或无法连接，则表明不能连接数据库的原因是数据库监听程序宕。

 

处理：

使用oracle 用户执行lsnrctl start 命令手工启动数据库监听。

 

 

5 数据库响应速度慢

 

现象：

1、数据库响应慢，部分或者全部业务响应时间超时；

2、数据库服务器资源消耗较大，内存、IO 或CPU 资源使用率高。

 

分析：

1、使用oracle 用户连接数据库，执行如下SQL 语句查看数据库中的等待事件：

set lines 200

col username format a8

col osuser format a8

col machine format a15

col sid format 99999

col event format a30

select /*+ rule */

 a.sid, a.serial#, a.osuser, a.username, a.machine, a.SQL_ID, b.event

  from v$session a, v$session_wait b

 where b.event not like 'SQL%'

   and b.event not like '%mon%'

   and b.event not like 'rdbms%'

   and b.wait_class != 'Idle'

   and b.sid = a.sid

 order by machine, event, SQL_ID;

2、通过等待事件的SQL_ID，执行如下SQL 语句可以定位到具体的应用程序SQL 文本，并查看该SQL 的执行计划：

set linesize 132

select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));

3、通过分析现有执行计划，以及该SQL 所涉及表的索引信息，可判断当前执行计划是否最优。若不为最优，则需要使用重建索引、新建索引、更新统计信息或重新编写SQL 语句等方法进行优化。

 

处理：

1、新建索引：

CREATE INDEX 索引名ON 表名(主键名) TABLESPACE 表空间名；

2、重建索引：

alter index 用户名.索引名 rebuild online;

该方法可在线做，效率较高。

3、更新统计信息：

exec dbms_stats.gather_table_stats('用户名','表名');

4、查看索引状态

select * from user_indexes where status <>'VALID';

 

 

6 数据库坏块

 

现象：

数据库无法访问或事务执行失败，查询$ORACLE_BASE/admin/$ORACLE_SID/bdump 下的数据库报警日志文件alert_$ORACLE_SID.log 时，出现ORA-01578：ORA-01578: ORACLE data block corrupted (file # 2, block # 31061)错误信息。

 

分析：

1、使用oracle 用户执行dbv FILE=/app/oracle/oradata/epm/数据文件名命令检查报错的数据文件；执行SQL> select * from v$database_block_corruption;查询坏块的详细信息。

2、若数据库已开启归档，可使用oracle 用户执行如下命令：

验证全库

run {

allocate channel d1 type disk;

allocate channel d2 type disk;

backup validate check logical database;

}

验证某个文件

RMAN> backup validate 数据文件名;

 

处理：

1、若数据库已开启归档，可利用rman 备份恢复数据块。操作如下：

RMAN> blockrecover 数据文件名坏块编号 from backupset;

2、若rman 备份无法恢复或未开启归档，则需要丢弃坏块的数据，此时会丢失部分数据。操作如下：

更改当前连接的属性，跳过坏块：

SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';

创建一个新表，将原表中的数据全部插入新表：

SQL> create table 表名_new as select * from 表名;

将原表重命名为备份表：

SQL> alter table rename 表名 to 表名_bak；

将恢复后的新表重命名为原表：

SQL> alter table rename 表名_new to 表名；

检查重名后的新表数据是否正确：

SQL> select count(*) from 表名;

 

 

7 RAC 问题应急处理

 

现象：

执行crsctl status res –t 命令查看rac资源时，出现offline或者其它异常状态。

 

分析：

使用grid用户登录系统，执行crsctl status res –t 命令查看资源状态，正常情况下，应该只有ora.gsd 为OFFLINE，其余应该都是ONLINE。以密钥管理系统为例，输出如下：

$ crsctl status res -t

NAME TARGET STATE SERVER STATE_DETAILS

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

ONLINE ONLINE yzh-sc-mygl-m-1

ONLINE ONLINE yzh-sc-mygl-m-2

ora.GRID.dg

ONLINE ONLINE yzh-sc-mygl-m-1

ONLINE ONLINE yzh-sc-mygl-m-2

ora.LISTENER.lsnr

ONLINE ONLINE yzh-sc-mygl-m-1

ONLINE ONLINE yzh-sc-mygl-m-2

ora.asm

ONLINE ONLINE yzh-sc-mygl-m-1 Started

ONLINE ONLINE yzh-sc-mygl-m-2 Started

ora.gsd

OFFLINE OFFLINE yzh-sc-mygl-m-1

OFFLINE OFFLINE yzh-sc-mygl-m-2

ora.net1.network

ONLINE ONLINE yzh-sc-mygl-m-1

ONLINE OFFLINE yzh-sc-mygl-m-2

ora.ons

ONLINE ONLINE yzh-sc-mygl-m-1

ONLINE ONLINE yzh-sc-mygl-m-2

ora.registry.acfs

ONLINE ONLINE yzh-sc-mygl-m-1

ONLINE ONLINE yzh-sc-mygl-m-2

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE yzh-sc-mygl-m-1

ora.cvu

1 ONLINE ONLINE yzh-sc-mygl-m-2

ora.oc4j

1 ONLINE ONLINE yzh-sc-mygl-m-2

ora.scan1.vip

1 ONLINE ONLINE yzh-sc-mygl-m-1

ora.tsscdb.db

1 ONLINE ONLINE yzh-sc-mygl-m-1 Open

2 ONLINE ONLINE yzh-sc-mygl-m-2 Open

ora.tsscdb.tsscdbsrv.svc

1 ONLINE ONLINE yzh-sc-mygl-m-1

ora.yzh-sc-mygl-m-1.vip

1 ONLINE ONLINE yzh-sc-mygl-m-1

ora.yzh-sc-mygl-m-2.vip

1 ONLINE ONLINE yzh-sc-mygl-m-2

 

处理：

1、若某资源offline，可使用grid 用户执行crsctl start res 资源名建议尝试手工启动该资源。

2、若手工不能启动该资源，可使用oracle 用户执行srvctl 命令启停rac 服务：

srvctl stop database -d racdb

srvctl start database -d racdb

3、也可使用root 用户执行/$ORACLE_BASE/product/11.2.0/grid/bin/crsctl stop cluster 尝试停止该资源所在节点的rac 服务

4、若在停止rac 服务过程中，rac 管理的资源中有任何一个还在运行，则整个命令会执行失败。必须使用/$ORACLE_BASE/product/11.2.0/grid/bin/crsctl stop cluster –all 或

/$ORACLE_BASE/product/11.2.0/grid/bin/crsctl stop cluster -n rac01 rac02 命令在所有rac 节点上无条件地停止所有资源及rac 服务。

5、执行/$ORACLE_BASE/product/11.2.0/grid/bin/crsctl start cluster 命令启动rac 节点；或执行/$ORACLE_BASE/product/11.2.0/grid/bin/crsctl stop cluster –all 或

/$ORACLE_BASE/product/11.2.0/grid/bin/crsctl start cluster -n rac01 rac02 命令启动所有的rac节点。