Oracle 8i 备份/恢复(Recovery Manager)

本文永久地址:https://www.askmaclean.com/archives/oracle-8i-备份恢复(recovery-manager).html

说明

Recovery Manager(RMAN)是从Oracle8 R8.0之后追加的管理oracle数据库备份/储存/恢复的工具。在此,我将解说oracle8i的RMAN中扩展的新功能。

 

  • RMAN基本功能的扩展
  • 媒介管理软件相关功能的扩展
  • OPS相关功能的扩展

在此对于RMAN命令的详细语法方面的内容,请参考《Oracle8i备份、恢复指南》

 

RMAN基本功能的扩展

1-1.复制备份set

考虑到Oracle的备份时,类似于归档日志以及控制文件的案例,某个备份受损可能造成毁灭性的故障。Oracle8i的RMAN中,可以一次制成多个类似的的备份。因此,在存储时某个备份无法使用的话,用于其他备份就OK了。

为了制成多个备份,需要通过RMAN命令的SET DUPLEX命令来指定复制数,执行备份。SET DUPLEX命令如下所示。

 

SET DUPLEX = {ON|OFF|1|2|3|4};

 

关键词ON意味着指定为2 。DUPLEX最多可以制成4个拷贝。以下是使用SET DUPLEX命令的范例。

RUN {

SET DUPLEX =2;

ALLOCATE CHANNEL ch1 TYPE ‘sbt_tape’;

BACKUP

FORMAT ‘arc_%s_%p_%c’ FILESPERSET 1

ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL ch1;

}

SET DUPLEX命令需要比分配channel(ALLOCATE CHANNEL命令)更早执行。另外,执行SET DUPLEX命令后,在分配到的所有channel中执行备份复制。
在制成tape装置的备份是,需要指定初始化参数BACKUP_TAPE_IO_SLAVES = TRUE。
执行复制备份时,用指定备份名的FORMAT句,可以用替换字符串“%”来指定。在上述例子中,可以用替换字符串“%”来指定。在上述例子中,备份set编号为10,备份piece为1时制成 ‘arc_10_1_1’、‘arc_10_1_2’两个备份。

通过RMAN制成克隆数据库

Oracle8i的RMAN中,使用RMAN的备份,在远程主机或本地主机中,制成数据库的拷贝(克隆数据库)。因为克隆数据库与真正的数据库结构完全相同,所以并不会对正式环境的数据库产生影响。可以作为测试专用数据库。

左:正式数据库、克隆数据库、制成克隆数据库 本地主机

右:克隆数据库、远程主机

通过RMAN制成克隆数据库是,以下操作可以使用RMAN完成。

  • 克隆数据库中所使用的数据库文件的还原。在远程主机中制成克隆数据库时,是经过Net8来传送文件,还原文件的。
  • 直到执行命令时,需要执行应用了REDO的不完整恢复
  • 启动指定不完整恢复之后的RESETLOGS选项的数据库
  • 对新数据库重命名
  • 制成控制文件

通过RMAN制成克隆数据库时,伴随着指定了RESETLOGS的数据库的启动,就无法在制成stand by数据库时使用。

 

以下命令是远程主机制成克隆数据库的例子。为了完成这个操作,需要实现在远程主机中进行以下操作。

 

  • 收集远程主机中的ora参数文件拷贝
  • 设定需要连接远程主机的net8
  • 编辑ora、tnsnames.ora
  • 启动listener

 

完成这些设定之后,请使用以下命令来制成克隆数据库。下例是通过Net8连接描述符来指定在本地设备中的克隆数据库,制成远程主机中的克隆数据库。

 

 

# 启动RMAN,这时请指定克隆数据库的连接描述符。
%rman connect target / auxiliary sys/passwd@clonedb catalog rman/rman/@rcat

# 以下是制成克隆数据库的RMAN命令。
RMAN>STARTUP NOMOUNT;
RMAN> RUN{
2>      ALLOCATE AUXILIARY CHANNEL ch1 TYPE DISK;
3>      DUPLICATE TARGET DATABASE TO CLONEDB 
4>         LOGFILE ‘/Oracle/dbs/log1.f’ SIZE 200K,
5>                 ‘/Oracle/dbs/log2.f’ SIZE 200K 
6>         NOFILECHECK;
7>    }


DUPLICATE命令中,需要原数据库与克隆数据库中所使用的数据库文件名不重复。因此,在同样的目录结构中,在远程主机中制成克隆数据库时,需要设置为指定NOFILECHECK选项而不执行这个检查。
具体的克隆数据库制作顺序请参考《执行例与结果》。

通过RMAN来制成克隆数据库的详细内容请参考「Oracle8i备份、恢复指南」。

启动/关闭数据库

Oracle8 R8.0中为了对数据库进行冷备份以及恢复,需要启动、关闭数据库时,需要使用Server Manager执行这些操作。因此通过RMAN来执行修复时,就会组合SVRMGR与RMAN来一起使用。一些脚本由此就会变得复杂。

在Oracle8i的RMAN中,作为RMAN的组合命令支撑数据库的STARTUP、SHUTDOWN、ALTER DATABASE命令。由此就可以仅凭RMAN启动、关闭数据库。

 

追加命令 功能
STARTUP … 启动数据库。可以指定SQL*Plus的STARTUP命令以及同样的选项。
SHUTDOWN … 关闭数据库。可以指定SQL*Plus的SHUTDOWN命令以及相关选项。
ALTER DATABASE … 将数据库从MOUNT状态变更为OPEN状况。

 

在以下RMAN命令中,获得启动中终止的数据库的冷备份。重启数据库。

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN{
    ALLOCATE CHANNEL ch1 TYPE 'SBT_TAPE';
    BACKUP
      FORMAT 'db_%s_%p'
     (DATABASE);
}
ALTER DATABASE OPEN;

 

扩展LIST命令

LIST命令是展示至今为止获得的备份的信息的命令。Oracle8i的RMAN中,LIST命令已经被扩展为执行时会展示各自的备份片的名称、所有的数据文件的名称,控制文件信息等。以下是展示表区域bo1的备份set的一览的例子。

 

 

RMAN> LIST BACKUPSET OF TABLESPACE bol;

RMAN-03022: compiling command: list

List of Backup Sets
Key     Recid      Stamp      LV Set Stamp  Set Count  Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
1       1          365975325  0  365975324  2          20-APR-99

    List of Backup Pieces
    Key     Pc# Cp# Status      Completion Time        Piece Name
    ------- --- --- ----------- ---------------------- -----------------------
    2       1   2   AVAILABLE   20-APR-99              /bkup/bol_02at0los_1_2
    1       1   1   AVAILABLE   20-APR-99              /bkup/bol_02at0los_1_1

    List of Datafiles Included
    File Name                                  LV Type Ckp SCN    Ckp Time
    ---- ------------------------------------- -- ---- ---------- ------------
    4    /oradata/je815/dbs/bolje815.dbf       0  Full 578102     20-APR-99

 

LIST命令中,仅会展示备份的统计数据的平均值。展示deleted、unavailable状态的备份信息时,请使用LIST ALL … 命令。

通过RMAN制成、更新恢复目录

Oracle8 R8.0中,需要使用SQL*Plus来制成、更新、删除、储存RMAN使用的信息的恢复目录的脚本。

Oracle8i中,变更为将这些操作作为RMAN的命令来执行了。这些命令是用RMAN启动时的选项RCVCAT来指定的,对于用户的架构来执行的。

 

追加命令 功能
CREATE CATALOG 制成现在连接的架构中的恢复目录。可以用TABLESPACE语句来指定制成目录的表区域。
UPDATE CATALOG 连接到旧版本的目录时,执行目录的更新。UPDATE CATALOG命令为了确认,连续执行两次,于是就会进行更新。
DROP CATALOG 删除现在连接的架构中的目录。

DROP CATALOG命令连续执行两次时,就会删除目录。

执行对于目录的操作的用户,需要重新赋予RECOVERY_CATALOG_OWNER权限。

另外,Oracle8i中为了制成目录,而附属为SQL的脚本catrman.sql。必须要用到RMAN的CREATE CATALOG命令。

 

以下的命令是执行目录更新的例子。

 

RMAN> UPGRADE CATALOG
RMAN-06435: recovery catalog owner is rman
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG
RMAN-06408: recovery catalog upgraded to version 08.01.05

 

媒介管理软件相关功能的扩展

2-1. 恢复目录以及媒介管理软件目录的项目检查

使用RMAN中的tape装置,执行备份时,oracle并不需要媒介管理软件。因为一般媒介管理软件中,是通过目录来管理tape的,作为系统来考虑时,就会存在RMAN以及媒介管理软件两个目录。

Oracle8 R8.0的RMAN需要用户管理两个目录的同步。比如重新使用(覆盖)媒介管理软件中的tape时,RMAN的目录中包含这些tape的备份信息就会被留下。因此,用户使用RMAN的目录管理命令,需要从恢复目录中删除这些备份信息。

Oracle8i的RMAN,就会执行恢复目录以及媒介管理软件中的目录同步命令。

 

 

追加命令 功能
ALLOCATE CHANNEL …

FOR MAINTENANCE ;

为了执行目录维护,需要分配将要使用的channel。以下展示的命令在执行前,需要用这个命令来分配channel。
CROSSCHECK … ;

CHANGE… CROSSCHECK ;

这些命令用于确认指定的备份是否存在(可以从RMAN进行访问)。执行命令时,RMAN无法访问备份的话,那个备份就会被标记以及EXPIRED ,被判断为无效。这个备份可以用LIST命令以及REPORT命令来确认。

以下是使用CROSSCHECK命令的脚本例。

 

ALLOCATE CHANNEL FOR MAINTENANCE TYPE ‘sbt_tape’;

CROSSCHECK BACKUPSET OF “/Oracle/dbs/tbs_8.f”;

没有连接恢复目录时,无法使用CROSSCHECK命令。这时,请使用CHANGE … CROSSCHECK命令。CHANGE … CROSSCHECK命令没有连接恢复目录时,也可以通过控制文件中的信息以及每集管理软件的信息来进行交互检测。

EXPIRED以及被标记的备份,可以在执行下次的CHANGE … CROSSCHECK命令时,访问备份时,会被标记为AVAILABLE。类似的功能就不会再用CROSSCHECK命令来执行。这是为了确认CROSSCHECK命令是否将状态变为AVAILABE。

CROSSCHECK命令,CHANGE … CROSSCHECK命令中,需要检测是否可以访问。因此,检测备份破损时,请使用VALIDATE命令。

2-2. 使用了媒介管理软件的固有的功能的数据库备份
(PROXY COPY)

使用Oracle8 R8.0的RMAN获得的备份,对数据文件的I/O,是由oracle的服务器进程来执行。因此媒介管理软件会执行oracle的服务器进程以及数据流的对策。

Oracle8i中媒介管理软件以及接口中,媒介管理软件自身对于数据库的文件追加了执行I/O的API。通过使用API,使用媒介管理软件的固有功能,就可以高速执行备份以及还原。Oracle中这个功能称为PROXY COPY。

 

通过这个API执行备份时,oracle将对象变更为备份模式之后,将包含在对象中的文件列表移交给媒介管理软件。媒介管理软件根据被移交的列表,使用各个公司自带的功能来启动数据库。

媒介管理软件对应PROXY COPY功能时,执行备份命令时,通过指定PRXY语句,可以执行使用了媒介管理软件固有功能的备份。

 

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’;
BACKUP
  PROXY
  (TABLESPACE SYSTEM FORMAT ‘system_%s_%p’); 
} 

为了使用PROXY COPY功能需要对应PROXY COPY的媒介管理软件。不对应PROXY COPY时,指定PROXY语句时也可以通过API来执行备份。但是PROXY COPY功能无法使用时就会报错。

指定备份池

在大部分的内存管理软件中,可以在tape装置以及那个装置中使用的tape组作为“池”的概念来管理。换言之,各个tape装置以及tape装置都属于某个池,对其备份时进行选择时,可以选择想使用的tape装置以及tape。

Oracle8 R8.0中,因为可以在备份时选择池,媒介管理软件所参考的环境变量中,采取重新指定池的方法以及指定RMAN的ALLOCATE CHANNEL命令PARMS语句中的环境变量的方法。

Oracle8i中通过RMAN的BACKUP命令,可以直接指定池。通过BACKUP命令的POOL语句可以指定对应数字进行设定。

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’;
BACKUP 
  POOL 2
  (TABLESPACE SYSTEM FORMAT ‘system_%S_%P’);
} 


能否使用POOL语句来指定依赖于媒介管理软件。详细内容请参考媒介管理软件手册。

3. Oracle Prallel Server(OPS)相关的功能扩展

 

备份时的磁盘亲和性(Disk Affinity)以及分散负荷

MPP结构的OPS中,因为会连接到各个设备的各个磁盘中,访问速度并不是相等的。因此,为了使得各个设备中的磁盘I/O最优化,需要尽可能对连接到本地的设备执行I/O。

Oracle8 R8.0的OPS环境中,使用RMAN执行备份时,为了执行磁盘I/O的最优化,重要的是,在用户端中对各个文件分割合适的节点的channel(执行备份的服务器进程)。以下是RMAN的命令例,在由4个节点构成的OPS环境中,对各节点连接到本地设备上的数据文件进行备份。

 

 

 

RUN{
    ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
    ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
    ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
    ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
    BACKUP 
      FILESPERSET 1 FORMAT ‘DBFILE_%s_%p’
      (DATAFILE ‘/dev/rdsk/lv1’ CHANNEL ch1)
      (DATAFILE ‘/dev/rdsk/lv2’ CHANNEL ch2)
      (DATAFILE ‘/dev/rdsk/lv3’ CHANNEL ch3)
      (DATAFILE ‘/dev/rdsk/lv4’ CHANNEL ch4);
}

Oracle8i的RMAN中,在执行备份时,以设备亲和性为基础,对备份对象数据文件进行集群化(group化)这时,备份对象的数据文件所属的节点中如果分配到channel的话,通过channel就可以进行备份。节点中没有分配到channel时,节点用于的数据文件,特别是不需要考虑到亲和性。对于可以使用的channel依次分配,执行备份。
以下的例子是设备亲和性有效的环境中,对之前页面的列表执行相同的备份的命令的例子。(数据文件 /dev/rdsk/lv1 ~ lv4包含于表领域tbs1中的情况)

 

RUN{
    ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
    ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
    ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
    ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
    BACKUP
      FORMAT ‘tbs1_%s_%p’ 
      FILESPERSET 1
      (TABLESPACE tbs1);
}

另外在磁盘亲和性有效的环境中,指定包含在备份set中的磁盘数的最小值,可以对磁盘进行数据分散。这个设定可以通过指定RMAN的BACKUP命令中的DISKRATIO参数来设定。(在DISKRATIO参数中指定整数)

RUN{
    ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
    ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
    ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
    ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
    BACKUP 
      FORMAT ‘%d_%s_%p’
      DISKRATIO 3
      (DATABASE);
}


在DISKRATIO中指定整数n时,各个备份set中所包含的数据文件的总计块数为了不超过n分之一,对各个数执行数据文件分割。
比如DISKRATIO指定为3时,就会如此调整备份set:对一个备份set,从一个备份set从一个磁盘读入的块数,就会小于整体块数的三分之一。(被分散到4个磁盘中)。
这是考虑到磁盘的亲和性的备份,支持MPP平台固有功能。您如果想查看自己使用的平台是否支持磁盘亲和性的话,请参考各个平台的管理者手册。

Oracle8i的OPS中不会考虑磁盘亲和性,日志归档以及复制数据文件的备份时,不会考虑到Load Balancing。

RBO基于规则的优化器access paths优先级

RBO基于规则的优化器access paths优先级:

RBO Path 1: Single Row by Rowid

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan

 

注意在不违反如上优先级的前提下,若有2个优化级一样的索引可用,则RBO会选择晚建的那个索引, 解决方法是重建你想要让RBO使用的那个索引,或者使用CBO…….. 😆

 

 

在Oracle 10g以后虽然RBO (optimizer_mode=RULE)仍可用,但是不受官方的支持认可。

Histograms: An Overview

NOTE: This article was originally created for version 7.3. Conceptually the ideas presented are
the same as in later versions but implementation details may have changed or have
been enhanced.

Histograms
==========

For uniformly distributed data, the cost-based approach makes fairly accurate
guesses at the cost of executing a particular statement. However, when the
data is not uniformly distributed, the optimizer cannot accurately estimate
the selectivity of a query. Beginning in release 7.3, for columns which do not
have uniform data distribution, Oracle will allow you to store histograms
describing the data distribution of a particular column.

When to Use Histograms
———————-

Histograms are stored in the dictionary and computed by using the DBMS_STATS
command on a particular column. Therefore, there is a maintenance and space
cost for using histograms. You should only compute histograms for columns
which you know have highly-skewed data distribution.

When to Not Use Histograms
————————–

Also, be aware that histograms, as well as all optimizer statistics, are
static. If the data distribution of a column changes frequently, it is
necessary to recompute the histogram for a given column. Histograms are not
useful for columns with the following characteristics:

  o all predicates on the column use bind variables
  o the column data is uniformly distributed
  o the column is not used in WHERE clauses of queries
  o the column is unique and is used only with equality predicates

How to Use Histograms
———————

Create histograms on columns that are frequently used in WHERE clauses of
queries and have a highly-skewed data distribution. You create a histogram
by using the ANALYZE and DBMS_STATS TABLE command for later versions. For example, if you want to create a 10-bucket histogram on the SAL column of the EMP table, issue the following
statement:

    DBMS_STATS.GATHER_TABLE_STATS (NULL,’EMP’, method_opt => ‘FOR COLUMNS sal SIZE 10’);
    ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

The SIZE keyword states the maximum number of buckets for the histogram.
You would create a histogram on the SAL column if there were an unusual
number of employees with the same salary and few employees with other
salaries.

The ‘FOR’ clause can be used with either COMPUTE STATISTICS or ESTIMATE
STATISTICS.  The following clauses can be used with the ANALYZE TABLE command:

    FOR TABLE  
        collect table statistics for the table
    FOR ALL COLUMNS
        collect column statistics for all columns in the table
    FOR ALL INDEXED COLUMNMS   
        collect column statistics for all indexed columns in the table
    FOR COLUMNS
        collect column statistics for the specified columns
    FOR ALL INDEXES
        all indexes associated with the table will be analyzed
    SIZE
        specifies the maximum number of partitions (buckets) in the
        histogram.
         Default value:    75
         Range of values:  1 – 254

 For DBMS_STATS, syntax is the following:

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.

For 11.2, there is following additional parameter:

– extension : can be either a column group in the format of (column_name, Colume_name [, …]) or an expression

In 10g and 11g, the default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Choosing the Number of Buckets for a Histogram
———————————————-

The default number of buckets is 75. 10g onwards, the default value is FOR ALL COLUMNS SIZE AUTO.  This value provides an appropriate level of detail for most data distributions. However, since the number of buckets in the histogram, the sampling rate, and the data distribution all affect
the usefulness of a histogram, you may need to experiment with different
numbers of buckets to obtain the best results.

If the number of frequently occurring distinct values in a column is relatively
small, then it is useful to set the number of buckets to be greater than the
number of frequently occurring distinct values.

Viewing Histograms
——————

You can find information about existing histograms in the database through the
following data dictionary views:

    USER_TAB_HISTOGRAMS, ALL_TAB_HISTOGRAMS, and DBA_TAB_HISTOGRAMS.
    USER_PART_HISTOGRAMS, ALL_PART_HISTOGRAMS, and DBA_PART_HISTOGRAMS.
    USER_SUBPART_HISTOGRAMS, ALL_SUBPART_HISTOGRAMS, and DBA_SUBPART_HISTOGRAMS.

The number of buckets in each column’s histogram is found in these dictionary views :

 o USER_TAB_COL_STATISTICS, ALL_TAB_COL_STATISTICS,DBA_TAB_COL_STATISTICS
   (extracted from USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS)
 o USER_PART_COL_STATISTICS,ALL_PART_COL_STATISTICS, DBA_PART_COL_STATISTICS,
 o USER_SUBPART_COL_STATISTICS, ALL_SUBPART_COL_STATISTICS, DBA_SUBPART_COL_STATISTICS

These views have the same definition.

DBA_TAB_HISTOGRAMS

This view lists histograms on columns of all tables.

Column name           Represents This
———————————————————
OWNER                 Owner of table
TABLE_NAME            Table name
COLUMN_NAME           Column name
ENDPOINT_NUMBER       Endpoint number
ENDPOINT_VALUE        Normalized endpoint values for this bucket

DBA_TAB_COLUMNS

This view contains information which describes columns of all tables.
(NOTE: Views and clusters, although included in this view are not relevant
 to histograms.)

Column Name           Represents This
———————————————————-
OWNER                 Owner of table
TABLE_NAME            Table name
COLUMN_NAME           Column name
DATA_TYPE             Datatype of the column
DATA_LENGTH           Length of the column
DATA_PRECISION        Precision for NUMBER or FLOAT datatypes
DATA_SCALE            Digits to right of decimal
NULLABLE              NULL allowable?
COLUMN_ID             Sequence no. of column
DEFAULT_LENGTH        Length of default value
DATA_DEFAULT          Default value
NUM_DISTINCT          Number of distinct values for the column
LOW_VALUE             Smallest value for the column, expressed in hex
                        for the internal representation ofthe first 32
                        bytes of the value
HIGH_VALUE            Highest value for the column, expressed in hex for
                        the internal representation of the first 32 bytes
                        of the value
DENSITY               Density of the column (a measure of how distinct
                        the values are)
NUM_NULLS             The number of columns with null value
NUM_BUCKETS           The number of buckets in the histogram
LAST_ANALYZED         The date that analyze was last run on the table
SAMPLE_SIZE           The amount of data sampled

The column LAST_ANALYZED is useful in determining the last time
statistics, with or without histograms, were computed.  This is
often important to assess the reason for cost-based optimizer’s
choices of execution paths.  All tables involved in a query must be
regularly analyzed as data changes.

How to trigger ORA-00600,ORA-7445 by manual

Sometime we’d like to trigger ORA-00600/7445 internal errors for testing purpose, But it’s not easy if you don’t know a little trick like below:

declare
a exception;
pragma exception_init(a,-600);
begin
raise a;
end;

declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [],
[], [], []
ORA-06512: at line 5

or
oradebug unit_test dbke_test dde_flow_kge_ora ouch! 0 0

7445:
select spidfrom v$processp, v$sessions
where p.addr= paddr
and sid= sys_context('USERENV','SID');

kill -SEGV $PID

or

declare
a exception;
pragma exception_init(a,-7445);
begin
raise a;
end;

【Oracle数据恢复】ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error

某用户windows 2003上的数据库由于存储故障导致系统表空间system.dbf出现IO问题,当打开数据库OPEN database时报错:

 

 

ORA-01115: IO error reading block from file 15 
ORA-01110: data file … 
ORA-27091: unable to queue I/O 
ORA-27070: async read/write failed 
OSD-04006: ReadFile() failure, unable to read from file 
O/S-Error: (OS 121) The semaphore timeout period has expired.


以上ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error 这堆报错本质与Oracle数据库层面没有关系,问题的根本原因是Windows上对应磁盘驱动器下的文件无法读取出来,这可能是OS bug 也可能就是对应磁盘出现了坏道或其他物理故障,所以对于该问题有限考虑在OS层面解决文件的读取问题, 如果确实发现无法从OS层面或从备份解决,那么可以考虑特殊的恢复手段。‘

 

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

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

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

 

 

Error: OSD 4006
Text: ReadFile() failure, unable to read from file
—————————————————————————
Cause: Unexpected return from Windows NT system service ReadFile()
Action: Check OS error code and consult Windows NT documentation

This is due to a problem in Windows such that when Oracle attempted to access the data file on that device, it could not because the device timed out. This suggests that Windows has run out of asynchronous I/O buffers or there is a communications delay on the device.

There is nothing you’re going to be able to do at the database level to resolve this error, unless you move the data files to another drive. Ask the O/S system administrator to run diagnostics tools to check for possible faulty hardware and disk corruption on the disk device where the error is showing in the loader log. If the error persists, then log a call with Microsoft Support.

Oracle processes may encounter various (OS 1117) errors on a Windows 2003 Server. The text of the (OS 1117) error can be seen as follows:

C:\>net helpmsg 1117
The request could not be performed because of an I/O device error.
This error may manifest itself in different ways, depending on which Oracle process encounters the error:

Oracle RDBMS Instance Encounters (OS 1117) error

1. If an Oracle RDBMS instance encounters the error, you may see messages such as the following in the alert log for the RDBMS instance:

==========================================================================

Fri Jul 13 01:21:33 2007
Errors in file d:\oracle\db\product\admin\mydb\bdump\mydb1_lmon_4608.trc:
ORA-27091 : unable to queue I/O
ORA-27070 : async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
==========================================================================

Oracle ASM Instance Encounters (OS 1117) errors

2. If an Oracle ASM instance encounters the error, you may see similar errors in the ASM instance’s Alert log:

============================================
Fri Jul 13 01:22:10 2007
Errors in file d:\oracle\asm\product\admin\+asm\bdump\+asm1_gmon_3836.trc:
ORA-27091 : unable to queue I/O
ORA-27070 : async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
============================================

CRS Daemon (crsd.exe) encounters 1117 errors
3. If you are running in an Oracle Clusterware environment, then you may also see errors in the crsd.log and/or certain resource logs, indicating a problem accessing the OCR (Oracle Cluster Registry). An example of those errors would be:

================================================
2007-07-13 01:21:51.766: [ OCROSD][4272]utwrite:4: Problem writing the buffer phy offset 184320 and oserror 1117
2007-07-13 01:21:51.766: [ OCROSD][4352]utwrite:4: Problem writing the buffer phy offset 184320 and oserror 1117
2007-07-13 01:21:51.766: [ OCRRAW][4352]beginlog: problem 26 clearing the log metadata buffer
2007-07-13 01:21:51.766: [ OCRRAW][4352]proprdkey: Problem in begin log
2007-07-13 01:21:51.766: [ OCRRAW][4352]proprseterror: Error in accessing physical storage [26] Marking context invalid.
================================================

CSS Daemon (ocssd.exe) encounters 1117 errors

4. Also, in an Oracle Clusterware environment, the Cluster Synchronization Services daemon (ocssd.exe) may experience problems accessing the voting disk. If this occurs, you will see an error in the ocssd.log similar to the following:

============================================
[ CSSD]2007-07-13 01:22:12.501 [4052] >ERROR: Internal Error Information:
Category: 1234
Operation: scls_block_write
Location: WriteFile
Other: unable to write block(s)
Dep: 1117

[ CSSD]2007-07-13 01:22:12.501 [4052] >ERROR: clssnmvReadBlocks: read failed 1 at offset 533 of \\.\votedsk2
[ CSSD]2007-07-13 01:22:12.501 [4052] >TRACE: clssnmDiskStateChange: state from 4 to 3 disk (1/\\.\votedsk2)
[ CSSD]2007-07-13 01:22:12.501 [2200] >TRACE: clssnmDiskPMT: disk offline (1/\\.\votedsk2)
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: clssnmDiskPMT: Aborting, 1 of 2 voting disks unavailable
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: ###################################
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: clssscExit: CSSD aborting
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: ###################################
==============================================

5. When you are running in an Oracle Clusterware environment, if the ocssd process encounters an I/O error when accessing the Voting Disk, the CSS daemon will evict the node from the cluster. This is done by signalling the Oracle Fence Driver (OraFencedrv.sys) to reboot the machine. When the fence driver reboots the machine, this will be seen as a bugcheck with stop code 0x0000ffff. You will be able to see this in the System Log with a message such as:

The computer has rebooted from a bugcheck.
The bugcheck was: 0x0000ffff (0x0000000000000000, 0x0000000000000000,
0x0000000000000000, 0x0000000000000000).
A dump was saved in: C:\WINDOWS\MEMORY.DMP.

Note that the bugcheck is expected behavior when ocssd.exe (the Cluster Synchornization Services daemon) encounters an I/O error when accessing the voting disk. The node experiencing the I/O error is intentionally rebooted to avoid a split-brain and possible data corruption when access to the voting disk is lost.
CHANGES

You may encounter this error after upgrading the Microsoft Storport driver to version 5.2.3790.4021 or later.

CAUSE

Reference Microsoft KB article#932755, available at the following URL:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;932755

Per that article, one of the changes introduced in this version of the Storport driver is the following:

=========================================================
If a target returns a SCSI status of BUSY or Task Set Full, the port driver retries the command immediately. Storport retries the command an unlimited number of times. Therefore, if the busy status continues, the system could eventually experience problems.

This update configures the following behavior:

• It limits the number of retries. The default is 20.

• If the target returns a status of BUSY, the Storport driver performs a time-based pause before the Storport driver retries the command.

• If the target returns a status of Task Set Full, the Storport driver performs an I/O completion-based pause before the Storport driver retries the command.
=========================================================

Therefore, prior to upgrading the Storport driver, if a storage path had become saturated, the Storport driver would immediately continue to retry – indefinitely. This would result in slow I/O and perhaps a hang or spin scenario, but no error would be returned.

With the later version of the Storport driver, the retries are limited to 20 retries by default, with a pause between each retry. After 20 failures with a device busy status, the (OS 1117) error is returned to applications waiting on I/O. For more information on changes to the Storport driver, you must contact Microsoft.

SOLUTION

This is an I/O performance problem. You will need to increase the performance/capacity of the storage system to avoid the prolonged BUSY status. Specific solutions will vary, depending on your storage vendor, so the storage vendor may need to be contacted to assist with tuning the storage. One potential solution includes implementing multi-pathing technology to improve the throughput of the storage.

Script to show Active Distributed Transactions

该脚本可以用于显示活跃的分布式事务(Distributed Transactions from dblink),可以协助诊断dblink远程事务:

 

REM distri.sql
column origin format a13
column GTXID format a35
column LSESSION format a10
column s format a1
column waiting format a15
Select /*+ ORDERED */
    substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
    substr(g.K2GTITID_ORA,1,35) "GTXID",
    substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
    substr(decode(bitand(ksuseidl,11),
               1,'ACTIVE',
               0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
               2,'SNIPED',
               3,'SNIPED', 'KILLED'),1,1) "S",
    substr(event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where  g.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7
where  g.K2GTDXCB =t.ktcxbxba -- comment out if running in Oracle8 or later
   and g.K2GTDSES=t.ktcxbses
   and s.addr=g.K2GTDSES
   and w.sid=s.indx;

REM distri_details.sql
set headin off
select /*+ ORDERED */
'----------------------------------------'||'
Curent Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||'
'||'GTXID='||substr(g.K2GTITID_EXT,1,10) ||'
'||'Ascii GTXID='||g.K2GTITID_ORA ||'
'||'Branch= '||g.K2GTIBID ||'
Client Process ID is '|| substr(s.ksusepid,1,10)||'
running in machine : '||substr(s.ksusemnm,1,80)||'
  Local TX Id  ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,10) ||'
  Local Session SID.SERIAL ='||substr(s.indx,1,4)||'.'|| s.ksuseser ||'
  is : '||decode(bitand(ksuseidl,11),1,'ACTIVE',0,
          decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
          2,'SNIPED',3,'SNIPED', 'KILLED') ||
  ' and '|| substr(STATE,1,9)||
  ' since '|| to_char(SECONDS_IN_WAIT,'9999')||' seconds' ||'
  Wait Event is :'||'
  '||  substr(event,1,30)||' '||p1text||'='||p1
        ||','||p2text||'='||p2
        ||','||p3text||'='||p3    ||'
  Waited '||to_char(SEQ#,'99999')||' times '||'
  Server for this session:' ||decode(s.ksspatyp,1,'Dedicated Server',
                                          2,'Shared Server',3,
                                         'PSE','None') "Server"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where  g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7
where  g.K2GTDXCB =t.ktcxbxba -- comment out if running Oracle8 or later
   and  g.K2GTDSES=t.ktcxbses
   and  s.addr=g.K2GTDSES
   and  w.sid=s.indx;
set headin on
-- end script

 

Oracle内部错误ORA-07445: [ACCESS_VIOLATION] [unable_to_trans_pc][UNABLE_TO_READ]

ORA-07445:  [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ]一般是Windows平台上常见的内存不足问题引起,在64 bit 或 32bit 平台均可能发生,一般建议通过增加SGA_MAX_SIZE和SGA_TARGET(ASMM)来解决该问题,同时增加SHARED_POOL_SIZE、 JAVA_POOL_SIZE、 STREAMS_POOL_SIZE到200M以上。

也可能是由于Windows平台感染了计算机病毒引起的,建议同时也要查杀病毒。

 

ORA-07445 [ACCESS_VIOLATION] [unable_to_trans_pc] on Windows Platforms
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 06-Oct-2011***
RDBMS 9.2 or greater on Windows platforms
Symptoms
ORA-07445: exception trouvee : image memoire [ACCESS_VIOLATION] [kpudcr2c+89] [PC:0x100CD3D1] 
[ADDR:0x19EC0000] [UNABLE_TO_WRITE] [] 
ORA-07445: Message 7445 not found; product=RDBMS; facility=ORA
; arguments: [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ]
ORA-27300: OS system dependent operation:WaitForSingleObject failed with status: 0
ORA-27301: OS failure message: The operation completed successfully.
ORA-27302: failure occurred at: sssxcpttcs5
Changes
none known
Cause
This error
ORA-07445: Message 7445 not found; product=RDBMS; facility=ORA
; arguments: [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF]
[UNABLE_TO_READ]
is typical for WIN out of memory and is commonly seen when INIT.ORA parameters are set to 
that of the DBCA starter DB.
This problem appears as likely on 64 bit platforms as on 32 bit
Solution
Please refer to Note 342443.1 and apply latest minipack.
Have sufficient physical memory on Server so that you can allocate more SGA/PGA to the database.
1) Increase SGA_MAX_SIZE  and SGA_TARGET so that you can accommodate following pools.
2) Restart the instance.
3) Increase INIT.ORA memory parameters and make sure following pools are set to recommended value i.e. 200M.
a) SHARED_POOL_SIZE
b) JAVA_POOL_SIZE
c) STREAMS_POOL_SIZE
ORA-07445: [ACCESS_VIOLATION] [unable_to_trans_pc]...[UNABLE_TO_READ] on Windows
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Microsoft Windows (32-bit)
***Checked for relevance on 06-Oct-2011***
Symptoms
Session aborted on a select statement and a trace file is created with :
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
[unable_to_trans_pc] [PC:0x7C34538C] [ADDR:0xFFFFFFFF] [UNABLE_TO_READ] []
Cause
Such arguments often highlight a resource failure where memory is the most common cause of this problem.
Two hidden processes on the server were generating error messages and consuming all of the CPU : LSASS and SCCHOST
These are often due to virus.
(A later case was discovered due to a  third party software named Quest Intrust Agent. )
Solution
Problem was fixed after these two actions were performed:
1. Both processes were killed.
2. Antivirus software found some viruses and eliminated them. 
ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] [UNABLE_TO_WRITE] ORA-27301: 
OS failure message: Not enough storage ORA-27300 ORA-27302 
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2010***
Symptoms
These errors were encountered and the DB crashed.:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc]
[PC:0x7C34126B] [ADDR:0x0] [UNABLE_TO_WRITE] []
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr.
You may observe this in the trace file.:
"Current SQL information unavailable - no SGA."
Changes
This could be triggered by hardware issues or an increase in volume or by day to day operations.
Cause
Insufficient memory.
Solution
Ensure that the existing memory is functioning properly.
Verify you have enough memory available to support the configuration implemented. 
Another option is to decrease the size of the SGA.
Check your OS log for hardware errors. 
ORA-07445: [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x609C6FA2] [ADDR:0x79A0040] [UNABLE_TO_READ] 
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
Symptoms
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x609C6FA2]
[ADDR:0x79A0040] [UNABLE_TO_READ] []
occuring on a select with bind variables.
The call stack will be exact or similar to:
_clscugblmterm -> _clsc_term -> _clscterm -> _prom_terminate
AND/OR
malloc -> _malloc_unlocked -> cleanfree -> realfree 
--clscugblmterm is a RAC function, however in the case of this bug, the instances ARE NOT RAC. 
Cause
The cause of this problem has been identified and verified in an unpublished Bug 4723824.
This bug was introduced in 10.2.0.2.
Solution
Your options are:
1) Verify the patch you are on for 10.2.0.2 on windows is at least at patch least 8 as this bug is
fixed in windows 10.2.0.2 patch 8.  Please see Note:161549.1 for further information about windows patching
OR
2)  If on UNIX, determine from MOS if there is a one-off patch available on your database/OS version combination
OR
3) RECOMMENDED:  Upgrade to 11g as this bug is fixed in the 11g database version.
Ora-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] After Applying Windows 10.2.0.4 Patch Bundle 10 
Applies to:
Oracle Server - Standard Edition - Version: 10.2.0.4 to 10.2.0.4 - Release: 10.2 to 10.2
z*OBSOLETE: Microsoft Windows XP
Microsoft Windows XP
Symptoms
On Windows, using Oracle 10.2.0.4, getting the following error in the alert.log:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc]
[PC:0x7C911669] [ADDR:0x0] [UNABLE_TO_READ] []
--- Call Stack ----
_sldmGetHostName _sldmInit _ldmInit _keltnfy _kscnfy _ksucrp _opiino _opiodr _opidrv _sou2o
_opimai_real _opimai
Cause
High CPU usage
Solution
Install KB 951312 from Microsoft to overcome this problem of high CPU usage. http://support.microsoft.com
Drop User Cascade Results In ORA-03113 and ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Checked for relevance on 4-Nov-2011
Symptoms
Drop user cascade fails with ORA-3113 error:
SQL> drop user vipr cascade;
drop user vipr cascade
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
The alert.log shows:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc]
[PC:0x61FF2728] [ADDR:0x4] [UNABLE_TO_READ] []
In the trace we see:
Current SQL statement for this session:
drop java source "VIPR"."HOST"
----- Call Stack Trace -----
joxdrp opiexe opiosq0 opiosq opiodr rpidrus rpidru rpiswu2 rpidrv rpisplu rpispl kzdukl kzudrp
opiexe opiosq0 kpooprx kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o
It is failing when trying to drop java source.
Cause
JVM is in REMOVING status.
COMP_NAME VERSION STATUS
Oracle Enterprise Manager 10.2.0.3.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
Oracle Database Catalog Views 10.2.0.3.0 VALID
Oracle Database Packages and Types 10.2.0.3.0 VALID
JServer JAVA Virtual Machine 10.2.0.3.0 REMOVING
Solution
Follow Note 276554.1 How to Reload the JVM in 10.1.0.X and 10.2.0.X to remove or reload JVM.

Network Interface No Longer Operational?

Solaris平台上的Oracle数据库,Alert日志偶尔会出现”Network Interface No Longer Operational”的相关记录:

ospid 11223: network interface with IP address 192.4.1.22 no longer operational
requested interface 192.4.1.22 ioctl get mtu. Check output from ifconfig command

该错误一般是由Solaris操作系统Bug 6546482引起的,该错误一般可以忽略。

Know about Oracle High Water Mark

there’s no HWM for datafiles, it’s just a virtual term to describe the last block containing data in the data file, which is the minimum size allowed for sizing down the data file.

This article intends to provide an SQL script to find tables which are fragmented (i.e Data is much lower then High Water Mark),so that we can target those segments (tables) for recreation.

Software Requirements/Prerequisites

Execution Environment    :SQL, SQL*Plus

Access Privileges              :Requires dba privileges as script is to be run as   the owner SYS or SYSTEM

Prerequisites                     :Do an Analyze  with compute statistics on all tables present in the Users schema

i.e Analyze table <table_name> compute statistics

Usage                                :Sqlplus username/<password>

SQL> @fragment.sql

Advisory                            Will not work on Compressed tables, may return negative numbers.

 

Configuring the Script

1.User needs dba privileges to access dba_tables  .

2.Statistics needs to be collected on all the tables using  compute statistics

option for the input schema before fragment.sql is   run.

Running the Script

Step 1:- Copy this script to a file named fragment.sql.

Step 2:- Connect as user SYS or SYSTEM.

Step 3:- Run Analyze on all the tables present in the schema  for which you want to find the fragmented table.

SQL> Analyze table <table_name> compute statistics ;

Step 4:- Execute the fragment.sql script.Note the script will prompt for Schema name.

SQL> @fragment.sql

 

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

REM This is an example SQL*Plus Script to find tables fragmentated below high water mark
set heading off verify off echo off
Spool fragment.sql
REM The below queries gives information about the size of the table with respect to the High water Mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192.  Substitue your DB blocksize.
REM SELECT BLOCKS*8192/1024/1024 FROM  DBA_TABLES WHERE  TABLE_NAME='<TABLE_NAME>'  and    owner='<owner>'   ;
REM The below queries gives the actual size in MB used by the table in terms of data .
REM SELECT NUM_ROWS*AVG_ROW_LEN/1024/1024 FROM  DBA_TABLES WHERE TABLE_NAME='<TABLE_NAME>' and  owner='<owner'
REM
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragementation below high water mark prompt Enter name(s) of schema for which you want to find
REM fragemented object.
PROMPT Please enter the schema name
SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;
Spool off

Goal

This article explains, with examples, how to view the high water mark and when the high water mark is reset. The queries given in this article applies when the segment , whose high water mark has to be determined ,  is in one datafile and is not spawned across multiple datafiles .

Solution

The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points to becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

The high-water mark is the level at which blocks have never been formatted to receive data.

When a table is created in a tablespace, some initial number of blocks / extents are allocated to the table. Later, as the number of rows inserted increases, extents are allocated accordingly.

To find out how many blocks / extents are allocated to the table, query DBA_SEGMENTS for ‘blocks’ and ‘extents’.

For example:

SQL>create table test1 (num number) tablespace tbsp1;

Table created

SQL>select blocks, extents from dba_segments where segment_name=’TEST1′

BLOCKS EXTENTS
——– ————-
8 1

Now, to view the high water mark, perform an:

SQL> analyze table test1 compute statistics;

Querying dba_tables for ‘Blocks’ and ‘Empty_blocks’ should give the high water mark.

Blocks — > Number blocks that has been formatted to recieve data
Empty_blocks —> Among the allocated blocks, the blocks that were never used

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name=’TEST1′

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
0 7 0

If you insert some rows, then the output of the above query returns:

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
1 6 8

blocks+Empty_blocks=1+6=7 (but not 8) because 1 block is for segment header.

Insert some more rows into table ‘TEST1’ to increase the number of extents allocated so that
DBA_SEGMENTS will show :

BLOCKS EXTENTS
————– —————
32 4

And dba_tables (after analyze table) shows:

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
28 3 14338

Deleting the records doesn’t lower the high water mark. Therefore, deleting the records doesn’t raise the ‘Empty_blocks’. After deleting the records, if you query dba_segments or dba_tables, there would be no change. Even an ‘Alter table test1 deallocate unused;’ will not bring the high water mark down.

To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) “used blocks” from TEST1;

This works fine if only one file is used for the segment. If more files, we need to include the file number in some way, for instance:

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||’-‘||dbms_rowid.rowid_relative_fno (rowid)) “used blocks” from TEST1

USED_BLOCKS
———————–
22

From this we can conclude that for table ‘TEST1’, 32 blocks are allocated out of which 28 blocks are formatted to receive data but only 22 blocks contain the actual data.

The high water mark can be reset with a truncate table or if the table is moved to another tablespace.  Additionally, in 10g the following option to shrink a segment was introduced to reset the high water mark. eg. ALTER TABLE <tablename> SHRINK SPACE;

When the table is created with CTAS from another table, the high water mark of the latter table is not reflected in the new table. If the tablespace is moved back to the same tablespace, the high water mark is reset. In this case, query on obj#, dataobj# of obj$. Obj# remains the same but dataobj# changes.

 

PURPOSE
This article describes how to find out how many blocks are really being
used within a table ie. are not empty. Please note that this article does
not cover what to do when chaining is taking place.

SCOPE & APPLICATION
For DBA’s needing to determine how many blocks within a table are
empty blocks.

How many blocks contain data (are not empty)
——————————————–
Each row in the table has pseudocolumn called ROWID.
This pseudo contains information about physical location
of the row in format
block_number.row.file

If the table is stored in a tablespace which has one
datafile, all we have to do is to get DISTINCT
number of block_number from ROWID column of this table.

But if the table is stored in a tablespace with more than one
datafile then you can have the same block_number but in
different datafiles so we have to get DISTINCT number of
block_number+file from ROWID.

The SELECT statements which give us the number of “really used”
blocks is below. They are different for ORACLE 7 and ORACLE 8
because of different structure of ROWID column in these versions.

For ORACLE 7:

SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
SUBSTR(rowid,1,8)) “Used”
FROM schema.table;

For ORACLE 8+:

SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
FROM schema.table;
or

SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) “Used”
FROM schema.table;

You could ask why the above information could not be determined
by using the ANALYZE TABLE command. The ANALYZE TABLE command only
identifies the number of ‘ever’ used blocks or the high water mark
for the table.

What is the High Water Mark?
—————————-
All Oracle segments have an upper boundary containing the data within
the segment. This upper boundary is called the “high water mark” or HWM.
The high water mark is an indicator that marks blocks that are allocated
to a segment, but are not used yet. This high water mark typically bumps
up at 5 data blocks at a time. It is reset to “zero” (position to the start
of the segment) when a TRUNCATE command is issued. So you can have empty
blocks below the high water mark, but that means that the block has been
used (and is probably empty caused by deletes). Oracle does not move the
HWM, nor does it *shrink* tables, as a result of deletes. This is also
true of Oracle8. Full table scans typically read up to the high water mark.

Data files do not have a high water mark; only segments do have them.

How to determine the high water mark
————————————
To view the high water mark of a particular table::

ANALYZE TABLEESTIMATE/COMPUTE STATISTICS;

This will update the table statistics. After generating the statistics,
to determine the high water mark:

SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name =;

BLOCKS represents the number of blocks ‘ever’ used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the ‘HIGH WATER MARK’
.
Deleting records doesn’t lower the high water mark. Therefore, deleting
records doesn’t raise the EMPTY_BLOCKS figure.

Let us take the following example based on table BIG_EMP1 which
has 28672 rows (Oracle 8.0.6):

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name=’BIG_EMP1′;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
—————————– —————– ———- ——-
BIG_EMP1 TABLE 1024 2
1 row selected.

SQL> connect scott/tiger

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 28672 700 323
1 row selected.

Note: BLOCKS + EMPTY_BLOCKS (700+323=1023) is one block less than
DBA_SEGMENTS.BLOCKS. This is because one block is reserved for the
segment header. DBA_SEGMENTS.BLOCKS holds the total number of blocks
allocated to the table. USER_TABLES.BLOCKS holds the total number of
blocks allocated for data.

SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
4> FROM big_emp1;
Used
———-
700
1 row selected.

SQL> DELETE from big_emp1;
28672 rows processed.

SQL> commit;
Statement processed.

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 0 700 323
1 row selected.

SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
4> FROM big_emp1;
Used
———-
0
1 row selected.

SQL> TRUNCATE TABLE big_emp1;
Statement processed.

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 0 0 511
1 row selected.

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name=’BIG_EMP1′;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
—————————– —————– ———- ——-
BIG_EMP1 TABLE 512 1
1 row selected.

Note: TRUNCATE has also deallocated the space from the deleted rows.
To retain the space from the deleted rows allocated to the table use:
TRUNCATE TABLE big_emp1 REUSE STORAGE

PGA Usage Larger than PGA_AGGREGATE_TARGET setting?

pga_aggregate_target is a target, as opposed to a hard limit – so it isn’t unusual to go above that.
13G above that, now that’s unusual though! There IS an enhancement request in,
to make a hard-limit setting, but that does not currently exist.
There is a known bug in 10203 with certain statements burning up memory – bug 5947623 – however,
the 10203/aix version of this patch is 64-bit, and the SR header says you are on 32-bit, so that isn’t
an option….and 10203 is old enough that I can’t get a new version of the patch made.

As I was unable to see any errors (e.g., ORA-4030) thre does not seem to be any problem with the operation of the database.

PGA_AGGREGATE_TARGET does not set a hard limit on pga usage. It is only a target value used to dynamically size the process work areas. It also does not affect other areas of the pga that are allowed to grow beyond this limit.

There are certain areas of pga that cannot be controlled by initialization parameters. Such areas include pl/sql memory collections such as pl/sql tables and varrays.

Depending on the programming code and amount of data being handled these areas can grow very large (up to 20G internal limit on 10) and can consume large amounts of memory. This memory growth can be controlled by good programming practices. As an example, use LIMIT clause with BULK COLLECT.

Additionally, programming mistakes can also lead to excessive memory usage.

You can take steps to control the size of a process. However, from within the database framework you cannot place a hard limit on the size of a process by setting any initialization parameters or database configuration.

You can limit the size of a process from the OS side by setting kernel limits or user shell limits but this leads to the ORA-4030 and will cause transaction rollback.

As noted in bug 7279150, “… this is not a hard limit and that we will exceed it when it is undersized and the workload increases rapidly, such as when they start the workload for their testing or when they spawn a new set of sessions from their application servers.”

As the DBA you need to get confirmation from your operating system administrator that the amount of memory reported as being in use by a process includes or does not include shared memory. If shared memory is included in the value displayed by the operating system utility, then the shared pool size must be deducted from that value to know how much private memory the process is actually using.

See note 174555.1 “UNIX Determining the Size of an Oracle Process”.

If an RDBMS user process is using more private memory than expected, then the DBA has three options:

– Do nothing
– Monitor the RDBMS iuser session to find out what SQL statements are being performed or were being performed by that RDBMS session. Using the SQL*Trace functionality of the database would normally be done if information from the end user cannot be obtained directly as to what they were doing since the memory usage was higher than expected or what they are doing right now.
– Kill that RDBMS user session.

沪ICP备14014813号

沪公网安备 31010802001379号