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:
- 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.
- Grant the new role to the users you want to deny connections on the primary DB.
- Edit the trigger script Reporting_Role_Filter_Logon_Trigger by replacing RPTG with the substring you chose.
- (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.
- 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).