DB2 常見(jiàn)問(wèn)題排查
2021-08-16 09:40 更新
- DB2 常見(jiàn)問(wèn)題排查
- 備份或者還原未完成
- 問(wèn)題
- 排查
- 解決
- 還原完后不能連接到數(shù)據(jù)庫(kù)
- 問(wèn)題
- 排查
- 解決
- 空間不足不能執(zhí)行命令
- 問(wèn)題
- 排查
- 解決
- 事務(wù)日志已經(jīng)滿
- 問(wèn)題
- 排查
- 解決
- 連接數(shù)據(jù)庫(kù)，分配共享內(nèi)容失敗，內(nèi)核限制
- 問(wèn)題
- 創(chuàng)建字段 varchar 設(shè)置過(guò)大
- 問(wèn)題
- 參考引用
- 備份或者還原未完成
DB2 常見(jiàn)問(wèn)題排查
備份或者還原未完成
問(wèn)題
執(zhí)行操作，提示如下：
[db2inst@localhost ~]$ db2 deactivate db necc_dbSQL1120N A connection to or activation of database "NECC_DB" cannot be madebecause a previous backup or restore is incomplete. SQLSTATE=57019
排查
根據(jù) https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01120n.html描述：“因?yàn)橄惹皞浞莼驈?fù)原不完整，所以不能連接或激活數(shù)據(jù)庫(kù) 名稱。”
解決
直接刪除了數(shù)據(jù)，再執(zhí)行還原該數(shù)據(jù)庫(kù)
[db2inst@localhost ~]$ db2 drop db necc_dbDB20000I The DROP DATABASE command completed successfully.
還原完后不能連接到數(shù)據(jù)庫(kù)
問(wèn)題
從備份中恢復(fù)DB2數(shù)據(jù)庫(kù)（本例是指“NECC_DB”）的拷貝到新的DB2服務(wù)器（本例是指“NECC_DBA”）。用戶已創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)連接（指向這個(gè)新的數(shù)據(jù)庫(kù)副本），當(dāng)試圖連接這個(gè)新的數(shù)據(jù)庫(kù)時(shí)，提示如下錯(cuò)誤:
[db2inst@localhost ~]$ db2 connect to necc_dbSQL1117N A connection to or activation of database "NECC_DB" cannot be madebecause of ROLL-FORWARD PENDING. SQLSTATE=57019
排查
出現(xiàn)SQL1117N 由于 ROLL-FORWARD PENDING，不能連接或激活數(shù)據(jù)庫(kù) “NECC_DBA”。
解決
將數(shù)據(jù)庫(kù)前滾。
執(zhí)行:
db2 rollforward db <database> complete
如：
[db2inst@localhost ~]$ db2 rollforward db necc_dba completeRollforward StatusInput database alias = necc_dbaNumber of members have returned status = 1Member ID = 0Rollforward status = not pendingNext log file to be read =Log files processed = -Last committed transaction = 2016-04-27-09.18.11.000000 UTCDB20000I The ROLLFORWARD command completed successfully.[db2inst@localhost ~]$
空間不足不能執(zhí)行命令
問(wèn)題
執(zhí)行 db2 語(yǔ)句創(chuàng)建或者刪除數(shù)據(jù)庫(kù)操作，均出現(xiàn)如下提示
SQL1004C There is not enough storage on the file system to process the command.
排查
用戶的磁盤(pán)不夠了。增加用戶磁盤(pán)，或者刪除一些數(shù)據(jù)，來(lái)增大用戶能使用的空間。
解決
刪除了沒(méi)有用的數(shù)據(jù)庫(kù)和老舊的數(shù)據(jù)庫(kù)備份文件。
事務(wù)日志已經(jīng)滿
問(wèn)題
執(zhí)行 db2 語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)索引、或者執(zhí)行修改操作，均出現(xiàn)如下提示
DB2 Database Error: ERROR [57011] [IBM][DB2/LINUXX8664] SQL0964C The transaction log for the database is full.
排查
事務(wù)日志已滿。
通過(guò) db2 => get db cfg 語(yǔ)句來(lái)查看日志文件的配置：
Log file size (4KB) (LOGFILSIZ) = 1024Number of primary log files (LOGPRIMARY) = 13Number of secondary log files (LOGSECOND) = 12
解決
把日志文件的大小和日志文件的數(shù)量擴(kuò)充。
db2 => update db cfg using LOGFILSIZ 10240DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.SQL1363W Database must be deactivated and reactivated before the changes toone or more of the configuration parameters will be effective.db2 => update db cfg using LOGPRIMARY 100DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.SQL1363W Database must be deactivated and reactivated before the changes toone or more of the configuration parameters will be effective.db2 => update db cfg using LOGSECOND 100DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.db2 =>
連接數(shù)據(jù)庫(kù)，分配共享內(nèi)容失敗，內(nèi)核限制
問(wèn)題
連接到數(shù)據(jù)庫(kù)時(shí)，出現(xiàn)如下提示
ERROR [57019] [IBM] SQL1084C The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached. SQLSTATE=57019
創(chuàng)建字段 varchar 設(shè)置過(guò)大
問(wèn)題
創(chuàng)建字段時(shí)，varchar 設(shè)置為 500 ，報(bào)錯(cuò)
SQL 錯(cuò)誤 [42727]: A table space could not be found with a page size of at least "8192" that authorization ID "DB2INST" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.16.53com.ibm.db2.jcc.am.SqlSyntaxErrorException: A table space could not be found with a page size of at least "8192" that authorization ID "DB2INST" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.16.53
- http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061057.html
- http://www.db2china.net/Question/39301
- http://www.west.cn/www/info/28275-1.htm
db2 => connect to necc_dbDatabase Connection InformationDatabase server = DB2/LINUXX8664 10.1.0SQL authorization ID = DB2INSTLocal database alias = NECC_DBdb2 => LIST TABLESPACES SHOW DETAILTablespaces for Current DatabaseTablespace ID = 0Name = SYSCATSPACEType = Database managed spaceContents = All permanent data. Regular table space.State = 0x0000Detailed explanation:NormalTotal pages = 32768Useable pages = 32764Used pages = 32244Free pages = 520High water mark (pages) = 32244Page size (bytes) = 4096Extent size (pages) = 4Prefetch size (pages) = 4Number of containers = 1Tablespace ID = 1Name = TEMPSPACE1Type = System managed spaceContents = System Temporary dataState = 0x0000Detailed explanation:NormalTotal pages = 1Useable pages = 1Used pages = 1Free pages = Not applicableHigh water mark (pages) = Not applicablePage size (bytes) = 4096Extent size (pages) = 32Prefetch size (pages) = 32Number of containers = 1Tablespace ID = 2Name = USERSPACE1Type = Database managed spaceContents = All permanent data. Large table space.State = 0x0000Detailed explanation:NormalTotal pages = 16384Useable pages = 16352Used pages = 14048Free pages = 2304High water mark (pages) = 14048Page size (bytes) = 4096Extent size (pages) = 32Prefetch size (pages) = 32Number of containers = 1Tablespace ID = 3Name = SYSTOOLSPACEType = Database managed spaceContents = All permanent data. Large table space.State = 0x0000Detailed explanation:NormalTotal pages = 8192Useable pages = 8188Used pages = 192Free pages = 7996High water mark (pages) = 192Page size (bytes) = 4096Extent size (pages) = 4Prefetch size (pages) = 4Number of containers = 1db2 =>
ALTER TABLESPACE tablespacename CONVERT TO LARGE
參考引用
- http://www-01.ibm.com/support/docview.wss?uid=swg21962486
- http://www.cnblogs.com/frankliiu-java/articles/2186343.html
以上內(nèi)容是否對(duì)您有幫助：
更多建議：