Bruce's Oracle DBA Toolkit

Oracle发布高危安全补丁(CPUApr2007)

2007-04-18T19:35:00.000-07:00

昨天收到Oracle公司的安全公告，修复了Oracle产品的多个安全漏洞，这些安全漏洞可导致本地或远程的威胁，严重的可能导致直接入侵数据库。
MetaLink as Doc ID 420055.1:
https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=420055.1

Oracle Technology Network:
http://www.oracle.com/technology/deploy/security/alerts.htm

Oracle Database产品，Patch Number: 5901891
注：Ensure that your Oracle Database installation is the same release for which you are applying this patch. In other words, only apply the Release 10.2.0.3 CPUApr2007 patch to an Oracle Database Release 10.2.0.3.

Memory Notification: Library Cache Object Loaded Into Sga

2007-04-11T20:47:00.000-07:00

生产库环境下，alert.log文件一直报Memory Notification: Library Cache Object Loaded Into Sga ,Heap size 3010K exceeds notification threshold (2048K)。这些警告信息不会对应用程序有任何影响。Oracle 10G Release 2中新的事件消息机制和内存管理。Oracle数据库企业版10.2.0.1所有平台都会出现这种情况,10.2.0.1默认极限通知值是2M。在10.2.0.2已经调整到50M，因此如果运行的数据库是10.2.0.2或以上版本，就不会出现诸如此类的警告信息。

解决办法：

升级你的数据库至10.2.0.2以上版本。
修改非文档化的参数 _kgl_large_heap_warning_threshold，如果将它设置成0，表示禁止报告警告信息，也可以将它调整到一定高度，一般调整为8M左右应该足够了。

"tossing bad MUPD msg pid XX" during reconfiguration

2006-12-19T17:34:00.000-08:00

RAC (Real Application Clusters) / OPS;Bug [4773318] "tossing bad MUPD msg pid XX" during reconfiguration；Bugs fixed in the 10.2.0.3 Patch Set

[EXP-00056]全部数据库导出终止

2006-11-27T23:11:00.000-08:00

使用Oracle导出工具exp导出全部数据时错误，如果使用单张表导出正常。
[oracle@oracle02 ~]$ exp userid=test/test file=test.dmp buffer=1024 owner=test

Export: Release 10.2.0.1.0 - Production on Tue Nov 28 15:09:42 2006

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
原因：数据字典中有存在一些无效KU$_视图；
SQL> select * from sys.ku$_xmlschema_view;
select * from sys.ku$_xmlschema_view
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
解决办法：
1.执行对象视图定义脚本 $ORACLE_HOME/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/catmetx.sql

Package altered.
Index altered.
View created.
Grant succeeded.
View created.
Grant succeeded.
Procedure created.
System altered.
System altered.
System altered.
PL/SQL procedure successfully completed.
Procedure dropped.

2.执行重新编译无效对象脚本 $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
验证一下程序
SQL> select * from ku$_xmlschema_view;

V V OWNER_NUM OWNER_NAME URL
- - ---------- ------------------------------ ---------------
1 0 46 MDSYS http://www.opengis.net/gml/geometry.xsd
1 0 46 MDSYS http://www.w3.org/1999/xlink/xlinks.xsd
1 0 46 MDSYS http://www.opengis.net/gml/feature.xsd
重新执行导出程序exp，一些正常。

inbound connection timed out (ORA-3136)

2006-11-27T18:59:00.000-08:00

早上发现数据库出现了一条警告信息WARNING: inbound connection timed out (ORA-3136)；查了一下Metalink关于ORA-3136错误代码，是Oracle Bug 4677000 WARNING: INBOUND CONNECTION TIMED OUT (ORA-3136)。
解决办法：
1.在$ORACLE_HOME/network/admin/listener.ora, 增加下面代码：INBOUND_CONNECT_TIMEOUT_LISTENER = 0

2.在$ORACLE_HOME/network/admin/sqlnet.ora,增加下面代码:
SQLNET.INBOUND_CONNECT_TIMEOUT = 0

3. 重新启动监听器和数据库使增加的代码生效。

ORA-20000错误

2006-11-21T17:53:00.000-08:00

平常有个习惯，一上班，会泡杯咖啡，然后去查看数据库的alert_sid.ora文件。今天早上即如往常打开生产数据库，只是一些日志切换记录。已经好几天没有去查看客服系统的alert_sid.ora文件了（不是我不敬业，而是我是业余的DBA，呵呵），惊奇的发现，警告文件里面多了很多，像下面的信息
ORA-12012: error on auto execute of job 8888
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
客服系统运行在RedHat AS4，Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
首先想到一定是JOB出了问题，于是查看一下JOB信息
SQL> select job_name,state,run_count,failure_count from dba_scheduler_jobs;

JOB_NAME STATE RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ---------- -------------
AUTO_SPACE_ADVISOR_JOB SCHEDULED 19 1
GATHER_STATS_JOB SCHEDULED 19 0
FGR$AUTOPURGE_JOB DISABLED 0 0
PURGE_LOG SCHEDULED 22 0
RLM$SCHDNEGACTION SCHEDULED 546 0
RLM$EVTCLEANUP SCHEDULED 525 0

6 rows selected.
从上面的查询结果不难看出是由于AUTO_SPACE_ADVISOR_JOB出了问题。
Oracle 号称10G A self Managed Database ，AUTO_SPACE_ADVISOR_JOB 是在数据库安装时预定义的一个Scheduler Job。
原因：
查看Metalink它说是Oracle bug,when the tablespace is created the statistics are captured for this tablespace.When the tablespace is dropped the segments for which the segstats are collected continue to reference the dropped tablespace.AUTO_SPACE_ADVISOR_JOB找不到表空间返回的错误。通过查询dba_auto_segadv_ctl表可以获得被删除的表空间
SQL> select tablespace_name from dba_auto_segadv_ctl
2 where tablespace_name not in (select tablespace_name from dba_tablespaces)
3 /
TABLESPACE_NAME
------------------------------
TEST
1 rows selected.
由于TEST表空间已经被删除，在运行AUTO_SPACE_ADVISOR_JOB时还会对它进行段建议。导致了ORA-20000错误发生。Internal BUG:4707226
解决方法：
Bug 4707226 will be fixed in release 11.0
可以通过手动删除DBA_AUTO_SEGADV_CTL里表空间记录。
SQL> DELETE FROM dba_auto_segadv_ctl
2 where tablespace_name not in (select tablespace_name from dba_tablespaces)
3 /
1 rows deleted.
验证一下：
SQL> exec dbms_space.auto_space_advisor_job_proc

PL/SQL procedure successfully completed.

Oracle Trunc()函数

2006-09-30T01:10:00.000-07:00

Oracle提供的函数Trunc()有两种类型：

Trunc(for Number[,m])

Trunc(for Number)函数返回截取后的数值，其功能与Round函数类似，只是该函数做四舍五入处理，而是全部截去。

默认省略参数m，截取全部小数点部分，如果m为负数，截取小数点左边第m位。

SQL> SELECT TRUNC(123.45) FROM DUAL;
TRUNC(123.45)
-------------
123
SQL> SELECT TRUNC(123.45,1) FROM DUAL;
TRUNC(123.45,1)
---------------
123.4
SQL> SELECT TRUNC(123.45,3) FROM DUAL;
TRUNC(123.45,3)
---------------
123.45
SQL> SELECT TRUNC(123.45,-1) FROM DUAL;
TRUNC(123.45,-1)
----------------
120
SQL> SELECT TRUNC(123.45,-4) FROM DUAL;
TRUNC(123.45,-4)
----------------
0

Trunc(for Date[,fmt])

该函数返回截取后的日期格式，省略fmt，截取最近的日期。

fmt格式

Y、YY、YYY、YYYY、YEAR 年（返回四位数字的年）

MONTH、MON，MM 月

D、DD、DDD、DAY、DY 日

HH、HH12、HH24 时

MI 分

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'Y'),'YYYY-MM-DD HH24:MI') FROM DUAL;

YEAR

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

2006-01-01 00:00

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'YEAR'),'YYYY-MM-DD HH24:MI') FROM DUAL;

YEAR

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

2006-01-01 00:00

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'MONTH'),'YYYY-MM-DD HH24:MI') FROM DUAL;

MONTH

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

2006-07-01 00:00

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'DD'),'YYYY-MM-DD HH24:MI') FROM DUAL;

DAY

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

2006-07-19 00:00

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'HH'),'YYYY-MM-DD HH24:MI') FROM DUAL;

HOUR

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

2006-07-19 23:00

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'MI'),'YYYY-MM-DD HH24:MI') FROM DUAL;

MINUTE

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

2006-07-19 23:22

The first Blog!

2006-09-12T00:15:00.000-07:00

This is the first Blog!