Saturday, December 27, 2014

How to Connect to a Database through SQL Developer in MS Window

Prerequisites:

  • Oracle SQL Developer installed on the machine. If not installed already then download it from Oracle website and install. Note that SQL Developer requires Java JDK already installed on the machine.
  • The database should be up and running

Step 1: Setting TNS_ADMIN environment variable

TNS Admin (TNS_ADMIN) environment variable should be set to the directory where tnsname.ora file resides i.e C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN in our case
To setup this variable:
  1. Open the My Computer properties (Start > My Computer - Right Click and choose properties), Advance System Settings, Environment Variables. 
  2. Then create a new system variable with variable name as TNS_ADMIN and value as C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN. Note that this the oracle home might be different in your case depending upon how you have installed the Oracle Database Application.

Step 2: Updating tnsnames.ora and listener.ora files

Both the tnsnames.ora and listener.ora should have the entry for SID or Database Name to establish connection with database.

PeopleSoft HRMS Online Training

Lets say the SID for our database is HRDEMO, this is how the tnsnames.ora and listener.ora located under \NETWORK\ADMIN will be updated (highlighted in red):

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

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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

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

HRDEMO =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = HRDEMO)
    )
)



listener.ora

# 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 = HRDEMO)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
    ) 
  )

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

ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\dbhome_1
DIRECT_HANDOFF_TTC_LISTENER=OFF


Step 3: Restart the TNS Listener (OracleOraDb11g_home1TNSListener)

Open the Windows Services utility from Start > Run > services.msc and start the TNS Listener if its not started already. If already started, then stop and start back again.

Step 3: Establishing Connection through Oracle SQL Developer


  1. Start the database HRDEMO, to know how to start the database visit this article Connecting and Starting the Database through sqlplus Command
  2. Launch the SQL Developer by executing the binary from <SQL Developer Home>\sqldeveloper.ext.
  3. Open the new connection window by clicking on + sign on top left as shown below:Creating a New Connection in SQL Developer
  4. Enter the Connection Name HRDEMO, User Name - SYSADM, Password - SYSADM. Select the option 'Save Password', Role - Default, Connection Type - TNS. Finally select the Network Alias - HRDEMO. Note that SYSADM is the Access ID that we created while completing the database setup.
  5. Click on Test Connection, if the connection was established then the status on bottom left of this window will show as 'Success', if not then error message will be thrown. Click on Save to save the connection.
  6. If the connection was successful then click on Connect which will establish connection with the database HRDEMO and open the SQL Editor to write/execute SQL queries on this database.

PeopleSoft HRMS Online Training

4 comments:


  1. Thanks for giving Good Example.
    Fantastic article, Viral. Very well written, clear and concise. One of the best links explaining one to many and hierarchy in Peoplesoft DBA. Thanks a lot.It is uaefull to me and my training Ithubonlineonlinetraining center.

    ReplyDelete
  2. This is great information about people soft admin this is useful to now the people soft admin and help to the online training class peoplesoftadminonlinetraining

    ReplyDelete
  3. Thanks for giving Good Example.
    Fantastic article, Viral. Very well written, clear and concise. One of the best links explaining one to many and hierarchy Peoplesoft DBA. Thanks a lot.It is uaefull to me and my training Ithubonlinetraining center.

    ReplyDelete
  4. Thanks for giving Good Example, It is very useful to us.
    Fantastic article, Viral. Very well written, clear and concise. One of the best links explaining one to many and hierarchy Microstrategy. Thanks a lot.It is uaefull to me and my training Microstrategyonlinetraining center.

    ReplyDelete