模拟普通ORA-08103并解决
在上一篇中说到:模拟极端ORA-08103并解决,不能通过修改成坏块来解决,这里演示了是一个普通的数据块出现异常,然后通过bbed修改为坏块通过dbms_repair来解决该故障,补充说明:在11.2.0.3.3的库中,使用该方法不能重现该错误,而是直接提示ORA-01578,证明ORACLE的新版本在这一方面进行了改进
创建测试表
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t_xifenfei as
2 select * from dba_objects where rownum<3000;
Table created.
SQL> SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
2 FROM dba_extents
3 WHERE segment_name='T_XIFENFEI' AND owner='CHF';
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ --------------- ---------- ---------- ---------- ----------
CHF T_XIFENFEI 0 4 57 8
CHF T_XIFENFEI 1 4 65 8
CHF T_XIFENFEI 2 4 73 8
CHF T_XIFENFEI 3 4 81 8
CHF T_XIFENFEI 4 4 89 8
CHF T_XIFENFEI 5 4 97 8
6 rows selected.
SQL> SELECT DISTINCT dbms_rowid.rowid_block_number(rowid) blk#,
2 dbms_rowid.rowid_relative_fno(rowid) file#
3 FROM t_xifenfei
4 ORDER BY 2,1;
BLK# FILE#
---------- ----------
60 4
61 4
62 4
63 4
64 4
65 4
66 4
67 4
68 4
69 4
70 4
BLK# FILE#
---------- ----------
71 4
72 4
74 4
75 4
76 4
77 4
78 4
79 4
80 4
81 4
82 4
BLK# FILE#
---------- ----------
83 4
84 4
85 4
86 4
87 4
88 4
90 4
91 4
92 4
93 4
94 4
BLK# FILE#
---------- ----------
95 4
96 4
97 4
98 4
37 rows selected.
模拟ORA-08103
SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据快(其实就是清空一个数据块block 95,注意dd和实际数据的block对应关系相差1)
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users01.dbf bs=8192 seek=95 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000187113 seconds, 43.8 MB/s
SQL> STARTUP
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 104860124 bytes
Database Buffers 205520896 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI;
SELECT COUNT(*) FROM CHF.T_XIFENFEI
*
ERROR at line 1:
ORA-08103: object no longer exists
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei file=/tmp/t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Fri Jan 13 22:09:43 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_XIFENFEI
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.
[oracle@xifenfei ~]$ expdp chf/xifenfei tables=t_xifenfei dumpfile=t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Friday, 13 January, 2012 22:10:26
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=t_xifenfei dumpfile=t_xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CHF"."T_XIFENFEI" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
/u01/oracle/oracle/product/10.2.0/db_1/rdbms/log/t_xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:10:50
测试证明当出现ORA-08103的时候,全表扫描,exp,expdp均不能正常工作
找到出现ORA-08103数据块
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
……………………
ERROR:
ORA-08103: object no longer exists
2700 rows selected.
--在trace文件结尾发现如下记录,表示读到这个数据块时发生错误
KTRVAC: path typ=0, rdba=100005f
SQL> select to_number('100005f','xxxxxxxxxxxxx') from dual;
TO_NUMBER('100005F','XXXXXXXXXXXXX')
------------------------------------
16777311
SQL> select
2 dbms_utility.data_block_address_file(16777311) FILE_NO,
3 dbms_utility.data_block_address_block(16777311) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 95
bbed继续破坏异常块
BBED> set filename '/u01/oracle/oradata/XFF/users01.dbf'
FILENAME /u01/oracle/oradata/XFF/users01.dbf
BBED> set block 95
BLOCK# 95
BBED> map
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> set count 32
COUNT 32
BBED> d
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Offsets: 0 to 31 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
--这个就是和11gr2的区别,在11g中使用该方法来模拟ORA-08103,直接提示坏块,从而不会出现ORA-08103
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/users01.dbf
BLOCK = 95
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 1
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED> d offset 8180
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Offsets: 8180 to 8191 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 01010101 offset 8188
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
--修改sumcheck
BBED> m /x 01010101 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 95 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01010101
<32 bytes per line>
BBED> sum
Check value for File 0, Block 95:
current = 0x0000, required = 0x0000
测试修改为坏块效果
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 95)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
2918
通过让ORA-08103对应的块变为真正的坏块,然后使用dbms_repair或者event来跳过坏块,达到拯救数据的目的
模拟极端ORA-08103并解决
ORA-08103错误在数据库日程运维和异常恢复中都可能遇到,出现该错误的原因很多,有很多情况下(模拟普通ORA-08103并解决),直接通过修改块使其在数据库查询的时候表标志为坏块,然后使用event或者dbms_repair包来标志该块,然后跳过就可以解决该问题,但是有些时候,遇到极端情况,该方法会失效,需要借助极端工具来处理该极端问题.
分析表相关EXTENT
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> create table t_xifenfei as
2 select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
74504
SQL> select data_object_id,object_id from dba_objects where object_name='T_XIFENFEI';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
75592 75592
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
4 688 695
4 696 703
4 704 711 <---注意
4 712 719
4 720 727
4 728 735
…………
24 rows selected.
SQL> Select segment_name,header_file,header_blocK
2 from dba_segments where
3 segment_name in ('T_XIFENFEI') and owner='CHF'
4 ;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
T_XIFENFEI 4 682
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 682;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28933.trc
--trace文件
Dump of memory from 0xB6CFD600 to 0xB6CFF600
B6CFD600 0000A223 010002AA 000DB4EA 04010000 [#...............]
B6CFD610 00008ECF 00000000 00000000 00000000 [................]
B6CFD620 00000000 00000018 00000480 00000A9C [................]
B6CFD630 00000017 0000003F 00000080 010007BF [....?...........]
B6CFD640 00000000 00000017 00000000 0000043F [............?...]
B6CFD650 00000000 00000000 00000000 00000017 [................]
B6CFD660 0000003F 00000080 010007BF 00000000 [?...............]
B6CFD670 00000017 00000000 0000043F 01000780 [........?.......]
B6CFD680 01000780 00000000 00000000 00000000 [................]
B6CFD690 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
B6CFD6D0 00000001 00002000 00000000 00001434 [..... ......4...]
B6CFD6E0 00000000 010002A9 00000001 01000781 [................]
B6CFD6F0 010002A9 00000000 00000000 00000000 [................]
B6CFD700 00000000 00000000 00000018 00000000 [................]
B6CFD710 00012748 10000000 010002A8 00000008 [H'..............]
B6CFD720 010002B0 00000008 010002B8 00000008 [................]
B6CFD730 010002C0 00000008 010002C8 00000008 [................] <----dump中找到下面值
……
Extent Map
-----------------------------------------------------------------
0x010002a8 length: 8
0x010002b0 length: 8
0x010002b8 length: 8
0x010002c0 length: 8 <-----选择第四个exent
0x010002c8 length: 8
…………
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x010002a8 Data dba: 0x010002ab
Extent 1 : L1 dba: 0x010002a8 Data dba: 0x010002b0
Extent 2 : L1 dba: 0x010002b8 Data dba: 0x010002b9
Extent 3 : L1 dba: 0x010002b8 Data dba: 0x010002c0 <---同上
Extent 4 : L1 dba: 0x010002c8 Data dba: 0x010002c9
Extent 5 : L1 dba: 0x010002c8 Data dba: 0x010002d0
…………
--------------------------------------------------------
--确定Extent 3的记录在file 4 block 682 的偏移量为304
------省略了相同部分B6CFD
SQL> SELECT TO_NUMBER('730','XXXXX') FROM DUAL;
TO_NUMBER('730','XXXXX')
------------------------
1840
SQL> SELECT TO_NUMBER('600','XXX') FROM DUAL;
TO_NUMBER('600','XXX')
----------------------
1536
SQL> SELECT 1840-1536 FROM DUAL;
1840-1536
----------
304
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
这里我们假设修改SEGMENT HEADER中关于EXTENT 3(从0开始计数)的映射地址,从而来使得该表在查询的时候出现ORA-08103错误
bbed修改相关值
[oracle@xifenfei ~]$ bbed parfile=bbed.par
BBED> SET MODE EDIT
MODE Edit
BBED> INFO
File# Name Size(blks)
----- ---- ----------
4 /u01/oracle/oradata/ora11g/users01.dbf 0
BBED> SET FILE 4 BLOCK 682
FILE# 4
BLOCK# 682
BBED> D
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
23a20000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> m /x 1100
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
11000000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 682 Offsets: 0 to 511 Dba:0x010002aa
------------------------------------------------------------------------
11000000 aa020001 eab40d00 00000104 cf8e0000 …………
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 682:
current = 0x2cfd, required = 0x2cfd
重现ORA-08103
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 239078260 bytes
Database Buffers 67108864 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
定位坏块位置
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
*
ERROR at line 1:
ORA-08103: object no longer exists
--trace文件关键内容
block_row_dump:
tab 0, row 0, @0x1f70
tl: 48 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 9] 44 49 43 54 2e 42 41 53 45
col 1: [ 1] 32
col 2: [32]
…………
end_of_block_dump
The buffer with tsn: 0 rdba: 0x00400321 has already been dumped
The buffer with tsn: 4 rdba: 0x010002aa was pinned,
but could not be dumped
SQL> Select to_number('010002aa','xxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('010002AA','XXXXXXXXXXXXXXXXXX')
------------------------------------------
16777898
SQL> select
2 dbms_utility.data_block_address_file(16777898) FILE_NO,
3 dbms_utility.data_block_address_block(16777898) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 682
检查坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 13 18:03:13 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Block Checking: DBA = 16777898, Block Type = Unlimited data segment header with flg blks
Incorrect total map count: 24
Page 682 failed with check code 17006
DBVERIFY - Verification complete
Total Pages Examined : 2240
Total Pages Processed (Data) : 1421
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 199
Total Pages Failing (Index): 0
Total Pages Processed (Other): 229
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 391
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 898278 (0.898278)
--对应块
SQL> select
2 dbms_utility.data_block_address_file(16777898) FILE_NO,
3 dbms_utility.data_block_address_block(16777898) BLOCK_NO
4 from dual;
FILE_NO BLOCK_NO
---------- ----------
4 682
通过这里dbv检查发现,该数据库已经是坏块了,也就是说,网上流传的人工标志为坏块的方法在此处行不通,这里我们考虑使用dul等类此工具挖取数据,来拯救数据
dul和odu常规操作
DUL> unload table chf.t_xifenfei; . unloading table T_XIFENFEI DUL: Warning: Using data objno from segment header( 16779199) instead of expected id (75592) from dictionary DUL: Error: No entry in control file for block: ts# = 4 rfile# = 0 block# = 1087 DUL: Error: While processing unknown file block# 1087 DUL: Error: Could not read/parse data block ODU> unload table chf.t_xifenfei Unloading table: T_XIFENFEI,object ID: 75592 Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0) corrupted block 0 rows unloaded
通过上面的测试证明,在该中情况下(SEGMENT HEADER)出现异常时,dul/odu均不能使用最常规的方法挖的数据
扫描数据文件方式挖
因为odu在这个方面的操作人性化于dul,所以只用odu进行相关测试,dul肯定能过实现相同功能
ODU> SCAN EXTENT TABLESPACE 4 PARALLEL 2 scan extent start: 2012-9-23 2:47:51 scanning extent... scanning extent finished. scan extent completed: 2012-9-23 2:47:51 ODU> unload table chf.t_xifenfei object 75592 Unloading table: T_XIFENFEI,object ID: 75592 Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0) 74504 rows unloaded
试验证明通过odu扫描表空间/数据文件找回来所有的数据,然后truncate掉问题表,导入该数据库,问题可以得意顺利解决
EXADATA EHCC初试
今天有幸见识了下EXADATA的强大功能之一EHCC(Exadata Hybrid Columnar Compression),发现压缩效果确实很让人心动,压缩效率大大超过我的预计,压缩97%左右(1-628.1875/20573)
创建模拟表T_FF_SOURCE
14:32:52 SQL> create table t_FF_source as select * from dba_objects; Table created. Elapsed: 00:00:00.16 14:35:54 SQL> begin 14:37:05 2 for i in 1..100000 loop 14:37:05 3 insert into t_FF_source select * from dba_jects; 14:37:05 4 commit; 14:37:05 5 end loop; 14:37:05 6 end; 14:37:05 7 / Elapsed: 00:13:07.76 14:51:05 SQL> select count(*) from t_FF_source; COUNT(*) ---------- 197015655 Elapsed: 00:00:33.18 14:51:56 SQL> col segment_name format a45 heading "Segment Name" 14:52:55 SQL> select segment_name Segment_Name 14:52:55 2 , segment_type "Segment Type" 14:52:55 3 , round(sum(bytes)/1024/1024/1024,2) "Size In GB" 14:52:55 4 from dba_segments 14:52:55 5 where 14:52:56 6 segment_name ='T_FF_SOURCE' 14:52:56 7 group by segment_name,segment_type 14:52:56 8 order by 1; Segment Name Segment Type Size In GB --------------------------------------------- ------------------ ---------- T_FF_SOURCE TABLE 20.09
创建各种情况下压缩表
--BASE create table t_FF_c compress NOLOGGING as select /*+ PARALLE 24*/ * from t_FF_source; --OLTP create table t_FF_c_o compress for oltp NOLOGGING as select /*+ PARALLE 24 */ * from t_FF_source; --QUERY LOW create table t_FF_q_l compress for query low NOLOGGING as select /*+ PARALLE 24 */ * from t_FF_source; --QUERY HIGH create table t_FF_q_h compress for query high parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source; --ARCHIVE LOW create table t_FF_a_l compress for archive low parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source; --ARCHIVE HIGH create table t_FF_a_h compress for archive high parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source;
其实BASE和OLTP是数据库基本的压缩功能,该功能不仅限于EXADATA,但是后面的四种压缩就是我们所说的EHCC,也只有EXADATA用户才能够体验到.
数据压缩结果
16:19:13 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 16:19:20 2 from dba_segments s,dba_tables t 16:19:20 3 where s.owner=t.owner and t.table_name=s.segment_name 16:19:20 4 and s.owner='FF' and t.table_name like 'T_FF%'; OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ----------------------------------- ---------- ------------ FF T_FF_A_L 1244.625 ARCHIVE LOW FF T_FF_SOURCE 20573 FF T_FF_Q_H 1244.875 QUERY HIGH FF T_FF_A_H 628.1875 ARCHIVE HIGH FF T_FF_C 6961.625 BASIC FF T_FF_Q_L 2799.875 QUERY LOW FF T_FF_C_O 7759.1875 OLTP
试验结果证明
1.BASE也OLTP的压缩效率差不多(可能是因为BASIC的PCTFREE为0,OLTP的PCTFREE为10)
2.在EHCC的四种压缩中:QUERY LOW相对压缩率不高,采用LZO压缩算法,但是也比ORACLE自带的压缩效果高很多
3.QUERY HIGH和ARCHIVE LOW压缩率差不多,都是使用ZLIB压缩算法
4.ARCHIVE HIGH是压缩率极高,采用Bzip2压缩算法实现.
EXADATA EM性能监控
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 : 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
解决因/etc/fstab错误导致系统不能启动故障
发现如果人品不好做试验都是问题很多,晚上又把fstab给写错了,导致系统不能起来,因为当时处理该故障未截图,后续在网上找了几张图片,大体说明处理思路
系统启动报 filesystems 失败,输入root密码进入repair filesystem模式
尝试修改 /etc/fstab 发现系统是read-only模式
重新mount -n -o remount,rw /重新mount文件系统
重新修改/etc/fstab,除掉错误记录,然后使用init 6/reboot命令重启系统
安装ORACLE db /tmp空间不足解决办法
因测试需要装一个ORACLE 11G,在安装检测阶段报下图错误
详细信息
Free Space: xifenfei:/tmp - This is a prerequisite condition to test whether sufficient free space is available in the file system. Error: - PRVF-7501 : Sufficient space is not available at location "/tmp" on node "xifenfei" [Required space = 1GB ] - Cause: Not enough free space at location specified. - Action: Free up additional space or select another location. Expected Value : 1GB Actual Value : 238MB
错误提示很明显ORACLE安装过程需要1G的临时空间，但是现在/tmp只有238M,空间明显不足,是的oracle检测失败,为了安装过程不出意外,决定分析并解决该问题
磁盘空间使用情况
[ora11g@xifenfei ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
3.9G 3.3G 238M 93% /
/dev/sda1 99M 24M 71M 25% /boot
tmpfs 1002M 0 1002M 0% /dev/shm
/dev/sdb1 20G 7.8G 11G 42% /u01
这里可以看出来/tmp没有另外的分配分区,而是挂载在/下面,也就是说,/tmp最多使用的空间就是/dev/mapper/VolGroup00-LogVol00分区能够使用的最大空间,也就是238M,证明ORACLE的检查程序说的是事实。
解决该问题
1.建立新tmp目录
[root@xifenfei ora11g]# mkdir /u01/tmp [root@xifenfei ora11g]# chown root:root /u01/tmp [root@xifenfei ora11g]# chmod 1777 /u01/tmp
2.设置数据库用户变量
vi db_home/.bash_profile export TEMP=/u01/tmp export TMPDIR=/u01/tmp [ora11g@xifenfei ~]$ env|grep TMP TMPDIR=/u01/tmp [ora11g@xifenfei ~]$ env|grep TEMP TEMP=/u01/tmp
3.重新运行runInstaller
4.安装完成清理相关/u01/tmp 和相关环境变了,让数据库使用系统默认(根据实际情况处理)
ORA-600和ORA-7445错误文章汇总
ORA-600和ORA-7445错误都是很多dba忌讳的错误,这里对本blog截止于:2012年09月15日的相关类此错误进行一个简单汇总,方便大家查阅.同时感谢“广州-紫恒”朋友在百忙中帮我整理出来.
ORA-600错误集合
ORA-600 [12235]
ORA-00600 [2662]
ORA-00600[4454]
ORA-00600[KSSADP1]
重现ORA-600[4000]异常
ORA-00600[4194]故障解决
ORA-00600[ktspNextL1:4]
ORA-600[4194]/[4193]解决
TOAD导致ORA-00600[17281]
ORA-00600[729]分析和处理方法
使用bbed解决ORA-00600[2662]
记录一次ORA-600[13013]处理过程
因为高版本引起ORA-00600[17059]
记录另一起ORA-00600[13013]处理
异常断电导致current redo损坏处理
记录一次ORA-00600[kdsgrp1]分析
异常断电导致current redo损坏处理
记录一次ORA-00600[2252]故障解决
因使用OEM引起ORA-00600[12761]
10.2.0.5出现ORA-00600[kcblasm_1]
ORA-00600[kgscLogOff-notempty]
通过bbed解决ORA-00600[4000]案例
ORA-00600 [ktbdchk1: bad dscn] 解决
11G RAC库 ORA-00600[ktubko_1]错误
收集统计信息出现ORA-00600[ksxprqfre3]
ORA-607/ORA-600[4194]不一定是重大灾难
创建控制文件遭遇ORA-00600[3753]故障解决
控制文件异常导致ORA-00600[kccsbck_first]
数据库报ORA-00607/ORA-00600[4194]错误
ORA-00600[kcratr_nab_less_than_odr]故障解决
PL/SQL Developer编译过程引起ora-600[15419]
ORA-07445[kslgetl()+120]/ORA-00108错误解决
双机mount数据库出现ORA-00600[kccsbck_first]
使用bbed解决ORA-00607/ORA-00600[4194]故障
通过bbed模拟ORA-00607/ORA-00600[4194]故障
truncate table强制终止导致ORA-00600[ktspfundo-2]
客户端版本导致ORA-00600[kssadd_stage: null parent]
动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]
ORA-00600[kcratr1_lostwrt]/ORA-00600[3020]错误恢复
ORA-600 [LibraryCacheNotEmptyOnClose] on shutdown
ORA-600[6749] 发生在 SYSMAN.MGMT_METRICS_RAW表
老版本PL/SQL Developer操作数据库导致ORA-00600[17113]
ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]
表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程
spfile被覆盖导致ORA-600[kmgs_parameter_update_timeout_1]
ORA-7445错误集合
ORA-7445[__milli_memcpy]分析
8i升级到9i出现ORA-07445[pevm_MOVC_i()+18]
ORA-07445[kslgetl()+120]/ORA-00108错误解决
遭遇ORA-07445[kkdliac()+346]使用odu抢救数据
ORA-07445[dbgrmqmqpk_query_pick_key()+0f88]
ORA-07445 [ACCESS_VIOLATION] [UNABLE_TO_READ] []