OEM Password Management (oempw.sql)

What This Script Does

Queries the OEM SNMP password repository (DBSNMPPWD table) to retrieve monitoring credentials used by Oracle Enterprise Manager for SNMP-based database monitoring. This appears to be part of a custom OEM credential management system.

The Script

rem  oempw.sql
rem
ttitle 'OEM SNMP Password Repository'
rem
select
    HOSTNAME,
    SID,
    PWD
from
    dbpwd.DBSNMPPWD
where
    upper(HOSTNAME) like nvl(upper('&host'), '%')
    AND upper(SID) like nvl(upper('&sid'), '%')
order by
    HOSTNAME,SID
/

Usage

-- Query all OEM SNMP passwords
@oempw.sql
-- Enter: [press enter for all hosts]
-- Enter: [press enter for all SIDs]

-- Filter by specific hostname
@oempw.sql
-- Enter: prodserver01 (for specific host)
-- Enter: [press enter for all SIDs]

-- Filter by database SID
@oempw.sql
-- Enter: [press enter for all hosts]
-- Enter: PROD (for specific database)

Parameters

The script prompts for:

host: Hostname pattern (% for all hosts)
sid: Database SID pattern (% for all SIDs)

Required Privileges

SELECT on DBPWD.DBSNMPPWD
-- (Requires access to the OEM password repository)

Sample Output

                                OEM SNMP Password Repository

HOSTNAME             SID             PWD
-------------------- --------------- -------------------------
prodserver01         PROD            snmp_monitoring_password
prodserver02         PROD            snmp_monitoring_password
testserver01         TEST            test_snmp_password
devserver01          DEV             dev_snmp_password

Key Output Columns

HOSTNAME: Server hostname where database monitoring is configured
SID: Database SID/service identifier for monitoring
PWD: SNMP password for monitoring access (sensitive information)

Security Considerations

Access Control

This script reveals sensitive SNMP monitoring credentials
Should only be accessible to authorized OEM administrators
Consider implementing additional authentication layers

Monitoring Security

SNMP passwords enable database monitoring access
Compromise could allow unauthorized monitoring
Regular password rotation recommended

Audit Requirements

Log access to OEM password repository
Monitor who queries monitoring credentials
Maintain change history for password updates

Common Use Cases

OEM Configuration Management

-- Retrieve monitoring credentials for OEM setup
@oempw.sql
-- Enter: target_hostname
-- Use credentials for SNMP configuration

Monitoring Troubleshooting

-- Verify SNMP credentials during monitoring issues
@oempw.sql
-- Confirm password consistency across environments

Environment Migration

-- Extract monitoring credentials for environment setup
@oempw.sql
-- Configure monitoring in new environments

Security Audit

-- Review monitoring access credentials
@oempw.sql
-- Ensure appropriate password strength and rotation

SNMP Monitoring Context

Oracle Enterprise Manager

Uses SNMP for database performance monitoring
Requires credentials for authenticated access
Enables remote monitoring capabilities

Database Agent Configuration

SNMP credentials configure database monitoring agents
Passwords authenticate monitoring requests
Essential for OEM Grid Control/Cloud Control

Repository Management

Table Structure (Assumed)

CREATE TABLE DBPWD.DBSNMPPWD (
    HOSTNAME VARCHAR2(50),
    SID VARCHAR2(30),
    PWD VARCHAR2(100)
);

Maintenance Operations

-- Update SNMP password
UPDATE DBPWD.DBSNMPPWD
SET PWD = 'new_snmp_password'
WHERE HOSTNAME = 'server' AND SID = 'database';

-- Add new monitoring target
INSERT INTO DBPWD.DBSNMPPWD
VALUES ('newserver', 'NEWDB', 'snmp_password');

Security Best Practices

Password Management

Use strong, unique SNMP passwords
Implement regular password rotation
Encrypt passwords in repository
Secure repository access

Access Control

Limit access to monitoring credentials
Implement role-based access controls
Regular access reviews and audits
Monitor credential usage

Network Security

Use SNMP v3 when possible for encryption
Secure network channels for monitoring
Firewall rules for monitoring traffic
Regular security assessments

OEM Integration

Grid Control/Cloud Control

Credentials used for target discovery
Authentication for monitoring data collection
Essential for database target management

Monitoring Agents

Database agents use SNMP credentials
Authenticate with OEM management servers
Enable performance data collection

Troubleshooting

Monitoring Connection Issues

-- Verify correct credentials are stored
@oempw.sql
-- Compare with OEM target configuration

Password Synchronization

-- Ensure consistency across monitoring components
@oempw.sql
-- Update all references to changed passwords

Performance Impact

Minimal: Simple table query
Small Table: Typically contains limited records
Fast Execution: Instantaneous results