Oracle Openworld 2011 afterthoughts

Oracle OpenWorld  Came to a close Oct 7. Unfortunately too many issues prevented me from posting anything to this blog.
This year was the Year of the Cloud at Oracle Openworld.  Oracle released many products including Exalytics, The Oracle Database Appliance, The Big Data Appliance,  Oracle R Enterprise , Oracle Enterprise Manager 12C  (Cloud Control) and Oracle in the Cloud. These were just the big announcements but there was alot of fun and learning this year.  The RAC Attack OTN Lounge was the best place to hangout since there were so many people to learn  from . It is always interesting to meet people from All Aorund the world with a variety of experiences and exposure to so many technologies.

The Bloggers meetup was the culmination and the meeting of all the minds from all over the Oracle Community . Sponsored by the Good old folks at Pythian . This was a very well done and fun event.
My takeaways and the most exciting thing for me has been EM 12C . I plan on playing around with it and understanding how it can benefit me and my team.
It was very sad to be in CA when Steve Jobs died and  hearing about it during Larry's big keynote. Truly an inspirational person.
Some funny some Sad pictures from my Days at Openworld .

Oracle Openworld day 1

Day 1 here was IOUG Day.
There were a lot of good  sessions .

One of my Favorite for the Day was Arup Nanda's Exadata for DBA's . It was  very well presented   and Arup has already posted the details at his blog.
The RAC Expert Panel SIG was very well done and it seems like most of the questions were related to 11.2.0.3 which was just released Last Week.
The Announcement of the Day was the new member of the Exa family #Exalytics. Exalytics is a BI optimized Server. Larry Ellison announced the new addition to the Family along with his familiar Next Slide Please.
Since BI has not been my area. I'll defer to the pros like Alex Gorbachev to explain and the details are on Alex's Blog.
It Seems Like there will be a flurry of announcement this weeks and stay tuned on this Blog and My twitter Feed as well #oow11.
Some Pics of Day 1 are below for #oow11

Oracle Openworld 2011 My Schedule

Below is some of the sessions i intend to attend this Openworld 2011

Oracle openworld day 0

Day 0 of oracle openworld is here . Tomorrow the fun begins . Got my registration done and got to see Eddie Awad , Chris muir, Tim Hall , Arup Nanda.
Hoping to meet up with more fellow bloggers soon.
I will mostly be in RAC related sessions tomorrow.
Here are some pics of the registrations.

Oracle openworld 2011

Oracle openworld 2011 is almost here . Starting Sunday I will be very active in tweeting as @fuadar an blogging via this blog . This is an exciting year to be at openworld and hopefully there will much to report see and publish

Compatibility parameter and testing

As with any upgrade it is very important to have as proper test plan to test your application prior to a database upgrade. DBA's try to work with their application teams to ensure that the test are fair and balanced and go thru many weeks of planning before actually upgrading a major system.
One thing many DBA's do  as  a precautionary measure is to ensure that a backout plan is in place. For most it might includes backup, flashback or both. Once other medium of protecting your self is the compatibility parameters. After an upgrade the compatibility parameter is most often not changed till the application is stable. The compatibility parameter to a lower value allows for  potentially downgrading your database.
But what if your application testing was done with the compatibility parameter not set to the right version. This can cause a lot of headaches for the DBA's and the Application teams that are testing/
Compatibility parameters not only change the redo logs and the control file structure but also allow for oracle to take new code paths. Something that was very painfully evident to us when we migrated from 10.2.0.4 to 11.2.0.2 RAC. The compatibility changed actually caused us a lot of instability due to some new processes that oracle introduced. This  change caused us to go from a fully stable system to crashing down every couple of hours.
Unfortunately there is no easy remedy for changing the compatible parameter and we had to work thru the issues. A lesson well learned for us. Testing needs to be done with compatibility changes done  on dev and test to ensure process behaviour does not change

GTX Processes in Oracle 11.2.X

Oracle Has introduced new processes in Oracle 11 and above to handle XA type processes.
The Parameter global_txn_processes control is you want GTX processes to spawn or not.
If global_txn_processes >0  oracle will autotune in 11.2.0 the amount of gtx processes it needs per node.
these processes are normally used for distributed transactions and for XA based transactions. if you are using the OracleXA dataclass in your jdbc driver. you are probably using DTP or distributed transaction processing.

RAC Attack at Oracle Openworld

This year among many things the OTM Lounge will be host to IOUG RAC Attack
Jeremy  has information on this wonderful event at this blog.  Also if you have experience in RAC installation . please hit up  the Rac Attack  webpage for more info.
Hope to see everyone there.

The Arrival of #OOW11 and Festivties galore

Its that time of the year. Oracle OpenWorld 2011  starts in a few weeks.
Oracle Openworld  is host to an insanely massive amount of DBA's architects , developers and vendors. It is a massive Opportunity  not only to learn from peers but network with experts, Oracle ACE's and ACE directors.
While i will be posting the sessions i will be attending in a separate post i just thought I'd point out to some of the festivities  Like the Bloggers Meetup that is probably the best place to meet the greatest minds that have a real job working on Oracle related Technologies . The Details are on the Pythian Blog   and can be read in detail on pythian's blog . An excerpt from the pythian blog is below

"What: Oracle Bloggers Meetup 2011

When: Wed, 5-Oct-2011, 5:00pm

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103"

The Other Festivities include the OTN night Details on the OTN Blog. But one thing is good OTN Lounge is going to be in Howard Street which is awesome again. 

One Cool Thing on the OTN Lounge will be RAC SIG stuff

"(Special treat for Oracle RAC fans: Kick back at the OTN Lounge between 9am and 1pm each day of the show with experts from the IOUG RAC SIG, and work together to get an Oracle RAC cluster database running inside virtual machines on your own laptops.)" --From OTN Blog

I' am also hoping Oracle Closed World returns since i was unable to attend any #ocw sessions last year. Thati s a festive geek fest in itself.

Come Join us at Oracle Openworld for lots of fun and learning. and do introduce yourself.

i'm @fuadar on twitter  and #OOW11 is the official tag of Oracle OpenWorld 2011

 

AIX Bug with 64K Memory Pages

After our Production Upgrade to 11.2.0.2 on AIX. we hit an AIX Bug relating to  AIX starving Oracle from memory pages and thus grinding the system to a halt.. Metalink Doc  1088074.1 talk about this but there was conflicting information thru Doc ID 282036.1 which stops at AIX 6.1 TL5 for this particular bug.
It is the Bug is actually fixed in TL6 SP4 .
We were on TL6 SP2 and thus had a system outage.
Moral of the story is it always pays to ensure to look at future TL's and SP's for AIX to ensure the bug was actually fixed.

Book Review: Expert Oracle Goldengate

I was recently provided a copy of Expert Oracle Goldengate for review. The Title Written by Ben Prusinski . Ace Director. Steve Phillips and Richard Chung.
The Book starts with a very well written introduction discussing replication technologies , Their Application  and their evolution from Oracle Basic replication to Oracle Streams. This is a very good refresher for people who want to get into replication technologies to understand and seethe different options available in Oracle . I did feel that the authors could have added more to the introduction section but seeing that it was just an intro chapter  the length was ok.
Chapter 2 deals with mostly how to download and install the software. The problem with such chapters is that they get dated pretty quickly not only becuase Oracle likes to switch their OTN links on people. The Software updates can cause the chapter to become obsolete fairly quickly but never the less it provided detailed screenshots on the download as well as example configurations and  complete installation steps for various platforms.
Chapter 3 is where you start getting absolute value from this book. This Chapter is very well written and goes into a lot of details on how the golden gate architecture . The Authors have tried their best to explain the architecture in excruciating detail.
The Other chapters that makes this book a must have for any goldengate administrator or user include The Chapter on Tuning, Disaster Recovery options and Zero Downtime migration replication.
I'm Planning on keeping this book as a reference on my book Shelf and would recommend this book to anyone using Goldengate.

Kfed saved the Day =ASM Disk Header recovery

I just wanted to start this Blogpost as a quick note to myself to investigate and learn about kfed.
I was hit with a situation this morning where during a system migration the ASM headers were overwritten/blanked out in AIX. this was done with a process that is used to provision new ASM Luns in our environment
In AIX you can use
lquerypv -h to see the header of the disk which tells you that it is an ASM disk and what Diskgroup is attached to . if the pvid is cleared from such a disk after the header is written it will clear the header thus allowing for reuse of the disk.
we were stuck with such a situation with my database backup on the same disk and no metadata backup at all. ( Shame on me for not setting up these vital components early enough). This caused panic and chaos . A colleague googled thru and found references to kfed Here , Here and Here.
We looked at all the disk headers using lquerypv and then were able to use kfed  repair on each disk to get the headers back.
There aredetails  about  kfed in the links above and it is still and undocumented oracle utility so use with care. All i can say is kfed saved the day for us in a situation where we were in no hope of recovery.
A nice tool to have and learn about
 

Small Changes can Break Stuff i.e 11.2 ASMCMD

This is a going to be a small post justo to reiterate the fact that Oracle Keeps changing things on us to keep us moving .

In Oracle 11 ASMCMD introduced md_backup and md_restore to help with managing ASM Metadata.

To script around this and make our scripts universal we used


asmcmd -v to detect version and then proceed with backups.



So in 11.1.0.7

oracle@abc:$ asmcmd -v 
asmcmd version 11.1.0.6.0 

When i migrated to 11.2.0 for ASM my scripts for metadata backup Started failing

running the same command in 11.2.0 netted

oracle@abc:$ asmcmd -v

WARNING: Tracing levelnot specified

Default level of tracing is enabled.

ASMCMD> exit

In 11.2.0 to get version from ASM is

oracle@abc:$ asmcmd -V

asmcmd version 11.2.0.2.0

Not sure if this behaviour changed in 11.2.0.1 or 11.2.0.2 .


Although it would be nice if Oracle would not reuse the same flag for something else just makes our lives and scripts miserable.

Relinking causes many warning on AIX for 11.2.0

As i  started applying the first PSU to my 11.2.0.2 instances. I noticed OPatch generating crazy Ld messages

ld: 0711-319="" defined:="" exported="" freeinstance__14txsoqmdm10gtagfv="" not="" symbol="" warning:="">
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__17TxsOqSQLConstantsFv
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__19TxsOqPatternMatcherFv
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__19TxsOqSnapshotCasterFv
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__21TxsOqExecutionFactoryFv
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__21TxsOqRollupGIDManagerFv
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__22TxsOq11gPatternMatcherFv
ld: 0711-319 WARNING: Exported symbol not defined: freeInstance__24TxsOqSQLDerivationFinderFv

This is a Bug and is explained in Relinking causes many warning on AIX [ID 1189533.1].

Apparently all these messages can be ignored

Oracle 11.2.0.2 Memory Bug

my installs for 11.2.0.2 were going on . I noticed something peculiar . The Memory utilization on my AIX Box was more than 10.2.0.4 . I looked at it and said well Maybe 11.2.0.2 needs more memory and brushed it off. That was till i read kurtvm's blog.
Apparently there is a bug

This is apparently a bug

From Kurtvm's Blog 

Bug 10211065.-P Base Bug 9796810

Abstract: MEMORY FOOTPRINT FOR DEDICATED SERVER PROCESSES MORE THAN DOUBLED AFTER 11G UGP

  Bug 9796810.-P Base Bug 10190759

Abstract: 11G SERVER PROCESSES CONSUMING MUCH MORE MEMORY THAT 10G OR 9I

 

Bug 10190759.-P

Abstract:  PROCESSES CONSUMING ADDITIONAL MEMORY DUE TO 'USLA HEAP'

 

 

The good news is - there's a 'interim' patch - number 10190759.

On to patching my environments.

Upgrade From Oracle 10.2.0.4 to Oracle 11.2.0.2

Below is a simple GUI Method to upgrade your Database from 10.2.0.4 to 11.2.0.2
As part of the upgrade Process from 10.2.0.4 the First Step will be to check the Database for issues.
The Process will be to first install the 11.2.0.2 Software. And the run the @utlu112i.sql which is available in your new Software Home $NEW_ORACLE_HOME/rdbms/admin
Once you run this against your old system
You will see a result like this
-- Start Oracle Upgrade Tool --

Oracle Database 11.2 Pre-Upgrade Information Tool 02-23-2011 13:44:07
Script Version: 11.2.0.2.0 Build: 001
Database:
--> name: DTESTDB
--> version: 10.2.0.4.0
--> compatible: 10.2.0.4
--> blocksize: 8192
--> platform: AIX-Based Systems (64-bit)
--> timezone file: V4
Tablespaces: [make adjustments in the current environment]
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 2635 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 440 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> UNDO_01 tablespace is adequate for the upgrade.
.... minimum required size: 32 MB.
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
Remove all Parameterw listed in here
.**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
Check If anything is Invalid .
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 7 INVALID objects.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
--- End Oracle Upgrade Tool -----
After The Process is complete . Start ./dbua

As part of the Upgrade you have an option to Move the datafiles. In this we have chosen no



If you are fine with this Please Proceed . it is recommended that you upgrade your TimeZone File

Please Select Next and A warning will show up if you have decided not to turn off archiving

Select the new Values for you FRA & Diag Dest

Select Grid Control of DB Control Options

Add in the Passwords

It will provide with a summary

Oracle will now start the upgrade Process

This will complete and

Technorati Tags: oracle

Oracle 11.2.0.2 Software Install Guide

In the second part of the series of Installing Oracle Software here is the Installing the Database Software for a single instance system

This Document will graphically explain the Installation Process for Oracle 11.2.0.2 Database . Oracle 11.2.0.2 Database is used to either create a new Oracle Database or upgrade an existing Database.
The Images/Installation were performed on a system running AIX 6.1.6.2


The First step is to run rootpre.sh. If you have ASM . ensure the Grid Infrastructure guiide is followed first.  if rootpre.sh fails due to clusterware running and you have upgraded ASM to 11.2 . You can  ignore the messages and then  execute ./runInstaller  --- Please note setting up X is not in the realm of this document.

 

After you pass the initial screen. Select do not install updates and do not type in your Oracle Support ID and password.

 

The Second Screen Will provide you the following options. You can select Software only if you intend to do a  software install for upgrade later.

Once you Select  Install you will get the Screen below below .  Our Goal is to install  Software for a single instance based database & Select English as your language

The next option talks about the editions of Oracle . Based on your license agreements. Select the appropriate version. We will select Enterprise for this installation. Please note the space requirements are also listed below

 The select options screen at the bottom allows for selecting specific options. Select the  options appropriate to your needs.

Next screen you will ne asked to enter ORACLE_BASE and Software Home Directory

If you select a home directory that is the same as the user directory if will prompt youand let you know that Oracle Recommends not using the User Home Directory for the ORACLE_BASE .

 

You can say yes if you are sure .

Next Step will ask you for OS Groups. Please follow your standard  security practice for selecting OS Groups . For  purposes of this install i will select dba for all groups.

 

After This the software will start checks to ensure all OS Level parameters requirements are met. The Recommendation is to always use RDA first .  ./rda.sh –T hcve will give you a good overview of your system and ensure that the prerequisite checks are met prior to starting a software install.After the checks Oracle will now provide a script that will work on fixing the prerequisites that are missing .

After the fixup scripts are run in  conjunction with the sysadmins.  run Check Again and  reso,lve any ourstandign issue. If issues are non mandatory you can check ignore all and continue

After making sure all the values are right Select next and the install process will start

On AIX 6.1 You might Hit bug #Bug:10259393  talked about in ID 1264074.1 . An AIX APAR will be required based on the version . THE AIX APAR might be different  based on your configuration For AIX 6.1 TL6, IBM have released fix IZ88711:

If you get an Error during Linking that has similar attributes as described in the above note just Press continue . Relinking the agent is explained in the doc above after the APAR is installed.
After the install Has completed . Run or have your Sysadmins run root.sh and the software install is complete.

You have now completed the install for Oracle 11.2.0.2 Software .

Publish Post

New deinstall utility in Oracle 11.2

Oracle 11.2 Now does not allow you to deinstall software via the OUI interface directory . Any Requests for software deinstallation using the OUI will tell you to use the deinstall utility

There is a new utility know as deinstall available in $ORACLE_HOME/deinstall that allows for deinstallation

$ORACLE_HOME/deinstall/deinstall will allow for deinstallation in the form shown below

The deinstall process will cleanly deinstall the software and detach the Oracle Home.


######ORACLE DEINSTALL & DECONFIG TOOL START ############

Checking for existence of the Oracle home location /oracle/product/11.2.0/db_1
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: //oracle
Checking for existence of central inventory location /oracle/oraInventory


Install check configuration END


Skipping Windows and .NET products configuration check


Checking Windows and .NET products configuration END



Network Configuration check config START


Network de-configuration trace file location: /oracle/oraInventory/logs/netdc_check2011-02-14_10-32-02-AM.log


Network Configuration check config END


Database Check Configuration START


Database de-configuration trace file location: /oracle/oraInventory/logs/databasedc_check2011-02-14_10-32-02-AM.log


Use comma as separator when specifying list of values as input


Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END


Enterprise Manager Configuration Assistant START


EMCA de-configuration trace file location: /usr/local/oracle/oraInventory/logs/emcadc_check2011-02-14_10-32-12-AM.log


Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : //oraInventory/logs/ocm_check9011.log
Oracle Configuration Manager check END

Grid Control targets losing Config information

ew year New interesting issues.
This time we had a fellow DBA come to discuss  why this one particular database keeps  moving bad to metric collection error with database not configured anymore.

The Agent logs $AGENT_HOME/sysman/log/emagent.trc showed for this target
011-01-03 02:36:38,423 Thread-3643 WARN  vpxoci: OCI Error -- ErrorCode(1008): ORA-01008: not all variables bound
On doing some more  debugging. this issue  showed up to be a specific issue  with Oracle 10.2.0.3 and cursor_sharing as  described in  ID 422118.1.
The Workaround is to change the cursor sharing from similar to exact and flushing the shared pool.
Please also Note in Oracle 11 Cursor_sharing=similar has already been deprecated per Note # 1169017.1

Virtual Box 4.0 Is Out

Although the check for updates didnt work . Virtual Box 4.0
http://www.virtualbox.org
Change log is here
http://www.virtualbox.org/wiki/Changelog
Enjoy the upgrade

Grid Control 10.2.0.5 AIX Alert

If you are using Oracle Enterprise Manager Grid Control. Please be  aware of a security certificate expiring on Dec 31. This is critical since your agents will not communicate to the OMS thus causing issues in your monitoring infrastructure.
From the Oracle Note
"What is the Issue?
Enterprise Manager Grid Control installations configured to use the Secure Sockets Layer (SSL) protocol utilize a self-signed Certificate Authority (CA) which is stored in the Oracle Management Repository.
For the 10.2.0.5 release of Grid Control on AIX 5L, this Certificate Authority will expire on 31-Dec-2010 00:00:00, which will cause SSL authentication to fail.. This issue affects Oracle Management Service (OMS) 10.2.0.5 on AIX 5L, as well as SSL communication from all new 10.2.0.5 Management Agents on AIX 5L (regardless of OMS platform/release) added after 31-Dec-2010 00:00:00.
Note that this communication issue is specific to Enterprise Manager Grid Control release 10.2.0.5 on the AIX 5L platform only."
Please refer to MOS Note Note 1171558.1 for more info.
Update: I just added a copy of the Email we recieved. Seems like people didnt know about this . Not sure how MOS targets its emails for alerts like these. GC_Alert_OCT-10

Book Review - SQL Pocket Guide By Jonathan Gennick

SQL Pocket Guide by Jonathan Gennick is book that addresses SQL across multiple platforms. The Book tries to talk about SQL syntax across the major databases, which include Oracle 11Gr2, DB2 9.7, Postgres SQL and SQL Server.  Not All SQL is standard and variations of SQL in terms of Syntax are clearly highlighted and explained. The Book is written by Jonathan Gennick whose previous titles include SQL* Plus a definitive guide, Books on SQL Loader & regular expressions.
The SQL Pocket Guide is not a guide that is meant for cover-to-cover reading. This Book is rather a keep for the times when you need to find examples for SQL. Since my work with SQL has been for Oracle based Databases. I was intrigued by how SQL compares between Database vendors. E.g., CAST function was explained and pros and cons explained were brought to light that SQL Server has a better function called CONVERT that does a better job than CASE.
In This Age of Google searches and trying to find solutions in form of a web search, I feel Books Like these still play their part and should be in the arsenal of every Database Professional.  While I did not   read the entire book, the sections I scanned thru and the statements I used were definitely well explained and examples were of good value. The Book provides and easy reference with good online examples that can be downloaded and used to test syntax.
Further Book Details can be found here.

Querying EM Grid Control data

EM Grid Control provides lots of benefits in  the enterprise. Most people discuss Grid Control in the context if Diagnostic Packs and Tuning Packs which Grid Control has a nice interface to, but Grid Control  has  a wealth of other information  built into it.
I was recently asked by a colleague to help him find all the Listener ports that were being used in the Enterprise .
While Grid Control Doesn't provide a default  report for this . All the data is available in the Grid Control repository. A Simple Table Based report based on the below query can be used to get that data

SELECT mgmt$target.host_name,mgmt$target.target_name,mgmt$target.target_type,mgmt$target_properties.property_name,mgmt$target_properties.property_value
FROM mgmt$target, mgmt$target_properties
WHERE (mgmt$target.target_name = mgmt$target_properties.target_name)
AND (mgmt$target.target_type = mgmt$target_properties.target_type)
and (mgmt$target.target_type='oracle_listener')
and (mgmt$target_properties.property_name='Port');

The Above SQL Will Display all Listener's and their ports.
the MGMT$target and mgmt$target_properties can provide and great deal of other options and reports based on Block Sizes and other parameters set can be used to quickly find issues in your enterprise.
P.S The above query is based on the Grid Control 10.2.0.5 Repository

PSU and Upgrades

While upgrading  one of the instances on a server . We hit an error
ORA-00600: internal error code, arguments: [kccgtt_1], [3], [], [], [], [], [],
The Error was interesting and points to an aspect of the prechecks that we hadnt explored before. Differences in PSU Levels between Homes. The Database Home was installed recently and since it was a new Home the latest PSU was applied. What wasnt done was checking the ASM Home for the PSU level. Turns out ASM Home has 11.1.0.7.2 vs Database Home having 11.1.0.7.4. ASM has to follow the rule of Same or Higher patch level the the highest level of Database version.
Once ASM was Patched All was well but it just adds another layer of checking that DBA's have to do when installing or Upgrading Oracle.

11.1 DBUA & Blanks/Spaces

As part of a routine upgrade to 11.1 we decided to use dbua to complete all the upgrade tasks. Dev  & test were a breeze but the DBUA screens on Production came with a Big OK Sign  with no info whatsoever. It was kind of annoying but after digging into $ORACLE_BASE/cfgtoologs/dbua/logs  was able to Find the error to be
java.util.StringTokenizer.nextToken(StringTokenizer.java:259)
oracle.sysman.assistants.util.attributes.InitParamAttributes.loadInitParams(InitParamAttributes.java
:1763)
oracle.sysman.assistants.dbma.backend.Database.updateNonDefaultInitParams(Database.java:1068)
oracle.sysman.assistants.dbma.backend.CompManager.obtainDatabaseInformation(CompManager.java:2272)
Metalink search came up with a error in the DBUA utility for 10R2 (Oracle Please update that this issue exists in 11 also).   Note id # 444148.1 describes the situation as a parameter that have single quotes.
in our case i had smtp_out_server=' ' . This cause dbua to unable to parse the spfile.
Solution was to create a pfile remove this parameter and proceed. DBUA Proceeded to next step after this

Physical Standby Controlfile recovery when structures are different or

So this Thursday we were doing some application testing on our physical standby and after the testing was completed, My Backups kicked off while i was flashing back the Database. This caused the Controlfile to have an invalid entry with the ckp_time being null. The problem with an invalid entry int eh control file was that Oracle RMAN was not able to run an implicit resync catalog from the standby. Our Configuration has a primary and a physical standby that is used for backups so the backups were failing which is a problem.
A resync catalog would work for the primary so the issue was isolated to the physical standby. I thought hey that's simple , just get another copy of the control file from the primary using
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stdbycontrol.ctl';
sftp the file
and then run
RMAN> restore controlfile from 'stdbycontrol.ctl;
and all will be good.
While the assumption was right . I was missing important components . Structure,OMF & ASM.
The Standby was originally created from a Filesystem Based Database which was later converted to ASM. Ths meant the structure of the primary and standby were very different. Even after the primary was converted into ASM OMF and standby_file_management=auto meant that  OMF would kick in and all files created in the primary will not get the same  file name on the standby. This caused loads of headaches for me and i was glad i had a backup.
The Only Good thing was that the File number matched on both primary and standby.  This Help me Build a SQL File to
SQL > alter database rename file  ' File name of primary   database'  to ' Files Name of Standby database';
The Way to do this the fastest was to dump the V$datafile from the old controlfile on the standby . Load it into the primary and run a select like this
SQL> select 'alter database rename file    '  chr(39)||a.name ||' chr(39)'  to    ' chr(39)||b.name||chr(39)||';' from  v$datafile a stdby_datafile b where a.file#=b.file# order by file#
This generated a quick and dirty  datafile  renaming script to get me up and running as soon as the standby controlfile was restored.
A painful process but definitely faster than restoring the whole database.

Oracle OpenWorld Blogger Meetup 2010Oracle OpenWorld Blogger Meetup 2010

he venue was Jillians, The Host was Pythian, The Sponsors were HP. There was competition. Sounds like an Action Thriller but no this was the Oracle OpenWorld 2010 Bloggers Meetup.
The Shirt Competition was a repeat of Last Year but was a lot of fun. Asif Momen Tool the Prize With Paul Nash Being the Runner Up.
The Meetup has a second portion to it . Unfortunately due to personal commitments i was not able to meet the deadline but here is the People I met.
1. Alex Giorbachev - CTO Of Pythian - RAC guy
2. Paul Valee - CEO Of pythian
3. Marc Fielding - Exadata Dude at Pythian.
4. Dan Norris - Dan The Exadata Guy works for X-Team at Oracle.
5. Sheeri Cabral - MySQL Lady
6. Chet Justice - The Infamous Oracle nerd
7. George Woods - DBA from Pittsburg.
8. Iggy Fernandez - THE SQL Dude
9. Phillip Muwai - From Kenya Work in the Govt Sect.
10. Jared Still - Original moderator of Oracle-l
11. Tariq Farooq - Some Dude from Brain Surface.
12. Gwen(Chen) Shapira - Awesome DBA from Pythian. Very Good Presenter.
13. Leighton - Oracle Db Guy
14. Jeremy Scheider - Ardent Perf . Another Fellow Chicagoan
15. Martin Nash - Works For HSBC . Loves Performance bottlenecks.
16 . Karl Arao - DBA
17. Riyaj Shamsudeen - Texas bound . Crashes is his speciality
18. Mohan Dutt - aka OCP Advisor
19. Richard Foote - Index is his middle name
20. Meg bear - Apps , Apps & Apps
21. Kevin Closson - NUMA and Exadata
22. David Haimes - Another Apps Lover
23. John Piwowar - Incharge of Oraclenerd during Openworld
24. Kim Njeru - Napster. The music you stole he probably knows about it
25. Rob van Wijk - Ruby On Rails
26. Tim Hall - Oracle Base
27. Brian Huff - Bex
This is not even close to a complete List and i do have ot go back and the twitter/blog Addresses for each of the above

Oracle Openworld 2010 oDay .5 roundup ad some useful info

Saturday technically was the day before Oracle Openworld Starts but the tweets and the Blogs have Started Coming through.

The First Announcement that was tweeted was from @krisrice and @oracletechnet about SQL Developer Data Modeller now being free. Costing about $3000 per seat that is a significant  early announcement that it will be free to all Database Customers. Sql Developer can be downloaded here. It Does seem like the pricing page has no0t been updated yet since it still lists the same price.

THis Year there are festivities in the Mason Street tent as Wellas on Howard Street .  There is a Sunday Night Keynote Party at Mason Street tent Details here. If you are a regular to Openworld you will not the  OTN Lounge at its regular spot In Moscone. OTN Lounge as moved to Mason Street Tent , Details are Here.

you cam follow hashtag #oow10 on twitter to see the updates . With Iphone 4 i might be qik'ing at http://www.qik.com/fuadar  . enjoy the madness that is Oracle Openworld  2010.

Oracle Openworld 2010. Get your apps on

For this first time this year. Oracle has released not one but 2 Oracle Apps for Various Smartphones.
I have downloaded the Iphone versions of the Oracle Openworld App and the Oracle Now App.
Both Apps are a good first try. The Openworld App even got its first update in under 24 hours which was impressive. The accessibility to schedule builder is nice but the missing link is adding the schedules into the iphone calendar for easy access. The Schedule builder access was still slow but manageable. Hopefully schedule builder can handle the load  this year. It suffered outages alot last year at openworld. The Other Features like opentable . show your badge is also nice and eayily accessble and social media tab has all the big players. It would have been nice to add a location based service to allow checkins into each conference room. The Openwoeld app crashed every so often on my iphone 4 and i think it does not use location services or push services to push your next session to you.
The Oracle Now application while has an Openworld Component with the QR codes is more of a new and information app and i guess will find more use overall even after Openworld. The New And Events tab are nice.
Both the Apps are still nice and will find a lot of use During Openworld.

Interesting Observation regarding ASH in 10GR2

was looking thru some reports on a time period when we has Database Contention . I noticed something really Odd in the ASH reports
I have one index that was the top DB Object w.r.t to Db sequential file read. I wanted to move that index to  a different tablespace anyway so i rebuilt the table. What i noticed on the ASH report was very interesting
Prior to my Index Rebuild the ASH report

Top DB Objects

• With respect to Application, Cluster, User I/O and buffer busy waits only.

Object ID	% Activity	Event	% Event	Object Name (Type)	Tablespace
135617	13.76	db file sequential read	13.71	INDEX1 (INDEX)	X_BPGEN_50M
378375	1.20	enq: TX - row lock contention	1.06	TABLE1 (TABLE)	D_PAT_AL1
86047	1.16	db file sequential read	1.16	TABLE2	D_PAT1

Top DB Files

• With respect to Cluster and User I/O events only.

File ID	% Activity	Event	% Event	File Name	Tablespace
124	11.70	db file sequential read	11.54	+DATA02/xyz/datafile/x_bpgen_50m.691.727601609	X_BPGEN_50M
146	2.39	db file sequential read	2.18	+DATA02/xyz/datafile/x_bpgen_50m.550.727596551	X_BPGEN_50M

After the Index Rebuild the ASH Report for the same time period

Top DB Objects

• With respect to Application, Cluster, User I/O and buffer busy waits only.

Object ID	% Activity	Event	% Event	Object Name (Type)	Tablespace
135617	13.76	db file sequential read	13.71	INDEX1 (INDEX)	X_MISC_AL
378375	1.20	enq: TX - row lock contention	1.06	TABlE1 (TABLE)	D_PAT_AL1
86047	1.16	db file sequential read	1.16	TABl2	D_PAT1

Top DB Files

• With respect to Cluster and User I/O events only.

File ID	% Activity	Event	% Event	File Name	Tablespace
124	11.70	db file sequential read	11.54	+DATA02/xyzdatafile/x_bpgen_50m.691.727601609	X_BPGEN_50M
146	2.39	db file sequential read	2.18	+DATA02/xyzd/datafile/x_bpgen_50m.550.727596551	X_BPGEN_50M

While the Top DB Files remains the same the top DB Objects now has a different table space not because the table space was that at the time of the problem but because it seems oracle is performing a real time query to determine what the current table space this. this can be confusing and misleading since the top db files and the top db objects now no longer match.
Anyone seen this or have any explanation of why Oracle would be doing this.

Bloggers Meetup 2010

The 2010 Oracle Openworld Blogger Meetup has been announced.  Details are here. it was loads of fun last time . Here's my shirt from last year. Do you still have yours ?.
From The Pythian Blog
When: Wed, 22-Sep-2010, 5:30pm
Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.

Oracle openworld 2010 preparations underway

So Oracle Openworld starts sep 19-23 But the preparation are already underway with the Schedule Builder going Live Yesterday.
The Official HashTag for Openworld 2010 is #oow2010 and @oracleopenworld can  be followed to get all the updates.
George Woods has started his yearly Tweeters Attending Blogpost here and i'm not many more resources will popup as OpenWorld Nears.
This Year Oracle OPenworld is expected to  be be massive with Java One Also added to the mix.
I am going to be Down in SFO this year and hoping to meet people as well as learn new stuff.
More updates and my schedule as i finish it up .

Oracle Installer Bug on IBM POWER7 Hardware

Oracle 11.1.0.6 and 11.1.0.7 are impacted with this weird java core dumping bug. This seems to a POWER7 related issues.
MOS Doc id 1065579.1 OUI crash observed with IBM JDK/JRE 1.5 SR4 on AIX when running on the new POWER7 based hardware
details the bug and provide the patch to download and use to resolve this issue.

Oracle Dataguard broker & CFC Part 2

n an earlier post i mentioned how Oracle Dataguard Broker does not support a cold failover cluster like HACMP. Well turns out Broker can be used on a Cold Failover Cluster.
There are a few simple steps that needs to be followed to make the data broker  be able to function.
On the primary

alter system set local_listener=;

This will ensure that the database knows about the listener that is running on a virtual host name.
After this step is completed Follow the steps in Metalink Doc # 808783.1
I'm listing the steps below

ALTER SYSTEM SET DG_BROKER_START=FALSE;
 SQL> show parameter dg_broker_config_file
--- remove files that are displayed on the primary and the start broker on both the primary and the standby
SQL>ALTER SYSTEM SET DG_BROKER_START=TRUE;

After this connect to Dataguard Manager

DGMGRL>CREATE CONFIGURATION 'DGCONF' AS PRIMARY DATABASE IS 'PROD' CONNECT IDENTIFIER IS PROD;
DGMGRL> ADD DATABASE 'STDBY' AS CONNECT IDENTIFIER IS STDBY MAINTAINED AS PHYSICAL; --or LOGICAL
DGMGRL> ENABLE CONFIGURATION;
-- validate
DGMGRL> SHOW CONFIGURATION;
--Success means all is good

The above commands are from the Metalink Doc above and work perfectly for a CFC . We are now able to failover our clusters and have Dataguard broker work for us to manage our configurations.

Oracle ASM (Automatic Storage Management) 11.1 with a 10Gr2 database

The last couple of weeks I've been working on converting on of my Databases to ASM. As databases grow adding more disk breaks performance if file systems are being used at many levels and to control performance Oracle released ASM which would provide RAW disk performance with dynamic re-balancing. This was precisely one of my use cases. So After performing my due diligence (dev testing) and so forth. I spent the last 3 weeks slowly converting the database to ASM. There were 2 reasons for the slow conversion. one i didnt have the diskto be able to convert it all at once and the second was gettin enough downtime to perform the conversion was a challenge.
Everything went really well till week 3 when the conversion completed . I asked my Sysadmins to give me the last chunk of disk to add to my disk group and executed a power 1 re balance. All went well for 5 days until i got a call from our support informing me of ORA-600[kffmUnlock_3] followed by an instance crash. Instance crash is always something to be worried about but oracle over the years is resilient enough for such issues. The database came up only to crash again in 9 minutes. Why 9 minutes because that was the total time that the re balance operation had left.
Oracle does have a note here that explains the whole situation . There is a apatch but i opted to shutdown the database and allowed te rebal;ance to finish. Since this was the last set of disks for now I'm guessing  i will not hit this issues and plan to test the patch and  have it in production before then. TIll then i hope all is well for this database.

Goodbye 2009 and the end of the decade.

he decade that started with the Y2K is finally ending this week. it has been an interesting decade for me with my move from Pakistan to the US in 2001.  This decade has brought towards a lot of technological advances including Blogging, tweeting, Qiking/Ustreaming, Boxee and Xbox .
Being an Oracle DBA. Oracle has changed in this decade from 9.2 to  Oracle 10 and the promise of a self fixing database. Automatic Storage Management (ASM) and the potential power sharing with the SAN folks.  Self tuning Databases in the form of the STA (Sql Tuning Advisor) . Oracle has worked hard toward getting a viable monitoring product that now challenges all the incumbents in the form of Oracle Enterprise Manager Grid Control. Active Dataguard was built to solve the challenges of Logical Dataguard while offloading backups from the primary systems.
This is also the decade where Oracle decided to make more money from its customers with the concept of a pack. AWR  & ADDM which were replacement for Statspack are now the Diagnostic Pack. STA is now part of Tuning Pack. and to blackout  a database in Grid Control you need to purchase a diagnostic pack.
The end of the decade also brought forward  My Oracle Support  (and the demise of our beloved metalink) and 11.2 and Oracle's Ambition to create a global problem management database that would proactively inform customers of problems based on other people that already had that problem . Oracle had been trying to push OCM  for a while now and Larry Demoed the next version of Grid Control at OOW2009  that had this built in.
This decade had brought forward war and promise of change .  This Decade brought forward the Iphone , wich for me was the biggest technological feat. Today i am addicted to the iphone even though i was never a Mac fan, My next hardware purchase will probably entail a Mac.
I've had the opportunity to work  on great new technology and meet and tweet with a great many friends and people that are far more knowledgable than myself. Which is why in ending my new years resolution is to keep myself in technology and keep pusing myself in newer technologies.
What are your resolutions and what was the defining moment / feature in Oracle for you this decade.
Happy New Year to all and Please enjoy JibJab's Year in review

Try JibJab Sendables® eCards today!

Copyright and Software License Agreement (Buyer Beware)

------
I was really baffled today when on moving to a newer laptop i was unable to install Self Test Software for 10g that i have had for a number of years . I have liked some of the content and have used it for reference when needed. Today when i transferred stuff to my new laptop i was unable to activate a product that i paid for .
The customer Service rep politely pointed me to the software license agreement that apparently i had accepted in 2006 when i bought the product.
The License agreement states
"The term of this Agreement begins upon installation of this Software and shall terminate 3 years from the date of purchase. STS reserves the right to terminate this Agreement at any time if you violate any provision of this Agreement. If the Agreement is terminated for any reason, you agree immediately to return to STS all copies of this Software and all accompanying items. "
SO even though i own a piece i dont own it because it was only given to me for 3 years. While i agree to most people Oracle 10g test questions might not be valid after 3 years but i feel that i should own a product and have the right to use it if i paid for it . The Company can desupport the product but it should be on the discretion of the customer as to when he stops using it.
Just a Buyer Beware rant on software licensing agreements.

Pictures from Openworld 2009

Just thought I'd share some of the pictures from openworld