Time for action – using flashback on a standby database

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.

  1. Enabling flashback: To perform recovery of an object, flashback must be enabled on the standby database. Ensure MRP is cancelled before enabling flashback.
    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
  2. Adjusting the flashback retention period on the standby database: In order to perform recovery of an object with flashback, the object's drop/truncate time must not be more than the value specified in 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
  3. Gathering table information before truncation: We can collect the following data from the primary database before truncating the table, in order to ensure that we'll recover the same number of rows after the flashback:
    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.

  4. Truncating the table and capturing the time: From the primary database, let's truncate the table:

    Tip

    This truncate operation is only for testing purposes. Please do not perform this on production databases.

    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.

  5. Verifying the data on a standby database: We're using the standby database with real-time apply and active Data Guard features. So the transactions will be quickly replicated with no delay.
    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.

  6. Performing a time-based flashback on a standby database: Now connect as 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')

    Tip

    If there is any difference in time zones, you can use the log miner to analyze the archived redo logfiles and see at exactly what time the table was truncated.

    In the previous command, we used flashback 10 minutes prior to when the drop and flashback operations were successful.

  7. Verifying the data after flashback on a standby database: Now open the database and check the number of rows that have been recovered.
    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.

  8. Exporting the table from a standby database: We should now export the table from the standby database. If we create a DB link in the primary database pointing to the standby database, we can use 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.
    1. Create a database link in the primary database to point to standby.
      SQL> create public database link exp_turkey connect to system identified by "free2go" using 'india';
Database link created.
    2. Export the 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
  9. Importing the table in a primary database: This process checks the status of the database and row count in the 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
  10. Verifying the table data after importing into a primary database: From the previous step, we successfully imported data into the primary database and the number of the rows is same as in step 3.
    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
  11. Starting MRP on a standby database to synchronize with a primary database: Start the recovery on a standby database to synchronize it with the primary database after importing.
    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

What just happened?

We have seen how to recover a huge truncated table from the primary database by using the flashback technique and the Export/Import procedures.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
13.59.209.131