APEX 5.1 Early Adopter 1 Available

After a long (but I’m sure well worth it) wait, Oracle just announced APEX 5.1 Early Adopter is available.

Head over to https://apexea.oracle.com/ to sign up and request a Workspace.

The two big items everyone has been waiting for are

  • Interactive Grids
  • Jet Charts

but reading through the “Additional Features in Application Express 5.1” section on the EA homepage it looks like there are lots of other interesting improvements and enhancements.

 

 

 

Oracle Cloud – Querying Glassfish Memory Usage

In a previous post I posted about a Java Heap memory issue we were having with Glassfish.

One of the ways we tracked down the cause of the issue and by how much to increase the memory by was to use the asadmin command to generate a memory report – which is a really useful way to see a snapshot of how your Glassfish instance is consuming memory.

You can run a command like

./asadmin generate-jvm-report --type memory

which should give output similar to

[oracle@ahi-dhh-prod bin]$ ./asadmin generate-jvm-report --type memory
Enter admin user name> admin
Enter admin password for user "admin">
The uptime of Java Virtual Machine: 47 Hours 2 Minutes 44 Seconds
Memory Pool Name: PS Eden Space
Memory that Java Virtual Machine initially requested to the Operating System: 122,683,392 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 239,599,616 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 264,241,152 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 45,212,424 Bytes

Memory Pool Name: PS Survivor Space
Memory that Java Virtual Machine initially requested to the Operating System: 20,447,232 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 2,097,152 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 2,097,152 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 312,976 Bytes

Memory Pool Name: Code Cache
Memory that Java Virtual Machine initially requested to the Operating System: 2,555,904 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 17,956,864 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 50,331,648 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 17,726,208 Bytes

Memory Pool Name: PS Perm Gen
Memory that Java Virtual Machine initially requested to the Operating System: 67,108,864 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 99,614,720 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 201,326,592 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 99,278,352 Bytes

Memory Pool Name: PS Old Gen
Memory that Java Virtual Machine initially requested to the Operating System: 326,631,424 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 441,974,784 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 536,870,912 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 372,432,888 Bytes


Name of the Garbage Collector: PS MarkSweep
Number of collections occurred using this garbage collector: 244 Bytes
Garbage Collection Time: 37 Seconds 768 Milliseconds
Name of the Garbage Collector: PS Scavenge
Number of collections occurred using this garbage collector: 54,911 Bytes
Garbage Collection Time: 330 Seconds 761 Milliseconds

Heap Memory Usage:
Memory that Java Virtual Machine initially requested to the Operating System: 489,924,096 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 683,671,552 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 716,177,408 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 418,149,376 Bytes

Non-heap Memory Usage:
Memory that Java Virtual Machine initially requested to the Operating System: 69,664,768 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 117,571,584 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 251,658,240 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 117,004,560 Bytes

Approximate number of objects for which finalization is pending: 0


Command generate-jvm-report executed successfully.

As you can see there’s a lot of output here, but I’ve highlighted the section which shows how much Heap Memory we’re currently using and what the maximums are set to. By regularly monitoring these values we were able to hone in on how much memory was the optimal amount to allocate to the Glassfish instance.

Oracle Cloud – Glassfish Heap Memory Issues

I recently encountered some issues with our Oracle Cloud Glassfish server encountering out of memory issues.

This manifested itself by Glassfish becoming unrepsonsive and eventually crashing, digging into the logfile we found entries like this

server.log_2016-04-08T18-32-27:[#|2016-04-04T18:21:56.472+0000|SEVERE|oracle-glassfish3.1.2|null|_ThreadID=74;_ThreadName=Thread-2;|Java heap space
server.log_2016-04-08T18-32-27:java.lang.OutOfMemoryError: Java heap space

After a lot of Googling, I found that you can find out the current Java settings that are being used by Glassfish by running the list-jvm-options command, like this:

[oracle@prod bin]$ ./asadmin list-jvm-options
Enter admin user name> admin
Enter admin password for user "admin">
-XX:MaxPermSize=192m
-XX:PermSize=64m
-client
-Djava.awt.headless=true
-Djavax.management.builder.initial=com.sun.enterprise.v3.admin.AppServerMBeanServerBuilder
-XX: UnlockDiagnosticVMOptions
-Djava.endorsed.dirs=${com.sun.aas.installRoot}/modules/endorsed${path.separator}${com.sun.aas.installRoot}/lib/endorsed
-Djava.security.policy=${com.sun.aas.instanceRoot}/config/server.policy
-Djava.security.auth.login.config=${com.sun.aas.instanceRoot}/config/login.conf
-Dcom.sun.enterprise.security.httpsOutboundKeyAlias=s1as
-Djavax.net.ssl.keyStore=${com.sun.aas.instanceRoot}/config/keystore.jks
-Djavax.net.ssl.trustStore=${com.sun.aas.instanceRoot}/config/cacerts.jks
-Djava.ext.dirs=${com.sun.aas.javaRoot}/lib/ext${path.separator}${com.sun.aas.javaRoot}/jre/lib/ext${path.separator}${com.sun.aas.instanceRoot}/lib/ext
-Djdbc.drivers=org.apache.derby.jdbc.ClientDriver
-DANTLR_USE_DIRECT_CLASS_LOADING=true
-Dcom.sun.enterprise.config.config_environment_factory_class=com.sun.enterprise.config.serverbeans.AppserverConfigEnvironmentFactory
-Dosgi.shell.telnet.port=6666
-Dosgi.shell.telnet.maxconn=1
-Dosgi.shell.telnet.ip=127.0.0.1
-Dgosh.args=--nointeractive
-Dfelix.fileinstall.dir=${com.sun.aas.installRoot}/modules/autostart/
-Dfelix.fileinstall.poll=5000
-Dfelix.fileinstall.log.level=2
-Dfelix.fileinstall.bundles.new.start=true
-Dfelix.fileinstall.bundles.startTransient=true
-Dfelix.fileinstall.disableConfigSave=false
-XX:NewRatio=2
-Xmx128m
Command list-jvm-options executed successfully.

You can see a lot of info here, the Heap memory parameter is the Xmx one, which we can adjust by deleting the current setting:

./asadmin delete-jvm-options --target server-config -- '-Xmx128m'

and then assigning a new value

./asadmin delete-jvm-options --target server-config -- '-Xmx256m'

 

Then we restarted the Glassfish server and haven’t seen the issue occur since.
It’s important to not just blindly choose a value here, you need to understand why you’re running out of heap memory and not just increase it for the sake of it (but that’s a post for another day).

 

Oracle Cloud – Enterprise Manager Express

Oracle Cloud DBaaS provides a lightweight version of Enterprise Manager called Enterprise Manager Express which is enabled by default (just not perhaps enabled ‘fully’, as you’ll see).

You can check if EM Express is up and running by checking the Listener Status on your DBaaS instance.

[oracle@DEMO ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-MAR-2016 14:27:54

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 30-MAR-2016 23:52:34
Uptime 1 days 14 hr. 35 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DEMO/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/ORCL/xdb_wa
llet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCL.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "pdb1.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

notice the item running on Port 5500 here, that’s the EM Express instance running in the Container DB (CDB) of my Multi-tenant 12c database.

So you could either open up Port 5500 via the network rules or use an SSH tunnel, which I’ll do using a command like this:

[jes@mac oracle-cloud]$ ssh -L 5500:localhost:5500 -i oracle_cloud_rsa opc@129.191.3.59
[opc@DEMO ~]$

Now with my SSH tunnel in place I can navigate to https://localhost:5500/em (it’s important to remember the /em on the end since unfortunately it won’t redirect by default).

You should now see the EM Express login page

 

em-1.png

Enterprise Manager Express – Login Page

You can login using ‘sys’ or ‘system’ together with the password you specified during the DBaaS setup.

em-2.png

Enterprise Manager Express – Landing Page

 

Whilst it doesn’t offer the full functionality of Enterprise Manager, EM Express does ways to perform the most common DBA type tasks such as creating tablespaces, creating users etc.

Depending on the Database Edition you opted for, there is even a pretty nifty Performance Hub where you can review performance issues and drill down to find out the cause of issues.

em-3.png

Enterprise Manager Express – Performance Hub

So is this a replacement for Enterprise Manager (or Enterprise Manager Cloud Control)? No, absolutely not – there are many pieces of functionality available in EM that aren’t implemented in EM Express. For example of glaring omission I found was there was no way to query Scheduler Jobs (to create, delete or query if they’d executed for example).

EM Express and PDB’s

One thing I did find confusing at first was that I tried to create a tablespace. However I couldn’t find the option! You’d expect it to be under the ‘Storage’ tab, but it wasn’t there.

The penny finally dropped when I realised there was no way to drill down into the PDB information either. The EM Express instance running on port 5500 really only shows information about the CDB, whereas to create a tablespace you would create it for (or within) a PDB.

Therefore you need to enable EM Express for the PDB you want to create a tablespace for. To do that you need to login to the PDB and enable it, like this (you can find more information on this here).

[oracle@DEMO ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 13 14:47:44 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: system@pdb1
Enter password:
Last Successful login time: Fri May 13 2016 14:25:33 +00:00

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exec dbms_xdb_config.sethttpsport(5501);

PL/SQL procedure successfully completed.

SQL>

the important bit here is the call to dbms_xdb_config.sethttpsport where I pass in the port number I want to enable it on (you need to ensure nothing is running on that port already). By convention I like to increment the port, e.g. 5500, 5501, 5502 etc but really it’s an arbitrary choice.

Once you’ve done that you should be able to launch an SSH tunnel and navigate to the Enterprise Manager Express instance running just for that PDB.

em-4-cdb.png

Enterprise Manager Express – PDB View

You can see we now have a PDB-centric Enterprise Manager Express view, and we have the tablespace menu item to create tablespaces within this PDB.

I could see this becoming a little tedious / confusing if you have a lot of PDB’s to manage since you have the overhead of enabling them all (and using multiple ports) and also having to logout and login to different ones each time you wanted to perform some maintenance. On the other hand it’s a nice way to tie down access and restrict users to being able to administer a specific PDB.

I’m hoping Oracle enhances EM Express in the future to plug some of the gaps (e.g. scheduler jobs), but in the meantime at least you have something out of the box when you create a DBaaS instance.

Oracle Cloud – Updated UI

I was creating a new DBaaS Instance recently and noticed that much of the UI had changed from when I originally blogged about it previously. The differences are quite striking and it goes to show that you shouldn’t base your opinion on something you saw many months ago, always check your assumptions to see if they’ve changed!

So for example, in one of my older running DBaaS instances the home page looks like this

cloud_home.png

Old DBaaS Landing Page

whereas in the new instance it looks like this

cloud_home_new.png

New DBaaS Landing Page

So functionality equivalent, but very much nicer and more modern to look at.

The Database Monitor has been updated quite dramatically too, so for example it had an ‘ugly’ default webserver login page that looked like this

database_monitor.png

Database Monitor – Old Login Screen

and the home page looked like this

dbaas_home.png

Database Monitor – Old Version

the new version has a much cleaner HTML based login

dbaas_monitor_login.png

Database Login – New Login Page

and the home page looks much nicer

dbaas_monitor_new.png

Database Login – New Landing Page

To my eyes this is a much more visually appealing page.

As well as being visually updated, there has also been some functionality added, so for example I can now clone, unplug and plug PDB’s directly from this interface (I’m pretty sure you couldn’t before – although I could be mistaken).

clone.png

I have to say I’m pretty impressed by this update, whilst the visuals in no way change the underlying functionality (i.e. I can still perform most of these actions at the OS, DB level), having a visually pleasing UI gives another way to interact with the service.

Great job Oracle!

Oracle Cloud – Using dbaascli

One of the nice features of Oracle Cloud is that it comes with some additional command-line tooling in the form of the dbaascli command.

When you run this command (while logged into your  DBaaS instance) you’ll see

[oracle@DEMO ~]# dbaascli
DBAAS CLI version 1.0.0
DBAAS>

This dumps you into an interactive shell, typing ‘help’ gives us a bit more info.

DBAAS>help
Help for dbaascli
dbaascli is a command line interface for different tools to be used with Oracle Cloud DB.
This command line interface supports logging, command history and autocomplete.
Logging is done on a session level. Each time dbaascli is run the last log is overwritten.
To access command history use the up/down arrow keys as you would normally do in a terminal.
Autocomplete uses the Tab key. Autocomplete will complete to the longest common string in case there is not an exact match.
To obtain help on a command use the help keyword at the begining or end.
Autocomplete is supported if the first word is the "help" keyword.
Special commands are available.
The list of special commands is:

-H
 -HELP
 ? - Get dbaascli help
 CLEAR - Clear the screen
 HELP - Get dbaascli help
 HIST - Print command line history
 HISTORY - Print command line history
 LIST - List available commands
 Q - Exit Command Line Interface
 QUIT - Exit Command Line Interface

DBAAS>

Note – you can also run the command non-interactively by specifying the options on the command-line.

So, let’s list the commands available.

DBAAS>list

Very Long Text, press q to quit
Available commands:
 
database bounce
database changepassword
database start
database status
database stop
dataguard failover
dataguard reinstate
dataguard status
dataguard switchover
dbpatchm
dv off
dv off cdb
dv off pdb
dv on
dv on cdb
dv on pdb
gg setup
gg status
glassfish start
glassfish status
glassfish stop
listener bounce
listener start
listener status
listener stop
mrec

netsec config
netsec config encryption
netsec config integrity
netsec deconfig
netsec deconfig encryption
netsec deconfig integrity
netsec status
netsec status encryption
netsec status integrity
ore setup

orec
tde config
tde rotate
tde rotate masterkey
tde status
tfa install
tfa uninstall


DBAAS>

Most of these commands are wrappers around other commands but it does give you one “go-to” centralised command which is consistent across all your DBaaS instances rather than trying to remember each command (and perhaps having to deal with different configurations).

So let’s try a few commands, this time I’ll run them non-interactively (type ‘quit’ to exit the interactive version).

Database Commands

Let’s check the database status

[oracle@DEMO ~]$ dbaascli database status
DBAAS CLI version 1.0.0
Executing command database status
Database Status:
Database is open
Database name: ORCL

Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

You could leverage this script to perhaps grep for the phrase ‘Database is open’ to check for problems.

We could also do the commands

dbaascli database bounce
dbaascli database start
dbaascli database stop

Glassfish Commands

Lets check Glassfish

[oracle@DEMO ~]$ dbaascli glassfish status
DBAAS CLI version 1.0.0
Executing command glassfish status
Unable to run command. This command should be run as user: root. Currently: oracle

Hmmm ok, so depending on the command we need to run it as different users, lets switch to root and try it again

[root@DEMO ~]# dbaascli glassfish status
DBAAS CLI version 1.0.0
Executing command glassfish status
Calculating the status and metrics:
Authentication failed for user: admin
with password from password file: /root/statuspwd.txt
(Usually, this means invalid user name and/or password)
Authentication failed for user: admin
with password from password file: /root/statuspwd.txt
(Usually, this means invalid user name and/or password)
domain1 running
Command list-domains executed successfully.
Command list-http-listeners failed.
Command uptime failed.

 

Notice the error message stating it’s reading the /root/statuspwd.txt file to get the password. Once I entered the password there and re-ran the command worked (I’ll omit the output here as it’s quite lengthy).

Listener Commands

Let’s check the listener

[root@DEMO ~]# dbaascli listener status
DBAAS CLI version 1.0.0
Executing command listener status
Invalid parameter(s)
Command Error
Command: listener status
Listener Status

Ok, this is getting annoying, maybe I need to be the oracle user for this one?

[root@DEMO ~]# su - oracle
[oracle@DEMO ~]$ dbaascli listener status
DBAAS CLI version 1.0.0
Executing command listener status
Starting listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-MAY-2016 13:15:50

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 4-JAN-2015 23:52:34
Uptime 1 days 13 hr. 23 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DEMO/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/ORCL/xdb_wa
llet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCL.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "pdb1.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

So that worked, obviously the output is quite lengthy, but again you could see how this simple command could be used in a monitoring script.

Command Specific Help

If you’re unsure what a particular command might do, rather than risking running it, you can get some help on each command. For example what does that mrec command do?

[oracle@DEMO ~]$ dbaascli mrec help
DBAAS CLI version 1.0.0
Executing command mrec help
Command: mrec
Help for MREC tool.
Parameters:
 --args Format: Not defined Required: NO

Usage:
 mrec --args -oss_cfgfile <cfgfile> -old_hostname <vm_name>
 mrec --args -oss_cfgfile <cfgfile> -old_hostname <vm_name> -sid <oracle_sid>
Where:
 -help = Print this help.
 -oss_cfgfile = full path to OSS config file.
 -old_hostname = Name of the host to recover.
 -sid = ORACLE SID of the DB instance to recover. Default is orcl
Examples:
 mrec --args -oss_cfgfile /var/opt/oracle/mrec/oss.cfg -old_hostname vmprod01
 mrec --args -oss_cfgfile /var/opt/oracle/mrec/oss.cfg -old_hostname vmprod01 -sid orcl

Ok, so it doesn’t tell me what it actually does (I had to search the documentation to figure out MREC = Media Recovery), but it does at least tell you what parameters it expects.

 

Oracle APEX 5.0.3 Patchset Released

Oracle APEX 5.0.3 patchset was released (early Christmas Present!), with (again) fewer fixed bugs than the previous patchset, some stand out ones are:

  • 22173641 – APEX_PLUGIN_UTIL.GET_DATA raises ORA-06502 for CLOB or LONG VARCHAR2
  • 22301102 – Redirect Loop if Rejoin Sessions Enabled for All, Logged In and On Public Page

As always, read the patchset notes, but I’d definitely recommend installing this update.