1 2 Previous Next 16 Replies Latest reply on Nov 11, 2016 5:40 PM by mathguy

    Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop

    mathguy

      Hi,

       

      I spent a few hours today trying to figure this out. Thought I'd turn to the forum's wisdom before I spend much more time, to make sure I'm not missing some easy pointers (and/or to make sure this is even possible).

       

      I have Oracle 12 on my Windows 10 laptop, and it works fine. I installed it as a stand-alone DB (not in the CDB/PDB setup). I've been using it for a while, it's all good. SQL Developer on it, too, to work with the DB.

       

      Now I would like to be able to access it from a Mac laptop, OS X / Intel 64. I know Oracle no longer supports the DB on Mac. However, they do support SQL Developer, and somehow I got the idea that it should be possible to access my DB from the Windows computer through SQL Developer on the Mac.

       

      Is that completely idiotic, or does it make sense? I was able to download and install JDK 8 and SQL Developer on the Mac, so far so good. I wasn't able to establish a connection though, I spent an hour or so looking through the documentation but I am not able to find what I am looking for.

       

      The setup is a HOME network. I don't know much about networking - some time ago I got the idea, which may be wrong, that computers that share an Internet connection all have the same IP address, and I don't know if that is an issue. What I tried was to set up a connection just like I did on my Windows computer: Connection Type = Basic, Hostname = the name of my laptop (as recognized on the laptop, for example, by  SELECT host_name FROM v$instance; ) I am able to see the Windows machine, under the exact same name, from the Mac, in a Finder window. On the Windows machine I shared the entire "app" directory created when I installed Oracle; the path to the Oracle Home is C:\app\intro\product\12.1.0\dbhome_1\  (shown just to explain what the directory "app" is). I am, indeed, able to see everything in this directory and its subdirectories on the Mac. Is there anything else I need to "share" on the Windows machine to allow SQL Developer on the Mac to access the DB on the Windows machine?  Then, continuing with the Connection setup: I gave it a username and password I use on the Windows machine, Port=1521, Service Name = orcl (all are the same as on the Windows machine).

       

      When I tested the connection with these settings, I got the following error message: Status: Failure - Test failed: IO Error: The Network Adapter count not establish the connection.

       

      I think I am pretty good at following instructions (and also at asking follow-up questions if and as needed), so if this, or some of it, is already answered somewhere, please just point me in the right direction.

       

      Thank you!    mathguy

        • 1. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop

          somehow I got the idea that it should be possible to access my DB from the Windows computer through SQL Developer on the Mac.

          You can.

          Some time ago I got the idea, which may be wrong, that computers that share an Internet connection all have the same IP address,

          You are the one that assigns/determines the IP address a machine uses. You either hard-code the IP or you use DHCP to dynamically assign an address.

           

          But if two of your friends have phones with the same number how is the phone circuitry supposed to know which one you are calling?

           

          In other words, no - multiple machines should NOT have the same IP.

          Is there anything else I need to "share" on the Windows machine to allow SQL Developer on the Mac to access the DB on the Windows machine?

          There is NOTHING you need to share.

           

          The communication is typically using TCP/IP so the remote client doesn't need access to anything on the other machine.

           

          But you do need to follow my usual mantra and SHOW US:

           

          1. WHAT you do

          2. HOW you do it

          3. WHAT results you get

           

          The basic, thin, connection needs the hostname, port and service_name (not SID) of the DB machine. And if you configured your DB listener for 127.0.0.1 instead of its real IP address then no remote machine can EVER connect to it.

           

          Post the results of 'lsnrctl services' on the DB machine. That will show you what it is expecting.

          • 2. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
            mathguy

            Hi,

             

            Thank you for the reply. I will spend more time a little latter, but wanted to share the fact that I was able to set it up and connect to the DB.

             

            For now, I hard-coded the IP address of the "server" laptop (the Windows machine) in SQL Developer on the Mac. I read more and learned that different machines on a wi-fi network all share the same public IP address, but still have their distinct private IP addresses (which can't be accessed directly from the outside world, only from within the network - but that's exactly what I needed!) I was able to find the internal (private network) IP address on the Windows machine through Control Panel.

             

            I will still need to figure out how to access the server laptop through a symbolic name. I imagine that will require a tnsnames.ora or equivalent on the Mac (although other than accessing the DB, the Mac IS able to resolve the symbolic name - it does see my Windows machine, by its name, in a Finder window). Anyway, RP, I will read your reply in more detail and will see where that leads me.

             

            The other thing I needed to do was to mess with the firewall settings on the Windows machine - something else I never had to do in the past (since I didn't set up networks). I found that the default setting for a private network in Windows 10 is to allow outbound connections (as it were, for example, from the Windows machine to the Mac) but to block all inbound connections (for example, from SQL Developer on the Mac). For now I just changed that globally; I will have to figure out how to allow inbound connections only from specific apps, and perhaps only from specific devices. As soon as I changed this firewall setting I was able to connect and work in the DB. In my naive way of thinking, this is - fuzzily - what I meant by "share" on the Windows machine to allow SQL Developer on the Mac to access the DB on Windows.

             

            Thank you!     -     mathguy

            • 3. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop

              On windows you enter that 'alias' info in the 'hosts' file located at C:\WINDOWS\system32\drivers\etc

               

              There should be an equivalent for your OS.

              1 person found this helpful
              • 4. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                mathguy

                Regarding the Windows firewall - I read more and learned how to accept incoming connections only from one specific internal IP address (from the Mac and from no other devices). I could further figure out how to allow only certain applications to connect, but I will leave it as is.

                 

                So, yes, the remaining question is how to tell the Mac how to resolve the host name. Playing with the Mac (which is also something I don't normally do) I found that I can access a bash shell; I will poke around to see where the "hosts" file is on the file system. I don't speak "Mac" but I do speak bash.  : -)   Added:  And not surprisingly, it's /etc/hosts   !

                 

                Thank you!     mathguy

                • 5. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                  Roland Mueller

                  Just a minor comment: /etc/hosts has nothing to do with bash assuch. It's just the place where some unixoid operating systems lookup locally configured IP-to-hostname mappings. Mac OS is based on/derived from BSD Unix.

                   

                  BR,

                  Roland

                  • 6. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                    mathguy

                    That's how I meant it. I don't do Mac - I have no idea how to navigate its file system, for example. But if you can run bash natively, you shouldn't be surprised to find hosts in etc, either. Both are consequences of Mac OS being derived from some form of UNIX (something I had heard in the past but didn't have the need to take advantage of).  Cheers,  mathguy

                    • 7. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                      EdStevens

                      mathguy wrote:

                       

                      Regarding the Windows firewall - I read more and learned how to accept incoming connections only from one specific internal IP address (from the Mac and from no other devices). I could further figure out how to allow only certain applications to connect, but I will leave it as is.

                       

                      So, yes, the remaining question is how to tell the Mac how to resolve the host name. Playing with the Mac (which is also something I don't normally do) I found that I can access a bash shell; I will poke around to see where the "hosts" file is on the file system. I don't speak "Mac" but I do speak bash. : -) Added: And not surprisingly, it's /etc/hosts !

                       

                      Thank you! mathguy

                      But you need to be aware that the IP address of each machine on your home network normally has a DHCP assigned IP address.  That means that the next time they start up they could very well get a different IP address.  If you are able to assign a fixed IP address . . . well, I don' t know how well that will play with your ISP, or when you take that laptop to some other location, like Starbucks, the airport, or a friend's house.  I understand that at that point you won't care about connecting to your database from your Mac, but that laptop's networking doesn't know or care about why you have assigned a fixed IP address or when it might not be appropriate.

                      • 8. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                        mathguy

                        Hmm... good point. Showing my lack of understanding and experience with networking here.

                         

                        So, the DHCP server must be my gateway. It assigns itself an internal IP address, let's say 192.168.1.1. Then it assigns addresses to other devices (iPad, iPhone, laptops, whatever) say from 192.168.1.2 to 1.20 or whatever. As a device is removed from the network, the address may be reassigned to another device, and when the old device is back in range it may be assigned a different (always _internal_) IP address. So the Mac laptop may be 1.8 today, but if my wife takes it with her when she travels, when she comes back it may be .1.5 or .1.11 - is that what you are saying? (I believe it is.) In which case I need to mess with my firewall again to tell it which connections to accept.

                         

                        So, the alternative is to mess with the gateway instead - to find the documentation to see how I can assign fixed _internal_ IP addresses to a couple of devices in the house, so they remain the same if they go out of range and back in. That makes perfect sense. This should have nothing to do with my ISP, since we are only talking about internal IP addresses; if my ISP assigns a different public IP address to my gateway, that has nothing to do with the issue at hand. (I am on a permanent Internet connection, too, so the public IP address should be static, but again that is irrelevant to my home network setup.)

                         

                        I am not worried about traveling. It is true that someone on a different network may be able to access my laptop when I travel - I am sure hacker-rigged machines scan continuously for addresses and ports that accept incoming connections to exploit them. But whenever I connect to a network, the machine asks me if it is public or private, and my firewall setting is only for private networks. I am not accepting incoming connection requests from public networks. And if I am at a friend's house and I connect to their network... well, if one of their devices is infected/hacked and has just the right private IP address, then yes, I'm exposed, but I don't have state secrets on my laptop, so I'll take the risk.   :-)

                         

                        Thank you, this was helpful - more food for thought.     Cheers,   mathguy

                        • 9. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                          EdStevens

                          mathguy wrote:

                           

                          Hmm... good point. Showing my lack of understanding and experience with networking here.

                           

                          So, the DHCP server must be my gateway. It assigns itself an internal IP address, let's say 192.168.1.1. Then it assigns addresses to other devices (iPad, iPhone, laptops, whatever) say from 192.168.1.2 to 1.20 or whatever. As a device is removed from the network, the address may be reassigned to another device, and when the old device is back in range it may be assigned a different (always _internal_) IP address. So the Mac laptop may be 1.8 today, but if my wife takes it with her when she travels, when she comes back it may be .1.5 or .1.11 - is that what you are saying? (I believe it is.) In which case I need to mess with my firewall again to tell it which connections to accept.

                           

                          So, the alternative is to mess with the gateway instead - to find the documentation to see how I can assign fixed _internal_ IP addresses to a couple of devices in the house, so they remain the same if they go out of range and back in. That makes perfect sense. This should have nothing to do with my ISP, since we are only talking about internal IP addresses; if my ISP assigns a different public IP address to my gateway, that has nothing to do with the issue at hand. (I am on a permanent Internet connection, too, so the public IP address should be static, but again that is irrelevant to my home network setup.)

                           

                          I am not worried about traveling. It is true that someone on a different network may be able to access my laptop when I travel - I am sure hacker-rigged machines scan continuously for addresses and ports that accept incoming connections to exploit them. But whenever I connect to a network, the machine asks me if it is public or private, and my firewall setting is only for private networks. I am not accepting incoming connection requests from public networks. And if I am at a friend's house and I connect to their network... well, if one of their devices is infected/hacked and has just the right private IP address, then yes, I'm exposed, but I don't have state secrets on my laptop, so I'll take the risk. :-)

                           

                          Thank you, this was helpful - more food for thought. Cheers, mathguy

                          In general, yes you have it.  My own networking understanding is rather rudimentary compared to a few other people here.  I guess you could mess with your gateway.  I do know that on our corporate network some people (myself included) technically still have a DHCP address, but the net admin has configured it such that there is only one address for the dhcp server to choose from for assignment to that particular workstation.  I'm not sure how that is done on his end.

                           

                          Personally I never install oracle directly on a desktop system.  The only reason I would do that is for my own learning, and for that I install it into a virtual machine (using VirtualBox) on my desktop.  I configure that VM to use a 'hostonly' adapter, which can only talk to the host system or other VMs running on the host system.  I can then give that vm a fixed IP address, and from there everything works exactly as it does on my corporate network, with the proviso that the vm is ONLY visible within the confines of that physical machine.  I'm not sure how I would do it if I wanted/needed to connect to that database from another physical machine.  I've never had to go there.

                          • 10. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                            mathguy

                            Everything I do is for learning only. I am not an IT professional - nor do I intend to be; I am enjoying my (very) early retirement and use the free time to do what I always wanted to do, which is to play with computers in many different ways, including learning Oracle. I will have things like my CD collection in an Oracle DB schema on my laptop, and I installed SQL Developer on my wife's Mac so she can have access to it and similar schemas. Having the CD collection in the DB is not the point either - even that I only use for learning purposes. And setting up the Mac to access it is also only for learning purposes - it's not a critical thing for the house!  So, yes, our setup is quite different.   :-) 

                             

                            My assignment for next week (assigned by me to myself) will be to install Oracle 12c on Ubuntu, you can bet I'll have more questions then.   Cheers,   -   mathguy

                            • 11. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop

                               

                              My assignment for next week (assigned by me to myself) will be to install Oracle 12c on Ubuntu,

                              Except Ed's point is that you do NOT need to do that unless you are trying to learn installs.

                               

                              Just download VirtualBox and one, or more, of the preconfigured appliances and you will already have Oracle 12c installed and ready to go.

                               

                              There is also a RAC VM download so you don't have to install configure to play with that either.

                              • 12. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                                mathguy

                                Yes, learning how to do installs is one of the two or three big reasons to do that. Other reasons: I will attempt again to install 12c in the multi-tenant configuration (I failed the first time, this is why right now on my laptop and a Windows desktop I use I have 12c configured as a standalone db). And, I understand sometimes there are differences between Oracle on Windows and on Linux, and I would like to be able to see that for myself. (For example, bugs fixed in one but not the other, etc.) Besides, Ubuntu is not directly supported by Oracle, so I will learn a little more about installing stuff on Ubuntu in general.

                                 

                                Oracle 12c as currently configured on my two machines is working perfectly fine, but I am missing the opportunity to learn certain things. For example, as you may recall, you explained a while back why I should use service names, not SIDs, in SQL Developer connections - particularly important when setting up 12c in a multi-tenant configuration. I didn't forget!

                                 

                                One may ask, what is the purpose of all this learning. Besides it being fun (I've always enjoyed learning for its own sake), I find that I am already able to help people, on a voluntary basis, with Oracle SQL questions. The more I learn, the more I will be able to help with other types of questions. (I am re-learning UNIX and helping with UNIX-related questions too.) That increases the fun many-fold. No other agenda! At some point I may feel confident enough to join various open-source projects, but I only want to do that when I really have something to offer and I know what I'm talking about, and this helps in that direction.

                                 

                                Cheers,   mathguy

                                • 13. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                                  EdStevens

                                  Well, I must say that watching you on this forum, you've come up to speed and are grasping things much faster than a lot of people who've been around here for years ....

                                  +1

                                  • 14. Re: Home network setup: access Oracle on Windows machine through SQL Developer on Mac laptop
                                    Mike Kutz

                                    mathguy wrote:

                                     

                                    I don't know much about networking - some time ago I got the idea, which may be wrong, that computers that share an Internet connection all have the same IP address,

                                    Your home devices go through a Router/Gateway device (usually built into the Modem).

                                    The Router/Gateway will "mutiplex" all of your home computer's IPs into a single IP.

                                    That IP would be the IP of the Internet facing side of your Router/Gateway.

                                     

                                    So... to other people on the internet, all of your home devices will appear to be making connections from a single IP device (your Router/Gateway).

                                    And... if you have servers at home, it is up to your Router to forward the incoming traffic to the correct (single) home device.

                                     

                                    Now, for your home devices, they all must have a unique IP address.

                                     

                                    Back to answering Oracle related questions:

                                    The largest problem I've seen within home networks (regarding connecting to Oracle on a laptop) is the proper translation of hostname <-> IP address.

                                    (ie DNS related problems)

                                     

                                    Modifying the /etc/host file is one way to solve the problem... but.. you'll have to do that on every machine that needs to access your Oracle box.

                                    Adding an entry in you local DNS Server is another way (this is usually built into your Modem/Router/Gateway device)

                                     

                                    MK

                                    1 person found this helpful
                                    1 2 Previous Next