Network based recovery (12c new feature)

recover-standby-network-oracle-12c

In this article, I’ll explain how to manualy recover a standby database using the oracle RMAN new feature “RECOVER FROM SERVICE”.

Configuration for the test

HOSTS : 2 Virtual Machines with 2 GB of Memory
OS : Oracle Linux 6.4
ORACLE VERSION : Oracle 12.1.0.1 Standard Edition

A database named DB12C in archivelog mode has been created on the first host using DBCA, a listener is running on each server on the default port 1521 and the tnsnames.ora file has been defined as follow :

Standby database creation

We are going to create the standby database by duplicating the primary database for standby using RMAN. Since 11g no backup is needed, you can duplicate directly from an active database.
Before running the duplicate, we need to copy the spfile and the password file of the first database to the second server:

Second step is to create the directory structure which will contain the datafiles,archivelogs and audit files on the second host:

Before we can launch the duplication we have to create a pfile from the spfile. Once your pfile is created, drop your spfile and put your standby database in nomount mode :

We are now ready to launch the duplication. Here is the RMAN script that I used for my duplication, this script has to be run from the standby host:

here is the output of the script :

An important note is that you must connect to your auxiliary database using a service name and not using os authentication because during a duplication from active database, the primary database communicate with the standby using the service.

add the database to your oratab file:

Recover the standby database

To maintain ou standby database up to date we will use a new Oracle 12c RMAN functionality which is : RECOVER DATABASE FROM SERVICE

First we should verify than our standby is ready for recover :

We are now ready for recovery, we will now run the following RMAN script on standby server in order to recover our standby database.

Here is the output from the script:

As you can see, only the datafiles are restored, no archivelog applied, no controlfile restored, this means the standby database is not consistent, this is not a problem if you use dataguard as the redologs are applied in real time to the standby but in my case I don’t use dataguard and I need my standby to be consistent every time I recover it.

At this step, my standby controlfile is not in sync with my restored datafiles because it contains newer scn than my restored datafiles, I need to restore a new controlfile for standby from the primary database:

What happens now if I try to open my standby database?

By looking carefully, the datafile 1 that oracle is searching has the name of the primary database instead of the datafile restored, that makes sense because the controlfile comes from the primary database, we need to register the restored files to the restored controlfile:

My restored files are now known from my controlfile as datafiles copies, what I need to do now, is to switch all my datafiles to these copies :

Ok that looks good for the datafiles but what about the onlinelog ?

[oracle@svr-ora-04 archivelog]$ ls -l /u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9jppbtd0_.log
ls: cannot access /u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9jppbtd0_.log: No such file or directory
[oracle@svr-ora-04 archivelog]$ ls -l /u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9jppbtho_.log
ls: cannot access /u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9jppbtho_.log: No such file or directory
[oracle@svr-ora-04 archivelog]$

Same problem, the filenames are from the primary database, I need to clear the redologs register them with the controlfile :

Now, all my files are known of my controlfile, lets try to open the standby database again:

No more luck, but if we think about it for a minute, no archivelogs have been applied since the datafiles restoration, so the database is not intended to be consitent, I’m going to switch current log from the primary and copy the archivelogs to the standby host.

I have now to register these files to the controlfile to be able to use them :

Only the last one has been registered, the others are already registered. The question now is, until which scn will I have to restore the standby database to be consistent, we can use the following RMAN command to find out:

Ok, so lets try to recover our database beyond SCN 2638258, I’ll choose SCN+1 :

It doesn’t looks good, why it is not working ? This is because our controlfile has been restored after our datafiles, so it has a newer SCN than our datafiles, let’s check the current SCN of the controlfile :

We will have to recover the database beyond this SCN to be able to open our standby database :

Here we go, our database is finaly consistent, we can open it :

Conclusion :

This is not a good approach to recover a stanby regularly, first, this method uses incremental backups and as I use the standard edition, block change tracking is not enabled which implies that RMAN have to scan all my datafiles to see what changes have been made every time I recover my standby, this could be problematic for large databases. Secondly it requires many steps to open the standby database, it’s complicated.
The only interesting usage I can see is if you want to recover the standby database from a large gap sequence, in that case, this method should be more efficient than applying all missing archivelogs sequences.

Leave a Reply