EM dbconsole显示数据文件使用量为负数?

有网友在itpub上反应11g中查看EM dbconsole显示数据文件的使用量为负数,如图:

 

 

实际上导致(EM ( EM Home page -> Administration Tab -> Storage -> Datafiles ))显示负的使用量的Bug在11g和10g中均存在,这里将这些bug列出来以供参考:

 

10g Enterprise Manager Shows Negative Utilisation For An Oracle Datafile.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms

EM ( EM Home page -> Administration Tab -> Storage -> Datafiles ) shows negative 
utilization for some files. Value in Used (MB) column is negative.

Cause
This is because of Bug:5083393.

If the object is dropped and not purged, the value of FILE_ID / REL_FNO may be wrong in 
DBA_FREE_SPACE and this cause EM to display wrong value in Used (MB) column.

Solution
If available, download Patch:5083393 to resolve this issue.
Note that this patch is available on 10.2.0.2/10.2.0.3 for some platforms at the 
time of this writing this article (JULY 2007).

Workaround :
-------------
Purge all objects from the recycle bin.

$sqlplus "/as sysdba"
sql> purge dba_recyclebin 

Hdr: 5083393 10.1.0.4 RDBMS 10.1.0.4 SPACE PRODID-5 PORTID-912
Abstract: VALUE OF FILE_ID AND RELATIVE_FNO OF DBA_FREE_SPACE IS DIFFERENT

PROBLEM:
--------
When the table is dropped, the value of the FILE_ID column and
the RELATIVE_FNO column of dba_free_space might be different.

SQL> select * from dba_free_space where tablespace_name = 'TBS_TEST1';

TABLESPACE_NAME FILE_ID BLOCK_ID    BYTES BLOCKS RELATIVE_FNO
--------------- ------- -------- -------- ------ ------------
E
E
E
TBS_TEST1             6      105    65536      8            6
TBS_TEST1             6      113    65536      8            6
TBS_TEST1             6      121    65536      8            6
TBS_TEST1             6        9    65536      8            7   purge table tbs_test01;

Table purged.

SQL> select * from dba_free_space where tablespace_name = 'TBS_TEST1';

TABLESPACE_NAME FILE_ID BLOCK_ID    BYTES BLOCKS RELATIVE_FNO
--------------- ------- -------- -------- ------ ------------
TBS_TEST1             6        9   983040    120            6
TBS_TEST1             7        9   983040    120            7
TBS_TEST1             8        9 20905984   2552            8

In a word, there seems to be a problem in the display of an free space
concerning the segment that exists in RECYCLE BIN.

WORKAROUND:
-----------
None

RELATED BUGS:
-------------
None

REPRODUCIBILITY:
----------------
  100% (both In-house and Ct' site) 
  Rep?     Platform               RDBMS Ver. 
  -------  ---------------------  ---------- 
  Y(100%)  912 Intel Windows NT   10.1.0.4
  Y(100%)  912 Intel Windows NT   10.1.0.5
  Y(100%)  912 Intel Windows NT   10.2.0.2
  Y(100%)   46 Linux x86          10.1.0.4

 

11gR2 EMCONSOLE SHOWS NEGATIVE VALUES WHEN VIEWING DATAFILE USAGE SPACE IN 11.2.0.1

 

Hdr: 9868375 11.2.0.1 STORAGE 11.2.0.1 PRODID-1366 PORTID-226 9548105
Abstract: EMCONSOLE SHOWS NEGATIVE VALUES WHEN VIEWING DATAFILE USAGE SPACE IN 11.2.0.1

PROBLEM:
--------
The display of the usage of the data files (Tablespaces -> Datafiles) shows 
values in the range negative. The fill rate is not shown correctly.

This is the same exact issue as explained in note 559414.1 but on top of 
11.2.0.1 and also the issue does not seem fixed when trying the workaround by 
clearing the recycle bin or by turning it off.

DIAGNOSTIC ANALYSIS:
--------------------
Checked running the statements from sqlplus they provided the correct value 
and checked this Bug 5549540 seems it should have been fixed in 11G but still 
having the same issue.

WORKAROUND:
-----------
To run the statements and check the data file usage from sqlplus.

 

11gR1 Grid Control Shows Duplicate Datafile Names Multiple times (Some With Negative Values) When Drilling Onto Tablespace Link

Applies to:
Enterprise Manager Grid Control - Version: 10.2.0.5 to 11.1.0.1 - Release: 10.2 to 11.1
Information in this document applies to any platform.
Symptoms
The list of datafiles displayed for a tablespace in EM Grid Control is wrong, so that some 
datafiles are listed repeatedly multiple times, and some of the sizes are wrong and showing a negative value.
This is seen from database home page => server tab => tablespaces.
Click on tablespace name, a list of datafiles associated with that tablespace should be returned.
Cause
If the SQL statement which Grid Control uses is run manually at Sqlplus, the same output of the 
same datafile listed multiple times can also be seen. 
Here you have the SQL statement launched by the Grid Control to collect the list of datafiles :
SELECT d.file_name, TO_CHAR((d.bytes / 1024 / 1024), '99999990.000'), 
NVL(TO_CHAR(((d.bytes - s.bytes) / 1024 / 1024), '99999990.000'), TO_CHAR((d.bytes / 1024 / 1024), '99999990.000')), 
d.file_id, d.autoextensible, d.increment_by, d.maxblocks FROM sys.dba_data_files d, 
(SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space WHERE tablespace_name = '&tablespaceName' 
GROUP BY file_id) s WHERE (s.file_id (+)= d.file_id) AND (d.tablespace_name = '&tablespaceName')
This is the issue described in following bug :
Bug 8499373 GRID CONTROL SHOWS SAME DATAFILE NAME REPEATEDLY WHEN DRILL INTO A TABLESPACE
This bug is actually closed as a duplicate of a database bug Bug 8570838 WRONG RESULTS FOR QUERY AGAINST 
DBA_DATA_FILES IN 10.2.0.4
The root cause of the database bug is :
Bug 7155655 Intermittent Wrong Results/Plan Instability from Join Predicate Push Down (JPPD)
Solution
Apply the Patch 7155655. 
Note :
Depending on platform it may be necessary to apply a merge patch for this - eg. in some cases it was 
necessary to apply Patch 7278117.
Hdr: 9748363 11.2.0.1 STORAGE 11.1.0.1 PRODID-1366 PORTID-226 9548105
Abstract: NEGATIVE VALUES REPORTED BY GRIDCONTROLFOR TABLESPACE WITH AT LEAST TWO DATAFILE
*** 05/25/10 09:46 am ***
TAR
---
3-1772640611
Problem Description
-------------------
The Used (MB) column on the datafiles summary webpage in the 11.2 grid 
control 
shows negative values for a tablespace containing at least two datafiles
Environment Information
-----------------------
Test Case Step-by-Step Instructions
-----------------------------------
Test Case Location
------------------
Diagnostic Analysis
-------------------
This is the same as bug 9548105 except for this is for GridControl vs 
dbconsole.
SELECT d.file_name, TO_CHAR((d.bytes / 1024 / 1024), '99999990.000'),
NVL(TO_CHAR(((d.bytes - s.bytes) / 1024 / 1024), '99999990.000'),
TO_CHAR((d.bytes / 1024/ 1024), '99999990.000')), d.file_id, 
d.autoextensible,
d.increment_by, d.maxblocks FROM sys.dba_data_files d,  
(SELECT ts.name tablespace_name, SUM(e.length * ts.blocksize) bytes  
FROM sys.fet$ e, sys.ts$ ts  WHERE ts.ts# = e.ts# GROUP BY ts.name  
UNION ALL  SELECT ts.name tablespace_name, SUM(e.blocks * ts.blocksize) bytes 
FROM sys.dba_lmt_free_space e, sys.ts$ ts  WHERE ts.ts# = e.tablespace_id 
GROUP BY ts.name) s
WHERE (s.tablespace_name = d.tablespace_name) AND (d.tablespace_name = :1)
Performance
-----------
NLS Information
---------------
-------
Log Files Location
------------------
Reproducibility
---------------
yes
URL
---
Did you test with the latest version?
-------------------------------------
Available Workarounds
---------------------
none
Related Bugs
------------
9548105

Raid Level,该如何为Oracle存储选择才好?

Oracle Database中的文件可以分成多种:包括数据文件、联机日志文件、归档日志文件、临时文件及控制文件等。而这些文件在数据库运行时的I/O表现上也存在着巨大的差异:譬如控制文件的IO特点是随机读写多、日志文件和归档日志文件是顺序写、SYSTEM系统表空间总是有大量的随机读写IO、临时表空间和回滚表空间也会有大量随机读写IO。

那么针对以上不同文件的IO特征,我该如何选择合适的Raid Level呢?

著名的Cary Millsap为我们提供了以下图表可作参考:

raid5_color_coded3

以上1代表最佳,5代表最差

在OLTP环境中Raid 10比Raid 5更为合适,因为Raid 5的每次写出都会引发2次读取操作加上一次实际写出操作,而这2次读取操作可能与其他读取发生冲突。同时大多数情况下Raid 5会配置较小的条带化大小(stripe size),如32k或者64k,因而多块读操作可能需要跨越多个磁盘,这就增长了并行读取争用现象出现的概率。

以下为Oracle中常见IO等待事件的属性列表:
Wait Event R/W SIO/AIO Single/Multi Elapsed (with 1000+ waits per hour)
control file parallel write W AIO Multi < 15ms
control file sequential read R SIO Single < 20 ms
db file parallel read R AIO Multi < 20 ms
db file scattered read R SIO Multi < 20 ms
db file sequential read R SIO Single < 20 ms
direct path read R AIO Multi < 20 ms
direct path read temp R AIO Multi < 20 ms
direct path write W AIO Multi < 15 ms
direct path write temp W AIO Multi < 15 ms
log file parallel write W AIO Multi < 15 ms
Exadata Related
cell smart table scan R AIO Multi < 1 ms
cell single block physical read R SIO Single < 1 ms
cell multiblock physical read R SIO Multi < 6 ms

使用bbed解决ORA-01189错误

ORA-01189错误常见于使用Create Controlfile命令时发现OFFLINE的数据文件或者备份文件生成于上一次的RESETLOGS之前,一般来说如果是在RESETLOGS前offline的数据文件,可以在CREATE CONTROLFILE命令中省略该offline datafile,在打开数据库后使用rename file命令修复其丢失的数据文件名,之后再将数据文件online;若不是offline datafile所造成的ORA-01189错误,就必须要找出与其他数据文件版本一致的数据文件或其备份才能解决该问题。

设想存在这样一个场景:数据库处于非归档无备份的状态,在周日使用shutdown immediate命令干净地关闭了数据库,到周一发现因为磁盘故障丢失了所有的控制文件,不得不使用Create Controlfile RESETLOGS重建控制文件,而在重建过程中又漏输了某条数据文件的记录,之后又使用alter database open resetlogs重置日志文件并打开数据库,此时发现丢失了一个数据文件,尝试使用针对offline datafile的处理方法将丢失的数据文件重命名(rename),并尝试online该数据文件,毫无疑问我们会遇到ORA-01190 “control file or data file %s is from before the last RESETLOGS”错误,这时虽然我们手上有该”干净”的数据文件,却也没有办法使之online了。

如果自己搞不定可以找ASKMACLEAN专业ORACLE数据库修复团队成员帮您恢复!

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

 

诗檀软件专业数据库修复团队

 

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

这里我介绍一种使用bbed修改数据文件头的方法来解决该ORA-01189错误:

 

 

[oracle@rh2 controlfile]$ oerr ora 1189
01189, 00000, "file is from a different RESETLOGS than previous files"
// *Cause:  In a CREATE CONTROLFILE command either this file or all previous
//          files were backups from before the last RESETLOGS. This may also
//          occur if this is a file that is offline and has been offline since
//          before the last RESETLOGS.
// *Action: If the file was taken offline normal before the last RESETLOGS,
//          and is still offline, omit it from the CREATE CONTROLFILE command.
//          Rename and online the file after the database is open. Otherwise
//          find the version of the mentioned file consistent with the rest
//          of the datafiles and resubmit the command.
[oracle@rh2 controlfile]$ oerr ora 1190
01190, 00000, "control file or data file %s is from before the last RESETLOGS"
// *Cause: Attempting to use a data file when the log reset information in
//          the file does not match the control file. Either the data file
//          or the control file is a backup that was made before the most
//          recent ALTER DATABASE OPEN RESETLOGS.
// *Action: Restore file from a more recent backup.
/* 在我们的场景中wwli.dbf充当这样一个被"忽视"的数据文件 */
SQL> create tablespace we_will_lost_it datafile '/flashcard/wwli.dbf' size 20M;
Tablespace created.
SQL> create table lost_data tablespace we_will_lost_it as select * from obj$;
Table created.
SQL> alter database backup controlfile to trace ;
Database altered.
SQL> shutdown immediate;
/* 删除所有的控制文件,切勿用于生产环境! */
[oracle@rh2 ~]$ rm /flashcard/oradata/G10R2/controlfile/1.ctl 
[oracle@rh2 ~]$ sqlplus / as sysdba                           
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 27 20:42:31 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size                  2083560 bytes
Variable Size             318768408 bytes
Database Buffers          889192448 bytes
Redo Buffers               14692352 bytes
ORA-00205: error in identifying control file, check alert log for more info
/* 使用create controlfile resetlogs重建控制文件,这里我们"无意"中丢失了一个数据文件  */
SQL> CREATE CONTROLFILE REUSE DATABASE "G10R2" RESETLOGS  NOARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 292
7  LOGFILE
8    GROUP 1 (
9      '/flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log',
10      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_1_6v34jnst_.log'
11    ) SIZE 50M,
12    GROUP 2 (
13      '/flashcard/oradata/G10R2/onlinelog/o1_mf_2_6v34jokt_.log',
14      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_2_6v34jotq_.log'
15    ) SIZE 50M,
16    GROUP 3 (
17      '/flashcard/oradata/G10R2/onlinelog/o1_mf_3_6v34jpmp_.log',
18      '/s01/flash_recovery_area/G10R2/onlinelog/o1_mf_3_6v34jpyn_.log'
19    ) SIZE 50M
20  -- STANDBY LOGFILE
21  DATAFILE
22    '/flashcard/oradata/G10R2/datafile/o1_mf_system_6v34hhyw_.dbf',
23    '/flashcard/oradata/G10R2/datafile/o1_mf_undotbs1_6v34hj0l_.dbf',
24    '/flashcard/oradata/G10R2/datafile/o1_mf_sysaux_6v34hhyx_.dbf',
25    '/flashcard/oradata/G10R2/datafile/o1_mf_users_6v34hj0p_.dbf',
26    '/flashcard/oradata/G10R2/datafile/o1_mf_example_6v34jthj_.dbf',
27    '/flashcard/oradata/G10R2/datafile/o1_mf_test_6vhlm3mv_.dbf',
28    '/flashcard/oradata/G10R2/datafile/o1_mf_lostfile_6vhtgo7w_.dbf'
 29    -- we lost datafile here!!! '/flashcard/wwli.dbf'
30  CHARACTER SET UTF8
31  ;
Control file created.
SQL> recover database using backup controlfile  ;
ORA-00279: change 690423 generated at 04/27/2011 20:40:36 needed for thread 1
ORA-00289: suggestion :
/s01/flash_recovery_area/G10R2/archivelog/2011_04_27/o1_mf_1_1_%u_.arc
ORA-00280: change 690423 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/flashcard/oradata/G10R2/onlinelog/o1_mf_1_6v34jnkn_.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
9 OFFLINE OFFLINE FILE MISSING                                                               0
SQL> select name from v$datafile where file#=9;
NAME
--------------------------------------------------
/s01/db_1/dbs/MISSING00009
SQL> alter database rename file '/s01/db_1/dbs/MISSING00009' to '/flashcard/wwli.dbf';
Database altered.
SQL> alter database datafile 9 online;
alter database datafile 9 online
*
ERROR at line 1:
ORA-01190: control file or data file 9 is from
before the last RESETLOGS
ORA-01110: data file 9: '/flashcard/wwli.dbf'
/* 此时遭遇了我们之前描述的问题!  */
SQL> oradebug setmypid;
Statement processed.
/* 使用dump file_hdrs命令转储数据文件头,我们转储数据文件头的目的是为了获取resetlogs count,resetlogs scn */
SQL> oradebug dump file_hdrs 8;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_20029.trc
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000005 03/12/2008 00:39:08
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 04/27/2011 20:45:34
status:0x2004 root dba:0x00400179 chkpt cnt: 85 ctl cnt:84
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a88fa 04/27/2011 20:46:00
Tablespace #1 - UNDOTBS1  rel_fn:2
Creation   at   scn: 0x0000.00092a47 03/12/2008 01:19:05
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cade887 scn: 0x0000.000a88f9 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 04/27/2011 20:45:34
status:0x4 root dba:0x00000000 chkpt cnt: 45 ctl cnt:44
begin-hot-backup file size: 0
Tablespace #10 - WE_WILL_LOST_IT  rel_fn:9
Creation   at   scn: 0x0000.000a8849 04/27/2011 20:39:27
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2cadd4e7 scn: 0x0000.000a7f86 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2cadcee3 scn: 0x0000.000a2af7 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 01/01/1988 00:00:00
status:0x0 root dba:0x00000000 chkpt cnt: 3 ctl cnt:2
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a88f7 04/27/2011 20:40:36
thread:1 rba:(0x1.c6f.10)

以上可以看到数据文件1和2的resetlogs count(0x2cade887),scn(000a88f9)都是一样的值,而我们的问题数据文件头中的对应信息则与之不同!

接下来我们使用bbed命令去修改问题数据文件头中的resetlogs count,scn信息,关于bbed的编译和使用可以参考这里。

[oracle@rh2 ~]$ bbed filename=/flashcard/wwli.dbf blocksize=8192 password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Apr 27 20:59:14 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 1
BLOCK#          1
BBED> map
File: /flashcard/wwli.dbf (0)
Block: 1                                     Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes                    @0       
ub4 tailchk                                @8188    
BBED> p kcvfh
struct kcvfh, 676 bytes                     @0
struct kcvfhbfh, 20 bytes                @0
ub1 type_kcbh                         @0        0x0b
ub1 frmt_kcbh                         @1        0xa2                     format 10.2
ub1 spare1_kcbh                       @2        0x00
ub1 spare2_kcbh                       @3        0x00
ub4 rdba_kcbh                         @4        0x02400001           RDBA
ub4 bas_kcbh                          @8        0x00000000
ub2 wrp_kcbh                          @12       0x0000
ub1 seq_kcbh                          @14       0x01
ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
ub2 chkval_kcbh                       @16       0xafa1                checksum
ub2 spare3_kcbh                       @18       0x0000
struct kcvfhhdr, 76 bytes                @20
ub4 kccfhswv                          @20       0x00000000
ub4 kccfhcvn                          @24       0x0a200300          compatible
ub4 kccfhdbi                          @28       0xac859d12
text kccfhdbn[0]                      @32      G                         DB_NAME
text kccfhdbn[1]                      @33      1
text kccfhdbn[2]                      @34      0
text kccfhdbn[3]                      @35      R
text kccfhdbn[4]                      @36      2
text kccfhdbn[5]                      @37
text kccfhdbn[6]                      @38
text kccfhdbn[7]                      @39
ub4 kccfhcsq                          @40       0x00000245
ub4 kccfhfsz                          @44       0x00000a00
s_blkz kccfhbsz                       @48       0x00
ub2 kccfhfno                          @52       0x0009                   FILE NUM
ub2 kccfhtyp                          @54       0x0003                   FILE TYPE
ub4 kccfhacid                         @56       0x00000000
ub4 kccfhcks                          @60       0x00000000
text kccfhtag[0]                      @64
text kccfhtag[1]                      @65
text kccfhtag[2]                      @66
text kccfhtag[3]                      @67
text kccfhtag[4]                      @68
text kccfhtag[5]                      @69
text kccfhtag[6]                      @70
text kccfhtag[7]                      @71
text kccfhtag[8]                      @72
text kccfhtag[9]                      @73
text kccfhtag[10]                     @74
text kccfhtag[11]                     @75
text kccfhtag[12]                     @76
text kccfhtag[13]                     @77
text kccfhtag[14]                     @78
text kccfhtag[15]                     @79
text kccfhtag[16]                     @80
text kccfhtag[17]                     @81
text kccfhtag[18]                     @82
text kccfhtag[19]                     @83
text kccfhtag[20]                     @84
text kccfhtag[21]                     @85
text kccfhtag[22]                     @86
text kccfhtag[23]                     @87
text kccfhtag[24]                     @88
text kccfhtag[25]                     @89
text kccfhtag[26]                     @90
text kccfhtag[27]                     @91
text kccfhtag[28]                     @92
text kccfhtag[29]                     @93
text kccfhtag[30]                     @94
text kccfhtag[31]                     @95
ub4 kcvfhrdb                             @96       0x00000000
struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
ub4 kscnbas                           @100      0x000a8849
ub2 kscnwrp                           @104      0x0000
ub4 kcvfhcrt                             @108      0x2cade6ff           
ub4 kcvfhrlc                             @112      0x2cadd4e7               resetlogs count
struct kcvfhrls, 8 bytes                 @116
ub4 kscnbas                           @116      0x000a7f86              resetlogs scn
ub2 kscnwrp                           @120      0x0000
ub4 kcvfhbti                             @124      0x00000000
struct kcvfhbsc, 8 bytes                 @128
ub4 kscnbas                           @128      0x00000000
ub2 kscnwrp                           @132      0x0000
ub2 kcvfhbth                             @136      0x0000
ub2 kcvfhsta                             @138      0x0000 (NONE)
struct kcvfhckp, 36 bytes                @484
struct kcvcpscn, 8 bytes              @484                                  Checkpoint scn
ub4 kscnbas                        @484      0x000a88f7
ub2 kscnwrp                        @488      0x0000
ub4 kcvcptim                          @492      0x2cade744
ub2 kcvcpthr                          @496      0x0001
union u, 12 bytes                     @500
struct kcvcprba, 12 bytes          @500
ub4 kcrbaseq                    @500      0x00000001
ub4 kcrbabno                    @504      0x00000c6f
ub2 kcrbabof                    @508      0x0010
ub1 kcvcpetb[0]                       @512      0x02
ub1 kcvcpetb[1]                       @513      0x00
ub1 kcvcpetb[2]                       @514      0x00
ub1 kcvcpetb[3]                       @515      0x00
ub1 kcvcpetb[4]                       @516      0x00
ub1 kcvcpetb[5]                       @517      0x00
ub1 kcvcpetb[6]                       @518      0x00
ub1 kcvcpetb[7]                       @519      0x00
ub4 kcvfhcpc                             @140      0x00000003
ub4 kcvfhrts                             @144      0x00000000
ub4 kcvfhccc                             @148      0x00000002
struct kcvfhbcp, 36 bytes                @152
struct kcvcpscn, 8 bytes              @152
ub4 kscnbas                        @152      0x00000000
ub2 kscnwrp                        @156      0x0000
ub4 kcvcptim                          @160      0x00000000
ub2 kcvcpthr                          @164      0x0000
union u, 12 bytes                     @168
struct kcvcprba, 12 bytes          @168
ub4 kcrbaseq                    @168      0x00000000
ub4 kcrbabno                    @172      0x00000000
ub2 kcrbabof                    @176      0x0000
ub1 kcvcpetb[0]                       @180      0x00
ub1 kcvcpetb[1]                       @181      0x00
ub1 kcvcpetb[2]                       @182      0x00
ub1 kcvcpetb[3]                       @183      0x00
ub1 kcvcpetb[4]                       @184      0x00
ub1 kcvcpetb[5]                       @185      0x00
ub1 kcvcpetb[6]                       @186      0x00
ub1 kcvcpetb[7]                       @187      0x00
ub4 kcvfhbhz                             @312      0x00000000
struct kcvfhxcd, 16 bytes                @316
ub4 space_kcvmxcd[0]                  @316      0x00000000
ub4 space_kcvmxcd[1]                  @320      0x00000000
ub4 space_kcvmxcd[2]                  @324      0x00000000
ub4 space_kcvmxcd[3]                  @328      0x00000000
word kcvfhtsn                            @332      10                         Tablespace#
ub2 kcvfhtln                             @336      0x000f
text kcvfhtnm[0]                         @338     W                          TABLESPACE_NAME
text kcvfhtnm[1]                         @339     E
text kcvfhtnm[2]                         @340     _
text kcvfhtnm[3]                         @341     W
text kcvfhtnm[4]                         @342     I
text kcvfhtnm[5]                         @343     L
text kcvfhtnm[6]                         @344     L
text kcvfhtnm[7]                         @345     _
text kcvfhtnm[8]                         @346     L
text kcvfhtnm[9]                         @347     O
text kcvfhtnm[10]                        @348     S
text kcvfhtnm[11]                        @349     T
text kcvfhtnm[12]                        @350     _
text kcvfhtnm[13]                        @351     I
text kcvfhtnm[14]                        @352     T
text kcvfhtnm[15]                        @353
text kcvfhtnm[16]                        @354
text kcvfhtnm[17]                        @355
text kcvfhtnm[18]                        @356
text kcvfhtnm[19]                        @357
text kcvfhtnm[20]                        @358
text kcvfhtnm[21]                        @359
text kcvfhtnm[22]                        @360
text kcvfhtnm[23]                        @361
text kcvfhtnm[24]                        @362
text kcvfhtnm[25]                        @363
text kcvfhtnm[26]                        @364
text kcvfhtnm[27]                        @365
text kcvfhtnm[28]                        @366
text kcvfhtnm[29]                        @367
ub4 kcvfhrfn                             @368      0x00000009
struct kcvfhrfs, 8 bytes                 @372
ub4 kscnbas                           @372      0x00000000
ub2 kscnwrp                           @376      0x0000
ub4 kcvfhrft                             @380      0x00000000
struct kcvfhafs, 8 bytes                 @384
ub4 kscnbas                           @384      0x00000000
ub2 kscnwrp                           @388      0x0000
ub4 kcvfhbbc                             @392      0x00000000
ub4 kcvfhncb                             @396      0x00000000
ub4 kcvfhmcb                             @400      0x00000000
ub4 kcvfhlcb                             @404      0x00000000
ub4 kcvfhbcs                             @408      0x00000000
ub2 kcvfhofb                             @412      0x0000
ub2 kcvfhnfb                             @414      0x0000
ub4 kcvfhprc                             @416      0x2cadcee3     prev reset logs count
struct kcvfhprs, 8 bytes                 @420                        prev reset scn
ub4 kscnbas                           @420      0x000a2af7
ub2 kscnwrp                           @424      0x0000
struct kcvfhprfs, 8 bytes                @428
ub4 kscnbas                           @428      0x00000000
ub2 kscnwrp                           @432      0x0000
ub4 kcvfhtrt                             @444      0x00000000
/* 以上kcvfh结构是数据文件头的主要信息,其中ub4 kcvfhrlc(offset 112)记录了resetlogs count,
而 struct kcvfhrls 8 bytes(offset 116)记录了resetlogs scn */
/* 接下来将问题数据文件头上的kcvfhrlc和kcvfhrls信息修改成和1号文件中的一样,
以欺骗Oracle让其以为该数据文件参与了上一次的RESETLOGS                 */
BBED> set offset 112
OFFSET          112
/* 在使用bbed modify的时候需要注意所在平台的endian,Linux上使用Little Endian */
BBED> modify /x 87e8
File: /flashcard/wwli.dbf (0)
Block: 1                Offsets:  112 to  623           Dba:0x00000000
------------------------------------------------------------------------
87e8ad2c 867f0a00 00000000 00000000 00000000 00000000 00000000 03000000
00000000 02000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0a000000
0f005745 5f57494c 4c5f4c4f 53545f49 54000000 00000000 00000000 00000000
09000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 e3cead2c f72a0a00 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 f7880a00 00000000 44e7ad2c
01000160 01000000 6f0c0000 10000000 02000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhrlc
ub4 kcvfhrlc                                @112      0x2cade887
BBED> set offset 116
OFFSET          116
BBED> modify /x 0xf988
File: /flashcard/wwli.dbf (0)
Block: 1                Offsets:  116 to  627           Dba:0x00000000
------------------------------------------------------------------------
f9880a00 00000000 00000000 00000000 00000000 00000000 03000000 00000000
02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0a000000 0f005745
5f57494c 4c5f4c4f 53545f49 54000000 00000000 00000000 00000000 09000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 e3cead2c f72a0a00 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 f7880a00 00000000 44e7ad2c 01000160
01000000 6f0c0000 10000000 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes                    @116
ub4 kscnbas                              @116      0x000a88f9
ub2 kscnwrp                              @120      0x0000
/* 还需要将kcvfhckp结构中的kcvcpscn也修改成与1号文件一样的resetlogs scn ,
否则在recover数据文件的时候可能出现
ORA-00600: internal error code, arguments: [2608], [1], [0], [690423], [0], [690425], [], []错误  */
BBED> set offset 484
OFFSET          484
BBED> modify /x 0xf9
File: /flashcard/wwli.dbf (0)
Block: 1                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
f9880a00 00000000 44e7ad2c 01000160 01000000 6f0c0000 10000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02004002 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
struct kcvcpscn, 8 bytes                 @484
ub4 kscnbas                           @484      0x000a88f9
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x2cade744
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500
struct kcvcprba, 12 bytes             @500
ub4 kcrbaseq                       @500      0x00000001
ub4 kcrbabno                       @504      0x00000c6f
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> sum
Check value for File 0, Block 1:
current = 0xb897, required = 0xb899
/* 使用sum apply命令修改该数据块的checksum值  */
BBED> sum apply
Check value for File 0, Block 1:
current = 0xb899, required = 0xb899
SQL>  alter database datafile '/flashcard/wwli.dbf' online;
alter database datafile '/flashcard/wwli.dbf' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/flashcard/wwli.dbf'
/* 完美地recover datafile 9,因为该数据文件中的数据本身就是"干净"的,
仅仅是datafile header中的resetlogs信息与其他datafile不一致而已  */
SQL> recover datafile 9;
Media recovery complete.
SQL> select * from v$recover_file where file#=9;
no rows selected
SQL> select count(*) from lost_data ;
COUNT(*)
----------
51791
/* That's great!

虽然我们也可以利用adjust_scn和设置隐藏参数”_allow_resetlogs_corruption”来尝试解决ORA-01189错误,但使用bbed可以避免用到”_allow_resetlogs_corruption”这个危险参数,且在之后更完美地recover恢复数据文件。

需要注意的是使用bbed修复问题数据文件并不能保证必然都成功,在使用bbed之前必须执行必要的备份,并在专业人士的指导下操作!

Oracle等待事件:Data file init write

在给某一个大表加有default值的clob列时出现了Data file init write等待事件,这个等待事件是10gR2中新加入的,恰恰10gr2的文档(乃至11g的文档)都没有列出该等待事件。该等待事件一般在Oracle自动扩展数据文件(auto extend datafile)并串行地格式化数据文件的新创建的空间时作为前台(foreground)等待事件出现:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
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> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.3
/* 数据库版本10.2.0.4,compatible为10.2.0.3 */
SQL> alter table tv add spare6 clob default '........';
SQL> col event for a30;
SQL> select event, time_waited, current_file#, current_block#
2    from V$active_Session_History
3   where event like 'Data file init write'
4   order by sample_time desc;
EVENT                          TIME_WAITED CURRENT_FILE# CURRENT_BLOCK#
------------------------------ ----------- ------------- --------------
Data file init write                533810             4          54783
Data file init write                442042             4          54783
Data file init write                 47286             4          54783
Data file init write                 42932             4          54783
Data file init write                413477             4          54783
Data file init write                153503             4          54783
Data file init write                 33520             4          54783
Data file init write                307616             4          54783
Data file init write                214404             4          54783
Data file init write                  3400             4          54783
Data file init write                212386             4          54783
Data file init write                192631             4          54783
..............
Data file init write                611157             4          54775
Data file init write                  1379             4          54775
Data file init write                223541             4          54775

伴随着数据文件扩展(Data file init write等待事件的直接触发原因),Oracle需要通过以下递归dml语句维护相应的数据字典:

1.查询字典表file$确定表空间对应的数据文件号
select file# from file$ where ts#=:1
该操作可能伴随Data file init write等待事件发生:
PARSING IN CURSOR #3 len=36 dep=2 uid=0 oct=3 lid=0 tim=1261083587010014 hv=1570213724 ad='8f7d4210'
select file# from file$ where ts#=:1
END OF STMT
.............
WAIT #14: nam='Data file init write' ela= 1091 count=1 intr=256 timeout=4294967295 obj#=57314 
WAIT #14: nam='Data file init write' ela= 1078 count=1 intr=256 timeout=4294967295 obj#=57314 
WAIT #14: nam='Data file init write' ela= 1102 count=1 intr=256 timeout=4294967295 obj#=57314 
WAIT #14: nam='Data file init write' ela= 1156 count=1 intr=256 timeout=4294967295 obj#=57314 
WAIT #14: nam='Data file init write' ela= 1870 count=1 intr=256 timeout=4294967295 obj#=57314 
WAIT #14: nam='Data file init write' ela= 37 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 4 count=4294967295 intr=32 timeout=2147483647 obj#=57314 
2.若使用bigfile tablespace则可能出现以下insert seg$流程
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,
extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, 
spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,
DECODE(:17,0,NULL,:17),:18)
PARSE #15:c=0,e=368,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1261083586909081
BINDS #15:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2ba79a0e1330  bln=22  avl=03  flg=05
value=1024
SQL> select file#,block#,blocks from sys.seg$ where file#=1024;
FILE#     BLOCK#     BLOCKS
---------- ---------- ----------
1024         82   13434880
1024   13434962      16384
1024   13451346      16384
1024   13467730      16384
1024   13484114      16384
/* 会出现奇怪的1024号文件 */
3.维护用户表空间限额字典数据
update tsq$
set blocks    = :3,
maxblocks = :4,
grantor#  = :5,
priv1     = :6,
priv2     = :7,
priv3     = :8
where ts# = :1
and user# = :2
4.更新seg$数据字典基表以扩展数据段
update seg$
set type#     = :4,
blocks    = :5,
extents   = :6,
minexts   = :7,
maxexts   = :8,
extsize   = :9,
extpct    = :10,
user#     = :11,
iniexts   = :12,
lists     = decode(:13, 65535, NULL, :13),
groups    = decode(:14, 6 5535, NULL, :14),
cachehint = :15,
hwmincr   = :16,
spare1    = DECODE(:17, 0, NULL, :17),
scanhint  = :18
where ts# = :1
and file# = :2
and block# = :3

以上数据文件空间扩展流程中只有查询语句”select file# from file$ where ts#=:1″伴随有”Data file init write”等待。

沪ICP备14014813号

沪公网安备 31010802001379号