Oracle 12C VM & SqlPlus InstantClient setup

VM Setup:

Really handy helpful tips: http://www.thatjeffsmith.com/archive/2014/02/introducing-the-otn-developer-day-database-12c-virtualbox-image/

VM Image download: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Once you downloaded 5.6GB VM image, use import in Oracle Virtual Box. It might 2GB memory and 1GB disk space. It has default port forwarding setup. After import, start the VM and you can test your application with SQLDeveloper in VM itself (Already configured). 

 The connection params: 
     Username: system
  Password: oracle
  Hostname: localhost or 127.0.0.1
  Port: 1521
  Service name: orcl or cdb1


  NOTE: PDB1 setting didnt work for me (on March 6, 2015)
     PDB1 credentials suggested
  Username: HR
  Password: oracle
  Hostname: localhost or 127.0.0.1
  Port: 1521
  Service name: PDB1


   In VM service, you can use sqlplus to access Oracle DB. Use 'sqlplus sys as sysdba' or 'sqlplus system@orcl' with password 'oracle' to access.

Challenge: 
   Getting alert for password expiry in 7 days; While trying to password change with 'alter user system container=all identified by oracle;', getting following error: 'SQL Error: ORA-65050: Common DDLs only allowed in CDB$ROOT'.

Fix:
 In SQLDeveloper, login using system/oracle, 127.0.0.1:1521, service name: cdb1, run `alter user system identified by oracle container=all;`. It fixes the issue.

SQLPlus in local machine

Ref: http://www.talkapex.com/2013/03/oracle-instant-client-on-mac-os-x.html

Download all instantclient zip files. Unzip them and move the 'instantclient_11_2' into '/oracle' folder. Create '/oracle/instantclient_11_2/network/admin/tnsnames.ora' and following lines

localvm =

 (description=

   (address_list=

     (address = (protocol = TCP)(host = 127.0.0.1)(port = 1521))

   )

 (connect_data = (server=DEDICATED)(service_name=orcl))

)

Note: If you have to map more than one tns, don't seperate them with ','.

Set path and export following in ~/.bash_profile

#Oracle tools

ORACLE_HOME=/oracle/instantclient_11_2

export ORACLE_HOME

LD_LIBRARY_PATH=$ORACLE_HOME

export LD_LIBRARY_PATH

DYLD_LIBRARY_PATH=$ORACLE_HOME

export DYLD_LIBRARY_PATH

SQLPATH=$ORACLE_HOME

export SQLPATH

TNS_ADMIN=$ORACLE_HOME/network/admin

export TNS_ADMIN

PATH=$PATH:$ORACLE_HOME:.

export PATH

 Open new terminal. You can connect your vm Oracle 12C DB using sqlplus, by typing 'sqlplus system@//localhost:1521/orcl' or 'sqlplus system@localvm' with 'oracle' password.

More sqlplus help: http://dba.fyicenter.com/faq/oracle/Main-Features-of-SQL-Plus.html

SSH

 ConfigureVM network with (HOST) 127.0.0.1:2222 to GUEST 22 port. You can use 'ssh oracle@localhost -p 2222' to login with 'oracle' password.

For Ruby Dev:

Ref: https://coderwall.com/p/nvva7a/setup-oracle-instant-client-and-ruby-oci8-gem-on-mac

In terminal:

cd /oracle/instantclient_11_2

sudo ln -s libclntsh.dylib.11.1 libclntsh.dylib

sudo ln -s libocci.dylib.11.1 libocci.dylib

gem install ruby-oci8

Make sure gem installed without errors; Use 'irb' to check your connection

>require 'oci8'

>conn = OCI8.new('system', 'oracle', 'localvm') 

#OCI8.new('<user>', '<password>', '<tnsmap>')

>conn.exec("select 'string' from dual") {|r| puts r}

#prints string

SQL Developer:

To load tnsnames.ora

Preference -> Database -> Advanced -> Tnsnames Directory (Browse and load tnsnames.ora directory)

NLS (National Language Support)

Preference -> Database -> NLS -> Change setting or keep it default by selecting 'skip NLS Settings'