Toad World Blog

Oracle 12cR2 RMAN New Feature: UNTIL AVAILABLE REDO

Jun 2, 2017 3:04:00 PM by Deiby Gomez

Introduction

Oracle has introduced several new features in its new version Oracle Database 12.2.0.1.0 and RMAN it is not the exception. Most of the DBA would agree that one of the difficult tasks whenever a database needs to be restored is to calculate the SCN, or the Sequence to use in the “RECOVER DATABASE UNTIL (…)” operation, in order to apply as many archived logs as possible, to recover as much data as possible. Every DBA has different methods to discover the target SCN or the target Sequence.

 Some use the “PREVIEW” clause, some others the view v$log, some others the RMAN “LIST” commands, and so on. The problem is that when the calculation is not correct, and the database that is being restored is huge (let’s say 8TB), an error on the “RECOVER” phase might take us to restore the whole database from scratch. In Oracle database 12.2.0.1.0 the clause “UNTIL AVAILABLE REDO” is available. As its name indicates, this clause makes all the required calculations to recover the database up to the last available archive log. This is a really cool feature, since all the DBA has to do is catalog all the archivelogs available and use “UNTIL AVAILABLE REDO” in the “RECOVER DATABASE” phase, and Oracle will do all the work., This also lets us avoid human error in the calculations.

In order to show how this feature works I will use an empty database with the table DGOMEZ.COUNTRY; currently it has no rows.  This database is in archivelog mode.

 

Performing a backup:

RMAN> backup database;

Starting backup at 07-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/others/db1/DB1/datafile/o1_mf_system_djyxzjxt_.dbf
input datafile file number=00003 name=/others/db1/DB1/datafile/o1_mf_sysaux_djyy0ynm_.dbf
input datafile file number=00004 name=/others/db1/DB1/datafile/o1_mf_undotbs1_djyy23sy_.dbf
input datafile file number=00007 name=/others/db1/DB1/datafile/o1_mf_users_djyy24y4_.dbf
channel ORA_DISK_1: starting piece 1 at 07-MAY-17
channel ORA_DISK_1: finished piece 1 at 07-MAY-17
piece handle=/others/db1/fra/DB1/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T155509_djyywznq_.bkp tag=TAG20170507T155509 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:38
Finished backup at 07-MAY-17

Starting Control File and SPFILE Autobackup at 07-MAY-17
piece handle=/others/db1/fra/DB1/autobackup/2017_05_07/o1_mf_s_943372550_djyyy6vo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAY-17

I will insert a row with the value ‘Guatemala’ into the table, the row will be committed and a new archived log will be generated:

SQL> insert into dgomez.country values ('Guatemala');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.

A second row with the value ‘Canada’ will be inserted into the table, the row will be committed and a new archived log will be generated:

SQL> insert into dgomez.country values ('Canada');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.

A last row with the value ‘Colombia’ will be inserted into the table, the row will be committed and a new archived log will be generated:

SQL> insert into dgomez.country values ('Colombia');
1 row created 
SQL> commit;
Commit complete.
SQL> alter system switch logfile; 
System altered.

You can see that there were three archived logs created. This is because for every row that was inserted we executed a switch of the log file, and that resulted in the creation of a new archived log.

[oracle@nuvola2 2017_05_07]$ ls -ltr
total 155072
-rw-r----- 1 oracle dba 158784512 May  7 15:59 o1_mf_1_1_djyz5fgk_.arc
-rw-r----- 1 oracle dba      2560 May  7 16:00 o1_mf_1_2_djyz6dyd_.arc
-rw-r----- 1 oracle dba      3072 May  7 16:00 o1_mf_1_3_djyz723j_.arc
[oracle@nuvola2 2017_05_07]$

Confirming the three rows are in the table:

SQL> select * from dgomez.country;
NAME
--------------------
Guatemala
Canada
Colombia

Basically what I have done is what the following picture explains.  Initially the database was empty. The row with the value ‘Guatemala’ was inserted and then I generated an archived log (#1). I repeated these steps with the value ‘Canada’ and ‘Colombia’ respectively.

 

 

First Test – Using all the archived logs generated:

The first test that I will perform is to use these three newly generated archived logs to recover the database. For this I will simulate that all the datafiles of the existing database were deleted and we have to restore and recover the database.

 

Shutting down the existing database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

 

Mounting the database:

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  843055104 bytes
Fixed Size              8626288 bytes
Variable Size         322965392 bytes
Database Buffers      507510784 bytes
Redo Buffers            3952640 bytes
Database mounted.

 

Deleting datafiles and online logs in order to simulate a storage damage:

[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/DB1/datafile/*
[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/DB1/onlinelog/*
[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/fra/DB1/onlinelog/*

 

Restoring the database:

RMAN> restore database;

Starting restore at 07-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /others/db1/DB1/datafile/o1_mf_system_djyxzjxt_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /others/db1/DB1/datafile/o1_mf_sysaux_djyy0ynm_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /others/db1/DB1/datafile/o1_mf_undotbs1_djyy23sy_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /others/db1/DB1/datafile/o1_mf_users_djyy24y4_.dbf
channel ORA_DISK_1: reading from backup piece /others/db1/fra/DB1/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T155509_djyywznq_.bkp
channel ORA_DISK_1: piece handle=/others/db1/fra/DB1/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T155509_djyywznq_.bkp tag=TAG20170507T155509
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 07-MAY-17

 

Recovering the database:

Here is where the magic happens. All we have to do is use the “UNTIL AVAILABLE REDO” clause and Oracle automatically will apply all the archived logs that have registered into its control file or a catalog; if a catalog is used. There is no need to perform calculations for the target SCN.

RMAN> recover database until available redo;

Starting recover at 07-MAY-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_1_djyz5fgk_.arc
archived log for thread 1 with sequence 2 is already on disk as file /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_2_djyz6dyd_.arc
archived log for thread 1 with sequence 3 is already on disk as file /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_3_djyz723j_.arc
archived log file name=/others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_1_djyz5fgk_.arc thread=1 sequence=1
archived log file name=/others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_2_djyz6dyd_.arc thread=1 sequence=2
archived log file name=/others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_3_djyz723j_.arc thread=1 sequence=3
warning: attempt media recovery until thread 1, sequence 4
Finished recover at 07-MAY-17

We can see that the three archived logs were applied automatically and there were no errors.

 

Opening the database in resetlogs:

SQL> alter database open resetlogs; 
Database altered.

 

Verification of the data:

SQL> select * from dgomez.country;
NAME
--------------------
Guatemala
Canada
Colombia

Since the three rows are there, we can confirm that Oracle indeed applied the three archived logs automatically, without our having to specify any target SCN or target sequence.

 

Second Test – Deleting the last two archived logs:

The test that I will perform now is with the last two archived logs deleted and only the first archived log available. I will again use the UNTIL AVAILABLE REDO clause and Oracle should be able to discover that the maximum time to which the database can be recovered is right after the first row was inserted (with the value ‘Guatemala’).  

 

Shutting down the existing database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

 

Mounting the database:

SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  843055104 bytes
Fixed Size              8626288 bytes
Variable Size         322965392 bytes
Database Buffers      507510784 bytes
Redo Buffers            3952640 bytes
Database mounted.

 

Deleting datafiles and online logs in order to simulate a storage damage:

[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/DB1/datafile/*
[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/DB1/onlinelog/*
[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/fra/DB1/onlinelog/*

 

Confirming that our three archived logs are there:

[oracle@nuvola2 2017_05_07]$ ls -ltr  /others/db1/fra/DB1/archivelog/2017_05_07/*
-rw-r----- 1 oracle dba 158784512 May  7 15:59 /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_1_djyz5fgk_.arc
-rw-r----- 1 oracle dba      2560 May  7 16:00 /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_2_djyz6dyd_.arc
-rw-r----- 1 oracle dba      3072 May  7 16:00 /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_3_djyz723j_.arc

 

Deleting the last two archived logs that were generated:

[oracle@nuvola2 2017_05_07]$ rm -rf  /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_3_djyz723j_.arc
[oracle@nuvola2 2017_05_07]$ rm -rf /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_2_djyz6dyd_.arc

 

Confirming that only the first archived log is available now:

[oracle@nuvola2 2017_05_07]$ ls -ltr  /others/db1/fra/DB1/archivelog/2017_05_07/*
-rw-r----- 1 oracle dba 158784512 May  7 15:59 /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_1_djyz5fgk_.arc
[oracle@nuvola2 2017_05_07]$

The following image explains what we are doing. We deleted the last two generated archived logs in order to test whether Oracle is aware of it and whether it automatically handles the situation and applies all the redo data in the first archived log. If Oracle performs its job well, at the end, we will be see only one row inserted with the value ‘Guatemala’.

 

 

Restoring the database:

RMAN> restore database;

Starting restore at 07-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /others/db1/DB1/datafile/o1_mf_system_djyznwbl_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /others/db1/DB1/datafile/o1_mf_sysaux_djyznwby_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /others/db1/DB1/datafile/o1_mf_undotbs1_djyznwc9_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /others/db1/DB1/datafile/o1_mf_users_djyznwcn_.dbf
channel ORA_DISK_1: reading from backup piece /others/db1/fra/DB1/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T155509_djyywznq_.bkp
channel ORA_DISK_1: piece handle=/others/db1/fra/DB1/backupset/2017_05_07/o1_mf_nnndf_TAG20170507T155509_djyywznq_.bkp tag=TAG20170507T155509
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:22
Finished restore at 07-MAY-17

 

Recovering the database:

RMAN> recover database until available redo;

Starting recover at 07-MAY-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_1_djyz5fgk_.arc
archived log file name=/others/db1/fra/DB1/archivelog/2017_05_07/o1_mf_1_1_djyz5fgk_.arc thread=1 sequence=1
warning: attempt media recovery until thread 1, sequence 2
Finished recover at 07-MAY-17

You can see that Oracle automatically discovered that only one archived log is available and automatically calculated the target sequence for the database to be recovered.

 

Opening the database with resetlogs:

RMAN> alter database open resetlogs; 
Statement processed
 
Confirming the data:
RMAN> select * from dgomez.country;
NAME               
--------------------
Guatemala          

We can see that the result is correct. Since only the first archived log was applied, only the row with the value ‘Guatemala’ exists in the table.

 

Conclusion

Definitely the ‘UNTIL AVAILABLE REDO’ clause is something DBAs have been waiting for, since it eliminates time spent calculating the target SCN or sequence and also removes the risk of human error in the calculations that in might result in having to restore the entire database from scratch. That would be acceptable for small databases, but for huge, multi-terabyte databases it’s not acceptable.  Oracle has made our life easier.

Tags: Oracle

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

  • Oracle Database Cloud Administrator Certified Associate
  • Oracle Database Cloud Administrator Certified Professional
  • Oracle Database Cloud Service Operations Certified Associate
  • Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
  • Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
  • Oracle Database 12c Maximum Availability Architecture Certified Master
  • Oracle Database 12c: Data Guard Administrator
  • Oracle Database 12c Administrator Certified Master (OCM 12c)
  • Oracle Real Application Clusters 12c Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Master (OCM 11g)
  • Oracle Database 12c Administrator Certified Professional (OCP 12c)
  • Oracle RAC 11g and Grid Infraestructure Administrator
  • Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
  • Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
  • Oracle Database 11g Administrator Certified Professional (OCP 11g)
  • Oracle Linux Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Associate