Very clear explanation of an error. Quick follow up, can you elaborate on why in SQL PLUS we get the hostdef problem? The backend? What is hostdef exactly anyhow?

Hello TOM,
While running the trigger i'm getting end-of-file communication. The same trigger is running in other users with out any problem. I couldn't find any problem in the user. I'm not in a position to send my production triggers and procedures.

Please reply me and expecting your mail.

regards,
sivababu

Hi Tom,

Our database (9iR2) is shutdown early a.m. every first monday for cold backup, whereas the application is not restarted afterwards. When the users try to use the application again, they received ora-03113 errors. The database is already up at the time.

I can use a script to restart the application every first monday, but is there other ways such as fail-over in Listener?

Hi Tom
We get the End of file communication error when compiling a stored proc on a remote database using VPN connection.

It doesn't happen when we do the same from the local network.

We have been using the DCD and update expire time as 5 mins in the sqlnet.ora, and that sovled the problem of disconneting so often.
But if we are running some sql or compiling a procedure, and if there is an error, then the sqlplus session is getting disconnected with the "end-of-file communication" error.

We are running oracle 9.2.0.1 EE on sun solaris and using Check point VPN. I am sure it is a network issue but just want to check with you, why it is happening only there is an error..

Thanks as always

Hi Tom,

I have procedure in scott/tiger@cbadmin and call cbtest2 db.  After cbtest2 rebounced, I will get the error the FIRST TIME to call the procedure.

It is quite strange since I didn't connect to that remote server when it is rebooted.  Any solution for this?  Thanks so much for your help.

----------------------------------------------------------
Here is the testing case:

SQL> create or replace procedure p1
  2  is 
  3  
  4  v1 varchar2(100);
  5  
  6  begin
  7  
  8    select sysdate into v1 from dual@cbtest2;
  9     dbms_output.put_line('v1 is '|| v1);
 10  
 11  end;
 12  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1
v1 is 16-DEC-04

PL/SQL procedure successfully completed.


-- After cbtest2 rebooted.
SQL> exec p1
BEGIN p1; END;

*
ERROR at line 1:
ORA-02068: following severe error from CBTEST2
ORA-03113: end-of-file on communication channel
ORA-06512: at "SCOTT.P1", line 8
ORA-06512: at line 1

-- exec again.
SQL> exec p1
v1 is 16-DEC-04

PL/SQL procedure successfully completed.



 

Hi Tom,

It is not real bug. You can repeat that error when remote server is rebooted.

Thanks so much for your help.

Hi Tom,

I just thought that you would have educated me. Some thing like “This is how Oracle works. You call remote server, establish the connection. When remote server is down, the connection is broken. When you call remote server, you get 3113 error. If you call again, Oracle reestablish the link.”

The testing is so generic that if it is the bug, Oracle would have fixed it a long time ago.

Thanks so much for your help.



Thanks so much for your help.

I also don't see how it can be called a bug - it seems intuative to me.

When p1 is created, it opens a DB link to database2 to verify the remote reference. The link stays open on databse2 (visible in v$session on database2).

Database2 is bounced.

P1 is executed in the same session as it was created in predictably it fails with 3113 (because it doesn't know the remote session is gone until it tries to communicate over the "open" link again).

The next execution of P1 re-opens the DB link (a new session is started in databse2).

What am I missing?

That is what I meant. Thanks so much for your help.

Hi Tom,

The reason I asked this question is that we have VB application which calls stored procedure in local database, which then calls remote database. VB application always gets 3113 error every time the remote database is bounced (Of course, users use same VB session).

I tried to kill the remote session before bounce the remote db, but killing the remote session would kill local session too. Why?

Are there any ways to avoid this error assuming we need to bounce remote db and uses want to use same application session?

Thanks so much for your help.

Here is the demo.  Thanks so much.

-- In session on cbadmin  ( local db)

SQL> connect scott/tiger@cbadmin
Connected.

SQL> create or replace procedure p1
  2      is  
  3  v1 varchar2(100); 
  4  begin   
  5       select sysdate into v1 from dual@cbtest2;  -- connect to remote db
  6  end; 
  7  /

Procedure created.

SQL> exec p1

PL/SQL procedure successfully completed.


-- In session two on cbtest2 (remote db)
SQL> connec system/password@cbtest2
Connected.
SQL> select sid, serial# from v$session where username='SCOTT';

       SID    SERIAL#
---------- ----------
        13      26199

-- Kill the remote session.
SQL> alter system kill session '13, 26199';

System altered.


-- In session one on cbadmin (local db)
-- The session is still alive after the remote session is killed.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

-- Running this procedure shows the session is killed.
SQL> exec p1
BEGIN p1; END;

*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-02063: preceding line from CBTEST2
ORA-06512: at "SCOTT.P1", line 5
ORA-06512: at line 1


-- Running this query returnes error.
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-01012: not logged on


SQL>  

I just wonder how to close database link?

I just wonder how to close database link?

Hi Tom,

I always follow this site for any kind of problems. I really appreciate that.

I have a small query. What are Oracle's recommendations in order to avoid 3113 ?

Thanks in advance.

Hi Tom

We are running a mapping through OWB in databse test it runs fine and inserts 4,00,000 records
Now I am running the same Mapping through production database it just hangs or sometimes adds 39,000 records or aother times 214 records and then does nothing.
In the alert file no error is there but in table wb_rt_errors shows ora-3113 error in runtime env.

Actually in this environment we are taking data directly from oltp environment in which the table has 4,00,000 records.
Other 2 mappings which inserts nearlu 30,000 records runs fine

Hi Tom,

i'm getting these error end-of-file on communication channel when i'm starting my database.

Actually i'd copied all the datafiles, redofile, controlfiles, dump files, Archive files, pfile on other machine(all the files of database). i have created the service for these database on other machine (were the database files are copied). but when i'm trying to connect the database by SYSDBA the error end-of-file on communication channel is getting.

Hi Tom,

i'm stuck with the error end-of-file on communication channel.

what i did i copied database from one machine and put all the files with the same structure and drives.
but when i'm trying to connect through sqlplus it is giving me the above error. i checked the services it's runing ||||

i tried to create fresh database on different drive but the error is same..

i'd create the password file through orapwd utility also but not of used.

Regards
Rahul Chaudhari

I met 03113 with a query, as you suggested i went to alert log for detail but due to my pure knowledge,i can not tell anything from the error in alert.log, it is beyond my understanding, could you please kindly elaborate more on this case?

Below is backgroud:

purpose:
for a given ID who has more than 1 different price, get all distinct price, and just randomly choose a value for any other columns

input:
ID PRICE SYSID
---------- ---------- ----------
10 1000 1
10 1000 2
20 2000 3
20 2500 4
30 1000 5
30 1000 6
30 1500 7
40 1000 8
40 1000 9
40 1500 10
40 1500 11
50 2000 12
50 2500 13
50 3000 14

output:
ID PRICE SYSID
---------- ---------- ----------
20 2000 3
20 2500 4
30 1000 6
30 1500 7
40 1000 9
40 1500 11
50 2000 12
50 2500 13
50 3000 14

sql for preparation:
create table PRICE
(
ID VARCHAR2(10),
PRICE NUMBER,
SYSID VARCHAR2(10)
);
insert into price (ID, PRICE, SYSID)values ('10', 1000, '1');
insert into price (ID, PRICE, SYSID)values ('10', 1000, '2');
insert into price (ID, PRICE, SYSID)values ('20', 2000, '3');
insert into price (ID, PRICE, SYSID)values ('20', 2500, '4');
insert into price (ID, PRICE, SYSID)values ('30', 1000, '5');
insert into price (ID, PRICE, SYSID)values ('30', 1000, '6');
insert into price (ID, PRICE, SYSID)values ('30', 1500, '7');
insert into price (ID, PRICE, SYSID)values ('40', 1000, '8');
insert into price (ID, PRICE, SYSID)values ('40', 1000, '9');
insert into price (ID, PRICE, SYSID)values ('40', 1500, '10');
insert into price (ID, PRICE, SYSID)values ('40', 1500, '11');
insert into price (ID, PRICE, SYSID)values ('50', 2000, '12');
insert into price (ID, PRICE, SYSID)values ('50', 2500, '13');
insert into price (ID, PRICE, SYSID)values ('50', 3000, '14');
commit;


my bad query who cause 03113:
with tmp as (select * from price where rowid in (select max(rowid) from price group by id, price)),
tmp1 as (select id from tmp group by id having count(distinct price)>=2)
select b.* from tmp1 a, tmp b where a.id=b.id;

my relatively good:) query which got expected result:
with tmp as (select * from price where rowid in (select max(rowid) from price group by id, price)),
tmp1 as (select id from tmp group by id having count(distinct price)>=2)
select b.*
from tmp1 a,
(select * from price where rowid in (select max(rowid) from price group by id, price)) b
where a.id=b.id order by b.id,b.price;


So i am thinking it is due to those 2 tmporary tables in 'with' are refered interchangely and joined together, which might result in endless loop or something else. But can not quite sure about how ORACLE handle this kind of 'with' query and why cause 03113 error. Thanks very much

ops$tkyte%ORA11GR2> with tmp as (select * from price where rowid in (select max(rowid) from price
  2  group by id, price)),
  3  tmp1 as (select id from tmp group by id having count(distinct price)>=2)
  4  select b.* from tmp1 a, tmp b where a.id=b.id;

ID              PRICE SYSID
---------- ---------- ----------
20               2000 3
20               2500 4
30               1000 6
30               1500 7
40               1000 9
40               1500 11
50               2000 12
50               2500 13
50               3000 14

9 rows selected.

ops$tkyte%ORA11GR2> with tmp as (select * from price where rowid in (select max(rowid) from price
  2  group by id, price)),
  3  tmp1 as (select id from tmp group by id having count(distinct price)>=2)
  4  select b.* from tmp1 a, tmp b where a.id=b.id;

ID              PRICE SYSID
---------- ---------- ----------
20               2000 3
20               2500 4
30               1000 6
30               1500 7
40               1000 9
40               1500 11
50               2000 12
50               2500 13
50               3000 14

9 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table price2 as select * from price order by to_number(sysid) desc;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with tmp as (select * from price2 where rowid in (select max(rowid) from price2
  2  group by id, price)),
  3  tmp1 as (select id from tmp group by id having count(distinct price)>=2)
  4  select b.* from tmp1 a, tmp b where a.id=b.id;

ID              PRICE SYSID
---------- ---------- ----------
50               3000 14
50               2500 13
50               2000 12
40               1500 10
40               1000 8
30               1500 7
30               1000 5
20               2500 4
20               2000 3

9 rows selected.

thx very much for your response.
good to hear you can not reproduce it, it prove my bad query at least can function and get what i want.
Actually either of your result meet my requirement.

exceptation: for a given ID who have more than 1 different prices, want to get id, associated different prices and sysid(do not care which value for this column is choose, juse choose one is ok)

Let me explain my query in a step by step basis
1> select * from price where rowid in (select max(rowid) from price group by id, price)
--a result without duplicated rows in terms of id+price

2> select id from tmp group by id having count(distinct price)>=2
--from above result, get the id list who have more than 1 different prices

3> select b.* from tmp1 a, tmp b where a.id=b.id;
-- join it above two results together and get my expected result