数据库恢复服务微信: dbservice1234

oracleasm failed to createdisk on raw device

2007/04/25 by admin Leave a Comment

在测试ASMlib的时候出现了oracleasm无法创建磁盘的错误，具体如下:

[root@vrh1 yum.repos.d]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting [ENTER] without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:
                                                           [  OK  ]

/* configure没有出现错误 */

[root@vrh1 ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/raw/raw3
Marking disk "VOL1" as an ASM disk:                        [FAILED]

/* 之后发现在块设备上创建磁盘就没有问题，想想也是如果用了ASMLIB就没必要再建成裸设备了 */

[root@vrh1 ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb6
Marking disk "VOL1" as an ASM disk:                        [  OK  ]

记以录之！

Script:when transaction will finish rollback

2007/04/19 by admin Leave a Comment

-------------------------------------------------------------------------------
--
-- Script:	rolling_back.sql
-- Purpose:	to predict when transactions will finish rolling back
-- For:		9.0+
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

prompt
@restore_sqlplus_settings

Filed Under: Oracle, Oracle Internal Research内部原理研究, Oracle脚本script Tagged With: Oracle脚本script, rollback, transaction, x$ktuxe

VIEW: X$KTUXE – Transaction Entry (table)

2007/04/19 by admin 1 Comment

View:   X$KTUXE
         [K]ernel [T]ransaction [U]ndo
           Transa[x]tion [E]ntry (table)

  This view is very useful as it gives an indication of the state
  of the transaction tables in the rollback segment headers.
  The information here can be used to see the state of transactions
  requiring transaction recovery do not show in <View:V$TRANSACTION>
  <Event:10013> may be useful to trace transaction recovery.

 Column          Type               Description
 --------        ----               --------
 ADDR            RAW(4|8)           address of this row/entry in the array or SGA
 INDX            NUMBER             index number of this row in the fixed table array
 INST_ID         NUMBER       8.x   oracle instance number

Transaction ID
 KTUXEUSN        NUMBER             undo seg number
       KUSNOLTP UB2MAXVAL           is no-undo xac
 KTUXESLT        NUMBER             slot number
       KSLTINV  UB2MAXVAL           not a valid slot num
 KTUXESQN        NUMBER             wrap number, is savept # at start if no-undo xac
                                     position within transaction

 KTUXERDBF       NUMBER             relative File
 KTUXERDBB       NUMBER             relative Block
 KTUXESCNB       NUMBER             SCN base for prepare/commit
 KTUXESCNW       NUMBER             SCN wrap for prepare/commit
 KTUXESTA        VARCHAR2(16)       Transaction Status
 KTUXECFL        VARCHAR2(24)       Transaction flags
 KTUXEUEL        NUMBER             Used for extent of tx and link to commit list

Distributed tx: collecting dba and undo bk to start retrieving collecting info rec
 KTUXEDDBF       NUMBER             relative file
 KTUXEDDBB       NUMBER             relative dba

Parent transaction id
 KTUXEPUSN       NUMBER       8.x   undo seg number
       KUSNOLTP UB2MAXVAL           is no-undo xac
 KTUXEPSLT       NUMBER       8.x   slot number
       KSLTINV  UB2MAXVAL           not a valid slot num
 KTUXEPSQN       NUMBER       8.x   wrap number, is savept # at start if no-undo xac
                                      position within transaction

 KTUXESIZ        NUMBER       8.1  number of undo blocks used by the transaction

Notes:

To see any DEAD transactions for deferred transaction recovery
  after startup:

      select * from x$ktuxe where ktuxecfl='DEAD';

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: internal, transaction recovery, x$ktuxe

EVENT: 10500 "turn on traces for SMON"

2007/04/19 by admin Leave a Comment

Error:  ORA 10500
Text:   turn on traces for SMON
-------------------------------------------------------------------------------
Cause:
Action:
        Level:  <=5  trace instance recovery
        > 5  trace posting of SMON

To set event 10500:

For the instance:
a.  Shutdown database
b.  Edit the initialisation parameter file and add:
    event="10500 trace name context forever, level <value>"
c.  restart the database

For the SMON session:
Post the SMON process using oradbx (Oracle 7) or oradebug (Oracle 8).
For oradebug from server manager issue:

oradebug setospid <OS PID>
oradebug event 10500 trace name context forever, level <value>

For further information about oradebug Note 29786.1
                              oradbx   Note 28863.1

<value> is 'Level' as per above

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: debug event, internal trace, oradebug

Skip Scanning of Indexes Advantages

2007/01/24 by admin Leave a Comment

What is skip scanning?

In prior releases, a composite index would be used to retrieve the records, if the index prefix (leading) column was included in the predicate of the statement. With Oracle9i, the optimizer can use a composite index even if the prefix column value is not known. The optimizer uses an algorithm called skip scanning to retrieve ROWIDs for values that do not use the prefix column.

How does a skip scan work?

During a skip scan, the B*-tree is probed for each distinct value in the prefix column. Under each prefix column value, the normal search algorithm takes over. The result is a series of searches through subsets of the index, each of which appears to result from a query using a specific value of the prefix column. However, with the skip scan, the value of the prefix column in each subset is obtained from the initial index probe rather than from the command predicate.

The optimizer uses the statistics to determine whether a skip scan retrieval would be more efficient than a full table scan, or other possible retrieval paths, when parsing SQL statements.

Suppose a composite index exists on the two columns, LANGUAGE andTERRITORY, with LANGUAGE as the prefix column. The data values stored in the underlying table result in the combinations of values in the table, as shown below. Each combination can occur multiple times in the table and the resulting index.
In previous releases without the skip scan algorithm, a query on a value in theTERRITORY column was forced to execute a full table scan or a fast full index scan. If the query were more common, then you might have needed to add a new index on the TERRITORY column alone. This new index, could negatively impact the performance of DML on the table.
The skip scan solution provides an improvement without the need for the second index. While not as fast as a direct index look up, the skip scan algorithm is faster than a full table scan in cases where the number of distinct values in the prefix column is relatively low.

Note: The skip scan feature cannot be turned on and off by the user. This feature is always on and is transparent to the users.

What are the advantages of skip scanning?

Skip scans reduce the need to add an index to support occasional queries that do not reference the prefix column of an existing index. This can be useful when high levels of DML activity is expected to degrade because of the existence of too many indexes used to support infrequent queries.
The algorithm is also valuable in the cases where there are no clear advantages as to which column to use as the prefix column in a composite index.
The prefix column should be the most discriminating, but also the most frequently referenced in queries. Sometimes, these two requirements are met by two different columns in a composite index, forcing a compromise or the use of multiple indexes. Skip scanning helps to overcome these problems.

Filed Under: Oracle, Oracle SQL性能调优 Tagged With: Oracle索引index, skip scan

Know more about DML

2006/08/04 by admin Leave a Comment

The change vectors as constructed by the transaction layer are used by the cache layer to apply the physical block changes.

Redo log ordering and recovery as well as read consistency are the fundamental purposes of SCNs.

The change vector:
Change vectors are built in the PGA process of the process modifying a block, have the DBA and SCN/SEQ of the block before the change, and only ever perform the change for one block.

The system commit number:
The SCN is a 6-byte structure consisting of the SCN base and SCN wrap.
Is a 6-byte structure consisting of the SCN base and SCN wrap, 4 bytes for the SCN base and 2 bytes for the SCN wrap

The SCN is used within the data block to reflect the committed version of the block.
There is only one global SCN generator for each database.
A large transaction is defined as a transaction that is participating in a parallel DML (PDML) operation or a distributed transaction.
One of the first checks for rollback segment allocation is whether the transaction is participating in a PDML or distributed transaction. These are used to determine the largest rollback segment to be allocated.

steps below to illustrate the order necessary to begin a transaction.

1.bind to the available rollback segment
2.allocate a slot in the transaction table
3.allocate an undo block to the rollback segment

When a free slot cannot be allocated in the transaction table, exclusive access is acquired in the rollback segment header.
Only when a free slot can be allocated in the transaction table do you gain exclusive access to the RBS header.

The system rollback segment will be used only for data for the system tablespace and data that is being created from recursive SQL calls generated by user data.

The search for the allocation of an undo block to a rollback segment will occur in:
Searching for an undo block to be allocated will begin in the current extent if the extent boundary has not been reached, in the next extent provided there are no uncommitted transactions, or in a newly allocated extent if the next extent has uncommitted data.

Rollback segments are shrunk either explicitly through the ’alter rollback segment … shrink to …’ command or implicitly by the SMON process. Implicit shrinkage can be disabled by:
PCTINCREASE has no affect on rollback segments. All extents will be sized by the setting of the next_extent storage attribute.
Setting the event 10512 will disable the implicit shrink operation performed by SMON.
Setting the initialization parameter ‘rbs_disable_shk’. There is no such parameter. Setting the event 10512 will disable the implicit shrinkage operation performed by SMON.

The Oracle server begins shrinking a rollback segment starting from the current extent +2

Rollback segment shrinkage will occur while optimal or minextents is not reached, there are at least two remaining extents, the extent is not busy, and the extent is not extent #0.

An extent is reused if all its blocks are inactive. RBS undo blocks are reused on a per extent basis. An undo block becomes inactive when the active transaction in the undo block commits.

correct order of Interested Transaction List (ITL) allocation.

1. Find an Unused ITL
2. FInd ITL of oldest commited TX
3. DO a partial block cleanout that stops when an ITL becomes available
4. Extend ITL area

The variable portion of the transaction layer is dictated by the MAXTRANS parameter.
The INITRANS parameter dictates the number of ITL’s and hence the variable portion of the Transaction Layer.

An ITL must be acquired by any transaction wishing to modify a block. If an ITL is unavailable and there is no space left in the block, a transaction will wait.

A transaction that currently has an ITL within a block cannot update any row in the block if the required row is locked by a previous transaction. That transaction will wait on the TX enqueue.When attempting to update a row already held by a previous transaction, a transaction will wait on the TX enqueue in exclusive mode.

The free space credit field of the transaction layer maintains the space as freed for a given transaction, and that is returned on commit of the transaction.

When a transaction is unable to allocate an ITL within a data block for which it needs to modify a block, the transaction will:
That transaction will wait on the TX enqueue in share mode until an ITL becomes available within the block where the modification is required.

The Oracle server maintains the row header flag and lock byte (total 2 bytes) for the deleted row. It is necessary to keep these first two bytes to maintain the row lock. This is referred to as stub space.

The correct statistic Rollback changes – undo records applied is incremented for each link in the undo record chain.

The Data Block Address (DBA) in the Transaction Table slot indicates:
The beginning of the undo chain for the transaction Correct! The DBA in the Transaction Table slot is the starting point for rolling back a transaction

the correct sequence of events at the COMMIT of a transaction.

1. FIND an scn value
2. update the transaction table slot with the scn
3. create a commit record in the redo log buffer
4. flush the redo log buffer to disk
5. release locks held on the rows and tables

The application of the undo to the data block is performed by the
The data layer ,and the transaction layer do not have the right to perform physical block-level changes.

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: block, dml, ITL, oracle kernel, redo, scn, undo

EVENT:10211 check index block integrity

2006/07/22 by admin Leave a Comment

Event:10211                  See <Note:32969.1> for similar events / parameters
~~~~~~~~~~~                    

Version/Use:
  7.0 - 8.1.7  Check data block integrity after each modification
  9.0+         This event is no longer valid. Use <Parameter:DB_BLOCK_CHECKING>
                instead.

7.0 - 8.1.7 "Check data block integrity after each modification"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  NOTE: Events should NEVER be set by customers unless advised to do so by
        Oracle Support Services. Read <Note:75713.1> before setting any event.

Summary Syntax:
  This event should be set at instance level:
    EVENT="10211 trace name context forever, level 10"

Levels:
  This event is either ON (level 1 or higher) or OFF (not set)

  Note that in 8.1.6 onwards you should use the <Parameter:DB_BLOCK_CHECKING>
  instead of this event.

Description/Steps:
  Event:10211 enables index block integrity checking.
  The causes the index layer to perform a number of checks on the contents
  of a index block. If the block is found to be corrupt in some way it is
  marked as SOFTWARE CORRUPT.

  NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT checked
      when accessed for read.                                   

  Take care with this as if there are blocks in the database which
  are being accessed happily but have a minor corruption this will
  mark the block as corrupt.

  Once a block in an index is marked SOFTWARE CORRUPT it is best to
  rebuild the entire index.

*** WARNING:
        Releases with <Bug:792610> are best to keep this event disabled
        as an index corruption can lead to rollback segment corruption
        which is written to the redo stream.

  See <Note:32969.1> for more detail of various block checking features.

Example Output / Interpreting Output:
  This event will cause an ORA-600 error to be signalled if a corruption
  is detected after a data block has been modified. The exact content
  of the trace file depends on the ORA-600 and the action at the time
  of the corruption being noticed. Refer to the relevant ORA-600 article
  for the error which is signalled.

Related:
  Database block checking features                          <Note:32969.1>
  Table and Cluster block equivalents          <Event:10210> <Event:10212>

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: block check, event

EVENT: 10060 dump predicates in optimizer (kko)

2006/07/22 by admin Leave a Comment

Event:10060
Text:   dump predicates in optimizer (kko)
-------------------------------------------------------------------------------

Explanation:
        This is a special EVENT code.
        It should *NOT* be used unless explicitly requested by ST support.

        This event can be useful in conjunction with an explain plan
        to confirm which operations occur where in the execution plan.
        It dumps the predicates into a special table.

Usage:
        - Available in 7.1.3 onwards when using CBO
        - NOTE: This does NOT do anything under RULE

        - After 7.1.3 you must create a special table called
          "kkoipt_table" before you set the event. This table should be
          in the schema of the person running under event 10060.
    
            create table kkoipt_table (
                    c1 integer, c2 varchar2(80)
            );            

        - To enable tracing:
            ALTER session set events '10060 trace name context forever';

        - Run the statement to be traced

        - Set formatting:
          set pages 999
          column c1 format 990
          column c2 format a75

        - select * from KKOIPT_TABLE;

        - Best used in conjunction with <Event:10053> trace and an
          execution plan.


KKOIPT_TABLE.C2 output information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
fptconst                      - Folding constants
fptrnum                       - Remove rownum predicates
fptwhr                        - Remove all where predicates except remaining
                                rownum predicates
frofkks (rowid lookup)        - Rowid Lookup
frofkks[i] (and-equal lookup) - Start Key (And-Equal)
frofkke[i] (and-equal lookup) - End Key   (And-Equal)
froiand                       - Index only predicate
frofkksm[i] (sort-merge)      - Sort-Merg Key
frosand (sort-merge)          - Sort-Merge Predicates
frojand (sort-merge)          - Join Predicates
frofkks[i] (index start key)  - Index Start Key
frofkke[i] (index stop key)   - Index End Key
frofand (hash part)           - Table Predicate (Hash)
froiand (index only filter)   - Index Only Predicate
frofand                       - Table Predicate
froutand                      - Outer Join Predicates

Example:
~~~~~~~
select a.* from memp a, memp b
where a.sal > 5050 and rownum < 4 and 1=1
  and a.empno (+) = b.empno;

Output: (from kkoipt_table)

         1 fptrnum
         2  4>ROWNUM
         3 Table:
         4 MEMP
         5 frofand
         6  "A"."SAL">5050
         7 Table:
         8 MEMP
         9 frofand
        10  "A"."EMPNO"="B"."EMPNO"

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: dump, event, optimizer

EVENT:10218 dump uba of applied undo

2006/07/22 by admin Leave a Comment

@23-JAN-01, rpowell.uk, Distribution justification: "Should not be used by
@ customers unless told to by support so making noncustomer-viewable." - dbither.us

Event: 10218
Text:  dump uba of applied undo
-------------------------------------------------------------------------------

Note:
   Events should NEVER be set by customers unless advised to do so by
   Oracle Support Services.  See <Note:75713.1> for more details.

Description:
   This event simply shows the undo block addresses for the undo
   applied during recovery.  This can be useful to see the progress of
   the rolling out of transactions, and to help identify where a rollback
   has gotten stuck.

Example output:
         uba: 80000ca.0d.12
         uba: 80000ca.0d.11
         uba: 80000ca.0d.10
         uba: 80000ca.0d.0f
         uba: 80000ca.0d.0e
         uba: 80000ca.0d.0d
         uba: 80000ca.0d.0c
         uba: 80000ca.0d.0b

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: event, undo

EVENT:10226 trace CR applications of undo for data

2006/07/22 by admin Leave a Comment

Error:  ORA 10226
Text:   trace CR applications of undo for data operations
-------------------------------------------------------------------------------
Explanation:
        This is NOT an error but is a special EVENT code. It can be used
        to signal Oracle to perform SQL_TRACE actions.

	This event causes application of UNDO for producing CR copies of
	blocks. It can produce a lot of output (in the form of REDO
	application dump to rewind the block)

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: consistent read, event, undo