Friday, March 18, 2011

Oracle 11gR2 on AIX Memory Issue after Upgrade

Check this article if you plan or already upgraded to 11gR2 on AIX.

http://www.ora600.be/Memory+Footprint+For+Dedicated+Server+Processes+More+Than+Doubled+After+11g+Upgrade+On+AIX+Platform

Profile and Resource Limit

Recently i decided to set an idle_time limit for some of my production database users, beacause all the time left their sessions connected to the database after finishing their work consuming resources.

I create a profile limiting idle time to 2 hours , but i found out that its not working.

In order a profile to limit a resource you must set the RESOURCE_LIMIT initialization parameter to TRUE. Fortunately this parameter is dynamic so :

Issue :

For RAC DBs:
alter system set resource_limit = TRUE scope=both SID='*';
create profile tst_profile limit idle_time 120;
alter user my user profile tst_profile;

For non RAC DBs omit the sid='*'



For more info check the

Oracle® Database Security Guide 10g Release 2 (10.2)
Part Number B14266-06

Monday, March 14, 2011

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

ORA-00600: internal error code, arguments: [kdsgrp1] , This error may occur on a RAC database when (re)building index online.

The fixes for this bug are in Metalink Note : 285586.1

As a workaround please try to rebuild the index either offline or online with as little as possible activity on the affected table.

If error continues check the table for chained rows.

ORA-00230 operation disallowed: snapshot controlfile enqueue unavailable

On RMAN backup with automated controlfile backup enabled this error means that another process currently holds the snapshot controlfile enqueue , so the current process cannot backup it.

If you have this error with multiple channels, you may hit a bug. Search Oracle Support.

Wednesday, March 2, 2011

Exchanging Partitions Example

1) Exchanging a List partition table

The following example will show how to move from one partition table to a new one using an intermediate table.

-- Create our set of tables and populate with some data

CREATE TABLE myschema.my_part_table
(a char(1),
b int
) partition by list(b)
(
partition p_initial values (0),
partition p_part values (1)

);

create index myschema.my_part_table_idx on myschema.my_part_table(b) local;

insert into myschema.my_part_table
select 'A',1
from all_objects;

commit;


create table myschema.my_int_table as select * from myschema.my_part_table where 1=2;
create index myschema.int_my_part_table_idx on myschema.my_int_table(b);


CREATE TABLE myschema.my_new_part_table
(a char(1),
b int
) partition by list(b)
(
partition p_initial values (0),
partition p_part values (1)

);

create index myschema.my_part_table_idx on myschema.my_new_part_table(b) local;

-- Exchange original partition with intermediate table

alter table myschema.my_part_table exchange partition p_part with table myschema.my_int_table
including indexes without validation;

-- Exchange intermediate table with target partition table

alter table myschema.my_new_part_table exchange partition p_part with table myschema.my_int_table
including indexes without validation;


-- Verify the data are in the new partition table
select * from myschema.my_part_table partition (p_part);
select * from myschema.my_new_part_table partition (p_part);
select * from myschema.my_int_table;

Tuesday, March 1, 2011

ORA-00054 Resource busy and acquire with NOWAIT specified

A very nice entry for Oracle lock analysis

http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html