Tag: database

Autonomous Database : “Hand-tuning doesn’t scale”

I was at a talk by Chris Thalinger at Oracle Code One called “Performance tuning Twitter services with Graal and machine learning”. One of the things he said was, “Hand-tuning doesn’t scale”, and it brought into focus some of the things that have been going on in the Autonomous Database, which is closer to my world. 🙂

In my post called It’s not all about you! I discussed the reaction to a new feature mentioned in the ACE Director briefing. It has been spoken about publicly now, so I guess I’m allowed to mention it by name. The feature in question was Automatic Index Tuning that (insert Safe Harbour slide) might be in Oracle 19c, or in an autonomous database cloud service in the future. Once this feature was mentioned, the list of questions started to pile up, before we even knew what it was or how it was implemented. I mentioned my own reaction to this specific feature, but let’s look at this in the broader sense of autonomous services generally.

As I mentioned, watching Chris’ session brought all this into focus for me. Sorry if I’m stating the obvious, but here goes.

  • Even if I were capable of doing a better job than an automatic performance tuning feature, and I’m not sure I can, that is just me. Is everyone else I work with at my level of understanding or better? Is everyone else who works with the database across the world at my level of understanding or better? If the answer to that is no, then there is a need for feature X, whatever it is.
  • Let’s say I have a group of really skilled people that can do better than automatic feature X. Are they constantly looking at the system, trying to get the best performance possible, or are they working on hundreds or thousands of different targets, and actually spending very little time on each? As their workload grows, which it invariably will, will they be able to spend more or less time looking at each specific feature?

I know there are some consultants that get to go in and solve specific problems on specific systems, and maybe those folks will look down on automatic performance tuning features, but I have to look after loads of disparate systems and I get 30 seconds to get something done before I have to move on. I like to think I’m pretty good at Oracle database stuff, but I need all the help I can get if I want to keep things running smoothly.

When a new automatic feature is announced we always get super intense about it, which usually results in a lot of wailing and gnashing of teeth. Sometimes this is for very good reason, as the early incarnations of some features have been problematic, but over time they often become the norm. Think about the following, and what life would be like without them…

For some people reading this, they may never have experienced life without these features. Believe me, it wasn’t pretty! 🙂

Whether it’s a specific automatic feature, like Automatic Index Tuning, or a grander vision, like the Autonomous Database family of cloud services, this is part of the natural evolution of the database. At *some point* in the future I can see all my databases running on the cloud and all of them being some form of autonomous service, regardless of which cloud provider is running them.

Check out the rest of the series here.

Cheers

Tim…

PS. I hope people understand the spirit of what I’m saying, but I feel the need to include a few statements, as some people on Twitter seemed to get the wrong end of the stick.

  • I’m not saying you can do a rubbish job and leave it up to an automatic tuning feature to fix your crap application. Bad software always runs badly, no matter what you do with it. You might be able to mask some of the problems, but you don’t fix them.
  • I’m not suggesting the development process shouldn’t include proper testing, including unit, integration, UAT and performance testing. See previous point.
  • The more you know about your platform, the better job you can do, even if you have automatic features to help you.

Oracle Database 18c “Hands-Off” Data Guard Build Using VirtualBox and Vagrant

I wanted to try something with Oracle 18c Data Guard, so I thought I might as well create a hands-off build of it using VirtualBox and Vagrant, much as I did with my recent hands-off RAC build.

I did the 18c build and figured I might as well do 12cR2 and 12cR1 builds too, as they were pretty similar. I could have done them as a single build with a few tweaks to sort out the differences, but I couldn’t be bothered. 🙂

Along the way I noticed I hadn’t done a 12cR2 data guard article, so I did these.

Cheers

Tim…

Oracle Database 18.3.0 On-Prem for Linux

I was just about to go to bed when I saw this post by Mike Dietrich. Yay!

I’ve had access to 18c on the Oracle Cloud for some time, so I’ve already been able to write a bunch of stuff about it (see here), but it always feels geekier when it’s running on your own kit. It also makes demos a little less dangerous if you can fall back to your own machine. 🙂

Of course I’m starting the downloads now, so maybe I’ll get to have a play tomorrow? 🙂 If you want it you can grab it from here.

Happy upgrading…

Cheers

Tim…

Oracle Database 18c : Deprecated and Desupported

I was looking through the list of deprecated and desupported features along with terminal releases in Oracle Database 18c and there were some surprises.

This won’t be a complete list, so I would advise you to check it out for yourself (starting here), because what is important for me may not be for you, and vice versa. In no particular order, and not taking it too seriously, here we go.

  • Deprecation of Oracle Multimedia : I guess we could see this coming. There was a comment by someone important in a previous OpenWorld briefing that sounded to me very much like, “But you store images on the file system!” I commented to a couple of people at the time, and even went to speak to some folks on a demo stand who weren’t best pleased, and here we are. Of course you can still store stuff in BLOBs and CLOBs, but it seems Oracle have given up on trying to do more than be a data bucket for multimedia stuff. Now this isn’t one of those, “we’ll deprecate it, but never remove it”, messages. The docs say it will not be present in 19c. Looking at the release docs, 19c is the equivalent of 12.2.0.3, so it will be removed during the lifespan of the 12.2 product. I know we are not meant to think of the old numbers anymore, but I think that is interesting.
  • Package DBMS_XMLQUERY is deprecated : I’m including this for the purposes of nostalgia. It’s a long time ago, but I think DBMS_XMLQUERY was the first XML-related package I used for generating XML in an Oracle database, although I had already done some XML parsing in 8i. It’s a good thing this is going, because it had a natural successor in DBMS_XMLGEN for a long time. Having it around only serves to confuse. Even so. 🙁
  • Terminal Release of Oracle Streams : Streams have been deprecated for a long time, but 18c is the terminal release for it. The doc says, “Oracle Streams will be desupported from Oracle Database 19c onwards”, so in the lifespan of the 12.2 patchsets it will disappear. As soon as it was deprecated Streams was dead to me, but I still hear people talking about using it.
  • STANDBY_ARCHIVE_DEST and UTL_FILE_DIR desupported : Oh the bad old days, how I miss them. Everyone had TB and Rickets, but you could leave your back door unlocked! Screw your 24X7 operations, I’m bouncing the database because I want to add a new path to the UTL_FILE_DIR initialisation parameter. I actually had someone ask me about this a few months ago. Out with the old. I’ll get my coat!
  • Returning JSON True or False Values using NUMBER is Desupported : Returning 1 and 0 instead of ‘true’ and ‘false’ when speaking about JSON sounds silly to me, so I’m all in favour of this. I guess it will be annoying for those that have just got to grips with doing JSON in Oracle and have to refactor their code already because they chose the wrong path.
  • Oracle Administration Assistant for Windows is Desupported :  I only threw this one in because I know people lose their minds when some tool that is already long past its sell-by date is removed from the Windows implementation. Let’s start a Twitter campaign with #BringBackTheToolWeNeverUse and stick it to the man. 🙂

No time to worry about what they are getting rid of. There’s all the lovely new stuff you don’t understand yet. That’s really what you should be afraid of! 🙂

Cheers

Tim…

Oracle Database 18c on the Oracle Database Cloud Service

In a previous post I mentioned the release of 18c on Oracle Cloud and Engineered Systems. The only way you could get that on the Oracle Cloud at the time was if you were using an engineered system on the cloud. As a result us folks that don’t have pockets that deep were forced to get our 18c fix on LiveSQL.

You can now get Oracle Database 18c on the Oracle Database Cloud Service, the DBaaS offering, on Oracle Public Cloud (OPC), so you don’t have to have such deep pockets anymore. It also means you can probably get a free trial now.

I’ve fired one up and updated an old article (here) to have the latest screen shots.

Happy days!

Cheers

Tim…

PS. I’m not overly bothered about constantly updating screen shots for cloud service articles as they change so frequently, as do the names of the services from time to time. As long as the general message is OK, that’s good enough. If you try to stay on top of the quarterly roll out you would never get something new done. 🙂

Docker and Oracle Databases : Finding the Sweet Spot

One of the questions I’m asked, and indeed have asked myself on numerous occasions, is how do databases fit into the Docker world? More specifically, how does the Oracle database fit into the Docker world?

There is some Boring Context at the end of the post. Happy for you to ignore it, but please read it before giving me aggro. 🙂

I don’t feel the typical lifespan of a production Oracle database fits well into the Docker world. Lots of people, myself included, have been quick to show examples of Oracle databases running on Docker, because it’s really easy and it works, but the examples are all one-off builds. There is little in the way of realistic life-cycle discussed. Maybe I’ve missed the memo on that, or I’m following the wrong people. 🙂

The issues with Oracle on Docker come about when you try to do things “the Docker way”, which works great for application servers and small footprint databases with a simpler approach to patches and upgrades, but not so well for Oracle databases. In my opinion Oracle database patches and upgrades are clumsy from a Docker perspective. I wrote about this here. I know at least one person I respect that disagrees with my opinion on this (you know who you are 🙂 ). You can choose manual intervention in the upgrade process, but I feel like that is removing some of “the magic of Docker”. It’s well within Oracle’s capability to make database patching and upgrades more Docker-friendly, but time will tell if they think it is worth the effort.

From a monitoring and tuning perspective I’m not sure Docker really fits into what most Oracle DBAs are used to either. If Oracle start making DBA tools that are a bit more “Docker aware”, that could change of course. Once again, it depends on their priorities.

So what’s the sweet spot for Oracle databases on Docker? Well I think it’s great from a Dev, Test and QA perspective. I had a great example of this last night. I was doing some APEX patching (5.1.3 to 5.1.4) at work yesterday and hit a problem. I went home and tested the APEX upgrade (5.1.3 to 5.1.4) on four environments and they all worked.

  1. Oracle Database  12.1.0.2 using multitentant architecture with APEX installed in a PDB.
  2. Oracle Database  12.1.0.2 using non-CDB architecture.
  3. Oracle Database  12.2.0.1 using multitentant architecture with APEX installed in a PDB.
  4. Oracle Database  12.2.0.1 using non-CDB architecture.

I never use non-CDB architecture at home anymore, so I had to spend about 30 seconds altering two Dockerfiles for that, but the process of getting clean environments up and running was quick and simple. It was so quick in fact that when I finished I had time to update my database Dockerfiles to install APEX 5.1.4, and update my ORDS Dockerfile to use APEX 5.1.4 and Java 8u161. All built and tested.

I’ve been using virtualization for well over a decade and I have gold images, snapshots and build scripts coming out of my ears, but Docker is head and shoulders above everything else for pushing out these small short-lived clean environments.

A while back I watched an online presentation by Seth Miller about RAC on Docker. I guess some of you are thinking WTF at this point, which was pretty much how I felt before watching it. 🙂 Watch the presentation and see why it makes sense for Veritas to invest effort in this from a Dev/Test perspective. They have a use case that fits.

The important thing is to focus on the use cases where it adds value, rather than the typical “Emperor’s New Clothes” approach assuming it’s all things to all people. Sounds obvious, but it is so often lost on the IT world. 🙂

So in conclusion, I remain sceptical about me ever running an Oracle database on Docker for production, but I can see a bunch of situations where it is useful in the Dev, Test and QA space, where you need relatively small, clean, well-defined environments for short periods of time. That seems like the sweet spot to me at this point, but I reserve the right to change my mind over time. 🙂

I’m really interested to know what you think, especially if you think I’ve missed the point. I am still a Docker newbie. Like I said earlier, read the Boring Context before jumping to conclusions though. 🙂

Cheers

Tim…

Boring Context: I feel I need to make a few points so people don’t pounce on me. 🙂

  • I am talking about databases in the sense of the monolith running on Docker. The microservice approach where the data is self-contained as part of the microservice is different.
  • I’m not trying to make out every database engine has the set of same issues, although I suspect there will be some commonality when you start talking about long-term production usage.
  • There’s nothing wrong with using Docker like lightweight virtualization and doing manual installations, patches, upgrades and maintenance inside your container. It’s going to bloat the hell out of it, and it’s not how I want to use it, but you can choose to do that if you want. The Docker police will not arrest you. 🙂
  • There’s nothing wrong with you going off piste with how you organise your builds and images. You can have separate Dockerfiles per database or use build arguments rather than first-run configuration based on environment variables. Once again, you are going to waste lots of storage and that’s not how I like to play the game, but that is your choice.
  • I know that Docker was developed with application delivery in mind and databases probably weren’t at the forefront of the developers minds when it started. I hope it stays that way!
  • Some of the same issues exist when dealing with complex web applications like OBIEE. Yes, you can install them on Docker for a demo, but does it really work for the normal lifespan of that product?
  • I realise well planned virtualization can have many of the benefits of Docker, but it feels a little heavier in comparison. Each to their own.
  • I realise there are products out there for data virtualization (like Delphix), that are capable of doing some cool things for Dev, Test and QA environments. If you are using something like that, that’s great for you. 🙂
  • Docker is changing fast. My thoughts might be very different over time as Docker develops.
  • I realise there are a lot of “we just need to keep the lights on” databases that are small and don’t have extreme requirements. Maybe I’ll find Docker to be a good home for these. Maybe not.
  • I’m not a Docker hater. In fact I’m quite the opposite.

Docker : My First Steps

In a blog post after OpenWorld I mentioned I might not be writing so much for a while as something at work was taking a lot of my “home time”, which might result in some articles, but then again might not… Well, that something was Docker…

After spending a couple of years saying I was going to start looking at Docker, in June I wrote a couple of articles, put them on the website, but didn’t mention them to anyone.  I was finding it quite hard to focus on Docker because of all the fun I was having with ORDS. More recently it became apparent that we have a couple of use-cases for Docker at work, one of which involved ORDS, so it reignited my interest. There’s nothing like actually needing to use something to make you knuckle down and learn it… 🙂

Having gone back to revisit Docker, I realised the two articles I wrote were terrible, which wasn’t surprising considering how little time I had spent using Docker at that point. The more I used Docker, the more I realised I had totally missed the point. I had come to it with too many preconceptions, mostly relating to virtualization, that were leading me astray. I reached out to a few people (Gerald Venzl, Bruno Borges & Avi Miller) for help and advice, which got me back on track…

I’ve been playing around with Docker a lot lately, which has resulted in a few articles, with some more on the way. I’m not trying to make out I’m “the Docker guy” now, because I’m clearly not. I’m not suggesting you use my Docker builds, because there are better ones around, like these. I’m just trying to learn this stuff and I do that by playing and writing. If other people find that useful and want to follow me on the journey, that’s great. If you prefer to go straight to the source (docs.docker.com) that’s probably a better idea. 🙂

I do a lot of rewrites of articles on my website in general. This is especially true of these Docker articles, which seem to be in a permanent state of flux at the moment. Part of me wanted to wait until I was a little more confident about it all, because I didn’t want to make all my mistakes in public, then part of me thought, “sod it!”

If you want to see what I’ve been doing all the articles are on my website and the Dockerfiles on Github.

I’m having a lot of fun playing around with Docker. You could say, I’m having a “whale” of a time! (I’ll get my coat…)

Cheers

Tim…

OFS and DBFS Questions (Oracle 12.2)


I pushed out some new articles yesterday.

The SecureFiles article is mostly links to other articles, but it was requested by a reader. In the process of doing that I noticed the new OFS feature in 12.2, which took me down a rabbit hole for a few days.

I try to keep the main website for how-to articles, and the blog is where I post more opinion-based stuff. I got some questions, related to the OFS article, so I thought I would give some answers/opinions here. I could be wrong! 🙂

Q: Isn’t this just renamed DBFS?

A: No. If you compare the objects created for a DBFS and OFS file system they are different. They still do the same job (represent a file system and hold files in BLOBs in the database), but they are distinct file systems. This is not a straight “re-brand” of DBFS.

In addition, OFS is both the name of a file server, the OFSD background process is the OFS server, and a specific type of database file system, the OFS file system. The OFS file system is “similar” to DBFS. The OFS server process can mount both OFS and DBFS file systems.

I think it’s important to remember to use the terms “OFS server” and “OFS file system” so that people understand what bit you are talking about. 🙂

Q: There are now two database file systems (OFS and DBFS). Which should I use?

A: In my opinion, if you are happy with the restriction that you can only create the file system objects in the root container, or a non-CDB instance, you should probably use the OFS file system. It’s marginally easier to create and I’m guessing that is what the developers of the OFS server would prefer you to use. 🙂 If this restriction is a problem, use DBFS to create a file system in a PDB or a non-sys user.

If you need HTTP(S), WebDav or FTP access to the file system, DBFS is a better choice as those features come “out of the box” with DBFS from 12.1 onward.

Pick the one that matches your use case.

Q: Will OFS eventually replace DBFS?

A: I have no idea. What I can say is DBFS has some enhancements in 12.2 (POSIX File Locking) and it does some stuff “out of the box” that OFS doesn’t (HTTP(s), WebDav and FTP). It also plays well with multitenant. I’ve tried it and it works the same in a 12.2 PDB as it does in 11gR2.

IMHO if the plan were to replace DBFS with OFS, OFS would need to play well with multitenant and have the HTTP(S), WebDav and FTP (or preferably sFTP) support.

Until stated otherwise, I’m going to assume they will coexist and each serve slightly different use cases.

Q: Automatic mounting of DBFS using FUSE through “/etc/fstab” is confusing because of timing issues. Is OFS better?

A: Yes. The problem with any kind of auto-mount of a database file system is the database must be up before the mount is attempted. If you get the timing wrong it doesn’t work. The great thing about OFS is the mount/unmount is controlled by the OFSD process, so file systems mounted with the “persist” option will be mounted on instance startup and unmounted on instance shutdown. Great! No timing issues!

You have to be aware of the potential issues around a manual unmount of the file system and the impact of a shutdown abort, but you test all your failure situations before going live right?

From my limited testing, this aspect of OFS server feels really solid. It did what it said it would. I have no examples of heavy usage to refer to at this time.

Q: Is OFS Server a NFS Server.

A: No it isn’t and I think the term “NFS in the database” from the documentation is confusing. A file system mounted by OFS server can be exported using a NFS server, allowing NFS access to the database file system (OFS or DBFS). The database itself is not acting as a NFS server. So what you have is the NFS server on the OS exporting the FUSE file system, which is held in the DB. I would give that more emphasis in the docs if it were me. This totally confused me, so I reached out on Twitter and Oracle came to the rescue, which was cool, but I don’t feel that should be necessary to get a DB feature working. 🙂

I think it’s cool you can export it using the OS NFS server, but that doesn’t make Oracle a NFS server. 🙂

Q: Any issues with the NFS over FUSE?

A: Once you throw the NFS server into the mix you get timing issues again. During my (rather limited) testing I’ve had a few issues that could only be fixed by restarting the NFS server on the OS. Once I did that everything was golden.

From a resilience perspective, I’m not sure how I feel about this. Remember, I’m new to this feature and I’m not a NFS guru either, so don’t let me put you off.

As a general point, there does seem to be a lot of posts on the interwebs of people having issues with NFS over FUSE, so I’m not how solid this is as a technology. It does work though. Like I said, I’m no expert!

Q: How do you “feel” about the new OFS feature?

A: I used iFS in Oracle 8i. It worked fine, but was a massive resource hog. I’ve used the “file system” in the XML DB repository for some basic file handling. It works fine. I’ve used DBFS for some minor things and it’s fine, provided you are careful with mounting it using FUSE. Having used OFS for a few days I “feel” like it is the cleanest experience of accessing a database file system from the database server. I like it. 🙂

Cheers

Tim…

Database Administration : Dead or Alive?

I get this type of question a lot at the moment. It’s not surprising as I’ve done a few things of late that seem to have got people a bit riled up.

  • During my cloud database talks I’ve been saying things like, if you think a DBAs job is just to install, backup and patch the database, the cloud has taken your job.
  • I happened to mention the Oracle Cloud Apps DBA role does not exist. I thought I made it clear what I was saying, but a number of readers thought I was saying they shouldn’t go to work next week as they’ve been fired.
  • I’ve recently been doing some sessions with a title beginning with “Making the RDBMS relevant again…”, which suggests maybe it isn’t currently relevant.

I’ve been doing Oracle database development and DBA work for nearly 22 years. In that time the job of an Oracle DBA has changed a lot. Despite this, having people who understand what is going on below the surface has remained in demand. If you keep trying to be an old-school DBA you are going to find yourself in a very dark place very quickly. If you keep your ear to the ground and try to move with the times there will always be a role for you. Good people always land on their feet.

The way you move depends on your interests and the demands of your company. Some will move closer to an architecture role related to the infrastructure, which is pretty important when dealing with the cloud services, docker, DevOps, continuous deployment etc. Some will align themselves more closely to development, which is of greater importance in the new world. Others will completely move away from RDBMs into other technologies related to data or elsewhere.

The next question is typically, “When?” I’m not saying we should all run around screaming and pulling out our hair, but we should also not turn a blind eye to the way the world is changing. I can pretty much guarantee there will be comments by people telling me I’m wrong and the DBA role will exist forever, to which I will reply, “Denial is not just a river in Africa!” 🙂

Some companies, especially those that are more development led, will transform rapidly. DevOps, continuous deployment and technologies like Docker have the power to transform a company rapidly, whether on-prem or on the cloud. In all cases, someone needs to help build and maintain the layers that contain databases and app servers, and that could be you, but I don’t see the same volume of work we currently have, because if done properly it should be a build once, deploy many approach. For some companies that are into automation, this is already a reality. Very soon it will be true for much more of us.

Some companies will be slow in moving forward and their staff will wonder what all the fuss is about, until they apply for their next job and realise there isn’t one for them!

Having said all that, I did an “unconference” session at OOW in 2007 called “The Oracle DBA… A dying breed?” and we’re still here now. The important point is you need to take responsibility and shape your own destiny. Don’t sit idly by an watch the world take your job. It’s easier than ever to learn new things and prepare for the future, so do it! 🙂

Cheers

Tim…

Collation and Case-Insensitive Database in Oracle 12.2

A little while ago Jeff Smith asked me if I had done anything with the collation functionality in Oracle Database 12c Release 2 (12.2). At the time my response was I was letting it stew a little before I released anything about it.

For some functionality the manuals contain a lot of information, but it’s not always obvious how much you will really care about. The collation functionality is exactly like that. You can get bogged down in some quite heavy stuff, or you can give some basic examples to point people in the right direction and let them go further if they need to. That’s the route I took with this article.

I think this will be a game-changer for some applications. If you’re a MySQL user you are probably laughing now about how long it’s taken Oracle to catch up. 🙂

As always, you have to approach this stuff with a little caution. Making a change that affects every query against some data for comparisons and ordering is rather dangerous. I’m not sure how quickly, if ever, I would be considering a switch across to this for my actual table segments, but I can certainly see how some views or materialized views could be put to good use really quickly.

It will be interesting to see how this is recieved.

Cheers

Tim…