Creating Restore Point

Steps to do flashback:

1)COnfigure the three parameters.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

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

db_recovery_file_dest string /decoopdbdmmk1/oracle/arch/div03/fra

db_recovery_file_dest_size big integer 2G

SQL> show parameter flashback

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440

2)SQL> select flashback_on from V$database;

FLASHBACK_ON

NO

if the value is YES then go to step 3 else

SQL> alter database flashback on;

Database altered.

3)SQL> Create table vijay.flash_t (id int);

Table created.

SQL> begin

2 for i in 1..100

3 loop

4 insert into vijay.flash_t values(i);

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

4)SQL> create restore point vijay gUARANTEE flashback database;

Restore point created.

5)SQL> truncate table vijay.flash_t;

Table truncated.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size 2232640 bytes

Variable Size 2113932992 bytes

Database Buffers 1073741824 bytes

Redo Buffers 16928768 bytes

Database mounted.

6)SQL> flashback database to restore point vijay;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

7)SQL> select count(1) from vijay.flash_t;

COUNT(1)

100