14 Replies Latest reply: Jun 5, 2013 8:13 PM by user558643 RSS

    Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection

    user558643
      Hello,

      Discovoerer Plus and Viewere were working fine last week and today an attempt to login gives this error on the page:

      A connection error.
      - error while creating the session, check for other errors.
      - oracle.discoiv.connections.ConnectionStoreException: weblogic.common.resourcepool.ResourceDeadException: 0: Could not connect to 'oracle.jdbc.OracleDriver'. The returned message is: ORA-01017: invalid username/password; logon denied It is likely that the login or password is not valid. It is also possible that something else is invalid in the configuration or that the database is not available.
      - weblogic.common.resourcepool.ResourceDeadException: 0: Could not connect to 'oracle.jdbc.OracleDriver'. The returned message is: ORA-01017: invalid username/password; logon denied It is likely that the login or password is not valid. It is also possible that something else is invalid in the configuration or that the database is not available.
      - weblogic.common.resourcepool.ResourceDeadException: 0: Could not connect to 'oracle.jdbc.OracleDriver'. The returned message is: ORA-01017: invalid username/password; logon denied It is likely that the login or password is not valid. It is also possible that something else is invalid in the configuration or that the database is not available.
      unable to retrieve connection list
      - Cannot retrieve connection list. Check the Discoverer application log for more details.

      JFYI
      Discoverer is installed on the same server as EBS 12i.
      Last week we had issues when EBS promoted us to change the passwords of apps, SYSADMIN, APPLSYSPUB and even that of EUL4_US in the system. I am wondering which of these changes could have had an impact on Discoverer. How do I get ebs to recognize the new password of EUL4_US?

      Thanks
        • 1. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
          Michael Armstrong-Smith
          Hello
          With 11g there's a couple of database accounts in the repository whose password could have expired.

          Look in this log: oracle\Middleware\user_projects\domains\ClassicDomain\servers\WLS_DISCO\logs\WLS_DISCO.out

          Then look for any line that says the password has expired. This will tell you exactly which account it is.

          When you have figured out which account it is all you need to do is have your DBA reset the password. The account will either be DEV_DISCOVERER or DEV_DISCCOVERER_PS.

          Note: the word DEV might be PROD in some case.

          Best wishes
          Michael
          • 2. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
            user558643
            Thanks Micheal.

            I have located three account and reset their passwords to what they were before my password changes:
            DEV_DISCOVERER, DEV_DISCOVERER and EUL4_US.

            I then restarted Discoverer and again attempted login at discoverer/plus and it didn't go to me normal login window but took me to
            "Connect to Oracle BI Discoverer
            To connect to Oracle BI Discoverer, click on a connection name or enter your connection details directly.
            Choose Connection"
            Butthere was no connection to choose from.

            I then entered the filled in the "Indicates required field." with
            sysadmin, its password and connection Database name but it kept giving me
            - Failed to connect to database - ORA-01017: invalid username/password; logon denied

            I can connect as sysadmin into the EBS application just fine.

            I then entered the connection details for the EUL4_US account and I get
            "- You do not have access to any End User Layer tables"

            What could be the problem?

            Thanks
            Mathias
            • 3. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
              Michael Armstrong-Smith
              Mathias
              If you're certain your passwords are correct you might not have the correct version of the DBC file. This file comes from the database and tells the system how to securely connect. If the database has been refreshed or the file is out of date you will also see a username/password error.

              Ask your DBA for a copy of the file and ensure a copy is placed in the Secure folder on the server. Your DBA / Discoverer Administrator should know what I'm referring to.

              Best wishes
              Michael
              • 4. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                user558643
                Hello Mike,

                I have the database, EBS 12i & Discoverer installed on the same same OE Linux 6 server.I searched and found the file (OFD1.dbc) in $FND_SECURE. Indeed the OFD1.dbc was updated over a week ago when we were prompted to change passwords by EBS. I counldn't find OFD1.dbc under the database home (rdbms).

                I have copied $FND_SECURE/OFD1.dbc from apps to $ORACLE_INSTANCE/config/Discoverer/Discoverer_asinst_1/ofd1.dbc as per Note 1074326.1 step 4.4.3.
                After all these, I again tried connect using Discoverer Plus and Viewer and was still encountered with a login screen that led to "ORA-01017: invalid username/password;".

                I can connect as sysadmin into the EBS application just fine.

                I then entered the connection details for the EUL5_US account and I still get
                "- You do not have access to any End User Layer tables"
                or when I also enter the End User Layer value as EUL5_US, I get
                A connection error.
                - Default or specified schema containing EUL tables is inaccessible

                Thanks
                Mathias
                • 5. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                  Michael Armstrong-Smith
                  Hi Mathias
                  These are 2 different issues because you can't connect in Discoverer Plus as EUL5_US in an Apps mode instance. Let's take a step back and make sure everything is right.

                  First of all check your TNSNAMES file which should be in here: $ORACLE_INSTANCE/config

                  Inside TNSNAMES you should see all of the connections for Discoverer. There should be one equating to the OFD1 database. You have to make sure that the DBC file is named the same as the entry inside TNSNAMES. I also found that I usually need to have my casing the same on Linux, so if your TNSNAMES entry is OFD (without the 1) your DBS file should be named OFD.DBC, and if your TNSNAMES entry is OFD1 your DBS file should be named OFD1.DBC. Hope this part makes sense.

                  Next let's make sure the EUL is operating correctly. We can do this inside the Discoverer Admin tool by connecting as EUL5_US but with the Applications checkbox unchecked. If this works next try using SYSADMIN with the Applications checkbox checked. You will also need to check the TNSNAMES file stored here: c:\oracle\BIToolsHome_1\network\admin and make sure the DBC file on the Admin machine has the same name. In Windows the casing for the name does not matter just the name itself. Your DBC files are stored in whatever the Environment Variable called FND_SECURE is pointing at.

                  By the way, in your Linux environment make sure that if you have an FND_SECURE setting that your DBC file is in that location. You may also want to check OPMN.XML which contains a placeholder for FND_SECURE. You'll find OPMN.XML here: $ORACLE_INSTANCE/config/OPMN/opmn-component-name/ where opmn-component-name might be just opmn like this: $ORACLE_INSTANCE/config/OPMN/opmn/

                  Hope this helps
                  Michael
                  • 6. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                    user558643
                    Hello Micheal,

                    1) I just examined the $ORACLE_INSTANCE/config/tnsnames.ora file and it is good. I can connect from the appldisc environment successfully as
                    sqlplus EUL5_US/EUL5_US11@OFD1 & sqplus apps/***@OFD1

                    2) OFD1 DB inside the tnsnames.ora is uppercase and the .dbc file is named as OFD1.dbc

                    3) I have installed Oracle Discoverer Administrator on an XP manchine but at startup, it doesn't display the "Applications checkbox". Did I miss this during installation and can I simply reinstall?
                    EUL5_US connects find at this window and I am greeted with the Load Wizard Step 1. Does it mean that to connect as sysadmin, I have to reinstall this BI Discoverer?

                    4) I have copied OFD1.dbc into E:\secure = FND_SECURE variable.

                    5) $FND_SECURE is set in the EBS apps and Discoverer appldisc environments. they both contain OFD1.dbc.

                    This system was being accessed two weeks ago via discoverer/plus or viewer until some password changes that took place last week on the entire system using FNDPASS. But I have reverrt the passwords of EUL5_US, DEV_DISCOVERER & DEV_DISCCOVERER_PS tp what they were. I can connect with accounts in sqlplus. Is there a file that holds the passwords of this accounts or some other accounts that I am not aware of?

                    Thanks
                    Mathias
                    • 7. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                      Michael Armstrong-Smith
                      Hi Mathias
                      Let me answer at least a couple of your issues:

                      +3) I have installed Oracle Discoverer Administrator on an XP manchine but at startup, it doesn't display the "Applications checkbox". Did I miss this during installation and can I simply reinstall?+
                      EUL5_US connects find at this window and I am greeted with the Load Wizard Step 1. Does it mean that to connect as sysadmin, I have to reinstall this BI Discoverer?

                      You have to enable Discoverer Admin in APPS mode, it does not do it automatically. To do that follow this workflow:

                      1. Connect to Discoverer Admin as EUL5_US
                      2. On the Load Wizard Step 1 click the Cancel button
                      3. From the menu bar select Tools | Options and navigate to the Connection tab
                      4. Check the button labeled Connect to both standard and application EULs
                      5. In the box labeled Gateway User enter: applsyspub/pub
                      6. In the box labeled Foundation Name enter: apps
                      7. Click the OK button
                      8. From the menu bar select File | Connect
                      9. This time you should see the Oracle Applications User check box.

                      On the XP machine you need to also have the DBC file and tnsnames files. Here's how to enable the DBC security:

                      1. Create a folder under c:\Oracle called Secure so that you could have: c:\oracle\secure
                      2. Place your DBC file in this folder
                      3. Launch Control Panel
                      4. Select System | Advanced System Settings - this opens the System Properties box
                      5. Click the Environment Variables button - this opens the Environment Variables box
                      6. In the bottom half of the screen, labeled System variables click the New button - this opens the New System Variable box
                      7. In Variable Name enter: FND_SECURE
                      8. In Variable Value enter: c:\oracle\secure
                      9. Click the OK button to close the New Variable box
                      10. Click the OK button to close the Environment Variables box
                      11. Click the OK button to close the System Properties box
                      12. Close Control Panel

                      This should get Discoverer Admin working. Can you check and let me know?

                      As far as I know there are no additional passwords. If Discoverer Admin can be made to work using SYSADMIN then there is a setup issue on the server. Most likely the FND_SECURE is not picking up the location for the DBC but let's tackle that once we get Admin working.

                      Best wishes
                      Michael
                      • 8. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                        user558643
                        Micheal,
                        Thanks for those wonderful and precise steps. I can now connect as sysadmin in Discoverer Administrator.

                        I cannot still connect as using discoverer/plus or discoverer/viewer. See the window I am getting in this link: https://dl.dropboxusercontent.com/u/15497130/logs/LOGIN%20Failure.jpg

                        An attempt to connect as sysadmin gives me an "ORA-01017: invalid username/password; logon denied " and
                        trying to connect as EUL5_US gives "You do not have access to any End User Layer tables"

                        What am I missing here.

                        Thanks
                        Mathias
                        • 9. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                          Michael Armstrong-Smith
                          Hi Mathias
                          What we have done is to prove that there is nothing wrong with your EUL, that you can connect using SYSADMIN and that the DBC file is correct. This is all local on the Discoverer Admin machine now you have to repeat the same on the application server.

                          I've also seen this issue occur where you need to enter the password twice. Let's try that first.

                          Launch Discoverer Plus and connect as SYSADMIN to OFD1. When the connection fails just type the same password again and click Go a second time. Let me know if this works. If it does I will tell you how to fix it.

                          If this doesn't work then we'll look deeper.

                          Michael
                          • 10. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                            user558643
                            Hello Michael,

                            I have just tried Discoverer Plus & Viewer entering the password multiple times but to no avail. I am still getting "ORA-01017: invalid username/password; logon denied".

                            Thanks for your help
                            Mathias
                            • 11. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                              Michael Armstrong-Smith
                              Hi Mathias
                              So we can rule out a domain URL issue which would have been the case if a second password had worked.

                              I looked at your error picture again and it looks to me like you are not using the Oracle Applications login from the drop-down. The screenshot says you used Oracle BI Discoverer whereas you should be using Oracle Applications.

                              You can also prefill some connection information like this: http://<server-domain>:<port>/discoverer/plus?database=ofd1&us=sysadmin&lm=applications&eul=eul5_us

                              If you try this and still have an issue and if you are absolutely certain that you have been entering the SYSADMIN password correctly you need to check the following:

                              1. Check OPMN.XML and look for the FND_SECURE setting
                              2. Make sure you have a valid DBC file in the location pointed at by the FND_SECURE setting
                              3. Double check your TNSNAMES and check the name of the connection and the internal connection information, you can compare this to the settings inside the TNSNAMES file on the Discoverer Admin machine

                              Best wishes
                              Michael
                              • 12. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                                user558643
                                Hello Michael,

                                it's finally working. The last problem was the .dbc file which was capitalized.

                                cd $FND_SECURE
                                ls –lart
                                -rw------- 1 appldisc dba 804 May 29 16:42 OFTEST.dbc
                                mv OFTEST.dbc oftest.dbc
                                ll
                                -rw------- 1 appldisc dba 804 May 31 00:27 oftest.dbc

                                Thank you so much for helping me go through my first BI Discoverer 11 installation. My SR on this issue has not be responded to for over 24 hours but you patiently took me through the connectivity issues and gave me the answer.

                                God bless You
                                Mathias
                                • 13. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                                  Michael Armstrong-Smith
                                  Mathias
                                  You are very, very welcome.

                                  Could you please tag my answers as being helpful and mark one as being the solution. This way I'll get credited with points from Oracle on my account.

                                  God bless you too and good luck with your Discoverer adventures
                                  Michael
                                  • 14. Re: Unable to log into Discoverer Plus - oracle.discoiv.connections.Connection
                                    user558643
                                    Thanks fvery much Michael,

                                    you successfull solved all my numerous problems with this install and configuration of Discoverer for the Administrator to the .dbc file setup.

                                    thanks you
                                    Mathias