Gather statistics on fixed objects after upgrading to 10g

Recently RMAN backup failed after 10g upgrade. On digging in error logs, the following was discovered:

SQL> select count(*) from v$rman_status ;
select count(*) from v$rman_status
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 640 in tablespace TEMP

Incorrect statistics on FIXED objects

Bad execution plan needed much sort-space.
Multiple waitevents on 'direct path write temp'.

Fix: As per Metalink Note 357765.1

SQL> exec dbms_stats.gather_fixed_objects_stats;

IE8 beta 2 available for download

I have just downloaed IE8 beta 2 from here. The Microsoft IE Blog has more information about this version. I'll update this post more as I experience more of it.

How to check the status of windows services from command line

A command line tool called sc is available to communicate with Windows Services. Here's what you get if you type sc on command line:

DESCRIPTION:
SC is a command line program used for communicating with the
NT Service Controller and services.
USAGE:
sc [command] [service name] ...

The option has the form "\\ServerName"
Further help on commands can be obtained by typing: "sc [command]"
Commands:
query-----------Queries the status for a service, or
enumerates the status for types of services.
queryex---------Queries the extended status for a service, or
enumerates the status for types of services.
start-----------Starts a service.
pause-----------Sends a PAUSE control request to a service.
interrogate-----Sends an INTERROGATE control request to a service.
continue--------Sends a CONTINUE control request to a service.
stop------------Sends a STOP request to a service.
config----------Changes the configuration of a service (persistant).
description-----Changes the description of a service.
failure---------Changes the actions taken by a service upon failure.
qc--------------Queries the configuration information for a service.
qdescription----Queries the description for a service.
qfailure--------Queries the actions taken by a service upon failure.
delete----------Deletes a service (from the registry).
create----------Creates a service. (adds it to the registry).
control---------Sends a control to a service.
sdshow----------Displays a service's security descriptor.
sdset-----------Sets a service's security descriptor.
GetDisplayName--Gets the DisplayName for a service.
GetKeyName------Gets the ServiceKeyName for a service.
EnumDepend------Enumerates Service Dependencies.

The following commands don't require a service name:
sc
boot------------(ok | bad) Indicates whether the last boot should
be saved as the last-known-good boot configuration
Lock------------Locks the Service Database
QueryLock-------Queries the LockStatus for the SCManager Database
EXAMPLE:
sc start MyService

Would you like to see help for the QUERY and QUERYEX commands? [ y | n ]:

So here are the commands to start, stop and monitor status of your service:

sc start MyService
sc stop MyService
sc query MyService

Here's an example:

C:\>sc start Alerter

SERVICE_NAME: Alerter
TYPE : 20 WIN32_SHARE_PROCESS
STATE : 2 START_PENDING
(NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x7d0
PID : 1528
FLAGS :

C:\>sc query Alerter

SERVICE_NAME: Alerter
TYPE : 20 WIN32_SHARE_PROCESS
STATE : 4 RUNNING
(STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

C:\>sc stop Alerter

SERVICE_NAME: Alerter
TYPE : 20 WIN32_SHARE_PROCESS
STATE : 1 STOPPED
(NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

Instance won't start with message ORA-01089: Immediate shutdown in progress - no operations are permitted

Sandeep Nagpal called me yesterday and told me that he was having issues with an Oracle 8.1.7 instance. An INSERT operation was taking a very long time, so he had done a shutdown immediate. That also took time. So he did shutdown abort. That didn't work, so he killed all oracle processes from OS level with kill -9 command. Inspite of this, when he tries to start the instance now, the following error is displayed:

ORA-01089: Immediate shutdown in progress - no operations are permitted

This is usually due to orphaned shared memory segments and semaphores. Metalink Note 1010214.6 describes how to remove these.

To remove the orphaned shared memory segments and semaphores, use the
following commands:

 ipcrm -m       - for shared memory
 ipcrm -s       - for semaphores

Once Sandeep identified and removed the shared memory segments and semaphores, he was able to startup the instance.

adpreclone 0% completed Error while running Stage...

Boda pinged me today and reported this error:

perl adpreclone.pl dbTier
Running Rapid Clone with command...
perl /erp11i/oracle/10.2.0/appsutil/bin/adclone.pl java=/erp11i/oracle/10.2.0/jdk mode=stage stage=/erp11i/oracle/10.2.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/erp11i/oracle/10.2.0/appsutil/ORPROD01_arctic.xml showProgress

Beginning database tier Stage - Fri Aug 22 09:32:45 2008
APPS Password : apps
Log file located at /erp11i/oracle/10.2.0/appsutil/log/ORPROD01_arctic/StageDBTier_08220932.log
| 0% completed
ERROR while running Stage...

ERROR while running perl /erp11i/oracle/10.2.0/appsutil/bin/adclone.pl java=/erp11i/oracle/10.2.0/jdk mode=stage stage=/erp11i/oracle/10.2.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/erp11i/oracle/10.2.0/appsutil/ORPROD01_arctic.xml showProgress ... Please check the log for more details..

The log file didn't have any details. Oracle advised on an SR to set this environment variable before re-executing adpreclone:

TIMEDPROCESS_DEBUG=YES
export TIMEDPROCESS_DEBUG

After setting the above and re-executing it we found that zip 2.2 was in path. As it is well known one of the pre-requisites of cloning is that you should be on zip 2.3, we modified the path so that zip 2.3 was in path first. However running adpreclone.pl still showed that zip 2.2 was being used. This was happening when the .env file was being sourced. When you logged in as oracle, zip was 2.3. But as soon as you sourced the $CONTEXT_NAME.env file the path changed and zip was 2.2. We corrected this in the .env file after which we got perl errors. So we changed

The DB was upgraded to 10.2.0.4 and no $ORACLE_HOME/Apache directory was present. So we pointed the PERL5LIB to the perl inside the IAS_ORACLE_HOME

PERL5LIB= $IAS_ORACLE_HOME/Apache/perl/lib/5.00503:$IAS_ORACLE_HOME/Apache/perl/lib/site_perl/5.005:$ORACLE_HOME/appsutil/perl
export PERL5LIB
ADPERLPRG=$IAS_ORACLE_HOME/Apache/perl/bin/perl
export ADPERLPRG

The same changes were also made in the context file variables s_adperlprg for ADPERLPRG and s_perl5lib for PERL5LIB so that these values remain correct after autoconfig.

adpreclone.pl succeeded after these changes.

Veritas Cluster Manager for Windows native resource types

We are currently working on making Kewill Flagship (which is a third party solution for automated shipping) highly available. We are using Veritas Cluster Manager to make the Kewill services automatically failover to a secondary server in case any service goes down. The native resource types supported by Veritas Cluster Manager on Windows Server 2003 are:

CompositeFileShare
ElifNone
ExchProtocol
ExchService
FileNone
FileOnOnly
FileShare
GenericService
HIC
IIS
IP
IPMultiNicPlus
Lanman
MSDTC
MSMQ
MSSearch
MSVirtualMachine
MetroMirror
MirrorView
MountV
NIC
NetBackup
Netlsnr
NotifierMngr
Oracle
PPRC
Phantom
PrintShare
PrintSpool
Process
Proxy
RVGPrimary
RegRep
RemoteGroup
SQLAgService2005
SQLOlapService2005
SQLServer2005
SRDF
ServiceMonitor
VMDg
VRTSWebApp
VvrRvg

Unfortunately Apache for Windows and MySQL for Windows are not present in this list which are present in Kewill Flagship's architecture. Kewill Flagship also supports IIS, so we have opted out Apache and gone with IIS. However for the others we will have to configure Generic Services. I'll update this post, once I learn more.

How to play webex .wrf files

WebEx Communications Inc. is a Cisco company that provides on-demand collaboration, online meeting, web conferencing and video conferencing applications. Its products include "Meeting Center", "Training Center", "Event Center", "Support Center", "Sales Center" "MeetMeNow", "PCNow", "WebEx AIM Pro Business Edition", "WebEx WebOffice", "WebEx Connect" and others.

WebEx was founded in 1995 by Subrah Iyar and Min Zhu. Zhu co-founded Future Labs (one of the first companies to produce multi-point document collaboration software) in 1991. Zhu met Iyar, then a vice president and general manager of Quarterdeck Inc., when Quarterdeck acquired Future Labs in 1996. Iyar was named president of Future Labs, which had been made a Quarterdeck subsidiary, and the same year Iyar and Min went on to co-found WebEx. On March 15, 2007, Cisco Systems announced it would acquire WebEx for $3.2 billion.

Webex is used extensively by many companies for remote helpdesk services, webinars, training and various other applications. Any webex session can be recorded and saved as a webex recording file which has the extension .wrf. To play these files offline on your computer you need to download the Webex Player available on http://support.webex.com/support/downloads.html.

Importance of proxy exception list in Browsers

We spent half a day trying to troubleshoot a printer problem. This was occuring in Kewill Flagship which supports client side printing. To configure client side printing, the kewill server has the details of the computer name and printer name in the format \\computername\printername. The computername was EXAMPLEPC. However when we queried the computer name from Kewill, it said that the computername is AMERICAS.JUSTANEXAMPLE.COM. This was weird. But as all technical people know, there is nothing weird in this world. It is just that you are unable to comprehend it logically, because of which it appears weird.

I did a telnet americas.justanexample.com and got this:

Blue Coat Shell proxy

===========================================================================
Property of Justanexample.com

THIS SYSTEM IS FOR USE BY JUSTANEXAMPLE EMPLOYEES AND CONTRACTORS ONLY.

>>> UNAUTHORIZED USE IS STRICTLY PROHIBITED! <<< >>> ALL USAGE IS SUBJECT TO MONITORING <<< =========================================================================== Usage: tn-gw-> connect 'telnet_server_name'
Shell-proxy>

I googled for BlueCoat shell proxy and found that it was a product from www.bluecoat.com. On a hunch I used americas.justanexample.com as my proxy server. It worked. So that was it. When we were using the browser to access the application, it was going through proxy and the application was getting the name of the computer as the name of the proxy server. I checked the Proxy Exception box (accessed in IE through Tools > Internet Options > Connections > LAN Settings > Advanced button > Exceptions Do not use proxy server for addresses begining with) and found it blank. Because of this for accessing both internal and external sites, proxy was being used. Once I put the line *.justanexample.com in the exception list, Kewill was able to get the correct name of the computer which was EXAMPLEPC. Once that was done, the web application was able to print to the local printer without issues.

Oracle Applications password encryption

Stephen Kost has written an excellent white paper on Oracle Apps password encryption. Some key points from his white paper:

Oracle Applications 11i stores passwords in two tables: FND_USER and FND_ORACLE_USERID. The FND_USER table stores application user account passwords and the FND_ORACLE_USERID table stores internal Oracle Applications database account passwords. Both tables use the same encryption algorithm to protect the passwords.

The APPLSYS.FND_USER table contains all the application accounts. There are two password columns in this table: ENCRYPTED_FOUNDATION_PASSWORD and ENCRYPTED_USER_PASSWORD.

Column Value Encryption Key
ENCRYPTED_FOUNDATION_PASSWORD APPS password username/password
ENCRYPTED_USER_PASSWORD username/password APPS password

These two columns provide for a two-way encryption of the passwords –
1. if you know a username and password, you can get the APPS password = ENCRYPTED_FOUNDATION_PASSWORD
2. if you know the APPS password, you can get any user's password = ENCRYPTED_USER_PASSWORD

It is possible to decrypt APPS password or any front end user's password, if you have OS access and database access.

TXK patches in a CPU are cumulative

Yesterday, while discussing about a patch scoping analysis with Sandhya, I realized that the TXK patch provided in any given CPU is cumulative. For example, the TXK patch published in April 2008 CPU contains all TXK patches published in previous CPUs. If you go through the readme of ATG_PF.H RUP6 which is Metalink Note 444524.1. It says:

Oracle Applications Technology 11i.ATG_PF.H.delta.6 (RUP6) contains Oracle Applications Technology (ATG) security fixes for core ATG products from the January 2005 Critical Patch Update (CPUJan2005) through the July 2007 Critical Patch Update (CPUJul2007). The following core ATG products are included in 11i.ATG_PF.H.delta.6: FND, OAM, OWF, FWK, JTT, JTA, TXK, XDO, ECX, EC, AK, ALR, UMX, BNE, and FRM.

However, the following CPU patches for ATG core products are not included in 11i.ATG_PF.H.delta.6 and must be applied on top of 11i.ATG_PF.H.delta.6 in order to obtain the complete set of ATG security fixes, if you are applying 11i.ATG_PF.H.delta.6 as part of a CPU implementation. Additionally, you must apply the CPU patches for products other than the core ATG products, if you have not already done so. Please refer to the CPU documentation for information on CPU patches for products not listed above.

• Customers using AutoConfig must apply patch 5658489 (TXK).

• Customers who are not using AutoConfig must apply patch 5082356 (TXK) and refer to the readme file for patch 5658489 (TXK) for more details.

Note: Customers who apply the TXK patches 5658489 or 5082356 must have installed a minimum of Oracle Developer Suite 6i patchset 17 plus the CPU patches for Oracle Developer Suite 6i patchset 17

You can verify about the cumulative nature of the TXK patches by this process:

Download the TXK patch 6831988 (present in April 2008 CPU) and unzip it.
cd to the patch directory
vi u5648489.drv

Here's three of previous TXK patches released in older CPUs:

5046719 TXK patch present in April 2006 CPU
4759690 TXK patch present in Jan 2006 CPU
5658489 TXK patch present in Jan 2007 CPU

If you search for these patch numbers inside the driver, you'll find them present. There is no TXK patch in July 2008 CPU. However you can verify this in future CPUs easily by searching for older TXK patch numbers inside the TXK patch driver file.

Deprecated and recommended initialization parameters after 9206 to 11g upgrade

While upgrading the 11.5.10.2 Vision instance which comes with RDBMS 9.2.0.6, the following initialization parameters came up as deprecated:

user_dump_dest
background_dump_dest
core_dump_dest
enqueue_resources
row_locking
undo_suppress_errors
optimizer_max_permutations
max_enabled_roles
sql_trace

The 11g manual upgrade metalink note 429825.1 mentions some more:

Initialization parameters deprecated in Oracle Database 11g release 1 (11.1)

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



Appendix B:

Initialization Parameters Obsolete in Oracle Database 11g release 1 (11.1)

DDL_WAIT_FOR_LOCKS
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS


Metalink Note 216205.1 mentions the Release-Specific Database Initialization Parameters for 11gR1 (11.1.X):

####################################################################
#
# Oracle Applications Release 11i - database initialization parameters
#
# This file contains the release-specific database initialization parameters
# for 11gR1. Oracle Applications Release 11i requires a minimum of 11.1.0.6.


#########
#
# Compatible
#
# Compatibility should be set to the current release.
#
#########

compatible = 11.1.0

########
#
# Cache Sizes
#
# For 11g, the automatic SGA tuning option (sga_target) is required.
# This avoids the need for individual tuning of the different
# SGA caches, such as the buffer cache, shared pool, and large
# pool. Use of the automatic SGA tuning option also improves
# manageability and overall performance.
#
# sga_target refers to the total size of the SGA. This includes
# all the sub-caches, such as the buffer cache, log buffer,
# shared pool, and large pool. The sizing table in the
# section Database Initialization Parameter Sizing contains
# sizing recommendations for sga_target.
#
# When the automatic SGA tuning option is used to dynamically size
# the individual caches, it is recommended to use a Server Parameter
# file (SPFILE) to store the initialization parameter values.
# Using an SPFILE allows the dynamically-adjusted values to persist
# across restarts. Refer to the Oracle 11g Database Administrator's
# Guide for information on how to create and maintain an SPFILE.
#
#
########

sga_target = 1G

########
#
# Shared Pool
#
# The shared pool should be tuned so as to minimize contention for SQL
# and PL/SQL objects. A value of 400M is a reasonable starting point for
# Release 11i, and automatic SGA tuning will adjust the caches as per
# the workload. The values below for the shared pool related caches are
# simply minimum (starting) values.
#
########

shared_pool_size = 400M
shared_pool_reserved_size = 40M

#########
#
# NLS and Character Sets
#
#########

nls_length_semantics = BYTE #MP

########
#
# Rollback Segments
#
# From 9iR2, Oracle Applications requires the use of System Managed Undo.
# This is straightforward to manage and administer, much more efficient
# than manually managed rollback segments, and reduces the chances of
# "snapshot too old" errors. To use System Managed Undo, you must create
# an UNDO tablespace.
#
########

undo_management=AUTO #MP
undo_tablespace=apps_undots1 #MP

########
#
# Private Memory Areas
#
# The Automatic Memory Manager is used to manage PGA memory. This avoids
# the need to tune sort_area_size and hash_area_size manually.
#
# The Automatic Memory Manager also improves performance and scalability,
# as memory is released back to the operating system.
#
########

pga_aggregate_target = 1G
workarea_size_policy = AUTO #MP
olap_page_pool_size = 4194304

########
#
# Cursor-Related Settings
#
# Prior to 10g, PL/SQL (server-side) used the setting of the open_cursors
# parameter as the upper limit for caching PL/SQL (server-side) cursors.
# In 10g, the upper limit was controlled by the parameter session_cached_cursors.
#
# 11g changes the default behavior for the server side PL/SQL cursor cache.
# For 11g environments, the parameters open_cursors and session_cached_cursors
# should be set as follows, in accordance with this change in behavior.
#
########

open_cursors = 600
session_cached_cursors = 500

########
#
# Events
#
# Events should not be set unless specifically requested by Oracle Support,
# or in Applications documentation.
#
########

#########
#
# PL/SQL Parameters
#
# The following parameters are used to enable the PL/SQL global optimizer
# and specify the compilation type.
#
# Release 11i environments that use Oracle Database 11g can employ either
# interpreted or compiled (native) PL/SQL code. The default is interpreted.
# While native PL/SQL compilation can improve runtime performance, this may
# be at the expense of increased downtime, because of the need to generate
# and compile the native shared libraries.

# If native compilation is to be used, uncomment the plsql_code_type = NATIVE
# line below. Note that in 11g, the parameters plsql_native_library_dir and
# plsql_native_library_subdir_count have no effect and are are not needed, as
# natively compiled code is now stored in the database, not a filesystem.

# plsql_code_type = NATIVE # Uncomment if you want to use NATIVE compilation

#########
#
# Optimizer Parameters
#
# Release 11i uses the Cost Based Optimizer (CBO). The following optimizer
# parameters must be set as shown, and should not be changed.
#
#########

_b_tree_bitmap_plans = FALSE #MP
optimizer_secure_view_merging = FALSE #MP
_optimizer_autostats_job=false # Turn off automatic statistics

#########
#
# Database Password Case Sensitivity (new with Oracle Database 11g)
#
# Database password case sensitivity is a new feature available with 11g.
# Oracle E-Business Suite does not currently integrate with this feature,
# so the parameter must be set to FALSE.
#
#########

sec_case_sensitive_logon = FALSE #MP

MISSING_COMPONENT : oracle.rsf.oracore_rsf

I was trying to apply DST v4 timezone patch 5632264 to Oracle 9.2.0.6 RDBMS home of a Vision 11i instance which is being upgraded to 11g. Applying time zone patch is one of the pre-requisites. However this patch was not available for 5632264. The DST v4 Metalink note said that if patchset is not available, you could try to apply 9.2.0.7 or 9.2.0.8 patch. Whenever I tried to apply the patch through opatch, I got this error:

MISSING_COMPONENT : oracle.rsf.oracore_rsf, 9.2.0.8.0

This Oracle Home does not have components/versions required by the patch.

ERROR: OPatch failed during pre-reqs check.

I tried applying DSTv7 patch 6672979 also. However the same error cropped up. I downloaded new version of OPatch 1.0.0.0.58. However, there was no change in the error. In the end, I followed the manual process to apply the DST v7 patch:

The manual process is:
a) download the correct 6672979 patch for your base version and platform.
b) unzip the 6672979 patch
c) make a backup of the 2 *.dat files in your $ORACLE_HOME/oracore/zoneinfo
directory and removed them (Databases may be running but if possible stop them to avoid any file locks)
d) copy the 2 *.dat files and the readme.txt from the unzipped patch 6672979/files/oracore/zoneinfo directory to your $ORACLE_HOME/oracore/zoneinfo directory (Databases may be running but if possible stop them to avoid any file locks).
e) stop all databases using this $ORACLE_HOME and after all databases are stopped start them again.

Applying this manually does not update the v$timezone_file. I am still figuring out how to do that manually.

Upgrade 11i/9i vision to 11i/11g

I am currently doing a test upgrade of a freshly installed 11i vision instance with database 9.2.0.6 to database version 11.1.0.6. Till now I have achieved the following:

Install Oracle Enterprise Linux 5
Install pre-requisite patches for 11.5.10.2 on OEL5
Install E-Business Suite 11.5.10.2 Vision instance
Apply pre-requisite patch ATG_PF.H RUP6
Install 11g software
Executed catupgrd.sql successfully

Recompilation of invalids is in progress now.

I'll update this post with more details about this activity.

How to find the DNS domain name of your server

There is a command called domainname in Unix. However, the 'domainname' command returns the NIS domain name. This is unrelated to the DNS domain, although you could set them to be the
same. Unfortunately, there's no general way to obtain the DNS domain. Parsing the 'domain' line from /etc/resolv.conf is probably the best way. If the local hostname is fully-qualified, you could split it at the first dot to obtain the hostname. If it isn't, you could qualify it with a DNS lookup first.

awk '$1=="domain" {print $2}' /etc/resolv.conf

How to find out the file through which an Apps standard package is created

Veera Boda pinged me today. He gave me this error which was coming during adconfig:

Cause: afpgdbcf failed due to ORA-04063: package body "APPS.FND_PROFILE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APPS.FND_PROFILE"
ORA-06512: at "APPS.FND_WEB_CONFIG", line 269
ORA-06512: at "APPS.FND_SECURITY_PKG", line 350

He said that he had followed Metalink Note 178001.1 to drop obsolete objects. On checking 178001.1, I found that it was related to 11.5.2 and advised running a script $FND_TOP/admin/sql/fnddold.sql which dropped obsolete objects. Apparently that script also dropped some objects like FND_PROFILE and FND_USER_PKG. Boda wanted to know how he could create those dropped objects. I thought of Electronic Technical Reference Manual available thru http://etrm.oracle.com which requires metalink login. However etrm has definitions of objects starting from 11.5.7. They don't have 11.5.2 objects. Then I thought of an easy way which I had discovered for fixing invalid objects. If you query a package's source code, you can find the line which has the version information. The version information line has the name of the file through which the package was created. For example:

select line,text from dba_source where object_name='FND_USER_PKG' and line < style="font-weight: bold;">/* $Header: AFSCUSRB.pls 115.150 2007/06/01 16:37:07 fskinner ship $ */
3
4
/* START PARTY */


From the above query I come to know that AFSCUSRB.pls file created the package body FND_USER_PKG. The naming convention of the files is like this:

If the file's primary name ends with an S then it creates the package specification.
If the file's primary name ends with a B then it creates the package body.

For example AFSCUSRS.pls creates package specification of FND_USER_PKG and AFSCUSRB.pls creates the package body.

After tracing the files which created the dropped packages, Boda was able to recreate all of them and restore normalcy to his instance.

Disable Anonymous access to Diagnostics

Recently we had a security scan from security team. Here's one high vulnerability item they highlighted:

Users from "guest" role can see sensitive information by calling "jtfqalgn.htm"
test page.
URIs:
· http://erp11i.justanexample.com:8000/OA_HTML/jtfqalgn.htm
Steps to Reproduce Exploit:
1. Type in the address bar the following URL:
http://erp11i.justanexample.com:8000/OA_HTML/jtfqalgn.htm and press
Enter key
2. You will see the page "Oracle Diagnostic" and you get access like "guest".
3. You can access application's test, do click on combo box and select the
application or click on registered test number.
4. Click on the "Run All Groups" button
5. You will see the report with test that you can run.
6. Click on the report image.
7. You will see test details.

Recommendations
1. Remove this test page from the production server.

Removing the page from the server is not a good solution, as further diagnostic patches will bring back the page. We have an SR logged with Oracle for this. However based on the documentation in metalink note 230331.1:

Diagnostic Roles
Diagnostic Roles determine the activities or tasks that a user can perform on
Diagnostics. Some activities of importance are:

>> Running test cases (with different input values
incase of advanced tests)
>> Viewing detailed test reports after tests have been run
>> Configuring input values for test cases
>> Adding, deleting test cases and test groups across
applications registered with Diagnostics
>> Viewing historical reports for test runs using the LogViewer

We decided that it was appropriate to permit these and other diagnostic
activities according to different roles (explained below).

Diagnostics Super User Role:
Has unrestricted privileges to execute, configure, view reports and
setup security for all groups and all applications. Out of the box,
we have granted this role to CRM Foundation application responsibility
"CRM HTML Administration" which in turn has been assigned to user: "sysadmin"

Application Super User:
Has unrestricted privileges (execute, configure, view reports and setup security for
test groups) for the application associated with his responsibility.
However, this role also permits the user to execute and configure inputs
for test groups of low and medium sensitivities across other applications.

End User:
Can execute and configure inputs for test groups of "low
sensitivity" only. This user cannot view detailed test reports.

Anonymous User:
If none of the user's responsibilities have an association with any of the above
3 roles, then the user is considered to be an Anonymous user. The Diagnostics engine
will restrict access to HTML Platform only. All other test groups across applications are restricted from this user.

Roles can be granted to users through responsibilities.
In case a responsibility has been granted a particular ROLE and that responsibility
is accessible to a user, the user automatically gets a grant on that ROLE.

So Anonymous User access is allowed by design. I have no idea how they can circumvent this without changing their existing code.

ORA-4030 during ASCP plan run due to swap issues

Recently we had an agonizing problem of ASCP Plan failing with ORA-4030 error, every time it was launched. After a lot of research, I found that it was due to a known bug and that we were running out of swap. After I added a 10GB swapfile as described in previous post, the error was resolved.

Solaris swap

Think of swap space as an overflow area for RAM. Here's how you can add swap space as given in Sun's official documentation:

How to Create a Swap File and Make It Available

1. Become superuser.

   You can create a swap file without root permissions. However, to avoid accidental overwriting, root should be the owner of the swap file.

2. Create a directory for the swap file, if needed.

3. Create the swap file.

   # mkfile nnn[k|b|m] filename

   The swap file of the size nnn (in Kbytes, bytes, or Mbytes) and filename you specify is created.

4. Activate the swap file.

   # /usr/sbin/swap -a /path/filename

   You must use the absolute path name to specify the swap file. The swap file is added and available until the file system is unmounted, the system is rebooted, or the swap file is removed. Keep in mind that you can't unmount a file system while some process or program is swapping to the swap file.

5. Add an entry for the swap file to the /etc/vfstab file that specifies the full path name of the file, and designates swap as the file system type, as follows:

   /path/filename - - swap - no -

6. Verify that the swap file is added.

   $ /usr/sbin/swap -l

Example—Creating a Swap File and Making It Available

The following examples shows how to create a 100–Mbyte swap file called /files/swapfile.

# mkdir /files
# mkfile 100m /files/swapfile
# swap -a /files/swapfile
# vi /etc/vfstab
(An entry is added for the swap file):
/files/swapfile   -      -       swap     -     no     -
# swap -l
swapfile             dev  swaplo blocks   free
/dev/dsk/c0t0d0s1   136,1      16 1638608 1600528
/files/swapfile        -       16 204784  204784

How to Remove Unneeded Swap Space

1. Become superuser.

2. Remove the swap space.

   # /usr/sbin/swap -d /path/filename

   The swap file name is removed so that it is no longer available for swapping. The file itself is not deleted.

3. Edit the /etc/vfstab file and delete the entry for the swap file.

4. Recover the disk space so that you can use it for something else.

   # rm /path/filename

   If the swap space is a file, remove it. Or, if the swap space is on a separate slice and you are sure you will not need it again, make a new file system and mount the file system.

   For information on mounting a file system, see Chapter 39, Mounting and Unmounting File Systems (Tasks).

5. Verify that the swap file is no longer available.

   # swap -l

Example—Removing UnneededSwap Space

The following examples shows how to delete the /files/swapfile swap file.

# swap -d /files/swapfile # (Remove the swap entry from the /etc/vfstab file) # rm /files/swapfile # swap -l swapfile dev swaplo blocks free /dev/dsk/c0t0d0s1 136,1 16 1638608 1600528

While searching about swap, I found a very good blog post Myths and Facts about Solaris swap space on Jim Laurent's blog.

Jim says that the rule of thumb for swap size is 30% of RAM. He has given many other links like Solaris Internals Wiki which are a must visit.

Swap works differently in Solaris 2 as compared with other Unix systems, including SunOS 4. These systems must always have some swap space, and it must be bigger than RAM. Every program in RAM has its total size reserved on the swap disk in case it needs to be swapped out to disk. Since there are systems with 5 gigabytes (SPARCcenter 2000) or more of RAM, it seems ridiculous that systems that already have huge RAM capacity would need huge swap disks that would probably not be used. Solaris 2 changes the rules by adding the RAM and the disk space. If you can buy enough RAM for your workload, you can run with no swap disk at all! In practice common database applications that are sized to run in a few gigabytes of RAM will actually need many gigabytes of disk allocated as swap space.

Exception in TimedProcess...Failed to execute command

$ adstpall.sh apps/apps

You are running adstpall.sh version 115.19

Executing service control script:
/erp11i/apperp/comn/admin/scripts/$CONTEXT_NAME/adalnctl.sh stop
script returned:
****************************************************
ERROR :
Exception in TimedProcess...Failed to execute command = /erp11i/apperp/comn/admin/scripts/$CONTEXT_NAME/adalnctl.sh stop apps/apps
****************************************************


Executing service control script:
/erp11i/apperp/comn/admin/scripts/$CONTEXT_NAME/adcmctl.sh stop
script returned:
****************************************************
ERROR :
Exception in TimedProcess...Failed to execute command = /erp11i/apperp/comn/admin/scripts/$CONTEXT_NAME/adcmctl.sh stop apps/apps
****************************************************


Executing service control script:
/erp11i/apperp/comn/admin/scripts/$CONTEXT_NAME/adrepctl.sh stop
script returned:
****************************************************
ERROR :
Exception in TimedProcess...Failed to execute command = /erp11i/apperp/comn/admin/scripts/$CONTEXT_NAME/adrepctl.sh stop apps/apps
****************************************************


Executing service control script:
/erp11i/apperp/appl/ieo/11.5.0/admin/scripts/$CONTEXT_NAME/ieoicsm.sh stop
script returned:
****************************************************
AC-00003: Service Script - /erp11i/apperp/appl/ieo/11.5.0/admin/scripts/$CONTEXT_NAME/ieoicsm.sh stop not found.
Raised by oracle.apps.ad.autoconfig.ServiceControl
****************************************************


Check logfile /erp11i/apperp/comn/admin/log/$CONTEXT_NAME/08040824.log for details

Exiting with status 4
$ adcmctl.sh stop apps/apps

You are running adcmctl.sh version 115.28

Shutting down concurrent managers for erp11i ...
Submitted request 619203 for CONCURRENT FND SHUTDOWN


adcmctl.sh: exiting with status 0

$ adalnctl.sh stop

adalnctl.sh version

Shutting down listener process APPS_erp11i.

adalnctl.sh: exiting with status 0

$ adrepctl.sh stop

You are running adrepctl.sh version 115.32

stopping Reports Server for erp11i on port 9036.
kill -9 13523
Reports Server for erp11i successfully stopped.

adrepctl.sh: exiting with status 0

$

This is most probably due to the fact that the server is maxed out on memory.

adstpall.sh and adstrtal.sh use this code to stop and start all services. $STOP is passed as a parameter to stop and $START is passed as a parameter to start

#

# Call the ServiceControl API

# Add the logfile parameter to the service control API

{ echo $unpw; } | $JAVA_TOP -classpath "${CLASSPATH}:${JAVA_TOP}:${FND_TOP}/java/3rdparty/stdalone/xmlparserv2.zip"

oracle.apps.ad.autoconfig.ServiceControl -e $APPL_TOP/admin/$CONTEXT_FILE -m $START -l $LOGFILE -debug -promptmsg hide

Each individual script like adfrmctl.sh, adapcctl.sh has the actual calls which execute the binaries. So, running each script separately worked fine.