FLASHBACK DATABASE

$export ORACLE_SID=test

$sqlplus "/as sysdba"

SQL> select name from v$database;

NAME

---------

TEST

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+RECO';

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

NO                 ARCHIVELOG

SQL> alter database flashback on;

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

YES                ARCHIVELOG

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

SQL> show parameter flashback

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer     1440

SQL> alter system set db_flashback_retention_target=1440 scope=both sid='*';

SQL> show parameter flashback

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer     1440

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

Создание негарантированной RESTORE POINT.

SQL> create restore point rp1;

Restore point created.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME   SCN           TIME                                                              DATABASE_INCARNATION#  GUA            STORAGE_SIZE

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

RP1       14633448  01-AUG-12 10.28.32.000000000 AM  1                                                        NO               0

Удаление негарантированной RESTORE POINT:

SQL> drop restore point rp1;

Restore point dropped.

Создание тесеовой таблицы:

SQL> create table test (name varchar2(35));

Table created.

SQL> insert into test values ('aaa');

1 row created.

SQL> insert into test values ('bbb');

1 row created.

SQL> insert into test values ('ccc');

1 row created.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

Создание гарантированной RESTORE POINT:

SQL> create restore point rp1 guarantee flashback database;

Restore point created.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

SQL> insert into test values ('ddd');

1 row created.

SQL> insert into test values ('eee');

1 row created.

SQL> insert into test values ('fff');

1 row created.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

ddd

eee

fff

6 rows selected.

SQL> commit;

Commit complete.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

ddd

eee

fff

6 rows selected.

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point rp1;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

Отменим FLASHBACK

SQL> shutdown immediate;

SQL> startup mount;

SQL> recover database;

SQL> alter database open;

Database altered.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

ddd

eee

fff

6 rows selected.

Вернемся к RESTORE POINT

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point rp1;

Flashback complete.

Окончательно откроем базу

SQL> alter database open resetlogs;

Database altered.

SQL> select * from test;

NAME

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

aaa

bbb

ccc

SQL> exit

Удалаем точки восстанавления и отключаем режим FLASHBACK:

$sqlplus "/as sysdba"

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME  SCN             TIME                                                               DATABASE_INCARNATION#   GUA  STORAGE_SIZE

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

RP1       14634255  01-AUG-12 10.37.23.000000000 AM   1                                                          YES      52428800

SQL> drop restore point rp1;

Restore point dropped.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

YES                ARCHIVELOG

SQL> alter database flashback off;

Database altered.

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON       LOG_MODE

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

NO                 ARCHIVELOG

SQL>