-
加我微信
加我QQ
标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,609)
- DB2 (22)
- MySQL (71)
- Oracle (1,473)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (535)
- Oracle安装升级 (85)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 11.2.0.4最新psu-202404
- ORA-600 2131故障处理
- 收集统计信息报ORA-00600 16515问题解决
- rm -rf误删Oracle数据库恢复
- 分布式存储故障导致数据库无法启动故障处理
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG（单机to单机）
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
月归档：九月 2012
ora程序简单测试
oracle的监控很多,朋友推荐的ora工具,测试了下,发现确实很好用也很强大
ora使用帮助
[oracle@xifenfei ~]$ ./ora
Syntax Error
Usage: ora [-u user] [-i instance#] <command> [<arguments>]
General
-u user/pass use USER/PASS to log in
-i instance# append # to ORACLE_SID
-sid <sid> set ORACLE_SID to sid
-top # limit some large queries to on # rows
- repeat <interval> <count|forever> <ora command>
Repeat an coomand <count> time
Sleep <interval> between two calls
Command are:
- execute: cursors currently being executed
- longops: run progression monitor
- sessions: currently open sessions
- stack <os_pid> get process stack using oradebug
- cursors [all] <match_str>: [all] parsed cursors
- sharing <sql_id>: print why cursors are not shared
- events [px]: events that someone is waiting for
- ash <minutes_from_now>
[duration]
[-f <file_name>] active session history for specified period
e.g. 'ash 30' to display from [now - 30min]
to [now]
e.g. 'ash 30 10 -f foo.txt' to display a 10
minutes period from [now - 30min]
and store the result in file foo.txt
- ash_wait_graph <minutes_from_now>
[duration]
[-f <file_name>] PQ event wait graph using ASH data
Arguments are the same as for ash
except that the output must be shown
with the mxgraph tool
- ash_sql <sql_id> Show all ash rows group by sampli_time and
event for the specified sql_id
- [-u <user/passwd>] degree degree of objects for a given user
- [-u <user/passwd>] colstats stats for each table, column
- [-u <user/passwd>] tabstats stats for each table
- params [<pattern>]: view all parameters, even hidden ones
- snap: view all snapshots status
- bc: view contents of buffer cache
- temp: view used space in temp tbs
- asm: Show asm space/free space
- space [<tbs>]: view used/free space in a given tbs
- binds <sql_id> : display bind capture information for
specified cursor
- fulltext <sql_id> : display the entire SQL text of the
specified statement
- last_sql_hash [<sid>]: hash value of the last styatement executed
by the specified sid. If no sid speficied,
return the last hash_value of user sessions
- openv <sql_id>
[<pattern>]: display optimizer env parameters for
specified cursor
- plan <sql_id> [<fmt>]: get explain plan of a particular cursor
- pxplan <sql_id> : get explain plan of a particular cursor and
all connected cursor slave SQL
- wplan <sql_id> [<fmt>]: get explain plan with work area information
- pxwplan <sql_id> : get explain plan with work area information
of a particular cursor and all connected
cursor slave SQL
- eplan <sql_id> [<fmt>]: get explain plan with execution statistics
- pxeplan <sql_id> : get explain plan with execution statistics
of a particular cursor and all connected
cursor slave SQL
- gplan <sql_id> : get graphical explain plan of a particular
cursor using dot specification
- webplan <sql_id> get graphical explain plan of a particular
[/<child_number>] cursor using gdl specification
[<decorate>]: optional: child_number, default is zero.
optional: decorate to print further node
information. default is 0,
1 => print further node information such as
cost, filter_predicates etc.
2 => in addition to the above, print
row vector information
sample usage:
# ora webplan 4019453623
print more information (decorate 1)
# ora webplan 4019453623/1 1
more information, overload! (decorate 2)
# ora webplan 4019453623/1 2
using sql_id along with child number
instead of hash value
# ora webplan aca4xvmz0rzup/3 1
- hash_to_sqlid <sql_id> : get the sql_id of the cursor given its hash
value
- sqlid_to_hash <sql_id>: get the hash value of the cursor given its
(unquoted) sql_id
- exptbs: generate export tablespace script
- imptbs: generate import tablespace script
- smm [limited]: SQL memory manager stats for active
workareas
- onepass: Run an ora wplan on all one-pass cursors
- mpass: Run an ora wplan on all multi-pass cursors
- pga: tell how much pga memory is used
- pga_detail <os_pid>|
-mem <size_mb>: Gives details on how PGA memory is consumed
by a process (given its os PID) or by the
set of precesses consuming more than
<size_mb> MB of PGA memory (-mem option)
- pgasnap [<snaptab>] Snapshot the pga advice stats
- pgaadv [-s [<snaptab>]]
[-o graphfile]
[-m min_size]: generate a graph from v
and display it or store it in a
file if the -o option is used.
-s [<snaptab>] to diff with a
previous snapshot (see pgasnap cmd)
-o [graphfile] to store the result
in a file instead of displaying it
-m [min_size] only consider
workareas with a minimum size
- pgaadvhist [-f <f_min>
[<f_max>]] display the advice history for all
factors or for factor between
f_min and f_max
- sga: tell how much sga memory is used
- sga_stats: tell how sga is dynamically used
- sort_usage: tell how temp tablespace is used in detail
- sgasnap [<snaptab>] Snapshot the sga advice stats
- sgaadv [-s [<snaptab>]]
[-o graphfile] generate a graph from v
and v and store it in a
file if the -o option is used.
-s [<snaptab>] to diff with a
previous snapshot (see sgasnap cmd)
-o [graphfile] to store the result
in a file instead of displaying it
- process [<min_mb>]: display process info with pga memory
- version: display Oracle version number
- cur_mem [ <sql_id> ] display the memory used for a
given or all cursors
- shared_mem [ <sql_id> ] detailed dump of cursor shared mem
allocations
- runtime_mem [ <sql_id> ] detailed dump of cursor runtime
memory allocations
- all_mem [ <sql_id> ] do all of the memory dumps
- pstack <pid>|all
[<directory>] run pstack on specified process
(or all if 'all' specified) and store
files in specified dir ( when
not specified)
- idxdesc [username]
<tabName> list all indexes for a given user or
for a given user and table
- segsize [username]
<objName> list size of all objects(segments) for
given user for a given user and object
- tempu <username> list temporary ts usage of all users or
for a given user
- sqlstats [ <sql_id> ] list sql execution stats (like
buffer_gets, phy. reads etc)
for a given sql_id/hash_value of statement
- optstats [username] list optimizer stats for all tables stored
<tabname> in dictionary for a given user or for a
given user and table
- userVs list all user Views (user_tables,
user_indexes etc)
- fixedVs list all V$ Views
- fixedXs list all X$ Views
- px_processes list all px processes (QC and slaves)
- cursor_summary summarize stats about (un)pinned cursors
- rowcache summarizes row cache statistics
- monitor_list lists all the statements that have been
monitored
- monitor 1: wraps dbms_sqltune.report_sql_monitor().
Directly passe the arguments to the PL/SQL
procedure. Args are:
sql_id, session_id, session_serial,
sql_exec_start, sql_exec_id, inst_id,
instance_id_filter, parallel_filter,
report_level, type.
Examples:
- monitor xml shows XML report
- monitor show last monitored stmt
- monitor sql_id=>'8vz99cy9bydv8',
session_id=>105 will
show monitor info for sql_id
8vz99cy9bydv8 and session_id 105
Use simply ora monitor 8vz99cy9bydv8
to display monitoring information for
sql_id 8vz99cy9bydv8.
Syntax for parallel filters is:
[qc][servers(<svr_grp>[,] <svr_set>[,]
<srv_num>)]
Use /*+ monitor */ to force monitoring.
- monitor_old [ash_all] [<sqlid>]
[qc|<slave_grp#> [<slave_set#> [<slave#>]]]
Old version of SQL monitoring, use a
SQL query versus the report_sql_monitor()
package. Display monitoring info for the
LAST execution of the specified cursor.
Cursor response time needs to be at
least 5s for monitoring to start (use the
monitor hint to force monitoring). Without
any parameter, will display monitoring info
for the last cursor that was monitored
- ash_all will aggregate ash data over all
executions of the cursor (useful for short
queries that are executed many times).
If parallel:
- qc to see only data for qc
- slave_grp# to see only data for one
parallelizer
- slave_grp# + slave_set# to see only data
for one slave set of one parallelizer,
- slave_grp# + slave_set# + slave# to see
data only for the specified slave
- sql_task [progress | interrupt <task_name> | history <#execs> |
report <task_name> <section> <exec_name> ]
progress: progress monitoring for executing
sql tasks
interrupt: interrupt an executing sql task
history: print a history of last n executions
report: get a sql tune report
- sh Run a shell command. E.g.
ora repeat 5 10 sh 'ps -edf | grep DESC'
Memory: The detailed memory dumps need to have events set to work.
The events bellow can be added to the init.ora file
event="10277 trace name context forever, level 10" # mutable mem
event="10235 trace name context forever, level 4" # shared mem
NOTE
====
- Set environment variable ORA_USE_HASH to 1 to get SQL hash values
instead of SQL ids
- Set environment variable DBUSER to change default connect string which
is "/ as sysdba"
- Set environment variable ORA_TMP to the default temp directory (default
if /tmp when not set)
数据库版本
[oracle@xifenfei ~]$ ./ora version Oracle version: 11.2.0.3.0
正在执行sql
[oracle@xifenfei ~]$ ./ora execute
SQL_ID EXEC SQL_TEXT
------------------ ----- ---------------------------------------------------------------------------
g6gu1n3x0h1h4 1 select streams_pool_size_for_estimate s,
streams_pool_size_factor * 100 f, estd_spill_time +
estd_unspill_time, 0 from v$streams_pool_advice
5yv7yvjgjxugg 1 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO
Pool Memory'
0rc4km05kgzb9 1 select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
当前的会话
[oracle@xifenfei ~]$ ./ora sessions
SID SERIAL# CL_PID PID USERNAME TYPE SERVER PROGRAM SQL_ID
---------- ---------- ---------- ---------- -------------------- ---------- --------- ------------------------------------------------ --------
ACTION
----------------------------------------------------------------
1 1 706 706 BACKGROUND DEDICATED oracle@xifenfei (PMON)
126 1 710 710 BACKGROUND DEDICATED oracle@xifenfei (PSP0)
2 1 714 714 BACKGROUND DEDICATED oracle@xifenfei (VKTM)
127 1 720 720 BACKGROUND DEDICATED oracle@xifenfei (GEN0)
3 1 724 724 BACKGROUND DEDICATED oracle@xifenfei (DIAG)
128 1 728 728 BACKGROUND DEDICATED oracle@xifenfei (DBRM)
4 1 732 732 BACKGROUND DEDICATED oracle@xifenfei (DIA0)
129 1 736 736 BACKGROUND DEDICATED oracle@xifenfei (MMAN)
5 1 740 740 BACKGROUND DEDICATED oracle@xifenfei (DBW0)
130 1 744 744 BACKGROUND DEDICATED oracle@xifenfei (LGWR)
6 1 748 748 BACKGROUND DEDICATED oracle@xifenfei (CKPT)
131 1 752 752 BACKGROUND DEDICATED oracle@xifenfei (SMON)
7 1 756 756 BACKGROUND DEDICATED oracle@xifenfei (RECO)
132 1 760 760 BACKGROUND DEDICATED oracle@xifenfei (MMON)
8 1 764 764 BACKGROUND DEDICATED oracle@xifenfei (MMNL)
125 87 12732 12732 BACKGROUND DEDICATED oracle@xifenfei (W000)
KTSJ Slave
15 141 13132 13136 SYS USER DEDICATED sqlplus@xifenfei (TNS V1-V3) d5zj45xcq95d3
9 5 819 819 BACKGROUND DEDICATED oracle@xifenfei (ARC0)
135 5 823 823 BACKGROUND DEDICATED oracle@xifenfei (ARC1)
10 1 827 827 BACKGROUND DEDICATED oracle@xifenfei (ARC2)
136 3 831 831 BACKGROUND DEDICATED oracle@xifenfei (ARC3)
12 1 835 835 BACKGROUND DEDICATED oracle@xifenfei (QMNC)
QMON Coordinator
133 11 855 855 BACKGROUND DEDICATED oracle@xifenfei (Q000)
QMON Slave
14 7 896 896 BACKGROUND DEDICATED oracle@xifenfei (SMCO)
KTSJ Coordinator
17 3 859 859 BACKGROUND DEDICATED oracle@xifenfei (Q001)
QMON Slave
sql执行情况
[oracle@xifenfei ~]$ ./ora ash_sql d5zj45xcq95d3 SAMPLE_TIME ACTIVITY P1 P2 NAME NB ---------------------------------------- ------------------------------ ------------------------------ -------------------- ---------- ---- 13-JAN-12 03.07.05.299 AM CPU - - 1
完整sql语句
[oracle@xifenfei ~]$ ./ora fulltext d5zj45xcq95d3
SQL_ID SQL_FULLTEXT
------------------ ---------------------------------------------------------------------------
d5zj45xcq95d3 select s.sid, s.serial#, s.PROCESS cl_pid, p.spid pid, s.username,
s.type, s.server, s.PROGRAM, sql_id sql_id, s.action
from v$session s, v$process p
where s.PADDR = p.addr
sql执行计划
[oracle@xifenfei ~]$ ./ora plan d5zj45xcq95d3
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID d5zj45xcq95d3, child number 0
-------------------------------------
select s.sid, s.serial#, s.PROCESS cl_pid, p.spid pid, s.username,
s.type, s.server, s.PROGRAM, sql_id sql_id, s.action from
v$session s, v$process p where s.PADDR = p.addr
Plan hash value: 1456042965
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | NESTED LOOPS | | 1 | 264 | 0 (0)|
| 2 | NESTED LOOPS | | 1 | 251 | 0 (0)|
| 3 | MERGE JOIN CARTESIAN | | 5 | 350 | 0 (0)|
|* 4 | FIXED TABLE FULL | X$KSUPR | 1 | 44 | 0 (0)|
| 5 | BUFFER SORT | | 100 | 2600 | 0 (0)|
| 6 | FIXED TABLE FULL | X$KSLWT | 100 | 2600 | 0 (0)|
|* 7 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 181 | 0 (0)|
|* 8 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)|
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$573A9BEE
4 - SEL$573A9BEE / X$KSUPR@SEL$5
6 - SEL$573A9BEE / W@SEL$3
7 - SEL$573A9BEE / S@SEL$3
8 - SEL$573A9BEE / E@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
7 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND
"S"."KSUSEPRO"="ADDR" AND "S"."INDX"="W"."KSLWTSID"))
8 - filter("W"."KSLWTEVT"="E"."INDX")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "KSUPRPID"[VARCHAR2,24], "S"."INDX"[NUMBER,22],
"S"."KSSPATYP"[NUMBER,22], "S"."KSUUDLNA"[VARCHAR2,30],
"S"."KSUSESER"[NUMBER,22], "S"."KSUSEFLG"[NUMBER,22],
"S"."KSUSEPID"[VARCHAR2,24], "S"."KSUSEPNM"[VARCHAR2,48],
"S"."KSUSESQI"[VARCHAR2,13], "S"."KSUSEACT"[VARCHAR2,64]
2 - "KSUPRPID"[VARCHAR2,24], "W"."KSLWTEVT"[NUMBER,22],
"S"."INDX"[NUMBER,22], "S"."KSSPATYP"[NUMBER,22],
"S"."KSUUDLNA"[VARCHAR2,30], "S"."KSUSESER"[NUMBER,22],
"S"."KSUSEFLG"[NUMBER,22], "S"."KSUSEPID"[VARCHAR2,24],
"S"."KSUSEPNM"[VARCHAR2,48], "S"."KSUSESQI"[VARCHAR2,13],
"S"."KSUSEACT"[VARCHAR2,64]
3 - "ADDR"[RAW,4], "KSUPRPID"[VARCHAR2,24], "W"."KSLWTSID"[NUMBER,22],
"W"."KSLWTEVT"[NUMBER,22]
4 - "ADDR"[RAW,4], "INST_ID"[NUMBER,22], "KSSPAFLG"[NUMBER,22],
"KSUPRPID"[VARCHAR2,24]
5 - (#keys=0) "W"."KSLWTSID"[NUMBER,22], "W"."KSLWTEVT"[NUMBER,22]
6 - "W"."KSLWTSID"[NUMBER,22], "W"."KSLWTEVT"[NUMBER,22]
7 - "S"."INDX"[NUMBER,22], "S"."INST_ID"[NUMBER,22],
"S"."KSSPAFLG"[NUMBER,22], "S"."KSSPATYP"[NUMBER,22],
"S"."KSUUDLNA"[VARCHAR2,30], "S"."KSUSEPRO"[RAW,4],
"S"."KSUSESER"[NUMBER,22], "S"."KSUSEFLG"[NUMBER,22],
"S"."KSUSEPID"[VARCHAR2,24], "S"."KSUSEPNM"[VARCHAR2,48],
"S"."KSUSESQI"[VARCHAR2,13], "S"."KSUSEACT"[VARCHAR2,64]
8 - "E"."INDX"[NUMBER,22]
sga和pga信息
[oracle@xifenfei ~]$ ./ora pga NAME PID USED(KB) ALLOC(KB) MAX_ALLOC(KB) MAP_SIZE(KB) ------------------------------------------------------------------------------------- Total 102M 119M 26173 0M [oracle@xifenfei ~]$ ./ora sga POOL NAME SIZE_KB ------------ -------------------------- ---------- large pool PX msg pool 480 large pool free memory 3616 java pool free memory 4096 large pool (total) 4096 java pool (total) 4096 shared pool free memory 14149 shared pool (total) 14149 Total 22341 8 rows selected. [oracle@xifenfei ~]$ ./ora sga_stats SGA DYNAMIC COMPNENTS COMPONENT CURRENT_SIZE_MB MIN_SIZE_MB MAX_SIZE_MB LAST_OPER_TYP LAST_OPER -------------------------------------------------- --------------- ----------- ----------- ------------- --------- shared pool 84 84 84 STATIC large pool 4 4 4 STATIC java pool 4 4 4 STATIC streams pool 0 0 0 STATIC DEFAULT buffer cache 72 72 72 INITIALIZING KEEP buffer cache 0 0 0 STATIC RECYCLE buffer cache 0 0 0 STATIC DEFAULT 2K buffer cache 0 0 0 STATIC DEFAULT 4K buffer cache 0 0 0 STATIC DEFAULT 8K buffer cache 0 0 0 STATIC DEFAULT 16K buffer cache 0 0 0 STATIC DEFAULT 32K buffer cache 0 0 0 STATIC Shared IO Pool 0 0 0 STATIC ASM Buffer Cache 0 0 0 STATIC
ora的功能非常强大,其实本质都是通过sql语句查询实现,可以通过数据库做10046来捕获相关sql.学习oracle,工具只是提高大家工作的效率,而不是刻意去追求工具本身
bbed_wrap脚本获取数据块内容
bbed的功能很强大,可以通过bbed_wrap来获得数据块记录,相当用途:抢救坏块中的数据
环境准备
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 12 18:29:50 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, OLAP, Data Mining and Real Application Testing options
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei
2 as
3 select object_id,object_name from dba_objects where rownum<20;
Table created.
SQL> select file_id,block_id,block_id+blocks-1
2 from dba_extents
3 where segment_name ='T_XIFENFEI' AND owner='CHF';
FILE_ID BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
4 680 687
SQL> alter system checkpoint;
System altered.
--查询记录
SQL> col object_name for a20
SQL> select object_id,object_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;
OBJECT_ID OBJECT_NAME REL_FNO BLOCKNO ROWNO
---------- -------------------- ---------- ---------- ----------
20 ICOL$ 4 683 0
46 I_USER1 4 683 1
28 CON$ 4 683 2
15 UNDO$ 4 683 3
29 C_COBJ# 4 683 4
3 I_OBJ# 4 683 5
25 PROXY_ROLE_DATA$ 4 683 6
41 I_IND1 4 683 7
54 I_CDEF2 4 683 8
40 I_OBJ5 4 683 9
26 I_PROXY_ROLE_DATA$_1 4 683 10
17 FILE$ 4 683 11
13 UET$ 4 683 12
9 I_FILE#_BLOCK# 4 683 13
43 I_FILE1 4 683 14
51 I_CON1 4 683 15
38 I_OBJ3 4 683 16
7 I_TS# 4 683 17
56 I_CDEF4 4 683 18
19 rows selected.
bbed参数配置
[oracle@xifenfei ~]$ more bbed_file
1 /u01/oracle/oradata/ora11g/system01.dbf
2 /u01/oracle/oradata/ora11g/sysaux01.dbf
3 /u01/oracle/oradata/ora11g/undotbs01.dbf
4 /u01/oracle/oradata/ora11g/users01.dbf
5 /u01/oracle/oradata/ora11g/dbfs01.dbf
[oracle@xifenfei ~]$ more bbed.par
blocksize=8192
listfile=/home/oracle/bbed_file
mode=browse
SILENT=yes
PASSWORD=blockedit
bbed_wrap脚本执行
[oracle@xifenfei ~]$ ./bbed_wrap.sh 4 683 "/rn2cntn" There are 19 rows in block 683 on file 4 " 20 "," ICOL$" " 46 "," I_USER1" " 28 "," CON$" " 15 "," UNDO$" " 29 "," C_COBJ#" " 3 "," I_OBJ#" " 25 "," PROXY_ROLE_DATA$" " 41 "," I_IND1" " 54 "," I_CDEF2" " 40 "," I_OBJ5" " 26 "," I_PROXY_ROLE_DATA$_1" " 17 "," FILE$" " 13 "," UET$" " 9 "," I_FILE#_BLOCK#" " 43 "," I_FILE1" " 51 "," I_CON1" " 38 "," I_OBJ3" " 7 "," I_TS#" " 56 "," I_CDEF4" --和我们查询的结果完全一致
创建DBFS
DBFS(Oracle Database File System)就是Oracle数据库11gR2中提供的能够在Linux和Solaris操作系统中将Oracle数据库当成文件系统来使用的功能.在DBFS内部,文件是以SecureFiles LOBs（对比与以前的BasicFiles LOBs）的形式存储在数据表中.这个功能第一个是存储图片或者文档,第二个功能就是在RAC或者XD中部署OGG是一个不错的选择.
安装fuse相关包
[root@xifenfei ~]# mount /dev/cdrom /media mount: block device /dev/cdrom is write-protected, mounting read-only [root@xifenfei ~]# cd /media/Server [root@xifenfei Server]# ls fuse* fuse-2.7.4-8.0.1.el5.x86_64.rpm fuse-devel-2.7.4-8.0.1.el5.x86_64.rpm fuse-libs-2.7.4-8.0.1.el5.x86_64.rpm fuse-devel-2.7.4-8.0.1.el5.i386.rpm fuse-libs-2.7.4-8.0.1.el5.i386.rpm [root@xifenfei Server]# rpm -ivh fuse-libs-2.7.4-8.0.1.el5.x86_64.rpm warning: fuse-libs-2.7.4-8.0.1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159 Preparing... ########################################### [100%] 1:fuse-libs ########################################### [100%] [root@xifenfei Server]# rpm -ivh fuse-devel-2.7.4-8.0.1.el5.x86_64.rpm warning: fuse-devel-2.7.4-8.0.1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159 Preparing... ########################################### [100%] 1:fuse-devel ########################################### [100%] [root@xifenfei Server]# rpm -ivh fuse-2.7.4-8.0.1.el5.x86_64.rpm warning: fuse-devel-2.7.4-8.0.1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159 Preparing... ########################################### [100%] 1:fuse ########################################### [100%]
系统配置
[root@xifenfei Server]# cd / [root@xifenfei /]# mkdir dbfs [root@xifenfei /]# chown ora11g:oinstall dbfs [root@xifenfei /]# ls -l|grep dbfs drwxr-xr-x 2 ora11g oinstall 4096 Sep 1 16:41 dbfs [root@xifenfei /]# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf [root@xifenfei /]# export ORACLE_HOME=/u01/oracle11 [root@xifenfei /]# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 /usr/local/lib/libclntsh.so.11.1 [root@xifenfei /]# ln -s $ORACLE_HOME/lib/libnnz11.so /usr/local/lib/libnnz11.so [root@xifenfei /]# ln -s /lib64/libfuse.so.2 /usr/local/lib/libfuse.so.2 [root@xifenfei /]# cd /usr/local/lib [root@xifenfei lib]# ls -l total 0 lrwxrwxrwx 1 root root 35 Sep 1 16:45 libclntsh.so.11.1 -> /u01/oracle11/lib/libclntsh.so.11.1 lrwxrwxrwx 1 root root 19 Sep 1 16:46 libfuse.so.2 -> /lib64/libfuse.so.2 lrwxrwxrwx 1 root root 29 Sep 1 16:46 libnnz11.so -> /u01/oracle11/lib/libnnz11.so [root@xifenfei lib]# ldconfig [root@xifenfei lib]# chmod +x /usr/bin/fusermount [root@xifenfei lib]# ls -l /usr/bin/fusermount lrwxrwxrwx 1 root root 15 Sep 1 16:37 /usr/bin/fusermount -> /bin/fusermount [root@xifenfei lib]# ls -l /bin/fusermount -rwsr-x--x 1 root fuse 23544 Oct 18 2011 /bin/fusermount
相关表空间/用户配置
SQL> create tablespace dbfs_ts 2 datafile '/u01/oradata/ora11g/xifenfei01.dbf' 3 size 20m autoextend on next 10m maxsize 30g; Tablespace created. SQL> create user dbfs identified by dbfs 2 default tablespace dbfs_ts 3 quota unlimited on dbfs_ts; User created. SQL> grant create session, resource, create view, dbfs_role to dbfs ; Grant succeeded.
创建filesystem
[ora11g@xifenfei admin]$ cd $ORACLE_HOME/rdbms/admin
[ora11g@xifenfei admin]$ sqlplus dbfs/dbfs@ora11g
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 1 16:43:04 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbfs_create_filesystem.sql dbfs_ts my_dbfs
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_MY_DBFS', tbl_name =>
'T_MY_DBFS', tbl_tbs => 'dbfs_ts', lob_tbs => 'dbfs_ts', do_partition => false,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_MY_DBFS', provider_name
=> 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_MY_DBFS',
store_mount=>'my_dbfs'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/my_dbfs', 16895); end;
No errors.
挂载dbfs
[ora11g@xifenfei ~]$ more /home/ora11g/xifenfei_pwd
dbfs
[ora11g@xifenfei ~]$ nohup dbfs_client dbfs@ora11g /dbfs <xifenfei_pwd &
[1] 3694
[ora11g@xifenfei ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.2G 462M 88% /
/dev/sda1 99M 24M 71M 25% /boot
tmpfs 1002M 184M 818M 19% /dev/shm
/dev/sdb1 20G 8.9G 9.9G 48% /u01
df: `/dbfs': Resource temporarily unavailable
查询mos发现 OS 2.6.32-100.26.2.el5 to: 2.6.32-300.10.1.el5uek (Linux UEK Kernel)会出现该问题,解决方法是升级Kernel或者不使用UEK
[ora11g@xifenfei ~]$ uname -a Linux xifenfei 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:37:40 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
不幸刚好中招,现在升级是不太可能的事情,只能使用其他kernel来启动系统(下图选择第二个)
重新挂载dbfs并且测试
[ora11g@xifenfei ~]$ uname -a
Linux xifenfei 2.6.18-308.el5 #1 SMP Sat Feb 25 12:40:07 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
[ora11g@xifenfei ~]$ nohup dbfs_client dbfs@ora11g /dbfs <xifenfei_pwd &
[1] 3694
[ora11g@xifenfei ~]$ nohup: appending output to `nohup.out'
[ora11g@xifenfei ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.2G 462M 88% /
/dev/sda1 99M 24M 71M 25% /boot
tmpfs 1006M 184M 822M 19% /dev/shm
/dev/sdb1 20G 8.9G 9.9G 48% /u01
dbfs-dbfs@ora11g:/ 19M 120K 19M 1% /dbfs
[ora11g@xifenfei ~]$ cd /dbfs
[ora11g@xifenfei dbfs]$ ls
my_dbfs
[ora11g@xifenfei dbfs]$ cd my_dbfs/
[ora11g@xifenfei my_dbfs]$ ls
[ora11g@xifenfei my_dbfs]$ cat /etc/passwd>xifenfei.chf
[ora11g@xifenfei my_dbfs]$ ll
total 2
-rw-r--r-- 1 ora11g oinstall 1736 Sep 1 21:05 xifenfei.chf
卸载dbfs
[ora11g@xifenfei ~]$ fusermount -u /dbfs
[ora11g@xifenfei ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.2G 462M 88% /
/dev/sda1 99M 24M 71M 25% /boot
tmpfs 1006M 184M 822M 19% /dev/shm
/dev/sdb1 20G 8.9G 9.9G 48% /u01
删除filesystem
cd $ORACLE_HOME/rdbms/admin sqlplus dbfs_user/dbfs_user SQL> @dbfs_drop_filesystem.sql my_dbfs