|
Проблемы с БД после увеличения bufferpool'ов
#36563123
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Земля
Сообщения: 59
|
|
Уважаемые коллеги, посоветуйте что можно сделать для восстановления БД
База на DB2/Win32 v8.1.18.980 FP18. После необдуманного увеличения bufferpool'ов она не стартует,
актуального backup'а нет.
db2 => connect to nsi27
SQL1042C An unexpected system error occurred. SQLSTATE=58004
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
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
———————————————————————————————————————————
в логе
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
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.
не помогло
Что можно сделать для восстановления БД, или как бы вытянуть структуры таблиц + данные?
Спасибо! |
|
|
