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 youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator