inix数据库常用备份方法 - 总结.doc

informix数据库常用备份方法1.1 数据库导出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_dbinformix /opt/informix /db_backlsdbexport.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导入数据失败l 错误现象如果导入的时候直接执行dbimport test_db这样的话，从建数据库后，就会出现如下错误（原因是直接恢复后，数据库日志级别不是buffer log这样就会出现下面的错误，利用-d指定恢复到哪个数据库空间，不指定的话，默认是rootdbs）：16:45:47Error: Transaction begin failed.sqlca.sqlcode = -25616: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: (java.sql.SQLException: Transactions not supported)l 解决方法方法1：导入数据库的时候，就直接指定日志类别-l buffered；方法2：通过ontape s B test_db L 0 修改日志级别为buffer log； FAQ2：dbimport导入如果存在同名的数据库则会失败l 错误现象informix /opt/informix /db_back_01dbimport test_db* create database330 - Cannot create or rename database.100 - ISAM error: duplicate value for a record with unique key.l 解决方法利用informix用户登录删除该重名数据库 FAQ3：dbexport导出数据库失败l 错误现象informix /opt/informix /db_back_01dbexport test_db-425 - Database is currently opened by another user.-107 - ISAM error: record is locked.l 解决方法：停止所有和test_db数据库相关的应用，如果还出现上面的问题，就使用informix用户登录，查看连接该数据库的session，然后强制停止。informix :/opt/informixonstat -g sql |grep test_db401320 - test_db CR Wait 5 0 0 9.03 Off401315 - test_db CR Not Wait 0 0 9.03 Off杀死对应的Session进程informix :/opt/informixonmode -z 401320informix :/opt/informixonmode -z 401315然后再用onstat -g sql |grep test_db查看，直到杀死的没有session为止。1.2 informix数据库0级备份及恢复说明：利用dbexport备份数据库时有个缺点就是要把所有和数据库操作相关的应用都要停止，而0级备份则不需要停应用。但是dbexport可以导出某个单独的数据库，而0级备份会备份该informix服务器上所有的数据库。 0级备份步骤第1步：对于数据库备份最好单独建立文件系统jfs2scp2/#lsfsName 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 /informixbacktouch informixdate +%Y%m%d会创建一个：informix20080528 的文件chown informix:informix informix20080528chmod 777 informix20080528第3步：修改onconfig文件（informix用户登录）cd /opt/informix/etc/vi onconfig修改：TAPEDEV为上面创建的文件全路径，然后保存# System Archive Tape Device#TAPEDEV /dev/null # Tape device pathTAPEDEV /informixback/informix20080528TAPEBLK 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 DeviceLTAPEDEV /opt/informix/lp # Log tape device path#LTAPEDEV /dev/null # Log tape device path#LTAPEDEV /opt/informix/temp/testsLTAPEBLK 64 # Log tape block size (Kbytes)LTAPESIZE 8192000 # Max amount of data to put on log tape (Kbytes)第4步：执行0级备份注意：要保证备份文件所在文件系统有足够空间ontape -s -L 0Please 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 DeviceTAPEDEV /dev/null # Tape device path#TAPEDEV /informixback/informix20080528TAPEBLK 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 DeviceLTAPEDEV /opt/informix/lp # Log tape device path#LTAPEDEV /dev/null # Log tape device path#LTAPEDEV /opt/informix/temp/testsLTAPEBLK 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 -kyonstat -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 pathTAPEDEV /informixback/informix20080528TAPEBLK 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 DeviceLTAPEDEV /opt/informix/lp # Log tape device path#LTAPEDEV /dev/null # Log tape device path#LTAPEDEV /opt/informix/temp/testsLTAPEBLK 64 # Log tape block size (Kbytes)LTAPESIZE 8192000 # Max amount of data to put on log tape (Kbytes)第3步：执行恢复（informix用户登录）ontape -rPlease mount tape 1 on /informixback/informix20080528 and press Return to continue . Archive Tape InformationTape 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 Tape size (in k): 8890888 Tape number in series: 1 Spaces to restore:1 rootdbs 2 logdbs 3 phydbs 4 workdbs Archive InformationIBM Informix Dynamic Server Copyright(C) 1986-2004 IBM Informix Software, Inc.Initialization Time 08/27/2007 21:54:22System Page Size 4096Version 13Archive CheckPoint Time 02/20/2008 09:15:36Dbspacesnumber flags fchunk nchunks flags owner name1 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 Chunkschk/dbs offset size free bpages flags pathname1 1 10 31750 29706 PO- /dev/rlvrootdbs2 2 10 275000 24947 PO- /dev/rlvlogdbs3 3 10 255750 230697 PO- /dev/rlvphydbs4 4 10 511750 83 PO- /dev/rlvworkdbs15 4 10 500000 310934 PO- /dev/rlvworkdbs26 5 10 124975 124922 PO- /dev/rlvtempdbsContinue restore? (y/n)yDo you want to back up the logs? (y/n)nRestore a level 1 archive (y/n) nDo you want to restore log tapes? (y/n)n/opt/informix/bin/onmode -syProgram over.onstat -IBM Informix Dynamic Server Version 9.40.FC7W4 - Fast Recovery - Up 00:08:02 - 2245392 Kbytesonstat -IBM Informix Dynamic Server Version 9.40.FC7W4 - Quiescent - Up 00:08:55 - 2245392 Kbytesonmode -monstat -IBM Informix Dynamic Server Version 9.40.FC7W4 - On-Line - Up 00:09:28 - 2245392 Kbytesexit第4步：0级恢复结束后恢复onconfig文件cd /opt/informix/etc/vi onconfig修改：TAPEDEV为上面创建的文件全路径，然后保存# System Archive Tape DeviceTAPEDEV /dev/null # Tape device path#TAPEDEV /informixback/informix20080528TAPEBLK 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 DeviceLTAPEDEV /opt/informix/lp # Log tape device path#LTAPEDEV /dev/null # Log tape device path#LTAPEDE