ORA-29260: Network Error

Today I have been notified a problem with the following error

ORA-29260: network error: TNS:connection closed
ORA-06512: at “SYS.UTL_TCP”, line 28
ORA-06512: at “SYS.UTL_TCP”, line 257
ORA-06512: at line 12

There is a stored procedure that connects to a web service on another machine, when it run gives this error ORA-29260. In the other machine there is no database, so it should only be the process / program that makes the function of web service, after speaking with the systems people (in this case it is a windows server) shows that there is a windows service that is stopped, after startup the procedure in the database begins to work normally.

Not everything is the fault of the database…

HTH – Antonio NAVARRO

 

 

Advertisements

Get out of here!

Sometimes we need to take some cursor outside the library cache, for this we have the DBMS_SHARED_POOL.PURGE procedure, of course there are some very old tricks, like to create synonyms on one of the tables using the cursor or put comments in columns tables, but this invalidates all cursors on the table, not just one.

First we get address and hash_value in v$sqlarea view by SQL_ID

ANR> SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID =’118mxumpyfqbn’;

ADDRESS HASH_VALUE
________________ __________
0000000571707EE8 3957807476

execute the following command

ANR> exec DBMS_SHARED_POOL.PURGE (‘0000000571707EE8, 3957807476’, ‘C’);

If we perform the search in the v$sqlarea  again, this should return zero rows

ANR> SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID =’118mxumpyfqbn’;

0 rows selected

 
HTH – Antonio NAVARRO

 

One More Thing About ONLINE_INDEX_CLEAN

Using  DBMS_REPAIR.ONLINE_INDEX_CLEAN required two parameters

  • object_id
  • lock_for_wait

Object_id can be ALL_INDEX_ID, this is equivalent to zero value, or a > 0 value, this is the object_id for the index to  clean.

Lock_for_wait can be LOCK_WAIT (equivalent to 1) retry until get it but there is an internal retry limit or LOCK_NOWAIT (equal to 0) don’t retry.

The function return us TRUE or FALSE

TRUE : all indexes (if several) specified were cleaned up.
FALSE : one or more indexes could not be cleaned up. Some may have been cleaned if you used ALL_INDEX_ID.

HTH – Antonio NAVARRO

 

ORA-08104: This Index Object X Is Being Online Built …

Today I was rebuilding an index that has about 300 gigabytes (using parallel, nologging, online, etc..) And took a long when I’ve canceled it. After adjusting session-level parameters, I get the following error when executing it again;

PROD*ANR> ALTER INDEX siebel.S_EVT_ACT_P1 rebuild nologging online parallel 3
2 ;
ALTER INDEX siebel.S_EVT_ACT_P1 rebuild nologging online parallel 3
*
ERROR en línea 1:
ORA-08104: this index object 419709 is being online built or rebuilt

This is a known bug and that happens quite often in versions 10, has been a lock to be released we will use one of twice  scripts below;

From Oracle documentation

DBMS_REPAIR.ONLINE_INDEX_CLEAN (
object_id IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,
wait_for_lock IN BINARY_INTEGER DEFAULT LOCK_WAIT)
RETURN BOOLEAN;

One time, it can doesn’t work. In our case only one index (object_id = 419709)

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
OK BOOLEAN;
BEGIN
OK := FALSE;
OK := DBMS_REPAIR.ONLINE_INDEX_CLEAN (419709, DBMS_REPAIR.LOCK_NOWAIT);

IF OK THEN
DBMS_OUTPUT.PUT_LINE (‘ RESULT : OK ‘);
ELSE
DBMS_OUTPUT.PUT_LINE (‘ RESULT : KO ‘);
END IF;

END;
/

With pooling, until free the lock

DECLARE
OK BOOLEAN;
BEGIN
OK := FALSE ;
WHILE (OK = FALSE) loop
OK := DBMS_REPAIR.ONLINE_INDEX_CLEAN (419709, DBMS_REPAIR.LOCK_NOWAIT);
DBMS_LOCK.SLEEP (60); — Sleep for one minute
END LOOP;
END;
/

HTH – Antonio NAVARRO

Best Practices, Be Careful With Inner Commit

Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT orROLLBACK statement not issued directly by the application itself, then specify DISABLE COMMIT IN PROCEDURE clause to prevent procedures and stored functions called during your session from issuing these statements.

You can subsequently allow procedures and stored functions to issue COMMIT  and ROLLBACK statements in your session by issuing the following command;

ALTER SESSION ENABLE COMMIT IN PROCEDURE;

Or disable it using;

ALTER SESSION DISABLE COMMIT IN PROCEDURE;

HTH – Antonio NAVARRO

 

 

 

 

 

ORA-06512: at SYS.DBMS_SPACE And ORA-20000

Today I have been reported this error

ORA-12012: error on auto execute of job 140600
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at “SYS.PRVT_ADVISOR”, line 1624
ORA-06512: at “SYS.DBMS_ADVISOR”, line 186
ORA-06512: at “SYS.DBMS_SPACE”, line 1344
ORA-06512: at “SYS.DBMS_SPACE”, line 1560

Look at MOS I can see several bugs for this version but this system is a 24×7 and it isn’t easy to apply any patch. Because I don’t use DBMS_SPACE Advisor set it off by using this command;

execute dbms_scheduler.disable(‘AUTO_SPACE_ADVISOR_JOB’);

HTH – Antonio NAVARRO.

How To Disable/Enable Gather Stats Job

You can disable the automatic gather statistics with (this disable the job);

exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

We can enable the job with;

exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

We can see the status of the automatic collection of statistics with the following query

SELECT  STATE  

FROM DBA_SCHEDULER_JOBS 

WHERE JOB_NAME = ‘GATHER_STATS_JOB’;

 

HTH – Antonio NAVARRO