Oracle ASM Header Backup -- Update

This post will be an update to a post that I made on Thursday, December 09, 2010 about Oracle ASM Header Backups.  

There are multiple ways to skin this cat.  Another option for backing up your ASM file headers is to use "kfed".  Yes, I know, Oracle Support doesn't recommend that we use it.  However, if you ever need to recover the header, you will be very glad you did.  Below is a partial example of a backup using kfed:

/u01/app/oracle/product/12.1.0/grid_1/bin/kfed read ${ASM_DEV_DIR}/${i} text=${HDR_BACKUP_DIR}/${i}.${TODAY}.bkp

Utilizing "kfed read" will allow you to recover the header in case of corruption.  If you have header corruption and your other methods of header backup (like md_backup and dd's) do not work, then you can tell Oracle Support that you have a kfed file of the header from before the corruption.

As with any corruption, make sure you open an SR with Oracle Support before attempting any repair yourself.

For more information, review the information in My Oracle Support (MOS) regarding kfed.

Wow, 6 Years Without A Post

Hard to believe that six years have passed since my last post.  I've been busy apparently.  Going to start making posts again after attending a PASS webinar entitled "What SQL Server Professionals Can Learn From Writing and Blogging" by Edwin Sarmiento.  The webinar was sponsored by the Professional Development PASS Virtual Chapter.

[http://www.EdwinMSarmiento.com]

So, what is a 19 year experienced Oracle DBA doing learning Microsoft SQL Server?   I'm Starting to work with different RDBMSs and NOSQL technologies.  Which is really a blast after being limited to only one vendor.  I'm also getting to work with DataStax [Cassandra].  Who said you can't teach an old dog new tricks?

In Edwin's presentation, he talked about the 3M's with blogging:

MASTERY

• Writing helps CLARIFY your thinking
• Writing forces you to VALIDATE your assumptions
• Writing helps REINFORCE your knowledge

MARKETING

• Writing helps ESTABLISH CREDIBILITY
• Blogging builds your ONLINE REPUTATION (e.g. Brent Ozar, Pinal Dave, ...)

 

 MEETING

• Writing builds CONNECTIONS with readers
• Writing helps you see other's PERSPECTIVE
• Writing develops valuable RELATIONSHIPS

 

That is all for now.  Edwin recommended scheduling 15 minutes everyday for blogging.  My time is up ;-)

10g Impdp ORA-00959 $deleted$ Fix

Ran into a problem with an Import Datapump table restore. This is the error message:

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 06 December, 2010 17:57:27 Copyright (c) 2003, 2007, Oracle. All rights reserved. ;;; Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=hm_camp_m010.par Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39083: Object type TABLE failed to create with error: ORA-00959: tablespace '_$deleted$16$0' does not exist

Basically, bombed because the tablespace _$deleted$16$0 doesn't exist. Looked it up on MetaLink and found this document:

Import Partition Table Fails With Ora-00959: Tablespace '_$Deleted$0$0' [ID 798142.1]

There is an open unpublished bug where basic problem is that a tablespace is renamed to one that used to exist on the database. Here are the steps that will cause this problem.

create tablespace aa
create tablespace bb
drop tablespace aa
rename bb to aa

Then why it occurs is because when a tablespace is dropped, its ts$ entry is set to an invalid state, but it is not deleted. When a rename is done, if the target name already has a ts$ entry, then during the rename, because we cannot have two ts$ entries with the same name, the _$deleted$ construct is introduced. The change is not taken care of properly, and is flushed to other dictionary tables causing this problem.

The note didn't have good instructions to fix my issue. So, I'm recording what I did here:

1. Run impdp with this parameter SQLFILE=dir:name to pull the DDL for the object.
2. Modify and execute the SQL created in step 1.
3. Run impdp with this parameter CONTENT=DATA_ONLY to bypass the Metadata portion of impdp steps.

In step 2 you take the create table SQL and modify it by creating the table in a tablespace that does exist.

Hopefully, this will help you.

Oracle ASM Header Backup

One of my DBA friends shared this information with me which I reviewed and researched. Kudos to my friend.

A situation popped up recently for one team where a drive went into predictive failure, and a controller hung, corrupting the ASM headers upon reboot. This resulted them having to completely rebuild the disk groups and restore the database from tape. If the team would have had a backup of their ASM headers, all of that wouldn’t have been necessary.

ASM metadata corruption is a clear and present danger that we as DBAs have to recognize and prepare for. It is nothing to be embarrassed about, and nothing to fear, as long as you are ready for it.

Oracle 11g

md_backup

md_backup is ran from the ASMCMD> prompt. It very simply creates a file that is a backup of your metadata, for all of your disk groups, or just a few if you want to specify. By default, it writes to the current directory and backs up all of the mounted disk groups header files.

Arguments: -b and –g

-b Specifies a location to store the backup file

-g Specifies the disk group to back up

Example: ASMCMD> md_backup –b /mydir/somebackupfile –g DG1 –g DG3

This will backup disk groups DG1 and DG3 to the file /mydir/somebackupfile.

md_restore

md_restore is a bit more involved than md_backup. As you may have guessed, it restores previously backed up metadata to disk groups you specifiy.

Arguments: -b –i –t –f –g –o

-b Specifies the backup file to read

-i Ignores errors. (Normally, if md_restore hits an error, it stops. This argument suppressed that.

-t Specifies the type of disk group to be created/restored. “full” creates a disk group and restores the metadata. “nodg” restores metadata only, and “newdg” creates a new disk group with a different name using in conjunction with the –o argument.

-o Renames the disk group

-f writes the SQL commands to be performed in the restore to a file instead of executing them (sql_script_file).

-g selects the disk groups to be restored. If none are specified, it restores all of them.

Example: ASMCMD> md_restore –t full –g DG1 –i backup_file

This will restore disk group DG1 using the specified file.

Example: ASMCMD> md_restore –t nodg –g DG1 –i backup_file

This will restore an existing disk group’s metadata.

Oracle 10g

When implementing ASM backups should not only be made of the data within ASM but the disk headers should also be backed up. Oracle 10g does not provide a way out of the box to do this but Oracle 11g provides two new commands in the asmcmd utility to backup disk headers. The header of a disk is comprised of the first 4096 bytes. While not supported it is possible to use the UNIX dd to read and write these bytes to a backup file.

dd if=device name of=unique header file bs=4096 count=1

That will capture the first 4k block of the ASM file. You would do this for each file, creating a separate backup for each. A simple dd will write it back in to the header, if you need to restore from your backup. Example:

dd if=/dev/rdisk/asmvol1 of=/u01/ora_backup/asmvo1_header.bak bs=4096 count=1

This would capture the first 4k of the given ASM file. Example:

dd if=/u01/ora_backup/asmvol1_header.bak of=/dev/rdisk/asmvol1

This would write in the backed up header back into the file it was backed up from.

It is always a good idea to keep backups of your disk group metadata, because no one can know when the corruption gremlin might strike.

VIP Address Fun

During a recent RAC install, we were given an IP address for our VIP to be created at install. However, the IP address we got from network was a "private" address and not a "public" address. During the Clusterware install the vipca failed complaining that the interface is not public. As per RFC 1918 networks in the following ranges are considered private. For more details please review http://tools.ietf.org/html/rfc1918

10.0.0.0 - 10.255.255.255 (10/8 prefix)
172.16.0.0 - 172.31.255.255 (172.16/12 prefix
192.168.0.0 - 192.168.255.255 (192.168/16 prefix

Here is another link: http://www.faqs.org/rfcs/rfc1918.html

When the vipca silent installation fails because of the address range, you have to run it manually or issue the command:

$ORA_CRS_HOME/bin/srvctl add nodeapps -n crmnode1 -o /ora/ora10 -A 1.2.3.4/255.255.255.0/lan0

See Note 316583.1 VIPCA FAILS COMPLAINING THAT INTERFACE IS NOT PUBLIC for more information.

Just follow the instructions in the note and you will be fine. However, when I first followed the instructions of the note, I couldn't get the add nodeapps for the VIP to work. Turns out the note did not have the correct syntax. I pointed it out to Oracle Support and the author of the note updated the syntax. So, it is always good to verify syntax from a separate source if you have problems with Oracle documents.

Also, the cluvfy will indicate the following:

WARNING:
Could not find a suitable set of interfaces for VIPs.
Result: Node connectivity check failed.

Even though everything is OK, you are just using a non-standard private VIP.

Legacy Import Tricks

This blog entry is for those unfortunate to be stuck with the old legacy exp/imp instead of the expdp/impdp (Datapump).

We are importing a ton of data and here are some import tricks that are documented in Oracle MetaLink doc id 93763.1. The ones I'm listing here only apply to imports from direct exports. We had already done the other things in the note.

1. Increase the BUFFER size of the import
2. Create some huge redo log files to minimize the LGWR switches.

It was amazing. This MetaLink note actually works :-)

Cluvfy Open File Descriptors Failed

If your Cluvfy OS check fails for the following:

Check: Hard resource limit for "open file descriptors"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
coronet 4096 65536 failed
vail 4096 65536 failed
Result: Hard resource limit check failed for "open file descriptors".

Increase these kernel parameters in HP-UX ia64:

nfile=131072
maxfiles_lim=65536

Use kctune nfile and kctune maxfiles_lim to verify the correct values after the UNIX administrator corrects them.

More HP-UX ia64 VIP Fun

Here is a tip which will save you a headache if you are installing Clusterware on a new server.
Before you begin a new Clusterware install, make sure when you receive your servers from the UNIX admins that the VIPs are not already configured and active. However, you should have the host and IP for the Virtual IP in the domain server and local hosts file.

Do netstat -inw at a UNIX prompt to see the interfaces. You should not see the VIP interface.

If it is configured or active, you can unplumb it if it is NOT aggregated (e.g. APA):

ifconfig lan0:1 unplumb
or if it is APA:
ifconfig lan900:1 0.0.0.0

By setting the IP address to 0.0.0.0 it disables the secondary interface.
/etc/rc.config.d/netconf file has the interfaces which are started at boot up. They can be commented out to remove an interface following a reboot.

HP-UX 11.31 IA64 Memory / Filecache

Following this last install, we noticed that our HP-UX 11.31 IA64 servers were using a lot of system memory. Over half of the available memory was being eaten up by sys memory and file cache. We determined that the default settings for file cache were way to high. So, we made some adjustments.

The filecache_min and filecache_max kernel parameters are the ones to check. These should be tuned to leave more memory for Oracle. You can also mount the file system with direct IO which bypasses file buffer cache. If you are using file system for your Oracle datafiles then you want those mount points mounted with direct IO.

So, we solved it by setting constant values for filecache_min and max instead of taking the default %. By lowering them significantly, we were able to give more memory back to Oracle and the other applications.

We still have quite a bit of sys memory being utilized. However, the filecache issue is solved. I'll update this post if we figure out why so much sys memory is utilized. Or, it could be "just the way it is".

Bug 7171446 - NUMA Issues from 10.2.0.4 Patchset

Here is a NUMA bug introduced in 10.2.0.4. If you are running HP-UX IA64 and your system starts locking up or you find a number of unexpected problems such as skewed CPU usage and ORA-600 errors. Here are some details you should review in note: 7171446.8 on MetaLink.

Unless the system has been specifically set up and tuned for NUMA at both OS and database level then disable Oracle NUMA optimizations by setting the following in the pfile / spfile / init.ora used to start the instances:

_enable_NUMA_optimization=FALSE
_db_block_numa=1

At some point, Oracle should release a patch for bug 7171446. Once it is released, we will install the patch and remove the hidden parameters from the parameters.

CRS Log Directory Permissions

During this last RAC install, we ran into an issue where the crs home log directory for the one of the nodes had the wrong owner, group and permissions. So, CRS couldn't log an issue that it was having.

Here is a hint, if you are having CRS issues, always first check to make sure all of the directories exist for the CRS processes to create log files.

What was totally crazy, the other node owner, group and permissions were right. Don't know if the owner, etc. got goofed up during the patchset install for 10.2.0.4. or if it was another Clusterware merge patch. Never did figure out the why of it.

11.1 CLUVFY HP-UX ia64 Issues

For our latest install of 10.2 RAC on HP-UX 11.31 Itanium 64 bit, we used the 11g version of Cluster Verify (cluvfy). Here are the issues that we had. First, it would complain about the MTU values being different between the private and public interfaces.

Different MTU values used across network interface(s).

These different values are necessary because we are using Infiniband as the private interconnect and you want the larger Maximum Transmission Unit (MTU) value for the more robust interconnect. The public interface is a standard gigabit network so a lower value makes sense. So, we basically ignored that error because changing the Infiniband to a lower MTU value is not practical just to get a clean cluvfy before installing the Clusterware. For more info on MTU and Oracle RAC see MetaLink note: 341788.1

This is a known bug discussed in metalink note 758102.1. Root cause is BUG
7493420 fixed in 11.2. This configuration is valid since the interfaces across nodes have the same MTU. As long as the interfaces across the nodes have the same MTU, you are good to go.

The next issue had to do with shared storage. With HP-UX ia64 11.31, we created shared raw ASM LUNs then created aliases to those LUNs for the ASM diskstring. This storage is shared between the two nodes using EVA8000 storage. Cluvfy does not recognize that shared storage is available and it is working correctly. The failure message you get can be ignored. Here is the message:

Shared storage check failed on nodes "xxxxx"

In the known limitations section of the cluvfy readme, it clearly states the following:

"Sharedness check of SCSI disks is currently not supported."

If these are SCSI disks, then this error is expected as cluvfy cannot handle this check. As long as these disks can be seen from both nodes and has the correct permissions and ownership, ASM should install/work fine.

As long as your storage is working correctly, you can ignore the shared storage check because cluvfy is not able to verify multipath / autopath type software like that built in to HP-UX 11.31 using virtual disk devices on EVA8000 storage.

HP-UX Async IO

After we installed a 10.2.0.4 RAC database on an HP-UX Itanium 64-bit platform, we noticed some errors related to asynchronous IO in the database trace files. Here is the message:

Ioctl ASYNC_CONFIG error, errno = 1

After further analysis on MetaLink, and assistance from Support, we determined that asynch IO was not configured. The following are the steps that we did as root to resolve the issue:

• created /etc/privgroup. Added the following entries in the file:
• dba RTPRIO RTSCHED MLOCK
• oinstall RTPRIO RTSCHED MLOCK
• /usr/sbin/setprivgrp -f /etc/privgroup
• getprivgrp dba
• getprivgroup oinstall
• cd /dev/async
• chown oracle:dba async
• chmod 660 async

This was an interesting issue because of the Oracle 10gR2 documentation. The Oracle Clusterware and Oracle Real Application Clusters Installation Guide for HP-UX doesn't include these procedures. However, the Administrator's Reference for UNIX-Based Operating Systems does in an appendix for HP-UX.

We just had to say "Isn't that interesting..."

ASM Disk Group and Securepath

Here is a point of confusion that I've seen during ASM installs on HP-UX 11.23 using Securepath virtual devices. On one install we used one each of the underlying /dev/rdsk/xxxx paths from the autopath display to create the ASM disk groups. If you look at an autopath display, you will see the Device Path listed at the end of Lun WWN. That is what we used and it took us a while to get it straight.

The best practice is to ignore the individual devices in the path and use the secure virtual device file instead when creating your ASM disk groups. For example /hpap/dsk/hpap??

The secure virtual device takes away the confusion of the underlying devices which are just paths to the same device. Securepath handles the rest. Also, by using the secure virtual device file, the ASM disk group automatically shows up on all instances that use the same virtual device file.

Cannot Lock /etc/mnttab

Here is a strange issue. On HP-UX Itanium we couldn't reach some of our directories on the EVA5000 storage. Whenever we tried to list the contents of a couple of specific mount points it would lock up our PuTTY session. Also, we were getting the following error whenever we tried a bdf or df command:

bdf: cannot lock /etc/mnttab; still trying ...
bdf: cannot lock /etc/mnttab; still trying ...
bdf: cannot lock /etc/mnttab; still trying ...
bdf: cannot lock /etc/mnttab; still trying ...

The /etc/mnttab is the mounted file system table. Our SAN was showing that 3 disks had failed in the same 7 disk group. Oracle was trying to expdp to the mount points and NetBackup was trying to read from the same mount points. We couldn't get Oracle to shutdown or NetBackup either.

HP physically went and pulled out one of the bad disks. When the bad disk was physically ejected, Oracle came down and so did NetBackup. Turns out that there was only one bad disk in the Data Replication Group. The bad disk did not eject itself cleanly and hung up the other 2 disks. So, it looked like there were 3 bad disks. Once the bad disk was replaced, the disk group began leveling as normal. The /etc/mnttab became available and all data was present. There was no data loss. We started Oracle and everything looked fine. So, what looked like a bad issue, turned out to be a disk that was not ejected cleanly from the disk group that also locked up the /etc/mnttab file.

Go figure, still not impressed with HP storage especially the EVA5000. We have had lots of bad issues with EVA5000 storage everything from strange things like above to data corruption of the Oracle database to loosing mount points.

Opatch with RAC

I got to use Opatch for RAC today to install some one-off patches. That is a subject for later. Man, what a lot of bugs in 10.2. Opatch is cluster aware and will propagate the changes to the other nodes and relink Oracle on the other nodes. There is an issue however. You cannot have UNIX banners being used with ssh. This is the same issue that we ran into on the Clusterware install. So, we had to rename the /etc/issue.net in order to get Opatch to propagate the changes to the other nodes. Opatch actually works really well.

ORA-29701 Cluster Manager

I had something strange occur today. A user query kept returning ORA-29701: unable to connect to Cluster Manager. Therefore, I searched MetaLink for this error. But, I couldn't find anything that applied to my situation (10gR2 RAC on HP-UX Itanium). This error only showed up on one of the nodes. The other nodes and instances were fine.

After stopping everything on the node, the ASM instance would not start and immediately barked at me with the same ORA-29701 error. At this point I asked someone else that has more experience with Oracle Clusterware than I do.

They checked it out and found that somehow the ASM /dev/rdsks and special files for the OCR and voting disks had changed ownership. Someone with root access must have run insf -e to reinstall the special files. Oh, great!

A sys admin had already created a shell script to change the ownership of the /dev/rdsks to oracle:dba and chmod them to 660. So, all we had to do was ask one of our sysadmins to run the script. They also had to manually chown root:oinstall /dev/voting and chown oracle:oinstall /dev/ocr.

So, if you get an ORA-29701 and can't figure it out, check the owner and permissions of your Oracle devices.

Database Control RMAN Wizard Bug

Here is a bug that I found in our 10gR1 systems. This bug has to do with Enterprise Manager (EM) Database Control 10.1.0.5 and scheduling backups with the Recovery Manager (RMAN) wizard. Whenever you do a custom RMAN backup and choose to only backup certain tablespaces, the UNDO tablespace is never given as an option.

Now, without the undo tablespace, database recovery is not possible. However, the EM RMAN wizard will not allow you the option of adding the undo tablespace to the list of tablespaces to be selected for backup.

So, here is the workaround:

• Modify the RMAN script that is created and manually include the undo tablespace in your list of tablespaces to be backed up. Then submit your job.

I will be creating some RMAN backups for a 10.2 database soon and will let you know if this bug is also in 10gR2.

Old Dog New Tricks

With all of the assistance I've had lately, this old dog has learned some new tricks. Now, everybody I'm sure already knows these; however, in my career I've had to learn things by the seat of my pants and on the job in production.

tnsnames.ora changes do not require restart of the listener. Kind of cool. And may favorite is "lsnrctl reload". The reload resets the listener without stopping and starting it.

HP EVA8000 Autopath Tuning

Here is some information that might be useful to you. Following our RAC cluster setup and install, the EVA 8000 performance was very disappointing. First, our Oracle performance was disturbing. Then we did some more I/O benchmarks using "dd" and found that I/O performance was actually worse than the first baseline benchmark. So, this indicated a problem with the underlying storage and not the Oracle setup.

The UNIX administrators, HP, and my peers began looking into the problem. I wasn't totally engaged but want to post the information anyway. Props to my colleages. This is more system administration territory; however, as DBAs we need to know the impact of EVA tuning.

HP checked the load balancing policy on the nodes. It was set to “No Load Balancing” which greatly impacts performance. Now, how it was changed to "No Load Balancing" after the first I/O benchmarks is still a mystery.

To see the LUNs, issue the command “autopath display” as root. This will list all the LUNs and show the HP Autopath load balancing policy.

root@hostname:/ # autopath display
...
Load Balancing Policy : No Load Balancing
...

So, HP and my peers recommended that the HP Autopath load balancing policy be set to round robin for all LUNs. The autopath set_lbpolicy command sets the load balancing policy for the specified device path.

autopath set_lbpolicy <{policy name} {path}>
description: sets load balancing policy
usage: autopath set_lbpolicy <{policy name} {path}>
Policy name: The load balancing policy to set
Valid policies are
RR : Round Robin.
SST : Shortest Service Time.
SQL : Shortest Queue Length.
NLB/OFF : No load Balancing.
Path : Device Special File e.g./dev/dsk/c#t#d#

Example:
# autopath set_lbpolicy RR /dev/dsk/c0t0d0
The example above sets the policy to Round Robin.

Here is a little more information about what the policies mean:

• RR : Round Robin. - I/O is routed through the paths of the active controller in round-robin order
• SST : Shortest Service Time. - is a measurement against the average service time of the IOs on a path
• SQL : Shortest Queue Length. - is a calculation on the device queue depth when it is on a certain path.
• NLB/OFF : No load Balancing. - No consideration given to service times or queue lengths, typically impacts performance.