Recover a TRUNCATE TABLE Statement Using FLASHBACK DATABASE Feature

Scenario Summary:

This was a real production scenario I've gone through to restore big number of tables been truncated mistakenly by someone who ran a script that suppose to run on test environment. The DB version is 11.2.0.4 and the Flashback Database feature was turned ON.

Scenario Details:

- An Architect mistakenly ran a script to truncate multiple tables on production DB instead of running it on Test DB --Was opening multiple windows at the same time, one of them was Prod.
- The issue been identified few hours later.
- Because of the late identification of the issue, other applications which were not impacted by that truncation has loaded data on other tables sharing the same tablespace of the truncated tables.
- Due to the storage free space constraint on the same server and because of other infrastructure limitations, it was very difficult to restore the impacted tablespace to a point in time in the same server or even in a different server.
- I decided to use the Flashback Database approach to restore those tables, as I managed to get a downtime for 1 hour on the impacted DB.

Facts: 

- If your DB version is 12c and above I recommend you to use "RECOVER TABLE" RMAN command to restore the truncated tables instead of using Flashback method explained in this article; unfortunately, because my impacted DB version was 11.2.0.4, "Recover Table" feature is not available.

- When it comes to recover from a TRUNCATE statement in 11g, you will end up having few recoverability options to select from. Up to Oracle 19c, still "FLASHBACK TABLE" feature cannot recover a TRUNCATE operation, because this feature is dependent on UNDO data which get invalidated if a DDL get executed against the table which change its architecture, TRUNCATE is doing the same by moving the table's high watermark.
Available options to recover a TRUNCATE statement can be using "Flashback Data Archive" feature, if it's enabled before the truncation, otherwise the best option you will have in your arsenal is to flash back the whole DB, then export the impacted tables, then flash forward/recover the DB to return it back to its latest status, then import the impacted tables. This solution involves a downtime on the whole DB. Another option you can use; is to restore the whole tablespace --where the truncated tables reside to a point in time to a different/new DB and export the truncated tables from there and import them back to the impacted DB.

- To Flash back a database using Flashback Database feature, the following requirements should be met:

    1- Flashback Database feature should be turned on before the truncation.
    2- All the Flashback Logs generated up to the time before the truncation should be available on the server (FRA).
    3- All the Archive Logs generated up to before the truncation time should be available on the server.(either as files or backup pieces).

V.Imp Note:
Once you come to know about the truncation incident, DO NOT change the DB structure by CREATE OR DELETE any TABLESPACE or DATAFILE, or SHRINK a DATAFILE or RECREATE the CONTROLFILE as this may invalidate the recovery when you try to recover the DB to flash it forward to the original status!

Technical Steps:

Check the prerequsites for the Flashback to succeed:

- Check how far the DB can be flashed back:

SQL> col OLDEST_FLASHBACK_SCN for 999999999999999999999
           select * from V$FLASHBACK_DATABASE_LOG;

- Increase the flashback retention to avoid the auto-deletion of the Flashback archive files: [e.g. 4 days]

SQL> alter system set db_flashback_retention_target=6000;

- Stop/Disable any cron or backup jobs that can delete any RMAN backups or archivelogs: [Until the truncated tables get restored]

Get the SCN just before the truncation time: [Just before the errorenous truncate]

My Truncation time happened at 11/07/2022 18:00:12

-- Convert Timestamp to SCN

SQL> col scn for 999999999999999999999999
           select timestamp_to_scn(to_timestamp('11/07/2022 18:00:00','DD/MM/YYYY HH24:MI:SS')) as scn from dual;




- Check the availability of the archive logs required to cover the flashback period: [Requirement for Flashback to succeed]

SQL> select SEQUENCE#,FIRST_TIME from v$archived_log where FIRST_TIME>sysdate-1;

For my bad luck only the last 6 hours archive logs are available on the server, which means I need to restore the older archives to cover the last 24 hours needed for the flashback to work:

- Checking the needed Archive logs that are not available and restore them:

SQL> select SEQUENCE#,FIRST_TIME from v$archived_log where FIRST_TIME>sysdate-1 and name is  null;

 

- Restore the needed Archive logs from TAPE to the server: [Below tape parameters are specific to my environment]

RMAN> run
{
allocate channel 'dev_1' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=DB01,OB2BARLIST=RDB01_ORCL_Arch _Log_SO)';
#send device type 'sbt_tape' 'OB2BARHOSTNAME=rdb01';
set archivelog destination to '/backup/arch/';
restore archivelog from sequence 470 until sequence 485;
}


-- Catalog the restored archivelogs:

RMAN> catalog start with '/backup/arch';


It's always wise to take a full DB backup before starting the flashback task, just in case the DB have a problem with flashing it forward.


Flashback the DB to before the tables truncation time:

-- Note down the current SCN:
SQL> col current_scn for 999999999999999999999999
           select current_scn from v$database;

6605314662302

-- Flashback the DB to before the Truncation:

SQL> alter system checkpoint;
SQL> shu immediate
           startup mount

>>From rman:
# rman target /
RMAN> FLASHBACK DATABASE TO SCN 6607836579369;

-- Open the DB in Read Only mode:
RMAN> alter database open read only;


Export the truncated tables: [Using legacy exp as expdp will not work in Read Only mode]

# exp  \'/ as sysdba\' TABLES=PL.F_PRICE,PL.E_CODES,PL.ET_STATUS,PL.BATCH_SO,PL.FL_INFO DIRECT=y CONSISTENT=y STATISTICS=NONE FEEDBACK=100000 RESUMABLE=y RESUMABLE_NAME=DBA_BUNDLE_EXPORT RESUMABLE_TIMEOUT=86400 FILE=/backup/EXPORT_TABLES.dmp log=/backup/EXPORT_TABLES.log

Note: Because legacy exp tool doesn't provide parallelism option, you can make your own parallelism by running the same exp command from different sessiosns providing different set of tables in each session and run them at the same time.

Once the export is done, Return the DB back to its original state before the flashback:

-- First use recover technique to recover the DB until the latest REDOLOG:
   SQL> SHU IMMEDIATE
   SQL> STARTUP MOUNT

  # rman target /
   RMAN> recover database;
   RMAN> alter database open;

Unfortunately, in my case a new tablespace was created after the truncation of tables which made the above command to fail, so instead of using "recover database" command I had to use the FLASHBACK command using the SCN I noted down before the first shutdown of the DB.

If recover technique didn't work use Flashback:
-- From mount mode:
RMAN> FLASHBACK DATABASE TO SCN 6605314662302;
RMAN> alter database open resetlogs;

Once the DB is open in RW mode,

Import the dump file to recover the truncated tables:

-- Disable the triggers on the truncated tables first: [If found]
SQL> select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where owner='PL' and table_name in ('F_PRICE','E_CODES','ET_STATUS','BATCH_SO','FL_INFO');

-- You can also set the tables into NOLOGGING mode (if you have no data guard setup)

-- Execute the import commands:
I distributed the tuncated tables on three sets based on their size, then execute each set in the background to have the tables get imported in parallel:

Note: In my real case I used INDEXES=N parameter during the import as I've collected the DDL for all indexes on the impacted tables before the import, then dropped them, and lastly created them back after the import with high PARALLEL degree and NOLOGGING option to save the time.

# Set1:
nohup imp  \'/ as sysdba\' fromuser=PL touser=PL TABLES=F_PRICE, E_CODES  file=/backup/EXPORT_TABLES.dmp log=/backup/imp_PL_truncated_tables_set1.log ignore=y  RESUMABLE=Y RESUMABLE_NAME=SET1_SUSPENDED resumable_timeout=3600 commit=y buffer=10240 feedback=10000  > nohup1.log 2>&1 &

 

# Set2:
nohup imp  \'/ as sysdba\' fromuser=PL touser=PL TABLES=BATCH_SO  file=/backup/EXPORT_TABLES.dmp log=/backup/imp_PL_truncated_tables_set2.log ignore=y  RESUMABLE=Y RESUMABLE_NAME=SET2_SUSPEND  resumable_timeout=3600 commit=y buffer=10240 feedback=10000  > nohup1.log 2>&1 &

 

# Set3:
nohup imp  \'/ as sysdba\' fromuser=PL touser=PL TABLES=FL_INFO  file=/backup/EXPORT_TABLES.dmp log=/backup/imp_PL_truncated_tables_set3.log ignore=y  RESUMABLE=Y RESUMABLE_NAME=SET3_SUSPEND  resumable_timeout=3600 commit=y buffer=10240 feedback=10000  > nohup1.log 2>&1 &

After the completion of the import:

-- Enable the constraints: [In case the import fails to enable them]
SQL> select 'alter table pnl_owner.'||table_name||' ENABLE CONSTRAINT '|| constraint_name||' ;' from dba_constraints where owner='PNL_OWNER' and table_name in ('ADV_STG_FUEL_INFO','DIM_FUEL_PRICE','ERROR_CODES','PNL_ETL_JOB_STATUS','DIM_INFLIGHT','SYS_C0029531 DIM_STANDARD_COSTS','PROCESS_EXECUTION_STATISTICS','STG_CARGO','STG_FUEL_INFO','STG_RCT_INFO','WEEKLY_CARGO','DIM_CURRENCY','DIM_GLOBAL_VAL','DIM_OTHER_INC','DIM_SERVICE_TYPE_AIMS','PROCESS_CHECKLIST','SNAPSHOT_WORKING','STG_BSP_REVENUE');

-- Enable back the triggers: [In case you disabled them before the import]
SQL> select 'alter trigger '||owner||'.'||trigger_name||' enable;' from dba_triggers where owner='PNL_OWNER' and STATUS='DISABLED' and table_name in ('ADV_STG_FUEL_INFO','DIM_FUEL_PRICE','ERROR_CODES','PNL_ETL_JOB_STATUS','BATCH_STATUS','DIM_INFLIGHT','SYS_C0029531 DIM_STANDARD_COSTS','PROCESS_EXECUTION_STATISTICS','STG_CARGO','STG_FUEL_INFO','STG_RCT_INFO','WEEKLY_CARGO','DIM_CURRENCY','DIM_GLOBAL_VAL','DIM_OTHER_INC','DIM_SERVICE_TYPE_AIMS','PROCESS_CHECKLIST','SNAPSHOT_WORKING','STG_BSP_REVENUE','WEEKLY_UNUTILIZED_BLOCK','STANDARD_BASE_AUDIT','DIM_PROV_NON_REPATRIABLE_FUNDS','STG_PAX_INFO','DIM_DOC_RATES','APEX_UPLOAD_STATUS','DAILY_UNUTLZD_BLOCK','DIM_ROUTES','WEEKLY_REVENUE','DATA_LOADING_INFO','DIM_CREW_LO','DIM_DATE','DIM_FLT_SCHEDULE','DIM_REV_CODES','EMAIL_LOG','FINAL_STANDARDS','STANDARD_BASE','DIM_HEDGED_QTY','FLT_SECTOR_PAIR','DIM_AIRPORTS','THRESHOLD_SETTINGS','AUDIT_BATCH_STATUS','PNL_ETL_EMAIL_CONFIG','MONTHLY_ACTUALS_FEEDER','STG__OAL_NO_SHOW_PAX','STG_MEAL_REV_CNT','STG_OAL_NO_SHOW_PAX','STG_RCT_DETAILS','PARAMRTAG','DIM_INFLIGHT_PARAM','STG_UNUTLZD_BLOCK');

Reset back the Flashback retention to its original setting:
SQL> alter system set db_flashback_retention_target=3000;

Enable back the disabled cron and backup jobs which you may disabled before the flashback:

Gather statistics on the imported tables:
EXEC DBMS_STATS.GATHER_TABLE_STATS('PL', 'F_PRICE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('PL', 'E_CODES');
EXEC DBMS_STATS.GATHER_TABLE_STATS('PL', 'ET_STATUS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('PL', 'BATCH_SO');
EXEC DBMS_STATS.GATHER_TABLE_STATS('PL', 'FL_INFO');

It's always wise to take a Full DB Backup after such activity 😀