ORACLE数据库自带的opath,Oracle数据库常见真实问题处理步骤

【IT168技术文章】

1 解决Oracle 9.2.0.6版本数据库由于ORA-07445宕机问题故障现象：

XX网数据库宕机，查看日志发现以下内容：

Wed Jun820:24:172005Errorsinfile/u02/app/oracle/admin/unicom/udump/unicom_ora_661.trc:

ORA-07445: \263\366\317\326\322\354\263\243: \272\313\320\304\327\252\264\242[0000000101C3089C][SIGSEGV][Address not mappe

d to object][0x000000000][][]Wed Jun820:24:222005Errorsinfile/u02/app/oracle/admin/unicom/bdump/unicom_pmon_11598.trc:

ORA-07445: exception encountered: coredump[0000000101C399A0][SIGSEGV][Address not mapped to object][0x000000000][][]Wed Jun820:24:232005Errorsinfile/u02/app/oracle/admin/unicom/bdump/unicom_pmon_11598.trc:

ORA-07445: exception encountered: coredump[0000000101C399A0][SIGSEGV][Address not mapped to object][0x000000000][][]ORA-00602: internal programming exception

ORA-07445: exception encountered: coredump[0000000101C399A0][SIGSEGV][Address not mapped to object][0x000000000][][]Wed Jun820:24:332005CKPT: terminating instance duetoerror472Instance terminatedbyCKPT, pid=11604Wed Jun821:04:472005Starting ORACLE instance (normal)

解决办法：

Oracle工程师建议安装Oracle补丁p3949307_9206_SOLARIS64，经过测试，安装步骤如下： (注意，首先shutdown数据库)

1，解压补丁文件

unzip p3949307_9206_SOLARIS64.zip

解开后的目录是：4060756

2，修改oraclehomeproperties.xml文件，该文件在$ORACLE_HOME/inventory/ContentsXML目录下。

cp oraclehomeproperties.xml oraclehomeproperties.xmlb.bak

vi oraclehomeproperties.xml

更改数字453 －>23,存盘退出

3，修改PATH路径为

PATH=$ORACLE_HOME/bin:/usr/ccs/bin:${PATH}

4，执行opatch apply命令

cd 4060756

$ORACLE_HOME/OPatch/opatch apply

5，安装成功后会出现如下结果

Updating inventory...

/oracle92/app/oracle/product/9.2.0.1/OPatch/opatch.pl version: 1.0.0.0.51

Copyright (c) 2001-2004 Oracle Corporation. All Rights Reserved.

OPatch succeeded.

2 查看Oracle表空间大小及利用率的SQL语句－非常实用的语句

setlinesize300col 表空间名fora30SELECTUPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME) F,

(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2) TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILES DDGROUPBYDD.TABLESPACE_NAME) DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;