The Oracle 10g Database New Features

The Flashback feature in the Oracle 10g database

The flashback gives users the capability to query past version of schema objects, query historical data, and perform change analysis.

How it works?

Every transaction logically generates a new version of the database. You can navigate through these versions to find an error and its cause.

Why flashback?

It eliminates restore process and it is faster than traditional point-in-time recovery.

What is its architecture?

Now, one more log was added as Flashback Database log. The Oracle database server regularly logs before images of data blocks in the Flashback Database logs from Flashback buffer in the SGA Oracle memory. The Flashback Database must be enabled. When it is enabled, the new RVWR (Recovery Version Writer) background process will be started. The RVWR background process sequentially writes Flashback Database data from the flashback buffer to the Flashback Database logs which are circularly reused.

To Configure Flashback Database:

Assuming:

The database is in archive mode.

The database flash recovery area was configured.

Set the database flashback retention time target.

SQL> ALTER SYSTEM SET db_flashback_retention_target = 2880;   -- Ex: for two days.

Enable Flashback Database. Before altering your database, the database must be in MOUNT EXCLUSIVE mode, ARCHIVELOG mode, and the Flashback be enabled. To check whether it is enable do the following SQL statement.

SQL> SELECT flashback_on FROM v$database;

SQL> ALTER DATABASE FLASHBACK ON;

If you disable the flashback (OFF), all existing Flashback Database logs are deleted automatically.

How to Flashback a database?

The FLASHBACK DATABASE command force the database back to a past time or SCN. See the following examples:

SQL> FLASHBACK DATABASE TO TIMESTAMP (sysdate-5/24);  -- Go back 5 hours from now.

SQL> FLASHBACK DATABASE TO SCN 65473;

How to monitor Flashback Database?

Use the V$FLASHBACK_DATABASE_LOG view to display the approximate lowest SCN and time to which you can flash back your database.

SQL> SELECT oldest_flashback_scn, oldest_flashback_time

            FROM v$flashback_database_log;

Use the V$FLASHBACK_DATABASE_LOG view to determine how much disk space is needed to meet the current flashback retention target.

SQL> SELECT estimated_flashback_size, flashback_size

            FROM v$flashback_database_log;

Use the V$FLASHBACK_DATABASE_STAT view to monitor the overhead of logging flashback data.  You can use this to adjust the retention time or the flash recovery area size.

SQL> SELECT * FROM v$flashback_database_stat;

How to exclude a tablespace from flashback database?

If you don�t want the USER tablespace to be included to log Flashback Database data, do the following SQL statement.

SQL> ALTER TABLESPACE users FLASHBACK OFF;

When are you not able to Flashback Database?

The control file has been restored or recreated,

A tablespace has been dropped,

A data file has been shrunk, and

A RESETLOSG operation is required.

You can query the content of the recycle bin by using the DBA_RECYCLEBIN view.

SQL> SELECT * FROM dba_recyclebin WHERE can_undrop = �YES�;

SQL> SHOW RECYCLEBIN

How to restore from recycle bin?

Use the FLASHBACK TABLE command to recover a table and all its possible dependent objects form the recycle bin.

SQL> DROP TABLE oracle.emp;

SQL> SELECT original_name, object_name, type, ts_name,

            dropttime, related, space

            FROM dba_recyclebin

            WHERE original_name = �EMP�;

SQL> FLASHBACK TABLE emp TO BEFORE DROP;

SQL> FLASHBACK TABLE emp

            TO BEFORE DROP RENAME TO employee;

SQL> FLASHBACK TABLE emp

            TO TIMESTAMP to_timestamp (�14:45�,�HH24:MI�);

To reclaim the recycle bin:

SQL> PURGE TABLE emp;  -- Purges the specified table.

SQL> PURGE TABLESPACE scott_ts USER scott;  -- All the Scott�s objects.

SQL> PURGE RECYCLEBIN;     -- Purges all user objects.

SQL> PURGE DBA_RECYCLEBIN;   -- Purges all the objects.

You can perform queries on the database as of a certain clock time or SCN.

SQL> SELECT versions_xid, sal, versions_operation

            FROM emp

            VERSIONS BETWEEN TIMESTAMP sysdate-10/24 AND sysdate

            WHERE empno = 100;

You can use the CURRENT_SCN column in the V$DATABASE view to obtain the current SCN.

SQL> SELECT current_scn FROM v$database;

To guaranteed UNDO retention, you can do one of the following SQL statements.

SQL> CREATE UNDO TABLESPACE my_undotbs1

            DATAFILE �my_undotbs01.dbf� SIZE 10G AUTOEXTEND ON

            RETENTION GUARANTEE;

SQL> ALTER TABLESPACE my_undotbs1

            RETENTION GUARANTEE;

To check it:

SQL> SELECT tablespace_name, retention FROM dba_tablespaces;

Hands-On #1:

A user drops a table and you should get it back by using the Flashback command.

Connect as sysdba and enable the database flashback and set its retention time.

SQL> CONNECT / AS SYSDBA

SQL> ALTER SYSTEM SET db_flashback_retention_target = 2880;   -- Ex: for two days.

SQL> ALTER DATABASE FLASHBACK ON;

Connect as oracle and create a table. This table should have trigger, constraint, index, primary key, and materialized view log.

SQL> CONNECT self/schooling

Create a table.

SQL> CREATE TABLE flashback_test (

            c1 NUMBER, c2 NUMBER, c3 VARCHAR2(50));

Create a trigger doing nothing.

SQL> CREATE OR REPLACE TRIGGER doing_nothing

            BEFORE DELETE OR UPDATE OR INSERT ON flashback_table

            BEGIN

                        NULL;

            END;

            /

Create a primary key constraint.

SQL> ALTER TABLE flashback_table

            ADD CONSTRAINT pk4flashback_table

            PRIMARY KEY (c1)

            /

Create an index on column c2.

SQL> CREATE INDEX index4flashback_table

            ON flashback_table (c2)

            /

Make sure that the column c2 value doesn�t exceed 3999

SQL> ALTER TABLE flashback_table

            ADD CONSTRAINT ck01_flashback_table

            CHECK (c2 < 4000)

            /

Create a materialized view log on flashback_table.

SQL> CREATE MATERIALIZED VIEW LOG on flashback_table;

Add some value in it.

SQL> BEGIN

            FOR this IN 1..1000 LOOP

                        INSERT INTO flashback_table

                        (this, this + 100, �Test flashback��);

            END LOOP;

            COMMIT;

          END;

          /

Display all the objects and constraints that belong to the oracle user.

SQL> SELECT object_name, object_type

            FROM user_objects

            /

SQL> SELECT constraint_name, constraint_type, table_name

            FROM user_constraints

            /

Now, drop flashback_table.

SQL> DROP TABLE flashback CASCADE;

Again, display all the objects and constraints that belong to the oracle user. You can that deleted objects were renamed and still belong to the ORACLE user. Also, you should notice that you lost all your constraints and have been renamed as well.

SQL> SELECT object_name, object_type

            FROM user_objects

            /

SQL> SELECT constraint_name, constraint_type, table_name

            FROM user_constraints

            /

Now, connect as sysdba and flashback the table.

SQL> CONNECT / AS SYSDBA

Use the FLASHBACK TABLE command to recover a table and all its possible dependent objects form the recycle bin.

Check what do you have in your recycle bin.

SQL> SELECT original_name, object_name, type, ts_name,

            dropttime, related, space

            FROM dba_recyclebin

            WHERE original_name = �FLASHBACK_TABLE�

            /

SQL> FLASHBACK TABLE oracle.emp TO BEFORE DROP;

Test flashback_table is back.

SQL> SELECT count(*) FROM flashback_table;

Notice that the materialized view log was not flash back. So remember the materialized view log can not be recovered.