Join David Yahalom for an in-depth discussion in this video Complete vs. incomplete recovery, part of Oracle Database 12c: Backup and Recovery.
- [Instructor] Before getting into the nitty gritty of Oracle backup and recovery technologies, let's first discuss some core fundamental concepts when it comes to Oracle database backup and recovery. The first concept we need to flesh out is incomplete vs. complete recovery. Both terms are used to describe different methods of recovering the database from media failure. That is when you lose actual database data files and not just restart your Oracle instance after it has crashed.
When you perform complete recovery, you bring your database to the state where it is fully up to date, including all completed transactions and database modifications up to the present date and time. Essentially, you recover your database to the most current point in time. All of the database changes contained in your database backup file will be recovered. So if you have a backup of your database from say 4 PM and you perform complete recovery using that backup set your database will be restored to its state when the backup was taken.
That is 4 PM. This is different from incomplete recovery which brings your database to a specific point in time in the test. This is also known as point in time recovery, or PITR. I prefer to use the term point in time recovery. When you perform point in time recovery it means that there will be missing transactions or database modification between the point in time to which you decided to restore your database to, let's say 7 PM, and present time, say 8 PM.
In many cases this is actually the desired outcome because there may have been some dead changes made to your Oracle databases, changes that you wish to undo. Using point in time recovery to restore your database to a previous point in time, essentially unrolls those changes. Sounds complex well let's see an example that can help make things simpler. Let's take a look at the sequence of events that occurred in a theoretical database timeline, starting at 2 PM and ending at 8 PM.
At the start of our theoretical timeline the database is well and life is good. At 4 PM you took an Oracle database backup. Nevermind how or using which Oracle backup technology, we'll talk about all of that later. So at 4 PM you took a database backup. Following which life continues on normally and the database is running as smoothly as expected. But then at 7 PM something bad happens, a database catastrophe. A user might have wrongly dropped the table that contains super important data.
Maybe the bonuses for the coming quarter, or maybe there was a database hard drive corruption. Let's assume you, as the DBA, didn't catch that problem until 8 PM. Well you took another full database backup assuming your database data is good and well. Immediately after you took the backup at 8 PM, you detect the problem. What can you do? Restoring the backup from 8 PM isn't an option as the backup already contains the incorrect or bad data.
Using point in time recovery, we can leverage a combination of the full database backup that we took at 4 PM combined with the database archived redo log files which are a backup of the database redo logs which contain a sequence of all events and transactions which have occurred in our database. Using this combination of the backup set, plus the archived redo logs, we can restore our database to a specific point in time.
In our case, 6:59:59 PM. Just before the data loss occurred, and essentially undoing the bad changes which have occurred to the database. This is why point in time recovery or incomplete recovery is often so useful.
- Complete vs. incomplete recovery
- Point-in-time recovery
- Failure categories
- Preparing the virtual machine
- Performing user-managed backups
- Restoring from manual backups
- Performing full and incremental backups with RMAN
- Recovering a PDB and CDB
- Exporting and importing data with Oracle Data Pump
- Performing logical backups with Data Pump
- Using Flashback Query, Table, and Database
- Creating restore points