LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in 12c

love-sqlI was looking for something in the New Features Manual and I had a total WTF moment when I saw this stuff.

If you look at the final section of the article, you can see in some cases these just get transformed to regular joins and outer joins, but there is certainly something else under the hood, as shown by the pipelined table function example.

I think it’s going to take me a long time before I think of using these in my regular SQL…

Cheers

Tim…

Update: The optimizer has used LATERAL inline views during some query transformations for some time, but they were not documented and therefore not supported for us to use directly until now. Thanks to Dominic Brooks and Sayan Malakshinov for the clarification.

Auditing Enhancements (Audit Policies and Unified Audit Trail) in Oracle Database 12c

security_image1_smallA little over a year ago I was at the BGOUG Spring Conference and I watched a session by Maja Veselica about auditing in Oracle Database 12c. At the time I noted that I really needed to take a look at this new functionality, as is was quite different to what had come before. Fast forward a year and I’ve finally got around to doing just that. :)

I’ve tried to keep the article quite light and fluffy. The Oracle documentation on this subject is really pretty good, so you should definitely invest some time reading it, but if you need a quick overview to get you started, my article might help. :)

My 12c learning experience continues…

Cheers

Tim…

Native Network Encryption and SSL/TLS are not part of the Advanced Security Option

security_image1_smallI had a little surprise the other day. I was asked to set up a SSL/TLS connection to a database and I refused, saying it would break our license agreement as we don’t have the Advanced Security Option. I opened the 11gR2 licensing manual to include a link in my email response and found this.

“Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.”

I checked the 11gR1, and 10gR2 docs also. Sure enough, it was removed from the Advanced Security Option from 10gR2 onward (check out update below). Check out the 10g licensing doc here, specifically the last paragraph in that linked section.

The documentation on this configuration is split among a number of manuals, most of which still say it is part of the Advanced Security Option. That made me a little nervous, so I raised an SR with Oracle to confirm the licensing situation and file bug reports against the docs to correct the inconsistency. Their response was it is definitely free and the docs are being amended to bring them in line with the licensing manual. Happy days! :)

Lessons learned here are:

  • Skim through the licensing manual for every new release to see what bits are now free.
  • Don’t trust the technical docs for licensing information. Always cross check with the licensing manual and assume that’s got the correct information. If in doubt, raise an SR to check.

As far as the configuration is concerned, I had never written about this functionality before, so I thought I should do backfill articles on it.

The documentation for TCP/IP with SSL/TCP is rather convoluted, so you could be forgiven for thinking it was rocket science. Actually, it’s pretty simple to set up. It was only after I finished doing it I found a reference to the following MOS note.

It would have saved me a lot of bloody time if the documentation included this. I would never have bothered to write the article in the first place!

cloudFor a lot of people, encrypting database connections is probably not that big a deal. If your databases and application servers are sitting behind a firewall in a “safe” part of your network, then why bother?

If there are direct database connections crossing network zones, that’s a different matter! Did anyone mention “cloud”? If you need to connect to your cloud databases from application servers or client tool sitting on-premise, I guess encrypted database connections are pretty high up your list of requirements, or at least they should be. Good job it is free now. :)

It seems I’m not the only person behind the times on this licensing change. The Amazon AWS RDS for Oracle documentation has made the same mistake. I’ve written to them to ask them to correct this page also. :)

Cheers

Tim…

Update: Simon, Jacco, Franck and Patrick all pointed out this licensing change was due to this security exploit. It was made public during 11.2, but the license change was made retrospectively back to 10.2. I don’t feel so bad about it now. :)

Update2: I’ve added a link to the Native Network Encryption stuff, based on the comment by Markus.

Why do people show Azure so much love?

cloudThe title of this post is taken from tweet I saw a few weeks ago and it keeps coming back to haunt me, so I thought I would comment on it.

Let me start by saying I don’t have any context as to why the tweeter thought people were showing Azure so much love. From my perspective, I kind-of like Azure and I think it is what my employer will end up using, but I’m not a crazed fan-boy about it. :)

Also, I fully understand a move to the cloud is not the right thing for everyone, so this post is focused on those people who do want/need to move to the cloud. Just because it is not right for you, it doesn’t mean it’s not right for everyone. So when I’m talking about running services on the cloud, it is not a recommendation. I’m not telling you you’ve got to. I’m speaking about cloud services to try to explain why someone might say something like the title of this post. I’m hoping this paragraph will stem the hate-comments that invariably come when you mention the cloud. :)

Interface

The Azure interface it pretty neat. It’s clean and reasonably intuitive. I’m a casual user, so I can’t say how I would feel about it if I were managing hundreds or thousands of resources, but from my brief time with it, I like it.

I don’t dislike the AWS interface, but it does feel a bit more cluttered and ugly than the Azure interface. I guess that could be enough to put off some people maybe.

Services

Coming from the Oracle world, we tend to think of UNIX/Linux as being the centre of the universe, but if I think back to the companies I’ve worked for over the years, the majority of their kit has been Windows-based, with the exception of the bits I work on. :) Since most corporate desktops are still Windows-based, Outlook, Office and Active Directory tend to rule the roost. If you are thinking of moving those services on to the cloud, Azure seems the “obvious choice”. Am I saying they are the best products and Azure is the best place to run them? No. What I’m saying is it will be seen as the “obvious choice” for many people wanting to move to the cloud.

The same goes with SQL Server. I happen to like the AWS RDS for SQL Server implementation, but I’m guessing a lot of SQL Server folks will get a warmer and fuzzier feeling about running SQL Server on Azure. Lots of decisions in IT are based on gut instinct or personal bias of the buyers, not necessarily fact. I can see how someone will “feel happier” there.

Once the Oracle Cloud becomes generally available, we may see a similar issue there. People may feel happier about running Oracle products on the Oracle Cloud than on AWS or Azure. Time will tell.

What’s under the hood?

This is where cloud really turns stuff on its head. If I want to run a Linux VM, I can do that on AWS, Azure, Oracle Cloud, VMware vCloud Air etc. From my perspective, if the VM stays up and gives me the performance I paid for, do I really care about what’s under the hood? You can be snobbish about hypervisors, but do I care if Oracle are using less hardware to service the same number of VMs as Azure? No. Where infrastructure as a service (IaaS) is concerned, it is all about the price:performance ratio. As I’ve heard many times, it’s a race for the bottom.

Call me naive, but I really don’t care what is happening under the hood of a cloud service, provided I get what I pay for. I think this is an important factor in how someone like Microsoft can go from zero to hero of the cloud world. If they provide the right services at the right price, people will come.

Conclusion

Q: Why do people show Azure so much love?

A: Because it does what it is meant to do. It provides the services certain companies want at a price they are willing to pay. What’s not to love?

Q: So it’s the best cloud provider right?

A: That depends on your judging criteria. No one cloud provider is “the best”. For some people Azure will be the best option. For others it might be the worst.

Cheers

Tim…

New Monitor… Again…

220px-Commodore_PET2001I’ve just bought myself a Dell U3415W 34-Inch IPS LCD Monitor for use with the laptop. It’s quite an extravagant purchase, but it’s pretty amazing. Having 3440×1440 resolution on a single screen feels much more useful than sitting a couple of smaller monitors next to each other. It feels almost like having 3-4 screens in one.

I bought it to replace the Asus PB298Q 29 inch Widescreen AH-IPS Multimedia Monitor I got about 7 months ago. The resolution of 2560×1080 is pretty darn decent, but I don’t like having a depth of 1080. When you are using a wider screen, the limited height feels really restrictive for some reason.

Currently I have both screens plugged into the laptop, but I can’t see it staying that way. I’ve really had no reason to look at the MacBook or ASUS screen yet. I’ll see how I feel over the next few days. If I’m happy to let it go I’ll probably take the ASUS screen to work and use it there. It’s better than either of my work monitors. :)

Ditching the second screen will also free up some room on my desk, which is looking a little crazy at the moment… :(

Cheers

Tim…

Oracle Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5) : My first two installations

em-12cI’ve done a couple of play installations of EM12c 12.1.0.5, just to get a feel for it. You can see the result of that here.

From an installation perspective, everything was pretty similar to the previous releases. I tried the installation on both OL5 and OL6, in both cases using 12c as the database repository. No dramas there.

A couple of things of note.

  1. The 12c repository template database is a Non-CDB architecture.
  2. The Weblogic installation uses Java6.

Interesting…

The next step is to try some upgrades from EM 12.1.0.4 (on DB 11.2.0.4) to EM 12.1.0.5, which is what I’ll need for my upgrades at work. The testing is quite time consuming and boring, but it’s got to be done before I can unleash this on the company. :)

Cheers

Tim…

PS. Remember to download from edelivery.oracle.com (in a couple of days) for your production installations. Apparently there is a difference to the license agreement.

Feedback from the Oracle documentation team

feedbackI got some feedback from the Oracle documentation team, based on my recent post.

GUIDs

One of the concerns I raised was about how the GUIDs would be used in different releases of the documentation. Although I don’t like the look of the GUIDs, I can understand why they might be more convenient that trying to think of a neat, descriptive, human readable slug. My concern was the GUID might be unique for every incarnation of the same page. That is, a new GUID for the same page for each patchset, DB version and/or minor text correction. That would make it really hard to flick between versions, as you couldn’t predict what the page was called in each variant.

It seems my worries were unfounded. The intention is the GUID of a specific page will stay the same, regardless of patchset, DB version or document correction. That’s great news!

Broken Links

The team are trying to put some stuff in place to correct the broken links. I think I might know who is developing this solution. :)

The quick fix will be to direct previously broken links to the table of contents page of the appropriate manual. Later, they will attempt to provide topic-to-topic links. No promises here, but it sounds promising.

Conclusion

I’m going to continue to fix the broken links on my site as I want to maintain the direct topic links in the short term, but this sounds like really good news going forward.

It also sounds like the documentation team are feeling our pain and putting stuff in place to prevent this happening in future, which is fantastic news! :)

Note to self: It’s much better to engage with the right people and discuss the issue, rather than just bitch about stuff.

Cheers

Tim…

Oracle Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5) – Just Born

em-12cOracle Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5) was announced a few days ago. I woke up today and checked the interwebs and it’s actually available for download.

I must admit I’m a little nervous about the upgrade. I had a few bad times with upgrades in the early days of Grid Control and Cloud Control and that has left me with a little bit of voodoo lurking in the back of my mind. The last couple of upgrades have been really easy, so I’m sure it will be fine, but that voodoo…

I’ll download it now and do a clean install. Then do a couple of practice upgrades. If all that goes well, I’ll schedule a date to sacrifice a chicken, raise a zombie from the dead to do my bidding, then do the real upgrade.

Cheers

Tim…

Update. Looking at the certification matrix, the repository is now certified on 12.1.0.2, as well as 11.2.0.4 and 11.2.0.3.

Update 2. Pete mentioned in the comments that 12.1.0.2 has been certified for the Cloud Control repository since march, with some restrictions. So it’s not new to this release. See the comments for details.

Update 3. Remember to download from edelivery.oracle.com (in a couple of days) for your production installations. Apparently there is a difference to the license agreement.

Site maintenance and how to manage changing URLs

DiagnosticsAfter my recent rants about Oracle changing URLs and breaking stuff, I’ve actually done some changes myself. :)

From time to time change is forced on internet content producers. This might be because of platform changes, or changes in the way search engines behave. The important thing is how you handle that change.

Followers of the blog will know I recently made my website responsive. That happened in part because Google recently announced they would downgrade the rankings of sites that weren’t “mobile friendly” and “responsive”. The search ranking were only meant to affect mobile searches. What they didn’t say, but many people including myself believe, is that these rankings actually affect normal desktop-based searches as well. When this Google announcement was made, I noticed a drop in my hit rate. Once I changed the site to be responsive, the hit rate went up again somewhat. When I recently corrected about 100 of the remaining non-responsive articles, the hit rate went up again. It could be conincidence, but it certainly seems there was a bleed-over of this ranking change into the desktop side of things, which represents over 95% of my traffic. Those changes affected content, but not the URLs to the content.

Since I’m revisiting almost every article to fix broken links to Oracle docs, I thought I would take the opportunity to do some additional site maintenance, specifically in the following two areas.

  • HTTPS : About 9 months ago I got a certificate for the website to allow it to be accessed using HTTPS. This was also influenced by a Google decision that they would improve the ranking of content that was available over HTTPS, as well as HTTP. It was part of their “HTTPS Everywhere” campaign. Even though the site could handle HTTPS, I did not make it the default. As of a couple of days ago, you may have noticed all pages on oracle-base.com are how delivered over HTTPS. Unfortunately, this represents a URL change as far as the internet is concerned, so it means lots of broken links, unless you handle it properly. More about that later.
  • Removal of “.php” extension : You will notice many blogs and websites don’t display a file extension of pages, or display a generic “.htm” or “.html” extension. It’s pretty easy to do this using query rewrites in Apache or a “.htaccess” file. For a while, the site could be accessed with or without the “.php” extension. Now it is removed by default. The nice thing about this is you can change the underlying technology at any time, without having to support an inconsistent file extension. Once again, this represents a URL change.

So how do you manage this change without pissing off “the internet”?

The answer is rewrites and redirects done in real web pages, Apache config or “.htaccess” files. Essentially, you are supporting the old URL and redirecting the browser to the new URL, using a 301 redirect, so all search engines know the content has moved location and can be re-indexed in that new location. Over time, all the links from Google will go directly to the new URL.

So that means you can remove the redirects after a while right? NO! People will have links from their website to the old URLs forever. People will have bookmarks in their browsers forever. If you are going to change a URL, the old URL must be maintained forever.

Over the years I’ve made lots of structural changes to the site.

  • When my website started it was written in Active Server Pages, using a “.asp” extension.
  • After a while I switched to PHP, using a “.php” extension.
  • I used to name pages using initcap. A couple of years ago I switched to lower case and “-” separated names.
  • About 9 months ago I removed the “www.” because it seemed pointless in this day and age.
  • I’ve just swicthed to HTTPS.
  • I’ve just removed the “.php” extension.

If we look at a really old article, probably about 15 years old, we will see the history of these changes in the following URLs.

So all those structural changes over the last 15 years should have resulted in zero broken links, zero pissed off content producers who link to my content and zero uninformed search engines.

Now I’m not perfect, so if anyone finds something that is broken, I will fix it, assuming it’s not your bad typing or copy/pasting. :)

Cheers

Tim…

PS. Any structural changes, regardless of how well you do your 301 redirects, can result in lower search rankings, so it should not be done on a whim if you really care about hitting that top spot on Google. This is my hobby, so I will do whatever I want. :)