method_opt
Accepts:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]…]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
– integer : Number of histogram buckets. Must be in the range [1,254].
– REPEAT : Collects histograms only on the columns that already have histograms.
– AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
– SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
举例说明：
method_opt => ‘FOR COLUMNS size 254 object_id’ 收集objct_id列直方图
method_opt => ‘FOR COLUMNS size 1 object_id’ 删除object_id列直方图
method_opt => ‘for all columns size repeat’ 重新分析现有直方图
method_opt => ‘for all columns size auto’ oracle决定收集哪些列的直方图（需要设置table monitoring）
method_opt => ‘for all columns size skewonly’ oracle分析所有列的分布情况，生成直方图
method_opt => ‘FOR COLUMNS object_id size SKEWONLY’ 收集object_id列分布情况，生成直方图
method_opt => ‘FOR all INDEXED COLUMNS size SKEWONLY’ 收集index列分布情况，并生成直方图
granularity
Granularity of statistics to collect (only pertinent if the table is partitioned).
‘ALL’ – gathers all (subpartition, partition, and global) statistics
‘AUTO’- determines the granularity based on the partitioning type. This is the default value.
‘DEFAULT’ – gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the ‘GLOBAL AND PARTITION’ for this functionality. Note that the default value is now ‘AUTO’.
‘GLOBAL’ – gathers global statistics
‘GLOBAL AND PARTITION’ – gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
‘PARTITION ‘- gathers partition-level statistics
‘SUBPARTITION’ – gathers subpartition-level statistics.
options
Further specification of which objects to gather statistics for:
GATHER: Gathers statistics on all objects in the schema.
GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.
GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.
LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.
LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views.
LIST EMPTY: Returns list of objects which currently have no statistics.
BITMAP CONVERSION FROM ROWIDS
在有些执行计划中，可以会看到 BITMAP CONVERSION FROM ROWIDS这样的东东，也许你会感觉奇怪，我没有使用位图索引怎么出现了bitmap。
我通过一个sql和大家分析下原因：
sql语句为：
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM ( SELECT module_no, MAX (id) AS id
FROM vasoa.tab_moa_historypage t
WHERE t.company_id = :1 AND t.user_id = :2 AND t.TYPE = :3
GROUP BY module_no
ORDER BY id DESC) a
WHERE ROWNUM <= :4) b
WHERE b.rn >= :5
执行计划为：
表结构为：
目前索引情况：
通过观察执行计划的图片可以发现，使用了 BITMAP CONVERSION FROM ROWIDS，其目的是：.oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据。这种想象出现的原因为：oracle的cbo是根据cost来决定大小来选择合适的执行计划，当它计算获得通过bitmap的方式执行的时候cost会更小，它就会选择使用这样的执行计划。一般出现这样的情况，都是因为对表建立的不适当的index导致，特别是对表中的唯一度不高的列建立了index，然后oracle就有可能选择两个这样的列转为为bitmap来执行。根据oracle的执行计划，肯定是cost最小的，但是它很多时候忽略了一致性读等其他条件，导致这个执行计划并非像oracle想象的那样最优，因为把btree index转为为bitmap index执行，需要消耗更多的cpu，特别是在cpu比较紧张的系统中，所以这样的情况如果发生在oltp系统中，一般都需要解决。
本例的解决办法是删除唯一度低的index，建立组合index。分别删除COMPANY_ID与USER_ID上的index，建立COMPANY_ID和USER_ID的组合index，执行计划如下：
bbed 修改datafile header
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
提示数据文件2需要恢复，首选是recover datafile 2;，如果失败，可以考虑bbed修改scn的办法
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 107374278108
2 107374278108
3 107374278108
4 107374278108
5 107374278108
6 107374278108
7 107374278108
8 107374278108
9 107374278108
10 107374278108
11 107374278108
11 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
2 ONLINE 107374277136
[oracle@localhost tmp]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 22 06:05:34 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /opt/oracle/oradata/xifenfei/system01.dbf 1280
2 /opt/oracle/oradata/xifenfei/xff01.dbf 1280
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
* ub4 kscnbas @140 0x000175dc
ub2 kscnwrp @144 0x0019
* ub4 kcvcptim @148 0x2d49fbbb
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x00000005
ub4 kcrbabno @160 0x00008e05
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x00000005
ub2 kscnwrp @160 0x8e05
ub4 kcrtrtim @164 0x09110010
ub1 kcvcpetb[0] @168 0x02
ub1 kcvcpetb[1] @169 0x00
ub1 kcvcpetb[2] @170 0x00
ub1 kcvcpetb[3] @171 0x00
ub1 kcvcpetb[4] @172 0x00
ub1 kcvcpetb[5] @173 0x00
ub1 kcvcpetb[6] @174 0x00
ub1 kcvcpetb[7] @175 0x00
BBED> p kcvfhcpc
*ub4 kcvfhcpc @176 0x0000007a
BBED> p kcvfhccc
*ub4 kcvfhccc @184 0x00000079
星号表示使用bbed修改datafile header scn需要考虑的地方
SQL> select to_char(to_number('19000175dc','xxxxxxxxxxxx'),'999999999999') from dual;
TO_CHAR(TO_NU
-------------
107374278108
证实system01.dbf的scn为107374278108和v$datafile查询到的一致
BBED> set dba 2,1
DBA 0x00800001 (8388609 2,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
ub4 kscnbas @140 0x00017210
ub2 kscnwrp @144 0x0019
ub4 kcvcptim @148 0x2d49fa27
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x00000005
ub4 kcrbabno @160 0x00006f18
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x00000005
ub2 kscnwrp @160 0x6f18
ub4 kcrtrtim @164 0x09110010
ub1 kcvcpetb[0] @168 0x02
ub1 kcvcpetb[1] @169 0x00
ub1 kcvcpetb[2] @170 0x00
ub1 kcvcpetb[3] @171 0x00
ub1 kcvcpetb[4] @172 0x00
ub1 kcvcpetb[5] @173 0x00
ub1 kcvcpetb[6] @174 0x00
ub1 kcvcpetb[7] @175 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @176 0x00000034
BBED> p kcvfhccc
ub4 kcvfhccc @184 0x00000033
SQL> select to_char(to_number('1900017210','xxxxxxxxxxxx'),'999999999999') from dual;
TO_CHAR(TO_NU
-------------
107374277136
和v$recover_file视图中查询出来一致
BBED> set dba 2,1 offset 140
DBA 0x00800001 (8388609 2,1)
OFFSET 140
BBED> show
FILE# 2
BLOCK# 1
OFFSET 140
DBA 0x00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 32
LOGFILE log.bbd
SPOOL No
BBED> m /x dc750100
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 140 to 171 Dba:0x00800001
------------------------------------------------------------------------
dc750100 19000000 27fa492d 01000000 05000000 186f0000 10001109 02000000
<32 bytes per line>
BBED> m /x bbfb492d offset 158
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 158 to 189 Dba:0x00800001
------------------------------------------------------------------------
bbfb492d 00001000 11090200 00000000 00003400 00004cc0 492d3300 00000000
<32 bytes per line>
BBED> m /x 7a000000 offset 176
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 176 to 207 Dba:0x00800001
------------------------------------------------------------------------
7a000000 4cc0492d 33000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 79000000 offset 184
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 184 to 215 Dba:0x00800001
------------------------------------------------------------------------
79000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
注意：p打印出来的值和m修改的值可能不是完全一致（和cpu的计算类型有关，常用的intel cpu中是倒序存储），可以选择先dump正确的相关值，然后m修改
如：p打印出来的值为：80084d1f时，m修改时要为： 1f4d0880
BBED> sum apply dba 2,1
Check value for File 2, Block 1:
current = 0x7ece, required = 0x7ece
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile
提示控制文件太老，需要重建控制文件
SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/opt/oracle/oradata/xifenfei/redo01.log' SIZE 100M,
GROUP 2 '/opt/oracle/oradata/xifenfei/redo02.log' SIZE 100M,
GROUP 3 '/opt/oracle/oradata/xifenfei/redo03.log' SIZE 100M
DATAFILE
'/opt/oracle/oradata/xifenfei/system01.dbf',
'/opt/oracle/oradata/xifenfei/xff01.dbf',
'/opt/oracle/oradata/xifenfei/cwmlite01.dbf',
'/opt/oracle/oradata/xifenfei/drsys01.dbf',
'/opt/oracle/oradata/xifenfei/example01.dbf',
'/opt/oracle/oradata/xifenfei/indx01.dbf',
'/opt/oracle/oradata/xifenfei/odm01.dbf',
'/opt/oracle/oradata/xifenfei/tools01.dbf',
'/opt/oracle/oradata/xifenfei/users01.dbf',
'/opt/oracle/oradata/xifenfei/xdb01.dbf',
'/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Control file created.
SQL> alter database open;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/xifenfei/temp01.dbf'
2 SIZE 32505856 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
重建控制文件后，需要添加临时文件
补充说明：
1、datafile 的file header 存储在第一个block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）
（1）kscnbas (at offset 140) – SCN of last change to the datafile.
（2）kcvcptim (at offset 148) -Time of the last change to the datafile.
（3）kcvfhcpc (at offset 176) – Checkpoint count.
（4）kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误
通过bbed查看数据块结构
BBED> map /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Dba:0x00800212
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
b2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdbh, 14 bytes @92
ub1 kdbhflag @92
b1 kdbhntab @93
b2 kdbhnrow @94
sb2 kdbhfrre @96
sb2 kdbhfsbo @98
sb2 kdbhfseo @100
b2 kdbhavsp @102
b2 kdbhtosp @104
struct kdbt[1], 4 bytes @106
b2 kdbtoffs @106
b2 kdbtnrow @108
sb2 kdbr[101] @110
ub1 freespace[4270] @312
ub1 rowdata[3606] @4582
ub4 tailchk @8188
BBED>
////////////////////////////////////////////////////////////////////////////////////////
//////////// Block Header Structure, 20 bytes//////////////////////////////////////////
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 -- Block Type
-- 01 - Undo segment header
-- 02 - Undo data block
-- 03 - Save undo header
-- 04 - Save undo data block
-- 05 - Data segment header
-- 06 - Trans data, KTB managed data block(with ITL)
-- 07 - Temp table data block (no ITL)
-- 08 - Sort key
-- 09 - Sort Run
-- 10 - Segment free list block
-- 11 - Data file header
ub1 frmt_kcbh @1 -- Block Format 1=Oracle7, 2=Oracle8+
ub1 spare1_kcbh @2 -- Not used, filler field
ub1 spare2_kcbh @3 -- Not used, filler field
ub4 rdba_kcbh @4 -- RDBA (4 bytes) - Relative Data Block Address
ub4 bas_kcbh @8 -- SCN Base (4 bytes)
ub2 wrp_kcbh @12 -- SCN Wrap (2 bytes)
ub1 seq_kcbh @14 -- Sequence Number, incremented for every change made to the block at the same SCN
ub1 flg_kcbh @15 -- Flag:
-- 0x01 New Block
-- 0x02 Delayed Logging Chang advanced SCN/seq
-- 0x04 Check value saved - block XOR's to Zero
-- 0x08 Temporary block
ub2 chkval_kcbh @16 -- Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)
ub2 spare3_kcbh @18 -- Not used, filler field
/////////////////////////////////////////////////////////////////////////////////////////
/////////Transaction Fixed Header Structure, 72 Bytes////////////////////////////////////
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 -- Block type (1=DATA, 2=INDEX)
union ktbbhsid, 4 bytes @24 -- Segment/Object ID
struct ktbbhcsc, 8 bytes @28 -- SCN at last block cleanout
b2 ktbbhict @36 -- Number of ITL slots
ub1 ktbbhflg @38 -- 0=on the freelist
ub1 ktbbhfsl @39 -- ITL TX freelist slot
ub4 ktbbhfnx @40 -- DBA of next block on the freelist
struct ktbbhitl[2], 48 bytes @44 -- ITL list index, each ITL takes up 24 bytes
//////////////////////////////////////////////////////////////////////////////////////////
///////////////Data Header Structure, 14 bytes////////////////////////////////////////////
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 -- N=pctfree hit(clusters)
-- F=do not put on freelist
-- K=flushable cluster keys
b1 kdbhntab @101 -- Number of tables (>1 in clusters)
b2 kdbhnrow @102 -- Number of rows (2 bytes)
sb2 kdbhfrre @104 -- First free row entry index; -1=you have to add one
sb2 kdbhfsbo @106 -- Freespace begin offset
sb2 kdbhfseo @108 -- Freespace end offset
b2 kdbhavsp @110 -- Available space in the block
b2 kdbhtosp @112 -- Total available space when all TXs commit
////////////////////////////////////////////////////////////////////////////////////////
/////////////////////Table Directory Entry Structure, 4 bytes///////////////////////////
BBED> p kdbt
struct kdbt[1], 4 bytes @114
b2 kdbtoffs @114
b2 kdbtnrow @116
////////////////////////////////////////////////////////////////////////////////////////
////////////////// Row Directory ///////////////////////////////////////////////////////
BBED> p kdbr[100]
sb2 kdbr[100] @310
////////////////////////////////////////////////////////////////////////////////////////
///////////////// Free Space ///////////////////////////////////////////////////////////
BBED> p freespace[4269]
ub1 freespace[4269] @4581
///////////////////////////////////////////////////////////////////////////////////////
/////////////////////Row Data//////////////////////////////////////////////////////////
BBED> p rowdata[3605]
ub1 rowdata[3605] @8187 0x00
//////////////////////////////////////////////////////////////////////////////////////
/////////////////////Block Tail Check, 4 bytes////////////////////////////////////////
BBED> p tailchk
ub4 tailchk @8188 0x24500601
说明事宜：
1、tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
2、块的scn为：scn=wrp_kcbh+bas_kcbh
求scn语句：select to_char(to_number(‘scn’,’xxxxxxxxxx’),’999999999999′) from dual;
3、dba求文件号，块号为：
set serveroutput on
declare
p_dba VARCHAR2 (255) :='0x00800212';
l_str VARCHAR2 (255) DEFAULT NULL;
BEGIN
l_str :=
'datafile# is:'
|| DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
|| chr(10)||'datablock is:'
|| DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
dbms_output.put_line(l_str);
END;
bbed恢复删除数据
一、创建模拟环境
SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;
Table created.
SQL> insert into hr.xifenfei values(1,’xifenfei’);
1 row created.
SQL> insert into hr.xifenfei values(2,’xff’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
SQL> select rowid,
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 hr.xifenfei;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查询file#，block，后面恢复要用
SQL> delete from hr.xifenfei where id=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
二、bbed恢复删除数据
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0x00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set dba 2,530
DBA 0x00800212 (8389138 2,530)
BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0x00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2
<32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0x00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….
<16 bytes per line>
BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0x00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …<......xff,..
02c10208 78696665 6e666569 010680e2 l ....xifenfei....
<16 bytes per line>
BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0x00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….
<16 bytes per line>
BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0x00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .<......xff,....
02087869 66656e66 65690106 80e2 l ..xifenfei....
<16 bytes per line>
BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0x00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l <......xff,.....
08786966 656e6665 69010680 e2 l .xifenfei....
<16 bytes per line>
通过尝试，推断出来3c的offset
BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0x00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2
<32 bytes per line>
修改3c为2c
BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9
三、核对结果
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
说明：
1）如果数据未删除：row flag的值为 32+8+4=44或者0x2c
2）如果数据被删除：row flag的值为 32+16+8+4=60或者0x3c
监控Index注意事项
1、监控语句
ALTER INDEX owner.index_name MONITORING USAGE;
2、查看监控情况
select * from v$object_usage;
3、使用sys用户无法查询到监控对象问题解决
3.1）查询建立v$object_usage的语句
CREATE OR REPLACE FORCE VIEW v$object_usage (index_name,
table_name,
MONITORING,
used,
start_monitoring,
end_monitoring
)
AS
SELECT io.NAME, t.NAME, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),
DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring
FROM SYS.obj$ io, SYS.obj$ t, SYS.ind$ i, SYS.object_usage ou
WHERE io.owner# = USERENV ('SCHEMAID')
AND i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#;
3.2）从该语句中发现，只有当前用户才可以查询到index的监控情况，即使sys也不能查看到
3.3）解决sys可以查询到index监控情况可以除掉io.owner# = USERENV (‘SCHEMAID’)即可
SELECT *
FROM (SELECT USERNAME OWNER,
IO.NAME INDEX_NAME,
T.NAME TABLE_NAME,
DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,
DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
OU.START_MONITORING START_MONITORING,
OU.END_MONITORING END_MONITORING
FROM SYS.OBJ$ IO,
SYS.OBJ$ T,
SYS.IND$ I,
SYS.OBJECT_USAGE OU,
DBA_USERS U
WHERE I.OBJ# = OU.OBJ#
AND IO.OBJ# = OU.OBJ#
AND T.OBJ# = I.BO#
AND U.USER_ID = IO.OWNER#)
--USED='YES'--表示有使用
--USED='NO'--表示没使用
WHERE USED = 'YES';
如果想查询方便，可以创建sys下面的下个视图
4、停止监控
ALTER INDEX owner.index_name NOMONITORING USAGE;
bbed修改数据内容
1、创建模拟表
SQL> create table hr.b(id number,name varchar2(100)) tablespace xff;
Table created.
SQL> insert into hr.b values(1,’aaa’);
1 row created.
Commit complete.
2、查看数据位置
QL> select rowid,
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 hr.b;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHytAACAAAAIKAAA 2 522 0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
虽然bbed 可以在db open 状态来进行修改，但是建议在做任何修改操作之前先shutdown db。 这样避免checkpoint 进程重写bbed 对block 的修改。 也避免oracle 在bbed 修改完成之前读block 或者申明block 为corrupt。
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf（/tmp/parfile.cnf文件见bbed破坏数据文件）
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Sat Aug 20 17:10:24 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0x00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
3、设置dba位置
BBED> set dba 2,522
DBA 0x0080020a (8389130 2,522)
4、查询aaa位置
BBED> find /c aaa
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522 Offsets: 8185 to 8191 Dba:0x0080020a
————————————————————————
61616101 067735
<32 bytes per line>
5、查看该位置内容
BBED> dump /v offset 8185
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522 Offsets: 8185 to 8191 Dba:0x0080020a
——————————————————-
61616101 067735 l aaa..w5
<16 bytes per line>
BBED> show all
FILE# 2
BLOCK# 522
OFFSET 8185
DBA 0x0080020a (8389130 2,522)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
6、修改aaa为bbb
BBED> modify /c bbb
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522 Offsets: 8185 to 8191 Dba:0x0080020a
————————————————————————
62626201 067735
<32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 522 Offsets: 8185 to 8191 Dba:0x0080020a
——————————————————-
62626201 067735 l bbb..w5
<16 bytes per line>
7、应用变更
BBED> sum
Check value for File 2, Block 522:
current = 0xa285, required = 0xa286
此时 current checksum 是0xa285，requiredchecksum 是0xa286
BBED> sum apply
Check value for File 2, Block 522:
current = 0xa286, required = 0xa286
加上apply参数，使checksum一致。即之前的修改生效。
8、开启数据测试
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> col name for a20
SQL> select * from hr.b;
ID NAME
———- ——————–
1 bbb
ORA-00600 [2662]
一、错误现象（alert日志中）
Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc:
ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], []
二、错误解释
ORA-600 [2662] “Block SCN is ahead of Current SCN”，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数，分别代表不同的含义
ORA-600 [2662] [a] [b] {c} [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg {c} dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
注：897694446<897695488
三、错误原因
1.使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库
2.硬件错误引起数据库没法写控制文件和重做日志文件
3.错误的部分恢复数据库
4.恢复了控制文件但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题
四、解决办法
1、如果SCN相差不多，可以通过多次重起数据库解决（每次加1）
2、通过10015 ADJUST_SCN事件来增进current SCN
1）计算level
1.1) Arg {c}* 4得出一个数值，假设为V_Wrap
1.2) 如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level
1.3）SCN被增进了1024*1024*1024*level（level*10 billion）
2）执行内部事件
alter session set events ‘10015 trace name adjust_scn level N’;
注：mount状态下执行（open下无效）
alert日志中会出现：
Sat Aug 20 15:41:07 2011
Debugging event used to advance scn to 107374182400
SCN与Oracle数据库恢复的关系
一、SCN类型
1).System Checkpoint SCN
当checkpoint完成后，ORACLE将System Checkpoint SCN号存放在控制文件中。
我们可以通过下面SQL语句查询：
select checkpoint_change# from v$database;
2).Datafile Checkpoint SCN
当一个检查点动作完成之后，Oracle就把每个数据文件的scn单独存放在控制文件中.
我们可以通过下面SQL语句查询所有数据文件的Datafile Checkpoinnt SCN号。
select name,checkpoint_change# from v$datafile;
3).Start SCN (启动SCN)
checkpoint完成后，将产生的checkpoint SCN 写入数据文件头(称之为start scn).这个SCN用于检查数据库启动过程是否需要做media recovery.
我们可以通过以下SQL语句查询：
select name,checkpoint_change# from v$datafile_header;
注意：数据文件头中的检查点SCN(start SCN) 与控制文件中记录的数据文件检查点SCN号含义是一样的。 也就是说，一旦发生全局范围以及文件级别的检查点时，不仅会将这时的检查点SCN号记录到控制文件，还会记录在检查点作用的数据文件头部。
4).End SCN号(stop scn, 终止SCN)
这个SCN号用于检查数据库启动过程是否需要做instance recovery.
我们可以通过以下SQL语句查询：
select name,last_change# from v$datafile;
在正常的数据库操作过程中，所有正处于联机读写模式下的数据文件的终止scn都为null.
二、各SCN之间关系
1）数据库运行期间的scn值
在数据库打开并运行之后，控制文件中的系统检查点、控制文件中的数据文件检查点scn和每个数据文件头中的启动scn都是相同的。控制文件中的每个数据文件的终止scn都为null.
2）数据库正常关闭的scn值
在安全关闭数据库的过程中，系统会执行一个检查点动作，这时所有数据文件的终止scn都会设置成数据文件头中的那个启动scn的值。
3）数据库重启过程中scn作用
在数据库重新启动的时候, Oracle将文件头中的那个启动scn与数据库文件检查点scn(控制文件中)进行比较，如果这两个值相互匹配，那么不需要Media Recovery， oracle接下来还要比较数据文件头中的启动scn和控制文件中数据文件的终止scn, 如果这两个值也一致，就意味着所有对数据库的修改都没有在关闭数据库的过程中丢失，因此这次启动数据库的过程也不需要任何恢复操作(即不需要实例恢复)，此时数据库就可以打开了。当所有的数据库都打开之后，存储在控制文件中的数据文件终止scn的值再次被更改为null，这表示数据文件已经打开并能够正常使用了。
还需要注意的是:
在数据库重新启动的时候, Oracle首先比较(每个)文件头中的那个启动scn (start SCN) 与控制文件中记录的 (每个) 数据库文件检查点scn， 如果他们都相互匹配，那么不需要Media Recovery. 但是如果只是控制文件中记录的数据文件检查点(多个数据文件，对应多个SCN), 与 (对应的) 数据文件头中的启动SCN (start scn) 相同 , 而在每个在线的可读可写的数据文件“之间”，他们的检查点SCN不相同， 那么也要求Media Recovery .
ORA-01244/ORA-01110解决
rman 恢复过程中出现以下错误
RMAN> recover database; Starting recover at 20-AUG-11 using channel ORA_DISK_1 starting media recovery archive log filename=/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf thread=1 sequence=13 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/20/2011 03:54:30 ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf' ORA-00283: recovery session canceled due to errors ORA-01244: unnamed datafile(s) added to controlfile by media recovery ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
错误原因分析
在rman备份后，添加了数据文件，使用的是备份的控制文件进行恢复数据库导致（不能识别新的数据文件）
解决方法
通过sqlplus创建数据文件
SQL> alter database create datafile 2 as ‘/opt/oracle/oradata/xifenfei/xff01.dbf’;
Database altered.
然后继续在rman中执行恢复数据库操作
该情况说明
此中情况只有在oracle 9i中出现；在10g中，rman恢复过程会自动的创建新添加文件，见oracle 10g rman自动创建数据文件