Scripts

script for trimming alert log to 1 day and taking backup of prev day
Posted by Mohammed on April 23, 2010
#!/usr/bin/ksh

############################################################################
## Program : save_alert_log.sh #
## #
## Purpose : The alert logs on many Oracle databases can grow to a very #
## large size over time. This can often impede the maintenace#
## of the system – because the DBA will need to sometimes scan#
## through many days or months of data when researching an #
## issue. This script tries to avoid that by ensuring that #
## the log file can be “refreshed” on a daily basis, meaning #
## that only the current day’s data will be kept in the log, #
## while the previous day’s data will be saved to another file#
## in a backup area. #
## #
## This script should be run from Oracle’s crontab at midnight#
## every night, so that the database will always have a new #
## alert log file each day. An example crontab entry could be#
## 0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
## #
## Date : 19 May 2006. #
## Author : Basil S. Mullings #
############################################################################
## Modified: #
## #
## #
# Modification History: #
# DATE WHO DESC #
# ——– —– —————————————————-#
# 05/29/06 Basil Add an extra variable LOG_KEEP_DAYS to hold the #
# number of days that the log files should be kept on #
## the server before being deleted. #
## #
## #
############################################################################

##Setup some needed variables.
BKUP=bkup ##The backup directory to store the logs…
ORATAB=”/etc/oratab”
LOG_KEEP_DAYS=365 ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog ##Just a temp scratch work area.
SQLUSER=”/ as sysdba”
GEN_ORA_ERROR=”ORA\-[0-9][0-9]*”
PATH=”$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:.”
export PATH

## Now, parse the oratab file for all databases on the system.
## Then use the ORACLE_SID that is found in the oratab file
## to log onto that database, and retrieve the directory where
## the alter log file is stored (.ie. retrieve the path to the
## bdump directory.
##
#for sidEntry in `cat $ORATAB | grep -v “^#”`
for sidEntry in `cat $ORATAB | awk -F: ‘{print $1}’ | grep -v “^#”`
do
## Get date and time
CURR_DATE=`date ‘+%a_%m%d%H%M’` ##Example Fri_05191256 for Friday May 19th @1256 PM.

#ORACLE_SID=`echo $sidEntry | cut -f 1 -d :`
ORACLE_SID=$sidEntry
echo “Oracle Sid is $ORACLE_SID”

export ORACLE_SID
## Set the Oracle environment for this SID.
ORAENV_ASK=NO
. /usr/local/bin/oraenv
rm -f $TMPFILE > /dev/null 2>&1

##Now, let’s log onto the DB, and try to
##retrieve the bdump directory path.
sqlplus -s /nolog << EOF > $TMPFILE
connect $SQLUSER
set heading off;
set echo off;
set feedback off;

select ‘BACKGROUND_DUMP_DEST=’ ||value
from v\$parameter
where name=’background_dump_dest’;
exit;
EOF

##Ok, we had a problem talking to the database.
if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
then
echo “ERROR: Unable to find the path to the alert log for DB $ORACLE_SID”
rm -f $TMPFILE > /dev/null 2>&1

else ##Ok, we can log into the DB, now let’s go find our bdump directory.

bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F “=” ‘{print $2}’`
#echo “BDUMP is $bdump”
bkupDir=$bdump/$BKUP

##Make sure our backup directory exists.
if [ ! -d $bkupDir ]
then
mkdir $bkupDir > /dev/null 2>&1
fi

##Now, move the alert log.
#echo “now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE”
mv $bdump/alert_${ORACLE_SID}.log $bkupDir/alert_${ORACLE_SID}.$CURR_DATE

#Procedure to shrink the log to 365 days
##Keep only the last 365 days worth of logs…delete all logs older than 365 days.
#echo “Now shrinking the logs in dir $bkupDir …”
find $bkupDir -name “*.*” -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
fi

done
Posted in Administration, Scripts | Leave a Comment »
Good article on Response time Analysis
Posted by Mohammed on April 19, 2010
As DBA’s, one of our primary goal is to achieve optimal response time for the queries. But many a times we would be wondering how we can analyze whether a particular response time is optimal or not. If it is high, what issues are causing this large value etc….
For all such questions, here is one article for you which presents analyzing response time in a wonderful way.
http://www.oracle.com/technology/pub/articles/schumacher_analysis.html
It helped me alot in finding root causes in a performance problem in one of my database. Hope it will be same in your case….
Posted in Performance Tuning | Tagged: avg response time for queries, optimal response time | Leave a Comment »
script to list top 20 fragmented tables
Posted by Mohammed on December 31, 2009
SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 – 23 * INI_TRANS) *
(1 – PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 – 23 * INI_TRANS) * (1 – PCT_FREE / 100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) “WASTED_MB”
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE ‘%OWNER%’ AND
PARTITIONED = ‘NO’ AND
(IOT_TYPE != ‘IOT’ OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;
Posted in Performance Tuning, Scripts | Tagged: fragmentation, fragmented tables, script for fragmentation | Leave a Comment »
Script to check memory consumption by session
Posted by Mohammed on December 31, 2009
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING ‘SID’
COLUMN oracle_username FORMAT a12 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a9 HEADING ‘O/S User’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a8 HEADING ‘Machine’ JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory’) session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory max’) session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory’) session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory max’) session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/
or
SET LINESIZE 200
SET PAGESIZE 500
SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
SET TRIMSPOOL ON
COL “SESSION” FORMAT A50
COL “PID/THREAD” FORMAT A10
COL “ CURRENT SIZE” FORMAT A18
COL “ MAXIMUM SIZE” FORMAT A18
REM Setting user variables values
SET TERMOUT OFF
DEFINE sort_order = 3
DEFINE show_pga = ‘ON’
DEFINE show_uga = ‘ON’
COL sort_column NEW_VALUE sort_order
COL pga_column NEW_VALUE show_pga
COL uga_column NEW_VALUE show_uga
COL snap_column NEW_VALUE snap_time
SELECT nvl(:sort_choice, 3) “SORT_COLUMN”
FROM dual
/
SELECT nvl(:pga_choice, ‘ON’) “PGA_COLUMN”
FROM dual
/
SELECT nvl(:uga_choice, ‘ON’) “UGA_COLUMN”
FROM dual
/
SELECT to_char(sysdate, ‘YYYYMMDD_HH24MISS’) “SNAP_COLUMN”
FROM dual
/
REM Creating new snapshot spool file
SPOOL MEMORY_&snap_time
REM Showing PGA statistics for each session and background process
SET TERMOUT &show_pga
PROMPT
PROMPT :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::
SELECT to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) || ‘: ‘
|| nvl(lower(ssn.machine), ins.host_name) “SESSION”,
to_char(prc.spid, ’999999999′) “PID/THREAD”,
to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ CURRENT SIZE”,
to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ MAXIMUM SIZE”
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins
WHERE se1.statistic# = 20
AND se2.statistic# = 21
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
ORDER BY &sort_order DESC
/
REM Showing UGA statistics for each session and background process
SET TERMOUT &show_uga
PROMPT
PROMPT :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::
SELECT to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) || ‘: ‘
|| nvl(lower(ssn.machine), ins.host_name) “SESSION”,
to_char(prc.spid, ’999999999′) “PID/THREAD”,
to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ CURRENT SIZE”,
to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “ MAXIMUM SIZE”
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins
WHERE se1.statistic# = 15
AND se2.statistic# = 16
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
ORDER BY &sort_order DESC
/
REM Showing sort information
SET TERMOUT ON
PROMPT
BEGIN
IF (&sort_order = 1) THEN
dbms_output.put_line(‘Ordered by SESSION’);
ELSIF (&sort_order = 2) THEN
dbms_output.put_line(‘Ordered by PID/THREAD’);
ELSIF (&sort_order = 3) THEN
dbms_output.put_line(‘Ordered by CURRENT SIZE’);
ELSIF (&sort_order = 4) THEN
dbms_output.put_line(‘Ordered by MAXIMUM SIZE’);
END IF;
END;
/
REM Closing current snapshot spool file
SPOOL OFF
REM Showing the menu and getting sort order and information viewing choice
PROMPT
PROMPT Choose the column you want to sort: == OR == You can choose which information to see:
PROMPT … 1. Order by SESSION … 5. PGA and UGA statistics (default)
PROMPT … 2. Order by PID/THREAD … 6. PGA statistics only
PROMPT … 3. Order by CURRENT SIZE (default) … 7. UGA statistics only
PROMPT … 4. Order by MAXIMUM SIZE
PROMPT
ACCEPT choice NUMBER PROMPT ‘Enter the number of your choice or press to refresh information: ‘
VAR sort_choice NUMBER
VAR pga_choice CHAR(3)
VAR uga_choice CHAR(3)
BEGIN
IF (&choice = 1 OR &choice = 2 OR &choice = 3 OR &choice = 4) THEN
:sort_choice := &choice;
:pga_choice := ‘&show_pga’;
:uga_choice := ‘&show_uga’;
ELSIF (&choice = 5) THEN
:sort_choice := &sort_order;
:pga_choice := ‘ON’;
:uga_choice := ‘ON’;
ELSIF (&choice = 6) THEN
:sort_choice := &sort_order;
:pga_choice := ‘ON’;
:uga_choice := ‘OFF’;
ELSIF (&choice = 7) THEN
:sort_choice := &sort_order;
:pga_choice := ‘OFF’;
:uga_choice := ‘ON’;
ELSE
:sort_choice := &sort_order;
:pga_choice := ‘&show_pga’;
:uga_choice := ‘&show_uga’;
END IF;
END;
/
Posted in Performance Tuning, Scripts | Tagged: memory consumption, script, session memory consumption | Leave a Comment »
Queries to know how many archives generated
Posted by Mohammed on December 31, 2009
# to know archives generated in a month day wise
select to_char(first_time,’DD-MON’),count(*) from v$log_history
where to_char(first_time,’MON’)=’DEC’
group by to_char(first_time,’DD-MON’);
# to know archives generated in a day
select to_char(first_time,’DD-MON’),count(*) from v$log_history
where to_char(first_time,’DD’)=10
group by to_char(first_time,’DD-MON’);

# to know archives generated in a day including time
select to_char(first_time,’DD-MON:hh24:mi:ss’) from v$log_history
where to_char(first_time,’DD’)=10;

# to know archives generated on specific date in hourly basis
select to_char(first_time,’hh24′),count(*) from v$log_history
where to_char(first_time,’dd-mm-yy’) in(’21-05-08′)
group by to_char(first_time,’hh24′);

Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications

R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications [ID 438652.1]

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

Modified 06-MAR-2008 Type TROUBLESHOOTING Status PUBLISHED

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
References



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



Applies to:
Oracle Applications Technology Stack - Version: 12.0 to 12.1
Information in this document applies to any platform.
Forms in R12
Purpose
The Oracle Applications Release 12 technology stack introduces major enhancements to some of the most commonly used technologies in Oracle Applications, including the HTTP listener, Java deployments, Forms and Reports. The purpose of this document is to give an insight on tracing and diagnostics tools that can aid in diagnosis and detection of various anomalies and errors with forms in Oracle Applications Release.12i

Last Review Date
September 10, 2007
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
Release 12 now includes Oracle Application Server (OracleAS) 10g 10.1.2 as the foundation for Forms, Reports, and C code. This replaces the 8.0.6-based ORACLE_HOME that 9iAS 1.0.2.2 provides today in Release 11i.

This document is an introduction to the Forms Runtime Diagnostics, Forms Trace diagnostics utility and Servlet logging tools available in Oracle Applications Release. 12, and includes the following sections:

Section 1: Identify if forms is implemented in socket /servlet mode

Section 2: Forms Runtime Diagnostics (FRD)

Section 2.1: Activating Forms Runtime Diagnostics (FRD)
Section 2.2: Trace file location
Section 2.3: Sample output


Section 3: Forms Trace Diagnostics Utility

Section 3.1: Activating Forms trace
Section 3.2: Trace file location
Section 3.3: Viewing Forms trace output
Section 3.4: Sample output


Section 4: Servlet logging

Section 4.1: Activating logging
Section 4.2: Log file location
Section 4.3: Sample output



Section 1: Identify if forms is implemented in socket /servlet mode

--------------------------------------------------------------------------------
Oracle Applications Release12 by default configures Forms 10g stack in servlet mode, as this is the preferred and recommended deployment model for deploying forms on the web. In this mode, there is a java servlet called the Forms Listener Servlet, manages the communication between the Forms Java Client and OracleAS Forms Services. The Forms Listener Servlet architecture operates through the HTTP server port alone and does not need extra ports to handle communication between the client and the application server. The servlet architecture is designed to work with industry standards, and also to be fully supported for advanced network configurations with no additional ports or SSL configuration.

Although forms servlet is the preferred deployment method, there may be circumstances where customers need to switch from Forms servlet mode to socket mode, which allows desktop clients to access the Forms server directly.

To check if forms is implemented in socket /servlet mode perform the following steps:

Step 1: Source the middle-tier environment file from /apps/apps_st/appl directory
Note: Replace with the base directory where Oracle Application R12 is installed in your system

Step 2: Open the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE" and check the values for following parameters:

serverURL=
connectMode=
Note: Refer the table below for the values that they must contain based on the implementation mode

In servlet mode In Socket mode
serverURL=/forms/lservlet
connectMode=servlet serverURL=(should be blank)
connectMode=Socket

Step 3: Based on the values for these two parameters one should be able to determine if forms is implemented in socket /servlet mode


Section 2: Forms Runtime Diagnostics (FRD)
--------------------------------------------------------------------------------
The Forms Runtime Diagnostic (FRD) is a method for capturing all events that occur in a form session and writing a file with the details of those events. There is overhead in writing this file, so you should only use this for the purposes of development and debugging

When a form is run with FRD enabled, a combination of external user-application interactions and internal Forms processing events are written in chronological order to a log on the file system.

Section 2.1: Activating Forms Runtime Diagnostics (FRD)

Forms Runtime Diagnostics (FRD) can be enabled /activated in one of the following ways:

Option 1: Obtain FRD Trace Using Profile Options
Option 2: Obtain FRD Trace Using Appsweb.cfg
Option 3: Obtain FRD in an ADHOC way

Option 1: Obtain FRD Trace Using Profile Options

Method 1: Using profile option 'ICX: Forms Launcher'


When accessing Applications through the 'Home Page' (the page resultant after successful login) there is no way to pass command line parameters such as record=all, etc. for the duration of a single session. This method enables users to run FRD tracing through the 'Home Page' by using the profile option 'ICX: Forms Launcher'.

To start tracing in this way, modify Profile Option 'ICX: Forms Launcher' at USER level to include the Forms parameters required for FRD logging:

Note:
* It is recommended to pass FRD variables at the USER level and not at the SITE level since SITE level affects all users.

* If the 'ICX: Forms Launcher' profile option is not updatable at the USER level, then you may need to access 'Application Developer' to set the option 'Updatable' for the profile 'ICX_FORMS_LAUNCHER'






Step 1: Set the profile option 'ICX: Forms Launcher' at user level to same as site value
E.g. copy the site level value for profile option 'ICX: Forms Launcher' and paste it in user level field so as to set it at the user level


Step 2: Append the user value of ICX: Forms Launcher with the Forms parameters for FRD i.e '?record=collect'

E.g.
In Servlet mode In Socket mode
http://hostname.domain:port/forms/frmservlet?record=collect http://hostname.domain:port/OA_HTML/frmservlet?record=collect


replace hostname, domain and port as applicable

Step 3: Save this change at the USER level.
Step 4: For trace file details and location, refer Section 2.2 Trace file location.


Note:
You may need to bounce Apache for profile level changes to take effect

Step 4: Login into Oracle Applications and launch forms via self-service


Method 2: Using profile option 'Forms Runtime Parameters'


When accessing Applications through the 'Home Page' (the page resultant after successful login) there is no way to pass command line parameters such as record=all, etc. for the duration of a single session. This method enables users to run FRD tracing through the 'Home Page' by using the profile option 'Forms Runtime Parameters'


To start tracing in this way, modify Profile Option 'Forms Runtime parameters' at USER level to include the Forms parameters required for FRD logging:

Note:
* It is recommended to pass FRD variables at the USER level and not at the SITE level since SITE level affects all users.

* If the 'Forms Runtime Parameters' profile option is not updatable at the USER level, then you may need to access 'Application Developer' to set the option 'Updatable' for the profile 'Forms Runtime Parameters'


Step 1: Set the profile option 'Forms Runtime Paramter' with a value of 'record=collect' at user level (without quotes)

Step 2: Save this change and logout of applications


Note:
You may need to bounce Apache for profile level changes to take effect

Step 3: Login into Oracle Applications and launch forms via self-service

Step 4: For trace file details and location, refer Section 2.2: Trace file location.


Option 2: Obtain FRD Trace Using Appsweb.cfg

When accessing Applications through the 'Home Page' (the page resultant after successful login) there is no way to pass command line parameters such as record=all, etc. for the duration of a single session. It is also possible to obtain FRD trace by using Appsweb.cfg file.

To start tracing in this way, please refer to the steps below:

Step 1: Edit the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE"

Step 2: Forms parameters for FRD can be set in appsweb.cfg file in one of the following ways :


Method 1:
Look for "record=" parameter after the 'ENVIRONMENT SPECIFIC PARAMETERS' section and set it to collect as shown below.



Once done login into Oracle Applications and launch forms via self-service.


Extracts from appsweb.cfg file :

; ********************************
; ENVIRONMENT SPECIFIC PARAMETERS
; ********************************
; These parameters describe the main production environment.
; They have to be updated after every patching of this file.
;
; Forms Server Information: servlet, port, machine name and domain
; ----------------------------------------------------------------
serverURL=/forms/lservlet
; If you are using the Forms Servlet, uncomment the line that sets a value
; for the ServerURL and comment the line that that sets serverURL to
; no value. Lines are commented by inserting a semi-colon at line's beginning.
;
# Forms runtime argument: whether to run in debug mode
debug=no
# Other Forms runtime arguments: grouped together as one parameter.
# These settings support running and debugging a form from the Builder:
otherparams=buffer_records=%buffer% debug_messages=%debug_messages% array=%array% obr=%obr% query_only=%query_only%
quiet=%quiet% render=%render% record=%record% tracegroup=%tracegroup% log=%log% term=%term%
# Sub argument for otherparams
buffer=no
# Sub argument for otherparams
debug_messages=no
# Sub argument for otherparams
array=no
# Sub argument for otherparams
obr=no
# Sub argument for otherparams
query_only=no
# Sub argument for otherparams
quiet=yes
# Sub argument for otherparams
render=no
# Sub argument for otherparams
record=collect



For trace file details and location, refer Section 2.2: Trace file location.


OR

Method 2:
Update appsweb.cfg by adding the required Forms parameters for FRD

E.g.
[debug1]
record=collect

Logon to Applications 12i and start FRD trace by accessing the forms URL directly or updating profile option ICX: Forms Launcher by appending
'?config=debug1' as below

In Servlet mode In Socket mode
http://hostname.domain:port/forms/frmservlet?config=debug1 http://hostname.domain:port/OA_HTML/frmservlet?config=debug1

replace hostname, domain and port as applicable



Note:
Login into applications via forms directly using above URL's is not supported but can be used for basic troubleshooting purpose on recommendation of Oracle Support



For trace file details and location, refer Section 2.2: Trace file location.

Option 3: Obtain FRD in an ADHOC way

The steps below show how to create an FRD trace in an ADHOC way. This is not a supported or preferred method of tracing, however, it may be useful to use this method for ad hoc and basic troubleshooting purposes.

Step 1: Logon to Applications 12i and start FRD trace by accessing the forms URL directly and appending '?record=collect' as below

In Servlet mode In Socket mode
http://hostname.domain:port/forms/frmservlet?record=collect http://hostname.domain:port/OA_HTML/frmservlet?record=collect

replace hostname, domain and port as applicable


Note:
Login into applications via forms directly using above URL's is not supported but can be used for basic troubleshooting purpose on recommendation of Oracle Support



Step 2:For trace file details and location, refer Section 2.2: Trace file location.


Section 2.2: Trace file location

The FRD log file will be written in the directory pointed by environment variable $FORMS_TRACE_DIR. By default, the trace file with name collect_ gets written in $FORMS_TRACE_DIR , where is the process identifier.This is a simple text file and can be viewed directly.


Note:
Unlike in Release 11i, you can no longer create a trace file in any ad hoc directory by using '&log=' directive like '&log=/usr/tmp/user1.log'.In R12, the FRD log file will be written in the directory pointed by environment variable $FORMS_TRACE_DIR and one can specify the log filename with '.log'extension in one of the following ways:

1. In appsweb.cfg:
Look for "log=" parameter after the 'ENVIRONMENT SPECIFIC PARAMETERS' section and set it with name that you want the trace file to be created with.

E.g
log=user1.log

2. Using +log directive in the URL used to set the frd trace via profile options or in ad hoc way

E.g
http://hostname.domain:port/forms/frmservlet?record=collect+log=user1.log
http://hostname.domain:port/OA_HTML/frmservlet?record=collect+log=user1.log


Section 2.3: Sample output

When a form is run with FRD enabled, a combination of external user-application interactions and internal Forms processing events are written in chronological order to a log file. These events can be analyzed to determine user actions and corresponding system responses, which aid in problem diagnosis and issue resolution. Kindly refer below display for a brief extract from a frd log file.


Extracts from a frd log file :

File Name: /oracle1/PROD/inst/apps/PROD_vkaria/logs/ora/10.1.2/forms/collect_32188
Process ID: 32188
Client IP: 152.69.172.99
Forms 10.1 (Forms Runtime) Version 10.1.2.0.2 (Production)
PL/SQL Version 10.1.0.5.0 (Production)
Oracle Virtual Graphics System Version 10.1.2.0.0 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.4.0 Production


Opened file: /oracle1/PROD/apps/apps_st/appl/fnd/12.0.0/forms/US/FNDSCSGN.fmx

ON-LOGON Trigger Fired:
Form: FNDSCSGN

State Delta:
FNDSCSGN, 1, Trigger, Entry, 2016355936, ON-LOGON

FNDSCSGN, 2, Prog Unit, Entry, 2017421936, /FNDSCSGN-1/P53_04_JAN_200703_41_43

FNDSCSGN, 3, Prog Unit, Entry, 2018075936, /FNDSCSGN-1/DO_LOGON


Executing DEFAULT_VALUE Built-in:
In Argument 0 - Type: String Value: NULL
In Argument 1 - Type: String Value: GLOBAL.FNDSCSGN_UNAME

Executing GET_APPLICATION_PROPERTY Built-in:
In Argument 0 - Type: Number Value: 73
Out Argument 0 - Type: String Value: NULL


Section 3: Forms Trace Diagnostics Utility

--------------------------------------------------------------------------------
Forms Trace allows you to record information about a precisely defined part of forms functionality or a class of user actions. It provides detailed data collection and other features to assist the user in diagnosing and investigating forms runtime problems

Additional to old FRD logging, forms trace promises to have more structured logging with additional event which can be traced. Forms Trace works by assigning an event id to each traceable event; for example, event 66 = Trigger Start & End, event 41 = Window Close. For a complete list of events, refer to Oracle® Application Server Forms Services Deployment Guide, Section 8, Tracing and Diagnostics. These events can be pooled into a trace group to provide customized output that suits the problem being investigated.

Section 3.1: Activating Forms trace

In addition to activating options as suggested in Note:373548.1 - Using Forms Trace in Oracle Applications Release 12, you can also activate forms trace using :


Option 1: Obtain FRD Trace Using Appsweb.cfg
Option 2: Obtain FRD in an ADHOC way

Option 1: Obtain Forms Trace Using Appsweb.cfg

When accessing Applications through the 'Home Page' (the page resultant after successful login) there is no way to pass command line parameters such as record=all, etc. for the duration of a single session. It is also possible to obtain FRD trace by using appsweb.cfg file.

To start tracing in this way, please refer to the steps below:

Step 1: Edit the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE"

Step 2: Forms trace can be set in appsweb.cfg file in one of the following ways :


Method 1:
Look for "record=" parameter after the 'ENVIRONMENT SPECIFIC PARAMETERS' section and set it to form as shown below. This will activate Forms Trace. By default, it will trace errors only. To utilize the full flexibility of Forms Trace, the record=forms parameter must be used in conjunction with the 'tracegroup' parameter. This parameter defines specific events to be traced


The tracegroup parameter can be:
A list of events, E.g. tracegroup=1,2,3,4,5.
A range, E.g. tracegroup=1-5
A keyword that is specified in the file ftrace.cfg, E.g. tracegroup=mygroup.
A combination of some or all of the above, E.g. tracegroup=0-3,34,67,mygroup.

Exhibit:1
With the many trace events available, a trace file could quickly become a maze. After testing various combinations, Applications Development has created several recommended levels of tracing, which are predefined in the file $ORA_CONFIG_HOME/10.1.2/forms/server/ftrace.cfg:

light (0-71,192): Records user actions, error messages and form service events.
medium (0-97,192,194,195): Includes events in the light tracegroup also captures built-in and user-exit events.
full (0-98,100-199): Captures all information that is currently available through Forms Tracing.
dbsql (32,64,98,100,101,136,137,192): All events related to the database and forms interaction with it
network (64,32,128,129,131,130,132,133,134,192): All events related to communications between client tier and Forms server.

Complete list of traceable events is available in
Oracle® Application Server Forms Services Deployment Guide, Section 8, Tracing and Diagnostics, Table 8-2, "List of Traceable Events"



Once done login into Oracle Applications and launch forms via self-service


Extracts from appsweb.cfg file :

; ********************************
; ENVIRONMENT SPECIFIC PARAMETERS
; ********************************
; These parameters describe the main production environment.
; They have to be updated after every patching of this file.
;
; Forms Server Information: servlet, port, machine name and domain
; ----------------------------------------------------------------
serverURL=/forms/lservlet
; If you are using the Forms Servlet, uncomment the line that sets a value
; for the ServerURL and comment the line that sets serverURL to
; no value. Lines are commented by inserting a semi-colon at line's beginning.
;
# Forms runtime argument: whether to run in debug mode
debug=no
# Other Forms runtime arguments: grouped together as one parameter.
# These settings support running and debugging a form from the Builder:
otherparams=buffer_records=%buffer% debug_messages=%debug_messages% array=%array% obr=%obr% query_only=%query_only%
quiet=%quiet% render=%render% record=%record% tracegroup=%tracegroup% log=%log% term=%term%
# Sub argument for otherparams
buffer=no
# Sub argument for otherparams
debug_messages=no
# Sub argument for otherparams
array=no
# Sub argument for otherparams
obr=no
# Sub argument for otherparams
query_only=no
# Sub argument for otherparams
quiet=yes
# Sub argument for otherparams
render=no
# Sub argument for otherparams
record=collect
# Sub argument for otherparams
tracegroup=medium



For trace file location and details, refer Section 3.2: Trace File location


OR

Method 2:
Update appsweb.cfg by adding the required Forms parameters for FRD

E.g.
[debug1]
record=forms
tracegroup=medium


The tracegroup parameter can be:

A list of events, E.g. tracegroup=1,2,3,4,5.
A range, E.g. tracegroup=1-5
A keyword that is specified in the file ftrace.cfg, E.g. tracegroup=mygroup.

For more information on tracegroups, please refer Exhibit 1 in Method 1 of Section 3
Login to Applications R12 and start Forms trace by accessing the applications via forms URL directly or updating the value of profile option ICX: Forms Launcher by appending '?config=debug1' as below

In Servlet mode In Socket mode
http://hostname.domain:port/forms/frmservlet?config=debug1 http://hostname.domain:port/OA_HTML/frmservlet?config=debug1

replace hostname, domain and port as applicable


Note:
Login into applications via forms directly using above URL's is not supported but can be used for basic troubleshooting purpose on recommendation of Oracle Support


For trace file location and details, refer Section 3.2: Trace File location


Option 2: Obtain Forms trace in an ADHOC way

The steps below show how to create an forms trace in an ADHOC way. This is not a supported or preferred method of tracing, however, it may be useful to use this method for ad hoc and basic troubleshooting purposes.

Step 1: Login to Applications R12 and start FRD trace by accessing the forms URL directly and appending '?record=forms tracegroup=full' as below

The tracegroup parameter can be:

A list of events, E.g. tracegroup=1,2,3,4,5.
A range, E.g. tracegroup=1-5
A keyword that is specified in the file ftrace.cfg, E.g. tracegroup=mygroup.

For more information on tracegroups, please refer Exhibit 1 in Method 1 of Section 3
In Servlet mode In Socket mode
http://hostname.domain:port/forms/frmservlet?record=forms tracegroup=full http://hostname.domain:port/OA_HTML/frmservlet?record=forms tracegroup=full


Note:
Login into applications via forms directly using above URL's is not supported but can be used for basic troubleshooting purpose on recommendation of Oracle Support

Step 2: For trace file location and details, refer Section 3.2: Trace File location


Section 3.2: Trace file location

The forms trace file will be written in the directory pointed by environment variable $FORMS_TRACE_DIR. By default, the trace file with name forms_.trc gets written in $FORMS_TRACE_DIR , where is the process identifier. Trace data is stored in a binary file with a *.trc extension and one will need to use the Translate utility to view the trace output.



Note:
Unlike in Release 11i, you can no longer create a trace file in any ad hoc directory by using '&log=' directive like '&log=/usr/tmp/user1.log'.In R12, the FRD log file will be written in the directory pointed by environment variable $FORMS_TRACE_DIR and one can specify the log filename with '.trc' extension in one of the following ways:

1. In appsweb.cfg:
Look for "log=" parameter after the 'ENVIRONMENT SPECIFIC PARAMETERS' section and set it with name that you want the trace file to be created with.

E.g
log=user1.trc

2. Using +log directive in the URL used to set the forms diagnostics trace via profile options or in ad hoc way

E.g
http://hostname.domain:port/forms/frmservlet?record=forms+log=user1.trc tracegroup=full
http://hostname.domain:port/OA_HTML/frmservlet?record=forms+log=user1.trc tracegroup=full



Section 3.3: Viewing Forms trace output

To view the binary data one needs to convert it to a readable format using Translate utility. The Translate utility converts trace data to XML or HTML formats. You'll need to specify an additional parameter "OutputClass" which has two legal values: "WriteOut" and "WriteOutHTML". If you use "WriteOut", the output file will be in XML format. If you use "WriteOutHTML", the output file will in HTML format.These two values ("WriteOut" and "WriteOutHTML") are case-sensitive.

To perform the translation you will need access to the Java executable, and either a Forms 10.1.2 Home or the Forms Developer Suite. The command used to translate is as shown below:

E.g

To convert the binary file into XML format:

/bin/java -cp <10.1.2 ORACLE_HOME>/forms/java/frmxlate.jar oracle.forms.diagnostics.Xlate datafile=$1.trc outputfile=$1.xml outputclass=WriteOutTo convert the binary file into HTML format:

/bin/java -cp <10.1.2 ORACLE_HOME>/forms/java/frmxlate.jar oracle.forms.diagnostics.Xlate datafile=$1.trc outputfile=$1.html outputclass=WriteOutHTML


Note:
* Replace $1.trc with the trace file name as applicable
* Replace$1.xml /$1.html with the filename you wish to have the translated data into
* The XML format will include values that are not visible in the HTML output file. We therefore recommend using the XML formatted output for Oracle Applications
R12.

Section 3.4: Sample output

Kindly refer below display for a brief extract from a forms trace file (XML translated) wherin one can see trace events and assignment of event id to each traceable event.

Extracts from a forms trace file (XML translated):


/oracle1/PROD/inst/apps/PROD_vkaria/logs/ora/10.1.2/forms/forms_5035.trc
-
1
32
50
-

USER_START
5035
152.69.172.99
12-JUN-2007 15:36:35
vkaria.idc.oracle.com
network

..
..
..
..

Forms 10.1 (Forms Runtime) = Version 10.1.2.0.2 (Production)
-
2
128
70

-
3
129
70

-
4
133
70
-

1
0

-

1
FNDSCSGN(1)

-




Section 4: Servlet logging
--------------------------------------------------------------------------------

In addition to Forms Runtime Diagnostics (FRD) and Forms Trace, forms administrators can also enable servlet logging for forms implemented in servlet mode. Servlet logging options enable forms administrators in :

Recording of all Oracle Forms sessions, including session start and end times, and the user's IP address and host name (session-level logging)

Monitoring of Oracle Forms-related network traffic and performance (session-performance and request-performance-level logging)
Generating debugging information for site configuration issues (debug-level logging)
Supported Logging parameters are as shown below:
(none) No log messages are produced. However, during Forms Servlet initialization, a message is written to the log file stating the name and path of the configuration file being used.
/session Log messages are written whenever a Forms session starts or ends. These give the host name and IP address of the client (the computer on which the user's web browser is running), the runtime process id, and a unique internal session id number.
/sessionperf Performance summary statistics are included with the session end message.
/perf A performance message is written for every request from the client.
/debug Full debug messages. Other debug messages are written in addition to the messages mentioned above. This logging level is very verbose and is intended mainly for debugging and support purposes.


Section 4.1: Activating logging

Servlet logging can be enabled as shown below :

Option 1: Obtain Servlet logging Using Appsweb.cfg

Step 1: Edit the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE"

Step 2: Set the forms parameters for servlet logging (either /session , /sessionperf, /perf, /debug) in appsweb.cfg file as shown below :

Extracts from appsweb.cfg file :

; ********************************
; ENVIRONMENT SPECIFIC PARAMETERS
; ********************************
; These parameters describe the main production environment.
; They have to be updated after every patching of this file.
;
; Forms Server Information: servlet, port, machine name and domain
; ----------------------------------------------------------------
serverURL=/forms/lservlet/session



Step 3: For trace file location and details, refer Section 4.2: Log File location


Section 4.2: Log file location

The servlet log file is application.log. It is written to the application-deployments/formsapp directory of the OC4J instance to which Forms is deployed.
In Oracle Applications:

$LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log

Section 4.3: Sample output

•


Kindly refer below for the sample output from application.log file with servlet logging enabled. With no logging enabled, no log messages will be recorded except an entry stating the name and path of the configuration file being used .

Extracts from application.log file with servlet logging enabled:

07/06/23 16:01:02.26 formsweb: =============== ListenerServlet ===============
07/06/23 16:01:02.31 formsweb: GET request received, cmd=getinfo, qstring=ifcmd=getinfo&ifhost=VKARIA-lap&ifip=111.144.104.113
07/06/23 16:01:02.31 formsweb: Existing servlet session, id = 9845d02755fae8020de2c0d94f34902d914af3058c8d.e34Lc3yQbhmTaO0OahqPcheNby0, not from cookie
07/06/23 16:01:02.33 formsweb: Trying to get a prestarted proc
07/06/23 16:01:02.33 formsweb: PreStarted process is not available
07/06/23 16:01:02.34 formsweb: Creating new Runtime Process using default executable
07/06/23 16:01:02.34 formsweb: Starting Forms Server in EM mode
07/06/23 16:01:02.48 formsweb: startProcess: executing frmweb server webfile=HTTP-0,0,1,default,111.144.104.113
07/06/23 16:01:02.50 formsweb: startProcess: execution failed, trying again using /oracle1/PROD/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,111.144.104.113
07/06/23 16:01:02.57 formsweb: Getting stdin, stdout and stderr of child process
07/06/23 16:01:02.58 formsweb: New server process created
07/06/23 16:01:02.237 formsweb: Forms session <9> started for VKARIA-lap ( 111.144.104.113 )
07/06/23 16:01:04.255 formsweb: ***********************************************
07/06/23 16:01:04.256 formsweb: Got POST request, length = 8
07/06/23 16:01:04.309 formsweb: HTTP request headers:
07/06/23 16:01:04.315 formsweb: ACCEPT: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2
07/06/23 16:01:04.315 formsweb: CACHE-CONTROL: no-cache
07/06/23 16:01:04.315 formsweb: CONNECTION: keep-alive
07/06/23 16:01:04.315 formsweb: CONTENT-LENGTH: 8
07/06/23 16:01:04.315 formsweb: CONTENT-TYPE: application/octet-stream
07/06/23 16:01:04.315 formsweb: COOKIE: ORA_UCM_VER=%2FMP%2F8tgnsj%2Ci_pg_%3Emp_ajc%2CamkMP%2F8relqh*g%5Dne%5D%3Ckn%5D_ha*_kiMP%2F8pckmrcGnMP%2F8naikpaEl; ORA_UCM_INFO=3~EB7BEB414AEC643CE030018A18B92319~Vipul~Karia~vipul.karia@oracle.com~IND~en~39~34~-1~~1; ORA_UCM_SRVC=3*OPN~1~0~//~SE1%3ASE1%3ASE1%3ASE1%3ASE1%3ASE1%3ASE1%3ASE1%3A~*EMP~1~0~/34/~null~*GMO~1~0~/34/~null; oracle.uix=0^^GMT+5:30^p
07/06/23 16:01:04.315 formsweb: HOST: vkaria.idc.oracle.com:8002
07/06/23 16:01:04.315 formsweb: PRAGMA: 1
07/06/23 16:01:04.316 formsweb: USER-AGENT: Mozilla/4.0 (Windows XP 5.1) Java/1.6.0_01
07/06/23 16:01:04.316 formsweb: ORACLE-ECID: 1182594664:152.69.208.39:32120:0:2382,0

References
NOTE:384241.1 - Using Forms Socket Mode with Oracle E-Business Suite Release 12
Note:373548.1 - Using Forms Trace in Oracle Applications Release 12
Oracle® Application Server Forms Services Deployment Guide, Section 8, Tracing and Diagnostics, Table 8-2

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


Related



--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------

Oracle E-Business Suite > Applications Technology > Technology Components > Oracle Applications Technology Stack
Keywords
--------------------------------------------------------------------------------
FORMS RUNTIME DIAGNOSTICS; FORMS TRACE; FTRACE.CFG; ICX_FORMS_LAUNCHER; LOGGING TOOLS; SERVLET; TRACEGROUP




Back to top

Rate this document

Article Rating
Rate this document
Excellent
Good
Poor

Did this document help you?
Yes
No
Just browsing

How easy was it to find this document?
Very easy
Somewhat easy
Not easy
Comments
Provide some feedback

Cancel

Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File

Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File [ID 162491.1]

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

Modified 10-DEC-2008 Type BULLETIN Status PUBLISHED



PURPOSE
This note explains which initialization file can be used to start an instance,
depending upon if an spfile or init.ora file exists.


SCOPE & APPLICATION
Intended audience is DBAs.


Spfile and Init.ora Parameter File Startup of an Oracle9i Instance:
===================================================================

Before Oracle9i, Oracle instances were always started using a text file called
an init.ora. This file is by default located in the "$ORACLE_HOME/dbs"
directory.

In Oracle9i, Oracle has also introduced the 'SPFILE', which is a binary file
stored on the database Server. Changes which are applied to the instance
parameters may be persistent accross all startup/shutdown procedures.

In Oracle9i, you can startup the instance using either an spfile or an init.ora
file.

An Oracle9i instance may be started by:

- default spfile --> spfile.ora
- default init.ora file --> init.ora
- specified init.ora file
- specified spfile

The default initialization files are located as follows:

- on Unix ---> $ORACLE_HOME/dbs
- on WindowsNT/2000 ---> %ORACLE_HOME%\database


The examples below show the different ways an Oracle9i database can be started:


1. Specifying neither file:
========================

sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

Oracle first searches if the spfile.ora exists. If it does not, Oracle
searchs for the spfile.ora parameter file. If neither spfile.ora nor
spfile.ora exist, Oracle will use the init.ora parameter file.

If none of these files exist, you receive the following messages:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORA901\DATABASE\INITORA901.ORA'


2. Specifying init.ora file:
=========================

This behavior is the same as in previous database versions.

SQL> startup pfile=d:\ora901\database\initORA901.ora
ORACLE instance started.

Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.


This option is not available if you are using an spfile. If you try to
startup specifying an spfile, Oracle displays the following error message:

SQL> startup spfile=d:\ora901\database\spfileORA901.ora
SP2-0714: invalid combination of STARTUP options

If you startup the database by specifying am init.ora file, the spile
parameter is displayed as empty:

NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
spfile string


3. Specifying spfile:
==================

In this case, you must startup with an init.ora file in which you only
specify the spfile parameter full name:

spfile=d:\ora901\database\spfiletest.ora

SQL> startup pfile=d:\ora901\database\inittest.ora
ORACLE instance started.

Total System Global Area 122449892 bytes
Fixed Size 282596 bytes
Variable Size 88080384 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.

Now, you can check the usage of an spfile using the following command in
SQL*Plus:

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------ ----------- ---------------------------------
spfile string d:\ora901\database\spfiletest.ora


You can modify the content of the specified spfile as follows:

SQL>alter system set processes = 200 scope=spfile;

System altered.

SQL> select name, value from v$spparameter
where name = 'processes';

NAME VALUE
--------------- -----
processes 200



References:
===========

Note:152726.1 Oracle9i: Changes Made to Init.ora are not Applied at Instance
Startup
Note:146577.1 How to change static parameters through SPFILE parameter
Note:139646.1 Same Parameters Appear in Init.ora and in Embedded spfile.ora
Note:138129.1 What Happens and What to Do when SPFILE has been Manually
Modified

Database Instances Using Oracle Database 10g Release 2

Export/Import Process for Oracle E-Business Suite Release 12

Database Instances Using Oracle Database 10g Release 2
November 2009

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

This document describes the process of re-creating an existing Applications Release 12 database instance using the datapump utilities. The most current version of these notes is document 454616.1 on My Oracle Support. There is a change log at the end of this document.

The datapump utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, datapump export files can archive database data, or move data among different Oracle databases that run on the same or different operating systems. This document assumes that you are already familiar with datapump.

There are special considerations when exporting or importing an Applications Release 12 database instance. This process consists of five discrete steps. Each step is covered in a separate section in this document.


Section 1: Prepare the source system
Tasks that must be performed to prepare the source system for the database export

Section 2: Prepare a target Release 12 database instance
Tasks for creating an empty database instance in preparation for import

Section 3: Export the source Release 12 database instance
Tasks that must be performed to produce a valid export of an Applications Release 12 database instance

Section 4: Import the Release 12 database instance
Tasks for running the import utility

Section 5: Update the imported Release 12 database instance
Tasks that must be performed to restore the imported Applications Release 12 database instance to a fully functional state

The source (export from) and target (import to) ORACLE_HOME directories must be Oracle Database 10g Release 2 (10.2.0).

The export/import process requires the use of both the datapump utilities (expdp/impdp) and the traditional export/import (exp/imp). For more information, read Oracle Database Utilities 10g Release 2 (10.2).

Attention: This document uses UNIX/Linux syntax when describing directory structures. However, it applies to Windows servers as well. Where there is a significant difference in tasks for Windows, specific instructions are given.



Some of the tasks in this document affect the APPL_TOP of one or more application server tiers. Those tasks require that the Applications file system environment be enabled by running the APPSORA.env file (for UNIX or Linux) or the envshell.cmd file (for Windows) prior to performing the tasks. Other tasks affect the Applications database instance. Those tasks require that the Oracle 10g environment be enabled by running the [ORACLE_SID].env/cmd file under the Oracle 10g Oracle home on the database server node prior to performing the tasks. In addition, you may have more than one Oracle home installed on the database server node, so it is important that you run the correct [ORACLE_SID].env/cmd file before performing tasks that affect the database instance. Read the instructions carefully to determine which environment should be enabled for each step.

Attention: This document assumes that the source and target application server tiers are the same. Creating new application server tiers for the target environment has to be done either before starting or after completing all the steps in this document. Then, update and run AutoConfig for the source database and application server tiers to enable the source environment.



Attention: If you are using Oracle Database Vault, refer to Note 822048.1 before performing any step in this document.





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

Section 1: Prepare the source system
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.


Apply latest AutoConfig patches
Perform steps 3.1 and 3.2.1 of the Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 document. The other steps in 3.2 are not necessary as they will be done at the target side.


Apply the Applications consolidated export/import utility patch
Apply patch 7120092 to the source administration server node. This patch provides several SQL scripts that facilitate exporting and importing an Applications database instance, export and import parameter files, and a perl script, which creates an AD patch driver.


Apply latest Applications database preparation scripts patch (conditional)
If you are using Oracle E-Business Suite Release 12.0, apply patch 6342289 to every application tier server node in the source system.


Create a working directory
Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp


Generate target database instance creation script aucrdb.sql
The target database instance must be created with the same tablespace and file structure as the source database instance. The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script, which you use to create the target database instance with the appropriate tablespace and file structure. The script converts all tablespaces except for SYSTEM to locally managed tablespaces with auto segment space management, if they are not already so.

On the source administration server node, use SQL*Plus to connect to the database as SYSTEM and run the $AU_TOP/patch/115/sql/auclondb.sql script. It creates aucrdb.sql in the current directory.


$ sqlplus system/[system password] \
@$AU_TOP/patch/115/sql/auclondb.sql 10


Record Advanced Queue settings
Advanced Queue settings are not propagated in the target database instance during the export/import process. Therefore, you must record them beforehand and enable them in the target database instance afterwards. The export/import patch contains auque1.sql, which generates a script called auque2.sql. You can use auque2.sql to enable the settings in the target database instance.

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the auque1.sql script. It generates auque2.sql.


$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql


Create parameter file for tables with long columns
Tables with long columns may not propagate properly in datapump. Therefore, they have to be migrated separately using the traditional export/import utilities.

Copy the aulong.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the aulong.sql script. It generates aulongexp.dat.

$ sqlplus /nolog
SQL> connect system/[system password];
SQL> @aulong.sql


Remove rebuild index parameter in spatial indexes
Ensure that you do not have the rebuild index parameter in the spatial indexes. To see if you have any rebuild index parameters, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the following command:

SQL> select * from dba_indexes where index_type='DOMAIN' and
upper(parameters) like '%REBUILD%';
To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command: SQL> alter index [index name] rebuild parameters [parameters]
where [parameters] is the original parameter set without the rebuild_index parameter.

Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, deregister the current database server node by running the following command as the owner of the Oracle RDBMS file system and current database instance:


$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps
contextfile=$CONTEXT_FILE -removeserver



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

Section 2: Prepare a target Release 12 database instance
This section describes how to create the empty target database and populate it with all of the required system objects prior to running import.

The Oracle home of the target database instance can be the same Oracle home that the source database instance uses, or it can be different (on another machine running a different operating system, for example), as long as it uses Oracle Database 10g Release 2 Enterprise Edition.


Create target Oracle 10g Oracle home (conditional)
If you want the target Oracle 10g Oracle home to be separate from the source Oracle home, you must create it now. Decide whether you want to install the 10.2.0 Oracle home manually, or use the Rapid Install to create it for you.

If you choose to use Rapid Install, you must use Rapid Install Release 12.0.0. As the owner of the Oracle RDBMS file system, start the Rapid Install wizard by typing:

$ rapidwiz -techstack

Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home. Make sure that the SID environment setting is set to the same value as your existing database instance.

If you choose to manually install the 10.2.0 Oracle home, log in to the database server node as the owner of the Oracle RDBMS file system and database instance and perform the following steps:

Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create.

Perform all the steps in Chapter 3 of the Oracle Database Installation Guide 10g Release 2 (10.2), for your platform.

In the subsequent windows, click on the Product Languages button and select any languages other than American English that are used by your Applications database instance, choose the Enterprise Edition installation type, and select the options not to upgrade an existing database and to install the database software only.

Perform tasks in section 3.5, "Installing Oracle Database 10g Products" in the Oracle Database Companion CD Installation Guide for your platform. Do not perform the tasks in the "Preparing Oracle Workflow Server for the Oracle Workflow Middle Tier Installation" section.

In the Installation Types window, click on the Product Languages button to select any languages other than American English that are used by your Applications database instance.

Make sure that the following environment variables are set whenever you enable the 10g Oracle home:

ORACLE_HOME points to the new 10.2.0 Oracle home.

PATH includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).

LD_LIBRARY_PATH includes $ORACLE_HOME/lib.

PERL5LIB points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory. After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.

Attention: Check to make sure the $ORACLE_HOME/nls/data/9idata directory is created and is non-empty.




Attention (for Windows users): Keep track of the database home name used. For Rapidwiz installed Oracle homes, the home name is [SID]_db102_RDBMS. For manually installed Oracle homes, the home name is what you input when creating the Oracle home.




Upgrade to the latest 10.2.0 patch set (conditional)
If you are not on the latest patch set, perform the following steps from the Oracle E-Business Suite Release 12 with Oracle Database 10g Release 2 (10.2.0) Interoperability Notes on My Oracle Support:

Perform 10.2.0.x Patch Set installation tasks

Apply additional 10.2.0.x RDBMS patches

Do not perform any post-installation patch README steps.


Modify sqlnet.ora file (Windows only)
If the target database server node is running Windows, add the following line to the sqlnet.ora file in the %ORACLE_HOME%\network\admin\[SID] directory, if it does not already exist:

SQLNET.AUTHENTICATION_SERVICES=(NTS)


Create the target initialization parameter file and CBO parameter file
The initialization parameter file (init[SID>.ora) and cost-based optimizer (CBO) parameter file (ifilecbo.ora) are located in the $ORACLE_HOME/dbs directory on the source database server node. Copy both files to the Oracle 10g $ORACLE_HOME/dbs directory on the target database server node.

Refer to Database Initialization Parameters for Oracle Applications Release 12 and update both the init.ora and ifilecbo.ora files with any necessary changes. You may also need to update initialization parameters involving the db_name, control_files, and directory structures.

Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.

Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.


Create a working directory
Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp


Create the target database instance
Copy the aucrdb.sql script, generated in Section 1, from the source administration server node to the working directory in the target database server node. Then update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node. If the target database server node is running Windows, update the directory structure from UNIX/Linux format to Windows format or vice versa.

Attention: Using the source tablespace information does not guarantee that the target tablespaces will be enough. It is highly recommended that you go through the source dba_free_space table to see which of the tablespaces are running out and modify the aucrdb.sql script to ensure ample tablespace size on the target database.



Make sure that the environment of your session on the target database server node is set up properly for the target database instance, especially the ORACLE_HOME, ORACLE_SID, and ORA_NLS10 environment settings. (ORACLE_SID must be set to the same value as the db_name parameter in the init[SID>.ora file.) Then, use the following commands to run aucrdb.sql and create the target database instance:

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;

For UNIX or Linux:

SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;

For Windows:

SQL> startup nomount pfile=%ORACLE_HOME%\dbs\init%ORACLE_SID%.ora
SQL> @aucrdb.sql
SQL> exit;

If PL/SQL of the source database was natively compiled, see the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) for instructions on how to natively compile PL/SQL in the target database. Add the parameters that pertain to the native compilation where specified. Do not use the natively compiled code generated by the source database. Oracle does not support switching the PL/SQL compilation mode from interpreted to native (and vice-versa) for an export/import. Exporting/importing using native mode takes significantly more time than interpreted mode.

When the target database instance has been created, restart the database instance.


Copy database preparation scripts to target Oracle home
The database preparation scripts that you applied to the source administration server node in Section 1 contain four scripts that are needed on the target database server node. Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node: addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql (UNIX or Linux) or addb1020_nt.sql, adsy1020_nt.sql, adjv1020_nt.sql, and admsc1020_nt.sql (Windows).

As you run each of the next four steps, note the following:

The remarks section at the beginning of each script contains additional information.

Each script creates a log file in the current directory.


Set up the SYS schema
The addb1020.sql or addb1020_nt.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run addb1020.sql (UNIX/Linux) or addb1020_nt.sql (Windows).

Here is an example on UNIX or Linux:


$ sqlplus "/ as sysdba" @addb1020.sql


Set up the SYSTEM schema
The adsy1020.sql or adsy1020_nt.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run adsy1020.sql (UNIX/Linux) or adsy1020_nt.sql (Windows).

Here is an example on UNIX or Linux:


$ sqlplus system/[system password] @adsy1020.sql


Install Java Virtual Machine
The adjv1020.sql or adjv1020_nt.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run adjv1020.sql (UNIX/Linux) or adjv1020_nt.sql (Windows).

Here is an example on UNIX or Linux:


$ sqlplus system/[system password] @adjv1020.sql

Attention: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.




Install other required components
The admsc1020.sql or admsc1020_nt.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run admsc1020.sql (UNIX/Linux) or admsc1020_nt.sql (Windows). You must pass the following arguments to the script, in the order specified:

Argument Value
remove context? FALSE
SYSAUX tablespace SYSAUX
temporary tablespace TEMP


Here is an example on UNIX or Linux:


$ sqlplus system/[system password] \
@admsc1020.sql FALSE SYSAUX TEMP

Attention: All of the components are created in the SYSAUX tablespace regardless of where it was installed in the source database.




Disable automatic gathering of statistics
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:


$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> exit;


Back up the target database instance
The target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.



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

Section 3: Export the source Release 12 database instance
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.


Create the export parameter file
A template for the export parameter file has been included as part of the the export/import patch. Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node. Use a text editor to modify the file to reflect the source environment and other customized parameters.

The customizable parameters are:

Parameter Description Template Value
directory directory where the export dump files will be created dmpdir
dumpfile export dump file name(s) aexp%U.dmp
filesize export dump file size 1GB
log log file name expdpapps.log


interMedia, OLAP, and Data Mining schemas are not exported. The admsc1020.sql script creates these schemas in the target database. Ensure that the schema names in the exclude parameters reflect those in your database.

Create a directory in the system schema that corresponds to the directory specified in the template. Here is an example of how to create a directory named dmpdir:

$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';

Do not change the other parameters.

The export process uses as many of the listed file names as necessary to hold the exported data. You must ensure that the number of dump files specified, as well as the size of each dump file, is sufficient to contain all the data in your source database instance.


Shut down Applications server processes
Shut down all Applications server processes except the database and the Net8 listener for the database. Users cannot use the Applications until the import is completed.


Grant privilege to source system schema
Grant the exempt access policy privilege to system by using SQL*Plus to connect to the database as SYSDBA and run the following command:


SQL> grant EXEMPT ACCESS POLICY to system;


Export the Applications database instance
Start an export session on the source database server node using the customized export parameter file. Use the following command:


$ expdp system/[system password] parfile=[export parameter file name]

Typically, the export runs for several hours.

Attention: See document 339938.1 on My Oracle Support if you encounter the failure:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully




Export tables with long columns
Start an export session on the source database server node using the customized aulongexp.dat file generated in Section 1. Use the following command:


$ exp parfile=aulongexp.dat


Export tables with XML type columns
Copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node. Start an export session on the source database server node using the following command:


$ exp parfile=auxmlexp.dat


Revoke privilege from source system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:


SQL> revoke EXEMPT ACCESS POLICY from system;



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

Section 4: Import the Release 12 database instance
This section describes how to use the import utility to load the Oracle Applications data into the target database.


Create the import parameter files
Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node. Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.

Create a directory in the system schema with the name set to the directory specified in the template and the path set to where the export dump files will reside. Here is an example of how to create a directory named dmpdir:

$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';

Save the changed file.


Copy the export dump files
Copy the export dump files from the source database server node to the working directory in the target database server node.


Import the users into the target database
Start an import session on the target database server node using the customized import parameter file. Use the following command:


$ impdp system/[system password] parfile=auimpusr.dat


Import tables with long columns into the target database
Modify the aufullimp.dat file with the following:

Set userid to "sys/[sys password] as sysdba".

Set file to the dump file containing the long tables (longexp by default).

Set the log file appropriately.

Leave the ignore parameter commented out.

Import the tables using the following command:

$ imp parfile=aufullimp.dat

Attention: You will get failures for the triggers as the dependent tables have not yet been imported.




Import the Applications database instance
Start an import session on the target database server node using the auimpdp.dat parameter file. Use the following command:


$ impdp system/[system password] parfile=auimpdp.dat

Typically, import runs for several hours.


Import triggers into the target database
Modify the aufullimp.dat file with the following:

Set userid to "sys/[sys password] as sysdba".

Set file to the dump file containing the long tables (longexp by default).

Change the log file name.

Uncomment the ignore parameter.

Add a line with the parameter "rows=n".

Start an import session on the target database server node using the customized import parameter file. Use the following command:


$ imp parfile=aufullimp.dat


Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:


SQL> revoke EXEMPT ACCESS POLICY from system;



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

Section 5: Update the imported Release 12 database instance
This section describes how to recreate the database objects and relationships that are not handled by the export and import utilities.


Reset Advanced Queues
Copy the auque2.sql script that was generated in Section 1 from the working directory in the source database server node to the working directory in the target database server node. Then, on the target database server node, as the owner of the Oracle 10g file system and database instance, use SQL*Plus to connect to the target database as SYSDBA and run the auque2.sql script to enable the Advanced Queue settings that were lost during the export/import process. The script creates a log file in the current directory.

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql


Start the new database listener (conditional)
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable (UNIX/Linux) or Services (Windows). See the Oracle Database Net Services Administrator's Guide, 10g Release 2 (10.2) for more information.

Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.




Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the working directory in the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql) \
[APPS schema name]

Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.



Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:

$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS


Apply patch 6494466 (conditional)
If the target database is Windows and the source is not, apply patch 6494466 on the target database tier. Create the appsutil directory if needed.


Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.

See Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 on My Oracle Support, especially section 3.2, for instructions on how to implement and run AutoConfig.

Shut down all processes, including the database and the listener, and restart them to load the new environment settings.


Gather statistics for SYS schema
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:


$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

Attention: Make sure that you have at least 1.5 GB of free default temporary tablespace.




Re-create custom database links (conditional)
If the Oracle Net listener in the 10.2.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:


$ sqlplus apps/[apps password]
SQL> select db_link from dba_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:


$ sqlplus apps/[apps password]
SQL> drop database link [custom database link];
SQL> create database link [custom database link] connect to
[user] identified by [password] using
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])
(PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))';

where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect the new Oracle Net listener for the database instance.


Create ConText and AZ objects
Certain ConText objects and the AZ objects dependent on the tables with XML type columns are not preserved by the import process. The consolidated export/import utility patch that you applied to the source administration server node in Section 1 contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file. You use this driver file to call the scripts that create these objects. Run the following command:


$ perl $AU_TOP/patch/115/bin/dpost_imp.pl [driver file] 10

Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.


Import tables with XML type columns into the target database
Modify the aufullimp.dat file with the following:

Set userid to "az/[az password]"

Set file to the dump file containing the tables with XML types (xmlexp by default).

Change the log file name.

Comment out the ignore and rows parameters.

Start an import session on the target database server node using the customized import parameter file. Use the following command:


$ imp parfile=aufullimp.dat

Once the import is complete, you can delete the export dump files, as well as the export and import parameter files, from the source and target database server nodes.


Populate CTXSYS.DR$SQE table
To populate the CTXSYS.DR$SQE table, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following command:


$ sqlplus apps/[apps password]
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;


Compile invalid objects
On the target database server node, as the owner of the Oracle 10g file system and database instance, use SQL*Plus to connect to the target database as SYS and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.


$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql


Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:


Compile flexfield data in AOL tables

Recreate grants and synonyms for APPS schema


Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.


Create DQM indexes
Create DQM indexes by following these steps:

Log on to Oracle Applications with the "Trading Community Manager" responsibility

Click Control > Request > Run

Select "Single Request" option

Enter "DQM Staging Program" name

Enter the following parameters:

Number of Parallel Staging Workers: 4

Staging Command: CREATE_INDEXES

Continue Previous Execution: NO

Index Creation: SERIAL

Click "Submit"



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

Change Record
The following sections were changed in this document.

Date Summary of Changes
10-Dec-2007 Initial release
11-Jan-2008 Changed export/import patch 6258200 to 6723741
3-Jul-2008 Modified AutoConfig related instructions
Added step to populate CTXSYS.DR$SQE table
Added patch 6494466
Updated export/import patch to 6924477
Added instructions related to the exempt access policy grant.
Added aucrdb.sql attention box

25-May-2009 Added attention statement to see 339938.1 when encountering ORA-932
11-Sep-2009 Added Database Vault information
Added revoke exempt access policy for target system

16-Nov-2009 Modified OracleMetaLink to My Oracle Support
Modified AutoConfig step numbers as step numbers in AutoConfig has changed
Modified adgrants.sql to run with APPS parameter
Modified export/import patch to 7120092
Incorporating 12.1 into the document
Modified step to create 9idata directory to ensure directory exists
Replaced Interoperability note links from 454750.1 to 812362.1



Note 454616.1 by Oracle Applications Development
Copyright 2007 Oracle USA
Last modified: Monday, Nov 16, 2009

Frequently Asked Questions: Oracle E-Business Suite Support on x86-64

Frequently Asked Questions: Oracle E-Business Suite Support on x86-64
Last updated July 8, 2008
View Change Record


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

Questions addressed in this document

What is x86-64?
What operating systems are supported on x86-64?
Is Oracle E-Business Suite supported on x86-64?
What are the benefits of running Oracle E-Business Suite on x86-64?
How do I install Oracle E-Business Suite database on x86-64?
Why are there two installation packages for E-Business Suite Release 12 for Linux?
How do I migrate an existing Oracle E-Business Suite database to x86-64?
Is RAC supported with the database tier on x86-64?
Is cloning supported on an E-Business Suite system with an x86-64 database?
How do I clone the database on platforms that do not support Rapid Clone?

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

Questions and Answers
What is x86-64?
Answer:
x86-64 (also called x64) refers to both AMD Opteron (AMD64) and Intel Extended Memory (EM64T) processors. AMD64 and EM64T are x86-compatible chip sets with 64-bit extensions. x86-64 based servers can run either a 32-bit or a 64-bit operating system.

Refer to the Intel web site for more information on EM64T and to to the AMD web site for more information on AMD64.

[top]



What operating systems are supported on x86-64?
Answer:
Apple Macintosh, Linux, Microsoft Windows, Solaris and other operating systems are supported on x86-64. Each combination of an operating system and chip architecture is refered to as a platform. Not all these platforms are certified with Oracle E-Business Suite.

[top]



Is Oracle E-Business Suite supported on x86-64?
Answer:
The answer has three parts:

1. 32-bit Operating Systems for Oracle E-Business Suite Release 12 and 11i:
When Linux or Windows 32-bit operating systems are installed, x86-64 servers can be used for Oracle E-Business Suite application and database tiers; "Linux x86" and "Windows 32-bit" platform certification criteria apply. The following 32-bit operating systems are supported:

Microsoft Windows Server 2003
Oracle Enterprise Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
2. Some 64-bit platforms for Oracle E-Business Suite Release 12: the following 64-bit operating systems are supported for both application and database tiers on x86-64 servers:

Oracle Enterprise Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
3. Remaining 64-bit platforms for Release 12 and all 64-bit platforms for Release 11i : when Oracle E-Business Suite Release 11i or 12 application tier is installed on a certified platform, additional platforms may be used for a 64-bit database tier on x86-64 servers. This is an example of a mixed platform architecture. In Release 11i this was known as Split Configuration.

The following 64-bit operating systems are supported on x86-64 servers for E-Business Suite database tier only:

Microsoft Windows Server x64
Oracle Enterprise Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
Sun Solaris x64
For up-to-date information on mixed platform architecture, refer to "Database Tier Only " in Certify -> "View Certifications by Platform", choose "Database Tier Only " in the platform list.


See OracleMetaLink's Certify for more information on certified platforms and specific software versions.

[top]



What are the benefits of running Oracle E-Business Suite on x86-64?
Answer:
When a 32-bit OS is installed on x86-64, the maximum 64 GB of main memory (using Physical Address Extension) provides scalability and stability advantages over x86 (32-bit) processor systems, which are limited to 4 GB. Each process is still limited to a 32-bit memory space (4 GB for Linux, and up to 3 GB for Windows) when running a 32-bit OS on x86-64 processor systems.

When a 64-bit OS is installed on x86-64, the limits on physical and process memory significantly exceed today's manufacturing capabilities.

When larger amounts (upward of 16 GB) of memory are installed, running a 64-bit Linux OS is more stable than running a 32-bit Linux OS.

[top]



How do I install Oracle E-Business Suite database on x86-64?
Answer:
For new installations of Oracle E-Business Suite Release 12 on supported 64-bit Linux operating systems, you can use Rapid Install as directed in "Oracle Applications Installation Guide: Using Rapid Install" to install the database and application tiers.

For Database Tier Only platforms in a mixed platform architecture, you can use Rapid Install as directed in "Oracle Applications Installation Guide: Using Rapid Install" to install the database and application tiers on supported platforms, and then migrate the database tier to a certified 64-bit operating system (see references).

[top]



Why are there two installation packages for E-Business Suite Release 12 for Linux?
Answer:
The Oracle E-Business Suite Applications Release 12.0 Media Pack for Linux x86 is used to install on x86 or x86-64 servers with a supported 32-bit Linux operating system. The Oracle E-Business Suite Applications Release 12.0 Media Pack for Linux x86-64-bit is used to install on x86-64 servers with a supported 64-bit operating system. For the Installation and Upgrade Notes specific to each installation package, refer to the documentation in the references

[top]



How do I migrate an existing Oracle E-Business Suite database to x86-64?
Answer:
To migrate the database to a certified operating system on x86-64, refer to the documentation in the references.

[top]



Is RAC supported with the database tier on x86-64?
Answer:

Yes, it is. RAC certifications are generic and apply to all platforms unless otherwise specified.

Using Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12 (OracleMetaLink Note 388577.1) contains Oracle E-Business Suite Release 12 with RAC implementation details.

A list of the supported Release 11i versions with RAC is available in Oracle E-Business Suite 11i and Database FAQ (OracleMetaLink Note 285267.1). In addition, the following documents contain Oracle E-Business Suite 11i with RAC implementation details:

Oracle E-Business Suite Release 11i with 9i RAC; Installation & Configuration Using AutoConfig (OracleMetaLink Note 279956.1)
Configuring Oracle Applications Release 11i with 10g Real Application Clusters and Automatic Storage Management (OracleMetaLink Note 312731.1)
If you use parallel concurrent processing (PCP) with Oracle E-Business Suite 11i, we recommend applying ATG Family Pack 11i.ATG_PF.H RUP3 or higher (patch 4334965).

The use of RAC on Solaris 10 Containers (a feature that allows partitioning of an OS into separate virtual hosts) is restricted to global (non-local) containers - please see MetaLink Note 317257.1 for the best practices document for deploying Oracle databases in Solaris 10 Containers.

[top]



Is cloning supported on an E-Business Suite system with an x86-64 database?
Answer:

Yes, it is. For Oracle E-Business Suite systems with database tiers on 64-bit Linux operating systems, use Rapid Clone.

For Oracle E-Business Suite systems with database tiers on Windows x64, use Rapid Clone to clone the application tier and the Oracle database utilities to clone the database tier.

Note: Rapid Clone is not supported on Windows Server 2003 x64

More information about Rapid Clone is available in the references. See the next question to clone the database tier on platforms that do not support Rapid Clone.



How do I clone the database on platforms that do not support Rapid Clone?
Answer:

Cloning the E-Business Suite database tier using the Oracle database utilities is a process of cloning the database Oracle home and creating a duplicate database.

To clone the database Oracle home, use the Oracle Universal Installer (OUI). This process allows you to configure inventory information for the new target Oracle home. See the Oracle Universal Installer and OPatch User's Guide, 10g Release 2 for additional information.

To create a duplicate database, use Recovery Manager (RMAN). See the Oracle Database Backup and Recovery Advanced User's Guide, 10g Release 2 for additional information.




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

References
Oracle E-Business Suite Release 12
Cloning Oracle Applications Release 12 with Rapid Clone (OracleMetaLink Note 406982.1)

Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for Linux (32-bit) (OracleMetaLink Note 402310.1)

Oracle Applications Installation and Upgrade Notes Release 12 (12.0) for Linux (64-bit) (OracleMetaLink Note 416305.1)

Oracle E-Business Suite 11i
Split Configuration Database Tier documentation:

Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 2 (OracleMetaLink Note 369693.1)

Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 1 (OracleMetaLink Note 356839.1).

Using Oracle Applications with a Split Configuration Database Tier on Oracle 9i Release 2 (OracleMetaLink Note 304489.1)

Cloning Oracle Applications Release 11i with Rapid Clone (OracleMetaLink Note 230672.1)