Welcome to the new home of the Grumpy DBA May 7, 2010
Posted by Jay Caviness in general, Uncategorized.add a comment
Thanks to all who found this blog, or came with me on the move from grump-dba.com. The content is the same as before. I am finally back after a six-month hiatus and ready to start blogging again. There is much to learn about 11gR2 and the new Enterprise Manager 11g.
Off to Oracle Openworld October 9, 2009
Posted by Jay Caviness in general, Oracle Openworld.1 comment so far
I am leaving for Openworld tomorrow and presenting on Monday. I will be at most of the IOUG RAC-SIG events. Feel free to say hello, I look forward to seeing everyone again this year.
11gR2 new features: SCAN September 22, 2009
Posted by Jay Caviness in 11gR2, RAC.Tags: 11gR2, SCAN, single client access name, VIP
10 comments
One really nice new feature of 11gR2 is the SCAN (single client access name). A scan is the single point of access for all applications connecting to an 11gR2 RAC cluster and allows consistent connections without the need to know how many nodes are in a cluster. Vips are still used internally, and can still be used for connections, but the initial connection to the cluster is made via a scan. Connections to any database in a cluster will be made via the scan. No longer will a DBA need to create those large, complicated tnsnames.ora or jdbc (thin) connection strings. All can be accessed by a scan:
sqlplus joeuser/joespasswd@dodge-scan:1521/proddb or jdbc:oracle:thin:@dodge-scan:1521/proddb
One scan is needed for each cluster, it is a single DNS entry with three IP addresses attached to the name and set to round-robin (unless using GNS, but that is another post). The IP addresses must be unused (similar to a VIP). A good naming technique would be to name the scan after the cluster it is created for. For example, one of the clusters I use regularly contains three nodes, caravan, stratus and durango, affectionately called the Dodge cluster thus the name of the scan to be used. Once the networking folks have created the DNS entry it is ready to use. The scan is created in the cluster at installation. Note that VIPs are still needed and must still reside in DNS.
Anatomy of a SCAN
How does it all work? A new set of cluster processes called scan listeners will run on three nodes in a cluster. If you have more than three nodes, that is ok, regardless of the number of nodes you have, there will be at most three scan listeners. If any of these clustered processes fail, they are automatically restarted on a new node. When a new connection request is made, the request hits DNS on the scan name, DNS will round robin and choose one of the three IP addresses assigned to that scan name and route it to the correct scan listener. Each scan listener keeps updated cluster load statistics and will then route the request to the appropriate VIP IP which is returned to the requesting service. The requesting service will then connect directly to the returned VIP as in previous versions and the connection is made through a standard listener.
Can connections still be made directly to through the VIPs? Yes, connections via the virtual IP addresses are still supported, though I cannot see a reason to use the old method as yet. Will RAC services work with the scan? Yes, there is no difference in that functionality. SCANs are a layer on top of the old method of connecting to a RAC database and do not interfere with any current methods.
The SCAN provides a simple and effective mean to connect to any and all instances in a cluster without the need to hard code connection information which can change. This has been needed for years and is one of the best new features of 11gR2 to prevent headaches for DBAs in the future.
Slewing the 11gR2 dragon September 4, 2009
Posted by Jay Caviness in 11gR2.6 comments
One of the first new pieces of 11gR2 I like is the installer, for the grid and RAC install particularly. The feature I like best is the fact that it runs cluster verify and will give the DBA the chance to fix most problems with the fixup scripts. One problem it cannot fix however is a requirement which will pop up on most systems to use the “slewing” option for ntp. The installer simply says to enable slewing with the “-x” option, but that is it. It What is slewing, and how do you set it?
Slewing is an option for the ntp daemon (ie. ntpd -x, xntp -x) to prevent time from moving backwards in large amounts. This slewing will help reduce time changes into multiple small changes, such that they will not impact Oracle Clusterware. While it has been a requirement for a long time with Oracle Clusterware, it is more strongly enforced with 11gR2 as the clusterware is more sensitive now to time changes. (This fact is also shown in the need for Oracle to create CTS (Cluster Time System) which can be created and used on the nodes of a cluster to keep them in sync with each other if NTP is not available or accessible, but that is another post).
Implementing slewing is simple. On Linux, as root, edit the /etc/sysconfig/ntpd file. Generally, it looks something like this:
# Drop root to id 'ntp:ntp' by default. OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid" # Set to 'yes' to sync hw clock after successful ntpdate SYNC_HWCLOCK=no # Additional options for ntpdate NTPDATE_OPTIONS=""
Modify the file to add the “-x” option to ntp
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"
Restart the daemon:
# service ntpd stop Shutting down ntpd: [ OK ] # service ntpd start ntpd: Synchronizing with time server: [ OK ] Starting ntpd: [ OK ] # ps -ef |grep ntp ntp 32051 1 0 10:48 ? 00:00:00 ntpd -x -u ntp:ntp -p /var/run/ntpd.pid
That is it, you can copy this file to all the nodes in your new cluster and restart the daemon. Once done, the installer will cease to bug you with the ntp reminder and you can go on building your new 11gR2 cluster.
11gR2 is Available! September 1, 2009
Posted by Jay Caviness in general.add a comment
Finally! I have been holding my breath on this announcement for almost 18 months since I was invited to be on the RAC/ASM Beta test team for 11gR2. It has been a long road and have had to keep my mouth shut myriad times at Openworld last year and IOUG Collaborate back in May. I have not been this excited about a release since Oracle 10g. This is what a grid is supposed to be. The changes to RAC and ASM alone will take me several posts, but I will get out as much information as I can as quickly as I can.
Let’s just start this way, for RAC, 11gR2 is a quantum shift. 11gR1 is essentially the same clusterware as 10gR2, fixes but no major changes. R2 redefines the game. While you can use the old style RAC clusters, R2 offers the way out of defining nodes by name and number and allows on the fly provisioning across clusters. Other new features include:
1. moving ASM into the cluster (one less Oracle_Home to worrry about)
2. storing OCR and voting disks within ASM
3. the use of a SCAN to remove the need of clients knowing VIPs
4. the use of GNS (Grid Naming Service) to take advantage of Grid Plug’n’Play in expanding a cluster on the fly
5. ASM filesystem (ACFS)
I will delve more into these and other subjects in the near future. I have been working on 11gR2 for quite a while now and while there are lofty expectations, 11gR2 will be there to meet them all.
A word of warning, however, don’t click the video tab on the link above, or you will have to stare at my mug! I couldn’t see a thing without my glasses while being filmed!
TOD – Keep Listening, Stop Logging August 24, 2009
Posted by Jay Caviness in Tip 'o the day.2 comments
Every so often I get a call from a client because their database just up and stopped. Generally that means only one of two things, either the LOG_ARCHIVE_DEST is full and the database(s) can no longer write out their deltas or the filesystem which contains the $ORACLE_HOME for said database is full. In either case Oracle will quiesce each database hosted from that home and no changes except startup and shutdown may be made to said database.
When the latter happens, the first place I look is for log files, but not just any log files. The alert log can grow rapidly, but it doesnt compare to the listener.log (or listener_<hostname>.log). Since this logfile is a record of every SQL*Net connection to the database, on busy systems it can and does fill up rapidly. Most systems no longer have a 2GB limit, thus the log can reach huge sizes and fill a filesystem before you know it.
What can be done? You can rotate the log in some fashion or clear it (>listener.log will clear a file without removing it). If deleted or moved it will recreate itself on the next write. But what if you don’t care about logging all that information? If it isn’t needed for auditing or tracing, you can shut it off by adding the following line to your listener.ora file:
LOGGING_LISTENER = OFF
or
LOGGING_LISTENER_<hostname>=OFF (if RAC or named listeners are used)
and then reload the listener, no need for a restart here. At once logging will stop and all the free space on your filesystem will celebrate the fact that it will not be soon gobbled up by a rapidly growing listener.log. If you determine that it is needed, you can remove the line (the default is to log) or set the value to ON and reload the listener.
TOD – Top 5 reasons the cluster will not start on a node. July 28, 2009
Posted by Jay Caviness in general.Tags: cluster down, CRS, crsd, cssd
add a comment
I have mentioned before the top two, but here are the top 5 most common reasons that the CRS, CSS, EVM, etc, processes will not start on a node:
1. Network issue – NIC card might be unavailable, VIP assigned to a node on a different system, switch may be bad, etc.
2. Storage issue – verify that all the voting and OCR disks/luns are accessible on all nodes and are owned appropriately (OCR – root:dba & 640, Voting Disks – oracle:dba * 640). You can test by dumping with dd and piping to strings – dd if=/dev/raw/raw2 | strings .
3. Too many sockets – look in /tmp/.oracle and /var/tmp/.oracle and remove any and all socket files. These can build up between reboots after many failed attempts to start the cluster.
4. Filesystem full – if the filesystem containing CRS_HOME is full, or becomes full during the start of a cluster, it will just hang for the 600 second timeout and die. If it can’t write a log, it won’t start up.
5. /etc/oracle missing or corrupt. The /etc/oracle directory is created when root.sh is run for CRS. it contains the OCR location and other files needed by the cluster. If CRS cannot find the location of the OCR files, it will look in default locations and then simply hang while searching for the default 600 seconds.
As always, search out the logs in $CRS_HOME/log/. Expecially the alert.log and the files in crsd, cssd, and client will, while sometimes difficult to decipher, help you find the reason for the cluster problems.
Keep on RAC’n!
New format for Grumpy-DBA.com July 14, 2009
Posted by Jay Caviness in general.add a comment
I am experimenting with a new format on my blog site. I had received some comments that with the three column format, long text boxes were being cut off. I started using this theme with only one side column to allow for a wider post format.
TOD – ASM diskgroup space from any database July 14, 2009
Posted by Jay Caviness in ASM, Tip 'o the day.add a comment
Have you ever noticed that v$asm views are available in all databases? This is true whether using ASM or not because the ASM home and software is the same as the database software. On this note, while the DB and ASM can share a home, they should not in order to preserve the ability to upgrade ASM separately from the database home. Simply, ASM should always be at or above the highest database software version, but that is another post.
Ok, now that I am back from that tangent. I have had users call me in a panic when they run ASM scripts in a database instead of the ASM instance. When querying v$asm_disk and checking on the physical disk/luns, it will always report 100% used:
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;
Disk Group Name Path File Size (MB) Used Size (MB) Pct. Used
--------------- ----------------- -------------- -------------- ---------
DGROUP1 ORCL:VOL001 33,791 33,791 100.00
ORCL:VOL002 33,791 33,791 100.00
ORCL:VOL003 33,791 33,791 100.00
ORCL:VOL008 33,791 33,791 100.00
ORCL:VOL005 33,791 33,791 100.00
ORCL:VOL006 33,791 33,791 100.00
ORCL:VOL007 33,791 33,791 100.00
ORCL:VOL004 33,791 33,791 100.00
*************** -------------- --------------
270,328 270,328
-------------- --------------
Grand Total: 270,328 270,328
Which can induce great panic as it appears that ASM is full, but when properly run from ASM:
Disk Group Name Path File Size (MB) Used Size (MB) Pct. Used
--------------- ----------------- -------------- -------------- ---------
DGROUP1 ORCL:VOL001 33,791 24,925 73.76
ORCL:VOL002 33,791 24,911 73.72
ORCL:VOL003 33,791 24,910 73.72
ORCL:VOL008 33,791 24,921 73.75
ORCL:VOL005 33,791 24,921 73.75
ORCL:VOL006 33,791 24,905 73.70
ORCL:VOL007 33,791 24,932 73.78
ORCL:VOL004 33,791 24,919 73.74
*************** -------------- --------------
270,328 199,344
-------------- --------------
Grand Total: 270,328 199,344
Some users, including my own developers, need to know how much space is available on the diskgroup from within their own database. It was thought that we had to query asm via SQL*net or have ASM populate a file to be brought into the database to determine how much space was available. This idea has several problems, it is clumsy and complicated, and it is not up to date and must be refreshed often. I noticed, however, what while you cannot query v$asm_disk and get accurate results, you can query v$asm_diskgroup and get accurate results from any database.
SELECT
name group_name
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name;
Disk Group
Name Type Total Size (MB) Used Size (MB) Pct. Used
---------------- ------ --------------- -------------- ---------
DGROUP1 EXTERN 270,328 199,344 73.74
--------------- --------------
Grand Total: 270,328 199,344
This query will report the same data from both the ASM instance as well as any databases on the same server. The interesting thing is, even if the database is NOT using ASM, it will report as above since every (10g or 11g) database on a host communicates with ASM via its own “mini-cluster” (ocssd) if not RAC and the RAC cluster if it is.
TOD – Recovering a database past the current controlfile July 10, 2009
Posted by Jay Caviness in Backup and Recovery, Tip 'o the day.1 comment so far
Unless you have a great many database backups to manage, most DBAs using RMAN use the control file as a catalog instead of a catalog database. This simplifies management, but can make some types of recovery difficult. In a typical non-catalog RMAN recovery, the control file is restored and the instance is started for an RMAN restore and recovery. This method will provide a complete recovery to the point that the control file was backed up.
What happens, however, if that control file is lost? You can recover to the previous day’s backup (if you have it), but subsequent backups of the archive logs and then a full backup will be stored in a new control file backup. This is exactly what happened at a client of mine a few weeks ago. The backup script failed to back up the final archive log after the full backup. Due to Murphy’s Law, that was the exact backup that was needed to restore after a storage issue. Without the final archive log, the database threw the classic error:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘+DGROUP1/dmlive/datafile/system.294.689876999’
I had no previous copy of the control file except that of the night before, I did, however, have all of the archive logs in an RMAN backup set. But how to tell the control file that they exist? With a catalog database, all the backupsets already recorded, but the control file method is only as good as its last writing by RMAN. There is a method, however.
Using the previous nights control file, use RMAN to restore and recover all database and archivelogs. Do NOT try to open the database. Find the filename of the backupset that contains the archivelogs that are needed, and run the following command withing RMAN:
catalog backuppiece
'/backups/DMLIVE_9497_1_689621187_2009_Mon_full';
Once this is complete, restart the recovery using the same control file to roll forward through all the newly cataloged archive logs. Once complete, open the database with the resetlogs option and you are all set.
Happy recoveries!
The Grumpy DBA Blogs . . . 