Backup Methods

We will be discussing

1. Recovery Manager

2. Data Pump Export

3. User Managed

Feature Recovery Manager User-Managed Data Pump Export
Closed database backups Supported. Requires instance to be mounted. Supported. Not supported.
Open database backups Supported. No need to use BEGIN/END BACKUPstatements. Supported. Must use BEGIN/END BACKUPstatements. Requires rollback or undo segments to generate consistent backups.
Incremental backups Supported. Not supported. Not supported.
Corrupt block detection Supported. Identifies corrupt blocks and logs inV$DATABASE_BLOCK_CORRUPTION. Not supported. Supported. Identifies corrupt blocks in the export log.
Automatic specification of files to include in a backup Supported. Establishes the name and locations of all files to be backed up (whole database, tablespaces, datafiles, control files, and so on). Not supported. Files to be backed up must be located and copied manually. Not applicable.
Backup repository Supported. Backups are recorded in the control file, which is the main repository of RMAN metadata. Also you can store this metadata in a recovery catalog in a schema in a same or different database. Not supported. DBA must keep own records of backups. Not supported.
Backups to a media manager Supported. Interfaces with a media manager. RMAN also supports proxy copy, a feature that allows a media manager to manage completely the transfer of data between disk and backup media. Supported. Backup to tape is manual or controlled by a media manager. Not supported.
Backup of initialization parameter file Supported. Supported. Not supported.
Backup of password and networking files Not supported. Supported. Not supported.
Platform-independent language for backups Supported. Not supported. Supported.


Oracle Flashback Technology

This set of features provides an additional layer of data protection. Specifically, you can use flashback features to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. Flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.

Logical Flashback Features

The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy( exception of Flashback Drop). Logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update.

Oracle Database includes the following logical flashback features:

1. Oracle Flashback Query

You can specify a target time and run queries against a database, viewing results as they would have appeared at the target time. To recover from an unwanted change like an update to a table, you could choose a target time before the error and run a query to retrieve the contents of the lost rows.

2. Oracle Flashback Version Query

You can view all versions of all rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows including start and end time operation and transaction ID of the transaction that created the version. You can use this feature to recover lost data values and to audit changes to the tables queried.

3. Oracle Flashback Transaction Query

You can view changes made by a single transaction, or by all the transactions during a specific time period.

4. Oracle Flashback Transaction

You can reverse a transaction. Oracle Database determines the dependencies between transactions and in effect creates a compensating transaction that reverses the unwanted changes. The database rewinds to a state as if the transaction and any transactions that could be dependent on it had never happened.

5. Oracle Flashback Table

You can recover a table or set of tables to a specified point in time in the past without taking any part of the database offline. Flashback Table restores tables while automatically maintaining associated attributes as current indexes, triggers and constraints and in this way enabling you to avoid finding and restoring database-specific properties.

6. Oracle Flashback Drop

You can reverse the effects of a DROP TABLE statement. You can turn flashback archiving on and off for individual tables. By default, flashback archiving is turned off for every table.

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

Physical Flashback:

Flashback Database

1. Oracle Flashback Database provides a more efficient data protection alternative to database point-in-time recovery (DBPITR). If the current datafiles have unwanted changes, then you can use the RMAN command FLASHBACK DATABASE to revert the datafiles to their contents at a past time. The end product is much like the result of a DBPITR, but it is much faster because it does not require restoring datafiles from backup and requires less redo than media recovery. Flashback logging is not enabled by default.

2. Flashback Database uses flashback logs to access past versions of data blocks and some information from archived redo logs. Flashback Database requires that you configure a fast recovery area for a database because the flashback logs can only be stored there. Space used for flashback logs is managed automatically by the database and balanced against space required for other files in the fast recovery area.

3. Oracle Database also supports restore points along with Flashback Database and backup and recovery. A restore point is an alias corresponding to a system change number (SCN). You can create a restore point at any time if you anticipate needing to return part or all of a database to its contents at that time. A guaranteed restore point ensures that you can use Flashback Database to return a database to the time of the restore point.

Data Recovery Advisor Tool:

Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.

Failures are detected by data integrity checks, which are diagnostic procedures executed to assess the health of the database or its components. If a data integrity check reveals a failure, then Data Recovery Advisor automatically assesses the effect of a set of failures and maps it to a set of repair options. In most cases, Data Recovery Advisor presents both automated and manual repair options.

Data Recovery Advisor determines the best automated repair option and its effect on the database. The repair option may include repairs such as datafile restore and recovery, media recovery, Flashback Database, and so on. Before presenting an automated repair option, Data Recovery Advisor validates it for the specific environment and the availability of media components required to complete the proposed repair.

If you choose an automated repair option, then RMAN coordinates sessions on the Oracle database to perform the repair for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.

Database Verify
1. DBVERIFY can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or data file) is valid before it is restored

2. To DBVERIFY a single data or index segment you must be logged onto the database with SYSDBA privileges. During the verification the segment is locked; if the segment is an index then the parent table is also locked.

3. You can use the dbv to Validate Disk Blocks of a Single Data File Validate a Segment.

4. DBVERIFY is only for use with data files, it does not work against control files or redo logs. Corrupt block/s will be reported in alert log file and in most of the case a trace file will be generated which will have entry like…

table scan: segment: file# 45 block# 899385
skipping corrupt block file# 55 block# 251372
You can use below script to find the details about segment.

SQL> select segment_type from dba_extents where file_id=45 and 899385 between block_id and (block_id +( blocks -1));

Sample output:


Sample output:

———- ———- ———- —————— ———
37 554774 1 0 FRACTURED
37 464775 1 0 FRACTURED

Leave a Reply

You must be logged in to post a comment.