Monday, 14 February 2011

Apply oracle database patch Opatch without downtime

This is commonly asked question in interviews.

To apply Opatch coditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches. in single instance its not possible. but for RAC instance its possible.
as in RAC there will be two separate oracle home and two separate instances running once instance on each oracle_home

use this command:
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.

steps before applying patch:
1) check the database status.
select name,open_mode,database_name,created,log_mode,platform_name  from v$database;

2) Check the object's invalid.
SELECT owner,COUNT(*)   FROM dba_objects   WHERE status = 'INVALID'  GROUP BY owner;

select count(*)   from dba_objects WHERE status ='INVALID';

3) Take backup of invalid's

create table bk_inv_ as select * from dba_objects   where status='INVALID';

4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and download the latest version and uncompress in $ORACLE_HOME.

5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc


if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME

inventory must point to the current $ORACLE_HOME which is getting patched.

6) check free space on $ORACLE_HOME

7) chek the utilities like
which ld
which ar
which make
etc as per readme file.

8) unzip the patch
unzip -d /loc_2_unzip

9) Go the patch directory
cd /loc_2_unzip/patch_number

10) Bring down the listner.
lsnrctl stop

11) Bring down the database
Shutdown immediate.

12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin

13) Start the patch
opatch napply -skip_subset -skip_duplicate

for RAC database then database can be up as it may be having more then one instance so you can bring down one instance and listener and apply the patch and open it and then do the same on another node.
like this db will be up and no user will face issue in outage also.

to apply opatch in RAC instance
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.

. All-Node Patch

. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3

. (no downtime)

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3

14) Once patch installation is completed need to do post patching steps.

a) starup the instance
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

to check the logs generated

c) Recompiling Views in the Database
shutdown immediate
startup upgrade
shutdown immediate

If it is a RAC instance.
startup nomount
alter database set cluster database=false scope=spfile;
startup upgrade
alter database set cluster database=true scope=spfile;

restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows

SELECT owner,
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

select count(*)
from dba_objects
WHERE status ='INVALID';

if any new invalids seen then again take backup of invalid objects and compile it.

create table bk_inv_ as select * from dba_objects
where status='INVALID';

@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.

16) Confirm that patch has been applied successfully or not at db level also.

col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;

Convert a single instance to RAC manually

Manual Method

Following are the single instance details



Datafile location = /u03/oradata/prod — /u03 is a ocfs shared file system. So we are going to keep the datafiles are same location while converting to RAC.

In case your files are at some other slot or disk and not in shared file system, you need to copy the same and then rename the files when you mount the instance.

Database version = 10g R2 (

Steps to convert single instance to RAC

Step 1) Install clusterware on the nodes on which you want to setup RAC

For detailed steps on installing clusterware on the nodes, you can refer to my previous post –

or you can refer to any other post over the internet. Basically you need to setup the IP addresses and other OS related files and variables before you can install the clusterware.

Your clusterware version must be greater then or equal to the single instance RDBMS version. Make sure you do this step correct.

Following are cluster installation details

Cluster name : crs

Cluster install location : /u01/app/oracle/product/10.2.0/crs

OCR File location : /u03/oracrs/ocr.ora

Voting disk location : /u03/oracrs/

Step 2) Install Oracle Database 10g Real Application Cluster software

Again for this step, you can refer to my previous post – or any post on the internet.

Just install the s/w. The RDBMS software version must be same as your single instance RDBMS software version

Following are the details of installation

RAC RACLE_HOME=/u01/app/oracle/product/10.2.0/db

Number of instances = 2

Node names for 2 instances = ocvmrh2103, ocvmrh2190

/u01 is a individual filesystem and is not mounted commonly on both nodes. This is a separate ORACLE_HOME architecture.

Step 3) Take the backup of single instance database and restore the same to the shared file system location.

This step is not required in my case as I created the database on a shared filesystem only. But this is only for demo purpose. For real time scenario, you need to copy datafiles to shared filsystem.

Step 4) Copy init.ora file of single instance and add following parameters

bash-3.00$ cp initprod.ora /tmp/initprod.ora

In my case the database name is “prod” and I am converting this single instance database to a 2 node RAC. So in my case instance 1 name becomes prod1 and instance 2 name becomes prod2

So add following parameters to /tmp/initprod.ora file

*.cluster_database = TRUE
*.cluster_database_instances = 2

Step 5) change the location of controlfile in the above /tmp/initprod.ora file

In my case the controlfiles are present in /u03 location which is a OCFS shared filesystem. So I dont have to change the locaiton of controlfiles in my init.ora

Incase you have moved the controlfiles along with the datafiles to shared filesystem location, then you need to change the path of controlfile in the above init.ora file copied in/tmp location

Step 6) Create SPFILE from PFILE

SQL> select name from v$database;


SQL> create spfile='/u03/oradata/prod/spfileprod.ora' from pfile='/tmp/initprod.ora';

File created.


Step 7) Copy spfile to the RAC ORACLE_HOME/dbs location of instance 1 and create pfile

bash-3.00$ cp spfileprod.ora /u01/app/oracle/product/10.2.0/db/dbs/spfileprod.ora
bash-3.00$ pwd
bash-3.00$ cat initprod1.ora

Step 8) Create new password file for prod1 instance under RAC oracle home

bash-3.00$ orapwd file=orapwprod1 password=welcome1

Step 9) Start database in mount stage and rename datafiles and redo log files to new shared location

In my case since the datafiles and online redo logs are placed at same shared location, I dont need to do this step. However in real time scenario, this step is required.

make sure that your ORACLE_HOME variable is set to RAC ORACLE_HOME

bash-3.00$ echo $ORACLE_HOME
bash-3.00$ echo $ORACLE_SID
SQL> startup mount pfile=initprod1.ora
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  1222168 bytes
Variable Size             213912040 bytes
Database Buffers          620756992 bytes
Redo Buffers                2969600 bytes
Database mounted.

Step 10) Add second thread to database which will be for instance 2

SQL> alter database add logfile thread 2 group 4 ('/u03/oradata/prod/redo2_01.dbf') size 50M, group 5 ('/u03/oradata/prod/redo2_02.dbf') size 50M, group 6 ('/u03/oradata/prod/redo2_03.dbf') size 50M;

Database altered.


Database altered.

SQL> alter database enable public thread 2;

Database altered.

Step 11) Create undo tablespace for instance 2

The name of the undo tablespace should be same as you specified in the init.ora file in step 4 above.

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u03/oradata/prod/undotbs2_01.dbf' size 25M;

Tablespace created.

Step 12) Run $ORACLE_HOME/rdbms/admin/catclust.sql to create cluster database specific views within the existing instance 1

SQL> @?/rdbms/admin/catclust.sql

Step 13)

On the second node, set ORACLE_HOME and SID for instance 2

bash-3.00$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
bash-3.00$ export ORACLE_SID=prod2

Create initprod2.ora on second node similar to node 1. In this case you have to copy spfile to second node as well. You can also keep spfile in shared location (/u03 in my case) and put same path in initprod2.ora

bash-3.00$ pwd
bash-3.00$ ls -lrt spfileprod.ora
-rw-r-----  1 oracle oinstall 3584 Feb 19 12:36 spfileprod.ora
bash-3.00$ cat initprod2.ora

Step 14) Create new password file for instance 2

bash-3.00$ orapwd file=orapwprod2 password=welcome1

Step 15) Start the second instance

SQL> startup pfile=initprod2.ora
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  1222168 bytes
Variable Size             213912040 bytes
Database Buffers          620756992 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

You might face some issue while starting second instance as bdump, udump and cdump dir location will be that of single instance ORACLE_HOME which is not present in node2.

Also you might hit following error

SQL> startup
ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
SQL> Disconnected

Make sure you alter following parameters to a valid location and copy spfileprod.ora again to node2


Step 16) Add the converted database to cluster

move the spfile to the common location such as /u03/oradata/prod and modify both the pfiles so that both pfiles refers to same spfile and there are no 2 copies.

bash-3.00$ srvctl add database -d prod -o /u01/app/oracle/product/10.2.0/db -p /u03/oradata/prod/spfileprod.ora
bash-3.00$ srvctl add instance -d prod -i prod1 -n OCVMRH2103
bash-3.00$ srvctl add instance -d prod -i prod2 -n OCVMRH2190

Sunday, 13 February 2011

Multi node to Single node Cloning – 11i + post clone checks

Pre-cloning Tasks
1. Apply application tier patches using adpatch
Apply patch 4038964
Apply patch 4175764
Both the above patches are include in the consolidated update 2 or CU2 so in case you are on or later you can ignore this step.
2. Maintain snapshot information
Log in as the applications user on each application tier node and run ‘Maintain Snapshot Information’ by using adadmin.
3. Merge existing APPL_TOPs
Log in to the primary node of your application tier as the application user user and run:
$ cd [COMMON_TOP]/admin/scripts/[CONTEXT_NAME]
$ perl appsTier merge
This will prompt you with option to merge secondary nodes
Now log in as the applications user to each of the secondary application tier nodes being merged and run:
$ cd [COMMON_TOP]/admin/scripts/[CONTEXT_NAME]
$ perl appltop merge
4. Prepare the source system database tier.
Log on to the database tier of the source system as the oracle user and run the following commands:
$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]
$ perl dbTier
With this all the pre clone tasks on the source system have been completed.
Steps to be performed on target system
1. Create OS user accounts
Create a OS user account for your applications
$ useradd -g dba -d [home_directory] -p password username
Similarly create a OS user account for your database
2. Modify the orainventory to reflect the new location
$ vi /etc/oraInst.loc
3. Copy the following application tier directories
Copy the following application tier directories from the primary node of your source system to the target application tier node, retaining the original directory structure:
– [COMMON_TOP/util]
– [COMMON_TOP/clone>
– [iAS ORACLE_HOME]  4. Copy the required files for merging
Log in as the applications user to each of the secondary source nodes and recursively copy:
directory [COMMON_TOP]/clone/appl
- to -
directory [COMMMON_TOP]/clone/appl on the target system node
Before proceeding with the next steps you must shutdown your oracle applications services and the database on the source system
5. Copy the database tier file system
Log on to the source system as the database user
Copy the database (DBF) files from the source to the target system
Copy the source database ORACLE_HOME to the target system
After this you can now startup the database and applications services on your source system and release it for use.
6. Configure the target system database server
Log on to the target system as the database user and type the following commands to configure and start the database:
$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin
$ perl dbTier
This will prompt for new ORACLE_SID,ORACLE_HOME,Port Pool,JAVA_TOP and DATA_TOP give the appropriate values matching your target system
Once successful this should start your database and listener
7. Configure the application tier server nodes
The database and its listener should remain up before executing the next set of commands.
Log in to the merged APPL_TOP node as the applications user and execute the following commands:
$ cd [COMMON_TOP]/clone/bin
$ perl appsTier
This will prompt you new port pool for applictaion tier services as well as new APPLTOP,COMMON_TOP,ORACLE_HOME and IAS_TOP
Successful completion of this task will bring up your application tier services on the target or the cloned node.

Post-cloning Tasks

1- Change the directories – if any need to be changed
from all_directories;

- Edit the commands by replacing PROD with DEV ie source with target and Execute.
- You would also be required to check and recreate the DB links as per tns entries and requirements in the Target Instance.

2- Update apps.wf_systems (This should show the target Instance and not the source Instance)
SQL>select name,display_name from apps.wf_systems;
SQL>update apps.wf_systems set name=’’,display_name=’ .’;

3- Update Notification status (This needs to be bone before running adcfgclone on the apps tier , to avoid any Notifications to be sent from the Target Instance)

UPDATE wf_notifications SET status ='CLOSED', mail_status ='SENT', end_date ='01-JAN-01' WHERE mail_status in ('MAIL','INVALID','OPEN') ;

update wf_agents set address = replace ( address,'PROD','DEV' ) ;
select name ,display_name from wf_systems;
update wf_systems set

4- Update the target node in CM fnd_concurrent_queues table :
update fnd_concurrent_queues set node_name='' where node_name='';


5- remove the log files from APPLCSF/APPLLOG:

6- Make sure Dir are existing ; Use command to confirm
Delete all files under $APPLCSF/$APPLLOG

7- You can set the logfile to NULL
update fnd_concurrent_requests set logfile_name = null, logfile_node_name = null,
outfile_name = null, outfile_node_name = null;

8- You may need to run
to clear FND_NODES entries and rerun autoconfig (on all application tiers) as per step given below
For Details , Refer to : How-to-cleanup-FND_NODES-table-to-clear-corrupted-setup

9- Run Autoconfig

10- Change apps , system and sysadmin and a module specific passwords.

11- Log in to the target system application tier node as the APPLMGR user.
Run the following tasks in adadmin for all products:
- generate JAR files
- generate message files
- relink executables
- copy files to destination
- Remove the temporary directory [COMMON_TOP]/clone/appl to reduce disk space usage.

17 Bring up all services

18 Set the Site Name Profile Option

19 Set the Java Color profile option

20 Configure Workflow Mailer

Login to OAM. Click on
Notification Mailer => Edit Button => Advanced.
Make sure to type in the Target instance password.

PROCESS and DISCARD should exist on the Server for wf user.
The name of the Process and the Discard directory can be got from the workflow configuration

21 Check and Schedule the Concurrent Requests - these would include Gather Statistics , Purge old data and Workflow related Concurrent Requests.
You can submit these Conc. Requests using forms or use CONCSUB as described in Concsub-to-Quickly-submit-Concurrent-Requests


* Helpful commands:
tar cvf - fmsprod* | gzip < fmsprod.tar.gz
gunzip < /fmstest/fmsclone/
fmsprod.tar.gz | tar xvf -

tar cf - * | ssh bahix08 '(cd /fmstest/fmsclone/fmsclonedata; tar xf - )'

Some Clone issues which I got during my work
OUI-10197:Unable to create a new Oracle Home at /fmsclone/fmsclonedb. Oracle Home already exists at this location. Select another lo
SEVERE:OUI-10197:Unable to create a new Oracle Home at /fmsclone/fmsclonedb. Oracle Home already exists at this location. Select ano
ther location.
./runInstaller  -silent -detachHome ORACLE_HOME=

$  ./fmsclone/fmsclonedb/oui/bin/runInstaller  -silent -detachHome ORACLE_HOME=/fmstest/upgr/upgrdb

runInstaller  -silent -detachHome ORACLE_HOME=/fmstest/upgr/upgrdb
runInstaller  -silent -detachHome ORACLE_HOME=/fmstest/upgr/upgrora/iAS
runInstaller  -silent -detachHome ORACLE_HOME=/fmstest/upgr/upgrora/8.0.6

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /etc/oraInventory
'DetachHome' was successful.


Oracle Universal Installer has detected that there are processes running in the currently selected Oracle Home. The following processes need to be shutdown before continuing:
Oracle Universal Installer has detected that there are processes running in the currently selected Oracle Home. The following processes need to be shutdown before continuing:
This silent installation was unsuccessful.

Just shutdown the database , detach home again and re-ren cfgclone


RC-00118: Error occurred during creation of database
Raised by

java.lang.Exception: Control file creation failed
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(
        at java.lang.reflect.Constructor.newInstance(

Applsysd01.dbf not moved durnig the ftp :)

http 403
You don't have permission to access /OA_HTML/AppsLocalLogin.jsp on this server.

apache not considering the symbolic link

32.Temporarily enable iAS to use symbolic links.
Replace all occurrences of

'Option -FollowSymLinks'
for 'Option +FollowSymLinks' 

in  $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf 
and  $IAS_ORACLE_HOME/Apache/Apache/conf/httpd_pls.conf 
and bounce apache. If this is the issue, you should then go back and implement the changes via autoconfig. The value from this is controllable from your Apps Context, see s_options_symlinks .
"s_options_symlinks">Options +FollowSymLinks

rm -Rf $OA_HTML/_pages/*rm -Rf $COMMON_TOP/_pages/*
rm -Rf $IAS_ORACLE_HOME/Apache/modplsql/cache/*

JSP Error:
Request URI:/OA_HTML/AppsLocalLogin.jsp
java.lang.NoSuchMethodError: oracle/apps/fnd/sso/HttpLanguageMap.getOracleFromHttp(Ljava/lang/String;)Ljava/lang/String;

mod_security: Access denied with code 400. Pattern match "!^([-_@|#!=A-Za-z0-9/ :.$]){0,255}$" at ARGS_NAMES. [hostname ""] [uri "/oprocmgr-service"]


APP-FND-01926:The custom event WHEN-LOGON-CHANGED raised unhandled exception:
ORA-06508: PL/SQL: could not find program unit being called
The issue can be reproduced at will with the following steps:
1. Login to http://hostname.domain:port/OA_HTML/AppsLocalLogin.jsp
2. Navigate to any forms-based application
After applying new patches, the forms and pl/sql libraries were not regenerated.
  1. Tried copying source CUSTOM.pll from backup
  2. re-compiling existing $AU_TOP/resource/CUSTOM.pll
  3. Copy and re-compile CUSTOM.pll
None of the above three helped in my case.
Finally, I did the following
1. Use adadmin and generate JAR files with force option.
2. Use adadmin and run Generate Form Files and PL/SQL Library Files

Wednesday, 9 February 2011

Oracle Apps R12: Load Balancer Context Variables

s_webentryurlprotocol: Protocol that desktop clients use to communicate with the web entry point server.
Old value: http
New value: https

s_webentryhostName of the host that receives the first HTTP request from the desktop client.
Old value: appsrv1 , appsrv2
New value: LoadBalancerName

s_webentrydomain: Domain name of the host that receives the first HTTP request from the desktop client.
Old value:
New value:

s_active_webport: Port on the web server or load balancer that listens for HTTP requests
Old value: 8050
New value: 443

s_login_page: URL used to access the Applications log on pageserver.
Old value: on AppSrv1 on AppSrv2
New value:
s_external_url: URL that third party tools use to connect to the E-Business Suite System. This is used only by the Oracle Web Services product
Old value: on AppSrv1 on AppSrv2
New value:



Tuesday, 8 February 2011

Unix vi commands quick reference

General Notes:
1. Before practicing using this vi tutorial, type the following command followed by a   carriage return: :set showmode
2. vi is not VI. It is case sensitive!!! So make sure Caps Lock is OFF.
In order to work correctly the vi need correct terminal type (TERM) setting. The TERM setting depends on the type of terminal you have. Commonly used TERM types are vt100, vt220 and ANSI.  In most cases vt100 will work fine. In case vi is not able to understand the TERM you have given, it starts in open mode giving you a line by line display. Generally TERM is taken from .profile or /etc/profile, but can be set at the command line as:
$export TERM
echo $TERM will display the current TERM set.

Starting and Ending VI
Starting VI
vi filename Edits filename starting at line1
vi -r filename Recover filename that was being edited when system crashed
vi + n filename Edits filename and places cursor at line n
vi + filename Edits filename and places cursor on last line
vi +/string filename Edits filename and places cursor on first occurrence of string
vi filename file2 Edits filename, then edits file2 … After the save, use :n
Ending VI
ZZ or :wq or : x Saves and exits VI
:w Saves current file but doesn’t exit
:w! Saves current file overriding normal checks but doesn’t exit
:w file Saves current as file but doesn’t exit
:w! file Saves to file overriding normal checks but doesn’t exit
:n,mw file Saves lines n through m to file
:n,mw >>file Saves lines n through m to the end of file
:q Quits VI and may prompt if you need to save
:q! Quits VI and without saving
:e! Edits file discarding any unsaved changes (revert to previous saved version)
:e file Edits file (current file becomes alternate file)
:e# Edit alternate file
:we! Saves and continues to edit current file
% Display current filename
# Display alternate filename
:n Edit next file
:n! Edit next file (ignoring warnings)
:n files Specify new list of files
:r file Insert(read) file after cursor
:r !command Run command, and insert output after current line

Determining Line Numbers
:.= Returns line number of current line at bottom of screen
:= Returns the total number of lines at bottom of screen
^g Provides the current line number, along with the total number of lines,
in the file at the bottom of the screen
Vi has two modes: 1) Insertion mode and 2) command mode.The editor begins in command mode, where the cursor movement and text deletion and pasting occur. Insertion mode begins upon entering an insertion or change command. [ESC] returns the editor to command mode (where you can quit, for example by typing: q!).
Most commands execute as soon as you type them except for “colon” commands which execute when you press the return key.
Inserting, appending and replacing Text
Inserting and appending text
I Inserts text at the beginning of the current line
i Inserts text at the cursor
A Appends text at the end of current line
a Appends text after the cursor
Adding new line
O Open a new line above the current line
o Open a new line below the current line
Replacing a character & word
R Replace characters, starting with current cursor position, until hit
r Replace single character under cursor (no needed)
cw Replaces the word from cursor to the end indicated by $ sign
C Replaces till end of line
:r file Reads file and inserts it after current line
:nr file Reads file and inserts it after line n
Things to do while in Insert Mode:
CTRL-h or Backspace While inserting, deletes previous character
CTRL-w While inserting, deletes previous word
CTRL-x While inserting, deletes to start of inserted text
CTRL-i or TAB While inserting, inserts one shift width(tab)
CTRL-v char While inserting, ignores special meaning of char (e.g., for inserting characters like ESC and CTRL) until ESC is used
CTRL-v Take the next character literally. (i.e. To insert a Control-H, type Control-v Control-h)
h or or [left-arrow] Moves the cursor to the left
j or or [down-arrow] Moves down the cursor
k or [up-arrow] Moves up the cursor
l or or [right-arrow] Moves the cursor to the right
Arrow Keys These do work, but they may be too slow on big files. Also may have unpredictable results when arrow keys are not mapped correctly in client.
w Moves cursor to the beginning of the next word
W Moves the cursor to next blank delimited word
b Moves the cursor back to beginning of the preceding word
B Moves the cursor to the beginning of blank delimited word
^ Moves the cursor to the first non-blank character in the current line
+ or Enter Moves the cursor to the beginning of the next line
- Moves the cursor to the first non-blank character of the previous line
e Moves  cursor to the end of the current word
E Moves cursor to the end of Blank delimited word
( Moves cursor a sentence back
) Moves cursor a sentence forward
{ Moves cursor a paragraph back
} Moves cursor a paragraph forward
[[ Moves cursor a section back
]] Moves cursor a section forward
0 or | Moves cursor to the start of the current line
N| Moves cursor to the column n in the current line
$ Moves cursor to the end of the current line
1G or :0 Moves cursor to the start of the first line in the file
G or :$ Moves cursor to the start of the last line in the file
nG or :n Moves cursor to the start of the nth line in the file
F Moves cursor forward to c
Fc Moves cursor back to c
H Moves cursor to the first line on the screen, or “home”
nH Moves cursor to nth line from the top of the screen
M Move cursor to the middle line on the screen
L Move cursor to the last line on the screen
nL Moves cursor to nth line from the bottom of the screen
CTRL-d Moves down ½ screen
CTRL-f Moves forward one full screen
CTRL-u Moves up ½ screen
CTRL-b Moves backward one full screen
CTRL-e Moves screen up one line
CTRL-y Moves screen down one line
CTRL-l Clears and redraws the current screen
CTRL-r Redraws the screen, removing deleted lines
CTRL-T Moves cursor to next tab position
CTRL-W Moves back one word
Z z-carriage return makes the current line the top line on the page
Nz Makes the line n the top line on the page
z. Makes the current line the middle line on the page
Nz. Makes the line n the middle line on the page
z- Makes the current line the bottom line on the page
Nz- Makes the line n the bottom line on the page
% Move to associated ( ), { }, [ ]
Deleting Text
Almost all deletion commands are performed by typing d followed by a motion. For example, dw deletes a word. A few other deletes are:
x Delete character to the right of cursor
nx Deletes n characters starting with current; omitting n deletes current character only
X Delete character to the left of cursor
nX Deletes previous n characters; omitting n deletes previous character only
D Delete to the end of the line
D$ Deletes from the cursor to the end of the line
dd or :d or CTRL-U Delete current line
ndw Deletes the next n words starting with current
ndb Deletes the previous n words starting with current
ndd Deletes n lines beginning with the current line
:n,md Deletes lines n through m
dMotion_cmd Deletes everything included in the Motion Command (e.g., dG would delete from current position to the end of the file, and d4 would delete to the end of the fourth sentence).
“np Retrieves the last nth delete (last 9 deletes are kept in a buffer)
“1pu.u. Scrolls through the delete buffer until the desired delete is retrieved (repeat u.)
Yanking Text
Like deletion, almost all yank commands are performed by typing y followed by a motion. For example, y$ yanks to the end of the line. Two other yank commands are:
yy Yank the current line
:y Yank the current line
nyy or nY Places n lines in the buffer-copies
yMotion_cmd Copies everything from the curser to the Motion Command (e.g., yG would copy from current position to the end of the file, and y4 would copy to the end of the fourth sentence)
“(a-z)nyy or “(a-z)ndd Copies or cuts (deletes) n lines into a named buffer a through z; omitting n works on current line
Changing text
The change command is a deletion command that leaves the editor in insert mode. It is performed by typing c followed by a motion. For example cw changes a word. A few other change commands are:
C Change to the end of the line
cc or S Change the whole line until ESC is pressed
xp Switches character at cursor with following character
stext Substitutes text for the current character until ESC is used
cwtext Changes current word to text until ESC is used
Ctext Changes rest of the current line to text until ESC is used
cMotion_cmd Changes to text from current position to Motion Command until ESC is used
<< or >> Shifts the line left or right (respectively) by one shift width (a tab)
N<< or n>> Shifts n lines left or right (respectively) by one shift width (a tab)
<Motion_cmd or >Motion_cmd Use with Motion Command to shift multiple lines left or right
Putting text
p Put after the position or after the line
P Put before the position or before the line
“(a-z)p or “(a-z)P Pastes text from a named buffer a through z after or before the current line


Named buffers may be specified before any deletion, change, yank or put command. The general prefix has the form “c where c is any lowercase character. For example, “adw deletes a word into buffer a. It may thereafter be put back into text with an appropriate “ap.
Named markers may be set on any line in a file. Any lower case letter may be a marker name. Markers may also be used as limits for ranges.
mc Set marker c on this line
‘c Go to beginning of marker c line.
‘c Go to first non-blank character of marker c line.
Search for strings
/string Search forward for string
?string Search back for string
n Search for next instance of string
N Search for previous instance of string
% Searches to beginning of balancing ( ) [ ] or { }
fc Searches forward in current line to char
Fc Searches backward in current line to char
tc Searches forward in current line to character before char
Tchar Searches backward in current line to character before char
?str Finds in reverse for str
:set ic Ignores case when searching
:set noic Pays attention to case when searching
:n,ms/str1/str2/opt Searches from n to m for str1; replaces str1 to str2; using opt-opt can be g for global change, c to confirm change (y to acknowledge, to suppress), and p to print changed lines
& Repeats last :s command
:g/str/cmd Runs cmd on all lines that contain str
:g/str1/s/str2/str3/ Finds the line containing str1, replaces str2 with str3
:v/str/cmd Executes cmd on all lines that do not match str
, Repeats, in reverse direction, last / or ? search command
The search and replace function is accomplished with the :s command. It is commonly used in combination with ranges or the :g command (below).
:s/pattern/string/flags Replace pattern with string according to flags.
G Flag – Replace all occurrences of pattern
C Flag – Confirm replaces.
& Repeat last :s command
Regular Expressions
. (dot) Any single character except newline
* zero or more occurrences of any character
[...] Any single character specified in the set
[^...] Any single character not specified in the set
\< Matches beginning of word
\> Matches end of word
^ Anchor – beginning of the line
$ Anchor – end of line
\< Anchor – beginning of word
\> Anchor – end of word
\(…\) Grouping – usually used to group conditions
\n Contents of nth grouping
\ Escapes the meaning of the next character (e.g., \$ allows you to search for $)
\\ Escapes the \ character
[...] – Set Examples
[A-Z] The SET from Capital A to Capital Z
[a-z] The SET from lowercase a to lowercase z
[0-9] The SET from 0 to 9 (All numerals)
[./=+] The SET containing. (dot), / (slash), =, and +
[-A-F] The SET from Capital A to Capital F and the dash (dashes must be specified first)
[0-9 A-Z] The SET containing all capital letters and digits and a space
[A-Z][a-zA-Z] In the first position, the SET from Capital A to Capital Z
In the second character position, the SET containing all letters
[a-z]{m} Look for m occurrences of the SET from lowercase a to lowercase z
[a-z]{m,n} Look for at least m occurrences, but no more than n occurrences of the SET from lowercase a to lowercase z
Regular Expression Examples
/Hello/ Matches if the line contains the value Hello
/^TEST$/ Matches if the line contains TEST by itself
/^[a-zA-Z]/ Matches if the line starts with any letter
/^[a-z].*/ Matches if the first character of the line is a-z and there is at least one more of any character following it
/2134$/ Matches if line ends with 2134
/\(21|35\)/ Matches is the line contains 21 or 35
Note the use of ( ) with the pipe symbol to specify the ‘or’ condition
/[0-9]*/ Matches if there are zero or more numbers in the line
/^[^#]/ Matches if the first character is not a # in the line
1. Regular expressions are case sensitive
2. Regular expressions are to be used where pattern is specified
Nearly every command may be preceded by a number that specifies how many times it is to be performed. For example, 5dw will delete 5 words and 3fe will move the cursor forward to the 3rd occurrence of the letter e. Even insertions may be repeated conveniently with this method, say to insert the same line 100 times.
Ranges may precede most “colon” commands and cause them to be executed on a line or lines. For example :3,7d would delete lines 3-7. Ranges are commonly combined with the :s command to perform a replacement on several lines, as with :.,$s/pattern/string/g to make a replacement from the current line to the end of the file.
:n,m Range – Lines n-m
:. Range – Current line
:$ Range – Last line
:’c Range – Marker c
:% Range – All lines in file
:g/pattern/ Range – All lines that contain pattern
Shell Functions
:! Cmd Executes shell command cmd; you can add these special characters to indicate:% name of current file# name of last file edited
!! cmd Executes shell command cmd, places output in file starting at current line
:!! Executes last shell command
:r! cmd Reads and inserts output from cmd
:f Displays the name of the current file
:f file Renames current file to file
:w !cmd Sends currently edited file to cmd as standard input and execute cmd
:cd dir Changes current working directory to dir
:sh Starts a sub-shell (CTRL-d returns to editor)
:so file Reads and executes commands in file (file is a shell script)
!Motion_cmd Sends text from current position to Motion Command cmd
!}sort Sorts from current position to end of paragraph and replaces text with sorted text
:w file Write to file
:r file Read file in after line
:n Go to next file
:p Go to previous file
:e file Edit file
!!program Replace line with output from program
VI Settings
Note: Options given are default. To change them, enter type :set option to turn them on or :set nooptioni to turn them off. To make them execute every time you open VI, create a file in your HOME directory called .exrc and type the options without the colon (:) preceding the option
:set ai Turns on auto indentation
:set all Prints all options to the screen
:set ap Prints line after d c J m :s t u commands
:set bf Discards control characters from input
:set dir=tmp Sets tmp to directory or buffer file
:set eb Precedes error messages with a bell
:set ic Ignores case when searching
:set lisp Modifies brackets for Lisp compatibility.
:set list Shows tabs (^l) and end of line ($)
:set magic Allows pattern matching with special characters
:set mesg Allows others to send messages
:set nooption Turns off option
:set nu Shows line numbers
:set opt Speeds output; eliminates automatic RETURN
:set prompt Prompts for command input with :
:set re Simulates smart terminal on dumb terminal
:set report Indicates largest size of changes reported on status line
:set ro Changes file type to “read only”
:set scroll=n set n lines for CTRL-d and z
:set sh=shell_path set shell escape (default is /bin/sh) to shell_path
:set showmode Indicates input or replace mode at bottom
:set sw=n Sets shift width to n characters
:set term Prints terminal type
:set terse Shorten messages with terse
:set timeout Eliminates one-second time limit for macros
:set tl=n Sets significance of tags beyond n characters (0 means all)
:set ts=n Sets tab stops to n for text input
:set wa Inhibits normal checks before write commands
:set warn Warns “no write since last change”
:set window=n Sets number of lines in a text window to n
:set wm=n Sets automatic wraparound n spaces from right margin.
Key Mapping
NOTE: Map allows you to define strings of VI commands. If you create a file called “.exrc” in your home directory, any map or set command you place inside this file will be executed every time you run VI. To imbed control characters like ESC in the macro, you need to precede them with CTRL-v. If you need to include quotes (“), precede them with a \ (backslash). Unused keys in vi are: K V g q v * = and the function keys.
Example (The actual VI commands are in blue):map v /I CTRL-v ESC dwiYou CTRL-v ESC ESC
Description: When v is pressed, search for “I” (/I ESC), delete word (dw), and insert “You” (iYou ESC). CTRL-v allows ESC to be inserted
:map key cmd_seq Defines key to run cmd_seq when pressed
:map Displays all created macros on status line
:unmap key Removes macro definition for key
:ab str string When str is input, replaces it with string
:ab Displays all abbreviations
:una str Unabbreviate str
~ Toggle upper and lower case for the character at the current position
J Joins the line immediately below the current line with the current line
nJ Joins the next n lines together; omitting n joins the beginning of the next line to the end of the current line
. Repeat last text-changing command
U Undo the effect of last command (Note: u in combination with . can allow multiple levels of undo in some versions)
U Undo all changes to the current line
; Repeats last f F t or T search command

Automatic Storage Managment

Automatic Storage Management (ASM) is an oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems or files which can be made to look like disks as long as the device is raw. ASM uses its own database instance to manage the disks, it has its own processes and pfile or spfile, it uses ASM disk groups to manage disks as one logical unit.
The benefits of ASM are
  • Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
  • Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
  • Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
  • Uses redundancy features available in intelligent storage arrays
  • The storage system can store all types of database files
  • Using disk group makes configuration easier, as files are placed into disk groups
  • ASM provides stripping and mirroring (fine and coarse gain - see below)
  • ASM and non-ASM oracle files can coexist
  • ASM is free!!!!!!!!!!!!! 
ASM main components:
1- ASM Instance: Is a special instance that does not have any data files, there is only ASM instance one per server which manages all ASM files for each database. The instance looks after the disk groups and allows access to the ASM files. Databases access the files directly but uses the ASM instance to locate them. If the ASM instance is shutdown then the database will either be automatically shutdown or crash.
2- ASM Disk groups: Disks are grouped together via disk groups, these are very much like logical volumes.
3- ASM Files: Files are stored in the disk groups and benefit from the disk group features i.e. stripping and mirroring.

File types that can be managed by ASM:
Data files, Control files, Redo log files, SPFILE , RMAN backup files, Archived redo log files and FRA files.

ASM Processes in ASM instance:
1- RBAL( Re-BALancer): Coordinates the re-balancing activity when a new disk added or removed.
2- ARBn(Actual Rebalancer): Do the actual data movement requested by RBAL. n 1..9.

ASM Processes in Database instance:
1- RBAL( Re-BALancer): Open and Close ASM disks.
2- ASMB: Connect to the ASM channel and do communication between ASM and RDBMS instances. The requests through this could be file creation, deletion , resizing and various statistics and status messages.

ASM registers its name and disks with the RDBMS via the cluster synchronization service (CSS).
This is why CSS needed even not on clustered environment.

Validate oracle apps R12 application login throuhg sqlplus

Use the following select:


select fnd_web_sec.validate_login('sysadmin','oracle123') from dual;

Get database object creation script (DDL) from sqlplus

First method using DBMS_METADATA database package:


select DBMS_METADATA.GET_DDL('TABLE', 'EMP') from dual;
  Second method using database view called USER_SOURCE:
Note: This method just used for the code objects like procedure, package and so on.
select text from USER_SOURCE where name=OBJECT_NAME order by line asc;


select text from USER_SOURCE where upper(name)='AP_CHECKS_ALL_ADP' order by line asc;

Oracle Apps 11i/R12: Check locked tables by fnd_user

SELECT c.owner
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vp.spid AS os_process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
      ,'XX') != 'KILLED';

Monday, 7 February 2011

Enable Archive Log Mode in Oracle RAC 10g Database

The following will be the steps:
1- Login to one of the clustered database nodes and change parameter cluster_database to false:
   sqlplus "/ as sysdba"
   alter system set cluster_database=false scope=spfile sid='racdb1'
2- Stop all database instances:
    srvctl stop database -d racdb

3- On any node startup one instance in mount state and enable archivelog mode:

   sqlplus "/ as sysdba"
   startup mount;
   alter database archivelog;

4- Shutdown the previous running node:
   sqlplus "/ as sysdba"
   shutdown immediate;

5- Start all instance back:

     srvctl start database -d racdb

To check the mode:

   sqlplus "/ as sysdba"
   archive log list;


 select log_mode from v$database;

Friday, 4 February 2011

Oracle RAC: Find the Master node at cluster level

ocrconfig -showbackup: (Note the first column which represents the Node name)
$ ocrconfig -showbackup
gfmisdb1     2011/02/21 12:44:43     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/21 08:44:43     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/21 04:44:43     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/20 12:44:42     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/09 00:44:33     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS

Thursday, 3 February 2011

Script to monitor RMAN progress

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'

Terminate rman hang process at Unix OS leve

To do this you have to get the process id first:
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR;
---- ---------- ---------- -------------------- -------------
8642 sbtwrite2         600 WAITING              rman channel=ORA_SBT_TAPE_1
8374 sbtwrite2         600 WAITING              rman channel=ORA_SBT_TAPE_2
Now we will kill the processes by:
kill -9 8642 8374

Tuesday, 1 February 2011

Convert Single instance database to RAC Database methods 10g

Three methods used to convert Single database to RAC database:
1- DBCA.
2- Enterprise Manager.
3- rconfig tool.

Oracle Apps R12: Check if the patch applied from sqlplus

Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system:
SELECT   bug_number
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||3594604 || '%';

This table includes patches applied on the system:
SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||3594604 || '%'

Number of Visitors