Windows 8.1 VM - Oracle Virtual Box

Build VM

1. Collect your licensed windows iso [Used Window 8.1 iso image. Downloaded 3.65GB file locally]
2. Start Oracle Virtual Box
3. Click New in Oracle Virtual Box window
4. Name: 'Windows_8_1'
5. Type: Select 'Microsoft Windows' 
6. Version: Pick 'Windows 8.1 (64 bit)'
7. Memory size: pick 2048 [It is the default value picked for Windows 8.1. So leave it]
8. Hard drive: Check 'Create a virtual hard drive now'
9. Click 'Create'
10. In next window, leave all default: File size: 25GB, Hard disk file type: 'VDI (VirtualBox Disk Image)', Storage on physical hard drive: 'Dynamically allocated'
11. Click 'Create' in this window too
12. Now, you can see 'Windows_8_1' vm image created with 'Powered Off' state
13. Start the 'Windows_8_1' vm
14. In popup window, pick the iso file downloaded in first step and click 'Start'
15. It will go like regular Windows 8.1 installation
16. Pick zonal setting - I left as it is!
17. Click 'Install now' once prompted
18. 'Accept' the license terms in 'Windows Setup' and click 'Next'
19. Click on 'Custom: Install Windows only (advanced)'
20. Select default 25GB harddrive and click 'Next'
21. Let the process continue
22. Let it restart and restart
23. Once it is ready, give your pc name 'vm-pc'
24. Use 'Express setting' on next level
25. 'Create local account'
26. Enter your credentials and Password/hints
27. Finish - Your VM is ready. 
28. Windows 8.1 does some app installs and entertain you with some screen color changes. Please don't fall that cleverness. Still it is windows!

Export for future use

1. In Oracle VirtualBox, click File -> Export Appliance
2. Select 'Windows_8_1' from 'Virtual machines to export' list, then click 'Continue'
3. File: Update with correct file location and file name [Pick ova format in this window]
4. Format: Pick OVF 2.0 
5. Check 'Write Manifest file'
6. Click 'Continue'
7. In new dialog, check the options and leave as it is, click 'Export'
8. This creates the VM image for future imports
9. You can import the VM image to create new VM

Guest Additions

Ref: https://ludwigkeck.wordpress.com/2012/11/03/virtualbox-guest-additions-in-a-windows-8-virtual-machine/

Guest additions needed for seamless mode. Here is the steps I could use;

1. In VirtualBox VM: Devices menu, select 'Insert Guest Additions CD Image (Host+D)'. It suppose to popup some dialog, which didnt show up. 
2. Assume you didnt see any popups, in Windows 8.1 VM running instance, go to 'Explorer' and CD Drive
3. Double click on VBoxWindowsAdditions(.exe) file and follow the basic installation with default setup
4. At end of installation, select 'Reboot'
5. After rebooting, you can use normal VM and Seamless mode too.

Connecting To Local Domain

1. Right click on My Computers [For Windows 8.1, right click on desktop; In popup dialog click 'Change Desktop Icon'; In the following 'Desktop Icon Settings' dialog, check 'Computer' to show 'My Computer' in desktop. Pick other icons if needed], then select 'Properties'.
2. In 'System' dialog window, click 'Advance system settings'.
3. In 'System Properties' dialog, select 'Computer Name' tab
4. Click the 'Change' icon to change computer domain/workgroup
5. In bottom, 'Member of' section, check 'Domain' radio button, then fill the your local domain name [Check with your IT]
6. Depends your domain setup, you may prompted to enter user id and password; and possible restart too.
7. After restart, instead of login as default system user, click '<-' left arrow and select 'Other user'
8. Now, you see user name and password field and 'Sign in to:' DOMAIN name
9. Use your local domain account to login; Now your new domain profile is created in VM!

Oracle PL/JSON - Installation

Had good great experience with PL/JSON for a year; Here the steps I followed to install PL/JSON into my oracle 12C DB.

Installation

1. Got PL/JSON from GitHub using git clone
2. Opened 'install.sql' file in SQLDeveloper and selected the DB connection to run the release
3. Used 'Run Script' option from SQLDeveloper. 
4. Watched 'Script Output' for no errors

Test

1. Opened another 'SQL WorkSheet' and copy and pasted the Example-1 and executed the Script. 
2. In Script output, verified the JSON data print.

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'

AddEventListener Vs Bind/On

After a long time, I had to AddEventListener and native javascript to do file drag and drop. jQuery bind or on methods to adding evening listeners are giving files names dragged with the drag event. Might be future challenge.

Experimental code:
Ref 1: http://www.sitepoint.com/html5-file-drag-and-drop/
Ref 2: https://github.com/merty/simple-file-uploader

<html >

<head>

<title>AddEventListener Vs Bind/On</title>

<style>

#filedrag

{

  display: none;

  font-weight: bold;

  text-align: center;

  padding: 1em 0;

  margin: 1em 0;

  color: #555;

  border: 2px dashed #555;

  border-radius: 7px;

  cursor: default;

  height: 140px;

}

#messages

{

  padding: 0 10px;

  margin: 1em 0;

  border: 1px solid #999;

}

</style>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>

<script>

function event_arrestor(evt) {

  console.log( "%s started", evt.type);

  evt.stopPropagation();

  evt.preventDefault();

}

function process_drop(evt) {

  event_arrestor(evt)

  var files = evt.dataTransfer.files;

  var count = files.length;

  console.log("Totally " + count + " files were dropped");

  console.log(files);

  

  for (var i = 0, f; file = files[i]; i++) {

    var msg = "<p>File information: <strong>" + file.name +

      "</strong> type: <strong>" + file.type +

      "</strong> size: <strong>" + file.size +

      "</strong> bytes</p>";

    console.log(msg);

    $('#messages').append(msg);

  }

}

$(document).ready(function() {

  var filedrag = document.getElementById('filedrag');

  filedrag.style.display = "block";

  filedrag.addEventListener('dragenter', event_arrestor, false);

  filedrag.addEventListener('dragover', event_arrestor, false);

  filedrag.addEventListener('dragexit', event_arrestor, false);

  filedrag.addEventListener('dragleave', event_arrestor, false);

  filedrag.addEventListener('drop', process_drop, false);

  

  //$('#filedrag').css({display: "block"});

  // $('#filedrag').bind('dragenter', event_arrestor, false);

  // $('#filedrag').bind('dragover', event_arrestor, false);

  // $('#filedrag').bind('dragexit', event_arrestor, false);

  // $('#filedrag').bind('dragleave', event_arrestor, false);

  // $('#filedrag').bind('drop', event_arrestor, false);

  

  // $('#filedrag').bind({

    // dragenter: event_arrestor,

    // dragover: event_arrestor,

    // dragexit: event_arrestor,

    // dragleave: event_arrestor,

    // drop: function(evt){

      // console.log("DROPPED");

      // process_drop(evt);

      // return false;

    // } 

  // });

  

  

});

</script>

</head>

<body>

<h1>AddEventListener Vs Bind/On</h1>

<div id="filedrag"> Drop files here</div>

<div id="messages">

  <p>Status Messages</p>

</div>

</body>

</html>

Docker - PostgreSQL Setup

Docker Installation: 

Ref: http://www.maori.geek.nz/post/boot_2_docker_how_to_set_up_postgres_elasticsearch_and_redis_on_mac_os_x

Execute following Commands in shell [Mac Os]

brew install boot2docker
boot2docker init boot2docker up
Important: Watch the output; Following lines are needed for setup
To connect the Docker client to the Docker daemon, please set:

    export DOCKER_HOST=tcp://192.168.59.103:2376

    export DOCKER_CERT_PATH=<dir>/.boot2docker/certs/boot2docker-vm

    export DOCKER_TLS_VERIFY=1

Setup:

 Execute the suggestion from `boot2docker up`

 And,  export DOCKER_IP=`boot2docker ip`

Run Postgres: 

Note: Change to desired password

docker run -it -e POSTGRES_PASSWORD=password -p 5432:5432 postgres

Stop Postgres:

Use 'Ctrl+C' to stop Postgrs server

Stop docker:

boot2docker stop

Rerun Docker/Postgres:

boot2docker up
Re-establish setup, if needed

docker run -it -e POSGRES_PASSWORD=password -p 5432:5432 postgres

Access:

pgAdmin3 - Use 'DOCKER_IP' address and 'postgres' user, password used in 'docker run'

For psql - With pgadmin3 installation

export PATH=$PATH:/Applications/pgAdmin3.app/Contents/SharedSupport

psql -h $DOCKER_IP -U postgres

Persistance

No persistence with above setup; Good for RoR Devels. 

Ref: https://registry.hub.docker.com/_/postgres/

Import/Export - Yak Shaving Experience

Quick:

1. You cant export data from Synonym view [Exports only sql desc of Synonym]
2. You cant create table using 'create table as' [It throws column name zero errpr
3. Collect view's desc of Synonym view
4. Export Synonym view data into csv file (SQLDeveloper is handy)
5. Create the table in destination DB using modified desc (from step 2)
6. Import csv file using SQLDeveloper - Created table -> right click -> Import (has csv option by default in SQLDeveloper 4.0.3.16)

TL;DR:

Stage 0: Intro

As developer environment isolated from other environments, some of live feed tables were copied to local database (Oracle 11g) as table, instead of accessing them using Synonym (view) -> DB Links. 

Almost 100K records needed to be copied to the dev DB. 

Stage 1: Create the table using 'create table as'

  Ref: http://dba.stackexchange.com/questions/60175/export-synonyms-content-to-tables

 Create table as failed with  'zero-length columns are not allowed'. 
 create table HUGE_TABLE NOLOGGING PARALLEL 4 as select * from syn_table@dblink;

Stage 2: Describe table

 Using Desc of synonym view shows only the synonym info. After trying many options, 'DESC syn_table' gave table/view description only where the real view/table exists. 

 Searched 'dba_objects' [Alternative: all_objects, usr_objects] to verify view existence. Describe table info needed some modification to create the table!

 So table is created.

Stage 3: Export data - insert option

 The SQLDeveloper has an option to export the 'Query Result' window [Right click to see the options]. First trial, I used the Format option as 'Insert', which creates the SQL file with insert queries. This failed in Mac Os (10.10.2) with error 'File ** was not opened because it exceeds the maximum automatic open size'. So, I had to go pagination

Stage 4: Pagination with ROWNUM

Ref:http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

 Important: The following query wont return any results. Worth reading the article!! 

      select *   from t  where ROWNUM > 1; 

 Finally, after careful document reading, I found a query which could help with pagination.

select * from (select rec.*, rownum rnum from (select * from items order by items_id asc) rec where rownum <= 10) where rnum >= 5;

Stage 5: Text file limitation

 When I tried to export 10K per file, I realized that only 469 rows can be exported with record size for the given table. It is because, every insert line includes column names too.

Stage 6: CSV file

  Simple common sense thought told me that removing columns names may double the number of rows can be accommodated in single export. While exploring the format options, such 'loader', 'csv', only option works smoothly was 'csv'. Tried with 1000 records exported to csv. 

Stage 7: Import CSV

  In SQLDeveloper, tables list, right clicking on table gives import option and it accepts csv file. And, 1000 records worked smooth.

Stage 8: Working option

 Tried to export all 100K records into single csv file and it went smooth; Above all, SQLDeveloper has nice and informative tool to import csv file. It showed the import progress in Message log.