How to to find unused indexes and delete tham to release space

PURPOSE

To show how to view all indexes being monitored.

SCOPE & APPLICATION

Instructional.

Viewing All Indexes Being Monitored Under Another User's Schema:
=================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose usage is being monitored.

'ALTER INDEX MONITORING USAGE'

places an entry in V$OBJECT_USAGE for that particular index to help determine if the index is being used or not. The V$OBJECT_USAGE view uses the username logged into database when the 'ALTER INDEX MONITORING USAGE' is issued. This will not enable any user otherthan the user who issued the 'ALTER INDEX MONITORING USAGE' to view if index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its scope system-wide (see below) so that you may see all indexes being monitored.

For example:

Showing User Scott monitoring his Index on EMP table:

SQL> connect scott/tiger SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#

SQL> select index_name, table_name, uniqueness, status from user_indexes where table_name = 'EMP';

INDEX_NAME TABLE_NAME UNIQUENES STATUSPK_EMP EMP UNIQUE VALID

SQL> alter index PK_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORINGPK_EMP EMP YES NO 10/12/2001 06:42:35

Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;
Connected.

SQL> select * from v$object_usage;
no rows selected

To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,INDEX_NAME, TABLE_NAME, MONITORING,
USED, START_MONITORING, END_MONITORING)
as select u.name, io.name, t.name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring from sys.user$ u, sys.obj$ io, sys.obj$ t,
sys.ind$ i, sys.object_usage ou where i.obj# = ou.obj#
and io.obj# = ou.obj# and t.obj# = i.bo#and u.user# = io.owner#

View created.

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORINGSCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35

Unix/Linux Administrative Commands For DBA

Some UNIX/Linux commands are particularly useful for administrative purpose.They help us find the Server Statistics including CPU usage,Memory Usage,Virtual Memory statistics,I/O statistics.The commands and their details are given below :

tar - Unix Backup and restore utility

tar -cvf backup-devicepath pathofsourcefiletobe archived- creates a new archive.Hyphen is optional

tar -xvf backupdevicepath archivefile - extracts the archived file.

cpio - Copy input/output command

cpio -o - cpio command with -o option copies files to standard output such as disk or tape

cpio -i - cpio command with -i option extracts file from standard input.

Usage :

cpio -o > standardtape/disk path

cpio - i <>

iostat - displays input output statistics for all the disks on our system

sar - System Activity reporter.Its a very powerful tool that offers a way to analyze how the read/write operations are occuring from disk to buffer cache and from buffer cache to disk.By using various options of sar command we can monitor disk and CPU activity,in addition to buffercache activity.

top - It is a commonly used performance monitoring tool.It shows us a little bit of information on everything such as the top CPU and memory utilization processes,the percentage of CPU time used by the top processes, and memory utilization.

glance - A system-monitoring tool in HP-UNIX.Used for monitoring memory,Disk I/O,CPU Performance

netstat - Monitor network to make sure that there are no serious traffic bottlenecks.

ps - Once tha database or any application is started in a UNIX/Linux system the primary step is to ensure that processes have been invoked. ps -efgrep process-name is a clear way to ensure that processes are running. If the output doesnt give out any ros then processes are down and systems need to be restarted again.

ssh - Secure shell. It is a Protocol used to secure communications between hosts.It encrypts the username and password.It uses private keys to authenticate user communication between hosts without a passowrd.This is useful in automating operational tasks using Shell Script,RAC installation etc.

ftp - File Transfer Protocol. It is used to upload files to a host. It needs username/password authentication.telnet - Terminal that enables us to connect to a server.rlogin - Remote login.Enables us to login remotely into a server.

rcp - Remote copy.Enables us to copy files remotely.exit - Enables us to exit a user session.crontab - Schedule shell programs and automate tasks.

ls - List the files and subdirectory in a directory.Doesnt list the hidden files.

ls -a - Lists the hidden files.some files have name like .cshrc,.loin (prefix with .). They are called as hidden files and are used for security reasons.They are not visible under normal listing.

Various types of shells - sh,ksh,bash,csh,tcsh,zsh

AD Utilities (Applications DBA Utilities)

AD Utilities (Applications DBA Utilities) are set of Oracle supplied utilities that are used to administer Oracle Applications.

A brief listing of utilities and their usage is given below:

adadmin - Performs maintenance tasks for Oracle Applications.

adchkdig - Checks the integrity of Oracle Applications patches downloaded from OracleMetaLink.

adutconf.sql - Reports standard information about the installed configuration of Oracle Applications.

adctrl - Manages parallel workers in AD Administration and AutoPatch.

adident - Reports the version and translation level of an OracleApplications file.

adncnv - Converts a file from one character set to another.

admrgpch - Merges multiple patches into a single merged patch.

adrelink.sh - Relinks Oracle Applications executable programs withthe Oracle server product libraries.

adsplice - Adds off-cycle products.

adtimrpt.sql - Reports a summary of the timing for jobs run by parallel workers.

adpatch - Applies patches and other system updates.

admsi.pl - Generates customized installation instructions for a patch.

rapidwiz - Provides a wizard for entering parameters that are specific to a new installation or an upgrade of an Oracle Applications system.

How to create ASM instance

ASM instance is used to manage database files.If a node hosts multiple databases,single database,files can be managed using ASM.

Two possible ASM deployments are:

1) Seperate oracle home is there are multiple databases on a single node

2) No seperate oracle home if the node hosts a single database.

ASM instance needs to be created and started before the Oracle database instance is started.

css - cluster synchronization service daemon is used to establish connection between an ASM instance and oracle database instance.

To startup ASM instance alone and restrict the connection to Oracle database instance startup ASM instance as follows:

SQL>STARTUP RESTRICT;

Steps to startup an ASM instance are as follows:

1) Check is css is running using

ps -efgrep css

We can also use crsctl command to check if css appears healthy

crsctl check css

If css isn't up and running startup ASM as follows

1) Login as root user

2) export PATH=PATH:$ORACLE_HOME

3) localconfig addThe localconfig command creates /etc/oracle file,startsup css.The $ORACLE_HOME/css/admin has control script for css startup and shutdown.

It adds init.cssd to /etc/inittab file.

If we use DBCA to create an instance css is started automatically.

2) Create a initialization parameter file initasm+.ora with the following parameters set.

Instance_type=+ASM

ASM_POWER_Limit =Value from 1 to 11

ASM_Diskstring = '/dev/sda*',

ASM_DISKGROUPS=dg1,dg2

3) Startup the instance.ASM doesn't have a data dictionary.It is mandatory to login as a user with SYSDBA/SYSOPER privilege.For local authentication use Operating system(OS) authentication.For remote authentication use a password file.

4) Set ORACLE_SID=+ASM (ASM is the default value).The value set for instance_type is mapped internally to db_unique_name.

$sqlplus / nolog

SQL>connect / as sysdba

SQL> startup pfile='/asd/initasm+.ora'

This step creates an ASM instance.ASM cache is the SGA component specific to ASM.It takes as little as 25MB- 30MB space.Total spce needed for an ASM instance is 100MB as it doesnt have any datafiles.

5) Check is ASM instance is up and running using :

ps -efgrep asm

The step above lists background processes like:

asm_pmon_+asm

asm_smon_+asm

asm_lgwr_+asm

asm_ckpt_+asm

asm_dbw0_+asm

asm_mman_+asm

Few special is ASM like

asm_rbal_+asm

asm_psp0_+asm

asm_gmaon_+asm

Migrating Database To ASM Using RMAN

RMAN is the only tool that can backup ASM files. It is not possible to backup using :

1)user-managd bakups
2) Os utilities and commands - they cant see ASM files as ASM files are created on raw disk and there is no file system for ASM files.

RMAN is used to migrate database to ASM.Follow the steps below.

Consider the case where we have an instance named "neworacle".We have three ASM diskgroups dgnew1 - dtles,dgnew2 - controlfile, dgnew3 - online log files.

Follow the steps given below:

1) Change COROLFILES instance parameter to point towards disk group.

SQL> Alter system set controlfiles='+dgnew2','+dgnew3' scope=spfile;

2) Shutdown the instance.
SQL> shutdown immediate;

3) Start database in nomount mode.
SQL>startup nomount;

4) Launch RMAN(RMAN client),restore controlfile from its original location:
RMAN> restore controlfile from '/originalloc/contrl1.con';

5) Issue the steps from RMAN prompt.We can script the list of steps and execute from RMAN prompt as well.This migrates the datafiles from its original location to ASM.

RMAN>shutdown immediate;
RMAN> startup mount;
RMAN>backup as copy database format '+dgnew1';
RMAN> switch database to copy;
RMAN>alter database open;

6) To migrate redologs, create new members in the diskgroups and drop the old members.

SQL>alter database add logfile member '+dgnew2','+dgnew3' to group 1;
SQL>alter database drop logfile member '/originalloc/log1a.rdo','/originalloc/log1b.rdo';

7) Final step is to move tempfiles.It is not possible to move tempfiles(temporary tablespace).So drop and recreate the temporary tablespace on a disk group.

ITIL for DBA's

What is ITIL?

ITIL stands for Infrastructure Information Library.It is a standard from U.K government that has undergone various revisions.Latest Version is ITILv3.

What is ITILv3 focussed on?
ITIL in general deals with the processes related to service management.This includes issues related to incidents mangement,problem management,configuration management,change management,release management.

What is incident management?
Any unexpected event that has an impact on the existing system is an incident.This includes database shutdown due to various failures like media error,server failure.Processes needs to be formulated and followed to handle incidents.Incidents are unknown and hence proactive measures needs to be taken to prevent them and reactive actions should be taken to adress them.

What is problem management?
Problem is an recurring issue.It is mandatory to analyse the root cause of an issue and come out with a permanent solution.Database,Server,Application logs can be used for this purpose.A good example is Load in database that needs to be tuned,deadlock issue etc.

What is configuration Management?
Any chnages made to the system configuration.This can be changes to server,database,application.

What is change management?
Whenever an incident occurs it is mandatory to raise a ticket and depending on the severity it could be given severity(priority) say SEV1 etc.

What is release management?
It is mandatory to document the patch level,software version level of infrastructure components including servers,databases.This document needs to be available to everyone for quick reference.

So whenever a change happens it has to be documented.

Why does a DBa need ITIL?
Around 40% of billion dollar software companies have started adopting ITIL.This is a standard that integrates business with infrastructure.It has resulted in increased customer satisfaction.DBA's have data which is the sole property(heart) of an organization.

So it is mandatory for them to follow the processes and document their activites for the system to be up and running on a 24X7 basis and address issues if one happens.

Controlfile Autobackup and RMAN-06172

Controlfile AUTOBACKUP is one of the best option provide by Oracle for backup through RMAN method.

In this post i will show how to restore CONTROLFILE & SPFILE in different senario

1. when AUTOBACKUP controlfile option is enable/ON and AUTOBACKUP location is also set.

means we are set following two parameter before perform database backup

RMAN>configure controlfile autobackup ON;

RMAN>configure controlfile autobackup format for device type disk to 'F:\B_%F';

NOTE: 'F:\' is a backup location where AUTOBACKUP controlfile or spfile created and "%F" is format.

RMAN> backup database;

Starting Control File and SPFILE Autobackup at 19-JUL-08

piece handle=F:\B_C-1958649224-20080719-03 comment=NONE

Finished Control File and SPFILE Autobackup at 19-JUL-08

NOTE: above underline and blue colored number is DBID for the database

Now I deleted ALL CONTROLFILES & SPFILE

C:\>del F:\oracle\product\10.1.0\oradata\test\*.CTL

C:\>del F:\oracle\product\10.1.0\Db_1\database\SPFILETEST.ora


1. Restoring SPFILE

RMAN> set dbid=1958649224

RMAN> startup nomount force;

RMAN> set controlfile autobackup format for device type disk to 'F:\B_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMATRMAN> restore SPFILE from AUTOBACKUP;

Starting restore at 19-JUL-08

using channel ORA_DISK_1channel ORA_DISK_1: looking for autobackup on day: 20080719
channel ORA_DISK_1: autobackup found: F:\B_c-1958649224-20080719-03

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 19-JUL-08


2. Restoring CONTROLFILE

RMAN> restore controlfile from 'F:\B_C-1958649224-20080719-03';Starting restore at 19-JUL-08

using channel ORA_DISK_1channel ORA_DISK_1: restoring controlfile

channel ORA_DISK_1: restore complete

output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL01.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL02.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL03.CTL

Finished restore at 19-JUL-08

NOTE: It is always recommended to set CONTROLFILE autobackup ON and also set AUTOBACKUP LOCATION AND FOMRAT MASK "%F".


2. Second condition when controlfile autobackup is ON/ENABLE but AUTOBACKUP location and format mask it not set.

means the following parameter is not set.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

NOTE: flashback_recovery_area is set. so controlfile autobackup is created in flashback_recovery_area.

First enable the autobackup controlfile option

RMAN> configure controlfile autobackup on;
RMAN> backup database;

Starting Control File and SPFILE Autobackup at 19-JUL-08

piece handle=F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 19-JUL-08

NOTE: This time controlfile autobackup is created in FRA.

Now I deleted all controlfile & spfile

RMAN> restore spfile from autobackup;

Starting restore at 19-JUL-08
using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: ===============
ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/19/2008 18:54:49

RMAN-06495: must explicitly specify DBID with SET DBID command

First disadvantage is we can't get DBID becuase controlfile autobackup option is enable for format we are not defined. In this case if it is unix platform we can get DBID from database backupset through backupset location grep MAXVALUE

but I don't know how to find in windows. but here i also keep DBID.

RMAN> set dbid=1958649224

executing command: SET DBID

RMAN> restore spfile from autobackup;
Starting restore at 19-JUL-08

using channel ORA_DISK_1channel ORA_DISK_1: looking for autobackup on day: 20080719
channel ORA_DISK_1: looking for autobackup on day: 20080718
channel ORA_DISK_1: looking for autobackup on day: 20080717
channel ORA_DISK_1: looking for autobackup on day: 20080716
channel ORA_DISK_1: looking for autobackup on day: 20080715
channel ORA_DISK_1: looking for autobackup on day: 20080714
channel ORA_DISK_1: looking for autobackup on day: 20080713
channel ORA_DISK_1: no autobackup in 7 days found

RMAN-00571: ===========================================================
RMAN-00569:
===============
ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/19/2008 19:17:17

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Note: above error occur becuase we are not set autobackup location so we need to first set controlfile or spfile backup location, in this case it is FRA.

RMAN> restore spfile from autobackup db_recovery_file_dest='F:\oracle\product\10.1.0\flash_recovery_area' db_name='TEST';Starting restore at 19-JUL-08using channel ORA_DISK_1recovery area destination: F:\oracle\product\10.1.0\flash_recovery_area

database name (or lock name space) used for search: TEST

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 19-JUL-08

shutdown the database and startup nomout with restored spfile

RMAN> restore controlfile from autobackup;

Starting restore at 19-JUL-08allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=160 devtype=DISKrecovery area destination: F:\oracle\product\10.1.0\flash_recovery_area

database name (or lock name space) used for search: TEST

channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2008_07_19\O1_MF_S_660509348_483ZQ4MF_.BKP

channel ORA_DISK_1: controlfile restore from autobackup complete

output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL01.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL02.CTL
output filename=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\CONTROL03.CTL

Finished restore at 19-JUL-08


3. when FRA is not configured or autobackup controlfile location & format is not set.

rman target /

Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.connected to target database: TEST (DBID=1958649224)RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;using target database controlfile instead of recovery catalog

RMAN configuration parameters are successfully reset to default value

RMAN> configure controlfile autobackup on;

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> backup database;Starting backup at 19-JUL-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBFinput datafile fno=00003 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBFinput datafile fno=00002 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF

input datafile fno=00004 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 19-JUL-08
channel ORA_DISK_1: finished piece 1 at 19-JUL-08

piece handle=C:\WINDOWS\SYSTEM32\05JLT74O_1_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 19-JUL-08Starting Control File and SPFILE Autobackup at 19-JUL-08
piece handle=C:\WINDOWS\SYSTEM32\C-1958649224-20080719-03 comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-08

NOTE: This time backupset and autobackup controlfile is created in default windows location: "c:\windows\system32\", interesting autobackup controlfile is created with DBID.

Oracle Metalink Notes for Oracle Apps DBAs

Dear Friends,

Here are the list of Oracle Metalink Notes, I do refer regularly. Moreover, these are very informative. So, I thought of sharing those note ids here with you.

Note: You have to have an account in Oracle Metalink to access notes.

Installation

Note: 452120.1 - How to locate the log files and troubleshoot RapidWiz for R12
Note: 329985.1 - How to locate the Rapid Wizard Installation log files for Oracle Applications 11.5.8 and higher
Note: 362135.1 - Configuring Oracle Applications Release 11i with Oracle10g Release 2 Real Application Clusters and Automatic Storage Management
Note: 312731.1 - Configuring Oracle Applications Release 11i with 10g RAC and 10g ASM
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 279956.1 - Oracle E-Business Suite Release 11i with 9i RAC: Installation and Configuration using AutoConfig
Note: 294932.1 - Recommendations to Install Oracle Applications 11i
Note: 403339.1 - Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
Note: 455398.1 - Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 11i
Note: 402311.1 - Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Microsoft Windows
Note: 405565.1 - Oracle Applications Release 12 Installation Guidelines

AD Utilities

Note: 178722.1 - How to Generate a Specific Form Through AD utility ADADMIN
Note: 109667.1 - What is AD Administration on APPS 11.0.x ?
Note: 112327.1 - How Does ADADMIN Know Which Forms Files To Regenerate?
Note: 136342.1 - How To Apply a Patch in a Multi-Server Environment
Note: 109666.1 - Release 10.7 to 11.0.3 : What is adpatch ?
Note: 152306.1 - How to Restart Failed AutoInstall Job
Note: 356878.1 - How to relink an Applications Installation of Release 11i and Release 12
Note: 218089.1 - Autoconfig FAQ
Note: 125922.1 - How To Find Oracle Application File Versions

Cloning

Note: 419475.1 - Removing Credentials from a Cloned EBS Production Database
Note: 398619.1 - Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)
Note: 230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone
Note: 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone
Note: 364565.1 - Troubleshooting RapidClone issues with Oracle Applications 11i
Note: 603104.1 - Troubleshooting RapidClone issues with Oracle Applications R12
Note: 435550.1 - R12 Login issue on target after cloning
Note: 559518.1 - Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Note: 216664.1 - FAQ: Cloning Oracle Applications Release 11i

Patching

Note: 225165.1 - Patching Best Practices and Reducing Downtime
Note: 62418.1 - PATCHING/PATCHSET FREQUENTLY ASKED QUESTIONS
Note: 181665.1 - Release 11i Adpatch Basics
Note: 443761.1 - How to check if a certain Patch was applied to Oracle Applications instance?
Note: 231701.1 - How to Find Patching History (10.7, 11.0, 11i)
Note: 60766.1 - 11.0.x : Patch Installation Frequently Asked Questions
Note: 459156.1 - Oracle Applications Patching FAQ for Release 12
Note: 130608.1 - AdPatch Basics
Note::60766.1 - Patch Installation FAQ (Part 1)

Upgrade

Note: 461709.1 - Oracle E-Business Suite Upgrade Guide - Plan
Note: 293166.1 - Previous Versions of e-Business 11i Upgrade Assistant FAQ
Note: 224875.1 - Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note: 224814.1 - Installation, Patching & Upgrade Current Issues
Note: 225088.1 - Installation, Patching & Upgrade Patches Guide
Note: 225813.1 - Installation, Patching & Upgrade Setup and Usage Guide
Note: 224816.1 - Installation, Patching & Upgrade Troubleshooting Guide
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 362203.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Note: 423056.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.2)
Note: 726982.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.3)
Note: 452783.1 - Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
Note: 406652.1 - Upgrading Oracle Applications 11i DB to DB 10gR2 with Physical Standby in Place
Note: 316365.1 - Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions
Note: 418161.1 - Best Practices for Upgrading Oracle E-Business Suite

Printer

Note: 297522.1 - How to investigate printing issues and work towards its resolution ?
Note: 110406.1 - Check Printing Frequently Asked Questions
Note: 264118.1 - Pasta Pasta Printing Setup Test
Note: 200359.1 - Oracle Application Object Library Printer Setup Test
Note: 234606.1 - Oracle Application Object Library Printer Initialization String Setup Test
Note: 1014599.102 - Subject: How to Test Printer Initialization Strings in Unix

Performance

Note: 390137.1 - FAQ for Collections Performance
Note: 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note: 169935.1 - Troubleshooting Oracle Applications Performance Issues
Note: 171647.1 - Tracing Oracle Applications using Event 10046
Note: 153507.1 - Oracle Applications and StatsPack
Note: 356501.1 - How to Setup Pasta Quickly and Effectively
Note: 333504.1 - How To Print Concurrent Requests in PDF Format
Note: 356972.1 - 11i How to troubleshoot issues with printers

Others

Note: 189367.1 - Best Practices for Securing the E-Business Suite
Note: 403537.1 - Best Practices For Securing Oracle E-Business Suite Release 12
Note: 454616.1 - Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note: 394692.1 - Oracle Applications Documentation Resources, Release 12
Note: 370274.1 - New Features in Oracle Application 11i
Note: 130183.1 - How to Get Log Files from Various Programs for Oracle Applications
Note: 285267.1 - Oracle E-Business Suite 11i and Database FAQ
Note: 453137.1 - Oracle Workflow Best Practices Release 12 and Release 11i
Note: 398942.1 - FNDCPASS Utility New Feature ALLORACLE
Note: 187735.1 - Workflow FAQ - All Versions

Oracle Applications 11i/12 Online Documentation

http://www.oracle.com/technology/documentation/applications.html

Turn on Listener Logging

Enable loggin in listener:
In order to know, which listener commands were executing, one should enable the logging by the following:

LSNRCTL> set log_directory …/log
LSNRCTL> set log_file Listener_.log
LSNRCTL> set log_status on LSNRCTL> save_config

Set the Listener Password :
Setting Listener Password is strongly recommended in order to prevent the distinct attacks. There are two ways to do the same. Either by setting PASSWORDS_listenername parameter in listener.ora file (Password will be in a Clear Text – Not Recommended)Or By using lsnrctl set password command (Highly Recommended).

BLOCK RECOVER BY RMAN

Recover corrupt blocks in three datafiles
------------------------------------------

BLOCKRECOVER DATAFILE 2 BLOCK 12, 13;

BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5,98,99 DATAFILE 4 BLOCK 19;

Limit block recovery by restoration type

RUN {
BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4,5 TABLESPACE sales DBA 4194405, 4194409, 4194412
FROM DATAFILECOPY;
}

Limit block recovery by backup tag
-----------------------------------

BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 FROM TAG "weekly_backup";

Limit block recovery by time
-----------------------------
BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 RESTORE UNTIL TIME 'SYSDATE-2';


Repair all corrupt blocks in the database
-----------------------------------------
BACKUP VALIDATE DATABASE;BLOCKRECOVER CORRUPTION LIST;

This command will find, list and then recover all corrupted blocks in the database using the gv$database_block_corruption view.

Oracle 10g CRS upgrade to 11g CRS

To upgrade Oracle 10g CRS to 11g CRS, we have two options -

• Perform a rolling upgrade (this requires that your current CRS version >=10.2.0.3 (or 10.2.0.2 with Bundle patch). This option allows us to upgrade the CRS without a complete unavailabilty of downtime
  
• Upgrade the CRS on all the nodes at the same time with complete downtime.

I will go over the steps for upgrading the CRS to 11g using the first option mentioned above.

On Node 1:

1. Stop the CRS: Either use $CRS_HOME/bin/crsctl stop crs as root or run the following from the staging as root:

/Staging_Area/11.1.0.6/clusterware/upgrade/preupdate.sh –crshome $CRS_HOME –crsuser oracle

2. Invoke runInstaller from the staging area:



3. As you notice, the option to specify the Home and the destination directory is disabled since OUI has detected the existence of a Cluster.


4. Select the local node (in my case it is alps01), selecting both the nodes requires the CRS to be down on both the nodes - meaning it would require a complete downtime - and would no more be a rolling upgrade:



5. Not shutting down the CRS on the local node would cause the following error:

6. This information would appear in the install logs - pretty detailed and informative :-)

7. Once you shutdown the CRS on the local node and when re-attempted, it should be fine (no errors or warnings):


8. On clicking next, the summary screen would appear. Note that we are upgrading the CRS only the local node (alps01):

9. Install progress screen:

10. At the end of the upgrade, the Installer would prompted us to run the rootupgrade script as root:


11. Output of the rootupgrade script:

12. Check the status of CRS versions:


On Node 2:
13. Shutdown the CRS on node 2. Note that while performing a rolling upgrade, we have to invoke the OUI from the second node and this can't be done from node 1.

14. Invoke the OUI from node 2 and select only the local node and deselect the remote or first node:

15. Cluster Verification screen....looks good.

17. Prompt to run rootupgrade script after the upgrade on Node 2:

18. Output of rootupgrade script on Node 2:

[root@everest02 upgrade]# /opt/oracle/product/crs/install/rootupgrade
Checking to see if Oracle CRS stack is already up...
WARNING: directory '/opt/oracle/product' is not owned by root
WARNING: directory '/opt/oracle' is not owned by root

Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab
Attempting to start Oracle Clusterware stack
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Waiting for Cluster Synchronization Services daemon to start
Cluster Synchronization Services daemon has started
Event Manager daemon has started
Cluster Ready Services daemon has started
Oracle CRS stack is running under init(1M)
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 2: everest02 everest02-priv everest02
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
CRS stack on this node, is successfully upgraded to 11.1.0.6.0
Checking the existence of nodeapps on this node
Creating '/opt/oracle/product/crs/install/paramfile.crs' with data used for CRS configuration
Setting CRS configuration values in /opt/oracle/product/crs/install/paramfile.crs

[root@everest02 upgrade]#

19. Check or verify the version of CRS:

Configuring SSH

Configure SSH between two nodes

While logged in as oracle perform the following on each of the nodes of the RAC:

1. cd $HOME
2. mkdir ~/.ssh
3. chmod 700 ~/.ssh
4. /usr/bin/ssh-keygen -t rsa
5. /usr/bin/ssh-keygen -t dsa

On Node 1:

1. cd $HOME/.ssh
2. cat id_rsa.pub >> authorized_keys
3. cat id_dsa.pub >> authorized_keys
4. Copy the authorized_keys file to the node 2. scp authorized_keys node2:/opt/oracle/.ssh

On Node 2:

1. cd $HOME/.ssh
2. cat id_rsa.pub >> authorized_keys
3. cat id_dsa.pub >> authorized_keys
4. scp authorized_keys node1:/opt/oracle/.ssh

• Now perform a ssh between all the nodes including the node-priv hostnames as well. Check to make sure that ssh is configured well without prompting for the password (on both the nodes):
  

$ ssh coke.pinnacle.com date
Sun Aug 12 08:41:42 CDT 2007
$ ssh pepsi.pinnacle.com date
Sun Aug 12 08:42:23 CDT 2007
$ ssh coke-priv date
Sun Aug 12 08:42:45 CDT 2007
$ ssh pepsi-priv date
Sun Aug 12 08:43:22 CDT 2007

Oracle 11g RAC Install on Red Hat 5.0 (Linux) using NFS

Here is the procedure I followed to install Oracle 11g RAC on RHEL 5.0 and thought would share this information with everyone who is interested in checking out Oracle11g RAC on Linux. Most of the pre-installation steps should work for Oracle10g as well. This configuration is not for production systems and would be good for testing purposes only.

I have used my two existing two Intel based systems for the purpose, and in all it took about 8 hours to get the whole thing working (including the OS install – as a third bootable Operating System – the first one being Windows XP the second being RHEL 4.0 with 10gR2 RAC).....much better than the initial installation experiences with Oracle10g RAC.

Please note that I am not using Firewire or a third server here for provisioning the shared storage, but NFS - the details follow.


Hardware details:

• Node 1: pepsi.pinnacle.com (Intel Pentium 4 2.80Ghz, 2G RAM with 80GB and 160GB hard drives, 2 NICs)
• Node 2: coke.pinnacle.com (Intel Pentium 4 2.80Ghz, 2G RAM with 160GB, 2 NICs)

RHEL 5.0 Installation:

I have performed a complete/full installation – basically all/everything...so that all the RPMs are included therein. Kernel version used - 2.6.18-8.el5xen.

Installation Steps:

Here are high-level installation steps:

1. Check OS/Packages requirements
2. Configure public and private network.
3. Create oracle user and dba group.
4. Setup SSH for oracle user account between the two nodes.
5. Set up shared storage using NFS (details follow)
6. Oracle11g CRS Installation
7. Oracle11g RDBMS Software
8. Oracle11g RAC Database creation.

The details.......

1. Configure Operating System for Oracle11g RAC Install (Both nodes)

The following RPMs are required/recommended for Oracle11g RAC installation on RHEL 5.0.

1. binutils-2.17.50.0.6-2.el5
2. compat-libstdc++-33-3.2.3-61
3. elfutils-libelf-0.97-5 – elfutils-libelf-devel-0.125-3.el5 was installed by default
4. elfutils-libelf-devel-0.125
5. glibc-2.5-12
6. glibc-common-2.5-12
7. glibc-devel-2.5-12
8. gcc-4.1.1-52
9. gcc-c++-4.1.1-52
10. libaio-0.3.106
11. libaio-devel-0.3.106 – not installed by default
12. libgcc-4.1.1-52
13. libstdc++-4.1.1
14. libstdc++-devel-3.4.3-22.1 – libstdc++-devel-4.1.1-52.el5 was installed by default.
15. make-3.81-1.1
16. sysstat-7.0.0 – not installed.
17. UnixODBC-2.2.11 – not installed.
18. UnixODBC-devel-2.2.11 – not installed.

All the above RPMs were installed and available by default (may be because I have done a complete install). Installed the following 4 missing RPMs from RHEL 5.0 CDs (using rpm -ivh ).

1. libaio-devel-0.3.106
2. sysstat-7.0.0
3. UnixODBC-2.2.11
4. UnixODBC-devel-2.2.11

Kernel Parameters:

• Add the following lines to /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

• Run sysctl -p to read the values as indicated in /etc/sysctl.conf file.

CVUQDISK Package:

• Install the cvuqdisk package – located in the CRS software distribution (/clusterware/rpm):

[root@coke rpm]# export CVUQDISK_GRP=dba
[root@coke rpm]# rpm -ivh cvuqdisk-1.0.1-1.rpm
Preparing... ########################################### [100%]
1:cvuqdisk ########################################### [100%]
[root@coke rpm]# pwd
/share/Oracle-Linux-Software/11g/clusterware/rpm
[root@coke rpm]#

• Here is the information from the /etc/hosts file. The contents of this file are same on both the nodes.

$ cat /etc/hosts
#----Entries for Node1
192.168.1.160 pepsi.pinnacle.com pepsi
192.168.1.151 pepsi-vip.pinnacle.com pepsi-vip
172.1.1.160 pepsi-priv.pinnacle.com pepsi-priv
#---Entries for Node2
192.168.1.150 coke.pinnacle.com coke
192.168.1.161 coke-vip.pinnacle.com coke-vip
172.1.1.150 coke-priv.pinnacle.com coke-priv

Node 1:
[root@coke ~]# ifconfig -a eth0
eth0 Link encap:Ethernet HWaddr 00:16:76:4B:79:F1
inet addr:192.168.1.150 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::216:76ff:fe4b:79f1/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:3507 errors:0 dropped:0 overruns:0 frame:0
TX packets:1675 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:665921 (650.3 KiB) TX bytes:188926 (184.4 KiB)
[root@coke ~]# ifconfig -a eth1
eth1 Link encap:Ethernet HWaddr 00:09:5B:E1:CD:DD
inet addr:172.1.1.150 Bcast:172.1.255.255 Mask:255.255.0.0
inet6 addr: fe80::209:5bff:fee1:cddd/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:310738 errors:0 dropped:0 overruns:0 frame:0
TX packets:254557 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:141179047 (134.6 MiB) TX bytes:345107330 (329.1 MiB)

Node 2:
[root@pepsi ~]# ifconfig -a eth0
eth0 Link encap:Ethernet HWaddr 00:16:76:38:D8:C5
inet addr:192.168.1.160 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::216:76ff:fe38:d8c5/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:419 errors:0 dropped:0 overruns:0 frame:0
TX packets:244 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:80282 (78.4 KiB) TX bytes:27401 (26.7 KiB)
[root@pepsi ~]# ifconfig -a eth1
eth1 Link encap:Ethernet HWaddr 00:09:5B:BB:D8:F1
inet addr:172.1.1.160 Bcast:172.1.255.255 Mask:255.255.0.0
inet6 addr: fe80::209:5bff:febb:d8f1/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:361934 errors:0 dropped:0 overruns:0 frame:0
TX packets:326328 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:366491792 (349.5 MiB) TX bytes:148740977 (141.8 MiB)
Interrupt:17 Base address:0xe000


2. Configure SSH between two nodes

While logged in as oracle perform the following on each of the nodes of the RAC:

1. cd $HOME
2. mkdir ~/.ssh
3. chmod 700 ~/.ssh
4. /usr/bin/ssh-keygen -t rsa
5. /usr/bin/ssh-keygen -t dsa

On Node 1:

1. cd $HOME/.ssh
2. cat id_rsa.pub >> authorized_keys
3. cat id_dsa.pub >> authorized_keys
4. Copy the authorized_keys file to the node 2. scp authorized_keys node2:/opt/oracle/.ssh

On Node 2:

1. cd $HOME/.ssh
2. cat id_rsa.pub >> authorized_keys
3. cat id_dsa.pub >> authorized_keys
4. scp authorized_keys node1:/opt/oracle/.ssh

• Now perform a ssh between all the nodes including the node-priv hostnames as well. Check to make sure that ssh is configured well without prompting for the password (on both the nodes):
  

$ ssh coke.pinnacle.com date
Sun Aug 12 08:41:42 CDT 2007
$ ssh pepsi.pinnacle.com date
Sun Aug 12 08:42:23 CDT 2007
$ ssh coke-priv date
Sun Aug 12 08:42:45 CDT 2007
$ ssh pepsi-priv date
Sun Aug 12 08:43:22 CDT 2007


3. Configuring the Shared Storage

This is the one of the critical components required for RAC. There are couple of other storage options available for setting up Test environment at home, such as Firewire, NAS and/or NFS. I have installed Oracle RAC with both Oracle9i and Oracle10g using Firewire and this time around I wanted to see if I can use NFS instead. Please note that this is only for testing and experimentation only and will not be suitable for any kind of production use. Usually, we would be needing an separate server/node which can host the NFS file systems, but I have, instead of using a third server, I have configured one of the nodes to host the NFS file systems. Here is the procedure:

On Node 1 (coke):

1. Created three partitions on the local hard drive with a size of 10G each.

[root@coke ~]# fdisk /dev/sda
The number of cylinders for this disk is set to 9726.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): p
Disk /dev/sda: 80.0 GB, 80000000000 bytes
255 heads, 63 sectors/track, 9726 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 2550 20482843+ c W95 FAT32 (LBA)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
e
Partition number (1-4): 2
First cylinder (2551-9726, default 2551):
Using default value 2551
Last cylinder or +size or +sizeM or +sizeK (2551-9726, default 9726):
Using default value 9726
Command (m for help): p
Disk /dev/sda: 80.0 GB, 80000000000 bytes
255 heads, 63 sectors/track, 9726 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 2550 20482843+ c W95 FAT32 (LBA)
/dev/sda2 2551 9726 57641220 5 Extended
Command (m for help): n
Command action
l logical (5 or over)
p primary partition (1-4)
l
First cylinder (2551-9726, default 2551):
Using default value 2551
Last cylinder or +size or +sizeM or +sizeK (2551-9726, default 9726): +10G
Command (m for help): n
Command action
l logical (5 or over)
p primary partition (1-4)
l
First cylinder (3768-9726, default 3768):
Using default value 3768
Last cylinder or +size or +sizeM or +sizeK (3768-9726, default 9726): +10G
Command (m for help): n
Command action
l logical (5 or over)
p primary partition (1-4)
l
First cylinder (4985-9726, default 4985):
Using default value 4985
Last cylinder or +size or +sizeM or +sizeK (4985-9726, default 9726): +10G
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Device Boot Start End Blocks Id System
/dev/sda1 * 1 2550 20482843+ c W95 FAT32 (LBA)
/dev/sda2 2551 9726 57641220 5 Extended
/dev/sda5 2551 3767 9775521 83 Linux
/dev/sda6 3768 4984 9775521 83 Linux
/dev/sda7 4985 6201 9775521 83 Linux

[root@coke ~]# parted /dev/sda print
Model: ATA WDC WD800JD-75MS (scsi)
Disk /dev/sda: 80.0GB
Sector size (logical/physical): 512B/512B
Partition Table: msdos
Number Start End Size Type File system Flags
1 32.3kB 21.0GB 21.0GB primary fat32 boot, lba
2 21.0GB 80.0GB 59.0GB extended
5 21.0GB 31.0GB 10.0GB logical
6 31.0GB 41.0GB 10.0GB logical
7 41.0GB 51.0GB 10.0GB logical
Information: Don't forget to update /etc/fstab, if necessary.

• Create file systems

[root@coke ~]# mkfs.ext3 /dev/sda5
[root@coke ~]# mkfs.ext3 /dev/sda6
[root@coke ~]# mkfs.ext3 /dev/sda7

• Create local mount points

[root@coke ~]# mkdir /nfs1 /nfs2 /nfs3

• Label the partitions/file systems

[root@coke ~]# e2label /dev/sda5 nfs1
[root@coke ~]# e2label /dev/sda6 nfs2
[root@coke ~]# e2label /dev/sda7 nfs3

• Add the following lines to the /etc/fstab file on node 1(coke)

LABEL=nfs1 /nfs1 ext3 defaults 1 1
LABEL=nfs2 /nfs1 ext3 defaults 1 1
LABEL=nfs3 /nfs1 ext3 defaults 1 1

• Mount the files systems:

[root@coke ~]# mount /nfs2
[root@coke ~]# mount /nfs2
[root@coke ~]# mount /nfs3
[root@coke ~]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/hda3 47611656 2938484 42215632 7% /
tmpfs 966124 0 966124 0% /dev/shm
/dev/sda5 9621848 152688 8980384 2% /nfs1
/dev/sda6 9621848 152688 8980384 2% /nfs2
/dev/sda7 9621848 152688 8980384 2% /nfs3

• Add the following entries in the /etc/exports file:

/nfs1 *(rw,sync,no_wdelay,insecure_locks,no_root_squash)
/nfs2 *(rw,sync,no_wdelay,insecure_locks,no_root_squash)
/nfs3 *(rw,sync,no_wdelay,insecure_locks,no_root_squash)

• Configure the NFS services:

[root@coke ~]# chkconfig nfs on
[root@coke ~]# service nfs restart
Shutting down NFS mountd: [ OK ]
Shutting down NFS daemon: [ OK ]
Shutting down NFS quotas: [ OK ]
Shutting down NFS services: [ OK ]
Starting NFS services: [ OK ]
Starting NFS quotas: [ OK ]
Starting NFS daemon: [ OK ]
Starting NFS mountd: [ OK ]
[root@coke ~]#

• With the above, we have /nfs* file systems which are available for both the nodes. Peform the following steps on both the nodes:

1. Create mount points to mount the nfs file systems

#mkdir /u01 /u02 /u03 on two nodes

2. Add the following lines to the /etc/fstab file:

coke-priv:/nfs1 /u01 nfs
rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0
coke-priv:/nfs2 /u02 nfs
rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0
coke-priv:/nfs3 /u03 nfs
rw,bg,hard,nointr,tcp,vers=3,timeo=300,rsize=32768,wsize=32768,actimeo=0 0 0

3. Mount the nfs filesystem locally:

#mount /u01
#mount /u02
#mount /u03

4. Now we have all the three file systems shared among the two nodes and ready for use.

Here is how it will look like on Node 1:

$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/hda3 47611656 7029084 38125032 16% /
tmpfs 966124 270188 695936 28% /dev/shm
/dev/sda5 9621848 1925276 7207796 22% /nfs1
/dev/sda6 9621848 152700 8980372 2% /nfs2
/dev/sda7 9621848 152696 8980376 2% /nfs3
172.1.1.150:/nfs1 9621856 1925280 7207808 22% /u01
172.1.1.150:/nfs2 9621856 152704 8980384 2% /u02
172.1.1.150:/nfs3 9621856 152704 8980384 2% /u03

On Node 2:

$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda5 44639012 7245820 35089072 18% /
tmpfs 477484 266084 211400 56% /dev/shm
coke-priv:/nfs1 9621856 1925280 7207808 22% /u01
coke-priv:/nfs2 9621856 152704 8980384 2% /u02
coke-priv:/nfs3 9621856 152704 8980384 2% /u03

● Change the owner of the file systems /u01, /u02 and /u03 to oracle:dba.

4. CRS Installation

• Set up the environment variables for oracle (such ORACLE_BASE, PATH etc..)

• With this we have all the basic pre-installations tasks taken care of and are ready to invoke the Oracle11g OUI from the CRS software distribution, but before that, let's run the CVU:
  

$ ./runcluvfy.sh stage -pre crsinst -n coke,pepsi

• Invoke the Installer:

Running orainstRoot.sh and root.sh scripts on both the nodes:

[root@coke ~]# /opt/oracle/oraInventory/orainstRoot.sh
Changing permissions of /opt/oracle/oraInventory to 770.
Changing groupname of /opt/oracle/oraInventory to dba.
The execution of the script is complete
[root@coke ~]# /opt/oracle/product/11.1.0/crs/root.sh
WARNING: directory '/opt/oracle/product/11.1.0' is not owned by root
WARNING: directory '/opt/oracle/product' is not owned by root
WARNING: directory '/opt/oracle' is not owned by root
Checking to see if Oracle CRS stack is already configured
/etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up Network socket directories
Oracle Cluster Registry configuration upgraded successfully
The directory '/opt/oracle/product/11.1.0' is not owned by root. Changing owner to root
The directory '/opt/oracle/product' is not owned by root. Changing owner to root
The directory '/opt/oracle' is not owned by root. Changing owner to root
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: coke coke-priv coke
node 2: pepsi pepsi-priv pepsi
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Now formatting voting device: /u01/oradb/ocr/vote
Format of 1 voting devices complete.
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
coke
Cluster Synchronization Services is inactive on these nodes.
pepsi
Local node checking complete. Run root.sh on remaining nodes to start CRS daemons.
[root@coke ~]# /opt/oracle/product/11.1.0/crs/root.sh
[root@pepsi ~]# /opt/oracle/oraInventory/orainstRoot.sh
Changing permissions of /opt/oracle/oraInventory to 770.
Changing groupname of /opt/oracle/oraInventory to dba.
The execution of the script is complete
[root@pepsi ~]# /opt/oracle/product/11.1.0/crs/root.sh
WARNING: directory '/opt/oracle/product/11.1.0' is not owned by root
WARNING: directory '/opt/oracle/product' is not owned by root
WARNING: directory '/opt/oracle' is not owned by root
Checking to see if Oracle CRS stack is already configured
/etc/oracle does not exist. Creating it now.
Setting the permissions on OCR backup directory
Setting up Network socket directories
Oracle Cluster Registry configuration upgraded successfully
The directory '/opt/oracle/product/11.1.0' is not owned by root. Changing owner
to root
The directory '/opt/oracle/product' is not owned by root. Changing owner to root
The directory '/opt/oracle' is not owned by root. Changing owner to root
clscfg: EXISTING configuration version 4 detected.
clscfg: version 4 is 11 Release 1.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: coke coke-priv coke
node 2: pepsi pepsi-priv pepsi
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
coke
pepsi
Cluster Synchronization Services is active on all the nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
Creating VIP application resource on (2) nodes...
Creating GSD application resource on (2) nodes...
Creating ONS application resource on (2) nodes...
Starting VIP application resource on (2) nodes...
Starting GSD application resource on (2) nodes...
Starting ONS application resource on (2) nodes...
Done.
[root@pepsi ~]#

$ crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
$
We have a up and running Oracle 11g CRS.

Note that you have to always bring up Node 1 first and then Node 2, since Node 1 is hosting the NFS file system required by Node 2.

Export/Import Data Pump - Copying Schemas

Let’s say that we have a Schema in Database A, which needs to be copied over to Database B. With the traditional export/import, we have to precreate the user account in the Database B, before we can import the schema. But with oracle10g’s Pump utility, it is just one simple step:

Here is an example. In this example, the schema CHANDRA exists in database A and we attempt to copy over the same schema as CHANDRA3 in a remote database (rhes01.myoracle.com).

$impdp system/******* schemas=chandra network_link=rhes01.myoracle.com remap_schema=chandra:chandra3

Import: Release 10.2.0.3.0 - Production on Saturday, 05 May, 2007 18:54:21

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/******** schemas=chandra network_link=rhes01.myoracle.com remap_schema=chandra:chandra3

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 18:54:51

******************************************************************************************
This creates the user CHANDRA3 as well in the remote/target database.

Don’t forget to provide different filenames when PARALLEL is used with Export Dump.

If you use PARALLEL option with Export Dump and fail to provide file names equal to the number of PARALLEL, then it would perform the export in serial - meaning writes to a single file.

For example, if you have something like this: $ expdp dumpfile=expdat.dmp directory=dump_dir parallel=4
It would create just one file with the name expdat.dmp. Instead you can specify some thing like:

$ expdp dumpfile=expdat.%U.dmp directory=dump_dir parallel=4

This would create 4 files in parallel with names (expdat.01.dmp, expdat.02.dmp, expdat.03.dmp..).