Imagine this scenario: how would your organization respond?
Your organization receives an urgent call from a customer or an internal user. The company’s customer-facing application indicates that a customer’s account was inadvertently deleted. This needs to be corrected immediately, and any changes to the account must be tracked for compliance and auditing purposes.
How do many organizations respond?
Typically, organizations would use complex backup and restore procedures to undo the deletion and restore the account to the correct state.
While the time to mitigation is of the essence in this scenario, there are many questions that need to be answered before any mitigation can occur. When was the change made? What was the last correct data in the account? Who made the change? And more…
How easily could you answer these questions? And what happens if you find the change was made days, weeks, or months earlier? How much data will need to be restored, and how much time will that require?
How can Flashback Time Travel help?
Easily determine when the data was changed, what was changed, and who made the modification
Flashback Time Travel enables organizations to capture, and maintain, a secure historical record of all transactional changes to production tables. User-context metadata information can also be collected (if enabled), making it easier to determine who changed the data.
For more information on user-context tracking, please see the More Information section below.
In the scenario above, after the data was accidentally modified, Flashback Time Travel enables the organization to review the transactional change history for the specific account over time – the length of time depends on how long the organization wants to retain the historical data. Once the errant transaction was determined, the organization could then take action to mitigate the error without the need for complex backup and restore procedures, as well as identify who made the change.
For example:
Using the earlier scenario, let us say it was the customer who determined their account was missing, and made this discover just now. Fortunately, when the organization implemented Flashback Time Travel, they decided to maintain the transactional change history for ten years on the accounts table. This means they can review the state of the customer’s account for up to ten years in the past.
After reviewing the change history for the user’s account, it was determined that the account was inadvertently deleted five months earlier. The organization knows that at 9:30 AM on 10 October 2024, the account data for ‘Aperio123’ was correctly stored in the database (having been deleted at 9:31 AM that day). With this information, and without needing a complex restore procedure, the organization uses Flashback Query to confirm the contents of the table as of 9:30 AM and then re-inserts the lost data in the database.
The following query retrieves the state of the user’s account as of 9:30 AM, October 10, 2024:
SELECT * FROM accounts AS OF TIMESTAMP
TO_TIMESTAMP(‘2024-10-10 09:30:00’, ‘YYYY-MM-DD HH:MI:SS’)
WHERE acctname = ‘Aperio123’;
Then, restores the account information.
INSERT INTO accounts
(SELECT * FROM accounts AS OF TIMESTAMP
TO_TIMESTAMP(‘2024-10-10 09:30:00’, ‘YYYY-MM-DD HH:MI:SS’)
WHERE acctname = ‘Aperio123’);
Why is this important for your organization?
Business and service organizations are held accountable for maintaining a secure history of data on which key business decisions, which often have both legal and business bottom-line ramifications, are based. Having a reliable history of changes to your data is business critical. Flashback features, such as Flashback Query, enable organizations to track and retrieve old versions of data without writing complicated logic for historical data management.
For more information on Flashback Query and other Oracle Flashback Technologies, please see the More Information section.
While this is just a simple scenario, consider the everyday use cases that are driving the need to track changes to data over time — U.S. government regulations such as the Sarbanes-Oxley Act, the Health Insurance Portability and Accountability Act (HIPAA) and international agreements such as Basel II are driving the need to track transactional changes to data over time.
Please read on to learn how Oracle Database keeps your historic transactional data secure.
Secure transactional history recorded for regulatory compliance & auditing
Flashback Time Travel maintains the transactional change history in a read-only state. This means that users, including the schema owner and the database administrator, can only query the history; even these privileged users cannot alter the historic record. In addition, Oracle Database prevents any modifications to history tables that would invalidate the history.
Using Flashback Time Travel
Flashback Time Travel enables users to create multiple Flashback Archives. A Flashback Archive consists of one or more tablespaces or parts thereof. The space managed by a Flashback Archive maintains the historic transactional data for a specific table, or tables, using separate “history” table(s).
When a Flashback Archive is created, a retention time is specified. The retention time is the amount of time (day/month/year) the transactional change data will be available. All the historical transactional data, maintained in the same Flashback Archive, will have the same retention time. Different Flashback Archives can have different retention times.
Creating a Flashback Archive:
Creating a Flashback Archive named “longtime” that uses tablespace tbs2, and a five-year retention time:
CREATE FLASHBACK ARCHIVE longtime TABLESPACE tbs2 RETENTION 5 YEAR;
Creating a Flashback Archive named “shortime” that uses tablespace tbs3, and a one-month retention time:
CREATE FLASHBACK ARCHIVE shortime TABLESPACE tbs3 RETENTION 1 MONTH;
In this example, all the historical transactional data being maintained by the ‘longtime’ Flashback Archive will be available for five years. The historical transactional data being maintained by the “shortime” Flashback Archive will only be available for one month.
Enabling Flashback Archive for compliance and auditing
Adding a table, to a Flashback Archive for compliance, and auditing purposes, is easily done using CREATE or ALTER TABLE DDL.
Imagine you have an ACCOUNTS table that stores details like account number, account name, balance, contact, and account status. Due to company policy or regulatory requirements, you need to retain historical changes to this data for five years.
Using the scenario from earlier, here are the simple steps needed to begin tracking all historical changes for the ACCOUNTS table with a five-year retention time.
Create the ACCOUNTS table and maintain the historical data in the Flashback Archive “longtime”:
CREATE TABLE accounts (ACCTNUM NUMBER (4) NOT NULL, ACCCTNAME VARCHAR2(20), BALANCE NUMBER(4) NOT NULL, CONTACT VARCHAR2(20), ACCTSTATUS VARCHAR(4)) FLASHBACK ARCHIVE longtime;
Or, for an existing table:
ALTER TABLE accounts FLASHBACK ARCHIVE longtime;
Oracle Database provides a set of features collectively known as Oracle Flashback Technologies that support viewing past states of data without requiring the database to be restored from backup.
For more information on Oracle Flashback Technologies, please see the More Information section below.
Flashback Archive is supported with multitenant for both local and shared undo configurations. Flashback Archives are created at per PDB level just as user tables are created and enabled for Flashback Archive at a PDB level. The additional Flashback Technologies may have different levels of multitenant support.
For more information on Multitenant Support, please see the More Information section below.
Flashback Time Travel is included with both Oracle Database Enterprise Edition, and Standard Edition. Flashback Time will automatically partition the history table(s). A separate partitioning license is not required since the partitioning is implemented, and managed, internally.
Storage and IO Considerations
Capturing, and managing large amounts of historic transactional data (over long time periods) does impose additional data storage and IO requirements, these can include:
- Increased redo: Flashback Archive needs to be able to materialize the whole row, even if a single column was changed. Compared to typical transactional processing, where redo captures only changed column values. Because of this, users should expect additional redo space requirements proportional to the table’s row size times the volume of DML activity.
- Increased IO: Expect additional IO (upwards of three times per transaction) related to the increased IO needed to mine undo for transactional changes, and for the additional management needed to maintain the history tables.
General Usage Considerations
- Selective Usage: Flashback Archive should be enabled on a few chosen tables, and not all the tables in a schema. Maintaining transactional change history data consumes space and CPU resources, so a careful decision must be made on the choice of the tables.
Choose the table(s) that will most benefit your organization related to compliance and auditing, while avoiding tables that are not required for those needs.
Migrating Between Releases
A PL/SQL package called DBMS_FLASHBACK_ARCHIVE_MIGRATE enables the migration of Flashback Archive enabled tables from a database on any release (in which the package exists) to any database on any release (that supports Flashback Archive).
Using the DBMS_FLASHBACK_ARCHIVE_MIGRATE PL/SQL package users can export and import the Flashback Archive base tables, along with their history, to another database via the Oracle Transportable Tablespaces capability.
More information
Oracle Flashback Technologies, see here.
Flashback Time Travel usage examples, see here.
Gathering user-context tracking information, see here.
General guidelines for Oracle Flashback Technologies, see here.
Multitenant restrictions for Flashback Technologies, see here.
Migrating Flashback Archives between releases, see here.