The Oracle ACE Program : My 14 Year Anniversary

I was checking my calendar, thinking I was about to jack it in for the day, and I noticed it’s April 1st, which means it’s my 14th year anniversary of being an Oracle ACE. Can’t believe I nearly missed that!

As usual I’ll mention some of the other anniversaries that will happen throughout this year.

  • 25 years working with Oracle technology in August. (August 1995)
  • 20 years doing my website in July. (Original name: 03 July 2000 or current name: 31 August 2001)
  • 15 years blogging in June. (15 June 2005)
  • 14 years on the Oracle ACE Program. (01 April 2006)
  • A combined 3 years as an Oracle Developer Champion, now renamed to Oracle Groundbreaker Ambassador. (21 June 2017)

The 20 years of doing the website in July will be a pretty big one. I might have to do something for that. 🙂

I hope everyone is safe out there. Have a good one!

Cheers

Tim…

Video : Online Segment Shrink for Tables : Free Unused Space

In today’s video we’ll give a demonstration of how to shrink tables that contain a lot of free space. As I say in the video, this is not something you should do regularly. It’s only necessary if you’ve done some drastic one-off maintenance, like a large data purge maybe.

There are a few articles this relates to.

The star of today’s video is Kellyn Pot’Vin-Gorman, who is returning to her Oracle roots, but on Azure. 🙂

Cheers

Tim…

Data Guard and RAC on Docker : Perhaps I was wrong?

I’ve talked a lot about Docker and containers over the last few years. With respect to the Oracle database on Docker, I’ve given my opinions in this article.

Over the weekend Sean Scott tweeted the following.

“A while back @oraclebase said Data Guard didn’t make sense on Docker.

For those of us disinterested in the sensible I present #Oracle#DataGuard on #Docker. 19c only for now. Please let me know what’s broken. Enjoy!

https://github.com/oraclesean/DataGuard-docker

This was in reference to a statement in my article that said the following.

“Oracle database high availability (HA) products are complicated, often involving the coordination of multiple machines/containers and multiple networks. Real Application Clusters (RAC) and Data Guard don’t make sense in the Docker world. In my opinion Oracle database HA is better done without Docker, but remember not every database has the same requirements.”

For the most part I stick by my statement, for the reasons described in my article. Although both Data Guard and RAC will work in Docker, I generally don’t think they make sense.

But…

A few years ago I had a conversation with Seth Miller, who was doing RAC in Docker. In his case it made sense for testing because of his use cases. I discussed this in this post.

For that use case, Seth was right and I was wrong.

What about Data Guard?

For a two node data guard playground I don’t see any major advantages to using two containers in one VM, compared to two VMs. The overhead of the extra VM and OS is not significant for this use case. Remember, most of the resources are going to the Oracle instances, not the VM and OS. Also, the VM approach will give you something similar to what you will see in production. It feels like a more natural testing scenario to me.

But Sean’s scenario was not this simple. When I questioned him over the value of this, considering the two VM approach had so little extra overhead, he came back with the following.

“There I’ll disagree. I have a Docker/sharding build I’m working on. 7 databases. Starts in moments. On my laptop. I can’t do 7 VM. No way!”

Now this scenario changes the game significantly. All of a sudden we go from the overhead of one extra VM to an overhead of six extra VMs. That’s pretty significant on a laptop. All of a sudden the Docker method probably makes a lot more sense than the VM approach for testing that scenario on a laptop.

Once again, I’m wrong and Sean is correct for this use case.

Conclusion

If you are building a two node RAC or Data Guard playground, I still think the VM approach makes a lot more sense. It’s going to be a lot more like what you use at work, and you don’t have to deal with some of the issues that containers bring with them.

Having said that, if you are looking to build something more extreme, or you are just trying stuff for fun, then Docker may be the right solution for you.

I still don’t see a realistic future for an RDBMS monolith on containers. I don’t care if it’s a single container or a giant Kubernetes cluster. This is not a criticism of the RDBMS or of containers. They are just things from different worlds for different purposes and continuing to treat them differently seems totally fine to me. Having said all that, it doesn’t mean combining the two can’t be useful for some use cases.

Remember, this is just my opinion! 🙂

Cheers

Tim…

PS. As a general point, trying to build your own data service on containers feels like a mistake. I would just use a cloud service that gives you the features, performance and availability you need. Concentrate on your apps.

Video : Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results

Today’s video is a demonstration of returning REF CURSORs from PL/SQL using functions, procedures and implicit statement results.

I was motivated to do this after a conversation with my boss. He’s from a .NET and SQL Server background, and was a bit miffed about not being able to use a SELECT to pass out variable values from a procedure, like you can in T-SQL. So I piped up and said you can using Implicit Statement Results and another myth was busted. I guess most PL/SQL developers don’t use this, and I don’t either, but you should know it exists so you can be a smart arse when situations like this come up. 🙂

The video is based on these articles.

The star of today’s video is Bryn Llewellyn of PL/SQL fame, and more recently Yugabyte.

Cheers

Tim…

Video : SQLcl and Liquibase : Automating Your SQL and PL/SQL Deployments

In today‘s video we’ll give a quick demonstration of applying changes to the database using the Liquibase implementation in SQLcl.

The video is based on this article.

You might also find these useful. The secure external password store is a good way to make connections with SQLcl. If you support a variety of database engines, you may prefer to use the regular Liquibase client.

The star of today’s video is Steve Karam from Delphix. 🙂

Cheers

Tim…

Video : Secure External Password Store

Today’s video demonstrates how to use a Secure External Password Store to hold database credentials in a client wallet.

The video is based on this old article.

The star of today’s video is John King. This is actually his second staring role on the channel. The other one was called Making Dreams Come True: Video for a Superfan. 🙂

Cheers

Tim…

VirtualBox 6.1.4

VirtualBox 6.1.4 has been released.

The downloads and changelog are in the usual places.

I’ve done the installation on my Windows 10 PC at work and all is good. I’ll probably do the installations on my Windows 10, macOS and Oracle Linux 7 hosts at home tonight and update this post.

Happy upgrading!

Cheers

Tim…

Update: I did the upgrades on my Windows 10, macOS and Oracle Linux 7 hosts at home. Everything went fine, and it all looks good for now.

Data Pump Between Database Versions : It’s not just about the VERSION parameter! (Time Zone Files)

I was doing a small “quick” data transfer between two servers. The source was 19c and the destination was 18c, so I used the VERSION parameter during the export.

expdp … version=18 directory=…

The export went fine, but when I started the import I immediately got this error.

ORA-39002: invalid operation

A little Googling and I came across MOS Doc ID 2482971.1. In short, the time zone file was different between the two databases.

No problem. I know how to fix that, and both databases had the January quarterly patches applied, so the latest time zone files would be available right? Wrong. The 18c database was already at the maximum time zone version that was installed, and I needed to be one higher to match the 19c database.

After some Googling I re-found MOS Doc ID 412160.1. As soon as I opened it I remembered it. I find this note really messy an confusing, but the section labelled “C.1.d) DST patches list” had the list of patches, which is what I needed. I downloaded the patch to match the time zone file version of the source system and applied it with OPatch in the normal way. Always read the patch notes!!!

Once the new time zone file was in place in, it was time to update it in the database. I’ve written about this before.

Once the time zone file versions matched, the import worked as expected. Although the small data transfer that I expected to be quick had turned into a much bigger job. 🙂

I can’t remember if I’ve hit this issue before, but I don’t remember it. I guess I’ve just been lucky with the time zone file versions matching. This note is to remind myself, it’s not just about the VERSION parameter! I’ve also updated a couple of articles with pointers about this.

Cheers

Tim…

PS. It seems the later releases are more sensitive to time zone file differences than previous releases.

Cloud Control 13.4 : Silent Installation and Silent Upgrade

A little over a week ago Enterprise Manager Cloud Control 13.4 was released. The following weekend I spent 3 days running builds constantly trying to get a clean install to work. Eventually I tweeted out in frustration and a friendly face at Oracle, who I’ve stalked on numerous occasions, put me in touch with the EM dev team.

Having had a quick look at my Vagrant build, they suggested I unset the CLASSPATH environment variable, and a working build was born. Thanks very much to the EM dev team! Without them I would have spent days looking at it and would probably still have failed.

Installation

The resulting Vagrant build and an article about the silent installation of Cloud Control 13.4 can be found here.

One thing that still irks me somewhat is the documentation about the adaptive optimizer parameters. The documentation says the following.

“If your Management Repository is using Oracle Database 12.2 or higher, none of these parameters need to be set.”

This is not true, and you always get this error message.

“ERROR:
The following prerequisite check failed because the Oracle Database, where the Management Repository will be configured, does not meet the configuration requirements. Fix the issue manually based on the recommendation offered for this prerequisite, and click OK. For more details, check the logs: /u01/app/oracle/middleware/cfgtoollogs/oui/emdbprereqs
Prereq Name Recommendation
Check if all adaptive features parameters are unset All adaptive features parameters should be unset for improved SQL performance”

I even tried a GUI installation, in case there was a difference between the GUI and silent installations. There wasn’t.

The workaround is to amend a bunch of underscore parameters that are only meant to be necessary when running a patched version of Oracle database 12.1 as the repository database.

alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid=''; 
alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='';
alter system set "_px_adaptive_dist_method" = OFF scope=both sid=''; 
alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='';
alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid=''; 
alter system set "_optimizer_use_feedback" = FALSE scope=both sid='';
alter system set "_optimizer_gather_feedback" = FALSE scope=both sid=''; 
alter system set "_optimizer_performance_feedback" = OFF scope=both sid='';

It’s not a show stopper, so I can live with it, but it’s annoying, and the documentation should be altered to reflect the reality.

Upgrade

The next challenge was to work through an upgrade from a previous release. I worked through this using a starting point of 13.3. I already had a vagrant build for 13.3, but I made a few changes to bring it up to date, and add some more disk space. I also renamed the directory structure to make things a little neater.

The upgrade itself was very similar to that of the previous version. You can find the article about the silent upgrade to 13.4 and the Vagrant build I used to test the upgrade here.

Now remember, this is a simple upgrade of a totally clean 13.3 build to 13.4, so I’m not saying this is an exhaustive test, and I’m not saying this is proof it will work for you.

Next Steps

The next challenge will be to try a real upgrade at work. Work is crazy at the moment, so I’m not sure how long I will have to wait before doing this.

Most of our kit is VMware virtual machines running Oracle Linux, and the Cloud Control server is no exception, so I can get a backup of the whole VM before the upgrade, and just restore back to that in case of a disaster.

An ideal place to be is to have your build scripted, including the reconfiguration of all your targets. After a previous “issue”, I went through our existing config and built the EMCLI scripts to replace it all. I *think* I can rebuild everything from scratch if I need to. We do all new agent installations, target discovery and setup using EMCLI now, so I think all the retrofitted stuff will work too, but I have to admit I’m kind-of scared to try. 🙂

Conclusion

I don’t like to do anything at work unless I’ve already done it at home first. It’s taken me pretty much 5 full days (Fri, Sat, Sun, Fri, Sat) to get through this, but it’s done now, and I feel I can have a try at work without looking like a total fool now! 🙂

Cheers

Tim…

Video : Multitenant : Online Move of Datafiles in CDBs and PDBs

Today’s video is a quick look at online datafile moves in container databases (CDBs) and pluggable databases (PDBs).

If you’ve used this functionality in a non-CDB database, it’s going to look familiar, with a PDB-specific gotcha.

These articles discus moving and renaming files.

I’ve added this to my Multitenant YouTube playlist.

The star of today’s video is Todd Trichler, but he’s having the share the limelight with the top of Roel Hartman‘s head, and brief clips of John King and Debra Lilley on the video screen behind him.

Cheers

Tim…