物化视图快速刷新不支持标准外联接写法

Posted on 2011/08/27 by yangtingkun

发现对于REFRESH FAST ON COMMIT物化视图，并不支持标准外连接的写法，而Oracle特有的(+)方式则没有问题。
Oracle对于标准外联接的写法支持的并不好，类似的bug已经不是第一次碰到了。

SQL> CREATE TABLE T_P (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
表已创建。
SQL> CREATE TABLE T_F (ID NUMBER PRIMARY KEY, FID NUMBER);
表已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON T_P
  2  WITH ROWID (ID);
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON T_F
  2  WITH ROWID (FID);
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_T_ORACLE
  2  REFRESH FAST ON COMMIT
  3  AS SELECT A.ROWID A_RID, B.ROWID B_RID, A.ID, FID, NAME
  4  FROM T_P A, T_F B
  5  WHERE A.ID = FID (+);
实体化视图已创建。
SQL> CREATE MATERIALIZED VIEW MV_T_STARDAND
  2  REFRESH FAST ON COMMIT
  3  AS SELECT A.ROWID A_RID, B.ROWID B_RID, A.ID, FID, NAME
  4  FROM T_P A LEFT JOIN T_F B
  5  ON A.ID = FID;
FROM T_P A LEFT JOIN T_F B
*
第 4 行出现错误:
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性
 
SQL> CREATE MATERIALIZED VIEW MV_T_STARDAND
  2  REFRESH FAST 
  3  AS SELECT A.ROWID A_RID, B.ROWID B_RID, A.ID, FID, NAME
  4  FROM T_P A LEFT JOIN T_F B
  5  ON A.ID = FID;
FROM T_P A LEFT JOIN T_F B
*
第 4 行出现错误:
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图
 
SQL> CREATE MATERIALIZED VIEW MV_T_STARDAND
  2  REFRESH FAST 
  3  AS SELECT A.ROWID A_RID, B.ROWID B_RID, A.ID, FID, NAME
  4  FROM T_P A INNER JOIN T_F B
  5  ON A.ID = FID;
FROM T_P A INNER JOIN T_F B
*
第 4 行出现错误:
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

从上面的物化视图创建语句不难看出，Oracle并非不支持外联接的方式，采用(+)方式创建的物化视图是可以成功的，但是如果采用了标准SQL方式，不要说ON COMMIT方式，或者包含外联接了，就是普通内连接创建快速刷新的物化视图仍然会出现错误。
所以问题的根源还是标准写法，既然使用的是Oracle数据库，还是使用Oracle提供的语法出错的概率最小。

Posted in ORACLE | Tagged , , , , | Leave a comment

EXP无法导出延迟段创建的表

Posted on 2011/08/26 by yangtingkun

对于11g中使用了延迟段创建方式创建的表，如果导出时刻表的段没有被创建，那么EXP不会导出这张表。
检查测试如下：

SQL> CREATE TABLE t_defer (id NUMBER, name varchar2(30));
TABLE created.
SQL> CREATE TABLE t_imme (id NUMBER, name varchar2(30)) segment creation immediate; 
TABLE created.
SQL> SELECT TABLE_NAME, segment_created FROM user_tables;
TABLE_NAME                     SEG
------------------------------ ---
T_IMME                         YES
T_DEFER                        NO

下面通过EXP进行导出：

[oracle@DB11G ~]$ EXP yangtk/yangtk file=seg_defer.dmp log=seg_defer.log buffer=2048000
Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:11:39 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Export done IN US7ASCII CHARACTER SET AND AL16UTF16 NCHAR CHARACTER SET
server uses AL32UTF8 CHARACTER SET (possible charset conversion)
About TO export specified users ...
. exporting pre-schema PROCEDURAL objects AND actions
. exporting FOREIGN FUNCTION library names FOR USER YANGTK 
. exporting PUBLIC TYPE synonyms
. exporting private TYPE synonyms
. exporting object TYPE definitions FOR USER YANGTK 
About TO export YANGTK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YANGTK's TABLES via Conventional Path ...
. . exporting TABLE                         T_IMME          0 ROWS exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional AND extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups AND children
. exporting dimensions
. exporting post-schema PROCEDURAL objects AND actions
. exporting statistics
Export TERMINATED successfully WITHOUT warnings.
[oracle@DB11G ~]$ EXP yangtk/yangtk file=defer.dmp log=defer.log TABLES=t_defer buffer=2048000
Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:12:42 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Export done IN US7ASCII CHARACTER SET AND AL16UTF16 NCHAR CHARACTER SET
server uses AL32UTF8 CHARACTER SET (possible charset conversion)
About TO export specified TABLES via Conventional Path ...
EXP-00011: YANGTK.T_DEFER does NOT exist
Export TERMINATED successfully WITH warnings.
[oracle@DB11G ~]$ EXP yangtk/yangtk file=defer.dmp log=defer.log TABLES=t_defer buffer=2048000 direct=y recordlength=65534
Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:13:04 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Export done IN US7ASCII CHARACTER SET AND AL16UTF16 NCHAR CHARACTER SET
server uses AL32UTF8 CHARACTER SET (possible charset conversion)
About TO export specified TABLES via Direct Path ...
EXP-00011: YANGTK.T_DEFER does NOT exist
Export TERMINATED successfully WITH warnings.
[oracle@DB11G ~]$ expdp yangtk/yangtk dumpfile=seg_defer.dp logfile=seg_defer_expdp.log directory=d_output
 
Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 14:13:36 2011
 
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
 
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Starting "YANGTK"."SYS_EXPORT_SCHEMA_01":  yangtk/******** dumpfile=seg_defer.dp logfile=seg_defer_expdp.log directory=d_output 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "YANGTK"."T_DEFER"                              0 KB       0 rows
. . exported "YANGTK"."T_IMME"                               0 KB       0 rows
Master table "YANGTK"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for YANGTK.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dmp/seg_defer.dp
Job "YANGTK"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:14:33

可以看到，无论是使用常规路径还是直接路径，即使直接指定表名，也无法导出延迟段创建的表，而数据泵导出则没有这个问题。
对于已经设置了延迟段创建，又希望EXP导出的时候可以导出所有表，可以通过下面的简单方法：

SQL> BEGIN 
  2  FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE segment_created = 'NO') loop
  3  EXECUTE immediate 'alter table ' || i.table_name || ' allocate extent';
  4  END loop;
  5  END;
  6  /
PL/SQL PROCEDURE successfully completed.
SQL> SELECT TABLE_NAME, segment_created FROM user_tables;
TABLE_NAME                     SEG
------------------------------ ---
T_IMME                         YES
T_DEFER                        YES

这时EXP就可以正确导出所有表了：

[oracle@DB11G ~]$ EXP yangtk/yangtk file=seg_defer_2.dmp log=seg_defer_2.log buffer=2048000
Export: Release 11.2.0.1.0 - Production ON Wed Aug 24 15:44:21 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved.
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Export done IN US7ASCII CHARACTER SET AND AL16UTF16 NCHAR CHARACTER SET
server uses AL32UTF8 CHARACTER SET (possible charset conversion)
About TO export specified users ...
. exporting pre-schema PROCEDURAL objects AND actions
. exporting FOREIGN FUNCTION library names FOR USER YANGTK 
. exporting PUBLIC TYPE synonyms
. exporting private TYPE synonyms
. exporting object TYPE definitions FOR USER YANGTK 
About TO export YANGTK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YANGTK's TABLES via Conventional Path ...
. . exporting TABLE T_DEFER 0 ROWS exported
. . exporting TABLE T_IMME 0 ROWS exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional AND extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups AND children
. exporting dimensions
. exporting post-schema PROCEDURAL objects AND actions
. exporting statistics
Export TERMINATED successfully WITHOUT warnings.
Posted in ORACLE | Tagged , | Leave a comment

ROWNUM固化外部表结果集存在问题

Posted on 2011/08/25 by yangtingkun

在客户的11.2.0.2环境中碰到了这个问题，Oracle在处理包含ROWNUM固化的外部表加载数据时返回错误的结果。
外部表构造描述可以参考：利用外部表读取告警日志文件
客户环境中创建的外部表和上面链接中的例子几乎完全一致：

SQL> CREATE TABLE T_ALERT
  2  (TEXT VARCHAR2(4000)
  3  )
  4  ORGANIZATION EXTERNAL
  5  (TYPE ORACLE_LOADER
  6  DEFAULT DIRECTORY D_ALERT
  7  ACCESS PARAMETERS
  8  (RECORDS DELIMITED BY NEWLINE
  9  FIELDS (TEXT (1:255) CHAR))
 10  LOCATION ('alert_xshdb1.log'));
TABLE created.
SQL> SELECT * FROM T_ALERT WHERE ROWNUM < 10;
TEXT
------------------------------------------------------------------------------------------
Tue Nov 16 15:32:47 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Private Interface 'eth2:1' configured FROM GPnP FOR USE AS a private interconnect.
  [name='eth2:1', TYPE=1, ip=169.254.88.96, mac=00-15-17-f8-aa-a2, net=169.254.0.0/16, mask=255.255.0.0, USE=haip:cluster_interconnect/62]
Public Interface 'eth0' configured FROM GPnP FOR USE AS a public interface.
  [name='eth0', TYPE=1, ip=10.0.10.53, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1]
Public Interface 'eth0:1' configured FROM GPnP FOR USE AS a public interface.
9 ROWS selected.

外部表访问正常，但是下面这两个等价的写法，得到的结果却大相径庭：

SQL> SELECT COUNT(*) FROM T_ALERT;
  COUNT(*)
----------
    158299
SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  2  SELECT * FROM A B
  3  WHERE B.RN >= (SELECT C.RN FROM A C WHERE TEXT = 'Mon Aug 22 02:00:00 2011');
        RN TEXT
---------- --------------------------------------------------------------------------------
    158300 Starting ORACLE instance (normal)
    158301 LICENSE_MAX_SESSION = 0
    158302 LICENSE_SESSIONS_WARNING = 0
    158303 Private Interface 'eth2:1' configured FROM GPnP FOR USE AS a private interconnect.
    158304   [name='eth2:1', TYPE=1, ip=169.254.88.96, mac=00-15-17-f8-aa-a2, net=169.254.0.0/16, mask=255.255.0.0, USE=haip:cluster_interconnect/62]
    158305 Public Interface 'eth0' configured FROM GPnP FOR USE AS a public interface.
    158306   [name='eth0', TYPE=1, ip=10.0.10.53, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1]
    158307 Public Interface 'eth0:1' configured FROM GPnP FOR USE AS a public interface.
    158308   [name='eth0:1', TYPE=1, ip=10.0.10.55, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1]
    158309 Public Interface 'eth0:2' configured FROM GPnP FOR USE AS a public interface.
    158310   [name='eth0:2', TYPE=1, ip=10.0.10.57, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1]
    158311 Shared memory segment FOR instance monitoring created
    158312 Picked latch-free SCN scheme 3
.
.
.
    316587 Sun Aug 21 06:00:00 2011
    316588 VKRM started WITH pid=120, OS id=32339
    316589 Mon Aug 22 02:00:00 2011
    316590 Closing Resource Manager plan via scheduler window
    316591 Clearing Resource Manager plan via parameter
    316592 Mon Aug 22 02:00:35 2011
    316593 Thread 1 advanced TO log SEQUENCE 8130 (LGWR switch)
    316594   CURRENT log# 2 seq# 8130 mem# 0: +DATADG/xshdb/onlinelog/group_2.258.735233587
    316595   CURRENT log# 2 seq# 8130 mem# 1: +FRADG/xshdb/onlinelog/group_2.258.735233587
    316596 Mon Aug 22 02:00:36 2011
    316597 Archived Log entry 10625 added FOR thread 1 SEQUENCE 8129 ID 0x287369ad dest 1:
158298 ROWS selected.
SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  2  SELECT B.* FROM A B, A C
  3  WHERE B.RN >= C.RN
  4  AND C.TEXT = 'Mon Aug 22 02:00:00 2011';
        RN TEXT
---------- -------------------------------------------------------------------------------
    158299 Archived Log entry 10625 added FOR thread 1 SEQUENCE 8129 ID 0x287369ad dest 1:
    158298 Mon Aug 22 02:00:36 2011
    158297   CURRENT log# 2 seq# 8130 mem# 1: +FRADG/xshdb/onlinelog/group_2.258.735233587
    158296   CURRENT log# 2 seq# 8130 mem# 0: +DATADG/xshdb/onlinelog/group_2.258.735233587
    158295 Thread 1 advanced TO log SEQUENCE 8130 (LGWR switch)
    158294 Mon Aug 22 02:00:35 2011
    158293 Clearing Resource Manager plan via parameter
    158292 Closing Resource Manager plan via scheduler window
    158291 Mon Aug 22 02:00:00 2011、
9 ROWS selected.

第二个SQL语句的结果是我们所期望的，而第一个SQL的结果明显是错误的，对比范围结果不难发现，除了第一条记录之外，语句返回了alert日志文件中其他所有的数据，只不过ROWNUM的结果到是符合要求。
检查两个SQL分别的执行计划：

SQL> EXPLAIN PLAN FOR
  2  WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  3  SELECT * FROM A B
  4  WHERE B.RN >= (SELECT C.RN FROM A C WHERE TEXT = 'Mon Aug 22 02:00:00 2011');
Explained.
SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash VALUE: 2687076668
-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |  8168 |    15M|    58   (0)| 00:00:01 |
|*  1 |  VIEW                         |         |  8168 |    15M|    29   (0)| 00:00:01 |
|   2 |   COUNT                       |         |       |       |            |          |
|   3 |    EXTERNAL TABLE ACCESS FULL | T_ALERT |  8168 |    15M|    29   (0)| 00:00:01 |
|*  4 |   VIEW                        |         |  8168 |    15M|    29   (0)| 00:00:01 |
|   5 |    COUNT                      |         |       |       |            |          |
|   6 |     EXTERNAL TABLE ACCESS FULL| T_ALERT |  8168 |    15M|    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - FILTER("B"."RN">= (SELECT "C"."RN" FROM  (SELECT ROWNUM "RN","TEXT"
              "TEXT" FROM "T_ALERT" "T_ALERT") "C" WHERE "TEXT"='Mon Aug 22 02:00:00 2011'))
   4 - FILTER("TEXT"='Mon Aug 22 02:00:00 2011')
20 ROWS selected.
SQL> EXPLAIN PLAN FOR
  2  WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  3  SELECT B.* FROM A B, A C
  4  WHERE B.RN >= C.RN
  5  AND C.TEXT = 'Mon Aug 22 02:00:00 2011';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash VALUE: 883860547
-------------------------------------------------------------------------------------------
| Id | Operation                      |Name   |ROWS |Bytes|TempSpc| Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |       |3335K|  12G|       |  6960   (1)| 00:01:24 |
|  1 |  MERGE JOIN                    |       |3335K|  12G|       |  6960   (1)| 00:01:24 |
|  2 |   SORT JOIN                    |       |8168 |  15M|    31M|  3469   (1)| 00:00:42 |
|  3 |    VIEW                        |       |8168 |  15M|       |    29   (0)| 00:00:01 |
|  4 |     COUNT                      |       |     |     |       |            |          |
|  5 |      EXTERNAL TABLE ACCESS FULL|T_ALERT|8168 |  15M|       |    29   (0)| 00:00:01 |
|* 6 |   SORT JOIN                    |       |8168 |  15M|    31M|  3469   (1)| 00:00:42 |
|* 7 |    VIEW                        |       |8168 |  15M|       |    29   (0)| 00:00:01 |
|  8 |     COUNT                      |       |     |     |       |            |          |
|  9 |      EXTERNAL TABLE ACCESS FULL|T_ALERT|8168 |  15M|       |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   6 - access(INTERNAL_FUNCTION("B"."RN")>=INTERNAL_FUNCTION("C"."RN"))
       FILTER(INTERNAL_FUNCTION("B"."RN")>=INTERNAL_FUNCTION("C"."RN"))
   7 - FILTER("C"."TEXT"='Mon Aug 22 02:00:00 2011')
23 ROWS selected.

对于连接的写法，ORACLE采用了MERGE JOIN。而结果错误的SQL，Oracle直接通过对两个VIEW进行过滤的方式获取记录，这时第一个VIEW中的ROWNUM固化结果集并未生效，怀疑是Oracle在处理外部表时出现的bug。

Posted in BUG | Tagged , , | 2 Comments

利用外部表读取告警日志文件

Posted on 2011/08/24 by yangtingkun

数据库的告警日志以文本的格式保存到文件系统中，虽然可以很方便的通过操作系统命令进行查看，而且11g中Oracle甚至还提供了专门的adrci工具，但是对于只能通过SQLPLUS或者其他查询工具连接到数据库的人而言，还是非常不方便。
不过其实这个问题可以很容易的通过外部表的方式解决。最简单的办法就是将alert文件中的每一行记录都当做表中一个VARCHAR2(4000)类型列的一行记录，这样就可以轻松的通过SQL的方式来访问告警日志了。
在创建外部表之前，需要创建一个DIRECTORY，这个目录的位置就是background_dump_dest初始化参数指定的位置。

SQL> SHOW parameter background_dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      D:\ORACLE\PRODUCT\ADMIN\YTK102\BDUMP
SQL> CREATE directory d_alert AS 'D:\ORACLE\PRODUCT\ADMIN\YTK102\BDUMP';

目录已创建。
一旦目录创建成功后，就可以创建外部表了：

SQL> CREATE TABLE t_alert
  2  (text varchar2(1000)
  3  )
  4  organization external
  5  (TYPE oracle_loader
  6  DEFAULT directory d_alert
  7  access parameters
  8  (records delimited BY newline
  9  FIELDS (text (1:255) CHAR))
 10  location ('alert_ytk102.log'));
表已创建。
SQL> SELECT * FROM t_alert WHERE rownum < 10;
TEXT
-----------------------------------------------------------------------------
Dump file d:\oracle\product\admin\ytk102\bdump\alert_ytk102.log
Sat DEC 25 15:55:43  2010
ORACLE V10.2.0.5.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.1
CPU                 : 4 - TYPE 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1594M/2995M, Ph+PgF:4318M/5989M, VA:1911M/2047M
Sat DEC 25 15:55:43  2010
已选择9行。

现在已经可以正常访问告警日志了，为了确认外部表的加载没有错误，可以检查D_ALERT目录下的加载日志：

D:\>cd D:\oracle\product\admin\ytk102\bdump
D:\oracle\product\admin\ytk102\bdump>more T_ALERT_21436_532.log
 日志文件打开于 08/25/11 00:15:54
表 T_ALERT 的字段定义
  记录格式 DELIMITED BY NEWLINE
  文件中的数据与该平台的 endianness 格式相同
  接受带有空字段的行
  数据源中的字段:
    TEXT                            CHAR (255)
      记录位置 (1, 255)
      像 SQL 加载程序一样修剪空白
Posted in ORACLE | Tagged , , | 3 Comments

PSU补丁安装不全的问题

Posted on 2011/08/23 by yangtingkun

对于PSU补丁的安装，不是简单的opatch apply之后就完成了，还需要运行特定的sql。
在客户数据库中检查补丁情况，发现安装PSU只完成了第一步： fgms:/fgms/oracle/tru64_setupFile/OPatch
> ./opatch lsinventory
Invoking OPatch 10.2.0.5.0
Oracle Interim Patch Installer version 10.2.0.5.0
Copyright (c) 2010, Oracle Corporation. All rights reserved.

Oracle Home : /fgms/oracle10g/db
Central Inventory : /fgms/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.5.0
OUI version : 10.2.0.4.0
OUI location : /fgms/oracle10g/db/oui
Log file location : /fgms/oracle10g/db/cfgtoollogs/opatch/opatch2011-06-02_15-41-01PM.log
Patch history file: /fgms/oracle10g/db/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /fgms/oracle10g/db/cfgtoollogs/opatch/lsinv/lsinventory2011-06-02_15-41-01PM.txt
——————————————————————————–
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.2.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
There are 2 products installed in this Oracle Home.

Interim patches (2) :
Patch 9654991 : applied on Mon Sep 20 15:48:09 GMT+08:00 2010
Unique Patch ID: 12744499
Created on 7 Jul 2010, 15:37:03 hrs PST8PDT
Bugs fixed:
9714832, 9713537, 9678695, 9655017, 9173248, 8309642, 9678697, 9678690
9573054, 9654991
This patch overlays patches:
9352164
This patch needs patches:
9352164
as prerequisites
Patch 9352164 : applied on Mon Sep 20 15:34:43 GMT+08:00 2010
Unique Patch ID: 12389142
Created on 7 Apr 2010, 12:28:31 hrs PST8PDT
Bugs fixed:
9377578, 6418420, 7835247, 7207654, 7592346, 6724797, 7936993, 7331867
9093300, 7535152, 7552067, 5879114, 8866013, 5457450, 8344348, 7272297
7136866, 7196894, 6258480, 7013124, 6512622, 6355586, 7196532, 8568395
8309587, 7557226, 5399699, 6509115, 8568397, 8568398, 7127618, 5701695
6052226, 7424804, 6817593, 7553884, 6741425, 7513673, 6783812, 8437213
6452766, 6469211, 7527650, 8309592, 8479537, 5991038, 5686407, 6945157
7639602, 9119226, 6403091, 7589862, 7552082, 6711853, 8304589, 6052169
8199266, 6327692, 5756769, 9352191, 7460818, 6268409, 8232056, 6687381
6972843, 8230457, 6800507, 7027551, 6778714, 6200820, 6645719, 7393804
6775231, 3934160, 6683178, 6650256, 5085288, 7528105, 7329252, 6378112
6151380, 6844866, 4723109, 8544896, 5126719, 5890312, 5934363, 7036453
7610362, 8426816, 8433026, 7270434, 7172531, 6451626, 8247855, 5497338
6324944, 6874522, 7175513, 6960489, 7341598, 8576156, 6797677, 8342923
5895190, 6756739, 7150470, 7593835, 7356443, 7044551, 8227106, 4695511
7298688, 5747462, 8556340, 7197445, 5348308, 7937113, 8341623, 7569205
8409848, 6053134, 6163771, 6851669, 6181488, 6375150, 6825866, 7210921
7295780, 6345573, 7033630, 6954722, 6824129, 7523475, 7457766, 7309458
8324577, 6840740, 6804746, 7375611, 8268054, 6981690, 6512811, 6988017
7375613, 8344399, 7340448, 8362683, 7375617, 8251247, 5933656, 6005347
9145204, 6599920, 7238230, 6379441, 6452375, 6352003, 6833965, 7136489
6610218, 7612639, 6392076, 7225204, 9119194, 5476236, 9442328, 7609057
7609058, 6605106, 6374297, 6193945, 4693355, 8217795, 7039896, 7432514
7330909, 6952701, 7190270, 8287155, 7587008, 7207932, 6802650, 7189447
8481935, 4598439, 9442331, 6615740, 7155655, 6749617, 9442335, 7159505
5868257, 5727166, 7173005, 6917874, 9442339, 7013768, 7691766, 7385253
7291739, 7225720, 7257770, 7363767, 7244238, 6941717, 8267348, 7710551
8354686, 7247217, 8328954, 7299153, 8909984, 6681695, 8702276, 9119284
8217011, 7661251, 6265559, 6823287, 6991626, 6954829, 5259835, 6500033
5923486, 7432601, 7022234, 8534387, 5147386, 7697802, 6653934, 7375644
6490140, 7662491, 8331466, 5623467, 6070225, 6635214, 7396409, 6638558
7038750, 6714608, 6838714, 6870937, 7219752, 7263842, 7278117, 6882739
5404871, 8836667, 8373286, 7393292, 6678845, 6903051, 7936793, 6600051
7155248, 4966512, 7155249, 7197637, 8836308, 8568402, 8568404, 8568405
8431487, 5704108, 6343150, 7280764, 6923450, 7643632, 6145177, 8836671
8310931, 6640411, 8347704, 8836675, 7155250, 7155251, 8836677, 7155252
8836678, 7155253, 8292378, 7155254, 6219529, 7411865, 8227091, 8340379
7276960, 6145687, 7659217, 5863926, 7022905, 6852598, 7123643, 6596564
6413089, 6851438, 8836681, 8836683, 8836684, 8836686, 7579469, 7494333
7315642, 8340383, 6786022, 8340387, 6926448, 7600026, 7462072, 6679303
8815639, 7197583, 7172752, 7326645, 7008262, 9173244, 9173248, 7573151
8490879, 7477934, 6725634, 6733655, 6799205, 6980597, 7499353, 6084232
6014513, 7140204, 7254987, 8833280, 6647480, 6120004, 7693128, 6760697
6051177, 8247215, 6858062, 7189645, 6844739, 6768251, 7196863, 5630796
7378661, 7378735, 5970301, 6705822, 8290506, 6658484, 7599944, 8309623
7125408, 7257461, 6987790, 7568556, 6919819, 8886674, 5883691, 6955744
7801939, 6074620, 7149004, 6857917, 8283650, 6445948, 5929055, 6110752
7552042, 8210889, 8287504, 6506617, 7306915, 6271590, 5386204, 6976005
8330783, 7606362, 5377242, 7043989, 8309632, 7575925, 6870047, 8309637
5902053, 8309639, 7028176, 6827260, 7588384, 4726401, 6720712, 5910650
6752765, 6971433, 6024730, 8315482, 6628122, 8239142, 9352164, 5695562
4637902, 7345904, 8309642, 6994160, 8556586, 6404447, 8220734, 6919764
7597354, 7523787, 6029179, 5231155, 6455659
——————————————————————————–
OPatch succeeded.

从OPATCH工具信息可以看到，数据库的PSU已经打到了10.2.0.4.5，但是查询DBA_REGISTRY_HISTORY视图，却没有任何记录。 SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Compaq Tru64 UNIX: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
SQL> select * from dba_registry_history;
no rows selected

这说明补丁的安装过程只进行了前一半，而随后的调用sql的步骤没有执行，分别运行$ORACLE_HOME/psu/10.2.0.4.4/catpsu.sql和$ORACLE_HOME/psu/10.2.0.4.5/catpsu.sql： > cd $ORACLE_HOME/psu
fgms:/fgms/oracle10g/db/psu
> ls
10.2.0.4.4 10.2.0.4.5
fgms:/fgms/oracle10g/db/psu
> cd 10.2.0.4.4
fgms:/fgms/oracle10g/db/psu/10.2.0.4.4
> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jun 2 16:13:55 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> @catpsu.sql
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
.
.
.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry…
Updating registry…
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, ‘APPLY’,
7 SYS_CONTEXT(‘REGISTRY$CTX’,’NAMESPACE’),
8 ‘10.2.0.4’,
9 4,
10 ‘PSU’,
11 ‘PSU 10.2.0.4.4’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/fgms/oracle10g/db/cfgtoollogs/catbundle/catbundle_PSU_B1FGMS_APPLY_2011Jun02_16_14_08.log

整个TRACE文件超过了4W行，由于篇幅，这里只列出了开头和结尾部分内容，可以看到，如果不运行catpsu.sql，事实上补丁的很多内容只是应用到了数据库软件，而并没有真正应用到数据库字典中。 SQL> col action_time format a30
SQL> col action format a10
SQL> col namespace format a10
SQL> col version format a10
SQL> col comments format a15
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDL COMMENTS
—————————— ———- ———- ———- —— —– ————–
02-JUN-11 04.15.43.804266 PM APPLY SERVER 10.2.0.4 4 PSU PSU 10.2.0.4.4
1 row selected.

这时PSU的安装信息已经写入到数据字典中，下面运行10.2.0.4.5的catpsu.sql： SQL> @../10.2.0.4.5/catopsu.sql
PL/SQL procedure successfully completed.
1 row selected.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
1 row selected.
Generating apply and rollback scripts…
Check the following file for errors:
/fgms/oracle10g/db/cfgtoollogs/catbundle/catbundle_OPSU_B1FGMS_GENERATE_2011Jun02_16_21_39.log
Apply script: /fgms/oracle10g/db/rdbms/admin/catbundle_OPSU_B1FGMS_APPLY.sql
Rollback script: /fgms/oracle10g/db/rdbms/admin/catbundle_OPSU_B1FGMS_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file…

1 row selected.
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT ‘/fgms/oracle10g/db/cfgtoollogs/catbundle/’ || ‘catbundle_OPSU_’ || name || ‘_APPLY_’ || TO_CHAR(SYSDATE, ‘YYYYMonDD_hh24_mi_ss’, ‘NLS_DATE_LANGUAGE=”AMERICAN”’) || ‘.log’ AS spool_file FROM v$database;

1 row selected.
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace(‘SERVER’)
PL/SQL procedure successfully completed.
SQL> PROMPT Skipping OLAP Analytic Workspace because it is not installed or versions mismatch…
Skipping OLAP Analytic Workspace because it is not installed or versions mismatch…
SQL> PROMPT Skipping OLAP Catalog because it is not installed or versions mismatch…
Skipping OLAP Catalog because it is not installed or versions mismatch…
SQL> PROMPT Skipping Oracle OLAP API because it is not installed or versions mismatch…
Skipping Oracle OLAP API because it is not installed or versions mismatch…
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry…
Updating registry…
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, ‘APPLY’,
7 SYS_CONTEXT(‘REGISTRY$CTX’,’NAMESPACE’),
8 ‘10.2.0.4’,
9 1,
10 ‘OPSU’,
11 ‘PSU 10.2.0.4.5’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/fgms/oracle10g/db/cfgtoollogs/catbundle/catbundle_OPSU_B1FGMS_APPLY_2011Jun02_16_21_41.log

10.2.0.4.5的所有记录都列出来了，和10.2.0.4.4相比，这里的修改至少相差了不止一个数量级，这也是10.2.0.4.4是基础PSU，而10.2.0.4.5是OPSU的原因。
最后检查数据库中PSU信息： SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDL COMMENTS
—————————— ———- ———- ———- —— —– ————–
02-JUN-11 04.15.43.804266 PM APPLY SERVER 10.2.0.4 4 PSU PSU 10.2.0.4.4
02-JUN-11 04.21.41.080347 PM APPLY SERVER 10.2.0.4 1 OPSU PSU 10.2.0.4.5
2 rows selected.

至此，PSU的安装才算真正完成。

Posted in ORACLE | Tagged , , , | Leave a comment

TRUNCATE模式SQLLDR导致SECUREFILE的LOB空间不断增长

Posted on 2011/08/22 by yangtingkun

测试LOB的SECUREFILE存储方式时发现，如果利用SQLLDR的TRUNCATE方式导入数据，随着测试次数的增加，LOB对象占用的空间也会逐步增加。
创建表的脚本很简单：

CREATE TABLE t_load_4m_sf (id NUMBER, full_name varchar2(100), 
create_date DATE, contents BLOB, CONSTRAINT pk_t_load_4m_sf PRIMARY KEY(id))
lob (contents) store AS securefile;

用于加载LOB数据的控制文件如下：

[oracle@dbserver1 sqlldr]$ more sqlldr_4M_sf.ctl
LOAD DATA
INFILE 'filename.dat'
INTO TABLE T_LOAD_4M_SF
TRUNCATE
FIELDS TERMINATED BY ','
(ID CHAR(255),
FULL_NAME CHAR(255),
CREATE_DATE SYSDATE,
CONTENTS LOBFILE(FULL_NAME) TERMINATED BY EOF)

数据文件格式如下：

[oracle@dbserver1 sqlldr]$ more filename.dat
1,/home/oracle/2M/IMG_5015.JPG
2,/home/oracle/2M/IMG_5016.JPG
3,/home/oracle/2M/IMG_5017.JPG
4,/home/oracle/2M/IMG_5018.JPG
5,/home/oracle/2M/IMG_5022.JPG
6,/home/oracle/2M/IMG_5023.JPG
7,/home/oracle/2M/IMG_5025.JPG
.
.
.
663,/home/oracle/2M/DSC00142.JPG
664,/home/oracle/2M/DSC00143.JPG

导入命令如下：

[oracle@dbserver1 sqlldr]$ sqlldr enmotest/oracle control=sqlldr_4M_sf.ctl
SQL*Loader: Release 11.2.0.2.0 - Production ON Mon Aug 8 17:32:54 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
Commit point reached - logical record COUNT 64
Commit point reached - logical record COUNT 128
Commit point reached - logical record COUNT 192
Commit point reached - logical record COUNT 256
Commit point reached - logical record COUNT 320
Commit point reached - logical record COUNT 384
Commit point reached - logical record COUNT 448
Commit point reached - logical record COUNT 512
Commit point reached - logical record COUNT 576
Commit point reached - logical record COUNT 640
Commit point reached - logical record COUNT 664

第一次执行导入，检查表的空间占用：

SQL> SELECT TABLE_NAME, a.segment_name, bytes/1024/1024 FROM user_segments a, user_lobs b WHERE b.segment_name = a.segment_name  ORDER BY 1;
TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$            1220.1875

其中T_LOAD_4M是LOB没有采用SECUREFILE方式保存的普通表。这个表每次执行TRUNCATE方式的SQLLDR加载后，空间并不会发生变化，但是T_LOAD_4M_SF随着执行SQLLDR次数的增加，空间占用的情况分别变为：

SQL> SELECT TABLE_NAME, a.segment_name, bytes/1024/1024 FROM user_segments a, user_lobs b WHERE b.segment_name = a.segment_name  ORDER BY 1;
TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$            1348.1875
SQL> SELECT TABLE_NAME, a.segment_name, bytes/1024/1024 FROM user_segments a, user_lobs b WHERE b.segment_name = a.segment_name  ORDER BY 1;
TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$            1540.1875
SQL> SELECT TABLE_NAME, a.segment_name, bytes/1024/1024 FROM user_segments a, user_lobs b WHERE b.segment_name = a.segment_name  ORDER BY 1;
TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$             1732.125

除了第一次加载空间增长了128M外，以后每次加载都会导致表空间增长192M。而同样的操作并不会导致BASICFILE方式的LOB表。
导致这个现象的原因除了SECUREFILE存储方式外，也与SQLLDR的TRUNCATE导入方式有关，SQLLDR的TRUNCATE只是为了清空数据，由于随后还要执行导入，因此没有必要回收分配的空间，因此这个TRUNCATE语句会保留现有的空间，在加上SECUREFILE的特殊性，导致了每次加载LOB都使得表的空间不断的增长。
而如果尝试直接执行TRUNCATE TABLE语句，则会使得表的高水位线被清空，表的大小恢复到0。虽然这里存在一定的空间泄漏，但是对于实际生产环境中，这种TRUNCATE的SQLLDR方式应该极为罕见，因此并不会导致什么危害。

Posted in ORACLE | Tagged , , , | 1 Comment

HP Unix上oracle调用crontab报错

Posted on 2011/08/21 by yangtingkun

Oracle用户的crontab可以运行，但是发现oracle用户无法对其进行修改。
尝试通过crontab –e的方式修改crontab的配置，在保存的时候报错：

/app/oracle10g/rman_catalog_dmp> crontab -l
40 14 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
/app/oracle10g/rman_catalog_dmp> crontab -e
"/var/tmp/aaaa01943" 1 line, 59 characters 
47 14 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
~
~
~
~
"/var/tmp/aaaa01943" 1 line, 59 characters 
crontab: can't create your crontab file in the crontab directory.
/app/oracle10g/rman_catalog_dmp> crontab -l
40 14 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh

建立一个crontab文件，采用crontab crontab_file的方式同样出现错误：

/app/oracle10g/rman_catalog_dmp> vi crontab.oracle
"crontab.oracle" 1 line, 59 characters 
47 14 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
~
~
"crontab.oracle" 1 line, 59 characters 
/app/oracle10g/rman_catalog_dmp> crontab crontab.oracle
crontab: error IN message queue OPEN, errno=13
warning: commands will be executed USING /usr/bin/sh

通过GOOGLE查询发现，这个问题有很多人碰到了，可惜的是，没有找到一个合理的解释也没有一个靠谱的解决方法。
由于在网上找不到解决方法，只好自己想办法解决，尝试使用root来设置oracle的crontab，结果这次没有报错，而且EXP脚本也成功运行了：

acap3#[/]crontab -e oracle
"/var/tmp/aaaa02394" 1 line, 59 characters 
0 7 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh 
~
~
~
~
"/var/tmp/aaaa02394" 1 line, 57 characters 
warning: commands will be executed USING /usr/bin/sh
acap3#[/]crontab -l oracle
0 7 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh

切换到oracle用户，在导出结束后收到mail通知：

/app/oracle10g/rman_catalog_dmp> more /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
export ORACLE_SID=RMANDB
export ORACLE_BASE=/app/oracle10g
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export NLS_LANG=American_America.ZHS16CGB231280
export PATH=$ORACLE_HOME/bin:$PATH
DATE=`date +"%Y%m%d"`
EXP parfile=/app/oracle10g/rman_catalog_dmp/EXP.par file=/app/oracle10g/rman_catalog_dmp/rman_catalog_$DATE.dmp log=/app/oracle10g/rman_cat
alog_dmp/rman_catalog_$DATE.log
you have mail IN /var/mail/oracle
> mail 
FROM root@acap3 Thu Aug 18 15:21:46 EAT 2011
Received: (FROM root@localhost)
BY acap3 (8.9.3 (PHNE_35950)/8.9.3) id PAA02370
FOR oracle; Thu, 18 Aug 2011 15:21:45 +0800 (EAT)
DATE: Thu, 18 Aug 2011 15:21:45 +0800 (EAT)
FROM: root@acap3
Message-Id: <201108180721.PAA02370@acap3>
Subject: cron
 
Export: Release 10.2.0.4.0 - Production ON Thu Aug 18 15:15:00 2011
Copyright (c) 1982, 2007, Oracle. ALL rights reserved.
 
Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, DATA Mining AND REAL Application Testing options
Export done IN ZHS16CGB231280 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 RMAN_FGMS 
. exporting FOREIGN FUNCTION library names FOR USER RMAN_MODDB 
. exporting FOREIGN FUNCTION library names FOR USER RMAN_ACDB 
.
.
.
. exporting post-schema PROCEDURAL objects AND actions
. exporting statistics
Export TERMINATED successfully WITHOUT warnings.
 
*************************************************
Cron: The previous message IS the standard output
AND standard error OF one OF your crontab commands:
/app/oracle10g/rman_catalog_dmp/exp_by_date.sh
 
? q

这种办法虽然可以解决问题，但是同样也存在一个问题，就是oracle用户没有权限检查自己用户下的crontab设置了。
最后尝试在metalink中查询，没想到找到了最终的解决方案，文档参考ID 1005095.1。导致问题的原因是由于/usr/bin/crontab命令的权限不对：

acap3#[/]crontab -r oracle
acap3#[/]ls -l /usr/bin/crontab
-rwxrwxrwx 1 root bin 28672 Jul 18 2002 /usr/bin/crontab
acap3#[/]chmod 4555 /usr/bin/crontab
acap3#[/]ls -l /usr/bin/crontab
-r-sr-xr-x 1 root bin 28672 Jul 18 2002 /usr/bin/crontab

将crontab命令的权限修改为4555后，oracle用户的crontab恢复正常：

/app/oracle10g/rman_catalog_dmp> crontab -e
"/var/tmp/aaaa02482" 
# 0 0 * * * test.sh
~
~
~
"/var/tmp/aaaa02482" 1 line, 20 characters 
warning: commands will be executed USING /usr/bin/sh
/app/oracle10g/rman_catalog_dmp> more crontab.oracle
47 14 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
/app/oracle10g/rman_catalog_dmp> echo "0 7 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh" > crontab.oracle
/app/oracle10g/rman_catalog_dmp> more crontab.oracle
0 7 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
/app/oracle10g/rman_catalog_dmp> crontab crontab.oracle
warning: commands will be executed USING /usr/bin/sh

从这个问题的解决过程也可以看出，Oracle的metalink知识库的强大之处，一个操作系统上的问题，在HP以及一些操作系统相关的专业网站上都查找不到，反而在Oracle的metalink中查询到了。

Posted in OPERATING SYSTEM | Tagged , , | Leave a comment

CRONTAB调用的备份脚本

Posted on 2011/08/20 by yangtingkun

EXP备份是DBA常规操作之一，尤其是10g以前的版本没有EXPDP，所有的逻辑备份都是通过EXP来实现。而将EXP写成SHELL脚本，使之可以调用也并没有什么特别之处。不过如果并非oracle用户执行，而是需要CRONTAB自动允许，那么备份的脚本就有一些要求了。

客户环境使用RMAN CATALOG方式备份，而RMAN的CATALOG数据库采用EXP进行逻辑备份。部署了一个每天7点运行的CRONTAB，调用执行EXP的SHELL。巡检时发现这个脚本从未真正运行过。

/app/oracle10g> crontab -l
0 7 * * * /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
/app/oracle10g/rman_catalog_dmp> more exp_by_date.sh 
export ORACLE_SID=RMANDB
export ORACLE_BASE=/app/oracle10g
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export NLS_LANG=American_America.ZHS16CGB231280
EXP parfile=EXP.par file=./rman_catalog.dmp log=./rman_catalog.log

由于是CRONTAB进行调用，因此不会使用oracle用户的环境变量，这就需要在shell脚本中添加必要的环境变量的设置，这才能是的EXP命令可以正确的运行。脚本中虽然已经设置了一些环境变量，但是还是不够的。
对于CRONTAB运行SHELL时出现的错误，可以通过用户的mail来获取错误信息：

/app/oracle10g/rman_catalog_dmp> mail
FROM root@acap3 Thu Aug 18 07:00:01 EAT 2011
Received: (FROM root@localhost)
BY acap3 (8.9.3 (PHNE_35950)/8.9.3) id HAA27754
FOR oracle; Thu, 18 Aug 2011 07:00:01 +0800 (EAT)
DATE: Thu, 18 Aug 2011 07:00:01 +0800 (EAT)
FROM: root@acap3
Message-Id: <201108172300.HAA27754@acap3>
Subject: cron
/app/oracle10g/rman_catalog_dmp/exp_by_date.sh[5]: EXP: NOT found.
*************************************************
Cron: The previous message IS the standard output
AND standard error OF one OF your crontab commands:
/app/oracle10g/rman_catalog_dmp/exp_by_date.sh

显然导致错误的原因是执行EXP命令时，在默认目录下没有找到EXP可执行命令，显然脚本中漏设了PATH环境变量。
增加PATH=$ORACLE_HOME/bin环境变量后，再次测试发现，参数文件找不到，仿照导出文件和日志文件的方式，将参数文件设置为./exp.par，问题依旧。看来在CRONTAB中调用SHELL，应该设置绝对路径，而不能设置相对路径。
最终脚本修改为：

//app/oracle10g/rman_catalog_dmp> more /app/oracle10g/rman_catalog_dmp/exp_by_date.sh
export ORACLE_SID=RMANDB
export ORACLE_BASE=/app/oracle10g
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export NLS_LANG=American_America.ZHS16CGB231280
export PATH=$ORACLE_HOME/bin:$PATH
DATE=`date +"%Y%m%d"`
EXP parfile=/app/oracle10g/rman_catalog_dmp/EXP.par file=/app/oracle10g/rman_catalog_dmp/rman_catalog_$DATE.dmp log=/app/oracle10g/rman_cat
alog_dmp/rman_catalog_$DATE.log
you have mail IN /var/mail/oracle

利用CRONTAB来实现SHELL的自动调用，需要注意两个问题，第一是否在SHELL中设置了必要的环境变量；第二尽量所有的文件都采用完全路径方式，避免使用相对路径。

Posted in ORACLE | Tagged , , | 2 Comments

检查数据库CPU和PSU补丁信息

Posted on 2011/08/19 by yangtingkun

简单描述如何检查当前数据库的CPU和PSU补丁安装情况。
正常情况下有两种方法，一种是登录数据库检查DBA_REGISTRY_HIST视图：

MODDB2:[/home/oracle]$sqlplus / AS sysdba
SQL*Plus: Release 10.2.0.4.0 - Production ON Thu Aug 18 13:04:43 2011
Copyright (c) 1982, 2007, Oracle.  ALL Rights Reserved.
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, REAL Application Clusters, Oracle Label Security, DATA Mining
AND REAL Application Testing options
SQL> SET pages 100 LINES 120
SQL> col action format a6
SQL> col namespace format a10
SQL> col version format a10
SQL> col comments format a15
SQL> col action_time format a30
SQL> col bundle_series format a15
SQL> ALTER SESSION SET nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';
SESSION altered.
SQL> SELECT * FROM dba_registry_history;
ACTION_TIME                 ACTION NAMESPACE  VERSION      ID BUNDLE_SERIES  COMMENTS
--------------------------- ------ ---------- ---------- ---- -------------- ---------------
2011-06-02 16:54:21.301023  APPLY  SERVER     10.2.0.4      4 PSU            PSU 10.2.0.4.4
2011-06-02 16:54:42.711877  APPLY  SERVER     10.2.0.4      1 OPSU           PSU 10.2.0.4.5

另一种方式是通过opatch lsinventory方式：

MODDB2:[/home/oracle]$opatch lsinventory
Invoking OPatch 10.2.0.5.0
Oracle Interim Patch Installer version 10.2.0.5.0
Copyright (c) 2010, Oracle Corporation.  ALL rights reserved.
Oracle Home       : /oracle/db
Central Inventory : /oracle/oraInventory
   FROM           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.5.0
OUI version       : 10.2.0.4.0
OUI location      : /oracle/db/oui
Log file location : /oracle/db/cfgtoollogs/opatch/opatch2011-08-18_11-18-30AM.log
Patch history file: /oracle/db/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-08-18_11-18-30AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2): 
Oracle DATABASE 10g                                                  10.2.0.1.0
Oracle DATABASE 10g Release 2 Patch SET 3                            10.2.0.4.0
There are 2 products installed IN this Oracle Home.
Interim patches (4) :
Patch  6367097      : applied ON Tue Sep 14 22:08:15 GMT+08:00 2010
   Created ON 20 Jul 2008, 23:37:14 hrs US/Pacific
   Bugs fixed:
     6367097
Patch  9654991      : applied ON Tue Sep 14 22:06:57 GMT+08:00 2010
UNIQUE Patch ID:  12666999
   Created ON 1 Jul 2010, 19:02:09 hrs PST8PDT
   Bugs fixed:
     9714832, 9713537, 9678695, 9655017, 9173248, 8309642, 9678697, 9678690
     9573054, 9654991
   This patch overlays patches:
     9352164
   This patch needs patches:
     9352164
   AS prerequisites
Patch  9352164      : applied ON Tue Sep 14 22:02:20 GMT+08:00 2010
UNIQUE Patch ID:  12317594
   Created ON 1 Apr 2010, 11:23:39 hrs PST8PDT
   Bugs fixed:
     9377578, 6418420, 7835247, 7207654, 7592346, 6724797, 7936993, 7331867
     9093300, 7535152, 7552067, 5879114, 8866013, 5457450, 8344348, 7272297
     7136866, 7196894, 6258480, 7013124, 6512622, 6355586, 7196532, 8568395
     8309587, 7557226, 5399699, 6509115, 8568397, 8568398, 7127618, 5701695
     6052226, 7424804, 6817593, 7553884, 6741425, 7513673, 6783812, 8437213
     6452766, 6469211, 7527650, 8309592, 8479537, 5991038, 5686407, 6945157
     7639602, 9119226, 6403091, 7589862, 7552082, 6711853, 8304589, 6052169
     8199266, 6327692, 5756769, 9352191, 7460818, 6268409, 8232056, 6687381
     6972843, 8230457, 6800507, 7027551, 6778714, 6200820, 6645719, 7393804
     6775231, 3934160, 6683178, 6650256, 5085288, 7528105, 7329252, 6378112
     6151380, 6844866, 4723109, 8544896, 5126719, 5890312, 5934363, 7036453
     7610362, 8426816, 8433026, 7270434, 7172531, 6451626, 8247855, 5497338
     6324944, 6874522, 7175513, 6960489, 7341598, 8576156, 6797677, 8342923
     5895190, 6756739, 7150470, 7593835, 7356443, 7044551, 8227106, 4695511
     7298688, 5747462, 8556340, 7197445, 5348308, 7937113, 8341623, 7569205
     8409848, 6053134, 6163771, 6851669, 6181488, 6375150, 6825866, 7210921
     7295780, 6345573, 7033630, 6954722, 6824129, 7523475, 7457766, 7309458
     8324577, 6840740, 6804746, 7375611, 8268054, 6981690, 6512811, 6988017
     7375613, 8344399, 7340448, 8362683, 7375617, 8251247, 5933656, 6005347
     9145204, 6599920, 7238230, 6379441, 6452375, 6352003, 6833965, 7136489
     6610218, 7612639, 6392076, 7225204, 9119194, 5476236, 9442328, 7609057
     7609058, 6605106, 6374297, 6193945, 4693355, 8217795, 7039896, 7432514
     7330909, 6952701, 7190270, 8287155, 7587008, 7207932, 6802650, 7189447
     8481935, 4598439, 9442331, 6615740, 7155655, 6749617, 9442335, 7159505
     5868257, 5727166, 7173005, 6917874, 9442339, 7013768, 7691766, 7385253
     7291739, 7225720, 7257770, 7363767, 7244238, 6941717, 8267348, 7710551
     8354686, 7247217, 8328954, 7299153, 8909984, 6681695, 8702276, 9119284
     8217011, 7661251, 6265559, 6823287, 6991626, 6954829, 5259835, 6500033
     5923486, 7432601, 7022234, 8534387, 5147386, 7697802, 6653934, 7375644
     6490140, 7662491, 8331466, 5623467, 6070225, 6635214, 7396409, 6638558
     7038750, 6714608, 6838714, 6870937, 7219752, 7263842, 7278117, 6882739
     5404871, 8836667, 8373286, 7393292, 6678845, 6903051, 7936793, 6600051
     7155248, 4966512, 7155249, 7197637, 8836308, 8568402, 8568404, 8568405
     8431487, 5704108, 6343150, 7280764, 6923450, 7643632, 6145177, 8836671
     8310931, 6640411, 8347704, 8836675, 7155250, 7155251, 8836677, 7155252
     8836678, 7155253, 8292378, 7155254, 6219529, 7411865, 8227091, 8340379
     7276960, 6145687, 7659217, 5863926, 7022905, 6852598, 7123643, 6596564
     6413089, 6851438, 8836681, 8836683, 8836684, 8836686, 7579469, 7494333
     7315642, 8340383, 6786022, 8340387, 6926448, 7600026, 7462072, 6679303
     8815639, 7197583, 7172752, 7326645, 7008262, 9173244, 9173248, 7573151
     8490879, 7477934, 6725634, 6733655, 6799205, 6980597, 7499353, 6084232
     6014513, 7140204, 7254987, 8833280, 6647480, 6120004, 7693128, 6760697
     6051177, 8247215, 6858062, 7189645, 6844739, 6768251, 7196863, 5630796
     7378661, 7378735, 5970301, 6705822, 8290506, 6658484, 7599944, 9173253
     8309623, 7125408, 7257461, 6987790, 7568556, 6919819, 8886674, 5883691
     6955744, 7801939, 6074620, 7149004, 6857917, 8283650, 6445948, 5929055
     6110752, 7552042, 8210889, 8287504, 6506617, 7306915, 6271590, 5386204
     6976005, 8330783, 7606362, 5377242, 7043989, 8309632, 7575925, 6870047
     8309637, 5902053, 8309639, 7028176, 6827260, 7588384, 4726401, 6720712
     5910650, 6752765, 6971433, 6024730, 8315482, 6628122, 8239142, 9352164
     5695562, 4637902, 7345904, 8309642, 6994160, 8556586, 6404447, 8220734
     6919764, 7597354, 7523787, 6029179, 5231155, 6455659
Patch  9294403      : applied ON Tue Sep 14 21:29:10 GMT+08:00 2010
UNIQUE Patch ID:  12382380
   Created ON 19 Mar 2010, 08:00:09 hrs PST8PDT
   Bugs fixed:
     9294403
OPatch succeeded.

注意，CPU和PSU补丁分为两部分，首先通过opatch命令，随后还需要运行cat_bundle.sql。如果没有运行这个SQL，则补丁不算安装成功，这时通过OPATCH命令检查可以看到补丁的安装信息，但是在数据库的DBA_REGISTRY_HISTORY中是找不到对应的信息的。

Posted in ORACLE | Tagged , , , | 3 Comments

个人主页

Posted on 2011/08/18 by yangtingkun

我的个人主页在Kamus的大力忽悠下，终于面世了。
感谢Kamus在各方面的支持，使得我这个菜鸟也可以在一个小时之内搞定这个主页，感谢ITPUB长久以来的硬件、软件的支持，今后很长一段时间内，我的博客还会在ITPUB上的BLOG同时发布。
以往的博客，肯定会迁移到这个主页上，但是由于数据量太大，迁移操作可能花费很长的时间，而且由于以往博客中包含了大量的链接，完全修改到新的环境中工作量难以估量，如果找不到一个很方便的方法，则不会尝试进行更新了。
主页地址：https://yangtingkun.net

Posted in NEWS | 13 Comments