The Life of an Oracle Shadow Process

Here is a breakdown of the life of an Oracle shadow process and where it might be waiting as it
lives. This comes from Oracle Doc ID 61998.1 and shows what’s happening in less than one
second within Oracle.

State     Notes
IDLE    Waits for “SQL*Net message from client” (waiting for the user).Receives the SQL*Net packet requesting “parse/execute” of a statement.

ON CPU  Decodes the SQL*Net packet.

WAITING Waits for “latch free” to obtain the “library cache” latch. Gets the library cache latch.

ON CPU  Scans the shared pool for the SQL statement, finds match, frees
latch, sets up links to the shared cursor, etc., and begins to execute.

WAITING  Waits for “db file sequential read”; we need a block not in the cache
(wait for I/O).

ON CPU  Block read from disk complete. Execution continues. Construct SQL*Net packet with first row of data to send to client and sends.

WAITING  Waits on “SQL*Net message from client” for acknowledgment packet received.
IDLE           Waits for next SQL*Net message from client


Thank  Richard Niemeic for good information from oracle book "oracle performance tuning tip and technique"

Hope this help you:D

Chatchai Komrangded

Your Data Center. Optimized.

The new innovation to optimize your data center reduce cost, managing, complexity overhead and more.

See this link

http://www.youtube.com/watch?v=Hey-WF4GFVs

ORA-29702: error occurred in Cluster Group Service operation Tip

When you using Oracle RAC, you have grid , db components on each node, someday you want to remove it  (may cause of app requirement, buget and so on), you may disable grid service by add comment to /etc/inttab or un-install, the oracle startup and running fine in single environment but sometime you'll see ORA-29702: error occurred in Cluster Group Service operation that cause of
LMON process still active and It is assume it  to be RAC Instance

This situation if you ensure no need rac anymore you can delink it from oracle libary by

1. Shutdown the database completely.

2. Relink with RAC OFF :

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle

3. Startup the database.

Hope this help you:D

Chatchai Komrangded

RFS-process on physical standby database fails with Ora-00600:[Kcrrrfswda.9]

In Oracle Database Guard Environment, sometime you may see this error message at Standby Site on below:

Physical standby database

RFS-process fails on physical standby database with fallowing errors:

Errors in file d:\oracle\app\administrator\diag\rdbms\pihist\pihist3\trace\pihist3_rfs_5364.trc:
ORA-00272: error writing archive log L:\ARCHIVE\PIHIST\ARC00089_0731084629.001
ORA-00600: internal error code, arguments: [kcrrrfswda.9], [4], [368], [], [], [], [], []
RFS[29]: Possible network disconnect with primary database

On the primary database there may be fallowing errors reported:Errors in file d:\oracle\app\administrator\diag\rdbms\pdonds\pdonds\trace\pdonds_arc7_3572.trc:

ORA-12582: TNS:invalid operation
FAL[server, ARC7]: FAL archive failed, see trace file.
Errors in file d:\oracle\app\administrator\diag\rdbms\pdonds\pdonds\trace\pdonds_arc7_3572.trc:
ORA-16055: FAL request rejected

This errors are network related, eg firewall is enabled between primary/standby database.

This ORA-600 is a network related issue:

1. A firewall-feature is most likely the culprit for this ORA-600. Please ensure there is no feature enabled that is modifying TCP packets like 'fixup' or 'inspect' on Cisco Firewalls for example. Those features modify the TCP-packets containing redo data resulting into the RFS-Process on the standby database refusing those packets. This can then end up in the ORA-600.

Please make sure the following firewall features are disabled:

- SQLNet fixup protocol
- Deep Packet Inspection (DPI)
- SQLNet packet inspection
- SQL Fixup
- SQL ALG (Juniper firewall)

2. There is also a known issue reported in Bug 8605213: ORA-00600: [KCRRRFSWDA.9]:
The ORA-600 didn't happen anymore after setting  dead connection detection in Oracle Net (SQLNET.EXPIRE_TIME) to a value below the firewall timeout threshold.

3. There are different non-public bugs, most of them are closed as not reproducable, or customer was not willing to disable firewall-feature to verify if the error still happens afterwards.

Refererence

RFS-process on physical standby database fails with Ora-00600:[Kcrrrfswda.9], [4], [368], [], [], [], [], [] [ID 1269749.1]

Hope this help you:D

Chatchai Komrangded

Private Database Cloud

Oracle Database Cloud

Private Database Clouds are a proven model for the delivery of database services. Private Database Clouds enable customers to consolidate servers, storage, and database workloads onto a shared hardware and software infrastructure. By providing on-demand access to database services in a self-service, elastically scalable and metered manner, Private Database Clouds offer compelling advantages in cost, quality of service, and agility. Accelerate and optimize deployment by leveraging Oracle Database, RAC, Exadata, and Servers & Storage.

More information, see this link

http://www.oracle.com/technetwork/database/database-cloud/index.html?ssSourceSiteId=ocomen

Hope this help you:D

Chatchai Komrangded

Chatchai_K: Webcast: Three Compelling Reasons to Upgrade to Or...

Chatchai_K: Webcast: Three Compelling Reasons to Upgrade to Or...: Date:  Tuesday, September 18, 2012  Time:  10 a.m. PT/1 p.m. ET  If you or your organization is still working with Oracle Database 10 g  o...

Webcast: Three Compelling Reasons to Upgrade to Oracle Database 11g

Date: Tuesday, September 18, 2012 
Time: 10 a.m. PT/1 p.m. ET 

If you or your organization is still working with Oracle Database 10g or an even older version, now is the time to upgrade. Oracle Database 11g offers a wide variety of advantages to enhance your operation. Join us for this live Webcast and learn about what you’re missing: the business, operational, and technical benefits. 

With Oracle Database 11g, you can:

• Upgrade with zero downtime
• Improve application performance and database security
• Reduce the amount of storage required
• Save time and money

If you interest you can join this webcast as link below:

https://event.on24.com/eventRegistration/EventLobbyServlet?target=registration.jsp&eventid=502521&sessionid=1&key=8A3AC19FDC380F9E5E55808EA473A2F8&partnerref=DB11gupgrade_O.com_91812&sourcepage=register

Hope this help you:D

Chatchai Komrangded

Warning "aiowait timed out x times"

When the above message is produced in alert.log, the database may also

appear to be hanging.

The message indicates that Oracle has encountered problems while trying

to perform asynchronous I/O.

When the Oracle instance is configured to run with asynchronous I/O

(disk_asynch_io = true), it will use the aiowait system call to obtain the

completion status of such I/Os.

When you using truss (On Solaris you will see)

28463/1:        open("/etc/passwd", O_RDONLY)                   = 3

28463/1:        kaio(5, 0xFFFFFFE8, 0xFFFFFFFF, 0x00000000, 0x00000000, 0x000000

00, 0xEF7F4298) = 0

28463/1:        kaio(AIOREAD, 3, 0x00020FF0, 1024, 0, 0xEFFFF9FC) Err#48

28463/1:        lwp_create(0xEFFFF628, 0, 0xEF781F44)           = 3

28463/3:        lwp_create(0x00000000, 0, 0x00000000)           = -277341360

28463/3:        lwp_self()                                      = 3

28463/1:        lwp_create(0xEFFFF628, 0, 0xEF778F44)           = 4

28463/4:        lwp_create(0x00000000, 0, 0x00000000)           = -277378224

28463/4:        lwp_self()                                      = 4

28463/1:        lwp_create(0xEFFFF628, 0, 0xEF6BAF44)           = 5

28463/5:        lwp_create(0x00000000, 0, 0x00000000)           = -278156464

28463/5:        lwp_self()                                      = 5

28463/1:        lwp_create(0xEFFFF628, 0, 0xEF6B1F44)           = 6

28463/6:        lwp_create(0x00000000, 0, 0x00000000)           = -278193328

28463/6:        lwp_self()                                      = 6          

28463/3:        pread(3, " r o o t : x : 0 : 1 : 0".., 1024, 0) = 1024

28463/4:        pread(3, " r y h a : x : 2 1 6 : 1".., 1024, 1024) = 1024

28463/5:        pread(3, " s 1 / j c a h i l l : /".., 1024, 2048) = 1024

28463/6:        pread(3, " s h\n s f a x : x : 5 0".., 1024, 3072) = 115

This is the clever bit. The KAIO kernel driver cannot operate on a filesystem file, so as a result of the first kaio(AIOREAD) failing, the aioread() function

spawns 4 lightweight processes (threads). Each of these threads is responsible for reading a different section of the file, but in parallel; thereby SIMULATING asynchronous I/O.

There are a number of actions that can be done to deal with this problem:

#1) Restart the database without asynchronous I/O

This is not an ideal solution and will only serve as a workaround until the underlying problem with asynchronous I/O is identified and fixed at the O/S level.

To operate without asynchronous I/O set the init.ora parameter 

disk_asynch_io = false

At the same time you should also set the following parameter, dbwr_io_slaves, to a value other than 0. This will allow Oracle to simulate asynchronous I/O through usage of multiple I/O slave processes where each such process performs I/O synchronously.

#2) Reduce I/O load on the system from this and other databases

Again this is only an attempt to avoid the underlying condition that causes asynchronous I/O to fail in cases where this happens under heavy load.

This is only a temporary measure until the problem is found and fixed e.g. by applying O/S patches.

Possible actions are:
- avoid running heavy batch jobs known to generate lots of I/O
- allocate more memory to the Buffer Cache and Sort Areas to reduce I/O from/to datafiles and temporary tablespaces
(relevant parameters: db_block_buffers, db_cache_size,
pga_aggregate_target, sort_area_size, hash_area_size etc.)
- tune checkpoints to reduce frequency of write I/O by DBWR

#3) Check all Oracle prerequisites for O/S and I/O subsystem configuration

You will need to refer to Oracle platform-specific Installation manuals and Administrator's references and make sure all required O/S patches and kernel parameters are in place and that Asynchronous I/O is correctly configured.

Note 169706.1 Oracle® Database on AIX®,HP-UX®,Linux®,Mac OS® X

#4) Check for known O/S issues with Asynchronous I/O operation

#5)Check filesystem's disk and try FULL export to /dev/null to identify possible corruption. 

Hope this help you to more understand :D

Chatchai Komrangded

Matching Table Types to Business Requirements

Oracle provides a wide variety of table types. The default table type is heap-organized. For most

applications, a heap-organized table is an effective structure for storing and retrieving data. However,

there are other table types that you should be aware of, and you should know the situations under which

each table type should be implemented. Table 1-1 describes each table type and its appropriate use.

In most scenarios, a heap-organized table is sufficient to meet your requirements. This Oracle table type

is a proven structure used in a wide variety of database environments. If you properly design your

database (normalized structure) and combine that with the appropriate indexes and constraints, the

result should be a well-performing and maintainable system.

Normally most of your tables will be heap-organized. However, if you need to take advantage of a

non-heap feature (and are certain of its benefits), then certainly do so. For example, Oracle partitioning

is a scalable way to build very large tables and indexes. Materialized views are a solid feature for

aggregating and replicating data. Index-organized tables are efficient structures when most of the

columns are part of the primary key (like an intersection table in a many-to-many relationship). And

so forth.

Thank For Good Information From Good Oracle Book "Oracle.Database.11g.Performance.Tuning.Recipes"

Hope this help you to more understand :D

Chatchai Komrangded

Keep in mind about Oracle Nologging Option

Many Time, We using Oracle "Nologging" Option to avoid the redo generate and speed up of bulk load, CTAS and etc, This is pros of nologging, but in cons that come from you can't recover the objects that using "Nologging" in some case, The below demonstrate will let you see my explanation on above:

*****You must have backupset for database before follow below step *****

1. Prepare 
create table t nologging as select * from all_objects;

2. Query Test
select count(*) from t;

3. Flush Buffer Cache
alter system flush buffer_cache;

4. Check block
select min(dbms_rowid.rowid_relative_fno(rowid)) "File_Number",
       min(dbms_rowid.rowid_block_number(rowid)) "Block number"
from t;

5. Corrupt Data Block
dd of=$FILE bs=$BLOCKNO conv=notrunc seek=8196
dd of=$FILE bs=$BLOCKNO conv=notrunc seek=`expr 1 + 8196`

6. Query Test, You will see the error
select count(*) from t;

7. Restore + Recover
rman target /
sql 'alter database datafile 11 offline';
restore datafile 11;
recover datafile 11;
sql 'alter database datafile 11 online';

8. Query Test you will found nologging problem your object can't recover
select count(*) from t;

If you following step by step until step 8 you will see

ORA-26040: Data block was loaded using the NOLOGGING option

Cause: Trying to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option

Action: Drop the object containing the block.

Very carefully when using this option, it can make your object to be unrecoverable state.

Hope this help you to more understand :D

Chatchai Komrangded

ORA-01410: invalid rowid

Oracle error ORA-01410: invalid rowid. It’s a problem I’ve responded to a few times in newsgroups and presentations, but I realised recently that despite mentioning it casually for several years I’ve never written a specific note about it – so here it is.

Although most indexes in Oracle can safely be left to keep themselves tidy and efficient some DBAs (possibly many DBAs) have been persuaded that rebuilding indexes regularly is a good thing. Other DBAs are a little more cautious and write programs that attempt to make a sensible decision about which indexes really do need to be rebuilt – and then report suspect indexes rather than rebuilding them.

Even if you fall into the latter group and do manage to identify the few indexes that can benefit from being rebuilt,be careful about letting the rebuilds take place automatically – the timing can be critical.

Consider this: if you rebuild an index (whether or not you use the online option) Oracle will copy the index contents from one location to another and, when the copy is complete, free up the space that held the original index contents.

What’s going to happen to a long running query that started running before you did your rebuild ? Surprisingly the query will carry on running using the “old” index. Oracle introduced a form of ‘cross-DDL read consistency’ many years ago to make partition maintenance operations possible, and that’s the feature that allows your query to carry on running.

But what’s going to happen if some other process now demands the space and puts something into it – for example by creating or extending a table or index (เช่นสร้าง table ใน tablespace เดียวกัน). The next time your long-running query hits the “old” index it may find itself looking at a block that contains the wrong type of data for the wrong object id. And so you get Oracle error ORA-01410: invalid rowid.

The effect is easy to demonstrate if you start with a completely empty tablespace (preferably locally managed with uniform extents, though this isn’t an absolute necessity):

###Prepare Environment

create table t1 nologging as

select

    rownum  id,

    rpad(rownum,10) v1,

    rpad('x',100)   v2

from

    all_objects

where

    rownum <= 10000;

alter table t1 add constraint t1_pk primary key(id);

execute dbms_stats.gather_table_stats(user,'t1');

###Run Some SQL Report on Session1

declare

    m_v1 varchar2(10);

begin

    for r in (select /*+ index(t1) */  v1 from t1 where id > 0)

    loop

        m_v1 := r.v1;

        dbms_lock.sleep(0.01);

    end loop;

end;

/

###Open Session2 And Doing Following Command

alter index t1_pk rebuild online nologging;

create table t2 nologging as select * from t1;

The new table t2 should fill the hole left by rebuilding the index t1_pk, and your first session should crash moments later.

The behaviour is much less predictable on production systems, of course. In the real world a random process may simply crash at some random time in the overnight batch once every few weeks for no apparent reason

Credit : Jonathan Lewis

Hope this help you to more understand :D

Chatchai Komrangded