Sometimes it’s not bad to look back to the basics. Due to circumstances I’m focusing more on backups nowadays. This time I wanted to have a closer look to controlfile autobackups. Oh by the way, for the one who follow me on twitter, yes, it’s this post which is related to this picture 🙂

Most of the tests I do, I do them at night (not this one, because I needed it for my customer) and yes, I write most of my drafts on a very relaxed place. This is almost a picture perfect evening, so sorry for my photography skills, but here we go.

Imagine following situation, let’s say a 65TB database, running in archivelog mode and taking a weekly full backup with daily incrementals which “normally succeed”. Then a release moment comes up and new tablespaces are to be added and, despite int and uat and other environments for quality testing, a problem arises and you got an e-mail “Dear Dba, During the process the wrong tablespace is dropped, can you please get it back?”.

First answer is … yes, it depends. Well that’s the imaginary situation I had in mind and the idea is to demonstrate a TSPITR (tablespace point in time recovery ) in a pluggable database without having to restore the full database and export import it. It is not too hard, but it is nothing you do every day, so I decided to document it.

Controlfile autobackup

While doing this tests, I ran into an issue. In Oracle 12c the controlfile autobackup is “ON” by default in rman.

So we’re good with that. Starting from Oracle 11.2 (check the documentation here) the controlfile autobackup kicks off after several structural changes.

“Beginning with Oracle Database Release 11g Release 2, RMAN takes only one control file autobackup when multiple structural changes contained in a script (for example, adding tablespaces, altering the state of a tablespace or data file, adding a new online redo log, renaming a file, and so on) have been applied.”

So this leaves a place of doubt in my opinion. How many changes? How much time, it’s not documented. But … as always… there is a solution for this. There exists an underscore parameter “_controlfile_autobackup_delay” Which specifies a hard delay when Oracle should perform controlfile autobackups (if necessary).

I give the query to list the underscore parameters, you can be creative with it and get out of the db what you want know.

So anyhow, before 11.2 the database was doing controlfile autobackups at every change and that caused performance overhead, so now Oracle decided to bundle some operations (what’s the risk in a controlled environment anyway) and perform the backups less frequently. Which is, I think, a good thing. It’s only annoying for testing, so for testing I have put my delay to 0 which basically instructs the database to perform a controlfile autobackup when a structural change occurs.  I’m not telling you to do this in production environments, but I’m raising the awareness that this parameter exists and if the default does not fit you, you might want to consider to change it. The default is 300 seconds and this is, according me, a good trade-off between performance and safety.

So to demonstrate the tests, I did this in my cdb$root. I set the auto backup to 0 (to speed things up for the tests a little) and switched to my pdb:

Recover a tablespace

The scenario is pretty simple:

• Create a tablespace
• create a table in the tablespace
• add a record in the table
• check it
• drop the tablespace
• get it back without restoring the full database

then we want to get the tablespace back without having to restore everything. The rest of the database remains running so that means that we will have to do an incomplete recovery of one tablespace. Cool thing is … rman does everything for you. So you don’t have to do “alter database create datafile … ” anymore, just tell rman “recover tablespace …” and he will create an auxiliary instance for you, restore only the bare necessities  and perform the export/import for you. This without restoring the full 65TB. So, this is how it works:

you see I selected AND the scn AND the file_id AND the date/time. Basically to show how to gather the information on which you can to the recovery. The date/time is also available from the alertlog. It will tell you when the tablespace has been dropped. Basically that will be the most realistic situation and I’ll demonstrate also how to recover it from to a given timestamp.

VERY IMPORTANT. And this is what has bitten me in my own lab. I know, blame me… but rman/oracle cannot recover things it doesn’t know. That’s also why I wrote the first part about the auto backup. This is where all pieces start to fit together. If you don’t have an autobackup from you controlfile, the tablespace/datafile information is not included and Oracle/rman doesn’t know so no recovery possible. So make sure your controlfile backups are working properly. That is also why I spend a bit time to search for the underscore parameter, to facilitate my tests. Remember … before 11.2 changes were recorded automatically. Starting from 11.2 … changes are grouped before autobackup occurs. So pay attention.

Recovery

So it’s time to get our data back isn’t it? I always use my recovery catalog for doing backups and recovery and this is one of the cases (without a resync) it’s not going to work. The information is only available in the controlfile and not yet in the catalog, therefor i connected to rman just very easy as

then the recovery is actually a oneliner:

Here we tell rman to recover a tablespace in pdb s04n02pdb1 which has tablespace name “droptst”. We would like rman to recover it until a certain timestamp (which we retrieved earlier, alert log/query,… remember?) and to do so, rman will need an auxiliary instance, so we need to tell rman where he can put it. That’s pretty it.  Here is the log it creates:

You see that he crates an auxiliary instance, only containing the data it needs. And thus not the full database, which is the behavior we want. When the auxiliary instance has been setup and recovered, rman will launch datapump to  get the data back in the database. Let this be exactly what we want 🙂

So, depending on the size, this will take a while. But when it finishes the tablespace is back in the pdb. Except that is offline. As long as you know that, it’s not a big deal. Just put it online (if you’re sure this is the data you want).

So this is how you get your data back very easily.

Only take into account that, if you want to do this, you will need a good backup of the controlfile, which autobackup provides to you.

So that’s all for now folks.
As always, questions, remarks? find me on twitter @vanpupi