March 2009 Archives

我以前写的关于Data Guard的两篇文章

By cui hua on March 28, 2009 9:59 PM | 1 Comment

今天复习了一下Data Guard方面的内容，在整理资料的时候发现我这两年看过的关于oracle的资料的累计已经有1.2G了，其中纯文本的资料大概有700M。 

 

在windows平台搭建physical standby的过程.doc

RAC环境下Physical Standby的搭建过程.doc

Physical Standby环境备份与恢复测试.doc 

 

这三篇文章中有两篇是我写的，另外一篇是我对相关内容的一个总结。

 

最近认识了熊哥，用他写的ODU做了一系列的恢复测试，发现ODU恢复数据起来真是方便！他的ODU对我的影响倒还是其次，最关键的是他的风格－踏实、勤奋、低调、流水不争先的作风对我是一种很大的触动！

他的blog还有白鳝的blog是我经常去的地方，他们是真正的顶尖高手！

有了目标，也就有了努力的方向，我本来是不打算看如下几本书的，可是现在我还是决定认真看一看它们，也许它们可以帮助我在很多方面深入下去。

dsi401_support_skill.pdf

dsi401-Dumps Crashes and Corruptions.pdf

dsi402e-Data types and block structures.pdf

dsi403e-Recovery Architecture Components.pdf

dsi404_query_optimizer.pdf

 

目前我基本上还是保持了每个工作日至少会在metalink上看10篇文章的习惯，这个习惯还是要坚持下去。白鳝说他在两年的时间内在metalink上大概看了2000篇文章，我大概统计了一下，我才看了900篇左右，这个差的太远了！要继续努力！

关于PL/SQL中对存储过程add debug information

By cui hua on March 27, 2009 11:44 AM | No Comments

昨天有同事给我打电话，她说她的一个存储过程debug的时候debug不进去了，让我帮她看一下。

解决这个问题是很简单的，只需要在PL/SQL Developer中选择要debug的存储过程，然后点右键，在弹出的菜单中选择"Add debug information"后再重新开一个窗口开始debug就能debug进去了。

 

现在的关键问题是：当对一个存储过程选择"Add debug information"后，PL/SQL Developer到底做了什么事情？在PL/SQL Developer中如果某个存储过程能够被debug进去，则你在这个存储过程上点右键，在弹出的菜单中选项"Add debug information"前面会有一个小勾，PL/SQL Developer是从哪儿知道这个小勾应不应该勾上的？

 

第一个问题的答案是PL/SQL Developer实际是执行了ALTER PROCEDURE OWNER.PROCEDURENAME COMPILE DEBUG。

 

如下是我研究上述问题的整个过程：

打开一个 PL/SQL Developer，查询一下这个PL/SQL Developer的main session的sid，这里得到的结果是421。

然后再开一个sql plus的窗口，依次执行如下语句：

SQL> select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = 421;

 

       PID SPID                SID

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

        28 241816              421

 

SQL> oradebug setospid 241816

Oracle pid: 28, Unix process pid: 241816, image: oracle@p690ca

 

SQL> oradebug unlimit

已处理的语句

 

SQL> oradebug event 10046 trace name context forever,level 12

已处理的语句

 

上述几步做完后回到原先的那个PL/SQL Developer，选中存储过程A_TESTINGFORJOBMANAGER，然后点右键，在弹出的菜单中选择"Add debug information"。

 

再回到sqlplus窗口，依次执行如下语句：

SQL> oradebug tracefile_name

/u01/app/oracle/admin/ipratest/udump/ipratest_ora_241816.trc

 

SQL> oradebug event 10046 trace name context off

已处理的语句

 

然后去看上述trace文件，里面有这样一段：

PARSING IN CURSOR #9 len=60 dep=0 uid=55 oct=25 lid=55 tim=18452123749141 hv=884574241 ad='a2ac0198'

ALTER PROCEDURE CAIPRA.A_TESTINGFORJOBMANAGER COMPILE DEBUG

END OF STMT

PARSE #9:c=0,e=641,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=18452123749137

BINDS #9:

 

你可以随便再选另外一个debug不进去的存储过程，手工执行一下上述sql，你会发现，手工执行完上述sql后，原先debug不进去的存储过程现在已经可以debug进去了。

 

好了，这里我回答了第一个问题。现在我们来回答第二个问题：

如法炮制，可以很容易的看到PL/SQL Developer是通过视图sys.all_probe_objects中的字段debuginfo来判断是否应该给一个存储过程的"Add debug information"选项带上小勾。

当debuginfo为T的时候，会有小勾。

当debuginfo为F的时候，就没有小勾。

 

如下是ipradev中ipra用户下所有不能够debug进去的存储过程，大家在debug的时候注意一下：

SQL> select object_name from sys.all_probe_objects t where owner='IPRA' and object_type='PROCEDURE' and debuginfo='F';

 

OBJECT_NAME

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

P_SFINDDIFF

P_SATGENERATEAUDITINTERFACE

P_SAT_GETSATDATFROMSALDAT

P_SAT_GETDATAFROMSAL_1

P_IUPDATEWIV_NC

P_IPACCHECK

P_SATBATCHINTERFACE_NC

P_IPACTOWIV_NC

P_YCALLWRTLOG

 

9 rows selected

 

另外，可以用如下命令方便的在"能够debug"和"不能够debug"之间转换，这里是以caipratest中的存储过程P_ADCGETAGTFORALARM为例来说明：

SQL> select t.debuginfo from sys.all_probe_objects t where object_name='P_ADCGETAGTFORALARM';

 

DEBUGINFO

---------

F

 

SQL> alter procedure P_ADCGETAGTFORALARM compile debug;

 

Procedure altered

 

SQL> select t.debuginfo from sys.all_probe_objects t where object_name='P_ADCGETAGTFORALARM';

 

DEBUGINFO

---------

T

 

SQL> alter procedure P_ADCGETAGTFORALARM compile;

 

Procedure altered

 

SQL> select t.debuginfo from sys.all_probe_objects t where object_name='P_ADCGETAGTFORALARM';

 

DEBUGINFO

---------

F

 

解决历史数据转移权限错误一例

By cui hua on March 26, 2009 1:17 PM | No Comments

礼拜天在岳母家里看孩子，接到了同事的电话，东航历史数据转移作业出错了，错误原因不明。我岳母家上网不方面，就一台7、8年前的破电脑，连MSN都没有。后来用windows自带的windows messager连上MSN后定位出错原因在于执行历史数据转移的一个java存储过程的时候报错ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException

 

这个历史数据转移方案是我做的，里面用java存储过程封装了oracle的可移动表空间，上述提示信息很友好，直接照着做一下就能解决问题了，即执行如下的三条命令就可以了：

dbms_java.grant_permission( 'DRAS','SYS:java.io.FilePermission', '/mudrasu01/app/oracle/product/9.2.0/bin/exp','execute' );

dbms_java.grant_permission( 'DRAS','SYS:java.io.FilePermission', '/mudrasu01/app/oracle/product/9.2.0/bin/imp','execute' );

dbms_java.grant_permission( 'DRAS','SYS:java.io.FilePermission', '/bin/cp','execute' );

 

但现在的问题是，同样的历史数据转移作业，为什么在国航、南航没事，单单在东航就出权限问题了？

 

呵呵，原因就出在JAVASYSPRIV上面。

 

我们来做一个例子，重现一下上述问题：

SQL> conn dras/astca@astca;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> select distinct grantee_name from user_java_policy;

 

GRANTEE_NAME

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

DRAS

JAVASYSPRIV

JAVAUSERPRIV

JAVA_ADMIN

JAVA_DEPLOY

PUBLIC

 

6 rows selected

 

现在dras是有JAVASYSPRIV这个role的，所以应该可以成功执行上述java存储过程，也就是说可以用上述java存储过程调aix上的exp命令。

我们来测一下：

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

 

PL/SQL procedure successfully completed.

 

oracle:/dras20 $ls -l

total 23091960

drwxr-xr-x   2 oracle   dba            4096 Jan 27 18:56 armshis

drwxr-xr-x   2 oracle   dba             256 Nov 20 09:54 astca

-rw-r-----   1 oracle   dba      11822927872 Jan 10 2008  exp_mas.dmp

-rw-r--r--   1 oracle   dba          122880 Mar 26 11:01 exparmshistemptbs.dmp

-rw-r--r--   1 oracle   dba            3157 Mar 26 11:01 exparmshistemptbs.log

-rw-r--r--   1 oracle   dba           15082 Jan 10 2008  imp_mas.log

-rw-r--r--   1 oracle   dba             133 Jan 27 18:57 imparmshistemptbs.log

drwxr-xr-x   2 oracle   dba             256 Sep 13 2007  lost+found

 

oracle:/dras20 $cat exparmshistemptbs.log

 

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace ARMSHISTEMPTBS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table                         SALDAF

. . exporting table                         SALDCF

. . exporting table                         UPLPDF

. . exporting table                         SALDFF

. . exporting table                         SALXBF

. . exporting table               ACCOUNT_ENTRYBAS

. . exporting table               ACCOUNT_ENTRYREV

. . exporting table                         ADCBTH

. . exporting table                         ADCSBH

. . exporting table                         ADCSTK

. . exporting table                         ADCTKD

. . exporting table                         ADJDFT

. . exporting table                         ADJDPR

. . exporting table                         ADJIWC

. . exporting table                         ADJIWD

. . exporting table                         ADJLFT

. . exporting table                         ADJSAC

. . exporting table                         ADJSAD

. . exporting table                         ADJSCC

. . exporting table                         ADJSCD

. . exporting table                         ADJSVD

. . exporting table                         ADJSXC

. . exporting table                         ADJSXD

. . exporting table                         ADJTKN

. . exporting table                         ADJUDL

. . exporting table                         ADJUPL

. . exporting table                         IWBBTH

. . exporting table                         IWBPDT

. . exporting table                         IWBSUB

. . exporting table                         IWBXBG

. . exporting table                         OALTKT

. . exporting table                         PBLSAL

. . exporting table                         PBLSUB

. . exporting table                         PBLSUM

. . exporting table                         PBLUPL

. . exporting table                         PBLXBG

. . exporting table                         SALBTH

. . exporting table                         SALDAT

. . exporting table                         SALDCT

. . exporting table                         SALDFT

. . exporting table                         SALDMG

. . exporting table                         SALDVT

. . exporting table                         SALSUB

. . exporting table                         SALXBG

. . exporting table                         UPLBTH

. . exporting table                         UPLDPR

. . exporting table                         UPLPDT

. . exporting table                         UPLSCB

. . exporting table                         UPLWRK

. . exporting table                         UPLXBG

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

我们可以看到上述java存储过程在dras用户具有JAVASYSPRIV这个role的情况下是能够被成功执行的，

 

我们现在把JAVASYSPRIV和JAVAUSERPRIV给revoke掉：

SQL> revoke JAVASYSPRIV from dras;

 

Revoke succeeded.

 

SQL> revoke JAVAUSERPRIV from dras;

 

Revoke succeeded.

 

再来执行上述java存储过程：

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

BEGIN dras.pckexpimp.P_HTRANTBSEXP; END;

 

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.security.AccessControlException: the Permission (java.io.FilePermission

/cadrasu01/app/oracle/product/9.2.0/bin/exp execute) has not been granted to

DRAS. The PL/SQL to grant this is dbms_java.grant_permission( 'DRAS',

'SYS:java.io.FilePermission', '/cadrasu01/app/oracle/product/9.2.0/bin/exp',

'execute' )

ORA-06512: at "DRAS.PCKEXPIMP", line 0

ORA-06512: at line 1

可以看到，这里我们重现了错误。

 

现在我们来让上述错误消失：

SQL> grant JAVASYSPRIV to dras;

 

Grant succeeded.

 

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

BEGIN dras.pckexpimp.P_HTRANTBSEXP; END;

 

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.security.AccessControlException: the Permission (java.io.FilePermission

/cadrasu01/app/oracle/product/9.2.0/bin/exp execute) has not been granted to

DRAS. The PL/SQL to grant this is dbms_java.grant_permission( 'DRAS',

'SYS:java.io.FilePermission', '/cadrasu01/app/oracle/product/9.2.0/bin/exp',

'execute' )

ORA-06512: at "DRAS.PCKEXPIMP", line 0

ORA-06512: at line 1

 

 

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

oracle:/dras20 $sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Mar 26 12:30:10 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

SQL> exec dras.pckexpimp.P_HTRANTBSEXP;

 

PL/SQL procedure successfully completed.

 

SQL> select * from role_role_privs;

 

ROLE                           GRANTED_ROLE                   ADMIN_OPTION

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

DBA                            OLAP_DBA                       NO

DBA                            XDBADMIN                       NO

DBA                            JAVA_ADMIN                     NO

DBA                            JAVA_DEPLOY                    NO

DBA                            WM_ADMIN_ROLE                  NO

DBA                            DELETE_CATALOG_ROLE            YES

DBA                            SELECT_CATALOG_ROLE            YES

DBA                            EXECUTE_CATALOG_ROLE           YES

DBA                            GATHER_SYSTEM_STATISTICS       NO

OLAP_DBA                       SELECT_CATALOG_ROLE            NO

JAVASYSPRIV                    JAVAUSERPRIV                   NO

SELECT_CATALOG_ROLE            HS_ADMIN_ROLE                  NO

EXECUTE_CATALOG_ROLE           HS_ADMIN_ROLE                  NO

 

13 rows selected

 

从上述结果中我们可以很清晰的看到有如下结论：

1、JAVASYSPRIV包含JAVAUSERPRIV。

2、要想正常的执行java存储过程，只需要JAVASYSPRIV这个role就可以了。

3、在赋予JAVASYSPRIV这个role后，如果要马上看到效果，需要断开当前session、再重新连接一下。

4、oracle存储过程的默认authid为definer，这种情况下oracle是看不到role里的权限的，但是从上面结果里我们可以看到对java存储过程，没有这个限制。

 

最后来回答一下这篇文章开头的问题，即为什么国航、南航可以，但东航就不行?

说来也简单，我问过我同事了，她告诉我国航、南航的dras用户有JAVASYSPRIV这个role，但东航没有。

自动产生不同数据库下指定schema间DDL同步脚本的存储过程

By cui hua on March 24, 2009 2:32 PM | No Comments

写这个自动产生同步脚本的代码足足用了我两天的时间，今天上午终于做完了。

我这里说一下我处理这个问题的思路：

现在要做的是ipra开发环境ipradev的用户ipra、mas和ipra国航测试环境caipratest的用户caipra、camas之间的DDL同步。注意这里是不同数据库下不同schema间的DDL同步，并且这些schema下的default tablespace和索引所在的tablespace均不相同。

   

    我原先本来用streams已经实现了上述DDL同步，但是因为streams不够稳定，我决定放弃streams，转而用代码来自动产生同步脚本。

    生成这个同步脚本的思路是：

    1、比较两边数据库相对应schema下同名object的last_ddl_time，以ipradev为同步的源头，去同步那些在caipratest上last_ddl_time滞后的object。

    2、如果ipradev中有object，但在caipratest中并不存在同名object，则在caipratest中建立该object。

    3、如果caipratest中有object，但在ipradev中并不存在同名object，则在caipratest中drop该object。

 

    在写上述代码的时候考虑到了如下问题：

1、考虑到了table、constraint、table中的字段的default值、table comment、column comment、index、view、sequence、type、type body、function、package、package body、procedure和trigger间的同步。

2、考虑到了按指定的"表空间映射"生成的DDL脚本中的表空间转换。

       3、不支持synonym的同步、因为synonym的目标可能是不同的schema，这个schema间的转换我无法事前知道，synonym的同步就手工做一下好了。

       4、dbms_metadata.get_ddl的返回值是CLOB，我专门写了一个子存储过程来一行一行打印CLOB。

5、dbms_output.put_line打印行的时候有一个不能超过255个byte的限制，如果你要print 的行的长度超过255个byte，oracle这里会报错 ORA-06502: PL/SQL numeric or value error: host bind array too small，在我代码里专门对这种情况做了处理，并且考虑到了中文，这个里面为了保证print出来的代码能够顺利的在目标数据库执行，并没有生硬的从254处截断。

6、oracle里server output的最大长度是100万，如果你一次更新需要print的内容太多，会超过这个100万的限制，此时oracle会报错：ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes，出现这种情况的时候需要手工处理一下。

 

P_SYS_GEN_DIFF_DEV_AND_TEST.prc是自动产生同步脚本的主存储过程

P_SYS_PRINT_DDL.prc是专门处理CLOB的子存储过程

 

在有连接池的情况下如何定位session

By cui hua on March 20, 2009 10:55 AM | No Comments

yangtingkun在"一次ORA-942错误的跟踪（一）"中为了定位一个错误的产生原因，他用到了sql trace，但他所面临的系统用到了连接池，连接池会给定位要做trace的session带来困难。他在原文中也做了这样的描述"由于JAVA程序使用了连接池，导致一旦程序启动，就会有多个会话同时连接到数据库中，因此很难判断到底哪个会话会执行出错的SQL语句。当然可以修改程序，在运行出错的SQL前面运行ALTER SESSION SET SQL_TRACE = TRUE。但是还是由于连接池的特性，无法保证ALTER SESSION语句与随后的select语句在同一个会话上执行。随意修改程序的方法是行不通的。还有一个办法，就是修改中间件连接池，使得程序只有一个会话连接到Oracle数据库中。"

 

最后他用到的解决方法就是对所有连接池里的session都做trace，然后一个一个查看trace文件，最后来达到定位指定session的目的。

这实际上是一种穷举，要是后台连接池开了100个session，莫非你还要看100个trace文件？

太麻烦了！

他提到"随意修改程序的方法是行不通的"，真的不行吗？当然可以！

答案就是用DBMS_SESSION.SET_IDENTIFIER。

 

修改代码，在执行sql前都先执行一下DBMS_SESSION.SET_IDENTIFIER就可以很方面的定位指定的session了，这个对连接池有效！并且这也是FGA针对的连接池的一种通常的解决方法。

 

Session 1:

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

 

SQL> select * from v$mystat where rownum<2;

 

       SID STATISTIC#      VALUE

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

       413          0          1

 

SQL> exec DBMS_SESSION.SET_IDENTIFIER ('CUIHUA');

 

PL/SQL procedure successfully completed

 

Session 2:

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

 

SQL> select sid,serial#,CLIENT_IDENTIFIER from v$session where CLIENT_IDENTIFIER = 'CUIHUA';

 

       SID    SERIAL# CLIENT_IDENTIFIER

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

       413      14490 CUIHUA

 

关于10g的automatic statistics collection

By cui hua on March 18, 2009 4:19 PM | No Comments

10g引入了automatic statistics collection，目的是想把dba从繁重的收集统计信息的工作中解脱出来，这个出发点是很好的！

对于automatic statistics collection，有些地方需要我们注意一下：

1、STATISTICS_LEVEL为TYPICAL或者ALL的时候会开启automatic statistics collection，如果要禁掉它，你可以通过将STATISTICS_LEVEL修改为BASIC。但oracle通常并不推荐这样做！如果你是因为automatic statistics collection跟你自己的统计信息的脚本在执行时间上有冲突，你可以通过修改WEEKNIGHT_WINDOW和WEEKEND_WINDOW的执行时间与方式来规避这种冲突。

 

2、从脚本catmwin.sql中可以非常清晰的看出其整体搭建流程。

catmwin.sql中有关automatic statistics collection的内容如下：

-- Create weeknight window.  Weeknight window is 10pm - 6am Mon - Fri.

BEGIN

   BEGIN

   dbms_scheduler.create_window(

      window_name=>'WEEKNIGHT_WINDOW',

      resource_plan=>NULL,

      repeat_interval=>'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;' ||

                    'byminute=0; bysecond=0',

      duration=>interval '480' minute,

      comments=>'Weeknight window for maintenance task');

   EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

   END;

   dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','SYSTEM',TRUE);

   dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',

                                 'FOLLOW_DEFAULT_TIMEZONE',TRUE);

EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

END;

/

 

-- Create weekend window.  Weekend window is from 12am Saturday through 12am

-- Monday.

BEGIN

    BEGIN

    dbms_scheduler.create_window(

       window_name=>'WEEKEND_WINDOW',

       resource_plan=>NULL,

       repeat_interval=>'freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0',

       duration=>interval '2880' minute,

       comments=>'Weekend window for maintenance task');

    EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

    END;

    dbms_scheduler.set_attribute('WEEKEND_WINDOW','SYSTEM',TRUE);

    dbms_scheduler.set_attribute('WEEKEND_WINDOW',

                                 'FOLLOW_DEFAULT_TIMEZONE',TRUE);

EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

END;

/

 

-- Create maintenance window group and add weeknight and weekend windows to it.

BEGIN

   BEGIN

   dbms_scheduler.create_window_group('MAINTENANCE_WINDOW_GROUP');

   dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',

                    'WEEKNIGHT_WINDOW');

   dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',

                    'WEEKEND_WINDOW');

   EXCEPTION

     when others then

       if sqlcode = -27477 then NULL;

       else raise;

       end if;

   END;

   dbms_scheduler.set_attribute('MAINTENANCE_WINDOW_GROUP','SYSTEM',TRUE);

EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

END;

/

 

-- Create gather stats program.

BEGIN

dbms_scheduler.create_program(

  program_name=>'gather_stats_prog',

  program_type=>'STORED_PROCEDURE',

  program_action=>'dbms_stats.gather_database_stats_job_proc',

  number_of_arguments=>0,

  enabled=>TRUE,

  comments

      =>'Oracle defined automatic optimizer statistics collection program');

EXCEPTION

  when others then

    if sqlcode = -27477 then NULL;

    else raise;

    end if;

END;

/

 

-- Create resource manager consumer group.

execute dbms_resource_manager.create_pending_area;

 

BEGIN

  dbms_resource_manager.create_consumer_group(

     consumer_group=>'AUTO_TASK_CONSUMER_GROUP',

     comment=>'System maintenance task consumer group');

EXCEPTION

  when others then

    if sqlcode = -29357 then NULL;

    else raise;

    end if;

END;

/

 

execute dbms_resource_manager.submit_pending_area;

 

-- Create autotask job class

BEGIN

   BEGIN

      sys.dbms_scheduler.create_job_class(

        job_class_name=>'AUTO_TASKS_JOB_CLASS',

        resource_consumer_group=>'AUTO_TASK_CONSUMER_GROUP',

        comments=>'System maintenance job class');

    EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

    END;

    dbms_scheduler.set_attribute('AUTO_TASKS_JOB_CLASS','SYSTEM',TRUE);

EXCEPTION

  when others then

    if sqlcode = -27477 then NULL;

    else raise;

    end if;

END;

/

 

-- Create stats collection job

BEGIN

    BEGIN

    dbms_scheduler.create_job(

      job_name=>'gather_stats_job',

      program_name=>'gather_stats_prog',

      job_class=>'auto_tasks_job_class',

      schedule_name=>'MAINTENANCE_WINDOW_GROUP',

      enabled=>TRUE,

      auto_drop=>FALSE,

      comments

          =>'Oracle defined automatic optimizer statistics collection job');

    EXCEPTION

      when others then

        if sqlcode = -27477 then NULL;

        else raise;

        end if;

    END;

    dbms_scheduler.set_attribute('gather_stats_job','stop_on_window_close',

             true);

    dbms_scheduler.set_attribute('gather_stats_job','restartable', true);

    dbms_scheduler.set_attribute('gather_stats_job',

        'user_operations_callback','dbms_stats.cleanup_stats_job_proc');

    dbms_scheduler.set_attribute('gather_stats_job','user_callback_context',1);

EXCEPTION

  when others then

    if sqlcode = -27477 then NULL;

    else raise;

    end if;

END;

/

从这个脚本里我们可以看到automatic statistics collection的搭建流程实在是再清晰不过了！

377152.1中提到：

With Oracle Database 10g the default maintenance window is configured to cover the following periods:

   10 pm to 6 am every weekday

    All weekend (Friday 10 pm to Monday 6 am)

就是说automatic statistics collection在周末的运行时间是从礼拜五晚上10点到礼拜一早上6点。

从这个脚本里我们也可以看到上述说法是不正确的！WEEKEND_WINDOW的真正执行时间是从礼拜五晚上12点到礼拜一早上0点。

 

3、要改WEEKEND_WINDOW，要先disable，然后改，再enable：

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(600, 'minute'));
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."WEEKNIGHT_WINDOW"');
END;

  

 

如何通过存储过程名得到session id

By cui hua on March 13, 2009 8:54 AM | No Comments

有同事问我"有没有办法，通过存储过程名，取session id"。

答案是有办法的，通过v$access。

 

Session 1：

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

 7839 KING       PRESIDENT       1981-11-17    5000.00               10

 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20

 7900 JAMES      CLERK      7698 1981-12-3      950.00               30

 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20

 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

 

12 rows selected

 

SQL> select * from scott.bonus;

 

ENAME      JOB              SAL       COMM

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

 

SQL> create or replace procedure P_GET_SID_BY_PNAME is

  2 

  3    vc_name varchar2(10);

  4 

  5  begin

  6 

  7    update scott.emp set sal=1000 where ename='SMITH';

  8    insert into scott.bonus(ENAME,JOB,SAL,COMM) values('SMITH','CLERK',1000,15);

  9 

 10    commit;

 11 

 12    while 1=1 loop

 13      select ename into vc_name from scott.emp where empno=7499;

 14 

 15      if ( vc_name <> 'CUIHUA') then

 16        dbms_lock.sleep(30);

 17      else

 18        exit;

 19      end if;

 20    end loop;

 21 

 22  end P_GET_SID_BY_PNAME;

 23  /

 

Procedure created

 

SQL> select sid from v$mystat where rownum<2;

 

       SID

----------

       408

 

SQL> exec P_GET_SID_BY_PNAME;

Executing......

 

现在我们session 1的sid为408，我们在这个session中执行了存储过程P_GET_SID_BY_PNAME。

 

现在我们要通过这个存储过程的名字P_GET_SID_BY_PNAME在session 2中把session 1的sid给找到，即要得到408。

Session 2:

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

 

SQL> select sid from v$access where object='P_GET_SID_BY_PNAME';

 

       SID

----------

       408

 

看到了吗，这里我们如愿得到了我们想要的sid。

v$access实际上是来源于：

select distinct s.inst_id,s.ksusenum,o.kglnaown,o.kglnaobj,
decode(o.kglobtyp,   0, 'CURSOR',    1, 'INDEX',    2, 'TABLE',    3, 'CLUSTER',   
4, 'VIEW',    5, 'SYNONYM',    6, 'SEQUENCE',    7, 'PROCEDURE',    8, 'FUNCTION',   
9, 'PACKAGE',    10,'NON-EXISTENT',    11,'PACKAGE BODY',    12,'TRIGGER',   
13,'TYPE',    14,'TYPE BODY',    15,'OBJECT',    16,'USER',    17,'DBLINK',   
18,'PIPE',    19,'TABLE PARTITION',    20,'INDEX PARTITION',    21,'LOB',   
22,'LIBRARY',    23,'DIRECTORY',    24,'QUEUE',    25,'INDEX-ORGANIZED TABLE',   
26,'REPLICATION OBJECT GROUP',    27,'REPLICATION PROPAGATOR',    28,'JAVA SOURCE',   
29,'JAVA CLASS',    30,'JAVA RESOURCE',    31,'JAVA JAR',    'INVALID TYPE')
from x$ksuse s,x$kglob o,x$kgldp d,x$kgllk l
where l.kgllkuse=s.addr and l.kgllkhdl=d.kglhdadr
and l.kglnahsh=d.kglnahsh and o.kglnahsh=d.kglrfhsh and o.kglhdadr=d.kglrfhdl;

 

注意这个例子中session 1是一直在执行存储过程P_GET_SID_BY_PNAME，其实这里只要session 1执行过P_GET_SID_BY_PNAME，不管是不是正在执行，有没有执行完毕，只要session 1没断开，v$access里始终会有上述那条sid为408的记录。但一旦session 1断开了，v$access里那条记录就没有了。

解决Multibyte character error一例（续）

By cui hua on March 10, 2009 1:25 PM | No Comments

在"解决Multibyte character error一例"这篇文章里，我最后用到的解决方法是set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1，有同事看到这篇文章后问我为什么要这么做？原因是什么？

我这里做一下说明，之所以有了上述解决方法是因为我曾经看到227330.1里有这样一段话"Files made on Unix systems (using vi for example) are most of the time WE8ISO8859P1 depending on the LOCALE setting or used telnet/ssh config." 

 

下面我以一个例子来说明上述问题：

首先来重现一下问题：

E:\>sqlldr ipra/acca@ipradev control=ATPCO.ctl data=LKTCN081121.bad

 

SQL*Loader: Release 10.2.0.1.0 - Production on 星期二 3月 10 12:49:24 2009

 

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

这里sqlldr实际上是一条数据都没有导入进去。

 

这里的本质原因是因为这里windows客户端默认的NLS_LANG是SIMPLIFIED CHINESE_CHINA.ZHS16GBK，而ANSI编码方式中又没有A5所对应的字符，所以oracle这里报错Multibyte character error。

 

WE8ISO8859P1是很常用的西欧字符集，我当时估计里面肯定是有A5的，而且当时我的源数据文件中并没有中文，所以我这里放心的将NLS_LANG设成AMERICAN_AMERICA.WE8ISO8859P1，目的就是告诉oracle我这里A5不是ANSI编码，WE8ISO8859P1有A5，你帮我在WE8ISO8859P1和ZHS16GBK之间做一个转换吧。

 

E:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

 

E:\>sqlldr ipra/acca@ipradev control=ATPCO.ctl data=LKTCN081121.bad

 

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Mar 10 12:59:52 2009

 

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

 

Commit point reached - logical record count 2

 

转换后我们来看一下转换后A5变成了什么：

SQL> select substr(oatint,314,1) from owbatptemp;

 

SUBSTR(OATINT,314,1)

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

￥

￥

原来我们待导入的字符是A5，也就是¥。

现在导入完成后变成了￥。

用ultraEdit看了一下这个￥的16进制码，ultraEdit显示这个￥的16进制码是A3 A4，我们来验证一下       ：

SQL> select dump(substr(oatint,314,1)) from owbatptemp;

 

DUMP(SUBSTR(OATINT,314,1))

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

Typ=1 Len=2: 163,164

Typ=1 Len=2: 163,164

 

这里的163，164就是A3和A4：

SQL> select to_char(163,'XX') from dual;

 

TO_CHAR(163,'XX')

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

 A3

 

SQL> select to_char(164,'XX') from dual;

 

TO_CHAR(164,'XX')

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

 A4

我们可以很清晰的看到这里实际上是发生了字符集的转换。

 

注意这里如果待导入的源数据中有中文，这种方式就不可以采用！一旦采用，所有的中文都会变成乱码，而且不可逆！原因就不用我说了吧：）

将LKTCN081121.bad的最开始插入两个中文字符"崔华"，然后再次以同样方式导入，我们来看一下结果：

E:\>sqlldr ipra/acca@ipradev control=ATPCO.ctl data=LKTCN081121.bad

 

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Mar 10 13:16:38 2009

 

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

 

Commit point reached - logical record count 2

 

QL> select substr(oatint,1,10) from owbatptemp;

 

SUBSTR(OATINT,1,10)

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

′T？a070006

′T？a070006

0700060842

0700060851

 

好了，我来总结一下：

1、WE8ISO8859P1不是unicode字符集，它是ISO西欧字符集之一，它不是ZHS16GBK的超集。

2、如果待导入的源数据中有中文，就不能采用我上述的解决方式。

如何恢复一个被误drop的存储过程

By cui hua on March 6, 2009 11:07 AM | No Comments

今天有同事给我写信："我大概10分钟前错误地drop掉了一个存储过程：P_IPACCHECK_NC，而这个存储过程的源码我本机又没有备份，麻烦您恢复一下，谢谢"

 

这种恢复是非常容易的，原理就是利用了oracle里所有的存储过程的源代码都是存在dba_source里，而drop某个存储过程的时候，oracle这里肯定要去dba_source里把相关的源代码给delete掉，既然是delete，那就好办咯，直接flashback query就可以了。

 

如下是完整的恢复过程：

用sys用户登陆，执行如下的查询：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD HH24:MI:SS') where owner='IPRA' and name= 'P_IPACCHECK_NC' order by line;

 

TEXT

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

procedure P_IPACCHECK_NC(n_flag     out number,

                                           vc_message out varchar2) is

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

  --    PROCEDURE NAME      : P_IPACCHECK_NC                       --

  --    NAME IN SYSMTH      : NONE                                --

  --    DESCRIPTION         : 对IWBIBT记录进行有效性检查，没有错误的数据置标志为

  --

  --       INVOKED          :                                     --

  --    PROGRAMMED BY       : ZhouXin         DATE  2008/12/02    --

  --     MODIFIED BY        :

  --         TYPE           : ONLINE                              --

  --                   COPYRIGHT 1997~2008 ACCA-ARK               --

  --                                                              --

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

  vc_ipastc  varchar2(20);

  n_errcount number := 0;

begin

  for rec_pac in (select * from iwbpac where ipastc is null) loop

 

TEXT

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

    n_errcount := 0;

    vc_ipastc  := rec_pac.ipastc;

    --检查清算月

    if rec_pac.ipalrm > to_number(to_char(sysdate, 'YYYYMM')) then

      vc_ipastc  := vc_ipastc || 'A';

      n_errcount := n_errcount + 1;

    end if;

    --检查名义开账公司

    if f_masaln_existawbprefix(rec_pac.ipaarr) != true then

      vc_ipastc  := vc_ipastc || 'B';

      n_errcount := n_errcount + 1;

    end if;

    --检查实际开账公司

    if f_masaln_existawbprefix(rec_pac.ipacar) != true then

      vc_ipastc  := vc_ipastc || 'C';

      n_errcount := n_errcount + 1;

    end if;

    --检查开账公司

    if f_masaln_existawbprefix(rec_pac.ipairl) != true then

      vc_ipastc  := vc_ipastc || 'E';

      n_errcount := n_errcount + 1;

 

TEXT

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

    end if;

    --检查名义开账公司

    if rec_pac.ipalas <> 'P' then

      vc_ipastc  := vc_ipastc || 'F';

      n_errcount := n_errcount + 1;

    end if;

    --检查帐单录入日期

    if rec_pac.ipanpd > to_number(to_char(sysdate, 'YYYYMMDD')) then

      vc_ipastc  := vc_ipastc || 'G';

      n_errcount := n_errcount + 1;

    end if;

    --检查开账月

    if rec_pac.ipailm > to_number(to_char(sysdate, 'YYYYMM')) then

      vc_ipastc  := vc_ipastc || 'H';

      n_errcount := n_errcount + 1;

    end if;

    --检查原始开账金额

    if rec_pac.ipaemk = 'B' and rec_pac.ipaamt is null then

      vc_ipastc  := vc_ipastc || 'I';

      n_errcount := n_errcount + 1;

    end if;

 

TEXT

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

    --检查清算期

    if to_number(rec_pac.ipacpr) < 1 or to_number(rec_pac.ipacpr) > 4 then

      vc_ipastc  := vc_ipastc || 'J';

      n_errcount := n_errcount + 1;

    end if;

    --检查开账期

    if to_number(rec_pac.ipabpr) < 1 or to_number(rec_pac.ipabpr) > 4 then

      vc_ipastc  := vc_ipastc || 'K';

      n_errcount := n_errcount + 1;

    end if;

    --没有错误，置标志位'0'

    if n_errcount = 0 then

      update iwbpac

         set ipastc = '0'

       where ipacpr = rec_pac.ipacpr

         and ipairl = rec_pac.ipairl

         and ipacar = rec_pac.ipacar

         and ipanvn = rec_pac.ipanvn

         and ipanva = rec_pac.ipanva

         and ipalrm = rec_pac.ipalrm;

    else

 

TEXT

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

      update iwbpac

         set ipastc = vc_ipastc

       where ipacpr = rec_pac.ipacpr

         and ipairl = rec_pac.ipairl

         and ipacar = rec_pac.ipacar

         and ipanvn = rec_pac.ipanvn

         and ipanva = rec_pac.ipanva

         and ipalrm = rec_pac.ipalrm;

    end if;

  end loop;

exception

  when others then

    n_flag     := 0;

    vc_message := substr(sqlerrm, 1, 1000);

end P_IPACCHECK_NC;

 

100 rows selected

 

呵呵，剩下该怎样做就不用我说了吧。

解决Multibyte character error一例

By cui hua on March 2, 2009 6:03 PM | No Comments

今天有同事写信问我："刚才在用SQLLDR导入的过程中发现一个小问题：就是SQLLDR rejected了两条数据，这两条数据的末尾都包含ascii码值为165的字符。附件是导入时用到的control文件以及SQLLDR的日志文件，导入数据表的对应列为varchar2(500)类型的。烦请您帮忙看看，为什么会出现这种被rejected的情况。谢谢"

 

我看了看日志，oracle这里显示reject的原因为Multibyte character error。

找他要了源文件，用sqlldr重现一下错误，果然有两条数据插入不进去。

我看了一下sqlldr产生的bad file，发现这两行数据末尾都显示为?，这里我高度怀疑就是这个问号的问题。

 

用ultraEdit看了一下这个?的16进制码，ultraEdit显示这个?的16进制码是A5，A5其实就是ascii 165，如下所示：

SQL> select to_number('A5','XX') from dual;

 

TO_NUMBER('A5','XX')

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

                 165

 

然后我查了一下ASCII表，发现ASCII 165是¥。

你把上述字符随便保存到一个文本文档里，windows会提示你这个文本文档里包含unicode字符。

呵呵，原来是这样，那知道原因了，就很好处理了，设置一下NLS_LANG就OK了，如下所示：

 

如果保持NLS_LANG为默认值，则那两条错误数据导不进去：

E:\>sqlldr ipra/acca@ipradev control=ATPCO.ctl data=LKTCN081121.bad

 

SQL*Loader: Release 10.2.0.1.0 - Production on 星期一 3月 2 17:13:28 2009

 

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

这里sqlldr实际上是一条数据都没有导入进去。

 

设一下NLS_LANG，马上就看出区别来了：

E:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

 

E:\>sqlldr ipra/acca@ipradev control=ATPCO.ctl data=LKTCN081121.bad

 

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Mar 2 17:39:33 2009

 

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

 

Commit point reached - logical record count 2

 

这里sqlldr已经把那两条错误的数据导入进去了，如下所示：

SQL> select substr(oatint,303) from owbatptemp;

 

SUBSTR(OATINT,303)

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

DIP *2BJ104￥

DIP *2BJ104￥

 

« February 2009 | Main Index | Archives | April 2009 »

Search

Recent Comments

  • the diet solution program reviews: By far, one of the best post l have come read more
  • Lose Ten Pounds in Three Days: By a long shot, one of the best post l read more
  • exercises to help lose 15 pounds: Without doubt, one of the best article l have come read more
  • Lose 20 Pounds a Week: I certainly enjoy your post, but having problem subscribing to read more
  • Aiko Potsander: Please continue to keep the good work! Cheers. read more
  • 毕业论文: 学习了。 read more
  • cui hua: 不用改数据，你改row directory里的指针就可以了——这就是我文中提到的update internal。 read more
  • yangjiawei: 领导，不好意思，再请问一下 我现在遇到一个问题，我现在已经将ind$里两个索引的状态改好了，数据库也拉起来了~！ 但是在修改obj$里name里为DEPENDENCY$这一行数据的data_object_id时遇到了困难，因为他原先的长度为2个字节，现在由于我move了一下，他的长度变成了4个字节，结果如下： 原先： col 1[2] @906: 0xc1 0x5d ==>92 read more
  • cui hua: 可以从上述sql中推断出等待的顺序（如何推断我已经在文中提到了），是不是太直观，但是综合上述症状来看，只有我文中提到的这种可能。 read more
  • yangjiawei: 我最近也正好再研究这些比较Internal的东西，所以想麻烦您能对这个问题写一个比较详细的解释及解决方案，我对这些也是比较感兴趣的，可以吗？ read more

Recent Entries

Tag Cloud

About this Archive

This page is an archive of entries from March 2009 listed from newest to oldest.

February 2009 is the previous archive.

April 2009 is the next archive.

Find recent content on the main index or look in the archives to find all content.