AIX默认安装ksh，对于习惯了bash的人来说,不能tab自动补全,不能翻上/下,感觉使用起来很不方便,在ksh中不能直接实现这些功能,可以使用另外的方法来完成
一.安装bash程序,使用起来就和bash一样
二.ksh中通过其他方法完成
翻上/下条功能
1、在主目录中 vi .profile
2、添加一行：export EDITOR=vi
3、保存.profile，重新登陆;或者source ~/.profile
现在如果要使用翻上/下条功能,只需要按下esc键,然后使用j/k翻上/下即可;如果要退回到输入功能,直接输入i,然后输入即可.其实所有操作就是和vi中的操作一样.
自动补全功能
使用esc+\
记录一次比较棘手数据库恢复要点
在最近的一次数据库异常恢复过程中遇到不少问题,把重点记录下
ORA-00704/ORA-01555错误
Fri May 4 21:04:21 2012 select ctime, mtime, stime from obj$ where obj# = :1 Fri May 4 21:04:21 2012 Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 40 with name "_SYSSMU40$" too small Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 1286288 ORA-1092 signalled during: alter database open resetlogs... 这里的提示可以看出obj$基表中有事务存在,查询这个表的时候,要去找40号回滚段中相关数据;通过非常规方法, 查找到40号回滚段的状态是offliine了(这个查询出来的信息和是否使用隐含参数无关). 问题原因,为什么40号回滚段变得offline？ Fri May 4 17:36:26 2012 alter tablespace undotbs offline Fri May 4 17:36:26 2012 ORA-1109 signalled during: alter tablespace undotbs offline... Fri May 4 17:37:29 2012 alter database datafile '/dev/rundodbs01' offline drop Fri May 4 17:37:29 2012 Completed: alter database datafile '/dev/rundodbs01' offline drop 因为强制offline 了file# 2文件导致(一个undo表空间文件) 解决方法: 1.bbed提交事务 因为现在生产的trace文件中未有关于obj$ 未提交事务的记录,做10046也为发现该记录,如果要使用bbed修改该事务, 那需要dump obj$相关的数据块(在mount状态下dump),然后找到相关事务,再修改 2.强制让file# 2 online 因为在resetlogs前file#2 已经offline掉了,所以要使得该文件能够成功online,需要先推进scn
ORA-00600[krhpfh_03-1209]
SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [krhpfh_03-1209], [2], [782415504], [782428968], [3987078030], [2379], [0], [0] ORA-01110: data file 2: '/dev/rundodbs01' 问题原因： 数据库处于非归档模式下,连续三次resetlogs,引起该bug 解决办法： 重建控制文件 但是这里问题出现了,因为file# 2的resetlogs scn和其他数据文件不一致,导致在file# 2 online的前提下,无法重建. 这样就处在了一个循环中(需要online file# 2 又要重建控制文件),这样的问题,可以通过bbed修改file# 2的resetlogs scn完成 或者先让file# 2 offline(没有加drop)掉，重建控制文件(除掉file# 2的文件记录)
ORA-00600[25025]
SMON: enabling cache recovery Fri May 4 22:36:36 2012 Errors in file /oracle/admin/standdb/udump/perfdb_ora_1167402.trc: ORA-00600: internal error code, arguments: [25025], [2], [], [], [], [], [], [] Fri May 4 22:36:38 2012 Errors in file /oracle/admin/standdb/udump/perfdb_ora_1167402.trc: ORA-00600: internal error code, arguments: [25025], [2], [], [], [], [], [], [] Fri May 4 22:36:38 2012 Error 600 happened during db open, shutting down database USER: terminating instance due to error 600 Instance terminated by USER, pid = 1167402 错误原因: 因为有undo文件不在undo对应的表空间中,而我们的file# 2文件确实是undo文件,而且重建控制文件时候未加入进来 解决办法: undo_management = AUTO undo_tablespace = UNDODBS(file# 2属于该表空间) 修改为 undo_management = MANUAL undo_tablespace = SYSTEM 或者bbed修改file# 2的header,然后重建控制文件
ORA-00600[4137]
Errors in file /oracle/admin/standdb/bdump/perfdb_smon_1290564.trc: ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], [] Fri May 4 23:20:52 2012 create undo tablespace undotbs3 datafile '/dev/rundodbs21' size 20400M Fri May 4 23:23:47 2012 Errors in file /oracle/admin/standdb/bdump/perfdb_smon_1290564.trc: ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], [] Fri May 4 23:23:48 2012 Errors in file /oracle/admin/standdb/bdump/perfdb_pmon_1520126.trc: ORA-00474: SMON process terminated with error Fri May 4 23:23:48 2012 PMON: terminating instance due to error 474 Instance terminated by PMON, pid = 1520126 错误原因: _smon_internal_errlimit(limit of SMON internal errors) SMON遇到了内部错误，最大允许100次， 不断计数增长,达到100的时候,数据库smon进程自动down掉,从而导致数据库down 解决办法: 1.临时解决办法:设置_smon_internal_errlimit一个较大值 3.根本解决办法:使用undo隐含参数,删除有问题undo 回滚段和undo表空间或者使用10513 事件
Bind Variable Peeking 测试
相关参数
SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ---------------------- ----------------
optimizer_mode string ALL_ROWS
SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ---------------------- ----------------
cursor_sharing string EXACT
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
2 from x$ksppi a,x$ksppcv b
3 where a.inst_id = USERENV ('Instance')
4 and b.inst_id = USERENV ('Instance')
5 and a.indx = b.indx
6 and upper(a.ksppinm) LIKE upper('%¶m%')
7 order by name
8 /
Enter value for param: _optim_peek_user_binds
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
new 6: and upper(a.ksppinm) LIKE upper('%_optim_peek_user_binds%')
NAME VALUE DESCRIPTION
-------------------------------- ------------------------ ----------------------------------
_optim_peek_user_binds TRUE enable peeking of user binds
创建模拟表
SQL> create table t_xifenfei(id number,name varchar2(30)); Table created. SQL> begin 2 for i in 1..100000 loop 3 insert into t_xifenfei values(i,'xifenfei'); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed. SQL> update t_xifenfei SET name='www.xifenfei.com' where mod(id,20000)=0; 5 row updated. SQL> commit; Commit complete. SQL> create index i_xifenfei on t_xifenfei(name); Index created.
默认收集统计信息,查看执行计划
SQL> exec DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select id from t_xifenfei where name='xifenfei';
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='xifenfei')
SQL> select id from t_xifenfei where name='www.xifenfei.com';
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='www.xifenfei.com')
--这里可以发现,对于这样少量的列的情况，没有选择一个合适的执行计划
准确收集统计信息
SQL> exec DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE,
2 method_opt => 'FOR ALL COLUMNS SIZE 254',estimate_percent => 100);
PL/SQL procedure successfully completed.
再次查看执行计划
SQL> select id from t_xifenfei where name='www.xifenfei.com';
Execution Plan
----------------------------------------------------------
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='www.xifenfei.com')
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
320 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> select id from t_xifenfei where name='xifenfei';
99995 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 1367K| 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 99999 | 1367K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='xifenfei')
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
6970 consistent gets
0 physical reads
0 redo size
1455968 bytes sent via SQL*Net to client
73745 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99995 rows processed
--通过这里可以看出在完整的收集表和index包括直方图信息后,数据库执行计划正常
--也说明一点:在数据列分布不均匀的时候,依靠数据库自动收集直方图还是不怎么拷贝.
使用AUTOTRACE测试
SQL> set autot trace exp
SQL> var a varchar2(30);
SQL> exec :a := 'www.xifenfei.com';
PL/SQL procedure successfully completed.
SQL> select id from t_xifenfei where name=:a;
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"=:A)
--这里可以发现11g的Bind Variable Peeking 没有使用正确的执行计划,其实这个是AUTOTRACE本身的bug导致
收集下面sql执行计划(peeking测试需要)get_plan.sql脚本
SQL> select * from t_xifenfei where name='wwww.xifenfei.com' and id=100;
no rows selected
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 2708637417
select * from t_xifenfei where name='wwww.xifenfei.com' and id=100
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
2 - access("NAME"='wwww.xifenfei.com')
SQL> select * from t_xifenfei where name='xifenfei' and id=100;
ID NAME
---------- ------------------------------------------------------------
100 xifenfei
1 row selected.
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 1355242984
select * from t_xifenfei where name='xifenfei' and id=100
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 103 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ID"=100 AND "NAME"='xifenfei'))
--这里可以看到,两个执行计划都我们希望的
测试peeking功能
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei where name='xifenfei' and id=100;
ID NAME
---------- ------------------------------------------------------------
100 xifenfei
1 row selected.
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 2860562673
select * from t_xifenfei where name='xifenfei' and id=100
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 103 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ID"=100 AND "NAME"='xifenfei'))
SQL> var b varchar2(30);
SQL> exec :b := 'www.xifenfei.com';
PL/SQL procedure successfully completed.
SQL> select * from t_xifenfei where name=:b and id=100;
no rows selected
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 103 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ID"=100 AND "NAME"=:B))
--重新硬解析
SQL> alter system flush shared_pool;
System altered.
SQL> var b varchar2(30);
SQL> exec :b := 'www.xifenfei.com';
PL/SQL procedure successfully completed.
SQL> select * from t_xifenfei where name=:b and id=100;
no rows selected
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
2 - access("NAME"=:B)
SQL> var b varchar2(30);
SQL> exec :b := 'xifenfei';
PL/SQL procedure successfully completed.
SQL> select * from t_xifenfei where name=:b and id=100;
ID NAME
---------- ------------------------------------------------------------
100 xifenfei
1 row selected.
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
2 - access("NAME"=:B)
--虽然oracle 11g宣称在在Bind Variable Peeking上增强了很多,
--但是这里的实验,依然证明他存在问题,导致执行计划不正确
通过整体实验过程,证明几个问题：
1.默认的的DBMS_STATS收集统计信息不一定使得所有执行计划均正确,特别在数据很不均匀分布时.
2.AUTOTRACE不能跟踪Bind Variable Peeking
3.Bind Variable Peeking是在硬解析时候生效,虽然11g进行了改善,但是有些时候效果还是不明显,如果数据很不均匀,在发现sql语句很多不合适的时候,建议先删除该sql的执行计划,让其再次硬解析,碰碰运气,如果一直效果不好,建议不适用绑定参数形式(正确的执行计划,更多的硬解析)
4._optim_peek_user_binds参数可以关闭Bind Variable Peeking功能,很不推荐.
通过ftp/http拷贝asm中文件
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=
3.ftp和http两个功能,只需要配置一个即可(选择你需要的)
4.下图展示的是通过http方式访问结果(system用户登录)
bbed 找回被删除数据
创建模拟表数据
SQL> create table t_xifenfei(id number,name varchar2(10)); Table created. SQL> insert into t_xifenfei values(1,'xifenfei'); 1 row created. SQL> insert into t_xifenfei values(2,'XIFENFEI'); 1 row created. SQL> commit; Commit complete.
dump数据块
SQL> alter system flush BUFFER_CACHE; System altered. SQL> select rowid,id,name, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from chf.t_xifenfei; ROWID ID NAME REL_FNO BLOCKNO ROWNO ------------------ ---------- ---------- ---------- ---------- ---------- AAASdmAAEAAAACvAAA 1 xifenfei 4 175 0 AAASdmAAEAAAACvAAB 2 XIFENFEI 4 175 1 SQL> alter system dump datafile 4 block 175; System altered.
dump文件内容
block_row_dump: tab 0, row 0, @0x1f89 tl: 15 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 8] 78 69 66 65 6e 66 65 69 tab 0, row 1, @0x1f7a tl: 15 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [ 8] 58 49 46 45 4e 46 45 49 end_of_block_dump 2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4] End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
删除表数据
SQL> delete from t_xifenfei; 2 rows deleted. SQL> commit; Commit complete. SQL> alter system flush BUFFER_CACHE; System altered. SQL> alter system dump datafile 4 block 175; System altered.
dump文件内容
block_row_dump: tab 0, row 0, @0x1f89 tl: 2 fb: --HDFL-- lb: 0x2 tab 0, row 1, @0x1f7a tl: 2 fb: --HDFL-- lb: 0x2 end_of_block_dump 2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4] End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通过对比这两次的dump文件发现
1.数据内容被删除,并不是真正删除,而是给其增加了一个标识位(fd:---D----) 2.fb:--H-FL--(head of row piece+first data piece+last data piece ) 其有8个选项每个选项的值分别对应bitmask即32+8+4=44 or 0x2c 3.如果一个row被delete了,那么row flag就会更新,bitmask里的deleted被设置为16. 此时row flag为：32+16+8+4 = 60 or 0x3c. 4.如果我们要找回来被删除的数据,只需要把3c改为2c即可
关闭数据库
SQL> select * from chf.t_xifenfei; no rows selected SQL> select name from v$datafile where file#=4; NAME ------------------------------------------------ /tmp/user01.dbf SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
bbed修改数据
BBED> set filename '/tmp/user01.dbf'
FILENAME /tmp/user01.dbf
BBED> set block 175
BLOCK# 175
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set mode edit
MODE Edit
BBED> map
File: /tmp/user01.dbf (0)
Block: 175 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[8036] @122
ub1 rowdata[30] @8158
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15] @8173 0x3c
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @8158 0x3c
BBED> m /x 2c offset 8158
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8158 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106
b47e
<32 bytes per line>
BBED> m /x 2c offset 8173
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8173 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1020878 6966656e 66656901 06b47e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13
启动数据库验证
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 chf.t_xifenfei;
ID NAME
---------- ----------
1 xifenfei
2 XIFENFEI
exp导出数据报EXP-00056/ORA-01403错误
exp导出数据报EXP-00056/ORA-01403错误
[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf Export: Release 9.2.0.4.0 - Production on Sun Apr 29 03:11:31 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user CHF . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user CHF About to export CHF's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions EXP-00056: ORACLE error 1403 encountered ORA-01403: no data found EXP-00000: Export terminated unsuccessfully
查看组件信息
SQL> select * from v$version; BANNER ------------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production SQL> col comp_id for a15 SQL> col status for a7 SQL> col version for a10 SQL> col comp_name for a30 SQL> set pagesize 1000 SQL> SELECT substr(comp_id,1,15) comp_id, status, substr(version,1,10) version, 2 substr(comp_name,1,30) comp_name FROM dba_registry ORDER BY 1; COMP_ID STATUS VERSION COMP_NAME --------------- ------- ---------- ------------------------------ AMD VALID 9.2.0.4.0 OLAP Catalog APS LOADED 9.2.0.4.0 OLAP Analytic Workspace CATALOG VALID 9.2.0.4.0 Oracle9i Catalog Views CATJAVA VALID 9.2.0.4.0 Oracle9i Java Packages CATPROC VALID 9.2.0.4.0 Oracle9i Packages and Types CONTEXT VALID 9.2.0.4.0 Oracle Text JAVAVM VALID 9.2.0.4.0 JServer JAVA Virtual Machine ODM LOADED 9.2.0.1.0 Oracle Data Mining ORDIM VALID 9.2.0.4.0 Oracle interMedia OWM VALID 9.2.0.1.0 Oracle Workspace Manager SDO LOADED 9.2.0.4.0 Spatial WK VALID 9.2.0.4.0 Oracle Ultra Search XDB VALID 9.2.0.4.0 Oracle XML Database XML VALID 9.2.0.6.0 Oracle XDK for Java XOQ LOADED 9.2.0.4.0 Oracle OLAP API 15 rows selected. SQL> SELECT status, object_id, object_type, owner||'.'||object_name 2 "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID' 3 ORDER BY 4,2; no rows selected
做1403跟踪
SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack level 3'; [oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack off';
trace文件关键内容
*** SESSION ID:(11.17) 2012-04-29 03:17:13.555 *** 2012-04-29 03:17:13.555 ksedmp: internal or fatal error ORA-01403: no data found Current SQL statement for this session: SELECT xdb_uid FROM SYS.EXU9XDBUID
问题原因
因为控制文件重建或者使用历史控制文件恢复,忘记添加临时文件
SQL> select name from v$tempfile; no rows selected
解决方法
添加临时文件
SQL> alter tablespace TEMP add tempfile 2 '/u01/oracle/oradata/xifenfei/temp01.dbf' size 10M reuse; Tablespace altered. SQL> select name from v$tempfile; NAME ------------------------------------------------ /u01/oracle/oradata/xifenfei/temp01.dbf
验证exp导出
[oracle@xifenfei udump]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf Export: Release 9.2.0.4.0 - Production on Sun Apr 29 05:20:21 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user CHF . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user CHF About to export CHF's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export CHF's tables via Conventional Path ... . . exporting table ACC_OWE_TASK_LIST_HIS_07711202 4111 rows exported . . exporting table CHF_XIFENFEI 868 rows exported . . exporting table PLAN_TABLE 0 rows exported . . exporting table T_XFF01 0 rows exported . . exporting table T_XIFENFEI 1 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully no warnings.
sql profile 使用
本实验室为了说明sql profile的使用方法,不去研讨sql的执行效率.通过sql profile的方法使得一条本该使用index的sql该走全表扫描.
创建模拟表
SQL> select * from v$version; BANNER ----------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for 32-bit Windows: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> create table t_xifenfei as select * from dba_objects; 表已创建。 SQL> create index i_xifenfei on t_xifenfei(object_id); 索引已创建。 SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE); PL/SQL 过程已成功完成。
默认使用INDEX
SQL> SET AUTOT TRACE EXP
SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;
执行计划
----------------------------------------------------------
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
使用hint实现全表扫描
SQL> SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=10
0;
执行计划
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
查找hint对应sql的sql_id
SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'; SQL_ID ------------- SQL_TEXT -------------------------------------------------------------------------------- 0bbt69m5yhf3p SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100 68r1cnxmn8fjk SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%' ddmhrzhatfdyh EXPLAIN PLAN SET STATEMENT_ID='PLUS570193' FOR SELECT /*+ FULL(T_XIFENFEI)*/OBJE CT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100 SQL_ID ------------- SQL_TEXT -------------------------------------------------------------------------------- bybs0sds8yu9c SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'
获得对应Outline
SQL> SET PAGESIZE 10000
SQL> select * from table(dbms_xplan.display_cursor('0bbt69m5yhf3p',null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0bbt69m5yhf3p, child number 0
-------------------------------------
SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 300 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
已选择33行。
创建sql profile
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',
'DB_VERSION(''11.2.0.3'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")', --这个是由于hint产生,其实我们需要的就是这个
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100',
v_hints,'SQLPROFILE_XIFENFEI', --sql profile 名称
force_match=>true,replace=>true);
end;
/
验证sql profile
SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;
执行计划
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL profile "SQLPROFILE_XIFENFEI" used for this statement
DB2 主要行级锁模拟
S行锁和X行锁模拟
--会话1
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei(id int,name varchar(100))"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c "insert into t_xifenfei values(1,'www.xifenfei.com')"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:07:02 -- Date 2012-04-29-01.46.48.462742
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
0x99B3AE40 8 53514C4332473137315992A241 Internal P ..S G 8 1 0 0x00 0x40000000
0x99B3A690 8 02000700040080000000000052 Row ..X G 8 1 0 0x08 0x40000000
0x99B3A6F0 8 02000700000000000000000054 Table .IX G 8 1 0 0x00 0x40000000
--会话2
[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"
hang住
--会话3查询等待
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:08:40 -- Date 2012-04-29-01.48.26.676607
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
0x99B3AE40 8 53514C4332473137315992A241 Internal P ..S G 8 1 0 0x00 0x40000000
0x99B3A540 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000
0x99B3A690 8 02000700040080000000000052 Row ..X G 8 1 0 0x08 0x40000000
0x99B40C60 7 02000700040080000000000052 Row .NS W 8 1 0 0x00 0x00000001
0x99B3A420 7 02000000010000000100407056 Internal V ..S G 7 1 0 0x00 0x40000000
0x99B3A6F0 8 02000700000000000000000054 Table .IX G 8 1 0 0x00 0x40000000
0x99B3A510 7 02000700000000000000000054 Table .IS G 7 1 0 0x00 0x00000001
--这里可以发现Sts=W(STATUS=WAIT),会话的NS锁处于等待状态
--会话1
[db2inst1@xifenfei ~]$ db2 commit
DB20000I The SQL command completed successfully.
--会话2
[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"
ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 www.xifenfei.com
1 record(s) selected.
--结果出现
--会话3
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:09:06 -- Date 2012-04-29-01.48.52.069878
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
U锁模拟
--会话1
[db2inst1@xifenfei ~]$ db2 +c "declare c1 cursor for select * from t_xifenfei for update"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c open c1
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c fetch c1
ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 WWW.XIFENFEI.COM
1 record(s) selected.
--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:27 -- Date 2012-04-29-02.07.13.594441
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
0x99B3A420 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000
0x99B3A510 7 02000700040080000000000052 Row ..U G 7 1 0 0x00 0x00000001
0x99B40C60 7 01000000010000000100807256 Internal V ..S G 7 1 0 0x00 0x40000000
0x99B3A4E0 7 02000700000000000000000054 Table .IX G 7 1 0 0x00 0x00000001
--会话1
[db2inst1@xifenfei ~]$ db2 +c "update t_xifenfei set name='www.xifenfei.com'"
DB20000I The SQL command completed successfully.
--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:44 -- Date 2012-04-29-02.07.30.684616
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
0x99B3A420 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000
0x99B3A510 7 02000700040080000000000052 Row ..X G 7 2 0 0x00 0x40000001
0x99B40C60 7 01000000010000000100807256 Internal V ..S G 7 1 0 0x00 0x40000000
0x99B3A4E0 7 02000700000000000000000054 Table .IX G 7 2 0 0x00 0x40000001
--行级锁由U升级到X
--会话1
[db2inst1@xifenfei ~]$ db2 commit
DB20000I The SQL command completed successfully.
--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:31:43 -- Date 2012-04-29-02.11.29.167659
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
ASM未正常启动,使用dd找回数据文件
本篇主要测试在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
bbed修改ASM中数据
本篇文章演示了如何从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