While recovering a database, stuck with ORA-00600 and database crashed. We have an Oracle version 11.1 and were hitting a bug: 8310931 and fixed in 11.2. The bug says, this problem will happen usually on high number of CPUs while doing transaction recovery. We got 126 CPU in the DB server.
Errors in file /opt/oracle/diag/rdbms/xxxx/xxx/trace/xxx_smon_29786.trc (incident=120257):
ORA-00600: internal error code, arguments: [ktprhtnew6], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/xxx/xxxx/incident/incdir_120257/xxxx_smon_29786_i120257.trc
Fri Feb 22 22:49:14 2013
Trace dumping is performing id=[cdmp_20130222224914]
Fatal internal error happened while SMON was doing active (more...)
In past two posts ( from current schema and as SYS user ) , I have explained how we can restore a package. Some one posed me a question – how can I identify who dropped the package. Even though we are seeing a procedure or package as database object, it is stored as a table rows in the SOURCE$ table just like other tables in the database. So, any CREATE or DROP procedure / package are just INSERT or DELETE or UPDATE operations to the SOURCE$ table. We can use this along with FLASHBACK feature to identify who dropped (more...)
Ordering data in a table is important to avoid bottlenecks. In the previous post I had explained how to avoid ITL waits by ordering the data. This demo will shows how we can remove “buffer busy waits” by ordering the data. I ran the below 5 similar sql scripts on a table ABC concurrently.
$ more 5.sql
update abc set owner='ABC' where mod(object_id,10)=5;
commit;
exit
$ more 4.sql
update abc set owner='ABC' where mod(object_id,10)=4;
commit;
exit
$ more 3.sql
update abc set owner='ABC' where mod(object_id,10)=3;
commit;
exit
$ more 2.sql
update abc set (more...)
As I said in the previous post, the straight forward method to resolve ITL wait is to increase the ITL slots while creating the table or modifying an existing table. As this is a standard way, I am not going explain this method. But, sometimes the data distribution in a table can contribute to the ITL waits. From my test table I will create two tables using the same data.
SQL> create table abc_unorg as select * from abc where 1 = 2 ;
Table created.
SQL> alter table abc_unorg pctfree 1 ;
Table altered.
SQL> create (more...)
ITL waits are so common and sometimes it will kill the concurrency of the application. In general there are 1 ITL slot (caused by INITRANS) for tables and 2 slots for indexes. When a data block is formatted the ITL slot is created in the variable part of the block header as specified by INITRANS for that segment. As long as free space is available in the block, ITL slots can be grown up to MAXTRANS for any future requirements. Here plays the important role PCTFREE which can impact negatively. With the default setting 10% (more...)
After upgrading VCS to 5.1 CRS came up normally but database startup failed with error,
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:lcin: LMXIOC_GETVERSION failed: failed with status: 122
ORA-27301: OS failure message: Operation not supported on transport endpoint
ORA-27302: failure occurred at: vcsipc_lmxci
ORA-27303: additional information: lcin: LMXIOC_GETVERSION failed: errno 122[Operation not supported on transport endpoint], fd 13, c0x1069017d8
MOS was not able to troubleshoot the issues, but the fix came from Veritas. The solution is here http://sfdoccentral.symantec.com/sf/5.1SP1/linux/html/sfrac_install/ch16s01.htm
I am sure this will be helpful for someone in the future, so (more...)
What is the easiest way to identify chained or migrated rows, specially you want to just check few rows, they are migrated / migrated or not. Here is the test. Prepare the test table with PCTFREE 0.
SQL> create table t1 (c1 varchar2(4000)) pctfree 0 ;
Table created.
SQL> select table_name,PCT_FREE from user_tables;
TABLE_NAME PCT_FREE
------------------------------ ----------
T1 0
SQL> insert into t1 values('A');
1 row created.
SQL> commit ;
Commit complete.
SQL> insert into t1 select * from t1;
1 row created.
..........
SQL> insert into t1 select * from t1;
1024 rows created.
SQL> commit ;
Commit complete.
SQL> (more...)
In many occasions, SQLs failed with “ORA-01555 snapshot too old error” due to DMLs along with the SELECT query. There is no view in Oracle database to tell us who is or which transaction is forcing to read UNDO for consistency. Below example shows,
SQL> select count(*) from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 580 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| (more...)
Had an interesting file corruption problem today. Oracle 9i database in Solaris OS. Processes started failing with TEMP file corruption.
ksedmp: internal or fatal error
ORA-01114: IO error writing block to file 201 (block # 1329161)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1329161
Additional information: 31
Additional information: 1327105
ORA-01114: IO error writing block to file 201 (block # 1329161)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1329161
Additional information: 31
Additional information: 1327105
ORA-01114: IO error writing block to file 201 (block # 1329161)
(more...)
I was confronted with an application team recently for creating their 19th index on a large table with close to 100 columns! The developer community was entirely unhappy as their idea was shot down by me. Added to above, recently more requests started coming on our way to faster the ROLLBACKS. So, thought to write a test case, what will happen if we create too many indexes on a table, too many indexes on a same column.
Here is my test table with just 0.25 million records and I am just UPDATing and ROLLBACKing the (more...)
RSS