Tag: 12c

Oracle Database 12c : Some Backup and Recovery Stuff

In a previous post, I had already listed an article on backup and recovery when using the Oracle 12c multitentant option.

In that article I made reference to a restriction when using flashback database against a CDB with a PDB that has had a point in time recovery. I finally got my head round this and posted a note about it here.

As well as this stuff, I’ve also had a play around with table recovery. It’s pretty cool! In previous versions you had to do a manual point in time clone to recover a table in this way. In Oracle 12c it still does this, but it has wrapped it up and hidden it behind a nice RMAN command, making it feel a lot simpler. Definitely a neat feature when you need to recover old versions data that pre-date the level of undo available.

I word of advice before you launch into playing with any of this. TAKE A SNAPSHOT OF YOUR VM!

I’ve been playing about with this stuff using Oracle 12c on Oracle Linux, running on an Oracle VirtualBox VM. You would not believe the number of times I’ve totally screwed up my databases while figuring this stuff out. I’ve recreated PDBs and CDBs a number of times. I’ve had to recover databases that I broke by accident. I’ve even had to restore a previous snapshot of the VM a couple of times to get myself back to a stable state. All this messing about is great for getting to grips with the tech, but it can be frustrating if you are only trying to investigate one specific feature. For this reason, I would say avoid trying to learn this stuff on a physical machine. Especially when learning backup and recovery, it can be a time consuming pain in the ass when you screw things up.You want to be able to get back to the start and try again as quickly as possible. This is where VM snapshots rule! Of course, if you’ve got the time to spare, you can come across some interesting things when you accidentally break things and try to fix them! 🙂

One of things I’ve noticed specifically about RMAN and PDBs is the error handling of some commands is not what it could be. Sometimes you perform a relative simple RMAN command while the PDB is closed and it gives you a “sky is falling in” type message, including the odd ORA-00600, which leads you to believe things are really screwed. You notice the PDB is closed, so you open it and hey presto, the RMAN command works just fine. Now admittedly, I was attempting to do something stupid, but a “You can’t do that when the PDB is closed dumb-ass!” message would have been a lot less scary than an ORA-00600. 🙂 I’m going to try and recreate the specific scenarios and log them on MOS so future versions can handle people doing stupid stuff a bit better. 🙂

Cheers

Tim…

Articles on Oracle Database 12c Multitenant Option

From a technology perspective, the Multitenant option is really cool, but it can be rather difficult to get to grips with some aspects of it. Everything is so intertwined and every feature in 12c somehow links back to the Multitentant option. As I’ve been working my way through the features the same pattern keeps repeating.

  • Write an article on a feature X.
  • Put it live.
  • Move on to looking at feature Y.
  • Notice something about feature Y that affects the article I’ve written about feature X.
  • Go back and revise the article on feature X.
  • Rinse and repeat.

This pattern has made me rather reluctant to post anything on the blog about new articles, because although I don’t release articles until I think they are finished, all these Multitenant articles feel very much like a work-in-progress. I fully expect to step back and revise/rewrite all of them as I figure more out about this stuff.

The first link is not so much an article, more of a links page. I originally wanted to write about Multitenant as a single article. Looking back now that is quite laughable. Ten articles in and I’ve barely scratched the surface. As I keep adding more content, I’ll keep adding it to the overview article, so it remains the index page for the option.

I’ve said it before and I’ll say it again, it’s going to take a long time to get fully up to speed with this option, so you really need to start learning about it now!

Cheers

Tim…

Scheduler Enhancements in Oracle Database 12c

I’ve spent the last couple of days playing around with the scheduler enhancements in Oracle 12c.

I guess the big news is the new “script jobs”, which are pretty cool. This kind-of passed me by until Brynn Llewellyn mentioned them at UKOUG in his Multitenant presentation, at which point I made a note to check them out.

I’ve been having some trouble with the “BACKUP_SCRIPT” jobs up until a few minutes ago. My problem was I couldn’t see what the stdout/stderr text was, so I couldn’t determine why there were not working. The “$ORACLE_HOME/scheduler/log” directory was empty and there were no messages in the trace files or alert log. Then I stumbled upon the new columns added to the ALL_SCHEDULER_JOB_RUN_DETAILS view. The OUTPUT column, not surprisingly, gives you the output from the scripts. Once I could see the error message it took me a few seconds to fix the issue and Bingo! 🙂

The new job types are a nice addition, allowing you to run file-based scripts or incline scripts much more easily that before.

Cheers

Tim…

 

Data Pump Enhancements in Oracle Database 12c

Another one to file under “Not sexy but flippin’ awesome!”

If you are a DBA, you are going to spend a lot of time with Data Pump. All roads seem to lead back to it. 🙂 There are some more headline worthy features, like transportable database, but the two that jumped out at me were actually pretty small, but awesome.

  • “TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y” – Switches table and/or index imports to NOLOGGING for the lifespan of the import operation.
  • “LOGTIME=ALL” – Puts a timestamp in the output message so you can see how long individual operations took.

I wrote up an article about it here.

Cheers

Tim…

PL/SQL White Lists in Oracle Database 12c Release 1 (12.1)

I’ve been playing about with the ACCESSIBLE BY clause to create PL/SQL white lists in Oracle 12c. Here’s the article I wrote about it.

There seem to be some discrepancies in the documentation*, which I’ve highlighted in the article. Not sure if they are documentation errors, functionality that has been pulled and will reappear in 12cR2, or just misunderstandings on my part. 🙂

Cheers

Tim…

* I’ve posted comments on the docs, so if they are documentation errors they may get fixed.

Online Move Datafile in Oracle 12c

The ability to move datafiles as an online operation is bloody awesome! It’s another one of those cool little things that you will wonder how you ever lived without. I can’t wait to move to 12c just for this feature. I could clean up so much crap so easily. Here’s a little write-up on it.

I’ve also amended this article to mention this new feature and link to the article.

Cheers

Tim…

Cloud Control 12c Database Backup Jobs (Continued)

I’ve been rather critical of the way Cloud Control handles database backup jobs, as can be seen in these two previous posts.

Yesterday I found out I schedule database backups in Cloud Control the “wrong way”…

So typically, when I am sorting out a new database, I do something like this:

  • If the host doesn’t already have an agent, push one out the to server.
  • Discover the targets. For existing hosts, this may just be discovering a new database.
  • Tell Cloud Control to used the RMAN catalog for backups. (Oracle Database > Availability > Backup & Recovery > Recovery Catalog Settings)
  • Set the default location for the backups and any other stuff. (Oracle Database > Availability > Backup & Recovery > Backups Settings)
  • Finally schedule the backup. (Oracle Database > Availability > Backup & Recovery > Schedule Backups…)

Notice how all the backup-related things come from the same location (Oracle Database > Availability > Backup & Recovery). Obvious right? Wrong!

So it turns out, if you schedule the backup using the (Oracle Database > Availability > Backup & Recovery > Schedule Backups…) screen, you do schedule a backup job, it works and it is visible in the (Enterprise > Job > Activity) screen, but it is not a “real” Cloud Control job. It is actually created using the job type of “Backup”, which is not a supported job type, rather than the job type “RMANScript”.

If you schedule a backup in this way, you can’t:

  • use the job library.
  • do a create-like of an existing job.
  • edit most of the details about the job after it’s created, including the RMAN script.
  • describe the job using EMCLI.

So what is the solution? Well, you do all the prep in the same way, but you don’t actually schedule the job using the (Oracle Database > Availability > Backup & Recovery > Schedule Backups…) screen. You do it directly from the (Enterprise > Job > Activity) screen. If you do it from here, then you get to pick the “RMANScript” job type and suddenly everything works much better and things seem a lot more consistent. Basically, you’ve created a “real” Cloud Control job.

So in my latest round of [enchancement requests | bug reports] I got the answer back from development that I was doing it wrong, which is good to know, and they want to close the SR. My response to that was, why do you let me do it the “wrong way”? Why does the (Oracle Database > Availability > Backup & Recovery > Schedule Backups…) screen not schedule a “real” Cloud Control job? It seems odd to me that you build a product that lets you do things inconsistently. It’s probably a throw back to the old DBConsole stuff, but that doesn’t make it any less annoying.

I’m going to recreate all my backup jobs to make them use the “RMANScript” job type, which will solve my EMCLI issues, but I really think this mess should get cleaned up. I wonder how many other people out there are not creating “real” Cloud Control jobs for their database backups?

I’m going add this blog post to my SR and see what the response is…

Cheers

Tim…

Quick Pluggable Database (PDB) Update…

I’ve been playing around with using SQL Developer and Cloud Control 12cR3 for managing pluggable databases (PDBs) this morning. I’ve added a couple of quick references to my PDB article.

If you understand the DBCA and SQL*Plus approach, the SQL Developer and Cloud Control screens are pretty self explanatory, so I’ve made my references to them brief. 🙂

Cheers

Tim…

Oracle 12c Multitenant Option : CDBs and PDBs…

I started trying to play with the Oracle multitenant option (all that pluggable database stuff) a little while ago and gave up. It’s wasn’t that it was that difficult. More than anything my problem was I didn’t know what to focus on first. There is so much to it and it’s all interrelated, so you start to write about one small piece and before you know it your article has lost focus and is growing too big. As a result I decided to let it simmer in the background and start looking at some of the other smaller 12c new features first…

My next stumbling block was just about everything in 12c seems to relate back to pluggable databases in some way. So you either pretend it doesn’t exist and have gaping wholes in everything you write, or you have to bite the bullet and get to grips with pluggable databases. So back I came to pluggable databases…

Even with my new found commitment to getting to grips with it, I was still struggling to decide how to break this stuff up into manageable pieces so I could write concise articles and order my thoughts on this functionality. Writing is part of my learning process, so if I can’t see how to break down and structure the articles I’m a little lost. Then the recently released OCP syllabus came to the rescue. I always try to write revision notes for the OCP upgrade exam, so I decided the way they break it down will be my template. I’m probably going to write a separate article per bullet point, and try and keep them as directed as possible.

So the first couple of articles have gone live.

Now I’ve got a plan of attack I feel a lot happier.

As always, I will make multiple passes and keep adding things to articles as I encounter things of interest. It’s always a fine balance between being too brief on one hand or regurgitating the manual on the other. The important thing is the content constantly evolves.

The other thing to consider is the OCP syllabus is only a starting point. There is lots of cool stuff that is not on the OCP syllabus, so I’m not going to be a slave to it. As I’ve said before, the OCP syllabus is a nice framework to start you on your journey, but it is not the final goal, it’s just a stepping stone…

I’m not sure why I felt the need to write this little 12c mission statement. Probably to help me solidify in my head how I’m going to tackle the next 18 months while I’m getting to grips with this new version. So that’s the plan, until I come up with a better one… 🙂

Cheers

Tim…

Oracle Certified Professional (OCP) Database 12c…

I just noticed the Database 12c OCP beta exams have started to appear (see here).

Not surprisingly, the multitenant option seems to be a big factor in the content of the both the Advanced Administration and Upgrade exam.

There are a few things that seem a little odd (to me) though:

  • Use Oracle Restart to manage components. Isn’t this a deprecated feature in 12c?
  • Configure and use Oracle Secure Backup. Do they mean Oracle Secure Backup Express?
  • Use Flash Cache.
  • Key DBA Skills (Upgrade Exam only). This whole section looks out of place to me for an upgrade exam.

Ignoring the “Key DBA Skills”, the upgrade exam contains about 49 bullet points, which means the production exam will on average have about 1.5 questions per bullet. The “Key DBA Skills” adds another 34 bullets, so now we are talking less than 1 question per bullet in the production exam…

There is enough new stuff to keep me writing articles for quite a while before I get in a position to sit this exam. I don’t think I’m going to be anywhere near ready before the end of the beta period, which is a pity. I think the only people who are likely to make the beta exam are those people who were on the database beta program, or people who are trying to wing it. Since I fit into neither of those categories, it looks like I’m waiting for the production exam…

Cheers

Tim…

PS. I wrote an Oracle Certification Frequently Asked Questions article a little while back.