Oracle 10gR2: ORA-0600 when accessing data over database link
in [Oracle]
|
From: AlexB on 16 Feb 2007 16:09 On 16 Feb 2007 05:48:41 -0800, "bdbafh" <bdbafh(a)gmail.com> wrote: >On Feb 15, 5:43 am, AlexB <b...(a)pisem.net> wrote: >> On Thu, 15 Feb 2007 11:52:47 +0200, AlexB <b...(a)pisem.net> wrote: >> >On 14 Feb 2007 08:21:22 -0800, "Mark D Powell" <Mark.Pow...(a)eds.com> wrote: >> >> >>On Feb 14, 9:37 am, AlexB <b...(a)pisem.net> wrote: >> >>> Good day, everyone. >> >> >>> I am testing Oracle 10gR2 on a Linux for POWER platform, and I came across a >> >>> strange crash when trying to access data over database link: >> >> >>> SQL> desc workorder(a)l_amos >> >>> ERROR: >> >>> ORA-00600: internal error code, arguments: [kzdlk_zt2 err], >> >>> [18446744073709551603], [], [], [], [], [], [] >> >> >>> and then a huge text file in ?/admin/@/udump directory, containing all the >> >>> environment at the time of crash: >> >> >>> [quote] >> >>> *** ACTION NAME:() 2007-02-14 15:49:11.349 >> >>> *** MODULE NAME:(SQL*Plus) 2007-02-14 15:49:11.349 >> >>> *** SERVICE NAME:(SYS$USERS) 2007-02-14 15:49:11.349 >> >>> *** SESSION ID:(139.24400) 2007-02-14 15:49:11.349 >> >>> *** 2007-02-14 15:49:11.349 >> >>> ksedmp: internal or fatal error >> >>> ORA-00600: internal error code, arguments: [kzdlk_zt2 err], >> >>> [18446744073709551603], [], [], [], [], [], [] >> >>> Current SQL statement for this session: >> >>> select count(*) from address(a)l_amos >> >>> ----- Call Stack Trace ----- >> >>> calling call entry argument values in hex >> >>> location type point (? means dubious value) >> >>> -------------------- -------- -------------------- ---------------------------- >> >>> ksedst()+36 bl ksedst1()+0 >> >>> ksedmp()+700 bl ksedst()+0 >> >>> ... >> >> >>> .... >> >>> [unquote] >> >> >>> Any type of SQL statement crashes - SELECT, DESCRIBE, UPDATE etc. Tables which I >> >>> am trying to access do not contain any LONG columns. >> >> >>> I did some tests and found out, that this crash happens if database link has >> >>> been created with 'IDENTIFIED BY VALUES' clause: >> >> >>> SQL> CREATE PUBLIC DATABASE LINK L_AMOS CONNECT TO AMOSLINK IDENTIFIED BY VALUES >> >>> '3412736517' USING 'DBSRV2'; >> >> >>> DBSRV2 is an Oracle 9.2.0.8 database running on AIX. >> >> >>> If I create database link and use IDENTIFIED BY <password> clause - everything >> >>> works. >> >> >>> Did not test same on Oracle9i yet, but this behavior seem to be stable on 10gR2 >> >> >>> Oracle version is: >> >> >>> Connected to: >> >>> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production >> >>> With the Partitioning, OLAP and Data Mining options >> >> >>> I cannot remember password for remote user, this is why I am trying to use >> >>> CREATE DATABASE LINK ... IDENTIFIED BY VALUES, otherwise I have to change >> >>> password for this user and then sdjust other database links on other servers. >> >> >>> Anyone has this issue or am I the only one? >> >> >>> Kind regards, >> >> >>> Alex >> >> >>I checked metalink but did not find a hit on the "kzdlk_zt2 err" as >> >>being published. >> >> >>You findings are interesting. Are you using the Oracle Wallet >> >>feature? >> >> >>I would suggest recreating the links using username password strings >> >>if you know them as a work-a-round since you have discovered this. >> >> >>Since you have a repeatable case I suggest that if you have support >> >>you open a Service Request with Oracle. I would attempt to duplicate >> >>you error if we had a working 10gR2 system to do this with but it is >> >>on hold while we migrate other database across platforms. >> >> >>If you do not have a support contract post back with this fact and >> >>maybe someone who does and also uses database links will have the time >> >>to duplicate your error and will want to pursue this. >> >> >>How did you get data and the links into 10gR2: Upgrade in place, data >> >>migration between versions, or new install. >> >> >>HTH -- Mark D Powell -- >> >> >I failed to find anything on Metalink, too, and that is why I posted my findings >> >here. Finally, I managed to recover password for the 'AMOSLINK' account from my >> >records and re-created database links. Now everything works fine. I am not using >> >Oracle Wallet feature, label security or any additional authentication modules - >> >authentication is straight password check. We do have a support contract, never >> >opened a Service Request, though. I bet it is a good opportunity to try opening >> >a Service request. I will make some more experiments to see if situation is >> >steadily reproducible before aplpying to Oracle (will check 9i, too) and post >> >results here. >> >> >Data has been exported from 9i using exp utility 9.2.0.8 and imported to fresh >> >install 10gR2 using imp 10.2.0.2. Somehow database links were not imported with >> >our schemas (possibly they were PUBLIC, created by SYS), so I re-created them >> >using Quest Toad for Oracle tool: extracted script for AMOSLINK user, then >> >pasted 'IDENTIFIED BY VALUES' clause into CREATE DATABASE LINK statement. >> >> >--- >> >Alex >> >> As promised, I conducted few experiments (few times just to be sure), and >> problem seems to be reproducible. I am posting console logs for all actions >> (both local 10gR2 database and remote 9i database - target for dblink). As you >> can see from 3rd log - 9i does NOT support creating dblinks IDENTIFIED BY >> VALUES, so this issue does not exist in 9i. Seems to be a valid reason for >> opening a Service Request with Oracle. >> >> === LOCAL DATABASE 10gR2 ==== >> >> SQL> connect bozy/testpass >> Connected. >> SQL> select * from v$version; >> >> BANNER >> ---------------------------------------------------------------- >> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod >> PL/SQL Release 10.2.0.2.0 - Production >> CORE 10.2.0.2.0 Production >> TNS for Linux: Version 10.2.0.2.0 - Production >> NLSRTL Version 10.2.0.2.0 - Production >> >> SQL> create database link testlink_plain connect to bozy identified by testpass >> using 'dbsrv1'; >> >> Database link created. >> >> SQL> create database link testlink_values connect to bozy identified by values >> 'F054C7341555E21D' using 'dbsrv1' >> >> Database link created. >> >> SQL> column data format a15 trunc >> SQL> select * from testtab(a)testlink_plain; >> >> DATA PKEY >> --------------- ---------- >> data line 1 1000 >> data line 2 2000 >> data line 3 3000 >> >> SQL> select * from testtab(a)testlink_values; >> select * from testtab(a)testlink_values >> * >> ERROR at line 1: >> ORA-00600: internal error code, arguments: [kzdlk_zt2 err], >> [18446744073709551603], [], [], [], [], [], [] >> >> SQL> >> >> === REMOTE DATABASE 9i: ===== >> >> SQL*Plus: Release 9.2.0.8.0 - Production on Thu Feb 15 12:35:26 2007 >> >> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. >> >> SQL> connect bozy/testpass >> Connected. >> SQL> describe testtab >> Name Null? Type >> ----------------------------------------- -------- ---------------------------- >> DATA VARCHAR2(100) >> PKEY NOT NULL NUMBER(8) >> >> SQL> select * from >> column data format a20 trun >> SQL> select * from testtab; >> >> DATA PKEY >> -------------------- ---------- >> data line 1 1000 >> data line 2 2000 >> data line 3 3000 >> >> SQL> >> >> === LOCAL DATABASE 9i: ==== >> SQL> select * from v$version; >> >> BANNER >> ---------------------------------------------------------------- >> Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production >> PL/SQL Release 9.2.0.8.0 - Production >> CORE 9.2.0.8.0 Production >> TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production >> NLSRTL Version 9.2.0.8.0 - Production >> >> SQL> create database link testlink_values connect to bozy identified by values >> 'F054C7341555E21D' using 'dbsrv1' >> * >> ERROR at line 1: >> ORA-00988: missing or invalid password(s) >> >> =========================== >> >> -- >> Alex > > >IIRC, 10g R2 encrypts (or hashes) the password used for database >links. It used to be stored in clear text. Perhaps this behavior is >closer to the intended behavior than one might think. It still >deserves a better exception handling message than ora-600. > >Thanks for posting the test case. > >-bdbafh > It seems you are right. I received Oracle's reply to my SR. What they say is that IDENTIFIED BY VALUES clause is not intended for general users and ORA-600 is 'normal behavior'. I looked through at Oracle manual, and, indeed, this syntax is not listed. All my fault, while I agree that it will be better to report incorrect syntax (as it is in Oracle9i) rather than producing a misleading ORA-600 message. Alex PS. Anyway, it was a good experience - my first SR filed with Oracle (I am using Oracle products since RDBMS version 4 for VAX/VMS and IBM VM/SP back in 1986) |