An example of DB2 data recovery: SQL0928N

An example of DB2 data recovery: SQL0928N

The environment is AIX 6.1 and DB2 9.7.0.7.
First, check whether the db2 master process exists.
 
Ps-ef | grep db2sys
 
If the database does not exist, use db2start to open the database.
 
The backup media is a cold backup data source,
 
Cd to the directory where the media is located:
 
Cd/home/db2inst1/backup
 
Use the redirect parameter to change the directory:
 
1.> db2 restore db xxx from. replace existing redirect without rolling forward
 

SQL1277W A redirected restore operation is being stored med. Table space
 
Configuration can now be viewed and table spaces that do not use automatic
 
Storage can have their containers reconfigured.
 
DB20000I The restore database command completed successfully.
 

 

List xxx database container details:
 
2.> db2pd-db xxx-tablespaces
 

Database Partition 0 -- Database xxx -- Active -- Up 0 days 00:00:16 -- Date 2014-05-07-13.06.01.022471
 

 

Tablespace Configuration:
 
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe lastconsecpname
 
0x0700000103DED1C0 0 SMS Regular 4096 32 Yes 32 1 1 On 1 0 31 SYSCATSPACE
 
0x0700000103DF06E0 1 SMS messages MP 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE1
 
0x0700000103DF3C00 2 SMS Regular 4096 32 Yes 32 1 1 On 1 0 31 USERSPACE1
 
0x0700000103DF7360 3 SMS Regular 4096 32 Yes 32 1 1 On 1 0 31 then oolspace
 
0x0700000103DFAAC0 4 SMS UsrTmp 4096 32 Yes 32 1 1 On 1 0 31 then oolstmpspace
 
0x0700000103DFE220 5 DMS Large 4096 32 No 32 1 1 Off 1 0 31 CDTS
 
0x0700000105280080 6 DMS Large 4096 32 No 64 1 1 Off 1 0 31 TSASNCA
 
0x0700000105281560 7 DMS Large 4096 32 No 64 1 1 Off 1 0 31 TSASNUOW
 
0x0700000105282A40 8 SMS Regular 8192 32 No 32 2 Off 1 0 31 CLOBTBS1
 

 

Tablespace Statistics:
 
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
 
0x0700000103DED1C0 0 0 0 0 0 0 0 0 0 0x02001100 1373014041 0 No
 
0x0700000103DF06E0 1 0 0 0 0 0 0 0 0 0x02001100 0 No
 
0x0700000103DF3C00 2 0 0 0 0 0 0 0 0 0x02001100 1398850212 0 No
 
0x0700000103DF7360 3 0 0 0 0 0 0 0 0 0x02001100 1373021563 0 No
 
0x0700000103DFAAC0 4 0 0 0 0 0 0 0 0 0x02001100 1301089227 0 No
 
0x0700000103DFE220 5 50000 49952 0 0 0 0 0x02001100 1374892759 0 No
 
0x0700000105280080 6 38400 38368 0 0 0 0 0x02001100 1374890249 0 No
 
0x0700000105281560 7 12800 12768 0 0 0 0 0x02001100 1374890249 0 No
 
0x0700000105282A40 8 0 0 0 0 0 0 0 0x02001100 1398850032 0 No
 

 

Tablespace Autoresize Statistics:
 
Address Id as ar InitSize IncSize IIP MaxSize LastResize LRF
 
0x0700000103DED1C0 0 No 0 No 0 None No
 
0x0700000103DF06E0 1 No 0 0 No 0 None No
 
0x0700000103DF3C00 2 No 0 0 No 0 None No
 
0x0700000103DF7360 3 No 0 0 No 0 None No
 
0x0700000103DFAAC0 4 No 0 0 No 0 None No
 
0x0700000103DFE220 5 No 0 0 No 0 None No
 
0x0700000105280080 6 No 0 No 0 None No
 
0x0700000105281560 7 No 0 No 0 None No
 
0x0700000105282A40 8 No 0 0 No 0 None No
 

 

Containers:
 
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
 
0x0700000103CFFA40 0 0 Path 0 0-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/SQLT0000.0
 
0x0700000103CFFC80 1 0 Path 0 0-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/SQLT0001.0
 
0x0700000103DF50E0 2 0 Path 0 0-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/SQLT0002.0
 
0x0700000103DF8840 3 0 Path 0 0-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/SYSTOOLSPACE
 
0x0700000103DFBFA0 4 0 Path 0 0-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/SYSTOOLSTMPSPACE
 
0x0700000103DFF700 5 0 File 50000 49952-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/CDTABLESPACE. DAT
 
0x0700000103DFF940 6 0 File 38400 38368-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/TSASNCA
 
0x0700000103DFFB80 7 0 File 12800 12768-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/TSASNUOW
 
0x0700000103DFFDC0 8 0 Path 0 0-0/db2sys/db2inst1/XXX/NODE0000/SQL00003/XXXCLOB
 

 

Create a storage path for the xxx database container in the/db2sys directory:
 
Cd/db2sys/db2inst1
 
Mkdir xxx
 
3. Change the storage path of containers and files:
 

Db2 "set tablespace containers for 0 using (Path '/db2sys/db2inst1/xxx/NODE0000/SQL00001/SQLT0000.0 ')"
 
Db2 "set tablespace containers for 1 using (Path '/db2sys/db2inst1/xxx/NODE0000/SQL00001/SQLT0001.0 ')"
 
Db2 "set tablespace containers for 2 using (Path '/db2sys/db2inst1/xxx/NODE0000/SQL00001/SQLT0002.0 ')"
 
Db2 "set tablespace containers for 3 using (Path '/db2sys/db2inst1/xxx/NODE0000/SQL00001/SYSTOOLSPACE ')"
 
Db2 "set tablespace containers for 4 using (Path '/db2sys/db2inst1/xxx/NODE0000/SQL00001/SYSTOOLSTMPSPACE ')"
 
Db2 "set tablespace containers for 5 using (File '/db2sys/db2inst1/xxx/NODE0000/SQL00001/CDTABLESPACE. DAT' 50000 )"
 
Db2 "set tablespace containers for 6 using (File '/db2sys/db2inst1/xxx/NODE0000/SQL00001/TSASNCA' 38400 )"
 
Db2 "set tablespace containers for 7 using (File '/db2sys/db2inst1/xxx/NODE0000/SQL00001/TSASNUOW' 12800 )"
 
Db2 "set tablespace containers for 8 using (Path '/db2sys/db2inst1/xxx/NODE0000/SQL00001/xxxclob ')"
 

 

Ps: One SQL0298N error is reported:
 
SQL0298N Bad container path. SQLSTATE = 428B2
 
The specified container type and container name are correct. I couldn't find a way at the end, and finally used a clumsy method;
 
Solution:
 
Delete xxx database:
 

> Db2 drop db xxx
 
Use db2 restore again to restore and add the to parameter:
 
> Db2 restore db xxx from. on/db2sys/db2inst1/xxx replace existing redirect without rolling forward
 
Repeat the preceding command to change the container and data file path.
 
After the path is changed, restore continue:
 
> Db2 restore db xxx continue
 
DB20000I The restore database command completed successfully.
 

 

Change archive path
 
> Db2 update db cfg for xxx using LOGARCHMETH1 disk:/db2sys/db2log/xxx
 

 

Restart the database to make the parameters take effect:
 
> Db2 stop
 
> Db2 start
 

 

Ps: SQL0332N during db2 connect
 

> Db2 connect to xxx
 
SQL0332N Character conversion from the source code page "1386" to the target
 
Code page "819" is not supported. SQLSTATE = 57017
 
Solution:
 
> Db2set
 
DB2COMM = tcpip
 

 

> Db2set DB2CODEPAGE = 1386
 

 

> Db2set
 

DB2COMM = tcpip
 
DB2CODEPAGE = 1386
 
> Db2 terminate
 

 

> Db2 connect to cnap
 

 

Database Connection Information
 

 

Database server = DB2/AIX64 9.7.7
 
SQL authorization ID = DB2INST1
 
Local database alias = XXX
 

 

OK, DB2 is restored.

This article permanently updates the link address: