[Webinar] Streamline your web hosting managementRegister Today


Access Oracle Database remotely..

Posted on 2003-03-09
Medium Priority
Last Modified: 2007-12-19

I have installed and running Oracle 9i database on Windows XP OS. I can also access the database over the LAN(Local Area Network). How can I configure to access the same database remotely over the internet. What type of configuration and tools will be required for it. Can I access to the same database using SQL*Plus remotely. I need to know the steps to work on it..


Question by:obaig
LVL 48

Expert Comment

ID: 8101067
How to Enable Remote Access with ORAPWD on Oracle Parallel Server    platform:  0 unix generic  component:  RDBMS -- OPS  comp vsn:  7.x    Questions:  how to set up the password file for sysoper/sysdba?  where does the file used for orapwd need to go?  do you need a copy on each  machine?    Example of error when not set up correctly:    SVRMGR> grant sysdba to scott;  ORA-01994: GRANT failed: cannot add users to public password file  Solution Description:  =====================  How to Enable Remote Access with ORAPWD on Oracle Parallel Server    Refer to the manual:  Oracle7 Server Documentation Addendum Release 7.1 for a  complete reference on ORAPWD (Chapter 5 Database Connection Security).    To enable remote access, create a password file:    1) run 'orapwd' to create the password file  2) set the init.ora praameter 'remote_login_passwordfile' to 'exclusive' or  'shared'    For Parallel Server:      Assuming that each node has its own ORACLE_HOME on its local hard disk and a  unique ORACLE_SID, you would need to configure  this for each Node.    TO DETERMINE IF REMOTE ACCESS HAS BEEN ENABLED:    Example (here it has not been configured):    %svrmgrl  SVRMGR> connect internal    SVRMGR> show parameter remote_login_passwordfile  NAME                                TYPE    VALUE  -------------------- ------- ------------------------------  remote_login_passwordfile      string  NONE     SVRMGR> select * from v$pwfile_users;  USERNAME   SYSDB SYSOP  ------------------------------ ----- -----  0 rows selected.    TO CONFIGURE A REMOTE ACCESS PASSWORD FILE    1) run 'orapwd' to create the password file    Example:  % orapwd file=/u01/oracle/V722/dbs/orapwV722 password=letmeon entries=1    where /u01/oracle/V722 is ORACLE_HOME    which creates a file like this:  -rwSr-----   1 oracle dba     6144 Mar 18 10:51 orapwV722    2) set the init.ora parameter 'remote_login_passwordfile' to 'exclusive' or  'shared'    Example:  % vi init<SID>.ora  remote_login_passwordfile=exclusive           3) Grant SYSDBA or SYSOPER to a user    Example:    %svrmgrl  SVRMGR> connect internal    SVRMGR> show parameter remote_login_passwordfile  NAME                    TYPE    VALUE  -------------------- ------- ------------------------------  remote_login_passwordfile        string  EXCLUSIVE     SVRMGR> select * from v$pwfile_users;  USERNAME  SYSDB SYSOP  ------------------------------ ----- -----  INTERNAL        TRUE  TRUE  SYS                            TRUE  TRUE  2 rows selected.    SVRMGR> grant sysdba to scott;  SVRMGR> disconnect  SVRMGR> connect scott/tiger as sysdba;  SVRMGR> {shutdown and startup can be entered}

Accepted Solution

Datamonkey earned 240 total points
ID: 8101634
schwertner, if you're going to copy and paste oracle notes please at least spend a few seconds making sure it's readable. It would also be helpful if it answered the question and wasn't 8 years old (Oracle 7.1 Documentation Addemdum Release 7.1, please I thought I'd seen the last of that a few years ago).

The question is how do I give access over the internet, not how do I give remote access to sysdba users.

To answer the question:
The problem is always that you're more than likely to have a firewall between your network and the internet outside, that means that you need to find a way for data to get through that.
The best way to make data available on the internet is to do it through an application of course. Run a webserver on your side of the firewall, poke a hole in the firewall at port 80 so http traffic can flow between it and the outside world has access to your webserver. Because the webserver is in your network any webapplication that runs from it can connect to the database in exactly the same way as normal clients do.
If you really want to give people on the outside world direct sqlnet access to your database it becomes slightly more tricky but there are quite some options to do this.
There is actualy a good oracle note (125021.1) I've just before about this on Oracle metalink. In stead of rewritting all the information I've copied a part of it here:

====<start quotes from note 125021.1>===========

Solution: Firewall Vendor

The first solution is to contact the firewall vendor and see if they have an
upgrade to allow for Oracle connectivity with OS port redirection. If the
firewall software can be upgraded, it is the best solution to follow.

Solution: Oracle Connection Manager

The second solution is available from Oracle Net8 (8.0.x and above), and
requires the configuration of Oracle Connection Manager (CMAN) to allow the
clients to connect through a firewall - <Note:2077721.6> discusses CMAN in
detail including configuration examples). CMAN is an executable that allows
clients to connect despite a firewall being in place between the client and
server. CMAN is similar to the Listener i that it reads a CMAN.ORA
configuration file, which contains an address that Oracle Connection Manager
listens for incoming connections, usually default ports of 1610 or 1630. CMAN
starts similar to the Listener and will enter a LISTEN state (see netstat). The
Oracle client must be Net8 or above and have the following in the TNSNAMES.ORA
cmantest =
   (description =
     (address_list =
       (address =                 <- first address is to CMAN        
         (host=hostname or ip of cman)
       (address=                  <- second address is to Listener
         (host=hostname or ip of listener)
     (connect_data = (sid =  sidname))
     (source_route = yes)         <- This tells the client that it is using
    )                                CMAN and it must take the first two
                                     addresses listed.
When the client contacts Oracle Connection Manager, CMAN completes the
connection with the second address the client brought with it. The second
address will point to the host machine where the listener is running. CMAN
then uses that address to 'pass' the connection to the Listener and the
connection to the database is established. All redirection takes place between
CMAN and the database, therefore the Oracle Connection Manager installation
should be placed after the firewall but before the database.
Several documents exist in MetaLink (http://metalink.oracle.com) that explain
Oracle Connection Manager and provide more detailed configurations. This note
serves to explain the connection process with firewalls, not to go into detail
regarding Oracle Connection Manager.


A third solution for Windows NT/2000 servers is to set USE_SHARED_SOCKET to
TRUE in the Windows registry. This allows the OS to share
the listening port (e.g. 1521) and clients then remain  using that same port
when connecting to the database - no port redirected takes place.  A pitfall of
this option is all connections will stay on the Listener port and if the
Listener is stopped or restarted, all connections are severed from the database.
Unix platforms can have problems connecting to a database through firewalls if
they have implemented Multi-Threaded Server (MTS). MTS Dispatchers will
redirect connection ports like the Windows Platforms mentioned above.

Solution: Setting MTS ports

A workaround for this is to specify the port in the MTS parameters of the
INIT<SID>.ORA file.  This allows the Dispatcher to
use the port specified and will not select a random port.  Ensure the
user-defined MTS port/s are opened on the firewall.  The following example
shows the ports set to 2450 and 3125.  Set these parameters according to your
individual systems.  This solution will also work for Windows.

Opening ports 2450 and 3125 on the firewall will allow clients to connect to

Using SSL will cause Port redirection. The workaround is to select and set the
ports using MTS in the INIT<SID>.ORA .
=====<end quotes from note 125021.1>============

i hope this is clear and helps your problem

Author Comment

ID: 8113326
thanks to Datamonkey, but I don't know how to install and configure Connection Manager in case using the Web Server on different host or the same host of the Oracle Server database.. I tried to search for it.. but didn't get anthing useful..


LVL 13

Expert Comment

ID: 9999324
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: Datamonkey {http:#8101634}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question