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

inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba

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
df -h $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 p.zip

9) Go the patch directory
cd /loc_2_unzip/patch_number

10) Bring down the listner.
cd $ORACLE_HOME/bin
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
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log

c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup

If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
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



user_inv.sql
============
SELECT owner,
COUNT(*)
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.


post_patch.sql
--------------
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

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

ORACLE_SID=prod

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 (10.2.0.1.0)

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 – http://avdeo.com/oracle-real-application-cluster-10g/

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/vote.crs

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

Again for this step, you can refer to my previous post – http://avdeo.com/oracle-real-application-cluster-10g/ 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
*.undo_management=AUTO
prod1.undo_tablespace=UNDOTBS1
prod1.instance_name=prod1
prod1.instance_number=1
prod1.thread=1
prod1.local_listener=listener_ocvmrh2103
prod2.instance_name=prod2
prod2.instance_number=2
prod2.local_listener=listener_ocvmrh2190
prod2.thread=2
prod2.undo_tablespace=UNDOTBS2

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;

NAME
---------
PROD

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

File created.

SQL>

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
/u01/app/oracle/product/10.2.0/db/dbs
bash-3.00$ cat initprod1.ora
spfile='/u01/app/oracle/product/10.2.0/db/dbs/spfileprod.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
/u01/app/oracle/product/10.2.0/db
bash-3.00$ echo $ORACLE_SID
prod1
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.
SQL>

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.

SQL> ALTER DATABASE OPEN;

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
/u01/app/oracle/product/10.2.0/db/dbs
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
spfile='/u01/app/oracle/product/10.2.0/db/dbs/spfileprod.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

audit_file_dest
background_dump_dest
user_dump_dest
core_dump_dest

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

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 11.5.10.2 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 adpreclone.pl 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 adpreclone.pl 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 adpreclone.pl 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:
– [APPL_TOP]
– [OA_HTML]
– [OA_JAVA]
– [COMMON_TOP/util]
– [COMMON_TOP/clone>
– [806 ORACLE_HOME]
– [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 adcfgclone.pl 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 adcfgclone.pl 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

THE CHECKS/STEPS LISTED HERE SHOULD BE PERFORMED AFTER DATABASE CLONE BEFORE APPLICATIONS CLONE
1- Change the directories – if any need to be changed
select 'CREATE OR REPLACE DIRECTORY '||DIRECTORY_NAME||' AS '||''''||DIRECTORY_PATH||''''||';'
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=’ .’;
SQL>COMMIT;


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') ;
Commit;

update wf_agents set address = replace ( address,'PROD','DEV' ) ;
select name ,display_name from wf_systems;
update wf_systems set
DISPLAY_NAME = replace ( DISPLAY_NAME, 'PROD.DBALOUNGE.COM','DEV.DBALOUNGE.COM');
Commit;


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

THE CHECKS/STEPS LISTED HERE SHOULD BE PERFORMED AFTER APPLICATIONS CLONE BUT with APPS SERVICES DOWN


5- remove the log files from APPLCSF/APPLLOG:

6- Make sure Dir are existing ; Use command to confirm
cd $APPLCSF/$APPLLOG
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;
commit;

8- You may need to run
exec FND_CONC_CLONE.SETUP_CLEAN;
commit;

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
Changing-Look-and-feel-of-Oracle-Application&highlight=java+color

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

Purge-concurrent-request-and-or-manager-data
Purge-Obsolete-Workflow-Runtime-Data-Concurrent-Program
Purge-Obsolete-Workflow-Runtime-Data-Concurrent-Program



* 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
cation.
SEVERE:OUI-10197:Unable to create a new Oracle Home at /fmsclone/fmsclonedb. Oracle Home already exists at this location. Select ano
ther location.
ERRORCODE = 255 ERRORCODE_END
./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.
$


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

silentInstall2011-10-21_09-43-41PM.log
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
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
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 oracle.apps.ad.clone.ApplyDatabase


StackTrace:
java.lang.Exception: Control file creation failed
        at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:734)
        at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:376)
        at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:347)
        at oracle.apps.ad.clone.ApplyDBTier.(ApplyDBTier.java:165)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:80)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:44)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:315)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:68)
        at java.lang.Thread.run(Thread.java:568)


Applsysd01.dbf not moved durnig the ftp :)
===================================================================================

http 403
Forbidden
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/*
 
466739.1
JSP Error:
Request URI:/OA_HTML/AppsLocalLogin.jsp
Exception:
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 "bahix09.aemeweb.com"] [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

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_webentryhost: Name 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: oracle.com
New value: oracle.com

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:
http://appsrv1.company.com:8050/OA_HTML/AppsLogin on AppSrv1
http://appsrv2.company.com:8050/OA_HTML/AppsLogin on AppSrv2
New value: https://LoadBalancerName.company.com/OA_HTML/AppsLogin
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:
http://appsrv1.company.com:8050/ on AppSrv1
http://appsrv2.company.com:8050/ on AppSrv2
New value: https://LoadBalancerName.company.com

sslterminator:


#

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.
Requirements:
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:
$TERM=vt100
$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

Modes

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)

Motion

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

Buffers

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.

Markers

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

Replace

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
Notes: 1. Regular expressions are case sensitive 2. Regular expressions are to be used where pattern is specified

Counts

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

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

Files

: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

Other

~	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:

fnd_web_sec.validate_login(USERNAME,PASSWORD)

Example:
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(OBJECT_TYPE, OBJECT_NAME) from dual;

Example:

select DBMS_METADATA.GET_DDL('TABLE', 'EMP') from dual;
select DBMS_METADATA.GET_DDL('PROCEDURE', 'AP_CHECKS_ALL_ADP') 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;

Example:

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
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.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 vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
      ,'XX') != 'KILLED';

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;

or

 select log_mode from v$database;

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

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:
SELECT p.SPID ,CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR;

SPID EVENT        SEC_WAIT STATE                CLIENT_INFO
---- ---------- ---------- -------------------- -------------
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

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 || '%'