Tomcat with tnsname

Oracle Thin JDBC connection to a TNS alias (tnsname)

1. Log in as user that owns tomcat

2. Shutdown tomcat if running

3. export this env
export JAVA_OPTS="-Doracle.net.tns_admin=/u01/oracle/product/10.2.0/network/admin"

5. Make sure tomcat owner is able to read file sin the above TNS directory

4. Startup tomcat

5. DB connection using tns should work

Note:

• Above worked for me on 10.2.0.1.0 database with, tomcat-5.5.20 (Linux) and jdk1.6.0_12 (May 2013)


• Above worked for me on 11g database with, tomcat-7.0.40 (Win) and 1.7.0_21 (May 2013)


• Similar to normal thin connection ("jdbc:oracle:thin:@nidevdb", username, password)
  
• Some docs / sites also say you can use below, instead (i could not get it to work).

System.setProperty("oracle.net.tns_admin", "/u01/oracle/product/10.2.0/network/admin");


more info, see book:
Book: Oracle® Database JDBC Developer's Guide (11g Release 2 (11.2))
Chapter: 8 Data Sources and URLs

Data Pump option

Simple Datapump create directory dump_dir as '/u11/oracle/exp_cust'; create directory log_dir as '/u11/oracle/exp_cust/logs'; grant read,write on directory dump_dir to CUST_DUMMY_STAGE; grant read,write on directory log_dir to CUST_DUMMY_STAGE; -- Export expdp CUST_DUMMY_STAGE/nidb schemas=CUST_DUMMY_STAGE dumpfile=dump_dir:expdp_09FEB2013_CUST_DUMMY_STAGE_%U.dmp logfile=log_dir:full1.log_09FEB2013_expdp_CUST_DUMMY_STAGE.log parallel=4 filesize=10G -- Import impdp system schemas=CUST_DUMMY_STAGE dumpfile=dump_dir:expdp_09FEB2013_CUST_DUMMY_STAGE_*.dmp logfile=log_dir:full1.log_09FEB2013_impdp_CUST_DUMMY_STAGE.log parallel=4 -- with remap / fromuser-touser impdp system schemas=CUST_DUMMY_PROD remap_schema=CUST_DUMMY_PROD:CUST_DUMMY_STAGE dumpfile=dump_dir:cust_dummy_prod_201305182300_01.dp logfile=dump_dir:log_imp_cust_dummy_prod_201305182300_.log parallel=4