New Features of Oracle 10 Gb Flashback

  1. Flashback drop

   Currently, Oracle10g provides the new flashback drop feature to accelerate the recovery of user error operations.

   The flashback drop function allows you to restore a dropped object from the current database. When you perform the drop operation, Oracle does not delete the object but automatically puts the object into the recycle bin. The recycle bin is a virtual container used to store all deleted objects. In the recycle bin, the deleted object occupies the same space during creation. You can even query the deleted table or use the flashback function to restore it, this is the flashback drop function.

   The information in the recycle bin can be obtained from views such as recyclebin/user_recyclebin/dba_recyclebin, or through the show recyclebin command of SQL * Plus.

Example:

SQL> Create Table myemp as select * from EMP

Table creation.

 

SQL>Drop table myemp

Table deletion.

 

SQL> show recyclebin

Original Name     Recyclebin name              Object TypeDrop time

-----------------------------------------------------------------------------

Myemp          Bin $ 3ompfwhfr6q1ccyegxwbvg ==$ 0 table      2008-05-17: 09: 42: 54

 

----------------------- Flash restore

SQL> flashback table myemp to before drop

 2/

 

Flash back complete.

 

SQL> select empno, ename, job, Sal deptno from myemp where deptno = 20

2     /

3     

4         Empno ename    Job         Deptno

5     ---------------------------------------

6          7369 Smith    Clerk          800

7          7566 Jones    Manager       2975

8          7788 Scott    Analyst       3000

9          7876 Adams    Clerk         1100

10        7902 Ford     Analyst       3000

 

   Note: You can use "purge recyclebin" to clear all objects in the recycle bin.

   You can also clear different recycle bin objects through purge user_recyclebin or purge dba_recyclebin. Run the purge tablespace tsname and purge tablespace tsname User Username commands to clear the recycle bin.

   If you want to completely delete a table and do not want to put it in the recycle bin, you can add the purge option in the drop statement, for example: Drop table tablename purge

   Note: The drop operation of sysdba is not recorded. Oracle never recommends that you use sysdba to create user objects.

 

  2. Flashback Database

   To enable the flashback database function, you must execute alter database in Mount mode.Flashback on command. Or alter tablespace tsname flashback on. The database collects the falshback log. To disable this function, change on to off.
   To run the flashback DATABASE Command, run RMAN and sqlplus in two ways.

SQL> flashback database to time to_date (XXX );
SQL> flashback database to time to_timestamp (XXX );
SQL> flashback database to SCN xxx
SQL> flashback database to sequence XXX thread 1
SQL> flashback database to timestamp (sysdate-1/24)

   Let's take a look at the specific examples.

SQL> shutdown immediate; -- immediately shut down the database and instance

Database closed.
Database dismounted.
Oracle instance shut down.

SQL> startup Mount; -- start the instance and load the database
Oracle instance started.
total system global area 113246208 bytes
fixed size > 787708 bytes
variable size 87030532 bytes
database buffers > 25165824 bytes
redo buffers 262144 bytes
database mounted.

SQL> alter database flashback on; -- enable the flash back function
Alter database flashback on
*
Error at line 1:
ORA-38706: cannot turn on flashback database logging.
ORA-38707: Media recovery is not enabled.
As you can see,Flashback must also be archived

SQL> alter database archivelog; -- set to archive Mode
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open; -- open the database
Database altered.

SQL> archive log list; -- View Current Mode
Database Log mode archive mode
Automatic Archival enabled
archive destination use_db_recovery_file_dest
oldest online log sequence 13
next log sequence to archive 15
current log sequence 15
> after the above steps, we have ensured the flashback database function. We can also find that 10 Gb automatic archiving does not require manual intervention. If archiving is enabled, automatic archiving is required, the archive area is also located at the location specified by the db_recovery_file_dest parameter.

Use the following query
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
   Session altered.
SQL> select oldest_flashback_scn, oldest_flashback_timeFrom v $ flashback_database_log;

Oldest_flashback_scn oldest_flashback_ti
---------------------------------------
    1135440 15:20:54

   We can know the earliest SCN and time point that can be restored by the rollback. If flashback database is not ensured, no query results will be found in this view.

   We create three identical tables.
SQL> Create Table T1 as select * From dba_objects;
Table created.
SQL> Create Table T2 as select * from T1;
Table created.
SQL> Create Table T3 as select * from T1;
Table created.
SQL> set time on;
SQL> select sysdate from dual;
Sysdate
-------------------
2008-05-17 15:29:33

   We have determined a time point
   Now, we truncate a table and drop a table respectively to simulate misoperations.

SQL> set time on;
15:30:10 SQL> truncate table T2;
Table truncated.
15:30:43 SQL> drop table T3;
Table dropped.
   No backup is available. We use flashback to restore the database to 15:29:33.

15:39:02 SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.

15:39:31 SQL> startup Mount exclusive
Oracle instance started.

15:41:19 SQL> flashback database to timestamp (to_date (15:29:33 ', 'yyyy-mm-dd hh24: MI: ss '));
Flashback complete.

   Then, we can use alter database open read only to check whether the results are correct. If the results are met, we can use resetlog to officially start the database. Note that once resetlogs is complete, the time point before the resetlogs of flashback will no longer be available.

At 15:41:32 SQL> alter database open resetlogs;
Database altered.
We are now querying the three tables
15:42:10 SQL> select count (*) from T1;
  Count (*)
----------
   47708
15:42:47 SQL> select count (*) from T2;
  Count (*)
----------
   47708
15:42:50 SQL> select count (*) from T3;
  Count (*)
----------
   47708

   It is found that the truncate record or the drop table exists. Now the database has rolled forward to the previous time point.

15:44:56 SQL> select oldest_flashback_scn, oldest_flashback_time from $ flashback_database_log;

commandid
------------------ -----------------
1136169 15:41:50
both the earliest flashback time and SCN time of the database are returned to the resetlog time.