Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

FLASHBACK DATABASE glavu čuva!

Tuesday, 23.10.2012 – Dejan

Zašto Flashback?

Sigurno ste se već našli u situaciji da imate veoma kratak “maintenance window” u sklopu kojeg se vrše neke izmjene nad bazom, npr. instaliranje novog patcha, novi release ogromne aplikacije ili neka slična operacija, koja bi mogla biti kritična, što znači, da bi u slučaju neke greške ili neželjenog rezultata morali vratiti bazu u prvobitno stanje, dakle prije te izmjene.

U mnogim slučajevima se previdi taj “fallback” scenario, pa Oracle DBA radi restore kompletne baze… A to traje, pogotovo ukoliko je baza velika… Taman ste pomislili:”Još ova sitnica, 5-10 minuta i gotovo!“, kad ono cvrc! Greška. Panika. Psovanje. Restore baze. Pa se onda češkate po glavi i nestrpljivo čekate kad će restore biti okončan…

Taaa-daaam!

Od sad, pa nadalje i ubuduće za većinu tih “maintenance taskova” (plaćam pivo i ćevape za iole smislen prevod ovih riječi!) možete koristiti Flashback tehnologiju! Ukratko, Flashback omogućava “povratak u prošlost” do nekog zadanog trenutka.

Dakle, prije nego što započnete sa izmjenama na bazi, aktivirate Flashback i postavite tačku (“RESTORE POINT“) do koje želite vratiti bazu ukoliko dođe do nekih problema.

Kako Flashback?

Pokazaću kako se Flashback aktivira, kako se postavlja RESTORE POINT i kako se koristi za vraćanje kompletne baze do te tačke.

1. Da bi Flashback mogao biti aktiviran, baza mora biti u ARCHIVELOG režimu rada. Provjeriti parametre i ukoliko je potrebno, prebaciti bazu u ARCHIVELOG režim. U suprotnom, preskočite taj korak.

SQL> select log_mode, flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
NOARCHIVELOG NO

SQL> sho parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\_Oracle_\app\flash_recovery_area
db_recovery_file_dest_size           big integer 3912M


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2178496 bytes
Variable Size            1811939904 bytes
Database Buffers          738197504 bytes
Redo Buffers               19828736 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

2. Potrebno je obavezno podesiti dva parametra u bazi: veličinu i lokaciju za Flashback arhivu:

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G scope=BOTH;

System altered.

SQL>  alter system set db_recovery_file_dest='D:\_Oracle_\app\flash_recovery_area' scope=BOTH;

System altered.

Parametri moraju ostati permanentno u SPFILE ili u init.ora datoteci, da se ne bi nakon restarta baze izgubili. Ako ne stavite “scope=BOTH” ili ukoliko u init.ora datoteci ne sačuvate te parametre, onda ćete nakon restarta baze dobiti slijedeću gresku:

SQL> flashback database to restore point BEFORE_CHANGE;
flashback database to restore point BEFORE_CHANGE
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-38770: FLASHBACK DATABASE failed during recovery.
ORA-38760: This database instance failed to turn on flashback database

a u alert logu ćete pronaći ove greske:

Thu Oct 25 09:45:09 2012
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 684437221
Allocated 14761792 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Thu Oct 25 09:45:14 2012
RVWR started with pid=18, OS id=58916978
RVWR could not begin generation of flashback log data because
DB_RECOVERY_FILE_DEST is not set.
Errors in file ..._rvwr_58916978.trc:
ORA-38776: cannot begin flashback generation - recovery area is disabled
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Thu Oct 25 09:45:24 2012
flashback database to restore point BEFORE_CHANGE
Flashback Restore Start
Thu Oct 25 09:46:18 2012
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Thu Oct 25 09:46:18 2012
Slave exiting with ORA-38770 exception
Errors in file ..._pr00_24903742.trc:
ORA-38770: FLASHBACK DATABASE failed during recovery.
ORA-38760: This database instance failed to turn on flashback database
Recovery Slave PR00 previously exited with exception 38770
Flashback Media Recovery failed with error 10879
ORA-283 signalled during: flashback database to restore point BEFORE_CHANGE...

3. Aktiviranje Flashback-a i postavljanje tačke povratka:

3.1. U verziji 10g baza mora biti u MOUNT režimu, da bi se Flashback mogao aktivirati:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2178496 bytes
Variable Size            1811939904 bytes
Database Buffers          738197504 bytes
Redo Buffers               19828736 bytes
Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> create restore point BEFORE_CHANGE guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

3.2. U verziji 11gRel2 baza ne mora biti u MOUNT režimu, tako da odmah možemo aktivirati Flashback i postaviti RESTORE POINT:

SQL> alter database flashback on;

Database altered.

SQL> create restore point BEFORE_CHANGE guarantee flashback database;

Restore point created.

SQL> col name for a25
SQL> col time for a34
SQL> set lines 180
SQL> select SCN, NAME, GUARANTEE_FLASHBACK_DATABASE as GUA, STORAGE_SIZE, TIME
 from v$restore_point;

       SCN NAME               GUA STORAGE_SIZE TIME
---------- ------------------ --- ------------ ----------------------------------
   1673006 BEFORE_CHANGE      YES     15941632 23-OCT-12 10.00.42.000000000 PM

4. Napravimo neku izmjenu u bazi – za ovaj primjer ću samo kreirati jedan tablespace i jednog korisnika:

SQL> create tablespace dejan 
datafile 'D:\_ORACLE_\APP\ORADATA\DWHTEST\dejan.dbf' size 16M autoextend off 
extent management local segment space management auto;

Tablespace created.

SQL> create user dejan identified by dejan default tablespace users temporary tablespace temp;

User created.

Opcionalno možete povećati “retention time”, ukoliko taj “maintenance task” traje duže od jednog dana. Standardno je parametar “db_flashback_retention_target” postavljen na 1440 minuta, odnosno jedan dan, pa ga po potrebi povećajte:

SQL> sho parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440 

SQL> alter system set db_flashback_retention_target=2880;

System altered.

5. Ok, gotovi smo sa izmjenama, pa možemo ili vratiti bazu u prvobitno stanje ukoliko se pojavila neka kritična greška, ili deaktivirati Flashback i obrisati RESTORE POINT:

5.1. Vraćanje baze u prvobitno stanje:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2178496 bytes
Variable Size            1811939904 bytes
Database Buffers          738197504 bytes
Redo Buffers               19828736 bytes
Database mounted.

SQL> flashback database to restore point BEFORE_CHANGE;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> drop restore point BEFORE_CHANGE;

Restore point dropped.

5.2. Sve je u redu, nastavljamo u revijalnom tonu:

SQL> alter database flashback off;

Database altered.

SQL> drop restore point BEFORE_CHANGE;

Restore point dropped.

6. OBAVEZNO!!!! uraditi full backup baze, zato što smo napravili novu inkarnaciju baze sa ALTER DATABASE OPEN RESETLOGS.

Eto, pa vi sad usporedite vrijeme potrebno za restore kompletne baze i vrijeme potrebno da se baza pomoću Flashback tehnologije vrati u prvobitno stanje.