Backing up Databases in Oracle

In layman words, a data backup is a copy of the data that you keep so as to restore your original data in case of events such as data loss. Needless to say that if you are running a business or even if it’s just some data on your computer that you wouldn’t want to lose, taking the backup of the data is considered best practice. Backup is key to providing a secure and reliable database environment.
Now data loss can be caused due to various reasons which are why taking backup becomes so important. Some of the reasons for data loss are:

  • Accidentally misplacing or deleting data
  • Viruses corrupting
  • Hard-wares crashing
  • Moving your data from one system to another and so on.

Now that you understand what a backup is and why do we need it, let’s see how Oracle database, is one of the biggest database management systems, helps in taking backups.

Backup solutions in Oracle

There are basically two ways to perform Oracle backup:

  • Oracle Recovery Manager:

Oracle provides a powerful utility to backup and restores databases which are known as Oracle Recovery manager, “RMAN” for short. You can use RMAN to backup databases as a completed backup or incremental backup. RMAN is a feature of Oracle database server so there is no need to separately install it.

  • User managed backup and recovery:

Other than RMAN, oracle also supports User-managed backup and recovery, where you can implement back up and recovery of databases using a mixture of the host operating commands as well as SQL Plus commands, meaning that you will have to use different commands for different OS. In user-managed backup and recovery, the user will be handling how and when the backup has to be implemented.
When comparing both backup and recovery solutions, RMAN is most recommended since it offers one common interface for implementing backup across all different host operating systems which makes it very user-friendly.
RMAN
System Database administrators can set up RMAN database backups as well as schedule the RMAN database backups for multiple databases. In this section, we will learn how to take backups and recover files, databases and more in oracle. Let’s start off by understanding how oracle strategies all backups and recovery.

Kick-start your career in Oracle DBA with the perfect Oracle DBA Training Course now!

Backup Strategies

The Oracle DBCA (Database Configuration Assistant) sets up RMAN backup and maintenance plans, using Oracle Enterprise Manager. OEM or Oracle Enterprise Manager is a set of web-based tools used to manage software and hardware that are produced by Oracle Corporation.
To take RMAN backup of a database, we create and save copies of databases to disk and tape with certain retention policies. A duplicate or copy database created by the recovery manager, that is, RMAN is mostly used to test backup and recovery procedures before implementing them on the real databases and eventually storing the RMAN backups.
As shown in the following image the RMAN connects to the source database that is the database that needs to be backed up. RMAN then copies that database over a network to an auxiliary instance, creating a duplicate database that is connected to RMAN.
RMAN1
Now before we can start backing up the databases we will have to configure our Recovery manager. Let’s see how to do that.

RMAN Configuration for Backups

RMAN is installed with the Oracle software and placed in the ORACLE_HOME/bin/ directory. RMAN can use a catalog to track and manage the backups, or it can use the control file of the database for this purpose.
To configure RMAN, create a user to be catalog owner, and grant that user a quota on the tablespace where the schema is going to live, as well as the RECOVERY_CATALOG_OWNER role.

  • Open RMAN command line by typing the following command in the command prompt
rman
  • Next, create the catalog using the RMAN command line
RMAN> connect the target

connected to target database: DBDEV1 (DBID=280973718)
— DBDEV1 will be the database that will have the catalog schema.

  • If another database is to have the catalog, the connect catalog string will have the database as part of the string: rman/rmanpswdRMANCAT
RMAN> connect catalog rman/rmanpswd
  • To create a recovery catalog, use the following command
RMAN> create the catalog

recovery catalog created

  • To register a database in the recovery catalog
RMAN> register database

database registered in the recovery catalog

  • Starting full resync of recovery catalog
RMAN> RESYNC CATALOG;

full resync complete.
After the configuration of RMAN, we can start backing up our databases in oracle. Let’s first understand how to take RMAN backups using Oracle Enterprise Manager and then we will understand how to do the same using commands.

Have you got more queries? Come to our Oracle DBA Community and get them clarified today!

Backing up using Enterprise Manager

We can back up a database in a few steps using Oracle Enterprise manager without having to write RMAN scripts. The Enterprise Manager will create the RMAN script using OEM cloud control. This saves us a lot of time and is also cost-efficient since the whole backup approach has been automated.
Lets now understand how we can use OEM to backup a database.
Step 1: From your Database home page move to Availability then select Backup & Recovery and move to Backup Settings.
oem
The following page will appear:
backup setting
You can fill the fields as shown in the screenshot above.
The value of parallelism is 1 which is the default value and keeping the backup type as Compressed Backup Set will help you make the best use of backup space. You can also use an image copy for this backup type. It will create an exact copy of all the data files which is useful for fast recovery. You can use the test Disk backup button to ensure the proper disk backup setup.
Similarly, if you want your backups to be created on tape, you can fill the tape settings section
Step2: Next, we can move to the Backup set tab as shown below:
backup settings3
In this tab, you have to specify the size of the maximum backup piece and you can choose the compression algorithm. The release field can be filled with a specific version or as default.
Step3: Next move on to the policy tab as shown in the following screenshot:
backup settings2
Policy tab is used for specifying the automatic backup of Database Control File and Server Parameter File with every database backup.
Note: It is strongly recommended to specify the automatic backup given that Database Control File and Server Parameter File are very important files.
Scrolling down in the policy tab shows the retention policy Archived Redo Log Deletion Policy as shown in the below screenshot.
Backup settings4
Selecting the Retain at least the specified number full backups for each data file as 1 (1 is the default value) will mark the older backup as obsolete whenever a new backup is taken. Only one backup will be retained at a time.
Later on you can delete the obsolete backups using the following command.

DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;

In the end click ok to save the backup settings in the control file of the database.

Backup and Restore Commands

The basic commands for backing up and restoring Oracle databases should look familiar to SQL Server DBAs. Let’s start with the SQL Server command to back up a database:

backup database customer_db to
disk =’E:\mssql\bkups\customer_db_02022010.bak’
backup log customer_db to
disk=’E:\mssql\bkups\customer_db_log_02022010.trn’

In Oracle, you use a Recovery Manager (RMAN) command, like this:

RMAN> run {
allocate channel disk1 device type disk;
backup database plus archivelog;
}

This command will back up the database with the archive logs and write the backup file to disk.

Backup Options

Some common backup types and how to run them in SQL Server and RMAN:-

Backup TypeSQL Server commandOracle (RMAN) commands
FULLbackup databaseBackup database
Files or filegroupsbackup database db1 filegroup=’db1file1’ to disk …Backup as backups datafile ‘/u01/data/users01.dbf’;
TablespacesBackup tablespace system, users;
Logs (transaction and archive)backup log db1 to disk …Backup archive log all;
Incremental backups/ base backupBackup database db1 to disk=’S:\bkups\db1.bak’ with initBackup incremental level 0 database
Incremental backups/differential backupsBackup database db1 to disk=’S:\bkups\db1.bak’ with differentialBackup incremental level 1 cumulative database;
Backup incremental level 1 database;

OEM Backup Jobs

OEM, as we already know by now is short for Oracle Enterprise. With OEM you can configure RMAN backups and schedule them for later as jobs in the database. OEM will generate the RMAN script and display it for your review. This provides a good way to gain a better understanding of the backup options and RMAN commands.
OEM

Restoring and Recovering Databases

Okay so till now we have learned how to take backups in oracle. Now let’s assume that you took a backup of your database in oracle and then due to some reason you lose your original data. Now even though you have a backup, it won’t be any good to you until you can recover and restore it. So let’s understand how we can recover and restore databases in oracle.
Before we dive into restoring and recovering the databases lets first understand under what circumstances we might need to recover a database, what are the different ways a database can fail.
Hardware failures/firmware issues:

  • User error
  • Bad code
  • Loss of a file, control file, redo log, or datafile
  • Corrupt block
  • Upgrade issues
  • Bad change
  • Disaster

Oracle provides various options for recovery, such as rolling back a query or returning to a point before a change.

Restore and Recover Options

To use RMAN to restore or recover a database, you must first connect to the recovery catalog, and then allocate channels to the tape or disk. The catalog has information about the database backup and backup set. A control file can be used for the same information. The restore database command restores the database files, and the recover database command applies any of the changes that are in the archive logs.

  • Recovering to a Specific Point

In SQL Server, you have the option to restore with recovery or with no recovery. With Oracle, you can just restore the database, and then use the recover database command with options to define to which point to recover. Along with recovering everything possible, the Oracle RMAN recover database command can bring the database to a point in time, to a change number, or to a specific archive log.
You can recover to a system change number (SCN). The current SCN can be seen in the v$database view (select current_scn from v$database;). You can also recover to an “until time” or a sequence from archive logs. If a restore point, such as before_upgrade, has been set for the backup, you can recover the database to that point.

  • Restoring Tablespaces, Datafiles, and Blocks

If there are more tablespaces in the database, this would be one way to recover with downtime for only the applications or users in the damaged tablespace. To recover a tablespace to a point in time, to before an error occurred or it was corrupted, an auxiliary database or files would be used. After restoring a tablespace, you should run a backup, because recovering the tablespace after the restore is not possible.
If a block of data is corrupt, DBVERIFY will throw an error and provide some details about the datafile number and block number. The system view v$database_block_corruption will confirm the block number. Using RMAN, you can supply the datafile number and block number to recover the blocks.

  • Data Recovery Advisor

If there was an issue with one of the database files, you can use the LIST FAILURE command and ADVISE FAILURE command to help figure out what to do. the advisor also provides a repair script and the recovery is possible without data loss. The script can be run after running the advisor, with the REPAIR FAILURE. Using REPAIR FAILURE PREVIEW will show you the script first.

Want to get certified in Oracle DBA! Learn Oracle DBA from top Oracle DBA experts and excel in your career with Intellipaat’s Oracle DBA certification!

Managing Backups

Managing backups is not just about purging and maintaining the retention policy, but also about knowing which backups are available for restores. Oracle provides several ways to get information about backup sets.

  • Viewing Backups

RMAN has a LIST command that will return the backup sets that are present in the catalog or control file. The listing shows the different backup pieces and details, including the checkpointed SCN, the date, full or incremental, and tablespaces that were backed up.
Data dictionary views and recovery catalog tables also provide views into the backup sets, to help manage backups and know which backups are available for restoring.

  • Purging Obsolete Files

 In Oracle, the parameters REDUNDANCY and RECOVERY WINDOW set the number of backups and the number of days for retention policies.

OptionSQL ServerOracle
ExpirePart of backup job or script parameters EXPIREDATE or RETA INDAYS (number of days or on a date)RMAN parameters REDUNDANCY and RECOVERY WINDOW(numbers of days or number of backups)
Delete (from msdb/catalog)Sp_delete_backuphistoryDELETE EXPIRED
Delete expired backup files)Maintenance Cleanup TaskDELETE OBSOLETE
BACKUPA ARCHIVELOGS
DELETE ALL INPUT

Backing Up and Restoring Objects

 With Oracle, restoring the database is normally to restore the full system, but there are utilities available to pull out just the objects by schema, or even at the table level. This allows you to secure backups for these objects or copy them to another system, perhaps to refresh a test environment with just the needed schema or tables.

  • Copying Objects at the Table and Schema Level

 A backup table can be defined (such as CREATE table TAB_BACKUP AS select * from TAB_PROD) with tablespaces, no logging (to avoid some of the logging in the redo logs), and with some of the other table options.
The table will not include any of the indexes, constraints, or triggers that might be on the “real” table, but it will have the same datatypes and the same data. A WHERE clause can also be defined in the CREATE table AS statement to capture data.

  • Using Data Pump

Oracle has the Data Pump utility, which handles both exports and imports, as well as older EXP and IMP utilities. You can set up a Data Pump export job to allow you to recover just a table or another object, such as a view or stored procedure. The exports include the Data Definition Language (DDL), which creates the structures of the tables, procedures, trigger, indexes, views, and other objects. Exports can also be done without data, to provide just these structures, which you can then copy to another schema or save as a backup.

Recycle Bin

The Oracle recycle bin works the same way with tables that have been dropped. For example, if you were refreshing a couple of tables, and realized you dropped the wrong tables, you can retrieve those objects from the recycle bin. The recycle bin has been available since Oracle Database 10g and is on by default. Users have their own recycle bins.
The user_recyclebin and dba_recyclebin views show information about the contents of the recycle bin. The dba_recyclebin view has an owner column, which lists who owns the object.
What is Flashback in oracle?
Flashback in oracle is a set of tools that allow the system administrator and the users to view and even manipulate the past state of data without having to recover to a fixed point in time. Using flashback command you can pull a table out of the recycle bin.

  • Restoring Tables from the Recycle Bin

To “undrop” a table in the recycle bin, you flashback the table.

SQLPLUS> flashback table TAB_CUST to before drop;

Flashback complete.

Prepare yourself for the industry by going through this Top Oracle DBA Interview Questions and Answers!

  • Purging the Recycle Bin

The objects in the recycle bin can be cleared out with the purge command. Users can clear their own recycle bins:

SQLPLUS> purge recyclebin;
  • Purging the DBA recycle bin clears out all of the user’s recycle bins.
SQLPLUS> conn / as sysdba
SQLPLUS> purge dba_recyclebin;

Flashback in oracle is a set of tools that allow the system administrator and the users to view and even manipulate the past state of data without having to recover to a fixed point in time. Using flashback command you can pull a table out of the recycle bin.flashback

Recommended Videos

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
21 + 2 =