Well there have been lot of issues related to some versions of oracle database till 11.2.0.3 like Grid Control Agent Crashes with Core Dump if the Password of the Monitoring Configuration (DBSNMP) user in any Monitored Database is in Grace Period or may be a nightmare with Grid Control Shows DBSNMP Account Locked ORA-28000 Or Agent Connection to Instance Failed ORA-28001... So there have been some workarounds but before that let us take a look on some things..
There have been lot myths of what is a DBSNMP user and why it is needed and what are the bugs and corruptions caused by some of the changes in the password of this user account. Well to be simple folks DBSNMP is a user account which is actually used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database. Sometimes there is a bit confusion among the DBA's about SNMP process and DBSNMP user, SNMP is a process which will make sure metrics of the database will be forwarded to other SNMP monitoring tools because the administrators can store and analyze historical data that has been obtained through SNMP. Its still has to be searched in some of the docs of EM and metalink notes whether SNMP and DBSNMP have any internal relation between them...
Having said some lines about DBSNMP, this user has SELECT ANY DICTIONARY system privilege which can read the passwords from SYS.USER$ and would therefore will be handling the intelligent agent stuff mentioned above. The default passwords are very simple and has to be changed by the security administrators however changing this password itself is not a single step procedure. We need to perform several steps to make sure the password is changed successfuly. It would have been good if it was done at DB creation not later.
The DBCA and Oracle universal installer will ask to you for the passwords for SYS, SYSTEM, SYSMAN and DBSNMP anyways, so its good to provide a strong password in that case and moreover there is also a myth that Enterprise pages are found corrupted when we change DBNSMP password in not a proper manner, that is not the case, Actually a user named as MGMT_VIEW which is offcourse having a password definately, corruptions happen when password of this user is changed so it is advisable not to change the password of MGMT_VIEW user at all. So corrupt pages of EM will actually be found when the password of MGMT_VIEW is changed, so best is to have a special profile for this account in which password never expires because it gets MONITORING_PROFILE profile by default which specifies PASSWORD_LIFE_TIME is for 180 days, however FAILED_LOGIN_ATTEMPTS are set to unlimited in that case. This user account has read only access to tables which contain management data like percent of CPU used over time for the database server.
For operating systems like Linux the problems mentioned above have been solved with one-off Patch 8300283 on top of the 10.2.0.5 Grid Control Agent Home. This patch is available for the 10.2.0.4 and 10.2.0.5 Grid Control Agent on major Unix / Linux platforms. Its best to see instructions for the respective patches.
So let us take a look at how to change a password of DBSNMP user, the following are the steps :=
Stop the EM dbconsole
On Linux - DB Control Release up to 11.1.x
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ emctl stop dbconsole
On Linux - DB Control Release 11.2.x and higher
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
[oracle@dodwal ~]$ emctl stop dbconsole
SQL> alter user dbsnmp identified by new_password ;
SQL> connect dbsnmp/@prod
Go to $ORACLE_HOME/host_sid/sysman/emd
Save the file targets.xml to targets.xml.orig
Edit the file targets.xml with your favorite text editor
Search for the line:
<property name="password" value="<encrypted_string>" encrypted="TRUE">
Replace the encrypted value by the new password value
Replace TRUE by FALSE
Restart the standalone dbconsole
On Linux - DB Control Release up to 11.1.x
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ emctl start dbconsole
On Linux - DB Control Release 11.2.x and higher
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
[oracle@dodwal ~]$ emctl start dbconsole
Check that the password has been encrypted
Edit the file targets.xml
Search for the line: <property name="password" value="<encrypted_string>" encrypted="TRUE">
Check that the password VALUE is encrypted
Check that the value of ENCRYPTED is TRUE
Note: In case of RAC DB, the dbsnmp password should be changed in targets.xml not only for instances(oracle_database), but also for rac_database target. The file targets.xml needs to be updated on all the RAC Cluster nodes.
________________________________________________________________________________________________
Steps for Changing the DBSNMP Account Password in an Oracle Database Vault Environment:
Before you can change the password for the DBSNMP user account, you must revoke the DV_MONITOR role from this account. In an Oracle Database Vault environment, the DBSNMP user account is granted the DV_MONITOR role. (The DBSNMP user can change it's own password directly in sqlplus, without having the DV_MONITOR role revoked first.)
To change the DBSNMP user password:
1) Log in to SQL*Plus using an account that has been granted the DV_OWNER role.
2) Revoke the DV_MONITOR role from the DBSNMP user account.
3) Connect as a user who has been granted the DV_ACCTMGR role and then change the DBSNMP user account password.
4) Connect as the DV_OWNER user and then grant the DV_MONITOR role back to the DBSNMP user account.
Alternatively, you can temporarily disable Oracle Database Vault, log on as a user who has been granted the ALTER USER privilege, and then modify the DBSNMP password. Afterwards, re-enable Database Vault.
________________________________________________________________________________________________
There have been lot myths of what is a DBSNMP user and why it is needed and what are the bugs and corruptions caused by some of the changes in the password of this user account. Well to be simple folks DBSNMP is a user account which is actually used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database. Sometimes there is a bit confusion among the DBA's about SNMP process and DBSNMP user, SNMP is a process which will make sure metrics of the database will be forwarded to other SNMP monitoring tools because the administrators can store and analyze historical data that has been obtained through SNMP. Its still has to be searched in some of the docs of EM and metalink notes whether SNMP and DBSNMP have any internal relation between them...
Having said some lines about DBSNMP, this user has SELECT ANY DICTIONARY system privilege which can read the passwords from SYS.USER$ and would therefore will be handling the intelligent agent stuff mentioned above. The default passwords are very simple and has to be changed by the security administrators however changing this password itself is not a single step procedure. We need to perform several steps to make sure the password is changed successfuly. It would have been good if it was done at DB creation not later.
The DBCA and Oracle universal installer will ask to you for the passwords for SYS, SYSTEM, SYSMAN and DBSNMP anyways, so its good to provide a strong password in that case and moreover there is also a myth that Enterprise pages are found corrupted when we change DBNSMP password in not a proper manner, that is not the case, Actually a user named as MGMT_VIEW which is offcourse having a password definately, corruptions happen when password of this user is changed so it is advisable not to change the password of MGMT_VIEW user at all. So corrupt pages of EM will actually be found when the password of MGMT_VIEW is changed, so best is to have a special profile for this account in which password never expires because it gets MONITORING_PROFILE profile by default which specifies PASSWORD_LIFE_TIME is for 180 days, however FAILED_LOGIN_ATTEMPTS are set to unlimited in that case. This user account has read only access to tables which contain management data like percent of CPU used over time for the database server.
For operating systems like Linux the problems mentioned above have been solved with one-off Patch 8300283 on top of the 10.2.0.5 Grid Control Agent Home. This patch is available for the 10.2.0.4 and 10.2.0.5 Grid Control Agent on major Unix / Linux platforms. Its best to see instructions for the respective patches.
So let us take a look at how to change a password of DBSNMP user, the following are the steps :=
Stop the EM dbconsole
On Linux - DB Control Release up to 11.1.x
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ emctl stop dbconsole
On Linux - DB Control Release 11.2.x and higher
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
[oracle@dodwal ~]$ emctl stop dbconsole
SQL> alter user dbsnmp identified by new_password ;
SQL> connect dbsnmp/@prod
Go to $ORACLE_HOME/host_sid/sysman/emd
Save the file targets.xml to targets.xml.orig
Edit the file targets.xml with your favorite text editor
Search for the line:
<property name="password" value="<encrypted_string>" encrypted="TRUE">
Replace the encrypted value by the new password value
Replace TRUE by FALSE
Restart the standalone dbconsole
On Linux - DB Control Release up to 11.1.x
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ emctl start dbconsole
On Linux - DB Control Release 11.2.x and higher
[oracle@dodwal ~]$ export ORACLE_SID=prod
[oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
[oracle@dodwal ~]$ emctl start dbconsole
Check that the password has been encrypted
Edit the file targets.xml
Search for the line: <property name="password" value="<encrypted_string>" encrypted="TRUE">
Check that the password VALUE is encrypted
Check that the value of ENCRYPTED is TRUE
Note: In case of RAC DB, the dbsnmp password should be changed in targets.xml not only for instances(oracle_database), but also for rac_database target. The file targets.xml needs to be updated on all the RAC Cluster nodes.
________________________________________________________________________________________________
Steps for Changing the DBSNMP Account Password in an Oracle Database Vault Environment:
Before you can change the password for the DBSNMP user account, you must revoke the DV_MONITOR role from this account. In an Oracle Database Vault environment, the DBSNMP user account is granted the DV_MONITOR role. (The DBSNMP user can change it's own password directly in sqlplus, without having the DV_MONITOR role revoked first.)
To change the DBSNMP user password:
1) Log in to SQL*Plus using an account that has been granted the DV_OWNER role.
2) Revoke the DV_MONITOR role from the DBSNMP user account.
3) Connect as a user who has been granted the DV_ACCTMGR role and then change the DBSNMP user account password.
4) Connect as the DV_OWNER user and then grant the DV_MONITOR role back to the DBSNMP user account.
Alternatively, you can temporarily disable Oracle Database Vault, log on as a user who has been granted the ALTER USER privilege, and then modify the DBSNMP password. Afterwards, re-enable Database Vault.
________________________________________________________________________________________________
No comments:
Post a Comment