在版本10.2 到 11.1.0.6 和在版本 11.1.0.7及以后中如何重建 ASM 磁盘头的例子

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

Oracle Database – 企业版版本10.2.0.112.1.0.2 [版本10.2 12.1]
本文献中的信息适用于任何平台。

摘要

本文旨在提供一个例子,关于如何在版本10.2 11.1.0.6 和版本 11.1.0.7 及以后重建一个ASM磁盘头当之前的那一个损坏时 

历史

描述:  关于如何重建ASM磁盘头的例子

细节

 你可以点击此处获得On Hands ASM Disk Header Corruption Lab 文献。

总结

注释: 如果分配单位大小为>1MB,则需要指定“AUS”参数。

例如,如果使用AU_SIZE=4M (4194304)创建磁盘组, 那么使用接下来的测试案例 (aus=4194304),例如:

# kfed repair /dev/dm-7 aus=4194304

# kfed read /dev/dm-7    | head -40

# /etc/init.d/oracleasm  scandisks # executed on all the nodes

# /etc/init.d/oracleasm   querydisk REDO01  # executed on all the nodes

如何复原/维修/修复被覆盖的 (KFBTYP_INVALID) ASM 磁盘头 (First 4K) 10.2.0.5, 11.1.0.7, 11.2 及以后版本

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

适用于:

Oracle Database – 企业版版本 10.2.0.5 12.1.0.1 [版本 10.2 12.1]
本文献中的所有信息适用于所有的平台。

目标

当前文献提供了一个例子,关于如何复原/维修/修复被覆盖的 (KFBTYP_INVALID) ASM 磁盘头 (First 4K) 10.2.0.5, 11.1.0.7, 11.2 及以后版本。

解决方法

ASM 磁盘头的复制 (first 4K)存在于版本 10.2.0.5, 11.1.0.7, 11.2 及以后中,它可以用来尝试复原一个有效的 ASM磁盘头 (假设只有磁盘的第一个4k 受到影响/覆盖),为了复原ASM 磁盘头(假设自动的ASM磁盘头备份处于良好的状态),请执行接下来的步骤:


1)
备份受影响的第一个50MB (该步骤是强制的):

$> dd if=<full path affected disk name> of=/tmp/<affected disk name>.dump bs=1048576 count=50

例如:

[grid@dbaasm ~]$ dd if=/dev/oracleasm/disks/ASMDISK2 of=/tmp/ASMDISK2.dump bs=1048576 count=50
50+0 records in
50+0 records out
52428800 bytes (52 MB) copied, 0.667474 seconds, 78.5 MB/s

这里: “/dev/oracleasm/disks/ASMDISK2是受影响的ASM磁盘成员。


2) 
从另一个正常的磁盘成员收集分配单元大小(kfdhdb.ausize)(从同一个受影响的磁盘组):

$> <ASM Oracle Home>/bin/kfed read <full path healthy disk name> | egrep ‘ausize|dsknum|dskname|grpname|fgname’

例如:

[grid@dbaasm ~]$ kfed read /dev/oracleasm/disks/ASMDISK1  | egrep ‘ausize|dsknum|dskname|grpname|fgname’

kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.dskname:                ASMDISK1 ; 0x028: length=8
kfdhdb.grpname:                    DATA ; 0x048: length=4
kfdhdb.fgname:                 FG1_SAN1 ; 0x068: length=8
kfdhdb.ausize:                  2097152 ; 0x0bc: 0x00200000

注释: 在这个例子中, 使用AU_SIZE=2M (2097152 ) 创建磁盘组, /dev/oracleasm/disks/ASMDISK1″ 是正常的ASM 磁盘成员。


3)
然后从备份复原ASM 磁盘头,如下:

$> <ASM Oracle Home>/bin/kfed repair <full path affected disk name> ausz=<AU size from point #2>

例如:

[grid@dbaasm ~]$ kfed repair /dev/oracleasm/disks/ASMDISK2  ausz=2097152


4)
验证受影响的磁盘中的ASM 磁盘头被重建/复原:

$> <ASM Oracle Home>/bin/kfed read <full path affected disk name>  | head -40

例如:

[grid@dbaasm ~]$  kfed read /dev/oracleasm/disks/ASMDISK2  | head -40
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:              2147483650 ; 0x008: disk=2
kfbh.check:                  4052202307 ; 0x00c: 0xf187b343
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: ORCLDISKASMDISK2 ; 0x000: length=16
kfdhdb.driver.reserved[0]:   1145918273 ; 0x008: 0x444d5341
kfdhdb.driver.reserved[1]:    843797321 ; 0x00c: 0x324b5349
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:                186647296 ; 0x020: 0x0b200300
kfdhdb.dsknum:                        2 ; 0x024: 0x0002
kfdhdb.grptyp:                        2 ; 0x026: KFDGTP_NORMAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:                ASMDISK2 ; 0x028: length=8
kfdhdb.grpname:                    DATA ; 0x048: length=4
kfdhdb.fgname:                 FG2_SAN2 ; 0x068: length=8
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32974423 ; 0x0a8: HOUR=0x17 DAYS=0x12 MNTH=0x9 YEAR=0x7dc
kfdhdb.crestmp.lo:           1180930048 ; 0x0ac: USEC=0x0 MSEC=0xe4 SECS=0x26 MINS=0x11
kfdhdb.mntstmp.hi:             33003184 ; 0x0b0: HOUR=0x10 DAYS=0x15 MNTH=0x5 YEAR=0x7de
kfdhdb.mntstmp.lo:           1230240768 ; 0x0b4: USEC=0x0 MSEC=0xff SECS=0x15 MINS=0x12
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  2097152 ; 0x0bc: 0x00200000
kfdhdb.mfact:                    228480 ; 0x0c0: 0x00037c80
kfdhdb.dsksize:                    9769 ; 0x0c4: 0x00002629
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
.
.
.
.

5) 最后, 安装磁盘组:

SQL> alter diskgroup <diskgroup name> mount ;

例如:

SQL> alter diskgroup DATA mount;

Diskgroup altered.

注释

注释 1:如果下列条件是正确的,本文献中提供的方法就会起作用:

   a) 只有受影响的第一个4K 被覆盖/清除/重叠。

   b) ASM 磁盘头备份处于良好的状态。

注释2: 如果这种方法不能解决你的问题, 那么不要在受影响的磁盘组上尝试其他步骤/措施, 因此请通过新的服务请求寻求Oracle 支持,以确定问题的根源和可能的解决方法。

注释3:带有一个损坏的磁盘头ASM 磁盘会产生下列输出

[grid@dbaasm ~]$ kfed read /dev/oracleasm/disks/ASMDISK2

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

000000000 00000000 00000000 00000000 00000000  […………….]

  Repeat 255 times

KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

归零的ASM块

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

症状

1. ASM在警报日志文件中报告一个或几个损坏的块,以及错误ORA-15196

警告: 缓存读取损坏快: group=1(<DISKGROUP>) dsk=0 blk=1 disk=0 (<DISK>) incarn=3916383599 au=0 blk=1 count=1
Errors in file <trace file>

ORA-15196:不可用的ASM 块头 [kfc.c:26076] [endian_kfbh] [2147483648] [1] [0 != 1]
注意: CRSDG 组中的损坏快被转储到<trace file>

2. AMDU在文件report.text中报告元数据损坏块:

———————— SUMMARY FOR DISKGROUP <DISKGROUP>————————
Allocated AU’s: 253711
Free AU’s: 145649
AU’s read for dump: 15
Block images saved: 1273
Map lines written: 15
Heartbeats seen: 0
Corrupt metadata blocks: 768  ——>  HERE
Corrupt AT blocks: 2        ——>  HERE

3. kfed揭示了块,其中几个为零:

$kfed read <disk1> aunum=0 blknum=1

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
2B98D2EFF400 00000000 00000000 00000000 00000000  […………….]
Repeat 255 times

$kfed read <disk2> aunum=0 blknum=2

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
2B2D30FBB400 00000000 00000000 00000000 00000000  […………….]
Repeat 255 times

$kfed read <disk3> aunum=0 blknum=3

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
2B9D91687400 00000000 00000000 00000000 00000000  […………….]
Repeat 255 times

上面显示的输出,是从不同磁盘组的不同磁盘中收集来的。

发展发现块1,2,3,4都是零。

原因

这种情况没什么特别的原因,但由于ASM不以那样的方式更新ASM元数据块,大多数时候这种情况是由于:

1. 硬件问题

2. DBASM实例外部的项目

解决方案

不幸的是,若几个ASM元数据块被零代替,这种情况便无法恢复。唯一的选择是重建磁盘组并还原数据库备份。

ORACLE Support使用的 ASM 工具 : KFOD, KFED,AMDU

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

应用于:

Oracle数据库企业版– 10.2.0.1版本和更高版本

本文档中的信息适用于任何平台。.

目的

本文是为了教育目的:,以便对ASM工具有大概了解:KFODKFEDAMDU

根据你的情况,Oracle Support可能会要求额外的或其他具体信息。.

Note 1345068.1 – Files for Upload When Creating ASM/Storage Service Requests

范围

 ASM工具KFODKFEDAMDU使用的总体概述。

细节

1. KFOD – 核心文件 OSM 磁盘

KFOD工具是用来模拟从操作系统层面的磁盘发现,在$ GRID_HOME/ bin目录(或者老版本中的ASM_HOME/ bin)中可以找到它。

注意:
1) KFOD
在安装时才使用(由OUIDBCAASMCA使用),以发现磁盘。

2)在安装失败的情况下,(例如  $ GRID_HOME/ bin不存在)KFOD可以在stage文件夹中找到: <stage_folder>/grid/stage/ext/bin/

这种情况下,你可能需要设置LD_LIBRARY_PATH<stage_folder>/grid/stage/ext/lib


Help screen can be seen using:

$> kfod help=y

_asm_a/llow_only_raw_disks     KFOD allow only raw devices [_asm_allow_only_raw_disks=TRUE/(FALSE)]
_asm_l/ibraries                       ASM Libraries[_asm_libraries=’lib1′,’lib2′,…]
_asms/id                               ASM Instance[_asmsid=sid]
a/sm_diskstring                      ASM Diskstring [asm_diskstring=’discoverystring’, ‘discoverystring’ …]
d/isks                                    Disks to discover [disks=raw,asm,all]
g/roup                                   Group discover [group=controlfile]
n/ohdr                                   KFOD header suppression [nohdr=TRUE/(FALSE)]
o/p                                        KFOD options type [OP=DISKS/GROUPS/ALL]
p/file                                     ASM parameter file [pfile=’parameterfile’]
s/tatus                                   Include disk header status [status=TRUE/(FALSE)]
v/erbose                                KFOD verbose errors [verbose=TRUE/(FALSE)]

 
用于磁盘发现的常用语句是:

kfod status=TRUE asm_diskstring='<your_path_to_ASM_devices>’ disks=ALL

  

$ export LD_LIBRARY_PATH=/u01/stage/11.2.0.1/grid/stage/ext/lib
$ /u01/stage/11.2.0.1/grid/stage/ext/bin/kfod status=TRUE asm_diskstring=’/dev/rdsk/*’ disk=all dscvgroup=TRUE
——————————————————————————–
Disk Size Header Path Disk Group User Group
================================================================================
1: 10040 Mb MEMBER /dev/rdsk/c0t600144F0E08ACF0000004C2F6FBB0005d0s1  DG oracle oinstall
2: 10040 Mb MEMBER /dev/rdsk/c0t600144F0E08ACF0000004C2F6FBC0006d0s1  DG oracle oinstall
3: 10142 Mb MEMBER /dev/rdsk/c0t600144F0E08ACF0000004C2F6FBE0007d0s1  DG oracle oinstall
4: 10142 Mb MEMBER /dev/rdsk/c0t600144F0E08ACF0000004C2F6FBF0008d0s1  DG oracle oinstall
5:  9734 Mb MEMBER /dev/rdsk/c0t600144F0E08ACF0000004C2F6FC00009d0s1 FRA oracle oinstall
——————————————————————————–

 2. KFED – 核心文件元数据编辑器

KFED是个有用的工具,ASM磁盘组不能安装时,它可以分析ASM磁盘头信息。

Note 1180491.1 – KFED Tool For Windows OS.

Note 1346190.1 – KFED.PL for diagnosing – ORA-15036 ORA-15042 ORA-15020 ORA-15033

注意:
11.1
以上的版本安装时就有KFED可执行; 而在旧版本上,则必须建立KFED

但它不包含在软件的来源中,因此,直到GI安装完成才可用。

如果在GI安装之前你需要使用它,请参考下列文档


Note 1505005.1 – Where to find kfed utility before Oracle Grid Infrastructure is installed

Help screen can be seen using:

$>kfed help=y

as/mlib         ASM Library [asmlib=’lib’]
aun/um        AU number to examine or update [AUNUM=number]
aus/z           Allocation Unit size in bytes [AUSZ=number]
blkn/um       Block number to examine or update [BLKNUM=number]
blks/z          Metadata block size in bytes [BLKSZ=number]
ch/ksum       Update checksum before each write [CHKSUM=YES/NO]
cn/t              Count of AUs to process [CNT=number]
de/v            ASM device to examine or update [DEV=string]
dm/pall        Don’t suppress repeated lines when dumping corrupt blocks [DMPALL=YES/NO]
o/p              KFED operation type [OP=READ/WRITE/MERGE/REPAIR/NEW/FORM/FIND/STRUCT]
p/rovnm       Name for provisioning purposes [PROVNM=string]
s/eek           AU number to seek to [SEEK=number]
te/xt            File name for translated block text [TEXT=string]
ty/pe           ASM metadata block type number [TYPE=number]

 读取ASM磁盘头常用的语句是 

$> kfed read <your_device>

:

$> kfed read /dev/raw/raw1
    
   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: T=0 NUMB=0x0
   kfbh.block.obj:                   2147483648 ; 0x008: TYPE=0x8 NUMB=0x0
   kfbh.check:                        2932902794 ; 0x00c: 0xaed08b8a
   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:         ORCLDISK ; 0x000: length=8
   kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
   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:                 168820736 ; 0x020: 0x0a100000
   kfdhdb.dsknum:                     0 ; 0x024: 0x0000
   kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
   kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
   kfdhdb.dskname:               ASM01_0000 ; 0x028: length=10
   kfdhdb.grpname:               ASM01 ; 0x048: length=5
   kfdhdb.fgname:                 ASM01_0000 ; 0x068: length=10
   kfdhdb.capname:               ; 0x088: length=0
   kfdhdb.crestmp.hi:             32837774 ; 0x0a8: HOUR=0xe DAYS=0x4 MNTH=0x4 YEAR=0x7d4
   kfdhdb.crestmp.lo:             1555722240 ; 0x0ac: USEC=0x0 MSEC=0x29c SECS=0xb MINS=0x17
   kfdhdb.mntstmp.hi:            32837774 ; 0x0b0: HOUR=0xe DAYS=0x4 MNTH=0x4 YEAR=0x7d4
   kfdhdb.mntstmp.lo:            1563864064 ; 0x0b4: USEC=0x0 MSEC=0x1ab SECS=0x13 MINS=0x17
  …
   kfdhdb.ub4spare[60]:         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

注意:

  • 只有当设备是ASM磁盘组的一部分时,KFED会返回到可读输出。否则(如果该设备还没有被添加到磁盘组又或者磁盘不再有效)输出是这样的:

KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

2) 若是非默认的AU大小,应在命令中指定它:

$> kfed read <your_device> AUSZ=<au_size>

 3. AMDU – ASM 元数据转储工具

AMDU是一个实用程序,可以从一个或多个ASM磁盘提取所有可用的元数据,和/或生成个别块的格式化打印输出,该工具不需要安装任何磁盘组。

Oracle Support 可能会要求你提供AMDU输出,尤其是遇到ASM元数据相关的内部错误时。

注意:
11.1
以上的版本安装时就有KFED可执行。虽然该工具是在11g版本上发行,但也可以在ASM10g上使用。

Note 553639.1 – Placeholder for AMDU binaries and using with ASM 10g

磁盘组相关的元数据转储的常用语句是:

$> amdu -diskstring ‘<your_path_to_ASM_disks>’  -dump  ‘<diskgroup>’ 

注意:

每次执行AMDU,就会在当前位置创建一个目录,除非它被-directory参数覆盖。

该目录格式为amdu_YYYY_MM_DD_HH24_MM_SS

默认参数会生成下列文件:

<diskgroup>_0001.img     –  a exact dump of the content of the diskgroup ;size is limited to 2gb but can be more than one file
<diskgroup>.map            –  can be used to find the exact location of the ASM metadata on the disks
report.txt                        –  include details about the disks scanned

REPORT.TXT文件显示,磁盘的盘头信息属于磁盘组,如下所示:

例:

$> amdu -diskstring ‘/dev/raw/raw*’ -dump ‘DG11’

——————————————————————————–

<report.txt content>

—————————– DISK REPORT N0003 —————————
Disk Path: /dev/raw/raw3
Physical Sector Size: 512 bytes
Disk Size: 977 megabytes
Group Name: DG11
Disk Name: DG11_0000
Failure Group Name: DG11_0000

—————————– DISK REPORT N0004 —————————
Disk Path: /dev/raw/raw4
Physical Sector Size: 512 bytes
Disk Size: 978 megabytes
Group Name: DG11
Disk Name: DG11_0001
Failure Group Name: DG11_0001

….

注意:没有Oracle Support的指导,使用上述工具时切勿使用选项,这样可能会损坏你的磁盘。 

参考

NOTE:1345068.1 – Files for Upload When Creating ASM/Storage Service Requests
NOTE:1346190.1 – KFED.PL for diagnosing – ORA-15063 ORA-15042 ORA-15020 ORA-15033
NOTE:553639.1 – Placeholder for AMDU binaries and using with ASM 10g
NOTE:1505005.1 – Where to find kfed utility before Oracle Grid Infrastructure is installed.
NOTE:1180491.1 – KFED Tool For Windows OS.

AMDU下载地址 ORACLE ASM AMDU工具在ASM10g中使用

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

Oracle数据库企业版版本10.1.0.310.2.0.4[发行10.110.2]

本文档中的信息适用于任何平台。

Oracle服务器企业版版本:10.1.0.310.2.0.4

目的

Oracle 10g中,如果磁盘组已安装,磁盘组的内容可以回看,还有一些内部视图显示了磁盘内文件(ASM文件和数据库文件)的具体分配。

若磁盘组未安装,该信息不可用,这使得不安装磁盘组便难以诊断错误。此问题用AMDU可以解决。


AMDU
是在11g中引入的工具,在11g中可以从一个或多个ASM磁盘中提取所有可用的元数据,从转储输出生成格式化块打印输出,从一个磁盘组(安装/卸载)中提取一个或多个文件,并将其写入OS文件系统。

处理ASM元数据相关的内部错误时,该工具非常重要。

尽管该工具在11g中发行,在ASM10g中也可使用。

本文档提供的链接,可以在ASM10g环境中下载运行AMDU所需的文件。

平台

 

Linux X86 (Platform 46)

amdu_lnx_32.zip

Linux X86-64 AMD64/EM64T (Platform 226)

amdu_lnx_X86-64.zip

Solaris 10 Sparc 64bit (Platform 23)

amdu_solaris10_64.zip

Solaris 9 Sparc 64bit (Platform 23)

amdu_solaris9_64.zip

HP-UX PA-RISC 64bits (Platform 59)

amdu_HP-PARISC.zip

HP-UX Itanium (Platform 197)

amdu_hp_itanium.zip

AIX

amdu_aix.zip

其他平台待添加

Table 1.  List of platforms


本文档的范围不是去解释AMDU可用性和特性。其主要目标是提供一个占位符,其中可以下载AMDU,并在Oracle 10g环境中使用。至于Oracle11g,这个工具是该发行版本的一部分,出现在$ ORACLE_HOME/ bin目录。

要求

有关具体细节,请一步一步查看相关章节。

注意:如果执行过程中报告有核心转储或任何错误,请提交报告,可以替换二进制。

配置

1. 下载表1引用的zip文件,并将其转让到服务器。

2.解压内容

zip文件中包含了4个文件:

1. amdu — amdu binary
2. libclntsh.so.11.1
3. libnnz11.so
4. libskgxp11.so

注意:lib*文件的扩展名在某些平台上可能会不同。

3. 如果对ASMDB使用不同ORACLE_HOMEs,使用ASM位置。修改变量LD_LIBRARY_PATHLIBPATH(在AIX上)或同等变量,包括下载amdu的目录。同时修改PATH变量:

$cd <your directory>
$export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:`pwd`
or $setenv LD_LIBRARY_PATH ${LD_LIBRARY_PATH}:`pwd`
$export PATH=$PATH:`pwd`  or setenv PATH ${PATH}:`pwd`

or

$cd <your directory>
$export LIBPATH=$LIBPATH:`pwd`
or $setenv LIBPATH ${LIBPATH}:`pwd`
$export PATH=$PATH:`pwd` or setenv PATH ${PATH}:`pwd`

已知事项
*
amdu
的执行可能因为一个核心转储而失败。

回顾核心转储中的调用堆栈:

#0 0x0806235d in slfipn ()
#1 0x0805e88c in lfimknam ()
#2 0x081123bd in kfmuCreateDirectory ()
#3 0x0810a753 in kfmu_main ()
#4 0x08091a13 in lpmcall ()
#5 0x08065678 in lpmpmai ()
#6 0x08057fb9 in main ()

这是由于错误5997071. 解决方法是去掉所有NLS*变量,从NLS_LANG开始。

说明

$ amdu -diskstring ‘/dev/asm1_disk*’ -dump ‘DATA1’

有两个主要参数:

-diskstring    Identify the location where the devices used by diskgroup are located.
-dump          Define the diskgroup whose content will be dumped by AMDU

下面这个例子是关于如何使用amdu,尤其是为了找到磁盘目录的位置。

This example scans disks under ‘/dev/asmdisk* and will dump details for diskgroup DATA.

Executing amdu:

$ ./amdu -diskstring ‘/dev/asmdisk*’ -dump ‘DATA’

Change to the directory created which the name will be printed in the screen
during execution of amdu.

Two files will be created:

* report.txt which provides details of disks identified during
the amdu execution
* <diskgroup>.map, Map files are ASCII files that describe the data in the
image files for a particular disk group

在地图文件(*.MAP)中,我们找寻F00000002:(磁盘目录始终是ASM文件#2

N0014 D0002 R00 A00000002 F00000002 I0 E00000000 U00 C00256 S0001 B0035663872

现在,阅读report.text,从而使用N0014D0002识别磁盘:

N0014 — DISK REPORT N0014
D0002 — Disk # 2
A00000002 — Allocation Unit 2
F00000002 — File#2

The  file report.txt will have the path for this disk, using the values
referenced before:

—————————– DISK REPORT N0014 ———————-
Disk Path: /dev/asmdisk17
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 954 megabytes
Group Name: DATA
Disk Name: DATA_0002
Failure Group Name: DATA_0002 Disk Number: 2 Header Status: 3

AMDU信息表明磁盘目录位于磁盘#2中的AU2,用路径 /dev/asmdisk17来识别。

使用KFED来获取特定的AU

$ kfed read /dev/asmdisk17 aunum=2 blknum=0 text=disk17_dd.txt

注意:从块0开始审查,所添加的磁盘条目可以是在不同的块中

注意

此示例代码仅供教育目的,而不是由Oracle Support支持。已经过内部测试,但是,我们不保证它对你有效。使用之前请确保是在测试环境中运行它。

脚本

样本数出

每次执行AMDU,都会在当前位置创建一个目录,除非它被参数-directory覆盖。该目录格式为amdu_YYYY_MM_DD_HH24_MM_SS

$amdu -diskstring ‘/dev/asm1*’ -dump ‘DATA1’
amdu_2008_02_14_15_04_11/


step-by-step 部分中引用的默认命令,将生成以下文件:

DATA1_0001.img   — could be more than one. size is limited to 2gb.
DATA1.map
report.txt


这里是文件的一些细节:

1. report.txt

如其名称所示,它包含了扫描的磁盘信息(姓名,磁盘组名称,failgroup名称,大小,创建时间等)。

2. *.map

-dump命令引用的每个磁盘组,都有一个*.map文件,它可以用来寻找磁盘上ASM元数据的精确位置。回看元数据的特定区域时,这个文件会是一个关键组成部分。

3. *.img
每一个磁盘组,可能有很多图像文件。大小限制为2GB,将成为磁盘组内容的准确转储。

压缩目录下的所有文件,并要求客户将其上传到服务请求。

【Oracle Database 12c新特性】Information Lifecycle Management ILM和Storage Enhancements

Oracle Database 12c中引入了Information Lifecycle Management ILM 信息生命周期管理和Storage Enhancements 存储增强的特性。

Lifecycle Management ILM 的一个最重要部分是 Automatic Data Placement 自动数据存放, 简称ADP。

存储增强方面 12c引入了在线移动Datafile的特性 Online Move Datafile, 该特性允许用户在线将一个有数据的datafile在存储之间移动,且数据库保持打开并访问该文件。

目前为止(12.1.0.1)Automatic Data Optimization和heat map仍存在以下的限制:

 

  1. 在一个多租户数据库 (CDB)中仍不支持Automatic Data Optimization和heat map
  2. Row-level policies for ADO are not supported for Temporal Validity. Partition-level ADO and compression are supported if partitioned on the end-time columns.
  3. Row-level policies for ADO are not supported for in-database archiving. Partition-level ADO and compression are supported if partitioned on the ORA_ARCHIVE_STATE column.
  4. Custom policies (user-defined functions) for ADO are not supported if the policies default at the tablespace level.
  5. ADO does not perform checks for storage space in a target tablespace when using storage tiering.
  6. ADO is not supported on tables with object types or materialized views.
  7. ADO concurrency (the number of simultaneous policy jobs for ADO) depends on the concurrency of the Oracle scheduler. If a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later.
  8. Policies for ADO are only run in the Oracle Scheduler maintenance windows. Outside of the maintenance windows all policies are stopped. The only exceptions are those jobs for rebuilding indexes in ADO offline mode.
  9. ADO has restrictions related to moving tables and table partitions.

 

 

用户可以在行row,segment数据段和表空间级别指定ADO策略,具体可以在create table或alter table语句中指定。 通过指定ADO策略,用户可以实现数据的自动化移动,这种移动发生在数据库的多个存储层 storage tier , 同时也可以为每一个storage tier指定不同的压缩粒度, 以及何时发生上述的数据移动。ADO策略的作用域可以指定为 segment、row或者group。

在CREATE TABLE和ALERT TABLE中加入ILM的子句,可以实现创建、删除、启用和禁用相关的ADO policy。 一个ILM policy策略子句决定了压缩和存储层策略。 当创建一张表时 可以加入ADO policy, 也可以通过alter table  增加更多的策略,亦或者启用、禁用和删除策略。

 

CREATE TABLE sales_ado
 (PROD_ID NUMBER NOT NULL,
  CUST_ID NUMBER NOT NULL,
  TIME_ID DATE NOT NULL,
  CHANNEL_ID NUMBER NOT NULL,
  PROMO_ID NUMBER NOT NULL,
  QUANTITY_SOLD NUMBER(10,2) NOT NULL,
  AMOUNT_SOLD NUMBER(10,2) NOT NULL )
  ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT
      AFTER 6 MONTHS OF NO ACCESS;

SQL> SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled
  2         FROM USER_ILMPOLICIES;

POLICY_NAME          POLICY_TYPE                ENABLED
-------------------- -------------------------- --------------
P41                  DATA MOVEMENT              YES

ALTER TABLE sales MODIFY PARTITION sales_1995 
     ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
     AFTER 6 MONTHS OF NO ACCESS;

SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled 
   FROM USER_ILMPOLICIES;

POLICY_NAME              POLICY_TYPE   ENABLE
------------------------ ------------- ------
P1                       DATA MOVEMENT YES
P2                       DATA MOVEMENT YES

/* You can disable an ADO policy with the following */
ALTER TABLE sales_ado ILM DISABLE POLICY P1;

/* You can delete an ADO policy with the following */
ALTER TABLE sales_ado ILM DELETE POLICY P1;

/* You can disable all ADO policies with the following */
ALTER TABLE sales_ado ILM DISABLE_ALL;

/* You can delete all ADO policies with the following */
ALTER TABLE sales_ado ILM DELETE_ALL;

/* You can disable an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DISABLE POLICY P2;

/* You can delete an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DELETE POLICY P2;

 

ILM 的语法主要如下:

 

ilm_clause

 

ilm_policy_clause

 

tiering_clause

 

table_compression

 

 

 

ADO Automatic Data Optimization策略语法详解:

ALTER TABLE sales ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
ROW AFTER 3 DAYS OF NO MODIFICATION;  

解释为无修改3天后,则将数据行 高级压缩

 

 

ROW STORE COMPRESS ADVANCED ==>压缩类型

可用的压缩类型包括:

ROW STORE COMPRESS (Basic 压缩)
ROW STORE COMPRESS ADVANCED (Advanced Row 压缩)
COLUMN STORE COMPRESS FOR QUERY LOW/HIGH (HCC Query )
COLUMN STORE COMPRESS FOR ARCHIVE LOW/HIGH (HCC Archive )

 

ROW =>处理对象范畴

处理对象范畴包括:
Tablespace
GROUP ==> 包括表上的索引和LOB
Segment => 表/分区/子分区
ROW => 最小处理单位

 

 
NO MODIFICATION ==> 行为
行为:

NO MODIFICATION =>没有INSERT/UPDATE/DELETE/Merge等修改
NO ACCESS =>没有INSERT/UPDATE/DELETE/Merge/SELECT
CREATION => 创建

 

 

AFTER 3 DAYS ==> 时间
n DAY[s]
n MONTH[s]
n YEAR[s]

 

 

另一种移动Storage Tier的模式:

ALTER TABLE sales ILM ADD POLICY  TIER TO Low_Cost_tbs;

 

TIER 移动到

Low_Cost_tbs为指定的表空间

 

在充分利用ILM ADP策略之前,需要有几个步骤:

首先需要启动 活跃追踪 activity tracking, 可选的有2个级别的追踪方式,会从不同的维度激活系统自动生成统计信息:

  • SEGMENT-LEVEL段级活跃度是指对一张表或某个分区的读和写
  • ROW-LEVEL行级是指行的生成,最后修改和访问

 

我们来举几个例子:

1、段级活跃追踪 SEGMENT-LEVEL activity tracking

ALTER TABLE interval_sales ILM  ENABLE ACTIVITY TRACKING SEGMENT ACCESS

上面启用了对于INTERVAL_SALES表的segment level  activity tracking,对该表段的读和写均会被收集为统计信息

2、 行的创建和修改活跃追踪

ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING (CREATE TIME , WRITE TIME);

 

3、行的访问活跃追踪

ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING  (READ TIME);

在12.1.0.1.0正式发行版中 使用HEAT_MAP特性来追踪数据活跃度, 可以通过在system或者session级别来修改heap_map参数达到启用和关闭的目的。

例如在系统级别启用HEAT MAP特性,则

ALTER SYSTEM SET HEAT_MAP = ON;

当HEAT MAP特性被启用时,所有的访问均会被追踪并存放在内存中的活跃追踪模块中。  注意SYSTEM和SYSAUX表空间上的对象不会被追踪。

 

在系统级别关闭HEAT MAP特性:

ALTER SYSTEM SET HEAT_MAP = OFF;

默认情况下 HEAT_MAP是关闭的, 当HEAT_MAP关闭时 对数据的访问不会就到内存中的活跃追踪模块中。

 

该HEAT_MAP同样负责启用和关闭Automatic Data Optimization (ADO)特性。 对于ADO而言,Heat Map 必须在实例级别启用。

 

可以通过V$HEAT_MAP_SEGMENT 来观察内存中的 HEAT MAP数据

 

 

SQL> select * from V$heat_map_segment;

no rows selected

SQL> alter session set heat_map=on;

Session altered.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

SQL> select * from v$heat_map_segment;

OBJECT_NAME          SUBOBJECT_NAME             OBJ#   DATAOBJ# TRACK_TIM SEG SEG FUL LOO     CON_ID
-------------------- -------------------- ---------- ---------- --------- --- --- --- --- ----------
EMP                                            92997      92997 23-JUL-13 NO  NO  YES NO           0

 

 

其中v$heat_map_segment的定义,该v$heat_map_segment动态视图的数据来源于内部视图X$HEATMAPSEGMENT

 

V$HEAT_MAP_SEGMENT displays real-time segment access information.

Column Datatype Description
OBJECT_NAME VARCHAR2(128) Name of the object
SUBOBJECT_NAME VARCHAR2(128) Name of the subobject
OBJ# NUMBER Object number
DATAOBJ# NUMBER Data object number
TRACK_TIME DATE Timestamp of current activity tracking
SEGMENT_WRITE VARCHAR2(3) Indicates whether the segment has write access: (YES or NO)
SEGMENT_READ VARCHAR2(3) Indicates whether the segment has read access: (YES or NO)
FULL_SCAN VARCHAR2(3) Indicates whether the segment has full table scan: (YES or NO)
LOOKUP_SCAN VARCHAR2(3) Indicates whether the segment has lookup scan: (YES or NO)
CON_ID NUMBER The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
  • 1: This value is used for rows containing data that pertain to only the root
  • n: Where n is the applicable container ID for the rows containing data

The Heat Map feature is not supported in CDBs in Oracle Database 12c, so the value in this column can be ignored.

 

 

由于HEAP MAP在内存中的数据每一小时才写入到磁盘上,所以查看DBA_HEAT_MAP_SEGMENT一般是有延迟的。 实际数据存放在HEAT_MAP_STAT$字典基表上。

 

关于Automatic Data Optimization的一个架构图:

 

ADO

 

  • 先介绍一下我们演示中要用到的脚本和存储过程
  • ilm_setup_basic 是我们测试ILM的基础环境脚本 负责创建下面的一些过程
  • print_compression_stats 打印出表的压缩状态 主要通过dbms_compression.get_compression_type包
  • list_ilm_policies 列出ILM策略 ,通过查询dba_ilmdatamovementpolicies 、dba_ilmobjects 、dba_ilmpolicies 三个视图
  • set_back_chktime 通过修改ilmobj$等基表 实际将policy的chktime 修改为几天前,这样我们测试ILM就不需要等好几天了!!但是真实的环境中,显然我们不会也不该用到set_back_chktime
  • set_window 设置维护窗口, 用的是dbms_scheduler.open_window ,由于非行级的策略仅在维护窗口中被执行,所以我们通过手动打开窗口来方便演示
  • ilm_demo_cleanup脚本负责清理实验环境

 

 

 

 

实验场景 1 Background Compression and Compression Tiering:

 

 

SQL> alter system set heat_map=on;

系统已更改。

使用下面的页面中的脚本构建 scott用户
http://www.askmaclean.com/archives/scott-schema-script.html

SQL> grant all on dbms_lock to scott;

授权成功。

 SQL> grant dba to scott;

授权成功。

@ilm_setup_basic C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm.dbf
@tktgilm_demo_env_setup 

SQL> connect scott/tiger ;
已连接。

SQL> select count(*) from scott.employee;

  COUNT(*)
----------
      3072

已选择 1 行。

SQL> set serveroutput on
SQL> exec print_compression_stats('SCOTT','EMPLOYEE');
Compression Stats
------------------
Uncmpressed           : 3072
Adv/basic compressed  : 0
Others                : 0

PL/SQL 过程已成功完成。

上面的输出显示3072行数据未压缩

我们执行下面的语句 加入一个policy 对三天未修改的行数据压缩

alter table employee ilm 
      add policy row store compress advanced row 
      after 3 days of no modification 
/ 

SQL> set serveroutput on
SQL> execute list_ilm_policies;
--------------------------------------------------
Policies defined for SCOTT
--------------------------------------------------
Object Name------ : EMPLOYEE
Subobject Name--- :
Object Type------ : TABLE
Inherited from--- : POLICY NOT INHERITED
Policy Name------ : P1
Action Type------ : COMPRESSION
Scope------------ : ROW
Compression level : ADVANCED
Tier Tablespace-- :
Condition type--- : LAST MODIFICATION TIME
Condition days--- : 3
Enabled---------- :   YES
--------------------------------------------------

PL/SQL 过程已成功完成。

SQL> select sysdate from dual;

SYSDATE
--------------
29-7月 -13

SQL> execute set_back_chktime(get_policy_name('EMPLOYEE',null,'COMPRESSION','ROW','ADVANCED',3,null,null),'EMPLOYEE',null,6);
Object check time reset ...
--------------------------------------
Object Name    : EMPLOYEE
Object Number  : 93123
D.Object Numbr : 93123
Policy Number  : 1
Object chktime : 23-7月 -13 08.13.42.000000 上午
Distnt chktime : 0
--------------------------------------

PL/SQL 过程已成功完成。

讲policy的chktime设回到6天前, 注意这里set_back_chktime是通过修改数据字典的方法来实现“时空穿梭”的,不要用在产品环境中,仅仅用来测试的。

打开维护窗口

 alter system flush buffer_cache;
 alter system flush buffer_cache;
 alter system flush shared_pool;
 alter system flush shared_pool;

SQL> execute set_window('MONDAY_WINDOW','OPEN');
Set Maint. Window  OPEN
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : TRUE
-----------------------------

PL/SQL 过程已成功完成。

SQL> exec dbms_lock.sleep(60) ;

PL/SQL 过程已成功完成。

SQL> exec print_compression_stats('SCOTT', 'EMPLOYEE');
Compression Stats
------------------
Uncmpressed           : 338
Adv/basic compressed  : 2734
Others                : 0

PL/SQL 过程已成功完成。

可以看到进入维护窗口一段时间后 Adv/basic compressed  : 2734 部分行被压缩了

SQL> col object_name for a20
SQL> select object_id,object_name from dba_objects where object_name='EMPLOYEE';

 OBJECT_ID OBJECT_NAME
---------- --------------------
     93123 EMPLOYEE

SQL> execute list_ilm_policy_executions ;
--------------------------------------------------
Policies execution details for SCOTT
--------------------------------------------------
Policy Name------ : P22
Job Name--------- : ILMJOB48
Start time------- : 29-7月 -13 08.37.45.061000 上午
End time--------- : 29-7月 -13 08.37.48.629000 上午
-----------------
Object Name------ : EMPLOYEE
Sub_obj Name----- :
Obj Type--------- : TABLE
-----------------
Exec-state------- : SELECTED FOR EXECUTION
Job state-------- : COMPLETED SUCCESSFULLY
Exec comments---- :
Results comments- :
---
--------------------------------------------------

PL/SQL 过程已成功完成。

ILMJOB48是后台实施policy的JOB,在12.1.0.1中由J00x进程执行

另MMON_SLAVE进程如M00x大约每15分钟实施一些行策略

select sample_time,program,module,action from v$active_session_history where    action  ='KDILM background EXEcution'  order by sample_time;

29-7月 -13 08.16.38.369000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.17.38.388000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.17.39.390000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.23.38.681000000 上午	ORACLE.EXE (M002)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.32.38.968000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.33.39.993000000 上午	ORACLE.EXE (M003)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.33.40.993000000 上午	ORACLE.EXE (M003)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.36.40.066000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.37.42.258000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.37.43.258000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.37.44.258000000 上午	ORACLE.EXE (M000)	MMON_SLAVE	KDILM background EXEcution
29-7月 -13 08.38.42.386000000 上午	ORACLE.EXE (M001)	MMON_SLAVE	KDILM background EXEcution 

select distinct action  from v$active_session_history where    action like 'KDILM%' 

KDILM background CLeaNup
KDILM background EXEcution

SQL> execute set_window('MONDAY_WINDOW','CLOSE');
Set Maint. Window  CLOSE
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : FALSE
-----------------------------

PL/SQL 过程已成功完成。

SQL> drop table employee purge ;

表已删除。

关闭窗口 并清理环境

spool ilm_usecase_1_cleanup.lst
@ilm_demo_cleanup ;
spool off

 

 

 

实验场景2 ILM policy with Storage tiering

 

 

 

 

@ilm_setup_basic C:\APP\XIANGBLI\ORADATA\MACLEAN\maclean1.dbf
@ilm_adv_setup C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm_part1.dbf C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm_part2.dbf C:\APP\XIANGBLI\ORADATA\MACLEAN\low_cost_store.dbf C:\APP\XIANGBLI\ORADATA\MACLEAN\source_tbs.dbf
@tktgilm_demo_env_setup

pause
connect scott/tiger
set serveroutput on 

alter table customer_bak ilm 
      add policy tier to low_cost_store
/ 

SQL> execute set_ilm_param('TBS PERCENT USED',10);
ILM parameter settings ...
--------------------------------------
TBS PERCENT USED : 10

PL/SQL 过程已成功完成。

SQL> execute set_ilm_param('TBS PERCENT FREE',95);
ILM parameter settings ...
--------------------------------------
TBS PERCENT FREE : 95

PL/SQL 过程已成功完成。

pause
execute set_back_chktime(get_policy_name('CUSTOMER_BAK',null,'STORAGE','SEGMENT',null, 0,'LOW_COST_STORE',null),'CUSTOMER_BAK',null,6);

SQL> execute set_back_chktime(get_policy_name('CUSTOMER_BAK',null,'STORAGE','SEGMENT',null, 0,'LOW_COST_STORE',null),'CUSTOMER_BAK',null,6);
Object check time reset ...
--------------------------------------
Object Name    : CUSTOMER_BAK
Object Number  : 116367
D.Object Numbr : 116367
Policy Number  : 61
Object chktime : 29-7月 -13 07.52.46.000000 下午
Distnt chktime : 0
--------------------------------------

PL/SQL 过程已成功完成。

pause

SQL> execute list_ilm_policies
--------------------------------------------------
Policies defined for SCOTT
--------------------------------------------------
Object Name------ : CUSTOMER_BAK
Subobject Name--- :
Object Type------ : TABLE
Inherited from--- : POLICY NOT INHERITED
Policy Name------ : P61
Action Type------ : STORAGE
Scope------------ : SEGMENT
Compression level :
Tier Tablespace-- : LOW_COST_STORE
Condition type--- :
Condition days--- : 0
Enabled---------- :   YES
--------------------------------------------------

PL/SQL 过程已成功完成。

pause
SQL> column table_name format a30
SQL> column tablespace_name format a30
SQL> select table_name, tablespace_name
  2  from user_tables
  3  where table_name = 'CUSTOMER_BAK'
  4  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CUSTOMER_BAK                   SRC_TBS

pause

SQL> execute dbms_stats.gather_table_stats('SCOTT','CUSTOMER_BAK');

PL/SQL 过程已成功完成。

SQL> execute estimate_tbs_usage('CUSTOMER_BAK');
Table Name : CUSTOMER_BAK
Num rows : 9999
Avg.Rlen : 37
spc used : 369963
Ttl used : 369963
Net avbl : 10115797
MAX spc. : 10485760

PL/SQL 过程已成功完成。
pause
--
-- Open maintenance window
--
SQL> execute set_window('MONDAY_WINDOW','OPEN');
Set Maint. Window  OPEN
-----------------------------
Window Name   : MONDAY_WINDOW
Enabled?      : TRUE
Active?       : TRUE
-----------------------------

PL/SQL 过程已成功完成。

SQL> insert  into customer_bak select * from customer
  2      where rownum < 8000   3  / 已创建 7999 行。 SQL> commit;

提交完成。
pause
execute dbms_stats.gather_table_stats('SCOTT','CUSTOMER_BAK');
SQL> execute dbms_stats.gather_table_stats('SCOTT','CUSTOMER_BAK');

PL/SQL 过程已成功完成。

SQL> execute estimate_tbs_usage('CUSTOMER_BAK');
Table Name : CUSTOMER_BAK
Num rows : 17998
Avg.Rlen : 37
spc used : 665926
Ttl used : 665926
Net avbl : 41277114
MAX spc. : 41943040

PL/SQL 过程已成功完成。

pause

-- sleep to allow the policy to kick in
execute dbms_lock.sleep(180) 

-- Verify the table is moved to the target tablespace
SQL> select table_name, tablespace_name
  2  from user_tables
  3  where table_name = 'CUSTOMER_BAK'
  4  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CUSTOMER_BAK                   LOW_COST_STORE

pause
SQL> select compression, compress_for
  2  from user_tables
  3  where table_name = 'CUSTOMER_BAK'
  4  /

COMPRESSION      COMPRESS_FOR
---------------- ------------------------------------------------------------
DISABLED
pause
SQL> set serveroutput on
SQL> execute list_ilm_policy_executions
--------------------------------------------------
Policies execution details for SCOTT
--------------------------------------------------
Policy Name------ : P61
Job Name--------- : ILMJOB382
Start time------- : 04-8月 -13 07.53.17.173000 下午
End time--------- : 04-8月 -13 07.53.34.341000 下午
-----------------
Object Name------ : CUSTOMER_BAK
Sub_obj Name----- :
Obj Type--------- : TABLE
-----------------
Exec-state------- : SELECTED FOR EXECUTION
Job state-------- : COMPLETED SUCCESSFULLY
Exec comments---- :
Results comments- :
---
--------------------------------------------------

PL/SQL 过程已成功完成。

pause
SQL> execute report_extended_stat
Extended statistics :
Policy details ....
-------------------
Policy Name           : P61
Obj. number           : 116371
Data Obj. number      : 116371
Last check time       : 04-8月 -13 07.53.17.172000 下午
Last execution time   : 04-8月 -13 07.53.34.341000 下午
Last job status       : 2
Execution results..
-------------------
Policy Name           : P61
Obj. number           : 116371
Execution id          : 3815
Job Name              : ILMJOB382
Job Status            : 2
Completion time       : 04-8月 -13 07.53.34.341000 下午
Execution comments    :
Result comments       :

PL/SQL 过程已成功完成。
pause
set serveroutput off 
-- 
-- Clean up 
-- 
drop table employee purge ; 
drop table customer purge ; 
drop table customer_bak purge ; 

spool off 

spool ilm_usecase_3_cleanup.lst
@ilm_demo_cleanup
spool off

【Oracle Database 12c新特性】ORACLE_MAINTAINED

ORACLE_MAINTAINED是Oracle 12c中一系列视图的新增信息字段,该字段代表对象或用户是Oracle提供的脚本生成的,即Oracle-Supplied objects。

 

ORACLE_MAINTAINED VARCHAR2(1) Denotes whether the object was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). An object for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

我们来看看那些视图有该字段

oracle@localhost:/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin$ grep -i "ORACLE_MAINTAINED" *|grep comment
cdcore.sql:comment on column USER_OBJECTS.ORACLE_MAINTAINED is
cdcore.sql:comment on column ALL_OBJECTS.ORACLE_MAINTAINED is
cdcore.sql:comment on column DBA_OBJECTS.ORACLE_MAINTAINED is
cdcore.sql:comment on column USER_OBJECTS_AE.ORACLE_MAINTAINED is
cdcore.sql:comment on column ALL_OBJECTS_AE.ORACLE_MAINTAINED is
cdcore.sql:comment on column DBA_OBJECTS_AE.ORACLE_MAINTAINED is
cdenv.sql:comment on column USER_USERS.ORACLE_MAINTAINED is
cdenv.sql:comment on column ALL_USERS.ORACLE_MAINTAINED is
cdenv.sql:comment on column DBA_USERS.ORACLE_MAINTAINED is
cdsec.sql:comment on column DBA_ROLES.ORACLE_MAINTAINED is

DBA_USERS、DBA_OBJECTS、DBA_OBJECTS_AE以及与之相关的ALL_、USER_视图均有ORACLE_MAINTAINED字段。

以下为ORACLE MAINTAINED用户名


  1* select username from dba_users where ORACLE_MAINTAINED='Y'
SQL> /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
AUDSYS
GSMUSER
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
APEX_PUBLIC_USER
SYSDG
DIP
SYSBACKUP
MDDATA
GSMCATUSER
SYSKM
XS$NULL
OJVMSYS
ORACLE_OCM
OLAPSYS
SI_INFORMTN_SCHEMA
DVSYS
ORDPLUGINS
XDB
ANONYMOUS
CTXSYS
ORDDATA
GSMADMIN_INTERNAL
APPQOSSYS
APEX_040200
WMSYS
DBSNMP
ORDSYS
MDSYS
DVF
FLOWS_FILES
SYS
SYSTEM
OUTLN
LBACSYS

Oracle Streams流复制性能优化脚本

Oracle Streams流复制性能优化脚本

 

 

---------------------------------------------------------------------------------------------------------------------
--
-- 1. Days to Retain Checkpoints CHECKPOINT_RETENTION_TIME (STAGE)
--

begin
dbms_capture_adm.alter_capture('<Capture_Name>',CHECKPOINT_RETENTION_TIME=>3);
end;
/

---------------------------------------------------------------------------------------------------------------------
--
-- 2. LogMiner (STAGE)
--

BEGIN
dbms_capture_adm.set_parameter('<Capture_Name>','_CHECKPOINT_FREQUENCY','1000');
END;
/

Make sure that the hidden capture parameter _CHECKPOINT_FREQUENCY is set to 1000 (new default value for 11g).
This will reduce the number of logminer (not database) checkpoints taken which has the side effect of reducing
the size of the SYSAUX tablespace and redo generation, as well.

---------------------------------------------------------------------------------------------------------------------
--
-- 3. Change _HASH_TABLE_SIZE (TARGET)
--

BEGIN
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','_HASH_TABLE_SIZE','10000000')
END;
/

_HASH_TABLE_SIZE: (releases prior to 10.2.0.4) In an mixed (Insert/UPdate/Delete) or heavy update workload that
results in a large number of dependency waits (WAIT_DEPs) on replicated tables with many constraints , consider
increasing the size of the dependency hash table with the hidden parameter _HASH_TABLE_SIZE. Set the value of
this parameter to a large number, for example, 1 million (1000000) or 10 million (10000000).
---------------------------------------------------------------------------------------------------------------------
--
-- 4 Change Parallelism (TARGET)
--

BEGIN
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','PARALLELISM','8')
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','_TXN_BUFFER_SIZE','32')
END;

Test with parallelism values based on available CPU. Typical settings are 4, 8, 16, or 24. 16 and
24 are normally used for high transaction loading with high end machines Values between 1 and 4
are typically used on low end machines. Midrange system typically configure 4 or 8 apply
servers.

Reduce _TXN_BUFFER_SIZE. What this will do is, reduce the number of messages that can be in
the memory which can prevent queue spillage due to the transaction staying in the memory for
more than 5 mins

---------------------------------------------------------------------------------------------------------------------
--
-- 5. Dynamic Statements (TARGET)
--

BEGIN
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','_DYNAMIC_STMTS','Y')
END;
/

If Y, then for UPDATE statements, the apply process will optimize the generation of SQL statements based on required columns.

---------------------------------------------------------------------------------------------------------------------
--
-- 6. Maintenance Issues: Keeping Segments small (STAGE)
--

You need to manually use shrink command to free unused space (but you need to stop the capture)

ALTER TABLE system.LOGMNR_RESTART_CKPT$ ENABLE ROW MOVEMENT;
ALTER TABLE system.LOGMNR_RESTART_CKPT$ SHRINK SPACE;
ALTER TABLE system.LOGMNR_RESTART_CKPT$ DISABLE ROW MOVEMENT;

You need to manually use shrink command to free unused space (but you need to stop the capture)
To improve the performance of the streams spillover table after major spillover activity, do the following:

ALTER TABLE aq$_<queue_table_name>_p ENABLE ROW MOVEMENT;
ALTER TABLE aq$_<queue_table_name>_p SHRINK SPACE;
---------------------------------------------------------------------------------------------------------------------
--
-- 7 Apply Performance (TARGET)
--

Modify the INITRANS, PCTFREE of SYS.STREAMS$_APPLY_PROGRESS table. The INITRANS value should be >= to
the apply process parallelism. Set PCTFREE to at least 10:

ALTER TABLE SYS.STREAMS$_APPLY_PROGRESS INITRANS 16 PCTFREE 10;

---------------------------------------------------------------------------------------------------------------------
--
-- 8. _SGA_SIZE (STAGE)
--

!!! This parameter should not be increased unless the logminer error ORA-1341 is encountered. !!!

Amount of memory available from the streams pool for logminer processing.
The default amount of streams_pool memory allocated to logminer is 10Mb.
Increase this value especially in environments where large LOBs are processed.
Streams pool memory allocated to logminer is unavailable for other usage.
Capture parameters can be set using the SET_PARAMETER procedure from the DBMS_CAPTURE_ADM package.
For example, to set the checkpoint frequency of the streams capture process named <CAPTURE_NAME>,
use the following syntax while logged in as the Streams Administrator:

BEGIN
dbms_capture_adm.set_parameter('<Capture_Name>','_SGA_SIZE','100');
END;
/

---------------------------------------------------------------------------------------------------------------------
--
-- 9. Propagation Latency (STAGE)
--

Maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued.

The default value is 60. Caution: if latency is not specified for this call, then latency will over-write
any existing value with the default value.

For example, if the latency is 60 seconds, then during the propagation window, if there are no messages to be
propagated, then messages from that queue for the destination will not be propagated for at least 60 more seconds.
It will be at least 60 seconds before the queue will be checked again for messages to be propagated for the specified
destination.

BEGIN
dbms_aqadm.alter_propagation_schedule
( queue_name => 'STRMADMIN.STREAMS_OUT_Q'
, destination => '<TARGET_DB>'
, destination_queue => 'STRMADMIN.STREAMS_IN_QOA'
, latency => 3
);
END;
/
---------------------------------------------------------------------------------------------------------------------
--
-- 10. TXN_LCR_SPILL_THRESHOLD (TARGET)
--

BEGIN
dbms_apply_adm.set_parameter('<Apply_Name>','TXN_LCR_SPILL_THRESHOLD','250000');
END;
/

---------------------------------------------------------------------------------------------------------------------
--
-- 11. _JOB_QUEUE_INTERVAL (STAGE)
--

alter system set _job_queue_interval=1
---------------------------------------------------------------------------------------------------------------------
--
-- 12. Event Setting (_capture_publisher_flow_control_threshold) (STAGE)
--

alter system set events '10868 trace name context forever, level 30000';
-- restart the database

---------------------------------------------------------------------------------------------------------------------

### CHeck for Consraints aus HC herausnehmen

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569