Data Recovery Advisor(DRA)

Oracle 11g come up with one new cool feature for Database Backup & recovery called DRA (Data Recovery Advisor) which help us to recover the database without any trouble with few RMAN commands.

What is Data Recovery Advisor?

DRA is an oracle database tool that automatically diagnoses data failures, determines and presents appropriate repair options and executes repairs at user requests.

The following RMAN commands are use to perform Data Recovery Advisor.

1. List Failure

2. Advise Failure

3. Repair Failure

4. Change Failure

1. List Failure:     List failure command gives us information regarding failures and the effect of these on database operations. Each failures uniquely identified by failure number.

2. Advise Failure:   Advise failure give us advise for how to deal with failure against database means advise failure give us solution for particular failure.

3. Repair failure:  Repair failure command gives us "rman generated scrits" which restore and recover database from backup.

4. Change failure:  Change failure is RMAN command which change the failure status or priority.

Like there is two status : OPEN or CLOSED and Priority is HIGH or LOW.

If some failure is rman showing HIGH and we want to change it to LOW then using change failure command we can change it.

1. Suppose I lost my system data file.

2. Now I am not worry because I have database backup with 11g database.

3. I need to just connect with RMAN, after connect with RMAN I do the following task.

RMAN>      List failure;

Using target database control file instead of recovery catalog

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

After got the failure description we can get "advise" from oracle about failure through advice failure command.

RMAN>     Advise failure;

List of Database Failures

=========================
Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

602 CRITICAL OPEN 26-JUL-08 System datafile 1: 'C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF' is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=152 device type=DISK

analyzing automatic repair options complete
Mandatory Manual Actions

========================

no manual actions available
Optional Manual Actions

=======================

1. If file C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Restore and recover datafile 1

Strategy: The repair includes complete media recovery with no Data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

Above is rman advise regarding particular failure if above suggested repair option is helpful and fix the current problem then ok otherwise need to call oracle support services.

now check oracle suggested repair options or scripts.

RMAN>   repair failure preview;

Strategy: The repair includes complete media recovery with no data loss Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Above is suggested script from RMAN to restore and recover database for particular failure, if suppose we want to use above script then again run "repair failure" command without 'preview' keyword.

RMAN>   repair failure ;
Strategy: The repair includes complete media recovery with no data loss

Repair script: c:\app\m.taj\diag\rdbms\test\test\hm\reco_2508517227.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script
Starting restore at 26-JUL-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBF

channel ORA_DISK_1: reading from backup piece C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1

channel ORA_DISK_1: piece handle=C:\APP\M.TAJ\PRODUCT\11.1.0\DB_1\DATABASE\05JMEU48_1_1 tag=TAG20080726T124808

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:05:25

Finished restore at 26-JUL-08

Starting recover at 26-JUL-08

using channel ORA_DISK_1
starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 26-JUL-08

If we lost "tempfiles"  in 10gr1 we need to manually RE-CREATE temporary tablespace but in 11g it is automatically done by ORACLE .

Flashback Architechture

Flashback Technology, first introduced in Oracle 9i,is a set of Oracle Database features that allow you to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. This new technology reduces recovery time from hours to minutes.

The flashback features are:
1.   Flashback Query                       (Introduced in 9i)
2.   Flashback Version Query          (Introduced in 9i)
3.   Flashback Transaction Query   (Introduced in 9i)
4.   Flashback Table                       (Introduced in 9i)
5.   Flashback Drop                        (Introduced in 9i)
6.   Flashback Database                (Introduced in 10g)
7.   Flashback Data Archive           (Introduced in 11g)

There are a number of flashback levels

row level	flashback query, flashback versions query, flashback transaction query
table level	flashback table, flashback drop
database level	flashback database

Oracle 10g has several error-correction techniques that use undo data, however they are only available if you use automatic undo management (AUM),

• Flashback query - retrieves data from a past point in time
• Flashback versions query - shows you different versions of data rows, plus start and end times of a particular transaction that created that row
• Flashback transaction query - lets you retrieve historical data for a given transaction and the SQL code to undo the transaction.
• Flashback table - recovers a table to its state at a past point in time, without having to perform a point in time recovery.

There are two other flashback technologies that do not use the undo data, they use flashback logs and recyclebin instead.

• flashback database - restore the whole database back to a point in time.
• flashback drop - allows you to reverse the effects of a drop table statement, without resorting to a point-in-time recovery

DBMS_FLASHBACK, flashback table query, flashback transaction query, flashback version query and select .. as of .. statements all use the undo segments. Flashback database uses the flashback logs and flashback drop uses the recycled bin.

When using flashback, if any operations violate a constraint the flashback operation will be rolled back, you can disable constraints but it’s probably not a good idea. If you have a table using a foreign key it is a good idea to flashback both tables. Flashback technology requires you to lock the whole table if it cannot it will fail immediately.

RMAN can only do flashback database and no other flashback technology.

Flashback Query:

Using flashback query involves using a select statement with an AS OF clause. you can select data from a past point in time. If you get a ORA-08180 it means that the data is no longer available in the undo segments.

Flashback Version Query:

Flashback version query provides you with all the versions of a row between two points in time or SCN, this is useful if you want to audit a table finding out what happened to a row. However there are some points to remember:

• You can only retrieve committed rows
• They query will retrieve all deleted rows as well as current rows
• The query will retrieve any rows that were deleted and reinserted later on
• Query result is table format and contains a row for each version of a row during the time or SCN interval you specify

The limitations of flashback version query are:

• You can only query actual tables not views
• you cannot apply the versions clause across DDL operations
• The query will ignore physical row changes for example during a segment shrink operation
• You cannot use against external or temporary tables.

The most useful columns to obtain are below:

• VERSIONS_STARTTIME - start timestamp of version
• VERSIONS_STARTSCN - start SCN of version
• VERSIONS_ENDTIME - end timestamp of version
• VERSIONS_ENDSCN - end SCN of version
• VERSIONS_XID - transaction ID of version
• VERSIONS_OPERATION - DML operation of version

Flashback Transaction Query:

Identifies which transaction or transactions were responsible for a certain change in a table's data during a specified time period. Basically it queries the flashback_transaction_query view. It provides the SQL code that will undo the change, flashback transaction query can use an index path to retrieve data instead of reading the entire redo log file.

Flashback transaction considerations:

• Turn on minimal supplemental logging if your operations involve chained rows and special storage structures, such as clustered tables
• When querying IOT, an update is shown as a delete/insert operation.
• If the query involves a dropped table or a dropped user, it returns object numbers and user ID's instead of the object names and usernames.

Consider setting the retention guarantee option for the undo tablespace, this will ensure that the unexpired data in the undo segments is preserved.

Flashback transaction query will contain the following columns

• start_scn and start_timestamp - identify when a certain was created
• commit_scn and commit_timestamp - tell you when a certain row was committed
• xid_row_id and undo_change# - identify the row, transaction and change numbers
• operation - tells you what sort of operation occurred insert, delete or update.
• logon_user, table_name and table_owner - username, table name and schema name
• undo_sql - the exact SQL code to undo the change

If you have chained rows or use clustered tables then oracle recommends that you should turn on supplemental logging on at the database level.

Flashback Table:

There are two distinct table related flashback table features in oracle, flashback table which relies on undo segments and flashback drop which lies on the recyclebin not the undo segments.

Flashback table lets you recover a table to a previous point in time, you don't have to take the tablespace offline during a recovery, however oracle acquires exclusive DML locks on the table or tables that you are recovering, but the table continues to be online.

When using flashback table oracle does not preserve the ROWIDS when it restores the rows in the changed data blocks of the tables, since it uses DML operations to perform its work, you must have enabled row movement in the tables that you are going to flashback, only flashback table requires you to enable row movement.

If the data is not in the undo segments then you cannot recover the table by using flashback table, however you can use other means to recover the table.

Restriction on flashback table recovery

• You cannot use flashback table on SYS objects
• You cannot flashback a table that has had preceding DDL operations on the table like table structure changes, dropping columns, etc
• The flashback must entirely exceed or it will fail, if flashing back multiple tables all tables must be flashed back or none.
• Any constraint violations will abort the flashback operation
• You cannot flashback a table that has had any shrink or storage changes to the table (pctfree, initrans and maxtrans)

Flashback Drop:

Flashback drop lets you reinstate previously dropped tables exactly as it was before the drop, below is a table of what is kept where when a table is dropped:

• Recyclebin: tables and indexes
• Data dictionary: unique keys, primary key, not-null constraints, triggers and grants
• Not recovered: foreign key constraints

If two tables exist in the recyclebin with the same name the newest one will be restored unless you state which one you want to restore. If you restore a table it is removed from the recyclebin.

Limitations on flashback drop:

• Recyclebin is only available to non-system, locally managed tablespaces.
• There is no guaranteed timeframe for how long an object will be stored in the recyclebin
• DML and DDL cannot be used on objects in the recyclebin
• Must use the recyclebin name to query the table
• All dependent objects are retrieved when you perform a flashback drop.
• Virtual private database (VPD) and FGA policies defined on tables are not protected for security reasons
• Partitioned index-organised tables are not protected by the recycle bin.
• Referential constraints are not protected by the recycle bin. They must be re-created after table has been rebuilt.

Flashback Database:

The database can be taken back in time by reversing all work done sequentially. The database must be opened with resetlogs as if an incomplete recovery has happened. This is ideal if you have a database corruption (wrong transaction, etc) and require the database to be rewound before the corruption occurred. If you have media or a physical problem a normal recovery is required.

Flashback database is not enabled by default, when enabled flashback database a process (RVWR – recovery Writer) copies modified blocks to the flashback buffer. This buffer is then flushed to disk (flashback logs). Remember the flashback logging is not a log of changes but a log of the complete block images. Not every changed block is logged as this would be too much for the database to cope with, so only as many blocks are copied such that performance is not impacted. Flashback database will construct a version of the data files that is just before the time you want. The data files probably will be in a inconsistent state as different blocks will be at different SCN’s, to complete the flashback process, Oracle then uses the redo logs to recover all the blocks to the exact time requested thus synchronizing all the data files to the same SCN. Archiving mode must be enabled to use flashback database. An important note to remember is that Flashback can never reserve a change only to redo them.

The advantage in using flashback database is speed and convenience with which you can take the database back in time.

You can use rman, sql and Enterprise manager to flashback a database. If the flash recovery area does not have enough room the database will continue to function but flashback operations may fail. It is not possible to flashback one tablespace, you must flashback the whole database. If performance is being affected by flashback data collection turn some tablespace flashbacking off.

You cannot undo a resized data file to a smaller size. When using ‘backup recovery area’ and ‘backup recovery files’ controlfiles , redo logs, permanent files and flashback logs will not be backed up.

Note: if one or more tablespaces are not generating flashback data, then before carrying out a flashback operation the files making up the tablespace must be taken offline. Offline files are ignored by recover and flashback. Remember that you must make these files to the same point as the flashback otherwise the database will not open.

Flashback Recovery Area:
The alert log and DBA_OUTSTANDING_ALERTS will hold status information regarding the flash recovery area. You can use the commands backup copy or backup for flash recovery area. Controlfiles and redo logs are permanently stored in the flash recovery area.

Active Data Guard

Oracle Active Data Guard enhances the performance of production databases by offloading resource intensive operations to one or more standby databases, protecting interactive users and critical business tasks from the impact of long-running operations. Active Data Guard enables a physical standby database to be used for real-time reporting with minimal latency, and compared to traditional replication methods is simple to use, transparently supports all datatypes, and offers very high performance.

                Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production database.

                Active Data Guard also enables the use of fast incremental backups when offloading backups to a standby database, and can provide additional benefits of high availability and disaster protection against planned or unplanned outages at the production site.

Features

                   Physical Standby with Real-Time Query

                  Fast Incremental Backup on Physical Standby

Benefits

·        Increase performance - Offload unpredictable workloads to an up-to-date replica of the production database

·        Simplify operations - Eliminate management complexity that accompanies traditional replication solutions

·        Eliminate compromise - The reporting replica is up to date and online at all times, which is not possible with traditional storage mirroring technology

·        Reduce cost - An Oracle Active Data Guard physical standby database can also provide disaster recovery and/or serve as a test database - no additional storage or servers required

·        Enables standby database for use of real-time reporting

·         Offloads backup operations

·        Insulates critical operations from unexpected system impacts

·         Provides high availability and disaster protection

AWR Report

The AWR collects and stores database statistics relating to problem detection and tuning. AWR is a replacement for the statspack utility which helps gather database performance statistics. AWR generates snapshots of key performance data, such as system and session statistics, segment-usage statistics, time-model statistics, high-load statistics and stores it in the sysaux tablespace.

AWR provides statistics in two formats

• temporary - in memory collection of statistics in the SGA, accessible via the V$ views
• persistent - type of performance data in the form of regular AWR snapshots which you access via the DBA_ views

The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespaces.

AWR will collect data on the following

• Base statistics that are also part of the v$SYSSTAT and V$SESSTAT views
• SQL statistics
• Database object-usage statistics
• Time-model statistics
• Wait statistics
• ASH (active session history) statistics
• Operating system statistics

Tables that AWR uses to collect statistics
v$sys_time_model	time model stats (db time, java execution time, pl/sql execution time, etc)
v$osstat	operating system stats (avg_busy_ticks, avg_idle_ticks, etc)
v$service_stats	wait statistics ( db cpu, app wait time, user commits, etc)
v$sysstat	system stats
v$sesstat	session stats

Database performance stats fall into one of three categories:

• Cumulative values - collect stats over a period of time from the v$sysstat, etc
• Metrics - use the collected stats to make some sort of sense.
• Sampled data - the ASH sampler is used to collect these stats.

AWR Setup

To active the AWR change the system parameter statistics_level to one of three values

• basic - this option disables the AWR
• typical (default) - activates standard level of collection
• all - same as typical but includes execution plans and timing info from the O/S

Active	alter system set statistics_level = typical; alter system set statistics_level = all;
De-active	alter system set statistics_level = basic;
Display	show parameter statistics_level;

To change the snapshot interval and how many days the snapshots are kept you use the package dbms_workload_repository or Enterprise Manager

Snapshot configuration
Change snapshotting values	exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200); interval = minutes retention = seconds
Display values	select * from dba_hist_wr_control;
Snapshot Management
Create a snapshot	exec dbms_workload_repository.create_snapshot;
Delete snapshots	exec dbms_workload_repository.drop_snapshot_range (low_snap_id => 1077, high_snap_id => 1078);
Create a baseline	exec dbms_workload_repository.create_baseline (start_snap_id => 1070, end_snap_id => 1078, baseline_name => 'Normal Baseline');
Delete a baseline	exec dbms_workload_repository.drop_baseline (baseline_name => 'Normal Baseline', cascade => FALSE);
Display snapshots	select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
View the repository tables	select table_name from dba_tables where tablespace_name = ‘SYSAUX’ and substr(table_name, 1,2) = ‘WR’ and rownum <= 20 order by 1;
Useful Views
dba_hist_active_sess_history	ASH info (see below)
dba_hist_baseline	baseline info
dba_hist_database_instance	environment data
dba_hist_sql_plan	sql execution path data
dba_hist_wr_control	AWR settings
dba_hist_snapshot	snapshot info in the AWR

AWR Report

To run AWR report you can use the following operating system scripts or use Enterprise Manager.

awrrpt.sql	the script will ask for begin snapshot and end snapshot and will be generated in text format Note: reports went in $oracle_home\db_1\bin
awrrpti.sql	the script will ask for begin snapshot and end snapshot and will be generated in HTML format Note: reports went in $oracle_home\db_1\bin