DB2 Backup logów transakcyjnych w trybie ONLINE

Dzisiejszym tematem będzie wykonanie kopii logów transakcyjnych czyli kopia bazy w trybie ONLINE.
Aby wykonywać backupy bazy w trybie ONLINE musimy ją najpierw odpowiednio skonfigurować.

Sprawdzamy konfigurację bazy danych TESTOWA

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

 User exit for logging status                            = NO
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Number of primary log files                (LOGPRIMARY) = 3
 Number of secondary log files               (LOGSECOND) = 2
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5

Mamy 3 logi transakcyjne PRIMARY i 2 SECONDARY. W normalnej pracy wykorzystywane są logi PRIMARY. Logi SECONDARY są wykorzystywane gdy logi PRIMARY są „przepełnione”.

Opcja „First log archive method = OFF” świadczy o tym, że nie jest włączona opcja wykonywania backupu w trybie ONLINE. Opcja LOGARCHMETH1 odpowiedzialna za miejsce zrzutu archivelogów jest wyłączona. Parametr LOGARCHMETH2 definiuje miejsce „zapasowe”, gdzie będą się wykonywały archivelogi (będą kopiowane w dwa miejsca).

Trzeba stworzyć folder do archivelogów:
[db2inst1@localhost ~]$ mkdir db2arch

Nadać uprawnienia dla użytkownika db2inst1 choć wydaje mi się że jeśli zakładamy folder z usera db2inst1 to nie ma takiej potrzeby:
[db2inst1@localhost ~]$ chown -R db2inst1. /home/db2inst1/db2arch

Ustawiamy miejsce wykonywania backupów

[db2inst1@localhost ~]$ db2 "update db cfg using LOGARCHMETH1 'DISK:/home/db2inst1/db2arch'"

 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.

Wykonujemy restart bazy aby zaczytać nowe zmienne

[db2inst1@localhost ~]$ db2 terminate

 DB20000I  The TERMINATE command completed successfully.

[db2inst1@localhost ~]$ db2stop

 02/21/2017 22:56:36     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

[db2inst1@localhost ~]$ db2start

 02/21/2017 22:56:41     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

[db2inst1@localhost ~]$ db2 connect to TESTOWA

 SQL1116N  A connection to or activation of database "TESTOWA" cannot be made
because of BACKUP PENDING.  SQLSTATE=57019

Wyświetlamy parametry bazy dotyczące logów

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

  User exit for logging status                            = YES
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000000.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/db2arch/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5

[db2inst1@localhost ~]$ db2 backup db TESTOWA online

 SQL2413N  Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.

Backup nie idzie, bo po przełączeniu należy wykonać backup bazy i dopiero uruchomić backup ONLINE. Dopóki nie wykonamy backupu nie można zmienić parametrów, ani podłączyć się do bazy (Activate).

[db2inst1@localhost ~]$ db2 connect to TESTOWA

 SQL1116N  A connection to or activation of database "TESTOWA" cannot be made
because of BACKUP PENDING.  SQLSTATE=57019

[db2inst1@localhost ~]$ db2 deactivate database TESTOWA

 SQL1496W  Deactivate database is successful, but the database was not
activated.


Wykonujemy backup bazy TESTOWA

[db2inst1@localhost BackupyBaz]$ db2 BACKUP DATABASE TESTOWA to /home/db2inst1/BackupyBaz

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

A następnie backup ONLINE

[db2inst1@localhost BackupyBaz]$ db2 backup db TESTOWA online to /home/db2inst1/BackupyBaz

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

[db2inst1@localhost BackupBaz]$ ls -la

 total 211268
drwxr-xr-x.  2 db2inst1 db2iadm1      4096 Feb 22 14:25 .
drwxr-xr-x. 33 db2inst1 db2iadm1      4096 Feb 22 14:17 ..
-rw-------.  1 db2inst1 db2iadm1 111534080 Feb 22 14:24 TESTOWA.0.db2inst1.NODE0000.CATN0000.20170222142454.001
-rw-------.  1 db2inst1 db2iadm1 104787968 Feb 22 14:25 TESTOWA.0.db2inst1.NODE0000.CATN0000.20170222142504.001 

Sprawdzamy nasze backupu bazy TESTOWA

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

                    List History File for TESTOWA

Number of matching file entries = 10


 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


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20171130010439000   N       S0000000.LOG
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA ONLINE
 Start Time: 20171130010439
   End Time: 20171130010440
     Status: A
 ----------------------------------------------------------------------------
  EID: 8 Location:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2413   sqlerrml: 0

 sqlerrmc:
 sqlerrp : sqlubIni
 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  20171130010519001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA OFFLINE
 Start Time: 20171130010519
   End Time: 20171130010521
     Status: A
 ----------------------------------------------------------------------------
  EID: 9 Location: /db2/db2home/db2inst1


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

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


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

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTOWA ONLINE
 Start Time: 20171130010600
   End Time: 20171130010603
     Status: A
 ----------------------------------------------------------------------------
  EID: 12 Location: /db2/db2home/db2inst1/backup_testowa

Typ backupu zmienił się na N (czyli logów)

Po wykonaniu pierwszego backup zaczną się tworzyć logi i pojawi się nowy parametr „First active log file” w konfiguracji

[db2inst1@localhost BackupyBaz]$ db2 get db cfg for TESTOWA | grep log

  User exit for logging status                            = YES
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = (MAXAPPLS*5)
 Number of primary log files                (LOGPRIMARY) = 3
 Number of secondary log files               (LOGSECOND) = 2
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /db2/db2home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000001.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/db2/db2home/db2inst1/archlogs/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5

Folder a logami transakcyjnymi : /db2/db2home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/

[db2inst1@localhost SQLOGDIR]$ ls -la

 total 12032
drwxr-x---. 2 db2inst1 db2iadm1    4096 Dec 23  2013 .
drwxr-x--x. 4 db2inst1 db2iadm1    4096 Dec 23  2013 ..
-rw-------. 1 db2inst1 db2iadm1 4104192 Dec 23  2013 S0000000.LOG
-rw-------. 1 db2inst1 db2iadm1 4104192 Dec 23  2013 S0000001.LOG
-rw-------. 1 db2inst1 db2iadm1 4104192 Dec 23  2013 S0000002.LOG

Miejsce składowania archów: /db2/db2home/db2inst1/archlogs/db2inst1/TESTOWA/NODE0000/C0000000

[db2inst1@localhost C0000000]$ ls -la

 total 20
drwxr-x---. 2 db2inst1 db2iadm1  4096 Feb 21 23:03 .
drwxr-x---. 3 db2inst1 db2iadm1  4096 Feb 21 22:56 ..
-rw-r-----. 1 db2inst1 db2iadm1 12288 Feb 21 23:03 S0000000.LOG

Od tej pory logi transakcyjne po każdorazowym przełączeniu będą się archiwizowały jako ArchiLogi. Za ich pomocą będzie można przywrócić bazę do punktu w czasie.

BACKUP TABLESPACE

Czasami może się zdarzyć sytuacja gdy potrzebujemy wykonać tylko backup konkretnej przestrzeni tabel. W tym celu wykonujemy:

[db2inst1@localhost ~]$ db2 "backup database TESTOWA tablespace (userspace1) online without prompting"

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


i backup pełen całej bazy

[db2inst1@localhost ~]$ db2 "backup database TESTOWA online without prompting"

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

Jeśli teraz porównamy ona backupy widać że znacznie różnią się rozmiarem

[db2inst1@localhost ~]$ ls -la

...
-rw-------.  1 db2inst1 db2iadm1 108290048 Dec  5 21:55 TESTOWA.0.db2inst1.NODE0000.CATN0000.20171205215540.001
-rw-------.  1 db2inst1 db2iadm1  14974976 Dec  5 21:54 TESTOWA.3.db2inst1.NODE0000.CATN0000.20171205215441.001

Backup wykonany. Już niedługo omówię jak odzyskać bazę z kopii zapasowej.

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

Dodaj komentarz