Author Archives: 惜分飞

ASM DISK HEADER 备份与恢复

Posted on by

最近遇到几次ASM DATA HEADER出问题导致DISK GROUP 不能正常的MOUNT,是的数据库不能正常工作,从来带来了无穷的麻烦,这个时候心想,如果我做了ASM DATA HEADER的备份该多好啊,可惜世上没有后悔药,建议大家检查下自己的ASM库,ASM HEADER是否已经做了备份,如果没有请及时处理下.这里试验提供了dd和kfed备份和恢复ASM HEADER
查询ASM相关信息

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE        TOTAL_MB    FREE_MB
------------ ------------------------------ ---------- ---------- ----------
           1 DATA                           MOUNTED          2046       1314
           2 XIFENFEI                       MOUNTED          6141       4378

SQL> select group_number,DISK_NUMBER,PATH,STATE from v$asm_disk;

GROUP_NUMBER DISK_NUMBER PATH                           STATE
------------ ----------- ------------------------------ ----------------
           1           1 /dev/oracleasm/disks/VOL2      NORMAL
           1           0 /dev/oracleasm/disks/VOL1      NORMAL
           2           1 /dev/oracleasm/disks/VOL4      NORMAL
           2           0 /dev/oracleasm/disks/VOL3      NORMAL

找出ASM DISK对应磁盘分区

[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL1
Disk "VOL1" is a valid ASM disk on device [8,33]
[grid@rac1 ~]$ ll /dev |grep 8|grep 33
brw-r----- 1 root disk     8,  33 Apr 17 11:37 sdc1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL2
Disk "VOL2" is a valid ASM disk on device [8,34]
[grid@rac1 ~]$ ll /dev |grep 8|grep 34
brw-r----- 1 root disk     8,  34 Apr 17 11:37 sdc2
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 17
brw-r----- 1 root disk     8,  17 Apr 17 11:37 sdb1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 18
brw-r----- 1 root disk     8,  18 Apr 17 11:37 sdb2

备份ASM HEADER信息(备份XIFENFEI磁盘组)

--dd方式备份
[root@rac1 backup_asmheader]# dd if=/dev/sdb1 of=vol3header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000143581 seconds, 28.5 MB/s
[root@rac1 backup_asmheader]# dd if=/dev/sdb2 of=vol4header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000147727 seconds, 27.7 MB/s

--kfed方式备份
[grid@rac1 backup_asmheader]$ kfed read /dev/oracleasm/disks/VOL3 text=vol3header.txt
[grid@rac1 backup_asmheader]$ more vol3header.txt 
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                   198826195 ; 0x00c: 0x0bd9d8d3
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:     ORCLDISKVOL3 ; 0x000: length=12
kfdhdb.driver.reserved[0]:    860639062 ; 0x008: 0x334c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969259 ; 0x0b0: HOUR=0xb DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           2707277824 ; 0x0b4: USEC=0x0 MSEC=0x372 SECS=0x15 MINS=0x28
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    2353 ; 0x0c4: 0x00000931
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0x00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[5]:                   0 ; 0x110: 0x00000000
kfdhdb.ub4spare[6]:                   0 ; 0x114: 0x00000000
kfdhdb.ub4spare[7]:                   0 ; 0x118: 0x00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[9]:                   0 ; 0x120: 0x00000000
kfdhdb.ub4spare[10]:                  0 ; 0x124: 0x00000000
kfdhdb.ub4spare[11]:                  0 ; 0x128: 0x00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[13]:                  0 ; 0x130: 0x00000000
kfdhdb.ub4spare[14]:                  0 ; 0x134: 0x00000000
kfdhdb.ub4spare[15]:                  0 ; 0x138: 0x00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000
kfdhdb.ub4spare[18]:                  0 ; 0x144: 0x00000000
kfdhdb.ub4spare[19]:                  0 ; 0x148: 0x00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[21]:                  0 ; 0x150: 0x00000000
kfdhdb.ub4spare[22]:                  0 ; 0x154: 0x00000000
kfdhdb.ub4spare[23]:                  0 ; 0x158: 0x00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[25]:                  0 ; 0x160: 0x00000000
kfdhdb.ub4spare[26]:                  0 ; 0x164: 0x00000000
kfdhdb.ub4spare[27]:                  0 ; 0x168: 0x00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[29]:                  0 ; 0x170: 0x00000000
kfdhdb.ub4spare[30]:                  0 ; 0x174: 0x00000000
kfdhdb.ub4spare[31]:                  0 ; 0x178: 0x00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[33]:                  0 ; 0x180: 0x00000000
kfdhdb.ub4spare[34]:                  0 ; 0x184: 0x00000000
kfdhdb.ub4spare[35]:                  0 ; 0x188: 0x00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[37]:                  0 ; 0x190: 0x00000000
kfdhdb.ub4spare[38]:                  0 ; 0x194: 0x00000000
kfdhdb.ub4spare[39]:                  0 ; 0x198: 0x00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000
[grid@rac1 backup_asmheader]$ kfed read /dev/oracleasm/disks/VOL4 text=vol4header.txt

破坏VOL3 ASM磁盘

[root@rac1 backup_asmheader]# dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.00165264 seconds, 2.5 MB/s

[grid@rac1 backup_asmheader]$ kfed read /dev/oracleasm/disks/VOL3
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
B4BFE200 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

启动ASM

[grid@rac1 backup_asmheader]$ sqlplus / as sysoper

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 17 12:29:42 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

恢复ASM DATE HEADER

--dd 恢复
[root@rac1 backup_asmheader]# dd if=vol3header.dd of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.00197913 seconds, 2.1 MB/s

--kfed 恢复
[grid@rac1 backup_asmheader]$ kfed merge /dev/oracleasm/disks/VOL3 text=vol3header.txt

MOUNT DISKGROUP

SQL> alter diskgroup xifenfei mount;

Diskgroup altered.

找出调用DBLINK的SESSION信息

Posted on by

怎么找出通过dblink访问的用户信息?这个问题困惑了很久,今天在朋友的帮助下,终于通过基表实现了这个功能,记录下来

SELECT /*+ ORDERED */
 S.KSUSEMNM "O_HOSTNAME",S.KSUSEPID "O_SPID",--操作dblink用户信息
 G.K2GTITID_ORA "O_TXID",
 S.INDX "S_SID",S.KSUSESER "S_SERIAL#",--dblink session信息
 DECODE(BITAND(KSUSEIDL, 11),
               1,
               'ACTIVE',
               0,
               DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
               2,
               'SNIPED',
               3,
               'SNIPED',
               'KILLED') "S_STATUS",
                S.KSUUDNAM "DBLINK_USER"
  FROM SYS.X$K2GTE G, SYS.X$KTCXB T, SYS.X$KSUSE S
 WHERE G.K2GTDXCB = T.KTCXBXBA
   AND G.K2GTDSES = T.KTCXBSES
   AND S.ADDR = G.K2GTDSES;

查询结果如下

O_HOSTNAME             O_SPID       O_TXID                      S_SID  S_SERIAL#  S_STATUS   DBLINK_USER
----------------------- ---------- -----------------------  ---------- ---------- --------  ------------
localhost.localdomain  2571         MCRM.757120d4.8.31.21425     5462         20  INACTIVE   TEST1
localhost.localdomain  1021         MCRM.757120d4.6.17.21298     5467        664  INACTIVE   TEST
localhost.localdomain  1385         MCRM.757120d4.10.2.16138     5473        155  INACTIVE   TEST

如果需要获取更加信息的信息,可以通过结合两端的v$session和v$process视图获得

ORA-01075: you are currently logged on

Posted on by

rm删除文件后alert中出现错误

Mon Apr 16 21:36:59 2012
Errors in file /home/oracle/oracle/admin/XGS/bdump/xgs_j000_1349.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

数据库进程还在运行

oracle     779     1  0 21:21 ?        00:00:01 ora_pmon_XGS
oracle     781     1  0 21:21 ?        00:00:10 ora_psp0_XGS
oracle     783     1  0 21:21 ?        00:00:00 ora_mman_XGS
oracle     785     1  0 21:21 ?        00:00:00 ora_dbw0_XGS
oracle     787     1  0 21:21 ?        00:00:00 ora_lgwr_XGS
oracle     789     1  0 21:21 ?        00:00:00 ora_ckpt_XGS
oracle     791     1  0 21:21 ?        00:00:00 ora_smon_XGS
oracle     793     1  0 21:21 ?        00:00:00 ora_reco_XGS
oracle     795     1  0 21:21 ?        00:00:00 ora_cjq0_XGS
oracle     797     1  0 21:21 ?        00:00:01 ora_mmon_XGS
oracle     799     1  0 21:21 ?        00:00:00 ora_mmnl_XGS
oracle     801     1  0 21:21 ?        00:00:00 ora_d000_XGS
oracle     803     1  0 21:21 ?        00:00:00 ora_s000_XGS

尝试登陆数据库

[oracle@dbtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 16 21:40:06 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys
Enter password: 
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oracle/oradata/XGS/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

问题原因

Internal triggers are trying to fire but one or more datafiles for the SYSAUX tablespace is offline, 
this is preventing the database from allowing new connections.

NOTE: At this point, you cannot connect to verify the status in V$DATAFILE, 
but you may find an indication of the offline datafile(s) in the alert.log file.

For example:
In one case, a media problem occurred which made disks unavailable.
This caused several files to be taken offline automatically including a SYSAUX datafile.

解决方法
kill进程,重启数据库到mount状态,然后根据特定情况恢复数据库或者online相关文件

DB2数据迁移之db2lock/db2move

Posted on by

1.模拟带有identity表

[db2inst1@xifenfei ~]$ db2 "create table t_xff(xid smallint not null generated always as identity 
> (start with 1,increment by 1),x_name varchar(200)) in ts_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('www.orasos.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from t_ff"
SQL0204N  "DB2INST1.T_FF" is an undefined name.  SQLSTATE=42704
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"

XID    X_NAME           
------ -------------
     1 www.orasos.com 
     2 XIFENFEI         
     3 xifenfei         

  3 record(s) selected.

2.导出表结构

[db2inst1@xifenfei ~]$ mkdir move_s
[db2inst1@xifenfei ~]$ cd move_s/
[db2inst1@xifenfei move_s]$ db2look -d sample -e -l -o db2_sample.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- Output is sent to file: db2_sample.ddl

3.导出数据

[db2inst1@xifenfei move_s]$ db2move sample export

Application code page not determined, using ANSI codepage 1208

*****  DB2MOVE  *****

Action:  EXPORT

Start time:  Thu Apr  5 20:21:28 2012


Connecting to database SAMPLE ... successful!  Server : DB2 Common Server V9.5.9

Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!

Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!

EXPORT:     18 rows from table "DB2INST1"."ACT"
EXPORT:      5 rows from table "DB2INST1"."CL_SCHED"
EXPORT:     14 rows from table "DB2INST1"."DEPARTMENT"
EXPORT:     42 rows from table "DB2INST1"."EMPLOYEE"
EXPORT:  10000 rows from table "DB2INST1"."EMPMDC"
EXPORT:     73 rows from table "DB2INST1"."EMPPROJACT"
EXPORT:      8 rows from table "DB2INST1"."EMP_PHOTO"
EXPORT:      8 rows from table "DB2INST1"."EMP_RESUME"
EXPORT:    145 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT:      0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT:      3 rows from table "DB2INST1"."IN_TRAY"
EXPORT:      8 rows from table "DB2INST1"."ORG"
EXPORT:      5 rows from table "SYSTOOLS"."POLICY"
EXPORT:     65 rows from table "DB2INST1"."PROJACT"
EXPORT:     20 rows from table "DB2INST1"."PROJECT"
EXPORT:     41 rows from table "DB2INST1"."SALES"
EXPORT:     35 rows from table "DB2INST1"."STAFF"
EXPORT:     35 rows from table "DB2INST1"."STAFFG"
EXPORT:      3 rows from table "DB2INST1"."T_XFF"

Disconnecting from database ... successful!

End time:  Thu Apr  5 20:21:32 2012

4.目标端创建数据库

C:\Windows\system32>db2 "create db db_XFF pagesize 8 k"
DB20000I  CREATE DATABASE命令成功完成。

5.目标端创建对象

C:\Windows\system32>DB2 -tvf D:\move_s\db2_sample.ddl -l d:\xifenfei.log

--检查xifenfei.log文件,发现错误,手工修复

6.导入数据文件

D:\move_s>db2move db_xff load

*****  DB2MOVE  *****

Action:  LOAD

Start time:  Sun Apr 15 23:00:17 2012


Connecting to database DB_XFF ... successful!  Server : DB2 Common Server V9.5.0

Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2COMMON.BND ... successful!

Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2MOVE.BND ... successful!

* LOAD:  table "DB2INST1"."ACT"                
  -Rows read:         18
  -Loaded:            18
  -Rejected:           0
  -Deleted:            0
  -Committed:         18

* LOAD:  table "DB2INST1"."CL_SCHED"           
  -Rows read:          5
  -Loaded:             5
  -Rejected:           0
  -Deleted:            0
  -Committed:          5

--中间很多记录省略

--发现identity表导入失败，需要手工处理
* LOAD:  table "DB2INST1"."T_XFF"              
***  WARNING 3107.  Check message file tab19.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  消息文件中至少有一条警告消息。


  -Rows read:          3
  -Loaded:             0
  -Rejected:           3
  -Deleted:            0
  -Committed:          3


Disconnecting from database ... successful!

End time:  Sun Apr 15 23:00:26 2012

7.重新导入identity表

D:\move_s>db2 connect to db_xff

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = XIFENFEI
 本地数据库别名       = DB_XFF
D:\move_s>DB2 "load from tab19.ixf of ixf modified by identityoverride insert into db2inst1.t_xff"
SQL3501W  由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。

SQL3551W  表至少包含实用程序将覆盖的一个 GENERATED ALWAYS 列。

SQL3109N  实用程序正在开始从文件 "D:\move_s\tab19.ixf" 装入数据。

SQL3500W  在时间 "2012-04-15 23:06:52.393775"，实用程序在开始 "LOAD"。

SQL3150N  PC/IXF 文件中的 H 记录具有产品 "DB2    02.00"，日期 "20120405"和时间 "202132"。

SQL3153N  PC/IXF 文件中的 T 记录具有名称 "tab19.ixf"，限定符 "" 和源 ""。

SQL3519W  开始装入一致点。输入记录数 = "0"。

SQL3520W  “装入一致点”成功。

SQL3110N  实用程序已完成处理。从输入文件读了 "3" 行。

SQL3519W  开始装入一致点。输入记录数 = "3"。

SQL3520W  “装入一致点”成功。

SQL3515W  在时间 "2012-04-15 23:06:52.451619"，实用程序已经完成了 "LOAD"。


读取行数         = 3
跳过行数         = 0
装入行数         = 3
拒绝行数         = 0
删除行数         = 0
落实行数         = 3

D:\move_s>db2 "select * from db2inst1.t_xff"

XID    X_NAME


------ ------------------------------
     1 www.orasos.com
     2 XIFENFEI
     3 xifenfei

  3 条记录已选择。
Posted in DB2 |

DB2数据迁移之load

Posted on by

一.load原理性知识
1.为什么要使用LOAD
load不需要写日志(或很少日志),不做检查约束和参照完整性约束,不触发Trigger,锁的时间比较短,因此特别适合大数据量的导入.

2.load过程分为4个阶段
load/build/delete/index copy.
load阶段是将源文件parser成物理数据存储的格式,直接装入到页中,而不通过db2引擎,load阶段会检查表定义,违背定义的数据不会装入到表中.
build阶段建议索引（如果装入表有索引的话）,会检查唯一性约束,违背了唯一性的数据会在delete阶段删除.
index copy阶段将index数据从指定的临时表空间拷贝到初始的表空间里.
index copy只适应于allow read access场景.load的4个阶段会记录在messages文件里.

3.load的offline和online
缺省情况下,load过程不允许其他应用访问表,即allow no access,或叫offline load（离线加载）.Allow read access,或叫online load(在线加载）,只有在load …insert into的时候才允许使用,其他应用读到的数据是加载前的数据,load … replace into会将数据先删除,再load,只能是离线加载.

4.load表的状态
load可能出现的几种状态,某一时刻可能会同时处于几种状态.只有当表是normal状态时,表才能进行正常的增删改查操作.
normal: 正常状态
set integrity pending: 如果目标表有check约束或reference约束,那么Load后此表处于set integrity pending,表明表有约束还未检查,稍后解释.
load in progress:load正在数据加载过程中.
load pending:数据提交前出现了故障,需要通过load..terminate,load..replace或load..restart解除暂挂状态
read access only:目标表数据是可以读的,当load时指定了allow read access,那表就会处于read access only状态
unavailable:表可能被删除了或从backup中恢复了.
unknown:通过load..query命令无法得知表的状态.

二.load试验测试
需要导入数据

[db2inst2@xifenfei ~]$ more /tmp/xifenfei.data 
10,"MANAGE","MANAGE/ADVISE"
20,"ECOST ","ESTIMATE COST"
30,,"DEFINE SPECS"
40,"LEADPR","LEAD PROGRAM/DESIGN"
50,"SPECS ",
60,"LOGIC ","DESCRIBE LOGIC"
70,"CODE  ","CODE PROGRAMS"
80,"TEST  ","TEST PROGRAMS"
90,"ADMQS ","ADM QUERY SYSTEM"
100,"TEACH ","TEACH CLASSES"
110,"COURSE","DEVELOP COURSES"
60,"STAFF ","PERS AND STAFFING"
130,"OPERAT","OPER COMPUTER SYS"
140,"MAINT ","MAINT SOFTWARE SYS"
150,"ADMSYS","ADM OPERATING SYS"
160,"ADMDB ","ADM DATA BASES"
170,"ADMDC ","ADM DATA COMM"
80,"DOC   ","DOCUMENT"

一共18条记录

创建目标表

[db2inst2@xifenfei ~]$ db2 "CREATE TABLE XIFENFEI_LOAD(ACTNO SMALLINT NOT NULL primary key,
> ACTKWD CHAR(6) NOT NULL,ACTDESC VARCHAR(20) NOT NULL)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
XIFENFEI_LOAD                   DB2INST2        T     2012-04-05-18.53.42.296503

  1 record(s) selected.

[db2inst2@xifenfei ~]$ db2 describe table xifenfei_load

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No    
ACTKWD                          SYSIBM    CHARACTER                    6     0 No    
ACTDESC                         SYSIBM    VARCHAR                     20     0 No    

  3 record(s) selected.

创建异常表

[db2inst2@xifenfei ~]$ db2 "create table xifenfei_exp like xifenfei_load"
DB20000I  The SQL command completed successfully.

[db2inst2@xifenfei ~]$ db2 "alter table xifenfei_exp add column ts timestamp
> add column msg clob(32k)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 describe table xifenfei_exp

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No    
ACTKWD                          SYSIBM    CHARACTER                    6     0 No    
ACTDESC                         SYSIBM    VARCHAR                     20     0 No    
TS                              SYSIBM    TIMESTAMP                   10     0 Yes   
MSG                             SYSIBM    CLOB                     32768     0 Yes   

  5 record(s) selected.

load导入数据

[db2inst2@xifenfei ~]$ db2 "load from /tmp/xifenfei.data of del modified by dumpfile=/tmp/xifenfei.dmp
messages xifenfei.msg insert into xifenfei_load for exception xifenfei_exp"

Number of rows read         = 18
Number of rows skipped      = 0
Number of rows loaded       = 16
Number of rows rejected     = 2
Number of rows deleted      = 2
Number of rows committed    = 18

SQL3107W  There is at least one warning message in the message file.

提示一共18条记录,成功了14条,拒绝2条,删除2条

查看表中记录

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_load"

ACTNO  ACTKWD ACTDESC             
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE       
    20 ECOST  ESTIMATE COST       
    40 LEADPR LEAD PROGRAM/DESIGN 
    60 LOGIC  DESCRIBE LOGIC      
    70 CODE   CODE PROGRAMS       
    80 TEST   TEST PROGRAMS       
    90 ADMQS  ADM QUERY SYSTEM    
   100 TEACH  TEACH CLASSES       
   110 COURSE DEVELOP COURSES     
   130 OPERAT OPER COMPUTER SYS   
   140 MAINT  MAINT SOFTWARE SYS  
   150 ADMSYS ADM OPERATING SYS   
   160 ADMDB  ADM DATA BASES      
   170 ADMDC  ADM DATA COMM       

  14 record(s) selected.

果真14条记录,和文件相比缺少4条记录

查看dump file

[db2inst2@xifenfei ~]$ more /tmp/xienfei.dmp.load.000 
30,,"DEFINE SPECS"
50,"SPECS ",

发现两条违背表定义记录,导入过程中,直接被拒绝的两条

查看异常表

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_exp"

ACTNO  ACTKWD ACTDESC              TS                         MSG  
------ ------ --------------------  
    60 STAFF  PERS AND STAFFING    2012-04-05-19.02.19.984440 00001I0000500001 
    80 DOC    DOCUMENT             2012-04-05-19.02.19.984440 00001I0000500001

发现两条违背唯一性约束记录,构建index的过程中删除