Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 27 March 2012

Flash Back Configuration in Oracle 10g

Oracle 10g offer flashback facility. In some situation flashback should be your first choice to recover. If you consider "normal" recovery as anything having to do with the RMAN then you are actually limiting yourself in terms of what oracle has offer you. 
The idea of flashback is pretty simple but how it is implemented or used within the database is important specially where flashback lives and what frame are involved. Flashback lives in couple of places in oracle one is in UNDO tablespace and the other is in flashback log files. Both of these area are limited by space, which is related to time. Flashback depending on what is being flashed back, can also require archive redo logs.
For the flashback that lives in UNDO your flashback time is related to undo retention time. If the undo segment is overwritten then flashback table table_name to timestamp (or SCN) whenever no option then go for other means of recovery such as RMAN. Here in terms of flashback major plus point is the speed of recovery and the major minus is the time limitation of flashback.
Overall your flashback recovery tools are your flashback transaction backout, flashback table, flashback drop and flashback database. Your flashback tools for error analysis are flashback query, flashback versions query and flashback transactions query. You can perform flashback operation through the use of SQL*PLUS, RMAN and Enterprise Manager.
Check Other post related to this topics:
Oracle Flashback Version and Transaction Query
Requirements:
·         Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
·         You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
·         For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
Step1: Ensure db_recovery_file_dest is set.
sqlplus '/ as sysdba'
SQL> Alter system set db_recovery_file_dest= C:\oracle1\oradata\SHAAN\FLASHAREA' SCOPE=spfile;
Step 2: Ensure db_recovery_file_dest_size is set
SQL> Alter system set db_recovery_file_dest_size=100G SCOPE=spfile;
Step 3: Start SQL*Plus and ensure that the database is mounted, but
           not open.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Step 4: Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the
            length of the desired flashback window in minutes:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).
Step 5: Enable the Flashback Database feature for the whole database:
SQL> ALTER DATABASE FLASHBACK ON;
By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging for specific tablespaces:
SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;
You can re-enable flashback logging for a tablespace later with this command:
SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;
Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.
Determining the Current Window for Flashback Database
SQL>SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 
      FROM V$FLASHBACK_DATABASE_LOG;
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
Determine if Flashback Database is Already Enabled
SQL> select flashback_on from v$database;

Creating and Using Flashback Restore points.

SQL> create restore point <restore point name> [GUARANTEE FLASHBACK DATABASE];
Rolling Back to a Restore Point
select name, time, guarantee_flashback_databse from v$restore_point;

Dropping a Restore Point

Drop restore poijnt <restore point name>;

Monitoring Flashback Logging

Select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

Finding the Earliest Flashback Point

Alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

Disabling Flashback Database

ALTER DATABASE FLASHBACK OFF;

Use Rman to restore the missing archive logs.

The sequence and thread numbers are in the error message.
rman target /
      run{
         allocate channel c1 device type 'sbt_tape';=
FLASHBACK DATABASE TO RESTORE POINT <restore point name>;
The flashback feature allows you to shift within the time not only the backward as well as forward too. You can move forward only with applying the redologs where as backward in time by applying the flashback logs along with smaller amount of redo data.

To use flashback make sure you must have the flashback and archive redologs for the whole time period. With the flashback you can return the database to its state as in every point in time in an extremely fast way. The speed of flashback operation depends on the number of applied flashback logs (the number of data blocks changes within the database for this time period).
Perform Flashback via Enterprise Manager
You can perform a Flashback Database operation via Enterprise Manager as well.
Go to Tab: Maintenance -> Section: Backup/Recovery -> Link: Perform Recovery
Different use of flashback:
In SQL prompt you can perform by using FLASHBACK DATABASE command and in RMAN by FLASHBACK command.
Flashes back the database to a point in time just as in the specified SCN:
SQL> FLASHBACK DATABASE TO SCN 5225962112; /* returns database to its state as in the point in time marked with SCN 5225962112 */
Flashes back the database to a point in time just as specified custom TIMESTAMP
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('20.09.2012 15:10','dd.mm.yyyy hh24:mi');/* returns database to its state as in the point in time '20.09.2012 15:10 */
Flashes back the database to a point in time just as specified sys TIMESTAMP
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP -1/24); /* returns database to its state as in the point in time one hour ago */
Flashesback to a point in time just as specified sys TIMESTAMP INTERVAL
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE); /* returns database to its state as in the point in time one minute ago */
Flashes back the database to a point in time just as in the specified restore point
SQL> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; /* returns database to its state as in the point in time marked with the restore point with name before_flashback_operation */
Flashbacks database to the point in time just before the last RESETLOG
SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;
Flashesback the database to a SCN just before the specified SCN
SQL> FLASHBACK DATABASE TO BEFORE SCN 6125762178;
Flashesback the database to a point in time one second before the specified TIMESTAMP
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP to_timestamp(''20.09.2012 15:10','dd.mm.yyyy hh24:mi');
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSDATE -1/24); 
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);
Flashes back the database to a point in time just before the specified restore point
SQL> FLASHBACK DATABASE TO BEFORE RESTORE POINT
Flashback of the database via RMAN
RMAN> FLASHBACK DATABASE TO SCN 6125762178; 
RMAN> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); 
RMAN> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('20.09.2012 15:10','dd.mm.yyyy hh24:mi');
RMAN> FLASHBACK DATABASE TO RESTORE POINT; 
RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;

0 comments:

Post a Comment