8 Replies Latest reply on Aug 26, 2017 12:55 PM by EdStevens

    Connecting to a database via Oracle SQL Developer

    Chris9

      Hello,

       

      I have successfully installed EBS r12.1.3 Single Node Vision DEMO in my laptop via VirtualBox. But I have no idea on how to connect to the database via SQL Dev.

      I'm not sure what user to use as I have already tried the root, oracle and applmgr user but can connect with error ORA-01017:invalid username/password; logon denied.

      I can confirm that I'm using the correct Host, Port and SID.

       

      Host: ebs.example.com

      Port: 1521

      SID: DEMO.

       

      Appreciate your help on this.

       

      Thanks,

      Chris

        • 1. Re: Connecting to a database via Oracle SQL Developer

          I have successfully installed EBS r12.1.3 Single Node Vision DEMO in my laptop via VirtualBox. But I have no idea on how to connect to the database via SQL Dev.

          I'm not sure what user to use as I have already tried the root, oracle and applmgr user but can connect with error ORA-01017:invalid username/password; logon denied.

          I can confirm that I'm using the correct Host, Port and SID.

           

          Host: ebs.example.com

          Port: 1521

          SID: DEMO.

           

          That exception is pretty clear: either you are using the WRONG credentials or that user doesn't exist in the DB you are trying to connect to.

           

          Two common issues with Oracle 12c are:

           

          1. there can be 'common' or 'local' users.

          2. there can be a root/CDB and one or more PDBs

           

          You can NOT connect to the root/CDB as a local user. Local users can ONLY connect to a PDB.

           

          An O/S level connection will be (by defaultl) to the root/CDB.

           

          In sql dev the default connection type is 'basic' - which means it uses a 'thin' driver and an Oracle client is NOT involved.

          You need to have the PDB service name configured in the TNS NAMES file on the server (not the client - the client isn't involved).

           

          That will cause the listener to show the PDB as being registered.

           

          Execute 'lsnrctl services' on the DB server and post the results. The results will show the DBs the listener is aware of and can connect to.

           

          Also post the screenshot showing the sql dev connection dialog and the parms you are using.

          1 person found this helpful
          • 2. Re: Connecting to a database via Oracle SQL Developer
            Chris9

            Thanks for the reply.

             

            Honestly, I'm new to this and I'm not familiar in navigating the DB, all I can do is starting and shutting down the DB and APPS.

            I'm more on the Functional side, just want to have my own EBS for some testing.

             

            How do I run the 'lsnrctl services'? All I know is to login to root, oracle and applmgr. Below is the screen shot of my sql dev new connection parameters:

             

             

            Thanks,

            Chris

            • 3. Re: Connecting to a database via Oracle SQL Developer
              EdStevens

              How do I run the 'lsnrctl services'?

               

              It's a command line command, executed on the machine hosting the database.

               

              As for your SQL Dev connection definition, while it is not directly responsible for your reported error, you should be specifying SERVICE_NAME instead of SID.  Especially with a 12c multinenant database.  What service name should you specify?  We'll know that when we see the output of 'lsnrctl status' or 'lsnrctl services'.

              • 4. Re: Connecting to a database via Oracle SQL Developer
                Gary Graham-Oracle

                In addition, it makes a difference whether SQL Developer is also installed in the EBS demo image, or merely at the host (laptop) level...

                http://www.thatjeffsmith.com/archive/2014/03/connecting-to-oracle-from-your-host-to-a-virtualbox-oel-guest/

                • 5. Re: Connecting to a database via Oracle SQL Developer
                  Chris9

                  I can't run this command in my Oracle Linux Server login as root, oracle and applmgr.

                  Maybe there is another step I should do before I can run this?

                   

                  Another information for my setup, I'm running VB on my windows10, and SQL Dev is installed in the host.

                  I was able to configure my own Test environment on the application on top of Vision Demo instance without any problem. All I want is to connect to DB so I can run simple queries on it.

                   

                  • 6. Re: Connecting to a database via Oracle SQL Developer
                    Chris9

                    I don't have the '12c PDB HR -Using IP Address' sub menu in my SQL Dev.

                     

                    Below is my sql dev menus. I'm creating a new connection to Oracle DB running on VirtualBox.

                    • 7. Re: Connecting to a database via Oracle SQL Developer
                      EdStevens

                      Chris9 wrote:

                       

                      I can't run this command in my Oracle Linux Server login as root, oracle and applmgr.

                      Maybe there is another step I should do before I can run this?

                       

                      Another information for my setup, I'm running VB on my windows10, and SQL Dev is installed in the host.

                      I was able to configure my own Test environment on the application on top of Vision Demo instance without any problem. All I want is to connect to DB so I can run simple queries on it.

                       

                      First - never do anything as root unless that extreme and unprotected level of access is necessary.  And this is not one of those times.

                       

                      The 'command not found' simply means there was not an executable file named 'lsnrctl' in any of the directories in the PATH.  You need to set your environment first,  The easiest way would be to use the 'oraenv' command:

                       

                      First, find the name of an available database, with this command:

                       

                      cat /etc/oratab

                       

                      Then, use the oraenv command to set the environment for one of the listed databases:

                       

                      source oraenv

                      <respond to the resulting prompt with the name of one  of the databases listed from 'cat /etc/oratab' >

                       

                      And to confirm your oracle environment variables:

                       

                      env | grep ORA | sort

                      • 8. Re: Connecting to a database via Oracle SQL Developer
                        EdStevens

                        Chris9 wrote:

                         

                        I don't have the '12c PDB HR -Using IP Address' sub menu in my SQL Dev.

                         

                        Of course you don't.  That was from Jeff's demo of how he set up a demo system for demo purposes.  From that you are supposed to see how the dots are connected, not find the exact same dots on your system.