DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Source Database standby in waiting state.

  1. #1
    Join Date
    Apr 2005
    Location
    Saudi Arabia
    Posts
    13

    Source Database standby in waiting state.

    Sir i write a Databbse trigger on Before insert, i want to insert values in another databse on the network. My Code is below:

    CREATE OR REPLACE TRIGGER "REP_TEST" AFTER INSERT OR DELETE OR UPDATE OF EMPNO, ENAME ON TEST
    REFERENCING NEW AS N OLD AS O FOR EACH ROW
    BEGIN
    IF INSERTING THEN
    INSERT INTO TEST@MY_LINK(EMPNO,ENAME)
    VALUES (:N.EMPNO,:N.ENAME);
    ELSIF UPDATING THEN
    UPDATE TEST@MY_LINK
    SET EMPNO = :N.EMPNO,ENAME = :N.ENAME
    WHERE EMPNO = :O.EMPNO;
    ELSIF DELETING THEN
    DELETE FROM TEST@MY_LINK
    WHERE EMPNO = :O.EMPNO;
    END IF;
    END;

    Now when the target database (machine) unplug the network cable or restart then the source machine standby in waiting state, and after sometime the following error araise.

    ERROR at line 1:
    ORA-02068: following severe error from MY_LINK
    ORA-03113: end-of-file on communication channel
    ORA-06512: at "HANCO.REP_TEST", line 4
    ORA-04088: error during execution of trigger 'HANCO.REP_TEST'

    Now even the values is not inserting in the source database.
    Please help me regarding that.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    so you unplug the network cable and it doesnt work?

    What are you expecting to happen?

  3. #3
    Join Date
    Apr 2005
    Location
    Saudi Arabia
    Posts
    13

    At least value should be insert in saource database

    I want if the target databse did not find then source database should not be down. At leat values should be insert in the source database.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    well thats not going to happen with a before insert trigger is it as the statement fails and everything is rolled back (an autonomous transaction may work though)

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: At least value should be insert in saource database

    Originally posted by Sohail Nawaz
    I want if the target databse did not find then source database should not be down. At leat values should be insert in the source database.
    You need to understand the ACID properties of Oracle. Either the whole transaction works, or it fails and gets rolled back.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Using a trigger to update a remote table is not a good idea (now you know why), better look into materialized views or any other of the replication options offered by Oracle.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width