Flat 10% & upto 50% off + Free additional Courses. Hurry up!

Database Backup, Restore, and Recovery


2.1 Backing up Databases

This is definitely an important task for the system DBA, and backups are key to providing a secure and reliable database environment.


  • Backup Strategies

The Oracle DBCA has a step to set up maintenance plans, which include backup settings. You also have the option to create the database in NOARCHIVELOG mode, which is similar to the SQL Server SIMPLE option, or ARCHIVELOG mode, which is like the FULL option.

Running backup database and transaction logs, and saving copies to disk and tape with certain retention policies are all part of this plan. Besides the backups, there are exports, a recycle bin, and a way to flashback queries and even 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



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.


  • 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. Next, create the catalog using the RMAN command line:


RMAN> connect 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

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete


oem backup settings, device tab



oem backup settings, policy tab


  • Backup Options

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


backup options in sql server and oracle

  • OEM Backup Jobs

With OEM, you can configure backups and schedule them 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.


customizing a backup job in oem


2.2 Restoring and Recovering Databases

 Understanding the different ways a database can fail and reasons for a restore can help in planning a backup strategy.

■ 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 the 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.


  1. Recovering to a Specific Point

In SQL Server, you have the options 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.


  1. 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 REPAIR FAILURE. Using REPAIR FAILURE PREVIEW will show you the script first.


2.3 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.


managing backups in oem


  • Purging Obsolete Files

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

delete and expire backup options in sql server and oracle


2.4 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.


scheduling a data pump job in oem


2.5 Protecting Users from Users


  • 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.


  1. 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.


  1. 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 recycle bins.

SQLPLUS> conn / as sysdba

SQLPLUS> purge dba_recyclebin;


  • Flashback

The flashback command lets you pull a table out of the recycle bin. But flashback also has a greater purpose in the recovery strategy. You can flashback a query, table, and even the database. But before you can use flashback in this way, you must configure a recovery area.


  1. Configuring the Recovery Area

     2. Flashing Back Items

"0 Responses on Database Backup, Restore, and Recovery"

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.


Sales Offer

  • To avail this offer, enroll before 27th October 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
DW offer

Sign Up or Login to view the Free Database Backup, Restore, and Recovery.