How to Recover a Table using Flashback
FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.
SQL> startup
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> alter system set db_recovery_file_dest_size=10g;
System altered.
SQL> alter system set db_recovery_file_dest=' /u01/app/oracle';
System altered.
SQL> alter database flashback on;
Database altered.
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle
db_recovery_file_dest_size big integer 10G
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> alter system set db_flashback_retention_target=2800;
System altered.
SQL> show parameter db_flashback_retention_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 2800
SQL> create restore point before_load guarantee flashback database;
Restore point created.
SQL> set time on;
02:09:20 SQL> select * from DBATEAM;
ID NAME DEPT
---------- ---------- ----------
1 INBA CSE
2 SAGA ECE
3 SHANKAR EEE
4 SIVA CSE
5 BIJU MECH
02:09:30 SQL> drop table DBATEAM purge;
drop table DBATEAM purge
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.AW_DROP_TRG
ORA-01775: looping chain of synonyms
02:10:30 SQL> ALTER SYSTEM SET "_system_trig_enabled" = FALSE
System altered.
02:10:35 SQL> drop table DBATEAM purge;
Table dropped.
02:10:39 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
02:11:16 SQL> startup mount
ORACLE instance started.
Total System Global Area 641728512 bytes
Fixed Size 8623976 bytes
Variable Size 427821208 bytes
Database Buffers 201326592 bytes
Redo Buffers 3956736 bytes
Database mounted.
02:11:38 SQL> flashback database to restore point before_load;
Flashback complete.
02:12:09 SQL> alter database open read only;
Database altered.
02:12:39 SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
02:13:33 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
02:14:07 SQL> startup mount
ORACLE instance started.
Total System Global Area 641728512 bytes
Fixed Size 8623976 bytes
Variable Size 427821208 bytes
Database Buffers 201326592 bytes
Redo Buffers 3956736 bytes
Database mounted.
02:14:21 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
02:14:32 SQL> alter database open resetlogs;
Database altered.
02:15:32 SQL> select * from DBATEAM;
ID NAME DEPT
---------- ---------- ----------
1 INBA CSE
2 SAGA ECE
3 SHANKAR EEE
4 SIVA CSE
5 BIJU MECH
No comments:
Post a Comment