Video : Multitenant : PDB Refresh in Oracle Database 12.2 Onward

Today’s video demonstrates the PDB refresh functionality introduced in Oracle database 12.2.

If you prefer to read rather than watch, you can get all the information from this video from here.

The star of today’s video is Markus Michalewicz. He’s so serious in this clip it makes me laugh. 🙂



Oracle 12c on Fedora 28

As always, I have to start with a warning.

So here we go…

Fedora 28 has been out for quite a while now. I installed it pretty much when it was released, but hit a problem with installing Oracle 12c on it. I didn’t have much time to investigate at the time and I kind-of forgot about it. A couple of days ago someone asked me about it, so I had another attempt. The only difference compared to Fedora 27 was a couple of extra symbolic links were needed.

Here were the articles I did as a result of this.

It’s pretty much the same as the installation on Fedora 27, with the exception of those extra symbolic links.

If you are feeling lazy/efficient you can do it all with Vagrant, as described here. 🙂

So now you know how to do it, please don’t! 🙂



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. 🙂



Certification : OCP 12c and OCP 12cR2 Updates

Just a couple of certification related updates.

Upgrade to oracle Database 12c (1Z0-060)

About a week ago I finally got round to doing the 12c certification. As followers of the blog/website know I’ve been writing articles about Oracle Database 12c since it was released nearly 4 years ago and for pretty much all of that time I’ve been trying to convince myself to sit the 12c OCP DBA upgrade exam. It’s not that I care about the piece of paper itself (see my certification FAQ), but I’ve had so many people write and thank me for my 12c revision notes, that it felt a little bit odd I hadn’t actually sat the exam myself.

The biggest factor in delaying taking this exam was it was not a conventional 12cR1 new features exam. Instead it was an upgrade from OCP 9i, 10g, 11g to 12cR1. That meant it potentially covered anything introduced from 9iR2 onward.  That means in addition to learning the new features well enough to pass the exam, you also have to cover the old ground, which is quite a burden. Oracle used to provide catch-up exams like this as well as a straight new features exam and I think that was a better option.

Now I’m sure some of you will think, “But you should know that stuff anyway!”, and you are right, but knowing enough to do the job and knowing the type of things you are tested on in an exam are not the same thing. The thought of revising everything from 9iR2 onward was quite daunting and it put me off doing this exam. This seemed to be a common reaction amongst many people I spoke to.

I realise maintaining a catch-up exam and a regular new features exam is extra work for the certification folks, but if you want to keep people on the OCP treadmill, I think you’ve got to do this. I came very close to ditching the whole OCP thing as a result of this 12c upgrade exam, and I’m sure I’m not the only one.

Upgrade Oracle DBA 11g/12c to 12cR2 (1Z1-074)

I mentioned in a recent post there is an exam called “Upgrade Oracle DBA 11g/12c to 12cR2 (1Z1-074)” in beta at the moment. I’ve been writing about Oracle Database 12cR2 since its release on the Oracle Public Cloud, so there are quite a few articles on my website already.

Normally the release 2 articles get lumped together, since they don’t form part of an exam syllabus, but now they do, so I’ve split them up into two sections.

I’ll continue to add new articles to both sections, and at some point I will have a complete set of revision notes for the new exam. 🙂

Once again the new exam is a catch-up exam, but at least it only covers 12cR1 and 12cR2 this time. As before, I think this is a mistake, but that’s just my opinion. 🙂

Part of me wants to cram for this new exam and get it done on the cheap while it is in beta. Part of me thinks I may never take this new exam. Time will tell I guess. You’ll probably see an update post in 4 years when I’ve finally got round to it. 🙂



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.



Online Table Moves and Partitioning in Oracle Database 12c Release 2 (12.2)

A couple of articles have crept out over the last few days that relate to online operations on Oracle Database 12c Release 2 (12.2). Both of them are pretty small in terms of what you have to do, but are pretty awesome in terms of impact, so I’m giving them the official thumbs-up! 🙂

The first one is about moving tables online.

I wonder if this was a 12.1 feature that got pulled? In 12.1 we got to move partitions and sub-partitions online, but being able to move a table as an online operation is great. Saves you having to mess around with DBMS_REDEFINITION for a bunch of tasks.

The second one is about converting non-partitioned tables to partitioned tables.

This is pretty cool too. For one, you can use a single command to do it now, rather than having to use EXCHANGE PARTITION or DBMS_REDEFINITION to do it. What’s more, it can be done online!

As I’ve said many times before, it’s these convenience new features that usually have a bigger impact on my day-to-day life than the big sexy features.



Oracle Database 12c Release 2 (12.2): Installation Articles

If you follow me on social media, you will know I’ve put out a few 12cR2 installation articles recently. It’s good motivation for me to read through the installation guides and see what’s changed, if anything, between versions. I quite like doing some of this nuts & bolts stuff. 🙂

If you’re interested, this is what I’ve done so far.

I always feel like I should stress a few things in relation to my install guides, or anyone else’s for that matter.

  • You should always read the installation manuals. I know it can be boring, but there is a bunch of stuff you should understand that I don’t include, because my intention isn’t to regurgitate the manual. Maybe you have a situation where something is relevant that other’s don’t care about.
  • The RAC articles are intentionally really basic. I avoid some of the job separation (users and groups) that you may want in your organisation. I keep the storage very simple. I avoid a lot of the possible topology variations. I would always consider these as a starting point for playing with RAC on a VM, rather than something you would consider a RAC “best practice”. Once you’ve got one of these running you can try variations. Don’t expect me to write a separate guide for all variations. Try it yourself.
  • I will make choices over my approach to make stuff suit the wider audience. For example I’ve tended to steer clear of ASMLib and the new “ASM Filter Driver” because a big chunk of my audience are sceptical of them and prefer to use UDEV. I’m happy to use either and if you are using Oracle Linux and UEK it really doesn’t make a great deal of difference. I think you should play with both and decide for yourself which you like. Remember, I’m not going to write multiple variations of the article.
  • The installation guides are all using interactive installations, but I rarely do these nowadays, preferring to use silent installations and silent database creations. I’m not sure how many people would be happy with a bunch of response files as their introduction to a new version. I think the GUI screens aid with understanding when you are new. You should definitely graduate to the silent stuff though! I’ve included the response files generated during each interactive installation.
  • I modify the installation guides over time as I learn more about the stuff. That’s true of all my articles, but don’t assume because I installed it this way today, that’s definitely the way to do it!

I guess what I’m saying is I’ve only done a handful of 12.2 installations, so I’m no guru. Keep that in mind and learn to think for yourself! 🙂

Happy installing!



Oracle Database 12c Release 2 (12.2) : Default APEX Installation Gone!

I’m a fan of APEX, but I am not a fan of the shared installation used in Oracle Database 12.1. I’m sure it is great for some people, but not for me. I prefer to have APEX installed locally in the PDB, which prompted this article.

Fast forward to Oracle Database 12c Release 2 (12.2) and APEX is no longer installed by default. It’s still shipped with the software, see $ORACLE_HOME/apex (see update), but not installed. I am conflicted about this…

  • On the one hand this is great because I don’t have to worry about removing the shared APEX installation before installing it into the PDB.
  • On the other hand this means people have to make an active choice to install APEX, which not everyone will do. That’s a pity. In addition to the tool itself, there is some useful APIs that come with it, like APEX_WEB_SERVICE and APEX_JSON. You don’t really need the later in 12.2 as you have lots of new JSON support, but it’s good to have for backwards compatibility.

So just remember you have an extra job to do after you’ve installed your database, which to install the latest version of APEX. 🙂



Update: As per comment from Joel Kallman, always download the latest version and install in the PDB locally, not in the root!

Oracle Database 12cR2 (12.2) On-Prem : It’s finally here!

Oracle 12cR2 (12.2) has been around for a while on the cloud, but for many people it doesn’t really exist until they’ve downloaded it and run it on an “on-prem” server or a VM on their PC.

If you are of that mind, you will be pleased to know the on-prem release has arrived! You can download it from eDelivery or OTN.

I’ve been writing about it for a while (here), but it’s nice to have the on-prem release and the knowledge that someone might actually read the stuff I’m writing now (or in about 5 years when people start to use it). 🙂

There will of course be the usual rash of installation articles coming from me for a while, before I get back to more interesting things…

Happy downloading…



Update: My first installs here.

Multitenant Enhancements in Oracle Database 12.2 (Some More Articles)

The Oracle Database 12c Release 2 (12.2) learning process continues. I’m determined to get to the bottom of all this new multitenant stuff. 🙂

Here’s the latest batch of articles.

So the last one is actually for 12.1, but I hadn’t written about it already, so I added it.

I also updated this article to include the “CONTAINERS Hint” feature functionality.

There is a new feature listed in the docs as as “Support for PDBs with Different Character Sets, Time Zone File Versions, and Database Time Zones in a CDB”. I’ve already written about the PDB character set stuff and it is also listed separately as a new feature. The time zone stuff works in 12.1, so it doesn’t appear to be a 12.2 new feature. The different Time Zone File Versions functionality, as far as I can see, relates to OCI client connections to the server and was introduced in 11.2. With all that in mind, I’m really not sure what this listed new feature specifically relates to. If someone can clear that up for me I would be really grateful, as I’m wondering if I’ve just missed the point somewhere. 🙂

I’ve added these to the list of all my multitenant articles here.