AIX常用命令之查看cpu/memory/swap/network/disk信息

一.查看CPU

--物理cpu数
1.prtconf
Processor Type: PowerPC_POWER5
Number Of Processors: 2
Processor Clock Speed: 2097 MHz
CPU Type: 64-bit
2.lsdev
-bash-3.00$ lsdev -Cc processor
proc0 Available 00-00 Processor
proc2 Available 00-02 Processor
--逻辑cpu数
3.bindprocessor
-bash-3.00$ bindprocessor -q
The available processors are:  0 1 2 3
4.vmstat
-bash-3.00$ vmstat
System configuration: lcpu=4 mem=1904MB
kthr    memory              page              faults        cpu
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 1  1 386273 51470   0   0   0   0    1   0 120 5221 937  1  2 97  1
5.smtctl
因为无权限执行,没有测试

二.查看内存

1.lsattr
-bash-3.00$  lsdev -Cc memory
L2cache0 Available  L2 Cache
mem0     Available  Memory
-bash-3.00$  lsattr -El L2cache0
size 1920 Size of L2 cache in Kbytes False
-bash-3.00$  lsattr -El mem0
goodsize 1904 Amount of usable physical memory in Mbytes False
size     1904 Total amount of physical memory in Mbytes  False
2.prtconf
-bash-3.00$ prtconf|grep Memory|grep -v +
Memory Size: 1904 MB
Good Memory Size: 1904 MB
3.vmstat
-bash-3.00$ vmstat
System configuration: lcpu=4 mem=1904MB
kthr    memory              page              faults        cpu
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 1  1 387550 50188   0   0   0   0    1   0 120 5221 937  1  2 97  1
4.topas
 MEMORY
Real,MB    1904
% Comp     76
% Noncomp  13
% Client   13

三.查看交互分区

1.lsps
-bash-3.00$ lsps -a
Page Space      Physical Volume   Volume Group    Size %Used Active  Auto  Type
hd6             hdisk0            rootvg         512MB    38   yes   yes    lv
2.lsps
-bash-3.00$  lsps -s
Total Paging Space   Percent Used
      512MB              38%
3.prtconf
Paging Space Information
        Total Paging Space: 512MB
        Percent Used: 38%
4.topas
PAGING SPACE
Size,MB     512
% Used     38
% Free     62

四.查看网络配置

--网络硬件信息
1.lsdev
-bash-3.00$ lsdev -Cc adapter|grep ent
ent0      Available 0D-08 2-Port 10/100/1000 Base-TX PCI-X Adapter (14108902)
ent1      Available 0D-09 2-Port 10/100/1000 Base-TX PCI-X Adapter (14108902)
2.lscfg
-bash-3.00$ lscfg -vl ent0
  ent0             U788C.001.AAA8364-P1-T1  2-Port 10/100/1000 Base-TX PCI-X Adapter (14108902)
      2-Port 10/100/1000 Base-TX PCI-X Adapter:
        Network Address.............001125C5CDA4
        ROM Level.(alterable).......DV0210
        Hardware Location Code......U788C.001.AAA8364-P1-T1
--网络配置信息
3.ifconfig
-bash-3.00$ ifconfig -a
en0: flags=5e080863,c0<UP,BROADCAST,NOTRAILERS,RUNNING,SIMPLEX,MULTICAST,GROUPRT,64BIT,CHECKSUM_OFFLOAD(ACTIVE),PSEG,LARGESEND,CHAIN>
        inet 192.168.1.18 netmask 0xffffff00 broadcast 192.168.1.255
         tcp_sendspace 131072 tcp_recvspace 65536
lo0: flags=e08084b<UP,BROADCAST,LOOPBACK,RUNNING,SIMPLEX,MULTICAST,GROUPRT,64BIT>
        inet 127.0.0.1 netmask 0xff000000 broadcast 127.255.255.255
        inet6 ::1/0
         tcp_sendspace 131072 tcp_recvspace 131072 rfc1323 1
4.netstat
-bash-3.00$ netstat -in|grep en
en0   1500  link#2      0.11.25.c5.cd.a4  1071676281     0 688122516     4     0
en0   1500  192.168.1   192.168.1.18      1071676281     0 688122516     4     0
5.lsattr
-bash-3.00$ lsattr -El en0
alias4                      IPv4 Alias including Subnet Mask           True
alias6                      IPv6 Alias including Prefix Length         True
arp           on            Address Resolution Protocol (ARP)          True
authority                   Authorized Users                           True
broadcast                   Broadcast Address                          True
mtu           1500          Maximum IP Packet Size for This Device     True
netaddr       192.168.1.18  Internet Address                           True
netaddr6                    IPv6 Internet Address                      True
netmask       255.255.255.0 Subnet Mask                                True
prefixlen                   Prefix Length for IPv6 Internet Address    True
remmtu        576           Maximum IP Packet Size for REMOTE Networks True
rfc1323                     Enable/Disable TCP RFC 1323 Window Scaling True
security      none          Security Level                             True
state         up            Current Interface Status                   True
tcp_mssdflt                 Set TCP Maximum Segment Size               True
tcp_nodelay                 Enable/Disable TCP_NODELAY Option          True
tcp_recvspace               Set Socket Buffer Space for Receiving      True
tcp_sendspace               Set Socket Buffer Space for Sending        True
--配置网络
ifconfig en0 192.168.0.5 netmask 255.255.255.0 up
chdev -l en1 -a netaddr='192.168.1.1' -a netmask='255.255.255.0'

五.查看硬盘

--硬盘使用情况
1.df
-bash-3.00$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hd4              5.2G   77M  5.1G   2% /
/dev/hd2              5.5G  1.6G  4.0G  28% /usr
/dev/hd9var           640M  640M     0 100% /var
/dev/hd3              128M   48M   81M  38% /tmp
/dev/fwdump           128M  348K  128M   1% /var/adm/ras/platform
/dev/hd1              640M  580M   61M  91% /home0
/proc                    -     -     0   -  /proc
/dev/hd10opt          2.3G  496M  1.8G  22% /opt
192.168.2.5:/store/home
                      2.3T  576G  1.7T  26% /home
--物理硬盘
2.lspv
-bash-3.00$ lspv
hdisk0          000b45c0688a5c5d                    rootvg          active
hdisk1          00c97ac06a9cef35                    None
-bash-3.00$ lspv hdisk0
PHYSICAL VOLUME:    hdisk0                   VOLUME GROUP:     rootvg
PV IDENTIFIER:      000b45c0688a5c5d VG IDENTIFIER     000b45c00000d7000000011b688a6044
PV STATE:           active
STALE PARTITIONS:   0                        ALLOCATABLE:      yes
PP SIZE:            128 megabyte(s)          LOGICAL VOLUMES:  11
TOTAL PPs:          546 (69888 megabytes)    VG DESCRIPTORS:   2
FREE PPs:           417 (53376 megabytes)    HOT SPARE:        no
USED PPs:           129 (16512 megabytes)    MAX REQUEST:      256 kilobytes
FREE DISTRIBUTION:  109..90..00..109..109
USED DISTRIBUTION:  01..19..109..00..00
3.lsdev
-bash-3.00$ lsdev -Cc disk
hdisk0 Available 0G-08-01-8,0 16 Bit LVD SCSI Disk Drive
hdisk1 Available 0G-08-01-5,0 16 Bit LVD SCSI Disk Drive
4.lsattr
-bash-3.00$ lsattr -El hdisk0
PCM             PCM/friend/scsiscsd                         Path Control Module           False
algorithm       fail_over                                   Algorithm                     True
dist_err_pcnt   0                                           Distributed Error Percentage  True
dist_tw_width   50                                          Distributed Error Sample Time True
hcheck_interval 0                                           Health Check Interval         True
hcheck_mode     nonactive                                   Health Check Mode             True
max_transfer    0x40000                                     Maximum TRANSFER Size         True
pvid            000b45c0688a5c5d0000000000000000            Physical volume identifier    False
queue_depth     3                                           Queue DEPTH                   False
reserve_policy  single_path                                 Reserve Policy                True
size_in_mb      73400                                       Size in Megabytes             False
unique_id       2B08004F478B0FHUS153073VL380008IBM   H0scsi Unique device identifier      False
5.lscfg
-bash-3.00$ lscfg -vpl hdisk0
  hdisk0           U788C.001.AAA8364-P1-T11-L8-L0  16 Bit LVD SCSI Disk Drive (73400 MB)
        Manufacturer................IBM   H0
        Machine Type and Model......HUS153073VL3800
        FRU Number..................03N5282
        ROS Level and ID............53343130
        Serial Number...............004F478B
        EC Level....................H17923Y
        Part Number.................03N5281
        Device Specific.(Z0)........000004129F00013A
        Device Specific.(Z1)........VBPSS410
        Device Specific.(Z2)........0068
        Device Specific.(Z3)........08076
        Device Specific.(Z4)........0001
        Device Specific.(Z5)........22
        Device Specific.(Z6)........H17923Y
        Brand.......................H0
  PLATFORM SPECIFIC
  Name:  sd
    Node:  sd
    Device Type:  block
--逻辑卷
6.lsvg
-bash-3.00$ lsvg
rootvg
-bash-3.00$ lsvg -l rootvg
rootvg:
LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT
hd5                 boot       1       1       1    closed/syncd  N/A
hd6                 paging     4       4       1    open/syncd    N/A
hd8                 jfs2log    1       1       1    open/syncd    N/A
hd4                 jfs2       41      41      1    open/syncd    /
hd2                 jfs2       44      44      1    open/syncd    /usr
hd9var              jfs2       5       5       1    open/syncd    /var
hd3                 jfs2       1       1       1    open/syncd    /tmp
hd1                 jfs2       5       5       1    open/syncd    /home0
hd10opt             jfs2       18      18      1    open/syncd    /opt
fwdump              jfs2       1       1       1    open/syncd    /var/adm/ras/platform
lg_dumplv           sysdump    8       8       1    open/syncd    N/A
7.lslv
-bash-3.00$ lslv  hd5
LOGICAL VOLUME:     hd5                    VOLUME GROUP:   rootvg
LV IDENTIFIER:      000b45c00000d7000000011b688a6044.1 PERMISSION:     read/write
VG STATE:           active/complete        LV STATE:       closed/syncd
TYPE:               boot                   WRITE VERIFY:   off
MAX LPs:            512                    PP SIZE:        128 megabyte(s)
COPIES:             1                      SCHED POLICY:   parallel
LPs:                1                      PPs:            1
STALE PPs:          0                      BB POLICY:      relocatable
INTER-POLICY:       minimum                RELOCATABLE:    no
INTRA-POLICY:       edge                   UPPER BOUND:    32
MOUNT POINT:        N/A                    LABEL:          primary_bootlv
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?:     NO
8.prtconf
Volume Groups Information
==============================================================================
rootvg:
PV_NAME           PV STATE          TOTAL PPs   FREE PPs    FREE DISTRIBUTION
hdisk0            active            546         417         109..90..00..109..109
==============================================================================
9.topas
Disk    Busy%     KBPS     TPS KB-Read KB-Writ
hdisk0   21.0     3.2K   20.0     0.0     3.2K
hdisk1    0.0     0.0     0.0     0.0     0.0
cd0       0.0     0.0     0.0     0.0     0.0

六.说明
1.cpu/memory/swap/network/disk的主要信息都可以通过topas和parconf查看
2.这些命令还和shell的类型有很多关系,这里是bash

dd操作数据文件

1.dd主要参数

       Copy a file, converting and formatting according to the operands.
       bs=BYTES
              force ibs=BYTES and obs=BYTES
       cbs=BYTES
              convert BYTES bytes at a time
       count=BLOCKS
              copy only BLOCKS input blocks
       ibs=BYTES
              read BYTES bytes at a time
       if=FILE
              read from FILE instead of stdin
       obs=BYTES
              write BYTES bytes at a time
       of=FILE
              write to FILE instead of stdout
       seek=BLOCKS
              skip BLOCKS obs-sized blocks at start of output
       skip=BLOCKS
              skip BLOCKS ibs-sized blocks at start of input

2.拷贝数据文件头部

[oracle@node1 chf]$ dd if=system01.dbf of=/tmp/dd_xifenfei01.dbf bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 6.2e-05 seconds, 132 MB/s
[oracle@node1 chf]$ ll /tmp/dd_xifenfei01.dbf
-rw-r--r-- 1 oracle oinstall 8192 01-13 16:49 /tmp/dd_xifenfei01.dbf
BBED> set filename '/tmp/dd_xifenfei01.dbf'
        FILENAME        /tmp/dd_xifenfei01.dbf
BBED> set block 1
        BLOCK#          1
BBED> dump
 File: ././dd_xifenfei01.dbf (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 00a20000 0000c0ff 00000000 00000000 64590000 00200000 00a30200 7d7c7b7a
 a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set block 2
BBED-00309: out of range block number (2)
BBED> set offset 8190
        OFFSET          8190
BBED> dump
 File: ././dd_xifenfei01.dbf (0)
 Block: 1                Offsets: 8190 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0000
 <32 bytes per line>
--证明只有1个数据块(8192),并且拷贝数据文件头部成功

3.模拟创建文件

[oracle@node1 tmp]$ ll /tmp/dd_xifenfei02.dbf -h
-rw-r--r-- 1 oracle oinstall 1.0G 01-13 16:58 /tmp/dd_xifenfei02.dbf

4.拷贝数据块到一个文件中

[oracle@node1 chf]$ dd if=/tmp/dd_xifenfei01.dbf of=/tmp/dd_xifenfei02.dbf bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 6e-05 seconds, 137 MB/s
[oracle@node1 chf]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Jan 13 17:01:02 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/tmp/dd_xifenfei02.dbf'
BBED> set block 1
        BLOCK#          1
BBED> dump
 File: ././dd_xifenfei02.dbf (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 00a20000 0000c0ff 00000000 00000000 64590000 00200000 00a30200 7d7c7b7a
 a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set block 2
BBED-00309: out of range block number (2)
BBED> set offset 8190
        OFFSET          8190
BBED> dump
 File: ././dd_xifenfei02.dbf (0)
 Block: 1                Offsets: 8190 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0000
 <32 bytes per line>

通过对比在2步骤中拷贝出来的数据文件头部,证明现在已经把该头部拷贝到了3步骤创建的新文件中

诡异dblink问题解决–dblink insert操作数据类型发生改变

1.展示列属性

[oracle@saas-xunzhi-db1 ~]$ sqlplus testga/testga
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 12 16:26:56 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc t_xifenfei;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROCESS_INSTANCE_ID                       NOT NULL NUMBER(10)
 STATUS_NEW                                         NUMBER
 PACKAGE_NAME                                       VARCHAR2(50)
SQL> desc wf_proc_inst@oldmoa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                               VARCHAR2(500)
 PROC_ID                                   NOT NULL NUMBER(10)
 PARE_INST_ID                              NOT NULL NUMBER(10)
 PARE_ACTI_ID                              NOT NULL NUMBER(10)
 INST_CREATOR                                       VARCHAR2(40)
 CREAT_DATE                                         DATE
 STATUS                                             CHAR(1)
 ATT1                                               NUMBER(10)
 ATT2                                               VARCHAR2(255)
 SEQVALUE                                           VARCHAR2(50)

从这里可以看出,原表的status列是char,而目标表的STATUS_NEW列是number类型

2.通过dblink查询列值

SQL> select t.inst_id as ID,
  2         case w.status
  3           when '0' then
  4            4
  5           when '2' then
  6            3
  7           else
  8            1
  9         end as  status_new,
 10         p.name as PACKAGE_NAME
 11    from wf_proc_inst@oldmoa i
 12    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 13    left join wf_proc_type@oldmoa p on t.type_id = p.id
 14    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 15   where t.inst_id = i.id
 16     and t.proc_id <> 53
 17     and t.is_ok = 1
 18     AND t.inst_id <= 4837
 19     AND t.inst_id>=4735;
        ID STATUS_NEW PACKAGE_NAME
---------- ---------- --------------------------
      4755          3 呈批处理
      4836          3 公文处理

从这里可以看出来,case工作正常,传入的char类型,通过case转换为number类型

3.使用dblink插入数据并查询

SQL> INSERT INTO t_xifenfei
  2  select t.inst_id as ID,
  3         case w.status
  4           when '0' then
  5            4
  6           when '2' then
  7            3
  8           else
  9            1
 10         end as  status_new,
 11         p.name as PACKAGE_NAME
 12    from wf_proc_inst@oldmoa i
 13    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 14    left join wf_proc_type@oldmoa p on t.type_id = p.id
 15    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 16   where t.inst_id = i.id
 17     and t.proc_id <> 53
 18     and t.is_ok = 1
 19     AND t.inst_id <= 4837
 20     AND t.inst_id>=4735;
2 rows created.
SQL> commit;
Commit complete.
SQL> col package_name for a10
SQL> select * from t_xifenfei;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          1 呈批处理
               4836          1 公文处理

这里出现异常,怀疑case中传入的值,都走到else那边了,怀疑是在使用dblink 的时候,char的类型发生的改变,转为了number,导致case匹配失败,所以走到else。

4.在dblink端创建视图,插入数据

SQL> CREATE VIEW GZZJ.v_xifenfei
  2  AS
  3  select t.inst_id as ID,
  4         case to_char(w.status)
  5           when '0' then
  6            4
  7           when '2' then
  8            3
  9           else
 10            1
 11         end as  status_new,
 12         p.name as PACKAGE_NAME
 13    from GZZJ.wf_proc_inst i
 14    left join GZZJ.wf_proc_info_inst t on t.inst_id = i.id
 15    left join GZZJ.wf_proc_type p on t.type_id = p.id
 16    left join GZZJ.wf_proc_inst w on t.inst_id = w.id
 17   where t.inst_id = i.id
 18     and t.proc_id <> 53
 19     and t.is_ok = 1
 20     AND t.inst_id <= 4837
 21     AND t.inst_id>=4735;
View created.
SQL> insert into t_xifenfei
  2  select * from v_xifenfei@oldmoa;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          3 呈批处理
               4836          3 公文处理

通过远程视图,接触数据类型发生改变异常(怀疑是dblink 在insert时候,char数据类型变成了number,但是未得到官方或者权威的确认)

RBA和实例恢复关系

1.关于heartbeat和checkpoint
在这次的删除过程中我没有执行commit,而是直接abort数据库。整个删除过程执行了近6分钟,控制文件的心跳每三秒进行一次,心跳是把low cache rba记录到了控制文件中,而没有真正的把全部的脏数据写入到磁盘( 只有发生了checkpoint时候,才会把相关的脏数据写入到磁盘,而这里的控制文件的heartbeat和checkpoint是两回事,checkpoint一般是在切换日志,数据文件正常离线,执行begin backup命令时发生,昨晚晚上后面的一个困惑就是上面的英文描述,让我把这两者搞混淆了)

2.三种rba解释
low rba :在buffer cache中的数据块第一次数据改变所对应的RAB。
high rba :在buffer cache中的数据块最近一次数据改变时所对应的RAB。
on-disk rba:是 lgwr 写日志文件的最末位置的地址。

3.实例恢复过程解释
实例恢复的时候,是从控制文件heartbeat记录的low rba开始读redo log数据(会多读取一点,因为heartbeat是每三秒执行一次,假设在2.9秒的时候,数据库异常down了,控制文件中记录的还是2.9秒前的low rba,这个时候,从该点开始读取redo),恢复到on-disk rba,而不是high rba(high rba一般情况下会大于on-disk rba,但是因为high rba比on-disk rba多的部分记录在redo log buffer中,在实例恢复的时候,因为其未被记录到redo log file中,所以不能被恢复,其实也没有必要恢复,因为该数据肯定是没有commit或者rollback)

具体内容和实验请见:RBA和实例恢复关系

ORA-600 kcratr_nab_less_than_odr故障解决

朋友的数据库服务器出现ORA-00600[kcratr_nab_less_than_odr],不能open数据库
1.open数据库报ORA-00600[kcratr_nab_less_than_odr]

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],
[43531], [43569], [], [], [], [], [], [], []

2.查看alert日志

Wed Jan 11 13:56:16 2012
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 read 54591 KB redo, 0 data blocks need recovery
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc  (incident=818557):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
Incident details in: d:\dbdms\diag\rdbms\dbdms\dbdms\incident\incdir_818557\dbdms_ora_3936_i818557.trc
Aborting crash recovery due to error 600
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
Errors in file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Trace dumping is performing id=[cdmp_20120110214555]

3.查看trace文件

Trace file d:\dbdms\diag\rdbms\dbdms\dbdms\trace\dbdms_ora_3108.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:2250M/4060M, Ph+PgF:5868M/8119M
Instance name: dbdms
Redo thread mounted by this instance: 1
Oracle process number: 17
Windows thread id: 3108, image: ORACLE.EXE (SHAD)
…………………………
WARNING! Crash recovery of thread 1 seq 99189 is
ending at redo block 43531 but should not have ended before
redo block 43569
Incident 826550 created, dump file: d:\dbdms\diag\rdbms\dbdms\dbdms\incident\incdir_826550\dbdms_ora_3108_i826550.trc
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []
ORA-00600: ??????, ??: [kcratr_nab_less_than_odr], [1], [99189], [43531], [43569], [], [], [], [], [], [], []

通过alert和trace中的内容可以知道,数据库需要恢复到rba到43569,但是因为某种原因实例恢复的时候,只能利用1 thread 99189 seq#,恢复rba到43531。从而导致数据库无法正常open

This Problem is caused by Storage Problem of the Database Files.
The Subsystem (eg. SAN) crashed while the Database was open.
The Database then crashed since the Database Files were not accessible anymore.
This caused a lost Write into the Online RedoLogs and so Instance Recovery is not possible and raising the ORA-600.

4.解决方法

SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
------------
MOUNTED
--尝试直接recover database
SQL> RECOVER DATABASE ;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复
--提示不用恢复
--再打开数据库,还是kcratr_nab_less_than_odr错误警告
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [99189],
[43531], [43569], [], [], [], [], [], [], []
--尝试不完全恢复
SQL> RECOVER DATABASE UNTIL CANCEL;
ORA-10879: error signaled in parallel recovery slave
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: 'D:\DBDMS\DATA\SYSTEM01.DBF'
--重建控制文件
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:/1.TXT';
数据库已更改。
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP NOMOUNT;
ORACLE 例程已经启动。
Total System Global Area  417546240 bytes
Fixed Size                  2176328 bytes
Variable Size             268438200 bytes
Database Buffers          138412032 bytes
Redo Buffers                8519680 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DBDMS" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 18688
  7  LOGFILE
  8    GROUP 1 'D:\DBDMS\LOG\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\DBDMS\LOG\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\DBDMS\LOG\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\DBDMS\DATA\SYSTEM01.DBF',
 13    'D:\DBDMS\DATA\SYSAUX01.DBF',
 14    'D:\DBDMS\DATA\RBSG01.DBF',
 15    'D:\DBDMS\DATA\DATA01.DBF',
 16    'D:\DBDMS\DATA\INDX01.DBF',
 17    'D:\DBDMS\DATA\DATA02.DBF',
 18    'D:\DBDMS\DATA\DATA03.DBF',
 19    'D:\DBDMS\DATA\DATA04.DBF',
 20    'D:\DBDMS\DATA\INDX02.DBF',
 21    'D:\DBDMS\DATA\SYSTEM02.DBF'
 22  CHARACTER SET ZHS16GBK
 23  ;
控制文件已创建。
--继续尝试恢复
SQL> RECOVER DATABASE ;
完成介质恢复。
SQL> ALTER DATABASE OPEN;
数据库已更改。
--open成功

在这次恢复中,主要就是重建控制文件,然后直接恢复成功,如果redo有损坏,那么可能需要使用不完全恢复,然后使用resetlogs打开数据库

CAST本质探讨

1.试验前提

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
   2 SUPPLEMENTAL_LOG_DATA_MIN from v$database;
FOR SUP SUP SUPPLEME
--- --- --- --------
NO  NO  NO  NO
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/archivelog/chf
Oldest online log sequence     1879
Next log sequence to archive   1881
Current log sequence           1881
SQL> select count(*) from t_xifenfei_move;
  COUNT(*)
----------
   7432085
SQL> select bytes/1024/1024 from user_segments where segment_name='T_XIFENFEI_MOVE';
BYTES/1024/1024
---------------
            832

从上面信息可以看到数据库处于归档模式,强制日志和辅助日志为开启,试验测试表t_xifenfei_move有7432085条记录,占用硬盘空间832M

2.常规CAST

SQL>  alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> SET TIMING ON
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            100028
undo change vector size                                               16172
Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_CAST tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:01:58.10
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873150548
undo change vector size                                              131384
Elapsed: 00:00:00.01
SQL> select 873150548-100028 "redo size" from dual;
 redo size
----------
 873050520
SQL> select 131384-16172 "undo size" from dual;
 undo size
----------
    115212

通过这个可以得出结论,产生redo为873050520,undo为115212

3.普通INSERT

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.18
SQL> CREATE TABLE t_xifenfei_move_INSERT
  2  AS
  3  SELECT * FROM T_XIFENFEI_MOVE WHERE 1=0;
Table created.
Elapsed: 00:00:00.32
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22712
undo change vector size                                                6512
Elapsed: 00:00:00.02
SQL> INSERT INTO t_xifenfei_move_INSERT
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:01:59.47
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862261580
undo change vector size                                            27980508
--redo
SQL> select 22712-732 "create redo size" from dual;
create redo size
----------------
           21980
SQL> select 862261580-22712 "insert redo size" from dual;
insert redo size
----------------
       862238868
--undo
SQL> select 6512-136 "create undo size" from dual;
create undo size
----------------
            6376
SQL> select 27980508-6512 "insert undo size" from dual;
insert undo size
----------------
        27973996

通过这个可以得出CREATE TABLE 过程中产生redo:21980,undo:6376,而INSERT 过程中产生的redo:862238868,undo:27973996,整个过程总的产生redo:862260848(862238868+21980),undo:27980372(27973996+6376)

4.INSERT+APPEND

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:25.19
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_INSERT_A tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.18
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             21892
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+ append */INTO t_xifenfei_move_INSERT_A
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:26.37
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         872792032
undo change vector size                                               87764
--redo
SQL> select 21892-732 "create redo size" from dual;
create redo size
----------------
           21160
SQL> select 872792032-21892 "insert redo size" from dual;
insert redo size
----------------
       872770140
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 87764-6308 "insert undo size" from dual;
insert undo size
----------------
           81456

这个过程可以得到结论,create 表的过程产生的redo:21160,undo:6172;insert 表的过程redo:872770140,undo:81456;整个过程产生的redo:872791300(21160+872770140),undo:87628(6172+81456)

5.INSERT+NOLOGGING

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:02.21
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.06
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136
Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_INSERT_N tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.22
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22056
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+ NOLOGGING */INTO t_xifenfei_move_INSERT_N
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:30.33
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862123984
undo change vector size                                            27982876
--redo
SQL> select 22056-780 "create redo size" from dual;
create redo size
----------------
           21276
SQL> select 862123984-22056 "insert redo size" from dual;
insert redo size
----------------
       862101928
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 27982876-6308 "insert undo size" from dual;
insert undo size
----------------
        27976568

这个试验过程得出,create table得到redo:21276,undo:6172,insert table 得到redo:862101928,undo:27976568,整个过程redo:862123204,undo:27982740(27976568+6172)

6.INSERT+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:23.68
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               800
undo change vector size                                                 136
Elapsed: 00:00:00.18
SQL> create table chf.t_xifenfei_move_INSERT_N_new nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.71
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             23296
undo change vector size                                                6756
Elapsed: 00:00:00.00
SQL> INSERT INTO t_xifenfei_move_INSERT_N_new
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:37.51
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862031304
undo change vector size                                            27982776
SQL> select 23296-800 "create redo size" from dual;
create redo size
----------------
           22496
SQL> select 862031304-23296 "insert redo size" from dual;
insert redo size
----------------
       862008008
SQL> select 6756-136 "create undo size" from dual;
create undo size
----------------
            6620
SQL> select 27982776-6756 "insert undo size" from dual;
insert undo size
----------------
        27976020

通过该试验得出,create table 产生redo:22496,undo:6620;insert into 产生redo:862008008,undo:27976020;整个过程产生redo:862030504(22496+862008008),undo:27982640(6620+27976020)

7.APPEND+NOLOGGING(TABLE)

SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:23.59
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.05
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136
Elapsed: 00:00:00.04
SQL> create table chf.t_xifenfei_move_INSERT_NA nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.42
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22000
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+APPEND */ INTO t_xifenfei_move_INSERT_NA
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:01:08.92
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            602352
undo change vector size                                               82756
--redo
SQL> select 22000-780 "create redo size" from dual;
create redo size
----------------
           21220
SQL> select 602352-22000 "insert redo size" from dual;
insert redo size
----------------
          580352
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 82756-6308 "insert undo size" from dual;
insert undo size
----------------
           76448

在这个试验中,create table产生redo:21220,undo:6172,insert into产生redo:580352,undo:76448;整个过程产生的redo:601572(
21220+580352),undo:82620(6172+76448)

8.CAST+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:03.35
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL>
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_cast_N nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:00:56.41
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            769848
undo change vector size                                              124944
SQL> select 769848-732 "redo size" from dual;
 redo size
----------
    769116
SQL> select 124944-136 "undo  size" from dual;
undo  size
----------
    124808

这个试验产生的redo:769116,undo:124808

9.通过试验得出结论
1)sql hint中的nologgging无效
2)普通的cast(不含hint),其本质是append,无nologgging
3)nologgging(表级别)可以使得cast效率较高
4)nologgging(表级别)+append(hint)可以使得insert效率较高

MOVE和CAST比较(续)

本篇文章是MOVE和CAST比较的续篇,主要是对于第一篇中没有涉及到的redo和undo的情况加以叙述
1.查询move产生redo和undo量

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                          VALUE
---------------------------------------- ----------
redo size                                       844
undo change vector size                         136
SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE TEST_OCP;
Table altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                          VALUE
---------------------------------------- ----------
redo size                                 873074928
undo change vector size                      110748
--产生redo
SQL> select 873074928-844 "redo size" from dual;
 redo size
----------
 873074084
--产生undo
SQL> select 110748-136 "undo size" from dual;
 undo size
----------
    110612

2.查询cast产生redo和undo 大小

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
and lower(a.name) in
  4    5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               776
undo change vector size                                                 136
SQL> create table chf.t_xifenfei_move_new tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873017580
undo change vector size                                              115340
--产生redo
SQL> select 873017580-776 "redo size" from dual;
 redo size
----------
 873016804
--产生undo
SQL> select 115340-136 "undo size" from dual;
 undo size
----------
    115204

3.两次实验比较

--redo(分母使用cast操作产生redo)
SQL> select 873074084-873016804 "redo" from dual;
      redo
----------
     57280
SQL> select 57280/873074084 from dual;
57280/873074084
---------------
     .000065607
--undo(分母使用cast操作产生undo)
SQL> select 110612-115204 undo from dual;
         undo
-------------
        -4592
SQL> select 4592/115204 from dual;
4592/115204
-----------
 .039859727

通过这两个的比较可以知道move操作产生的redo多了万分之七不到,undo少了百分之四,这些也是在实验允许的误差范围内,再说move操作还包括了cast的一些后续步骤在其中,所以通过这个验证和上一篇试验(MOVE和CAST比较),基本上可以大胆操作move操作的本质就是全表扫描+append插入数据,操作过程中产生的redo大小几乎和表本身大小(872415232)相等,这个证明,move和cast都是以logging模式运行(数据库本身是非force logging模式)

MOVE和CAST比较

1.创建模拟表

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table chf.t_xifenfei_move
  2  as
  3  select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei_move;
  COUNT(*)
----------
     73585
SQL> create table chf.t_xifenfei_move
  2  as
  3  select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei_move;
  COUNT(*)
----------
     73585
SQL> DECLARE
  2     i NUMBER;
  3      BEGIN
  4        FOR i IN 1..100 LOOP
  5          INSERT INTO chf.t_xifenfei_move
  6          select * from dba_objects;
  7          END LOOP;
  8          COMMIT;
  9      END;
 10      /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('CHF','T_XIFENFEI_MOVE');
PL/SQL procedure successfully completed.
SQL> select bytes from dba_segments where segment_name='T_XIFENFEI_MOVE';
     BYTES
----------
 872415232

2.测试move
2.1)执行move操作,记录时间

SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON;
SQL> alter session set events
  2  '10046 trace name context forever,level 1';
Session altered.
Elapsed: 00:00:00.00
SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS;
Table altered.
Elapsed: 00:02:11.77
SQL> alter session set events
  2  '10046 trace name context off';
Session altered.
Elapsed: 00:00:00.04
SQL> select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from
  2  (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
  3  (select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,
  4  (select value from v$parameter where name = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc

从这里可以看出,move操作执行了00:02:11.77

2.2)查看trace内容

[oracle@node1 ~]$ tkprof  /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc /tmp/xifenfei_move.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 10:57:59 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
********************************************************************************
SQL ID: c1yk5pv0v1wg1 Plan Hash: 2931676921
ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          7          8          0           0
Execute      1     11.29     131.23     105584     106275     115654     7432085
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     11.30     131.29     105591     106283     115654     7432085
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=117799 pr=105602 pw=105585 time=131351005 us)
   7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 pr=105586 pw=0 time=4735560 us cost=23453 size=720912245 card=7432085)
********************************************************************************

从这里可以看出执行move,其实本质是全表扫描表,然后append方式插入数据,而不是真的数据块拷贝

3.测试CAST
3.1).CAST插入数据过程

SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON;
SQL> alter session set events
2 ‘10046 trace name context forever,level 1’;
Session altered.
Elapsed: 00:00:00.01
SQL> create table chf.t_xifenfei_move_new tablespace test_ocp
2 as
3 select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:01:59.22
SQL> alter session set events
2 ‘10046 trace name context off’;
Session altered.
Elapsed: 00:00:00.00
SQL> select d.value||’/’||lower(rtrim(i.instance,chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name from
2 (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
3 (select t.instance from v$thread t,v$parameter v where v.name = ‘thread’ and(v.value = 0 or t.thread# = to_number(v.value))) i,
4 (select value from v$parameter where name = ‘user_dump_dest’) d;
TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc

从这里看出cast操作用时:00:01:59.22,比move稍微少,但是cast要实现move完全的功能,还需要表重命名,表授权,编译无效对象等。

3.2)查看trace内容

[oracle@node1 ~]$ tkprof  /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc /tmp/xifenfei_create.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 11:08:19 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
********************************************************************************
create table chf.t_xifenfei_move_new tablespace test_ocp
as
select * from chf.t_xifenfei_move
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          1          4          0           0
Execute      1      9.85     118.37     105587     106097     112387     7432085
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.85     118.40     105588     106101     112387     7432085
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=106631 pr=105592 pw=105585 time=118338607 us)
   7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 pr=105586 pw=0 time=2935008 us cost=23453 size=720912245 card=7432085)
********************************************************************************

通过这个可以看出,CAST其实本质也是全表扫描,然后append方式插入数据

4.比较move和cast
4.1)通过比较执行时间,cast稍微少,但是还有后续操作需要时间
4.2)通过比较执行计划,两者是一样的
4.3)move操作在整个过程中都会锁表,而cast不会锁住原表(select+where可以减少停业务时间)
4.4)move操作会一次性处理好权限,plsql/view等有效,而cast在rename之后,相关对象可能需要重新编译,重新授权等操作
4.5)cast操作index需要新建(create),而move操作index需要重建(rebuild)
4.6)cast完成后,需要对表重命名,删除原表操作操作,而这个操作move不用

5.选择使用谁
5.1)如果停业务时间够长,建议使用move操作
5.2)如果停业务时间不能太长,可以使用cast+where实现
5.3)如果数据库版本>=10g,且表空间使用local管理,那么可以考虑在不停业务的情况下使用shrink实现类此功能

至于MOVE和CAST在执行过程中,关于产生的redo和undo的比较,请见下篇:MOVE和CAST比较(续)

创建含sysdate的函数index

1.模拟环境
创建表插入数据库

[oracle@node1 ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 9 16:27:19 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table t_xifenfei(id number,intime date);
Table created.
SQL> DECLARE
  2  i NUMBER;
  3  BEGIN
  4    FOR i IN 1..1000 LOOP
  5      INSERT INTO t_xifenfei VALUES(i,SYSDATE-i);
  6      END LOOP;
  7      COMMIT;
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
      1000
SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);
PL/SQL procedure successfully completed.

2.无index查询

SQL> set autot trace exp stat
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    10 |   120 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |    10 |   120 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NVL("INTIME",SYSDATE@!)>=TO_DATE(' 2011-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed
SQL> set autot off

这里只是做了一个简单的查询,因为这个nvl(intime,sysdate)的条件,无法使用正常的index,所以没有建立intime索引的测试。

3.尝试创建index

SQL>  create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging;
 create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging
                                                      *
ERROR at line 1:
ORA-01743: only pure functions can be indexed
SQL> !oerr ora 1743
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS).  SQL
//          expressions must not use SYSDATE, USER, USERENV(), or anything
//          else dependent on the session state.  NLS-dependent functions
//          are OK.
--因为含有sysdate创建函数index失败
SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
  2  RETURN DATE
  3  IS
  4  otime DATE;
  5  BEGIN
  6    otime:=NVL(itime,SYSDATE);
  7    RETURN otime;
  8  END;
  9  /
Function created.
--想采用自定义函数屏蔽掉sysdate在创建index时候的影响
SQL>  create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;
 create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging
                                           *
ERROR at line 1:
ORA-30553: The function is not deterministic
SQL> !oerr ora 30553
30553, 00000, "The function is not deterministic"
// *Cause:  The function on which the index is defined is not deterministic
// *Action: If the function is deterministic, mark it DETERMINISTIC.  If it
//          is not deterministic (it depends on package state, database state,
//          current time, or anything other than the function inputs) then
//          do not create the index.  The values returned by a deterministic
//          function should not change even when the function is rewritten or
//          recompiled.
--因为函数缺少deterministic不能使用于index上
SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
  2  RETURN DATE deterministic
  3  IS
  4  otime DATE;
  5  BEGIN
  6    otime:=NVL(itime,SYSDATE);
  7    RETURN otime;
  8  END;
  9  /
Function created.
SQL> create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;
Index created.
--创建函数index成功
SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);
PL/SQL procedure successfully completed.

4.再次查询
确定已经使用函数index,达到在index中使用sysdate函数index的目的。

SQL> set autot on  exp stat
SQL> select * from t_xifenfei where f_xifenfei(intime)>=to_date('2011-12-31','yyyy-mm-dd');
Execution Plan
----------------------------------------------------------
Plan hash value: 2005404611
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    10 |   200 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI    |    10 |   200 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_T_XIFENFEI |    10 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CHF"."F_XIFENFEI"("INTIME")>=TO_DATE(' 2011-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

5.总结说明
5.1)通过函数屏蔽函数index的时候,不能使用sysdate
5.2)在创建函数时,需要指定deterministic关键字

DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常BUG

1.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

2.查询DBA_DATA_FILES视图

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(MAXBYTES)/1024
--------------- ------------------
EXAMPLE                   33554416
OGG                        5242880
SYSAUX                    33554416
SYSTEM                    33554416
TEMP                      33554416
TS_INDEX_BASE             15728640
TS_PUB_BASE               15728640
UNDOTBS1                  33554416
USERS                     33554416
9 rows selected.

3.查询DBA_HIST_TBSPC_SPACE_USAGE视图

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;
NAME            TABLESPACE_MAXSIZE
--------------- ------------------
EXAMPLE                    4194302
OGG                         655360
SYSAUX                     4194302
SYSTEM                     4194302
TEMP                       4194302
TS_INDEX_BASE              1966080
TS_PUB_BASE                1966080
UNDOTBS1                   8388604
USERS                      4194302
9 rows selected.

观察者两个视图的运行结果,DBA_HIST_TBSPC_SPACE_USAGE视图收集到的统计大小和实际大小都存在一定的误差,但是UNDO表空间出入太明显(UNDOTBS1),特别是最大值和当前值,几乎是真实大小的两倍

4.排除原因
4.1)收集信息是否是最新

SQL> select MAX(rtime) FROM DBA_HIST_TBSPC_SPACE_USAGE;
MAX(RTIME)
-------------------------
01/09/2012 15:00:50

4.2)statistics_level是否被设置为basic

SQL> show parameter statistics_level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

通过这两个查询证明,收集信息和statistics_level都是符合要求,那么为什么undo空间的空间信息还是正常的两倍呢?

5.怀疑bug,查询mos

6.查询11.2.0.3中DBA_HIST_TBSPC_SPACE_USAGE是否正常

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;
NAME            TABLESPACE_MAXSIZE
--------------- ------------------
DRSYS_1                    4194302
EXAMPLE                    4194302
ODU                        8139262
SYSAUX                     4194302
SYSTEM                     4194302
TEMP                       4194302
TEST_OCP                   4194302
UNDOTBS01                  3938560
USERS                      4194302
9 rows selected.
SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024  3
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(MAXBYTES)/1024
--------------- ------------------
DRSYS_1                   33554416
EXAMPLE                   33554416
ODU                       65114096
SYSAUX                    33554416
SYSTEM                    33554416
TEMP                      33554416
TEST_OCP                  33554416
UNDOTBS01                 31457280
USERS                     33554416
9 rows selected.

通过对比,发现基本误差不大,确定在该版本,bug7578292已经被修复