Saturday, September 18, 2010

How flashback database can saves your time

============ Part 1, crash the database ============

SQL> conn / as sysdba

SQL> delete from sys.user$;

41 rows deleted.

SQL> commit;

SQL> select * from dba_users;

no rows selected

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [],
[], [], [], [], []
Process ID: 3366
Session ID: 125 Serial number: 5


============ Part 2, flashback the database ============

SQL> startup mount
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 348128992 bytes
Database Buffers 167772160 bytes
Redo Buffers 5869568 bytes
Database mounted.


SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE
-------------------- -------------------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
680531 2010-SEP-05 06:35:35 1440 75988992
95232000


SQL> flashback database to timestamp to_timestamp('2010-SEP-05 20:30:00','YYYY-MON-DD HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from sys.user$;

COUNT(*)
----------
41