This discussion is archived
7 Replies Latest reply: Oct 25, 2012 3:57 AM by sachinpawan RSS

allow access to DATAGAURD

sachinpawan Newbie
Currently Being Moderated
hi all,

how to block access to production and allow access to DG for users?

thanks in advance

pa1
  • 1. Re: allow access to DATAGAURD
    teits Journeyer
    Currently Being Moderated
    sachinpawan wrote:
    hi all,

    how to block access to production and allow access to DG for users?

    thanks in advance

    pa1
    i can think of two option now...
    1. tnsnames.ora in client side should point to standby DB.(i like to believe your DG is in active real-time apply and standby DB open in readonly)

    2. check: http://uhesse.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard
    when you are creating trigger, use standby instead of primary e.g
    ...
    select database_role into v_role from v$database;
     if v_role != 'PHYSICAL'   --this will prevent connection to primary DB. 
    hth
    Tobi
  • 2. Re: allow access to DATAGAURD
    Sunny kichloo Expert
    Currently Being Moderated
    You want to prevent users from accessing Production Database.Correct me if i am wrong??

    That can be done By revoking privileges .
  • 3. Re: allow access to DATAGAURD
    sachinpawan Newbie
    Currently Being Moderated
    thank you for kind response!

    Yes i want users shouldnt access production, at the same time users could access from DG!
    thankyou!
  • 4. Re: allow access to DATAGAURD
    mseberg Guru
    Currently Being Moderated
    Hello;

    I have not tested but in theory RESTRICT mode might work on production and still allow redo to be applied on the Standby which is not in RESTRICT.

    Best Regards

    mseberg
  • 5. Re: allow access to DATAGAURD
    sachinpawan Newbie
    Currently Being Moderated
    thank you sir!

    if its in RESTRICT MODE, it will block all users! right?
    i want some users should be blocked!

    thankyou!
  • 6. Re: allow access to DATAGAURD
    mseberg Guru
    Currently Being Moderated
    Yes it would block any User who did not have RESTRICT ACCESS granted to them. So generally SYS and SYSTEM would have this.

    Your tnsnames will need an additional setting too.

    Example
    RECOVER2=
      (DESCRIPTION=
        (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=myhost)
          (PORT=1521)
        )
        (CONNECT_DATA=
          (SERVICE_NAME=RECOVER2)
          (UR=A)
        )
      )
    The UR=A is need for restrict

    Test
    SQL> startup restrict
    ORACLE instance started.
    Total System Global Area  830930944 bytes
    Fixed Size                  2231048 bytes
    Variable Size             503317752 bytes
    Database Buffers          318767104 bytes
    Redo Buffers                6615040 bytes
    Database mounted.
    Database opened.
    SQL> alter system switch logfile;
    SQL> /
    SQL> 
    Results - Worked!




    Best Regards

    mseberg

    Edited by: mseberg on Oct 25, 2012 5:09 AM

    Edited by: mseberg on Oct 25, 2012 5:38 AM
  • 7. Re: allow access to DATAGAURD
    sachinpawan Newbie
    Currently Being Moderated
    ThanQ sir,

    let me try!

    thank you once again!

Legend

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