ORA-00600 while creating an Index

Error:
ORA error while executing CREATE INDEX Statement of type:
CREATE INDEX TABLE_NAME ON INDEX_NAME (COLUMN)
TABLESPACE PSDEMO STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING


ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [3], [0], [16], [], [], [], []

[ or ]

ORA-12801: error signaled in parallel query server P086
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [5], [0], [8], [], [], [], []


Steps to resolve the error:

1. SQL> alter table TABLE_NAME move;
2. Now you will be to create the index successfully.

Note: It is NOT a good idea to create index with keyword PARALLEL but no DEGREE specified. Oracle will assume "default" degree which usually is not good - too high

43531: WARNING: inbound connection timed out (ORA-3136)

Error:- 43531: WARNING: inbound connection timed out (ORA-3136)

Cause: Inbound connection was timed out by the server because user
authentication was not completed within the given time specified by

SQLNET.INBOUND_CONNECT_TIMEOUT or its default value (default value is
60seconds)

Actions:
1. Check the sqlnet.log for any suspicious actions
2. Change the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora
file to 0 (indefinite)

Managing blocks in Buffer Cache

The blocks in the buffer cache are basically managed in a single place with two different lists pointing at them:
• The dirty list of blocks that need to be written by the database block writer
(DBWn; we’ll take a look at that process a little later)
• A list of nondirty blocks

The X$BH table shows information about the blocks in the block buffer cache. Here, we can see the touch count get incremented as we hit blocks. Oracle will attempt to increment the TCH once every three seconds (there is a TIM column that shows the last update time to the TCH column), but it is not considered important that the number be 100 percent accurate, as it is close. Also, Oracle will intentionally “cool” blocks and decrement the TCH count over time.


Query to find out top five hot blocks:

select tch, file#, dbablk,
case when obj = 4294967295
then 'rbs/compat segment'
else (select max( '('||object_type||') ' ||
owner || '.' || object_name ) ||
decode( count(*), 1, '', ' maybe!' )
from dba_objects
where data_object_id = X.OBJ )
end what
from (
select tch, file#, dbablk, obj
from x$bh
where state <> 0
order by tch desc
) x
where rownum <= 5
/

(or)

select tch, file#, dbablk, DUMMY
from x$bh, (select dummy from dual)
where obj = (select data_object_id
from dba_objects
where object_name = 'DUAL'
and data_object_id is not null)

Creating a tablespace with a different block size other than the default:

SQL>create tablespace ts_16k
datafile size 5m
blocksize 16k;
create tablespace ts_16k

*
ERROR at line 1:
ORA-29339: tablespace blocksize 16384 does not match configured blocksizes

SQL> show parameter 16k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0

Here the tablespace does not get created since I have configured db_16k_cache_size parameter. Inorder to create a tablespace with different cache size other than the default cache that is for the SYSTEM and all TEMPORARY tablespaces ( you can have upto four other blocksize) you need to shrink your db_cache_size if it set to a large value:


Ex:
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 1G

SQL> alter system set db_cache_size = 768m;

System altered.

SQL> alter system set db_16k_cache_size = 256m;

System altered.

SQL> create tablespace ts_16k
datafile size 5m
blocksize 16k;

Tablespace created.

Here the two cache sizes db_cache_size and db_16k_cache_size are exclusive; if one “fills up,” it cannot use space in the other. These multiple blocksizes were not intended as a performance or tuning feature, but rather came about in support of transportable tablespaces—the ability to take formatted data files from one database and transport or attach them to another database. They were implemented in order to take data files from a transactional system that was using an 8KB blocksize and transport that information to a data warehouse using a 16KB or 32KB blocksize.The transactional data would benefit from the smaller blocksizes due to less contention on the blocks (less data/rows per block means less people in general would go after the same block at the same time) as well as better buffer cache utilization (users read into the cache only the data they are interested in—the single row or small set of rows). The reporting/warehouse data, which might be based on the transactional data, would benefit from the larger blocksizes due in part to less block overhead (it takes less storage overall), and larger logical I/O sizes perhaps. And since reporting/warehouse data does not have the same update contention issues, the fact that there are more rows per block is not a concern, but a benefit.

Note: For example, if after startup your SGA size was 128MB and you wanted to add an additional 64MB to the buffer cache, you would have had to set the SGA_MAX_SIZE to 192MB or larger to allow for the growth.

Oracle Processes

An Oracle instance has three broad classes of processes:
Server processes: These perform work based on a client’s request. We have already looked at dedicated and shared servers to some degree. These are the server processes.

a. Dedicated server, whereby you get a dedicated process on the server for your connection. There is a one-to-one mapping between a connection to the database and a server process or thread

b. Shared server, whereby many sessions share a pool of server processes spawned and managed by the Oracle instance. Your connection is to a database dispatcher, not to a dedicated server process created just for your connection.

Background processes: These are the processes that start up with the database and perform various maintenance tasks, such as writing blocks to disk, maintaining the online redo log, cleaning up aborted processes, and so on.

Slave processes: These are similar to background processes, but they are processes that perform extra work on behalf of either a background or a server process.