Friday, September 28, 2012

Workaround for ORA-00600: internal error code, arguments: [kzdlk_zt2 err]

To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:

How to Create a Database Link in Another User’s Schema

Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.


Recently I had situation when I didn’t know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.

In that case my method was:
(execute this steps as user with admin privileges)

-- Grant privilege to create database link to user
grant create database link to locuser;


-- Create temporary procedure to execute create database link as another user
CREATE OR REPLACE PROCEDURE locuser.create_dblink AS
BEGIN
    EXECUTE IMMEDIATE 'CREATE DATABASE LINK TEST_DB CONNECT TO RMTUSER IDENTIFIED BY VALUES ''E977335D83B8468E'' USING ''testdb''';
END create_dblink;
/

-- Execute procedure and create database link in another schema
exec locuser.create_dblink;


-- Drop temporary procedure
drop procedure locuser.create_dblink;


-- Revoke privilege to create database link
revoke create database link from locuser;


But when I tried to test database link I’ve received this nasty error:

ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], [], [], [], [], []


There is MOS note related to this error: [ID 1309705.1]

The error suggests that when the database link was created, the was established using the syntax IDENTIFIED BY VALUES as compared to the document syntax of IDENTIFIED BY

Use of IDENTIFIED BY VALUES is reserved for internal Oracle use only.

While earlier Oracle releases allowed the use of IDENTIFIED BY VALUES, this is not documented as being valid syntax.

From Oracle release 10gR2, database links must be created using the documented syntax.

Solution: Recreate the database link using valid syntax.


So Oracle support says that I cannot use syntax IDENTIFIED BY VALUES while creating database link.

Luckily I’ve found workaround :-)



Environment: Oracle EE 11.1.0.7 on OEL 5.8


To reproduce error I’ve pulled password of another user's schema from "sys.user$" internal table logged as sys user.
Now I will try to create a database link using this password.

-- Create database link
SQL> CREATE DATABASE LINK TEST_DB CONNECT TO RMTUSER IDENTIFIED BY VALUES 'E977335D83B8468E' using 'testdb';

Database link created.

-- Test database link
SQL> select * from dual@TEST_DB;
select * from dual@TEST_DB
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err],
[18446744073709551603], [], [], [], [], [], [], [], [], [], []


-- Drop
SQL> drop database link TEST_DB;

Database link dropped.



To workaround this problem you could extract user password using “dbms_metadata.get_ddl” package to generate DDL for database link.

-- Use this query to generate DDL
select dbms_metadata.get_ddl('DB_LINK',db_link,owner)||';'
from dba_db_links;

-- Create database link
SQL> CREATE DATABASE LINK "TEST_DB" CONNECT TO RMTUSER IDENTIFIED BY VALUES '05F57DA167252CDAD388952CAE91A1ED33' USING 'testdb';

Database link created.

-- Test database link
SQL> select * from dual@TEST_DB;

D
-
X


-- Drop
SQL> drop database link TEST_DB;

Database link dropped.


In this case, with differently hashed password, using IDENTIFIED BY VALUES is valid syntax.

Although you can create database link using this syntax have in mind that this is not documented as being valid syntax. So use valid and documented syntax when you can.

5 comments:

  1. Hi Marco,
    Nice post. I put a comment on my own post linking another method of creating DB links in other schemas by Shervin:

    http://oradbatips.blogspot.co.uk/2010/01/tip-84-create-private-db-link-for-user.html

    I think this one would require the same work-a-round as you discuss here but is nice as you don't need to create any supporting objects. The down side is you need SYS access.

    ReplyDelete
  2. Thanks Neil,

    this is nice tip from Shervin (saved) but still I will continue to use your method. I tend to avoid SYS schema on production databases as much as I can.

    Best Regards,
    Marko

    ReplyDelete
  3. Hello Marko,

    this is nice post greet idea . i put link of site which i blog for ones you can seen.

    ReplyDelete
  4. Many thanks. I will share it on my blog also...

    ReplyDelete
  5. please how did you get the hash password 05F57DA167252CDAD388952CAE91A1ED33

    ReplyDelete