Important Points - Flashback Table

• You can’t flashback the table to an SCN prior to SCN XXXX ,when a specific type of DDL occurred at XXXX. These DDL operations are as follows,
  
  1. Adding constraint to a table
  2. Dropping a column
  3. Modifying a column
  4. Truncating a table
  5. Moving table to a different Tablespace
  6. Adding, dropping, merging, splitting, coalescing, truncating a partition or subpartition. Adding a range partition is acceptable.
  7. Adding table to a cluster.
  
• When you flash back the table to a SCN prior to XXXX(when the index dropped), the index is not reinstated. Dropped objects are not recreated.
• When you flash back a table, the statistics on the table are not reinstated. When you flashback the table to SCN 1500 the statistics on the table are as of current SCN.

Thanks

Timestamp and SCN Conversion

You can convert timestamp into SCN and vice versa.

SQL> Select current_scn, systimestamp, timestamp_to_scn(systimestamp), scn_to_timestamp(current_scn) from v$database;

Thanks

Flashback Table - Privileges

Flashback Any Table - System Privilege.
Flashback privilege on that particular table.

Q) Can you flashback a table owned by another user.

A) Yes. But to do so you need select, insert, delete and alter privilege on the table as well as one of the above mentioned privilege.

Q) Can you flashback all types of tables

A) No. The flashback will not work for the following types of tables,

Advanced Queuing (AQ) tables
Individual table partitions or subpartitions
Materialized Views
Nested tables
Object tables
Remote tables
Static data dictionary tables
System tables
Tables that are part of a cluster

Thanks

Flashback Table - Procedure

Step 1: Make sure that the table has row movement enabled.

SQL> Select row_movement From user_tables where table_name=’EMP’;

Step 2: Enable Row movement if it is disabled. If enabled ignore this step.

SQL> Alter Table emp Enable Row Movement;

Step 3: Check how far back you can flash back.

SQL> Select Oldest_flashback_SCN, to_char(oldest_flashback_time, ‘MM/DD/YY HH24:MI:SS’) from v$flashback_database_log;

Step 4: Flashback the table to a specific time stamp.

SQL> Flashback table emp to timestamp to_date (’28-Mar-2009 16:25:00’, ‘dd-Mon-yyyy hh24:mi:ss’);

Note: You can flashback to a specific SCN also.

Note: If the flashback was not enough you can flash back once more. For eg to 16:23:00. Also if you have gone too far then you can go forward using the same flashback statement to 16:24:00. You can flash the table back and forth until you arrive at the exact point.

Thanks

Flashback Query

To find all the changes to the table during a certain period, issue the following query.

SQL> SELECT empno, ename, versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN SCN 3816659800 AND 3816659980
/*where empno = 7900*/

Using the above query with where clause enables you to view the history of a table row over a period of time.

When the versions_endtime is null it means that the rows has not been changed yet.

In addition to SCN you can also use the timestamp as shown below.

SQL> SELECT empno, ename, sal, versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN TIMESTAMP to_date('26/03/2009 09:45:00', 'dd/mm/yyyy hh24:mi:ss') and to_date('26/03/2009 09:52:00', 'dd/mm/yyyy hh24:mi:ss')
/*where empno = 7900*/

To view status of table at a particular time.

SQL> SELECT * FROM emp
AS OF TIMESTAMP to_date('26/03/2009 09:30:00', 'dd/mm/yyyy hh24:mi:ss');

Thanks

Recycle bin - Space Calculation

To determine the space occupied by the objects in recycle bin.

SQL> select sum(space*8)/1024 space_in_MB from dba_recyclebin;

Where space is the number of blocks and 8 is the block size in kb.

Note: The free space calculation (dba_free_space) excludes the recyclebin objects, so the free space reported is accurate.

Thanks

Flashback Drop - Scenarios

1) Suppose there are two tables in the recycle bin with the same name – Emp. Which table will be reinstated when you use the flashback drop command?

The table that was dropped last (latest Drop time) will be reinstated. To reinstate the previously dropped table, use the recycle bin’s system generated name method.


2) When you undrop a table, what happens to the dependent objects like the constraints, indexes etc.

When you undrop a table, the dependent objects are not undropped. But those objects do exist, and you can rename them to their original names. The only exceptions are foreign key constraints, which are lost when a table is dropped.

Use the following statements to rename the various dependent objects

Alter table table_name rename constraint “recyclebin name” to constraint_name;
Alter index “recyclebin name” rename to index_name;
Alter trigger “recyclebin name” rename to trigger_name;

Thanks

Remove Flashback drop Feature

To disable the Flashback Drop Feature set the init parameter RECYCLEBIN to OFF.

To remove all objects from the recycle bin that are part of a Tablespace,

SQL> Purge Tablespace users;

To remove all objects of a single user, scott (along with any dependent objects that live in other Tablespaces) from the Tablespace users,

SQL> Purge Tablespace users USER scott;

To query the table in recyclebin

SQL> Select * from owner.”recyclebin_name”;

SQL> Select * from ins."BIN$YkyfmFKl8MLgQwppGAzwwg==$0";

Note 1: The recycle bin name must be enclosed within double quotes.

Note 2: You can only query the objects in Recycle bin, no DML is allowed.

Oracle renames all objects in the recycle bin, including any dependent objects such as indexes, constraints, triggers etc. When you recover a table, oracle will recover the dependent objects as well, but they’ll retain these cryptic system generated names, so you will need to rename them appropriately.

Thanks

Flashback Drop - Procedure

In Oracle 10g, when you drop a table, the table is not really dropped. Rather, the table is renamed. The data in the table is still available. You can use the Flashback Drop technologies to recover an accidentally dropped table. The procedure is as follows,


Step 1: Check whether the dropped table is still available in the recycle bin, command.

User level

Scott@prod> show recyclebin;

Database level

dba@prod> Select * from dba_recyclebin;

The original_name column shows the dropped table’s original name. It also has the recycle bin’s name and the dropped time.

Step 2: Use the Flashback Drop command to recover the table

Scott@prod> Flashback Table Employees to before drop [Rename to New_Employees];

Or you can use the system generated name as follows

Scott@prod> Flashback Table “BIN$YkyfmFKl8MLgQwppGAzwwg==$0" to before drop [Rename to New_Employees];

Step 3: To permanently drop the table.

Scott@prod> Drop Table Employees purge;

Scott@prod> purge dba_recyclebin;

Flashback Database - Limitations

Limitations of Flashback Database Feature

1. Database must be running in archivelog mode.

2. There should not be any media failure.

3. If a control file has been restored or recreated during the time span you want to flashback over, then you can’t use the flashback feature.

4. You can’t flash back a database to before a reset logs operation.

5. You can’t flash back a data file that was shrunk or dropped during the time span covered by the flashback table operation.

Thanks

Flashback Database - Procedure

Step 1: Check how far back you can flash back.

SQL> Select Oldest_flashback_SCN, to_char(oldest_flashback_time, ‘MM/DD/YY HH24:MI:SS’) from v$flashback_database_log;

Step 2: Connect to RMAN

$ rman target /

Step 3: Shutdown the database

RMAN> shutdown immediate;

Step 4: Start the database in mount mode

RMAN> Startup Mount;

Step 5: Flashing back to a specific SCN

RMAN> Flashback database to SCN 10956234;

Step 6: Open the database in Read-Only mode to check whether you have flashed back to the exact point in time.

RMAN> Alter database open read only;

If the database is not flashed back to the desired SCN repeat the steps with the new SCN from steps 3 to 6.

Step 7: Open the database

RMAN> shutdown immediate;
RMAN> Startup Mount;
RMAN> Alter database open resetlogs;

To Flashback to a specific time instead of SCN, change the step 5.

Step 5: Flashing back to a specific time.

RMAN> Flashback database to time ‘sysdate-2/60/24’;
Or
RMAN> Flashback database to time “to_date(‘01/23/09 13:00:00’, ‘mm/dd/yy hh24:mi:ss’)”;

To Flashback to a restore point, change the step 5.

Step 5: Flashing back to a restore point.

RMAN> Flashback database to restore point rp1;

To Flashback before the last resetlogs operation, change the step 5.

Step 5: Flashing back to before the last reset logs.

RMAN> Flashback database to before resetlogs;

Note: Database records the SCN when the database was opened with resetlogs. So flashback to the last resetlogs is merely same as issuing the flashback to that SCN.

Thanks

Flashback Query - Scenario

I have a table EMP with 8 rows at 1 pm. I insert 8 more rows at 1.30 pm and committed. What will happen in the following scenarios.

1. Flashback to 1.15 pm.

8 Rows will be displayed.

2. Drop the table at 2 pm. Flashback to 1.15 pm.

Table or View does not exist

3. Create a new table with the same name EMP and insert 4 rows at 2.30 pm. Flashback to 1.15 pm.

ORA - 08176 consistent read failure, rollback does not available.

Summary

Flashback Query uses the Undo tablespace for its operation. It will use the Object ID of the objects to get the details at the specified time. If the object is dropped then the Object ID does not exist in the data dictionary, hence it cannot flashback. You have to use the Flashback Drop Feature if you want to recover from the dropped table.

Thanks

Restore Points

Normal Restore Points

To create a normal restore point

SQL> Create Restore Point test;

To drop a normal restore point

SQL> Drop Restore Point test;

Guaranteed Restore Points

To create a Guaranteed restore point

SQL> Create Restore Point test_guarantee Guarantee Flashback Database;

To Drop a Guaranteed Restore Point,

SQL> Drop Restore Point test_guarantee Guarantee Flashback Database;

To recover using restore point,

RMAN> Flashback Database to Restore Point test_guarantee;

To view the Restore Points,

SQL> Select name, scn, storage_size, time, guarantee_flashback_database
From v$restore_point;

Thanks

Flashback Estimation Queries

1. The following query will give you some important details as mentioned below,

SQL> Select * from v$flashback_database_log;

The oldest_flashback_scn and the oldest_flashback_time columns indicate how far you can flash back your database.

The flashback_size and estimated_flashback_size columns indicate the space usage by the flashback logs.

The retention_target column lets you know the retention period for the flashback logs.

2. To estimate how much space the flashback logs consume at various point of time (hour by hour progress)

SQL> Select * from V$flashback_database_stat order by begin_time;

Thanks

Recovery Writer (RVWR)

Recovery Writer (RVWR)

Enabling Flashback Database Feature starts the Recovery Writer (RVWR) Background process. Whenever an oracle block undergoes a change, the Recovery Writer (RVWR) background process writes the flashback data to the Flashback Logs. The flashback data contains database data and the redo data.

Thanks

Configuring Flashback Database

Step 1: Check whether the database is in archive log mode.
Step 2: Enable the FRA.
Step 3: Set the init parameter. It specifies how far back you can flashback your database.

SQL> Alter System set DB_FLASHBACK_RETENTION_TARGET=1440 Scope=both;
Step 4: Shutdown and restart the database in Mount mode.

SQL> Shutdown Immediate;
SQL> Startup Mount;

Step 5: Enable the Flashback Database Feature

SQL> Alter Database Flashback ON;

Step 6: Open the database and confirm the status.

SQL> Alter Database open;
SQL> Select flashback_on from v$database;

Step 7: Disable the Flashback Database Feature.

SQL> Shutdown Immediate;
SQL> Startup Mount;
SQL> Alter database flashback off;

Step 8: Disable the Flashback Database for certain Tablespaces.

SQL> Alter Tablespace users FLASHBACK OFF;

Step 9: Enable the Flashback Database for the Tablespace.

SQL> Alter Tablespace users FLASHBACK ON;

Thanks