I've been searching online for a solution to my problem without any luck. I'm trying to connect to a remote server (~ 700 km of distance), but since I'm new in this Database administration world I'm afraid of messing this up. So I've been practicing with a VirtualBox machine (Windows 7) on my Ubuntu real Machine for almost a week and nothing seems to work.
This are the steps that I'm following to achieve the connection:
ON SERVER SIDE:
- Start database:
conn sys as sysdba
. - Start LISTENER:
lsrnctl start
on cmd. - listener.ora:
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\xe)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\xe)
(PROGRAM = extproc)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
tnsnames.ora:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ON CLIENT SIDE:
tnsnames.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
- Start oracle client:
sudo service start oracle-xe start
- Try to connect:
conn SYS/pass@HostName
(tried with different users and host IP)
Please help!
Best Answer
Your connection string is wrong. With easy connect you need to provide a hostname and servicename/SID. In your case you would provide
sqlplus user/"SomePass"@HOSTNAME:$PORT/servicename
If you want to configure your connection in the
tnsnames.ora
, you can connect to it usingsqlplus user/"SomePass"@TNSALIAS
I actually don't understand the part of your question with your local Oracle XE installation. If you only want to connect to a remote database, you don't need it. All you need is an oracle Client (Instant Client with sqlplus will do fine), or a tool like SQLDeveloper. If you are unfamiliar with Database administration a tool like SQLDeveloper might make life a little bit easier.