2008-08-21 Thu
Quite common beginners mistake is not to understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes but all single column ones. This can be spotted with a glance view. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely be poor choice too 
Entry posted by peter | 3 comments
Add to: 
| 
| 
| 
| 
Anybody who has tried this multi-node to single-node cloning in 11i knows that it’s difficult and very error-prone. If we outline the Apps Tier cloning process, it looks like this (supposing we have a two-node instance with the DB and CM on one node, and Web and Forms on the other):
# CM Node # copy all files to the target $ perl adpreclone.pl appsTier merge # Web Node # copy $COMMON_TOP/clone/appl directory only to the target $ perl adpreclone.pl appltop merge # Target Node $ perl adcfgclone.pl appsTier
This process is called merging appltops. It’s not as easy as it looks. Many times, the production environment might not have proper values in the fnd_nodes table, which makes adcfgclone.pl fail to properly recognize the appltops for merging.
But in R12, life is made easy. (more…)
So you need to design highly available MySQL powered system… how do you approach that ?
Too often I see the question is approached by focusing on expensive hardware which in theory should be reliable. And this really can work quite well for small systems. It is my experience - with quality commodity hardware (Dell,HP,IBM etc) you would see box failing once per couple of years of uptime which is enough to maintain level of availability needed by many small systems. In fact they typically would have order of magnitude more availability issues caused by their own software bugs, DOS attacks and other issues.
However as your system growths the reliability goes down. If you have 100 servers with each failing every 2 years this is about a server a week which is bad and if you’re into thousands and tens of thousands of servers server failures are becoming common place so it is important to make sure failing server does not affect your system and also what you can recover from server failure easily
So you should assume every component in the system can fail (if it is Server,Switch,Router,Cable, SAN) etc and you’re ready to deal with this. It does not mean you always have to ensure you stay fully operational after any failure but at least you should understand the risks. For example you may want to choose to keep single Cisco router because it has its own internal high availability on the component level which makes it extremely unlikely to fail, because you have 4 hour onsite repair agreement and because it is just freaking expensive. Though may be redundant less expensive systems could be better choice.
I would highlight again every component can fail it does not matter how redundant it is inside. The SAN is very good example - I’ve seen Firmware glitches causing failure in the SAN which was fully redundant on the component level. It is not every hardware component but also any code may fail as well. This is actually what makes your own code often the weakest link in availability.
Depending on failure rate you also should be thinking about automation - for frequent failures you want to recovery (like getting spare Web server and putting it online) to be automatic or done with simple manual command. For complex and rare failures you may have less automation - if certain type of failure happens once per couple of years for many evolving systems there is very high chance the old automation tools may not work well (this is of course unless you always test all automated failure scenarios regularly).
So if we’re designing the system so it can tolerate hardware failures should we bother about hardware quality at all ? The answer is yes in particular for classic database/storage systems. Few systems are design with so much error detection and automated handling in mind as Google File System.
In particular you want to make sure Error Detection is on the good level. For example if you’re running the system without ECC memory chances are your data will melt down and you will not notice it for long time (in particular if you’re using MyISAM tables) which can cause the error to propagate further in the system and make recovery much more complicated than simply swapping the server. This is exactly one of the reasons many high scale installations prefer Innodb - it is paranoid and this is how you want your data storage to be. This is also why Sun is so proud about checksums on the file system level in ZFS.
What is about RAID when ? As strange as it may sound but you should not relay on RAID for your data safety. There are many ways to loose data on RAID system even if you’re running RAID6 with couple of hot spare. The RAID is just dramatically reduces chance of data loss in case of hard drive failure and this is good because recovering database servers is not fully automated in most cases. Plus there may be system performance impact and (in particular if you use MySQL Replication for HA) the switch to the new server may not be 100% clean with few updates lost. RAID, especially with BBU also makes a good sense to get extra performance out of the box.
Some installations are using RAID0 for slaves - in these cases there are typically many slaves and recovery of the slave is very easy and causes no business impact. This is fine assuming you do the math and the performance gains or cost savings are worth it.
Another good RAID question is if Hot Spare should be used. I normally do not use it because it a large waste, especially as most of systems have even number of drives, so if you’re looking for RAID10 setting up hot spare costs you 2 drives. Having hot spare does not add a lot to high availability - if you have proper RAID monitoring in place and keep couple of spare hard drives on the shelf in the data center we’re speaking about couple of extra hours running in degraded mode. Even if you do not have spare hard drive you can often pool the one from the spare server and have the “warranty man” to replace it instead.
It is also a good question if you need redundant power supplies. In my experience they rarely fail so having redundant power supplies does not increase availability when it comes to hardware failures that much and so if you just look from this angle it may be justified only for the most critical servers. Do not forget redundant power supplies also increase server power usage a bit. Redundant power supplies however are helpful if you have multiple power feeds, so server can stay up if one of the phases has a power loss. Another benefit is - in redundant power supply will often allow to do some power work (like moving server to different circuit) without downtime which may be or may not be something important for you.
Finally I should mention about spare component. These are paramount if you’re designing highly available system. Having spare drives on the shelf, spare switches, spare servers (which are same as better as servers which are in production) is paramount. It is important promotion happens easily and there are no performance gotchas (ie 8 core server can be slower than 4 core with MySQL). It is best if you just put couple of spare servers in each purchase batch so they are absolutely same configuration but I know it is not always possible. Dealing with spares is yet another reasons to avoid the “server zoo” and have limited set of purchased configurations which are reviewed yearly (on other regular interval) rather than finding different best configuration each week.
Having spare servers also means you often do not need most expensive support agreements and Business Hours Monday-Friday is good enough for you - you’re not waiting for support for production anyway just fall back to another server and use it. Of course you can imagine cases when problem could affect all servers of the same type but it is not that frequently seen in practice.
To avoid multiple servers failing at the same time it is of course important to QA/Stress test servers before you put the load on them. I’ve seen multiple cases when something would go wrong and all servers of same configuration will experience the same problem. Proper QA/Stress test reduces the chance of this but you better to be testing with load similar to what you expect in production.
Requirement to have Spare hardware is also the reason why commodity inexpensive hardware is often better choice. If you have couple of $1M in production you need another $1M server as a spare and this is expensive. If instead you have 10 pairs $10K boxes having couple of spares would cost you only $20K plus I found it in many cases much easier to convince “finance” people to buy something cheap which is not used most of the time when to spend a lot of money on the server which will be where sitting doing idle.
How many spare servers you need - you would see it in practice. As I mentioned at least one for any hardware class you have. If you have many failures you need more of course. You may also decide to keep more spare systems when you can use them to help capacity management, especially if you have multiple applications which do not share hardware but share the data center. You may have “spares” to provide extra on demand capacity for web servers or memcached quite easily, or say increase number of slaves if you have unexpectedly high number of reports launched by users etc.
Entry posted by peter | 5 comments
Add to: | | | |
Just a quick post to get myself back into blogging mode. Recently in IRC (#oracle on freenode, to be precise), a fresh face asked if the Block Change Tracking file ever shrinks. She had been worrying about the file in her instance continuing to grow. A number of us speculated (non-BAAG!) that perhaps taking an RMAN backup would somehow purge the file of what it was keeping track of, and then the magical Oracle fairies would promptly resize it for us. Needless to say, I was hesitant to take this theory forward with Alex Gorbachev aware of my home address.
After setting up Oracle 10.2.0.1 on a nice VirtualBox image
(more on that in another post) running CentOS 5, I began to do some reading. For some reason, actually reading the official tahiti docs was last on my list. A search of the 10gR2 docs quickly yielded this (from RMAN Incremental Backups):
So, we’re coming up on that time again. When I moved back to Malta in order to work for Pythian one of the things that I wanted to do was to involve myself more in the community. Currently, I’m doing this by trying to keep an active blog with tips and tricks, good standard knowledge, and just overall trying to enlighten people. I’m also doing this by organizing a MySQL User Group here in Malta.
We’re set to have our 2nd meeting this coming Thursday, the 28thof August, in Ta’ Xbiex where we have graciously been donated a board room and projector (and parking space, luckily) for use. I’m trying to get one of our members to give a session about security (not only on MySQL, but also on the OS level to secure the process), and I know there is work being done on a presentation on MySQL Cluster, which I personally find very interesting and can’t wait to see.
In this second meeting, we’ll again focus on general best practices and free discussions, in order to share our knowledge as much as we can. The last meeting was in Mellieha and it was a success — we had a good turnout and some very interesting discussions. Darren, one of our members, blogged about our meeting here and so did I in one of my previous posts.
2008-08-20 Wed
I tripped over an old oracle-l exchange (not that old, from March of this year) and I thought it would make good content for a blog post on the critical difference between outsourcing and offshoring.
It started when Ethan Post posted a link to this fascinating story at the Ludwig von Mises Institute about how the U.S. dollar’s collapse affects the outsourcing industry. As many of these posts do, the idea of outsourcing gets conflated with that of offshoring. What the author really means to say is that the “downward dollar delivers a blow to offshoring“, not outsourcing.
Let me explain further. I am now cribbing shamelessly from my oracle-l post and so if you read this already this spring, my apologies.
Ethan had posed the following question:
Interesting article on the effects of the dollar’s fall on outsourcing. Would be interesting to hear a few of you who are perhaps feeling these effects to comment.
To which I replied:
Our margins were definitely squeezed painfully from April 07 until late last year (follow that link to see a 20% or so decline in the USD/CAD exchange, and remember that a substantial chunk of Pythian’s costs are in CAD and about 70% of our income is in USD). So it hasn’t been really that much fun adjusting to our new currency realities. That being said, I think there is a meaningful difference between offshorers and outsourcers and that these different ideas get conflated a lot, including in this case.
If your company’s entire business model is simply shifting work from a country where wages are high to a country where wages are lower, you have two problems. First, you are very vulnerable to this type of currency shift because it is at the core of your profit model. This is called labour arbitrage. Second, as time marches on and we continue our trend to a global rate for any given IT service, your company will cease to have any reason to exist. This article from the Economist covers general India inflation, trust me focus on labour inflation in the information technology sector and the situation is much, much worse.
However, outsourcing properly conceived can be highly successful even when the resources are hired locally to the market being targeted; meaning without relying on exchange rate differences nor differences in global payscales. These companies are successful because by concentrating expertise, adopting best practices, innovating and reusing work they have found efficiencies that add up to more than their direct costs of services delivery + overhead (meaning, that profit model has nothing to do with currency or wage geography).
I would count Pythian among companies conceived along those lines, we are far from alone, certainly our direct competitors based only in the U.S. dbaDirect, Contemporary and DCC (hi guys! well done!) do not rely to any degree on wage differences or exchange rates as parts of their profit models. To put things into perspective, although Pythian has a presence in 10 countries now (including four U.S. cities), our profit model also has innovation, expertise, scale and re-use as it’s heart and soul, not labour arbitrage.
So anyway, we’ve successfully adjusted over here and I think offshoring will lose a lot from the collapse of the USD, but these companies that are offshorers only will be forced to morph into something better to survive, which is good for everyone.
A few days ago I had a new idea for a blog post. A post about what it really takes to be a good database administrator. I began by researching what others had done on the topic. At the end of this post you will find links to six of the posts I found that provided some insight into this question. Even after uncovering this information, I thought I could add something to the mix from my own experiences. So here we go!
2008-08-19 Tue
I’d like to share some great news — The Pythian Group and Open Query have become partners!
Open Query is a leading provider of high-quality MySQL, PostgreSQL and related training in Australia and New Zealand. They offer consulting services too, and are also known for their MySQL Graph Storage Engine. Feel free to browse through Open Query web-site for more info.
Open Query was founded by Arjen Lentz, who was employee number 25 at MySQL AB. If you follow the MySQL community then I’m sure you already read Arjen’s blog.
Since you’re reading this blog, I guess you probably already know what Pythian does, but if you want to learn more, please click through to our home page.
Together with Open Query, we are going to extend our service offerings and strengthen our positions in outsourced database management services, consulting, and training.
Continuing my experiments with our 2 Node 10g RAC Test system, I carried out upgrade of Oracle Clusterware and Oracle RAC Database from 10.2.0.1 to 10.2.0.4. I have tried to document the steps for upgrading Oracle Clusterware(Rolling Upgrade) and RAC Database upgrade in this post. In case you observe any mistakes, please let me know (I am still learning RAC and bound to make mistakes 
)
First step is to download the 10.2.0.4 Patchset from metalink. In our case ,we downloaded Patch 6810189 (10g Release 2 (10.2.0.4) Patch Set 3 for Linux x86). You can follow Patch Readme for detailed steps.
We will be doing Rolling upgrade for Oracle Clusterware i.e we will only bring one node down for patching while other node will be available and accepting database connections.
1)Shutdown the DBconsole and Isqlplus
$ emctl stop dbconsole
$ isqlplusctl stop
2) Shutdown the associated service on the node
[oracle@blrraclnx1 ~]$ srvctl stop service -d orcl -s orcl_taf -i orcl1
3) Shutdown Database Instance and ASM instance on node (if present)
[oracle@blrraclnx1 ~]$ srvctl stop instance -d orcl -i orcl1
To stop ASM, use following command
[oracle@blrraclnx1 ~]$ srvctl stop asm -n blrraclnx1
4)Next step is to stop Nodeapps services on the node
[oracle@blrraclnx1 ~]$ srvctl stop nodeapps -n blrraclnx1
Before proceeding to installing Oracle Clusterware Patch, let’s confirm if services have been stopped
HA Resource Target State ----------- ------ ----- ora.blrraclnx1.ASM1.asm OFFLINE OFFLINE ora.blrraclnx1.LISTENER1_BLRRACLNX1.lsnr OFFLINE OFFLINE ora.blrraclnx1.gsd OFFLINE OFFLINE ora.blrraclnx1.ons OFFLINE OFFLINE ora.blrraclnx1.vip OFFLINE OFFLINE ora.blrraclnx2.ASM2.asm ONLINE ONLINE on blrraclnx2 ora.blrraclnx2.LISTENER1_BLRRACLNX2.lsnr ONLINE ONLINE on blrraclnx2 ora.blrraclnx2.gsd ONLINE ONLINE on blrraclnx2 ora.blrraclnx2.ons ONLINE ONLINE on blrraclnx2 ora.blrraclnx2.vip ONLINE ONLINE on blrraclnx2 ora.orcl.db ONLINE ONLINE on blrraclnx2 ora.orcl.orcl1.inst OFFLINE OFFLINE ora.orcl.orcl2.inst ONLINE ONLINE on blrraclnx2 ora.orcl.orcl_taf.cs ONLINE ONLINE on blrraclnx2 ora.orcl.orcl_taf.orcl1.srv OFFLINE OFFLINE ora.orcl.orcl_taf.orcl2.srv ONLINE ONLINE on blrraclnx2
5)Set DISPLAY variable and execute runinstaller from Patch Directory
[oracle@blrraclnx1 Disk1]$ ./runInstaller
This will open OUI screen. Select Oracle Clusterware Home for Patching. Find below screenshot for same
crs10204patch
This will automatically select all the nodes available in cluster and propogate patch binaries to the other node.
10204patch2
6) On the Summary screen, click Install.OUI will prompt you now to run, following two scripts as Root which will upgrade Oracle Clusterware
# $ORA_CRS_home/bin/crsctl stop crs
# $ORA_CRS_home/install/root102.sh
Now we need to repeat the steps 1-4 and step 6 on Node 2. Step 5 is not required as binaries have been already copied over to node 2.
RAC Database Patching cannot be done in a rolling fashion and requires Database to be shutdown.
1)Shutdown the DBconsole and Isqlplus
$ emctl stop dbconsole
$ isqlplusctl stop
2) Shutdown the associated service for database
[oracle@blrraclnx1 ~]$ srvctl stop service -d orcl
3) Shutdown Database Instance and ASM instance on node (if present)
[oracle@blrraclnx1 ~]$ srvctl stop database -d orcl
To stop ASM, use following command on both the nodes
[oracle@blrraclnx1 ~]$ srvctl stop asm -n blrraclnx1 [oracle@blrraclnx1 ~]$ srvctl stop asm -n blrraclnx2
4)Next step is to stop Listener on both the nodes
[oracle@blrraclnx1 ~]$ srvctl stop listener -n blrraclnx1 -l LISTENER1_BLRRACLNX1 [oracle@blrraclnx1 ~]$ srvctl stop listener -n blrraclnx2 -l LISTENER1_BLRRACLNX2
5)Set DISPLAY variable and execute runinstaller from Patch Directory
[oracle@blrraclnx1 Disk1]$ ./runInstaller
This will open OUI screen. Select Database Home for Patching.
6) On the Summary screen, click Install.When prompted, run the $ORACLE_HOME/root.sh script as the root
user on both the nodes. On completion of this , we need to perform post installation steps.
7)Start listener and ASM Instance on both the nodes
[oracle@blrraclnx1 ~]$ srvctl start listener -n blrraclnx1 -l LISTENER1_BLRRACLNX1
[oracle@blrraclnx1 ~]$ srvctl start listener -n blrraclnx2 -l LISTENER1_BLRRACLNX2
[oracle@blrraclnx1 ~]$ srvctl start asm -n blrraclnx1
[oracle@blrraclnx1 ~]$ srvctl start asm -n blrraclnx2
8)For Oracle RAC Installation, we need to set CLUSTER_DATABASE=FALSE before upgrading
[oracle@blrraclnx1 ~]sqlplus "/ as sysdba"
SQL>startup nomount
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL>shutdown immediate;
SQL>startup upgrade
SQL>spool 10204patch.log
SQL>@?/rdbms/admin/catupgrd.sql
SQL>spool off
Log file needs to be reviewed for any errors. catupgrd.sql took 42 minutes on my system. In case CLUSTER_DATABASE parameter is not set to False, you will get following error while starting database in upgrade mode
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
We need to Restart the database now and run utlrp.sql.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql
Confirm whether Database has been upgraded successfully by querying DBA_REGISTRY;
select comp_name,version,status from dba_registry;
Now set Cluster_database parameter to TRUE and start Database
SQL>alter system set cluster_database=true scope=spfile;
SQL>Shutdown immediate;
[oracle@blrraclnx1 ~]$ srvctl start database -d orcl
[oracle@blrraclnx1 ~]$ srvctl start service -d orcl
To upgrade DBConsole, run following command
emca -upgrade db -cluster
This completes the upgrade process.
Related Posts
While going through the routine checks from Grid Control, I found a critical alert stating “clusterware integrity check failed” and by clicking on this message it says that there is problem with some metric collections on RAC environment.
To check the node reachability status following query was run:
$ $CRS_HOME/bin/cluvfy comp nodecon -n all
This will check the internode connectivity for all nodes in the cluster. It came out with following message:
$ $CRS_HOME/bin/cluvfy comp nodecon -n all Verifying node connectivity Verification of node connectivity was unsuccessful on all the nodes.
Even the CRS component check was unsuccessful:
$ $CRS_HOME/bin/cluvfy comp crs -n all
It came out with the following message:
$ $CRS_HOME/bin/cluvfy comp crs -n all Verifying CRS integrity Verification of CRS integrity was unsuccessful on all the nodes.
After this it was quite obvious to check the CRS status:
$ crsctl check crs CSS appears healthy CRS appears healthy EVM appears healthy
$crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.orcl.db application ONLINE ONLINE rac1 ora....11.inst application ONLINE ONLINE rac1 ora....12.inst application ONLINE ONLINE rac2 ora....vice.cs application ONLINE ONLINE rac2 ora....l1.srv application ONLINE ONLINE rac1 ora....l1.srv application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....DC.lsnr application ONLINE ONLINE rac1 ora....idc.gsd application ONLINE ONLINE rac1 ora....idc.ons application ONLINE ONLINE rac1 ora....idc.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora....dc2.gsd application ONLINE ONLINE rac2 ora....dc2.ons application ONLINE ONLINE rac2 ora....dc2.vip application ONLINE ONLINE rac2
$$CRS_HOME/bin/olsnodes rac1 rac2
This confirmed that the CRS install is valid, but the question now is why the cluster verification utility (CVU) was failing?
To find the reason I enabled the tracing of CVU as:
$export SRVM_TRACE=true
It will set the environment variable SRVM_TRACE to true and tracing of CVU will generate a trace file under $CRS_HOME/cv/log with name like “cvutrace.log.X”
After setting this and again running $CRS_HOME/bin/cluvfy comp crs -n all trace file with name cvutrace.log.0 was generated.
And a message in cvutrace.log like
"ksh: CVU_10.2.0.2_dba/exectask.sh: cannot execute"
Now its is clear that oracle is not able to execute exectask.sh and cheking the permission and ownership of exectask.sh:
$CRS_HOME/cv/remenv ls -ltr -rw-r--r-- 1 oracle dba 184 Jan 9 2008 exectask.sh -rw-r--r-- 1 oracle dba 268386 Jan 9 2008 exectask
The permission of these two files was changed. After changing the permission back to 755 CUV was showing correct results.
$chmod 755 exectask*
It is still not discovered how the permission of these files got changed.
