This discussion is archived
4 Replies Latest reply: Mar 21, 2012 10:55 PM by 621100 RSS

usage of dblink in active dataguard physical standby

user325480 Newbie
Currently Being Moderated
Hi All,

We are planning to run pl/sql procedures daily to extract data ( using UTL_FILE ) from physical standby tables and send it to EDW and once the procedure is successful it updates the master table designed for this extract process. Since physical standby is read only..we can not update this table. Is there any way like we can use dblink for accesseing master table present in primary db to update the table from procedure.

Please suggest if there are any other ways.

Regards,
prasad.
  • 1. Re: usage of dblink in active dataguard physical standby
    CKPT Guru
    Currently Being Moderated
    886061 wrote:
    Hi All,

    We are planning to run pl/sql procedures daily to extract data ( using UTL_FILE ) from physical standby tables and send it to EDW and once the procedure is successful it updates the master table designed for this extract process. Since physical standby is read only..we can not update this table. Is there any way like we can use dblink for accesseing master table present in primary db to update the table from procedure.

    Please suggest if there are any other ways.

    Regards,
    prasad
    Any operations that will modify or PL/SQL packages changes are not allowed in Read only mode. For that you have to bring in only read n write mode.
    When you run PL/SQL packages it will use all of the objects even compile, Which is not supported even on active dataguard.

    from the scenerio you have mentioned, I think LOGICAL STANDBY Database is more favourable than physical standby database.
    -- But i never went through with this scenario...
  • 2. Re: usage of dblink in active dataguard physical standby
    Shivananda Rao Guru
    Currently Being Moderated
    Hi Prasad,

    As I said yesterday, DML operations are not allowed on the standby database when opened in read only mode. They are not allowed on active dataguard as well.

    While the standby is open read-only, the following operations are disallowed

    Any Data Manipulation Language (DML) except for select statements
    Any Data Definition Language (DDL)
    Access of local sequences
    DMLs on local temporary tables


    You need to have the database opened in Read Write mode to perform the DML operations. Even the same is the case with the db links

    886061

    Handle: 886061
    Status Level: Newbie
    Registered: Sep 19, 2011
    Total Posts: 6
    Total Questions: 5 (4 unresolved)
    Please keep the forum clean by marking your questions as answered.

    Edited by: Shivananda Rao on Mar 20, 2012 11:35 AM
  • 3. Re: usage of dblink in active dataguard physical standby
    734641 Newbie
    Currently Being Moderated
    With Active Data Guard Physical Standby, you can make updates in the primary database via database link.
    See the white paper in technet: Active Data Guard 11g Best Practices (includes best practices for Redo Apply)
  • 4. Re: usage of dblink in active dataguard physical standby
    621100 Newbie
    Currently Being Moderated
    after the extract is over you can update the primary database using a dblink and that any ways will be replicated to your physical standby database.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points