Wednesday, 6 June 2018

New Developer day VM

Virtualbox 5.2.8 or above
for performance use 3GB RAM 2 CPU

New ish:

rest enabled sql
rest enabled jdbc sql username/password@http://...(to ORDS)

+18.1 ORDS SQLDeveloper SQLCL modeller APEX
Oracle Linux 7 update5 (yum updated from 7u3 iso)

executables now under applications->other
(only .txt and .html can be easily clicked on desktop with no popups)

readme.txt and readmeCopy.txt - the same - one sometimes gets half hidden in icon layout.

Same as last release + more recent software
any requests/what does not work let me know in the comments.

Friday, 4 May 2018

Things I worked around when setting up MIT kerberos and sqldeveloper over thin jdbc against Oracle Database:

Server used Oracle Linux 7 for Kerberos (yum -y install krb5-server krb5-libs)
and Oracle Database
Clients used Oracle Linux 7 and Windows both SQLDeveloper 18.1

MIT Kerberos / oracle thick driver went OK once I used global c## user.

use c## user:
SQL>  create user c##user1 identified externally as 'user1@...'

(non c## user did not work for me.).

Used sqldeveloper with jre included (on windows).

SQLdeveloper jdbc thick:
'just like sqlplus' requires Oracle Client or Instant Client (not sure (which/if both) has Kerberos).

Sqldeveloper thin kerberos configuration:

tools->preferences->database->advanced:  config file (krb5.conf) - eg (on my
linux client) /etc/krb5.conf   Credential cache file (on my linux client) eg
/home/oracle/mycache (not (Microft Windows) MSLSA or OSMFT)
- sqldeveloper thin driver does not read sqlnet.ora
- or currently recognize non file based cache.

On connection either:
1/Give principal (no ticket/cache required) give / get prompted for password - went OK.
2/Leave principal blank (and get it from cache):
Use kinit (from sqldeveloper on windows - from yum -y install krb5-workstation on Oracle Linux) to set up existing ticket:
 needed "kinit -A" (for addressless ticket on windows) (used kinit default cache location and pointed to default from sqldev) (i.e. -A to get around: incorrect net address)

(Also tried at the same time kinit -p -f (proxiable and forwardable, and setting non proxy java -D setting) after -A it worked.)

klist -f

Issues  - customers might not be using MIT Kerberos.
Workarounds: Often thick jdbc works 'just like sqlplus' to workaround thin jdbc issues. 

Wednesday, 24 January 2018

1/Cool stuff from Oracle (Developer Day VM related)

2/Showing off your Developer Day VM to remote users (one method only requiring ssh).

3/Do not 'yum update' 2017 Developer Day VM

1/Cool stuff from Oracle (Developer Day VM related)

Any trouble getting these working on your VM let me know. (unzip and go)
jdk-8u152-linux-x64.tar.gz (or later) (unzip in ~/java [might need to remove existing java in that directory] and go, check ~/.bashrc) - include REST JDBC - unzip and go - unzip and go (lower version already installed) (lower version already installed - but both can run on different ports/pdbs) 
- one line admin change to be  REST SQL (and REST JDBC server) note no longer prompts for sys user. (Would only have trouble running old and new if existing ORDS is installed on the CDB rather than pdb, which is not the case) existing should work (for start stop) (put in full path to new war) - initial configure/install  will change.

2/Showing off your Developer Day VM to remote users (one method only requiring ssh).

If your networking firewall etc are out of your control port 22 (ssh) probably still works.

For other people to access your VM for test / development purposes you can (obviously but I had forgotten) ssh port forwarding (for 9090 8080 8081 or 1521) - requires ssh login to host machine, for example for 1521:

Note the default is to keep the ssh login open (might have to if all else fails kill it to stop it).

1 ssh required (no sqlplus required) (login and password on host required)
// log in to host (desktop hosts IP my change)
bash-4.2$ ssh -p 22 user@___the_ip_address__
[need password for user]
>ssh -p 2222 oracle@localhost
[password oracle]
. oraenv -- ie setup oracle environment
orcl12c -- the default environment may have several
>sql system/oracle

2: ssh required (sqlcl required) (login and password on host required)
[IN ADVANCE] ssh -L 1521:localhost:1521 user@__the_ip_address__
[need password for user]
On your own machine using sqlcl sqlplus sqldeveloper etc locally and your local 1521 port: (port forward from a different port if that is in use)

sql system/oracle@localhost:1521/orcl

3/Do not 'yum update' 2017 Developer Day VM

Warning do not 'yum update' a 2017 developer day VM (if you do you will upgrade to Oracle Linux 7.4 and you guest desktop will not resize by mouse action (you can still resize by command line))

Tuesday, 4 April 2017

SQLDeveloper/SQLcl LDAP + url + 3rd party connection tricks

SQLDeveloper/SQLcl LDAP + url + 3rd party connection tricks:

SQLDeveloper LDAP look up only tested with (i.e. only works with) OID LDAP Very little code - issue is setup for testing non default setup (with different OID setups) before release.
Note look up is at configuration save time not at live connection create time.

Can be avoided:

Note you can use url based LDAP calls (connection type -> advanced) - awkward - the ldap server is queried at connect time (for host port etc).
Note there is a different syntax depending on whether you are using thick(oci/C) or thin(pure java) driver.
These 'refer to ldap by url' connections can be exported imported etc as normal.

Old semi related LDAP blog:

SQLCL(+connect command) LDAP;
ie set LDAPCON jdbc:oracle:thin:@ldap:__WHATEVER_YOU_WANT__  use #ENTRY# for whatever you want your lookup is called eg ORCL in connect system/manager@ORCL
LDAPCON can be shell/dos environmental variable. 
This is a very thin helper search and replace for the raw 'explicit ldap url' method.

Actually you can put in connect system/manager@[1]


Some 3rd party url used to work if classpath OK
That has been deemphasised not tested
I do not think we went out of our way to break it but not tested usually eventually means does not work: is a special case for getting 3rd party information into Oracle database (select on 3rd party side (limited to select i.e. more likely to work), insert on Oracle side).

Thank you for your time,
-SQLDeveloper Team

Monday, 3 April 2017

Kerberos and SQLDeveloper (without Oracle Client)

Hi Kerberos users,

#1/Confirm your configuration with sqlplus.
If sqlplus works -> with Oracle Client should work.

If you do not want to use Oracle Client:
The non Oracle Client route is a different 'pure java' code path - no sqlnet.ora configuration.

#2/Cache - needs to be blank or point to a file ie not OSMFT If blank password is prompted for (nearly) each time.
#3/ jce may be required for some common authentication/encryption algorithms.

#4/Oracle Kerberos connection code is available at:

#5/There is a way of tracing thin - it is documented somewhere - never used it myself (as I have access to debugging into the code) - could not find it with a few searches - from memory it involves a Man[person/process]In The Middle technique.
Old style 'C' tracing (set on the server/listener side) may be of some use.

#6/I wrote up this:
However it was more for the text of what sql command failed rather than for connection issues.

Thank you for your time,
Turloch O'Tierney

-SQLDeveloper Team

Thursday, 5 May 2016

Developer day VM Updated: 4/25/2016 Errata

Download 'Developer Days VM' : including patched

Use case:
Developer - VM on their machine with recent Oracle software, with no non VM install deinstall setup issues..
Testing (i.e. non production) use, no non VM install deinstall setup issues...

Update 25 August: enabled ORDS/Rest enabled table (hrrest.employees) :
    (Thought this was in the readme)

Shipped with second pdb[1] for from scratch ORDS setup if you want.
Bump up cpus/Ram
- absolute minimum/default (but slow)1 cpu 2Gig RAM,
-(fast enough but impacts basic host machine (i5/8Gb))2cpu 3Gig RAM

New in this release small bump in versions of (especially) ORDS and sqlcl.[4]
Better setup of ORDS by default (url for select from an example table enabled)
Reset does not cover initial ORDS.
Drop pdb[2]/create pdb[3] and reset up ORDS from the labs if you want to rebuild ORDS configuration.

Absolutely tiny:
No fancy virtualbox options documented especially for this VM - out of the box: port forward from host to guest.
Built with virtualbox 5.0.0 (and extras 5.0.0)
Last Linux 'yum update' about 1 March (might need proxy in yum.conf) - if you update the kernel you will need to reinstall virtualbox  extras to get a better UI integration guest to host.
~/ called on gnome login to set screen size and pop up terminal.
APEX is too slow the first time - after that it succeeds OK.
To reset firefox (eg to clear history) try:
     __log out/exit from firefox__ then cd ~; rm -rf ~/.mozilla ; unzip takes an argument path/to/the/war - so it knows which one to start/stop/ ps -ef| grep for

[1]pdb -> Oracle Multitenant - big new feature in 12 - few extra resources (besides 1.5Gig disk disk) for pdb which appears to the user (with a few restrictions to DBA operations) a new database. I was thinking the VM might host many PDBs
- pdb per developer in the group?
- pdb per software version/product?
- create pdb automatically on demand?(2 minutes)
- Have one spare PDB available so requester does not have to wait 2 mins? (Have a function that returns service name of pdb to use so prebuilt pdb does not need to be renamed (rather than demanding new pdb by name))
Bump up the [Core database RAM - currently 800meg]i.e. SGA/Add Extra Virtual Disk if you are going over 6 VMs...

[2] To drop PDB named SCRATCHT (initially pdb named ORDS is pretty blank - just 'hr' schema)

[3] To create PDB (THE_NAME if not entered defaults to pdb name "ORDS" - if there is an "ORDS" pdb already it errors out).

createnewpdb THE_NAME
[type y enter to confirm details]
creates pdb (including users tablespace)
creates HR schema, create tnsnames.ora entry,
Ensures pdb is open by default
Ensures passwords do not expire (change this to suit your security requirements)
tests 32k varchar is on (set in seed).

[4]sqlcl is undergoing continuous development/updates - any problems confirm first on latest download from:
(it is less than 20 meg download).

Thursday, 28 January 2016

Oracle VM errata released 27 Jan 2016

Oracle VM errata

Download 7.5Gig from

Get back to me if there are any issues so I can make a better one.

Should have told people:

(I am a developer on (sqlcl/sqldeveloper) user of (Oracle Database/jdbc/java/Oracle Linux/bash) so my biases are in that order) 

Introduction to Oracle Database? Not used them myself but...
(TODO: Need to check links from outside Oracle)
Oracle doc - Oracle Database Concepts - 
high level and useful view of the Oracle Database.

Oracle doc was reorganized to include
2 day dba
2 day developer

Fancy new database stuff in Oracle Database 12c:
multitenant - pluggable databases.
32k varchar2
in memory option

1/(Oracle software: database (i.e. patched as of October/November) (APEX/ORDS/sqldev/sqlcl/modeller/labs) - as of Christmas 2015 (or later) Oracle Linux as of November ).

2/Check the licensing (I am not a lawyer...)

3/Oracle Linux 7.1 - you might be able to (put a proxy in /etc/yum.conf if necessary) and yum update -> Oracle Linux 7.2. I had to remove some old kernels so they would not break the update to 7.2 - I think they are removed in the final VM so you should not have kernel issues.

4/Virtualbox extras - 4.0.30 - you might be able to updated to 5.x (i.e. dependencies - kernel headers installed)
4.0.30 should work - with warnings - in 5.

5/Defaults to 2GigRAM/1CPU - that is the bare minimum - update possible on import or settings after import. 

6/It port forwards from the host so:
sqlplus system/oracle@localhost:1521/orcl12c
sqlplus system/oracle@localhost:1521/orcl
should work on host (if you have sqlplus installed)  or guest - to connect to the guest database

7/Other new new stuff: sqlcl [startup script sql] 11 meg download (requires java) acts sort of like sqlplus 

(Download or run from guest:)
sql system/oracle@localhost:1521/orcl12c
sql system/oracle@localhost:1521/orcl

8/ If you are not familiar with Oracle:
Example sql to confirm connection:
select 1 from dual;
[dual is a fake 1 row table in Oracle].

9/Sqldeveloper home:


10/Other Oracle Database downloads (check the licenses) 
- other VMs
- full Oracle Database
- Oracle database XE 11,2 (Easy install/'Cut down' 'slightly more lenient' license)
- instant client including sqlplus

11/Setup decisions:

TWO_TASK=ORCL is set in .bashrc
wrapper script ~/bin/sql - not used (further down the path)
(Other wrapper scripts are to ensure .bashrc are set for PATH/Java/Database environmental variables when for example called from icon click)
APEX 5 installed on pdb (rather than cdb)
varchar2(32k) is on
Default system connection in sqldeveloper is for pdb (pluggable database).

What can go wrong:
-VM might not start properly - looks like a timing issue on slow machine. (I can eventually ssh in but UI broken) - 'ssh oracle@localhost' in and 'sudo shutdown -r now' might help.
-Overlap of icons occasionally
-Some things missing due to desire to reduce size of VM -  for example the database creation DBCA assistant removed,
-patches applied to seed  (rather than (as seen in the documentation) not patch seed and get dba to patch new pluggable databases on creation).
-ORDS startup shutdown script will be confused with two ORDS running (1 startup ords and 1 labs ords (launched from sqldeveloper) for example).

For fancy stuff (SQLDeveloper) developer/manager blogs see:

For example: javascript and aliases in sqlcl