13 Replies Latest reply on Nov 28, 2019 10:01 AM by Peter de Vaal

    Network access of oracle db

    Sunhawk

      Hello,

       

      I need help with the network configuration of an oracle database. The database is installed in a virtual box guest:

       

      VirtualBox - host: Fedora 30

      VirtualBox - guest: Oracle Linux 8

      Oracle database 18c: installed in guest, port is 1521, service name XE

       

      I used a network bridge to access the guest from the host e.g. via ssh. That works. But although the firewall inside the guest is switched off I cannot connect to the database. I am trying to connect with a tool like dbeaver or sqldeveloper. On the host no oracle client is running. I don't know whether that is ok. It looks like I am missing some configuration.

       

      tnsnames.ora and listener.ora are like standard (localhost, port 1521, xe).

       

      Could anybody help me?

       

      Thanks,

       

      Jain

        • 1. Re: Network access of oracle db
          Dude!

          If you have a networking issue you need to troubleshoot your network installation and also make sure your database listener and the oracle database are running.

           

          Are the database and listener running?

          Can you connect to the database from within the guest system?

           

          How is your network configured. Just because you use a bridged network adapter does not necessarily mean you can connect, for example, it won't work if host and guest are on different network segments.

           

          You are not providing enough info to work with. Btw, does OL 8 support 18c?

          • 2. Re: Network access of oracle db
            EdStevens

            db237b3e-7c6f-4bcb-abb4-54a56f2eb7e8 wrote:

             

            Hello,

             

            I need help with the network configuration of an oracle database. The database is installed in a virtual box guest:

             

            VirtualBox - host: Fedora 30

            VirtualBox - guest: Oracle Linux 8

            Oracle database 18c: installed in guest, port is 1521, service name XE

             

            I used a network bridge to access the guest from the host e.g. via ssh. That works. But although the firewall inside the guest is switched off I cannot connect to the database. I am trying to connect with a tool like dbeaver or sqldeveloper. On the host no oracle client is running. I don't know whether that is ok. It looks like I am missing some configuration.

             

            tnsnames.ora and listener.ora are like standard (localhost, port 1521, xe).

             

            Could anybody help me?

             

            Thanks,

             

            Jain

            I've created scores of vms under virtual box (and vmworkstation before that).  Only once have I used a bridged adapter, and that was for a special purpose.  Generally I create one hostonly adapter and one NAT.  The NAT is used only for outgoing calls from the guest to the internet, to download installation packages.  On the hostonly, I treat it just like I would any server in the data center, with a fixed ip address and an assigned server name.  See https://edstevensdba.wordpress.com/2012/12/15/configure-the-virtualbox-network-2/  for more details.

            • 3. Re: Network access of oracle db
              Dude!

              It depends, however, what you need to accomplish. Using the host-only and default NAT adapter does not allow other machines on the network to access your virtual machine. I always use the bridged network adapter and assign a static IP address, similar to the host system.

              • 4. Re: Network access of oracle db
                EdStevens

                Dude! wrote:

                 

                It depends, however, what you need to accomplish. Using the host-only and default NAT adapter does not allow other machines on the network to access your virtual machine. I always use the bridged network adapter and assign a static IP address, similar to the host system.

                True enough.  We need more information from the OP regarding what his exact needs are.  However, on re-reading his original post just now, I see that he DID say "access the guest from the host e.g. via ssh. That works".  So I'd say we can assume the networking is ok at the os level (though perhaps not optimal to his needs)  and we need to look at the oracle piece.  I think we both overlooked that and got sidetracked by the fact that it is on VBox guest and our knowledge that that in itself often presents configuration challenges for a newbie.

                 

                I'll post back to the OP directly, to ask for the necessary information.

                • 5. Re: Network access of oracle db
                  EdStevens

                  So, if your ssh connection from host to guest is working, you've got the basic networking config working.  So ...

                   

                   

                  You give us no information about the failed db connection.  You give no error messages. You give no diagnostics.  You need to show us the actual error message, in context.  You need to show us the output of 'lsnrctl status', as executed from an ssh session on the vm guest.

                   

                  As for whether or not you need to install any client software on the host, well, of course you have to install SOME sort of client software if you want a client connection to the database.  Depending on your needs, that could just be SQL Dev, and using it's 'basic' connection type.  Just about anything else, you will need to install the standard Oracle client - either instant client or runtime client, at the very least.  I like to install a full 'admin' client on my workstation.  Partly because I need it for my work with my 'live' databases (the company databases in the various data centers), and I want to treat and access my private VBox servers, and their databases, exactly as I would the 'live' ones.

                   

                  As for your use of a bridged adapter, I'd suggest you consider if you really need a bridged, or if you'd be better served with a hostonly.  With a bridged, your vm is a node on whatever network your host is attached to .   With hostonly, your vm is on a private, virtual network that exists only within the host OS.  That means they have no visibility to the wider network.  If you have no need for any machine outside of your host workstation to ever contact the vm, then I'd recommend hostonly instead of bridged.

                  • 6. Re: Network access of oracle db
                    MikeFr

                    Sorry for the late answer.

                     

                    The firewall of the VM is switched off and a ssh from the host to the guest works. The network is not the problem. Although I will have to go via a host only since in the company vpn with a bridge I do not get an ip adress assigned. So far so good.

                     

                    I tried to connect with dbeaver. But as I read from EdStevens that is not enough, right!? I need an oracle client. Hm, I though I hoped a tool like SQL Developer or DBeaver is enough.

                     

                    Here some more information as requested

                     

                    $ lsnrctl status

                     

                    LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 28-OCT-2019 18:12:34

                     

                    Copyright (c) 1991, 2018, Oracle.  All rights reserved.

                     

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

                    STATUS of the LISTENER

                    ------------------------

                    Alias                    LISTENER

                    Version                  TNSLSNR for Linux: Version 18.0.0.0.0 - Production

                    Start Date                28-OCT-2019 18:11:22

                    Uptime                    0 days 0 hr. 1 min. 12 sec

                    Trace Level              off

                    Security                  ON: Local OS Authentication

                    SNMP                      OFF

                    Default Service          XE

                    Listener Parameter File  /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora

                    Listener Log File        /opt/oracle/diag/tnslsnr/oralin/listener/alert/log.xml

                    Listening Endpoints Summary...

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))

                    Services Summary...

                    Service "95940284288f2c99e05569cd5ea5b08f" has 1 instance(s).

                      Instance "XE", status READY, has 1 handler(s) for this service...

                    Service "XE" has 1 instance(s).

                      Instance "XE", status READY, has 1 handler(s) for this service...

                    Service "XEXDB" has 1 instance(s).

                      Instance "XE", status READY, has 1 handler(s) for this service...

                    Service "xepdb1" has 1 instance(s).

                      Instance "XE", status READY, has 1 handler(s) for this service...

                    The command completed successfully

                     

                    When trying to connect with DBeaver (without an oracle client on the host) I get the following error message

                     

                    I/O-Fehler: The Network Adapter could not establish the connection

                      The Network Adapter could not establish the connection

                      The Network Adapter could not establish the connection

                        Connection refused (Connection refused)

                        Connection refused (Connection refused)

                     

                    Could you please confirm that without a separate oracle client installed in the host it won't work!? I had hoped to avoid that.

                    • 7. Re: Network access of oracle db
                      Dude!

                      SQLDeveloper will be enough to access your database.

                       

                      Perhaps you are using a wrong connect string. How are you connecting?

                       

                      https://www.orafaq.com/wiki/EZCONNECT

                      • 8. Re: Network access of oracle db
                        Dude!
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

                        Also, if you want to use the loop back adapter and require external connections, you should use "localhost" and make sure the vm hostname is also localhost (localhost.localdomain) so that the Oracle listener will listen on "any" network interface - otherwise you can only connect from within the virtual machine. If the hostname of your VM is anything else but localhost, use the same name or IP address for the listener configuration and make sure you have a /etc/hosts entry.

                        • 9. Re: Network access of oracle db
                          EdStevens

                          IF you use SQL Dev with a 'basic' connection type, it will use SQL Dev's built-in jdbc thin client to access the database.  If you use SQL Dev with the 'tns' connection type, it will rely on a separately installed oracle client - instant client or run-time client or admin client.  I can't say what other products may or may not use, but you certainly cannot assume that a product will come with its own client drivers.  Just because the product is named "Whiz-Bang GUI Client for Oracle", that does not mean that the product developer doesn't expect their product - obviously designed to be used in an Oracle shop - to rely on software that is readily available to such a shop.

                           

                          BTW, you really should change your screen name to something recognizable.  It doesn't have to be your real name. (Who says my name is really Ed Stevens?  Maybe I just like a certain TV show!)  But it should be something recognizable as you.  All youc96cec01-2d14-44bd-a3dd-47690af3d2dc's look the same.

                          • 10. Re: Network access of oracle db
                            EdStevens

                            Peter de Vaal wrote:

                             

                            It looks this problem has not been solved yet. I have the same problem.

                            Just installed DB 19c on a VMWare VM with Oracle Linux 7.7. I specified port 1521 as open on firewalld (also tried with firewall disabled). I have 2 network adapters, one is host only with fixed ip-address, the other bridged (tried also with the bridged adapter switched off).

                            I can ping the VM from the host and can also access the VM using ssh. I can connect with SQLPlus on the VM to the database. But when I try to connect with SQLDev (basic connection) from the host (which is a Mac) I get 'The network adapter cannot establish a connection'. I also tried to telnet on port 1521 from the host, then I get 'Connection Refused'. I have another VM with OL 7 with DB 12.2, that one does not have a problem, I can really not find the difference, except that this dB version is higher and this Linux version is higher. Might it have to do with SE-Linux?

                            What does the installation docs say about SE-Linux?  I seem to recall that it is supposed to be disabled, or perhaps set to 'permissive'.

                            • 11. Re: Network access of oracle db
                              Peter de Vaal

                              It is permissive out of the box. But I set it to disabled. Did not solve the problem

                              • 12. Re: Network access of oracle db
                                Peter de Vaal

                                I have re-installed the OS and the database on the guest VM and now it is working. No idea what has happened in the prevous install. It might have to do with network circumstances: on my first install I was on the company network, on my new install I was on my home network (not connected with VPN to company network during install).