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 viewDictionary viewDictionary view
dba_cdb_rsrc_plan_directivesgv_$session_waitv_$memory_target_advice
dba_constraintsgv_$sort_segmentv_$nls_parameters
dba_data_filesgv_$spparameterv_$open_cursor
dba_db_linksgv_$sqlv_$osstat
dba_directoriesgv_$sysstatv_$parameter
dba_extentsgv_$temp_extent_poolv_$pdbs
dba_free_spacegv_$undostatv_$pga_target_advice
dba_indexesobj$v_$pgastat
dba_jobsrecyclebin$v_$pq_sysstat
dba_jobs_runningts$v_$process
dba_librariesuet$v_$px_session
dba_objectsuser$v_$recovery_file_dest
dba_pdb_historyv_$active_session_historyv_$resource
dba_profilesv_$archive_destv_$result_cache_statistics
dba_recyclebinv_$archived_logv_$rman_status
dba_role_privsv_$asm_clientv_$rowcache
dba_rolesv_$asm_diskv_$segment_statistics
dba_rollback_segsv_$asm_disk_statv_$segstat
dba_scheduler_jobsv_$asm_diskgroupv_$services
dba_scheduler_running_jobsv_$asm_diskgroup_statv_$sess_time_model
dba_segmentsv_$asm_operationv_$session
dba_sequencesv_$asm_templatev_$session_event
dba_synonymsv_$backup_set_detailsv_$session_wait
dba_sys_privsv_$cellv_$sesstat
dba_tab_columnsv_$con_sysstatv_$sga
dba_tab_privsv_$con_system_eventv_$sga_dynamic_components
dba_tablesv_$containersv_$sga_target_advice
dba_tablespacesv_$controlfilev_$sgainfo
dba_temp_filesv_$databasev_$sgastat
dba_temp_free_spacev_$datafilev_$spparameter
dba_undo_extentsv_$dataguard_configv_$sql
dba_usersv_$dataguard_statusv_$sql_bind_capture
dba_viewsv_$dbfilev_$sql_plan
dba_waitersv_$diag_alert_extv_$sql_shared_cursor
fet$v_$diag_infov_$sqlarea
file$v_$dispatcherv_$sqltext_with_newlines
gv_$archive_destv_$enqueue_statv_$standby_log
gv_$archive_dest_statusv_$enqueue_statisticsv_$statname
gv_$archived_logv_$event_namev_$sysmetric
gv_$dataguard_statsv_$filestatv_$sysstat
gv_$dataguard_statusv_$fixed_tablev_$system_event
gv_$instancev_$flash_recovery_area_usagev_$system_parameter
gv_$instance_cache_transferv_$flashback_database_logv_$tablespace
gv_$lockv_$instancev_$temp_extent_pool
gv_$managed_standbyv_$instance_cache_transferv_$temp_space_header
gv_$parameterv_$iostat_filev_$tempfile
gv_$pq_sysstatv_$librarycachev_$tempstat
gv_$px_sessionv_$lockv_$transaction
gv_$rman_configurationv_$logv_$undostat
gv_$rman_outputv_$log_history
gv_$sessionv_$logfile

Access to v_$active_session_history requires an Oracle Diagnostics Pack license.

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_grantee =>'TEST', p_privilege => 'SELECT');
      EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'UET$', p_grantee =>'TEST', p_privilege => 'SELECT');
      EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'FILE$', p_grantee =>'TEST', p_privilege => 'SELECT');
      EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'OBJ$', p_grantee =>'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_grantee =>'TEST', p_privilege => 'SELECT');
      EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'RECYCLEBIN$', p_grantee =>'TEST', p_privilege => 'SELECT');
      EXEC RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(p_obj_name=>'ALERTLOG', p_grantee =>'TEST', p_privilege => 'SELECT');
      
      Replace TEST with the name of the Foglight monitoring user.