|
|
|
|
|
|
|
|
Oracle 10g a Flashback Database |
|
|
|
|
Back
Next
Introduction to Oracle 10g Flashback Database
Oracle 10g’s brilliant alternative to database point in time
recovery is the the Flashback Database feature. With this
feature in place you can do almost everything that you can
do with point in time recovery, without actually having to
go through all the disruptions and hassle that a PITR
necessarily entails.
Unlike other flashback features, which depend on undo data
for reconstructing your lost data, Flashback Database uses
flashback 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
and flashback 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.
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:
DB_RECOVERY_FILE_DEST_SIZE
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 flashback
information. 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 a complete 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
Back
Next
|
|
|
|
|
|
|
|
Home