DB2 Backup przyrostowy INCREMENTAL w trybie OFFLINE

Dziś omówimy backup przyrostowy INCREMENTAL w trybie OFFLINE

Załóżmy, że mamy bardzo dużą bazę i backup wykonuje się kilka godzin. Jeśli zależy nam na czasie wykonywania backupu możemy zamiast backupu pełnego wykonywać backupy przyrostowe.
Przy próbie uruchomienia backupu przyrostowego (INCREMENTAL) zwraca nam błąd.

[db2inst1@localhost ~]$ db2 BACKUP DB TESTOWA INCREMENTAL

SQL2426N  The database has not been configured to allow the incremental backup
operation. Reason code = "1".

Zwróciło nam komunikat że baza nie jest skonfigurowana do tego typu backupów. odpowiedzialny za to jest parametr bazy TRACKMOD który powinien być ustawiony na YES. Sprawdzamy jego stan:

[db2inst1@localhost ~]$ db2 get db cfg for TESTOWA | grep TRACKMOD

 Track modified pages                         (TRACKMOD) = NO

Zmieniamy na odpowiedni:
[db2inst1@localhost ~]$ db2 "update db cfg using TRACKMOD YES"

DB20000I  The UPDATE DATABASE CONFIGURATION command completed 
successfully.SQL1363W  One or more of the parameters submitted
for immediate modification were not changed dynamically. For these 
configuration parameters, the database must be shutdown and 
reactivated before the configuration parameter changes 
become effective.

Restartujemy bazę aby zaczytała parametry:
db2 terminate
db2stop
db2start

[db2inst1@localhost ~]$ db2 get db cfg for TESTOWA | grep TRACKMOD

 Track modified pages                         (TRACKMOD) = YES

Aby uruchomić backup przyrostowy po tej zmianie należy wykonać pełny backup
[db2inst1@localhost ~]$ db2 BACKUP DB TESTOWA COMPRESS

 Backup successful. The timestamp for this backup image is : 20171130003144

Podłączamy się do bazy (db2 connect to TESTOWA) i tworzymy sobie w bazie tabelę:

db2 "CREATE TABLE mytable (name char(10), lastname char(20), phone char(9))"

DB20000I  The SQL command completed successfully.

Dodajemy kilka razy wiersz do naszej tabeli MyTable:

[db2inst1@localhost ~]$ db2 "INSERT INTO MyTable VALUES ('Robert','Badura','853964725')"

 DB20000I  The SQL command completed successfully.

[db2inst1@localhost BackupBaz]$ db2 "select * from MyTable"

NAME       LASTNAME             PHONE
---------- -------------------- ---------
Robert     Badura               853964725
Robert     Badura               853964725
Robert     Badura               853964725
Robert     Badura               853964725
Robert     Badura               853964725
Robert     Badura               853964725

  6 record(s) selected.

Teraz możemy wykonać backup przyrostowy czyli zawierający różnice w bazie od ostatniego backupu FULL.
[db2inst1@localhost ~]$ db2 BACKUP DB TESTOWA INCREMENTAL

 Backup successful. The timestamp for this backup image is : 20171130003328

Możba też wykonać backup DELTA zawierający zmiany od ostatniego backupu FULL, INCREMENTAL lub DELTA.

[db2inst1@localhost ~]$ db2 "INSERT INTO MyTable VALUES ('Maria','Nowak','752964715')"

 DB20000I  The SQL command completed successfully.

[db2inst1@localhost ~]$ db2 BACKUP DB TESTOWA INCREMENTAL DELTA

 Backup successful. The timestamp for this backup image is : 20171130004814

Backup został wykonany. Mamy już sporą ilość plików z kopiami zapasowymi bazy TESTOWA:

[db2inst1@localhost BackupBaz]$ ls -la | grep TESTOWA

 total 255976
-rw-------.  1 db2inst1 db2iadm1 108597248 Nov 29 23:54 TESTOWA.0.db2inst1.NODE0000.CATN0000.20171129235429.001
-rw-------.  1 db2inst1 db2iadm1  21151744 Nov 29 23:56 TESTOWA.0.db2inst1.NODE0000.CATN0000.20171129235649.001
-rw-------.  1 db2inst1 db2iadm1  21151744 Nov 30 00:31 TESTOWA.0.db2inst1.NODE0000.CATN0000.20171130003144.001
-rw-------.  1 db2inst1 db2iadm1  12087296 Nov 30 00:33 TESTOWA.0.db2inst1.NODE0000.CATN0000.20171130003328.001
-rw-------.  1 db2inst1 db2iadm1   6053888 Nov 30 00:48 TESTOWA.0.db2inst1.NODE0000.CATN0000.20171130004814.001 

Sprawdzimy listę naszych backupów naszej bazy

[db2inst1@localhost]$ db2 list history backup all for TESTOWA

                    List History File for TESTOWA

Number of matching file entries = 6


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171129235429001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171129235429
   End Time: 20171129235432
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /db2/db2home/db2inst1/backup_testowa


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171129235649001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171129235649
   End Time: 20171129235652
     Status: A
 ----------------------------------------------------------------------------
  EID: 2 Location: /db2/db2home/db2inst1/backup_testowa


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171130003058000   I       S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

 00001 SYSCATSPACE
 00002 TEMPSPACE1
 00003 USERSPACE1
 00004 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171130003058
   End Time: 20171130003059
     Status: A
 ----------------------------------------------------------------------------
  EID: 4 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2426   sqlerrml: 1

 sqlerrmc: 2
 sqlerrp : sqlubBui
 sqlerrd : (1) 0                (2) 0                (3) 0
           (4) 0                (5) 0                (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171130003144001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171130003144
   End Time: 20171130003147
     Status: A
 ----------------------------------------------------------------------------
  EID: 5 Location: /db2/db2home/db2inst1/backup_testowa


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171130003328001   I    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171130003328
   End Time: 20171130003329
     Status: A
 ----------------------------------------------------------------------------
  EID: 6 Location: /db2/db2home/db2inst1/backup_testowa


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171130004814001   D    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171130004814
   End Time: 20171130004815
     Status: A
 ----------------------------------------------------------------------------
  EID: 7 Location: /db2/db2home/db2inst1/backup_testowa

Jak widać ostatni nasz backup zmienił typ (TYPE) z F (full czyli pełny backup bazy) na I (incremental czyli zmiany od ostatniego backupu pełnego) a następnie wykonaliśmy D (delta czyli zmiany od ostatniego jakiegokolwiek backupu).
W przypadku konieczności odzyskania z backupu należy odzyskać backup pełny, backup inkrementalny, a następnie backup delta

Ten wpis został opublikowany w kategorii DB2 i oznaczony tagami , . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz