Now we are going to see how to recover a dropped/truncated table if a standby database exists, and using the flashback feature. We won't make any changes to the primary database and even the flashback feature may be off on the primary database.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> select db_unique_name,flashback_on from v$database;
DB_UNIQUE_NAME FLASHBACK_ON
--------------- ------------------
INDIA_UN YES
On the alert log, you will get the following:
Thu Dec 20 15:22:21 2012 RVWR started with pid=25, OS id=7900 Thu Dec 20 15:22:24 2012 Allocated 3981120 bytes in shared pool for flashback generation buffer Flashback Database Enabled at SCN 6082371 Completed: alter database flashback on
DB_FLASHBACK_RETENTION_TARGET
and all the flashback and archive logs should be available.SQL> show parameter db_flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- -------- db_flashback_retention_target integer 5760
SQL> select segment_name,sum(bytes/1024/1024) from dba_segments where segment_name='PACKT' group by segment_name; SEGMENT_NAME SUM(BYTES/1024/1024) --------------- -------------------- PACKT 7738 SQL> select count(*) from packt; COUNT(*) --------------- 88080384
The PACKT
table's size is around 7.7 GB with 88080384
rows.
SQL> truncate table packt; Table truncated. SQL> select count(*) from packt; COUNT(*) -------- 0 SQL> select sysdate from dual; SYSDATE --------- 20-DEC-2012 16:11:41
The table was truncated on 20-DEC-2012
, at 16:11:41
.
SQL> select db_unique_name,database_role from v$database; DB_UNIQUE_NAME DATABASE_ROLE --------------- ---------------- INDIA_UN PHYSICAL STANDBY SQL> select count(*) from packt; COUNT(*) ---------- 0
The number of rows for the table PACKT
on standby is also 0
, so the truncate
operations are applied on the standby database.
SYSDBA
, cancel recovery, shut down the standby database, and start in MOUNT
status:SQL> connect / as sysdba Connected. SQL> recover managed standby database cancel; Media recovery complete. SQL> shutdown immediate SQL> startup mount Database mounted.
From step 4, we have captured the time of the table's truncate
operation, and now will use that time to flash back the standby database:
SQL> flashback database to timestamp to_date('20-DEC-2012 16:10:00','DD-MON-YYYY HH24:MI:SS'),
Flashback complete.
On the alert log, you will get the following:
Thu Dec 20 16:26:04 2012 flashback database to timestamp to_date('20-DEC-2012 16:10:00','DD-MON-YYYY HH24:MI:SS') Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start Serial Media Recovery started Flashback Media Recovery Log /u02/app/oracle/flash_recovery_area/INDIA_UN/archivelog/2012_12_20/o1_mf_1_985_8f5vcxhj_.arc Incomplete Recovery applied until change 6090032 time 12/20/2012 16:10:01 Flashback Media Recovery Complete Completed: flashback database to timestamp to_date('20-DEC-2012 16:10:00','DD-MON-YYYY HH24:MI:SS')
In the previous command, we used flashback 10 minutes prior to when the drop and flashback operations were successful.
SQL> select db_unique_name,database_role,resetlogs_change# from v$database; DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE# --------------- ---------------- ----------------- INDIA_UN PHYSICAL STANDBY 945184 SQL> select count(*) from packt; COUNT(*) ---------- 88080384
We can now compare the actual rows before truncating with the number of rows after the flashback
operation. In steps 3 and 7, the number of rows are the same. So we've successfully recovered the data.
NETWORK_LINK
to export the table from standby. We have already discussed this option in Chapter 7, Active Data Guard, Snapshot Standby, and Advanced Techniques. You should perform the following steps from the primary database; it will export data from standby using NETWORK_LINK
.SQL> create public database link exp_turkey connect to system identified by "free2go" using 'india'; Database link created.
PACKT
table.[oracle@oracle-primary expdp]$expdp system/free2go directory=EXPDP_INDIA network_link=exp_turkey tables=oracle.packt dumpfile=Packt_table.dmp logfile=packt_table.log Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_02": system/******** directory=EXPDP_INDIA network_link=exp_turkey tables=oracle.packt dumpfile=Packt_table.dmp logfile=packt_table.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 7.556 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ORACLE"."PACKT" 3.386 GB 88080384 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded ********************************************************************* Dump file set for SYSTEM.SYS_EXPORT_TABLE_02 is: /u02/backups/expdp/Packt_table.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_02" successfully completed at 17:24:18
packt
table.SQL> select db_unique_name,database_role,resetlogs_change# from v$database; DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE# -------------------- ---------------- ----------------- turkey_un PRIMARY 945184 SQL> select count(*) from packt; COUNT(*) ---------- 0
We have the table metadata in the database, so we only need to perform import of data using the parameter CONTENT=DATA_ONLY
:
[oracle@oracle-primary expdp]$ impdp system/free2go directory=EXPDP_INDIA tables=scott.packt dumpfile=Packt_table.dmp logfile=packt_table_imp.log content=data_only
Import: Release 11.2.0.3.0 - Production on Thu Dec 20 17:31:06 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=EXPDP_INDIA tables=scott.packt dumpfile=Packt_table.dmp logfile=packt_table_imp.log content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."PACKT" 3.386 GB 88080384 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 17:50:43
SQL> select db_unique_name,database_role,resetlogs_change# from v$database; DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE# -------------------- ---------------- ----------------- turkey_un PRIMARY 945184 SQL> select count(*) from packt; COUNT(*) ---------- 88080384
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
On the alert log, you will get the following:
Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 1036 (in transit) Recovery of Online Redo Log: Thread 1 Group 11 Seq 1036 Reading mem 0