FAQ
Hi All,

I am trying to do DML activity on both local and remore DB. If the
remote db is down, I need to insert the values into temporary table on
the local db so that later when the remore db becomes available I can
push the records.

This is what I did but don't know how to handle the exception when
remote db is down.

SQL> desc db_lnk_test

Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 VARCHAR2(100)
C3 DATE

SQL> desc db_lnk_test_Q
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 VARCHAR2(100)
C3 DATE

SQL> create database link testa connect to MY_USER identified by
temp_123 using 'testa';


Database link created.

SQL> get p1
1 create or replace procedure db_lnk_test_proc(i_p1 number)
2 as
3 begin
4 insert into db_lnk_test values(i_p1,'From a1 db',sysdate);
5 begin
6 insert into db_lnk_test_at_testa values(i_p1,'From a1 db',sysdate);
7 exception when others then
8 insert into db_lnk_test_Q values(i_p1,'From a1 db',sysdate); --
If the remote db is down insert into QUEUE table
9 end;
10 commit;
11* end;
SQL>

SQL> exec db_lnk_test_proc(1);


PL/SQL procedure successfully completed.

SQL> exec db_lnk_test_proc(5);
BEGIN db_lnk_test_proc(5); END;


*
ERROR at line 1:

ORA-02067: transaction or savepoint rollback required
ORA-06512: at "MY_USER.DB_LNK_TEST_PROC", line 8
ORA-02055: distributed update operation failed; rollback required
ORA-02068: following severe error from TESTA
ORA-03113: end-of-file on communication channel
ORA-06512: at line 1

Thanks
Sami

Search Discussions

  • Anonymous at Feb 16, 2005 at 3:50 am
    May I suggest you consider using Advanced Queuing.=20

    Nick

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Sami Seerangan
    Sent: 16 February 2005 01:47
    To: oracle-l_at_freelists.org
    Subject: How to handle the exception when remote db is down

    Hi All,

    I am trying to do DML activity on both local and remore DB. If the
    remote db is down, I need to insert the values into temporary table on
    the local db so that later when the remore db becomes available I can
    push the records.

    This is what I did but don't know how to handle the exception when
    remote db is down.

    SQL> desc db_lnk_test

    Name Null? Type
    ----------------------------------------- -------- -----------------------=
    -----
    C1 NUMBER
    C2 VARCHAR2(100)
    C3 DATE

    SQL> desc db_lnk_test_Q
    Name Null? Type
    ----------------------------------------- -------- -----------------------=
    -----
    C1 NUMBER
    C2 VARCHAR2(100)
    C3 DATE=20

    =20
    =20
    SQL> create database link testa connect to MY_USER identified by
    temp_123 using 'testa';
    =20
    Database link created.

    SQL> get p1 =20
    1 create or replace procedure db_lnk_test_proc(i_p1 number)
    2 as
    3 begin
    4 insert into db_lnk_test values(i_p1,'From a1 db',sysdate);
    5 begin
    6 insert into db_lnk_test_at_testa values(i_p1,'From a1 db',sysdate);
    7 exception when others then
    8 insert into db_lnk_test_Q values(i_p1,'From a1 db',sysdate); --
    If the remote db is down insert into QUEUE table
    9 end;
    10 commit;
    11* end;
    SQL>=20

    SQL> exec db_lnk_test_proc(1);
    =20
    PL/SQL procedure successfully completed.

    SQL> exec db_lnk_test_proc(5);
    BEGIN db_lnk_test_proc(5); END;
    =20
    *
    ERROR at line 1:

    ORA-02067: transaction or savepoint rollback required
    ORA-06512: at "MY_USER.DB_LNK_TEST_PROC", line 8
    ORA-02055: distributed update operation failed; rollback required
    ORA-02068: following severe error from TESTA
    ORA-03113: end-of-file on communication channel
    ORA-06512: at line 1

    =20
    Thanks
    Sami

    --
    http://www.freelists.org/webpage/oracle-l

    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D
    This message is intended solely for the use of the individual or organisati=
    on to whom it is addressed. It may contain privileged or confidential info=
    rmation. If you have received this message in error, please notify the ori=
    ginator immediately. If you are not the intended recipient, you should not=
    use, copy, alter, or disclose the contents of this message. All informati=
    on or opinions expressed in this message and/or any attachments are those o=
    f the author and are not necessarily those of VarTecTelecom Europe Ltd or i=
    ts affiliates. VarTec Telecom Europe Ltd accepts no responsibility for loss=
    or damage arising from its use, including damage from virus.=20
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D

    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F at Feb 16, 2005 at 8:40 am
    Sami,

    The problem is that Oracle is still holding the update to the remote
    database open. Try the following:

    1 create or replace procedure db_lnk_test_proc(i_p1 number)
    2 as
    3 begin
    4 insert into db_lnk_test values(i_p1,'From a1 db',sysdate);

    Commit; <========== This makes sure your first insert works
    5 begin
    6 insert into db_lnk_test_at_testa values(i_p1,'From a1 db',sysdate);
    7 exception when others then

    Rollback; <===== Rollback the remote transactions
    8 insert into db_lnk_test_Q values(i_p1,'From a1 db',sysdate); --
    If the remote db is down insert into QUEUE table
    9 end;
    10 commit;
    11* end;

    You could also look at using savepoints. Nick had a good idea also about
    looking at Advanced Queueing.

    Tom

    -----Original Message-----
    From: Sami Seerangan
    Sent: Tuesday, February 15, 2005 8:47 PM
    To: oracle-l_at_freelists.org
    Subject: How to handle the exception when remote db is down

    Hi All,

    I am trying to do DML activity on both local and remore DB. If the
    remote db is down, I need to insert the values into temporary table on
    the local db so that later when the remore db becomes available I can
    push the records.

    This is what I did but don't know how to handle the exception when
    remote db is down.

    SQL> desc db_lnk_test

    Name Null? Type
    ----------------------------------------- --------
    ----------------------------
    C1 NUMBER
    C2 VARCHAR2(100)
    C3 DATE

    SQL> desc db_lnk_test_Q
    Name Null? Type
    ----------------------------------------- --------
    ----------------------------
    C1 NUMBER
    C2 VARCHAR2(100)
    C3 DATE

    SQL> create database link testa connect to MY_USER identified by
    temp_123 using 'testa';


    Database link created.

    SQL> get p1
    1 create or replace procedure db_lnk_test_proc(i_p1 number)
    2 as
    3 begin
    4 insert into db_lnk_test values(i_p1,'From a1 db',sysdate);
    5 begin
    6 insert into db_lnk_test_at_testa values(i_p1,'From a1 db',sysdate);
    7 exception when others then
    8 insert into db_lnk_test_Q values(i_p1,'From a1 db',sysdate); --
    If the remote db is down insert into QUEUE table
    9 end;
    10 commit;
    11* end;
    SQL>

    SQL> exec db_lnk_test_proc(1);


    PL/SQL procedure successfully completed.

    SQL> exec db_lnk_test_proc(5);
    BEGIN db_lnk_test_proc(5); END;


    *
    ERROR at line 1:

    ORA-02067: transaction or savepoint rollback required
    ORA-06512: at "MY_USER.DB_LNK_TEST_PROC", line 8
    ORA-02055: distributed update operation failed; rollback required
    ORA-02068: following severe error from TESTA
    ORA-03113: end-of-file on communication channel
    ORA-06512: at line 1

    Thanks
    Sami
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Sami Seerangan at Feb 16, 2005 at 9:42 am
    Thomas and Nick, Thanks for your responses.

    AQ is to propagate messages to and from non-Oracle messaging systems.
    Here I just interact with another oracle database thru distributed
    transaction. Why do I need AQ in this place?

    On Wed, 16 Feb 2005 08:37:23 -0500, Mercadante, Thomas F
    wrote:
    Sami,

    The problem is that Oracle is still holding the update to the remote
    database open. Try the following:

    You could also look at using savepoints. Nick had a good idea also about
    looking at Advanced Queueing.

    Tom


    -----Original Message-----
    Hi All,

    I am trying to do DML activity on both local and remore DB. If the
    remote db is down, I need to insert the values into temporary table on
    the local db so that later when the remore db becomes available I can
    push the records.
    --
    http://www.freelists.org/webpage/oracle-l
  • Anonymous at Feb 16, 2005 at 10:09 am
    AQ is messaging functionality for Non-Oracle databases AND Oracle database.
    The reason you need it in this case is to take away some admin/management a=
    ctivities.
    The way I read your requirements is that you want a local program to comple=
    te
    correctly regardless of the status of the remote database.

    It therefore makes sense to make the part of your program that deals with t=
    he remote
    database a two-phase one.

    EG, (A) insert into the local table.=20
    (B) enqueue an AQ message to be asynchronously picked up and pushed to the =
    remote DB.

    Using AQ for the remote task means simple admin tasks like handling queue e=
    xceptions and=20
    attempting retries can be taken care of.

    Nick
    -----Original Message-----
    From: Sami Seerangan
    Sent: 16 February 2005 14:40
    To: Mercadante, Thomas F; Nick Tilbury @ Northampton
    Cc: oracle-l_at_freelists.org
    Subject: Re: How to handle the exception when remote db is down

    Thomas and Nick, Thanks for your responses.

    AQ is to propagate messages to and from non-Oracle messaging systems.
    Here I just interact with another oracle database thru distributed
    transaction. Why do I need AQ in this place?

    On Wed, 16 Feb 2005 08:37:23 -0500, Mercadante, Thomas F
    wrote:
    Sami,
    =20
    The problem is that Oracle is still holding the update to the remote
    database open. Try the following:
    =20
    You could also look at using savepoints. Nick had a good idea also about
    looking at Advanced Queueing.
    =20
    Tom
    =20
    =20
    -----Original Message-----
    Hi All,
    =20
    I am trying to do DML activity on both local and remore DB. If the
    remote db is down, I need to insert the values into temporary table on
    the local db so that later when the remore db becomes available I can
    push the records.
    =20
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D
    This message is intended solely for the use of the individual or organisati=
    on to whom it is addressed. It may contain privileged or confidential info=
    rmation. If you have received this message in error, please notify the ori=
    ginator immediately. If you are not the intended recipient, you should not=
    use, copy, alter, or disclose the contents of this message. All informati=
    on or opinions expressed in this message and/or any attachments are those o=

    f the author and are not necessarily those of VarTecTelecom Europe Ltd or i=
    ts affiliates. VarTec Telecom Europe Ltd accepts no responsibility for loss=
    or damage arising from its use, including damage from virus.=20

    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
    =3D=3D=3D=3D=3D
  • Yechiel Adar at Feb 17, 2005 at 2:18 pm
    I think that Oracle Streams was created espacially for this.
    It will pick up your updates from the redo log and send them to be applied
    in the second database.

    Yechiel Adar
    Mehish Computer Services
    ----- Original Message -----
    From: "Sami Seerangan"
    To:
    Sent: Wednesday, February 16, 2005 3:47 AM
    Subject: How to handle the exception when remote db is down

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 15, '05 at 8:50p
activeFeb 17, '05 at 2:18p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase