How to Shrink the Datafile of UNDO Tablespace

Goal
Your production database has bi-annual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on file system.
The purging process is run only few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don’t want to buy additional disks unnecessarily.

You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA 1651 : unable to extend save undo segment by in tablespace .

You have tried “alter database datafile .. resize” which always fails with Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value.

You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.
Solution

– Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile ‘undorbs1.dbf’ size 100m;

– Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

– Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.

NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace ‘%s’ is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

Points to Consider:

- The value for undo_retention also has a role in growth of undo tablespace. If there is no way to get the undo space for a new transaction, then the undo space (retention) will be reused. But, if the datafiles for undo tablespace are set to auto extensible, it will not reuse the space. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.

- Is big really bad? Overhead on larger file/tablespaces can theoretically impact the database and the OS. With a small file, the OS would have to do minimal I/O. Oracle would be able to cache the whole file and there would be less segments to manage. With AUM you get bitmapped files and all its (space management) performance benefits– (number of) undo segments are automatically managed and are not related to the size of the tablespace. With the bigger file/tablespace you will have other overhead–e.g. backup will take longer–but as far as the undo management there should be no performance impact just because the file/tbs is bigger. That said, it is important to monitor systems (e.g. with statspack) and watch for environment-specific issues.

Managing Rollback/Undo Segments in AUM (Automatic Undo Management)

Managing Rollback/Undo Segments in Automatic Undo Management:
=============================================================

This new feature simplifies and automates the management of undo segments.

DBAs have the choice to manage rollback segments as they used to do under
versions Oracle7, Oracle8, and Oracle8i, or to let the RDBMS do it.

There are now two modes of rollback segments management and usage:

* AUTOMATIC or
* MANUAL

To distinguish between the two types of segments, ROLLBACK segments are called
UNDO segments when AUM is enabled.

In both cases, rollback/undo segments are still the only way for transactions
to execute and complete. This means that with either method, rollback/undo
segments are present in the database and use disk space.

*******************
INIT.ORA parameters
*******************

1. UNDO_MANAGEMENT can be set to AUTO if you want the RDBMS to manage undo
segments automatically:

– RDBMS creates them when you create a new UNDO tablespace
– RDBMS alters them ONLINE/OFFLINE when you choose a specific UNDO
tablespace
– RDBMS drops them when you drop an UNDO tablespace

In this case, DBAs cannot manage undo segments at all, though they still do
exist as “rollback” segments.

Note: Though you can create rollback segments in UNDO tablespaces, it is
strongly recommended not to do it.

UNDO_MANAGEMENT can be set to MANUAL if you want to keep the control on
rollback segments.

2. If you decide to use AUM, you have to create at least one UNDO tablespace to
store the undo segments automatically created.

Even if AUM uses only one UNDO tablespace at the instance level, you can
create several UNDO tablespaces. In this case, specify which UNDO tablespace
is to be used:

UNDO_TABLESPACE=rbs

SQL> select name,value from v$parameter
where name in (‘undo_management’,'undo_tablespace’);

NAME VALUE
———————————— ——————————
undo_management AUTO
undo_tablespace RBS

Having several UNDO tablespaces available in the database provides the
possibility to switch and use a different tablespace with smaller or
larger global size for different purposes of usage, such as OLTP, BATCH.

*************************
UNDO Tablespaces Creation
*************************

1. You create the UNDO tablespace at database creation. (Refer to
Note 135053.1 How to create a database with Automatic Undo Management).

2. Or after database creation:

SQL> create undo tablespace UNDO_RBS1
datafile ‘undorbs1.dbf’ size 100m;
Tablespace created.

********************************
UNDO Tablespaces Characteristics
********************************

1. They are locally-managed with system extent allocation:

SQL> select TABLESPACE_NAME, CONTENTS,
EXTENT_MANAGEMENT, ALLOCATION_TYPE,
SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces where contents=’UNDO’;

TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
—————————— ——— ———- ——— ——
RBS UNDO LOCAL SYSTEM MANUAL
UNDO_RBS1 UNDO LOCAL SYSTEM MANUAL

2. You cannot use UNDO tablespaces for other purposes than UNDO SEGMENTS and
cannot do any operation on system generated undo segments:

SQL> create table T (c number) tablespace undo_rbs1;
create table T (c number) tablespace undo_rbs1
*
ERROR at line 1:
ORA-30022: Cannot create segments in undo tablespace

SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
create rollback segment undo_rs1 tablespace undo_rbs1
*
ERROR at line 1:
ORA-30019: RBU Rollback Segment operation not supported in SMU mode

Note: You can create rollback segments on an UNDO tablespace while the
database runs in manual mode, but it is useless since these rollback
segments cannot be set online when running in AUM mode.

3. Only one UNDO tablespace can be used at the instance level:

=> use UNDO_TABLESPACE=rbs in init.ora parameter file to set it before
instance startup
=> or use the SQL command to change the UNDO tablespace during instance
life:

SQL> alter system set undo_tablespace=undo_rbs1;
System altered.

**************************************
Rollback Segments versus UNDO Segments
**************************************

1. When creating an UNDO tablespace, these are automatically created:
* n undo segments (based on SESSIONS parameter value)
* named as _SYSSMUn$
* owned by PUBLIC (usable for OPS configuration)
* not manually manageable

SQL> select owner,segment_name,tablespace_name
from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME
—— —————————— ——————————
PUBLIC _SYSSMU1$ RBS
PUBLIC _SYSSMU2$ RBS
PUBLIC _SYSSMU3$ RBS
PUBLIC _SYSSMU5$ RBS
PUBLIC _SYSSMU7$ RBS
PUBLIC _SYSSMU9$ RBS
PUBLIC _SYSSMU10$ RBS
PUBLIC _SYSSMU8$ RBS
PUBLIC _SYSSMU6$ RBS
PUBLIC _SYSSMU4$ RBS
SYS SYSTEM SYSTEM
PUBLIC _SYSSMU11$ UNDO_RBS1
PUBLIC _SYSSMU12$ UNDO_RBS1
PUBLIC _SYSSMU13$ UNDO_RBS1
PUBLIC _SYSSMU14$ UNDO_RBS1
PUBLIC _SYSSMU15$ UNDO_RBS1
PUBLIC _SYSSMU16$ UNDO_RBS1
PUBLIC _SYSSMU17$ UNDO_RBS1
PUBLIC _SYSSMU18$ UNDO_RBS1
PUBLIC _SYSSMU19$ UNDO_RBS1
PUBLIC _SYSSMU20$ UNDO_RBS1

2. If you choose to use AUM, you have no chance to manage any undo or rollback,
even on an non UNDO tablespace.

SQL> create public rollback segment rs1 tablespace system;
create public rollback segment rs1 tablespace system
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

3. Only undo segments of the active UNDO tablespace and the SYSTEM rollback
segment are kept ONLINE. All other rollback segments and undo segments of
other UNDO tablespaces are OFFLINE.

Nevertheless, not all undo segments of the active UNDO tablespace are ONLINE
at startup: this depends on the SESSIONS parameter. For example, if 10 undo
segments exist and you startup the instance with a lower SESSIONS parameter
value, the existing UNDO segments are kept but only a few of them are
onlined. The OFFLINE undo segments of the active UNDO tablespace are onlined
when more transactions require the use of offlined undo segments.

*******************************************************
Automatic Undo Management and Real Application CLusters
*******************************************************

The undo space management feature is also useful in Real Application Clusters
environments.

1. All instances within Real Application Cluster environments must run in the
same undo mode.

2. Set the global parameter UNDO_MANAGEMENT to AUTO in your server parameter
file.

If you use client-side parameter files, the setting for UNDO_MANAGEMENT must
be identical in all the files.

3. Set the UNDO_TABLESPACE parameter to assign the appropriate undo tablespace
to each respective instance. Each instance requires its own undo tablespace.
If you do not set the UNDO_TABLESPACE parameter, each instance uses the
first available undo tablespace.

Remarks
——-
1. There is another undo_ init.ora parameter: UNDO_SUPPRESS_ERRORS.

Use it very carefully: set to TRUE, it suppresses any error message issued
when attempting manual operations while in AUTO mode.

SQL> alter rollback segment “_SYSSMU1$” online;
Rollback segment altered.

SQL> alter rollback segment “_SYSSMU13$” offline;
Rollback segment altered.

SQL> alter rollback segment rs1 online;
Rollback segment altered.

All these statements seem to have executed the operation, but in reality did
not do anything.

2. Like rollback segments dropped MANUALLY, queries that need to access the
transaction undo information residing in a dropped UNDO tablespace may
result in ORA-01555 “snapshot too old (rollback segment too small)” error,
if the snapshot is older than the DROP-SCN of the UNDO tablespace.

CRS Integrity Check Fails When Using cluvfy

CRS integrity check fails when using cluvfy

Symptoms

CRS integrity check fails when using cluvfy.
But CRS installation is proper and working fine

——————————————————————-
cluvfy comp crs -n node-1,node-2 -verbose

Verifying CRS integrity

Checking CRS integrity…

ERROR:
CRS is not installed on any of the nodes.
Verification cannot proceed.

CRS integrity check failed.

Verification of CRS integrity was unsuccessful on all the nodes.
——————————————————————-

Cause

Problem in locating the correct Oracle Inventory.

The /etc/oraInst.loc is pointing to a wrong location

Cluvfy internally uses the inventory location and if proper inventory
location is not found, it will report this error

Solution

Check the /etc/oraInst.loc file and and see if it is pointing to the
correct inventory location.

If it is not, correct it to point to the the actual inventory location and
then retry the cluvfy operation.

How To Install Oracle 10g Grid Agent on RAC

How To Install Oracle 10g Grid Agent On Rac

Solution

The Grid Control agent installer will automatically detect installed clusterware and will provide an option to install the agent on every cluster member during the installation.

The current session’s environment can affect clusterware detection and discovery process. Please consider the following:

1. Ensure that your cluster hostnames are resolveable. Also, ensure that your /etc/hosts file on each cluster member node lists node names and IP addresses properly.
Example:

127.0.0.1 localhost.localdomain localhost << This line should not list the hostname of the server
10.10.10.1 node1.domain node1
10.10.10.2 node1-vip.domain node1-vip
.
.
etc…

2. Ensure that you remove any environment variable with a space ” “, a double colon “::”, or a semi colon “;” (in the variable name or its value) before launching the agent installer.

Example:

SSH_CLIENTS=aaa.bbb.ccc.ddd aaa.bbb.ccc.def
PATH2=/var/opt/java/bin;/home/oracle/scripts;

3. Be sure to remove any ORACLE_HOME, NLS, LIB, or any environment variable you would set for an Oracle product from the current session where you will be installing the agent. Also, make sure that your software owner user default login script does not set Oracle specific environment variables.

4. Ensure that the Grid Control OMS is up and running.

5. If you choose to use a seperate inventory for the agent than any other installed Oracle software, you can selectively add each inventory to the agent after installation. Please see Grid Control Advanced Configuration Guide for details.

6. If you have configured CRS to use a non-default cluster name, then skip to step 6. Otherwise, to use a non-default cluster name (the default is crs), you can pass CLUSTER_NAME= argument when you run the installer.

Example:

./runInstaller -CLUSTER_NAME=myTestCluster

(to find your cluster name, see Note below)

7. Run the agent installer and select all cluster nodes.

8. Optionally, you can run the installer in silent mode by editing the response file and providing values for the following parameters:
BASEDIR, CLUSTER_NODES, sl_OMSConnectInfo

Make sure these parameters are uncommented in the response files then kickoff the silent install

This should complete successfully.

Save responsefile to the some physical location and issue command as below
runInstaller -silent -responseFile -noconfig

If you run into problems after the install stage has completed, but the Agent Configuration Assistant fails, you may be able to re-run “agentca” after exiting OUI:

– Run through steps 1 through 6 above
– Go to the agent10g home and cd to the bin directory
– Run the following command:

./agentca -f -n -c -i

The agents on each node should be configured and started successfully.

– Check the status of agents on each node:

./emctl status agent
For more details, please refer to Grid Control Installation and Basic Configuration

5.5.1.1.1 Standalone Agent Install Using the agentDownload Script
The agent can be installed by executing the agentDownload script on one node at a time using the correct arguments. If you want to discover the cluster targets, then you must set the CLUSTER_NAME environment variable prior to executing the agentDownload script, or pass the cluster name to the agentDownload script as
agentDownload -n

Note: To find out the value of your CLUSTER_NAME from CRS (OCR), do the following from the CRS_HOME:
cd $CRS_HOME/bin
./ ocrdump
this will create a text file called OCRDUMPFILE
open that file and look for this entry
[SYSTEM.css.clustername]
ORATEXT : crs_cluster
In this case, “crs_cluster” is the cluster name.

Note: If the CLUSTER_NAME environment variable is not set, the agent cannot discover any Cluster targets.

Table 5–2 Command-line Options for Cluster Installation Options Description
-c This option is followed by a comma-separated list of the nodes in the cluster. It is possible to install a standalone agent that will still discover cluster targets by not specifying this option while still specifying the cluster name with the -n option.

-*n This option is followed by the name of the cluster. This option is required for 9i clusters; for 10g clusters, if this option is not specified, the default cluster name from the targets.xml file will be used.*

-l This option specifies that the install should be done just on the local node of the cluster.

CLUSTER VERIFICATION UTILITY FAQ

CLUSTER VERIFICATION UTILITY FAQ

Concept
What is CVU? What are its objectives and features?
What is a stage?
What is a component?
What is nodelist?
Do I have to be root to use CVU?
What about discovery? Does CVU discover installed components?
What about locale? Does CVU support other languages?
@How do I report a bug?

Installation
What are the requirements for CVU?
How do I manually install CVU?
From where can I download CVU?
What Linux versions are supported ?
How do I make Cluvfy work with Suse 9 ES?
What Windows versions are supported?
What Solaris versions are supported?
What AIX versions are supported
What HP-UX versions are supported?

Usage
How do I know about cluvfy commands? The usage text of cluvfy does not show individual commands.
What are the default values for the command line arguments?
Do I have to type the nodelist every time for the CVU commands? Is there any shortcut?
How do I get detailed output of a check?
How do I check network or node connectivity related issues?
How do I check whether OCFS is properly configured?
How do I check the CRS stack and other sub-components of it?
How do I check user accounts and administrative permissions related issues?
How do I check minimal system requirements on the nodes?
Can I check if the storage is shared among the nodes?
Is there a way to compare nodes?
Why the peer comparison with -refnode says “passed” when the group or user does not exist?
Is there a way to verify that the CRS is working properly before proceeding with RAC install?
At what point cluvfy is usable? Can I use cluvfy before installing CRS?
How do I turn on tracing?
Where can I find the CVU trace files?
Why cluvfy reports “unknown” on a particular node?
What does cluvfy error “Could not find a suitable set of interfaces for VIPs” mean?
Where can I find the disk rpm?

General Questions:
How do I check that user equivalence through SSH is setup properly?
How can I check the requirements for installing Oracle Clusterware or RAC from Oracle Database Release 10g Release1 (10.1) ?
What is CVU`s configuration file? How do I use it?
How do I run CVU from installation media?
What database versions are supported by CVU?

Limitations:
What are the known issues with gf release?
What kinds of storage does cluvfy check for shared-ness?

What is CVU? What are its objectives and features?
CVU brings ease to RAC users by verifying all the important components that need to be verified at different stages in a RAC environment. The wide domain of deployment of CVU ranges from initial hardware setup through fully operational cluster for RAC deployment and covers all the intermediate stages of installation and configuration of various components. The command line tool is cluvfy. Cluvfy is a non-intrusive utility and will not adversely affect the system or operations stack.

What is a stage?
CVU supports the notion of Stage verification. It identifies all the important stages in RAC deployment and provides each stage with its own entry and exit criteria. The entry criteria for a stage define a specific set of verification tasks to be performed before initiating that stage. This pre-check saves the user from entering into a stage unless its pre-requisite conditions are met. The exit criteria for a stage define another specific set of verification tasks to be performed after completion of the stage. The post-check ensures that the activities for that stage have been completed successfully. It identifies any stage specific problem before it propagates to subsequent stages; thus making it difficult to find its root cause. An example of a stage is “pre-check of database installation”, which checks whether the system meets the criteria for RAC install.

What is a component?
CVU supports the notion of Component verification. The verifications in this category are not associated with any specific stage. The user can verify the correctness of a specific cluster component. A component can range from a basic one, like free disk space to a complex one like CRS Stack. The integrity check for CRS stack will transparently span over verification of multiple sub-components associated with CRS stack. This encapsulation of a set of tasks within specific component verification should be of a great ease to the user.

What is nodelist?
Nodelist is a comma separated list of hostnames without domain. Cluvfy will ignore any domain while processing the nodelist. If duplicate entities after removing the domain exist, cluvfy will eliminate the duplicate names while processing. Wherever supported, you can use ‘-n all’ to check on all the cluster nodes. Check this for more information on nodelist and shortcuts.
[ go to the top ]

Do I have to be root to use CVU?
No. CVU is intended for database and system administrators. CVU assumes the current user as oracle user.

What about discovery? Does CVU discover installed components?
At present, CVU discovery is limited to these components. CVU discovers available network interfaces if you do not specify any interface or IP address in its command line. For storage related verification, CVU discovers all the supported storage types if you do not specify a particular storage. CVU discovers CRS HOME if one is available.

What about locale? Does CVU support other languages?
CVU supports all the languages that are supported by other Oracle products.

@How do I report a(or tons of) bug?
is not covered in those documents, file a bug against product# 5,
@component: OPSM and sub-component: CLUVFY. Please provide the relevant log file while filing a bug.
[ go to the top ]

What are the requirements for CVU?

CVU requires:
1._ An area with at least 30MB for containing software bits on the invocation node.
2._ Java 1.4.1 location on the invocation node.
3._ A work directory with at least 25MB on all the nodes. CVU will attempt to copy the necessary bits as required to this location. Make sure, the location exists on all nodes and it has write permission for CVU user. This dir is set through the CV_DESTLOC environment variable. If this variable does not exist, CVU will use “/tmp” as the work dir.

How do I manually install CVU?
Here is how one can install CVU from a zip file(cvupack.zip).
1.) create a cvhome( say /home/mycvhome ) directory. It should have at least 30M of free disk space.
2.) cd /home/mycvhome
3.) copy the cvupack.zip file to /home/mycvhome
4.) unzip the file:
Example : unzip cvupack.zip
5.) set these environmental variables:
CV_HOME: This should point to the cvhome.
Example: setenv CV_HOME /home/mycvhome
CV_JDKHOME: This should point to a valid jdk1.4 home with hybrid support. By default the installation points to the right JDK
Example: setenv CV_JDKHOME /usr/local/packages/jdk14
CV_DESTLOC (optional ): This should point to a writable area on *all* nodes. The tool will attempt to copy the necessary bits as required to this location. Make sure, the location exists on all nodes and it has write permission for CVU user. It is strongly recommended that you should set this variable. If this variable has not been set, CVU will use “/tmp” as the default.
Example : setenv CV_DESTLOC /tmp/cvu_temp

To verify, run /home/mycvhome/bin/cluvfy. This should show the usage.

From where do I download CVU
http://www.oracle.com/technology/products/database/clustering/cvu/cvu_download_homepage.html

What Linux distributions are supported?
This release supports
RedHat 2.1AS (Note that the CVU for 2.1 and other versions are not binary compatible)
RedHat 3 (Update 2 or higher)
RedHat 4
Suse 9.

How do I make Cluvfy work with Suse 9 ES?
For this you will have to edit the configuration file called cvu_config under
CV_HOME/cv/admin directory. Modify the property CV_ASSUME_DISTID=Taroon to CV_ASSUME_DISTID=Pensacola

What Windows versions are supported?
This release supports Windows 2000 and Windows 2003

What Solaris versions are supported?
This release supports Solaris 8, Solaris 9 and Solaris 10

What AIX are versions are supported?
This release supports AIX 5L (5.1,5.2,5.3)

What HP-UX versions are supported?
This release supports 11.11 and 11.23

[ go to the top ]

How do I know about cluvfy commands? The usage text of cluvfy does not show individual commands.
Cluvfy has context sensitive help built into it. Cluvfy shows the most appropriate usage text based on the cluvfy command line arguments.

If you type ‘cluvfy’ on the command prompt, cluvfy displays the high level generic usage text, which talks about valid stage and component syntax.

If you type ‘cluvfy comp -list’, cluvfy will show valid components with brief description on each of them. If you type ‘cluvfy comp -help’, cluvfy will show detail syntax for each of the valid components. Similarly, ‘cluvfy stage -list’ and ‘cluvfy stage -help’ will list valid stages and their syntax respectively.

If you type an invalid command, cluvfy will show the appropriate usage for that particular command. For example, if you type ‘cluvfy stage -pre dbinst’, cluvfy will show the syntax for pre-check of dbinst stage.
[ go to the top ]

What are the default values for the command line arguments?
Here are the default values and behavior for different stage and component commands:

For component nodecon:
If no -i is provided, then cluvfy will get into the discovery mode.
For component nodereach:
If no -srcnode is provided, then the local(node of invocation) will be used as the source node.
For components cfs, ocr, crs, space, clumgr:
If no -n argument is provided, then the local node will be used.
For components sys and admprv:
If no -n argument is provided, then the local node will be used.
If no -osdba argument is provided, then ‘dba’ will be used.
If no -orainv argument is provided, then ‘oinstall’ will be used.
For component peer:
If no -osdba argument is provided, then ‘dba’ will be used.
If no -orainv argument is provided, then ‘oinstall’ will be used.

For stage -post hwos:
If no -s argument is provided, then cluvfy will get into the discovery mode for shared storage verification.
For stage -pre crsint:
If no -c argument is provided, then cluvfy will skip OCR related checks.
If no -q argument is provided, then cluvfy will skip voting disk related checks.
If no -osdba argument is provided, then ‘dba’ will be used.
If no -orainv argument is provided, then ‘oinstall’ will be used.
For stage -pre dbinst:
If no -osdba argument is provided, then ‘dba’ will be used.
If no -orainv argument is provided, then ‘oinstall’ will be used.
[ go to the top ]

Do I have to type the nodelist every time for the CVU commands? Is there any shortcut?
You do not have to type the nodelist every time for the CVU commands. Typing the nodelist for a large cluster is painful and error prone. Here are few short cuts.

To provide all the nodes of the cluster, type ‘-n all’. Cluvfy will attempt to get the nodelist in the following order:
1. If a vendor clusterware is available, it will pick all the configured nodes from the vendor clusterware using lsnodes utility.
2. If CRS is installed, it will pick all the configured nodes from Oracle clusterware using olsnodes utility.
3. If neither the Vendor Clusterware or Oracle clusterware is installed, then it searches for a value of CV_NODE_ALL in the configuration file.
4. If none of the above, it will look for the CV_NODE_ALL environmental variable. If this variable is not defined, it will complain.

To provide a partial list(some of the nodes of the cluster) of nodes, you can set an environmental variable and use it in the CVU command. For example:
setenv MYNODES node1,node3,node5
cluvfy comp nodecon -n $MYNODES
[ go to the top ]

How do I get detail output of a check?
Cluvfy supports a verbose feature. By default, cluvfy reports in non-verbose mode and just reports the summary of a test. To get detailed output of a check, use the flag ‘-verbose’ in the command line. This will produce detail output of individual checks and where applicable will show per-node result in a tabular fashion.

How do I check network or node connectivity related issues?
Use component verifications commands like ‘nodereach’ or ‘nodecon’ for this purpose. For detail syntax of these commands, type cluvfy comp -help on the command prompt.

If the ‘cluvfy comp nodecon’ command is invoked without -i argument, cluvfy will attempt to discover all the available interfaces and the corresponding IP address & subnet. Then cluvfy will try to verify the node connectivity per subnet. It would also obtain the list of interfaces that are suitable for use as VIPs and the list of interfaces to private interconnects. You can run this command in verbose mode to find out the mappings between the interfaces, IP addresses and subnets.

You can check the connectivity among the nodes by specifying the interface name(s) through -i argument.
[ go to the top ]

Can I check if the storage is shared among the nodes?
Yes, you can use ‘comp ssa’ command to check the sharedness of the storage. Please refer to the known issues section for the type of storage supported by cluvfy.

How do I check whether OCFS is properly configured?
You can use the component command ‘cfs’ to check this. Provide the OCFS file system you want to check through the -f argument. Note that, the sharedness check for the file sytem is supported for OCFS version 1.0.14 or higher.

How do I check the CRS stack and other sub-components of it?
Cluvfy provides commands to check a particular sub-component of the CRS stack as well as the whole CRS stack. You can use the ‘comp ocr’ command to check the integrity of OCR. Similarly, you can use ‘comp crs’ and ‘comp clumgr’ commands to check integrity of crs and clustermanager sub-components. You can use the `comp nodeapp` command to check whether the node applications, namely VIP, GSD and ONS, have been configured properly.

To check whether the Oracle Clusterware has been installed properly, run the stage command ’stage -post crsinst’.

How do I check user accounts and administrative permissions related issues?
Use admprv component verification command. Refer to the usage text for detail instruction and type of supported operations. To check whether the privilege is sufficient for user equivalence, use ‘-o user_equiv’ argument. Similarly, the ‘-o crs_inst’ will verify whether the user has the correct permissions for installing CRS. The ‘-o db_inst’ will check for permissions required for installing RAC and ‘-o db_config’ will check for permissions required for creating a RAC database or modifying a RAC database configuration.
[ go to the top ]

How do I check minimal system requirements on the nodes?
The component verification command sys is meant for that. To check the system requirement for RAC, use ‘-p database’ argument. To check the system requirement for CRS, use ‘-p crs’ argument. To check the system requirements for installing the Oracle Clusterware or RAC from Oracle Database 10g release 1 (10.1), use the -r 10gR1 argument.

Is there a way to compare nodes?
You can use the peer comparison feature of cluvfy for this purpose. The command ‘comp peer’ will list the values of different nodes for several pre-selected properties. You can use the peer command with -refnode argument to compare those properties of other nodes against the reference node. To compare the properties pertaining to Oracle Database 10g release 1 (10.1), use the -r 10gR1 argument.

Why the peer comparison with -refnode says passed when the group or user does not exist?
Peer comparison with the -refnode feature acts like a baseline feature. It compares the system properties of other nodes against the reference node. If the value does not match( not equal to reference node value ), then it flags that as a deviation from the reference node. If a group or user does not exist on reference node as well as on the other node, it will report this as ‘passed’ since there is no deviation from the reference node. Similarly, it will report as ‘failed’ for a node with higher total memory than the reference node for the above reason.
[ go to the top ]

Is there a way to verify that the CRS is working properly before proceeding with RAC install?
Yes. You can use the post-check command for cluster services setup(-post crsinst) to verify CRS status. A more appropriate test would be to use the pre-check command for database installation(-pre dbinst). This will check whether the current state of the system is suitable for RAC install.

At what point cluvfy is usable? Can I use cluvfy before installing CRS?
You can run cluvfy at any time, even before CRS installation. In fact, cluvfy is designed to assist the user as soon as the hardware and OS is up. If you invoke a command which requires CRS or RAC on local node, cluvfy will report an error if those required products are not yet installed.

How do I turn on tracing?
Set the environmental variable SRVM_TRACE to true. For example, in tcsh “setenv SRVM_TRACE true” will turn on tracing.

Where can I find the CVU trace files?
CVU log files can be found under $CV_HOME/cv/log directory. The log files are automatically rotated and the latest log file has the name cvutrace.log.0. It is a good idea to clean up unwanted log files or archive them to reclaim disk place.
Note that, no trace files will be generated if tracing has not been turned on.

Why cluvfy reports “unknown” on a particular node?
Cluvfy reports unknown when it can not conclude for sure if the check passed or failed. Please refer to the Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for details on this.

What does cluvfy error “Could not find a suitable set of interfaces for VIPs” mean?
Cluvfy reports this error when it could not discover at least one subnet that connects all the nodes using the same interface name and does not support IP addresses like 10.*,172.16.*-172.31.* and 192.168.*. Related Note 316583.1

Where can I find the disk rpm
The disk rpm can be found in the cvuqdisk-1.0.1-1.rpm under “Disk1/rpm

[ go to the top ]

How do I check that user equivalence through SSH is setup properly
To verify user accounts and administrative permissions-related issues, use the component verification command admprv as follows:
cluvfy comp admprv [ -n node_list ] [-verbose]
| -o user_equiv [-sshonly]
| -o crs_inst [-orainv orainventory_group ]
| -o db_inst [-orainv orainventory_group ] [-osdba osdba_group ]
| -o db_config -d oracle_home
For example cluvfy comp admprv -n all -o user_equiv -verbose. More details are in the Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

How can I check the requirements for installing Oracle Clusterware or RAC from Oracle Database Release 10g Release1 (10.1) ?
runcluvfy.sh stage -pre crsinst -r 10gR1 -n node1,node2. More details are in the Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

What is CVU`s configuration file? How do I use it?
Please review the documentation at Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

How do I run CVU from installation media?
After mounting the CRS DVD, cd to the Disk1/cluvfy and execute runcluvfy with the same arguments as cluvfy.
For example
./runcluvfy stage -pre crsinst -n ,

What database versions are supported by CVU?
Current CVU release supports only 10g RAC and CRS and is not backward compatible. In other words, CVU can not check or verify pre-10g products.

What are the known issues with this release?
Shared storage accessibility(ssa) check reports
1).Current release of cluvfy has the following limitations on Linux regarding shared storage accessibility check.
a. Currently NAS storage ( r/w, no attribute caching) and OCFS( version 1.0.14 or higher ) are supported.
b For sharedness check on NAS, cluvfy requires the user to have write permission on the specified path. If the cluvfy user does not have write permission, cluvfy reports the path as not-shared.

2.) CVU complains missing packages in Suse
The preinstallation stage verification checks for Oracle Clusterware and Oracle Real Applications Clusters and reports missing packages. Ignore the following missing packages and continue with the installation:
compat-gcc-7.3-2.96.128
compat-gcc-c++-7.3-2.96.128
compat-libstdc++-7.3-2.96.128
compat-libstdc++-devel-7.3-2.96.1

3.) Cluvfy complains about missing Vendor Clusterware packages (e.g. Sun Cluster, ORCLudlm) when deployment is planned with Oracle Clusterware without any vendor clusterware. This is a known issue and is documented in all the release notes

What kind of Storage does cluvfy check for shared-ness?
Cluvfy currently can only check for scsi disks and may error out for special devices like EMC powerpath

Automatic SQL Tuning – SQL Profiles

Automatic SQL Tuning – SQL Profiles

Clarification/Explanation

==========================

The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view. By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions.

It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

SQL Profiles apply to the following statement types:

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

Managing SQL Profiles
=====================

While SQL Profiles are usually handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process, SQL Profiles can be managed through the DBMS_SQLTUNE package. To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

Accepting a SQL Profile
=======================

You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database. For example:

DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘my_sql_tuning_task’,
name => ‘my_sql_profile’);
END;

Where my_sql_tuning_task is the name of the SQL tuning task. You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile
======================

You can alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure. For example:

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => ‘my_sql_profile’,
attribute_name => ‘STATUS’,
value => ‘DISABLED’);
END;
/

In this example, my_sql_profile is the name of the SQL Profile that you want to alter. The status attribute is changed to disabled which means the SQL Profile is not used during SQL compilation.

Dropping a SQL Profile
======================
You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘my_sql_profile’);
END;
/

In this example, my_sql_profile is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE is accepted.

Example
=======

SESSION 1 — SCOTT
=========

SQL> create table test (n number );

Table created.

declare
begin
for i in 1 .. 10000
loop
insert into test values(i);
commit;
end loop;
end;

PL/SQL procedure successfully completed.

create index test_idx on test(n);

Index created.

analyze table test estimate statistics

Table analyzed.

select /*+ no_index(test test_idx) */ * from test where n=1

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)
1 0 TABLE ACCESS (FULL) OF ‘TEST’ (TABLE) (Cost=6 Card=1 Bytes
=13)

SESSION 2 — SYS
=========

1 DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := ’select /*+ no_index(test test_idx) */ * from test where n= 1′;
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => ‘SCOTT’,
9 scope => ‘COMPREHENSIVE’,
10 time_limit => 60,
11 task_name => ‘my_sql_tuning_task_2′,
12 description => ‘Task to tune a query on a specified table’);
13* END;
14 /

PL/SQL procedure successfully completed.

1 BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_2′);
3* end;
SQL> /

PL/SQL procedure successfully completed.

1 SET LONG 1000
2 SET LONGCHUNKSIZE 1000
3 SET LINESIZE 100
4* SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_2′) from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2′)
——————————————————————————–
——————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : my_sql_tuning_task_2
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/04/2004 17:36:05
Completed at : 05/04/2004 17:36:05

——————————————————————————-
SQL ID : d4wgpc5g0s0vu

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2′)
——————————————————————————–
——————–
SQL Text: select /*+ no_index(test test_idx) */ * from test where n=1

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 83.84%)

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2′)
——————————————————————————–
——————–
——————————————
Consider accepting the recommended

1 DECLARE
2 my_sqlprofile_name VARCHAR2(30);
3 BEGIN
4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => ‘my_sql_tuning_task_2′,
6 name => ‘my_sql_profile’);
7* END;
8 /

PL/SQL procedure successfully completed.

SQL> select to_char(sql_text) from dba_sql_profiles;

TO_CHAR(SQL_TEXT)
————————————————————————
select /*+ no_index(test test_idx) */ * from test where n=1

SESSION 1 — SCOTT

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 INDEX (RANGE SCAN) OF ‘TEST_IDX’ (INDEX) (Cost=1 Card=1 By
tes=13

.

Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node

Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node

Planning for VIP Changes

Changing the VIP involves modification of the nodeapps, which includes the Virtual IP address, the GSD, the Listener, and Oracle Notification Services (ONS). The VIP can be modified while the nodeapps are running, however changes will not take effect until the VIP, and hence the nodeapps, are restarted.
Depending on the version of Oracle Clusterware that you are running, other resources on a node, such as database instances and ASM instances, are dependent on the VIP, so stopping the nodeapps may cause other resources to be stopped – therefore, this change should be made during a scheduled outage.

In most cases, changing Data Centers or IP addresses within a Data Center will already incur an outage for other reasons, because changes need to be made at the operating system level, and servers may even need to be moved – so there is most likely a scheduled outage for this type of maintenence already.

However, in some cases – for example, if a VIP IP address was perhaps mistyped in DNS and needs to be corrected, you may be able to minimize the downtime by only modifying a single node. Also, as of 10.2.0.3 and higher versions, the ASM instance and DB instances no longer depend on the VIP, so it is possible to stop the nodeapps without bringing down the ASM instance or DB instance – but client connectivity is limited while the VIP is offline.

Verifying Current VIP configuration

Below, we will provide examples on how to modify the nodeapps to change the VIP or VIP hostname.

1. The first step that should be done is to confirm the current configuration of the VIP. This is most easily accomplished by running the command:

srvctl config nodeapps -n -a

Using the ‘-a’ switch will give you the current VIP Hostname, VIP IP address and interface. The following example is from a Windows System. Note that the nodename is ‘node1′ as passed via the ‘-n’ argument.

C:\>srvctl config nodeapps -n node1 -a
VIP exists.: /node1-v/10.148.44.94/255.255.255.0/Public

These outputs show that:

The VIP Hostname is ‘node1-v’
The VIP IP address is ‘10.148.44.94′
The VIP subnet mask is ‘255.255.255.0′
The Interface Name used by the VIP is called ‘Public’

Any of the above configuration parameters associated with the VIP can be changed if they were originally input incorrectly, or perhaps they need to be changed due to external reasons (such as a data center move, or IP change as mentioned above).

Note:
In addition, you can also verify the IP address and interface of the VIP by running the ‘ifconfig’ command or ‘ifconfig -a’, and looking for the IP address associated with an interface ending in ‘:1′. The example below, on Linux, shows the interface as ‘eth0:1′

=============================
eth0:1 Link encap:Ethernet HWaddr 00:01:03:2C:69:BB
inet addr:192.168.1.125 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
…
…
=============================

On the Windows platform, run the command ‘ipconfig /all’. This will list all IP addresses bound to a given adapter, including the VIP, but it will not be obvious just from this output which one is the VIP address and which is the actual host IP address. Therefore, using ’srvctl config nodeapps -n -a’ is the surest bet.

2. Once you have determined what the current configuration of the nodeapps is, be sure to make note of what values will remain unchanged, and what the correct/new values that you need will be. For example, if you are just changing the “Interface Name” you will need to know what the correct interface name is, but you also need to note the VIP hostname, IP address and subnet mask to ensure that this information is re-entered correctly.
Stopping Resources

3. Once you are ready to make the change, stop all resources that are dependent on the VIP on a given node. This includes all RAC database instances on that node, as well as the ASM instance on that node, if it exists and is being used (Note that starting with 10.2.0.3, the dependency between the VIP and the database and ASM instances has been removed):

3a). Stop database instances:
$ srvctl stop instance -d grid -i grid1

Where the database name is specified via the ‘-d’ option, and the instance on the appropriate node is specified with the ‘-i’ option.

Alternatively, to stop the entire database, on all nodes, you can issue the stop database command:

$ srvctl stop database -d grid

3b). To stop the ASM instance on the node, issue the following command:

$ srvctl stop asm -n node1

This command should be issued for each ASM instance using the appropriate node name. Alternatively, it is also possible to stop these resources via SQL*Plus and/or, on the Windows platform by stopping the associated services.

4. Next, stop the nodeapps using srvctl – i.e:

$ srvctl stop nodeapps -n node1

This will stop the VIP, GSD, Listener, and ONS daemon currently running on the nodes specified

If this is being done as part of a data center move, then you will most likely be stopping these resources prior to moving the equipment. In that case, you may want to disable the resources after stopping them, to prevent them from re-starting once the machines are brought back up and the Oracle Clusterware stack is started. You can do this via commands such as:

srvctl disable database -d grid
srvctl disable asm -n node1
srvctl disable nodeapps -n node1
etc.

5. Verify that the VIP is no longer running by executing the ‘ifconfig -a’ or ‘ipconfig /all’ command again, and confirm that the IP address is no longer listed as running in the output.

If the interface still shows as online, this may be an indication that a resource which is dependent on the VIP is still running. The crs_stat command can help to show resources that are still online.

6. Make any changes necessary to all nodes’ /etc/hosts files (on Unix/Linux), or the
\WINDOWS\System32\drivers\etc\hosts file (on Windows) and/or make the necessary
DNS changes, to associate the new IP address with the old hostname.
Syntax for modifying nodeapps

6. To make the actual modification to the nodeapps, the Oracle Clusterware stack must be up on the node where you are running srvctl from. To modify the nodeapps use the ’srvctl modify nodeapps’ command with the following syntax:

srvctl modify nodeapps -n [-o ] [-A ]

Options Description:
-n Node name.
-o Oracle home for the cluster database.
-A The node level VIP address (/netmask[/if1[|if2|...]]).

As noted previously, any of the above parameters can be changed from their original values (though it is unlikely that the ORACLE_HOME would change), provided that the match the expected characteristics.

So – for example, be sure that the interface name specified is the correct name as seen from the OS (refer to Note 283684.1 ), be sure that the subnet mask used for the VIP matches the subnet mask used for the actual publicNote 283684.1 ), be sure that the subnet mask used for the VIP matches the subnet mask used for the actual public IP addresses, and that the VIP hostname is correctly registered in DNS and/or the hosts file. An example of the ‘modify nodeapps’ command is as follows:
$ srvctl modify nodeapps -n node1 -A 192.168.2.125/255.255.255.0/eth0

It should be noted that for the first parameter, you can specify either the hostname associated with the VIP, or the IP address associated with the VIP. Either way, the srvctl command will actually attempt to resolve the IP to a hostname, or the hostname to an IP, and it will store both entries in the OCR. So, assuming that the virtual hostname of ‘node1-v’ resolves to an IP address 192.168.2.125, the below command would have the same effect as the command using the IP address:
$ srvctl modify nodeapps -n node1 -A node1-v/255.255.255.0/eth0
Note that the interface names are case senstive on all platforms. On some platforms, such as Windows, the Interface Name may have spaces in it – i.e. “Local Area Connection 1″. If that is the case, you must enclose the interface name in double quotes – i.e.

srvctl modify nodeapps -n node1 -A 192.168.2.125/255.255.255.0/”Local Area Connection 1″

On Unix and Linux systems, this command should be run as root. Attempting to run this command
as the ‘oracle’ user or software owner will result in the following error:

PRKO-2117 : This command should be executed as the system privilege user.

On Windows systems, this command should be run as the user who did the original installation. This account should be an account with Local Administrator privileges on each node.

7. After making the change, you should verify that it is correct by re-running

’srvctl config nodeapps -n -a’

Double-check the output to confirm that all parameters are correct.
Starting Resources Back Up

7. Once the modify nodeapps has been executed, you can re-start node-level applications via srvctl with the following syntax:

srvctl start nodeapps -n
i.e.:
$ srvctl start nodeapps -n rnode1

If any resources (such as database or ASM) were previously disabled, then they should now be re-enabled and re-started as well.

Repeat the same steps for all the nodes in the cluster. Since SRVCTL is a cluster wide management tool, you can accomplish these tasks for any specific nodes from one node, without the need to login individually to each of the cluster nodes.

Note: If only the IP address is changed, it should not be necessary to make changes to the LISTENER.ORA and TNSNAMES.ORA, provided they are using the vip hostnames for
the ‘HOST=’ entries.

If changing both the hostname and the VIP for a node, it will be necessary to modify the LISTENER.ORA and change the ‘HOST=’ entries to the new VIP hostname. This can be done manually, or by using the NETCA to reconfigure the listener. In addition, changes may need to be made to the TNSNAMES.ORA of any clients connecting to the old HOSTNAME.

In addition to modifying the nodeapps after an IP address or network change of some type, it may also be necessary to modify the networks that are stored in the OCR. If that is the case, please refer to the following note:

Note 283684.1 How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster

For complete srvctl syntax on 10gR1, Refer to Appendix B of the RAC Admin Guide:
http://download-west.oracle.com/docs/cd/B13789_01/rac.101/b10765/toc.htm

For complete srvctl syntax on 10gR2, Refer to Appendix E of the RAC Admin Guide:
http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/toc.htm

How To Configure Virtual IPs for 10g RAC

How to Configure Virtual IPs for 10g RAC

In 10g RAC, we now require virtual IP addresses for 10g RAC. These addresses
are used for failover and are automatically managed by CRS (Cluster Ready
Services). The VIPCA (Virtual IP Configuration Assistant) that is called from
the root.sh script of a RAC install, configures the virtual IP addresses for
each node. Prior to running VIPCA, you just need to make sure that you have
unused public IP addresses available for each node and that they are configured
in the /etc/hosts file.

When installing 10g RAC, at least 2 network interfaces are required for each
node:

- Public Interface
- Private Interface

The VIP addresses must also be added to DNS.

From the Oracle® Real Application Clusters Installation and Configuration Guide
it states the following requirements for installing RAC:

- External shared disks.

- One private internet protocol (IP) address for each node to serve as
the private interconnect. This IP address must be separate from the
public network and it must have the same interface name on every node that
is part of your cluster.

- One public IP address for each node to serve as the Virtual IP address for
client connections and for connection failover. This is in addition to the
operating-system managed public host IP address that is already assigned to
the node by the operating system. This public Virtual IP must be associated
with the same interface name on every node that is part of your cluster. In
addition, the IP addresses that you use for all of the nodes that are part of
a cluster must be from the same subnet. The host names for the VIP must be
registered with the domain name server (DNS). The Virtual IP address should
not be in use at the time of the installation because this is a Virtual IP
address that Oracle manages.

- Redundant switches as a standard configuration for all cluster sizes.

To verify that each node meets the network requirements, follow these steps:

1. If necessary, install the network adapters for the public and private
networks and configure them with either public or private IP addresses.

2. Register the host names and IP addresses for the public network interfaces
in DNS.

3. For each node, register one virtual host name and IP address in DNS.

4. Add the private, public, and virtual interfaces to the /etc/hosts file.
Example:

[opcbrh1]/etc> more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
138.1.137.45 opcbrh1.us.oracle.com opcbrh1
138.1.137.46 opcbrh2.us.oracle.com opcbrh2
192.168.0.10 int-opcbrh1.us.oracle.com int-opcbrh1
192.168.0.20 int-opcbrh2.us.oracle.com int-opcbrh2
138.1.137.25 opcbrh1-v.us.oracle.com opcbrh1-v
138.1.137.26 opcbrh2-v.us.oracle.com opcbrh2-v
127.0.0.1 localhost.localdomain localhost

In the above example, the int-* addresses are private and the *-v
addresses are for the virtual IP’s.

5. To identify the interface name and associated IP address for every network
adapter, enter the following command:

# /sbin/ifconfig -a

…or /usr/bin/netstat -in on HP-UX

You do not have to add the virtual IP’s to ifconfig. The VIPCA (Virtual IP
Configuration Assistant) will do this for you. Also make sure that the
private network has the attribute “PRIVATE” so that VIPCA will not try to use
the private network (it should use the public).

At this point you should be able to ping the public IP name from any machine
on the network:

C:\>ping opcbrh1

Pinging opcbrh1.us.oracle.com [138.1.137.45] with 32 by

Reply from 138.1.137.45: bytes=32 time<10ms TTL=255
Reply from 138.1.137.45: bytes=32 timeping opcbrh1-int

Pinging opcbrh1-int [192.168.0.10] with 32 by

Reply from 192.168.0.10: bytes=32 time<10ms TTL=255
Reply from 192.168.0.10: bytes=32 timenslookup opcbrh1-v

Once you have done the above, you should be ready to install CRS and RAC.

At the end of the RAC installation, you will be prompted to run root.sh. When
the root.sh is run, the VIPCA (Virtual IP Configuration Assistant) will come
up. When this happens, perform the following steps:

1. Review the information on the VIPCA Welcome page, click Next, and the VIPCA
displays the Public Network Interfaces page.

2. On the Public Network Interfaces page determine the network interface cards
(NICs) to which you want to assign your public VIP addresses. ALWAYS choose
a public network here, NEVER choose a private network, click Next, and the
VIPCA displays the IP Address page.

3. On the IP Address page enter an unused (unassigned) public virtual IP
address for each node displayed on this page and click Next. After you click
Next, the VIPCA displays a Summary page. Review the information on this page
and click Finish. A progress dialog appears while the VIPCA configures the
virtual IP addresses with the network interfaces that you selected. The
VIPCA then creates and starts the VIPs, GSD, and Oracle Notification Service
(ONS) node applications. When the configuration completes, click OK to see
the VIPCA session results. Review the information on the Configuration
Results page, and click Exit to exit the VIPCA.

4. Repeat the root.sh procedure on all nodes that are part of this installation.
The VIPCA will not run again on the remote node because the remote node is
already configured.

If your VIP’s are not set up correctly, VIPCA may fail with CRS-215 “Could not
start resource” errors for the VIP resource and any resources that depend on
the CRS resource such as GSD and ONS.

After VIPCA has been run, you should now see the VIP addresses in ifconfig (or
netstat -in on HP-UX). Here is an example of my VIP ifconfig output:

eth0:1 Link encap:Ethernet HWaddr 00:90:27:BC:D9:8C
inet addr:138.1.137.25 Bcast:138.1.255.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
Interrupt:11 Base address:0×9000

You should also be able to see the resource information for the VIP which
is managed by CRS. To do this, go to your CRS_HOME/bin directory and run
“crs_stat”. Here is an example of my VIP resource information from crs_stat:

NAME=ora.opcbrh1.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on opcbrh1

NAME=ora.opcbrh2.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on opcbrh2

This shows that both of my VIP’s are online and are assigned to their
respective nodes. In the event of a failover scenerio, one or more VIP’s may
be moved to another node. This is automatically managed by CRS.

If you have to change your VIP’s to a different address you will need to remove
the node-level applications and re-create them with:

srvctl stop nodeapps
srvctl remove nodeapps
srvctl add nodeapps

See the Oracle® Real Application Clusters Administrator’s Guide Appendix B for
the exact srvctl syntax.

FILES TO REVIEW IF THERE ARE VIP ISSUES
—————————————

- “ifconfig -a” output from each node
- “nslookup ” for each virtual host name
- /etc/hosts file from each node
- output of “$ORA_CRS_HOME/bin/crs_stat”
- output of “srvctl start nodeapps -n ” on the node having the issue

How To Recreate OCR/Voting Disk Accidentally Deleted

How to recreate OCR/Voting disk accidentally deleted

Goal

The goal of this document is to help customers who have accidentally deleted the OCR, voting disk or the files that are required for the operation of Oracle clusterware.

Depending on the issue, it may or may not be good idea to execute the steps provided.

* OCR
o If the OCR has been deleted, then check if the OCR mirror is OK and vice versa. It may be prudent to use the OCR mirror to create the OCR. For steps on this check the documentation Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide
o If the OCR mirror and OCR have been deleted, then it may be faster to restore the OCR using the OCR backups. For steps on this check the documentation Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide
* Voting Disk
o If there are multiple voting disks and one was accidentally deleted, then check if there are any backups of this voting disk. If there are no backups then we can add one using the crsctl add votedisk command. The complete steps are in the Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide
* SCLS directories
o These are internal only directories which are created by root.sh, if this directory is accidentally removed then they can only be created by the steps documented below
* Socket files in /tmp/.oracle or /var/tmp/.oracle
o If these files are accidentally deleted, then stop the Oracle Clusterware on that node and restart it again. This will recreate these socket files. If the socket files for cssd is deleted then the Oracle Clusterware stack may not come down in which case the node has to be bounced.

Solution

If none of the steps documented above can be used to restore the file that was accidentally deleted or is corrupted, then the following steps can be used to re-create/reinstantiate these files. The following steps require complete downtime on all the nodes.

1. Shutdown the Oracle Clusterware stack on all the nodes using command crsctl stop crs as root user.
2. Backup the entire Oracle Clusterware home.
3. Execute /install/rootdelete.sh on all nodes
4. Execute /install/rootdeinstall.sh on the node which is supposed to be the first node
5. The following commands should return nothing
* ps -e | grep -i ‘ocs[s]d’
* ps -e | grep -i ‘cr[s]d.bin’
* ps -e | grep -i ‘ev[m]d.bin’
6. Execute /root.sh on first node
7. After successful root.sh execution on first node Execute root.sh on the rest of the nodes of the cluster
8. Add the database to the OCR using the appropriate srvctl add database command as the user who owns the database. Please ensure that this is not run as root user
9. Add ASM, DB, Instance, services using approproate srvctl add commands. Please refer to the documentation for the exact commands.
10. Add the listener using netca. This may give errors if the listener.ora contains the entries already. If this is the case, move the listener.ora to /tmp from the $ORACLE_HOME/network/admin or from the $TNS_ADMIN directory if the TNS_ADMIN environmental is defined and then run netca. Add all the listeners that were added earlier.
11. execute cluvfy stage -post crsinst -n node1,node2 ### Please ensure to replace node1,node2 with the node names of the cluster

@initialize OCR re-initialize OCR reinitialize recreate OCR ==> Added for improved searchability

Verifying CRS Integrity Error ‘User Equivalence Unavailable On All The Nodes’

Verifying Crs Integrity Error ‘User Equivalence Unavailable On All The Nodes’

Symptoms

You are using Grid Control 10.2.0.1 to monitor a RAC database and clusterware.

Clusterware was installed (successfully) and the oracle software owner can ssh from one node to the other, and back again
without having to enter a password (i.e. ssh keys are correctly set up).
However, the OMS is reporting a critical violation for each db node, stating:

“Clusterware on the local host has problems. Verifying CRS integrity ERROR:
User equivalence unavailable on all the nodes. Verification cannot proceed.
Verification of CRS integrity was unsuccessful on all the nodes.”

Navigation path is:

Targets > Click on the link under the Alerts critical column for the host in question i.e. the red number > click on Message > click on Message again to get the full text.
The navigation near the top of the screen shows
Host: All Metrics > Clusterware Status >

Cause

The clusterware software owner and agent owner are diffrent.
The agent owner cannot run /bin/cluvfy comp crs
Most lilkely this is a permission issue.

When perl tracing is enabled on the agent emagent_perl.trc shows this

crs_status.pl: Fri May 26 12:28:02 2006: DEBUG: ##crs home is: /u03/app/oracle/crs/10.2.0.1
crs_status.pl: Fri May 26 12:28:03 2006: ERROR: ‘/u03/app/oracle/crs/10.2.0.1/bin/cluvfy comp crs’: 256
Verifying CRS integrity

Verification of CRS integrity was unsuccessful on all the nodes.

Solution

1. Make sure that the agent owner can run /bin/cluvfy comp crs succesfully
This is normally done by ensuring the agent owner is in the same OS group as the cluster software owner. However specific issues may be encountered.

2. Force a clearstate of the agent:

AGENT_HOME/bin/emctl clearstate agent

3. Force an upload:

AGENT_HOME/bin/emctl upload

4. This should clear the alerts