How FNDLOAD Utility is useful for Oracle Apps DBA

Hi,
FNDLOAD(Generic Loader) utility is very useful for Apps DBA.Let us try to understand how it works and how we can utilize this utility well.

Understanding FNDLOAD utility:
The Generic Loader (FNDLOAD) is a concurrent program that can download data from an application entity into a portable,editable text file. This file can then be uploaded into any other database to copy the data.
Data structures supported by the Loader include master- detail relationships and foreign key relationships.
FNDLOAD uses script to ensure consistent migration of objects within Oracle Applications

FNDLOAD utility Modes of Operation:

The FNDLOAD(Generic Loader) utility operates in 2 modes:
1)Download mode or
2)Upload mode
In the download mode data is downloaded from a database according to a configuration (.lct) file and then converts the data into a Data (.ldt) file. This data file can be uploaded to a different database.In both downloading and uploading, the structure of the data involved is described by a configuration file.
The configuration file describes the structure of the data and also the access methods use to copy the data into or out of the database.
The same configuration file may be used for both uploading and downloading.
When downloading,the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading.
The data file has a standard syntax for representing the data that has been downloaded.

When uploading,the Generic Loader reads a data file to get the data that it is to upload.In most cases, the data file was produced by a previous download, but may have come from another source.
The data file cannot be interpreted without the corresponding configuration file available.


FNDLOAD utility syntax:

DOWNLOAD COMMAND SYNTAX:

FNDLOAD 0 Y DOWNLOAD <${FND_TOP}/patch/115/import/

UPLOAD COMMAND SYNTAX:

FNDLOAD 0 Y UPLOAD <${FND_TOP}/patch/115/import/

FNDLOAD Usage Details:

FNDLOAD can be used to migrate the following system administrator objects between instances
1.Printer Styles
2.Lookup Types and codes
3.Descriptive Flexfield (DFF)
4.Key Flexfield (KFF)
5.Concurrent programs with the parameters
6.Request Sets (when the programs are not triggered based on success)
7.Value Sets and Value set Values
8.Profiles
9.Request Groups
10.Responsibilities
11.Forms
12.Functions
13.Menus
14.Messages

Merits of FNDLOAD utility:

1.Need to maintain a baseline environment (Source for Clone) and update it on a regular basis
2.Base environment can have issues and the cloning strategy made totally ineffective. In such a case every new environment created will have to be updated with a lot of changes
3.Cloning /Refresh not possible in short intervals
4.Selective replication of setups and AOL objects not possible with cloning
5.Environments not delivered as per timeline and affecting the Testing schedules
6.Manually maintaining Environments at different level of configuration is tedious
7.Time consuming to manually update multiple environment with defect fixes and error prone
8.FNDLOAD is fully supported and recommended by Oracle for migration of FND objects. Requires 0 learning curve and 0 investment.

Demerits of FNDLOAD utility:


1.This utility can be only used for FND (System administrator) objects only.
2.Application Patching mechanisms use FNDLOAD heavily. There is a possibility of negative impact.
3.There is no validation of sensitive data that is being migrated by the FNDLOAD tool itself.

Examples of FNDLOAD utility:

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_ARIS_SITES"

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_POINTS_SITES"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_procedure.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_PRCS_EAST"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_EAST_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_MW_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_process.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_DATA_PROCES"


Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt

Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_procedure.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_process.ldt


Enjoy and Happy Oracle Apps DBA learning.

Hope it helps...


Best regards,

Rafi.

Adding Responsibility to an Application User for Oracle E-Business Suite Application

Hi,
Adding Responsibility to an Application User can be done as follows:

Step 1:Login with 'sysadmin' user into Oracle E-Business Suite front
end page.


Step 2:In the Oracle Applications Home Page go to Navigator section,select the User Management Responsibility

Step 3:Click the User Management Responsibility,In the User Management,click Users

Step 4:In the Oracle User Management of page,we find lot of search options available for use,we can use % also to find the name of the Oracle Application user
If you now the employee id than you can directly enter here.

Let us say we want to change password for Employee id 'E0111' ,than I will enter 'EO111' in the place where User Name is present

Step 5:Search the user
After entering the User Name,click Go

We see the details as specified below:
Last Name First Name Email User Name Status Create User Reset Password Update

Step 6:Adding Responsibility

Click on Update tab shown with Pencil mark to add responsibility to the Application User.

Click on Assign Roles tab

Now,search the Roles or Responsibility we want and than click Select

Once selected,Give justification for adding the Responsibility.

Than Click on Save button at the bottom or top

Click on Apply or else any how the changes are applied by Apps engine after 5 minutes.


Step 7:Verify and Validate


Verify the added responsibilities are assigned to user and are active and also inform the user for adding
the responsibilities.



Note 1:We have one more method of adding responsibilities from front end(also called as form based).
i.e By going to System Administrator Responsibility =>Security:User Define
This opens the form page

In the Navigator-System Administrator form Page
Go to security,Click the + symbol
click the + User Tab
Click Define

From here also we can change the password and Add Responsibilities to Users,use % to search the User or Responsibility we want to search and add.

Note 2:We have the other method also for adding responsibilities to User from backend,but this method
is rarely used.Any how this is just for your information we can do as follows:

We can use FND_USER_PKG package present in Apps Schema to do so.

Conn Apps/****

BEGIN
fnd_user_pkg.addresp ('TESTAPP','SYSADMIN','SYSTEM ADMINISTRATOR','STANDARD',
'Add Sysadmin Responsibility to TESTAPP user using pl/sql', SYSDATE, SYSDATE + 100);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not added due to ' || SQLCODE || SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
/


Hope it helps.....

Happy Apps DBA learning...


Best regards,

Rafi.

Changing Oracle Application user password for Oracle E-Business Suite R12 Application

As a part of system administrator's task,Apps DBA do lot of tasks
One such task is changing or resetting Oracle Application user password.
We can follow the below steps to do this task:

Step 1:Login with 'sysadmin' user into Oracle E-Business Suite front
end page.


Step 2:In the Oracle Applications Home Page go to Navigator section,select the User Management Responsibility

Step 3:Click the User Management Responsibility,In the User Management,click Users

Step 4:In the Oracle User Management of page,we find lot of search options available for use,we can use % also to find the name of the Oracle Application user
If you now the employee id than you can directly enter here.

Let us say we want to change password for Employee id 'E0111' ,than I will enter 'EO111' in the place where User Name is present


Step 5:Seach the user
After entering ther User Name,click Go

We can see the details as specified below:
Last Name First Name Email User Name Status Create User Reset Password Update


Step 6:Reset the password
Click the Reset Password tab and Choose Enter Manually option of resetting the password.Now,we can enter the password and confirm the password and
Click Submit.

Step 7:Verify and inform the User
After resetting or changing the password,we can verify once ourself by logging from that user from the Oracle E-Business Suite front end page

Let us say we have reset the password of 'E0111' to welcome123,than we can login successfully and inform the user for the change.



Hope it helps....

Happy oracle Apps DBA learning..


Best regards,

Rafi.

Creating Soft link or Symbolic link for Concurrent Manager program files

Soft Link or Symbolic Link is required to read a shell script (.prog file) through a concurrent program.

We can create soft links or symbolic links for Concurrent Manager program files as follows:

Step 1:Go to the custom top as below:

>cd $xxtest_TOP/bin
testapp01(APPDEV) /product/appl/apps/apps_st/appl/xxtest/12.0.0/bin

Step 2: Use 'fndcpesr' command to create soft link

Lets say we have to create two symbolic links for two .prog files xxtest_techtest_ets_loader.prog and xxtest_techtest_points_loader.prog respectively,than we can proceed as follows:

$pwd
testapp01(APPDEV) /product/appl/apps/apps_st/appl/xxtest/12.0.0/bin
$ln -s $FND_TOP/bin/fndcpesr xxtest_techtest_ets_loader

$ln -s $FND_TOP/bin/fndcpesr xxtest_techtest_points_loader


Note:prog_name should be without .prog extension while creating symbolic link for .prog files.

Step 3:Verify the two created symbolic links:

$ls -altr xxtest_techtest*
lrwxrwxrwx 1 applmgr oinstall 55 Feb 7 14:29 xxtest_techtest_ets_loader -> /product/appl/apps/apps_st/appl/fnd/12.0.0/bin/fndcpesr
lrwxrwxrwx 1 applmgr oinstall 55 Feb 7 14:29 xxtest_techtest_points_loader -> /product/appl/apps/apps_st/appl/fnd/12.0.0/bin/fndcpesr
As we see above,after creating symbolic link the new attribute 'l' is added that is nothing but symbolic link.

Note:
1)'fndcpesr' is a standard utility available in $FND_TOP/bin directory.
2) Its mainly used by the application to parse arguments for shell scripts.
3)It is located in $FND_TOP/bin path along with other FND Utilities.
4)'fndcpesr' is an executable and read only file.

Hope it helps.

Happy Apps DBA learning...

Reference summary of asmcmd commands

asmcmd is a various nice feature of ASM.It helps us understand and do things from command line itself.I find this utility very useful.Thanks to Oracle again for making things easy.

Reference summary of asmcmd commands:

cd Changes the current directory to the specified directory.

du Displays the total disk space occupied by ASM files in the
specified ASM directory and all its subdirectories, recursively.

exit Exits ASMCMD.

find Lists the paths of all occurrences of the specified name (with
wildcards) under the specified directory.

help Displays the syntax and description of ASMCMD commands.

ls Lists the contents of an ASM directory, the attributes of the
specified file, or the names and attributes of all disk groups.

lsct Lists information about current ASM clients.

lsdg Lists all disk groups and their attributes.

mkalias Creates an alias for a system-generated filename.

mkdir Creates ASM directory.

pwd Displays the path of the current ASM directory.

rm Deletes the specified ASM files or directories.

rmalias Deletes the specified alias, retaining the file that the alias points to.

asm.sh, a Linux shell script, that demonstrates some of the asmcmd functionality.

lsct :list all connected Oracle instances

ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM1 GRID_DATA_01
+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM1 TESTDB_DATA_01
TESTDB CONNECTED 11.2.0.1.0 11.2.0.0.0 TESTDB1 TESTDB_FRA_01
TESTDB CONNECTED 11.2.0.1.0 11.2.0.0.0 TESTDB1 TESTDB_DATA_01
ICMQB CONNECTED 11.2.0.1.0 11.2.0.0.0 ICMQB1 TESTDB_FRA_01
ICMQB CONNECTED 11.2.0.1.0 11.2.0.0.0 ICMQB1 TESTDB_REDO_01
ICMQB CONNECTED 11.2.0.1.0 11.2.0.0.0 ICMQB1 TESTDB_REDO_02

lsdg:List Diskgroup status and type.

ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 5934 5002 173 2414 0 N GRID_DATA_01/
MOUNTED EXTERN N 512 4096 1048576 2161538 112220 0 112220 0 N TESTDB_DATA_01/
MOUNTED EXTERN N 512 4096 1048576 654684 560350 0 560350 0 N TESTDB_FRA_01/
MOUNTED EXTERN N 512 4096 1048576 32652 11922 0 11922 0 N TESTDB_REDO_01/
MOUNTED EXTERN N 512 4096 1048576 32652 11954 0 11954 0 N TESTDB_REDO_02/


ASMCMD> iostat
Group_Name Dsk_Name Reads Writes
GRID_DATA_01 GRID_DATA_01_0000 3121340416 0
GRID_DATA_01 GRID_DATA_01_0001 3616768 0
GRID_DATA_01 GRID_DATA_01_0002 4354048 4096
GRID_DATA_01 GRID_DATA_01_0003 2061918208 4096
GRID_DATA_01 GRID_DATA_01_0004 3616768 0
GRID_DATA_01 GRID_DATA_01_0005 3731456 4096
TESTDB_DATA_01 TESTDB_DATA_01_0000 1868196700160 205746507776
TESTDB_DATA_01 TESTDB_DATA_01_0001 1860077395968 203464622080
TESTDB_DATA_01 TESTDB_DATA_01_0002 1860528062464 204175556608

Reference :MY ORACLE SUPPORT and Oracle Documentations

Hope is helps...

Happy ASM learning...

Changing the profile value in Oracle EBS applications

How do we change the profile value in Oracle EBS applications?

Ans: Below are the steps for changing profile value in Oracle EBS applications:

Step 1:
Login to EBS home page with 'SYSADMIN' or privileged application user.


Step 2:

In the Navigator,Go to System Administrator Tab and On the right side Go to Profile=>System



Step 3:

Click System you will see Oracle Application Manager Form Page is opened.


Step 4:

Double Click Profile Tab,go to System,Click Open Tab at the bottom.


Step 5:

We will see form page Find System Profile Values

Enter the Profile Name at the Bottom where Profile Tab is there and Click Find.

Where Profile:Name_of_profile whose value we want to set.


Step 6:
Now we will see Form Page where
Profile Option Name:Profile_name
Site:Value you want to set

Site=0,1...


Step 7:
Save the Changes.


File=>Save

And Close the Form.

File=>Close Form or Exit Oracle Applications.


Hope it helps Beginners Apps DBA

HAPPY NEW YEAR 2012

Hi All,
Thanks for visiting,reading and giving suggestion regarding my blog,I wish you
all very HAPPY AND PROSPEROUS NEW YEAR 2012.

Last year(2011) was interesting for me,as I learn new technologies Oracle SOA(Service Oriented Architecture) and ODI(Oracle Data Integrator).Apart from these
I stepped forward to learn Oracle 11g RAC,Dataguard and Oracle EBS 12.1.1 and later versions.
Going forward this year(2012) I'm interested in extending my skillsets and implement
these skillsets more in my work to gain perfection.It won't be easy,but I will be
trying my best to maintain these skillsets by reading and practise.

MY GOALS FOR THE YEAR 2012::

1)Practise and Master the skillsets Oracle 11g RAC,Dataguard.

2)Practise and Master the skillsets Oracle EBS R12.1.1 & later versions.

3)Practise and Master the skillsets Oracle SOA 11g.

4)Practise and Master the skillsets Oracle Data Integrator 11g.

5)In order to get more perfection I need to take Oracle 11g RAC,Dataguard training
from Oracle which will also act as base for my prepartion of OCM Exam.

6)Practise and Complete PMP Certification.

7)Bring Excellence to my work by adopting best work practises in terms of technology
and work practises.

Oracle and DBA Market Survey as per me:

DBA Market Demanded the following major skillsets:
1)RAC: RAC skillset I found ruled the DBA market in the year 2011(last year).It makes sense as it is used by lot of financial and highly critical Databases.

2)Dataguard: Dataguard skillset was the second major skillset which I found ruled the DBA market.As many companies grow their data,they worry for protecting it from Disaster.
ASM,RMAN,Performance tuning were not behind and were also in demand as always.

Apps DBA Market Survery:

Apps DBA market Demanded the follwing skillset:

1)Clonning and Patching:
The most repeated task of the Apps DBA again ruled the last year(2011).It can be called as refreshing the Database and Application and Clonning
in other sense.Patching is again the repeated task for the Apps DBA

2)SHARED APPL_TOP ,LOAD BALANCING AND PCP:
As experience grows the expectation of companies for you will also.I found few companies specifically asking the combination of RAC and Apps for
senior Apps consultant/Consultant positions on a large scale.

DMZ configuration,Apps DBA Administraion(AD Utilities) and Workflow configuration skillsets were also not behind.

3)Oracle SOA market survey:
Oracle SOA 11g market was hot last year.I found many companies
asking for Oracle SOA Developer/Admins.Along with these,Oracle Weblogic server skillsets were in demand.

4)ODI market survey:
Oracle Data Integrator 11g(ODI 11g) was the hot ETL tool last year as
many companies were moving Data from one source to many targets.

5)Oracle Fusion Apps DBA(Fusion DBA) market survey:
I found a new job role emerged in 2011,It is the combination of Oracle Apps DBA and Fusion Middleware Administration(SOA Administration + Weblogic Administration).It is a kind of profile where company is asking too much from one candidate,considering future demand of Oracle Weblogic(BEA product).Some companies
were specific on hiring Oracle Fusion Middleware Administrators.

Note:The above survey is just based on my personal experience and should not be considered for any publicity or stratergy.


I will be writing my next survey in next year beginning.All the best.Have a bright and skillfull year 2012.

Enjoy and Happy Oracle learning.