ASM File Locations

From Mike Ault's book Oracle 10g Grid & Real Application Cluster, the following shows the files locatable on the ASM instance and files that have to reside on the file system:

On the ASM Instance:

• Data Files
• Redo Files
• Control Files
• Archive Log Files

On Raw Partitions:

• Voting Disk
• OCR File

On the Local File System:

• Oracle Home Files
• Clusterware Home Files
• ASM Instance Home Files
• Alert Log, Trace Files
• Files for External Tables
• utl_file_dir location

X11 Forwarding From SUDO

X11 forwarding is not working using PuTTY and ssh for users once you "become" them using SUDO. Here is the error message:

$ Xlib: connection to "xxx.xx.xx.xxx" refused by server
Xlib: PuTTY X11 proxy: wrong authentication protocol attempted
Error: Can't open display: xxx.xx.xx.xxx:xx.x

Below are the steps used to successfully transfer xauth information to another user (Oracle in this case)

1. Enable X11 forwarding on your terminal application and login as you (as stated above)
   ‘chmod 644 .Xauthority’ (needs to be done every time service account needs access, it will reset when you log out)
2. ‘become service account x’
3. ‘xauth merge ~username/.Xauthority’ (needs to be done every time service account access is needed)

Once you get a copy of the .Xauthority file to /home/oracle it should work.

10gR2 RAC Installation Guide

Here is something interesting. The HP-UX Itanium platform doesn't have it's own configuration guide in the Oracle documentation. You have to use the Oracle Clusterware and Oracle Real Application Clusters Installation and Configuration Guide for hp HP-UX PA-RISC (64-Bit). However, the Itanium platform instructions are included in the document also.

Here is my advice. Before beginning the install of RAC read all of the Installation Guides specific to the release and OS that you will using. Don't just rely on the one installation guide. Read the Release Notes also. And read the Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide. Check for documentation updates.

I plan on following the installation guide step by step. Will create other posts for the sections of the install that are different for HP-UX Itanium or are not clear in the Installation Guide.

Here are some notes that I gathered from the RAC Installation Guides which I felt were noteworthy:

• You must install Oracle Clusterware and Oracle Database in separate home directories. If you will use multiple Oracle Database homes with ASM, then you should install a separate Oracle Database home for ASM. You should create the listener in the Oracle Database Oracle home.
• With Oracle Database 10g Release 2 (10.2) with RAC, CSS has been modified to allow you to configure CSS with multiple voting disks. In 10g Release 1 (10.1), you could configure only one voting disk. By enabling multiple voting disk configuration, the redundant voting disks allow you to configure a RAC database with multiple voting disks on independent shared physical disks. If you intend to use multiple voting disks managed by Oracle Clusterware, then you must have at least three disks to provide sufficient redundancy, and you must ensure that each voting disk is located on physically independent storage.
• For OCR: Configure one disk if you have existing redundancy support. If you intend to use OCR mirroring managed by Oracle Clusterware, then you must have two OCR locations, and you must ensure that each OCR is located on physically independent storage.
• Although you can specify a logical volume as a device in an ASM disk group, Oracle does not recommend their use. Because logical volume managers can hide the physical disk architecture, ASM may not operate effectively when logical volumes are specified as disk group devices. (Got to find out about this one because we are going to use logical volumes with ASM)
• Cluster Verification Utility (CVU) is a tool that performs system checks. CVU is used to assist you with confirming that your system is properly configured for Oracle Clusterware and Oracle Real Application Clusters installation. CVU does not check kernel parameter settings. This issue is tracked with Oracle bug 4565046.

Oracle recommends that you use the following Oracle Database 10g features to simplify RAC database management:

• Oracle Enterprise Manager—Use Enterprise Manager to administer your entire processing environment, not just the RAC database. Enterprise Manager lets you manage a RAC database with its instance targets, listener targets, host targets, and a cluster target, as well as ASM targets if you are using ASM storage for your database.
• Automatic undo management—This feature automatically manages undo processing.
• Automatic segment-space management—This feature automatically manages segment freelists and freelist groups.
• Locally managed tablespaces—This feature enhances space management performance.

In addition to this being my first RAC install, this is also my first time to use ASM (Automatic Storage Management). Am I a glutton for punishment or what? New to RAC and ASM at the same time.

Setting up the VIPs with HP APA

Well, we had our first question - setting up the VIPs along with HP Auto Port Aggregation (APA). This is even before we attempt to install Oracle.

First some background, VIP stands for Virtual IP address. And for more on HP APA, see a previous post of mine. When installing 10g RAC you need at least a minimum of 3 network interfaces for each node in the RAC cluster.

• A public interface for normal network communications to the node or partition.
• A virtual (public) interface which will be used for failover in case the primary public interface fails. Also used for RAC managment.
• A private interface for the cluster interconnect.

We have 4 software APA NICs (Network Interface Cards) and two regular Gb NICs on each node of the RAC cluster. So, here are our questions:

• How do we use VIP with APA?
• Do they have to be on the same network subnet?

As always, the answer to the first question can be found in a MetaLink document. (296874.1) Configuring the HP-UX Operating System for the Oracle 10g VIP. As stated, there are 2 ways of configuring HP-UX systems for network redundancy to be used for the Virtual IP:

• Oracle VIP with MC/ServiceGuard configured networks only, via multiple physical interfaces on many redundant networks.
• Oracle VIP with APA(i.e. NIC teaming) only via a single logical interface on many redundant networks.

HP Auto Port Aggregation (APA) is a software product that creates link aggregates, often called "trunks," which provide a logical grouping of two or more physical ports into a single "Fat-Pipe". The link aggregates can be active/active(APA aggregate) or active/standby(hot standby mode). An IP is configured only on the single logical interface (usually lan90X), and failure to a single NIC would be transparent to applications that are dependent on a specific interface name.

Auto Port Aggregation(APA) is a NIC teaming solution provided by HP. Although APA is not required when using MC/ServiceGuard (since MC/ServiceGuard has its own network redundancy solution), it is worthwhile to note that a NIC teaming solution can provide highly available VIPs. APA will configure 2 physical NIC's to 1 logical NIC interface. It is usually configured to be lan90x. All that needs to be done for the VIP is to configure it on that 1 logical NIC, similar to what is done on a single NIC configuration.

The answer to the second question is a simple "Yes". The public interface and the virtual interface must both be on the same subnet.

10gR2 RAC Install -- Soon

Soon, I will get to install 10gR2 RAC for the first time. I will be working with another DBA who has RAC experience. The install will be on 3 nodes with HP-UX Itanium operating systems. Of course the first thing to install is the Oracle Clusterware. With Oracle Database 10g Release 2 (10.2), Cluster Ready Services, or CRS, is now called Oracle Clusterware.

With the large number of bugs in 10gR2 RAC, I expect some real challenges and multiple patches. There should be lots of entries on this blog to document the "fun".

ORA-12516 or TNS-12516

I found an error that is new to me: ORA-12516 TNS:listener could not find instance with matching protocol stack

Every new remote connection into the database from GUI tools like TOAD and SQL Developer received this error. The listener was blocking connections. The problem went away on its own after the server and Oracle became less busy. The server was being slammed at the time of the errors.

(A friend of mine calls GUI tools point-click and drool environments)

After further research and a Service Request with Oracle Support, this is what I found. The error can happen when resources are low. Apparently, when resources are at a premium the listener can block new connections and return this ORA-12516. By increasing the initialization parameter PROCESSES, I hope to keep this from happening again.

Kind of a weird error and response from Oracle whenever resources are low. Oh well, strange error messages keep us gainfully employed.

See Oracle Note: 240710.1 on MetaLink for more information.

Security Checklist

Ok, I admit it. I'm stuck on Audit and Security right now. But, we as oracle DBAs are being bombarded by requirements from everybody because of the malicious attacks by hackers and other countries on our technology infrastructure. During my last audit, an auditor wanted to see my security checklist. So, I gave it to them. In order to continue the theme of this blog in providing help, here is one of my favorite security checklists:

http://www.oracle.com/technology/deploy/security/pdf/twp_security_checklist_db_database.pdf

Again, another white paper from Oracle. Use this checklist as a basis for your own and in accordance with your company's security requirements. Don't you just love audits and security?

How often do you apply the quarterly security patches? Did you know that each patchset released (e.g. 10.1.0.5) also incorporates the last security patch available during that time?

Food for thought and to chew on.

OS Authentication

I found an excellent SECURITY resource white paper at Oracle Technology Network. It is called "Project Lockdown" by Oracle Ace Arup Nanda. Here is the link:

http://www.oracle.com/technology/pub/articles/project_lockdown/index.html

In particular, I was interested in what Mr Nanda had to say about OS Authentication because it is becoming more of a requirement in companies to keep users from imbeding user and password information in UNIX and SQL applications.

I used to think that OS Authentication removed an additional security control by not forcing an Oracle login and password. However, with the more secure Operating Systems (particularly the big three Sun, HP, and IBM), it becomes a valid option. If your OS is not rock solid and very secure, then I would definitely not use it. If you do use it, then remote OS authentication should be disabled. Disabling remote OS authentication should be a requirement.

This article is definitely required reading. All the procedures in the article should be implemented for the systems you manage. I plan on following these procedures and using this article in my next ET audit.

Thank-you Arup Nanda

9iR2 Datafile Queries

Found that I had some 9iR2 tablespaces which the space was not being evenly distributed across all of the datafiles. Some datafiles had over 50% space utilized and others had < 20% utilized. Seems to be related to the use of uniform extents. However, I haven't proved it yet. Strange how the data is not evenly distributed across the datafiles. Used 9i OEM to catch it. Didn't have any SQL but found the following. This is for those that prefer not to use the point and click GUI approach.

select substr(a.file_name,1,50) as file_name,
substr(a.file_id,1,4) as file_id,
a.bytes as allocated,
nvl(b.free,0) as free,
a.bytes-nvl(b.free,0) as used
from dba_data_files a,
( select file_id, sum(bytes) free
from dba_free_space
group by file_id ) b
where a.file_id = b.file_id (+)
and a.tablespace_name = 'TABLESPACENAME'
order by 1;

Here is a query to show the contents of a particular datafile. The input is the datafile path and name:

REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on DBA_EXTENTS and DBA_DATA_FILES
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Cary Millsap, Oracle Corporation
REM Copyright (c) 1991 by Oracle Corporation
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The following script prints a record of each ORACLE database
REM segment (table, index, cluster, rollback segment, temporary
REM segment or cache segment) physically stored in a datafile.
REM This information is useful to determine the contents of a
REM database file identified as particularly hot in a SQL*DBA
REM monitor of ORACLE file I/O.
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
define fname = &&1

ttitle left 'Database Segments in File &fname' skip 2

col stype format a12 justify c heading 'Segment Type'
col sname format a66 justify c heading 'Segment Name'

break on stype skip 1

select
segment_type stype,
owner'.'segment_name sname
from
dba_extents ext,
dba_data_files fil
where
fil.file_name = '&fname'
and
ext.file_id = fil.file_id
order by
segment_type,
segment_name
/

undef fname
set verify on

Using tnsping

tnsping is useful in determining whether your Orace NET is working without logging in to the remote database. Here are some Oracle notes on it:

The TNS ping utility (usually named tnsping.exe) can be used to determine whether or not a service on a TNS network can be successfully reached. The service being contacted is an Oracle Listener. If tnsping successfully pings, it will display an estimate of the round trip time to reach the service. If it fails, it will display a message describing the error that occurred. This allows you to see the network error that is occurring without the overhead of a database connection. Tnsping is typically invoked on the command line as follows: tnsping service count

Even though tnsping shows time estimates, it should not be used to test network performance. A very good Oracle note on Oracle NET performance is 67983.1

Encrypting Oracle Net Traffic

I've gotten some questions about encryption of the Oracle Net data thru the network and thought that I would post some information. The following contains information that I got from a conversation with Oracle Support.

Without encryption, (using the Oracle Advanced Security Option OAS or network encryption) the data leaving the server or client via Oracle Net will be transmitted in the clear. And, although switched and routed networks in the industry today minimize eavesdropping on broadcast data, the possibility of eavesdropping still exists without encryption. Everything beyond the local zone, data center and DMZ (or secured zone) to the outside should be encrypted by your routers. Additionally, physical security prevents entry to the secured zones.

These days, to actually sniff packets from a routed/switched network, a network analyst needs to shadow a port in a router to even hookup a packet capturing tool like a Network General (<====OLD COMPANY) Sniffer. However, it is possible to shadow/mirror a router port at any router along the path of traffic and reach some clear text data from within the packets as long as the data is not encrypted using the OAS and as long as another method of encryption has not yet been employed along the traffic path.

Physical security and Human Resources screening are more important to companies than worrying about Oracle Net traffic to the listener port.

Bottom line:

I can’t imagine the expertise required to find and enter a secured zone network much less creating a shadow port. Sounds like something from Mission Impossible to me. If you have some network paths which are not encrypted, then install Oracle Advanced Security (OAS) which comes free with the Oracle Enterprise Edition software.

Back From Vacation

Well, I'm back in the saddle now. Been on vacation and just been too busy with work to keep my blog up-to-date. Went on vacation at Cape San Blas in Florida. I would highly recommend it. The beach was not crowded at all and was beautiful. What a great way to clear out the DBA cobwebs by enjoying the Gulf of Mexico beach. Now for some more posts...