Sometimes you might run into situations where the controlfile does not represent the backups and archivelogs correctly, because of a mismatch of the control_file_record_keep_time and the RMAN retention. The controlfile has non circular and a circular records. Non circular are e.g. database information, redo threads, datafiles and so on. These non circular records don’t age out, however, they can be reused, e.g. when a tablespace is dropped. The circular records are e.g. the log history, archived logs, backupsets, datafile copies and so on. These records can age out. So, when you have a control_file_record_keep_time of 7 days and a RMAN recovery window of 14 days, then you obviously have a mismatch here. In 11gR2, Oracle stores 37 different record types in the control file, which can be check with:
12cR1 stores 41 different record types, where the AUXILIARY DATAFILE COPY, MULTI INSTANCE REDO APPLY, PDB RECORD and PDBINC RECORD was added. In 12cR2 there are even more. The TABLESPACE KEY HISTORY record type was added, so you end up with 42 different record types in 12cR2.
If RMAN needs to add new backup set or archive log record to the control file, any records that expired as per the control_file_record_keep_time parameter are overwritten. But coming back to my issue. My controlfile is out of sync with the recovery catalog and in some situation you can’t correct it anymore, even with delete force commands or alike, and you end up with error like the following:
There might be other solutions to fix it, however, I want to have a clean control file and so I am recreating it manually. However, I don’t want to open the DB with resetlogs.
The high level steps to get this done are
Disable everything that might interfere with your action e.g. Fast Start Failover, Broker and so on
Adjust your control_file_record_keep_time to a higher value
Create the controlfile to trace
Unregister from RMAN catalog
Shutdown immediate and re-create the controlfile
Re-catalog your backups and archivelogs
Re-register into the RMAN catalog
Ok, let’s get started and disable fast start failover first. We don’t want that the observer to kick in and do any nasty stuff during my action.
As a next step, I increase the control_file_record_keep_time to a much higher time. The formula is usually CONTROL_FILE_RECORD_KEEP_TIME = retention period + level 0 backup interval + 1. Meaning that with a retention period of 24 days and a weekly level 0 backup, it would be 24+7+1, so at least 32. But I don’t care if my controlfile is 20MB in size 30MB, so I set it directly to 72 days.
The next important step is to create a trace of the controlfile, which can be adjusted manually later on, depending on your needs. Beforehand, I specify a tracefile identifier, so that I easily spot my trace file in the DIAG destination.
Let’s take a look at the control file trace which was created. It contains nearly everything that we need. Some parts might have to be adjusted, and some parts do not work at all or have to be done in a different way, but we will see later. But in general it is a very good starting point to get the job done.
I am also stopping the broker to avoid any side effects and afterwards I unregister the database from the RMAN catalog. I will re-create it later on with the clean entries.
The next step is very important. We need to shutdown the DB cleanly, either with normal or immediate. Afterwards, I create a copy of the current controlfiles. You never know, it is always good to have another fallback.
Now we can startup nomount, and recreate our control from scratch. It is very important that you specify REUSE and NORESETLOGS here.
Now we can configure the RMAN persistent settings like retention and so on.
The next step is to the re-create the incarnation table. This might fail with a recursive SQL error if you use the SQL provided in the trace file. Just use REGISTER PHYSICAL LOGFILE instead of REGISTER LOGFILE and then it works.
Because I have shutdown the database cleanly, there is no need to do any recovery and I can continue to enable the block change tracking file, open the database, and add my tempfile back to the database.
Regarding the Standby Redo logs, the easiest is to remove the old ones, and simply recreate them afterwards, because you can’t add them back as long as they have Oracle managed file names.
Don’t forget to enable Flashback as well, if your DataGuard is running in Max availability mode.
Now we need to recatalog all our backups and archivelogs again.
We are nearly done. We just need to enable the Data Guard broker again and enable fast start failover.
Re-register the database into the RMAN catalog.
Ready. That’s it. In case your Standby Controlfile is not correct as well (was not in my case), you can now simply create a new standby controlfile on the primary and move it to the standby like documented in the following MOS note (Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1).
Conclusion
As a last resort, sometimes it is needed to recreate the controlfile manually, but in case you have all the online redo logs and your datafiles in place, you can do it with noresetlogs. And take care that your RMAN retention is always higher than your control file record keep time.
Cet article Oracle 12c – Recreating a Controlfile in a Data Guard environment with noresetlogs est apparu en premier sur Blog dbi services.