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

This will automatically select all the nodes available in cluster and propogate patch binaries to the other node.

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

• Verification of CRS Integrity Was Unsuccessful (0)
• 10g RAC - Single Node Install Error (0)
• Enabling Flashback On RAC Database (0)
• Raw Devices to be deprecated in 12G (0)
• 10.2.0.4 Window’s Patchset overwrites Sqlnet.ora (0)

There are quite a few “tuning primers” and “my.cnf generators” and “sample my.cnf files” online. The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.

I don’t know exactly the percentage, but quite a few of the servers I take a look at have been “optimized” with some tuning primer or question-and-answer script that spits out “optimal” parameters for my.cnf.

Most of the time these servers are far from optimal. Sometimes the my.cnf parameters are extremely wrong, to the point of causing a severe performance penalty.

If it were as easy as writing a tool to do this, don’t you think Maatkit would have mk-optimal-mycnf already? In my opinion — as someone who knows very well the complexity of creating a good my.cnf — it’s practically impossible. Much harder than syncing data, or manipulating a replication hierarchy, or any of the other things Maatkit can do already. And I doubt I’ll ever even feel motivated to try creating such a tool.

Don’t bother with scripts. Don’t waste your time with most of the advice you see on the web in forums — much of it is fundamentally wrong, even when it seems to come from an informed source. Don’t put too much faith in the my.cnf samples that come with your operating system; many of them have very bad advice in the comments, such as instructing you on how to set up replication in ways that guarantee breakage.

If you want solid advice, ask someone who knows what they’re doing (and can prove it). Or buy our book.

But even more fundamentally, you should not focus so much on my.cnf. It is not the be-all and end-all of performance. Tuning your server settings has far less impact on performance than tuning your schema, indexing, queries and — you guessed it — thinking deeply about your application architecture. Server settings are a distraction and a waste of time for most people.

Most my.cnf files I see only need minor tweaks, which give only so-so performance improvements. Tuning my.cnf only helps a lot when my.cnf has extremely bad parameters. The kind you’ll get from tuning primers and automated my.cnf optimization scripts.

---

Entry posted by Baron Schwartz | 13 comments

Add to: | | | |

How do you design a reliable distributed file system when the expected availability of the individual nodes are only ~1/5? That is the case for P2P systems. Dominik Grolimund, the founder of a Swiss startup Caleido will show you how! They have launched Wuala, the social online storage service which scales as new nodes join the P2P network.

The goal of Wua.la is to provide distributed online storage that is:

• large
• scalable
• reliable
• secure

by harnessing the idle resources of participating computers.

This challenge is an old dream of computer science. In fact as Andrew Tanenbaum wrote in 1995:
"The design of a world-wide, fully transparent distributed filesystem fot simultaneous use by millions of mobile and frequently disconnected users is left as an exercise for the reader"

After three years of research and development at at ETH Zurich, the Swiss Federal Institute of Technology on a distributed storage system, Caleido is ready to unveil the result: Wuala. Wuala is a new way of storing, sharing, and publishing files on the internet. It enables its users to trade parts of their local storage for online storage and it allows us to provide a better service for free. In this Google Tech Talk, Dominik will explain what Wuala is and how it works, and he will also show a demo.

read more

Update: Dormando asks Should you use memcached? Should you just shard mysql more?. The idea of caching is the most important part of caching as it transports you beyond a simple CRUD worldview. Plan for caching and sharding by properly abstracting data access methods. Brace for change. Be ready to shard, be ready to cache. React and change to what you push out which is actually popular, vs over planning and wasting valuable time.

Feedster's François Schiettecatte wonders if Fotolog's 21 memcached servers wouldn't be better used to further shard data by adding more MySQL servers? He mentions Feedster was able to drop memcached once they partitioned their data across more servers. The algorithm: partition until all data resides in memory and then you may not need an additional memcached layer.

Parvesh Garg goes a step further and asks why people think they should be using MySQL at all?

Related Articles

The Death of Read Replication by Brian Aker. Caching layers have replaced read replication. Cache can't fix a broken database layer. Partition the data that feeds the cache tier: "Keep your front end working through the cache. Keep all of your data generation behind it."

Read replication with MySQL by François Schiettecatte. Read replication is dead and it should be used only for backup purposes. Take the memory used for caching and give it to your database servers.

Replication++, Replication 2.0, Replication.Next by Ronald Bradford. What should read replication be used for?

Replication, caching, and partitioning by Greg Linden. Caching overdone because it adds complexity, latency on a cache miss, and inefficiently uses cluster resources. Hitting disk is the problem. Shard more and get your data in memory.

Pre-generating static files is an oldy but a goody, and as Thomas Brox Røst says, it's probably an underused strategy today. At one time this was the dominate technique for structuring a web site. Then the age of dynamic web sites arrived and we spent all our time worrying how to make the database faster and add more caching to recover the speed we had lost in the transition from static to dynamic.

Static files have the advantage of being very fast to serve. Read from disk and display. Simple and fast. Especially when caching proxies are used. The issue is how do you bulk generate the initial files, how do you serve the files, and how do you keep the changed files up to date? This is the process Thomas covers in his excellent article Serving static files with Django and AWS - going fast on a budget", where he explains how he converted 600K thousand previously dynamic pages to static pages for his site Eventseer.net, a service for tracking academic events.

Eventseer.net was experiencing performance problems as search engines crawled their 600K dynamic pages. As a solution you could imagine scaling up, adding more servers, adding sharding, etc etc, all somewhat complicated approaches. Their solution was to convert the dynamic pages to static pages in order to keep search engines from killing the site. As an added bonus non logged-in users experienced a much faster site and were more likely to sign up for the service.

The article does a good job explaining what they did, so I won't regurgitate it all here, but I will cover the highlights and comment on some additional potential features and alternate implementations...

read more

Welcome to the 110^th edition of Log Buffer, the weekly review of database blogs.

From the MySQL side, Jeremy Zawodny gets things going this week. He writes about his doubts over the long-term performance of InnoDB, specifically the cost of multiversion concurrency control, particularly in a master-slave arrangment or a DW. Jeremy comments, “[The] disk bloat, fragmentation, and ongoing degradation in performance may be an argument for having some slaves that keep the same data in MyISAM tables.” His readers, however, point out some diagnostics and tools to remedy this concern.

Not that MyISAM is without foibles. Case in point, on the MySQL Performance Blog, Peter Zaitsev says, Beware of MyISAM Key Cache mutex contention. “. . . inserting in parallel into different tables when indexes fit in memory results in very bad contention causing hundreds of thousands of context switches per second with far less work done than one would hope.” Happily, as Peter reports, multiple key caches mitigates.

Peter also surveys the choices for picking a datatype for STATUS fields — “. . . ’new’, ‘confirmed’, ‘in production’, ’shipped’ status of job, message etc. People use variety of ways to handle them often without giving enough thought to the choice which can cause problems later.” VARCHAR(N), ENUM, TINYINT — which is right for the job?

Here on the Pythian Group Blog, Nicklas Westerlund demonstrates adding a unique constraint on a table with duplicate data.

Also here at home, Sheeri Cabral’s item on why you want to switch to MySQL 5.1. She argues that additions such as online ALTER TABLE, online table-based logging, and INFORMATION_SCHEMA.PROCESSLIST are compelling features worth an upgrade.

(more…)