2022年informix数据库常用备份方法 .pdf

informix数据库常用备份方法informix 数据库常用备份方法数据库导出dbexport 和导入 dbimport 说明： dbexport用户导出和备份数据库，dbimport用于恢复dbexport备份的数据库；注意：dbimport导入数据库是，会创建数据库，所以不能存在与要导入重名的数据库。利用dbexport备份数据库时有个缺点就是要把所有和数据库操作相关的应用都要停止，而 0 级备份则不需要停应用。假如我的数据库为：test_db dbexport 用法：informix /opt/informix/db_backdbexport - Invalid option list. Usage: dbexport -X -c -q -d -ss -si -o | -t -b -s -f NOTE: arguments to dbexport are order independent. Dbexport 执行后，产生一个文件dbexport.out和一个文件夹test_db.exp 里面是 unl 文件 如果想备份数据库以及其中的数据：informix /opt/informix /db_backdbexport test_db informix /opt/informix /db_backls dbexport.out test_db.exp/ dbimport用法：informix /opt/informix dbimport - Invalid option list. Usage: dbimport -X -c -q -d -l buffered | -ansi -i | -t -b -s -f NOTE: must be a complete path arguments to dbimport are order independent 执行下面命令恢复数据库：informix /opt/informix/db_backdbimport test_db -d workdbs -l buffered FAQ1：dbimport 导入数据失败错误现象如果导入的时候直接执行dbimport test_db这样的话，从建数据库后，就会出现如下错误（原因是直接恢复后，数据库日志级别不是buffer log 这样就会出现下面的错误，利用-d 指定恢复到哪个数据库空间，不指定的话，默认是rootdbs ） ：16:45:47Error: Transaction begin failed.sqlca.sqlcode = -256 16:45:47Error: Connect to databaseevcscp_dbsmp4_online_netfailed, sqlcode=-256 或是：14:59:07,560 WARN TransactionImpl XAException: tx=TransactionImpl:XidImpl FormatId=257, GlobalId=smp4/1, BranchQual= errorCode=XA_UNKNOWN(0) org.jboss.resource.connectionmanager.JBossLocalXAException: Error trying to start local tx: ; - nested throwable: (org.jboss.resource.JBossResourceException: SQLException; - nested throwable: 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - (java.sql.SQLException: Transactions not supported) 解决方法方法 1：导入数据库的时候，就直接指定日志类别-l buffered ；方法 2：通过 ontape s B test_db L 0 修改日志级别为buffer log ；FAQ2：dbimport 导入如果存在同名的数据库则会失败错误现象informix /opt/informix /db_back_01dbimport test_db * create database 330 - Cannot create or rename database. 100 - ISAM error: duplicate value for a record with unique key. 解决方法利用 informix 用户登录删除该重名数据库FAQ3：dbexport 导出数据库失败错误现象informix /opt/informix /db_back_01dbexport test_db -425 - Database is currently opened by another user. -107 - ISAM error: record is locked. 解决方法：停止所有和test_db 数据库相关的应用，如果还出现上面的问题，就使用informix 用户登录，查看连接该数据库的session，然后强制停止。informix :/opt/informixonstat -g sql |grep test_db 401320 - test_db CR Wait 5 0 0 9.03 Off 401315 - test_db CR Not Wait 0 0 9.03 Off 杀死对应的Session 进程informix :/opt/informixonmode -z 401320 informix :/opt/informixonmode -z 401315 然后再用onstat -g sql |grep test_db查看，直到杀死的没有session 为止。informix 数据库 0 级备份及恢复说明：利用dbexport备份数据库时有个缺点就是要把所有和数据库操作相关的应用都要停止，而 0 级备份则不需要停应用。但是dbexport 可以导出某个单独的数据库，而0 级备份会备份该informix 服务器上所有的数据库。0 级备份步骤第 1 步：对于数据库备份最好单独建立文件系统jfs2 scp2/#lsfs Name Nodename Mount Pt VFS Size Options Auto Accounting /dev/lvscu - /tellin/scu jfs 36962304 - yes no /dev/fslv00 - /informixback jfs2 41943040 rw no no 第 2 步： touch 创建一个备份文件名root 用户登录，进入专门用于备份的文件系统cd /informixback touch informixdate +%Y%m%d 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 会创建一个：informix20080528 的文件chown informix:informix informix20080528 chmod 777 informix20080528 第 3 步：修改 onconfig 文件（ informix 用户登录）cd /opt/informix/etc/ vi onconfig 修改： TAPEDEV 为上面创建的文件全路径，然后保存# System Archive Tape Device #TAPEDEV /dev/null # Tape device path TAPEDEV /informixback/informix20080528 TAPEBLK 128 # Tape block size (Kbytes) #TAPESIZE 2048000 # Maximum amount of data to put on tape (Kbytes) TAPESIZE 8192000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /opt/informix/lp # Log tape device path #LTAPEDEV /dev/null # Log tape device path #LTAPEDEV /opt/informix/temp/tests LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 8192000 # Max amount of data to put on log tape (Kbytes) 第 4 步：执行 0 级备份 注意：要保证备份文件所在文件系统有足够空间 ontape -s -L 0 Please mount tape 1 on /informixback/informix20080528 and press Return to continue . 10 percent done. 20 percent done. 30 percent done. 第 5 步：备份结束后恢复onconfig 文件cd /opt/informix/etc/ vi onconfig 修改： TAPEDEV 为上面创建的文件全路径，然后保存# System Archive Tape Device TAPEDEV /dev/null # Tape device path #TAPEDEV /informixback/informix20080528 TAPEBLK 128 # Tape block size (Kbytes) #TAPESIZE 2048000 # Maximum amount of data to put on tape (Kbytes) TAPESIZE 8192000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /opt/informix/lp # Log tape device path #LTAPEDEV /dev/null # Log tape device path 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - #LTAPEDEV /opt/informix/temp/tests LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 8192000 # Max amount of data to put on log tape (Kbytes) 0 级恢复步骤注意： 0 级恢复首先要保证数据库有足够的workdbs 空间。第 1 步：停止 informix 数据库（ informix 登录）onmode -ky onstat - shared memory not initialized for INFORMIXSERVER smp2_online_net 第 2 步：修改 onconfig 文件（ informix 用户登录）cd /opt/informix/etc/ vi onconfig 修改： TAPEDEV 修改为 0 级备份文件全路径，然后保存# System Archive Tape Device #TAPEDEV /dev/null # Tape device path TAPEDEV /informixback/informix20080528 TAPEBLK 128 # Tape block size (Kbytes) #TAPESIZE 2048000 # Maximum amount of data to put on tape (Kbytes) TAPESIZE 8192000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /opt/informix/lp # Log tape device path #LTAPEDEV /dev/null # Log tape device path #LTAPEDEV /opt/informix/temp/tests LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 8192000 # Max amount of data to put on log tape (Kbytes) 第 3 步：执行恢复（informix 用户登录）ontape -r Please mount tape 1 on /informixback/informix20080528 and press Return to continue . Archive Tape Information Tape type: Archive Backup Tape Online version: IBM Informix Dynamic Server Version 9.40.FC7W4 Archive date: Wed Feb 20 09:15:35 2008 User id: informix Terminal id: /dev/pts/ 0 Archive level: 0 Tape device: /informixback/informix20080528 Tape blocksize (in k): 128 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - Tape size (in k): 8890888 Tape number in series: 1 Spaces to restore: 1 rootdbs 2 logdbs 3 phydbs 4 workdbs Archive Information IBM Informix Dynamic Server Copyright(C) 1986-2004 IBM Informix Software, Inc. Initialization Time 08/27/2007 21:54:22 System Page Size 4096 Version 13 Archive CheckPoint Time 02/20/2008 09:15:36 Dbspaces number flags fchunk nchunks flags owner name 1 20001 1 1 N informix rootdbs 2 20001 2 1 N informix logdbs 3 20001 3 1 N informix phydbs 4 20001 4 2 N informix workdbs 5 2001 6 1 N T informix tempdbs Chunks chk/dbs offset size free bpages flags pathname 1 1 10 31750 29706 PO- /dev/rlvrootdbs 2 2 10 275000 24947 PO- /dev/rlvlogdbs 3 3 10 255750 230697 PO- /dev/rlvphydbs 4 4 10 511750 83 PO- /dev/rlvworkdbs1 5 4 10 500000 310934 PO- /dev/rlvworkdbs2 6 5 10 124975 124922 PO- /dev/rlvtempdbs Continue restore? (y/n)y Do you want to back up the logs? (y/n)n Restore a level 1 archive (y/n) n Do you want to restore log tapes? (y/n)n /opt/informix/bin/onmode -sy 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - Program over. onstat - IBM Informix Dynamic Server Version 9.40.FC7W4 - Fast Recovery - Up 00:08:02 - 2245392 Kbytes onstat - IBM Informix Dynamic Server Version 9.40.FC7W4 - Quiescent - Up 00:08:55 - 2245392 Kbytes onmode -m onstat - IBM Informix Dynamic Server Version 9.40.FC7W4 - On-Line - Up 00:09:28 - 2245392 Kbytes exit 第 4 步： 0 级恢复结束后恢复onconfig 文件cd /opt/informix/etc/ vi onconfig 修改： TAPEDEV 为上面创建的文件全路径，然后保存# System Archive Tape Device TAPEDEV /dev/null # Tape device path #TAPEDEV /informixback/informix20080528 TAPEBLK 128 # Tape block size (Kbytes) #TAPESIZE 2048000 # Maximum amount of data to put on tape (Kbytes) TAPESIZE 8192000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /opt/informix/lp # Log tape device path #LTAPEDEV /dev/null # Log tape device path #LTAPEDEV /opt/informix/temp/tests LTAPEBLK 64 # Log tape block size (Kbytes) LTAPESIZE 8192000 # Max amount of data to put on log tape (Kbytes) FAQ1:文件系统空间不足code -1 errno 28 错误现象ontape -s -L 0 Please mount tape 1 on /informixback/informix20080528 and press Return to continue . 10 percent done. Archive failed - function write to tape failed code -1 errno 28 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - Program over. 原因分析及解决方案可以用 root 用户登录，执行下面命令chfs -a size=+1024M /informixback 说明：给文件系统/informixback增加 1G 空间， 2048M 就是 2G，这个地方根据需要修改 FAQ2:文件系统Limit 错误 code -1 errno 27 错误现象ontape -s -L 0 Please mount tape 1 on /tellin/scu/informixbak/informix20080528 and press Return to continue . 10 percent done. 20 percent done. Archive failed - function write to tape failed code -1 errno 27 Program over. 原因分析及解决方案文件系统为jfs，因为 jfs 文件系统不支持超过2G 的文件，所以引起数据库0 级备份失败。解决方法是单独创建大文件系统jfs2。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -