The flashback database
command returns the database to a past time or SCN, providing a fast
alternative to performing incomplete database recovery. Database can be
flashed back to scn or timestamp.
After
a flashback database operation in order to have write access to the flashed
back database, you must reopen it with an "alter database open
resetlogs" command.
To
enable flashback database make sure your database is in archivelog mode after
confirming do the following.
SQL>shutdown
immediate;
SQL>startup mount exclusive;
# flashback database command demands that the database should be
# mounted in exclusive mode.
SQL>alter database flashback on;
SQL>alter database open;
check the database's flashback status.
SQL>select FLASHBACK_ON from V$DATABASE;
this column should show a value of "yes".
check the following view to check the how far you can flashback
the database.
SQL>select * from V$FLASHBACK_DATABASE_LOG;
suppose the database is running for over 1 hour and you need to
flash it back then do the following.
SQL>shutdown;
SQL>startup mount exclusive;
SQL>flashback database to timestamp sysdate-1/24;
The Database will be flashed back and all the changes made since the
past hour will be lost.
SQL>alter database open resetlogs;
Disable Flashback logging.
SQL>shutdown immediate;
SQL>startup mount exclusive;
SQL>alter database flashback off;
SQL>alter database open;
SQL>startup mount exclusive;
# flashback database command demands that the database should be
# mounted in exclusive mode.
SQL>alter database flashback on;
SQL>alter database open;
check the database's flashback status.
SQL>select FLASHBACK_ON from V$DATABASE;
this column should show a value of "yes".
check the following view to check the how far you can flashback
the database.
SQL>select * from V$FLASHBACK_DATABASE_LOG;
suppose the database is running for over 1 hour and you need to
flash it back then do the following.
SQL>shutdown;
SQL>startup mount exclusive;
SQL>flashback database to timestamp sysdate-1/24;
The Database will be flashed back and all the changes made since the
past hour will be lost.
SQL>alter database open resetlogs;
Disable Flashback logging.
SQL>shutdown immediate;
SQL>startup mount exclusive;
SQL>alter database flashback off;
SQL>alter database open;
Flashback Recovery
Concepts:--
Type Of Flashback recovery:--
1)Flashback Database
2)Flashback Drop
3)Flashback Table
4)Flashback Query
Requirement for Flashback:--
Type Of Flashback recovery:--
1)Flashback Database
2)Flashback Drop
3)Flashback Table
4)Flashback Query
Requirement for Flashback:--
1) Database must be in
Archive log mode
2) Fash recovery area must be enabled.
Dependent Objects
V_$FLASHBACK_DATABASE_LOG
V_$FLASHBACK_DATABASE_LOGFILE
V_$FLASHBACK_DATABASE_STAT
For The RAC Database:--
GV_$FLASHBACK_DATABASE_LOG
GV_$FLASHBACK_DATABASE_LOGFILE
GV_$FLASHBACK_DATABASE_STAT
Syntax base on SCN:
SCN FLASHBACK DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number>;
Syntax base on TIMESTAMP:--
TIMESTAMP FLASHBACK DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value>;
Syntax base on RESTORE POINT:---
RESTORE POINT FLASHBACK DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>;
How to OFF/ON Flashback:
ALTER DATABASE FLASHBACK OFF;
ALTER DATABASE FLASHBACK ON;
Start/Stop flashback on a tablespace:--
ALTER TABLESPACE <tablespace_name> FLASHBACK ON;
ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;
2) Fash recovery area must be enabled.
Dependent Objects
V_$FLASHBACK_DATABASE_LOG
V_$FLASHBACK_DATABASE_LOGFILE
V_$FLASHBACK_DATABASE_STAT
For The RAC Database:--
GV_$FLASHBACK_DATABASE_LOG
GV_$FLASHBACK_DATABASE_LOGFILE
GV_$FLASHBACK_DATABASE_STAT
Syntax base on SCN:
SCN FLASHBACK DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number>;
Syntax base on TIMESTAMP:--
TIMESTAMP FLASHBACK DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value>;
Syntax base on RESTORE POINT:---
RESTORE POINT FLASHBACK DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>;
How to OFF/ON Flashback:
ALTER DATABASE FLASHBACK OFF;
ALTER DATABASE FLASHBACK ON;
Start/Stop flashback on a tablespace:--
ALTER TABLESPACE <tablespace_name> FLASHBACK ON;
ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;
Initialization Parameters:--
db_recovery_file_dest=<location>
db_recovery_file_dest_size= <Size> ------ keep in integer i,e 2G
db_flashback_retention_target=<minute> ---Keep in minute.
By Sql syntax:--
ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
How to Enable Flashback:--
Step 1: Verify the Database in flash back mode and the retention_target.
SQL> SELECT flashback_on, log_mode
FROM v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
NO ARCHIVELOG
db_recovery_file_dest=<location>
db_recovery_file_dest_size= <Size> ------ keep in integer i,e 2G
db_flashback_retention_target=<minute> ---Keep in minute.
By Sql syntax:--
ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
How to Enable Flashback:--
Step 1: Verify the Database in flash back mode and the retention_target.
SQL> SELECT flashback_on, log_mode
FROM v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
NO ARCHIVELOG
Step 2:-- shutdown the
database
SQL> shutdown immediate;
Step 3:-- Enable the Archive log and Set the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.
Flash Recovery Area contains the Flashback Logs, Redo Archive logs, backups files by RMAN and copies of control files. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters.
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter system set db_recovery_file_dest=<location> scope=both;
SQL> alter system set db_recovery_file_dest_size=2G scope=both;
SQL> alter system set db_flashback_retention_target=600 ;---- In Minutes.
Step 4:-- On the Flash back and open the database.
SQL> alter database flashback on;
SQL> alter database open;
Step 05: Now Verify the Database flashback mode.
SQL> SELECT flashback_on, log_mode FROM v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG
How to Recover Database from Flashback recovery area:-
Step 01: Find the Current SCN and Flashback time.
SQL> SELECT current_scn
2 FROM v$database;
CURRENT_SCN
-----------
1143033
SQL> SELECT oldest_flashback_scn,oldest_flashback_time
2 FROM gv$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
1141575 23-Feb-12
Step 02: Grant flashback to the user.
GRANT flashback any table TO <user_name>;
Step 03: Shutdown the database and start in exclusive mode
SQL> SHUTDOWN immediate;
SQL> startup mount exclusive;
Step 04: Be sure to substitute your SCN and issue the following command
SQL> FLASHBACK DATABASE TO SCN <SCN Number>;
Flashback complete.
Or
If restore point create by the user
FLASHBACK DATABASE TO RESTORE POINT <RESTORE POINT>;
Or
if flashback using TIMESTAMP
FLASHBACK DATABASE TO TIMESTAMP Timestamp ‘2012-02-23 14:00:00’;
Step 05: Now open database using resetlogs
SQL> alter database open resetlogs;
SQL> shutdown immediate;
Step 3:-- Enable the Archive log and Set the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.
Flash Recovery Area contains the Flashback Logs, Redo Archive logs, backups files by RMAN and copies of control files. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters.
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter system set db_recovery_file_dest=<location> scope=both;
SQL> alter system set db_recovery_file_dest_size=2G scope=both;
SQL> alter system set db_flashback_retention_target=600 ;---- In Minutes.
Step 4:-- On the Flash back and open the database.
SQL> alter database flashback on;
SQL> alter database open;
Step 05: Now Verify the Database flashback mode.
SQL> SELECT flashback_on, log_mode FROM v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG
How to Recover Database from Flashback recovery area:-
Step 01: Find the Current SCN and Flashback time.
SQL> SELECT current_scn
2 FROM v$database;
CURRENT_SCN
-----------
1143033
SQL> SELECT oldest_flashback_scn,oldest_flashback_time
2 FROM gv$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
1141575 23-Feb-12
Step 02: Grant flashback to the user.
GRANT flashback any table TO <user_name>;
Step 03: Shutdown the database and start in exclusive mode
SQL> SHUTDOWN immediate;
SQL> startup mount exclusive;
Step 04: Be sure to substitute your SCN and issue the following command
SQL> FLASHBACK DATABASE TO SCN <SCN Number>;
Flashback complete.
Or
If restore point create by the user
FLASHBACK DATABASE TO RESTORE POINT <RESTORE POINT>;
Or
if flashback using TIMESTAMP
FLASHBACK DATABASE TO TIMESTAMP Timestamp ‘2012-02-23 14:00:00’;
Step 05: Now open database using resetlogs
SQL> alter database open resetlogs;
*************************************************************************
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment