For companies that have policies to change passwords often, this document will walk through a process developed to change DPA monitoring passwords for Oracle instances through a combination of scripts and the DPA interface.
Note: This document does not apply to changing the DPA repository password. That process is covered in this KB article.
If an instance is not currently being monitored, this process does not make any modifications to it in case there are connection issues. Any instance not being monitored can have the password changed manually via the process outlined in this KB article.
Note: The SQL statements in this document can be run from the Database Query Tool in DPA or SQL*Plus.
The following SQL statement is used to get a list of databases that are currently being monitored and save to a temporary table. Run this from the DPA repository:
create table ignite_running as select id, name, username, password, status, command from cond where status = 'STARTED' and db_type = 'Oracle';
Run the following from the DPA repository:
update cond set command='STOP' where id in (select id from ignite_running); commit; -- only needed if running from SQL*Plus
At this point all DPA monitors are now currently stopped and ready for the password change.
This step uses a DPA alert to change the DPA user password on each monitored instance. This alert will fail, but the alter user command will execute successfully and change the DPA password on each instance. The alert should be marked as inactive so it does not run automatically. Use the Test Alert button to execute the alert and change the password on all instances.
This step is needed to get the encrypted version of the new password used in step 2. Click Options > Update Connection Info, and follow the wizard to change the password for one instance.
select id, name, password from cond where lower(name) like lower('%&InstanceName%')
update cond set password = '&NewEncryptedPassword', command='START' where id in (select id from ignite_running);
drop table ignite_running