Názorný příklad, jak lze efektne obnovit data za pomocí vlastnosti database flashback.Abyste mohli flashback databáze udělat, musíte splnit dva základní předpoklady:
1. Databáze musí běžet v archívním módu2. Musíte mít zapnutý flashback
As SYS============================================================================SQL> SELECT flashback_on, log_modeFROM v$database;
FLASHBACK_ON LOG_MODE—————— ————YES ARCHIVELOGRead MoreSQL> set linesize 121col name format a30col value format a30
SELECT name, valueFROM gv$parameterWHERE name LIKE ‚%flashback%‘;Read MoreNAME VALUE—————————— ——————————db_flashback_retention_target 1440
SQL> SELECT estimated_flashback_sizeFROM gv$flashback_database_log;
ESTIMATED_FLASHBACK_SIZE————————263331840
SQL> SELECT current_scnFROM v$database;
CURRENT_SCN———–2876929432
SQL> SELECT oldest_flashback_scn,oldest_flashback_timeFROM gv$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL——————– ———2876844665 15-AUG-09
SQL> GRANT flashback any table TO test;
Grant succeeded.
As TEST============================================================================SQL> show userUSER is „TEST“SQL> create table t (mycol VARCHAR2(20))ROWDEPENDENCIES;
Table created.
SQL> INSERT INTO t VALUES (‚ABC‘);
1 row created.
SQL> INSERT INTO t VALUES (‚DEF‘);
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE RESTORE POINT bef_damage;
Restore point created.
SQL> INSERT INTO t VALUES (‚GHI‘);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT ora_rowscn, mycol FROM t;
ORA_ROWSCN MYCOL———- ——————–2876930705 ABC2876930705 DEF2876930762 GHI
As SYS============================================================================SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount exclusive;ORACLE instance started.
Total System Global Area 1043886080 bytesFixed Size 2166288 bytesVariable Size 301990384 bytesDatabase Buffers 729808896 bytesRedo Buffers 9920512 bytesDatabase mounted.SQL> FLASHBACK DATABASE TO RESTORE POINT bef_damage;
Flashback complete.
SQL> alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
alert.log———-FLASHBACK DATABASE TO RESTORE POINT bef_damageFlashback Restore StartFlashback Restore CompleteFlashback Media Recovery StartFast Parallel Media Recovery enabledparallel recovery started with 2 processesRecovery of Online Redo Log: Thread 1 Group 3 Seq 7 Reading mem 0Mem# 0: /opt/oradata/TPROD/onlinelog/o1_mf_3_58b7213v_.logIncomplete Recovery applied until change 2876930740 time 08/17/2009 06:56:54Flashback Media Recovery CompleteCompleted: FLASHBACK DATABASE TO RESTORE POINT bef_damage
As TEST============================================================================
SQL> connect test/test123Connected.SQL> SELECT ora_rowscn, mycol FROM t;
ORA_ROWSCN MYCOL———- ——————–2876930705 ABC2876930705 DEF
I used testing scenario from psoug