Problem statement: Suppose you have a process to change
sys/system password of all your databases from a central server. This process
has an impact if you are using sys/system as named credential in OEM. All your
login through OEM will fail for database targets.Moreover,this can be a time
consuming process if you have thousands of database targets.This article will
help you to update all impacted named credential after sys/system password
change.
Solution: Before jump into solution directly we need to
understand how do we update OEM repository database. Definitely we are not
going to fire SQL statement directly into OEM repository database.
We shall use emcli tool already available in OMS
server.EMCLI is a tool which is nothing but a zython interpreter where you can
write python script and call using emcli.I am not going into that scripting
part but I will show you some basic commands to resolve this problem.
Step 1: Create a session using sysman user
emcli login -username=sysman -password=********
Login successful
Step 2: Find all database credentials like below
EBSGP5_SYS SYSMAN oracle_database DBCreds cebsgp5 sys
NC_CEBSGP5__2021-05-28-162346 SYSMAN oracle_database DBCreds cebsgp5_EBSGP5 sys
NC_TESTD2_2025-04-08-092927 SYSMAN oracle_database DBCreds testd2 sys
NC_TESTD3_2024-05-27-035554 SYSMAN oracle_database DBCreds TESTD3 sys
NC_TESTD4_2025-02-05-164844 SYSMAN oracle_database DBCreds testd4 sys
NC_TESTPREP6_2025-05-05-140633 SYSMAN oracle_database DBCreds testprep6 sys
NC_TESTPREP9 SYSMAN oracle_database DBCreds testprep9 sys
NC_TESTQ2_2024-12-20-102450 SYSMAN oracle_database DBCreds TESTQ2 sys
NC_TESTQ3_2024-02-29-101701 SYSMAN oracle_database DBCreds testq3 system
NC_TESTT1_2024-01-31-135804 SYSMAN oracle_database DBCreds testt1 system
NC_TESTT2_2024-08-22-093600 SYSMAN oracle_database DBCreds testt2 sys
NC_TESTT3_2024-09-26-062948 SYSMAN oracle_database DBCreds TESTT3 sys
NC_TESTT4_2025-03-15-082356 SYSMAN oracle_database DBCreds testt4 sys
NC_TESTDWD SYSMAN oracle_database DBCreds testdwd sys
NC_TESTDWQ SYSMAN oracle_database DBCreds testdwq sys
NC_TESTDWT_2023-07-26-154146 SYSMAN oracle_database DBCreds testdwt system
NC_TESTDWT_2025-03-14-094116 SYSMAN oracle_database DBCreds testdwt system
NC_TESTMD1ODA_2024-06-13-012130 SYSMAN oracle_database DBCreds testmd1oda sys
NC_TESTMQ1ODA_2024-07-03-080029 SYSMAN oracle_database DBCreds testmq1oda sys
NC_TESTMQ3ODA_SYS SYSMAN oracle_database DBCreds testmq3oda sys
NC_TESTMT2ODA SYSMAN oracle_database DBCreds testmt2oda sys
NC_TESTMT3ODA SYSMAN oracle_database DBCreds testmt3oda sys
NC_ORACLE_D_2020-05-01-115902 SYSMAN oracle_database DBCreds sys
NC_ORACLE_D_2022-06-03-084945 SYSMAN oracle_database DBCreds sys
NC_ORACLE_D_2024-07-24-015533 SYSMAN oracle_database DBCreds testmt1oda sys
Step 3. Validate any existing named credential like below
emcli modify_named_credential -cred_name=NC_TESTD2_2025-04-08-092927 -cred_type=DBCreds -cred_scope=Instance -target_name=testd2 -target_type=oracle_database -test
Credential test failed for testd2:oracle_database
Authentication failed ORA-01017: invalid username/password; logon denied
It shows that existing password is not working
Step 4. Change the password with test validation now
emcli modify_named_credential -cred_name=NC_TESTD2_2025-04-08-092927 -attributes="DBUserName:sys;DBPassword:******" -test
Credential updated.
Now same things can be automated using below simple shell script.
Disclaimer: This is just an example.Please modify and test the script in non-prod before deploying into production.
#!/bin/sh
ORACLE_BASE=/u01/app/oracle/gc_inst
ORACLE_HOME=/u01/app/oracle/13_4_0_0_middleware
echo "Enter repository database connection using format host:port/service_name:"
read repdb
echo "Enter sys/system password:"
read cookie
echo "Enter password for sysman existing in OMS repository:"
read sysmanpwd
emcli login -username=sysman -password=$sysmanpwd
emcli list_named_credentials | grep DBCreds | grep sys | awk -F " " '{if ($5 != "sys") print $1,$5,$6}' >~/named_cred.txt
while read line
do
cred_name=`echo $line | awk -F ' ' '{print $1}'`
db_target_name=`echo $line | awk -F ' ' '{print $2}'`
username=`echo $line | awk -F ' ' '{print $3}'`
echo "Changing password for $cred_name....."
if [ "$username" = "sys" ]; then
emcli modify_named_credential -cred_name=$cred_name -attributes="DBUserName:$username;DBPassword:${cookie};DBRole:sysdba" -test
else
emcli modify_named_credential -cred_name=$cred_name -attributes="DBUserName:$username;DBPassword:${cookie}" -test
fi
done < ~/named_cred.txt
emcli logout
Hope you have learnt something useful…
