OTN Appreciation Day: Flashback

This is my contribution to the excellent OTN Appreciation Day idea by Tim

The Flashback (particularly Flashback Query) features of the database have saved my neck many (too many!) times over the years.

For example retrieving the value of the Employees salary as it was 10 minutes ago:

SQL> select sal 
 from emp 
 as of timestamp sysdate - interval '10' minutes 
 where ename = 'SCOTT';

This feature can be used almost everywhere Oracle is used, for example it’s embedded into the Export Application feature of Oracle Application Express

2016-10-11_08-38-54

As an extension to this, using the Flashback Data Archive feature allows you maintain an archive  of your application data that you can query in real time – without having to write your own logic.

Flashback – an extremely useful but often little known and underused feature of Oracle!

APEX 5.1 Early Adopter 2 Available

2016-11-10_15-14-47.png

Oracle have updated the Application Express Early Adopter instance to EA2, you can try it out here – https://apexea.oracle.com/

There are some fairly significant changes and enhancements in EA2 versus the EA1 release, particularly around the Interactive Grid feature (which every APEX developer can’t wait to get their hands on!). For example, the list below shows some very cool additions.

2016-11-10_15-17-52.png

There seem to have been some improvements to the Jet Charts too, this is shaping up to be the best APEX release ever IMHO.

 

APEX Integration with SQLCl

Oracle SQLcl contains some very useful built in commands that APEX developers might find useful.

For example, lets say I’m connected to a schema and want to know what APEX applications are installed in the workspace linked to that schema, I can simply issue the APEX command and it will list the APEX applications, for example:

SQL> apex
WORKSPACE APPLICATION_ID APPLICATION_NAME BUILD_STATUS LAST_UPDATED_ON
JES_DEMO 105 LDAP Demo Run and Develop 12-JUN-16
JES_DEMO 280 Amazing jQuery Run and Develop 12-JUN-16

Let’s say you want to export an application from the command line, we can use the APEX EXPORT command like this:

SQL> spool f105.sql
SQL> apex export 105

...application spools out

Now I can do cool things like automate exports from my application using cron, how cool is that?

I see a lot of potential in SQLCl and it keeps improving with each release, go and check it out!

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.