accessing oracle with instant client and toad.

on server (oracle server)

1. ensure that listener is running, "C:\app\Administrator\product\11.2.0\dbhome_1\BIN\lsnrctl status", it use "C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora". the sample as below :  

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

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

ADR_BASE_LISTENER = C:\app\Administrator


my TNSNAME.ora

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.168.42.128)
    )
  )



2. check language on server with sqlplus

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select userenv('LANGUAGE') from dual;

USERENV('LANGUAGE')
---------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

3. ensure that your firewall on port 1521 is accessable from outside
4. unlock the user.
SQL> alter user hr identified by hr account unlock;

on client (instant client and toad)

1. download and install oracle instant client and toad. Ensure your architechture versions are same (toad 32 bit for instant client 32 bit, and so on, if you got error can not load dll, change to other version of instant client)
2. add to your system variable
"TNS_ADMIN"  value "C:\Users\Administrator\Desktop\instantclient-basic-win-x86-64-11.1.0.7.0\"
"NLS_LANG" value should same with your server language, there is "AMERICAN_AMERICA.AL32UTF8"
"Path" add value with ";C:\Users\Administrator\Desktop\instantclient-basic-win-x86-64-11.1.0.7.0\instantclient_11_1"
3. open your toad, add new connection with direct connection.

Read more ...