Archive

Archive for the ‘Flashback Recovery’ Category

ORA-00439: feature not enabled: Flashback Database

March 13, 2011 1 comment

Some feature is not enabled in oracle database standard edition, as a result we can not use flashback feature in Oracle Database if you are use in standard edition of oracle.

Following Error Message show in Oracle 10g standard edition

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Version of Oracle

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Features are available in standard edition

SQL> SELECT Parameter,Value FROM V$OPTION Where Value = 'TRUE';
PARAMETER                                                        VALUE                                                           
----------------------------------------------------------------------
Objects                                                          TRUE                                                            
Connection multiplexing                                          TRUE                                                            
Connection pooling                                               TRUE                                                            
Database queuing                                                 TRUE                                                            
Incremental backup and recovery                                  TRUE                                                            
Instead-of triggers                                              TRUE                                                            
Parallel load                                                    TRUE                                                            
Proxy authentication/authorization                               TRUE                                                            
Plan Stability                                                   TRUE                                                            
Transparent Application Failover                                 TRUE                                                            
Sample Scan                                                      TRUE                                                            
Java                                                             TRUE                                                            
OLAP Window Functions                                            TRUE

So flashback database is disabled in standard edition or in standard one  edition. To be able to flashback on you have to use enterprise edition.

Advertisements

Flashback Recovery

December 16, 2009 Leave a comment

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