How to confine a set of users to the Active Data Guard DB only

Active Data Guard is a new (paid) feature of Oracle 11g where a physical standby database can be open for read-only access while Redo Apply is simultaneously active.

With the objective of relieving the production database from a group of users which run reports only, you open the standby database for reporting purposes.

Despite any and all communication attempts, however, there will always be users which will fail to change their configs to connect to the newly available database.

How can you ensure these users will only connect to the standby, thereby effectively making room in production to the transactional users?

With a combination of at least one role and a database logon trigger, this is easily achievable in a few steps:

  1. Create a role whose name contains a specific substring that no other role in the database has. I chose RPTG, and created the role as MYAPPLICATION_RPTG_ROLE. No specific privileges must be granted to the role.
  2. Grant the new role to the users you want to deny connections on the primary DB.
  3. Edit the trigger script Reporting_Role_Filter_Logon_Trigger by replacing RPTG with the substring you chose.
  4. (Optional) This trigger features a list of users that you DON’T want to deny connections even though they may have that role granted to them. (SYS,SYSTEM,app owner,…) I also recommend to add any accounts with high new connection rates there to avoid the overhead of running the trigger’s two selects too frequently.
  5. Compile the trigger.

Done! Any users that have the role granted, except for those listed in the trigger’s exception list, will get an error when connecting to the primary database.

Bonus: this trigger is switchover/failover aware, so you don’t need to bother – if the standby becomes primary, users will be denied over there (and vice-versa).

This entry was posted in Oracle, Performance and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s