AFPASSWD - New password utility in EBS 12.1.2

AFPASSWD -New password utility in EBS 12.1.2:

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

AFPASSWD:

In R12.1.1 and before oracle EBS Versions EBS passwords are changed using FNDCPASS.

From R12.1.2 Oracle has come up with another Utility AFPASSWD for the same purpose.

Advantage is that you can run AFPASSWD utility from DB tier or from Midtier where as FNDCPASS can only be run from midtier side.

AFPASSWD is an enhanced version of FNDCPASS, and includes the following features:

     AFPASSWD only prompts for passwords required for the current operation,

allowing separation of duties between applications administrators and database administrators.

This also improves interoperability with Oracle Database Vault. In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.

   

     When changing a password with AFPASSWD, the user is prompted to enter the

New password twice to confirm.

  

     AFPASSWD can be run from the database tier as well as the application tier. In

contrast, FNDCPASS can only be run from the application tier.

AFPASSWD Usage and Syntax:

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-f <FNDUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-o <DBUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-a]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-l <ORACLEUSER> [<TRUE>] |[<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-L [<TRUE>] | [<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-s] <APPLSYS>

These options have the following functions:

• -c {APPSUSER}[@{TWO_TASK}] - Specifies the connection string to use, the

Applications user, and/or the value of TWO_TASK. This option can be use in

combination with others. If it is not specified, default values from the environment

will be used.

Note: The password will be prompted for, and is not to be

provided in the connection string.

• -f {FNDUSER} - Changes the password for an Applications user. A username that

contains spaces must be enclosed in double quotation marks; for example, "JOHN

SMITH".

• -o {DBUSER} - Changes the password for an Oracle E-Business Suite database user.

Note: This only applies to users listed in the FND_ORACLE_USERID table, not database users in general.

•  -a - Changes all Oracle (ALLORACLE) passwords (except the passwords of APPS,APPLSYS, APPLSYSPUB) to the same password, in the same way as the ALLORACLE mode does in FNDCPASS.

•  -l - Locks individual {ORACLE_USER} users (except required schemas). {TRUE} =LOCK, {FALSE} = UNLOCK.

•  -L - Locks all Oracle (ALLORACLE) users (except required schemas). {TRUE} =LOCK, {FALSE} = UNLOCK.

•  -s {APPLSYS} - Changes the password for the APPLSYS user and the APPS user. This requires the execution of autoconfig (in ALL-TIERS) to distribute the changes on your instance \

Ie: DB-TIER and ADMIN-TIER.

•  -h - Displays help.

 AFPASSWD Examples:

 AFPASSWD Examples:

FNDUSER:

DBUSER:

APPLSYS:

Run autoconfig on db tier and application tier after changing apps password:

Db side:

Application side:

Enable a trace for a concurrent program

How to enable a trace for a concurrent program:

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

Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.
After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.
SQL to identify the trace file
select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];
DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*
P.S: Dont forget to disable the trace

find and remove files older then 30 days

find and remove files older then 30 days:

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

:find /eb_rao0_p/applmgr/pnoltpcomn/admin/log/pnoltp_auxpap02/*.log  -mtime +30 -exec rm  {} \;

find tablespaces consuming more then 85% space

find tablespaces consuming more then 85% space:

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

set lines 999
set pagesize 999
select tablespace_name, ROUND(bytes_free,2) "Free Space (MB)" , ROUND(bytes_total-bytes_free,2) "Used Space (MB)",
ROUND(bytes_total,2) "Total Size (MB)", perused "% Used", 100-perused "% Free" from
(select a.tablespace_name, a.bytes_free,b.bytes_total, ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 85 and tablespace_name not like '%UNDO%'  ORDER BY 6 desc;

Oracle APPS Basics:

Oracle APPS Basics:================

What is .dbc file , where its stored , whats use of .dbc file ?
dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

What is an UTL_FILE? What are different procedures and functions associated with it?
The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

What is different between TRUNCATE and DELETE?
The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

What is main concurrent Manager types.
# ICM - Internal Concurrent Manager which manages concurrent Managers
# Standard Managers - Which Manage processesing of requests.
# CRM - Conflict Resolution Managers , resolve conflicts in case of incompatibility.

Whats is difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP ?
APPS<CONTEXT>.env is main environment file which inturn calls other environment files like <CONTEXT>.env under $APPL_TOP, <CONTEXT>.env under 806 ORACLE_HOME and custom.env for any Customized environment files.

Where would I find .rf9 file, and what it dose ?
These files are used during restart of patch in case of patch failure because of some reason


What is US directory in $AD_TOP or under various product TOP's .
US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored and why its used ?
This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in directory $OA_HTML/bin on forms tier.
This file is used by any forms client session. When a user try to access forms , f60webmx picks up this file and based on this configuration file creates a forms session to user/client

What is Multi Node System ?
Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database, Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

What are things you do to reduce patch timing ?
You can take advantage of following -
# Merging patches via admrgpch
# Use various adpatch options like nocompiledb or nocompilejsp
# Use defaults file
# Staged APPL_TOP during upgrades
# Increase batch size (Might result into negative )

ADIDENT UTILITY is used for what ?
ADIDENT UTILITY in ORACLE application is used to find version of any file . AD Identification. for ex. "adident Header <filename>

What is iAS Patch ?
iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shiiped as Shell scripts and you apply iAS patches by executing Shell script. Note that by default ORACLE_HOME is pointing to 8.0.6 ORACLE_HOME and if you are applying iAS patch export ORACLE_HOME to iAS . You can do same by executing environment file under $IAS_ORACLE_HOME

Where to check log files after autoconfig is executed ?
On database tier its under $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MM_DD_YYYY/adconfig.log
On Application tier its under $APPL_TOP/admin/$CONTEXT_NAME/log/MMDDYYYY/adconfig.log

How can I roll back an AutoConfig session?
All backup configuration files from each AutoConfig session are stored in:
On the application tier: /admin//out//
On the database tier: /appsutil/out///
Inside these directories, there will be several files which autoconfig has taken a backup off before making changes. Also there will be a script restore.sh.
You can run restore.sh (Unix) or restore.cmd (Windows) to roll back an AutoConfig session.
You can find adautocfg.sh script for Database tier in $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME and for Application tier in $COMMN_TOP/admin/scripts/$CONTEXT_NAME

What is difference between .xml file and AutoConfig ?
Autoconfig is Utility to configure your Oracle Application environment. .xml file is repository of all configuration from which AutoConfig picks configuration and polulates related files.

What is .lgi files ?
lgi files are created with patching along with .log files . .lgi files are informative log files containing information related to patch. You can check .lgi files to see what activities patch has done. Usually informative logs.

How will you skip worker during patch ?
If in your adctrl there are six option shown then seventh is hidden option.(If there are seven options visible then 8th option is to Skip worker depending on ad version).

Which two tables created at start of application Patch and drops at end of Patch ?
FND_INSTALLED_PROCESS and AD_DEFFERED_JOBS are the tables that get updated while applying a patch mainly d or unified driver.

How to compile an Oracle Reports file ?
Utility adrepgen is used to compile Reports. Synatx is given below adrepgen userid=apps\<psswd> source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

What is difference between AD_BUGS and AD_APPLID_PATCHES ?
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records

What is ADSPLICE UTILITY ?
ADSPLICE UTILITY in ORACLE application is utility to add a new product.

What is access_log in apache , what entries are recored in access_log ? Where is default location of this file ?
access_log in Oracle Application Server records all users accessing oracle applications 11i. This file location is defined in httpd.conf with default location at $IAS_ORACLE_HOME/Apache/Apache/logs. Entries in this file is defined by directive LogFormat in httpd.conf Typical entry in access_log is
198.0.0.1 - - [10/Sep/2006:18:37:17 +0100] "POST /OA_HTML/OA.jsp?.... HTTP/1.1" 200 28035
where 200 is HTTP status code and last digits 28035 is bytes dowloaded as this page(Size of page)

How can you licence a product after installation ?
You can use ad utility adlicmgr to licence product in Oracle application.

What is MRC ? What you do as application DBA for MRC ?
MRC also called as Multiple Reporting Currency in oracle application. Default you have currency in US Dollars but if your organization operating books are in other currency then you as application DBA need to enable MRC in applications. How to enable MRC coming soon...

Where is Jserv configuration files stored ?
Jserv configuration files are stored in $IAS_ORACLE_HOME/Apache/Jserv/etc

How to compile JSP in Oracle Applications ?
You can use ojspCompile.pl perl script shipped with Oracle Applications to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is
perl ojspCompile.pl --compile --quiet

Whats is location of access_log file ?
access_log file by default is located in $IAS_ORACLE_HOME/ Apache/Apache/logs. Location of this file is defined in httpd.conf by patameter CustomLog or TransferLog

What is your Oracle application 11i Webserver Version and how to find it ?
From 11.5.8 to 11.5.10 Webserver version is iAS 1.0.2.2.2, In order to find version under $IAS_ORACLE_HOME/Apache/Apache/bin execute ./httpd -version
./httpd -version
Server version: Oracle HTTP Server Powered by Apache/1.3.19
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)

What is Location of Jserv configuration files ?
Jserv configuration files are located in $IAS_ORACLE_HOME /Apache/Jserv/etc

What is *.DBC file and whats is location of DBC file ?
DBC as name stands for is database connect descriptor file used to connect to database. This file by default located in $FND_TOP/secure directory also called as $FND_SECURE directory.

What are few profile options which you update after cloning ?
Rapid clone updates profile options specific to site level . If you have any profile option set at other levels like server, responsibility, user....level then reset them


What is TWO_TASK in Oracle Database ?
TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don't want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

Where GWYUID defined and what is its used in Oracle Applications ?
GWYUID is defined in dbc i.e. Database Connect Descriptor file . It is used to connect to database by think clients.

Where will you find forms configuration details apart from xml file ?
Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg (defined by environment variable FORMS60_WEB_CONFIG_FILE) for forms client connection used each time a user initiates forms connection.

What is forms server executable Name ?
f60srvm

What are different modes of forms in which you can start Forms Server and which one is default ?
You can start forms server in SOCKET or SERVLET by defualt Forms are configured to start in socket mode.

What are the Back ground processes in Oracle and what are they.
This is one of the most frequently asked question.There are basically 9 Processes but in a general system we need to mention the first five background processes.They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

Oracle Apps DBA important scripts

oracle Apps DBA important scripts:

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

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2.Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';


3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

4.To find sid,serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


5.To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;


6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'
/

OPatch failed with error code 1:

CauseIssue:
=====
Opatch Fails with Exception in thread "main" java.lang.NoClassDefFoundError: # OPatch failed with error code 1:

Solution:
======

Applies to: 
Oracle Server - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Opatch fails with errors:
$ORACLE_HOME/OPatch/opatch lsinventory
Exception in thread "main" java.lang.NoClassDefFoundError: #

OPatch failed with error code 1

11i/R12 - Daily Checks (for Apps. DBA)

 EBusiness 11i/R12 - Daily Checks (for Apps. DBA) - Admin Node:

As Oracle Apps DBA / Sysadmin you Apply patches & restart servives or make some configuration changes in applications 11i before releasing Instance to Application Users you wish to check if all components in E-Business Suite 11i are working fine. Here is list of components you should check apart from your custom services.   1. Check Web Server is Up : Check in browser http://hostname.domainname:port where hostname.domain name is Machine Name on which Web Server(OHS) is running. If you are using load balancer or Proxy Server in front of Web Server it will be loadbalancer / Proxy Server Name. Port is port on which Web Server is Listening. It is defined in $IAS_ORACLE_HOME /Apache /Apache /conf /httpd.conf , To find more about WebServer & its parameters Oracle Web Server also called as OHS (Oracle Http Server) or Oracle-Apache is built on Apache Webserver. Oracle has added few custom component (like mod_plsql, mod_osso...) in Apache webserver & shipped it as Oracle Http Server. Discussion in this page is currently w.r.t. Oracle Http Server Version 1.0.2.2.2 which is shipped with 11i (11.5.8-10). In coming weeks I'll add 10G Application Server Integration with Oracle Applications 11i. Oracle Web Server or OHS is entry point in applications, It processes requests coming from network over Client. There is Web Listener listening for Web Requests. If request is for simple html page core Apace returns HTML page but if URL refernced need further processing it will be forwarded to respective module like mod_pls, mod_jserv ... I will start with basics like how to start stop webserver, important directories & files , troubleshooting , tuning & important things to know in oracle applications E-Business suite Webserver. How to Start/Stop Web Server All startup shutdown scripts in Oracle Applications 11i(11.5.10) are in $OAD_TOP/admin/script/$CONTEXT_NAME where OAD_TOP is also called as COMMON_TOP and CONTEXT_NAME is your SID_hostname , lets say your SID or Instance Name is VISION and its on machine with name machine1 so your CONTEXT_NAME will be VISION_machine1. Script to start/stop Web Server is adapcctl.sh where ad (application dba), apc(apache), ctl(control) and sh stands for shell script. (You will find this script only on node on which you have webserver, if you have multi mode Installation and node 1 is only for Concurrent Manager, Reports & Database and node2 is webserver & forms then adapcctl.sh should be on node2 & not node1) adapcctl.sh callsapachectl(shellscriptin $IAS_ORACLE_HOME/Apache/Apache/bin) which in turn calls HTTPD (in same directory which is executable called http daemon) it starts using configuration filehttpd.conf (from $IAS_ORACLE_HOME/Apache/Apache/conf directory , this file calls other configuration file. To know all important configuration file follow config files link)  . If you can't access this check error_log at $IAS_ORACLE_HOME /Apache /Apache/ logs

Linux Server Monitoring Commands

 Linux Server Monitoring Commands:

Here you will get all Good tricks and tips how to monitor Linux Server or how to manage Linux Server if its causing load,  How to find Spammer, how to check http and mysql processes.

General Commands :-

To check server load and which users are logged on the server with IP address you can fire this command
           w

To check for the server load and watch for process

top

top –d2

top –c d2

Memory status

free –m

To see all processes running on the server

ps –aufx

With above commands you can which process is causing load on the server after that you can go with next steps.

If you see many processes of exim then you can check exim in more detail. shows the total no of email in qmail

R12 CLONING

R12 CLONING:

Cloning Oracle Applications Release 12 with Rapid Clone

This knowledge document describes the process of cloning an Oracle Applications Release 12 system.

The content applies to all Release 12.x.x versions, such as 12.0, 12.0.4, and 12.1.x. Where applicable, 12.0.x releases will in general be referred to as Release 12.0, and 12.1.x releases as Release 12.1.

The most current version of this document can be obtained in Oracle Support Knowledge Document 406982.1.

There is a change log at the end of this document.

In This Document

• Section 1: Prerequisite Tasks
• Section 2: Cloning Tasks
• Section 3: Finishing Tasks
• Section 4: Advanced Cloning Options