Oracle World: ORA

Showing posts with label ORA - Errors. Show all posts

Tuesday, March 31, 2009

ORA-00600 arguments: [keltnfy-ldmInit], [46], [1]

Problem Description
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []

If you try to create your database manually then also the command startup nomount fails with above error message.

Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.

Solution of the Problem
Step 01: Check permission on /etc/hosts


$ ls -l /etc/hosts
-rw-r--r--  1 root root 153 Nov 24  2007 /etc/hosts

Note that you need read permission of all users.

Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.


IP Address      fully qualified hostname    simple or short hostname     Alias

A simple example,


$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               database localhost.localdomain localhost

Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database

$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms

If you get the following message,
$ ping database
ping: unknown host database

then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,


127.0.0.1               database localhost.localdomain localhost

And then ping database again. Make sure you are able to ping your host.

Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.

$nslookup www.google.com
The forward and reverse lookup should succeed.

Step 05: Check nsswitch.conf


$ cat /etc/nsswitch.conf
hosts:      files dns

Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.

Step 06: Check resolv.conf


$ cat /etc/resolv.conf
nameserver 4.2.2.2

Make sure nameserver with DNS name is added there.

ORA-31655: no data or metadata objects selected for job

Problem Description
You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp.
In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc)

Your parameter file is like below.


userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" 
include =TABLE:"LIKE 'CV%' "

And you invoke expdp as
expdp parfile=d:\parfile.txt
from command line. But it fails with below message on my windows PC.


C:\>expdp parfile=d:\parfile.txt

Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01":  parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 14:53:50

As you expected 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION' will be exported but not a single table is exported and error ORA-31655: no data or metadata objects selected for job returned.

Cause of the Problem
This problem happens because of multiple INCLUDE options was set in expdp. Note that multiple INCLUDE parameter can be given in expdp but I recommend not to do that because if multiple filters(EXCLUDE/INCLUDE) are specified , an implicit AND operation is applied to them.

In this example data pump interprets


include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" 
include =TABLE:"LIKE 'CV%' "

as,
TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" AND TABLE:"LIKE 'CV%'"

As AND operation is applied, so all tables is filtered out (because no tables is there that starts with CV and between 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP') and no tables are exported.

A bit clear example I will show you.

Your parameter file is like below.


userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" 
include =TABLE:"LIKE 'CV%' "

And you invoke datapump


C:\>expdp parfile=d:\parfile.txt

Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:54

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01":  parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/STATISTICS/TABLE_STATISTICS
. . exported "SMILEBD"."ACCOUNT_GROUP"                   11.75 KB     132 rows
Master table "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SMILEBD.SYS_EXPORT_SCHEMA_01 is:
 G:\B.DMP
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:54:38

Note that here only one table is exported.
Because with first INCLUDE parameter,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"

first 3 tables are selected
and with second INCLUDE parameter
include =TABLE:"LIKE 'ACC%' "
both output are ANDed and only one table is selected that is ACCOUNT_GROUP (which starts with word ACC.)

ORA-12557: TNS:protocol adapter not loadable

Problem Description
In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,

With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009

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

ERROR:
ORA-12557: TNS:protocol adapter not loadable

Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.

Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.

C:\>sqlplus.exe shaik/a@192.168.100.160/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009

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

ERROR:
ORA-12557: TNS:protocol adapter not loadable

Connecting to old oracle database using new binaries are not supported in oracle and error will return.

Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.

Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe shaik/a@192.168.100.160/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Alternatively you can follow below.

Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.

Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.

ORA-04062: timestamp of procedure has been changed

Problem Description
In the database I have created one procedure named a as below.

create or replace procedure a(a number) as
begin
insert into t1 values(1);
commit;
end;
/

Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure.

SQL> exec shaik.a@orastdby_m(1);
PL/SQL procedure successfully completed.

Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2.

create or replace procedure a(a number) as
begin
insert into t1 values(2);
commit;
end;
/

Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subsequent execution goes ok without any error unless I change something inside the original procedure.

SQL> exec shaik.a@orastdby_m(1);
BEGIN shaik.a@orastdby_m(1); END;

*
ERROR at line 1:
ORA-04062: timestamp of procedure "SHAIK.A" has been changed
ORA-06512: at line 1

SQL> exec shaik.a@orastdby_m(1);

PL/SQL procedure successfully completed.

Problem Analysis
ORA-4062 indicates that TIMESTAMP or SIGNATURE of NAME has been changed.
When a local piece of PL/SQL references a remote package, function, or procedure, the local PL/SQL engine needs to know if the reference is still valid, or, if the remote procedure has changed.

The locally compiled PL/SQL code is dependent on the remote code. This dependency is tracked by two models either TIMESTAMPS OR SIGNATURES in oracle.

The initialization parameter REMOTE_DEPENDENCIES_MODE is responsible which method to choose. This parameter can be set to either TIMESTAMP or SIGNATURE and can be set at the instance level(By setting ALTER SYSTEM) or at the session level(By setting ALTER SESSION). This can also be set at the client side session.

Also oracle allows "runtime binding" by which client PLSQL allows to delay for the actual binding up of a reference to a SCHEMA.OBJECT.

REMOTE_DEPENDENCIES_MODE = Timestamp
If the dependency mode is set to TIMESTAMP, the local PL/SQL block can only execute the remote PL/SQL block if the timestamp on the remote procedure matches the timestamp stored in the locally compiled PL/SQL block. If the timestamps do not match, the local PL/SQL must be recompiled.

REMOTE_DEPENCIES_MODE = Signature
If the dependency mode is set to SIGNATURE, the local PL/SQL block can still execute the remote PL/SQL block if its "signature" is the same, even if the timestamp has changed.

The term "signature" basically means the interface (procedure name, parameter types or modes) is the same, even if the underlying implementation has changed.

The error "ORA-04062: timestamp of procedure has been changed" is reported if the local PL/SQL block cannot call the remote procedure, since the timestamp or signature has changed on the remote end. A local recompilation may be required to make the call.

In the case of server to server calls, the local PL/SQL block is implicitly recompiled on the next call after an ORA-4062 error. In the case of client tools to server calls, the client Form or Report usually needs to be recompiled explicitly.

Solution of the Problem
When client-side PL/SQL tools are used OR when server-side PL/SQL calls are used across database links , set REMOTE_DEPENDENCIES_MODE to SIGNATURE. This reduces the chances of the ORA-4062 errors and the need for unnecessary recompilations.
You can change in client side by,

SQL> alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE;
Session altered.

Now changing the definition of procedure "A" in the source database will not result ORA-4062 in the remote database.

ORA-02082: a loopback database link must have a connection qualifier

Problem Description
You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example.

SQL> create database link tiger;
create database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

SQL> drop database link tiger;
drop database link tiger
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

Cause of the Problem
This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database.

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM

We see the global name of the database is started with tiger(db_name) and the default db_domain is REGRESS.RDBMS.DEV.US.ORACLE.COM.

Now whenever we try to create a database link named TIGER (without any domain) it takes name as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM (original database link name+ default domain) which is equivalent to global_name of the database and thus error will occur because database link name must not be equal to the global database name.

Solution of the Problem
Two different solution of this problem.
1)Change the database link name so that it is different from global database name.

SQL> create database link tiger.net connect to user_name identified by password using 'TNS_NAME';

Database link created.

2)Change the global name of the database, create/drop database link and then back to global name of the database to the original name.
i)Error when creating database link name with same of global_name.

SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';
create database link tiger connect to user_name identified by password using 'TNS_NAME'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

ii)Change the global database name.
SQL> alter database rename global_name to test;
Database altered.

iii)Now dropping the database link tiger will work as now it (TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) is not same as the global name (TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM)

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> drop database link tiger;
Database link dropped.

iv)Also creating the database link with named Tiger (by default take as TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM) will work.
SQL> create database link tiger connect to user_name identified by password using 'TNS_NAME';

Database link created.

You can check it by,


SQL>  col host for a10
SQL>  set lines 140
SQL>  col owner for a10
SQL>  col db_link for a40
SQL> select * from dba_db_links where host='TNS_NAME';

OWNER      DB_LINK                                  USERNAME         HOST       CREATED
---------- ---------------------------------------- ---------------- ---------- ---------
MAXIMSG    TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM    USER_NAME        TNS_NAME   24-JAN-09
MAXIMSG    TIGER.NET                                USER_NAME        TNS_NAME   24-JAN-09

v)Back to the original global database name.
SQL> alter database rename global_name to tiger;

Database altered.

Monday, February 9, 2009

ORA-02082: a loopback database link must have a connection qualifier


SQL>  col host for a10
SQL>  set lines 140
SQL>  col owner for a10
SQL>  col db_link for a40
SQL> select * from dba_db_links where host='TNS_NAME';

OWNER      DB_LINK                                  USERNAME         HOST       CREATED
---------- ---------------------------------------- ---------------- ---------- ---------
MAXIMSG    TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM    USER_NAME        TNS_NAME   24-JAN-09
MAXIMSG    TIGER.NET                                USER_NAME        TNS_NAME   24-JAN-09

v)Back to the original global database name.
SQL> alter database rename global_name to tiger;

Database altered.

ORA-00845: MEMORY_TARGET not supported on this system

Problem Description
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
df -h
The output should be similar like


$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm

We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

ORA-02082: a loopback database link must have a connection qualifier


SQL>  col host for a10
SQL>  set lines 140
SQL>  col owner for a10
SQL>  col db_link for a40
SQL> select * from dba_db_links where host='TNS_NAME';

OWNER      DB_LINK                                  USERNAME         HOST       CREATED
---------- ---------------------------------------- ---------------- ---------- ---------
MAXIMSG    TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM    USER_NAME        TNS_NAME   24-JAN-09
MAXIMSG    TIGER.NET                                USER_NAME        TNS_NAME   24-JAN-09

v)Back to the original global database name.
SQL> alter database rename global_name to tiger;

Database altered.

ORA-00845: MEMORY_TARGET not supported on this system


$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm

ORA-04062: timestamp of procedure has been changed

Problem Description
In the database I have created one procedure named a as below.

create or replace procedure a(a number) as
begin
insert into t1 values(1);
commit;
end;
/

Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, maestro is the schema name and value 1 is the argument value though argument value is not used in the procedure.

SQL> exec maestro.a@orastdby_m(1);
PL/SQL procedure successfully completed.

Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2.

create or replace procedure a(a number) as
begin
insert into t1 values(2);
commit;
end;
/

Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subsequent execution goes ok without any error unless I change something inside the original procedure.

SQL> exec maestro.a@orastdby_m(1);
BEGIN maestro.a@orastdby_m(1); END;

*
ERROR at line 1:
ORA-04062: timestamp of procedure "MAESTRO.A" has been changed
ORA-06512: at line 1

SQL> exec maestro.a@orastdby_m(1);

PL/SQL procedure successfully completed.

Problem Analysis
ORA-4062 indicates that TIMESTAMP or SIGNATURE of NAME has been changed.
When a local piece of PL/SQL references a remote package, function, or procedure, the local PL/SQL engine needs to know if the reference is still valid, or, if the remote procedure has changed.

The locally compiled PL/SQL code is dependent on the remote code. This dependency is tracked by two models either TIMESTAMPS OR SIGNATURES in oracle.

The initialization parameter REMOTE_DEPENDENCIES_MODE is responsible which method to choose. This parameter can be set to either TIMESTAMP or SIGNATURE and can be set at the instance level(By setting ALTER SYSTEM) or at the session level(By setting ALTER SESSION). This can also be set at the client side session.

Also oracle allows "runtime binding" by which client PLSQL allows to delay for the actual binding up of a reference to a SCHEMA.OBJECT.

REMOTE_DEPENDENCIES_MODE = Timestamp
If the dependency mode is set to TIMESTAMP, the local PL/SQL block can only execute the remote PL/SQL block if the timestamp on the remote procedure matches the timestamp stored in the locally compiled PL/SQL block. If the timestamps do not match, the local PL/SQL must be recompiled.

REMOTE_DEPENCIES_MODE = Signature
If the dependency mode is set to SIGNATURE, the local PL/SQL block can still execute the remote PL/SQL block if its "signature" is the same, even if the timestamp has changed.

The term "signature" basically means the interface (procedure name, parameter types or modes) is the same, even if the underlying implementation has changed.

The error "ORA-04062: timestamp of procedure has been changed" is reported if the local PL/SQL block cannot call the remote procedure, since the timestamp or signature has changed on the remote end. A local recompilation may be required to make the call.

In the case of server to server calls, the local PL/SQL block is implicitly recompiled on the next call after an ORA-4062 error. In the case of client tools to server calls, the client Form or Report usually needs to be recompiled explicitly.

Solution of the Problem
When client-side PL/SQL tools are used OR when server-side PL/SQL calls are used across database links , set REMOTE_DEPENDENCIES_MODE to SIGNATURE. This reduces the chances of the ORA-4062 errors and the need for unnecessary recompilations.
You can change in client side by,

SQL> alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE;
Session altered.

Now changing the definition of procedure "A" in the source database will not result ORA-4062 in the remote database.

ORA-04062: timestamp of procedure has been changed

Problem Description
In the database I have created one procedure named a as below.

create or replace procedure a(a number) as
begin
insert into t1 values(1);
commit;
end;
/

Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, maestro is the schema name and value 1 is the argument value though argument value is not used in the procedure.

SQL> exec maestro.a@orastdby_m(1);
PL/SQL procedure successfully completed.

Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2.

create or replace procedure a(a number) as
begin
insert into t1 values(2);
commit;
end;
/

Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subsequent execution goes ok without any error unless I change something inside the original procedure.

SQL> exec maestro.a@orastdby_m(1);
BEGIN maestro.a@orastdby_m(1); END;

*
ERROR at line 1:
ORA-04062: timestamp of procedure "MAESTRO.A" has been changed
ORA-06512: at line 1

SQL> exec maestro.a@orastdby_m(1);

PL/SQL procedure successfully completed.

Problem Analysis
ORA-4062 indicates that TIMESTAMP or SIGNATURE of NAME has been changed.
When a local piece of PL/SQL references a remote package, function, or procedure, the local PL/SQL engine needs to know if the reference is still valid, or, if the remote procedure has changed.

The locally compiled PL/SQL code is dependent on the remote code. This dependency is tracked by two models either TIMESTAMPS OR SIGNATURES in oracle.

The initialization parameter REMOTE_DEPENDENCIES_MODE is responsible which method to choose. This parameter can be set to either TIMESTAMP or SIGNATURE and can be set at the instance level(By setting ALTER SYSTEM) or at the session level(By setting ALTER SESSION). This can also be set at the client side session.

Also oracle allows "runtime binding" by which client PLSQL allows to delay for the actual binding up of a reference to a SCHEMA.OBJECT.

REMOTE_DEPENDENCIES_MODE = Timestamp
If the dependency mode is set to TIMESTAMP, the local PL/SQL block can only execute the remote PL/SQL block if the timestamp on the remote procedure matches the timestamp stored in the locally compiled PL/SQL block. If the timestamps do not match, the local PL/SQL must be recompiled.

REMOTE_DEPENCIES_MODE = Signature
If the dependency mode is set to SIGNATURE, the local PL/SQL block can still execute the remote PL/SQL block if its "signature" is the same, even if the timestamp has changed.

The term "signature" basically means the interface (procedure name, parameter types or modes) is the same, even if the underlying implementation has changed.

The error "ORA-04062: timestamp of procedure has been changed" is reported if the local PL/SQL block cannot call the remote procedure, since the timestamp or signature has changed on the remote end. A local recompilation may be required to make the call.

In the case of server to server calls, the local PL/SQL block is implicitly recompiled on the next call after an ORA-4062 error. In the case of client tools to server calls, the client Form or Report usually needs to be recompiled explicitly.

Solution of the Problem
When client-side PL/SQL tools are used OR when server-side PL/SQL calls are used across database links , set REMOTE_DEPENDENCIES_MODE to SIGNATURE. This reduces the chances of the ORA-4062 errors and the need for unnecessary recompilations.
You can change in client side by,

SQL> alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE;
Session altered.

Now changing the definition of procedure "A" in the source database will not result ORA-4062 in the remote database.

Tuesday, February 3, 2009

Expdp fails with ORA-31693, ORA-06502, ORA-31605

Problem Description
$expdp parfile=pfile_maxim_history_sel.par directory=d

Export: Release 10.2.0.1.0 - 64bit Production on Monday, 05 January, 2009 17:23:52

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

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=pfile_maxim_history_sel.par directory=d
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 964 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MAXIM"."HISTORY" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/db1/oracle/dump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 17:24:11

My parameter file was below,
$ cat pfile_maxim_history_sel.par
tables=maxim.history
query=maxim.history:'"where cdate between '01-JAN-09' and '03-JAN-09'"'

Cause of the Problem
In the data pump export operation ORA-31693, ORA-06502, ORA-31605, LPX-314 happened due to incorrect usage of single or double quotes for the QUERY parameter. So in the query parameter within the parameter files quote is given incorrect.
We see there is single quote(') surrounding double quote(") in the query parameter.

Solution of the Problem
Just omit the single quote (') from the query parameter in the expdp.
$ cat pfile_maximsg_history_sel.par
tables=maxim.history
query=maxim.history:"where cdate between '01-JAN-09' and '03-JAN-09'"

And now run your datapump export operation.

Expdp fails with ORA-31693, ORA-06502, ORA-31605

ORA-01779: cannot modify a column which maps to a non key-preserved table

Problem Description
I wanted to update a table column based on another table data but it fails with error
ORA-01779: cannot modify a column which maps to a non key-preserved table.


SQL> update
        (select t1.code col1, t2.code col2 from table_1 t1
         JOIN table_2 t2 ON t1.id=t2.id)
         set col1=col2;
     set col1=col2
         *
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Cause, Description and Solution of the Problem
Let's look both of table's data.


SQL> select * from table_1;

       ID CODE
---------- --------------------
        1 First Row
        2 Rows to be updated

SQL> select * from table_2;

       ID CODE
---------- --------------------
        2 Second Row

From the above update query, the select part return following rows,


SQL> select t1.code col1, t2.code col2 from table_1 t1
            JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row

In this case col1 value will be replaced by col2 value. Now if source table's id column is not unique then see the below case:


SQL> insert into table_2 values(2,'Test Row');

1 row created.

SQL> select t1.code col1, t2.code col2 from table_1 t1
               JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row
Rows to be updated   Test Row

Now the col1 data "Rows to be updated" will be replaced by which value of col2? "Second Row" or "Test Row" which is ambiguous. So in order to update col1 in this way the id of the table containing col2 must be unique so that ambiguous situation will not occur.

SQL> rollback;

Rollback complete.

Adding an unique constraint will solve the problem.
SQL> alter table table_2 add constraint table_2_uk unique(id);

Table altered.

Now we have unique on id. So no ambiguous situation will occur.


SQL> select t1.code col1, t2.code col2 from table_1 t1
                JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row

So update will work fine.


SQL> update
            (select t1.code col1, t2.code col2 from table_1 t1
             JOIN table_2 t2 ON t1.id=t2.id)
             set col1=col2;

1 row updated.

SQL> select * from table_1;

       ID CODE
---------- --------------------
        1 First Row
        2 Second Row

ORA-01779: cannot modify a column which maps to a non key-preserved table

Problem Description
I wanted to update a table column based on another table data but it fails with error
ORA-01779: cannot modify a column which maps to a non key-preserved table.


SQL> update
        (select t1.code col1, t2.code col2 from table_1 t1
         JOIN table_2 t2 ON t1.id=t2.id)
         set col1=col2;
     set col1=col2
         *
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Cause, Description and Solution of the Problem
Let's look both of table's data.


SQL> select * from table_1;

       ID CODE
---------- --------------------
        1 First Row
        2 Rows to be updated

SQL> select * from table_2;

       ID CODE
---------- --------------------
        2 Second Row

From the above update query, the select part return following rows,


SQL> select t1.code col1, t2.code col2 from table_1 t1
            JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row

In this case col1 value will be replaced by col2 value. Now if source table's id column is not unique then see the below case:


SQL> insert into table_2 values(2,'Test Row');

1 row created.

SQL> select t1.code col1, t2.code col2 from table_1 t1
               JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row
Rows to be updated   Test Row


SQL> select t1.code col1, t2.code col2 from table_1 t1
                JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row

So update will work fine.


SQL> update
            (select t1.code col1, t2.code col2 from table_1 t1
             JOIN table_2 t2 ON t1.id=t2.id)
             set col1=col2;

1 row updated.

SQL> select * from table_1;

       ID CODE
---------- --------------------
        1 First Row
        2 Second Row

ORA-12838: cannot read/modify an object after modifying it in parallel

Problem Description
SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 4 from STATION_RATE_DUMMY;

7561 rows created.

SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY;
INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Cause of the Problem
A table is modified in parallel or with direct path load in a transaction. Now within the same transaction if an attempt was made to read or modification statements on a table then ORA-12838 will occur. In oracle within same transaction table is modified with direct path load or parallel and then access of it is not permitted.

Solution of the Problem
Break up the transaction into two or rewrite the transaction. You can break the transaction into two simply after doing a commit after direct path load or parallel modification of the table.

SQL> commit;

Commit complete.

SQL> INSERT /*+APPEND NOLOGGING */ INTO STATION_RATE(ID,COUNTRY,CODE,PURCHASE_RATE,STATION_ID) SELECT
ID.NEXTVAL, COUNTRY,CODE,PURCHASE_RATE, 5 from STATION_RATE_DUMMY;

7561 rows created.

As now data is loaded direct path load so we can't read data from the table unless we do a commit.

SQL> select count(*) from station_rate;
select count(*) from station_rate
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> commit;

Commit complete.

SQL> select count(*) from station_rate;

COUNT(*)
----------
53228

ORA-12838: cannot read/modify an object after modifying it in parallel

Thursday, January 29, 2009

ORA-02070: database does not support in this context

Problem Description
I like to differentiate between columns of table HISTORY from two databases using database link as below and it resulted error ORA-02070.
SQL> select * from cols where table_name='HISTORY' minus select * from cols@tiger1.net where table_name='HISTORY';
select * from cols where table_name='HISTORY' minus select * from cols@tiger1.net where table_name='HISTORY'
*
ERROR at line 1:
ORA-02070: database TIGER1.NET does not support operator NLS_CHARSET_DECL_LEN
in this context

I found that the cols contain LONG column and hence result above error. If I only query long datatype data_default then same error return with different error message.

SQL> select data_default from cols minus select data_default from cols@tiger1.net;
select data_default from cols minus select data_default from cols@tiger1.net
*
ERROR at line 1:
ORA-02070: database does not support in this context

Experiment and cause of the Error
The reason behind the error is the long datatype of data_default column. Wheenever you go to access long datatype column of cols then above error returns. However for experiment I created LONG datatype column table but did not get above error. I conclude that only SYSTEM defined table/view/synonym throw above error and user defined table/view/synonym throw ORA-00997.

For experiment,
1)Create table t with only long column in both database and then try to use minus operation using database link.

SQL> create table t(a long);

Table created.

SQL> select * from t minus select * from t@tiger1.net;
select * from t minus select * from t@tiger1.net
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
ORA-02063: preceding line from TIGER1.NET

So in order to avoid above error just don't do any operation over LONG column through database link.

ORA-02070: database does not support in this context

ORA-39095: Dump file space has been exhausted

Problem Description
I ran my datapump export operation with the combination of multiple dumpfile arguments and filesize parameter but it fails with ORA-39095 as below.

maximsg@TIGER> $expdp maximsg/a dumpfile=d:parti1.dmp,e:parti2.dmp,c:resit%U.dmp filesize=188417 tablespaces=users

Export: Release 10.2.0.1.0 - Production on Thursday, 01 January, 2009 12:23:30

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MAXIMSG"."SYS_EXPORT_TABLESPACE_05": maximsg/******** dumpfile=d:parti1.dmp,e:parti2.dmp,c:resit%U.dmp filesize=188417 tablespace
s=users
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 188416 bytes
Job "MAXIMSG"."SYS_EXPORT_TABLESPACE_05" stopped due to fatal error at 12:23:47

Just a variant of the above is below.
>expdp maximsg/a@tiger tablespaces=users filesize=200K directory=d dumpfile=b.dmp

Export: Release 10.2.0.1.0 - Production on Saturday, 03 January, 2009 22:29:34

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MAXIMSG"."SYS_EXPORT_TABLESPACE_08": maximsg/********@tiger tablespaces=users filesize=200K directory=d dumpfile=b.d
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 208896 bytes
Job "MAXIMSG"."SYS_EXPORT_TABLESPACE_08" stopped due to fatal error at 10:30:17

Note that both in first and second case same errors are return but they show various bytes in order of unable to allocate. In first case Unable to allocate 188416 bytes and in second case Unable to allocate 208896 bytes.

Cause of the Error
They are many cause for which ORA-39095 is fired.
1)If the summation of dumpfile sizes that is specified in the datapump parameters is less than total dumpfile size that produces then ORA-39095 returns. Like in the second case the filesize is specified as 200K and only one dumpfile is specified. So maximum dumpfile size that can be produced is no more than 200K. If dumpfile to be produced is greater than 100K then above error will return.

Note that %U specification for the dump file can expand up to 99 files.
If 99 files have been generated before the export has completed, it will again return the ORA-39095 error. If in the second case %U is specified then maximum dumpfile size can be 99*200K=19800K. If dumpfile to be produced is greater than 19800K then error will return.

2)Like in the first case the size of filesize is too small and hence the error returns. filesize is defined about 184K(188407 bytes). And in the error message it shows unable to allocate 184K(188407 bytes). The solutions is and extra block is needed. As with 4k (with standard block size 8k) a block is generated so 188K filesize will solve the problem.

maximsg@TIGER> $expdp maximsg/a dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=188K tablespaces=users

Export: Release 10.2.0.1.0 - Production on Thursday, 01 January, 2009 12:25:09

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MAXIMSG"."SYS_EXPORT_TABLESPACE_06": maximsg/******** dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=188K tablespaces=use
rs
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CR_2"."OUT_CDR_TEST" 310.9 KB 2166 rows
. . exported "CR_2"."OUT_CDR" 484.4 KB 2606 rows
. . exported "CR_2"."USER_IP" 126 KB 3403 rows
. . exported "CR_2"."OUT_CDR_TEMP" 33.72 KB 361 rows
. . exported "CR_2"."USERS" 42.57 KB 230 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.851 KB 15 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "MAXIMSG"."SYS_EXPORT_TABLESPACE_06" successfully loaded/unloaded
******************************************************************************
Dump file set for MAXIMSG.SYS_EXPORT_TABLESPACE_06 is:
D:\PART1.DMP
E:\PART2.DMP
C:\REST01.DMP
C:\REST02.DMP
C:\REST03.DMP
C:\REST04.DMP
C:\REST05.DMP
Job "MAXIMSG"."SYS_EXPORT_TABLESPACE_06" successfully completed at 12:25:32

Solution of the Error
1)Increase the total dumpfile number so that dump produced by export operation is lower than total dumpfile sizes that is specified in the expdp.
With the 'wildcard' specification (%U) the dump file can expand up to 99 files.

2)Use a bigger filesize value.

3)Another option is to use,
dumpfile=dmpdir1:part1_%U.dmp, dmpdir2:part2_%U.dmp, dmpdir3:part3_%U.dmp

Tuesday, March 31, 2009

Monday, February 9, 2009

Tuesday, February 3, 2009

Thursday, January 29, 2009

Blog Archive