Oracle Database

Verify Statistics Collection

-- Check STATISTICS_LEVEL parameter
SELECT name, value FROM v$parameter WHERE name = 'statistics_level';

-- Should be TYPICAL or ALL (not BASIC)
-- If needed, change it:
ALTER SYSTEM SET statistics_level = TYPICAL SCOPE=BOTH;

Verify AWR Settings (Requires Diagnostics Pack License)

-- Check AWR snapshot interval and retention
SELECT snap_interval, retention FROM dba_hist_wr_control;

-- Modify if needed (e.g., 30 minute snapshots, 30 day retention)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  interval => 30,
  retention => 43200
);

Enable SQL Trace and Tuning

-- Check SQL tracing parameters
SELECT name, value FROM v$parameter
WHERE name IN ('timed_statistics', 'sql_trace');

-- Enable timed statistics
ALTER SYSTEM SET timed_statistics = TRUE SCOPE=BOTH;

Create User and Grant Basic Privileges

-- Create monitoring user
CREATE USER db24x7_monitor IDENTIFIED BY "YourSecurePassword123!";

-- Grant connection privileges
GRANT CREATE SESSION TO db24x7_monitor;
GRANT SELECT_CATALOG_ROLE TO db24x7_monitor;

-- Grant SELECT on performance views
GRANT SELECT ANY DICTIONARY TO db24x7_monitor;

Grant Required System View Access

-- V$ views
GRANT SELECT ON v_$session TO db24x7_monitor;
GRANT SELECT ON v_$sql TO db24x7_monitor;
GRANT SELECT ON v_$sqlarea TO db24x7_monitor;
GRANT SELECT ON v_$sql_plan TO db24x7_monitor;
GRANT SELECT ON v_$sqlstats TO db24x7_monitor;
GRANT SELECT ON v_$database TO db24x7_monitor;
GRANT SELECT ON v_$instance TO db24x7_monitor;
GRANT SELECT ON v_$system_event TO db24x7_monitor;
GRANT SELECT ON v_$system_wait_class TO db24x7_monitor;
GRANT SELECT ON v_$sysstat TO db24x7_monitor;
GRANT SELECT ON v_$osstat TO db24x7_monitor;
GRANT SELECT ON v_$process TO db24x7_monitor;
GRANT SELECT ON v_$session_wait TO db24x7_monitor;
GRANT SELECT ON v_$session_wait_history TO db24x7_monitor;
GRANT SELECT ON v_$active_session_history TO db24x7_monitor;

Grant AWR Access (Requires Diagnostics Pack)

-- DBA_HIST views for AWR data
GRANT SELECT ON dba_hist_snapshot TO db24x7_monitor;
GRANT SELECT ON dba_hist_sqlstat TO db24x7_monitor;
GRANT SELECT ON dba_hist_sqltext TO db24x7_monitor;
GRANT SELECT ON dba_hist_sql_plan TO db24x7_monitor;
GRANT SELECT ON dba_hist_active_sess_history TO db24x7_monitor;
GRANT SELECT ON dba_hist_sysstat TO db24x7_monitor;
GRANT SELECT ON dba_hist_system_event TO db24x7_monitor;
GRANT SELECT ON dba_hist_wr_control TO db24x7_monitor;

-- Execute on DBMS packages
GRANT EXECUTE ON dbms_workload_repository TO db24x7_monitor;

Grant RAC Monitoring Access (For RAC Environments)

-- GV$ views for RAC
GRANT SELECT ON gv_$session TO db24x7_monitor;
GRANT SELECT ON gv_$sql TO db24x7_monitor;
GRANT SELECT ON gv_$sqlarea TO db24x7_monitor;
GRANT SELECT ON gv_$instance TO db24x7_monitor;
GRANT SELECT ON gv_$active_session_history TO db24x7_monitor;

Easy Connect Syntax

oracle://db24x7_monitor:YourSecurePassword123!@hostname:1521/ORCL

With Service Name

oracle://db24x7_monitor:password@hostname:1521/service_name

TNS Connect Descriptor

oracle://db24x7_monitor:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))

RAC Connection with SCAN

oracle://db24x7_monitor:password@scan-hostname:1521/service_name

RAC with Multiple Nodes

oracle://db24x7_monitor:password@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))

Connection Parameters

ORA-12154: TNS Could Not Resolve Service Name

Connection string issues:

• •Verify service name is correct
• •Use full TNS descriptor instead of alias
• •Check hostname and port are reachable

ORA-01017: Invalid Username/Password

Verify credentials and user status:

-- Check if user exists and is unlocked
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'DB24X7_MONITOR';

-- Unlock user if needed
ALTER USER db24x7_monitor ACCOUNT UNLOCK;

-- Reset password if needed
ALTER USER db24x7_monitor IDENTIFIED BY "NewPassword123!";

ORA-12514: TNS Listener Does Not Know of Service

Verify service registration:

-- Check registered services
lsnrctl services

-- Verify service name in database
SELECT name, value FROM v$parameter WHERE name = 'service_names';

-- Register service manually if needed
ALTER SYSTEM REGISTER;

Permission Errors on V$ Views

Verify user privileges:

-- Check granted privileges
SELECT * FROM dba_sys_privs WHERE grantee = 'DB24X7_MONITOR';
SELECT * FROM dba_tab_privs WHERE grantee = 'DB24X7_MONITOR';

-- Check role privileges
SELECT * FROM dba_role_privs WHERE grantee = 'DB24X7_MONITOR';