Oracle Applications DBA Blog

Wednesday, March 28, 2012

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.

Wednesday, March 14, 2012

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.