Article: Mastering Data History using Oracle's Flashback Data Archive Feature

The Historical data is key for business decisions and having reliable data is very important for organizations. There are frequent implications for both financial and legal data for organizations and having a secure and accurate history of change of data is crucial for business.

Oracle Flashback technology offers significant benefits for database management and recovery. 

Please see the below article how it will help to meet the organizational needs.

Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature
https://www.linkedin.com/pulse/mastering-data-history-guide-using-oracles-flashback-archive?trk=public_profile_article_view

Thanks,

https://oracleracexpert.com

How to Register or Catalog Backups, Archive logs, data file copy, FRA...etc

When you backup using RMAN all backups  will be registered with catalog. If you have taken backup without catalog and you can also register manually.

Backup pieces copied manually to different location to catalog backup piece run below command.
RMAN> CATALOG BACKUPPIECE /oracle/backup/DB1_1978087_6446005710.bkp' ;

In case if you have taken backup using o/s command and to catalog a datafile copy use below command.
RMAN> CATALOG DATAFILECOPY '/oracle/backup/employee01.bak' LEVEL 0;

In case, archive log location full and you have moved backups to different location using o/s command then you can register the archive logs using below command

RMAN> CATALOG ARCHIVELOG ‘/oracle/backup2/db1_arch_99089_0876589.arc'
If you are using Flash recovery area and want to catalog all files in the FRA use below command.
RMAN> CATALOG recovery area;

This command will prompt to confirm that you want the found files to be cataloged. If you do not want to prompt use “noprompt” clause.
RMAN> CATALOG recovery area noprompt;

If you want to catalog only files Start with specific name or belongs to specific SID (db1) use below command
RMAN> CATALOG START WITH '/oracle/backup/db1'

If you run CROSSCHECK, it will go through the files that match criteria and will be marked as available, and files not found will be marked as EXPIRED.

RMAN> CROSSCHECK BACKUP ;
If you want to DELETE EXPIRED backups you can use below command, note that EXPIRED copy will no longer appear in RMAN. You have to be extra cautious to delete backups.

RMAN> DELETE EXPIRED BACKUP ;

If you find extra copies and want to delete those extra copies use DELETE BACKUPPIECE command:
RMAN> DELETE BACKUPPIECE 4555;
Hope this helps managing your backups taken outside of RMAN recovery catalog .

Thanks & Regards 

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.

You will receive ORA-19815 is reaching maximum size of flash backup recovery area (FRA) and note that this limitation will be managed by db_recovery_file_dest_size parameter

If you got this error as one time then you might be running adhoc load or some batch job. Also you will get below message in alert.log file. Please follow one of the choice to fix the issue.

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.
2020-02-21T11:31:19.892420-07:00

************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.

In case if you archive logs are going to FRA then you should run RMAN backups and delete the unnecessary files to avoid Database hung or CRASH situation.

RMAN> backup archivelog all delete input;

If the Flash recovery area is 100% then you might receive below error when starting up the database

Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9789
Session ID: 321 Serial number: 3

Pls make sure you follow below steps to add more space to flash recovery area (FRA)

SQL> startup mount;
SQL> alter system set db_recovery_file_dest_size=<size>G scope=both;
Replace <size> with a number larger than the current and ensure is sufficient space in the destination
SQL> alter database open;

To see what FRA has run below command

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID

-------------------- ------------------ ------------------------- --------------- ----------

CONTROL FILE                          0                         0               0          0

REDO LOG                              0                         0               0          0

ARCHIVED LOG                          0                         0               0          0

BACKUP PIECE                          0                         0               0          0

IMAGE COPY                            0                         0               0          0

FLASHBACK LOG                     99.76                         0             192          0

FOREIGN ARCHIVED LOG                  0                         0               0          0

AUXILIARY DATAFILE C                  0                         0               0          0

In my case I am using for FLASHBACK LOG, these logs are different from redo logs or undo. They are a separate, optional recording of database activity.

These logs are automatically deleted when FRA is our of space and logs is not needed for recvoery within the window specified in DB_FLASHBACK_RETENTION_TARGET parameter

To determine the disk quota and current disk usage in the flash recovery area, query the view
V$RECOVERY_FILE_DEST.

Thanks & Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 The warning is clear that archive log not deleted and the below are the possible reasons. This post will help you how to manage the archive logs on standby.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

1. The archive log destination on Standby is full
2. Standby destination not accepting any logs due to issues
3. RMAN archive log configuration is set to none/default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

First run below command to identify the archive log gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 185685 185669 16

If there is an archive gap then the issue is either standby archive destination is full or standby destination has some issues and not accepting any more logs, based up on the error take appropriate action. As long as you archive logs backed upon on Primary you can proceed deleting the archive logs on standby

In order to manage the archive logs you have 2 options

1. Create a shell script to check applied logs on standby and delete rest on periodic basis
2. Configure FRA for archive logs and set the RMAN archive log deletion policy as per below

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=800GBG;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/FRA';

In case if you are using ASM specify disk group
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA_DISK';

You need to set LOG_ARCHIVE_DEST_1 parameter DB_RECOVERY_FILE_DEST so that archived logfiles will be created at Flash recovery area.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Set the RMAN archived log deletion policy as follows.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

With above setting the applied archived logs will be deleted automatically when there is a space constraint in flash recovery area.

It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archive log is deleted:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

Regards

Satishababu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Oracle Flashback Technology and features

Oracle Flashback Technology is a group of Database features that that will help you to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can do the following:
· Queries that return past data
· Recover rows or tables to a previous point in time
· Track and archive transactional data changes
· Roll back a transaction and its dependent transactions while the db online

Oracle flashback uses automatic undo management (AUM) for all flashback transactions.

Here are few Oracle flashback features.
· Flashback Database
· Flashback Table
· Flashback drop
· Flashback query
· Flashback Version/Transaction Query
· Flashback Data Archive (From Oracle 11g)
· Flashback Recovery Area:-

Flashback Database:- The FLASHBACK DATABASE is a fast alternative to performing an incomplete recovery. The database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

DB_FLASHBACK_RETENTION_TARGET- length of the desired flashback window in minutes (1440 min)

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

For ex-
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;

Limitations:
· It cannot be used to repair media failures, or to recover from accidental deletion of data files.
· You cannot use Flashback Database to undo a shrink data file operation.
· If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded
· Flashback database cannot be used against block corruptions.

Flashback Table The FLASHBACK TABLE used to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system and cannot restore a table to an earlier state across any DDL operation.

SQL> FLASHBACK TABLE test TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

Flashback Drop:

The Oracle 10g provides the ability to reinstating an accidentally dropped table from recyclebin.
SQL> FLASHBACK TABLE test TO BEFORE DROP;

Flashback Query The feature allows the DBA to see the value of a column as of a specific time, as long as the before-image copy of the block is available in the undo segment.

For ex:-
SQL> SELECT comments FROM employee AS OF TIMESTAMP TO_TIMESTAMP ('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

SQL> SELECT comments FROM employee AS OF SCN 722452;

Flashback Version/Transaction Query Flashback Query only provides a fixed snapshot of the data as of a time. Use Flashback Version Query feature to see the changed data between two time points.

Ex: - The following query shows the changes made to the table:

SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, rate from rates versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries using FLASHBACK_TRANSACTION_QUERY view. The UNDO_SQL column in the table shows the actual SQL Statement.

For Ex:-
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query

Oracle 11g has more enhancements in Flashback Transaction and LogMiner. The LogMiner Viewer has been incorporated into Enterprise Manager and integrated with the new Flashback Transaction feature, making it simple to recover transactions. Flashback Transaction allows the changes made by a transaction to be undone.

Flashback Data Archive (From Oracle 11g) : Flashback data archive allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive.

User needs the FLASHBACK ARCHIVE ADMINISTER, FLASHBACK ARCHIVE privileges

SQL> CREATE FLASHBACK ARCHIVE flash_back_archive

TABLESPACE flash_back_archive QUOTA 10G RETENTION 5 YEARS;

Flashback Recovery Area:-
Flash recovery area is a disk location in which the database can store and manage files related to Backup and Recovery. To setup a flash recovery area, you must choose a directory location or Automatic Storage Management disk group to hold the files.

Flash recovery area simplifies the administration of your database by automatically naming recovery-related files, retaining the files as long as they are needed for restore and recovery activities, and deleting the files when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To Setup Flash Recovery Area (FRA), you just need to specify below two parameters.
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)

2. DB_RECOVERY_FILE_DEST (Location of FRA)

Performance Guidelines for Oracle Flashback Technology
· For Oracle Flashback Version Query, use index structures.

· Not to scan entire tables and use indexes to query small set of past data. If you need to scan a full table then use parallel hint to the query

· Keep the statistics current as cost-based optimized relies on statistics and use the DBMS_STATS package to generate statistics for tables involved in a Oracle Flashback Query.

· In a Oracle Flashback Transaction Query, the xid column is of the type RAW(8). Use the HEXTORAW conversion function: HEXTORAW(xid)to take advantage of inbuilt function

· The I/O performance cost is mainly paging in the data and undo blocks I the buffer cache. The CPU performance cost is to apply undo.

· A Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Move Datafile between DikGroups in ASM

This is one of the common issues where users make mistakes while adding new data file or tablespace and creates in different disk group.

Follow the steps to move data file between disk groups

Here we are copying a Datafile from FLASH to DATA1 Disk Group.

1. Use RMAN to copy the data file from FLASH to DATA disk group.

RMAN> BACKUP AS COPY DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ FORMAT ‘+DATA1’;

Or you can also specify the data file number instead of full path

RMAN> BACKUP AS COPY DATAFILE 5 FORMAT ‘+DATA1’;

2. Offline the datafile that you want to move to new disk group.

SQL> ALTER DATABASE DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ OFFLINE;

3. Switch the data file to copy

RMAN> switch datafile ‘+FLASH/datafile/usertbs7.588.475363994’ to copy;

4. Recovery the datafile

RMAN> RECOVER DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ;

5. Bring the data file online.

SQL> ALTER DATABASE DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ONLINE;

6. Delete old datafile from ASM disk group.

RMAN> DELETE DATAFILECOPY ‘+FLASH/datafile/usertbs7.588.475363994’;

7. Check the Datafile Status

Check the status of data file by running the below query, you should be able to see the status as "AVAILABLE".

SQL> SELECT FILE_ID,FILE_NAME,STATUS FROM DBA_DATA_FILES;

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Oracle Flashback Data Archive - Oracle Total Recall

Flashback Data archive is the new feature introduced in Oracle 11g. It provides the ability to track and store all transitional changes to a table over its lifetime. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive. It allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time.

The database can have multiple flashback data archives, but only a single default archive. The individual flashback archive consists of one or more tablespaces or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace.

When a DML transaction commits an operation on a flashback archive enabled table, the FBDA (Flashback Data Archiver) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

To use this feature user should have below privileges
1.FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table
2.FLASHBACK ARCHIVE ADMINISTER system privilege to enable or disable Flashback data archive.

Creating a Flashback Data Archive
Create Flashback Data archive using below command
SQL> CREATE FLASHBACK ARCHIVE flashback_archive TABLESPACE flashback_archive_tbs QUOTA 15G RETENTION 5 YEARS;

Altering a Flashback Data Archive
You can add, modify and remove tablespaces, and change the retention, purge some or all of its data.

Add up to 2G of tablespace “flashback_archive_tbs1” to Flashback Data Archive flashback_archive1

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

Change the maximum space that Flashback Data Archive “flashback_archive1” can use in tablespace flashback_archive_tbs1 to 5G

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 MODIFY TABLESPACE flashback_archive_tbs1QUOTA 5G;

Remove tablespace “flashback_archive_tbs1” from Flashback Data Archive
SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 REMOVE TABLESPACE flashback_archive_tbs1;

Dropping a Flashback Data Archive
Remove Flashback Data Archive flashback_archive1 and all its historical data
SQL> DROP FLASHBACK ARCHIVE flashback_archive1;

Enabling and Disabling Flashback Data Archive
By default, flashback archiving is disabled for any table and you need FLASHBACK ARCHIVE privilege to enable for any table. After flashback archiving is enabled for a table, you can disable it only if you either have SYSDBA or FLASHBACK ARCHIVE ADMINISTER privilege.

Create table EMP and store the historical data in the DEFAULT Flashback Data Archive
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE;

Create table EMP and store the historical data in the Flashback_Archive1
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE Flashback_Archive1;

Enable flashback archiving for the table EMP and store data in the default Flashback Data Archive
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE;

Enable flashback archiving for the table EMP and store data in Flashback Data Archive Flashback_Archive1
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;

Disable flashback archiving for the table employee:
SQL> ALTER TABLE EMP NO FLASHBACK ARCHIVE;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Flash Recovery Area (FRA) in Oracle 10g

Flash recovery area is a disk location in which the database can store and manage files related to Backup and Recovery. To setup a flash recovery area, you must choose a directory location or Automatic Storage Management disk group to hold the files. FRA cannot be stored on a raw file system.

Flash recovery area simplifies the administration of your database by automatically naming recovery-related files, retaining the files as long as they are needed for restore and recovery activities, and deleting the files when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To Setup Flash Recovery Area (FRA), you just need to specify below two parameters.
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)
2. DB_RECOVERY_FILE_DEST (Location of FRA)

The DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

You can initialize these parameters by adding them in “init.ora” file or setting them using ALTER SYSTEM SET command or DBCA (Database Configuration Assistant).
For ex:-
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both
SQL> ALTER SYSTEM SET db_recovery_file_dest='/oradata/FRA;
For example, If FRA is a Automatic Storage Management (ASM) disk group
SQL> ALTER SYSTEM SET db_recovery_file_dest='+FLASH’sid='*’ ( sid=’*’, if using RAC)

In a RAC database, all database instances MUST have the same values for these parameters.

Disable the Flash Recovery Area:
To disable FRA set the DB_RECOVERY_FILE_DEST initialization parameter to a null string.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' scope=both;

Monitor the Flash recovery area using Dynamic performance views
To find out the current flash recovery area location, disk quota and current usage in flash recovery area query V$RECOVERY_FILE_DEST.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
------ ----------- ----------- ----------------- -------------
FLASH 10737418240 5368709120 256000 28

The $FLASH_RECOVERY_AREA_USAGE views shows the percentage of the total disk quota used by different types files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 2 0 4
ONLINELOG 6 0 8
ARCHIVELOG 8.05 1.11 20
BACKUPPIECE 33.94 5.46 32
IMAGECOPY 5.64 1.23 10
FLASHBACKLOG .06 0 1

Files Stored in the Flash Recovery Area
PERMANENT Files - Multiplexed copies of Control files and online redo log files
TRANSIENT Files - Archived redo logs not yet backed up, Datafile copies, Incremental backups, Contolfile auto backups (which include spfile)

Only TRANSIENT files will be deleted from flash recovery area based up on following rules:
•Files those are obsolete under the configured RMAN retention policy.
•Transient files that are copied to tape.
•In a Data Guard environment, archived redo log files can be deleted based up on archived redo log deletion policy.

Errors:
ORA-19809: limit exceeded for recovery files
Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.
Action: The error is accompanied by 19804. See message 19804 for further details

ORA-19804: cannot reclaim nnnnn bytes disk space from mmmmm limit
Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.
Action: There are five possible solutions:
1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5) Delete files from recovery area using RMAN.

ORA-38786: Flash recovery area is not enabled.
Cause: An attempt was made to perform a command that requires Flash recovery area to be enabled.
Action: Set DB_RECOVERY_FILE_DEST to an appropriate location and retry

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com