Change of database passwords through OEM

Once in a while passwords for system database accounts should be changed due to different requirements; it is a boring routine task, but it still needs to be completed. However, with the help of OEM it can be automated and varied based on life cycle status of database targets and/or their belonging to different groups.  If you are not making all passwords unique across all databases, you’re most likely following some password pattern for system user like “system_pwd_[db_type]” where db type can be for instance d, t or p.

If LifeCycle status is properly configured for each of databases, you do not need to rely on database names if they have dev, test, prod inside of names (which is not always true) but rather on target property.

Having OEM properly configured for preferred credentials change of passwords can be done through OEM job, emcli script. For OEM jobs you would need to add all database targets and have SQL, which would be extracting LifeCycle status and apply in relation to the type of database password, similar to that:

declare
  v_user varchar2(32) := 'SYSTEM';
  v_grp varchar2(1000) := '%orcl_gtp_lifecycle_status%';
  v_sql varchar2(1000);
  v_pass varchar2(1000);
begin
  select decode(v_grp, 'Production', 'system_pwd_p', 'Test', 'system_pwd_t', 'Development', 'system_pwd_d', '') into v_pass from dual;
  v_sql := 'alter user '||v_user||' identified by '''||v_pass||'''';
  if v_pass is null then
    dbms_output.put_line('LifeCycle status is not properly configured for '||'%TargetName%');
  else
    execute immediate v_sql;
    dbms_output.put_line('new password set in '||'%TargetName%');
  end if;
end;
/

The code is self-explanatory, and with proper configured notification for the job you can get output to the e-mail with results of changes. The emcli scripting approach is the same – to rely on orcl_gtp_lifecycle_status variable.  However, for emcli there is even the function update_db_password and an example in the documentation about how to use it.  Note that the function uses old password value as input as well, so that might be an issue in some cases. The code can be like:

from emcli import *
from emcli.exception import VerbExecutionError
import sys
import time
set_client_property('EMCLI_OMS_URL','https://oem:7802/em')
set_client_property('EMCLI_TRUSTALL','true')
login(username='sysman',password='sysman_pwd')
def check_job_status(job, p_target_name):
  count=0
  while (count < 10):
   count = count + 1
   obj = emcli.get_jobs(job_id=job)
   for entry in obj.out()['data']:
     l_status = entry['Status ID']
     l_exec_id = entry['Execution ID']
     if (l_status == '5'):
       print "SYSTEM password successfully changed on database: " + p_target_name
       count=100
     elif (l_status == '4'):
       l_resp = get_job_execution_detail(execution=l_exec_id, showOutput=True, xml=True)
       print "Job failed, error details "
       print "Output " + str(l_resp.out())
       count=100
     else:
       time.sleep(2)
def update_system_pwd_for_target(p_target_name, p_old_password, p_new_password):
  l_target_name = p_target_name
  print "Changing SYSTEM password for database: " + l_target_name
  try :
    l_resp = update_db_password (target_name=l_target_name,
                                 change_at_target="yes",
                                 user_name="SYSTEM",
                                 old_password=p_old_password,
                                 new_password=p_new_password,
                                 retype_new_password=p_new_password)
    l_job_submitted = l_resp.out()['JobId']
    check_job_status(l_job_submitted, l_target_name)
  except emcli.exception.VerbExecutionError, e:
    print "ERROR : Change of SYSTEM password failed for database: " + l_target_name
    print "ERROR : " + e.error()
l_d_old_pwd = "old_pwd_d"
l_d_new_pwd = "system_pwd_d"
l_t_old_pwd = "old_pwd_t"
l_t_new_pwd = "system_pwd_t"
l_p_old_pwd = "old_pwd_p"
l_p_new_pwd = "system_pwd_p"
search_list = ['TARGET_TYPE= \'oracle_database\'', 'PROPERTY_NAME=\'orcl_gtp_lifecycle_status\'']
l_targets = list(resource="TargetProperties", search=search_list, columns="TARGET_NAME,TARGET_TYPE,PROPERTY_VALUE")
for target in l_targets.out()['data']:
  print "Updating SYSTEM password on database: " + target['TARGET_NAME']
  if (target['PROPERTY_VALUE'] == "Production"):
    l_old_pwd = l_p_old_pwd
    l_new_pwd = l_p_new_pwd
  elif (target['PROPERTY_VALUE'] == "Test"):
    l_old_pwd = l_t_old_pwd
    l_new_pwd = l_t_new_pwd
  elif (target['PROPERTY_VALUE'] == "Development"):
    l_old_pwd = l_d_old_pwd
    l_new_pwd = l_d_new_pwd
  update_system_pwd_for_target(target['TARGET_NAME'], l_old_pwd, l_new_pwd)

However, you can certainly use the verb execute_sql and with sysdba credentials (or normal credentials if they are for system user) you can accomplish the same as above:

from emcli import *
from emcli.exception import VerbExecutionError
import sys
import time
set_client_property('EMCLI_OMS_URL','https://oem:7802/em')
set_client_property('EMCLI_TRUSTALL','true')
login(username='sysman',password='sysman_pwd')
l_d_new_pwd = "system_pwd_d"
l_t_new_pwd = "system_pwd_t"
l_p_new_pwd = "system_pwd_p"
search_list = ['TARGET_TYPE= \'oracle_database\'', 'PROPERTY_NAME=\'orcl_gtp_lifecycle_status\'']
l_targets = list(resource="TargetProperties", search=search_list, columns="TARGET_NAME,TARGET_TYPE,PROPERTY_VALUE")
for target in l_targets.out()['data']:
  print "Updating system password on database: " + target['TARGET_NAME']
  l_sql = "alter user system identified by "
  if (target['PROPERTY_VALUE'] == "Production"):
    l_new_pwd = l_p_new_pwd
  elif (target['PROPERTY_VALUE'] == "Test"):
    l_new_pwd = l_t_new_pwd
  elif (target['PROPERTY_VALUE'] == "Development"):
    l_new_pwd = l_d_new_pwd
  execute_sql(sql=l_sql + l_new_pwd, targets=target['TARGET_NAME']+":oracle_database", credential_set_name="DBCredsSYSDBA")

Whatever approach you use, it is always beneficial to have OEM properly configured for target properties and tested for usage of named credentials.

Have a good day!