分类目录归档：Oracle ASM

通过ftp/http拷贝asm中文件

发表于 2012 年 5 月 4 日由惜分飞

1.检查Oracle XML Database组件

SQL> select comp_name, status, version from DBA_REGISTRY where comp_name='Oracle XML Database';

COMP_NAME                 STATUS                 VERSION
------------------------- ---------------------- ------------------------------
Oracle XML Database       VALID                  11.2.0.3.0

SQL> select count(*) from dba_objects where owner='XDB' and status='INVALID';

  COUNT(*)
----------
         0

2.配置xdb的ftp和http

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 1 12:05:27 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> execute dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> execute dbms_xdb.setftpport(2100);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select dbms_xdb.GETFTPPORT() from dual;

DBMS_XDB.GETFTPPORT()
---------------------
                 2100

SQL> select dbms_xdb.GETHTTPPORT() from dual;

DBMS_XDB.GETHTTPPORT()
----------------------
                  8080
--根据你的需求,可以选择一个即可

SQL> show parameter dispatchers;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=XFFXDB)
--dispatchers参数会自动配置,这里需要说明,MOS中说的sidxdb是不恰当的,我这里是db_namexdb
--因为我这里是rac,sid为XFF1,总之相信自动配置

3.查看监听

[oracle@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAY-2012 12:09:14

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                01-MAY-2012 11:51:13
Uptime                    0 days 0 hr. 18 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/gridbase/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.31)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.33)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "XFF" has 1 instance(s).
  Instance "XFF1", status READY, has 1 handler(s) for this service...
Service "XFFXDB" has 1 instance(s).
  Instance "XFF1", status READY, has 1 handler(s) for this service...
The command completed successfully

--以下两条监听是自动增加上去，如果没有自动增加，需要手工增加并且重启或者重新加载监听
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=2100))(Presentation=FTP)(Session=RAW))

4.ftp基本操作

[oracle@rac1 ~]$ ftp -n
ftp> open rac1 2100
Connected to rac1.
220- rac1 
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 rac1 FTP Server (Oracle XML DB/Oracle Database) ready.
530  Please login with USER and PASS.
530  Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
ftp> user system xifenfei
331 pass required for SYSTEM
230 SYSTEM logged in
ftp> ls
227 Entering Passive Mode (192,168,1,31,181,5)
150 ASCII Data Connection
drw-r--r--   2 SYS      oracle         0 SEP 18 17:49 OLAP_XDS
drw-r--r--   2 SYS      oracle         0 SEP 18 17:47 home
drw-r--r--   2 SYS      oracle         0 SEP 18 18:02 images
drw-r--r--   2 SYS      oracle         0 SEP 18 17:49 olap_data_security
drw-r--r--   2 SYS      oracle         0 SEP 18 17:43 public
drw-r--r--   2 SYS      oracle         0 SEP 18 17:44 sys
-rw-r--r--   1 SYS      oracle         0 MAY 01 04:06 xdbconfig.xml
drw-r--r--   2 SYS      oracle         0 SEP 18 17:49 xds
226 ASCII Transfer Complete
ftp> cd sys
250 CWD Command successful
ftp> cd asm
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,1,31,98,133)
150 ASCII Data Connection
drw-r--r--   2 SYS      oracle         0 MAY 01 04:14 XIFENFEI
drw-r--r--   2 SYS      oracle         0 MAY 01 04:14 DATA
226 ASCII Transfer Complete
ftp> cd xifenfei
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,1,31,151,70)
150 ASCII Data Connection
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 XFF
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 ASM
226 ASCII Transfer Complete
ftp> cd xff
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,1,31,100,14)
150 ASCII Data Connection
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 DATAFILE
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 CONTROLFILE
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 ONLINELOG
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 TEMPFILE
drw-r--r--   2 SYS      oracle         0 MAY 01 04:15 PARAMETERFILE
-rw-r--r--   1 SYS      oracle      3584 MAY 01 04:15 spfileXFF.ora
226 ASCII Transfer Complete
ftp> cd xff/datafile
250 CWD Command successful
ftp> ls
227 Entering Passive Mode (192,168,1,31,30,63)
150 ASCII Data Connection
-rw-r--r--   1 SYS      oracle  744497152 MAY 01 04:20 SYSTEM.256.776961315
-rw-r--r--   1 SYS      oracle  618668032 MAY 01 04:20 SYSAUX.257.776961315
-rw-r--r--   1 SYS      oracle  83894272 MAY 01 04:20 UNDOTBS1.258.776961317
-rw-r--r--   1 SYS      oracle   6291456 MAY 01 04:20 user_dd.dbf
-rw-r--r--   1 SYS      oracle  26222592 MAY 01 04:20 UNDOTBS2.264.776961693
-rw-r--r--   1 SYS      oracle  157294592 MAY 01 04:20 xifenfei01.dbf
226 ASCII Transfer Complete
ftp> get xifenfei01.dbf
local: xifenfei01.dbf remote: xifenfei01.dbf
227 Entering Passive Mode (192,168,1,31,143,34)
150 ASCII Data Connection
550- Error Response 
ORA-31198: Mismatch in number of bytes transferred due to non-binary mode
550 End Error Response 
270340 bytes received in 0.053 seconds (5e+03 Kbytes/s)
ftp> binary
200  Type set to I.
ftp> get xifenfei01.dbf
local: xifenfei01.dbf remote: xifenfei01.dbf
227 Entering Passive Mode (192,168,1,31,9,112)
150 BIN Data Connection
226 BIN Transfer Complete
157294592 bytes received in 14 seconds (1.1e+04 Kbytes/s)
--主要需要设置为二进制传输模式,默认是ASCII方式的,可能会报错
ftp> quit
221 QUIT Goodbye.

这篇文章主要参考How to configure XDB for using ftp and http protocols with ASM [ID 357714.1],但是在自己试验过程中,发现文档中有些地方不太合适,这里做个补充说明：
1.ftp服务端不需要启动(这里只是用到了操作系统的ftp客户端功能,如果使用其他客户端工具,连操作系统客户端都省了)
2.dispatchers 中的SERVICE=XDB不准确,系统自动配置的XDB亦可以正常工作,更相信数据库自动配置
3.ftp和http两个功能,只需要配置一个即可(选择你需要的)
4.下图展示的是通过http方式访问结果(system用户登录)

发表在 Oracle ASM | 一条评论

ASM未正常启动,使用dd找回数据文件

发表于 2012 年 4 月 30 日由惜分飞

本篇主要测试在ASM未启动状态下,如何找出找出文件文件对应的AU,并且通过dd还原出数据文件
参考文档:ASM的文件管理深入解析（内含开源的ASM文件挖掘研究版程序）
模拟测试数据

SQL> alter database datafile '+XIFENFEI/xff/datafile/xifenfei.268.781905429' resize 50M ;

Database altered.

SQL> alter database datafile '+XIFENFEI/xff/datafile/xifenfei.268.781905429' autoextend off;

Database altered.

SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME like '%XIFENFEI%';
 
GROUP_NUMBER
------------
           2

SQL> select DISK_NUMBER,path from v$asm_disk where GROUP_NUMBER=2;

DISK_NUMBER PATH
----------- ---------------------------------------------
          1 /dev/oracleasm/disks/VOL4
          0 /dev/oracleasm/disks/VOL3

SQL> create table t_xifenfei tablespace xifenfei
  2  as
  3  select  * from dba_objects;

Table created.

SQL> select count(*)  from t_xifenfei;

  COUNT(*)
----------
     74537

kfed找出相关AU映射

------------------------------------------------------------------------------
1号文件的第一个AU（0号盘2号AU）中，只能保存1至255号文件的。
从256号文件开始，AU的分布信息保存在1号文件第二个AU中，也就是（1号盘，64号AU）。
其中第一个块（0号块），对应256号文件。1号块对应257号文件，等等，依此类推。
------------------------------------------------------------------------------
[grid@rac1 disks]$  kfed read /dev/oracleasm/disks/VOL3 aun=2 blkn=1|more

kfffde[0].xptr.au:                    2 ; 0x4a0: 0x00000002
kfffde[0].xptr.disk:                  0 ; 0x4a4: 0x0000
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 S=0
kfffde[0].xptr.chk:                  40 ; 0x4a7: 0x28
kfffde[1].xptr.au:                   64 ; 0x4a8: 0x00000040
kfffde[1].xptr.disk:                  1 ; 0x4ac: 0x0001
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 S=0
kfffde[1].xptr.chk:                 107 ; 0x4af: 0x6b
kfffde[2].xptr.au:           4294967295 ; 0x4b0: 0xffffffff
kfffde[2].xptr.disk:              65535 ; 0x4b4: 0xffff
kfffde[2].xptr.flags:                 0 ; 0x4b6: L=0 E=0 D=0 S=0
kfffde[2].xptr.chk:                  42 ; 0x4b7: 0x2a

[grid@rac1 disks]$  kfed read /dev/oracleasm/disks/VOL4 aun=64 blkn=12|more
kfffde[0].xptr.au:                  681 ; 0x4a0: 0x000002a9
kfffde[0].xptr.disk:                  0 ; 0x4a4: 0x0000
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 S=0
kfffde[0].xptr.chk:                 129 ; 0x4a7: 0x81
kfffde[1].xptr.au:                 1092 ; 0x4a8: 0x00000444
kfffde[1].xptr.disk:                  1 ; 0x4ac: 0x0001
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 S=0
kfffde[1].xptr.chk:                 107 ; 0x4af: 0x6b
kfffde[2].xptr.au:                 1093 ; 0x4b0: 0x00000445
kfffde[2].xptr.disk:                  1 ; 0x4b4: 0x0001
kfffde[2].xptr.flags:                 0 ; 0x4b6: L=0 E=0 D=0 S=0
kfffde[2].xptr.chk:                 106 ; 0x4b7: 0x6a
kfffde[3].xptr.au:                  682 ; 0x4b8: 0x000002aa
kfffde[3].xptr.disk:                  0 ; 0x4bc: 0x0000
kfffde[3].xptr.flags:                 0 ; 0x4be: L=0 E=0 D=0 S=0
kfffde[3].xptr.chk:                 130 ; 0x4bf: 0x82
kfffde[4].xptr.au:                 1094 ; 0x4c0: 0x00000446
kfffde[4].xptr.disk:                  1 ; 0x4c4: 0x0001
kfffde[4].xptr.flags:                 0 ; 0x4c6: L=0 E=0 D=0 S=0
kfffde[4].xptr.chk:                 105 ; 0x4c7: 0x69
kfffde[5].xptr.au:                 1095 ; 0x4c8: 0x00000447
kfffde[5].xptr.disk:                  1 ; 0x4cc: 0x0001
kfffde[5].xptr.flags:                 0 ; 0x4ce: L=0 E=0 D=0 S=0
kfffde[5].xptr.chk:                 104 ; 0x4cf: 0x68
kfffde[6].xptr.au:                  683 ; 0x4d0: 0x000002ab
kfffde[6].xptr.disk:                  0 ; 0x4d4: 0x0000
kfffde[6].xptr.flags:                 0 ; 0x4d6: L=0 E=0 D=0 S=0
kfffde[6].xptr.chk:                 131 ; 0x4d7: 0x83
kfffde[7].xptr.au:                 1096 ; 0x4d8: 0x00000448
kfffde[7].xptr.disk:                  1 ; 0x4dc: 0x0001
kfffde[7].xptr.flags:                 0 ; 0x4de: L=0 E=0 D=0 S=0
kfffde[7].xptr.chk:                 103 ; 0x4df: 0x67
kfffde[8].xptr.au:                  684 ; 0x4e0: 0x000002ac
kfffde[8].xptr.disk:                  0 ; 0x4e4: 0x0000
kfffde[8].xptr.flags:                 0 ; 0x4e6: L=0 E=0 D=0 S=0
kfffde[8].xptr.chk:                 132 ; 0x4e7: 0x84
kfffde[9].xptr.au:                 1097 ; 0x4e8: 0x00000449
kfffde[9].xptr.disk:                  1 ; 0x4ec: 0x0001
kfffde[9].xptr.flags:                 0 ; 0x4ee: L=0 E=0 D=0 S=0
kfffde[9].xptr.chk:                 102 ; 0x4ef: 0x66
kfffde[10].xptr.au:                1098 ; 0x4f0: 0x0000044a
kfffde[10].xptr.disk:                 1 ; 0x4f4: 0x0001
kfffde[10].xptr.flags:                0 ; 0x4f6: L=0 E=0 D=0 S=0
kfffde[10].xptr.chk:                101 ; 0x4f7: 0x65
kfffde[11].xptr.au:                 685 ; 0x4f8: 0x000002ad
kfffde[11].xptr.disk:                 0 ; 0x4fc: 0x0000
kfffde[11].xptr.flags:                0 ; 0x4fe: L=0 E=0 D=0 S=0
kfffde[11].xptr.chk:                133 ; 0x4ff: 0x85
kfffde[12].xptr.au:                1099 ; 0x500: 0x0000044b
kfffde[12].xptr.disk:                 1 ; 0x504: 0x0001
kfffde[12].xptr.flags:                0 ; 0x506: L=0 E=0 D=0 S=0
kfffde[12].xptr.chk:                100 ; 0x507: 0x64
kfffde[13].xptr.au:                 686 ; 0x508: 0x000002ae
kfffde[13].xptr.disk:                 0 ; 0x50c: 0x0000
kfffde[13].xptr.flags:                0 ; 0x50e: L=0 E=0 D=0 S=0
kfffde[13].xptr.chk:                134 ; 0x50f: 0x86
kfffde[14].xptr.au:                1100 ; 0x510: 0x0000044c
kfffde[14].xptr.disk:                 1 ; 0x514: 0x0001
kfffde[14].xptr.flags:                0 ; 0x516: L=0 E=0 D=0 S=0
kfffde[14].xptr.chk:                 99 ; 0x517: 0x63
kfffde[15].xptr.au:                1101 ; 0x518: 0x0000044d
kfffde[15].xptr.disk:                 1 ; 0x51c: 0x0001
kfffde[15].xptr.flags:                0 ; 0x51e: L=0 E=0 D=0 S=0
kfffde[15].xptr.chk:                 98 ; 0x51f: 0x62
kfffde[16].xptr.au:                 687 ; 0x520: 0x000002af
kfffde[16].xptr.disk:                 0 ; 0x524: 0x0000
kfffde[16].xptr.flags:                0 ; 0x526: L=0 E=0 D=0 S=0
kfffde[16].xptr.chk:                135 ; 0x527: 0x87
kfffde[17].xptr.au:                1102 ; 0x528: 0x0000044e
kfffde[17].xptr.disk:                 1 ; 0x52c: 0x0001
kfffde[17].xptr.flags:                0 ; 0x52e: L=0 E=0 D=0 S=0
kfffde[17].xptr.chk:                 97 ; 0x52f: 0x61
kfffde[18].xptr.au:                1103 ; 0x530: 0x0000044f
kfffde[18].xptr.disk:                 1 ; 0x534: 0x0001
kfffde[18].xptr.flags:                0 ; 0x536: L=0 E=0 D=0 S=0
kfffde[18].xptr.chk:                 96 ; 0x537: 0x60
kfffde[19].xptr.au:                 688 ; 0x538: 0x000002b0
kfffde[19].xptr.disk:                 0 ; 0x53c: 0x0000
kfffde[19].xptr.flags:                0 ; 0x53e: L=0 E=0 D=0 S=0
kfffde[19].xptr.chk:                152 ; 0x53f: 0x98
kfffde[20].xptr.au:                1104 ; 0x540: 0x00000450
kfffde[20].xptr.disk:                 1 ; 0x544: 0x0001
kfffde[20].xptr.flags:                0 ; 0x546: L=0 E=0 D=0 S=0
kfffde[20].xptr.chk:                127 ; 0x547: 0x7f
kfffde[21].xptr.au:                 689 ; 0x548: 0x000002b1
kfffde[21].xptr.disk:                 0 ; 0x54c: 0x0000
kfffde[21].xptr.flags:                0 ; 0x54e: L=0 E=0 D=0 S=0
kfffde[21].xptr.chk:                153 ; 0x54f: 0x99
kfffde[22].xptr.au:                1105 ; 0x550: 0x00000451
kfffde[22].xptr.disk:                 1 ; 0x554: 0x0001
kfffde[22].xptr.flags:                0 ; 0x556: L=0 E=0 D=0 S=0
kfffde[22].xptr.chk:                126 ; 0x557: 0x7e
kfffde[23].xptr.au:                1106 ; 0x558: 0x00000452
kfffde[23].xptr.disk:                 1 ; 0x55c: 0x0001
kfffde[23].xptr.flags:                0 ; 0x55e: L=0 E=0 D=0 S=0
kfffde[23].xptr.chk:                125 ; 0x55f: 0x7d
kfffde[24].xptr.au:                 690 ; 0x560: 0x000002b2
kfffde[24].xptr.disk:                 0 ; 0x564: 0x0000
kfffde[24].xptr.flags:                0 ; 0x566: L=0 E=0 D=0 S=0
kfffde[24].xptr.chk:                154 ; 0x567: 0x9a
kfffde[25].xptr.au:                1107 ; 0x568: 0x00000453
kfffde[25].xptr.disk:                 1 ; 0x56c: 0x0001
kfffde[25].xptr.flags:                0 ; 0x56e: L=0 E=0 D=0 S=0
kfffde[25].xptr.chk:                124 ; 0x56f: 0x7c
kfffde[26].xptr.au:                 691 ; 0x570: 0x000002b3
kfffde[26].xptr.disk:                 0 ; 0x574: 0x0000
kfffde[26].xptr.flags:                0 ; 0x576: L=0 E=0 D=0 S=0
kfffde[26].xptr.chk:                155 ; 0x577: 0x9b
kfffde[27].xptr.au:                1108 ; 0x578: 0x00000454
kfffde[27].xptr.disk:                 1 ; 0x57c: 0x0001
kfffde[27].xptr.flags:                0 ; 0x57e: L=0 E=0 D=0 S=0
kfffde[27].xptr.chk:                123 ; 0x57f: 0x7b
kfffde[28].xptr.au:                1109 ; 0x580: 0x00000455
kfffde[28].xptr.disk:                 1 ; 0x584: 0x0001
kfffde[28].xptr.flags:                0 ; 0x586: L=0 E=0 D=0 S=0
kfffde[28].xptr.chk:                122 ; 0x587: 0x7a
kfffde[29].xptr.au:                 692 ; 0x588: 0x000002b4
kfffde[29].xptr.disk:                 0 ; 0x58c: 0x0000
kfffde[29].xptr.flags:                0 ; 0x58e: L=0 E=0 D=0 S=0
kfffde[29].xptr.chk:                156 ; 0x58f: 0x9c
kfffde[30].xptr.au:                1110 ; 0x590: 0x00000456
kfffde[30].xptr.disk:                 1 ; 0x594: 0x0001
kfffde[30].xptr.flags:                0 ; 0x596: L=0 E=0 D=0 S=0
kfffde[30].xptr.chk:                121 ; 0x597: 0x79
kfffde[31].xptr.au:                1111 ; 0x598: 0x00000457
kfffde[31].xptr.disk:                 1 ; 0x59c: 0x0001
kfffde[31].xptr.flags:                0 ; 0x59e: L=0 E=0 D=0 S=0
kfffde[31].xptr.chk:                120 ; 0x59f: 0x78
kfffde[32].xptr.au:                 693 ; 0x5a0: 0x000002b5
kfffde[32].xptr.disk:                 0 ; 0x5a4: 0x0000
kfffde[32].xptr.flags:                0 ; 0x5a6: L=0 E=0 D=0 S=0
kfffde[32].xptr.chk:                157 ; 0x5a7: 0x9d
kfffde[33].xptr.au:                1112 ; 0x5a8: 0x00000458
kfffde[33].xptr.disk:                 1 ; 0x5ac: 0x0001
kfffde[33].xptr.flags:                0 ; 0x5ae: L=0 E=0 D=0 S=0
kfffde[33].xptr.chk:                119 ; 0x5af: 0x77
kfffde[34].xptr.au:                 694 ; 0x5b0: 0x000002b6
kfffde[34].xptr.disk:                 0 ; 0x5b4: 0x0000
kfffde[34].xptr.flags:                0 ; 0x5b6: L=0 E=0 D=0 S=0
kfffde[34].xptr.chk:                158 ; 0x5b7: 0x9e
kfffde[35].xptr.au:                1113 ; 0x5b8: 0x00000459
kfffde[35].xptr.disk:                 1 ; 0x5bc: 0x0001
kfffde[35].xptr.flags:                0 ; 0x5be: L=0 E=0 D=0 S=0
kfffde[35].xptr.chk:                118 ; 0x5bf: 0x76
kfffde[36].xptr.au:                1114 ; 0x5c0: 0x0000045a
kfffde[36].xptr.disk:                 1 ; 0x5c4: 0x0001
kfffde[36].xptr.flags:                0 ; 0x5c6: L=0 E=0 D=0 S=0
kfffde[36].xptr.chk:                117 ; 0x5c7: 0x75
kfffde[37].xptr.au:                 695 ; 0x5c8: 0x000002b7
kfffde[37].xptr.disk:                 0 ; 0x5cc: 0x0000
kfffde[37].xptr.flags:                0 ; 0x5ce: L=0 E=0 D=0 S=0
kfffde[37].xptr.chk:                159 ; 0x5cf: 0x9f
kfffde[38].xptr.au:                1115 ; 0x5d0: 0x0000045b
kfffde[38].xptr.disk:                 1 ; 0x5d4: 0x0001
kfffde[38].xptr.flags:                0 ; 0x5d6: L=0 E=0 D=0 S=0
kfffde[38].xptr.chk:                116 ; 0x5d7: 0x74
kfffde[39].xptr.au:                1116 ; 0x5d8: 0x0000045c
kfffde[39].xptr.disk:                 1 ; 0x5dc: 0x0001
kfffde[39].xptr.flags:                0 ; 0x5de: L=0 E=0 D=0 S=0
kfffde[39].xptr.chk:                115 ; 0x5df: 0x73
kfffde[40].xptr.au:                 696 ; 0x5e0: 0x000002b8
kfffde[40].xptr.disk:                 0 ; 0x5e4: 0x0000
kfffde[40].xptr.flags:                0 ; 0x5e6: L=0 E=0 D=0 S=0
kfffde[40].xptr.chk:                144 ; 0x5e7: 0x90
kfffde[41].xptr.au:                1117 ; 0x5e8: 0x0000045d
kfffde[41].xptr.disk:                 1 ; 0x5ec: 0x0001
kfffde[41].xptr.flags:                0 ; 0x5ee: L=0 E=0 D=0 S=0
kfffde[41].xptr.chk:                114 ; 0x5ef: 0x72
kfffde[42].xptr.au:                 697 ; 0x5f0: 0x000002b9
kfffde[42].xptr.disk:                 0 ; 0x5f4: 0x0000
kfffde[42].xptr.flags:                0 ; 0x5f6: L=0 E=0 D=0 S=0
kfffde[42].xptr.chk:                145 ; 0x5f7: 0x91
kfffde[43].xptr.au:                1118 ; 0x5f8: 0x0000045e
kfffde[43].xptr.disk:                 1 ; 0x5fc: 0x0001
kfffde[43].xptr.flags:                0 ; 0x5fe: L=0 E=0 D=0 S=0
kfffde[43].xptr.chk:                113 ; 0x5ff: 0x71
kfffde[44].xptr.au:                1119 ; 0x600: 0x0000045f
kfffde[44].xptr.disk:                 1 ; 0x604: 0x0001
kfffde[44].xptr.flags:                0 ; 0x606: L=0 E=0 D=0 S=0
kfffde[44].xptr.chk:                112 ; 0x607: 0x70
kfffde[45].xptr.au:                 698 ; 0x608: 0x000002ba
kfffde[45].xptr.disk:                 0 ; 0x60c: 0x0000
kfffde[45].xptr.flags:                0 ; 0x60e: L=0 E=0 D=0 S=0
kfffde[45].xptr.chk:                146 ; 0x60f: 0x92
kfffde[46].xptr.au:                1120 ; 0x610: 0x00000460
kfffde[46].xptr.disk:                 1 ; 0x614: 0x0001
kfffde[46].xptr.flags:                0 ; 0x616: L=0 E=0 D=0 S=0
kfffde[46].xptr.chk:                 79 ; 0x617: 0x4f
kfffde[47].xptr.au:                 699 ; 0x618: 0x000002bb
kfffde[47].xptr.disk:                 0 ; 0x61c: 0x0000
kfffde[47].xptr.flags:                0 ; 0x61e: L=0 E=0 D=0 S=0
kfffde[47].xptr.chk:                147 ; 0x61f: 0x93
kfffde[48].xptr.au:                1121 ; 0x620: 0x00000461
kfffde[48].xptr.disk:                 1 ; 0x624: 0x0001
kfffde[48].xptr.flags:                0 ; 0x626: L=0 E=0 D=0 S=0
kfffde[48].xptr.chk:                 78 ; 0x627: 0x4e
kfffde[49].xptr.au:                1122 ; 0x628: 0x00000462
kfffde[49].xptr.disk:                 1 ; 0x62c: 0x0001
kfffde[49].xptr.flags:                0 ; 0x62e: L=0 E=0 D=0 S=0
kfffde[49].xptr.chk:                 77 ; 0x62f: 0x4d
kfffde[50].xptr.au:                 700 ; 0x630: 0x000002bc
kfffde[50].xptr.disk:                 0 ; 0x634: 0x0000
kfffde[50].xptr.flags:                0 ; 0x636: L=0 E=0 D=0 S=0
kfffde[50].xptr.chk:                148 ; 0x637: 0x94
--到此截止
kfffde[51].xptr.au:          4294967295 ; 0x638: 0xffffffff
kfffde[51].xptr.disk:             65535 ; 0x63c: 0xffff
kfffde[51].xptr.flags:                0 ; 0x63e: L=0 E=0 D=0 S=0
kfffde[51].xptr.chk:                 42 ; 0x63f: 0x2a

dd操作

[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL3 bs=1024k count=1 skip=681 seek=0 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0924808 seconds, 11.3 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL4 bs=1024k count=1 skip=1092 seek=1 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.051765 seconds, 20.3 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL4 bs=1024k count=1 skip=1093 seek=2 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0695681 seconds, 15.1 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL3 bs=1024k count=1 skip=682 seek=3 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0516708 seconds, 20.3 MB/s
…………类此处理…………
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL4 bs=1024k count=1 skip=1122 seek=49 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0699421 seconds, 15.0 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL3 bs=1024k count=1 skip=700 seek=50 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0650649 seconds, 16.1 MB/s

修改所有者

[root@rac1 ~]#  chown oracle.oinstall /tmp/xifenfei01.dbf
[root@rac1 ~]# ll /tmp/xifenfei01.dbf 
-rw-r--r-- 1 oracle oinstall 53477376 Apr 30 12:57 /tmp/xifenfei01.dbf

dbv验证数据文件

[oracle@rac1 ~]$ dbv file='+XIFENFEI/xff/datafile/xifenfei.268.781905429' userid=sys/xifenfei

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Apr 30 12:53:32 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +XIFENFEI/xff/datafile/xifenfei.268.781905429


DBVERIFY - Verification complete

Total Pages Examined         : 6400
Total Pages Processed (Data) : 1066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 156
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5178
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
[oracle@rac1 ~]$ dbv file='/tmp/xifenfei01.dbf'

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Apr 30 12:53:50 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /tmp/xifenfei01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 6400
Total Pages Processed (Data) : 1066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 156
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5178
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1039267 (0.1039267)

数据库验证数据文件

SQL> alter database datafile '+XIFENFEI/xff/datafile/xifenfei.268.781905429' offline;

Database altered.

SQL> alter database rename file '+XIFENFEI/xff/datafile/xifenfei.268.781905429'
  2  to '/tmp/xifenfei01.dbf';

Database altered.

SQL>  recover datafile '/tmp/xifenfei01.dbf';
Media recovery complete.
SQL> alter database datafile '/tmp/xifenfei01.dbf' online;

Database altered.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     74537

发表在 Oracle ASM | 6 条评论

bbed修改ASM中数据

发表于 2012 年 4 月 30 日由惜分飞

本篇文章演示了如何从ASM中通过dd拷贝出某种表的记录,然后通过bbed修改相关记录,再拷贝到库中.说的简单点就是通过dd拷贝出最少的需要对象数据块,然后通过bbed绕过数据库级别对相关记录进行修改
模拟测试数据

--ORACLE数据库中执行
SQL> create tablespace xifenfei datafile '+xifenfei' size 30M autoextend on maxsize 10G;

Tablespace created.

SQL> create table t_xifenfei (id number,name varchar2(10)) tablespace xifenfei;

Table created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'XFF');

1 row created.

SQL> SELECT * FROM T_XIFENFEI;

        ID NAME
---------- ----------
         1 xifenfei
         2 XFF

SQL> commit;

Commit complete.

SQL> select EXTENT_ID, BLOCK_ID, BLOCKS, FILE_ID from dba_extents 
  2  where SEGMENT_NAME='T_XIFENFEI' and OWNER='SYS';

 EXTENT_ID   BLOCK_ID     BLOCKS    FILE_ID
---------- ---------- ---------- ----------
         0        128          8          6

SQL> select name from v$datafile where file#=6;

NAME
----------------------------------------------------
+XIFENFEI/xff/datafile/xifenfei.268.781905429

SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME like '%XIFENFEI%';

GROUP_NUMBER
------------
           2

在ASM用户中查询相关数据

--ASM中执行
SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp
  2  FROM x$kffxp                      
  3   WHERE GROUP_KFFXP=2 
  4  AND NUMBER_KFFXP=268; 

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        681          0
         1       1092          1
         1       1093          2
         0        682          3
         1       1094          4
         1       1095          5
         0        683          6
         1       1096          7
         0        684          8
         1       1097          9
         1       1098         10

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        685         11
         1       1099         12
         0        686         13
         1       1100         14
         1       1101         15
         0        687         16
         1       1102         17
         1       1103         18
         0        688         19
         1       1104         20
         0        689         21

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         1       1105         22
         1       1106         23
         0        690         24
         1       1107         25
         0        691         26
         1       1108         27
         1       1109         28
         0        692         29
         1       1110         30

31 rows selected.
--数据文件6的AU分配情况

SQL> select 128*8/1024 from dual;

128*8/1024
----------
         1


SQL> select 8*8/1024 from dual;

  8*8/1024
----------
     .0625
--可以得出该表T_XIFENFEI的数据分布在第二块AU中(DISK_KFFXP=1/AU_KFFXP=1092/XNUM_KFFXP=1)

SQL> select name, path from v$asm_disk where group_number=2     
  2  and disk_number=1;

NAME                           PATH
------------------------------ --------------------------
XIFENFEI_0001                  /dev/oracleasm/disks/VOL4

找出对应磁盘或者分区

[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ cat /proc/partitions |grep "8       18"
   8       18    3879697 sdb2

因为这里的block_id=128,刚好是下一个AU的起点,所以dd操作的起点是第二个AU(DISK_KFFXP=1/AU_KFFXP=1092),而终点是8*8=64K(第二个AU中offset 64KB)

执行dd导出表数据

of=/dev/sdb2
sb=1K
skip=1092*1024=1118208
count=64

[root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208|strings
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.000656471 seconds, 99.8 MB/s
XFF,
xifenfei
[root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208 of=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.00226337 seconds, 29.0 MB/s
[root@rac1 ~]# chown oracle.oinstall /tmp/t_xifenfe.tab 
[root@rac1 ~]# ll /tmp/t_xifenfe.tab 
-rw-r--r-- 1 oracle oinstall 65536 Apr 29 21:54 /tmp/t_xifenfe.tab

bbed 修改数据内容

[oracle@rac1 ~]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 29 22:43:56 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/tmp/t_xifenfe.tab'
        FILENAME        /tmp/t_xifenfe.tab

BBED> set block 4
        BLOCK#          4

BBED> set mode edit
        MODE            Edit

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> map
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                                     Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8041]                        @122     

 ub1 rowdata[25]                            @8163    

 ub4 tailchk                                @8188    


BBED> p kdbr
sb2 kdbr[0]                                 @118      8073
sb2 kdbr[1]                                 @120      8063

BBED> find /c XFF
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 5846462c 010202c1 02087869 66656e66 65690106 ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8170 to 8191  Dba:0x00000000
-------------------------------------------------------
 5846462c 010202c1 02087869 66656e66 l XFF,......xifenf
 65690106 ba33                       l ei...3

 <16 bytes per line>

BBED> m /c xff
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 7866662c 010202c1 02087869 66656e66 65690106 ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8170 to 8191  Dba:0x00000000
-------------------------------------------------------
 7866662c 010202c1 02087869 66656e66 l xff,......xifenf
 65690106 ba33                       l ei...3

 <16 bytes per line>

BBED> find /c xifenfei
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 78696665 6e666569 0106ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8180 to 8191  Dba:0x00000000
-------------------------------------------------------
 78696665 6e666569 0106ba33          l xifenfei...3

 <16 bytes per line>

BBED> m /c XIFENFEI
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 58494645 4e464549 0106ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8180 to 8191  Dba:0x00000000
-------------------------------------------------------
 58494645 4e464549 0106ba33          l XIFENFEI...3

 <16 bytes per line>

BBED> sum
Check value for File 0, Block 4:
current = 0xd332, required = 0xf332

BBED> sum apply
Check value for File 0, Block 4:
current = 0xf332, required = 0xf332

BBED> set offset 8073
        OFFSET          8073

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8073 to 8191  Dba:0x00000000
-------------------------------------------------------
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00002c01 0202c103 l ..........,.....
 03786666 2c010202 c1020858 4946454e l .xff,......XIFEN
 46454901 06ba33                     l FEI...3

 <16 bytes per line>

BBED> exit

dd导入修改后数据验证

--会话1关闭数据库
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:48:51 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


--会话2导入bbed修改后数据
[root@rac1 ~]# dd of=/dev/sdb2 bs=1k count=64 seek=1118208 if=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.0014908 seconds, 44.0 MB/s

--会话1启动数据库库查询
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:51:00 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> select * from t_xifenfei;

        ID NAME
---------- ----------
         1 XIFENFEI
         2 xff

发表在 Oracle ASM, 非常规恢复 | 标签为 bbed | 3 条评论

17813235971

QQ 咨询

分类目录归档：Oracle ASM

通过ftp/http拷贝asm中文件

ASM未正常启动,使用dd找回数据文件

bbed修改ASM中数据

文章分类

最近发表

分类目录归档：Oracle ASM

通过ftp/http拷贝asm中文件

ASM未正常启动,使用dd找回数据文件

bbed修改ASM中数据

标签云

文章分类

最近发表