How to check and see if Oracle Account is expired, locked, or open

In the EPM world, things are tricky sometimes because of too many things inter-related for the EPM system to work as a whole.

It comprises of several things
     The EPM System itself which is made up of several things - the most common, for example, being, Shared Services, Foundation, Essbase, Planning, plus the reporting and analysis tools - the financial reporting studio and web analysis. Then we have the Print services which seems very trivial but of equal importance and the Ghostscript (at least in the 11.1.2. version).
     Then, we have Smartview or Excel add-in and Web Logic
      Last but not the least, one of the most important piece of software - the database. We have the 11G in this case.

While working in the EPM environment, a slight issue in any of the above-mentioned area can lead up to errors, and because of the magnanimity of the EPM structure, the numbers of log files it creates, and the hard-to-remember path, it is sometimes a pain-in the-neck to resolve the issue.

Let me share this. Our secondary system was working fine and suddenly, one fine morning, while trying to pull up a financial report in workspace, it threw out an error. After having checked all the logs, checking the documentation, and doing some GOOGLE, we came to find out that the BIPLUS account password had expired. We never knew that those passwords would expire. It took so long to find out the cause of the error, however, after getting the information regarding the password expiry, all we had to do was set the password again and we were ready to rock.

In such cases, it is handy to check and see if any of the Oracle user accounts have expired. This is how you do it.
Open SQLPLUS.
Enter admin Username and ENTER
Enter password and ENTER
Now on the SQL> prompt, you can type in the command:
           SELECT username, account_status
           FROM dba_users;
It will give you a list of USERNAMEs and ACCOUNT_STATUS
ACCOUNT_STATUS will be OPEN, EXPIRED, or EXPIRED & LOCKED

TNS:could not resolve the connect identifier specified – ORA 12154

I was trying to load some data and each time I ran the SQL script, I got the message “ORA 12154 TNS:could not resolve the connect identifier specified.” Since, I am not on the Oracle side but rather on the Hyperion side, some research regarding “TNS names” and the “ORA 12154” error helped me solve the problem. The reason I am entering this on my blog is because it might be helpful to folks who come across the same issue.

The above error can be seen for various reasons as per documents:

                a. Oracle net libraries not being able to get the TNS alias from “TNSNAMES.ORA” file.

                b. Something is wrong with the way the alias is created in the “TNSNAMES.ORA” file, such as   incorrect syntax.

                c. TNS alias could not be resolved into a connect adapter.

There are several things we need to check and make sure they are correct.

1. Verify that the TNSNAMES.ORA file has the "service name" and "alias" that are being used. In my case, the path to the file is \\SERVER\D$\oracle\database\product\11.2.0\client_1\network\admin

2. Take a look at a file SQLNET.ORA in \\SERVER\D$\oracle\database\product\11.2.0\client_1\network\admin folder and make sure that you have TNSNAMES in NAMES.DIRECTORY_PATH.

For example:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3. Try and see if  you can log in to Oracle using SQL developer, SQL PLUS or any other tool that you use in your environment.

4. Ensure that the ENVIRONMENT VARIABLE "PATH" has the path for the TNSNAMES.ORA file specified.

If you are not sure how to check the ENVIRONMENT VARIABLE "PATH", - in Windows XP
a. Start -> My Computer -> Right Click -> Properties.
b. Click the ADVANCE tab and then ENVIRONMENT VARIABLES
c. Under "SYSTEM VARIABLES" you will see a bunch of "varialbes" and "values" They should be in alphabetical order. Key in "P" to get to the variables starting with "P"
d. When you get the "PATH" click the EDIT tab.
e. You will see a lot of paths here. The easiest way to view is to copy and paste it to a word processor such as Notepad or Textpad, whatever you prefer or have.
f. Check and see if the path to TNSNAMES.ORA is specified here.
g. Also ensure that you have the path for Oracle_Home specified.
h. One thing very tricky about the PATH that I have noticed is that even when you have the path specified, it does not work. In that case, if your variable path is towards the end of the path, try to move it towards the beginning. I am not sure if it is a glitch or if there is restriction in the number of letters, but I have noticed quite a few times when it worked by moving the path towards the beginning.
i. Another interesting and sometimes confusing issue is that sometimes we happen to have the Client installed more than once and the path points to the wrong one. This is also sometimes an issue. So make sure that you have the path of the right one.
j. Check the registry for Oracle_Home (the value). If you are not sure how to check it.
          a. Start command prompt. START -> RUN -> Type in "REGEDIT" and hit enter. You will see the REGISTRY EDITOR screen.
          b. On the left pane -> HKEY_LOCAL_MACHINE \SOFTWARE\ORACLE\Key_OraClient11g_home1. On the right pane, you will see the path. Ensure they are correct.
k. Check entry for TNS_ADMIN in the registry editor: HKEY_LOCAL_MACHINE \SOFTWARE\ORACLE. If it is there make sure it has the right path: ...client1\network\admin. If it is not there, create one.
i. If you think you have more than one instance of Oracle client, you can check if there are more in the registry editor: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\HOME_COUNTER.

After ensuring all of the things specified above are correct try to run the SQL script, it should work unless there are some other issues with the system.