Starting with Oracle 12c you can grant the ‘SYSDG’ Administration Privilege to a User so they can perform Data Guard-related operations including using Data Guard Broker. It is no longer necessary to use SYS or SYSDBA for Data Guard Administration.
Just grant this Privilege to a User in SQL*PLUS:
SQL> grant sysdg to [USER];
Example:
SQL> grant sysdg to scott;
Since this is an Administration Privilege, you will have to copy the Oracle password file to all Physical Standby Databases again after the grant succeeds. This will ensure the user is able to access the Standby Databases when they are not open. You can use this query to verify and compare the current Password file Contents for SYSDG from primary and each mounted or opened standby:
SQL> select username from v$pwfile_users where sysdg='TRUE';
Once granted you can connect to the Data Guard Configuration through SQL*PLUS or DGMGRL. For example:
SQL> connect scott/[Password]@[Database_TNS] as sysdg
Or
DGMGRL> connect scott/[Password]@[Database_TNS]
The SYSDG Administration Privilege provides access to the following SQL operations along with all DGMGRL command line operations:
- STARTUP
- SHUTDOWN
- ALTER DATABASE
- ALTER SESSION
- ALTER SYSTEM
- CREATE RESTORE POINT (including GUARANTEED Restore Points)
- CREATE SESSION
- DROP RESTORE POINT (including GUARANTEED Restore Points)
- FLASHBACK DATABASE
- SELECT ANY DICTIONARY (DBA_ Views)
- SELECT
- X$ Tables
- V$ and GV$ Views
- APPQOSSYS.WLM_CLASSIFIER_PLAN
- DELETE
- APPQOSSYS.WLM_CLASSIFIER_PLAN
- EXECUTE
- SYS.DBMS_DRS