Forum Stats

  • 3,836,793 Users
  • 2,262,193 Discussions
  • 7,900,114 Comments

Discussions

How to resolve SQL Developer error?

2

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    I never take my instructions from youtube. That said, he clearly was headed toward a RAC setup - even said so in the printed title and his opening remarks. But with what you've shown me of your VM defintion, it appears you have deviated from that and are heading toward a simpler, single-node system. Unless you very specifically want to work with RAC (a multi-node setup) I'd ignore all of that. I'll ignore the fact that your server name is poorly chosen, and just go with it.

    So, you need to modify your network config to use a fixed IP address on your hostonly adapter. To do that, make the following changes to /etc/sysconfig/network-scripts/ifcfg-enp0s8

    First, make a backup of it .. copy it to ifcfg-enp0s8.save

    Change

    BOOTPROTO=dhcp
    

    to

    BOOTPROTO=none
    

    Add the following:

    IPADDR=192.168.56.102  
    prefix=24 
    GATEWAY=192.168.56.1        
    

    After making those changes, either restart the entire VM, or restart the network services. I will leave it as a learning exercise for you to look up how to restart the network without rebooting the server.


    You already have the following in /etc/hosts of the VM

    192.168.56.102  rac1.localdomain
    

    You need to add it to C:\Windows\System32\drivers\etc\hosts

    Once you have done this, we need to test the network connectivity from the windows host to the linux guest vm. First test is to simply ping it:

    c:>  ping rac1.localhost
    


  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 91 Blue Ribbon

    Hi @EdStevens ,

    I have followed all the steps and now I can ping from Windows host to Linux VM as shown below

    Microsoft Windows [Version 10.0.19043.1165]
    (c) Microsoft Corporation. All rights reserved.
    
    
    C:\WINDOWS\system32>ping rac1.localdomain
    
    
    Pinging rac1.localdomain [192.168.56.102] with 32 bytes of data:
    Reply from 192.168.56.102: bytes=32 time<1ms TTL=64
    Reply from 192.168.56.102: bytes=32 time<1ms TTL=64
    Reply from 192.168.56.102: bytes=32 time=1ms TTL=64
    Reply from 192.168.56.102: bytes=32 time=1ms TTL=64
    
    
    Ping statistics for 192.168.56.102:
        Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
        Minimum = 0ms, Maximum = 1ms, Average = 0ms
    
    
    C:\WINDOWS\system32>
    

    Thanks

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Ok, so we have established network connectivity from the host to a known, fixed ip address on the guest (vm).

    I'd like to do one more thing before we get back to SQL Dev. That is go be able to create a shell session from the host to the guest. With that in place, you will be able to truly treat the vm as if it were a server in a locked data center - no physical access to the console. 

    While Windows comes with an ssh client, I prefer (for several reasons I won’t go into here) to install and use the lightweight ‘PuTTY’. You can look that up, but for now, I’ll continue with the ‘ssh’ that is included with windows. To use it, start at the Windows command prompt (cmd.exe) then enter the command: 

    C:\Users\edste>ssh [email protected]
    [email protected]'s password:
    Activate the web console with: systemctl enable --now cockpit.socket
     
    Last login: Mon Aug 30 08:05:54 2021 from 192.168.100.88
    The Oracle base has been set to /u01/app/oracle
    ============ oratab is ===========
    cdb:/u01/app/oracle/product/19.0.0/dbhome_1:N
    ==================================
     
    2021-08-30 08:08:20
    oracle:cdb$
    

    Let's break that down:

     C:\Users\edste>ssh [email protected]
    

     I enter the command 'ssh'. For the command-line parms, 'oracle' is the name of the os user I am using to connect to the remote server, and 'vbol83-02' is the name of the remote server, as listed in the 'hosts' file. The very first time, you will be prompted to accept a security key, not shown here as this is not my first time.

     [email protected]'s password:
    

     Prompted for the password for user 'oracle'. Input is not echoed.

     The remainder of what is shown above is simply the output from logging on, some of which is custom to my system, so don't worry if you don't see exactly that.

     At this point you are working with a shell session on the remove server. And I would like to see the status of the listener, so enter the 'lsnrctl' command:

     oracle:cdb$ lsnrctl status
     LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-AUG-2021 08:14:45
     
    Copyright (c) 1991, 2019, Oracle. All rights reserved.
     
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                    LISTENER
    Version                  TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    Start Date               25-AUG-2021 14:53:30
    Uptime                   4 days 17 hr. 21 min. 15 sec
    Trace Level              off
    Security                 ON: Local OS Authentication
    SNMP                     OFF
    Listener Log File        /u01/app/oracle/diag/tnslsnr/vbol83-02/listener/alert/log.xml
    Listening Endpoints Summary...
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbol83-02.localdomain)(PORT=1521)))
    Services Summary...
    Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "bb3e427449ea18e7e053c900a8c06e86" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "cdb" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "cdbXDB" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "pdb01" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    The command completed successfully
     
    2021-08-30 08:14:46
    oracle:cdb$
    

     Once you have done that, we will have the necessary information to start using SQL Dev, and I will show you how to configure that.

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 91 Blue Ribbon

    Hi @EdStevens ,

    I tried with both users which are configured in VM (kinju and oracle in my case) but I get the following error (I have removed the KEY from the text pasted here)

    Microsoft Windows [Version 10.0.19043.1165]
    (c) Microsoft Corporation. All rights reserved.
    
    
    C:\WINDOWS\system32>ssh [email protected]
    The authenticity of host 'rac1.localdomain (192.168.56.102)' can't be established.
    ECDSA key fingerprint is ....
    Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
    Warning: Permanently added 'rac1.localdomain,192.168.56.102' (ECDSA) to the list of known hosts.
    [email protected]'s password:
    Connection closed by 192.168.56.102 port 22
    
    
    C:\WINDOWS\system32>ssh [email protected]
    [email protected]'s password:
    Connection closed by 192.168.56.102 port 22
    

    Can you please suggest am I doing something wrong?

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Looks like a firewall issue. We could get into establising specific firewall rules, but as this appears to be a personal test system, I'd just disable the firewall completely. I leave it as an exercise for the student to research that.

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 91 Blue Ribbon

    Hi @EdStevens ,

    I stopped and disable the firewall but I still I get the same error , I have added debug which I got when I used -v

    Microsoft Windows [Version 10.0.19043.1165]
    (c) Microsoft Corporation. All rights reserved.
    
    
    C:\WINDOWS\system32>ssh -v [email protected]
    OpenSSH_for_Windows_8.1p1, LibreSSL 3.0.2
    debug1: Connecting to rac1.localdomain [192.168.56.102] port 22.
    debug1: Connection established.
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_rsa type 0
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_rsa-cert type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_dsa type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_dsa-cert type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_ecdsa type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_ecdsa-cert type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_ed25519 type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_ed25519-cert type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_xmss type -1
    debug1: identity file C:\\Users\\iTouchVision Valsad/.ssh/id_xmss-cert type -1
    debug1: Local version string SSH-2.0-OpenSSH_for_Windows_8.1
    debug1: Remote protocol version 2.0, remote software version OpenSSH_7.4
    debug1: match: OpenSSH_7.4 pat OpenSSH_7.0*,OpenSSH_7.1*,OpenSSH_7.2*,OpenSSH_7.3*,OpenSSH_7.4*,OpenSSH_7.5*,OpenSSH_7.6*,OpenSSH_7.7* compat 0x04000002
    debug1: Authenticating to rac1.localdomain:22 as 'oracle'
    debug1: SSH2_MSG_KEXINIT sent
    debug1: SSH2_MSG_KEXINIT received
    debug1: kex: algorithm: curve25519-sha256
    debug1: kex: host key algorithm: ecdsa-sha2-nistp256
    debug1: kex: server->client cipher: [email protected] MAC: <implicit> compression: none
    debug1: kex: client->server cipher: [email protected] MAC: <implicit> compression: none
    debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
    debug1: Server host key: ecdsa-sha2-nistp256 SHA256:WK8mNDtZuXZvVRyBqlOGaEBqIovFkiYyMoWOdpD2GDw
    debug1: Host 'rac1.localdomain' is known and matches the ECDSA host key.
    debug1: Found key in C:\\Users\\iTouchVision Valsad/.ssh/known_hosts:4
    debug1: rekey out after 134217728 blocks
    debug1: SSH2_MSG_NEWKEYS sent
    debug1: expecting SSH2_MSG_NEWKEYS
    debug1: SSH2_MSG_NEWKEYS received
    debug1: rekey in after 134217728 blocks
    debug1: pubkey_prepare: ssh_get_authentication_socket: No such file or directory
    debug1: Will attempt key: C:\\Users\\iTouchVision Valsad/.ssh/id_rsa RSA SHA256:MHqLgAs/401UOe0RgzDocbzvH97GCqMbSRXYp0ZIdTY
    debug1: Will attempt key: C:\\Users\\iTouchVision Valsad/.ssh/id_dsa
    debug1: Will attempt key: C:\\Users\\iTouchVision Valsad/.ssh/id_ecdsa
    debug1: Will attempt key: C:\\Users\\iTouchVision Valsad/.ssh/id_ed25519
    debug1: Will attempt key: C:\\Users\\iTouchVision Valsad/.ssh/id_xmss
    debug1: SSH2_MSG_EXT_INFO received
    debug1: kex_input_ext_info: server-sig-algs=<rsa-sha2-256,rsa-sha2-512>
    debug1: SSH2_MSG_SERVICE_ACCEPT received
    debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic,password
    debug1: Next authentication method: publickey
    debug1: Offering public key: C:\\Users\\iTouchVision Valsad/.ssh/id_rsa RSA SHA256:MHqLgAs/401UOe0RgzDocbzvH97GCqMbSRXYp0ZIdTY
    debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic,password
    debug1: Trying private key: C:\\Users\\iTouchVision Valsad/.ssh/id_dsa
    debug1: Trying private key: C:\\Users\\iTouchVision Valsad/.ssh/id_ecdsa
    debug1: Trying private key: C:\\Users\\iTouchVision Valsad/.ssh/id_ed25519
    debug1: Trying private key: C:\\Users\\iTouchVision Valsad/.ssh/id_xmss
    debug1: Next authentication method: password
    debug1: read_passphrase: can't open /dev/tty: No such file or directory
    [email protected]'s password:
    Connection closed by 192.168.56.102 port 22
    


  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Hmm. I've never seen that before. I'd suggest you open a new thread at https://community.oracle.com/tech/apps-infra/categories/oracle_linux to discuss that specific issue.

    In the mean time, we can go ahead and try to move on to your SQL Dev issue. But you'll have to go back to the vm console for a bit. Log on to the console as user 'oracle', and show me the output of 'lsnrctl status'. Will likely need a screen shot to do that, unfortunately. There may be a way to copy text from the vm console, but since I very rarely use the console myself, I've never needed to learn how.

    But even when we get SQL Dev working, please don't abandon the ssh issue.

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 91 Blue Ribbon
    edited Sep 3, 2021 7:06AM

    Hi @EdStevens ,

    I get this

    Even I am not able to find a listener.ora file in $ORACLE_HOME/network/admin/ as stated in the google search but I can find the file in $ORACLE_HOME/network/admin/samples/

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Sep 3, 2021 5:11PM

    The listener.ora file in the 'samples' directory is just that -- a sample. It is not actually functional. If there is no listener.ora, the listener will be quite happy to start, using all default values. This is actually quite common.

    The error simply means the listener is not started. You can start it from the command line, logged on as 'oracle'.

    oracle:cdb$ lsnrctl status
     
    LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-SEP-2021 12:05:30
     
    Copyright (c) 1991, 2019, Oracle. All rights reserved.
     
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
     TNS-00511: No listener
      Linux Error: 111: Connection refused
     
    2021-09-03 12:05:30
    oracle:cdb$ lsnrctl start
     
    LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-SEP-2021 12:05:34
     
    Copyright (c) 1991, 2019, Oracle. All rights reserved.
     
    Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
     
    TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    Log messages written to /u01/app/oracle/diag/tnslsnr/vbol83-02/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbol83-02.localdomain)(PORT=1521)))
     
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                    LISTENER
    Version                  TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    Start Date               03-SEP-2021 12:05:34
    Uptime                   0 days 0 hr. 0 min. 0 sec
    Trace Level              off
    Security                 ON: Local OS Authentication
    SNMP                     OFF
    Listener Log File        /u01/app/oracle/diag/tnslsnr/vbol83-02/listener/alert/log.xml
    Listening Endpoints Summary...
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbol83-02.localdomain)(PORT=1521)))
    The listener supports no services
    The command completed successfully
     
    2021-09-03 12:05:34
    oracle:cdb$
    

     

    When the listener first starts, it will, as shown above, report supporting no services. It will take a minute or two for the database to attempt to register with the listener, after which the listener will report the services:

    oracle:cdb$ lsnrctl status
     
    LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-SEP-2021 12:06:30
     
    Copyright (c) 1991, 2019, Oracle. All rights reserved.
     
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                    LISTENER
    Version                  TNSLSNR for Linux: Version 19.0.0.0.0 - Production
    Start Date               03-SEP-2021 12:05:34
    Uptime                   0 days 0 hr. 0 min. 55 sec
    Trace Level              off
    Security                 ON: Local OS Authentication
    SNMP                     OFF
    Listener Log File        /u01/app/oracle/diag/tnslsnr/vbol83-02/listener/alert/log.xml
    Listening Endpoints Summary...
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbol83-02.localdomain)(PORT=1521)))
    Services Summary...
    Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "bb3e427449ea18e7e053c900a8c06e86" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "cdb" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "cdbXDB" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "pdb01" has 1 instance(s).
     Instance "cdb", status READY, has 1 handler(s) for this service...
    The command completed successfully
     
    2021-09-03 12:06:30
    oracle:cdb$
    

     

    While we are at it, we should go ahead and check that the database itself is started, which I doubt.

    oracle:cdb$ ps -ef | grep pmon
    oracle     1861      1 0 Aug25 ?       00:01:02 ora_pmon_cdb
    oracle    23267  23169 0 12:06 pts/0   00:00:00 grep --color=auto pmon
     
    2021-09-03 12:06:55
    oracle:cdb$
    

     

    Here we are using 'ps' to list all running processes, and piping the output to 'grep' to filter out only those processes that include the literal 'pmon' in their name. All oracle databases will (if they are running) have a background process called 'ora_pmon_xxx', where 'xxx' is the name of the database instance. Thus, this will show all of the databases that are running.

    Also note that neither the database nor the listener will start 'automagicly' on server startup. You either have to set up a script that is invoked on startup or start them yourself. I prefer the latter.

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 91 Blue Ribbon

    Hi @EdStevens ,

    You are correct, after some time, I get the correct results as stated using lsnrctl status as shown below and also I have added the output for the last command

    [[email protected] ~]$ lsnrctl status
    
    
    LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 04-SEP-2021 14:02:09
    
    
    Copyright (c) 1991, 2021, Oracle.  All rights reserved.
    
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
    Start Date                03-SEP-2021 12:43:26
    Uptime                    1 days 1 hr. 18 min. 43 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.localdomain)(PORT=1521)))
    Services Summary...
    Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s).
      Instance "cdb1", status READY, has 1 handler(s) for this service...
    Service "ca2606ef7ec53b3ce055000000000001" has 1 instance(s).
      Instance "cdb1", status READY, has 1 handler(s) for this service...
    Service "cdb1" has 1 instance(s).
      Instance "cdb1", status READY, has 1 handler(s) for this service...
    Service "cdb1XDB" has 1 instance(s).
      Instance "cdb1", status READY, has 1 handler(s) for this service...
    Service "pdb1" has 1 instance(s).
      Instance "cdb1", status READY, has 1 handler(s) for this service...
    The command completed successfully
    [[email protected] ~]$  ps -ef | grep pmon
    oracle    6630     1  0 13:49 ?        00:00:00 ora_pmon_cdb1
    oracle    8876  8717  0 14:03 pts/0    00:00:00 grep --color=auto pmon
    

    Let me know what steps to be taken next to proceed?