How to Create Custom TOP in E-Business Suite

This is recommended to create your own Custom Top for all your custom needs hence it will make DBAs life easy while up-gradation of EBS.

1)  Make the directory structure for your custom application files.

 cd $APPL_TOP i.e /u01/appldev/apps/apps_st/appl

 mkdir xxaks
 mkdir xxaks/12.0.0
 mkdir xxaks/12.0.0/admin
 mkdir xxaks/12.0.0/admin/sql
 mkdir xxaks/12.0.0/admin/odf
 mkdir xxaks/12.0.0/sql
 mkdir xxaks/12.0.0/bin
 mkdir xxaks/12.0.0/reports
 mkdir xxaks/12.0.0/reports/US
 mkdir xxaks/12.0.0/forms
 mkdir xxaks/12.0.0/forms/US
 mkdir xxaks/12.0.0/$APPLLIB
 mkdir xxaks/12.0.0/$APPLOUT
 mkdir xxaks/12.0.0/$APPLLOG

2)  Add the custom module into the environment:

Add the entry in “Topfile.txt” as a standard product top entry (follow the existing model in the file)  

3) Customized environment variables needed to add in Source files.

Cd $APPL_TOP

vi customdev_dev.env or vi customprod_prod.env
XXAKS_TOP="/u01/appldev/apps/apps_st/appl/xxaks/12.0.0"
export XXAKS_TOP

SAVE THE FILE.

4) RUN AUTOCONFIG. ($INST_TOP - down apps services firsts then run on db tier then on apps tier)

Double check,

If you run $echo $XXAKS_TOP it should reflect the path if paths are set in profiles.

5)  Create new tablespace for database objects as sys or system user.

 Create tablespace xxaks datafile '/u01/oradev/db/apps_st/data/xxaks.dbf' size 500M   default storage(initial 10k next 10k)

6) Create schema

  Sql>create user xxaks identified by xxaks
  default tablespace xxaks
  temporary tablespace temp
  quota unlimited on xxaks
  quota unlimited on temp;

 sql>grant connect, resource to xxaks;

7)  Register your Oracle Schema.

 Login to Applications with System Administrator responsibility
 Navigate to Application-->Register
 Application             = xxaks Custom
 Short Name            = xxaks
 BasePath                = XXAKS_TOP
 Description             = xxaks Custom Application

8)  Register Oracle User

 Navigate to Security-->Oracle-->Register
 Database User Name  = xxaks
 Password                   = xxaks
 Privilege                     = Enabled
 Install Group              = 0
 Description                 = xxaks Custom Application User

9)  Add Application to a Data Group

 Navigate to Security-->Oracle-->DataGroup
 Query the” Data Group  = standard”

 Insert the following lines.

 Application = xxaks Custom
 Oracle ID   = APPS
 Description = xxaks Custom Application

Run a report/ concurrent program from the OS level

In special circumstance we might have to run a report/ concurrent program from the Operating System level without having to log on to Oracle Applications, you have to use CONCSUB utility for this purpose.

This utility can be found at $FND_TOP/bin

[applvis@oracle]$ find . -name CONCSUB
/u01/applvis/apps/apps_st/appl/fnd/12.0.0/bin/CONCSUB

Here are the CONCSUB Parameter Arguments:

NOTE: In green you can see the mandatory parameters.

<APPS username>/<APPS password> \
<Responsibility Application Short Name> \
<Responsibility Name> \
<User Name> \
[WAIT=N|Y|<n seconds>] \
CONCURRENT \
<Program application short name> \
<Program Name>  [PROGRAM_NAME=”<description>”] \
[REPEAT_TIME=<resubmission time>] \
[REPEAT_INTERVAL=<number>] \
[REPEAT_INTERVAL_UNIT=<resubmissionunit>] \
[REPEAT_INTERVAL_TYPE=<resubmissiontype>]
[REPEAT_END=<resubmission end date andtime>] \
[START=<Requested Start Date>] \
[REPEAT_DAYS=<number of days>] \
[IMPLICIT=<type of concurrent request> ] [PRINTER=<name of printer>] \
[NUMBER_OF_COPIES=<number of copies>]

Default values:

WAIT=N

REPEAT_INTERVAL_UNIT=DAYS

START = current time

IMPLICIT=NO

Example:

[applvis@oracle]$ cd $FND_TOP/bin
[applvis@oracle bin]$ Source your APPS Environment file.

[applvis@oracle bin]$ ./CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN WAIT=N CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
Submitted request 2720951 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users"

This report is the equivalent of :

 

NOTES: 

·                    the parameters are added at the end of the command

·                    The log and out file for this program is created at the $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT 

·                    for a NULL parameter we have to use '""'

·                    WAIT=Y/N is used to specify whether to wait for the first concurrent request to be completed before the second is submitted or not.

·                    CONCSUB utility could be used to manage the Concurrent Managers:

          Startup the ICM:

          cd $FND_TOP/bin

          startmgr.sh sysmgr=apps/apps@VIS
 

          Shutdown the ICM:

CONCSUB apps/apps SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND SHUTDOWN

CONCSUB apps/apps SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND ABORT   

R12 System Architecture

Base Directory: The directory where the Oracle Applications is installed. This is a generic directory. 

                          
 I will suppose that the Base Directory is /APPS

$ORACLE_HOME (for the database tier): /APPS/db/tech_st/10.2.0

$APPL_TOP:   /APPS/apps/apps_st/appl

$COMMON_TOP:  /APPS/apps/apps_st/comn

$ORACLE_HOME (for the apps tier): /APPS/apps/tect_st/10.1.2

$IAS_ORACLE_HOME (for the apps tier): /APPS/apps/tect_st/10.1.3

$INST_TOP:   /APPS/inst/apps/<SID_localhost> contains certifications, configurations, logs for the servers.

Oracle eBusiness Suite Electronic Technical Reference

For Technical Reference of EBS objects, please go through below site with proper metalink userid.

http://etrm.oracle.com/

Download Oracle APPS from edelivery

We can downlaod all oracle software from below link.

http://edelivery.oracle.com

Import/Export in Oracle Database

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files. It is the least option of backup and recoveries but it is useful in different scenarios.

 Modes of Import/Export

a) Full export/export

       The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export. 

b) Tablespace
Use the tablespaces export parameter for a tablespace export.

c) User
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import. 

d) Table
Specific tables (or partitions) can be exported/imported with table export mode. Use the tables export parameter for a table export/ import mode. 

 Possibility of exp/ imp to multiple files

Yes, is possible. Here is an example:  

exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log

 Use of exp/ imp in different Oracle database versions

·         exp must be of the lower version

·         imp must match the target version

Steps before importing database objects

Before importing database objects, we have to drop or truncate the objects, if not, the data will be added to the objects. If the sequences are not dropped, the sequences will generate inconsistent values.  If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import.
  

Possible to import a table in a different tablespace

By default we do not have possibility do to this. Because there is no tablespace parameter for the import operation.

However this could be done in the following manner:

·                    (re)create the table in another tablespace (the table will be empty)

·                    import the table using INDEXFILE parameter (the import is not  done, but a file which contains the indexes creation is generated)

·                    modify this script to create the indexes in the tablespace we want

·                    import the table using IGNORE=y option (because the table exists)

·                    recreate the indexes

 Frequent use of  imp/exp

Eliminate database fragmentation

·                    Schema refresh (move the schema from one database to another)

·                    Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption)

·                    Transporting tablespaces between databases

·                    Backup database objects

Enhancement of EXPORT performance

·                     Set the BUFFER parameter to a high value (e.g. 2M)

·                     If you run multiple export sessions, ensure they write to different physical disks. 

Enhancement of IMPORT performance

·         Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes

·         Store the dump file to be imported on a separate physical disk from the oracle data files

·         If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import

·         Set the BUFFER parameter to a high value (ex. BUFFER=30000000 (~30MB)  ) and COMMIT =y  or set COMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.)

·         use the direct path to import the data (DIRECT=y)

·         (if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init<SID>.ora file

·         (if possible) Set the LOG_BUFFER to a big value and restart oracle.

  Available EXP options

·         Keyword    Description (Default)      Keyword      Description (Default)

·         --------------------------------------------------------------------------

·         USERID     username/password          FULL         export entire file (N)

·         BUFFER     size of data buffer        OWNER        list of owner usernames

·         FILE       output files (EXPDAT.DMP)  TABLES       list of table names

·         COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record

·         GRANTS     export grants (Y)          INCTYPE      incremental export type

·         INDEXES    export indexes (Y)         RECORD       track incr. export (Y)

·         DIRECT     direct path (N)            TRIGGERS     export triggers (Y)

·         LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)

·         ROWS       export data rows (Y)       PARFILE      parameter filename

·         CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

·        

·         OBJECT_CONSISTENT    transaction set to read only during object export (N)

·         FEEDBACK             display progress every x rows (0)

·         FILESIZE             maximum size of each dump file

·         FLASHBACK_SCN        SCN used to set session snapshot back to

·         FLASHBACK_TIME       time used to get the SCN closest to the specified time

·         QUERY                select clause used to export a subset of a table

·         RESUMABLE            suspend when a space related error is encountered(N)

·         RESUMABLE_NAME       text string used to identify resumable statement

·         RESUMABLE_TIMEOUT    wait time for RESUMABLE

·         TTS_FULL_CHECK       perform full or partial dependency check for TTS

·         VOLSIZE              number of bytes to write to each tape volume

·         TABLESPACES          list of tablespaces to export

·         TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

·         TEMPLATE             template name which invokes iAS mode export 

Example: exp system/s              file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

      or      exp userid=system/s file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

USERID must be the first parameter on the command line.

  

Available IMP options

Keyword  Description (Default)       Keyword      Description (Default)

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

USERID   username/password           FULL         import entire file (N)

BUFFER   size of data buffer         FROMUSER     list of owner usernames

FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames

SHOW     just list file contents (N) TABLES       list of table names

IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record

GRANTS   import grants (Y)           INCTYPE      incremental import type

INDEXES  import indexes (Y)          COMMIT       commit array insert (N)

ROWS     import data rows (Y)        PARFILE      parameter filename

LOG      log file of screen output   CONSTRAINTS  import constraints (Y)

DESTROY                overwrite tablespace data file (N)

INDEXFILE              write table/index info to specified file

SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)

FEEDBACK               display progress every x rows(0)

TOID_NOVALIDATE        skip validation of specified type ids

FILESIZE               maximum size of each dump file

STATISTICS             import precomputed statistics (always)

RESUMABLE              suspend when a space related error is encountered(N)

RESUMABLE_NAME         text string used to identify resumable statement

RESUMABLE_TIMEOUT      wait time for RESUMABLE

COMPILE                compile procedures, packages, and functions (Y)

STREAMS_CONFIGURATION  import streams general metadata (Y)

STREAMS_INSTANTIATION  import streams instantiation metadata (N)

VOLSIZE                number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE import transportable tablespace metadata (N)

TABLESPACES tablespaces to be transported into database

DATAFILES datafiles to be transported into database

TTS_OWNERS users that own data in the transportable tablespace set

Example:  imp system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs     (Unix)

   or          imp userid=system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs   (Unix)

Note: USERID must be the first parameter on the command line.

 Common IMP/EXP errors

·                     ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh..

·                     IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

·                     ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).

·                     ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter ) while importing.