FLASHBACK DATABASE AND QUERIES

FLASHBACK DATABASE AND QUERIES

FLASHBACK FEATURES

 

Purging the Recycle Bin

 

purge table sales.woodscrews;

purge index sales.woodscrews_pk_idx;

purge tablespace sales;

purge recyclebin;

 

Undropping Objects in the Recycle Bin

 

flashback table ws_app.woodscrews to before drop;

select object_name, original_name, droptime, dropscn from user_recyclebin;

flashback table bonus to before drop rename to bonus_deb;

 

———–Accidentaly Delete——————————————

 

Accidentally dropped table MHC_PA_INT_PPA_SPN_RL_TBL from MHCPA schema

**********************************************************************

How to recover:

1)Login to schema from where you dropped:

2)SELECT * FROM recyclebin where ORIGINAL_NAME =’MHC_PA_INT_PPA_SPN_RL_TBL’;

3)If you get multiple dates check the drop date.

4)FLASHBACK TABLE “BIN$pLplsuQ1FXDgRAAhKFcB9g==$0” TO BEFORE DROP;

5)FLASHBACK TABLE XXX TO BEFORE DROP RENAME TO OLD_XXX;

 

Display recyclebin

show recyclebin;

 

Performing the Flashback Table from SQL

flashback table sales.woodscrew to timestamp
to_timestamp(‘2003-12-01 13:30:00′,’YYYY-MM-DD HH24:MI:SS’);
flashback table sales.woodscrew to scn 751652;

 

To recover a table to a previous timestamp

******************************************

 

The following query retrieves the previous state of a table:

SELECT * FROM AR_RECEIPT_METHODS AS OF TIMESTAMP TO_TIMESTAMP(‘2011-09-09 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);

SQL> alter session set nls_date_format=’YYYY/MM/DD HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

——————-

2011/08/30 00:43:01

SQL> flashback table MHCAP.MHC_INT_MASTRO_EBS_TBL to timestamp TO_TIMESTAMP(‘2011/08/30 00:20:01′,’YYYY/MM/DD HH24:MI:SS’);

Flashback complete.

select versions_startscn,versions_oper,* from t1 versions between scn 1239229 and 1239236;

select dbms_flashback.get_system_change_number from dual;

 

Flashback Retention Target

alter system set db_flashback_retention_target=720;

Configure flash back database

 

1.Database must be in mount

select status from v$instance;

archive log list;

2.Set the flashback retention target to your desired value. We will use 12 hours as the window.

alter system set db_flashback_retention_target=720

SCOPE=BOTH SID=’*’;

 

3.Set the values for db_recovery_file_dest and db_recovery_file_dest_size (flash recovery area parameters).

 

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2335825920 SCOPE=BOTH SID=’*’;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/u02/fra/’ SCOPE=BOTH SID=’*’;

4.Turn flashback logging on

alter database flashback on;

5.Turn flashback logging off for any tablespaces that you deem do not require it.

alter tablespace sales_idx flashback off;

Step 6. Open the database.

alter database open;

Flashback Database: Tuning

select estimated_flashback_size from v$flashback _database_log;

select oldest_flashback_scn, oldest_flashback_time

from v$flashback_database_log;

select * from v$flashback_database_stat;

Flashback example

1. First, get the current SCN from the database. Because we are simply testing, we can prepare for the test by getting the current SCN prior to putting the fault into the database.

SQL> select current_scn from v$database;

CURRENT_SCN

—————-

885524

2. Introduce the fault.

SQL> truncate table woodscrew;

Table truncated.

3. Shut down the database, and then remount. The database must be mounted and not open for flashback.

SQL> connect / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

4. Issue the FLASHBACK command.

SQL> flashback database to scn 885524;

Flashback complete.

5. Open the database read-only to confirm that the table has been flashed back to the appropriate SCN.

SQL> alter database open read only;

Database altered.

SQL> connect sales/sales;

Connected.

SQL> select count(*) from woodscrew;

COUNT(*)

———-

12

6. Open the database with resetlogs.

SQL> connect / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

 

####### Recover database flashback technology #########################

 

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

 

SELECT CURRENT_SCN FROM V$DATABASE;

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

 

FLASHBACK DATABASE TO SCN 46963;

FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

FLASHBACK DATABASE TO TIME “TO_DATE(’09/20/05′,’MM/DD/YY’)”;

ALTER DATABASE OPEN READ ONLY;

 

SHUTDOWN IMMEDIATE

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

 

SET UNTIL TIME ‘Nov 15 2004 09:00:00’;

SET UNTIL SEQUENCE 9923;

SET UNTIL RESTORE POINT before_update;

RESTORE DATABASE;

RECOVER DATABASE;

ALTER DATABASE OPEN RESETLOGS;