Oracle Apps & Core DBA Solutions

Query to monitor Alert Log report of last n days.

2014-12-26T10:36:00.000+05:30

SET linesize 500 pagesize 500
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
col ORIGINATING_TIMESTAMP for a20 head Date

SELECT to_char(originating_timestamp,'DD-MM-RR HH24:MI:SS') "Time", message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - &Days
AND
regexp_like(message_text, '(TNS-|ORA-|error)');

Enjoy :)
Gaurav Sahi

How to access Oracle Apps R12/11i on Linux Machine (Mozilla Firefox)

2014-05-27T10:59:00.000+05:30

How to access Oracle Apps 11i/R12 forms from Linux VM or Linux machine?

In you to access forms from Linux client, Three steps are installed (You can skip first if Mozilla is installed on your Linux client) –

1. Install Mozilla Browser

2. Install JRE on client machine (JRE version to install will depend on Server configuration,

Identify which version of JRE you need on client machine

– For 11i/R12 : Login to server as application o.s. user (applmgr), set environment variable and run grep plugin $CONTEXT_FILE (If you get output like 1.5.0_13 you need JRE 1.5.0 patchset 13, if its 1.5.0_10 you need 1.5.0 patchset 10; or for 1.4.2_04 you need 1.4.2 patchset 4 )

3. Use JRE installed above on client machine in Browser (Mozilla) Plugin

1. Install FireFox Mozilla

– Download Mozilla for Linux from http://www.mozilla.com/en-US/firefox/all.html#en firefox-2.0.0.7.tar.gz

– “gunzip -d firefox-2.0.0.7.tar.gz” (You will get unzipped file with name as firefox-2.0.0.7.tar)

– Extract tar file as “tar -xvf firefox-2.0.0.7.tar”

– This will create directory called “firefox” which is under you will see executable called “firefox“; Use this executable to start FireFox on Linux machine.

2. Install JRE on client machine

–Download JRE 5.0 Update 10 (change this as per your server plugin version shown above; I am on 12.0.0 hence 1_5.0.10) for Linux from http://java.sun.com/products/archive/j2se/5.0_10/index.html ”jre-1_5_0_10-linux-i586-rpm.bin”

– ”chmod 755 jre-1_5_0_10-linux-i586-rpm.bin”

– ./jre-1_5_0_10-linux-i586-rpm.bin (You will be asked to accept license agreement , click enter and when prompted to accept license enter Yes)

– This will install jre 1.5.10 in linux machine under /usr/java and plugin is installed under /usr/java/jre1.5.0_10/plugin/i386/ns7/libjavaplugin_oji.so

3. Configure JRE Plug-in in browser

– Go to Mozilla FireFox plug-in directory which is $location_where_you_unziped_firefox_directory/plugins and create soft link like

– ln -s /usr/java/jre1.5.0_10/plugin/i386/ns7/libjavaplugin_oji.so ./libjavaplugin_oji.so

Oracle Applications (11.5.10.2) Installation on Red Hat Enterprise Linux

2014-04-10T08:41:00.000+05:30

Before start installation:

1. The installation of E-Business Suite R11 needs minimum of 2G swap, secure Linux disabled and the following package groups installed:

X Window System
GNOME Desktop Environment
Editors
Graphical Internet
Server Configuration Tools
Development Tools
Administration Tools
System Tools

I strongly suggest , choose “install everything” option while you are making Redhat Enterprise Linux installation. Its little hard to try install-add some rpms and other tools on Redhat Enterprise Linux.In this article, i assume that you have been installed everything.

2.The installation size of Oracle E-Business Suite R11 is:

57 GB for a fresh install with a production database:

Application tier file system – 26 GB
Database tier file system (fresh install with a production database) – 31 GB

91 GB for a fresh install with a Vision Demo database.

Application tier file system – 26 GB
Database tier file system (fresh install with a production database) – 65 GB

3. Create stage area folder:

You need to setups file for can make Oracle E-Business Suite R11 installation. You can use edelivery.oracle.com site for can download Oracle E-Business Suite the lastest version.

PS: It’s not avaliable to download Oracle E-Business Suite R11 setup file from edelivery.oracle.com for a now. If you need setups file please log a SR(service request) or please contact with your local office)

Oracle E-Business Suite R11 setup files almost have 31 GB size.

You can create stage folder by following:

Oracle E-Business Suite R11 installation manual – Page 1-14 Setting Up the Stage Area

Mainly;

Put all the files under one directory (i.e. /data/R11stage), and extract all the files under the same directory and give it 777 permission For instance:

[root@test]# mkdir /data/R11Stage

[root@test]# chown –R 777 /data/R11Stage

[root@test]# cd /data/R11Stage

For Start Here DVD create below:

mkdir startCD

For APPL_TOP DVD create below:

mkdir oraApps

For RDBMS DVD create below:

mkdir oraDB

For Tools DVD create below:

mkdir oraiAS

For Databases DVD create below:

mkdir oraAppDB

Before start to installation, i strongly suggest check below:

MD5 Checksums for 11i10.2 Rapid Install Media [ID 316843.1] note for can observe you have any problem wiht your stage or not(Corrupted zip etc)

Ensure that /etc/hosts and /etc/oraInst.loc are set right:

[oracle@erp ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
192.168.1.50    erp.freeoraclehelp.com erp
[oracle@erp ~]$ cat /etc/oraInst.loc 
inventory_loc=/oracle/erp/R12/oraInventory
inst_group=oinstall
[oracle@erp ~]$

Create OS User:

# groupadd oinstall -g 2000
# useradd -g oinstall -u 2000 oracle
# chown -R oracle:oinstall /oracle

Set Kernel Parameters:

[root@erp ~]#  tail /etc/sysctl.conf
# Oracle Recommendations for Apps  #
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 256 32000 100 142
fs.file-max = 131072
net.ipv4.ip_local_port_range = 10000 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65535
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 262144
# Oracle Recommendations for Apps  #

[root@erp ~]# sysctl -p

Increase user limits: Add the following to /etc/security/limits.conf

  * hard nofile 65535
  * soft nofile 4096
  * hard nproc 16384
  * soft nproc 2047

Add the following to /etc/profile

if [ $USER = "oracle" ]; then 
        if [ $SHELL = "/bin/ksh" ]; then 
              ulimit -p 16384 
              ulimit -n 65536 
        else 
              ulimit -u 16384 -n 65536 
        fi 
fi

Ensure the following RPMs are installed: compat-db-4.1.25-9
compat-gcc-32-3.2.3-47.3
compat-gcc-32-c++-3.2.3-47.3
compat-libgcc-296-2.96-132.7.2
compat-libstdc++-296-2.96-132.7.2
compat-libstdc++-33-3.2.3-47.3
xorg-x11-deprecated-libs-devel-6.8.1-23.EL
xorg-x11-deprecated-libs-6.8.1-23.EL
openmotif-2.1.30-x

[root@erp RPM_OEL4]# ls -ltr
total 8
-rwxr-xr-x  1 root root 5814 Feb  5  2011 p4198954_40_LINUX.zip
[root@erp RPM_OEL4]# pwd
/dumps/ERP/11i/RPM_OEL4
[root@erp RPM_OEL4]# unzip p4198954_40_LINUX.zip 
Archive:  p4198954_40_LINUX.zip
  inflating: compat-libcwait-2.1-1.i386.rpm  
  inflating: compat-oracle-rhel4-1.0-5.i386.rpm  
  inflating: README.txt              
[root@erp RPM_OEL4]# rpm -ivh compat-*
Preparing...                ########################################### [100%]
   1:compat-oracle-rhel4    ########################################### [ 50%]
   2:compat-libcwait        ########################################### [100%]
[root@erp RPM_OEL4]#

Installation

Post-Install Checks

Once installation is complete, verify the logins at http://erp.freeoraclehelp.com:8010. Here are the default passwords.
Database:
SYS/change_on_install
SYSTEM/manager
Application Schema passwords:
APPS Schema: APPS/APPS
GWYUID User: APPLSYSPUB/PUB
Guest User: GUEST/ORACLE
Application Users:
SYSADMIN/SYSADMIN
MFG/WELCOME
OPERATIONS/WELCOME
SERVICES/WELCOME
MRC/WELCOME
HRMS/WELCOME

Admin scripts

RDBMS ORACLE_HOME Control scripts
(located under <RDBMS ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>)

Control Script	Functionality
addbctl.sh	Control database server
addlnctl.sh	Control Oracle Net listener for the database server

RDBMS ORACLE_HOME Install scripts
(located under <RDBMS ORACLE_HOME>/appsutil/install/<CONTEXT_NAME>)

Install Script	Functionality
adsvdlsn.sh	Start Oracle Net listener during installation
adcrdb.sh	Start database and create database control files
addbprf.sh	Set profile option values
adsvdcnv.sh	Perform character set conversion and licensing tasks
adsvdb.sh	Start database during installation

COMMON_TOP Control scripts
(located under <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>)

Control Script	Functionality	Node
adalnctl.sh	Control Oracle Net8 listener for Applications services	All application tier server nodes
adstrtal.sh	Start all Applications server processes	All application tier server nodes
adstpall.sh	Stop all Applications server processes	All application tier server nodes
adfrmctl.sh	Control Forms server	Forms server node
adfmcctl.sh	Control Forms Metrics Client	Forms server node
adfmsctl.sh	Control Forms Metrics Server	HTTP server node
adtcfctl.sh	Control TCF SocketServer	Concurrent processing server node
adcmctl.sh	Control Concurrent managers	Concurrent processing server node
adrepctl.sh	Control Reports server	Concurrent processing server node
adapcctl.sh	Control Apache processes	HTTP server node
jtffmctl.sh	Control Oracle fulfillment server	HTTP server node

COMMON_TOP Install scripts
(located under <COMMON_TOP>/admin/install/<CONTEXT_NAME>)

Install Script	Functionality	Node
adsvalsn.sh	Start Net8 listener processes for Applications services	All application tier server nodes
adsvfrm.sh	Start Forms server during install	Forms server node
adsvfmc.sh	Start Forms server during install	Forms server node
adsvfms.sh	Start Forms metric server during install	HTTP server node
adsvcm.sh	Start Concurrent manager during install	Concurrent processing server node
adsvtcf.sh	Start TCF server during install	Concurrent processing server node
adsvrep.sh	Start Reports server during install	Concurrent processing server node
adsvapc.sh	Start Apache server during install	HTTP server node
jtfsvfm.sh	Start Fulfillment server during install	HTTP server node

Retrieve Apps password in R12 or Forgot Apps Password in R12

2013-10-15T11:54:00.003+05:30

Steps to retrieve forgotten apps password in R12

Step 1 : login to DB-Node

sqlplus / as sysdba

STEP 2 : Create Function for to decrypt the encrypted password

SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/

Function created.

STEP 3 : (Query for password)

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

Output

ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A

STEP 4:

SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A') from dual;

Output

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A')
--------------------------------------------------------------------------------
DEMONSTRATE

STEP 5: (Test apps password)

SQL> conn apps/DEMONSTRATE;
Connected.

CHEERS :)

Query to find all current Oracle Application user logged in R12 & 11i

2013-07-29T15:00:00.000+05:30

There are 2 Queries:-

1) SELECT DISTINCT icx.session_id,
icx.user_id,
fu.user_name,
fu.description
FROM icx_sessions icx, fnd_user fu
WHERE disabled_flag != 'Y'
AND icx.pseudo_flag = 'N'
AND (last_connect +
DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
NULL, limit_time,
0 , limit_time,
fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
AND icx.counter < limit_connects
AND icx.user_id = fu.user_id;

2) SELECT DISTINCT ic.disabled_flag,
fu.user_name User_Name,
fr.RESPONSIBILITY_KEY Responsibility,
fu.user_id,
fu.description,
fu.employee_id,
ic.responsibility_application_id,
ic.responsibility_id,
ic.org_id,
ic.function_type,
ic.counter,
ic.first_connect,
ic.last_connect,
ic.nls_territory,
ic.time_out,
fr.menu_id,
fr.responsibility_key
FROM fnd_user fu,
fnd_responsibility fr,
icx_sessions ic
WHERE fu.user_id = ic.user_id
AND fr.responsibility_id = ic.responsibility_id
AND ic.disabled_flag ='N'
and IC.RESPONSIBILITY_ID is not null
AND ic.last_connect > sysdate - (ic.time_out/60)/96;

How to clean FND_NODES table in Oracle Applications

2013-06-10T09:03:00.000+05:30

Step 1: Always apply the latest cloning patches to avoid all the bugs and fixes

Step 2: SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
It will delete all old data from the table.

Step 3: Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers to repopulate the required system tables.

How to migrate responsibility / menu / form (personalizations) from one ERP apps (11i / R12) instance to another using FNDLOAD?

2013-01-17T23:49:00.003+05:30

Below are the commands

===========================
RESPONSIBILITY
===========================

For migrating a responsibility we need the responsibility key value.

Steps to get the RESPONSIBILITY KEY VALUE

Login as Sysadmin
System Administrator -> Security : Responsibility -> Define
Press F11 -> Enter the Responsibility Name -> Get the responsibility key value from the Responsibility Key Text Field.

To Download
FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct [give some name].ldt FND_RESPONSIBILITY RESP_KEY=”[RESPONSIBILITY_KEY_VALUE]“

To Upload
FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct [name given above].ldt

===========================
MENU
===========================

To Download
FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct [give some name].ldt MENU MENU_NAME=”[MENU_NAME]“

To Upload
FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct [name given above].ldt

=======================
FORMS PERSONALIZATIONS
=======================

For migrating the forms personalizations we need the function name that is associated with the form.
Steps to get the form function name (eg. Users Form)
Login as Sysadmin
System Administrator -> Security : User -> Define
Once the form opens up
On the Tool Bar Click Help -> Diagnostics -> Custom Code -> Personalize
This will open a new form with function name. This is the value we require.
You can perform simillar step for any form that you want to migrate by opening the form to be migrated.

To Download
FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct [give some name].ldt FND_FORM_CUSTOM_RULES function_name=”[FUNCTION_NAME]“

To Upload
FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct [give some name].ldt

WAS error while installing UPK Professional Release 11.1

2012-10-03T15:41:00.002+05:30

Hello Everybody,

While starting the User Productivity Kit Professional Release 11.1 installtion on Windows 2003 R2 Server with SP2 the most common error comes is:-

To resolve the issue we nned to install IIS on Windows 2003. Following are the steps:-

Click Start, point to Control Panel, and then click Add or Remove Programs.
In Add or Remove Programs, click Add/Remove Windows Components.
In the Windows Components Wizard, under Components, select Application Server.
After the wizard completes the installation, click Finish.
Click Next.

NOTE:- For thus you need Windows Server 2003 CD

After that you can continue with the UPK Server installation.

R12 installation 64 bit on OEL 5 update 5(From Scratch to END)

2012-09-17T14:39:00.000+05:30

1. Download R12 64 bit software from

https://edelivery.oracle.com (create your account if you using this site first time).
Select product E-business suite on 64 Bit.

Oracle E-Business Suite Release 12.1.1 Media Pack for Linux x86-64-bit ; download first 39 cd. and unzip them on the host where you want to perform the installation.

2. Set Up Stage Area Directory

$ cd /u01
$ mkdir StageR12

3. Unzip the Files

$ cd /u01/download

$ unzip -d /u01/StageR12 <ZipFile>

Prerequisites before Starting Installation:-

a. Make sure these package has been installed manually:

openmotif21-2.1.30-11.EL5.i386
xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386
These two rpm can be download from here:
http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/
For Update 1 or 2:
binutils-2.17.50.0.6-6.0.1.x86_642
For Update 3:
binutils-2.17.50.0.6-9.0.1.x86_642
For Update 4 (5.4) or higher, see workaround listed below in the 'After Installing or Upgrading' section.
The following packages must be installed from the Oracle Linux 5 or RHEL 5 distribution media:
gcc-4.1.2-14.el5.x86_64
gcc-c++-4.1.2-14.el5.x86_64
glibc-2.5-18.i686 (32-bit)
glibc-2.5-18.x86_64
glibc-common-2.5-18.x86_64
glibc-devel-2.5-18.i386 (32-bit)
glibc-devel-2.5-18.x86_64
libgcc-4.1.2-14.el5.i386
libgcc-4.1.2-14.el5.x86_64
libstdc++-devel-4.1.2-14.el5.i386
libstdc++-devel-4.1.2-14.el5.x86_64
libstdc++-4.1.2-14.el5.i386
libstdc++-4.1.2-14.el5.x86_64
make-3.81-1.1.x86_64
gdbm-1.8.0-26.2.1.i386
gdbm-1.8.0-26.2.1.x86_64
libXp-1.0.0-8.1.el5.i386
libXp-1.0.0-8.1.el5.x86_64
libaio-0.3.106-3.2.i386
libaio-0.3.106-3.2.x86_64
libgomp-4.1.2-14.el5.x86_64
sysstat-7.0.0-3.el5.x86_64
util-linux-2.13-0.45.el5.x86_64
compat-libstdc++-296-2.96-138.i386
compat-libstdc++-33-3.2.3-61.i386

b. Kernel Settings
Edit the /etc/sysctl.conf
Parameter Value
kernel.semmsl 256*
kernel.semmns 32000*
kernel.semopm 100*
kernel.semmni 142*
kernel.shmall 2097152
kernel.shmmax Half the size of the physical memory (in bytes), and at least 2147483648
kernel.shmmni 4096
kernel.msgmax 8192
kernel.msgmnb 65535
kernel.msgmni 2878
fs.file-max 65536
net.ipv4.ip_local_port_range 10000 65000**
net.core.rmem_default
262144
net.core.rmem_max 262144
net.core.wmem_default 262144
net.core.wmem_max 262144

c. Verify that the /etc/hosts
There should be information of hostname with IP address.

d. Modifying the Number of Open File Descriptors
Edit /etc/security/limits.conf
* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047

e. OS Library Patch for Oracle HTTP Server (on Oracle Linux 5 and RHEL 5 only)
Download and apply the patch 6078836 from My Oracle Support to fix an issue with the Oracle HTTP Server (missing libdb.so.2) bundled

Create User & Group:-

a) Create group dba and user oracle & .

# groupadd dba
#useradd -g dba oracle

b) Create directory

# mkdir -p /d01/oracle
# chown -R oracle:dba /d01/oracle
# chmod -R 775 /d01/oracle

INSTALLATION PROCESS:-

a. Login as applmgr and change directory to /stage/StageR12/StartCD/Disk1/rapidwiz
b. Run Rapidwiz ...Type './rapidwiz' and press Enter
c. A screen appears which takes you through the installation steps .....Click Next on first 3 screens.
d. Enter the CSI number, Metalink account and country and click Next else Ignore that.
e. Choose create new configuration and Click Next
f. Choose a port pool and Click Next
g. Choose Database type Vision/fresh, Specify a SID,verify host and domain.....
Give the username as applmgr and group as dba and specify the directory as /oraDB and Click Next
h. Select Suite Licensing and Click Next
i. Click Next on the Next 2 screens to accept license products and country specific functionalities.
j. Choose Database character set as UTF8 and click Next
k. Verify the host,domain and specify the base directory as /oraAPP and instance directory as /oraAPP/inst and Click Next
l. Click Next ...The rapidwiz verifies and validates the system configuration and notifies if there are any errors...All the checks have to return a GREEN
tick mark if passed. Correct the errors if there are any RED cross marks and restart the rapidwiz.
m. Click Next and then Yes on the next screen to start the installation .
n. Once the installation is finished it performs post installation checks and notifies if there are any errors. It should return all GREEN marks if successfully installed
o. Click on Finish and that Ends the Installation Steps

Common Error after Installation:-

a) rw-50015 error - HTTP is not responding

ln -s /usr/lib/libgdbm.so /usr/lib/libdb.so.2

but in my case it just created a broken link.

I had to use this instead:

ln -s /usr/lib/libgdbm.so.2 /usr/lib/libdb.so.2

Restart the Application and database server.

Start rapid installation again with $rapidwiz -restart

How to Create Readonly User (for specific Schema) in Oracle Database

2012-08-28T21:45:00.000+05:30

1) create user bolinf_readonly identified by readonly;

2) select username from dba_users
    where username like '%BOLINF%';

3) grant create session to BOLINF_READONLY

4) grant select any table to BOLINF_READONLY

5) create or replace trigger log_on_after_bolinf
   after logon ON BOLINF_READONLY.SCHEMA
BEGIN
    EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = BOLINF';
    END;

Enjoy. . . . .
:)

Login page error: 404 - The url /OA_HTML/AppsLogin not found

2012-07-19T09:24:00.003+05:30

This blog speaks about the Login Page Issue on R12.1.1 instance.

Suddenly the login page was throwing an error - 404 /OA_HTML/AppsLogin was not found on the server

The access_log shows no errors. error.log from $LOG_HOME/ora/10.1.3/Apache shows

[Tue Jan 18 11:27:51 2011] [error] [client 123.238.255.152] [ecid: 1295330271:127.0.0.1:10897:0:2,0] File does not exist: /oracle/CLON/inst/apps/CLON_obaserver/portal/OA_HTML/AppsLogin
[Tue Jan 18 11:27:54 2011] [error] [client 123.238.255.152] [ecid: 1295330274:127.0.0.1:10897:0:3,0] File does not exist: /oracle/CLON/inst/apps/CLON_obaserver/portal/OA_HTML/AppsLogin
[Tue Jan 18 11:30:10 2011] [error] [client 123.238.255.152] [ecid: 1295330410:127.0.0.1:10899:0:2,0] File does not exist: /oracle/CLON/inst/apps/CLON_obaserver/portal/OA_HTML/AppsLogin

I recreated the jar files using adadmin

and compiled the jsp using

cd $FND_TOP/patch/115/bin
perl ojspCompile.pl 
--compile --flush -p 20 -log /tmp/ojspc_error2.log

Nothing worked out.

Fix:
This problem occurred because wsrp_service.wsdl was owned by root but the services were being started by applmgr.

Ensure that the file
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/server-wsdl/wsrp_service.wsdl

it shows it is owned by root. Somebody started the services of Apps as root user.

Changed the owner of the file to Apps Owner.

Restarted the service.

Oracle Application Object Library (FND): Active Users Data Collection

2012-04-25T12:18:00.003+05:30

Select the responsibility "Oracle Diagnostics Tool"

Select application "Oracle Application Object Library" from the "Application" list of values

Click the "Advanced" tab

Scroll down to group "Data Collection"

Select test name "Active Users"

Input Parameters (* required)

Responsibility Id (LOV) *

Output contains

Product Installation Status and Patchset Level

Parameters

Key Profile Options

Self Service Web Application Users

Forms-Based Products Users

Concurrent Programs Users

References

Find SID from Concurrent Program Request ID

2012-04-12T18:15:00.002+05:30

SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(<your request id>)
)
);

ORA-04063: package body "APPS.AP_ACCTG_DATA_FIX_PKG" has errors

2012-03-23T17:23:00.000+05:30

Symptoms

After applying GDF patch 11787050:R12.AP.B or any other patch requiring undo accounting, running the script "ap_undo_acctg.sql" throws the below error:

ERROR
-----------------------
ORA-04063: package body "APPS.AP_ACCTG_DATA_FIX_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APPS.AP_ACCTG_DATA_FIX_PKG"
ORA-06512: at line 38

When trying to compile "APPS.AP_ACCTG_DATA_FIX_PKG", following errors are shown:

AP_ACCTG_DATA_FIX_PKG PLS-00394: wrong number of values in the INTO list of a FETCH statement

Cause

A technical dependency has been introduced between the
files apacpays.pls, apacpayb.pls and apgdfalb.pls with the
bugfix 9716573.

Due to the technical dependency introduced, the AP_ACCTG_DATA_FIX_PKG does not compile when there's a descrepancy between the datafix package and other packages.
Hence results in the package being invalid.

This is explained in the below internal bug:
Internal BUG 11772495 - RCA: REMOVING TECHNICAL DEP BETWEEN APACPAYB.PLS & APGDFALB.PLS

Solution

1. Download and review the readme and pre-requisites for Patch 11772495:R12.AP.B (to fix the package apacpayb.pls) and Patch.12582979:R12.AP.B (to upgrade the version of apgdfalb.pls).

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.
4. Confirm the following file versions:
ap patch/115/sql apacpayb.pls 120.12.12010000.28
ap patch/115/sql apacpays.pls 120.6.12010000.10
ap patch/115/sql apgdfalb.pls 120.1.12010000.39
ap patch/115/sql apgdfals.pls 120.1.12010000.13
You can use the commands like the following:
strings -a $XX_TOP/filename |grep '$Header'

5. Check if the AP_ACCTG_DATA_FIX_PKG is now valid and retest running the script.

6. Migrate the solution as appropriate to other environments.

Step by Step Create read only user in oracle

2012-01-19T17:14:00.002+05:30

Step to Create Read only User in Oracle

create role read_only; --- Give new Role name

Oracle Auditing is configured to use the Database Audit Trail, type the following command:

grant select on DBA_AUDIT_TRAIL to read_only;

create user gaurav identified by password; -- Create User

Oracle Auditing is configured to use the XML Audit Trail, type the following command:

grant select on V_$XML_AUDIT_TRAIL to gaurav;

grant connect to gaurav;

grant read_only to gaurav;

SQL> spool table.sql

SQL> SELECT 'GRANT SELECT ON APPS.' ||TABLE_NAME || ' TO read_only;' FROM DBA_TABLES WHERE OWNER='APPS';
'GRANTSELECTONAPPS.'||TABLE_NAME||'TO read_only;'
----------------------------------------------------------------------
GRANT SELECT ON APPS.HYA_PERSONAL_VAR TO read_only;
GRANT SELECT ON APPS.HFM_ERRORLOG TO read_only;
GRANT SELECT ON APPS.HSX_DATASOURCES TO read_only;

'GRANTSELECTONAPPS.'||TABLE_NAME||'TO read_only;'
----------------------------------------------------------------------
GRANT SELECT ON APPS.HSV_ACTIVITY_USERS TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_SESSIONS TO read_only;
GRANT SELECT ON APPS.HSV_USERS_ON_SYSTEM TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_KILL_USERS TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_NO_ACCESS TO read_only;

8145 rows selected.

SQL> SELECT 'GRANT SELECT ON APPS.' ||VIEW_NAME || ' TO read_only;' FROM DBA_VIEWS WHERE OWNER='APPS';

no rows selected

SQL> spool off

SQL>@table.sql

Creating Trigger:-

create or replace trigger log_on_after_gaurav
after logon ON gaurav.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = APPS';
END;
/

It's Done. . . .

Sample .bash_profile text file edited for Oracle installation

2011-12-21T12:43:00.000+05:30

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/8.1.7; export ORACLE_HOME
ORACLE_SID=prod1ora; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/8.1.7/bin; export PATH
CLASSPATH=.:/u01/app/oracle/product/8.1.7/jdbc/lib/classes111.zip; export CLASSPATH
LD_LIBRARY_PATH=/u01/app/oracle/product/8.1.7/lib; export LD_LIBRARY_PATH
LD_ASSUME_KERNEL=2.2.5; export LD_ASSUME_KERNEL
umask 022

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION", line 5130 ORA-06512: at line 1

2011-11-29T11:53:00.001+05:30

Appove Workflow Notification Mailer --

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION", line 5130 ORA-06512: at line 1

Solution :

select do.owner,do.object_name,do.object_type,dl.session_id,vs.serial#, vs.program,vs.machine,vs.osuser
from dba_locks dl,dba_objects do,v$session vs
where do.object_name ='WF_NOTIFICATIONS' and do.object_type='TABLE' and dl.lock_id1 =do.object_id and vs.sid = dl.session_id;

Issue the command

sql > alter system kill sessions 'sid,serial#' immediate;

Viewing Memory Use for Each User Session

2011-11-24T10:04:00.000+05:30

The following query lists all current sessions, showing the Oracle user and current User Global Area (UGA) memory use for each session:

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';

USERNAME            Current              UGA memory
--------------- -------------   ---------------------------------
                                   18636bytes
                                   17464bytes
                                   19180bytes
                                   18364bytes
                                   39384bytes
                                   35292bytes
                                   17696bytes
                                   15868bytes
USERSCOTT                          42244bytes
SYS                                98196bytes
SYSTEM                             30648bytes

Creating APPS READ ONLY schema in Oracle Applications

2011-11-23T15:16:00.000+05:30

step 1: Connect as sysdba and create the database user to be used for apps read only schema.

bash $ sqlplus "/ as sysdba"

SQL > create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

SQL> grant connect, resource to appsro;

SQL> grant create synonym to appsro;

SQL> exit;

step 2: Connect as APPS user and run the SQL commands:

bash $ sqlplus apps/******

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> spool grant_select.sql

SQL> select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to appsro;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> exit;

step 3:
- connect as sysdba :

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

step 4:
- connect as appsro

bash $ sqlplus appsro/appsro

SQL> @create_synonyms.sql

SQL> exit;

You are done. Now your users can use “appsro” schema to have the read only access to Apps Data.

FRM-92101: There was a failure in the Forms Server during startup - R12.1.1 & R12.1.3

2011-11-15T16:46:00.000+05:30

After successful completion of fresh installation of R12.1.1 on OEL(Oracle Eneterprise Linux 5.5), there was an error while accessing form server. All other services and application are working fine.

When we are trying to access form Server its giving error as below:

When we click on details:

This error message is because of the missing rpm openmotif21-2.1.30-11.EL5.i386.rpm on Linux OS.

You can download this rpm from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/

After Installing this rpm bounce the middle tiers and access form server.

Issue must be resolved.

Happy reading :)

error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory R12.1.1

2011-11-15T12:36:00.000+05:30

Hello,

I was Installing Oracle E-Biz R12.1.1 on OEL 5.6 - 64 Bit for one of our customer. All OS pre-requisites has been configured as per the MOS tech note for Installation and all pre-req check from the rapidwiz Installer went fine.

At last steps of Installation during validating system configuration it failed on HTTP, loginpage, Help Directory and JSP.

When checked all services were up and running except web server.



checking status of OPMN managed processes...

Processes in Instance: VIS_oel.oel.WORLD.com
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |   13776 | Alive
OC4JGroup:default_group          | OC4J:forms         |   13703 | Alive
OC4JGroup:default_group          | OC4J:oacore        |   13617 | Alive
HTTP_Server                      | HTTP_Server        |   13560 | Down 

Http_server is shown as Down.

Error messages recorded in logfiles:

Installation logfile:



HTTP 
----- 

checking URL = http://oel.WORLD.com:8011

RW-50015: Error: - HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button. 


Help Page 
---------- 

checking URL = http://oel.WORLD.com:8011/OA_HTML/help 

RW-50015: Error: - Help Page is not responding. The service might not have started on the port yet. Please check the service and use the retry button. 


Virtual Directory 
------------------ 

RW-50015: Error: - Http Server Virtual Directories is not responding. The service might not have started on the port yet. Please check the service and use the retry button. 


JSP 
---- 

checking URL = http://oel.WORLD.com:8011/OA_HTML/jtfTestCookie.jsp 

RW-50015: Error: - JSP is not responding. The service might not have started on the port yet. Please check the service and use the retry button. 


Login Page 
----------- 

RW-50015: Error: - Login Page is not responding. The service might not have started on the port yet. Please check the service and use the retry button. 
RW-10001: Rapidinstall wizard has detected that your configuration has errors. You must resolve these issues before continuing.

Apache logfiles:


07/04/11-10:23:44 :: adapcctl.sh: starting OPMN managed OHS instance

opmnctl: starting opmn managed processes...
================================================================================
opmn id=OEL.WORLD.COM:6210
    0 of 1 processes started.

ias-instance id=VIS_oel.oel.WORLD.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server/

Error
--> Process (index=1,uid=83499476,pid=12821)
    failed to start a managed process after the maximum retry limit
    Log:
    /u02/oracle/VIS/inst/apps/VIS_oel/logs/ora/10.1.3/opmn/HTTP_Server~1.log


/u02/oracle/VIS/inst/apps/VIS_oel/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
/u02/oracle/VIS/apps/tech_st/10.1.3/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

Cause:

http web server unable to start due to missing library.

Solution:

1) Shutdown all application services.
2) create link for library file as below



ln -s   /usr/lib/libgdbm.so.2.0.0  /usr/lib/libdb.so.2

3) Startup all application services.

Now you should be able to access the application.

Happy reading... :)

Upgrade from Oracle 9i to 11g Database

2011-09-14T12:05:00.000+05:30

Upgrade from 9i to 11g

The following is a real scenario for upgrading a 9.2.0.8 DB to 11.2.0.1.0 version on Redhat 4.7 in 28 Step

Before continue please check Metalink [ID 730365.1]
It Includes all patchset downloads + How to upgrade from any Oracle DB version to another one.

Required packges for installing 11g software: This for RHCL 4.7
---------------------------------------------------------
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
unixODBC-devel-2.2.11

Install 11g software on new ORACLE Home...

Apply the latest CPU patch (Optional)... Yes you can Apply the cpu patch on the 11g ORACLE_HOME -as a pre-Upgrade step- before having a database. Check Metalink Note [ID 461082.1]

Note ID: 429825.1
####### ########
Database upgrade steps from 9i to 11g:
#####################################

HHHHHHHHHHHH
On the 9i Server
HHHHHHHHHHHH

Step 1:
----------
Log into 11g server and copy the following scripts from 11g ORACLE_HOME/rdbms/admin directory on 11g server to 9i server:

$scp /u01/oracle/ora11g/11.2.0.1/rdbms/admin/utlu112i.sql oracle@9i_server:/home/oracle
$scp /u01/oracle/ora11g/11.2.0.1/rdbms/admin/utltzuv2.sql oracle@9i_server:/home/oracle

Step 2:
----------
=Run utlrp script to fix any invalid object before the upgrade:
--------------------------------------------------------------
SQL> @?/rdbms/admin/utlrp.sql

Note down invalid objects to compare after the upgrade to 11g:
--------------------------------------------------------------
SQL> set pages 0
SQL> set linesize 170
SQL> select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID';

Step 3: Save a script for Deprecated CONNECT Role:
----------
In 11g version CONNECT role has only the "CREATE SESSION" privilege, the other privileges are NOT exist in CONNECT role in 11g version like:

CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

So you need to re-grant these privileges to users who have connect role after upgrade to 11g.
This SQL will help you just save the result in somewhere to run it after the upgrade:

SQL> set linesize 170
SQL> set pages 100
SQL> spool /home/oracle/connect_role.sql
SQL> SELECT 'grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to '||grantee||';'
FROM dba_role_privs WHERE granted_role = 'CONNECT' AND grantee
NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR',
'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST',
'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR',
'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA',
'XDB', 'ODM');
SQL> spool off

Step 4:
----------
Create script to save DBLINKS creation script:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

Step 5: Upgrade the TIMEZONE version
----------
Convert the 9i database TIMEZONE from version 1 to version 4:

Download this interm patch 5632264 .. Extract it .. run "opatch apply" command .. As the following:

First shutdown the database:
---------------------------
SQL> shu immediate;

Apply the patch:
---------------
$cd /u01/oracle/ora9i/
$unzip p5632264_92080_timezoneV4.zip
$cd /u01/oracle/ora9i/5632264
$opatch apply

Startup the database again:
---------------------------
SQL> startup

The following query must return version 4:

SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;

VERSION
----------
4

Step 6:
----------
Run the script you extracted before from 11gr2 binaries

$cd /home/oracle/

$sqlplus "/ as sysdba"

SQL> spool utlu112i.log
SQL> @utlu112i.sql
SQL> spool off

This script will give you infomation about tablespaces if they need to adjusted according to 11g requirements ,also gives information about parameters need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.

Step 7: Gather SYS schema statistics
----------
Gather statistics for SYS schema:
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);

Step 8: Check the Dictionary
----------
Check for any corruption in the dictionary -will create a script then will run it-:

Script Creation:
Following code creates a script called analyze.sql

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

Run the following scripts for Dictionary checking:

SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

Step 9:
----------

Ensure there is no files need media recovery or in backup mode:

SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status!='NOT ACTIVE';

Step 10:
----------

Resolve any outstanding unresolved distributed transaction:

SQL> select * from dba_2pc_pending;

If that query returned rows you should do the following:
--
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Step 11:
----------
Ensure the users sys and system have 'system' as their default tablespace.

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');

Step 12:
-----------
Ensure that the aud$ is in the system tablespace when auditing is enabled.

SQL> SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';

Step 13: Saving database files location
----------
Note down the location of datafiles, redo logs, control files.

SQL> col name for a70
SQL> col file_name for a70
SQL> col member for a70
SQL> set feedback off
SQL> spool db_files.txt
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;
SQL> spool off

Step 14:
-----------
Check whether database has any externally authenticated SSL users.

SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';

If any SSL users are found then Step 31 has to be followed after the upgrade

Step 15: Set DB in NoArchivelog mode.
----------
Put the database in noarchivelog mode to minimize the upgrade time.

$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> alter database noarchivelog;
SQL> archive log stop;
SQL> shutdown immediate;

Step 16: Take a cold backup.
----------

Step 17:
----------
Copy the Datafiles + PFile from the 9i server to the new 11g server -in case you are upgrading in a new server-

HHHHHHHHHHHHH
On The 11g Server:
HHHHHHHHHHHHH

Step 18: Modify the copied 9i Pfile.
----------
=Comment out obsoleted parameters like:

transaction_auditing
hash_join_enabled
DDL_WAIT_FOR_LOCKS
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS

=Replace all deprecated parameters like:

BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
STANDBY_ARCHIVE_DEST
log_archive_start
max_enabled_roles
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)

=set the COMPATIBLE parameter to 10.1.0
compatible=10.1.0

=set the parameter CLUSTER_DATABASE=FALSE

=Add the parameter DIAGNOSTIC_DEST to hold database logs:
diagnostic_dest=/u01/oracle/ora11g/11.2.0.1/diagnostics/ORCL

When done copy the pfile to 11g ORACLE_HOME/dbs

Step 19: Create the Diagnostic directory for the DB
----------
mkdir -p $ORACLE_HOME/diagnostics/ORCL

Step 20:
----------
Make sure that ".bash_profile" holds the new 11g installation environment variables like:
ORACLE_BASE
ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
TNS_ADMIN
CLASSPATH
PATH

These variables are supposed to be exist since the 11gr2 installation on that server, If you are doing the upgrade on the same server you must create a new profile something like ".11g_bash_profile" holding all variables for 11g installation.

Step 21: Update oratab & oraInventory
----------
Update the oratab file:

$vi /etc/oratab

#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g/11.2.0.1:Y

-Hash the entry for 9i ORACLE_HOME in case you upgrading on the same server then add the an entry for 11g ORACLE_HOME

Update oraInventory location:
-----------------------------
$vi /etc/oraInst.loc

inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall

Step 22:
----------
Upgrading the Database to 11gR2...

Note: If you are upgrading on the same server run the profile holds the 11g environment variables -you prepared before- for the 11g installation.

Startup the DB in upgrade mode:
------------------------------
startup UPGRADE

Create the SYSAUX tablespace:
----------------------------
SQL>CREATE TABLESPACE SYSAUX
DATAFILE '/fiber_ocfs_pefms_data_1/oracle/pefms/sysaux01.dbf' SIZE 2000M reuse
autoextend on next 100m maxsize unlimited
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

start the upgrade script:
------------------------
$cd $ORACLE_HOME/rdbms/admin

SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

Database will be shutdown by catupgrd.sql script. Restart the Instance to reinitialize the system parameters for normal operation.

If you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file.

Run the Post-Upgrade Status Tool:
--------------------------------
SQL> @?/rdbms/admin/utlu112s.sql

Recompile any remaining stored PL/SQL:
-------------------------------------
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql

Check for the integrity of the upgraded database by running dbupgdiag.sql script:
--------------------------------------------------------------------------------
Download that script from Metalink Note [ID 556610.1] and run it.

During the test I found duplicate objects between SYS and SYSTEM so I followed the Metalink Note and dropped duplicate objects owned by SYSTEM user:

Warning:
Don't run the following commands unless you make sure that following objects are duplicate ones in your DB.

SQL> drop TABLE system.AQ$_SCHEDULES;
SQL> drop INDEX system.AQ$_SCHEDULES_PRIMARy;
SQL> drop PACKAGE system.DBMS_REPCAT_AUTH;
SQL> drop PACKAGE system.BODYDBMS_REPCAT_AUTH;
SQL> drop PACKAGE system.DBMS_SHARED_POOL;
SQL> drop PACKAGE system.BODYDBMS_SHARED_POOL;
SQL> drop TABLE system.UTL_RECOMP_INVALID ;
SQL> drop TABLE system.UTL_RECOMP_SORTED;
SQL> drop TABLE system.UTL_RECOMP_COMPILED;
SQL> drop TABLE system.UTL_RECOMP_BACKUP_JOBS;
SQL> drop TABLE system.UTL_RECOMP_LOG;
SQL> drop PACKAGE system.UTL_RECOMP;
SQL> drop PACKAGE system.BODYUTL_RECOMP;
SQL> drop TABLE system.CHAINED_ROWS;

Step 23:
----------
Grant the missing privileges related to "connect" which been deprecated in 11g:
------------------------------------------------------------------------------
-Using Script been created in Step3-
SQL> @/home/oracle/connect_role.sql

##################
Post Upgrade Steps:
##################

Step 24:
-----------
Check listener.ora for any modifications needed to listen on the upgraded DB.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pc-ora-test)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/ora11g/11.2.0.1)
(SID_NAME = ORCL)
)
)

Startup the listener:

lsnrctl start

Step 25: Create the SPFILE & Password file
-----------
Create the SPFILE:
-----------------
SQL> create spfile from pfile;

Create the Password File:
------------------------
$cd /u01/oracle/ora11g/11.2.0.1/dbs

$orapwd file=orapwORCL password=

Step 26:
----------
Change the compatability version to use the new 11g features:

alter system set compatible='11.1.0.6' scope=spfile;

shu immediate;
startup;

Step 27: Load Product user profile information
----------
This step is needed to tackle the error "Error accessing PRODUCT_USER_PROFILE" when you login to SQLPLUS.

Running script pupbld.sql as SYSTEM user:

SQL> conn system/xxx
SQL> @/u01/oracle/ora11g/11.2.0.1/sqlplus/admin/pupbld.sql

Step 28: Rebuild unusable indexes
----------
Using this script:
SQL> select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online parallel 4;'from dba_indexes where status ='UNUSABLE';

Step 29: Gather Statistics on the database
----------
This step is very important for performance on the database.

Gather FIXED OBJECTS stats:
--------------------------
SQL> Execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (no_invalidate => FALSE );

Note:"gather fixed objects" is not included in gather_dictionary_stats it is also not included in the the daily gather statistics job. The database does not gather these statistics automatically.

Gather DICTIONARY stats:
-----------------------
SQL> Execute DBMS_STATS.GATHER_DICTIONARY_STATS ();

Gather database statistics:
--------------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE,degree => 4);

Step 30: Fix Invalid objects:
-----------
SQL> @?/rdbms/admin/utlrp

Step 31: lock default accounts
-----------
-Oracle recommends that you lock all Oracle supplied accounts except SYS and SYSTEM.
-Never lock these two accounts or the Enterprise Manager will not work: DBSNMP & SYSMAN

List of Default accounts and their description:

User Description
---- -----------
SYS All of the base tables and views for the database's data dictionary are stored in the schema SYS.
SYSTEM contains additional tables and views that display administrative information and used by Oracle tools.
DBSNMP Supports Oracle SNMP (Simple Network Management Protocol).
OUTLN centrally manage metadata associated with stored outlines.
MDSYS Supports Oracle Spatial.
ORDSYS Supports Oracle interMedia.
ORDPLUGINS Supports Oracle interMedia.
CTXSYS Supports Oracle ConText for advanced linguistic processing of English-language text.
DSSYS Dynamic Services Secured Web Service.
WKSYS support Oracle's Ultrasearch option.
WMSYS store all the metadata information for Oracle Workspace Manager.
XDB support SQL XML management: XML DB
ODM support Oracle Data Mining.
OLAPSYS used to create OLAP metadata structures.
REPADMIN Replication user.
ANONYMOUS support SQL XML management.

Please check this link for full descrition:
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml

Step 32: Check for Oracle recommended patches
-----------
Metalink Note ID 756671.1 includes the recommended patches starting from release 10.2.0.3 and above.

Also: Note ID 742060.1 includes the most accurate information Oracle can provide for coming releases.

Steps to recover from Logical Corruption Without Backup

2011-09-11T15:06:00.002+05:30

Creating a Repair Table

The following example creates a repair table for the users tablespace.

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'USERS');

END;

For each repair or orphan key table, a view is also created that eliminates any rows that pertain to objects that no longer exist. The name of the view corresponds to the name of the repair or orphan key table and is prefixed by DBA_ (for example, DBA_REPAIR_TABLE or DBA_ORPHAN_KEY_TABLE).

The following query describes the repair table that was created for the users tablespace.

DESC REPAIR_TABLE

Name Null? Type

---------------------------- -------- --------------

OBJECT_ID NOT NULL NUMBER

TABLESPACE_ID NOT NULL NUMBER

RELATIVE_FILE_ID NOT NULL NUMBER

BLOCK_ID NOT NULL NUMBER

CORRUPT_TYPE NOT NULL NUMBER

SCHEMA_NAME NOT NULL VARCHAR2(30)

OBJECT_NAME NOT NULL VARCHAR2(30)

BASEOBJECT_NAME VARCHAR2(30)

PARTITION_NAME VARCHAR2(30)

CORRUPT_DESCRIPTION VARCHAR2(2000)

REPAIR_DESCRIPTION VARCHAR2(200)

MARKED_CORRUPT NOT NULL VARCHAR2(10)

CHECK_TIMESTAMP NOT NULL DATE

FIX_TIMESTAMP DATE

REFORMAT_TIMESTAMP DATE

Example: Creating an Orphan Key Table

This example illustrates the creation of an orphan key table for the users tablespace.

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'ORPHAN_KEY_TABLE',

TABLE_TYPE => dbms_repair.orphan_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'USERS');

END;

The orphan key table is described in the following query:

DESC ORPHAN_KEY_TABLE

Name Null? Type

---------------------------- -------- -----------------

SCHEMA_NAME NOT NULL VARCHAR2(30)

INDEX_NAME NOT NULL VARCHAR2(30)

IPART_NAME VARCHAR2(30)

INDEX_ID NOT NULL NUMBER

TABLE_NAME NOT NULL VARCHAR2(30)

PART_NAME VARCHAR2(30)

TABLE_ID NOT NULL NUMBER

KEYROWID NOT NULL ROWID

KEY NOT NULL ROWID

DUMP_TIMESTAMP NOT NULL DATE

Example: Detecting Corruption

The CHECK_OBJECT procedure checks the specified object, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition name, or subpartition name when you want to check a portion of an object.

Validation consists of checking all blocks in the object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK_OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is skipped.

The following is an example of executing the CHECK_OBJECT procedure for the scott.dept table.

SET SERVEROUTPUT ON

DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'DEPT',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

CORRUPT_COUNT => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

SQL*Plus outputs the following line, indicating one corruption:

number corrupt: 1

Querying the repair table produces information describing the corruption and suggesting a repair action.

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,

CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION

FROM REPAIR_TABLE;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR

------------------------------ ---------- ------------ ----------

CORRUPT_DESCRIPTION

------------------------------------------------------------------------------

REPAIR_DESCRIPTION

------------------------------------------------------------------------------

DEPT 3 1 FALSE

kdbchk: row locked by non-existent transaction

table=0 slot=0

lockid=32 ktbbhitc=1

mark block software corrupt

The corrupted block has not yet been marked corrupt, so this is the time to extract any meaningful data. After the block is marked corrupt, the entire block must be skipped.

Example: Fixing Corrupt Blocks

Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated by the CHECK_OBJECTprocedure. Before changing a block, the block is checked to ensure that the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a timestamp.

This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.

SET SERVEROUTPUT ON

DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME=> 'DEPT',

OBJECT_TYPE => dbms_repair.table_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

FIX_COUNT=> num_fix);

DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));

END;

SQL*Plus outputs the following line:

num fix: 1

The following query confirms that the repair was done.

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT

FROM REPAIR_TABLE;

OBJECT_NAME BLOCK_ID MARKED_COR

------------------------------ ---------- ----------

DEPT 3 TRUE

Example: Finding Index Entries Pointing to Corrupt Data Blocks

The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each index entry, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.

This information can be useful for rebuilding lost rows in the table and for diagnostic purposes.

Note:

This should be run for every index associated with a table identified in the repair table.

In this example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index entries pointing to rows in the corrupt data block.

SET SERVEROUTPUT ON

DECLARE num_orphans INT;

BEGIN

num_orphans := 0;

DBMS_REPAIR.DUMP_ORPHAN_KEYS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'PK_DEPT',

OBJECT_TYPE => dbms_repair.index_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',

KEY_COUNT => num_orphans);

DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));

END;

The following output indicates that there are three orphan keys:

orphan key count: 3

Index entries in the orphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same result set.

Example: Skipping Corrupt Blocks

The SKIP_CORRUPT_BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skipping applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.

The following example enables the skipping of software corrupt blocks for the scott.dept table:

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'DEPT',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.skip_flag);

END;

Querying scott's tables using the DBA_TABLES view shows that SKIP_CORRUPT is enabled for table scott.dept.

SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES

WHERE OWNER = 'SCOTT';

OWNER TABLE_NAME SKIP_COR

------------------------------ ------------------------------ --------

SCOTT ACCOUNT DISABLED

SCOTT BONUS DISABLED

SCOTT DEPT ENABLED

SCOTT DOCINDEX DISABLED

SCOTT EMP DISABLED

SCOTT RECEIPT DISABLED

SCOTT SALGRADE DISABLED

SCOTT SCOTT_EMP DISABLED

SCOTT SYS_IOT_OVER_12255 DISABLED

SCOTT WORK_AREA DISABLED

10 rows selected.

Manual upgrade from 9i to 10g Release 2

2011-09-11T14:57:00.002+05:30

Manual upgrade from 9i to 10g Release 2

This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.

1. Compile any invalid objects
@?/rdbms/admin/utlrp.sql

2. Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '<file_name>' size 512M
extent management local
segment space management auto
/

3. Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql

Review the output and make any necessary alterations. Make a note of how many invalid objects there are.

4. Shut the database down with either normal or immediate
shutdown immediate

5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* <new_home>/dbs/

6. Edit oratab
Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.

7. Upgrade the database
sqlplus "/ as sysdba"

startup upgrade
This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql

8. Recompile any invalid objects
@?/rdbms/admin/utlrp.sql
Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.

9. Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql

10. Alter or remove initialisation parameters
Temporarily creating a pfile is the easiest way.
create pfile from spfile;

shutdown immediate

vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
Alter/remove parameters identified in step 9. Set compatible to 10.2.0.0.0
startup

create spfile from pfile;

shutdown immediate

startup

That's it!

Pre-Requiste for R12 Installation

2011-09-11T14:54:00.001+05:30

How to Install Oracle EBS R12 on Redhat Enterprise Linux 5.5

Before we begin the wild ride, there a few modifications that needs to be done to the system, a few packages need to be installed. I’ll assume that you have searched for quite a while and maybe you even tried to install the damn thing and ran into errors, so I’ll get right into the process. Here what we’re going to do:

1. Install Development Tools

2. Install required packages

3. Modify the System Configuration and Kernel (don’t panic)

4. Create Required Users

5. Install Oracle Apps

Install Development Tools

1	yum groupinstall "Development Tools"

Install required packages

You can either install the following packages using yum or download this file which include all the files.

1. openmotif21-2.1.30-11.EL5.i386.rpm

2. xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386.rpm

3. elfutils-libelf-devel-0.125

4. elfutils-libelf-devel-static-0.125

5. libaio-devel-0.3.106

6. unixODBC-2.2.11

7. unixODBC-devel-2.2.11

Note: You need to make the following modifications after installing openmotif

1 2	$ unlink /usr/lib/libXtst.so.6 $ ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6

Modify the System Configuration and Kernel

Open /etc/sysctl.conf as root

1 2	$ su - root $ vi /etc/sysctl.conf

then add the following lines at the end of the file

#Oracle EBS R12.1.1 Kernel Settings

kernel.shmall = 2097152

kernel.shmmax = 4294967295

kernel.shmmni = 4096

kernel.sem = 256 32000 100 142

fs.file-max = 327679

net.ipv4.ip_local_port_range = 10000 65000

net.core.rmem_default=262144

net.core.rmem_max=262144

net.core.wmem_default=262144

net.core.wmem_max=4194304

Now lets apply the new settings

1	$ /sbin/sysctl –p

Add the following lines to the /etc/security/limits.conf file:

* hard nofile 65535

* soft nofile 4096

* hard nproc 16384

* soft nproc 2047

Add the following lines to the /etc/resolv.conf file:

1 2	options attempts:5 options timeout:15

Now we need to restart the system so the above modifications gets applied.

Create Required Users

We’ll need to create one group dba and two users, oracle and applmgr. The oracle users will be the owner of the database tier folders and services, while the applmgr user will be the owner of the applications tier.

You can either create the group and the users using GUI or use the following commands:

$ su - root

$ groupadd dba

$ useradd –g dba –d /home/oracle oracle

$ useradd –g dba –d /home/applmgr applmgr

Install Oracle Apps

Before we start the installation we need first to create the installation directory. We will name the directory ebs and create at root /.

$ su - root

$ cd /

$ mkdir ebs

$ chown oracle:dba ebs

$ chmod 777 ebs

Now we can start the rapid install wizard. I’ll not be using a staging area but the following applies to both installing from a staging area or from Oracle’s EBS media pack DVDs.
I’ll assume that the DVD-Rom is mounted at /media/cdrom.

// run the following as the oracle user, not root

$ cd /media/cdrom/Disk/rapidwiz

$ ./rapidwiz