oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Using Oracle Flashback Database 

Flashback Database  (Alternative to Point-In-Time Recovery)

Oracle Flashback Database, lets you quickly recover the entire database from logical data corruptions or user errors.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

Once you set these parameters, From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These Flashback logs are use to flashback database to a point in time.

Enabling Flash Back Database

Step 1. Shutdown the database if it is already running and set the following parameters

           DB_RECOVERY_FILE_DEST=/d01/ica/flasharea
          DB_RECOVERY_FILE_DEST_SIZE=10G
          DB_FLASHBACK_RETENTION_TARGET=4320

(Note: the db_flashback_retention_target is specified in minutes here we have specified  3 days i.e. 3x24x60=4320)

Step 2. Start the instance and mount the Database.

            SQL>startup mount;

Step 3. Now enable the flashback database by giving the following command

            SQL>alter database flashback on;

Now Oracle start writing Flashback logs to recovery area.

To how much size we should set the flash recovery area.

After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

This will show how much size the recovery area should be set to.

 How far you can flashback database.

To determine the earliest SCN and earliest Time you can Flashback your database,  give the following query:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Example: Flashing Back Database to a point in time

Suppose, a user erroneously drops a schema at 10:00AM. You as a DBA came to know of this at 5PM. Now since you have configured the flashback area and set up the flashback retention time to 3 Days, you can flashback the database to 9:50AM by following the given procedure

1. Start RMAN

$ rman target /

2. Run the FLASHBACK DATABASE command to return the database to  9:59AM by typing the following command

RMAN>FLASHBACK DATABASE TO TIME
        timestamp('2007-06-21 09:59:00');

or, you can also type this command.

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-8/24);

3.  When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.

 RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

               At this time, you have several options

Option 1:-

If you are content with your result you can open the database by performing ALTER 
DATABASE OPEN RESETLOGS

       SQL>ALTER DATABASE OPEN RESETLOGS;

Option 2:-

If you discover that you have chosen the wrong target time for your Flashback Database operation, you can use RECOVER DATABASE UNTIL to bring the database forward, or perform FLASHBACK DATABASE again with an SCN further in the past. You can completely undo the effects of your flashback operation by performing complete recovery of the database:

       RMAN> RECOVER DATABASE;

Option 3:-

If you only want to retrieve some lost data from the past time, you can open the database read-only, then perform a logical export of the data using an Oracle export utility, then run RECOVER DATABASE to return the database to the present time and re-import the data using the Oracle import utility

4. Since in our example only a schema is dropped and the rest of database is good, third option is relevant for us.

Now, come out of  RMAN and run export utility to export the whole schema

$ exp  userid=system/manager file=scott.dmp  owner=SCOTT

5.  Now Start RMAN and recover database to the present time

$rman target /

RMAN> RECOVER DATABASE;

6.  After database is recovered shutdown and restart the database in normal mode and import the schema by running IMPORT utility

$imp userid=system/manager file=scott.dmp

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved