Monthly Archives: March 2016

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!

SQLcl the Future?

I’ve worked with SQLPlus for years and like many people who have used Oracle for a while you come to learn its strengths and weaknesses. However I must say that over the years SQLPlus has always been reliable but hasn’t exactly changed much.

However there’s a new kids on the block, enter SQLCL (or SQL Command Line), which is available here.

Why is SQLcl a contender to the venerable SQLPus? Well this feature alone makes it worthwhile:

  • Command History

that’s right, it’s 2016 and SQLPlus still doesn’t let you recall previous commands easily (there are ways to do it, but nothing as simple and analogous to the BASH history command)

However, it’s some of the other features that really set it apart, how about –

  • Doing a ‘Create Table As Select’ in a single CTAS command
  • Generating the DDL for database objects using the DDL command
  • Being able to ALIAS your own commands (incredibly useful for creating shortcut commands for things you frequently type)

I really encourage you to try out SQLcl, it’s going to be a HUGE productivity gain over SQLPlus.