The
Flashback feature in the Oracle 10g database
"As
soon as we lose the moral basis, we cease to be religious. There is no such thing as
religion over-riding morality. Man, for instance, cannot be untruthful,
cruel or incontinent and claim to have God on his side."
Gandhi
|
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 iself.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 iself
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 iself 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 iself user. You can that deleted
objects were renamed and still belong to the ISELF 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 iself.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.
|