Type of flashback recovery:
- Flashback Database (We can revert database at a past time)
- Flashback Drop (Reverses the effects of a DROP TABLE statement)
- Flashback Table (Reverses a table to its state at a previous point in time)
- Flashback Query (We can specify a target time and then run queries, viewing results and recover from an unwanted change)
- Flashback Transaction Query (We can view changes made by a transaction during a period of time.)
Requirement for Flashback:
- Database must be in Archive log mode
- Must have flash recovery area enable
According to the Oracle documentation, Flashback technologies are applicable in repairing the following user errors.
- Erroneous or malicious DROP TABLE statements
- Erroneous or malicious update, delete or insert transactions
- Erroneous or malicious batch job or wide-spread application errors
Dependent Objects
- V_$FLASHBACK_DATABASE_LOG
- V_$FLASHBACK_DATABASE_LOGFILE
- V_$FLASHBACK_DATABASE_STAT
- GV_$FLASHBACK_DATABASE_LOG
- GV_$FLASHBACK_DATABASE_LOGFILE
- GV_$FLASHBACK_DATABASE_STAT
Syntax:
Syntax base on SCN:
SCN FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number>
Syntax base on TIMESTAMP:
TIMESTAMP FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value>
Syntax base on RESTORE POINT:
RESTORE POINT FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>
Flashback Syntax Elements
How to OFF Flashback
Syntax:
ALTER DATABASE FLASHBACK OFF;
How to ON Flashback
Syntax:
ALTER DATABASE FLASHBACK ON;
Start flashback on a tablespace
ALTER TABLESPACE <tablespace_name> FLASHBACK ON;
Stop flashback on a tablespace
ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;
Initialization Parameters
Setting the location of the flashback recovery area
db_recovery_file_dest=/oracle/flash_recovery_area
Setting the size of the flashback recovery area
db_recovery_file_dest_size=2147483648
Setting the retention time for flashback files (in minutes) -- 2 days
db_flashback_retention_target=2880
Set Retention Target
Syntax:
ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
SQL Code:
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;
How to Enable Flashback
Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes) and set recovery size up to 2 GB in file “/recovery/flashback”
Step 01: 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 02: Shutdown the database and start in exclusive mode
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 268438380 bytes
Database Buffers 339738624 bytes
Redo Buffers 2940928 bytes
Database mounted.
Step 03: Enable the Archive log and Set the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.
Flash Recovery Area created by the DBA, is the allocation of space on the disk to hold all the recovery related files in one, centralized place. 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> alter database archivelog;
Database altered.
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=600;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST=
‘C:\oracle\product\10.2.0\flash_recovery_area\ORCL\FLASHBACK’;
System altered.
N.B: For UNIX system issue the following command
alter system set DB_RECOVERY_FILE_DEST=‘/recovery/flashback’;
Step 04: On the Flash back and open the database.
SQL> alter database flashback on;
System altered.
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
SQL> SELECT name, value
FROM gv$parameter
WHERE name LIKE ‘%flashback%’;
NAME VALUE
---------------------------------------
db_flashback_retention_target 600
SQL> SELECT estimated_flashback_size
FROM gv$flashback_database_log;
ESTIMATED_FLASHBACK_SIZE
------------------------
22835200
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 16-DEC-09
Step 02: Grant flashback to the user.
So that user can create a restore point and flashback
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 flashback using TIMESTAMP
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
FLASHBACK DATABASE TO TIMESTAMP Timestamp ‘2009-11-05 14:00:00’;
FLASHBACK DATABASE TO TIMESTAMP
TO_TIMESTAMP (‘2009-11-11 16:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);
Step 05: Now open database using resetlogs
alter database open will fail
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
alter database open resetlogs will be succeed
SQL> alter database open resetlogs;
Database altered.
Step 06: See the flashback status
SELECT *
FROM gv$flashback_database_stat;
INST_ID BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------- ------------ --------- --------------- --------- ---------- ------------------------
1 16-DEC-09 16-DEC-09 827392 9797632 130048 0
Step 06: Now switch the log and use RMAN to clear the archive log following way
alter system switch logfile;
shutdown immediate;
startup mount exclusive;
alter database flashback off;
alter database noarchivelog;
alter database open;
SQL> SELECT flashback_on, log_mode
2 FROM v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
NO NOARCHIVELOG
Run rman to delete the archive log
$rman target sys/oracle@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 15:06:09 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1223903242)
RMAN> crosscheck archivelog all;
RMAN> delete archivelog all;
RMAN> list archivelog all;
Note:
In RAC Database, flashback recovery area must be store is clustered file system or in ASM
Default retation target is 1440 (One Days).
If we want to retain flashback logs to perform a 48 hour flashback, set the retention target to 2880 minutes (2 days x 24 hours/day x 60 minutes/hour)
By default, flashback logs are generated for all permanent tablespaces
Important:
- If we disable Flashback Database for a tablespace, then we must take its datafiles offline before running FLASHBACK DATABASE.
- We can enable Flashback Database not only on a primary database, but also on a standby database.
No comments:
Post a Comment