Prerequisites for monitoring Oracle Database
This section provides information about the prerequisites for monitoring Oracle instances.
Create a Foglight User
A Foglight user needs to be created using the following syntax:
- CREATE USER <username> IDENTIFIED BY <password> [CONTAINER=ALL];
- GRANT CONNECT TO <username> [CONTAINER=ALL];
When the feature multitenant container database (CDB) architecture is enabled, the customer should create the user with “container=all”.
Permissions for monitoring Oracle database
Following are the permissions required to monitor Oracle database. The installation wizard grants these permissions as part of the installation process:
For Oracle 12c and above, replace all the dba_* dictionary views with the cdb prefix (cdb_*)
Oracle views requiring Select permission:
| Dictionary view | Dictionary view | Dictionary view |
|---|---|---|
| dba_constraints | gv_$session | v_$logfile |
| dba_data_files | gv_$session_wait | v_$open_cursor |
| dba_db_links | gv_$sort_segment | v_$osstat |
| dba_directories | gv_$spparameter | v_$parameter |
| dba_extents | gv_$sql | v_$pgastat |
| dba_free_space | gv_$sysstat | v_$pq_sysstat |
| dba_indexes | gv_$temp_extent_pool | v_$process |
| dba_jobs | gv_$undostat | v_$recovery_file_dest |
| dba_jobs_running | obj$ | v_$resource |
| dba_libraries | recyclebin$ | v_$result_cache_statistics |
| dba_objects | ts$ | v_$rman_status |
| dba_profiles | uet$ | v_$rowcache |
| dba_role_privs | user$ | v_$segstat |
| dba_roles | v_$archive_dest | v_$segment_statistics |
| dba_rollback_segs | v_$archived_log | v_$sess_time_model |
| dba_scheduler_jobs | v_$asm_disk | v_$session |
| dba_scheduler_running_jobs | v_$asm_disk_stat | v_$session_wait |
| dba_segments | v_$asm_diskgroup | v_$sesstat |
| dba_sequences | v_$asm_diskgroup | v_$sga |
| dba_sequences | v_$asm_diskgroup_stat | v_$sga_dynamic_components |
| dba_synonyms | v_$asm_operation | v_$sgainfo |
| dba_sys_privs | v_$asm_template | v_$sgastat |
| dba_tab_columns | v_$cell | v_$spparameter |
| dba_tab_privs | v_$controlfile | v_$sql |
| dba_tables | v_$database | v_$sql_plan |
| dba_tablespaces | v_$datafile | v_$sqlarea |
| dba_temp_files | v_$dataguard_status | v_$sqltext_with_newlines |
| dba_temp_free_space | v_$dbfile | v_$standby_log |
| dba_undo_extents | v_$dispatcher | v_$statname |
| dba_users | v_$enqueue_stat | v_$sysmetric |
| dba_views | v_$enqueue_statistics | v_$sysstat |
| dba_recyclebin | v_$event_name | v_$system_event |
| fet$ | v_$filestat | v_$system_parameter |
| file$ | v_$fixed_table | v_$tablespace |
| gv_$archive_dest | v_$flash_recovery_area_usage | v_$temp_extent_pool |
| gv_$archived_log | v_$instance | v_$temp_space_header |
| gv_$instance | v_$instance_cache_transfer | v_$tempfile |
| gv_$instance_cache_transfer | v_$iostat_file | v_$tempstat |
| gv_$lock | v_$librarycache | v_$transaction |
| gv_$pq_sysstat | v_$lock | v_$memory_target_advice |
| gv_$rman_configuration | v_$log | v_$pga_target_advice |
| gv_$rman_output | v_$log_history | v_$sga_target_advice |
| v_$undostat | v_$sql_shared_cursor | |
| gv_$archive_dest_status | v_$flashback_database_log | v_$backup_set_details |
| gv_$dataguard_stats | v_$dataguard_config | v_$session_event |
| gv_$dataguard_status | v_$services |
Additional configurations for Amazon RDS for Oracle instances
There are two user credential options for monitoring Amazon RDS for Oracle instances:
- Master predefined user that comes as part of Amazon RDS for Oracle instance Or
- Separate user who has either of the following:
- SELECT_CATALOG_ROLE or SELECT ANY TABLE system privilege
- Grant additional permissions by manually executing the following commands from the master user:
- EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'FET$',p_grante e =>'TEST', p_privilege => 'SELECT'); - EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'UET$' p_grante e =>'TEST', p_privilege => 'SELECT'); - EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'FILE$' p_grant ee =>'TEST', p_privilege => 'SELECT'); - EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'OBJ$',p_grante e =>'TEST', p_privilege => 'SELECT'); - EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'TS$' p_grantee =>'TEST', p_privilege => 'SELECT'); - EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'USER$',p_grant ee =>'TEST', p_privilege => 'SELECT'); - EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'RECYCLEBIN$',p_grantee =>'TEST', p_privilege => 'SELECT');
