What will happen to ASM when the disk path changes in Linux?

Posted by David Yahalom under Storage, ASM, RAC, Oracle

Have you even wondered what happens if disk device names and/or major-minor numbers changes at the operating system level? Will this cause any problems for ASM as it tires to access the drives even when the drive path has changed?

Well, no ! This will cause absolutely no problem what so ever to ASM. In fact, its one of ASMs best features.

You see, ASM only cares about the LOGICAL disk names and not the PHYSICAL drive paths or numbers. Changing the drive paths or major-minor numbers in the O/S is no problem for ASM. This is because ASM scans the disks based on what is defined in asm_diskstring - so during boot, all drives that have a valid ASM header will be automatically added to ASM.

These devices are opened with system calls (like fopen, etc.) so paths and/or major numbers are not even used.

The disk paths, names and major-minor numbers are not persistently recorded in any of the ASM metadata. Each ASM disk has a disk header which contains the disk name and diskgroup stamped in it.

If ASM discovery finds the required number of disks for a given diskgroup then it will be able to mount the diskgroup. The ASM metadata header will contain the fixed and persistent logical disk name (for example - ORCL_DATA_DISK1). On boot, ASM will scan all disks for one with the above name in its header.

Once it finds that disk it will make the connection between the current (again, not persistent) disk path and the logical disk name.

The relation between an Oracle instance and memory in Windows

Posted by David Yahalom under Windows, Hardware, Oracle

The Oracle instance memory allocation works differently in Windows then it does in UNIX-Linux like operating systems.

In Windows environments, on starting up the Oracle instance all global memory pages are committed (like the buffer cache, redo log buffer and the library cache). However, and this is an important difference compared to POSIX operating systems, only a small number of these memory pages are actually cached in memory upon instance startup causing most of the SGA not to be part of  Oracle’s active working set.

This forces Oracle to compete on equal grounds with other processes. The O/S might swap certain pages from Oracle’s working set in memory to disk during periods of increased activity or when other processes are paging more and compete for RAM. Windows pages according to activity using an MRU-like mechanism, so when paging is going-on system-wide, Windows will try to reserve RAM pages to the most active process, the one that is paging the most.

If Windows decides to swap the database instance to disk, this will cause a severe performance degradation for the Oracle Database and might even grind the instance to a complete halt.

There are two registry parameters that exist and allow us to manipulate the working set of the Oracle process. These are:

• ORA_WORKINGSETMIN or ORA_%SID%_WORKINGSETMIN:
  Minimum working set for the ORACLE.EXE process (units = MB)
• ORA_WORKINGSETMAX or ORA_%SID%_WORKINGSETMAX:
  Maximum working set for the ORACLE.EXE process (units = MB)

You can set these parameters under:

• For single ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
• For multiple ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx (for multiple homes)

ORA_WORKINGSETMIN can be used to prevent the pages allocated to the Oracle process from dropping below the defined threshold (in MB) until the instance is shutdown.

The biggest benefit of setting these parameters will be in an environment where Oracle coexists with other applications. Although it can be beneficial in other scenraios as well, such as when production and test instances are running on the same physical machine.

Another very useful parameter that exist in the Win32/64 platform is: PRE_PAGE_SGA. This parameter causes Oracle will force Oracle to allocate all SGA pages upon instance startup thus bringing all of them to memory as the working set of the Oracle executable.

This will allow the instance to reach maximum performance more quickly rather than through an incremental build up as pages are loaded on a need-to basis.

Combining ORA_WORKINGSETMIN  with PRE_PAGE_SGA will force the Oracle instance to start above the minimum threshold and not drop below. Using ORA_WORKINGSETMIN in isolation will cause the Oracle working set rises above the threshold it will not drop below.

Blog has been renamed!

Posted by David Yahalom under General IT

As you probably noticed I’ve changed my blog’s name and URL to www.davidyahalom.com instead of www.authoritybase.com. After doing some thinking I’ve decided that it is more appropriate for my blog at its current stage to reflect my name instead of using something more original. You can still access it by typing www.authoritybase.com, but all new articles and views will be published under the www.davidyahalom.com domain.

I’ve also created a new logo for my blog, one that better reflects the database related content on this site.

Cloud Oracle Storage: how to make ASM even better, the NAS way.

Posted by David Yahalom under ASM, RAC, Oracle

No one can argue about the merits of Oracle ASM. Having a transparent, easy to use and high performance LVM built using the same fundamentals of the Oracle database is a great technology for Oracle DBAs. ASM is a relatively new technology that have gained popularity fast. I’m using ASM on a daily basis and I’m sure many of  you do as well. But it could be even better.

Imagine how wonderful it would be if Oracle took ASM even furthur and made ASM an enterprise-wide network-based unified database storage solution, allowing many remote Oracle DB Instances access a unified “cloud like” storage layer via the network.

Currently using ASM, we need to start an ASM instance for every physical server running an Oracle DB instance. Why not have ASM act as a network-based LVM as well? Let us be able to start an ASM instance on one server having all other Oracle databases, even those running on remote  machines, access that ASM instance over the network.

That would be like exposing our storage as a NAS solution - no matter the vendor.

The benefits this technology would have over regular NAS (Network Attached Storage - such as using NFS mounts for storing Oracle Data Files)  would be having a unified storage deployment strategy for our organization and potentially enjoying database-specific performance benefits that are not available with generic NAS.

It’s no secret that I’m a big fan of deploying Oracle over NFS using fast networks (which can even perform better than fiber), the problem with NFS is that it isn’t database-centric in its roots. First, you need support at both the storage level and the client level: you need to run a NetAPP filler (NFS on EMC isn’t as good) and have Linux as the operating system for optimal performance. Second, tuning NAS for Oracle, while doable, isn’t easy. Oracle made great progress in 11g with the DirectNFS client but it is a Liunx/NETAPP only solution.

Most organizations have a mix of Linux/Unix/Windows servers with both EMC and Netapp equipment in the storage layer. Using Network ASM could really make the storage layer transparent from the database prospective. Think of it as Cloud Storage for Oracle Databases. All databases would have a single, redundant, POC for storage. Your Oracle Instance could access both EMC and NetAPP machines easily and transparently over the network. All the benefits of regular ASM would apply here as well - being able to add disks, remove disks, strip and load-balance on the fly.

COS - Cloud-computing Oracle Storage or N-ASM - Netowrked ASM.

Remember where you heard it first!

ORA-600: Oracle process has no purpose in life!

Posted by David Yahalom under Oracle

Yes, that’s an actual error. Or more precisely: ORA-600 [12235] “Oracle process has no purpose in life !”.

Actually, This error shows up when Oracle detects a defunct Oracle process. You see, when an Oracle server process starts it reads certain data from the SGA that sets various parameters for that process (and defines its existence so to speak) .
If the process does not locate any valid customization data, it will report back with this error.

The funniest Oracle ORA error I’ve ever came across.

You can read about this in metalink note 33174.1.

Btw, an easy way to recreate this would be to type “oracle” at the O/S prompt. Try it for yourself.

Proactive monitoring with Oracle Grid Control

Posted by David Yahalom under ITIL, Grid Control, Monitoring, General IT, Oracle

Data centers today are becoming more and more complex. There is a real need to provide 24/7, always-on, high SLA services.

If that’s not enough, enterprise systems are also becoming much more complex to manage, maintain, develop and troubleshoot. Large clusters of mixed UNIX/Linux/Windows systems, tens of terabytes of storage, hundreds if not thousands of servers, super fast networks and an application mess that consist of web servers, application servers and database servers from several vendors working in tandem is the reality for more and more businesses nowadays. The sheer maintenance alone of such complex environments can be a daunting task for any IT staff.

As the complexity of systems increase, the need for better, more comprehensive, monitoring solutions rises. The monitoring tools and methodologies that were once considered good-enough just don’t cut it anymore. Knowing that servers are up and running and that the backups have finished successfully isn’t enough. Complex systems require complex monitoring methodologies taking into account complex SLAs scenarios that usually span dozens of servers and tens of connected systems.

There are two methodologies for troubleshooting system performance. You can be reactive – wait for the problems to happen and then do your best to solve them as fast as you can or be proactive – learn of potential problems and bottlenecks before they become critical and affect your business.

Many if not most of the organizations I consult are still pretty much reactive-only. Reactive DBAs usually have their day start with a phone call having an angry developer on the other side complaining users are calling about slow response time in the application. The DBA team will spend a considerable amount of time trying to pinpoint the exact source of the problem. Is it the database server? Nope – the load seems okay. Maybe the application servers are acting up? Doesn’t look like it - there are very few requests per second. So maybe the web server is the root of the problem? Or perhaps it is an isolated problem with the specific user? Or network lag?

Time wasted. When you are reactive, you try to address problems only after they have already caused damage/loss/downtime for you and your company. The proactive approach however, is derived from identifying problems before they happen and addressing them before they become a problem for your company. You can define a set of rules which will be defined as a baseline SLA indicator for the application.

This can be anything from “page load time” to system-oriented tests such as CPU load or blocking sessions. If you have an application in which a certain key page takes on average three seconds to load and the threshold for when users will start noticing performance degradation and complain is ten seconds, when thinking proactive you define a metric that will alert you once the page load time passed five seconds so that you will nip the problem in the bud - before it has fully manifested itself.

So how does Oracle Grid Control fit into all of this?

Grid Control is Oracle’s high-end, fully fledged, enterprise-wide, monitoring and management solution. Grid Control is a single, integrated solution for administering, operating and monitoring applications and systems.

Grid Control can monitor and manage many types of software applications and hardware solutions and is not limited to monitoring only Oracle databases as many believe. Grid Control support includes: Linux, Unix, NetAPP storage, EMC storage, VMWARE ESX servers, SQL Server, Active Directory servers, BizTalk servers, DB2 servers, JBoss servers, IIS servers, Exchange servers, Apache/TomCat servers, F5 network products and Checkpoint network equipment).

Oracle Grid Control’s provides the following capabilities and features out of the box:

1) Application Management.
2) Database Management.
3) Middleware management.
4) Real User Experience insight.
5) Host / Server management.
6) SOA management.
7) Identity management.

Each of these features consist of both predefined metrics for evaluating system performance and availability plus the ability to create custom metrics handing over control on what specifically Grid Control monitors to you. You have the capability to group several key systems together and define a service group that is dependent on the availability and performance of all the targets belonging to that service group.

This gives us the ability to evolve our monitoring beyond the specific system or server and monitor a complete “service” – emulating the experience of the end user.

Deploying Grid Control in your environment is very easy. You’ll need to install a server that will run the Oracle Management Services (OMS – the “brain” of Grid Control) and an Oracle Database repository to hold monitoring data such as metrics thresholds, past alerts, monitoring history etc. On most monitored targets you’ll need to install a Grid Control agent that will communicate with the OMS server. Some monitoring targets, such as NetApp storage filers, can be monitored without the need of installing any agent of any kind.

ROI out of the box - Grid Control is very powerful and complete solution giving us benefits immediately after the installation. Preloaded with best practice performance and availability metrics, giving you a head start on enterprise-level monitoring with minimal work. We can also define our own set of user-defined metrics based on SQL queries or running shell scripts, perform corrective actions straight via Grid Control and, of course, track the SLA levels you are providing your customers.

We can view the current and historical status of almost any system in your organization and drill down to pretty much any performance and availability metric that is relevant for us.

Monitoring NetApp filers? Using Grid Control you can view current and past performance indicators in graphical format such as: disk I/O, CPU utilization, NFS calls per second, CIFS calls per second, NetWork I/O, space used and more.

Monitoring a Linux server? Using Grid Control you can view, in a graphical format, current and past: CPU load, network load, Disk I/O utilization, mange security violations, manage configuration data and perform remote administration.

While Grid Control can be used to monitor a very extensive list of products maybe the single biggest advantage of deploying Grid Control is what it can do for your Oracle databases. Grid Control allows for self-tuned and self managed databases, automation of complex or routine manual tasks which are often error-prone, rapid root cause analysis through established standards and complete compliance enforcement.

Using the combination of Grid Control and Oracle 10g’s ADDM feature you can periodically examines the state of the database, automatically identify potential bottlenecks and have Grid Control recommend corrective actions. Oracle Enterprise Manager presents ADDM findings and recommendations in a convenient and intuitive fashion and guides administrators in step-by-step process to quickly resolve performance problems by implementing ADDM recommendations. ADDM runs automatically to coincide with the snapshots taken by the Automatic Workload Repository (AWR) and begins its analysis by focusing on the most time-consuming database activities. ADDM then presents a drill down using a sophisticated problem classification tree to determine the root of the problems.

Grid Control also offers a large array of compliance assessments (with out-of-box included policies), security violation reports, patch advisories, multiple-target deployment and of course – alerts. You can have Grid Control notify you of potential problems both via email, SMS text messages and SNMP traps.

Let’s get physical

Getting around Grid Control is easy. After logging into Grid Control web console, you are presented with an aggregated view giving you a one-glance overview of the status of your enterprise IT systems. The metrics displayed consist of a graphical status representation of your enterprise systems, a summery of all alerts currently triggered on all targets being monitored divided into severity levels, security violation summery and several other key indicators.

Everything on this page is a link so you can easily perform drill down operations on all data being displayed (for example, viewing all alerts with critical severity or all targets that are currently down).

Selecting the “targets” tab from the header navigation menu takes us to the real action. Here we see all of the targets being monitored by Grid Control divided into logical categories. We see hosts (O/S monitoring), Databases, Application Servers, Web Applications, Services, Systems and groups.

Clicking on each of these links will give us a quick overview of all targets that belong to the selected category. Providing information regarding version numbers, summery of all alerts, security violations and several other useful statistics. Clicking an instance name takes us to a much more detailed view of that system.

The front page is a one-glance view of the system status. We can see the load, active sessions (divided to CPU time, I/O time and wait time) plus an aggregated summery of space violations on the machine (such as user objects stored in the System Tablespace), a list of recent errors and instance recovery times.

This is just a small fraction of the options available trough Grid Control and just a quick taste of the interface. There’s much more waiting for you to discover by yourself.

Using GridControl for ITIL compliance

This last section of the article can be crucial to some of you looking for ITIL compliance. ITIL stands for IT Infrastructure Library - a widely accepted approach and methodologies to IT service management providing a complete and comprehensive set of best practice guidebooks supported by a qualifications scheme, training and implementation and assessment tools. ITIL was designed to promote the quality of computer services in the organization and provides management with a template framework for best practices to achieve quality IT services and overcome difficulties associated with the growth of IT systems. ITIL is divided into a set of text books, qualification programs, software tools and user groups slicing the services expected from an IT department:

1. Service support.
2. Service delivery.
3. Managerial.
4. Software support.
5. Computer operations.
6. Security management.
7. Environmental

So how exactly can Grid Control help you become ITIL certified?

Since Grid Control can essentially monitor everything in your DataCenter it can better help you align business aspects with your IT aspects.

• ITIL configuration management

The basic process defined by ITIL is that of configuration management. This is essential for both service support and service delivery.
Configuration management means tracking all of the individual hardware components supporting your business IT infrastructure and all of the changes these components undertake. Grid Control tracks all of these changes in a build-in Configuration Management DB (or CMDB for short) which keeps all history data easily accessible using Grid Control’s interface.

Each one of these Configuration Items is linkable to other Configuration Items in your infrastructure. You can define a connection between servers X,Y,Z and the organization HR system. Grid Control also has the ability to automatically identify the relation between separate configuration items and greatly reduce the overhead with manually documenting and linking individual configuration items.

This allows the developer to notify the system administrator that ever since he replaced the network card in the production server the entire application is running slower than usual. Identify the change that caused the problem.

• ITIL Problem management
  Another key ITIL process is the diagnostic of reoccurring problems and failures. Reoccurring errors are usually a sign of problems that were poorly handled when they first occurred. Grid Control allows us to identify the error when it happens by using root cause analysis, helping us identify the underlying problematic configuration item thus greatly simplifying troubleshooting.

• ITIL Change management

Change management in complex IT environments is all about they way your IT staff can coordinate changes in your infrastructure so that they can easily identify which systems are being affected by the proposed change. Grid Control automatically documents any change made in your business IT infrastructure. By creating “baseline snapshots” using Grid Control you can later compare your infrastructure configuration to a given point in time.

Grid Control also supports Change Automation allowing us to schedule automatic deployment of Oracle patches.

• ITIL Release Management
  ITIL release management is the ability to release final, production-grade synchronized versions of your IT system along with all of its configuration items corresponding to the various installation procedures and regulations in place.

  This gives us the ability to keep a “closed” version of our configuration management (combination of hardware, O/S choice, DB version…) as a standby baseline and use this baseline to easily make future copies. Grid Control fully supports ITIL release management by allowing us to automate our deployment procedures of the entire system from the bare-metal upwards.

• ITIL Capacity Management
  Since both system load and capacity play critical roles in the availability and performance of your business systems, ITIL defines a process to easily manage and quantify your systems capacities. In order to predict the capacity required to support future workloads, Grid Control tracks the system performance over a period of time allowing you to slice the performance by various indicators such as user activity.

This is just a quick tour of Grid Control and really, only a drop in the sea in terms of capabilities and options at your disposal. We strongly recommend that you install Grid Control for yourself and explore the world of proactive, smart IT infrastructure monitoring and management.

And remember…. Once you go Grid, you’ll never go back!

Special thanks to Liat Sabag, one of the most talented DBAs I’ve known.

Hashing strings in Oracle 8i,9i,10g

Posted by David Yahalom under Oracle

There’s an easy way to hash strings in Oracle 8i,9i,10g.

In Oracle 10g you can use the ORA_HASH function which computes a hash value for any given expression. It recieves three arguments:

• expr - determines the data for which you want Oracle Database to compute a hash value. You can go wild on the length of this string there isn’t any restriction on this.
• max_bucket (optional) - determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295.
• seed_value argument (optional) - enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr and seed_value. You can specify any value between 0 and 4294967295. The default is 0.

SQL> select ora_hash('hello world') from dual;

ORA_HASH('HELLOWORLD')
----------------------
1896528268

SQL>

In Oralce 8i and 9i there is not ORA_HASH function. What we can use instead is the DBMS_UTILITY.GET_HASH_VALUE function. It is similar yet different from ORA_HASH .

The GET_HASH_VALUE function takes three mandatory parameters:

• name - The string we want converted from string to integer via hash.
• base - The base value for the integer used in the hashing algorithm.
• hash_size - This is the size of the hash table. The total number of values that are available to the hashing algorithm as conversions from the string inputs. It is recommended that this will be the power of two.

For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value.

SQL> select DBMS_UTILITY.GET_HASH_VALUE('hello world1', 2,1048576) from dual;

DBMS_UTILITY.GET_HASH_VALUE('HELLOWORLD1',2,1048576)
----------------------------------------------------
113894

SQL>

Started a new job!

Posted by David Yahalom under General IT

Pop up the champagne bottles people!

I have started a new job as a Senior Project Manager at the Veracity Group , the leading Oracle Consulting group in Israel. Veracity is the only Oracle certified Value Added Distributor (VAD) in Israel and part of a very small and exclusive list of Oracle VADs around the world. Our customers benefit from the unique advantage we offer of both directly licensing and selling Oracle products as well as having an outstanding expert Professional Services team. We’re Simply the best in our field, trust me. As part of my new job at Veracity, I’m also holding the CTO title of a very special group in the Oracle Database development business, but more on this later.

I’ve been able to accomplish a great deal in the last six years as a database consultant, especially during my time at Xpert-One1 which was a very rewarding experience. For some time now I’ve been looking into getting a more managerial position and when the opportunity presented itself, I simply couldn’t resist. My new position puts me directly in the middle decision making process about all things technological - so I think this would make a great learning experience for me. I’m still (and always will be) a bits and bytes kind of guy so I’m looking forward combining my deep passion for technology with having a managerial position. Any tips on how to exactly accomplish this from all you veterans out there are welcome!

Migrating from SPARC to x86

Posted by David Yahalom under RAC, Hardware, Solaris, Unix, Oracle

Remember the SPARC Vs. x86 post I made some time ago? I’ve talked about sizing old SPARC hardware compared to new x86 based servers.

Well, I have just finished a rather big project migrating several single-instance Oracle 8i databases running on old(ish) SPARC machines to a state of the art Oracle 10g RAC on commodity x86 Linux servers.

I have migrated my client from this:

Two: 4 X UltraSparc III+ 1GHZ CPUs, 8GB RAM.
Two: 3X UltraSparc II 300Mhz CPUs, 4GB RAM.

To this:

Two HP DL580G5: 2X Intel QuadCore Xeon X7350@2.93GHz, 16GB RAM (list price: $15,000).
One HP DL380G5: 2X Intel QuadCore Xeon E5345@2.33GHz, 8GB RAM (list price: $8,000).

The workload remained the same. The old config had several different unique schemas on each physical server. Each applications running connected to a different schema on a different physical server. No shared data between servers. This was done to spread the workload across several machines. Five years ago they (client) considered it a poor man’s cluster.

When installing and configuration the RAC cluster, I have combines all of the different schemas under a single database.

I’ll try and published more detailed performance benchmarks later on, but now all I can say is this: in terms of raw CPU power (taking storage I/O and memory restrictions out of the function) the RAC cluster smokes, obliterates and completely destroys all of the older SPARC servers combined.

The RAC cluster consist a total of 6 CPUs, each with 4 cores (a total of 24 cores).  The average load on each node is around the 2.0-2.2 mark. Meaning a combined load of ~6/24 or roughly 25%.  Given the same workload,  the old SPARC machines reached well above 100%. For example, one of the “more powerful” Ultrasparc III+ 1Ghz machines (total of 4 CPUs) reached load ranges of above 20!

The workload is a mixed multi-million user, multi-session web application with short, cached queries and transactions plus several single-threaded very cpu-intensive financial applications that perform thousands of logical DML operations per second. One of these DML intensive processes caused a 4XUltraSprac III+ 1Ghz machine to reach average loads of 15-20. The same process running on the same data barely tickles the QuadCore Xeon.

Now, please keep in mind two things:
1) This isn’t scientific, far from it. At least not yet. I have also performed more concrete benchmarks, but until I’ll have time to arrange all the data into user-readable format you’ll just have to take my word on it.

2) You might say that comparing 4 year old SPARC hardware to brand new x86 machines might not be fair. And I’ll agree. It isn’t. But what’s important is to take the general sizing figures from this post and try to adapt them to occasions where you have a site “suck” with old SPARC hardware and you want sizing information to decide how exactly SPARC Mhz translates to x86 Mhz.

The bottom line is this: don’t be afraid to migrate old SPARC machines to modern Linux x86 hardware. It will usually be allot faster.
And…. you can achieve great performance when using cheap, x86 servers in a RAC cluster and very high ROI.

Show full process name / path / string in Solaris using ps

Posted by David Yahalom under Solaris

Let’s say you have a JAVA process running on an old Sun Solaris machine. You want to see the full path of the process running including any run-time variables that were passed to it.

Using ps -eaf | grep -i java gives us this:

[root@hostname ~]# which ps
/bin/ps

[root@hostname ~]# ps -eaf | grep -i java

nobody  4589  4588   0 14:26:35 ?           0:21 /usr/java1.4/bin/java -Djava.awt.headless=true -Xms200m -Xmx300m org.apache.jse

As you can see the process name is trimmed and you can’t see all parameters passed to Java. No matter what parameters or scripting you’ll try and do, using /bin/ps (default) will crop your process name.

There is, however, an entriely different ps program we can use.

[root@hostname ~]#/usr/ucb/ps -auxwww | grep -i java

nobody    4589  0.1  4.228480842528 ?        S 14:26:35  0:21 /usr/java1.4/bin/java -Djava.awt.headless=true -Xms200m -Xmx300m org.apache.jserv.JServ /jserv/etc/jserv.properties

Fantastic!