Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, I will talk about how to recover accidentally drop, truncate or deleted table in Production database by using flashback in standby database.
First of all, we enable the flashback mode of the standby database.
1 2 3 4 5 6 7 | SQL> alter database flashback on; SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES |
We stop the Apply process.
1 | SQL > recover managed standby database cancel; |
The restore point is created.
1 | SQL> create restore point before_flashback guarantee flashback database; |
Sending archives from the primary database to standby is stopped.
1 | SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; |
Flashback returns the table before it was dropped, truncated or deleted.
1 2 3 4 | SQL > shutdown immediate; SQL > startup mount; SQL > flashback database to timestamp to_date('01-NOV-2022 12:05:00','DD-MON-YYYY HH24:MI:SS'); |
We open the standby database in read only mode.
1 | SQL> alter database open read only; |
After the database is opened in read-only mode, the table can be transferred to the Production environment with two methods.
1.Export — Import
We export the table.
1 2 3 | $ expdp melek/<password>@orcl_stby directory=DUMP_DIR tables=HR.EMPLOYEES dumpfile=exp_Employees.dmp logfile=exp_Employees.log |
After copying the Dump file to the Production environment, we import the table.
1 2 | $ impdp melek/<password>@orcl directory=DUMP_DIR dumpfile=exp_Employees.dmp logfile=imp_Employees.log |
2. DBLink
By connecting the Production database to the Standby database with DBLink, we recover the table related to the CTAS method.
1 | SQL> CREATE TABLE HR.EMPLOYEES AS SELECT * FROM HR.EMPLOYEES@PROD_TO_STBY; |
After the table is recovered, the Standby database is closed, opened in mount mode, and the restored point created is returned.
1 2 3 | SQL > shutdown immediate SQL > startup mount SQL > FLASHBACK DATABASE TO RESTORE POINT before_flashback; |
We start the process of sending archives from the Production database to the Standby database.
1 | SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; |
We close the flashback, start the apply process in the Standby database and drop the restore point created.
1 2 3 | SQL> alter database flashback off; SQL> recover managed standby database disconnect from session; SQL> DROP RESTORE POINT before_flashback; |
