1) Oracle flashback transaction query lets you view changes made by a single transaction or by all the transactions during a period of time.
2)Oracle flashback table returns a table to its state at a previous point in time.
3)Oracle flashback Drop reverses the effects of a drop table statement.
4)Oracle flashback database provides a more efficient alternative to database point in time recovery.
To Check the Flashback status:-
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
PROD NO
-->archive log should be enabled.
--->show parameter undo_retention
---->show parameter DB_FLASHBACK_RETENTION_TARGET
---->show parameter db_recovery_file_dest
----->show parameter db_Recovery_file_dest_size
To Enable flashback feature on database?
1)database must be started with spfile.
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/home/db/tech_st/11.1.0/db
s/spfilePROD.ora
2)Oracle flashback table returns a table to its state at a previous point in time.
3)Oracle flashback Drop reverses the effects of a drop table statement.
4)Oracle flashback database provides a more efficient alternative to database point in time recovery.
To Check the Flashback status:-
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
PROD NO
-->archive log should be enabled.
--->show parameter undo_retention
---->show parameter DB_FLASHBACK_RETENTION_TARGET
---->show parameter db_recovery_file_dest
----->show parameter db_Recovery_file_dest_size
To Enable flashback feature on database?
1)database must be started with spfile.
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/home/db/tech_st/11.1.0/db
s/spfilePROD.ora
2)database must be in archivelog mode.
3)undo_management showld be auto
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
4)set the recovery file destination or flashback area which will contain all flashback logs depending on the undo retention period.
5)set the recovery file destination size.This is the hard limit on the total space to be used by target database recovery files created in the flash recovery area.
6)Set the flashback retention target.This is the upper limit (in minutes) on how far back in time the database may be flashed back.How far back one can flashback a database depends on how much flashback data oracle has kept in the flash recovery area.
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/home/db/tech_st/11.1.0/flashback' scope=both;
System altered.
SQL> alter system set db_flashback_retention_target=1440 scope=both;
System altered.
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
PROD NO
we need to set alter flashback in mount state.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1318172 bytes
Variable Size 436208356 bytes
Database Buffers 620756992 bytes
Redo Buffers 13049856 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
PROD YES
Thank you for sharing very nice content
ReplyDeleteSql server DBA Online Training