7 Replies Latest reply: Oct 25, 2012 5:57 AM by sachinpawan RSS

    allow access to DATAGAURD

    sachinpawan
      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
          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
            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
              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
                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
                  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
                    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
                      ThanQ sir,

                      let me try!

                      thank you once again!