Check which generic users are active in your Apps 12.1.x instance

/* Formatted on 28-Mar-12 3:22:03 PM (QP5 v5.163.1008.3004) */
SELECT a.user_name,
   a.description,
   a.email_address email_address,
   a.start_date,
   a.employee_id,
   a.password_lifespan_days
  FROM fnd_user a
 WHERE a.end_date IS NULL
   AND a.user_name IN
      ('GUEST',
     'AME_INVALID_APPROVER',
     'ANONYMOUS',
     'APPSMGR',
     'ASGADM',
     'ASGUEST',
     'AUTOINSTALL',
     'BOL-OPS',
     'BOL-SETUP',
     'BOL-SUPPORT',
     'CONCURRENT MANAGER',
     'FEEDER SYSTEM',
     'IBE_ADMIN',
     'IBE_GUEST',
     'IBEGUEST',
     'IEXADMIN',
     'INITIAL SETUP',
     'IRC_EMP_GUEST',
     'IRC_EXT_GUEST',
     'MOBILEADM',
     'MOBADM',
     'MOBDEV',
     'OP_CUST_CARE_ADMIN',
     'OP_SYSADMIN',
     'PORTAL30',
     'PORTAL30_SSO',
     'STANDALONE BATCH PROCESS',
     'SYSADMIN',
     'WIZARD',
     'XML_USER',
     'ORACLE12.0.0',
     'ORACLE12.1.0',
     'ORACLE12.2.0',
     'ORACLE12.3.0',
     'ORACLE12.4.0',
     'ORACLE12.5.0',
     'ORACLE12.6.0',
     'ORACLE12.7.0',
     'ORACLE12.8.0',
     'ORACLE12.9.0',
     'INDUSTRY DATA',
     'ASADMIN',
     'OA_IMPLEMENTER',
     'PASYSADMIN',
     'MFG',
     'PRESETUP',
     'SETUP',
     'CONVERSION',
     'CONVERSIONS')

Flashback Database 11g

To enable flashback database login as sysdba and execute the following:

SQL> alter system set db_recovery_file_dest_size=12G scope=both;

SQL> alter system set db_recovery_file_dest='/oraarch/oracle/flash_recovery_area' scope=both;

SQL> alter system set db_flashback_retention_target = 2880 scope=both;

SQL>  shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

SQL> alter database open;

　
To restore to a previous state:

SQL> shutdown immediate;

 SQL> startup mount exclusive;

-- be sure to substitute your SCN

SQL> FLASHBACK DATABASE TO SCN 19513917;

or

SQL> FLASHBACK DATABASE TO RESTORE POINT bef_damage;

 or

 SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

or

 SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp'2012-03-27 14:00:00';

or

 SQL> FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2012-03-27 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

SQL> alter database open resetlogs;

　

Flashback Related Database Views

GV_$FLASHBACK_DATABASE_LOG

V_$FLASHBACK_DATABASE_LOG

GV_$FLASHBACK_DATABASE_LOGFILE

V_$FLASHBACK_DATABASE_LOGFILE

GV_$FLASHBACK_DATABASE_STAT

MOUNT CIFS STORAGE DRIVE IN LINUX

1. Login to Linux box as root user
2. Check if you can ping the external network attached storage
    $ ping x.x.x.x (IP)

3. Create a directory in Linux on which you want to mount this external storage
    $ cd /
    $ mkdir xyz

4. Issue the following command
    $ mount -t cifs -o username=xxxxx,password=********   //1.2.3.4/abc   /xyz
   
Where 1.2.3.4 is IP of storage (replace it with you actual IP), "/abc" is the folder that we want to mount and "/xyz" is the local folder that we have created in setp 3.

OR

Make an entry in fstab so that this may be mounted automatically on server boot.

$ vi /etc/fstab

$ //1.2.3.4/abc   /xyz  cifs username=xxxxxx,password=***********    0    0

Delete large number of files in Linux

$ cd target dir

$ find -mtime +20 -exec rm -rf {} \;

It will delete all the files and sub-directories in target directoy which were modified more than 20 days  ago

Count number of files in a directory in Linux

$ ls -1 | wc -l

Concurrent Manager Status Shows 'System Hold Fix Manager before resetting counters'

To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
    Please make sure that no FNDLIBR, FNDSM, or any dead process is running.

2. Stop the database.

3. Start the database.

4. cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDLIBR"
$ adrelink.sh force=y "fnd FNDFS"
$ adrelink.sh force=y "fnd FNDCRM"
$ adrelink.sh force=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit). Article- ID : 134007.1
Title: CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
    select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
    CONCURRENT_QUEUE_NAME like 'FNDSM%';
7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

Oracle Applications and Database Monitoring Tools

Database Monitoring Tools

Database management involves the monitoring, administration, and maintenance of the databases and database groups in your enterprise. Enterprise Manager is the premier tool for managing your database environment.

With Enterprise Manager, you receive:

·         A complete set of integrated features for managing Oracle Databases

·         Unparalleled scalability that lets you manage a single database or thousands of instances

·         An intuitive management product that leads the industry in ease of deployment and use

There are two flavors of Enterprise Manager

1.       Oracle Enterprise Manager DBCosole or DBControl

2.       Oracle Enterprise Manager Grid Control

OEM DBConsole

·         Database Control is the Enterprise Manager Web-based application for managing Oracle Database 11g Release 1 (11.1) and later

·         Database Control is installed and available with every Oracle Database 11g installation

·         From Database Control, you can monitor and administer a single Oracle Database instance

·         There is no extra hardware, software or licensing cost to use it

OEM Grid Control

·         Grid Control is the Enterprise Manager console you use to centrally manage your entire Oracle environment

·         Within Grid Control, you access the multiple database targets using the Targets tab, then Databases

·         It also provide GUI interface for Oracle Data Guard

·         Separate server is required to install OEM 11g Grid Control

·         Additional software installation and licensing of the following is required to use OEM Grid Control

1.       Oracle WebLogic Server 11g Release 1

2.       Oracle Enterprise Manager Grid Control 11g Release 1

3.       Oracle Database 11g Release 1 or 2

Oracle E-Business Suite Monitoring Tools

The Oracle E-Business Suite Plug-in 4.0 can be used to manage Oracle E-Business Suite systems.

• The Oracle E-Business Suite Plug-in 4.0 includes functionality that was available in previous releases of the Oracle Application Management Pack for E-Business Suite (AMP) and the Oracle Application Change Management Pack for E-Business Suite (ACMP).
• All of the functionality that was previously available for AMP is now classified as "System Management for E-Business Suite".
• All of the functionality that was previously available for ACMP is now classified as "Change Management for E-Business Suite".

System Management for Oracle E-Business Suite

Oracle E-Business Suite Plug-in extends Enterprise Manager Grid Control to monitor and manage Oracle E-Business Suite systems.

This new release of Oracle E-Business Suite Plug-in offers the following key capabilities:

• Oracle Enterprise Manager 11g Grid Control Support: All components of the management suite are certified with Oracle Enterprise Manager 11g Grid Control.
• Built-in Diagnostic Ability: Release 4.0 has numerous major enhancements that provide the necessary intelligence to determine if the product has been installed and configured correctly. There are diagnostics for Discovery, Cloning, and User Monitoring that will validate if the appropriate patches, privileges, setups, and profile options have been configured. This feature improves the setup and configuration time to be up and operational.

Change Management for Oracle E-Business Suite

Oracle E-Business Suite Plug-in is Oracle's offering for customers to monitor and manage Oracle E-Business Suite changes. Application Change Management Pack Plug-in provides a centralized view to monitor and orchestrate changes (both functional and technical) across multiple Oracle E-Business Suite systems.

In Release 4.0 Oracle E-Business Suite Plug-in has been enhanced to provide more control and flexibility in managing Oracle E-Business Suite changes.

Signon Profile Options

Profile Option	Description	Default Value	My Recommendation
Signon Password Failure Limit	The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled.	NULL	5
Signon Password Hard to Guess	The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules: ·         The password contains at least one letter and at least one number. ·         The password does not contain the username. ·         The password does not contain repeating characters	No	Yes
Signon Password Length	Signon Password Length sets the minimum length of an Applications signon password.	5	7
Signon Password No Reuse	This profile option specifies the number of days that a user must wait before being allowed to reuse a password.	NULL	90
Signon Password Case	Oracle Applications gives you the ability to control case sensitivity in user passwords through this profile option.	Insensitive	Sensitive
ICX: Session Timeout	This profile option determines the length of time (in minutes) of inactivity in a user’s session before the session is disabled. If the user does not perform any operation in Oracle Applications for longer than this value, the session is disabled. The user is provided the opportunity to re-authenticate and re-enable a timed-out session. If re-authentication is successful, the session is re-enabled and no work is lost. Otherwise, Oracle Applications exit without saving pending work.	30 Minutes	30 Minutes

Limit concurrent connections?

Doc 375403.1 - "How Can I Restrict Applications Users To Be

Signed In Only Once At Any Time"

Query to show which database features are installed?

select * from dba_registry;

HOW TO KILL A PROCESS

$ kill -9 process_id

HOW TO CHECK CONCURRENT MANAGER SESSIONS

$ ps -ef | grep FNDLIBR

HOW TO COMPILE A 10g FORM ON LINUX

/oracle/apps/tech_st/10.1.2/bin/frmcmp_batch
module=<complete path of form file name .fmb> userid=APPS/<APPS password \
output_file=<complete path of form file name .fmx> module_type=form \
batch=yes compile_all=special

HOW TO PASS PARAMETERS TO A PROCEDURE IN FORM PERSONALIZATION

On the personalization form select the "Action" tab and choose the type "Builtin".
Select the "Execute a Procedure" and you can call your database procedure as follows:

='declare
v_field_value VARCHAR2(200);
begin
xx_procedure_name ('''||${item.block_name.fieled_name1.value}||''','''||${item.block_name2.fieled_name.value}||''');
end'

By using this example you can pass as many parameters as you want to you procedure.

HOW TO AUTOMATE ADPRECLONE ON DB NODE

If you have to run adprelcone.pl script on DB node it askes for apps user password. If you have scheduled backup to run on mid-night and you want to run adpreclone.pl before backup without entering apps user password manually. You will need to tweak the adpreclone.pl to remove the 'showProgress' flag...

# Use seeded script as basis for a non-interactive version by removing 'showProgress' flags

cp adpreclone.pl adprecloneBatch.pl

ex - adprecloneBatch.pl <<EOF

set noic

g/showProgress/s///g

wq

EOF

# Now run this version

perl adprecloneBatch.pl dbTier pwd=${PASSWD}

How to Clear Application Cache

Login to application with the user who have access to Functional Administrator responsibility.

1- Navigate to Functional Administrator Responsibility

2- Choose the " Core Services" Tab

3- Choose "Caching Framework"

4- Click on "Global Configuration"

5- Click on "Clear All Cache"

6- The Click on yes on the display which will appear.

CHECK IF ALL THE MIDDLE TEIR SERVICES ARE UP AND RUNNING

Login to apps tier node.
Source the apps tier environment file and run.

$ adopmnctl.sh status -l

Check the status of OC4J:oafm, OC4J:forms, OC4J:oacore and HTTP_Server process and it should return 'Alive' against all.

How to identify and unlock table locks

/* Identify locked objects */

SELECT

object_name, v.session_id SID, v.oracle_username, TYPE, lmode, request

FROM v$locked_object v, v$lock l, dba_objects o

WHERE l.SID = v.session_id AND v.object_id = o.object_id AND l.BLOCK > 0;

　

/* Identify who locks whom */
SELECT

(SELECT osuser

FROM v$session

WHERE SID = a.SID) blocker, a.SID, (SELECT serial#

FROM v$session

WHERE SID = a.SID) serial#,

' blocks ', (SELECT osuser

FROM v$session

WHERE SID = b.SID) blockee, b.SID, c.username username

FROM v$lock a, v$lock b, v$session c

WHERE a.BLOCK = 1

AND b.request > 0

AND a.id1 = b.id1

AND a.id2 = b.id2

AND b.SID = c.SID

　

/* login as SYSDBA and kill blocking session */
alter
system kill session 'sid,serial#';

How to check database is using pfile or spfile

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';