Oregon State University

Oracle Connections to Administrative Databases

This information is primarily for developers and end users who create reports using Oracle software, not standard users.

During the Thanksgiving break of 2016, the migration of administrative databases will take place. After this migration, customers will no longer connect to each individual server (per service), but instead connect to a single server cluster which hosts each service. This process is intended to be as seamless to the customer as possible. To assist in this transition this document will attempt to answer most of the common questions and provide technical details to your technical staff. If you experience issues with this service; please contact the Service Desk to receive assistance.

General Information:

Connections to the campus administrative databases are facilitated by the Oracle client installed on your Windows computer. This client uses a connection file (TNSNAMES.ORA) to store the names, IP address, and service details about each administrative database. This file is what needs to be updated post-Thanksgiving in order for your connections to continue working. Some IT Groups on campus are updating these files automatically for their customers during the migration but if you do run into problems please contact the Service Desk for assistance.

Oracle Client and TNSNAMES.ORA Information:

In general the Oracle client is installed in the C:\Oracle\ folder on your Windows computer. The TNSNAMES.ORA file is generally found in the C:\Oracle\Version\Client\Network\Admin folder (your location may vary slightly depending on the Oracle version installed). This file stores all of the connections that your Oracle client can make.

As a precaution we strongly suggest that you back up the TNSNAMES.ORA file before attempting any of the upgrade steps or modifications in this document.

Backing up your TNSNAMES.ORA File:

  1. Click on Start, Run
  2. Type in C:\ and press OK.
  3. In the window that opens; double-click on Oracle.
  4. Browse down the folder structure until you get to the Admin folder.
  5. Right-Click on the TNSNAMES.ORA file and select Copy.
  6. Right-Click on your desktop and select Paste.

Updating your TNSNAMES.ORA File:

Once you have safely backed up your TNSNAMES.ORA file the upgrade process can begin. If you have difficulties with the upgrade you can always revert to the backed up copy.

Obtain the updated TNSNAMES.ORA File:

  1. Browse to \\Software.oregonstate.edu\Software\Oracle\11\TNSNAMES\OSU
  2. Copy the TNSNAMES.ORA file and proceed to the next step.

Upgrading your TNSNAMES.ORA File:

  1. Please Note:This should only be completed AFTER November 27, 2016.
  2. Browse to your C:\Oracle folder and locate the ADMIN folder.
  3. Delete or Rename the existing TNSNAMES.ORA file.
  4. Copy the newly downloaded/obtained TNSNAMES.ORA file into the ADMIN folder.
  5. Upgrade Complete. – You should be able to access your connections as normal now.

Once your file has been updated, your client should be fully functional. There are no more steps to be completed. If the upgrade didn’t solve your issue or you continue to experience connection issues please contact the Service Desk or refer to the Advanced Technical information listed below.

Advanced Technical Information:

Remember: Host names in existing files can vary and may not only be db1 or db2. Look for host names ending with ucsadm.oregonstate.edu. The new cluster name should be consistent for all databases. When editing an existing TNSNAMES.ORA file is needed, please use a basic text editor like Windows Notepad to edit the file.

JDBC connection strings:

Before - SID format After - Service Name format
jdbc:oracle:thin:@[HOST][:PORT]:SID jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
jdbc:oracle:thin:@db1.ucsadm.oregonstate.edu:1521:prod jdbc:oracle:thin:@//ecs-cluster.ucsadm.oregonstate.edu:1521/qry_prod.ecs.osu
jdbc:oracle:thin:@banner.ucsadm.oregonstate.edu:1521:prod jdbc:oracle:thin:@//ecs-cluster.ucsadm.oregonstate.edu:1521/qry_prod.ecs.osu
jdbc:oracle:thin:@dwprod.ucsadm.oregonstate.edu:1521:dwprod jdbc:oracle:thin:@//ecs-cluster.ucsadm.oregonstate.edu:1521/qry_dwprod.ecs.osu

tnsnames.ora Updates Example:

Ex. Before

B1prod =
B4  (ADDRESS=(PROTOCOL=TCP)(Host=db1-oravip.ucsadm.oregonstate.edu)(Port=1521))
B5  (ADDRESS=(PROTOCOL=TCP)(Host=db2-oravip.ucsadm.oregonstate.edu)(Port=1521))
B6  (CONNECT_DATA=(SERVICE_NAME=prod.banner.osu))
B7 )

  • B3 - Load balancing will now be automatic, this line can now be removed
  • B4 - The host name has changed from the single server address to the server cluster address (seen in A3)
  • B5 - Each service connected to is now a single cluster. Duplicate connection names are no longer needed.
  • B6 - Service name has changed since the administrative databases migration (seen in A4)
Ex. After

A1prod =
A3  (ADDRESS=(PROTOCOL=TCP)(Host=ecs-cluster.ucsadm.oregonstate.edu)(Port=1521))
A4  (CONNECT_DATA=(SERVICE_NAME=qry_prod.ecs.osu))
A5 )

  • A3 - The host name has changed from the single server address to the server cluster address (changed from B4 & B5)
  • A4 - Service name has changed since the administrative databases migration (changed from B6)

General Service Name Changes:

Database Server-

Before After Description
db1 & db2 ecs-cluster.ucsadm.oregonstate.edu
PROD qry_prod.ecs.osu Production Banner-related schemas.
DEV2 qry_dev2.ecs.osu DEV2 Banner-related schemas.
DEVL qry_devl.ecs.osu DEVL Banner-related schemas.
DW PROD qry_dwprod.ecs.osu Production data warehouse.
DW DEVL qry_dwdevl.ecs.osu DEVL data warehouse.
db4 & db5 ecs-cluster.ucsadm.oregonstate.edu
OS PROD qry_osprod.ecs.osu Production application schemas.
OS DEV2 qry_osdev2.ecs.osu DEV2 application schemas.
OS DEVL qry_osdevl.ecs.osu DEVL application schemas.
db7 & db8 ecs-cluster.ucsadm.oregonstate.edu
OD PROD qry_odprod.ecs.osu Production ODS schemas.
OD DEV2 qry_oddev2.ecs.osu DEV2 ODS schemas.
OD DEVL qry_oddevl.ecs.osu DEVL ODS schemas.

Need help?

Contact the Service Desk at 541-737-8787

We want your feedback!

Helpdocs are made just for you, so please tell us how we can make this information more clear and accessible. The more feedback that you can provide, the more we can improve our services to you!

Contact Info

Copyright ©  2018 Oregon State University