Tuesday, April 5, 2011

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2]

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [2650],
[2647], [0x0], [], [], [], []
==============================================

Database Version: 10.2.0.1 and later



Problem when
==============

The database is getting the following errors on Startup:

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [2650],
[2647], [0x0], [], [], [], []

Changes

control file corruption, then try to recover database with one of the control file .

Cause

[kccpb_sanity_check_2] indicates that the seq# of the last read block is
higher than the seq# of the control file header block. This is indication of
the lost write of the header block during commit of the previous cf
transaction.


Solution

1) restore a backup of a controlfile and recover

OR

2) recreate the controlfile

OR

3) restore the database from last good backup and recover

NOTE: If you do not have any special backup of control file to restore and you are using Multiple Control File copies in your pfile/init.ora/spfile you can attempt to mount the database using each control file one by one. If you are able to mount the database with any of these control file copies you can then issue 'alter database backup controlfile to trace' to recreate controlfile.

Monday, April 4, 2011

Oracle Database file Limits on various OS

Database Limits

lists the default and maximum values for parameters in a CREATE DATABASE or CREATE CONTROLFILE statement.

Note:

Interdependencies between these parameters may affect permissible values.

Table H-1 CREATE CONTROLFILE and CREATE DATABASE Parameters

Parameter Default Maximum Value

MAXLOGFILES

16

255

MAXLOGMEMBERS

2

5

MAXLOGHISTORY

100

65534

MAXDATAFILES

30

65534

MAXINSTANCES

1

63


lists the Oracle Database file size limits in bytes.

Table H-2 File Size Limits

File Type Platform

Data files

Any

4,194,303 multiplied by the value of the DB_BLOCK_SIZE parameter

Import/Export files and SQL*Loader files

Tru64 UNIX

16 TB


AIX, HP-UX, Linux, and Solaris: 32-bit with 32-bit files

2,147,483,647 bytes


AIX, HP-UX, Linux, Mac OS X, and Solaris: 64-bit files

Unlimited

Control files

HP-UX, Linux, Mac OS X, and Solaris

20000 database blocks


AIX

10000 database blocks


Tru64 UNIX

19200 database blocks

Maximum file Size limitation on Sun Solaries 10

Maximum file Size limitation on Sun Solaries 10
===================================

In Sun Solaris, UFS file system default maximum file size is 2GB which restricts the maximum size of any file will be restricted to 2GB. This can cause problems with applications like Databases.

Solaris 10 by default however sets the Maximum file Size to unlimited on the UFS File System.

To check the limits (if any) set on the File System try

ROOT# ulimit -a | grep file

To Set unlimited maximum filesize using ulimit

ROOT# ulimit unlimited


Edit the /etc/default/login file

Edit the /etc/default/login file using your favorite editor and uncomment the line “ULIMIT” and set the value to “0″ as follows:

ULIMIT=0

Remount the File System with “nolargefiles” option

solarisserver# mount -F ufs -o remount,larefiles /dev/dsk/c0t0d0s7 /export/home

Edit /etc/vfstab

To preserve the setting at system boot, edit the /etc/vfstab and add “largefiles” under the option tab for the file system as follows. The next time the system reboots, the largefiles option is set.

/dev/dsk/c0d0s7 /dev/rdsk/c0d0s7 /export/home ufs 2 yes largefiles

This should fix the Maximum file size issue in UFS File System in Sun Solaris.




How to recreate the control file in oracle database 10g?

RECREATING THE CONTROL FILE:
----------------------------

In 10g, you can create the control file. In addition, you can get
Oracle to create the script for you. To do this, perform the following steps:

1. With the database mounted or open, issue the following commands:

SQL> alter database backup controlfile to trace;
or
SQL> alter database backup controlfile to trace as '/d04/admin/stlbas/udump/contral_04042011_trace.trc' ;
SQL> exit

2. A trace file will have been generated in your 'user_dump_dest'.
User_dump_dest is an init.ora parameter, and can be found by
issuing:

SQL> show parameter user_dump_dest

like
SQL> show parameter user

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest string /d02/admin/stlbas/udump
SQL>


The easiest way to locate the correct trace is to look at its date. A
file will exist with the current date and time. The naming convention
for these files is operating system specific.

3. Once the file is located, search through the file for the word "CONTROL"

You should find:

# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
ETC.ETC.

4. Copy this trace file to some location and rename it to end it ".sql",
for this example, it is called "recr_con.sql".

5. Edit the "recr_con.sql" deleting the trace header information. Then
Change as you want .

6. Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).

7. Take a full database backup at this time.

8. Remove the current control files. It is essential to remove all control
files, otherwise, you will receive an error. In addition, you CANNOT
REUSE the control file, since the size of the control file will increase
when you increase MAXDATAFILES.

9. Create the controlfile within SQLDBA:

SQL> connect internal [if using sqldba or svrmgrl]
or
connect / as sysdba [if using sqlplus]
SQL> @recr_con.sql
SQL> alter database open noresetlogs;

If you receive a "Statement processed" message, then your database is
now back up and running with your change value. It is recommended
to shutdown at this time and take a full backup.

Sunday, April 3, 2011

ORA-07445: exception encountered: core dump [ksuklms()+] [SIGSEGV] [Address not mapped to object]

ORA-07445: exception encountered: core dump [ksuklms()+672] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []
========================================================


Analysis of ORA-07445: exception encountered: core dump [ksuklms()+672] first argument


====================in alert log file =========================

Errors in file /d01/admin/orcl/bdump/orcl_j000_29155.trc:
ORA-07445: exception encountered: core dump [ksuklms()+672] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []
Sun Apr 3 10:02:32 2011


===============================in the associate trace file=========
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ksuklms()+672] [SIGSEGV] [Address not mapped to object] [0x000000062] [] []
Current SQL statement for this session:
ALTER SYSTEM KILL SESSION '2192,1'
----- PL/SQL Call Stack -----
object line object
handle number name
504e1e2b0 23 procedure orcl.DPR_USER_LOCKS
5107b8b28 1 anonymous block
----- Call Stack Trace -----





SQL>
SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


SQL>
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.3.0
SQL>



Cause of this problem:
======================

This problem is introduced in 10.2.0.4 and fixed in 10.2.0.5

A dump can occur under ksuklms leading to an instance crash following a kill of a system process.
Such a kill can occur automatically in RAC. It is not required that you be on RAC to encounter this problem but the issue is much less likely to occur in non-RAC.

It is caused by the Controlfile timeout


In this case the solution will be
=================================

if trace file we are seeing that

procedure orcl.DPR_USER_LOCKS try to killing a session with command "ALTER SYSTEM KILL SESSION '2192,1'" . but in that time Controlfile timeout occure. so stop the procedure will solve the problem.