Oracle Flashback Recovery

-
Flashback query is a powerful and useful feature introduced in Oracle 9i, and enhanced greatly in Oracle 10g, that can help us recover data, lost or corrupted, due to human error. One big advantages of using flashback over point-in-time recovery is that for the latter not only transactions from the time of error to the current time would be lost but also the system will be unavailable for the duration of the recovery. For flashback query, on the other hand, there will be no down time needed and repair or recovery is less labor and time intensive than what it used to be in earlier versions of Oracle. With the new features like Recycle Bin, Flashback databases and Flashback Drop in Oracle 10g, the flashback capability introduced in 9i has been improved tremendously now turning a small feature into a powerful tool in the new Oracle releases.

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-17

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 ‘2017-11-05 14:00:00’;
FLASHBACK DATABASE TO TIMESTAMP
                   TO_TIMESTAMP (‘2017-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-17   16-DEC-17 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 2017

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1223903243)

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.

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

-
SQL SERVER – Event ID 107- Report Server Windows Service cannot connect to the report server database. Error with Report Server One of my clients reported an error which is the subject of the blog. In the event viewer they were seeing below error:     Event Type: Error Event Source: Report Server Windows Service (MSSQLSERVER) Event Category: Management Event ID: 107 Computer: SQLSERVER Description: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database The error is generic because it doesn’t tell the exact error which is causing a connectivity error. My immediate question was that, are there any more errors? So, I asked them to look at Reporting Service Log file. Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. The application domain WindowsService_17 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report se
Read more

SQL Server Builds Information

-
Image
Very Useful information about SQL Server Builds .  It is nicely presented in the blog . Please see  below for our reference . Microsoft SQL Server Version List What version of SQL Server do I have? This unofficial build chart lists all of the known Service Packs ( SP ), Cumulative Updates ( CU ), patches, hotfixes and other builds of MS SQL Server 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Useful articles: How to identify your SQL Server version and edition SQL Server Internal Database Versions Microsoft SQL Server Support Lifecycle Microsoft SQL Server Home Microsoft SQL Server Developer Center Microsoft TechNet: Microsoft SQL Server Microsoft Knowledge Base Sqlservr.exe versions   Quick summary:   RTM (no SP ) SP1 SP2 SP3 SP4   SQL Server 2017      codename vNext 14.0.1000.169 *new           SQL Server 2016 13.0.1601.5 13.0.4001.0 or 13.1.4001.0 13.0.5026.0 or 13.2.5026.0   S
Read more

Using DBCA silent install and disabling automatic memory management

-
Image
Avoid issue [DBT-11211] /app/oracle/product/12.2.0/dbhome_1/bin=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 20 -emConfiguration NONE [FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.    CAUSE: The current total physical memory is 11GB. Solution : disable automatic memory management with in the response file and you will be fine :) .  /app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -automaticMemoryManagement FALSE -memoryPercentage 20 -emConfiguration NONE Enter SYS user password: Enter SYSTEM user password: [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.    CAUS
Read more