Pythian Group

Welcome to the third installment of a series describing the Oracle Exadata platform. In part 1 we talked about hardware components, and in part 2 went on to discuss software. We now move on to how these components are packaged and licensed.

Unlike version 1 of the Oracle database machine, which was sold in full-rack increments only, version 2 introduces the concept of half and quarter rack configurations for smaller products. All database machines include a full-size physical rack for equipment, networking equipment including two redundant InfiniBand fabrics, the Cisco administrative switch, a KVM management unit, and power distribution units.
Read the rest of this entry . . .

Hello Apps DBA,

I’ve just come across an old known issue again today. Was working in the greatest and latest 12.1.2 Oracle e-Business Suite environment. Surprisingly, I face the old issue when WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES objects (among other objects) are set to NOLOGGING mode. Strictly speaking, the LOGGING attribute was set to YES at the table level, but several partitions of that table were set to LOGGING = NO! The SQL’s below will help you check if your environments are effected. Keep in mind that you should sound the alarm at your business if any other modules’ objects are in the list.
Read the rest of this entry . . .

Hello Folks,

As part of my preparation for the Exadata OCP Exam – Oracle Exadata 11g Essentials (1Z1-536), I was reading through the “Oracle® Exadata Storage Server Software User’s Guide” (not available publicly at the moment as far as I know). I found that in the Exadata world the two famous events “db file scattered read” and “db file sequential read” that cause so much confusion in Oracle DBAs’ minds are renamed to “cell multiblock physical read” and “cell single block physical read” accordingly.

Hopefully Oracle will port those events’ names to the RDBMS world as well in version 12c.

PS: I passed the Beta exam this morning. As with any beta, Oracle will release results after a period of time. ETA: 3-5 weeks from now.

Stay tuned, more info is coming,

Yury

As I work on putting together slides, I’d like to remind you all of my Oracle OpenWorld 2010 presentation, a customer case study migrating a complex data warehouse environment to Exadata. I’ll be presenting along with Michael Aldrich of LinkShare and Aiman al-Khammash of Oracle, and focusing on our own experience with Exadata, lessons learned, and advice for others choosing to implement Exadata.

The session will begin at half past noon on Monday in Moscone South, room 308. Here’s a link to import it into your calendar in ICS format

There was a discussion on the OTN General database forum, in which the OP asked creating a table with just one row and restricting that table to just one row. Here is my attempt at it.

I created a table with two columns, and the second column is a virtual column and contains a constant. I created a unique index on this column. On every insertion, this second column always evaluates to 1, and unique index (which become the function based index on virtual column) ensures that only one row remains in the table.

oracle@test # sqlplus /nolog
 
SQL*Plus: Release 11.1.0.7.0
 - Production on Sat Aug 28 19:09:16 2010
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
idle> conn test/test
Connected.
test@test> create table t1
 (c1 number, c2 generated always as (1) virtual);
 
Table created.
 
test@test> create unique index idx1 on t1(c2);
 
Index created.
 
test@test> insert into t1(c1) values (1);
 
1 row created.
 
test@test> commit;
 
Commit complete.
 
test@test> insert into t1(c1) values (1);
insert into t1(c1) values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated
 
 
test@test> insert into t1(c1) values (2);
insert into t1(c1) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated

Hello folks,

If it happens and you are in and around the Sydney area 1-2 weeks from now then you might be interested in taking part in the following events:

The first event is organized by Oracle Community in Sydney (sponsored by Pythian), during the next Sydney Oracle Meetup. The good news is that you can have free beer and pizza to recharge your batteries after a working day and have a good discussion with Oracle professionals in friendly environment.
- a – RAC, Grid, Cloud or on the way to Oracle Cloud
Wednesday 8 September 2010 17:30 p.m. to 19:30 p.m.

Usually, there are not many full day Oracle-related events happening in Sydney. More good news is that another one is coming soon. Attend and hear about new products and features from Oracle themselves and win a Car Navigator (as one of the lucky Pythian’s employees did at last Oracle event in Sydney recently :)
- b – Oracle’s Next Generation Data Centre Summit 2010 – Sydney
Thursday 16 September 2010 8:30 a.m. to 6:00 p.m.

See you around,
Yury – a DBA from down under

Hello Everyone,

This is one of my fist posts under Pythian’s blog. I will try to keep those short and simple at the beginning.

Recently I was troubleshooting a new GNS (Grid Name Services) functionality.

For more information please see here: Oracle Clusterware Network Configuration Concepts.

I have noticed that there is a-trace-level parameter in the GNS process string.
# ps -ef | grep gns
root 26790 1 2 14:41 ? 00:00:00 /u01/app/11.2.0/grid/bin/gnsd.bin -trace-level 0 -ip-address 10.10.193.201 -startup-endpoint ipc://GNS_hostrac01_23867_408c49e351f1f6a8
root 26825 17210 0 14:41 pts/1 00:00:00 grep gns


Unfortunately there is no description as of now in the documentation or MOS on how to change it to generate invaluable diagnostic information.
NOTE: I am sure the documentation will be updated in Database 12c version (c for Cloud ;)

For a time being the following should work for you:

# /u01/app/11.2.0/grid/bin/crsctl modify resource ora.gns -attr "TRACE_LEVEL=6"
# /u01/app/11.2.0/grid/bin/srvctl stop gns
# /u01/app/11.2.0/grid/bin/srvctl start gns


I hope that this advice will help you to diagnose your GNS issue.

I will blog about the way I resolved future GNS-related issues later on.

It looks like I have said too much for my very first post already.

See you around,

Yury

Just another DBA from down under

It’s that time of the year again — Oracle OpenWorld time — and it’s my pleasure to announce our regular Oracle bloggers meetup again this year. We all know that Oracle community has grown this year so we expect to see folks from all the different technologies including MySQL, Java, Sun hardware folks in addition to the core Oracle database and apps crowd.

So… all of you Oracle bloggers attending Oracle Open World 2010…
… you are invited to attend this Oracle Bloggers Meetup during OOW 2010 — a chance to meet your online buddies face-to-face in relaxed and informal atmosphere.

When: Wed, 22-Sep-2010, 5:30pm

Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.
Read the rest of this entry . . .

There is a little know fact that Oracle table compression doesn’t work with tables that have more than 255. It’s has been reported here and compression limitations in general here.

As a follow up from my previous post on Exadata Design, where I question the use of dimensions for certain attributes in data warehouses, I figured I should test whether HCC works with tables that have more than 255 columns.

And it does. Here’s my test case.

Setup:

create table ctest (
col_1  varchar2(30) default 'JUSTSOMEDATA',
col_2  varchar2(30) default 'JUSTSOMEDATA',
...
col_255  varchar2(30) default 'JUSTSOMEDATA'
);
insert into ctest (col_1) select 'OTHERDATA' from dba_objects;
insert /*+APPEND*/ into ctest select * from ctest;
commit;
insert /*+APPEND*/ into ctest select * from ctest;
commit;

Build various scenarios (NOTE: second create adds the 256th column):

/*REGULAR*/
create table ctest_cmp compress as select * from ctest;
create table ctest_cmp1 compress as select t.*, 'NEW' col_256 from ctest t;

/*HCC QUERY*/
create table ctest_hcc compress for query low as select * from ctest;
create table ctest_hcc1 compress for query low  as select t.*, 'NEW' col_256 from ctest t;

/*HCC ARCHIVE*/
create table ctest_hccA compress for archive low as select * from ctest;
create table ctest_hccA1 compress for archive low  as select t.*, 'NEW' col_256 from ctest t;

And the results:

SQL> select round((bytes)/1024/1024) as mb ,  segment_name from user_segments t where segment_name like 'CTEST%' order by segment_name;

        MB SEGMENT_NAME
---------- ----------------------------
       824 CTEST
         3 CTEST_CMP
       824 CTEST_CMP1
         1 CTEST_HCC
         1 CTEST_HCC1
         1 CTEST_HCCA
         1 CTEST_HCCA1

Michigan OakTable Symposium (MOTS) is a unique event taking place just before Oracle OpenWorld — 16-17 September. Why unique? This is the first conference (is it not?) where all presenters are members of OakTable Network, a group that gathers number of like-minded IT professionals with scientific approach to Oracle database technology and to the life in general.

It happens in Ann Arbor, Michigan — a place I wanted to visit for a very long time. For those of you who are cost conscious and don’t have much in their education budget, this conference is a great value priced at a third of the Oracle OpenWorld pass and I can assure you that quality of presentations is on par with or higher then the top Oracle OpenWorld sessions. The conference is organized by volunteers from OakTable Network and few good friends as a non-commercial event so you will hear no marketing crap whatsoever.

Unlike, huge OpenWorld crowd, which has always intimidated me a little bit, MOTS is going to be a cozy event with strictly limited cap of only 300 participants – ideal size to support peer networking and opportunity to mingle with some of the folks you’ve been dying to talk to.

I especially enjoyed this video promotion: Read the rest of this entry . . .