Advantage/Difference of Image Copy over Backup Set

•RMAN supports binary compression of backup sets, where the backup set contents are compressed before being written to disk using a compression algorithm tuned for compression of datafiles and archived log files. But with Image copy no compression is done.

•A backup set never contains empty blocks. As RMAN passes through the datafiles, it simply skips blocks that have never been used. But image copy contains empty blocks. This means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.

•Incremental backups can't be performed with Image copy but incremental backups can be taken over backup sets.

•RMAN can take backup of backup sets directly to tape, if you have installed the RMAN drivers for your tape library.But RMAN can't take backup of image copies directly to tape.

•Both Image copy and backup sets can be restored from RMAN. From Operating System Backup sets can't be generated or restored.

•RMAN will check the contents for corruption for both Image copy and backupsets but native operating-system file copy utilities cannot check corruption while taking backup of Image copy.

Flashback Table in Oracle 10g

Description:Flashback Table can be used to recover one or more tables to a specific point in time. It rolles back only the changes made to the table or tables and their dependent objects, such as indexes. Flasback Table undoes recent transactions to an existing table, whereas Flashback Drop recovers a dropped table

Based on: UNDO-Tablespace

Privilege to use:  FLASHBACK ANY TABLE - System privilege and the appropriate object privilege
(Select, Insert, Update, Delete and ALTER)

Usage Syntax:


 

FLASHBACK TABLE table_1 [, table_x]
       TO {SCN | TIMESTAMP [expr] | RESTORE POINT restore_point}
       [{ENABLE | DISABLE} TRIGGERS];

Considerations:

• To be able to flashback a table, you must enable row movement for the table, also ROWIDs will be changed in the Table.
• Integrity constraints are not violated when one or more tables are flashed back.
• It can not be used if the flasback operation crosses a table structure change or a table shrink operation.
• Applying the UNDO_RETENTION initialization parameter to RETENTION GUARANTEE can cause the tablespace running out of space and database hangs.
• UNDO_RETENTION must be greater than the time-Intervall used to recover the table (Recovering a Table can be done if there are undo-records retained in the UNDO Tablespace.
• Flasback Table causes an Exclusive DML-Lock on the tables during recovery.
• By default, the database disables triggers on the affected table before performing a FLASHBACK TABLE operation, and after the operation returns them to the state they were in before the operation (enabled or disabled).
• Turning on the Triggers on the table during FLASHBACK TABLE can be made with the parameter 'ENABLE TRIGGERS'.
• If you either flasback too far or not far enough, you can simply rerun the FLASHBACK TABLE command with a different time stamp or SCN, as long as the undo data is still available.
• All the tables must be flashed back successfully or the entire transaction is rolled back.
• All flashback table operations must be at the beginning of any transaction.
• Flashback table operations are not supported for the SYS user.
• Current indexes and dependent objects, such as views, will be maintained.
• The Flasback Table operation will be written to the Alert.log.
• Constraints of the table will be checked. If the Operation violates any constraints, that it will be aborted.
• It does NOT cause the statistics of the table to be flashed back.

	Using Flashback Table
	ALTER SYSTEM SET UNDO_RETENTION = 2400; or ALTER TABLESPACE undotbs1 RETENTION GUARANTEE
	DELETE FROM tab_1 WHERE id = 5; COMMIT;
	ALTER TABLE tab_1 ENABLE ROW MOVEMENT;
	FLASHBACK TABLE tab_1 TO TIMESTAMP systimestamp – interval '15' minute [ENABLE TRIGGERS]; FLASHBACK TABLE tab_1 TO SCN 65444554 [ENABLE TRIGGERS];

Source: http://www.oraone.com/techblog/index.php?menu=1&begin=1&cikk=336&form=0

Unusable indexes

Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.
Queries and other operations agains a table with unuable indexes will generate errors:

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

Detecting

The following SQL comands can be used to detect unuable indexes:
Indexes:

SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';

Fixing

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:
Indexes:

SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';'
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'; 

Source: http://www.orafaq.com/wiki/Unusable_indexes 

Local Index Versus Global Index on Partition Table

Most of the interviewer ask these question on Partition index...

1. What is local index?
2. What is global index?
3. When would you force to create Global index on Partition table?
4. When would you recommend to create global index versus local index?

To answer these question....

1. What is local index?
Local Partitioned indexes are easier to manage and each partition of local indexes are associated with that partition. They also offer greater availability and are common in DSS environments. When we take any action(MERGE, SPLIT,EXCHANGE etc) on local partition, it impacts only that partition and other partition will be available. We can not explicity add local index to new partition. Local index will be added implicitly to new partition when we add new partition on table. Likewise, we can not drop the local index on specific partition. It can be dropped automatically when we drop the partition from underlying table. Local indexes can be unique when partition key is part of the composit index. Unique local indexes are useful for OLTP environment. We can can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.
SQL> CREATE TABLE employees
2 (employee_id NUMBER(4) NOT NULL,
3 last_name VARCHAR2(10),
4 department_id NUMBER(2))
5 PARTITION BY RANGE (department_id)
6 (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE ODS_STAGE_DATA,
7 PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE ODS_STAGE_DATA,
8 PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE ODS_STAGE_DATA);

Table created.

SQL> declare
2 v_no number :=1;
3 begin
4 delete employees;
5 for i in 1..10 loop
6 insert into employees values(v_no,'name...',v_no);
7 v_no := v_no+1;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> create index idx_local on employees(last_name) local;

Index created.

SQL>
2. What is global index?
Global index used in OLTP environments and offer efficient access to any individual record. We have two types of Global index. They are global Non-partitioned index and Global partitioned index. Global nonpartitioned indexes behave just like a nonpartitioned index.
Global partitioned index partition key is independent of Table partition key. The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. If you want to add new partition, always, you need to split the MAX partition. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.
Example of Global Non-partitioned index.
SQL> CREATE INDEX employees_global_idx ON employees(employee_id);

Index created.

SQL>
Example of Global Partitioned index .
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
2 GLOBAL PARTITION BY RANGE(employee_id)
3 (PARTITION p1 VALUES LESS THAN(3),
4 PARTITION p2 VALUES LESS THAN(6),
5 PARTITION p3 VALUES LESS THAN(9));
PARTITION p3 VALUES LESS THAN(9))
*
ERROR at line 5:
ORA-14021: MAXVALUE must be specified for all columns

SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
2 GLOBAL PARTITION BY RANGE(employee_id)
3 (PARTITION p1 VALUES LESS THAN(3),
4 PARTITION p2 VALUES LESS THAN(6),
5 PARTITION p3 VALUES LESS THAN(11),
6 PARTITION p4 VALUES LESS THAN(20),
7 PARTITION p5 VALUES LESS THAN(MAXVALUE));

Index created.
Now the partition p4 is empty. Let us drop the empty partition and see the status.
SQL> select count(*) from employees where
2 employee_id between 12 and 20;

COUNT(*)
----------
0
SQL> ALTER index employees_global_part_idx drop partition p4;

Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P3 USABLE
P5 USABLE
Now we will drop the partition P3 and see status. When we drop this partition, it should invalidate the next highest partition. Here, P5 is next highest partition.
SQL> alter index employees_global_part_idx drop partition p3;

Index altered.

SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 UNUSABLE
SQL> alter index employees_global_part_idx rebuild;
alter index employees_global_part_idx rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> alter index employees_global_part_idx rebuild partition p5;

Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 USABLE
Partition index can be maintained by using UPDATE GLOBAL INDEXES. Index will be available during the maintenance and it is available online. We do not need to rebuilt the index after the index maintenance.
For example,
SQL> alter table employees drop partition employees_part3
2 update global indexes;

Table altered.

SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';

PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 USABLE

SQL>
3. When would you force to create Global index on Partition table?

When you create a Primary key, you will be forced to create Global index. When you create unique index, you are forced to create global index. Enforcing unqiueness is most common reason for global indexes. If you try to create local index on unique key, you would get the below error.

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

So, we can create local index on unique key when we add the partition key as part of composit key in the index. Let me demonstrate this.

The below example, unique index failed since, partition key is not part of index composit key.

SQL> create unique index idx_emp_id on employees(employee_id) local;
create unique index idx_emp_id on employees(employee_id) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

The below example, it allowed to create global index.
SQL> create unique index idx_emp_id on employees(employee_id);

Index created.

SQL> drop index idx_emp_id;

Index dropped.

The below case, unique index is successfully created, since partition key department_id is part of composit keys.
SQL> create unique index idx_emp_id on employees(employee_id,department_id) local;

Index created.

SQL>

4. When would you recommend to create global index versus local index?
We can use Global index if Query that return a SMALL number of rows from a potentially LARGE number of partitions.

Source: http://myorastuff.blogspot.com/2008/08/local-index-versus-global-index-on.html

Finding a locking session

How to identify lockers
This article will explain about locks on rows and on objects in ORACLE.
Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).
When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.
Scenario 1:
Terminal A is locking a row and Terminal B is waiting on it:

–TERMINAL A

SQL> update map1 set col2='MYLOCK' where col1=300;   1 row updated.   SQL> (..no commit here..)

–TERMINAL B

SQL> update map1 set col2='NEWVAL2' where col1=300; (..waiting..)

Now, lets create a session as a DBA User to monitor the system, this query will tell the locking and waiting SIDs.

SELECT vh.sid locking_sid,  vs.status status,  vs.program program_holding,  vw.sid waiter_sid,  vsw.program program_waiting FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid;

LOCKING_SID STATUS   PROGRAM_HOLDING                WAITER_SID PROGRAM_WAITING
----------- -------- ------------------------------ ---------- ------------------------------
 144        ACTIVE   sqlplus@rh4_node1.fadeserver.n        131 sqlplus@rh4_node1.fadeserver.n
                     et (TNS V1-V3)                            et (TNS V1-V3)

Here is an expanded version of the same query, it also includes jobs information.

SELECT vs.username,  vs.osuser,  vh.sid locking_sid,  vs.status status,  vs.module module,  vs.program program_holding,  jrh.job_name,  vsw.username,  vsw.osuser,  vw.sid waiter_sid,  vsw.program program_waiting,  jrw.job_name,  'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command" FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,  dba_scheduler_running_jobs jrh,  dba_scheduler_running_jobs jrw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid  AND vh.sid = jrh.session_id(+)  AND vw.sid = jrw.session_id(+);

USERNAME OSUSER  LOCKING_SID STATUS   MODULE  PROGRAM_HO JOB_N USERNAME OSUSER  WAITER_SID PROGRAM_WA JOB_N Kill_
-------- ------- ----------- -------- ------- ---------- ----- -------- ------- ---------- ---------- ----- -----
CACOSTA  oracle          144 ACTIVE   SQL*Plu sqlplus@rh       CACOSTA  oracle         131 sqlplus@rh       alter
                                      s       4_node1.fa                                   4_node1.fa        syst
                                              deserver.n                                   deserver.n       em ki
                                              et (TNS V1                                   et (TNS V1       ll se
                                              -V3)                                         -V3)             ssion
                                                                                                            '144
                                                                                                            ,3897
                                                                                                            3';

We can see that the user CACOSTA, sid 144 is locking the session 131.
Scenario 2:
We are performing a DDL (alter somehow the object) and we get an ORA-00054 error.
I have canceled the waiting session in the example above and now I’m creating an index on the table:

SQL> create index ind2 on map1(col2); create index ind2 on map1(col2)  * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified

If I re-run the query fromt he previous scenario it won’t return any rows, because there are no waiting sessions (I canceled the waiting update).
First we need to find out the object ID:

SQL> select object_id from dba_objects    where owner='CACOSTA'    and object_name='MAP1';    OBJECT_ID ----------  52255

Now lets see who is blocking the object 52255

SELECT c.owner,  c.object_name,  c.object_type,  b.sid,  b.serial#,  b.status,  b.osuser,  b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id and a.object_id=52255;

OWNER    OBJECT_NAME   OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER  MACHINE
-------- ------------- ------------------- ---------- ---------- -------- ------- ---------------
CACOSTA  MAP1          TABLE                      144      38973 ACTIVE   oracle  rh4_node1.fades 

Source:  http://oracleexamples.wordpress.com/2010/04/08/find-a-locking-session/

Drop Oracle database

Here is how you wack a database in less than no time!

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.


Total System Global Area 146472960 bytes

Fixed Size 1311940 bytes

Variable Size 92277564 bytes

Database Buffers 50331648 bytes

Redo Buffers 2551808 bytes

Database mounted.

SQL> drop database;

drop database

*

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode



SQL> alter system enable restricted session;


System altered.


SQL> drop database;


Database dropped.


Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Overview of Oracle Flashback features

This is an overview of some of the flashback features of Oracle 11g: Flashback Query, Flashback Database, Flashback Drop and Flashback Table. These features are all similar in that they allow you to access data as it existed in the past, yet they are very different in their purpose and execution.
Flashback Query - this allows you to query a table as it was at a given time in the past. This can be helpful in various troubleshooting scenarios and also used to recover one or more rows rows to a past point in time. In order for this to work, the data must still be in the UNDO tablespace so you need to size it accordingly and have your undo_retention set appropriately.  Otherwise, you will receive “ORA-01555: snapshot too old”
Use the “AS OF TIMESTAMP” syntax to execute a Flashback Query

SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2012-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE fname=’MIKE’ and lname=’DEAN’;

In case you need to restore the record, you combine the above with an insert as follows:

INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2012-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE fname=’MIKE’ and lname=’DEAN’);

Flashback Database - Flashback Database allows you to restore your database to a prior point in time by undoing all the changes that have taken place since that time. This is different than a Point-In-Time Recovery in that you don’t restore a backup and then roll forward, instead you “rewind” the database by applying flashback logs. This operation can be very fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Keep in mind that this is NOT a replacement for regular backups (preferably rman hot backups) as it DOES NOT protect you in the case of media failure, filesystem corruption, accidental deletion or anything else that effects the physical data files. It is used to undo logical data corruption, such as from a failed application upgrade. It can also be very useful in Test databases where you want to test a process repeatedly, starting with an identical data set each time.  There is overhead associated with generating and managing the flashback logs but for many databases, this is well worth it.
In order to enable Flashback Database, put the database is mount mode and run “alter database flashback on”. The flashback logs will be written to your FRA as defined by the db_recovery_file_dest parameter. The db_flashback_retention_target parameter defines how long you wish to save your flashback logs.
Here is an example of enabling Flashback Database, creating a restore point and then flashing back to that restore point:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.

SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> create restore point flashback_test;
Restore point created.
SQL> select name,time from v$restore_point;
NAME                 TIME
-------------------- ---------------------------------------------------------------------------
FLASHBACK_TEST       17-APR-12 09.17.46.000000000 AM

SQL> create table flashback_test as select * from all_objects;
Table created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point flashback_test;
Flashback complete.

NOTE: You can also flashback to a given time or SCN as well as to a restore point

SQL> alter database open resetlogs;
Database altered.
SQL> select * from flashback_test;
select * from flashback_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

The table I created after the restore point is no longer there; the database was “rewound” to a prior point in time.  Related to this is the guaranteed restore point, which can be created even if Flashback Database has not been enabled.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> create restore point restore_point_test guarantee flashback database;
Restore point created.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> shutdown immediate

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point restore_point_test;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> drop restore point restore_point_test;

Once you are confident that you won’t be rolling back, make sure to drop the restore point or you will continue to generate flashback logs.

Flashback Drop - allows you to recover a non-SYS table that was dropped by making use of the Recycle Bin.  This is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table.  You can bypass the Recycle Bin by dropping the table with the “purge” option.

SQL> drop table drop_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DROP_TEST        BIN$veGmdD1Yy9/gQKjAYIkPIg==$0 TABLE        2012-04-17:10:21:10
SQL> flashback table drop_test to before drop;
Flashback complete.

SQL> drop table drop_test purge;
Table dropped.
SQL> show recyclebin;
SQL> flashback table drop_test to before drop;
flashback table drop_test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Flashback Table - This can be used to restore data in a table to past point in time, based upon time or SCN.  This is similar to the Flashback Query in that it relies on the data being present in the UNDO tablespace.

SQL> create table flashback_test (col1 number);
Table created.
SQL> alter table flashback_test enable row movement;
Table altered.
--insert a bunch of rows
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1048161
SQL> select count(*) from flashback_test;
  COUNT(*)
----------
       192

SQL> delete from flashback_test;
192 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table flashback_test to scn 1048161;
Flashback complete.
SQL> select count(*) from flashback_test;
  COUNT(*)
----------
       192

Source: http://www.dbspecialists.com/blog/database-recovery/overview-of-oracle-flashback-features/ 

FLASHBACK TECHNOLOGY IN LIEU OF COMPLEX DATABASE RECOVERY

Oracle11g Flashback
Executive Summary
Oracle Flashback technology is best suited to attain readiness and preparedness in scenarios such as human errors, and point-in-time database state restore without the need for conventional point-in-time recovery. Most importantly, it can be used to discriminate among backup and recovery planned strategies, including real-time mirroring through which a full database snapshot can actually be restored at any time, with nearly no downtime. Similarly, there is the convenience that this is an Oracle Enterprise Edition provided feature rather than an expensive and more comprehensive mirroring technology with both full snapshot and thing provisioning capabilities, in general, mor customizable and granular than those technologies. Besides, the fact that Oracle Flashback relies significantly on Automatic Undo Management (AUM), as discussed, implies the understanding of that concept as balance between the space provided in UNDO tablespace and the time provided through the UNDO_RETENTION setting in comparison to the actual need of undo resources determined by the effect of transactional workload and relevant duration involved, which is quite different from the traditional MANUAL undo management model which uses a round-robin algorithm instead.

Introduction

Oracle Flashback Technology is a set of features (resources and capabilities) that allow DBAs to reach past states of database objects, including bringing the database objects back to a previous state, without using point-in-time media recovery. Flashback recovery is quite cost effective and convenient in comparison to conventional backup and recovery procedures and operations.

With flashback features, it is possible:

• Perform queries that return metadata exhibiting a detailed history of changes to the database
  
• Perform queries that return past data
  
• Recover tables or rows to a previous point in time
  
• Automatically track and archive transactional data changes
  
• Roll back a transaction and its dependent transactions while the database remains online
  

Flashback features utilize the Automatic Undo Management (AUM) to get metadata and historical data for transactions. For instance, if a user runs an UPDATE statement to change a retail_price from 2000 to 5000, then Oracle Database stores the value 2000 as undo data, which is persistent in database beyond shutdown.

In addition to this, Oracle Database uses undo data to perform these actions:

• Roll back active transactions
• Recover terminated transactions by using database or process recovery
• Provide read consistency for SQL queries

Application Development Features

The key application development features, include:

Oracle Flashback Query

A developer or DBA would use this feature to retrieve data for an earlier time that he or she specified with the AS OF clause of the SELECT statement.

Oracle Flashback Version Query

A DBA or developer could use this feature to retrieve metadata and historical data for a specific time interval (for example, to view the set of rows in a table that ever existed for a given period of time). Metadata for each row version includes start and end time, type of change operation, and identity of the transaction that created the row version. To create an Oracle Flashback Version Query, they would need to use the VERSIONS BETWEEN clause of the SELECT statement.

Oracle Flashback Transaction Query

A developer or DBA can use this feature to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval; in order to perform an Oracle Flashback Transaction Query, they need to select from the static data dictionary view FLASHBACK_TRANSACTION_QUERY.

DBMS_FLASHBACK Package

Normally, a developer DBA could use this feature to set the internal Oracle Database clock to an earlier time so that the DBA can examine data that was current at that time, or to roll back a transaction and its dependent transactions while the database remains online.

Flashback Transaction

Development DBAs use Flashback Transaction to roll back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the corresponding compensating transactions that return the affected data to its original state.

Flashback Data Archive (Oracle Total Recall)

Development DBAs can utilize the Flashback Data Archive to automatically track and archive both regular queries and Oracle Flashback Query, providing SQL-level access to the versions of database objects without getting the ORA-001555 snapshot-too-old error.

Database Administration Features

These flashback features are mainly for data recovery and used normally by a DBA.

Oracle Flashback Table

A DBA uses this capability to restore a table to its state at a previous point in time. The DBA can restore a table while the database is on line, undoing changes solely to the specified table.

Oracle Flashback Drop

The DBA utilizes this feature to recover a dropped table. This feature can reverse the effects of a DROP TABLE statement.

Oracle Flashback Database

The DBA can use this feature to quickly return the database to an earlier point in time, by undoing all of the changes that have taken place since then. This occurs rather fast, since the DBA needs not to have database backups restored.

Database Configuration for Oracle Flashback Technology

Planning Flashback configuration is quite important. So, a DBA must first perform the set of configuration tasks for:

• Configuring the Database for Automatic Undo Management
• Configuring the Database for Oracle Flashback Transaction Query
• Configuring the Database for Flashback Transaction
• Enabling Oracle Flashback Operations on Specific LOB Columns
• Granting Necessary Privileges

Database Configuration for Automatic Undo Management

In order to configure the database for Automatic Undo Management (AUM), the DBA has to:

• Create an undo tablespace (or use an existing one) with enough space to keep the required data for flashback operations, and planning on capacity and growth accordingly.
  
• Enable Automatic Undo Management, setting the following initialization parameters accordingly:
  
  • UNDO_MANAGEMENT
  • UNDO_TABLESPACE
  • UNDO_RETENTION
  If the undo tablespace is fixed in size, Oracle Database automatically tunes the system to give the undo tablespace the best possible undo retention. Otherwise, if extensible, Oracle Database retains undo data longer than the longest query duration and the low threshold of undo retention specified by the UNDO_RETENTION parameter.
  The DBA can query the V$UNDOSTAT.TUNED_UNDORETENTION view to verify the amount of time for which undo is retained for the current undo tablespace. Further information can also be obtained from the V$UNDOSTAT view.
  It is important to understand that setting UNDO_RETENTION does not guarantee that unexpired undo data is not discarded. If the system needs more space, Oracle Database is able to overwrite unexpired undo with more recently generated undo data.
  
• Specify the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired undo data is not discarded.

Database Configuration for Oracle Flashback Transaction Query

In order to configure a database for the Oracle Flashback Transaction Query feature, the DBA needs to:

• Ensure that Oracle Database is running with version 10.0 compatibility.
• Enable supplemental logging using the statement:

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database Configuration for Flashback Transaction

To configure the database for the Flashback Transaction feature, the database administrator needs to:

• With the database mounted but not open, enable ARCHIVELOG:
  

  ALTER DATABASE ARCHIVELOG;

• Open at least one archive log:
  

  ALTER SYSTEM ARCHIVE LOG CURRENT;

• If not done, enable minimal and primary key supplemental logging:
  

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  
  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

• When tracking foreign key dependencies, enable foreign key supplemental logging:
  

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

If the database contains too many foreign key constraints, enabling foreign key supplemental logging may not be worth the performance overhead.

Oracle Flashback Operations on Specific LOB Columns

In order to enable flashback operations on specific LOB columns of a table, the DBA must use the ALTER TABLE statement with the RETENTION option.

Because undo data for LOB columns can be voluminous, the DBA has to preselect the LOB columns to use with flashback operations.

Necessary Privilege Grants

The DBA needs to grant privileges to users, roles, or applications that must use these flashback features.

For Oracle Flashback Query and Oracle Flashback Version Query

In order for a DBA to allow access to specific objects during queries, he needs to grant FLASHBACK and SELECT privileges on those objects; while to allow queries on all tables, he has to grant the FLASHBACK ANY TABLE privilege.

For Oracle Flashback Transaction Query

Grant the SELECT ANY TRANSACTION privilege.

To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction Query, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables.

For DBMS_FLASHBACK Package

To use the DBMS_FLASHBACK package, a grant to EXECUTE privilege on DBMS_FLASHBACK is required.

For Flashback Data Archive (Oracle Total Recall)

In order to permit for a specific user to enable Flashback Data Archive on tables, using a specific Flashback Data Archive, the DBA must grant the FLASHBACK ARCHIVE object privilege on that Flashback Data Archive to that user. Likewise, to grant the FLASHBACK ARCHIVE object privilege, the DBA needs to either be logged on as SYSDBA or have FLASHBACK ARCHIVE ADMINISTER system privilege.

Besides, in order to support the execution of these statements, the DBA must isssue a grant of the FLASHBACK ARCHIVE ADMINISTER system privilege:

• CREATE FLASHBACK ARCHIVE
• ALTER FLASHBACK ARCHIVE
• DROP FLASHBACK ARCHIVE

Also, to grant the FLASHBACK ARCHIVE ADMINISTER system privilege, the database administrator must be logged on as SYSDBA.

To create a default Flashback Data Archive, using either the CREATE FLASHBACK ARCHIVE or ALTER FLASHBACK ARCHIVE statement, the DBA must be logged on as SYSDBA.

To disable Flashback Data Archive for a table that has been enabled for Flashback Data Archive, the user must either be logged on as SYSDBA or have the FLASHBACK ARCHIVE ADMINISTER system privilege.

Oracle Flashback Query (Using SELECT AS OF)

To begin with, use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

• Simplifying application design by removing the need to store some kinds of temporal data.
  Oracle Flashback Query lets DBAs retrieve past data directly from the database.
  
• Applying packaged applications, such as report generation tools, to past data.
  
• Recovering lost data or undoing incorrect, committed changes.
  For instance, if a user mistakenly deletes or updates certain rows, and then commits them, the DBA can immediately undo this human error.
  
• Comparing current data with the corresponding data at an earlier time.
  For instance, the DBA can run a daily report that shows the change in data from yesterday. Then, the DBA can compare individual rows of table data or find intersections or unions of sets of rows.
  
• Providing self-service error correction for an application, therefore enabling users to undo and correct their errors.
  
• Checking the state of transactional data at a particular time.

Case Study on Examining and Restoring Past Data

Assume that the DBA discovered at 4:30 AM that the row for an employee named Smith was deleted from the personnel table, and he knows that the previous night at 7:30PM the data for Smith was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 7:30 PM to find out what data was lost. If appropriate, you can restore the lost data.

The employee record at 7:30PM March 21, 2007

Retrieving a Lost Row with Oracle Flashback Query

SELECT * 
  FROM personnel
  AS OF TIMESTAMP
  TO_TIMESTAMP('2012-03-21 07:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE UPPER(last_name) = 'SMITH';

Restoring a Lost Row After Oracle Flashback Query

INSERT INTO personnel 
 (
  SELECT * 
    FROM personnel
    AS OF TIMESTAMP
    TO_TIMESTAMP('2012-03-21 07:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE UPPER(last_name) = 'SMITH'
 );
Oracle Flashback Query Guidelines 
A DBA can perform the following tasks, namely:

• Specify or omit the AS OF clause for each table and specify different times for different tables.
  
• Use the AS OF clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as INSERT and DELETE) in the same session as Oracle Flashback Query.
  
• Use the result of Oracle Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.
  
• If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a time stamp.
  
• Create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.
  If the DBA specifies a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For instance:
  

  CREATE VIEW v_personnel_past_hour 
    AS
    SELECT * 
      FROM personnel
      AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);  

• The DBA can use the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times.
  You can store the results by preceding Oracle Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For instance, this query reinserts into table personnel the rows that existed an hour ago:
  

  INSERT INTO personnel
      (
        ( SELECT * FROM personnel
          AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
         )
        MINUS 
      SELECT * FROM personnel
      );

Oracle Flashback Version Query

DBAs can use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. The syntax is:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

where start and end are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (start and end).

Oracle Flashback Version Query Row Data Pseudocolumns

Pseudocolumn Name	Description
VERSIONS_STARTSCN VERSIONS_STARTTIME	It describes the starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query. Indeed, if this pseudocolumn is NULL, then the row version was created before start.
VERSIONS_ENDSCN VERSIONS_ENDTIME	The SCN or TIMESTAMP when the row version expired. If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_XID	This is the identifier of the transaction that created the row version.
VERSIONS_OPERATION	The operation performed by the transaction: I for insertion, D for deletion, or U for update, denoting the row version.

Here is a typical use of Oracle Flashback Version Query:

SELECT versions_startscn, 
       versions_starttime,
       versions_endscn, 
       versions_endtime,
       versions_xid, 
       versions_operation,
       last_name, salary
  FROM personnel
  VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2007-03-21 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_TIMESTAMP('2007-03-22 04:30:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE first_name = 'Anthony';

Oracle Flashback Transaction Query

DBA can use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY, whose columns are described in Oracle Database Reference. The column UNDO_SQL shows the SQL code that is the is the logical opposite of the DML operation performed by the transaction. The following statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code that shows the logical opposite of the operation:

SELECT xid, 
       operation, 
       start_scn, 
       commit_scn, 
       logon_user, 
       undo_sql
FROM  flashback_transaction_query
WHERE xid = HEXTORAW('000400070000004F');

This statement uses Oracle Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change:

SELECT xid,
               logon_user
FROM flashback_transaction_query
WHERE xid IN ( SELECT versions_xid
                                FROM personnel
                               VERSIONS BETWEEN
                              TIMESTAMP TO_TIMESTAMP('2007-03-21 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
                AND TO_TIMESTAMP('2007-03-22 04:30:00', 'YYYY-MM-DD HH24:MI:SS') );
Concluding Remarks

It is important to understand the relevance of appropriate settings for Automatic Undo Management (AUM) to attain preparedness and readiness in the event that an Oracle Flashback can take place accordingly without any issues, from which some possible scenarios will be discussed in the next edition of this blog series. Understanding Oracle Flashback Technology allows DBAs to attain good log Flashback mining skills practical in a dynamic recovery or business process strategy. 

Source: http://noriegaaoracleexpert.blogspot.com/2012/07/oracle11gflashback-executivesummary.html 

Flashback Technology – Step by step process

–Checking the status of Oracle Database
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
—————- ————
jrd              OPEN
SQL> select NAME,FLASHBACK_ON from v$database;
NAME      FLASHBACK_ON
——— ——————
JRD       NO
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Current log sequence           4
SQL> show parameter undo_retention
– FLASHBACK LOGS AND ARCHIVELOGS IN $ORACLE_BASE/fast_recovery_area
SQL> show parameter undo_retention
NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_retention                       integer     900
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_flashback_retention_target        integer     1440
SQL> show parameter  db_recovery_file_dest
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 2750M
SQL> show parameter  db_recovery_file_dest_size
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest_size           big integer 2750M
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /u01/app/oracle/product/11.2.0.3/db_1/dbs/spfilejrd.ora
SQL> show parameter undo_management
NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
–CHANGING THE LOCATION, FORMAT, SIZE, RETETION OF FLASHBACK LOGS – ARCHIVELOGS (OPTIONALLY).
ALTER SYSTEM SET log_archive_dest_1=”LOCATION=/u06/backups/archivelogs/jrd OPTIONAL REOPEN=300″ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format=”archJRD_%t_%r_%s.arc” SCOPE=SPFILE;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=”/u06/backups/fast_recovery_area” SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest_size=300G SCOPE=SPFILE;
–How to enable Flashback Feature on a Oracle Database.
SQL> shut immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
–The Database must be in Archivelogs mode, to use flasback technology.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL> SELECT LOG_MODE, NAME FROM V$DATABASE;
LOG_MODE     NAME
———— ———
ARCHIVELOG   JRD
SQL> select NAME, FLASHBACK_ON from v$database;
NAME      FLASHBACK_ON
——— ——————
JRD       YES
–generating archivelogs
SQL> alter system switch logfile; 
[/u01/app/oracle/fast_recovery_area/JRD/archivelog/2012_06_17]$ls -ltr
-rw-r—– 1 oracle oinstall 22849536 Jun 17 23:56 o1_mf_1_5_7xwr43jh_.arc

 – Flashback Query
SQL> conn scott/****
SQL> CREATE TABLE JRDTEST (  OBJECTID NUMBER  NOT NULL,  ALIAS VARCHAR2 (20)) TABLESPACE USERS;
SQL> ALTER SESSION SET NLS_DATE_FORMAT=’DD/MM/YYYY HH24:MI:SS’;
SQL> SELECT TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’)   from dual;
TO_CHAR(SYSDATE,’DD
——————-
18-06-2012 00:00:58
SQL> insert into JRDTEST values(101,’TABLE’);
SQL> insert into JRDTEST values(102,’TABLE’);
SQL> insert into JRDTEST values(103,’TABLE’);
SQL> insert into JRDTEST values(104,’TABLE’);
SQL> select * from JRDTEST;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
SQL> commit;
–Delete some data and commit
SQL> delete from JRDTEST where OBJECTID in(103,104);
SQL> commit;
SQL> select * from JRDTEST;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
SQL> SELECT TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’) from dual;  –time
TO_CHAR(SYSDATE,’DD
——————-
18-06-2012 00:04:33
– Insert some more data and commit
SQL> insert into JRDTEST values(105,’VIEW’);
SQL> insert into JRDTEST values(106,’VIEW’);
SQL> insert into JRDTEST values(107,’VIEW’);
SQL> commit;
SQL> SELECT TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’DD
——————-
18-06-2012 00:06:03
–identifying transactions
SQL> set linesize 512
SQL> col versions_starttime for a25
SQL> col versions_endtime for a25
SQL> col versions_xid for a45
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation, objectid
     from JRDTEST versions between timestamp minvalue and maxvalue
     order by VERSIONS_STARTTIME;

–errors with date format
–query these records using timestamp before actually recovering them.
SQL> SELECT * FROM JRDTEST  AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:04:15′,’DD-MM-YYYY HH:MI:SS’);
ERROR at line 1:
ORA-01849: hour must be between 1 and 12
SQL> SELECT * FROM JRDTEST  AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’);
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
–18-06-2012 00:03:30  ==> at this time until i delete two registers
–18-06-2012 00:04:33  ==> at this time i delete two registers (103, 140)
–inserting back these records to the original table
SQL> INSERT INTO JRDTEST (SELECT * FROM JRDTEST  AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’));
SQL> select * from JRDTEST;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       105 VIEW
       106 VIEW
       107 VIEW
  –>     101 TABLE
  –>     102 TABLE
  –>     103 TABLE
  –>     104 TABLE
–As dba user.
–verifying the table for flasback
select OWNER,TABLE_NAME,STATUS,PARTITIONED,ROW_MOVEMENT
from dba_tables
where TABLE_NAME like ‘%JRDTEST%’;
OWNER                          TABLE_NAME                     STATUS   PAR ROW_MOVE
—————————— —————————— ——– — ——–
SCOTT                          JRDTEST                          VALID    NO  DISABLED
grant flashback on scott.JRDTEST to scott;
–As scott user
alter table scott.JRDTEST enable row movement;
SQL> SELECT * FROM JRDTEST  AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’);
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
–Flash back the table till that time stamp
SQL> SELECT * FROM JRDTEST  AS OF TIMESTAMP TO_TIMESTAMP(’18-06-2012 02:03:30′,’DD-MM-YYYY HH24:MI:SS’);
SQL> SELECT * FROM JRDTEST order by 1;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       101 TABLE
       102 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
       105 VIEW
       106 VIEW
       107 VIEW
9 rows selected.
SQL> FLASHBACK TABLE JRDTEST TO TIMESTAMP TO_TIMESTAMP(’18-06-2012 00:03:30′,’DD-MM-YYYY HH24:MI:SS’);
Flashback complete.
SQL> SELECT * FROM JRDTEST order by 1;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
–Undo a DROP TABLE Operation using FLASH BACK
SQL> create table JRDTEST_drop as select * from JRDTEST;
Table created.
SQL> select * from JRDTEST_drop;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
SQL> drop table JRDTEST_drop;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
—————————— ——————————–
BIN$wu7OSqOSD1TgQ4LOqMDVgw==$0 JRDTEST_DROP
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
JRDTEST_DROP       BIN$wu7OSqOSD1TgQ4LOqMDVgw==$0 TABLE        2012-06-20:23:20:25
–verifying that Dropped table contains all rows.
SQL> SELECT * FROM “BIN$wu7OSqOSD1TgQ4LOqMDVgw==$0″;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
– Flash back the table to it’s before drop state.
SQL> select OWNER, TABLE_NAME from all_tables where table_name = ‘JRDTEST_DROP’ and owner like ‘SCO%’;
no rows selected
SQL> FLASHBACK TABLE scott.JRDTEST_drop TO BEFORE DROP;
Flashback complete.
SQL> show recyclebin;
SQL>
SQL> select OWNER, TABLE_NAME from all_tables where table_name = ‘JRDTEST_DROP’ and owner like ‘SCO%’;
OWNER                          TABLE_NAME
—————————— ——————————
SCOTT                          JRDTEST_DROP
SQL> select TABLE_NAME, STATUS, DROPPED from all_tables where TABLE_NAME like’%JRDTEST_DROP%’;
TABLE_NAME                     STATUS   DRO
—————————— ——– —
JRDTEST_DROP                     VALID    NO
–ALSO WE CAN RENAME THE TABLE REMOVED
SQL> SELECT * FROM JRDTEST order by 1;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
SQL> drop table JRDTEST;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
JRDTEST            BIN$wu7OSqOUD1TgQ4LOqMDVgw==$0 TABLE        2012-06-20:23:31:47
SQL> FLASHBACK TABLE “BIN$wu7OSqOUD1TgQ4LOqMDVgw==$0″ TO BEFORE DROP
     RENAME TO JRDTEST1;
Flashback complete.
–TESTING
SQL>  show recyclebin;
SQL>
SQL> SELECT * FROM JRDTEST;
SELECT * FROM JRDTEST order by 1
              *
ERROR at line 1:
ORA-00942: table or view does not exist       ok
SQL> SELECT * FROM JRDTEST1;
  OBJECTID ALIAS
———- ——————–
       101 TABLE
       102 TABLE
       103 TABLE
       104 TABLE
–Freeing Space in the Recycle Bin
SQL> PURGE TABLE JRDTEST;   –alright, because i haven’t anything in the recycle bin
PURGE TABLE JRDTEST
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN            ok
SQL> PURGE TABLE “BIN$wu7OSqOUD1TgQ4LOqMDVgw==$0″;
SQL> PURGE TABLESPACE USER; – - All deleted objects of this tablespace.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> PURGE DBA_RECYCLEBIN;
–ORACLE FLASHBACK DATABASE (Point-In-Time Recovery)
–AS SYSDBA
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
————
OPEN
SQL> select NAME,FLASHBACK_ON from v$database;
NAME      FLASHBACK_ON
——— ——————
JRD       YES
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL> show parameter undo_retention
NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_retention                       integer     900
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_flashback_retention_target        integer     1440
SQL> show parameter  db_recovery_file_dest
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 2750M

–Determining the Current Flashback Database Window
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASH
——————– ——————–
              861065 17-JUN-2012 11:53:27
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASH
——————– ——————–
              861065 17-JUN-2012 23:53:27
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN   date= 20-jun-2012 20:51:20
———–
     916953
–Execute the FLASHBACK DATABASE Command from RMAN
[oracle@localhost ~]$ rman target /
RMAN> FLASHBACK DATABASE TO TIME “TO_DATE(’17-06-2012 23:53:27′,’DD-MM-YYYY HH24:MI:SS’)”;
–another method
RMAN> flashback database to scn 916953;  –ORA-38743: Time/SCN is in the future of the database. ok
RMAN> flashback database to scn 861065;
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLASH
——————– ——————–
              861065 17-JUN-2012 23:53:27
SQL> SELECT CURRENT_SCN, RESETLOGS_TIME from v$database;
CURRENT_SCN RESETLOGS_TIME
———– ——————-
     866011 22/06/2012 22:23:07
–VERIFYING
SQL> conn scott/jrd;
Connected.
SQL> SELECT * FROM JRDTEST1;
SELECT * FROM JRDTEST1
              *
ERROR at line 1:
ORA-00942: table or view does not exist   –ok