‘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
Introduction
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.
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.
Configuring Flashback
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.
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;
Monitor Flashback
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;
Exclude a tablespace from
Flashback
If you do not want the USER
tablespace to be included to log Flashback Database data, do the
following SQL statement.
SQL> ALTER TABLESPACE
users FLASHBACK OFF;
Flashback restrictions
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.
The content of RECYCLEBIN
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
Restore From 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’);
Reclaim space from
RECYCLEBIN
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.
Query based on Clock time
or SCN
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;
Obtaining the current SCN
value
You can use the CURRENT_SCN
column in the V$DATABASE view to obtain the current SCN.
SQL> SELECT current_scn
FROM v$database;
Guarantee UNDO retention
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-Recovering
using Flashback
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 does not 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. In
addition, 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 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 cannot be recovered.
“There is nothing
more dangerous than to build a society, with a large segment of
people in that society, who feel that they have no stake in it;
who feel that they have nothing to lose. People who have a stake
in their society, protect that society, but when they don't have
it, they unconsciously want to destroy it.” Martin Luther King
Jr. |
Questions:
Questions on
The
Flashback feature in the Oracle 10g
database
Q: What is the flashback?
Q: How it works?
Q: Why do need to use the
flashback?
Q: What is its
architecture?
Q: How do you configure
Flashback Database?
Q: How do you Flashback a
database?
Q: How do you monitor
Flashback Database?
Q: How do you use the
V$FLASHBACK_DATABASE_LOG view to determine how much disk space is
needed to meet the current flashback retention target?
Q: How do you use the
V$FLASHBACK_DATABASE_STAT view to monitor the overhead of logging
flashback data?
Q: How do you exclude a
tablespace from flashback database?
Q: When are you not able to
Flashback Database?
Q: How can you query the
content of the recycle bin by using the DBA_RECYCLEBIN view?
Q: How do you restore from
recycle bin?
Q: How do you reclaim the
recycle bin?
Q: How can you perform
queries on the database as of a certain clock time or SCN?
Q: How can you use the
CURRENT_SCN column in the V$DATABASE view to obtain the current SCN?
Q: How can you enforce to
guaranteed UNDO retention?
Q: How can you check the
UNDO retention?
Q: How can you recover
deleted file?
Q: How do you test that
your recovery was successful?
|