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.
Start the discussion at forums.toadworld.com