Delete done on Primary using Flashback on a Standby

December 22, 2020

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.