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).


Turloch O'Tierney said...

2017 12.2 release:
#2/ (demos) for createnewpdb
#3/Reported issues: issue when install o windows d:
#4/Report any issues to: have VM in the subject
#5/Any requests?
#6/Any usecases?

#1/sqlcl:to use sqlcl: rm ~/LDLIB/sql $ORACLE_HOME/bin/sql
and start a new terminal (or use ~/bin/sql (no rm required))
#2/ (demos) for createnewpdb
- demos missed get from save download zip as ~/
note createnewpdbminhr works only for ORDS (hr schema is loaded could be easily modified.
#3/install on d: gave problems might have been: Virtualbox->preferences->General->Default_Machine_Folder or snapshot setting: machine->settings->advanced->snapshot folder.
#4/Report any issues to: have VM in the subject
[possible source of issues:
-use virtualbox 5.1.14 or later
-let it auto login do not click on Oracle user.
-12.1 prerequisites used ulimit (copy stack) might need to be more
-really should stop that firefox warning.
-We removed lots of stuff for space reasons dbca ~/apex/builder (for German Chinese .. language support - it should be the same as the current apex 5.1.0 download.)
#5/Any requests (I guess just update when other oracle software gets a version update)
Meant to have a load all demos script (not just HR) but it was too late for release.
If download and look at createnewpdb:
1/Remove PATH_PREFUX setting on pdb creation
2/It goes out of its way to only load HR - do not have the exit added to the .sql file and all the demos should build - I think I also made the directory a hard link (no soft links) in the version in development - might not have been necessary.
#6/Any usecases:
-source control based new pdb database for each continuous integration run ?
-note there is an oracle docker script: ( no latest other software (APEX/ORDS)).

Turloch O'Tierney said...

(also rm ~/LDLIB/oraenv ~/LDLIB/coraenv )

Turloch O'Tierney said...

Versions for 12.2 VM:
export DEMOSURL= (internal labs) as of March 1st
export JAVAURL= jdk-8u121-linux-x64.tar.gz
export SQLCLURL=
export APEXURL= 5.1.0 as of March 1st
export ORDSURL=
export MODELLER=

Built with packer 0.10.2 and Oracle Linux 7.3 as of mid February
If anyone want to know the minor issues worked around in the automated build - drop me an email with VM in the subject:

Turloch O'Tierney said...
32k varchar2 not set in 12.2VM (I thought it was by default already)

Turloch O'Tierney said...
This comment has been removed by the author.
Turloch O'Tierney said...

Please post if you used that 32k varchar2 script successfully.
Differences from a different install include:
export DONOTSETTWO_TASK=true [VM workaround]
export LD_LIBRARY_PATH=$ORACLE_HOME/lib [should be set by . oraenv]
/home/oracle/ path may vary
On a production system you probably want two double check each stage runs successfully.
I tried to keep the lines short by backslash at line continuation so over long lines may not be mangled in blog post cut and past.

Turloch O'Tierney said...

Updated had open upgrade at the end and forgot utlrp

Do not just press enter when eventually asked for the sys password!!!
For use on test 12.2 VM with 32kvarchar not set yet.

export TWO_TASK=
echo using /home/oracle/utl32k_cdb_pdbs_output for output dir
echo XXXX presss enter if database is up XXXX
ps -ef| grep pmon
read line
sqlplus sys/oracle as sysdba <<EOF
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
shutdown immediate
startup upgrade
cd $ORACLE_HOME/rdbms/admin
mkdir /home/oracle/utl32k_cdb_pdbs_output
echo XXXXXX enter sys password when prompted XXXXXXXXX
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/ \
-u SYS -d $ORACLE_HOME/rdbms/admin -l \
'/home/oracle/utl32k_cdb_pdbs_output' -b \
utl32k_cdb_pdbs_output utl32k.sql
echo XXXXXXX if successful press enter to continue XXXXX
read line
sqlplus sys/oracle as sysdba <<EOF
shutdown immediate

cd $ORACLE_HOME/rdbms/admin
mkdir /home/oracle/utlrp_cdb_pdbs_output
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/ \
-u SYS -d $ORACLE_HOME/rdbms/admin -l \
'/home/oracle/utlrp_cdb_pdbs_output' -b \
utlrp_cdb_pdbs_output utlrp.sql

Turloch O'Tierney said...
I am getting : failed to load system cursor: DnD.Cursor.CopyDrop : cannot load system cursor: CopyDrop.32x32

On launching sqldeveloper - went away on reboot - worrying - got it once before - just got it again.

Not reproducible enough to try the nomodeset grub setting suggested workaround

Turloch O'Tierney said...

Firewall probably still up in the vm (cannot sqlnet/sqlcl/sqldeveloper/firefox in from guest)

chkconfig iptables off
service iptables stop
service ip6tables stop

stays off on reboot

-SQLDeveloper team

Turloch O'Tierney said...

Latest release - a few setup fixes new labs and some minor version bumps.

Updated: 6/14/2017

Paolo Marzucco said...

Hello Turloch,
today I noticed that the new script newpdbords works fine when run once, but if you then drop the PDB it created and try to run it again, it will seemingly hang at: already unzipped

By looking at the storm_vm_log the script is waiting for input by the user as follows:

replace storm.dmp? [y]es, [n]o, [A]ll, [N]one, [r]ename:

but none of this is visible to the user. If I blindly type A in the console that started the script, it will then proceed further to the end.

I believe it's probably better for the script either to clean up the unzipped archives or to force overwriting the zip contents if they already exist in the destination directory.

Hope it helps,
Paolo Marzucco

Turloch O'Tierney said...

Hi Paolo,

Thanks for the information.
It slipped by us in testing.

Works altering newpdbords in my VM .

Will confirm next build (not sure when it will be externalized).

Any other issue please send it in.

The best workaround for the workshop team is:
[Either alter the newpdbords ]
or before newpdbords is reused:

cd ~/storm
rm import_sys.sql storm.log storm.dmp storm_vm_log


--SQLDeveloper Team


if test -f
if test -f storm.dmp
echo already unzipped
echo unzipping
unzip >> storm_vm_log 2>&1
echo importing storm.dmp
imp storm/oracle@ORDS FILE=storm.dmp FULL=Y >> storm_vm_log 2>&1


if test -f
if test -f storm.dmp
echo already unzipped
echo unzipping
unzip >> storm_vm_log 2>&1
echo importing storm.dmp
imp storm/oracle@ORDS FILE=storm.dmp FULL=Y >> storm_vm_log 2>&1

We have open sourced the scripts:

I welcome any additional feedback.

Why not just use the ova shipped (instead of building it yourself)?

-You can change the virtual disk size
-You can skip the delete of some things that were deleted for space reasons eg dbca (database creation wizard)
-You can add your own application 'on top' as part of your build.
-You can change the database properties bigger/smaller SGA
-You can update the newpdbords

There are other helper scripts:
uploaddemos - install lots of demos not just hr (ie OE AV etc)
createnewpdb which creates a new pdb with 'hr' - it does not do the storm import though.

Turloch O'Tierney said...

There is also:
in newpdbords

if test -f /home/oracle/Desktop/Database_Track/coffeeshop

instead of

if test -d /home/oracle/Desktop/Database_Track/coffeeshop

which is likely to also cause a second run of newpdbords to hang.