Flashback Query
Flashback
Query allows the contents of a table to be queried with reference to a specific
point in time,
using
the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK
functionality or Oracle 9i, but
in
a more convenient form. Flashback Table, Flashback Query, Flashback Transaction
Query and
Flashback
Version Query all rely on undo dataThe Flashback Drop operation recovers the
table from the
recycle
bin so undo_retention=0 doesn't prevent recover the dropped table from Recycle
bin. Oracle
strongly
recommends that you run your database in Automatic Undo Management mode by
setting
the
UNDO_MANAGEMENT initialization parameter to AUTO. In addition set
the
UNDO_RETENTION initialization parameter to an interval large enough to include
the oldest data
you
anticipate needing.
CREATE TABLE flashback_query_test
(
id NUMBER(10)
);
SELECT current_scn,
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM
v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
722452 2004-03-29 13:34:12
INSERT INTO flashback_query_test
(id) VALUES (1);
COMMIT;
SELECT COUNT(*) FROM
flashback_query_test;
COUNT(*)
----------
1
SELECT COUNT(*) FROM
flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-
03-29 13:34:12', 'YYYY-MM-DD
HH24:MI:SS');
COUNT(*)
----------
0
SELECT COUNT(*) FROM
flashback_query_test AS OF SCN 722452;
COUNT(*)
----------
0
Flashback
Table
The FLASHBACK TABLE command
allows point in time recovery of individual tables subject to the
following requirements.
·
You must have either the FLASHBACK ANY TABLE system
privilege or have FLASHBACK object
·
privilege on the table.
·
You must have SELECT, INSERT, DELETE, and ALTER privileges on the
table.
·
There must be enough information in the undo tablespace to
complete the operation.
·
Row movement must be enabled on the table (ALTER TABLE
tablename ENABLE ROW
·
MOVEMENT;).
The following example
creates a table, inserts some data and flashbacks to a point prior to the data
insertion. Finally it
flashbacks to the time after the data insertion.
CREATE TABLE flashback_table_test
(
id NUMBER(10)
);
ALTER TABLE flashback_table_test
ENABLE ROW MOVEMENT;
SELECT current_scn FROM
v$database;
CURRENT_SCN
-----------
715315
INSERT INTO flashback_table_test
(id) VALUES (1);
COMMIT;
SELECT current_scn FROM
v$database;
CURRENT_SCN
-----------
715340
FLASHBACK TABLE
flashback_table_test TO SCN 715315;
SELECT COUNT(*) FROM
flashback_table_test;
COUNT(*)
----------
0
FLASHBACK TABLE
flashback_table_test TO SCN 715340;
SELECT COUNT(*) FROM
flashback_table_test;
COUNT(*)
----------
1
Flashback of tables can
also be performed using timestamps.
FLASHBACK TABLE
flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03
10:00:00', 'YYYY-MM-DD
HH:MI:SS');
Flashback
Drop (Recycle Bin)
In Oracle 10g the default
action of a DROP TABLE command is to move the table to the recycle bin (or
rename it), rather than
actually dropping it. The DROP TABLE ... PURGE option can be used to
permanently drop a table.
DROP TABLE my_table PURGE;
The recycle bin is a
logical collection of previously dropped objects, with access tied to
the DROP privilege.
This feature does not use flashback logs or undo, so it is independent of the
other
flashback technologies.
The contents of the recycle bin can be shown using the SHOW
RECYCLEBIN command
and purged using the PURGE TABLE command. As a result, a previously
dropped table can be
recovered from the recycle bin.
CREATE TABLE flashback_drop_test
(
id NUMBER(10)
);
INSERT INTO flashback_drop_test
(id) VALUES (1);
COMMIT;
DROP TABLE flashback_drop_test;
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME
OBJECT TYPE DROP TIME
----------------
------------------------------ ------------ ----------------
---
FLASHBACK_DROP_T
BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-
29:11:09:07
EST
FLASHBACK TABLE
flashback_drop_test TO BEFORE DROP;
SELECT * FROM
flashback_drop_test;
ID
----------
1
Tables in the recycle bin
can be queried like any other table.
DROP TABLE flashback_drop_test;
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME
OBJECT TYPE DROP TIME
----------------
------------------------------ ------------ ----------------
---
FLASHBACK_DROP_T
BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE 2004-03-
29:11:18:39
EST
SELECT * FROM
"BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";
ID
----------
1
If an object is dropped
and recreated multiple times all dropped versions will be kept in the recycle
bin,
subject to space. Where
multiple versions are present it's best to reference the tables via
the RECYCLEBIN_NAME. For any
references to the ORIGINAL_NAME it is assumed the most recent object
is drop version in the
referenced question. During the flashback operation the table can be renamed.
FLASHBACK TABLE
flashback_drop_test TO BEFORE DROP RENAME TO
flashback_drop_test_old;
Enabling
Flashback Database in Oracle
flashback features, which
depend on undo data for reconstructing your lost data, Flashback Database
usesflashback logs to
access past versions of changed blocks and allied with some more information
mined
from the archive logs,
you can easily revert your database to a point in time in the past. Whilst the
end product
is very much like a point
in time recovery, Flashback database is much faster and less disruptive,
because you
do not restore from
backups andflashback logs are maintained on the disk itself. Setting it up at
the basic level
is pretty simple. It all
starts being in ARCHIVELOG mode. I give an example below of how I set it up on
my
system.
RVWR Background Process
Enabling Flashback Database starts a new RVWR( Recovery
Writer process) background
process. This process is similar to the LGWR (log writer)
process.
Setting up Archiving
system@ORCL-SQL>>alter
system set
log_archive_dest_1='location=c:\test_flash_arcc','mandatory';
System altered.
system@ORCL-SQL>>shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL-SQL>>startup
mount
ORACLE instance started.
Total System Global Area
171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
system@ORCL-SQL>>alter
database archivelog;
Database altered.
system@ORCL-SQL>>alter
database open;
Database altered.
sys@ORCL-SQL>>archive log
list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
c:\test_flash_arcc
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
Once archiving is set up,
you need to configure the flash recovery area and its size. For this you need
to set
two initialization
parameters:
o DB_RECOVERY_FILE_DEST_SIZE
o DB_RECOVERY_FILE_DEST
The first parameter
specifies the disk quota or maximum amount of space allocable for the
flash recovery area
files in the flash
recovery area. The second parameter points to the flash recovery area.
DB_RECOVERY_FILE_DEST_SIZE
must be set before DB_RECOVERY_FILE_DEST. In a RAC database, all
instances must have the same
values for these parameters and flash recovery area must be stored in a
clustered file system or
ASM.
Configuring
initialization parameters:
system@ORCL-SQL>>alter
system set db_recovery_file_dest_size=4g;
System altered.
system@ORCL-SQL>>alter
system set db_recovery_file_dest='e:\ora_flashback';
Note: In case
you have already configured a flashback area while creating the database, or
even
subsequently, then you do
need to set the destination parameter again, as Oracle already has a place to
write
backup and
flashbackinformation. In my case, I preferred to set my own flashback area.
How big the flashback
area ought to be is contingent on quite a few factors, such as size of the
datafiles,
redologs, controlfiles.
You have also to be aware of the mean frequency and number of your block
changes,
whether you store backups
only on disk, or on disk and tape, and whether you use a redundancy-based
retention policy, or a
recovery window-based retention policy etc.
But, once you have all
these figured out, the mechanics of enabling flashback is pretty simple:
Enabling
Flashback:
system@ORCL-SQL>>shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
system@ORCL-SQL>>startup
mount
ORACLE instance started.
Total System Global Area
171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
system@ORCL-SQL>>alter
system set db_flashback_retention_target=4320;--
flashback to be retained for
--three days
System altered.
sys@ORCL-SQL>>alter
database flashback on;
Database altered.
sys@ORCL-SQL>>alter
database open;
Database altered.
So, that enables
flashback for our database. We can query v$flashback_database_log to see what's
going on
system@ORCL-SQL>>select *
from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL
RETENTION_TARGET FLASHBACK_SIZE
ESTIMATED_FLASHBACK_SIZE
-------------------- ---------
---------------- -------------- --------------
----------
539853 09-MAY-07 4320 8192000
0
We see that oldest SCN
that we can flashback to is SCN 539853. In other words this is our baseline
SCN. The
entire technology of
flashback database is being implemented from this SCN. We can easily convert
the SCN
to a timestamp, if we are
interested in seeing the time from which flashback database stands enabled.
system@ORCL-SQL>>select
scn_to_timestamp(539853) from dual;
SCN_TO_TIMESTAMP(539853)
---------------------------------------------------------------------------
09-MAY-07 04.38.30.000000000 PM
Flashing Back
Its time to put flashback
database in action. For this, I am going to create a table s and then truncate
it. But
before truncating the
table I need to find out the scn and/ or timestamp to which I will revert back
to, after
truncating my table.
sys@ORCL-SQL>>create
table s as select * from tab;
Table created.
sys@ORCL-SQL>>select
current_scn, scn_to_timestamp(current_scn) from
v$database;
CURRENT_SCN
SCN_TO_TIMESTAMP(CURRENT_SCN)
-----------
-----------------------------------------------------------------
----------
591023 10-MAY-07
11.53.52.000000000 AM
sys@ORCL-SQL>>truncate
table s;
Table truncated.
sys@ORCL-SQL>>shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL-SQL>>startup
mount
ORACLE instance started.
Total System Global Area
171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
sys@ORCL-SQL>>flashback
database to scn 591023;
Flashback complete.
sys@ORCL-SQL>>alter
database open resetlogs;
Database altered.
sys@ORCL-SQL>>select *
from s where rownum=1;
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
ICOL$ TABLE 4
sys@ORCL-SQL>>select
count(*) from s;
COUNT(*)
----------
3339
sys@ORCL-SQL>>
So, that's it. Its that
simple. On the other hand, if you aren't satisfied with what you have got after
flashing back,
you can simply undo the
results of the entire flashback operation by issuing RECOVER DATABASE command.
This will perform acomplete
recovery by applying all of the changes from the archived logs and
restoring the
status of the database to
current.Or, in case you feel you haven't flashed back far enough, you
can do so by
running the FLASHBACK DATABASE command once
again to go back further.
No comments:
Post a Comment