Oracle 19c - Database Backup and Restore Validation Process Using RMAN - Backup and Restore - Session 2!
Oracle 19c - Database Backup and Restore Validation Process Using RMAN!
In this session, we shall mainly focus on RMAN backups. This brings us to a point of knowing how exactly we perform the backup.
I have categorized it into the following four sections
What is a Backup?
Why Backup?
How to Backup?
How to test/validate your Database Backup – Recovery Strategies?
What is a Database Backup?
Before we begin to learn more about backups, we need to understand an organization’s most important asset – Data. Considering your organization runs on Oracle database.
Data of an organization is the most integral part of an organization. Consider a retailing, banking company. They all have enormous amounts of data – user, system, etc. As a database administrator, System administrator or any personnel who has been assigned the job to protect this data should be aware of how important data is to an organization. How to make sure the data is always available? A backup is an exact copy of your database which can help you restore or reconstruct your data in case of any data loss.
Why Backup Database?
Consider a simple case where your banking organization who has data regarding millions of customers in terms of account numbers, names, nominees, bank balance and the organization lost all of their data, how would their customers react to it? How would the organization deal with the pressure of losing so much data? How would they be answerable to so many customers dissatisfaction?
This is why we backup this data so that in case of any failure of a disk (storage), the disk controller (storage controller) we can always rely on our backup from where we can restore it into the database i.e. storage filesystem and not have customers lose any of their data.
Why Backup Database?
Consider a simple case where your banking organization who has data regarding millions of customers in terms of account numbers, names, nominees, bank balance and the organization lost all of their data, how would their customers react to it? How would the organization deal with the pressure of losing so much data? How would they be answerable to so many customers dissatisfaction?
This is why we backup this data so that in case of any failure of a disk (storage), the disk controller (storage controller) we can always rely on our backup from where we can restore it into the database i.e. storage filesystem and not have customers lose any of their data.
How to Backup the Database?
To backup data in an Oracle Database, we have several methods. They can be broadly classified as physical and logical backups
Method #1 - Physical Backups:
Third Party Backup Tools:
Such as Veritas NetBackup, SAP, IBM Tivoli Manager, EMC, HP, and DDBoost
User-Managed Backups:
Backup of a database using OS utilities such as copy( windows), cp (Unix).
Oracle Secure Backup:
My favourite and the most preferred recommended Oracle utility – Recovery Manager (RMAN).
Method #2 - Logical Backups:
Conventional Export/import utilities and Datapump utilities. A logical backup is a backup of logical data – objects such as tables, indexes etc which are constituents of a database independent of the location of the above objects.
To understand physical and logical storage structures of a database you could refer to this and this oracle documentation.
Which is the best method for Database Backup?
Each of these backup strategies has their own pros and cons and we shall not deal too much with them in this session.
We need to understand that unless you have a physical backup in place, just having a logical backup isn’t always safe against physical data corruption, hardware storage issues. Having a valid, good physical backup makes it a good backup and recovery strategy. Always make sure you have a physical backup in place.
In reality, we can use any of the above methods but we always need to make sure we have a good backup and recovery strategy in place to avoid any unnecessary hiccups during the course of the operation of a database. Testing your back and recovery strategies on a mirrored test system is always advised so that we can predict the amount of time it takes to get your database up and running in case of any unforeseen situations.
Recovery Manager - RMAN:
Recovery Manager (RMAN) is an Oracle utility that can backup, restore, and recover database files. The product is a feature of the Oracle database server and does not require separate installation. Recovery Manager is a client/server application that uses database server sessions to perform backup and recovery.
Oracle RMAN (Oracle Recovery Manager) Backup Commands:
We can backup data either with help of Oracle Enterprise Manager (OEM) GUI mode or through OS command line prompt.
RMAN is a robust, sophisticated tool provided by Oracle to perform Backup and Recovery.
RMAN is automatically installed when you install the Oracle database so there isn’t any additional installation required to use RMAN.
The RMAN environment comprises of two components:
Target database (the database which you would backup, perform recovery of and
RMAN client which is the client which interprets user commands and executes them on behalf of the user while connecting to the Target Database.
A simple command to connect to the database using RMAN is as follows:
[oracle@srlab admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Dec 11 20:29:46 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRLAB (DBID=25965068)
DBID here is the unique identifier which is unique to each database we are planning to work with.
In this example, we are dealing with a database named SRLAB. We shall backup the data which belongs to the SRLAB database.
Since a backup is a physical copy of your database, we need a location/directory where we can save them.
To achieve this, we can make use of a special directory named db_recovery_file_dest which serves as the backup location. Define the size of this parameter with db_recovery_file_dest_size which marks the size of this backup location.
Although we have several ways to compress your backups and several techniques which can reduce the size of a backup, try to at least set the DB_RECOVERY_FILE_DEST_SIZE to a size of your actual data in your database.
Make sure you account for archive logs as well which is nothing but offline redo logs which records changes to your data blocks.
Your backup strategy would consist of all the files related to the database such are datafiles, control files, parameter files, network related files, archived redo log files.
RMAN or any other physical backup tool can backup datafiles, control files, parameter files, archived redo log files. Network related files need to be backed up manually using OS utilities such as cp or copy.
To backup a database we use:
"Backup database" – it's as simple as that. So, let’s begin to backup my lab SRLAB database. Since we have already connected to the Target database (SRLAB), we fire the "backup database" command.
RMAN> backup database;
Starting backup at 11-DEC-2021 20:36:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=268 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/data/SRLAB/system01.dbf
input datafile file number=00003 name=/oradata/data/SRLAB/sysaux01.dbf
input datafile file number=00004 name=/oradata/data/SRLAB/undotbs01.dbf
input datafile file number=00007 name=/oradata/data/SRLAB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-DEC-2021 20:36:57
channel ORA_DISK_1: finished piece 1 at 11-DEC-2021 20:40:42
piece handle=/backup/srlab/060gg3j9_1_1 tag=TAG20211211T203656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45
Finished backup at 11-DEC-2021 20:40:42
Starting Control File and SPFILE Autobackup at 11-DEC-2021 20:40:43
piece handle=/backup/srlab/c-25965068-20211211-00 comment=NONE
Finished Control File and SPFILE Autobackup at 11-DEC-2021 20:40:51
Here, we observe that the backup of all related files of the database – datafiles, control files, spfile (parameter file) has been completed. The backup operation took about 4 minutes and 4 seconds (Elapsed time). This is a small LAB database with just 5 datafiles so it took very less time to backup.
In cases where we want to backup data from databases of giant organizations, there could be hundreds of data files and each datafile could be in terabyte sizes and taking a complete backup of the database could potentially take hours of time. To know the details regarding the backup we just created, we shall execute:
RMAN> list backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
6 Full 287.02M DISK 00:03:42 11-DEC-2021 20:40:39
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TAG20211211T203656
Piece Name: /backup/srlab/060gg3j9_1_1
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- -------------------- ----------- ------ ----
1 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/system01.dbf
3 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/sysaux01.dbf
4 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/undotbs01.dbf
7 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
7 Full 1.03M DISK 00:00:04 11-DEC-2021 20:40:48
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: TAG20211211T204044
Piece Name: /backup/srlab/c-25965068-20211211-00
SPFILE Included: Modification time: 11-DEC-2021 19:55:30
SPFILE db_unique_name: SRLAB
Control File Included: Ckp SCN: 2164323 Ckp time: 11-DEC-2021 20:40:44
RMAN> VALIDATE DATABASE;
Starting validate at 11-DEC-2021 20:48:55
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oradata/data/SRLAB/system01.dbf
input datafile file number=00003 name=/oradata/data/SRLAB/sysaux01.dbf
input datafile file number=00004 name=/oradata/data/SRLAB/undotbs01.dbf
input datafile file number=00007 name=/oradata/data/SRLAB/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 18027 115208 2164300
File Name: /oradata/data/SRLAB/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 79333
Index 0 12693
Other 0 5147
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 21645 70400 2164527
File Name: /oradata/data/SRLAB/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6160
Index 0 3212
Other 0 39383
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 344 38400 2164527
File Name: /oradata/data/SRLAB/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 38056
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 101 641 1252497
File Name: /oradata/data/SRLAB/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 60
Index 0 15
Other 0 464
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 646
Finished validate at 11-DEC-2021 20:49:06
Validate RMAN Backups:
How do we test or validate our database backups that we can restore or recover our database during any crisis?
If due to hardware failure or some corruption of your storage disks, we would need a good backup available to restore this corrupted data so that we do not lose any data that belonged to that storage files.
It all depends on how you have designed the backups, the intervals at which the backups are scheduled, whether you take a full backup and have incremental backups.
In case of user errors – such as an unnecessary manipulation of data, we can restore parts of data or all of data that have been changed through logical backups.
In practice, we should be aware of and foresee any errors that could occur in the future and test every strategy to evade them.
Use BACKUP VALIDATE command to validate backup files:
RMAN>
The command for only physical corruption check:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Starting backup at 11-DEC-2021 20:56:31
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1089813646
input archived log thread=1 sequence=6 RECID=2 STAMP=1089814263
input archived log thread=1 sequence=7 RECID=3 STAMP=1089814557
input archived log thread=1 sequence=8 RECID=4 STAMP=1091044543
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
List of Archived Logs
=====================
Thrd Seq Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1 5 OK 0 388835 /oradata/archive/1_5_1089795600.dbf
1 6 OK 0 556 /oradata/archive/1_6_1089795600.dbf
1 7 OK 0 88 /oradata/archive/1_7_1089795600.dbf
1 8 OK 0 644 /oradata/archive/1_8_1089795600.dbf
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/data/SRLAB/system01.dbf
input datafile file number=00003 name=/oradata/data/SRLAB/sysaux01.dbf
input datafile file number=00004 name=/oradata/data/SRLAB/undotbs01.dbf
input datafile file number=00007 name=/oradata/data/SRLAB/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:56
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 18024 115208 2164814
File Name: /oradata/data/SRLAB/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 79336
Index 0 12693
Other 0 5147
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 21637 70400 2164668
File Name: /oradata/data/SRLAB/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6168
Index 0 3212
Other 0 39383
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 344 38400 2164814
File Name: /oradata/data/SRLAB/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 38056
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 101 641 1252497
File Name: /oradata/data/SRLAB/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 60
Index 0 15
Other 0 464
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 646
Finished backup at 11-DEC-2021 21:02:16
RMAN> BACKUP VALIDATE DATABASE;
Starting backup at 11-DEC-2021 21:07:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/data/SRLAB/system01.dbf
input datafile file number=00003 name=/oradata/data/SRLAB/sysaux01.dbf
input datafile file number=00004 name=/oradata/data/SRLAB/undotbs01.dbf
input datafile file number=00007 name=/oradata/data/SRLAB/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 18024 115208 2165496
File Name: /oradata/data/SRLAB/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 79336
Index 0 12693
Other 0 5147
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 22616 71680 2165457
File Name: /oradata/data/SRLAB/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6268
Index 0 3295
Other 0 39501
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 344 38400 2165496
File Name: /oradata/data/SRLAB/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 38056
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 101 641 1252497
File Name: /oradata/data/SRLAB/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 60
Index 0 15
Other 0 464
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 646
Finished backup at 11-DEC-2021 21:12:25
As you can observe above the Status of each file is “OK” which means these are usable and can be used to restore the files at any point of time.
We can perform a preview of the database restore. This gives you a nice list of files and their availability without actually restoring the files.
Use RESTORE command to validate backup:
RMAN> RESTORE DATABASE VALIDATE;
Starting restore at 11-DEC-2021 21:16:50
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /backup/srlab/060gg3j9_1_1
channel ORA_DISK_1: piece handle=/backup/srlab/060gg3j9_1_1 tag=TAG20211211T203656
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:02:25
Finished restore at 11-DEC-2021 21:19:16
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;
Starting restore at 11-DEC-2021 21:49:33
using channel ORA_DISK_1
channel ORA_DISK_1: scanning archived log /oradata/archive/1_5_1089795600.dbf
channel ORA_DISK_1: scanning archived log /oradata/archive/1_6_1089795600.dbf
channel ORA_DISK_1: scanning archived log /oradata/archive/1_7_1089795600.dbf
channel ORA_DISK_1: scanning archived log /oradata/archive/1_8_1089795600.dbf
Finished restore at 11-DEC-2021 21:49:38
RMAN> RESTORE DATABASE PREVIEW;
Starting restore at 11-DEC-2021 21:52:20
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
6 Full 287.02M DISK 00:03:42 11-DEC-2021 20:40:39
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TAG20211211T203656
Piece Name: /backup/srlab/060gg3j9_1_1
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- -------------------- ----------- ------ ----
1 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/system01.dbf
3 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/sysaux01.dbf
4 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/undotbs01.dbf
7 Full 2164104 11-DEC-2021 20:36:57 NO /oradata/data/SRLAB/users01.dbf
archived logs generated after SCN 2164104 not found in repository
recovery will be done up to SCN 2164104
Media recovery start SCN is 2164104
Recovery must be done beyond SCN 2164104 to clear datafile fuzziness
Finished restore at 11-DEC-2021 21:52:22
Conclusion:
These are just simple techniques to verify your Oracle RMAN backups. Hope you have a clear understanding of RMAN backup and recovery process with the help of various important RMAN commands.
Although in real case scenarios based on the size of the data, we could have several hundreds of data files and we need to make sure we backup each and every one of them to have a good backup strategy in place. Also, test the recovery on test systems to make sure you can use the same techniques on production.
We have dealt with different methods of backing up your critical/test databases and various methods to test them. As already suggested numerous times, having a good backup and recovery strategy will save your job and your organization.
Hope this helps!
Cheers!
Ramesh.
Post a Comment: