DB2恢复一例 SQL0928N
2024-09-04 16:08:14
环境是AIX 6.1。DB2版本号9.7.0.7
首先查看db2主进程是否存在
ps -ef|grep db2sys
若不存在使用db2start打开数据库
备份介质为冷备数据源,
cd 到介质所在文件夹下:
cd /home/db2inst1/backup
使用redirect參数来更改文件夹:
1.>db2 restore db xxx from . replace existing redirect without rolling forward
SQL1277W A redirected restore operation is being performed. 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.
列出xxx数据库容器具体信息:
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 LastConsecPg Name
0x0700000103DED1C0 0 SMS Regular 4096 32 Yes 32 1 1 On 1 0 31 SYSCATSPACE
0x0700000103DF06E0 1 SMS SysTmp 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 SYSTOOLSPACE
0x0700000103DFAAC0 4 SMS UsrTmp 4096 32 Yes 32 1 1 On 1 0 31 SYSTOOLSTMPSPACE
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 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 0x02001100 1373014041 0 No
0x0700000103DF06E0 1 0 0 0 0 0 0 0 0x02001100 0 0 No
0x0700000103DF3C00 2 0 0 0 0 0 0 0 0x02001100 1398850212 0 No
0x0700000103DF7360 3 0 0 0 0 0 0 0 0x02001100 1373021563 0 No
0x0700000103DFAAC0 4 0 0 0 0 0 0 0 0x02001100 1301089227 0 No
0x0700000103DFE220 5 50000 49952 0 0 0 0 0 0x02001100 1374892759 0 No
0x0700000105280080 6 38400 38368 0 0 0 0 0 0x02001100 1374890249 0 No
0x0700000105281560 7 12800 12768 0 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 No 0 0 No 0 None No
0x0700000103DF06E0 1 No No 0 0 No 0 None No
0x0700000103DF3C00 2 No No 0 0 No 0 None No
0x0700000103DF7360 3 No No 0 0 No 0 None No
0x0700000103DFAAC0 4 No No 0 0 No 0 None No
0x0700000103DFE220 5 No No 0 0 No 0 None No
0x0700000105280080 6 No No 0 0 No 0 None No
0x0700000105281560 7 No No 0 0 No 0 None No
0x0700000105282A40 8 No 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
在/db2sys文件夹下新建xxx数据库容器存储路径:
cd /db2sys/db2inst1
mkdir xxx
3.更改容器及文件的存储路径:
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:当中遇到一个SQL0298N的报错:
SQL0298N Bad container path. SQLSTATE=428B2
容器类型和容器名指定均正确。
查到最后查不出办法。最后使用一个比較笨拙的办法;
解决方法:
删除xxx数据库:
>db2 drop db xxx
又一次使用db2 restore 恢复并加入to參数:
>db2 restore db xxx from . on /db2sys/db2inst1/xxx replace existing redirect without rolling forward
在反复上面更改容器和数据文件路径的命令就OK了。
路径更改完成之后,restore continue:
>db2 restore db xxx continue
DB20000I The RESTORE DATABASE command completed successfully.
更改归档路径
>db2 update db cfg for xxx using LOGARCHMETH1 disk:/db2sys/db2log/xxx
重新启动数据库使參数生效:
>db2 stop
>db2 start
Ps:当中db2 connect 的时候遇到SQL0332N
>db2 connect to xxx
SQL0332N Character conversion from the source code page "1386" to the target
code page "819" is not supported. SQLSTATE=57017
解决方法:
>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 就算恢复完毕了。
最新文章
- C#6新特性,让你的代码更干净
- 99%的人都理解错了HTTP中GET与POST的区别(转载
- C++设计模式-Adapter适配器模式
- SharedPreferences 轻型的数据存储方式
- 使用ASP.Net WebAPI构建REST服务(六)——Self-Host
- Ultra Pull To Refresh下拉刷新
- 四种必须知道的Android屏幕自适应解决方案
- 我的VSTO之路(三):Word基本知识
- 数学(矩阵乘法,随机化算法):POJ 3318 Matrix Multiplication
- 两天快速开发一个自己的微信小程序
- Java HttpClient伪造请求之简易封装满足HTTP以及HTTPS请求
- 典型分布式系统分析:Bigtable
- Umbraco 7 支持Microsoft Word 2013 发布内容
- 【Leetcode】404. Sum of Left Leaves
- PoseNet: A Convolutional Network for Real-Time 6-DOF Camera Relocalization
- #001 Emmet的API图片
- Maven Oracle JDBC
- 深入探究jvm之类装载器
- Socket_FTP
- 让你的 Nginx 的 RTMP 直播具有统计某频道在线观看用户数量的功能
热门文章
- 用gulp+webpack构建多页应用——记一次Node多页应用的构建过程
- What is a fully qualified domain name (FQDN)?
- Unity3d / 3ds max 模型分享站点
- 2017.3.31 spring mvc教程(四)全局的异常处理
- 倍福TwinCAT(贝福Beckhoff)基础教程2.2 TwinCAT常见类型使用和转换_指针
- AnimatorStateInfo
- Php函数之end
- Ant Design Mobile 使用 rc-form
- Singleton单例模式是最简单的设计模式,它的主要作用是保证在程序执行生命周期中,使用了单类模式的类仅仅能有一个实例对象存在。
- 重启nginx后丢失nginx.pid的解决方法(转)