Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Проблемы с БД после увеличения bufferpool'ов  [new]
dvska
Member

Откуда: Земля
Сообщений: 75
Уважаемые коллеги, посоветуйте что можно сделать для восстановления БД

База на DB2/Win32 v8.1.18.980 FP18. После необдуманного увеличения bufferpool'ов она не стартует,
актуального backup'а нет.

db2 => connect to nsi27
SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

db2 => get db config for nsi27

Database Configuration for Database nsi27

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = RU
Database code page = 1251
Database code set = 1251
Database country/region code = 7
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =
Database page size = 4096

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = RECOVERY
User exit for logging status = NO

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 3000
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 665
Log buffer size (4KB) (LOGBUFSZ) = 98
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 84088
Buffer pool size (pages) (BUFFPAGE) = 5000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 0
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 2075

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 11595
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 1024

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 473
SQL statement heap (4KB) (STMTHEAP) = 8092
Default application heap (4KB) (APPLHEAPSZ) = 50000
Package cache size (4KB) (PCKCACHESZ) = 859
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 60
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

Track modified pages (TRACKMOD) = OFF

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 15
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 64000
Number of primary log files (LOGPRIMARY) = 8
Number of secondary log files (LOGSECOND) = 4
Changed path to log files (NEWLOGPATH) =
Path to log files = D:\NSI27_DB_Tablespaces\newlogpath\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) = D:\NSI27_Log\NODE0000\NODE0000\
First active log file = S0000119.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 120
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

First log archive method (LOGARCHMETH1) = LOGRETAIN
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
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (ACCESS)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

———————————————————————————————————————————

в логе

2010-04-05-18.04.03.828000+180 E19876441H858      LEVEL: Event
PID : 1484 TID : 1776 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911
MESSAGE : ADM7513W Database manager has started.
START : DB2 DBM
DATA #1 : Build Level, 124 bytes
Instance "DB2" uses "32" bits and DB2 code release "SQL08029"
with level identifier "030A0106".
Informational tokens are "DB2 v8.1.18.980", "s090715", "WR21443", FixPak "18".
DATA #2 : System Info, 1564 bytes
System: WIN32_NT HOSTNAME Service Pack 2 5.2 x86 Family 6, model 15, stepping 11
CPU: total:2 online:2 Cores per socket:2 Threading degree per core:1
Physical Memory(MB): total:3328 free:2413 available:1917
Virtual Memory(MB): total:5225 free:6824
Swap Memory(MB): total:1897 free:4411

2010-04-05-18.06.34.312000+180 I19877301H464 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, base sys utilities, sqleFirstConnect, probe:15
RETCODE : ZRC=0x820F0004=-2112946172=SQLO_MEM_SIZE "Mem Mgt invalid size"
DIA8563C An invalid memory size was requested.

2010-04-05-18.06.34.312000+180 I19877767H749 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, base sys utilities, sqleFirstConnect, probe:16
DATA #1 : String, 297 bytes
Failed to allocate the desired database shared memory set.
Check to make sure the configured DATABASE_MEMORY + overflow
does not exceed the maximum shared memory on the system. Will
attempt to allocate the minimum possible db shared memory size.
Desired database shared memory set size is (bytes):
DATA #2 : Hexdump, 4 bytes
0x02178E74 : 0080 FFFF ....

2010-04-05-18.06.34.375000+180 I19878518H361 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, base sys utilities, sqledint, probe:30
MESSAGE : Crash Recovery is needed.

2010-04-05-18.06.34.375000+180 E19878881H684 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:245
MESSAGE : ADM6015W EXTENDED STORAGE is disabled for the database but one or
more buffer pools are configured to use it. If EXTENDED STORAGE may
be enabled in the future then it is recommended that this message be
ignored. EXTENDED STORAGE can be enabled by changing the
NUM_ESTORE_SEGS database parameter.

2010-04-05-18.06.34.390000+180 I19879567H440 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:245
MESSAGE : estoreSize =
DATA #1 : Hexdump, 4 bytes
0x02173160 : 0000 0000 ....

2010-04-05-18.06.35.250000+180 E19880009H627 LEVEL: Severe (OS)
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, oper system services, getPrivateChunksFromOs, probe:100
CALLED : OS, -, VirtualAlloc
OSERR : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted. Or ulimit is
not high enough.
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
8388608

2010-04-05-18.06.35.281000+180 E19880638H627 LEVEL: Severe (OS)
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, oper system services, getPrivateChunksFromOs, probe:100
CALLED : OS, -, VirtualAlloc
OSERR : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted. Or ulimit is
not high enough.
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
8388608

2010-04-05-18.06.35.281000+180 E19881267H627 LEVEL: Severe (OS)
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, oper system services, getPrivateChunksFromOs, probe:100
CALLED : OS, -, VirtualAlloc
OSERR : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted. Or ulimit is
not high enough.
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
8388608

2010-04-05-18.06.35.281000+180 E19881896H627 LEVEL: Severe (OS)
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, oper system services, getPrivateChunksFromOs, probe:100
CALLED : OS, -, VirtualAlloc
OSERR : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted. Or ulimit is
not high enough.
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
8388608

2010-04-05-18.06.35.359000+180 I19882525H462 LEVEL: Severe
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:500
MESSAGE : BufferPool memory allocation failed
DATA #1 : Hexdump, 4 bytes
0x02173624 : 1000 0F8B ....

2010-04-05-18.06.35.640000+180 E19882989H714 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:2
MESSAGE : ADM6073W The table space "SYSCATSPACE" (ID "0") is configured to use
buffer pool ID "1", but this buffer pool is not active at this time.
In the interim the table space will use buffer pool ID "4096". The
inactive buffer pool should become available at next database startup
provided that the required memory is available.

... и так про все table space'ы

2010-04-05-18.06.35.843000+180 I19891566H519 LEVEL: Severe
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, buffer pool services, sqlbinit, probe:620
DATA #1 : String, 70 bytes
Database will come up with hidden buffer pools.
totalBufferPoolPages:
DATA #2 : Hexdump, 8 bytes
0x02173610 : 4000 0000 0000 0000 @.......

2010-04-05-18.06.36.000000+180 I19892087H425 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, recovery manager, sqlpresr, probe:410
MESSAGE : Crash recovery started. LowtranLSN 0000002D811AFCA5 MinbuffLSN
0000002D811A8C4C

2010-04-05-18.06.36.031000+180 I19892514H404 LEVEL: Warning
PID : 1484 TID : 1516 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, recovery manager, sqlprecm, probe:2000
MESSAGE : Using parallel recovery with 3 agents 12 QSets 36 queues and 6 chunks

2010-04-05-18.06.44.781000+180 E19892920H471 LEVEL: Error
PID : 1484 TID : 3952 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:122
MESSAGE : ADM6019E All pages in buffer pool "IBMHIDDENBP4K" (ID "4096") are in
use. Refer to the documentation for SQLCODE -1218.

2010-04-05-18.06.44.781000+180 I19893393H384 LEVEL: Severe
PID : 1484 TID : 3952 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
DATA #1 : String, 93 bytes
Could not fix page for objID=183, tbspaceID=2, objType=1, parentObjID=183 parentTbspaceID=2.

2010-04-05-18.06.44.796000+180 I19893779H335 LEVEL: Severe
PID : 1484 TID : 3952 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
DATA #1 : String, 44 bytes
Current size of bufferpool #4096: 16 slots.

2010-04-05-18.06.44.796000+180 I19894116H411 LEVEL: Severe
PID : 1484 TID : 3952 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : NSI27
APPHDL : 0-7 APPID: *LOCAL.DB2.100405150634
FUNCTION: DB2 UDB, index manager, sqlifix, probe:371
RETCODE : ZRC=0x8502002C=-2063466452=SQLB_BPFULL
"no available buffer pool pages"
———————————————————————————————————————————


db2 => UPDATE DATABASE CONFIGURATION for nsi27 using ESTORE_SEG_SZ 50000
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 => UPDATE DATABASE CONFIGURATION for nsi27 using num_estore_segs 128
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

не помогло

Что можно сделать для восстановления БД, или как бы вытянуть структуры таблиц + данные?
Спасибо!
6 апр 10, 12:57    [8585262]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с БД после увеличения bufferpool'ов  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4948
dvska,

--установить костыль
db2set DB2_OVERRIDE_BPF=32
db2 connect to nsi27
--уменьшить размер буфера:
db2 alter bufferpool ... size ...
db2 connect reset
db2set DB2_OVERRIDE_BPF=
--проверить подключение без костыля
db2 connect to nsi27
6 апр 10, 13:56    [8585795]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с БД после увеличения bufferpool'ов  [new]
dvska
Member

Откуда: Земля
Сообщений: 75
Mark Barinstein,

Огромное-преогромное БЛАГОДАРЮ, помогло!
6 апр 10, 14:22    [8586070]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с БД после увеличения bufferpool'ов  [new]
db2_db2_db2
Guest
нескромный вопрос,
(APPLHEAPSZ) = 50000 - не много ли?
как расчитали?
28 май 10, 20:15    [8854418]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с БД после увеличения bufferpool'ов  [new]
dvska
Member

Откуда: Земля
Сообщений: 75
db2_db2_db2
нескромный вопрос,
(APPLHEAPSZ) = 50000 - не много ли?
как расчитали?
Уж не припомню, кто, когда и по какой причине выставил такое значение..
12 июл 10, 19:28    [9091147]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить