My crsstat script: Improved Formatting of crs_stat on 10g and 11g
My crsstat shows a tabular format of CRS or HAS resources (i.e. Cluster 10gR2-11gR2 or ASM/Single instance scenario 11gR2). It has been a work in progress for a while. When I first started working with 10gR2 RAC, I quickly discovered that I didn’t quite like the formatting of the default crs_stat -t output, so I wrote my first version of the crsstat script, which consisted of a korn shell script and an awk script (which gave pretty close to the same output as the current version). I enhanced it at some point to show a different color, if the state didn’t match the target state. Then 11gR2 came out and I really didn’t like the output, so I sat down and wrote the awk script to format the 11gR2 output and added a variable at the top to choose which awk script. One of my colleagues saw it and liked the output, so I shared it; however, he asked if I could simplify the installation, as at that time you had to set the location of the script, whether it was 10g or 11g, and the location of the CRS_HOME. Finally, I sat down and decided to make it as universal and easy to use, as I could by adding the awk scripts as variables within the ksh script and adding a section to attempt to determine the CRS or HAS home automatically. It seems to work fairly well and several of my colleagues have been using it, so I thought I would share it more universally. Hope everyone finds it useful!
Read more
Table Functions: 9i Feature Worth Revisiting (Part 2)
In part I of this series I showed a simple example of how table functions can be used to treat PL/SQL collection types as tables in a SQL query. We used a nested table collection type to which we manually added a few entries. In this example, I intend to show a more practical use of table functions using all three PL/SQL collection types: associative arrays, nested tables, and VARRAYs.
Get Oracle Instant Client Working on Mac OS X Lion
CJ Travis has an excellent and thorough article on installing Oracle Instant Client on Mac OS X 10.7. The gist: you have to install the 32-bit version. As a bonus, he tells you how to download and install the handy rlwrap command line utility, which gives you the ability to navigate the statement history in SQL*Plus in a very bash-like fashion, using the up and down keys.
Table Functions: 9i Feature Worth Revisiting (Part 1)
Yes, they’ve been around for a while, but if you haven’t made use of table functions lately you might want to reacquaint yourself with this old gem. Table functions allow you to query the contents of PL/SQL collection types using SQL’s TABLE operator. Here is a simple example.
Make bash scripts crontab-compatible
When writing shell scripts in bash, consider whether your script can be automated in a batch scheduler like cron. If you reference any external resources in your script relative to the directory in which it resides, those references may break when running your script through cron.
By default, crontab runs scripts in the user’s home directory, as specified in /etc/passwd. For user “Tim” with home directory /home/tim, a sample crontab might look something like the following:
Invalidating Toplink Session Cache To Avoid Stale Data
Toplink is a Java framework that maps objects to database tables. It is the reference implementation for the Java Persistence Architecture, or JPA. Developers use Toplink to manage data persistence (storage), queries, and transactions in an Oracle database. By default, Toplink makes use of a special cache called a Session Cache, maintained on the server, which is meant to speed up performance. Queried data is stored in the Session Cache and used in subsequent queries to reduce or eliminate calls to the database.
One problem with this approach is how to deal with “stale data”. In other words, what happens when data is changing in the database? How does the developer ensure that the cached data is current?
Fixed Object Statistics Benefits in 11g
I wrote recently about fixed object statistics in 10g and used an example to illustrate
their benefit to the optimizer in one particular situation. I recently found a similar situation in an 11g (release 2) database and wanted to see if I could get the same benefit from generating optimizer statistics on the fixed objects. The automatic statistics gathering job in 11g does not collect the fixed object statistics by default, but the fact that a pre-configured optimizer job exists underscores the importance of optimizer statistics.
We will start with a typical query you would execute to find out who is accessing
certain objects in the database: select distinct owner
from v$access;
Our test system: Oracle 11.2.0.1.0 64-bit on Sun SPARC Solaris version 10
Silently removing an old ORACLE_HOME
During a database upgrade, the majority of the focus is on the pre-requisites for the upgrade, the upgrade itself, and post-upgrade tasks. Little focus is given to the downgrade process and none at all to the removal of the Oracle software used by the previous database version. I was at a new client site the other day and they were running tight on space in one particular file system. The file system contained the Oracle software for versions 8i and 10g. The single database had been upgraded from the 8i to the 10g version some years ago, but the 8i software had never been removed. It was my first time working with this environment, so I wanted to make sure I did not remove anything vital when removing the old Oracle software.
Was the tightening of space sudden or gradual?
Oracle Weblogic – Setup, Configuration, Tuning, and Considerations
This is a presentation I put together for a presentation to the Dallas Oracle User’s Group (DOUG) on March 24,2011.
It goes over the installation process of a Weblogic instance, the setup of a Nodemanager instance, connecting the two together, and the setup of a cluster. Additionally, it goes into Weblogic tuning tips and considerations for moving to and utilizing Weblogic with new and existing server environments and application developments.
Link to the presentation PDF
Fixed Object Statistics Benefits in 10g
Fixed objects statistics are often overlooked in the scheme of operations in the database. Like any database object in a production environment, these objects undergo changes and need to be analyzed from time to time to ensure that the optimizer has the latest information to be able to access them effectively. The automatic statistics gathering jobs in 10g and 11g do not collect the fixed object statistics by default, but the fact that these jobs exist underscores the importance of optimizer statistics. We are going to see in this scenario that statistics on the often neglected fixed object statistics are important as well.
We will start with a typical query you would execute to find out who is accessing certain objects in the database:
select distinct owner
from v$access;
Our test system: Oracle 10.2.0.4.0 on Sun SPARC Solaris version 10
