[DB2] 아카이브 모드 변경과 SQL1116N(database is in BACKUP PENDING state) 에러 해결

아카이브모드로 변경하고나서는 DB 재기동이 필요합니다.

제가 작업한 것은 아카이브 로그를 Local disk의 경로에 생성하는 방법입니다.

 

노아카이브 모드 확인

 First log archive method                 (LOGARCHMETH1) = OFF 부분이 OFF이기 때문에 현재 노아카이브 모드입니다.

해당 부분을 변경하여 아카이브모드로 변경합니다.

$ db2 get db cfg for db2test |grep "LOG"
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 2150
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 12
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2test/db2test/NODE0000/SQL00001/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 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
 First log archive method                 (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO

아카이브모드 변경 및 확인

$ db2 update db cfg for DB2TEST using LOGARCHMETH1 DISK:/db2/db2test/archivelog
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.


$ db2 get db cfg for db2test |grep "LOG"
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 2150
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 12
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2test/db2test/NODE0000/SQL00001/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 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
 First log archive method                 (LOGARCHMETH1) = DISK:/db2/db2test/archivelog/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO

DB재기동

$ db2stop
03/16/2023 14:01:27     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

$ db2start
03/16/2023 14:01:32     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

$ db2 get db cfg for db2test |grep "LOG"
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 2150
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 12
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2test/db2test/NODE0000/SQL00001/LOGSTREAM0000/
 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
 First log archive method                 (LOGARCHMETH1) = DISK:/db2/db2test/archivelog/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO

 

DB 접속시도 (SQL1116N 에러 발생)

$ db2 connect to db2test
SQL1116N  A connection to or activation of database "DB2TEST" failed because
the database is in BACKUP PENDING state.  SQLSTATE=57019

 

해결 : OFFLINE FULL BACKUP을 받으면 됩니다.

$ db2 backup db db2test to /home/db2test

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

$ db2 connect to db2test

   Database Connection Information

 Database server        = DB2/AIX64 11.1.4.7
 SQL authorization ID   = DB2TEST
 Local database alias   = DB2TEST

 

출처 : https://itsiti.com/sql1116n-connection-to-db2-failed-because-backup-pending/