This discussion is archived
9 Replies Latest reply: Feb 7, 2013 3:08 PM by 972260 RSS

DB Link to Physical Standby

972260 Newbie
Currently Being Moderated
How can we execute explain plan for queries connecting to Physical standby database over DB link.

explain plan for select * from test.test_table@stby;

Getting ORA-16000 when trying to that.

Tried to set transaction to read only.

Then tried to run explain plan, that gives error for ora-02047 cannot join the distributed

Any insight will be appreciated.

MM
  • 1. Re: DB Link to Physical Standby
    vlethakula Expert
    Currently Being Moderated
    In which mode your physical standby database is ?
    Mount or read only?
    I n mount mode you cannot query the database
  • 2. Re: DB Link to Physical Standby
    972260 Newbie
    Currently Being Moderated
    Its in read only mode. I can query the tables in physical standby but just can't run the explain plan for queries.

    MM
  • 3. Re: DB Link to Physical Standby
    sb92075 Guru
    Currently Being Moderated
    969257 wrote:
    Its in read only mode. I can query the tables in physical standby but just can't run the explain plan for queries.
    EXPLAIN PLAN UPDATES PLAN_TABLE table
  • 4. Re: DB Link to Physical Standby
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to
    PHYSICAL: ADG: Ora-16000 On Recursive Sql On Standby Database During Select Query [ID 1086514.1]
  • 5. Re: DB Link to Physical Standby
    karan Pro
    Currently Being Moderated
    You cannot use "explain plan" as it means inserting into "plan_table" table. So your standby is in read only mode, isnt it :)

    Activate the sql trace and execute the query

    Best would be convert your standby to snapshot standby :), Use it for any kind of testing like explain plan or even run that query and monitor, when you are satisfied come back to physical standby.

    Perform the following steps to convert a physical standby database into a snapshot standby database:

    Stop Redo Apply, if it is active.

    On an Oracle Real Applications Cluster (RAC) database, shut down all but one instance.

    Ensure that the database is mounted, but not open.

    Issue the following SQL statement to perform the conversion:

    SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

    The database is dismounted after conversion and must be restarted.

    Edited by: Karan on Feb 8, 2013 12:00 AM
  • 6. Re: DB Link to Physical Standby
    JohnWatson Guru
    Currently Being Moderated
    969257 wrote:
    How can we execute explain plan for queries connecting to Physical standby database over DB link.

    explain plan for select * from test.test_table@stby;

    Getting ORA-16000 when trying to that.

    Tried to set transaction to read only.

    Then tried to run explain plan, that gives error for ora-02047 cannot join the distributed

    Any insight will be appreciated.

    MM
    I haven't tersted this (I don't have a suitable environment to hand) but how about this:

    In the primary create a database link,
    create database link to_primary connect to system identified by manager using 'primary';

    Then generate your plans like this,
    explain plan into plan_table@to_primary for .....
    --
    John Watson
    http://skillbuilders.com
  • 7. Re: DB Link to Physical Standby
    972260 Newbie
    Currently Being Moderated
    Hi,

    Do you know, If we can have extra schemas/objects created while in snapshot mode which do not exist in primary.
    What will happen to those once we convert snapshot back to physical standby for recovery process.

    MM
  • 8. Re: DB Link to Physical Standby
    karan Pro
    Currently Being Moderated
    Suppose your snapshot standby was created at scn 100, and you test on that till 500, now once you go back to physical standby , your database will be flashbacked with the help of guaranteed restore point back to scn 100, so all your objects will get lost which were created during snapshot standby working.
  • 9. Re: DB Link to Physical Standby
    972260 Newbie
    Currently Being Moderated
    Thanks that should help !!

Legend

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