Recover a Table from Drop/Truncate/Delete done on Primary using Flashback on a Standby
In this article, we will learn steps needed to perform recovery of User wrong operation like delete/drop/truncate action on the production table without using Restore and Recover command on PRIMARY DATABASE.
Recovering the table from the standby database will also eliminate any required downtime on the primary database.
For this, you should have an up and running Standby database with Flashback On feature.
Please note the time when Drop/Truncate/Delete operation happened should be within the db_flashback_retention_target and all the flashback and archive logs should be available. Below is step by step recover a table.
First, perform the below commands on PRIMARY DATABASE.
SQL> set lines 300
SQL> col name for a10
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE,FLASHBACK_ON,FORCE_LOGGING,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE FLASHBACK_ON FOR PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- ---------------- -------------------- ------------ ------------------ --- -------------------- -------------------- --------------------
PROD01 PRIMARY READ WRITE ARCHIVELOG NO YES MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE
Execute the below commands on STANDBY DATABASE.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string M:\FLASH_RECOVERY_AREA
db_recovery_file_dest_size big integer 80G
SQL> show parameter retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 7200
undo_retention integer 86400
SQL> set lines 300
SQL> col name for a10
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE,FLASHBACK_ON,FORCE_LOGGING,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE FLASHBACK_ON FOR PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- ---------------- -------------------- ------------ ------------------ --- -------------------- -------------------- --------------------
PROD01 PHYSICAL STANDBY READ ONLY WITH APPLY ARCHIVELOG YES YES MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
SQL> set lines 200
SQL> set pages 200
SQL> select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR';
INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------
1 RFS IDLE LGWR 1 806 343899 0 0
1 MRP0 APPLYING_LOG N/A 1 806 343898 33 33
Suppose in PRIMARY DATABASE developer by mistake truncate table TEST_RECOVERY on “21-dec-20 10:50:08” and approach DBA to recover the table, So you can follow the below steps on the STANDBY DATABASE to recover the truncated table. Please note while doing flashback on STANDBY DATABASE, we went back in time by 1 min ‘21-dec-20 10:49:00‘.
Execute the below commands on PRIMARY DATABASE where we truncate table TEST_RECOVERY.
SQL> select count(*) from test_recovery;
COUNT(*)
----------
100
SQL> truncate table test_recovery;
Table truncated.
SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD-MON-YYH
---------------------------
21-dec-20 10:50:08
Execute all commands in STANDBY DATABASE.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> create restore point flashback_point guarantee flashback database;
Restore point created.
SQL> col name for a50
SQL> set lines 300
SQL> select name, to_char(scn), time from v$restore_point;
NAME TO_CHAR(SCN) TIME
-------------------------------------------------- ---------------------------------------- ---------------------------------------------------------------------------
FLASHBACK_POINT 13610647843 21-DEC-20 10.53.02.000000000 AM
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2288096 bytes
Variable Size 1509951008 bytes
Database Buffers 2751463424 bytes
Redo Buffers 12079104 bytes
Database mounted.
SQL> flashback database to timestamp TO_TIMESTAMP( '2020-12-21 10:49:00','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> alter database activate standby database;
Database altered.
SQL> select controlfile_type from v$database;
CONTROL
-------
CURRENT
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select count(*) from test_recovery;
COUNT(*)
----------
100
Please use EXPDP utility to take a backup of table TEST_RECOVERY from STANDBY DATABASE and import in PRIMARY DATABASE, Once export-import is done follow the below steps to convert DB to Standby, all commands in STANDBY DATABASE.
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2288096 bytes
Variable Size 1509951008 bytes
Database Buffers 2751463424 bytes
Redo Buffers 12079104 bytes
Database mounted.
SQL> flashback database to restore point flashback_point;
Flashback complete.
SQL> alter database convert to physical standby;
Database altered.
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2288096 bytes
Variable Size 1509951008 bytes
Database Buffers 2751463424 bytes
Redo Buffers 12079104 bytes
Database mounted.
SQL> select controlfile_type from v$database;
CONTROL
-------
STANDBY
SQL> drop restore point flashback_point;
Restore point dropped.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> set lines 200
SQL> set pages 200
SQL> select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents from gv$managed_standby where PROCESS like 'MRP%' or client_process like 'LGWR';
INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------
1 RFS IDLE LGWR 1 809 530 0 0
1 MRP0 APPLYING_LOG N/A 1 809 529 33 33
SQL> /
INST_ID PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
---------- --------- ------------ -------- ---------- ---------- ---------- ------------- ------------
1 RFS IDLE LGWR 1 809 534 0 0
1 MRP0 APPLYING_LOG N/A 1 809 533 33 33
In this final step, we just validate the row count of a table in PRIMARY DATABASE.
SQL> select count(*) from test_recovery;
COUNT(*)
----------
100
SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD-MON-YYH
---------------------------
21-dec-20 11:38:17
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
