Oracle 11g – How to Access Database Remotely

oracle-11g-r2

I have oracle database on my computer that i am accessing through a java program using localhost:1521 more precisely using ojdbc7 and host String as "jdbc:oracle:thin:@localhost:1521:orcl". Everything is working fine but now I want to access same database through another computer with different internet connection. I read that I have to change listener.ora file and add ip address where localhost is.

Now I have tried using ip address of my computer which i have set to 192.168.1.5 in network connection and same in listener.ora file and created a virtual server in my router settings and set this same ip address and port there as well. Still when I try to connect to server computer's Internet IP(not 192.168.1.5) it states "TNS:no listener".

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    )
  )

Firewall is off. I have also tried using 127.0.0.1 and creating virtual server for that in router as well but didn't work. Oracle 11g is installed on both server and client's computer.

I have also tried using 0.0.0.0 ip in listener.ora file so it may listen to all ip's but that didn't work as well.

Can someone point me in the right direction.

Edit: Different internet connection means one is my home internet (ip:39.40.xx.1xx) other is my friends home internet (different ip) we live few miles apart and use same ISP. My computer is acting as server which is using Windows 10. Its firewall is disabled. I am assuming that my friend would use my internet IP address which would be forwarded to my computer (192.168.1.5)

Best Answer

Presuming port forwarding and network items are working as expected (can be checked with telnet host 1521).

Make sure that you restart the listener service.

Also connect to the database from your local machine and run the following commands:

ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.5)(PORT=1521))";

ALTER SYSTEM REGISTER;

Outside of that it looks good from a listener config side.

Related Solutions

Oracle 11g – Create Database fails

One of the key differences between these platforms is that a database in MySQL is a schema in Oracle. Since a schema in Oracle is also a user, the command you are looking for is CREATE USER. I highly recommend that you read the Oracle Concepts Guide before continuing. You may also find this documentation on MySQL>>Oracle migrations using SQL Developer useful. Here is the part relevant to your situation:

When migrating MySQL databases to Oracle, SQL Developer maps each MySQL database to a tablespace in Oracle. Database objects, such as tables, indexes and views are stored in the respective tablespaces and are referenced from the Oracle schema for the user that owns them.

From the error message perspective, the salient part to pull out is "ORA-01100: database already mounted". This indicates that a database has already been created and mounted. It may also be open. Here is an example reproducing your issue, opening the database (which may not be necessary if it is already open) and creating a django user.

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted


SQL> SELECT status FROM v$instance;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted


SQL> CREATE USER django IDENTIFIED BY "No More ORA-001100.";

User created.

You should also assign permissions and create a django tablespace setting it as the default tablespace for django.

Oracle DBCA utility: “Listener is not up or database service is not registered with it”

As far as I understand, there is a database on the machine. This database should register itself on the listener. It can do so when it knows where - on which port - the listener runs. In you case the listener is on port 15321. To enable the auto registration of the instance, you should make sure that the LOCAL_LISTENER parameter is filled with the correct port number in order to be able to contact the listener. For docu check Oracle® Database Reference 11g Release 1 (11.1)

. oraenv
[YOUR_ORACLE_SID]
sqlplus / as sysdba
-- if the instance appears not to be running:
startup
alter system set local_listener = '(ADDRESS = (PROTOCOL=TCP)(HOST=Myhost)(PORT=15321))';
-- to force immediate registration:
alter system register;

Now your database should be visible in the listener services list listener.ora should look like this - note the PORT =

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = 
      (ADDRESS = 
        (PROTOCOL = TCP)
        (HOST = Myhost)
        (PORT = 15321)
      )
    )
  )

check the results:

  lsnrctl services

Best Answer

Related Solutions

Oracle 11g – Create Database fails

Oracle DBCA utility: “Listener is not up or database service is not registered with it”

Related Question