Using Oracle 10g ASMM

Using Oracle 10g ASMM

As with the other manageability features of Oracle Database 10g, ASMM requires you to set the STATISTICS_LEVEL parameter to at least TYPICAL (the default), so make sure this setting is correct before you try to enable ASMM. You can enable it in the following ways:
Set SGA_TARGET to a nonzero value in the initialization parameter file (pfile).
Use Oracle Enterprise Manager (or Oracle Grid Control) or ALTER SYSTEM to dynamically set a nonzero value for SGA_TARGET in the server parameter file (spfile).
To use the command line (ALTER SYSTEM), set a value for SGA_TARGET and then set the parameters for all of the autotuned pools to 0, as follows:


SQL> alter system set sga_target=160M scope=both;
System altered.

SQL> alter system set db_cache_size=0;
System altered.

SQL> alter system set shared_pool_size=0;
System altered.


If you don't set the values for each of the autotuned pools to zero after switching to ASMM, whatever value you had set for a parameter will function as a lower threshold value for that pool—ASMM won't go below a nonzero value, even if it needs the memory for another autotuned pool.

using OracleAS 10g with the E-Business Suite Release 11.

It is now possible to integrate the E-Business Suite Release 11i with OracleAS 10g, for the use of Single Sign-On, Oracle Internet Directory, Portal, Discoverer, Web Cache and Oracle Integration.

Integrated, Not Upgraded

The key concept is that Release 11i may be integrated with OracleAS 10g. The existing E-Business Suite application server, Oracle9i Application Server 1.0.2.2.2, is not upgraded to OracleAS 10g; the two instances are integrated together in a loosely-coupled architecture like this

Remember that if you want to upgrade your existing E-Business Suite 9iAS application server to OracleAS 10g, you'll be able to do that in Release 12. For Release 11i, it's always going to be an integration-based architecture.

One Server or Two?

The diagram above shows the existing E-Business Suite 9iAS services and the new external OracleAS 10g services running on two different physical servers. That's our recommended configuration, particularly if you're planning to upgrade from Discoverer 4i to 10g (due to the former's obsolescence in Oct 2006).

It's possible to install OracleAS 10g on the same physical server where 9iAS is installed... if you have sufficient resources available on that box. You must install OracleAS 10g in a separate ORACLE_HOME. OracleAS 10g cannot be installed into the existing E-Business Suite 9iAS ORACLE_HOME.

What Are The Main OracleAS 10g Components?

This is something new to E-Business Suite sysadmins, so you may have to review this a few times to let it sink in.

Architecturally, you should think of OracleAS 10g as being comprised of middle-tier (application tier) products and infrastructure services. Middle-tier products include Portal, Discoverer, and Oracle Integration.

The OracleAS 10g Infrastructure includes Single Sign-On, Oracle Internet Directory, and the actual LDAP database where user credentials are stored. In general, all of the OracleAS 10g middle-tier products share the same OracleAS 10g Infrastructure.

Middle-tier products like Portal have content such as portal page definitions, pictures, downloadable files, and so on. This content has metadata, too, which determines how content is displayed and accessed. All of this product-specific content and metadata is stored in a database called the OracleAS 10g Metadata Repository.

Apps Architecture

First I want to discuss terminology which you will encounter here & in most of Oracle Documentation.

Server - is a process or group of processes and provides a particular functionality/service For example, Database Server listen and process database requests, similarly Web Server listens for and processes HTTP requests.

Tier - is a logical grouping of services, may be on single machine or spread across more than one physical machine. For Example, Middle Tier in Oracle apps consist Form,Web,Report...Services. Forms & Report can be one machine 1 & Web Server can be on machine 2, still they will be part of same tier called as Application Tier or Middle Tier.

Node - is referred as Machine, Each tier may consist of one or more node and each node can potentially have more than one tier.

Desktop Tier - Oracle Applications/E-Business Suite is built on three tier Architecture with first Desktop Tier ic client machines accessing applications via browser(HTML based Self service applications) or Java Applet(Jinitiator) for Form based interface.

Application/Middle Tier - Second is Application Tier also called as middle tier consists of WebServer Forms, Reports, Concurrent processing, Discoverer and Admin Server. I suppose its known as Middle tier because it lies between our desktop & third tier called database tier.

Database Tier - Third is Database Tier contain Orale database server which store all your application data.

What is Autoconfig ?

Autoconfig is tool in Apps to configure oracle Applications 11i , Application Tier as well Database Tier.

How to run Autoconfig ?
If you want to configure database tier then you have to execute autoconfig script adautocfg.sh on database tier else if you want to configure on application tier (Middle Tier ) then you have to execute one on middle tier .

Here is the script location
For Database tier its $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
For Application Tier its $OAD_TOP/admin/scripts/$CONTEXT_NAME

adautocfg.sh calls adconfig.sh from $AD_TOP/bin which inturn calls adconfig.pl from $AD_TOP/bin

Don't forget to take backup before executing autoconfig.

What source of information for Autoconfig to update configuration files ?
If you are wondering where is configuration information stored about your apps 11i instance based on which it updates your Instance then this file is xml file also called as Context file and its in $APPL_TOP/admin for Application Tier or $ORACLE_HOME/appsutil in Database Tier

Clone Apps 11i Instance

Cloning is process of creating replica of your source apps 11i Instance ( lets say you have one apps Instance with name PROD and you want to create similar instance ( including same patches & user data) like DEV then you will use adclone utility (Also called as Rapid Clone these days). In this case source Instance will be PROD and target Instance will be DEV. There are lot for scenario in which you wish to clone your E-Business Suite 11i Instance , like you want to Test if everything is OK in Test & then after testing want to create Production instance or you want to move your Instance from one machine to other machine.


Step1 . Prerequisites Steps you do before start cloning using rapid clone
1.1 Verify source and target nodes software versions
1.2 Apply the latest AutoConfig Template patch
1.3 Apply the latest Rapid Clone patches

Step2 . Clone Source to Target
2.1 Run preclone on DB tier
2.2 Run preclone on Apps or middle tier
2.3 Copy source file system to target file system
2.4 Configure db tier
2.5 Configure apps/middle tier

Step 3 Finishing Task
3.1 Update profile options
3.2 Update settings (If printers are not configured or you don't want to use printer you can skip this step)
3.3 Update workflow configuration settings (Important)

Dummy shell script example on how to copy files and run a sql script

#!/bin/ksh
######################################################################
#
# Program: dummy.sh
# Description: Dummy shell script example on how to copy files and run a sql script
# Author: Raf Hussain
# Date: 16/07/2007
#
#######################################################################
clear

echo '
Copying files & running script

************************************************


Your input will be recorded without cursor movement

Enter the password for apps: \c'
stty -echo
read APW
stty echo

#Copy the files into the $WCC_TOP directory.
#
cp file1.rdf $XXWCC_TOP/reports/US
cp file2.cpd $XXWCC_TOP/admin/sql
cp file3.sql $XXWCC_TOP/admin/sql


sqlplus apps/$APW @$XXWCC_TOP/admin/sql/file2.cpd
sqlplus apps/$APW @$XXWCC_TOP/admin/sql/file3.sql

How to find out redo_log_size

login to sqlplus and run the following select statement

select sum(bytes)/1024/1024 from v$log;

How to find out db_growth_per_month for the last year

Run the following from sqlplus

set pagesize 50000
tti "Database growth per month for last year"

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
/

tti off

How to find out the db_size

set pagesize 50000
select sum(bytes)/1024/1024 from dba_data_files;

How to get stats on discoverer work books

Login to sqlplus as the owner of the discoverer eul schema and run the following select statement

SELECT
QS.QS_DOC_OWNER USERNAME,
QS.QS_DOC_NAME WORKBOOK,
QS.QS_DOC_DETAILS WORKSHEET,
MAX(QS.QS_CREATED_DATE) LAST_USED
FROM
EUL4_QPP_STATS QS
GROUP BY
QS.QS_DOC_OWNER,
QS.QS_DOC_NAME,
QS.QS_DOC_DETAILS

To determine the activity in the last 15 minutes:

select limit_time, limit_connects,
to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",
user_id, disabled_flag
from icx_sessions
where last_connect > sysdate - 1/96;

To get the number of users on the system in the past 1 hour:

select count(distinct user_id) "users"
from icx_sessions
where last_connect > sysdate - 1 and user_id != '-1';

To get the number of users on the system in the past 1 hour:

select count(distinct user_id) "users"
from icx_sessions
where last_connect > sysdate - 1/24 and user_id != '-1';

To count concurrent users in Oracle Apps

select count(distinct d.user_name)
from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);

To find out the users connected to Oracle Applications Release 11i:

select distinct raf.user_name User_Name,raf.RESPONSIBILITY_KEY Responsibility
from fnd_user raf,
fnd_responsibility raf, icx_sessions hussain
where raf.user_id = hussain.user_id AND
raf.responsibility_id = hussain.responsibility_id AND
hussain.disabled_flag='N' AND
hussain.responsibility_id is not null AND
hussain.last_connect like sysdate;

How to check if username sysadmin has been locked in Oracle Apps 11i

select end_date from fnd_user where user_name = 'SYSADMIN';

If it comes back with a date then the account is locked out and you need to run the following update statement to unlock it.

update fnd_user set end_date = '' where user_name = 'SYSADMIN';
commit;

How to find concurrent requests older then 30 days and delete them from the system.

cd $COMN_TOP/admin/log/
Issue the following UNIX command to delete the files that have extension req and are older then 30 days.

find . -name "*req" -mtime +30 -print -exec rm {} \;

How To Manually Terminate a Running Concurrent Request

1) Log on to Oracle applications and get the request id

2) In Sql*plus, login as apps/apps

3) At the command prompt issue the following SQL statement:

Update FND_CONCURRENT_REQUESTS
set STATUS_CODE='C',
PHASE_CODE='C'
WHERE REQUEST_ID= '';

How to Extract Data From Date Tracked Tables

To view data, insert a row into fnd_sessions for the session id.

In SQL*Plus:

Insert into fnd_sessions
(session_id, effective_date)
Values
(userenv('sessionid'), sysdate);

How to Run a PYUPIP trace on Concurrent Process, Report

Turn on the PYUPIP trace in a linux / unix session. Make sure that you are in the directory that you have write privileges to, and there is plenty of disk space. From the command line enter:

PYUPIP apps/apps REQID > PYUPIP.txt

After the process completes or errors, break the trace by entering -C, that is(control key) C in your UNIX session where you started the trace.

Upgrading 8.1.7 to 9.0.2 in an Applications 11.5.7 Environment

Upgrading 8.1.7 to 9.0.2 in an Applications 11.5.7 Environment




Section #1
==========

Preparation and Installation
============================

2. Log in as the Oracle user

3. Prepare to create the 9.2.0 Oracle home

Decide where you want to install the 9.2.0 Oracle home on the database server
node. It must be in a different directory than the current Oracle home.
$ mkdir <9.2.0 Oracle Home)

4. Install Oracle9i Release 9.2.0 Database Server. Do not run any migrate
scripts at this time

Can be staged and installed as follows:

/stage/Disk1 = A99349-01
/stage/Disk2 = A99350-01
/stage/Disk3 = A99351-01

The environment variables DISPLAY, TEMP, TMPDIR, TMP should be set before
installing and any reference to the 8.0.6 or 8.1.7 Oracle Homes in the path
or environment variables should be removed. You path should contain the
following:

/usr/ccs/bin:/usr/sbin:/usr/lib:/usr/local/bin:/usr/bin/usr/ucb/:/bin:
/usr/openwin/bin

$ cd /stage/Disk1
$ . runInstaller
- Choose a new ORACLE_HOME Name (APPS9I_)
- Verify the location of the Oracle Home
- Choose a Custom Install
- Remove any products that you do not wish to install

- set the JRE 1.3.1 directory to ORACLE_HOME/jre/1.3.1, JRE 1.1.8 directory to
ORACLE_HOME/jre/1.1.8 and OUI to ORACLE_HOME/appsoui

- Install the new database version
The following components are required by the Applications and must be installed:
� Oracle9i
� Enterprise Edition Options -> Oracle Spatial, Oracle Partitioning
� Oracle Net Services -> Oracle Net Listener, Oracle Connection Manager
� Oracle HTTP Server -> BC4J Runtime for Database, Oracle Mod PL/SQL Gateway

Attention: The following development tools should not be installed on
Applications database server nodes running Windows. These tools do not
support multiple Oracle homes and are not used by the Applications.

Oracle9i Development Kit -> Oracle Objects for OLE, Oracle ODBC Driver,
Oracle Provider for OLE DB

- Check the "No" radio button in response to the offer to create a new database
- Run ORACLE_HOME/root.sh as the root user when prompted to do so
- Cancel the Netconfig Assistant
- Exit the installer


5. Cleanup after the 9.2.0 installation
$ cd $ORACLE_HOME/Apache/Apache/bin
$ apachectl stop
$ cd $ORACLE_HOME/bin
$ agentctl stop

6. Move over and edit the environment (*.env) files from the 8.1.7 ORACLE_HOME
to the 9.2.0 Home
-- Modify the entry for ORACLE_HOME
-- Modify the line in environment files that execute ORACLE.env to point to
the 9.2.0 ORACLE_HOME.

7. Copy and modify the TNS files from your 8.1.7 $TNS_ADMIN directory
(listener.ora and tnsnames.ora).

ORACLE_HOME= /apps2/oracle/prodora/9.2.0
LOG_DIRECTORY_PROD = /apps2/oracle/prodora/9.2.0/network/admin/
TRACE_DIRECTORY_PROD = /apps2/oracle/prodora/9.2.0/network/admin/


8. Install 9.2.0.4 Patch Set (3095277). (This patch is 332 MB)

$ cd <9.2.0 ORACLE_HOME>
$ . .env
$ cd
$ unzip p3095277_9204_.zip
$ unzip p3095277_9204_cpio.zip
$ cpio –dvw 902_xx.cpio
$ cd $
$ . runInstaller
Choose the 9204 patchset location
Choose the 9204 home
Click next to get product list
Choose to install installer 2.2.0.18.0
Choose "Next Install"
Click next to load product list
Install Oracle 9iR2 Patch Set 9.2.0.4
Exit installer

NOTE: <>


9. Download and unzip the latest Opatch files from 2617419
$ cd <(9.2.0 Oracle Home>
$ unzip p2617419_220_GENERIC.zip
This will create an Opatch directory that will be used for applying one-off
RDBMS patches


10. Apply RDBMS patches for 9.2.0.4 to the 9.2.0 Oracle home (As of January 2004)

For all UNIX/Linux platforms:

-- 3131210(Unzips to 3207566)
-- 3167234(Unzips to 3207561)

$ cd <(9.2.0 Oracle Home>/OPatch
$perl opatch.pl apply /3207566
$perl opatch.pl apply /3207561


11. Check OracleMetalink for additional patches

12. Install OUI21 in the <9.2.0 Oracle Home>
cp p2949808_11i_SOLARIS.zip to 9.2.0 Oracle Home
cd 9.2.0 Oracle Home
unzip p2949808_11i_SOLARIS.zip

13. Install editcontext in the <9.2.0 Oracle Home>
cp p2873456_11i_GENERIC.zip to <9.2.0 Oracle Home>/appsutil
cd <9.2.0 Oracle Home>/appsutil
unzip p2873456_11i_GENERIC.zip

14. Logout



Section #2
==========

Prepare database for upgrade
=============================

1. Logins as the oracle user

2. Source the 8.1.7 environment

3. Add required space to your system tablespace and modify rollback segments
as needed. We recommend having at least 500MB of free SYSTEM tablespace
and 750MB of free rollback segment space, with MAXEXTENTS set to at least
512MB before performing the upgrade.

$ sqlplus system/

ALTER TABLESPACE system
ADD DATAFILE '/apps2/oracle/proddata/system07.dbf'
SIZE 750M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

ALTER ROLLBACK SEGMENT
STORAGE (MAXEXTENTS UNLIMITED);
COMMIT;

To check for free space in the System Tablespace:
=================================================
select nvl(sum(bytes)/1024,0) as "System Tablespace Avail(KB)"
from dba_free_space
where tablespace_name like 'SYSTEM';


To find out your Rollback Segment Names:
=========================================
select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
s.wraps, s.status
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;


4. Determine the files that you will back up in Step 6 by issuing the
following SQL statements

SPOOL v8files.log
SELECT member FROM v$logfile;

SELECT name FROM v$datafile;

SELECT name FROM v$controlfile;
SPOOL OFF

The v8files.log spool file lists all of the files that you must back up in
Step 6.


5. Shutdown the database


6. Perform a full offline backup of the database



Section #3
==========

Upgrade the Oracle Applications Database
=========================================

1. Copy configuration files to a location outside of the old Oracle home

NOTE:
=====
For Oracle Parallel Server, perform this step on all nodes. Also, if your
initdb_name.ora file resides within the old environment's Oracle home, then
move or copy the initdb_name.ora file to a location outside of the old
environment's Oracle home.

$ cd $<8.1.7>ORACLE_HOME/dbs
$ copy *.ora <9.2.0 ORACLE_HOME>/dbs/


2. Adjust the initialization parameter file for use with the new release.
Specifically, complete the following steps:

Changes
=======
3. Make sure the COMPATIBLE initialization parameter is properly set for the
new Oracle9i release. If COMPATIBLE is set below 8.1.0, then you will
encounter the following error when you attempt to start up your release 9.2
database later:

ORA-00401: the value for parameter compatible is not supported by this release

For release 8.1.7
==================
If COMPATIBLE is set to 8.0.x, then either remove COMPATIBLE from your parameter
file, or set COMPATIBLE to 8.1.0.

If COMPATIBLE is set to 8.1.x, then leave the setting as is.

InitPROD.ora
=============
JOB_QUEUE_PROCESSES = 0
AQ_TM_PROCESSES = 0
OPTIMIZER_MODE = choose
COMPATIBLE = 8.1.7
REMOTE_LOGIN_PASSWORDFILE = NONE
_system_trig_enabled = TRUE
O7_DICTIONARY_ACCESSIBILITY = TRUE
Db_domain =


Comment out temporarily
========================
ifilecbo.ora

ALWAYS_ANTI_JOIN
ALWAYS_SEMI_JOIN
OPTIMIZER_PERCENT_PARALLEL


Remove Obsoleted entries
=========================
event="10943 trace name context forever, level 2"

Adjust the following initialization parameters for the duration of the upgrade.

NOTE: After the upgrade is complete, you must change the settings for these
parameters back to their pre-upgrade settings:

1. Remove obsolete initialization parameters and adjust deprecated
initialization parameters.

2. If you are using a password file, then set REMOTE_LOGIN_PASSWORDFILE
to NONE in the parameter file.

3. If you are upgrading a cluster database, then set the CLUSTER_DATABASE
initialization parameter to false. After the upgrade, you must set this
initialization parameter back to true.

4. If the NLS_LENGTH_SEMANTICS initialization parameter is set to CHAR, then
set it to BYTE. This initialization parameter must be set back to CHAR after
the upgrade is complete.

On Windows platforms, change the BACKGROUND_DUMP_DEST and USER_DUMP_DEST
initialization parameters that point to RDBMS80 or any other environment
variable to point to the following directories instead:

Initialization Parameter
========================
Change Setting To
BACKGROUND_DUMP_DEST ORACLE_BASE\oradata\DB_NAME
USER_DUMP_DEST ORACLE_BASE\oradata\DB_NAME\archive

UNIX - Change location of dump files


3. Create these directories

user_dump_dest = /apps2/oracle/prodora/9.2.0/admin/udump
background_dump_dest = /apps2/oracle/prodora/9.2.0/admin/bdump
core_dump_dest = /apps2/oracle/prodora/9.2.0/admin/cdump

In the settings, substitute the complete ORACLE_BASE path for ORACLE_BASE
and substitute the database name for DB_NAME.

a. Make sure the COMPATIBLE initialization parameter is properly set for
Oracle9i. If COMPATIBLE is set below 8.1.0, then you will encounter the
following error when you attempt to start up your release 9.0.1 database
later in step 16:

ORA-00401: the value for parameter compatible is not supported by this
release

Either leave COMPATIBLE unset in your initialization parameter file or set
COMPATIBLE to 8.1.x.

b. If you are using Oracle Parallel Server, then modify the initdb_name.ora
file in the same way that you modified the initialization parameter file.

Make sure you save all of the files you modified after making these adjustments.

NOTE:
=====
For Oracle Parallel Server, perform this step on all nodes. Also, set the
CLUSTER_DATABASE initialization parameter to false. You can change it back
to true after the upgrade operation is complete.


4. If your operating system is UNIX, then make sure that the following
environment variables point to the new release 9.0.1 directories:

ORACLE_HOME, PATH, ORA_NLS33, LD_LIBRARY_PATH, ORACLE_SID

Example:
========
$ echo $ORACLE_HOME
/apps2/oracle/prodora/9.0.1
$ echo $PATH
/apps2/oracle/prodora/9.0.1/bin:/usr/ccs/bin:/usr/sbin:/usr/j2se/bin:
/usr/bin:/usr/sbin:/usr/dt/bin:/usr/ccs/bin:/usr/openwin/bin:/bin:
/usr/ucb:/usr/lib:.
$ echo $ORA_NLS33
$ echo $LD_LIBRARY_PATH
/apps2/oracle/prodora/9.0.1/lib:/usr/dt/lib:/usr/openwin/lib
$ echo $ORACLE_SID
PROD

NOTE:
=====
For Oracle Parallel Server, perform this step on all nodes.
See Also:

Your operating system-specific Oracle9i installation documents for information
about setting other important environment variables on your operating system.


5. Log in to the system as the owner of the Oracle home directory of the new
release. Set the environment.

$cd <9.2.0 ORACLE_HOME>
$ . .env


6. Change to the rdbms/admin directory and login into sqlplus as a user with
SYSDBA privileges.

$cd rdbms/admin
$ sqlplus /nolog
SQL> connect / as sysdba


7. Startup the database

You may need to use the PFILE option to specify the location of your
initialization parameter file.

You may see error messages listing obsolete initialization parameters. If so,
then make a note of the obsolete initialization parameters and continue with
the upgrade normally. Then, remove the obsolete initialization parameters the
next time you shut down the database.

SQL> Startup migrate
PFILE=/apps2/oracle/prodora/9.0.1/dbs/initPROD.ora


8. Set the system to spool results to a log file for later verification of
success.

SQL> SPOOL u0801070.log

If you want to see the complete detailed output of the script you will run,
then you can also issue a SET ECHO ON statement:

SQL> SET ECHO ON


9. Run uold_release.sql, where old_release refers to the release you had
installed prior to upgrading.

SQL> @u0801070.sql
(This script runs for a few hours)


10. Run the cmpdbmig.sql script to upgrade components that can be upgraded
while connected with SYSDBA privileges.

SQL> @cmpdbmig.sql

You will see output similar to the following:

COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle9i Catalog Views VALID 9.2.0.4.0
Oracle9i Packages and Types VALID 9.2.0.4.0
JServer JAVA Virtual Machine VALID 9.2.0.4.0
Oracle9i Java Packages VALID 9.2.0.4.0
Oracle XDK for Java UPGRADED 9.2.0.2.0
Oracle interMedia Text LOADED 8.1.7.4
Oracle9i Real Application Clusters INVALID 9.2.0.4.0
Oracle interMedia LOADED 8.1.7.0.0
Oracle Spatial LOADED 8.1.7.0.0

9 rows selected.


11. Turn off the spooling of script results to the log file

SQL> SPOOL OFF

Check spool file u0801070.log and verify that the packages and procedures
compiled successfully. Correct any problems you find in this file and rerun
the appropriate upgrade scripts if necessary. You can rerun any of the
scripts described in this chapter as many times as necessary.


12. Shut down and restart the instance to reinitialize the system parameters
for normal operation. The restart will also perform release 9.2 initialization
for JServer JAVA Virtual Machine and other components.

Your database is now upgraded to 9.2.0 release.


Section #4
==========

Modify init.ora parameters
==========================

1. Remove the following parameters from ifilecbo.ora if they exist

always_anti_join
always_semi_join
db_block_buffers
event="10943 trace name context forever, level 2"
event="38004 trace name context forever, level 1"
job_queue_interval
optimizer_percent_parallel
_complex_view_merging
_new_initial_join_orders
_optimizer_mode_force
_optimizer_undo_changes
_or_expand_nvl_predicate
_ordered_nested_loop
_push_join_predicate
_push_join_union_view
_use_column_stats_for_function
_unnest_subquery
_sortmerge_inequality_join_off
_table_scan_cost_plus_one
_always_anti_join
_always_semi_join
hash_area_size
sort_area_size
optimizer_mode
optimizer_index_caching
optimizer_index_cost_adj


2. Remove the following events only if you are using Oracle Applications
release 11.5.8 or later.

event="10932 trace name context level 32768"
event="10933 trace name context level 512"
event="10943 trace name context level 16384"


3. Add, verify or change the following Mandatory parameters in init.ora

Do not set the UNDO_% parameters yet; you will be directed to do so as part
of the System Managed Undo (SMU) instructions.

#################################################################
#
# Oracle Applications 11i common database
# initialization parameters
#
#################################################################

db_block_size = 8192 #MP

compatible = 9.2.0 #MP

_system_trig_enabled = true #MP

O7_DICTIONARY_ACCESSIBILITY = TRUE #MP

row_locking = always #MP

nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR #MP
nls_numeric_characters = ".,"
nls_sort = binary #MP
nls_comp = binary #MP

#audit_trail = true # if you want auditing
max_enabled_roles = 100 #MP

cursor_sharing = EXACT #MP

db_file_multiblock_read_count = 8 #MP
optimizer_max_permutations = 2000 #MP
query_rewrite_enabled = true #MP
_sort_elimination_cost_ratio = 5 #MP
_like_with_bind_as_equality = TRUE #MP
_fast_full_scan_enabled = FALSE #MP
_sqlexec_progression_cost = 2147483647 #MP

nls_length_semantics = BYTE #MP

workarea_size_policy = AUTO #MP

optimizer_features_enable = 9.2.0 #MP
_index_join_enabled = FALSE #MP
_b_tree_bitmap_plans = FALSE #MP


4. Shut down and restart the database instance once these changes have been
made.



Section 5
==========

Upgrade the Specific Database Components
=========================================
The following errors will appear as you startup the Database. While these
values are obsolete, Oracle Applications still searches for them. Please
ignore there errors.

ORA-25138: OPTIMIZER_PERCENT_PARALLEL initialization parameter has been made
obsolete
ORA-25138: ALWAYS_ANTI_JOIN initialization parameter has been made obsolete
ORA-25138: ALWAYS_SEMI_JOIN initialization parameter has been made obsolete


Upgrading Oracle Spatial
=========================

If you have linear referencing system (LRS) data, you must perform the steps
in Section A.5 regardless of whether or not you chose an automatic upgrade.

1. Connect to the database instance specifying AS SYSDBA.

SQL> connect / as sysdba


2. Grant the required privileges to the MDSYS user by running the following
procedure.

SQL> @$ORACLE_HOME/md/admin/mdprivs.sql


3. Connect as MDSYS.

SQL> connect mdsys/mdsys


4. Perform the migration by running the following procedure.

SQL> @$ORACLE_HOME/md/admin/c81Xu9X.sql

<< See Notes regarding Upgrading LRS Data >>

5. Exit sqlplus


Upgrading Oracle Text
=====================

1. Change to the ORACLE_HOME/ctx/admin directory.
$ cd $ORACLE_HOME/ctx/admin


2. Start SQL*Plus.
Sqlplus /nolog


3. Connect to the database instance as a user with SYSDBA privileges.

SQL> Connect / as sysdba


4. If the instance is running, shut it down using SHUTDOWN IMMEDIATE:

SQL> SHUTDOWN IMMEDIATE


5. Start up the instance in RESTRICT mode.

SQL> STARTUP RESTRICT
PFILE=/apps2/oracle/prodora/9.0.1/dbs/initPROD.ora


6. Set the system to spool results to a log file for later verification of
success.

SQL> SPOOL text_upgrade.log


7. Run s0900010.sql to grant new, required database privileges to user CTXSYS.

SQL> @s0900010.sql


8. Connect to the database instance as user CTXSYS.
sql> connect ctxsys/ctxsys


9. Run u0900010.sql

SQL> @u0900010.sql


10. Connect to the database instance as a user with SYSDBA privileges.

SQL> connect / as sysdba


11. Run s0902000.sql to grant new, required database privileges to user CTXSYS.

SQL> @s0902000.sql


12. Connect to the database instance as user CTXSYS.

SQL> connect ctxsys/ctxsys


13. Run u0902000.sql to upgrade the CTXSYS schema to release 9.2.

SQL> @u0902000.sql


14. Turn off the spooling of script results to the log file

SQL> SPOOL OFF


15. Shut down the instance

16. Exit SQL*Plus.


Upgrading interMedia
=====================

1. Startup the database

cd $ORACLE_HOME/ord/admin
sqlplus /nolog
connect / as sysdba
SQL> STARTUP PFILE=/apps2/oracle/prodora/9.0.1/dbs/initPROD.ora

2. Check Intermedia file

SQL> connect / as SYSDBA
SQL> @/ord/im/admin/imdbma.sql (on UNIX)
@\ord\im\admin\imdbma.sql (on Windows NT)
Returns: ord/im/admin/u0801070.sql

This script displays one of the following strings:

NOT_INSTALLED
INSTALLED
u080nnn0.sql


3. First upgrade Oracle interMedia Common Files.

SQL> @u0801070.sql
NOTE: This script may prompt for the password for ORDSYS. It is ORDSYS.
Password for ORDPLUGINS is ORDPLUGINS

4. Then upgrade interMedia.

SQL> @$ORACLE_HOME/ord/im/admin/u0801070.sql

NOTE: This script may prompt for passwords for ORDPLUGINS and ORDSYS.


5. Invoke imchk.sql to verify the upgrade:

SQL> Connect ORDSYS/ordsys
SQL> @/ord/im/admin/imchk.sql (on UNIX)
@\ord\im\admin\imchk.sql (on Windows NT)

6. Exit Sqlplus


Section 6
=========

Additional Upgrade tasks
========================

Recompile Invalid PL/SQL Modules
================================
The utlrp.sql script recompiles all existing PL/SQL modules that were
previously in an INVALID state, such as packages, procedures, types,and
so on. These actions are optional; however, they ensure that the cost of
recompilation is incurred during installation rather than in the future.

To run the utlrp.sql script, complete the following steps:

1. Change to the ORACLE_HOME/rdbms/admin and login as a user with sysdba privileges.

$ cd ../../../rdbms/admin
$ sqlplus /nolog
SQL> Connect / as SYSDBA


2.Run utlrp.sql or utlrcmp.sql
This script will take several hours to run.

SQL> @utlrp.sql



Enabling System Managed Undo (SMU)
==================================
As of 9iR1, Oracle Applications 11i supports only the use of system managed
undo (SMU). SMU is more efficient than traditional rollback segments and
reduces the possibilities of snapshot too old errors.

1. Change to the ORACLE_HOME/rdbms/admin and login as a user with sysdba
privileges.

$ cd ../../../rdbms/admin
$ sqlplus /nolog
SQL> Connect / as SYSDBA

2. Spool changes for SMU update and record information

SQL> spool smu_changes_info.txt
SQL> select segment_name, tablespace_name
from dba_rollback_segs;

SQL> select file_name, tablespace_name, bytes
from dba_data_files
where tablespace_name = '';

NOTE: is the tablespace name used by a rollback segment.


3. Drop all the private and public rollback segments except for the SYSTEM
rollback segment.

SQL> alter rollback segment offline;
SQL> drop rollback segment ;

Examples
========
alter rollback segment RBS01 offline;
drop rollback segment RBS01;
alter tablespace RBS offline;
drop tablespace RBS;


4. Drop the corresponding tablespace(s) where the rollback segments reside.

Example
========
SQL> alter tablespace RBS offline;
SQL> drop tablespace RBS;


5. Create a new System Managed Undo tablespace per instance as follows:

create undo tablespace APPS_UNDOTS1 datafile '' size
reuse extent management local ;

For the path and the size values, you should reuse the same files and sizes
which were used in the tablespaces which were dropped in step 2. The sizes
and file names were captured in step 2.

Example:
========
create undo tablespace APPS_UNDOTS1 datafile ‘/1158/oracle/vis1158data/rbs01.dbf’ size 100M reuse extent management local;

Check the creation with:
select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;

If you are not running Real Application Clusters (RAC), then only one
tablespace is needed. If you are using RAC, you will need to create one
undo tablespace per instance, and set undo_tablespace appropriately.

For example,
============
For instance 1, nd -

For instance 2, set undo_tablespace=APPS_UNDOTS2, etc..


6. Set the following database initialization parameters

undo_management = AUTO
undo_tablespace = APPS_UNDOTS1
undo_retention =
1800 = dev/test, 3600 less than 100 users, 7200= 101 to 500 users
10800=501 to 1000, 14400 = 1001-2000

Refer to the sizing table (Database Initialization Parameters Sizing section)
for the appropriate setting of undo_retention.


7. Remove the database initialization parameter rollback_segments.

#rollback_segments =(RBS01,RBS02,RBS03,RBS04,RBS05,RBS06)


Temporary Tablespace Setup
==========================
It is recommended that the temporary tablespace for Oracle Applications users
be created using locally maextent sizes of 128K.
The 128K extent size is recommended because numerous modules such as pricing
and planning make extensive use of global temporary tables which also reside
in the temporary tablespace. Since each user instantiates a temporary segment
for these tables, large extent sizes may result in space allocation failures.

The following is an example of creating a locally managed temporary tablespace
with temp files:

1) Drop the Temporary Tablespace
SQL> drop tablespace temp;


2) Recreate the new locally managed Temporary Tablespace

SQL> create temporary tablespace temp
tempfile '/1158/oracle/vis1158data/temp01.dbf' size 700M reuse
extent management local uniform size 128K;

3) Verify the following init.ora parameters

SHARED_POOL_SIZE > 150MB
JAVA_POOL_SIZE > 150MB
CLUSTER_PARAMETER=FALSE

4) Restart the database so that the system managed undo takes effect.


Section 7
=========

9.2.0.4 Post Install Actions
============================

1. Change to the ORACLE_HOME/rdbms/admin and connect to the database instance
AS SYSDBA

$ cd ../../../rdbms/admin
$ sqlplus /nolog
SQL> Connect / as SYSDBA


2. Check that there is at least 10MB free in the SYSTEM Tablespace

select nvl(sum(bytes)/1024,0) as "System Tablespace Avail(KB)"
from dba_free_space
where tablespace_name like 'SYSTEM';


3. MIGRATE Database to 9.2.0.4

SQL> startup migrate
SQL> spool catpatch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off
SQL> shutdown
SQL> startup

Output of catpatch.sql
=======================
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle9i Catalog Views VALID 9.2.0.4.0
Oracle9i Packages and Types VALID 9.2.0.4.0
JServer JAVA Virtual Machine VALID 9.2.0.4.0
Oracle9i Java Packages VALID 9.2.0.4.0
Oracle XDK for Java VALID 9.2.0.6.0
Oracle Text VALID 9.2.0.4.0
Oracle9i Real Application Clusters INVALID 9.2.0.4.0
Oracle interMedia VALID 9.2.0.4.0
Spatial VALID 9.2.0.4.0


4. Upgrade JAVA

cd <9.2.0> ORACLE_HOME
sqlplus /nolog
SQL> connect / as sysdba
SQL> @?/javavm/install/jvmsec3.sql
SQL> @?/javavm/install/jvmsec5.sql

Attention:
==========
When you run jvmsec3.sql and jvmsec5.sql, ignore the following failures:

# ORA-29532: Java call terminated by uncaught Java exception:
java.lang.SecurityException: policy table update
SYS:java.lang.RuntimePermission, loadLibrary.oraawt
# ORA-29532: Java call terminated by uncaught Java exception:
java.lang.SecurityException: policy table update
SYS:java.lang.RuntimePermission, loadLibrary.oraioser
# ORA-29532: Java call terminated by uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: EJBCLIENT is not a user or role

Your database is now upgraded to the new 9.2.0.4 release.


Applications 11i Post-Install actions
======================================

Implement or update AutoConfig
===============================
1. Migrate Autocongif to the DB Tier

. $APPL_TOP/APPSORa.env
cd $AD_TOP/bin
perl admkappsutil.pl
cp $APPL_TOP/admin/appsutil.zip <9.2.0 Oracle Home>
cd <9.2.0 Oracle Home>
unzip appsutil.zip
unset the applications environment
. <9.2.0 Oracle Home>/.env
cd appsutil/bin
adbldxml.sh appsuser=apps appspass=apps

2. Grant dictionary privileges to the SYSTEM, APPS, and MRC schemas

On the administration server node, use SQL*Plus to connect to the database
as APPS and run the $AD_TOP/patch/115/sql/adgrn9i.sql script with the
following parameters:

$ sqlplus apps/ @$AD_TOP/patch/115/sql/adgrn9i.sql


3. Apply Oracle Applications Object Library (FND) patch
Apply Patch 2838093 to the administration server node. This fixes the
package body DBMS_XMLQUERY.

4. Apply Oracle CRM Gateway for Mobile Devices patch (conditional)
If Oracle CRM Gateway for Mobile Devices (ASG) is licensed, apply patch
2637492 to the administration server node. This fixes the package body
ASG_REP_CONF.

5. Apply Oracle Contracts Core patch (conditional)
If Oracle Contracts Core (OKC) is licensed, apply Patch 2652080 to the
administration server node. This fixes the view OKC_QUE_RULES_V.

6. Apply AD patch (conditional)
If you are using Release 11.5.7 and have not already upgraded to AD patch G
or later, apply Patch 2361208 to the administration server node. This fixes
problems with the "Recreate grants and synonyms for APPS schema(s)" and
"Maintain Multiple Reporting Currencies schema(s)" tasks in AD Administration.

7. Re-create grants and synonym

On the administration server node, as the owner of the Applications file
system, run AD Administration and select the "Recreate grants and synonyms
for APPS schema(s)" task from the Maintain Applications Database Objects menu.

If MRC is implemented in your Applications system, then select the "Maintain
Multiple Reporting Currencies schema(s)" task afterwards. This will recreate
grants and synonyms for the MRC schema(s).

8. Apply latest certified Forms patchset

Use of Developer 6i with Oracle 9i requires at a minimum Forms Patch Set 6.
Instructions for installing Forms patch sets can be found on OracleMetaLink.
See Note 125767.1 Upgrading Developer 6i with Oracle Applications 11

How to compile fast formula from the server (via FFXBCP)

Oracle Human Resources
Oracle Payroll


Run the executable FFXBCP (from $FF_TOP/bin) with the following syntax:

1. To compile all formula
FFXBCP apps/ 0 Y %% %%

2. To compile all formula for a certain class i.e PAYMENT
FFXBCP apps/ 0 Y '' %%

3. To compile a specific formula
FFXBCP apps/ 0 Y %% ''

4. To compile all UNCOMPILED formula
FFXBCP apps/ 0 Y -U %% %%

How To Find Out Which Responsibility Was Granted To A Specific User

Run the following sql when prompted enter username

SELECT frvl.RESPONSIBILITY_NAME
FROM FND_USER_RESP_GROUPS_DIRECT furgd,
FND_RESPONSIBILITY_VL frvl, FND_USER fu
WHERE furgd.responsibility_id = frvl.responsibility_id AND
fu.user_id = furgd.user_id AND
( to_char(furgd.end_date) IS NULL
OR furgd.end_date > sysdate )
AND fu.user_name = '&user' ;

How to create database link in Oracle 9i

CREATE PUBLIC DATABASE LINK "RAF"
CONNECT TO "APPS"
IDENTIFIED BY "APPS"
USING '(description=(address=(protocol=TCP)
(host=server1)(port=1521))(connect_data=(sid=DEV)))'


Run the following from sqlplus to test your dblink
SELECT SYSDATE FROM DUAL@RAF

Fndload command for migrating fnd_users from one instance to another

Fndload command for migrating fnd_users from one instance to another

How to migrate users from one instance to another instance using FNDLOAD utility, steps to be run pre and post the clone process.

Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct /tmp/userlist.ldt FND_USER USER_NAME="RAF"

Upload

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct /tmp/userlist.ldt

If same user is not present in another instance then FNDLOAD will not migrate same password instead
user password will be changed to 'welcome'.
If same user is present in another instance then FNDLOAD will migrate same password.

Change your Oracle 11i apps password using FNDCPASS

Source your apps environment

Issue

FNDCPASS apps/test 0 Y system/manager SYSTEM APPLSYS apps


vi the following file and the change the password in there as well.

/ora111/dev/devora/iAS/Apache/modplsql/cfg/wdbsvr.app

Run the following script to see Database growth per month for last year

set pagesize 50000
"Database growth per month for last year"

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
/