oracle tutorial webinars

ORA-28000

In Oracle, as in just about any other type of computer-based interface, is set up to extend a security option for its users. Unfortunately, as we all know, sometimes the good old-fashioned username/password combination can prove to be the most difficult component of working with a particular computer application. For some websites, a message in a bright red font is triggered at a false combination; for Oracle, an error such as the ORA-28000 is the result. However, the ORA-28000 only applies to specific login circumstances. So what are they?

The Problem

The ORA-28000 is triggered by attempting an invalid password too many times, resulting in getting locked out of the system. Sounds simple, right? The catch is that you may not be the user triggering the error; in fact, it may be nobody at all. This is because of the DBSNMP, an artificial user that Oracle’s Enterprise Manager (OEM) would employ for certain activity, such as instance target discovery. The problem arises when the reset password for the DBSNMP account and the actual password are not synchronized. The DBSNMP account will try repeatedly to log in, until it is locked out at the max of ten attempts.

Maybe you had no idea about the DBSNMP password. It’s possible that the default setting during installation is set to locked. Another source of the error can be the 9i Intelligent Agent trying to connect as the DBSNMP with an invalid password and failing. Regardless of the journey to the problem, it’s safe to bet that your ORA-28000 error may very well be the result of faulty DBSNMP settings.

The Solution

The DBSNMP, if set to default settings, may attempt repeatedly to log in to the account on a particular schedule and possibly fail over and over. There are ways to circumvent this. First, you can check your password settings in the DEFAULT profile by issuing a query:

SELECT resource_name, limit

FROM dba_profiles

WHERE profile = ‘DEFAULT’

AND resource_type = ‘password’;

You can also change the password schedule to unlimited. You can also run a check on the database target for the OEM credentials by going to targets > database > database target name > monitoring configuration > test connection. If you find this navigation to fail, you can unlock the DBSNMP account and set its password to the same as the one you used to run the initial navigation. Furthermore, by ensuring that every task associated with the DBSNMP is attempting to operate using the same password, you can eliminate the error from your system.

Looking forward

Preventing this type of error is relatively simple for database managers. By configuring the account with a secured password and unlocking it immediately following installation, you can avoid the ORA-28000 in many instances. It just involves a bit of proactive thinking now that you know what to keep in mind. In the event that you are not the administrator for your system, contacting your administer about recurring ORA-28000 issues with the information you now have at your disposal can save you both a great deal of time. If you find that you are still having trouble with DBSNMP-generated errors or having continued difficulties logging into Oracle, consider contacting a licensed Oracle consultant to assist you with minimizing the amount of times you wind up locked out of your Oracle system.