Oracle World: 2/8/09

Monday, February 9, 2009

Resolve of DIM-00019: create service error

Problem Scenario
You have cold backup of oracle database directory structure along with oracle software. Now your Windows OS got corrupted. So you reinstall your windows operating system and you try to restore the database.

After installing software you set the ORACLE_SID and ORACLE_HOME environmental variable and whenever you create oracle service with oradim it fails with below message.
C:\>oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr
oduct\10.2.0\db_1\database\initorcl.ora'
Instance created.
DIM-00019: create service error
O/S-Error: (OS 2) The system cannot find the file specified.

Solution of the Problem
On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new oracle software in your windows machine without any database and then create oracle instance and service using oradim.

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.

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.

Monday, February 9, 2009

Blog Archive