Exadata Book

osborne | Sep 1, 2010 06:38 +0000

Here’s some white Exaddata text

Well, I guess it’s official. A couple of weeks ago I committed to write an Exadata book for Apress, along with my intrepid co-author Randy Johnson. For those of you who don’t know Randy, he’s a very experienced Oracle Guy with a wealth of knowledge, particularly around RAC. I think the two of us make a pretty good team - making up for each others weaknesses (oh wait, I should say we have “Complimentary Skill Sets” - yeah that sounds better).

Anyway, it turns out that writing a book is a lot of work! The way Tom Kyte turns them out I thought it must be pretty easy, but I’ve always been a little overconfident. So I’m starting to realize that I may not have time to do as many blog posts as I might like. But I must say that I am really excited about the subject matter! So I think it will be worth the effort. By the way, that’s not the official cover art (or even the official title as far as I know). I just hacked that together with a Adobe Illustrator. ;-)

As I have said many times, I think the Exadata storage software will usher in a whole new era in relational databases. Not just for Oracle, because you know the other guys will be trying to follow in their footsteps. But I think Oracle is miles ahead at this point, and they own their own hardware platform. Oracle claims that it has been their most successful product launch ever and I believe it. They are starting to pop up like weeds. It will be very interesting to see what the future holds for this platform. To be honest, I think we’re just seeing the tip of the iceberg at this point. Anyway, I appreciate the guys at Apress having the faith in us to take on this project. I hope we don’t disappoint them (I don’t think we will).

Convert Ext3 to Btrfs

After I make kernel 2.6.35.4 on Oracle Enterprise Linux 5.4 support Btrfs.
# cd /usr/src
# tar jxvf linux-2.6.35.4.tar.bz2
# cd linux-2.6.35.4
# cp /boot/config-2.6.18-164.el5 .config
# make && make modules_install headers_install install
# mkinitrd /boot/initrd-2.6.35.4 2.6.35.4
# reboot
then installed btrfs-progs-0.19 program.
# cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

# rpm -q enterprise-release
enterprise-release-5-0.0.20

# uname -a
Linux oel 2.6.35.4 #1 SMP Wed Sep 1 20:37:04 ICT 2010 x86_64 x86_64 x86_64 GNU/Linux
and then tested to convert ext3 to btrfs (read):
# mount | grep /dev/sda6
/dev/sda6 on /data type ext3 (rw)

# ls /data
linux-2.6.35.4.tar.bz2

# umount /data

# fsck.ext3 /dev/sda6
e2fsck 1.39 (29-May-2006)
/data: ...

# btrfs-convert /dev/sda6
creating btrfs metadata.
creating ext2fs image file.
cleaning up system chunk.
conversion complete.

# mount -t btrfs /dev/sda6 /data

# mount | grep /dev/sda6
/dev/sda6 on /data type btrfs (rw)

# ls /data
ext2_saved linux-2.6.35.4.tar.bz2
we will see ext3/4 snapshot (ext2_saved). we can mount loopback for image in snapshot:
# mount -t ext3 -o loop,ro /data/ext2_saved/image /mnt

# ls /mnt
linux-2.6.35.4.tar.bz2
check some command-line:
# btrfs-show
Label: /data uuid: 7721003c-adcb-4706-8238-68946a5e2547
Total devices 1 FS bytes used 8.73GB
devid 1 size 128.79GB used 128.79GB path /dev/sda6

Btrfs Btrfs v0.19
However, if we need to roll back the conversion(we should backup). we can:
# umount /mnt
# umount /data
# btrfs-convert -r /dev/sda6
Written By: Surachart Opun http://surachartopun.com

DOAG Conference in Nürnberg

This is a short note to point out that I just added to the Public Appearances page the next conference organized by the Deutsche ORACLE-Anwendergruppe (DOAG) in Nürnberg. It will take place on November 16-18. My talk, entitled “Transaktions-Management Internas”, will be the German version of then one I will give at the Michigan OakTable [...]

Parallel Processing With Standard Edition

As clearly stated in the Licensing Information guide, all features related to parallel processing (parallel backup and recovery, parallel query/DML, parallel statistics gathering, parallel index build/scans, parallel Data Pump export/import, in-memory parallel execution, parallel statement queuing and parallel spatial index builds) are only available with the Enterprise Edition. However, as of Oracle Database 11g Release [...]

RMAN-03009 ORA-19554 ORA-27211 (2)

This is the second post for the same error:

RMAN-00571: =======================================
RMAN-00569: ======= ERROR MESSAGE STACK FOLLOWS =======
RMAN-00571: =======================================
RMAN-03009: failure of allocate command on t1 channel at 08/03/2010 19:57:21
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management

Some Blog Errors Are Just Too Serious To Ignore. A Comparison of Intel Xeon 5400 (Harpertown) to Intel Xeon 5500 (Nehalem EP).

I’d like to direct readers to an important blog update/correction. In my post entitled An Intel Xeon 5400 System That Outperforms An Intel 5500 (Nehalem EP) System? Believe It…Or Know It I blogged about an erroneous conclusion I had drawn about a test performed on these two processor models. I think the update does the blog [...]

Statistically summarize Oracle Performance data

karlarao | Aug 31, 2010 09:13 +0000

Here’s the draft of this post http://karlarao.tiddlyspot.com/#dbms_stat_funcs , expounded version coming up! :)






Ensuring Table With Only One Row in Oracle 11g Using Virtual Column

Fahd Mirza | Aug 31, 2010 06:41 +0000

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

Oracle-related events in Sydney area (September 2010)

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

OOW 2010 Plans and Anti-plans

Dan | Aug 30, 2010 23:24 +0000

I have plenty of things that are keeping me busy for OOW 2010 and you’ll all get to see the results at the event (if you’re there), but I only have one traditional technical session where I’ll be on stage. I’m presenting the following session jointly with an Oracle Database Machine customer:

Session ID: S316824
Title: Top 10 Lessons Learned in Deploying the Oracle Exadata
Tuesday, September 21, 12:30PM
Location: Moscone South, Rm 307

Check the OOW 2010 content catalog for updated room assignments and times.

Even better than a technical session is the interview and Q&A session I’m doing on Oracle Technology Network Live which is 30 minutes of pure technical talk about Exadata. The session is properly titled “Exadata for Geeks” and I’ll be joining Justin Kestelyn, editor of Oracle Technology Network at the OTN Lounge which is located in the Mason Street tent this year (*not* the previous location in Moscone West).

Significantly, this year I elected not to organize what would have been the 3rd annual pre-OOW scuba dive in Monterey Bay. Time and my work requirements are the primary reasons for this, but it also is a result of the fact that not a single person asked me about it, so apparently it was just for me after all :) . Instead, I’m hoping that I might get to visit Alcatraz this year. I’ve been to SF so very many times in the past 12 years, but have yet to take that tour, so I think it’s time (I’ve heard it is a really interesting tour).

See you in SF!

Finding a New Home for a Client on Windows

August 30, 2010 As I am sure that you are aware, there are compatibility problems between Oracle Databases and Oracle Clients that are from different releases.  The Oracle Database 11.1 documentation states the following: “Upgrade Clients to the Current Release You can upgrade any or all of your Oracle8i, Oracle9i, or Oracle Database 10g clients [...]

Simple Reporting Without Materialized Views

A common initial resolution to reporting requirements without a reporting database is to utilize materialized views.  Unfortunately, the cost to refresh these views can be extremely expensive on a database, especially if it's an OLTP that rarely has a lull in activity.

A lesser known feature of Oracle's is the Change Data Capture, (a.k.a CDC
http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/cdc.htm )
The CDC process utilizes a materizalized view log on a source table with a destination copy of the source table that can be kept insync with the original in any interval chosen.  The result is often a much lighter footprint than an mview refresh, no matter if you've employed complete or fast refreshes. 

I've had added success by pulling sections of the CDC underlying code to syncronize destination objects and replicating just the pieces I need to get the results desired.  CDC has a lot of features and can be over-whelming the first time a DBA or developer delves into it.  For anyone who would like to reproduce the simple source-to-reporting object syncronization with an mview log, here are the high level steps:

1.  Create a materialized view log on the source table using a primary key or rowid.
2.  Create a stage table that will hold two columns of information:
            1st Column:  The type of row, Insert, Update or Delete, ('I','U','D').
            2nd Column: The primary key or rowid.
3.  Create two stored procs, either in a package or separately, where the 1st one will populate the stage table and the second will take the info from the stage table and process the rows to the destination table.

In depth, the process and code will appear similar to the following example:

Create the materizlied view log on the source table, (SRC_TBL):
CREATE MATERIALIZED VIEW LOG ON SCOTT.SRC_TBL;

Create the staging table:
CREATE TABLE STG_PK_IDS
(OPT CHAR(1),
PK_ID NUMBER not null)
tablespace USERS;

Create the stored procedure to load the stage table, (STG_TBL):
CREATE OR REPLACE PROCEDURE UPD_PKID_FOR_RPT IS
/*********************************************************************
--Get Records for both insert and update
CURSOR cur IS
SELECT *
FROM (SELECT 'I' opt, pk_id
FROM scott.mlog$_src_tbl where DMLTYPE$$='I')  --Query the mview log on the source
UNION ALL
(SELECT 'U' opt, pk_id
FROM scott.mlog$_src_tbl where DMLTYPE$$='U')
UNION ALL
(SELECT 'D' opt, pk_id
FROM scott.mlog$_src_tbl where DMLTYPE$$='D');


BEGIN


-- Insert records for transactions into staging table, all of them
FOR rec IN cur LOOP



IF rec.opt in ('I','U','D') THEN
INSERT INTO scott.STG_TBL (opt, pk_id) VALUES (rec.opt, rec.pk_id);
END IF;
END LOOP;
COMMIT;


--Purge processed rows from the mview log, using the reporting table to tell us which rows were added to it.
DELETE FROM scott.mlog$_src_tbl
WHERE OPT in ('I','U')
AND PK_ID in (select PK_ID from scott.RPT_TBL);


COMMIT;


END UPD_PKID_FOR_RPT;

Create a second stored prodedure to populate the data to the reporting table, (RPT_TBL)
CREATE OR REPLACE PROCEDURE UPD_RPTTBL_SP AS

/*********************************************************************

CURSOR cur IS
SELECT *
FROM (SELECT stg.opt,
stg.pk_id,
src.user_id,
src.phn_nbr,
src.login_id,
src.usr_type_cd,
src.usr_dt,
src.email_add
FROM scott.SRC_TBL src, scott.STG_TBL stg
WHERE src.pk_id = stg.pk_id
AND stg.opt='I'

UNION ALL
SELECT stg.opt,
stg.pk_id,
src.user_id,
src.phn_nbr,
src.login_id,
src.usr_type_cd,
src.usr_dt,
src.email_add
FROM scott.SRC_TBL src, scott.STG_TBL stg
WHERE src.pk_id = stg.pk_id
AND stg.opt='U'

UNION ALL
SELECT stg.opt,
stg.pk_id,
src.user_id,
src.phn_nbr,
src.login_id,
src.usr_type_cd,
src.usr_dt,
src.email_add
FROM scott.SRC_TBL src, scott.STG_TBL stg
WHERE src.pk_id = stg.pk_id
AND stg.opt='D'
);

v_bgn_cnt number :=0;
v_step_name varchar2(200);
v_rec_ins number := 0;
v_start_dt date := sysdate;
v_error_msg varchar2(500);


BEGIN
v_step_name := 'Process inserts and updates into scott.RPT_TBL';


SELECT count(*)
into v_bgn_cnt
FROM scott.STG_TBL;


IF v_bgn_cnt >0 THEN


-- Process each change


FOR rec IN cur LOOP


IF rec.opt = 'I' THEN
INSERT INTO scott.RPT_TBL
VALUES
( rec.pk_id,
rec.user_id,
rec.phn_nbr,
rec.login_id,
rec.usr_type_cd,
rec.usr_dt,
rec.email_add);
END IF;

IF rec.opt = 'U'
THEN
UPDATE scott.RPT_TBL
set USER_ID = rec.user_id,
PHN_NBR = rec.phn_nbr,
login_id = rec.login_id,
user_type_cd = rec.user_type_cd,
usr_dt = rec.usr_dt,
email_add = rec.email_add
WHERE pk_ID = rec.pk_ID;
END IF;

IF rec.opt = 'D'
THEN
DELETE from scott.RPT_TBL
WHERE pk_ID = rec.pk_ID;
END IF;


COMMIT;
END LOOP;
END IF;

--Insert Error handling here, I'm going to skip this in the example...
--Add a lookup table to track the pk_id from previous runs, etc.
--Truncate the stage table for the next run once success had been reported

END UPD_RPTTBL_SP;


We now can use the objects and procedures to maintain a copy of the SRC_TBL.  The DBA or developer simply needs to choose a functional interval to refresh and execute the procedures view a scheduled job or through a shell script.
exec UPD_PKID_FOR_RPT;
exec UPD_RPTTBL_SP;

Ta-da!  A simple process to syncronize data from a transactional table to a reporting copy.  This process can be scaled to load and commit in many different ways, depending on the demand and size of the targets.  It's simple and can support many levels of loading intervals without the impact that often occurs with a materialized view refresh.

 

How to GNS process log level for diagnostic purposes (11G R2 RAC / SCAN / GNS) ?

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

Where can I download CentOS 2.1?

Tim... | Aug 30, 2010 16:42 +0000

I need to download a copy of CentOS 2.1 (x86), but I can’t find it anywhere. I’ve been down the list of mirrors and they all list 2.1, but then have an empty tree below it.

If anyone knows how I can get hold of it please drop me a line.

Cheers

Tim…

Announcements- OpenWorld and Oracle ACE Director Status

Dear Readers,

I have become an Oracle ACE Director!

Even more exciting news is that I will be presenting at Oracle OpenWorld this year. Below are the details:

Session ID: S319054

Title: Oracle E-Business Suite 12 Performance Tuning 101

Abstract:
In this session, learn how to approach performance tuning issues in both proactive and reactive modes for all components of the complex Oracle E-Business Suite 12 environment. Discover the latest performance tuning methods and tools for the application and database tiers with Oracle E-Business Suite. Learn how to tune the concurrent manager, application-tier components, the Java Virtual Machine (JVM), and database tier servers.

Speaker(s): Ben Prusinski, Ben Prusinski and Associates, President and Chief Architect

Stream(s): APPLICATIONS
Track(s): Applications Tools and Technology, Oracle E-Business Suite
Schedule: Monday, September 20, 12:30 | Marriott Marquis, Salon 3

Hope to see everyone there! I will also be answering your technical Oracle questions at the Oracle OTN ACE Lounge as well.

I’m an Oakie now!

Yep, all of a sudden I was invited by Jonathan Lewis to the group of the most knowledgeable Oracle people in the world! I still can’t believe in this, but a number of e-mails reminds me I’m not sleeping and it’s not a dream. Thank you very much, Jonathan, for inviting me! Thanks to all [...]

I’m back

Long time no post… I post nothing for more than two months and there were reasons: I was on-site in Canada for 5 weeks in June/July, doing some stuff which I don’t do often in the back office – and most of the time quite busy; after that I spent two extremely hot and smoked [...]

HOWTO: Merge files under Windows

Small “howto”, because I always forget.

Today I needed to merge to Linux tar files on Windows. I downloaded the two Oracle VM Templates for Oracle Business Intelligence Enterprise Edition (OBIEE). After unzipping them you get two tar files called

  • OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2
  • OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2

To be able to merge them and then “untar” those files, under Linux or Unix you would do something like:

 
cat {file01} {file02}  | tar -xvz
 
cat OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2| tar -xvz
.

…with other words, you would “cat” the first and second file into a file and then “untar”-ed it on the fly via the “tar -xvz”. In Windows you would do this via the following command, that is the first “cat” part, merging the files, via:

 
copy /B {file01} {file02} {newfile}
 
copy /B OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 + OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2 OVM_EL4U5_X86_PVM_10gR3BIEE.tgz
.

the Windows equivalent of the Linux/Unix

 
cat {file01} {file02}  > {newfile}
 
cat OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 + OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2 > OVM_EL4U5_X86_PVM_10gR3BIEE.tgz
.

So you the Windows statement will “merge & copy” them into the needed file, in my case file: “OVM_EL4U5_X86_PVM_10gR3BIEE.tgz”.

Now you can, with a (un)zip utility like “winrar”, “untar” the ” “OVM_EL4U5_X86_PVM_10gR3BIEE.tgz” file into its original content.

Back home and writing


After a few trips abroad and very interesting projects with vip-big customers & new features that demanded lot of time and concentration ... I'm back!

Want to share with you some goodies from my trips ... and advice.

Trinidad & Tobago: be aware of displicent and slow service, lot of chinese places.

Brazil, Rio de Janeiro: try Feijoada at Rondinella Rest. (Av. Atlantica, Copacabana Beach); if you go alone, ask for 1 person sizing ... you won't regret!!


Key Capability Maturity Optimization: Key Capabilities

Epi Torres | Aug 30, 2010 10:00 +0000

As I mentioned in my last post, our key capabilities at Remote DBA Experts are those that support our ability to be most proactive, responsive and effective.  Let me now expand upon each of the key capabilities.

Proactiveness Capabilities

Proactiveness is first and foremost an attitude. Thus, it comes from people and it starts with them.  That means we must look for this attitude in our recruiting and hiring process.  We hire individuals who are technically capable and meet many other criteria (see our hiring criterion here).  That is where it all starts.  Furthermore, we provide the individual with the ability to be most proactive by enabling proactivity with key capabilities.  In the case of Proactiveness, our primary aim is internal and customer problem prevention.

In order to be most efficient and effective at problem prevention, we focus our attention and investment on the key capability components:

  • Key Information
  • Key Processes
  • Key Skills
  • Key Sub Capabilities
  • Key Tools
  • Other

The first step is to identify these key items and then to ensure they are in place and available to the team.  This is where we focus our optimization efforts.

Here at Remote DBA Experts, our monitoring capability is the primary tool that enables us to predict and prevent problems and we place significant effort to that end.  In fact, this year we invested a lot of money and attention to this capability.  We have also established a new internal organization that is completely focused on problem prevention and customer responsiveness.  It is working out to be one of our best ideas ever!

Responsiveness Capabilities

Like Proactiveness, Responsiveness is also an attitude we look for in the folks we recruit and hire. In this area we have also made significant efforts and investments recently to enhance this capability.  We implemented a new CRM that is the key to improving and maintaining our ability to respond to our customers both internal and external.

Responsiveness involves four key aspects:

  • Acknowledging
  • Timely Action
  • Updating
  • Closure

Our Responsiveness Capabilities are focused on enabling these aspects. This ensures that we acknowledge customer requests quickly, take timely action, keep the customer informed along the way, and close the matter to the satisfaction of the customer.

Effectiveness Capabilities

For Remote DBA Experts, Effectiveness means accomplishing two primary things for our customers: Ensure the highest database environment availability and performance possible. That is it!  That is what being effective means to our team.

Our Effectiveness Capabilities focus on two areas: Activities and Tasks.

Activities

Activities are what I call the set of “meta-activities” people must do effectively in order to accomplish value delivering tasks and achieve the key results customer expect.  Effectively completing them is part of the sequence.

  • Effective Preparation
  • Effective Learning
  • Effective Communication
  • Effective Documentation
  • Effective Search
  • Effective Analysis
  • Effective Collaboration
  • Effective Research

To execute these activities most effectively, you need to clearly define each.  You need to know and understand what Effectiveness means for every one of them.  You need to know which of them are more vital to the end result.  Spending too much time and/or effort on less vital activities will affect their proverbial strength as a link in the chain.

Tasks

Tasks are what customers hire us to do for them.  Each of these tasks breaks down into many more “sub-tasks” for the lack of a better term.  However, the three tasks listed below capture the essence of the services we deliver.  Their effective completion is the ultimate contributor to us being most effective as a whole.  Database availability and performance directly depend on how well we proactively monitor and maintain the databases under our stewardship.  Furthermore, effectively preventing and resolving problems enhances our effectiveness.

  • Effective Database Monitoring
  • Effective Problem Management (prevention and resolution)
  • Effective Database Maintenance

I hope this post gives you a better sense of our Key capabilities and how we go about them.  In my next post, I will delve into the Capability Maturity subject.  Thanks for reading!

The BEST is Yet to Come!

Epi Torres, CEO

RDBAELOGO

Key Capability Maturity Optimization: Key Capabilities is a post from: Remote DBA Experts