Focus on Oracle: December 2009 Archives

如何在oracle里使用java存储过程连接db2

By cui hua on December 31, 2009 4:41 PM | No Comments

我不知道我做的这件事情是否还有其他人成功做过，但这篇文章里记录的方法你在metalink或者google上是找不到的，因为这来源于我这几天以来不断的艰苦尝试。

在这篇文章里，我们利用oracle里的java存储过程成功连上了db2 v9 for z/OS，并且执行了sql和其中的一个db2存储过程。

这个问题来源于我现在正在做的项目的需要，我们需要连主机并执行主机里的存储过程。当然，.net前台或者java前台连主机是没问题的，但是我们还是希望能够在oracle数据库端直连db2 v9 for z/OS，因为这样可以减少前台同oracle数据库端的交互并且可以避免冗余代码。

第一个思路是安装Transparent Gateway（即tg4drda），安装完后发现连db2 v9 for z/OS是没问题的，也能读出其中表里的数据，但就是不能执行其中的存储过程。这大致是因为10gR2的tg4drda不支持LOB的缘故，oracle的tg4drda的文档里明确指出：

When the gateway receives a call to run a stored procedure on the DRDA Server (for example DB2/OS390), it first does a lookup of the procedure name in the server catalog. The information that defines a stored procedure is stored in different forms on each DRDA Server. For example, DB2/OS390 V5.0 uses the table SYSIBM.SYSPROCEDURES, while DB2/OS390 V6.1 uses the table SYSIBM.SYSROUTINES.

在db2 v9的SYSIBM.SYSROUTINES里新引入了一个LOB列，这大概就是10gR2的tg4drda不能执行db2 v9 for z/OS里的存储过程的原因。

第二个思路就是使用java存储过程，因为JDBC驱动应该是能连上db2 v9 for z/OS的，但是我在实际用的过程中发现oracle里并不自带JDBC for db2的驱动，所以在缺省情况下，你在oracle的java存储过程里是连不上db2的。

怎么办？

我们现在来想办法把JDBC for db2的驱动加载到oracle里。

如下是一个完整的例子，就以这个当作是给需要的朋友们的新年礼物 ：）

1、找到所需要的JDBC for db2的驱动

安装"DB2 Connect Personal Edition V9.1 for Windows(32-bit)"，装完后从安装路径（我的是D:\Program Files\IBM\SQLLIB\java）下把db2java.zip、db2jcc.jar、db2jcc_license_cu.jar、db2jcc_javax.jar、db2jcc_license_cisuz.jar拷到$ORACLE_HOME /jdbc/lib下。

2、用loadjava把上述驱动导入到oracle中，注意这里要以sys用户执行导入，导入的过程中会出一些错误，没关系，可以忽略掉，导入的语法为：

loadjava -user sys/oracle@ipratest /u01/app/oracle/product/10.2.0/jdbc/lib/db2java.zip -force

loadjava -user sys/oracle@ipratest /u01/app/oracle/product/10.2.0/jdbc/lib/db2jcc_license_cisuz.jar -force

loadjava -user sys/oracle@ipratest /u01/app/oracle/product/10.2.0/jdbc/lib/db2jcc_license_cu.jar -force

loadjava -user sys/oracle@ipratest /u01/app/oracle/product/10.2.0/jdbc/lib/db2jcc.jar -force

loadjava -user sys/oracle@ipratest /u01/app/oracle/product/10.2.0/jdbc/lib/db2jcc_javax.jar -force

3、然后在sys用户下写java存储过程和wrapper function，注意一定要是在sys用户下，否则用不了JCC驱动，核心的代码为：

DriverManager.registerDriver(new com.ibm.db2.jcc.DB2Driver());

conn = DriverManager.getConnection("jdbc:db2://10.1.21.215:446/LOCDSN3", "xbsyl", "abcd");

//执行一个db2存储过程----------begin------------------------------

callname = "{CALL " + ProcedureName + "(?,?)}";

proc = conn.prepareCall(callname);

proc.setString(1, InputString);

proc.registerOutParameter(2, Types.VARCHAR);

proc.execute();

OutputString = proc.getString(2);

//执行一个db2存储过程----------End--------------------------------

//执行一个sql-----------------begin------------------------------

Statement st = conn.createStatement();

String sqlStr= Sqltext;

ResultSet rs = st.executeQuery(sqlStr);

while (rs.next())

{

OutputString = rs.getString(1);

}

//执行一个sql------------------End---------------------------------

4、测试一下调wrapper function的效果，我这里写了两个wrapper function，分别是F_SYS_CALL_DB2_BY_SQL和F_SYS_CALL_DB2_PROC。其中F_SYS_CALL_DB2_BY_SQL是在db2 v9 for z/OS上执行一个我传入的sql语句，并返回查询的结果；F_SYS_CALL_DB2_PROC是在db2 v9 for z/OS上执行一个我传入的db2存储过程，它的输入参数是要调用的db2存储过程的名字，输出参数就是执行这个db2存储过程后的返回值，我们来实际看一下效果：

首先我在一个安装了tg4drda的库里执行一个sql：

SQL> select deptname from DSN8910.DEPT@plhi3 where deptno='A00';

DEPTNAME

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

SPIFFY COMPUTER SERVICE DIV.

这里的plhi3就是指向db2 v9 for z/OS的db link，可以看到查询结果为SPIFFY COMPUTER SERVICE DIV。

然后我在未安装tg4drda的库里执行我写好的F_SYS_CALL_DB2_BY_SQL：

SQL> set serveroutput on size 1000000;

SQL> var vc_return_flag varchar2(2000);

SQL> begin

2 :vc_return_flag := F_SYS_CALL_DB2_BY_SQL('select deptname from DSN8910.DEPT where deptno=''A00''');

3 end;

4 /

PL/SQL procedure successfully completed

vc_return_flag

---------

SPIFFY COMPUTER SERVICE DIV.

可以看到返回的结果与tg4drda的结果一致，即我们已经通过java存储过程连上了db2 v9 for z/OS.

执行db2 v9 for z/OS里的存储过程的语法跟上述过程是一样，这里不再赘述。

Stored Outline在不同版本之间的一点差异

By cui hua on December 30, 2009 7:49 PM | No Comments

在Editing Stored Outlines in Oracle10g and Oracle11g [ID 726802.1]里提到，当我们想要改变一个sql的执行计划的时候我们可以选择edit Store Outline，而edit Store Outline在10g/11g里的步骤为：

1、connect to a schema with CREATE ANY OUTLINE privilege

2、clone the existing (public) Stored Outline into a private Stored Outline

3、edit the hints in the private Stored Outline

4、resynchronize the private Stored Outline with the edits

5、test the private Stored Outline (optional but recommended)

6、publish the private Stored Outline, thus replacing the public Stored Outline

7、test the new public Stored Outline: (optional but recommended)

并且在上述文章中专门提到：

Note: do not use the procedure DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES in Oracle10g and Oracle11g. Although this was part of the instructions for Oracle9i, it is no longer so for Oracle10g and Oracle11g and will prevent the successful cloning of the public Stored Outline.

这里只提到了让我们不要再调用DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES了，但是并没有讲原因。

我们现在来看一下为什么在10g/11g里就不需要执行上述procedure了？

在9i里执行下述查询：

SQL> select owner,object_name,object_type from dba_objects where object_name='OL$HINTS';

OWNER OBJECT_NAME OBJECT_TYPE

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

OUTLN OL$HINTS TABLE

在10g里执行同样的查询：

SQL> select owner,object_name,object_type from dba_objects where object_name='OL$HINTS';

OWNER OBJECT_NAME OBJECT_TYPE

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

OUTLN OL$HINTS TABLE

PUBLIC OL$HINTS SYNONYM

SYSTEM OL$HINTS TABLE

可以看到在10g里多了一个public的synonym和system下的一个同名表，我们来看看这多出来的这两个东东是什么：

SQL> set heading off;

SQL> set echo off;

SQL> Set pages 999;

SQL> set long 90000;

SQL> select dbms_metadata.get_ddl('SYNONYM','OL$HINTS','PUBLIC') from dual;

CREATE OR REPLACE PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS"

SQL> select dbms_metadata.get_ddl('TABLE','OL$HINTS','SYSTEM') from dual;

CREATE GLOBAL TEMPORARY TABLE "SYSTEM"."OL$HINTS"

( "OL_NAME" VARCHAR2(30),

"HINT#" NUMBER,

"CATEGORY" VARCHAR2(30),

"HINT_TYPE" NUMBER,

"HINT_TEXT" VARCHAR2(512),

"STAGE#" NUMBER,

"NODE#" NUMBER,

"TABLE_NAME" VARCHAR2(30),

"TABLE_TIN" NUMBER,

"TABLE_POS" NUMBER,

"REF_ID" NUMBER,

"USER_TABLE_NAME" VARCHAR2(64),

"COST" FLOAT(126),

"CARDINALITY" FLOAT(126),

"BYTES" FLOAT(126),

"HINT_TEXTOFF" NUMBER,

"HINT_TEXTLEN" NUMBER,

"JOIN_PRED" VARCHAR2(2000),

"SPARE1" NUMBER,

"SPARE2" NUMBER,

"HINT_STRING" CLOB

) ON COMMIT PRESERVE ROWS

原来是global temporary table。

这里我们来猜一下原因，在10g/11g里oracle把DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES省掉了，因为oracle可以把你对private Stored Outline做的更改存在上述global temporary table里，所以就不需要再在执行上述存储过程的owner下建立OL$HINTS以记录你对private Stored Outline做的更改了。

这个倒不是说9i下的DBMS_OUTLN_EDIT.CREATE_EDIT_TABLE在执行它的owner下建立的OL$HINTS就不是global temporary table，我们从9i的脚本utledtol.sql可以猜到9i的DBMS_OUTLN_EDIT.CREATE_EDIT_TABLE会调用utledtol.ql，而且创建的OL$，OL$HINTS和OL$NODES也都是global temporary table。

如下是9i里utledtol.ql的内容：

Rem

Rem $Header: utledtol.sql 26-feb-2002.07:58:50 sbodagal Exp $

Rem

Rem utledtol.sql

Rem

Rem NAME

Rem utledtol.sql - Outline editing utility file

Rem

Rem DESCRIPTION

Rem This file creates the outline tables OL$, OL$HINTS and OL$NODES

Rem and the associated indices in the user schema. The created tables

Rem will be used to store private outlines generated during an outline

Rem editing session. Users are expected to create the outline tables

Rem in their schemas before starting an outline editing session.

Rem

Rem NOTES

Rem Global temporary tables have been chosen for OL$, OL$HINTS and

Rem OL$NODES in order to provide the appropriate level of isolation

Rem between different editing sessions.

Rem

Rem MODIFIED (MM/DD/YY)

Rem sbodagal 02/26/02 - sbodagal_bug-2229346

Rem sbodagal 02/25/02 - #2095076 and #2229346

Rem - rename utleditol.sql to utledtol.sql

Rem svivian 08/15/00 - add new hash_value2 column

Rem svivian 06/16/00 - add spare fields

Rem sbodagal 06/05/00 - Created

Rem

create global temporary table ol$

(

ol_name varchar2(30), /* name is potentially generated */

sql_text long, /* the SQL stmt being outlined */

textlen number, /* length of SQL stmt */

signature raw(16), /* signature of sql_text */

hash_value number, /* KGL's calculated hash value */

hash_value2 number, /* hash value for stripped text */

category varchar2(30), /* category name */

version varchar2(64), /* db version @ outline creation */

creator varchar2(30), /* user from whom outline created */

timestamp date, /* time of creation */

flags number, /* e.g. everUsed, bindVars, dynSql */

hintcount number /* number of hints on the outline */

)

on commit preserve rows;

create global temporary table ol$hints

(

ol_name varchar2(30), /* outline name */

hint# number, /* which hint for a given outline */

category varchar2(30), /* collection/grouping name */

hint_type number, /* type of hint */

hint_text varchar2(512), /* hint specific information */

stage# number, /* stage of hint generation/applic'n */

node# number, /* QBC node id */

table_name varchar2(30), /* for ORDERED hint */

table_tin number, /* table instance number */

table_pos number, /* for ORDERED hint */

ref_id number, /* node id that this hint is referencing */

user_table_name varchar2(64), /* table name to which this hint applies */

cost double precision, /* optimizer estimated cost of the */

/* hinted operation */

cardinality double precision, /* optimizer estimated cardinality */

/* of the hinted operation */

bytes double precision, /* optimizer estimated byte count */

/* of the hinted operation */

hint_textoff number, /* offset into the SQL statement to */

/* which this hint applies */

hint_textlen number, /* length of SQL to which this hint applies */

join_pred varchar2(2000), /* join predicate (applies only for */

/* join method hints) */

spare1 number, /* spare number for future enhancements */

spare2 number /* spare number for future enhancements */

)

on commit preserve rows;

create global temporary table ol$nodes

(

ol_name varchar2(30), /* outline name */

category varchar2(30), /* outline category */

node_id number, /* qbc node identifier */

parent_id number, /* node id of the parent node for current node */

node_type number, /* qbc node type */

node_textlen number, /* length of SQL to which this node applies */

node_textoff number /* offset into the SQL statement to which this */

/* node applies */

)

on commit preserve rows;

create unique index ol$name on ol$(ol_name);

create unique index ol$signature on ol$(signature,category);

create unique index ol$hnt_num on ol$hints(ol_name, hint#);

记一次current online redo log缺失后的恢复

By cui hua on December 23, 2009 9:57 PM | 1 Comment

有一位MSN上的朋友今天问我了一个关于ORA-600[4000]错误的恢复，他是这么做的：

1、插入了2000条记录，但不commit；

2、马上shutdown abort；

3、接着rm掉所有的redo log；

他尝试过一些恢复手段后，碰到了ORA-600[4000]错误。

此时他陷入了一种两难的境地：如果他不用_corrupted_rollback_segments，则上述ORA-600[4000]无法解决；如果他用了_corrupted_rollback_segments，则oracle报错：

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-01555: snapshot too old: rollback segment number with name "" too small

我看了他发过来的trace文件和操作步骤，里面还是有一些不必要的操作。

这样吧，我在这里就把他做过的事情再做一遍，然后我会尝试恢复上述数据库，希望如下的过程能对朋友们有所帮助：

首先我插入2000条记录但不commit：

SQL_testdb>conn scott/tiger@testdb;

Connected.

SQL_testdb>create table testtb (type number,ts timestamp);

Table created.

SQL_testdb>begin

2 for i in 1..2000 loop

3 insert into testtb values (i,sysdate);

4 end loop;

5 end;

6 /

PL/SQL procedure successfully completed.

然后起另外一个session，执行shutdown abort：

SQL_testdb>shutdown abort

ORACLE instance shut down.

最后我把所有的redo log都rm掉：

$ ls -l

total 2543072

-rw-r----- 1 oracle dba 1531904 Dec 23 18:59 control01.ctl

-rw-r----- 1 oracle dba 1531904 Dec 23 18:59 control02.ctl

-rw-r----- 1 oracle dba 1531904 Dec 23 18:59 control03.ctl

-rw-r--r-- 1 oracle dba 20979712 Dec 23 18:27 cwmlite01.dbf

-rw-r--r-- 1 oracle dba 20979712 Dec 23 18:27 drsys01.dbf

-rw-r--r-- 1 oracle dba 144842752 Dec 23 18:27 example01.dbf

-rw-r--r-- 1 oracle dba 26222592 Dec 23 18:27 indx01.dbf

-rw-r--r-- 1 oracle dba 18 Dec 23 18:47 login.sql

-rw-r--r-- 1 oracle dba 20979712 Dec 23 18:27 odm01.dbf

-rw-r----- 1 oracle dba 104858112 Dec 23 18:27 redo01.log

-rw-r----- 1 oracle dba 104858112 Dec 23 18:27 redo02.log

-rw-r----- 1 oracle dba 104858112 Dec 23 18:59 redo03.log

-rw-r--r-- 1 oracle dba 419438592 Dec 23 18:58 system01.dbf

-rw-r--r-- 1 oracle dba 42999808 Dec 23 18:17 temp01.dbf

-rw-r--r-- 1 oracle dba 10493952 Dec 23 18:27 tools01.dbf

-rw-r--r-- 1 oracle dba 209723392 Dec 23 18:59 undotbs01.dbf

-rw-r--r-- 1 oracle dba 26222592 Dec 23 18:27 users01.dbf

-rw-r--r-- 1 oracle dba 39985152 Dec 23 18:27 xdb01.dbf

$ rm redo*.log

$ ls -l

total 1928648

-rw-r----- 1 oracle dba 1531904 Dec 23 18:59 control01.ctl

-rw-r----- 1 oracle dba 1531904 Dec 23 18:59 control02.ctl

-rw-r----- 1 oracle dba 1531904 Dec 23 18:59 control03.ctl

-rw-r--r-- 1 oracle dba 20979712 Dec 23 18:27 cwmlite01.dbf

-rw-r--r-- 1 oracle dba 20979712 Dec 23 18:27 drsys01.dbf

-rw-r--r-- 1 oracle dba 144842752 Dec 23 18:27 example01.dbf

-rw-r--r-- 1 oracle dba 26222592 Dec 23 18:27 indx01.dbf

-rw-r--r-- 1 oracle dba 18 Dec 23 18:47 login.sql

-rw-r--r-- 1 oracle dba 20979712 Dec 23 18:27 odm01.dbf

-rw-r--r-- 1 oracle dba 419438592 Dec 23 18:58 system01.dbf

-rw-r--r-- 1 oracle dba 42999808 Dec 23 18:17 temp01.dbf

-rw-r--r-- 1 oracle dba 10493952 Dec 23 18:27 tools01.dbf

-rw-r--r-- 1 oracle dba 209723392 Dec 23 18:59 undotbs01.dbf

-rw-r--r-- 1 oracle dba 26222592 Dec 23 18:27 users01.dbf

-rw-r--r-- 1 oracle dba 39985152 Dec 23 18:27 xdb01.dbf

好了，现在我们来开始恢复。

现在直接open resetlog肯定是打不开的：

SQL_testdb>startup mount

ORACLE instance started.

Total System Global Area 505382744 bytes

Fixed Size 743256 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 1323008 bytes

Database mounted.

SQL_testdb>recover database until cancel;

ORA-00279: change 188425 generated at 12/23/2009 18:27:25 needed for thread 1

ORA-00289: suggestion : /cadrasu01/app/oracle/product/9.2.0/dbs/arch1_1.dbf

ORA-00280: change 188425 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/dras21/testdb/system01.dbf'

ORA-01112: media recovery not started

SQL_testdb>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/dras21/testdb/system01.dbf'

我们来创建pfile：

SQL_testdb>create pfile='/dras21/testdb/inittestdb.ora' from spfile;

File created.

然后我们在pfile中加入*._allow_resetlogs_corruption=TRUE后再次open resetlog：

SQL_testdb>startup mount pfile='/dras21/testdb/inittestdb.ora';

ORACLE instance started.

Total System Global Area 505382744 bytes

Fixed Size 743256 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 1323008 bytes

Database mounted.

SQL_testdb>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

此时的alert log里会记录:

Errors in file /cadrasu01/app/oracle/admin/testdb/udump/testdb_ora_7090390.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-01555: snapshot too old: rollback segment number with name "" too small

Wed Dec 23 19:20:51 2009

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 7090390

ORA-1092 signalled during: alter database open resetlogs...

这里我们重现了那位朋友提到的错误。

上述trace文件（即/cadrasu01/app/oracle/admin/testdb/udump/testdb_ora_7090390.trc）不具备参考价值，我们来做一个10046，步骤为：

SQL_testdb>startup mount pfile='/dras21/testdb/inittestdb.ora';

ORACLE instance started.

Total System Global Area 505382744 bytes

Fixed Size 743256 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 1323008 bytes

Database mounted.

SQL_testdb>oradebug setmypid

Statement processed.

SQL_testdb>oradebug unlimit

Statement processed.

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

Statement processed.

SQL_testdb>recover database until cancel;

ORA-00279: change 208432 generated at 12/23/2009 19:45:06 needed for thread 1

ORA-00289: suggestion : /cadrasu01/app/oracle/product/9.2.0/dbs/arch1_1.dbf

ORA-00280: change 208432 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/dras21/testdb/system01.dbf'

ORA-01112: media recovery not started

SQL_testdb>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

此时，我们再去分析上述10046产生的trace文件，直接看上述trace文件的最末尾，我们发现有这样的内容：

EXEC #10:c=0,e=236,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1232000925678362

WAIT #10: nam='db file sequential read' ela= 176 p1=1 p2=202 p3=1

WAIT #10: nam='db file sequential read' ela= 183 p1=1 p2=106 p3=1

FETCH #10:c=0,e=454,p=2,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1232000925678842

FETCH #4:c=0,e=1713,p=3,cr=5,cu=0,mis=0,r=0,dep=1,og=4,tim=1232000925678907

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-01555: snapshot too old: rollback segment number with name "" too small

EXEC #1:c=200000,e=5922169,p=75,cr=705,cu=1,mis=0,r=0,dep=0,og=4,tim=1232000925679637

ERROR #1:err=1092 tim=1602843205

也就是说oracle在读file 1，block 202和file 1，block 106的时候可能出了问题。

我用BBED看了上述block，没发现有什么异常，也就是说这条路已经走不下去了。

好了，我们来换一条路，直接open：

SQL_testdb>alter database open;

alter database open

*

ERROR at line 1:

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [4193], [0], [57], [], [], [], [], []

呵呵，见到4193了，那这个库肯定可以打开了。

我们把*._corrupted_rollback_segments=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)加到上述pfile里再次open：

SQL_testdb>startup mount pfile='/dras21/testdb/inittestdb.ora';

ORACLE instance started.

Total System Global Area 505382744 bytes

Fixed Size 743256 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 1323008 bytes

Database mounted.

SQL_testdb>alter database open;

Database altered.

好了，现在我们已经成功把上述数据库给打开了，接下来的收尾工作就是要切换undo tablespace，重建spfile，这个大家肯定都知道，我这里无需再赘述。

最后我们来看一下那2000条数据还在不在：

SQL_testdb>conn scott/tiger@testdb;

Connected.

SQL_testdb>select count(*) from testtb;

select count(*) from testtb

*

ERROR at line 1:

ORA-00942: table or view does not exist

正如kamus所说，online redo log缺失通常意味着必然会有数据的丢失。

log switch checkpoint

By cui hua on December 17, 2009 9:06 AM | No Comments

有同事问我----"当发生log switch的时候，这时候oracle是会做full checkpoint还是incremental checkpoint？"

这个问题由来已久，itpub上也有朋友讨论过这个问题，有说是full checkpoint的，也有说是incremental checkpoint的，晶晶也曾撰文指出在log switch的时候，发生的是incremental checkpoint。

其实如果你深入了解full checkpoint和incremental checkpoint的区别，当是能回答出上述问题。

我们说：

在8i以前，log switch的时候oracle确实是会做full checkpoint；但从8i开始，oracle在log switch的时候做的是"incremental checkpoint"，之所以这里要带上引号，是因为这里从严格意义上来说并不能完全算是incremental checkpoint（因为在log switch的时候，不仅会像incremental checkpoint那样更新control file，还会更新datafile header）。

实际上，在log switch的时候，发生的是oracle中的7种checkpoint类型之一的"Log Switch Checkpoint"。

我们来看一个实例，在这个例子里，我们证明了在log switch的时候，发生的既不是full checkpoint，也不是严格意义上的incremental checkpoint。

我们首先来证明在log switch的时候，发生的不是full checkpoint：

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 INACTIVE

2 CURRENT

3 INACTIVE

SQL> alter system switch logfile;

System altered

执行完上述switch logfile操作后等待1分钟，然后再次执行上述查询语句：

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 INACTIVE

2 ACTIVE

3 CURRENT

从结果里我们可以看到现在redo log group 2还是处于active状态，上述数据库只有我一个人用，很闲，如果switch logfile的时候发生的是full checkpoint，则当我等待1分钟后再次查询v$log的时候redo log group 2必然是处于inactive状态：

SQL> set time on

8:33:51 SQL> select group#,status from v$log;

GROUP# STATUS

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

1 INACTIVE

2 ACTIVE

3 CURRENT

8:33:57 SQL> alter system checkpoint;

System altered

8:34:08 SQL> select group#,status from v$log;

GROUP# STATUS

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

1 INACTIVE

2 INACTIVE

3 CURRENT

即现在我们已经证明了在log switch的时候，发生的不是full checkpoint。

现在我们来证明在log switch的时候，发生的不是严格意义上的incremental checkpoint。我们知道，incremental checkpoint只会更新control file，不会更新datafile header，我们证明的思路就来源于此：

BBED> set file 1

FILE# 1

BBED> set block 1

BLOCK# 1

BBED> p kcvfhckp

struct kcvfhckp, 160 bytes @140

struct kcvcpscn, 8 bytes @140

ub4 kscnbas @140 0xbb6aaade

ub2 kscnwrp @144 0x0008

ub4 kcvcptim @148 0x2a122556

ub2 kcvcpthr @152 0x0001

union u, 12 bytes @156

struct kcvcprba, 12 bytes @156

ub4 kcrbaseq @156 0x00000004

ub4 kcrbabno @160 0x0000000f

ub2 kcrbabof @164 0x0010

struct kcvcptr, 12 bytes @156

struct kcrtrscn, 8 bytes @156

ub4 kscnbas @156 0x00000004

ub2 kscnwrp @160 0x0000

ub4 kcrtrtim @164 0x001000ff

ub1 kcvcpetb[0] @168 0x02

......省略显示部分内容

ub1 kcvcpetb[128] @296 0x00

即现在的system01.dbf的datafile header的checkpoint scn的base是0xbb6aaade。

现在我执行一次switch logfile，再来观察system01.dbf的datafile header的checkpoint scn：

SQL> alter system switch logfile;

System altered

BBED> p kcvfhckp

struct kcvfhckp, 160 bytes @140

struct kcvcpscn, 8 bytes @140

ub4 kscnbas @140 0xbb6aaade

ub2 kscnwrp @144 0x0008

ub4 kcvcptim @148 0x2a122556

ub2 kcvcpthr @152 0x0001

union u, 12 bytes @156

struct kcvcprba, 12 bytes @156

ub4 kcrbaseq @156 0x00000004

ub4 kcrbabno @160 0x0000000f

ub2 kcrbabof @164 0x0010

struct kcvcptr, 12 bytes @156

struct kcrtrscn, 8 bytes @156

ub4 kscnbas @156 0x00000004

ub2 kscnwrp @160 0x0000

ub4 kcrtrtim @164 0x001000ff

ub1 kcvcpetb[0] @168 0x02

......省略显示部分内容

ub1 kcvcpetb[128] @296 0x00

我们发现现在的system01.dbf的datafile header的checkpoint scn的base还是0xbb6aaade，也就是说oracle在switch logfile的时候的checkpoint并不是马上发生，oracle在等待一个发生的时机。

我们现在强制让log switch checkpoint马上发生：

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

BBED> p kcvfhckp

struct kcvfhckp, 160 bytes @140

struct kcvcpscn, 8 bytes @140

ub4 kscnbas @140 0xbb6aac6e

ub2 kscnwrp @144 0x0008

ub4 kcvcptim @148 0x2a1229b6

ub2 kcvcpthr @152 0x0001

union u, 12 bytes @156

struct kcvcprba, 12 bytes @156

ub4 kcrbaseq @156 0x00000006

ub4 kcrbabno @160 0x00000002

ub2 kcrbabof @164 0x0010

struct kcvcptr, 12 bytes @156

struct kcrtrscn, 8 bytes @156

ub4 kscnbas @156 0x00000006

ub2 kscnwrp @160 0x0000

ub4 kcrtrtim @164 0x00100000

ub1 kcvcpetb[0] @168 0x02

......省略显示部分内容

ub1 kcvcpetb[128] @296 0x0

看到了吗？现在system01.dbf的datafile header的checkpoint scn的base已经变成了0xbb6aac6e，也就是说----在log switch的时候，发生的不是严格意义上的incremental checkpoint，因为其不仅更新了control file，还更新了datafile header。

最后，我们来说一下oracle中checkpoint的种类。oracle中的checkpoint一共有7种，它们分别是：

1、Full Checkpoint

2、Thread Checkpoint

3、File Checkpoint

4、Object Checkpoint

5、Parallel Query Checkpoint

6、Incremental Checkpoint

7、Log Switch Checkpoint

昨天我被问到的问题

By cui hua on December 16, 2009 3:26 PM | 3 Comments

昨天有HP的工程师打电话找我，他说他们那里有我一份简历，说是要跟我聊一聊oracle，算是面试吧。我很开心有人愿意跟我聊oracle，不管是采用哪种方式，问问题也好，面试也好。

这一聊就聊了45分钟，最后他的结论是："你的oracle的基本概念没有任何问题，但是实践经验不太够"。

总的来说，那位HP的工程师问我的问题并不难，也没有涉及到RAC、Data Guard、Streams等分支，但是他问的很细，有些细节我当时真的记不太清楚了。

如下是一些昨天我没有回答好或者没有回答全的问题，我整理了一下，希望能对朋友们有所帮助：

1、 dedicated模式、非RAC、无连接池、要求支持2000个连接，在这样的条件下如何设置PGA？

答：a) 先把PGA设置成总的物理内存的16％；

b) v$process中有一个字段PGA_USED_MEM用来表示这个process实际用掉的PGA，可以用这个估算出在2000个连接的情况下需要多少PGA，然后用这个值去调整上述PGA的初始值；

另外，用v$pgastat中的total PGA inuse可以查看当前系统的PGA用了多少，这个值应该和v$process中的sum(PGA_USED_MEM)接近。

2、 如何解决ORA-04031问题？

答：ORA-04031不是三言两语就能说清楚的，详情可参见146599.1和396940.1。

3、 Current online redo log被删掉或者损坏后如何恢复？

答：这个也挺复杂，我当时也没跟他说细节，我只是告诉他，这个我能恢复。在没有备份的情况下恢复的方法可能会利用到_allow_resetlogs_corruption、_corrupted_rollback_segments 、fast_start_parallel_rollback、10015或者~~_giga_minimum_scn~~_minimum_giga_scn以及BBED。

4、 oracle里的补丁具体分为哪几种类型？

答：oracle里的补丁具体分为如下这样6种类型：

Oracle Interim patches: A patch that is applied in between patchset releases with the Opatch utility. Interim patches are sometimes also referred to as patchset exceptions.

Oracle Merge patches: A type of interim patch that merges multiple fixes to ensure that one fix doesn't overwrite the other. Merge patches are typically required when a conflict is detected between an existing patch and a new patch.

Oracle Bundle patches: A type of interim patch that contains fixes to many important bugs, though not as many as a patchset.

Oracle Critical Patch Update (CPU) patches: A type of interim patch that contains fixes to critical security bugs delivered on a quarterly basis.

Patch Set Updates(PSUs) : PSUs are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule. PSUs are on the same quarterly schedule as the Critical Patch Updates (CPU), specifically the Tuesday closest to the 15th of January, April, July, and October.

Oracle Patchsets: Software-release mechanisms for delivering tested and integrated product fixes on a regular basis.

详细解析oracle中的transaction

By cui hua on December 8, 2009 3:41 PM | No Comments

之前已经写过：

"详细解析9i和10g的datafile header"

"详细解析LMT的datafile的物理结构"

"详细解析datafile的status"

这里是详细解析系列的第四篇文章，在这篇文章里，我们详细解析了oracle中的transaction的流程。

我们从一个实例开始说起：

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 12:00:39 2009

SQL_astca>conn armshistemp/armshistemp@astcatest;

Connected.

SQL_astca>select count(*) from uplpdt;

COUNT(*)

----------

6962716

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>select group#,status from v$log;

GROUP# STATUS

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

1 ACTIVE

2 CURRENT

3 INACTIVE

SQL_astca>select updagt,dump(updagt,16) from uplpdt where updprf='999' and updfrm='240' and updtkt='9854125' and updcpn=1;

UPDAGT DUMP(UPDAGT,16)

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

08300000 Typ=1 Len=8: 30,38,33,30,30,30,30,30

SQL_astca>update uplpdt set updagt='08306432' where updprf='999' and updfrm='240' and updtkt='9854125' and updcpn=1;

1 row updated.

SQL_astca>commit;

Commit complete.

SQL_astca>select updagt,dump(updagt,16) from uplpdt where updprf='999' and updfrm='240' and updtkt='9854125' and updcpn=1;

UPDAGT DUMP(UPDAGT,16)

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

08306432

Typ=1 Len=8: 30,38,33,30,36,34,33,32

SQL_astca>select member from v$logfile where group#=2;

MEMBER

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

/dras10/oradata/astca/redo02a.log

/dras11/oradata/astca/redo02b.log

SQL_astca>alter system dump logfile '/dras10/oradata/astca/redo02a.log';

System altered.

从上述dump文件中我们可以看到oracle在真正开始上述transaction前会做下述一系列的事情：

1、将job$的列THIS_DATE更新成2009-12-08 12:02:01后commit；

2、lock表REPCAT$_REPCAT中的第一个itl；

3、lock表REPCAT$_REPSCHEMA中的第二个itl；

4、lock表DBMS_LOCK_ALLOCATED中的第三个itl；

5、将表DBMS_LOCK_ALLOCATED的列EXPIRATION更新成2009-12-12 12:02:01后commit；

6、对表REPCAT$_REPCAT上的两个itl执行block clean out并lock表REPCAT$_REPCAT中的第二个itl；

7、对表REPCAT$_REPSCHEMA上的两个itl执行block clean out并lock表REPCAT$_REPSCHEMA中的第一个itl；

......省略显示部分类似内容

8、更新表SMON_SCN_TIME。

在了解opcodes的情况下读懂上述redo log并不困难。

上述几条都做完后，oracle开始了我们真正关注的transaction：

REDO RECORD - Thread:1 RBA: 0x000003.00000017.0010 LEN: 0x01b8 VLD: 0x01

SCN: 0x0008.bb667126 SUBSCN: 1 12/08/2009 12:04:02

CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0008.bb6670fd SEQ: 1 OP:5.2

ktudh redo: slt: 0x0016 sqn: 0x0000d0f8 flg: 0x000a siz: 136 fbi: 0

uba: 0x12c0002b.2eaa.01 pxid: 0x0000.000.00000000

CHANGE #2 TYP:1 CLS:34 AFN:75 DBA:0x12c0002b SCN:0x0008.bb667126 SEQ: 1 OP:5.1

ktudb redo: siz: 136 spc: 0 flg: 0x000a seq: 0x2eaa rec: 0x01

xid: 0x0009.016.0000d0f8

ktubl redo: slt: 22 rci: 0 opc: 11.1 objn: 84192 objd: 85820 tsn: 83

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

0x00000000 prev ctl uba: 0x12c0002a.2eaa.44

prev ctl max cmt scn: 0x0008.bb6646e9 prev tx cmt scn: 0x0008.bb664985

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

op: L itl: xid: 0x0001.02e.0000da74 uba: 0x14837dfc.2c60.1d

flg: C--- lkc: 0 scn: 0x0002.968c0104

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x1bc0000d hdba: 0x1bc0000c

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0

ncol: 101 nnew: 1 size: 0

col 6: [ 8] 30 38 33 30 30 30 30 30

CHANGE #3 TYP:2 CLS: 1 AFN:111 DBA:0x1bc0000d SCN:0x0008.bb666fd5 SEQ: 1 OP:11.5

KTB Redo

op: 0x11 ver: 0x01

op: F xid: 0x0009.016.0000d0f8 uba: 0x12c0002b.2eaa.01

Block cleanout record, scn: 0x0008.bb667126 ver: 0x01 opt: 0x02, entries follow...

itli: 2 flg: 2 scn: 0x0008.bb666fd5

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x1bc0000d hdba: 0x1bc0000c

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0

ncol: 101 nnew: 1 size: 0

col 6: [ 8] 30 38 33 30 36 34 33 32

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20

session number = 20

serial number = 1751

transaction name =

REDO RECORD - Thread:1 RBA: 0x000003.00000018.0010 LEN: 0x0054 VLD: 0x01

SCN: 0x0008.bb66712b SUBSCN: 1 12/08/2009 12:04:13

CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0008.bb667126 SEQ: 1 OP:5.4

ktucm redo: slt: 0x0016 sqn: 0x0000d0f8 srt: 0 sta: 9 flg: 0x2

ktucf redo: uba: 0x12c0002b.2eaa.01 ext: 1 spc: 8012 fbi: 0

END OF REDO DUMP

----- Redo read statistics for thread 1 -----

其中跟我们上述transaction所相关的opcodes的值及其含义为：

5.1 Generate undo block

5.2 Update rollback segment header

5.4 Commit Transaction(transaction table update)

11.5 Update Row Piece

URP表示update

从上述redo log的dump文件中我们可以清晰的看出oracle中的transaction的整体流程为：

1. Undo segment binding（即op:5.2）

2. Slot allocation in the transaction table

3. Undo block allocation（即op:5.1）

4. Find an interested transaction list (ITL) available in the block.

5. Lock the row you are modifying.

6. Generate the undo part of data describing （即op:5.1中的code:URP）

7. Generate the redo part of data describing the changes to the data block.（即op:11.5）

8. Create the redo record and apply the changes to the blocks.

9. Finds an commit SCN value（即0x0008.bb66712b）.

10. Updates the transaction table（即ktucm redo，注意这里的status已经变成9了）.

11. Puts the current undo block into the free block pool (under some conditions, 即ktucf redo).

12. Creates a commit record in the redo log buffer.（即op:5.4）

13. Flushes the redo log buffer to disk (for durability).

14. Releases locks held on rows and tables, may execute fast block cleanout.

December 2009 Archives

如何在oracle里使用java存储过程连接db2

Stored Outline在不同版本之间的一点差异

记一次current online redo log缺失后的恢复

log switch checkpoint

昨天我被问到的问题

详细解析oracle中的transaction

Categories

Monthly Archives

Search

Recent Comments

Recent Entries

About this Archive

December 2009 Archives

如何在oracle里使用java存储过程连接db2

Stored Outline在不同版本之间的一点差异

记一次current online redo log缺失后的恢复

log switch checkpoint

昨天我被问到的问题

详细解析oracle中的transaction

Categories

Monthly Archives

Search

Recent Comments

Recent Entries

Tag Cloud

About this Archive