I was participating in a forum on OTN, regarding a restore/recovery procedure. I was looking for an answer, when I also came across this graphical recovery procedure !!
http://www.pafumi.net/recovery_procedure.htm
If you have any questions about how to restore/recover your database, this beautiful diagram should answer them.
Thursday, June 29, 2006
Holiday
Starting tomorrow, I have two weeks to move to our new house....Their is a lot of painting and cleaning to do, so it will be working instead of vacation.
Wednesday, June 28, 2006
Case sensitivity on Solaris
I have been struggling with a shell script on Solaris.
The problem was the case sensitivity in unix. The shell script I made is expecting 4 parameters and I wanted the script to treat them case insensitive.
I had an example script from an HP Unix environment, but apparently the syntax on Solaris Unix is a little bit different...
On HP I used the following...
ENVIRONMENT=$(echo $IDENT tr "[a-z]" "[A-Z]")
but that didn't work on Solaris...
But I found the translation on google..
ENVIRONMENT=`echo ${IDENT} tr '[a-z]' '[A-Z]'`
The problem was the case sensitivity in unix. The shell script I made is expecting 4 parameters and I wanted the script to treat them case insensitive.
I had an example script from an HP Unix environment, but apparently the syntax on Solaris Unix is a little bit different...
On HP I used the following...
ENVIRONMENT=$(echo $IDENT tr "[a-z]" "[A-Z]")
but that didn't work on Solaris...
But I found the translation on google..
ENVIRONMENT=`echo ${IDENT} tr '[a-z]' '[A-Z]'`
EBS dba vs Oracle dba
Yesterday I had another discussion with a colleague, also EBS dba.
We talked about an old topic, the difference between an Oracle EBS dba and a regular Oracle dba. From my point of view an EBS dba is an Oracle dba +. That means as an EBS dba, I believe you are an Oracle dba, but with additional tasks. This is where other EBS dba's think I'm wrong. They want nothing to do with the database. Sure, they patch the application (and also database), but don't ask them to edit a tablespace or implement hot backup.
On the OTN applications forum I found the following, which I believe is a correct summary of what an EBS dba should be capable of...
To become a Oracle Applications DBA you need to learn
1)Oracle DBA
2)Oracle Applications Concepts (Manual Available in oracle.com)
3)Concurrent Processing Architecture ( Use www.dbatoolbox.com or medallions if you have one)
4)Web and Forms Server Architecture (9iAS Doc's)
5)Using AD utilities and AD procedures (Manual available)
6)Installing and Upgrading Applications (manual available )
7)Cloning Oracle Applications(White paper)
We talked about an old topic, the difference between an Oracle EBS dba and a regular Oracle dba. From my point of view an EBS dba is an Oracle dba +. That means as an EBS dba, I believe you are an Oracle dba, but with additional tasks. This is where other EBS dba's think I'm wrong. They want nothing to do with the database. Sure, they patch the application (and also database), but don't ask them to edit a tablespace or implement hot backup.
On the OTN applications forum I found the following, which I believe is a correct summary of what an EBS dba should be capable of...
To become a Oracle Applications DBA you need to learn
1)Oracle DBA
2)Oracle Applications Concepts (Manual Available in oracle.com)
3)Concurrent Processing Architecture ( Use www.dbatoolbox.com or medallions if you have one)
4)Web and Forms Server Architecture (9iAS Doc's)
5)Using AD utilities and AD procedures (Manual available)
6)Installing and Upgrading Applications (manual available )
7)Cloning Oracle Applications(White paper)
Tuesday, June 27, 2006
EBS performance
When you are experiencing performace trouble with your EBS, a dba should investigate your environment.
Here is something you could do to speed up the EBS a little....
Oracle keeps SQL statements, packages, object information and many other items in an area in the SGA known as the shared pool
Oracle Applications requires space in the ORACLE System Global Area (SGA) for stored packages and functions
To improve the performance using the Oracle EBS, it’s useful to load some important (sys) packages into the shared pool, when starting up the database.
This is called ‘pinning’…
The advantage of pinning these packages is that they are already in memory and they do not have to be loaded from the database, which saves time.
Most time is saved when large, frequently used packages are pinned.
The following (sys) packages should always be pinned ..
STANDARD
DBMS_STANDARD
DBMS_UTILITY
DBMS_DESCRIBE
DBMS_OUTPUT
To determine which Oracle EBS packages should be pinned, depends on what the users are doing. You need to find out which packages are being executed the most.
You can run the following script, as sys or apps, $AD_TOP/sql/ADXCKPIN.sql
This sql script will return an output file ADXCKPIN.lst, what will look like this…
OBJECT TYPE SPACE(K) LOADS EXECS KEPT
----------------------------- ------------ -------- ------- ----- ----
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE 15.2 1 9 NO
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE BODY 13.7 1 8 NO
APPS.FND_CLIENT_INFO PACKAGE 2.7 1 206 NO
APPS.FND_CLIENT_INFO PACKAGE BODY 13.0 1 206 NO
APPS.FND_CONCURRENT PACKAGE 15.2 1 199 NO
APPS.FND_CONCURRENT PACKAGE BODY 24.2 1 197 NO
Now you should look for those packages with a high number of executions (EXEC) or with a large space (SPACE K).
Don’t pin too many objects. According to Oracle, only 10 packages give a very good result.
If you are not sure whether or not you are already pinning packages, run the following query and find out.
SELECT owner, name, type, kept FROM v$db_object_cache WHERE kept='YES' AND TYPE like 'PACKAGE%';
How should you pin a package in the shared pool ?
You can use a standard Oracle sql script located in the $AD_TOP/sql…ADXGNPIN.sql. This sql script will generate a pinning script. You must edit this script before running it…otherwise all objects will be pinned !!
Another way to pin these packages is to adjust your database startup script and put the following lines into your script after you started the database.
Your script could look something like this..
sqlplus /nolog
conn / as sysdba
begin
dbms_shared_pool.keep('SYS.STANDARD');
dbms_shared_pool.keep('SYS.DBMS_STANDARD');
dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
dbms_shared_pool.keep('APPS.ICX_UTIL');
dbms_shared_pool.keep('APPS.OWA_CUSTOM');
dbms_shared_pool.keep('FA.FA_ADDITIONS_S','Q');
end;
Here is something you could do to speed up the EBS a little....
Oracle keeps SQL statements, packages, object information and many other items in an area in the SGA known as the shared pool
Oracle Applications requires space in the ORACLE System Global Area (SGA) for stored packages and functions
To improve the performance using the Oracle EBS, it’s useful to load some important (sys) packages into the shared pool, when starting up the database.
This is called ‘pinning’…
The advantage of pinning these packages is that they are already in memory and they do not have to be loaded from the database, which saves time.
Most time is saved when large, frequently used packages are pinned.
The following (sys) packages should always be pinned ..
STANDARD
DBMS_STANDARD
DBMS_UTILITY
DBMS_DESCRIBE
DBMS_OUTPUT
To determine which Oracle EBS packages should be pinned, depends on what the users are doing. You need to find out which packages are being executed the most.
You can run the following script, as sys or apps, $AD_TOP/sql/ADXCKPIN.sql
This sql script will return an output file ADXCKPIN.lst, what will look like this…
OBJECT TYPE SPACE(K) LOADS EXECS KEPT
----------------------------- ------------ -------- ------- ----- ----
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE 15.2 1 9 NO
APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE BODY 13.7 1 8 NO
APPS.FND_CLIENT_INFO PACKAGE 2.7 1 206 NO
APPS.FND_CLIENT_INFO PACKAGE BODY 13.0 1 206 NO
APPS.FND_CONCURRENT PACKAGE 15.2 1 199 NO
APPS.FND_CONCURRENT PACKAGE BODY 24.2 1 197 NO
Now you should look for those packages with a high number of executions (EXEC) or with a large space (SPACE K).
Don’t pin too many objects. According to Oracle, only 10 packages give a very good result.
If you are not sure whether or not you are already pinning packages, run the following query and find out.
SELECT owner, name, type, kept FROM v$db_object_cache WHERE kept='YES' AND TYPE like 'PACKAGE%';
How should you pin a package in the shared pool ?
You can use a standard Oracle sql script located in the $AD_TOP/sql…ADXGNPIN.sql. This sql script will generate a pinning script. You must edit this script before running it…otherwise all objects will be pinned !!
Another way to pin these packages is to adjust your database startup script and put the following lines into your script after you started the database.
Your script could look something like this..
sqlplus /nolog
conn / as sysdba
begin
dbms_shared_pool.keep('SYS.STANDARD');
dbms_shared_pool.keep('SYS.DBMS_STANDARD');
dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
dbms_shared_pool.keep('APPS.ICX_UTIL');
dbms_shared_pool.keep('APPS.OWA_CUSTOM');
dbms_shared_pool.keep('FA.FA_ADDITIONS_S','Q');
end;
Friday, June 23, 2006
FNDLOAD part II
I found out that when you delete a record and upload the new Alert to another database, FNDLOAD does not automatically delete that record. So, to accomplish that i had to build in an extra funtion in the config file and add an extra parameter to the FNDLOAD command.
The extra parameter is UPLOAD_MODE='REPLACE'
and the additional function is
if :UPLOAD_MODE = 'REPLACE'
then
delete from alr_actions
where alert_id = (select alert_id from alr_alerts
where alert_name like :alert_name);
end if;
With this extra code, the records are first deleted and inserted by FNDLOAD....
The extra parameter is UPLOAD_MODE='REPLACE'
and the additional function is
if :UPLOAD_MODE = 'REPLACE'
then
delete from alr_actions
where alert_id = (select alert_id from alr_alerts
where alert_name like :alert_name);
end if;
With this extra code, the records are first deleted and inserted by FNDLOAD....
Thursday, June 22, 2006
EBS release 12
EBS release 12 is on his way.
Some screen previews and new features on this great oapps blog !!!!
http://blogs.oracle.com/schan/release12
Some screen previews and new features on this great oapps blog !!!!
http://blogs.oracle.com/schan/release12
Wednesday, June 21, 2006
What does a DBA do ?
on the Oracle Technology Network i found the following posting about the DBA job...
gopalora summed up the following activity's :
1. Developers complain -> Look for Patch
2. Sit on Metalink all day
3. Drink Coffee day & night
4. Attend meetings, pretend you are listening.
5. Keep playing different scenarios in your mind
5.a Redo Log member missing
5.b Redo Group blown up
5.c Undo tablespace filled up
5.d Move datafiles around
5.e What is the latest oracle bug
5.f Which version of kernel is released
5.g How to size SGA
5.h Clone DB , hot / cold
5.i Move servers around for no rhyme or reason
5.j Run top and monitor it day and night.
Dont sleep just keep an eye on the D/B.
gopalora summed up the following activity's :
1. Developers complain -> Look for Patch
2. Sit on Metalink all day
3. Drink Coffee day & night
4. Attend meetings, pretend you are listening.
5. Keep playing different scenarios in your mind
5.a Redo Log member missing
5.b Redo Group blown up
5.c Undo tablespace filled up
5.d Move datafiles around
5.e What is the latest oracle bug
5.f Which version of kernel is released
5.g How to size SGA
5.h Clone DB , hot / cold
5.i Move servers around for no rhyme or reason
5.j Run top and monitor it day and night.
Dont sleep just keep an eye on the D/B.
EBS Dba home page
I have created my own Oracle EBS Homepage....
On this site i have posted all my Oracle links, so whenever you need info regarding Oracle or the Oracle EBS, take a look at my homepage
http://oappsdba.jouwpagina.nl/
On this site i have posted all my Oracle links, so whenever you need info regarding Oracle or the Oracle EBS, take a look at my homepage
http://oappsdba.jouwpagina.nl/
Using FNDLOAD to transfer Alerts
When you use Oracle Alert, this is a module from the Oracle EBS, you probably work with custom alerts instead of the standard alerts provided by Oracle.
When working with custom alerts, their will be changes to these alerts and those changes must also be applied to your production environment. Once you have tested the new/adjusted Alert, Oracle offers you a way to transfer the new/adjusted Alert from your test database to your production database. When signing in the Oracle Alert module as the Alert manager, there is a function called ‘Transfer Alert’, which allows you the transfer an Alert from one database to the other. This transfer works with a database link.
There are a few disadvantages when using this method to transfer Alerts to the production database.
There is no version administration, you do not know what version is in the production database.
What if somebody is changing the Alert in the Test environment……then this change is also transferred to production.
Before uploading an Alert, you must first delete the Alert in the target database
I was asked to find out if it is possible to transfer these alerts using the FNDLOAD tool from Oracle.
FNDLOAD is a concurrent program that can move Oracle Applications data between database and text file.
FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database. Conversion between database format and text file format is specified by a configuration file.
FNDLOAD can be used to upload or download.
Advantages when using FNDLOAD are :
Because downloaded data is stored in a text file, version administration is possible
When uploading data, the original data is updated. So, no need for deleting the alert first
The adjusted Alert for production is saved in a text file, and cannot be adjusted by end users
FNDLOAD Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1…..]
As you can see, some info is needed.
- First you will need to know the Apps password.
- The mode is either DOWNLOAD or UPLOAD.
- The configfile is the file that Fndload needs to download op upload data.
- The data file is the output file, in which the downloaded data is written
- the entity is the entity you want to download, for example alr_alerts
- parameters van be passed to download a certain Alert
By default Oracle delivers some configuration files you can use to download certain entities.
You can find these config files in $FND_TOP/patch/115/import
/afs292/appl/fnd/11.5.0/patch/115/import
> ls *.lct
afattach.lct afdict.lct afpinit.lct afumsaru.lct fndstd.lct
afcpexec.lct afdskob.lct afplist.lct afwfds.lct m57hrupv2.lct
afcplibr.lct affaudit.lct afprfma.lct afwfload.lct makhtml.lct
afcpmime.lct afffload.lct afpvalue.lct fndatttm.lct mappsnav.lct
afcppinf.lct affrmcus.lct afrole.lct fnddmrepos.lct umxrgsvc.lct
afcpprnt.lct afindust.lct afscapp.lct fnddmsec.lct wfdpart.lct
afcpprog.lct aflvmlu.lct afscprof.lct fndfwkmsg.lct wfdpview.lct
afcppstl.lct afmdmsg.lct afscursp.lct fndobjt.lct wfdrole.lct
afcpque.lct afmlhelp.lct afsload.lct fndpiagp.lct wfmlrp.lct
afcpreqg.lct afmoinit.lct afsncat.lct fndpicla.lct wfmlrt.lct
afcprset.lct afnls.lct afsvcc.lct fndpidef.lct
afcpsch.lct afoamcgp.lct afsvcct.lct fndpipra.lct
afcpsrvs.lct afoamdw.lct aftz.lct fndpipur.lct
afcpwksh.lct afoammet.lct afums.lct fndprtdep.lct
Config file for Alerts
Unfortunately Oracle did not create a standard config file for downloading Alerts. I had to make one myself.
From the documentation I learned that these config files always had the same layout.
At the beginning of the file you have the define part. Here you define the tables you want to download the data from.
Next is the download section, which is actually a group of select statements.
The final part of the config file is the upload section.
The upload section uses standard Oracle packages, so the download always checks whether the data must be updated or inserted.
I used the following packages to download one alert.
ALR_ACTION_GROUPS_PKG
ALR_ACTIONS_PKG
ALR_ALERT_INPUTS_PKG
ALR_ALERT_INSTALLATIONS_PKG
ALR_ACTION_OUTPUTS_PKG
ALR_ALERT_OUTPUTS_PKG
ALR_ACTION_SET_OUTPUTS_PKG
ALT_ACTION_SET_INPUTS_PKG
ALR_ACTION_SETS_PKG
ALR_ACTION_SET_MEMBERS_PKG
ALR_ALERTS_PKG
ALR_RESPONSE_SETS_PKG
The complete config file (alert.lct) I made is eleven pages long, so I will only show one packages. Each package is an entity in the config file, but because of the data structure in Alerts, alr_alerts is the main entity and all the others belong to this one. You get a sort of nested define section….
The define section of alr_alerts looks like this.
DEFINE ALR_ALERTS
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
KEY ALERT_ID NUMBER
KEY ALERT_NAME VARCHAR2(50)
KEY ALERT_CONDITION_TYPE VARCHAR2(1)
BASE ENABLED_FLAG VARCHAR2(1)
BASE START_DATE_ACTIVE VARCHAR2(20)
BASE END_DATE_ACTIVE VARCHAR2(20)
BASE TABLE_ID NUMBER
BASE TABLE_APPLICATION_ID NUMBER
TRANS DESCRIPTION VARCHAR2(240)
BASE FREQUENCY_TYPE VARCHAR2(1)
BASE WEEKLY_CHECK_DAY VARCHAR2(3)
BASE MONTHLY_CHECK_DAY_NUM NUMBER
BASE DAYS_BETWEEN_CHECKS NUMBER
BASE CHECK_BEGIN_DATE VARCHAR2(20)
BASE DATE_LAST_CHECKED VARCHAR2(20)
BASE INSERT_FLAG VARCHAR2(1)
BASE UPDATE_FLAG VARCHAR2(1)
BASE DELETE_FLAG VARCHAR2(1)
BASE MAINTAIN_HISTORY_DAYS NUMBER
BASE CHECK_TIME NUMBER
BASE CHECK_START_TIME NUMBER
BASE CHECK_END_TIME NUMBER
BASE SECONDS_BETWEEN_CHECKS NUMBER
BASE CHECK_ONCE_DAILY_FLAG VARCHAR2(1)
BASE SQL_STATEMENT_TEXT VARCHAR2(4000)
BASE ONE_TIME_ONLY_FLAG NUMBER
BASE TABLE_NAME VARCHAR2(31)
The end alr_alerts should be at the end of the define, but in this case the other entities are defined within the main entity alr_alerts.
The upload part of alr_alerts :
DOWNLOAD ALR_ALERTS
" select APPLICATION_SHORT_NAME, ALERT_ID, ALERT_NAME, ALERT_CONDITION_TYPE, alr.ENABLED_FLAG, to_char(START_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'), to_char(END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'), TABLE_ID, TABLE_APPLICATION_ID, DESCRIPTION, FREQUENCY_TYPE, WEEKLY_CHECK_DAY, MONTHLY_CHECK_DAY_NUM, DAYS_BETWEEN_CHECKS, to_char(CHECK_BEGIN_DATE,'YYYY/MM/DD HH24:MI:SS'), to_char(DATE_LAST_CHECKED,'YYYY/MM/DD HH24:MI:SS'), INSERT_FLAG, UPDATE_FLAG, DELETE_FLAG, MAINTAIN_HISTORY_DAYS, CHECK_TIME, CHECK_START_TIME, CHECK_END_TIME, SECONDS_BETWEEN_CHECKS, CHECK_ONCE_DAILY_FLAG, SQL_STATEMENT_TEXT, ONE_TIME_ONLY_FLAG, TABLE_NAME
from alr_alerts alr, fnd_application fab
where alert_name like :ALERTNAME
and alr.application_id = fab.application_id "
The upload section looks like this ….
UPLOAD ALR_ALERTS
BEGIN
"begin
if (:UPLOAD_MODE = 'NLS') then
null; /* There are no translated columns in NLS mode, so nothing*/
/* to do in NLS mode. */
else
alr_alerts_pkg.load_row(
X_APPLICATION_SHORT_NAME => :APPLICATION_SHORT_NAME,
X_ALERT_NAME => :ALERT_NAME,
X_OWNER => NULL,
X_ALERT_CONDITION_TYPE => :ALERT_CONDITION_TYPE,
X_ENABLED_FLAG => :ENABLED_FLAG,
X_START_DATE_ACTIVE => :START_DATE_ACTIVE,
X_END_DATE_ACTIVE => :END_DATE_ACTIVE,
X_TABLE_APPLICATION_SHORT_NAME => NULL,
X_DESCRIPTION => :DESCRIPTION,
X_FREQUENCY_TYPE => :FREQUENCY_TYPE,
X_WEEKLY_CHECK_DAY => :WEEKLY_CHECK_DAY,
X_MONTHLY_CHECK_DAY_NUM => :MONTHLY_CHECK_DAY_NUM,
X_DAYS_BETWEEN_CHECKS => :DAYS_BETWEEN_CHECKS,
X_CHECK_BEGIN_DATE => :CHECK_BEGIN_DATE,
X_DATE_LAST_CHECKED => :DATE_LAST_CHECKED,
X_INSERT_FLAG => :INSERT_FLAG,
X_UPDATE_FLAG => :UPDATE_FLAG,
X_DELETE_FLAG => :DELETE_FLAG,
X_MAINTAIN_HISTORY_DAYS => :MAINTAIN_HISTORY_DAYS,
X_CHECK_TIME => :CHECK_TIME,
X_CHECK_START_TIME => :CHECK_START_TIME,
X_CHECK_END_TIME => :CHECK_END_TIME,
X_SECONDS_BETWEEN_CHECKS => :SECONDS_BETWEEN_CHECKS,
X_CHECK_ONCE_DAILY_FLAG => :CHECK_ONCE_DAILY_FLAG,
X_SQL_STATEMENT_TEXT => :SQL_STATEMENT_TEXT,
X_ONE_TIME_ONLY_FLAG => :ONE_TIME_ONLY_FLAG,
X_TABLE_NAME => :TABLE_NAME );
end if;
end; "
In the upload you see that the load_row function from the alr_alerts_pkg is used. The load_row function is checking if the row must be inserted or updated.
For each package mentioned above, you should create a define, download and upload section.
Downloaded data
Well, hoes the downloaded data looks like.
Again, I will only look at the downloaded data from alr_alerts.
To download alert data use the following syntax :
FNDLOAD apps/….. 0 Y DOWNLOAD ALERT.LCT ALR_ALERT.UPL ALERTNAME=”TEST_BAS”
When downloading you should always tell FNDLOAD which entity should be downloaded.
BEGIN ALR_ALERTS "XXOF" "101060" "TEST_BAS" "P"
ENABLED_FLAG = "N"
START_DATE_ACTIVE = "1997/12/10 00:00:00"
DESCRIPTION =
"Dagelijkse Nachtverwerking voor BAS, Transfer Ap=>GL, Naportaal AP en Rapportages"
FREQUENCY_TYPE = "C"
DAYS_BETWEEN_CHECKS = "1"
MAINTAIN_HISTORY_DAYS = "0"
CHECK_START_TIME = "900"
CHECK_ONCE_DAILY_FLAG = "Y"
SQL_STATEMENT_TEXT = "select * from global_name"
Upload data
To upload the downloaded into another database, simply use the following command with the settings of the new database.
FNDLOAD apps/…… 0 Y UPLOAD ALR_ALERT.LCT ALR_ALERT.UPL
When uploading data, al entities from the configuration file will be uploaded.
When working with custom alerts, their will be changes to these alerts and those changes must also be applied to your production environment. Once you have tested the new/adjusted Alert, Oracle offers you a way to transfer the new/adjusted Alert from your test database to your production database. When signing in the Oracle Alert module as the Alert manager, there is a function called ‘Transfer Alert’, which allows you the transfer an Alert from one database to the other. This transfer works with a database link.
There are a few disadvantages when using this method to transfer Alerts to the production database.
There is no version administration, you do not know what version is in the production database.
What if somebody is changing the Alert in the Test environment……then this change is also transferred to production.
Before uploading an Alert, you must first delete the Alert in the target database
I was asked to find out if it is possible to transfer these alerts using the FNDLOAD tool from Oracle.
FNDLOAD is a concurrent program that can move Oracle Applications data between database and text file.
FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database. Conversion between database format and text file format is specified by a configuration file.
FNDLOAD can be used to upload or download.
Advantages when using FNDLOAD are :
Because downloaded data is stored in a text file, version administration is possible
When uploading data, the original data is updated. So, no need for deleting the alert first
The adjusted Alert for production is saved in a text file, and cannot be adjusted by end users
FNDLOAD Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1…..]
As you can see, some info is needed.
- First you will need to know the Apps password.
- The mode is either DOWNLOAD or UPLOAD.
- The configfile is the file that Fndload needs to download op upload data.
- The data file is the output file, in which the downloaded data is written
- the entity is the entity you want to download, for example alr_alerts
- parameters van be passed to download a certain Alert
By default Oracle delivers some configuration files you can use to download certain entities.
You can find these config files in $FND_TOP/patch/115/import
/afs292/appl/fnd/11.5.0/patch/115/import
> ls *.lct
afattach.lct afdict.lct afpinit.lct afumsaru.lct fndstd.lct
afcpexec.lct afdskob.lct afplist.lct afwfds.lct m57hrupv2.lct
afcplibr.lct affaudit.lct afprfma.lct afwfload.lct makhtml.lct
afcpmime.lct afffload.lct afpvalue.lct fndatttm.lct mappsnav.lct
afcppinf.lct affrmcus.lct afrole.lct fnddmrepos.lct umxrgsvc.lct
afcpprnt.lct afindust.lct afscapp.lct fnddmsec.lct wfdpart.lct
afcpprog.lct aflvmlu.lct afscprof.lct fndfwkmsg.lct wfdpview.lct
afcppstl.lct afmdmsg.lct afscursp.lct fndobjt.lct wfdrole.lct
afcpque.lct afmlhelp.lct afsload.lct fndpiagp.lct wfmlrp.lct
afcpreqg.lct afmoinit.lct afsncat.lct fndpicla.lct wfmlrt.lct
afcprset.lct afnls.lct afsvcc.lct fndpidef.lct
afcpsch.lct afoamcgp.lct afsvcct.lct fndpipra.lct
afcpsrvs.lct afoamdw.lct aftz.lct fndpipur.lct
afcpwksh.lct afoammet.lct afums.lct fndprtdep.lct
Config file for Alerts
Unfortunately Oracle did not create a standard config file for downloading Alerts. I had to make one myself.
From the documentation I learned that these config files always had the same layout.
At the beginning of the file you have the define part. Here you define the tables you want to download the data from.
Next is the download section, which is actually a group of select statements.
The final part of the config file is the upload section.
The upload section uses standard Oracle packages, so the download always checks whether the data must be updated or inserted.
I used the following packages to download one alert.
ALR_ACTION_GROUPS_PKG
ALR_ACTIONS_PKG
ALR_ALERT_INPUTS_PKG
ALR_ALERT_INSTALLATIONS_PKG
ALR_ACTION_OUTPUTS_PKG
ALR_ALERT_OUTPUTS_PKG
ALR_ACTION_SET_OUTPUTS_PKG
ALT_ACTION_SET_INPUTS_PKG
ALR_ACTION_SETS_PKG
ALR_ACTION_SET_MEMBERS_PKG
ALR_ALERTS_PKG
ALR_RESPONSE_SETS_PKG
The complete config file (alert.lct) I made is eleven pages long, so I will only show one packages. Each package is an entity in the config file, but because of the data structure in Alerts, alr_alerts is the main entity and all the others belong to this one. You get a sort of nested define section….
The define section of alr_alerts looks like this.
DEFINE ALR_ALERTS
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
KEY ALERT_ID NUMBER
KEY ALERT_NAME VARCHAR2(50)
KEY ALERT_CONDITION_TYPE VARCHAR2(1)
BASE ENABLED_FLAG VARCHAR2(1)
BASE START_DATE_ACTIVE VARCHAR2(20)
BASE END_DATE_ACTIVE VARCHAR2(20)
BASE TABLE_ID NUMBER
BASE TABLE_APPLICATION_ID NUMBER
TRANS DESCRIPTION VARCHAR2(240)
BASE FREQUENCY_TYPE VARCHAR2(1)
BASE WEEKLY_CHECK_DAY VARCHAR2(3)
BASE MONTHLY_CHECK_DAY_NUM NUMBER
BASE DAYS_BETWEEN_CHECKS NUMBER
BASE CHECK_BEGIN_DATE VARCHAR2(20)
BASE DATE_LAST_CHECKED VARCHAR2(20)
BASE INSERT_FLAG VARCHAR2(1)
BASE UPDATE_FLAG VARCHAR2(1)
BASE DELETE_FLAG VARCHAR2(1)
BASE MAINTAIN_HISTORY_DAYS NUMBER
BASE CHECK_TIME NUMBER
BASE CHECK_START_TIME NUMBER
BASE CHECK_END_TIME NUMBER
BASE SECONDS_BETWEEN_CHECKS NUMBER
BASE CHECK_ONCE_DAILY_FLAG VARCHAR2(1)
BASE SQL_STATEMENT_TEXT VARCHAR2(4000)
BASE ONE_TIME_ONLY_FLAG NUMBER
BASE TABLE_NAME VARCHAR2(31)
The end alr_alerts should be at the end of the define, but in this case the other entities are defined within the main entity alr_alerts.
The upload part of alr_alerts :
DOWNLOAD ALR_ALERTS
" select APPLICATION_SHORT_NAME, ALERT_ID, ALERT_NAME, ALERT_CONDITION_TYPE, alr.ENABLED_FLAG, to_char(START_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'), to_char(END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'), TABLE_ID, TABLE_APPLICATION_ID, DESCRIPTION, FREQUENCY_TYPE, WEEKLY_CHECK_DAY, MONTHLY_CHECK_DAY_NUM, DAYS_BETWEEN_CHECKS, to_char(CHECK_BEGIN_DATE,'YYYY/MM/DD HH24:MI:SS'), to_char(DATE_LAST_CHECKED,'YYYY/MM/DD HH24:MI:SS'), INSERT_FLAG, UPDATE_FLAG, DELETE_FLAG, MAINTAIN_HISTORY_DAYS, CHECK_TIME, CHECK_START_TIME, CHECK_END_TIME, SECONDS_BETWEEN_CHECKS, CHECK_ONCE_DAILY_FLAG, SQL_STATEMENT_TEXT, ONE_TIME_ONLY_FLAG, TABLE_NAME
from alr_alerts alr, fnd_application fab
where alert_name like :ALERTNAME
and alr.application_id = fab.application_id "
The upload section looks like this ….
UPLOAD ALR_ALERTS
BEGIN
"begin
if (:UPLOAD_MODE = 'NLS') then
null; /* There are no translated columns in NLS mode, so nothing*/
/* to do in NLS mode. */
else
alr_alerts_pkg.load_row(
X_APPLICATION_SHORT_NAME => :APPLICATION_SHORT_NAME,
X_ALERT_NAME => :ALERT_NAME,
X_OWNER => NULL,
X_ALERT_CONDITION_TYPE => :ALERT_CONDITION_TYPE,
X_ENABLED_FLAG => :ENABLED_FLAG,
X_START_DATE_ACTIVE => :START_DATE_ACTIVE,
X_END_DATE_ACTIVE => :END_DATE_ACTIVE,
X_TABLE_APPLICATION_SHORT_NAME => NULL,
X_DESCRIPTION => :DESCRIPTION,
X_FREQUENCY_TYPE => :FREQUENCY_TYPE,
X_WEEKLY_CHECK_DAY => :WEEKLY_CHECK_DAY,
X_MONTHLY_CHECK_DAY_NUM => :MONTHLY_CHECK_DAY_NUM,
X_DAYS_BETWEEN_CHECKS => :DAYS_BETWEEN_CHECKS,
X_CHECK_BEGIN_DATE => :CHECK_BEGIN_DATE,
X_DATE_LAST_CHECKED => :DATE_LAST_CHECKED,
X_INSERT_FLAG => :INSERT_FLAG,
X_UPDATE_FLAG => :UPDATE_FLAG,
X_DELETE_FLAG => :DELETE_FLAG,
X_MAINTAIN_HISTORY_DAYS => :MAINTAIN_HISTORY_DAYS,
X_CHECK_TIME => :CHECK_TIME,
X_CHECK_START_TIME => :CHECK_START_TIME,
X_CHECK_END_TIME => :CHECK_END_TIME,
X_SECONDS_BETWEEN_CHECKS => :SECONDS_BETWEEN_CHECKS,
X_CHECK_ONCE_DAILY_FLAG => :CHECK_ONCE_DAILY_FLAG,
X_SQL_STATEMENT_TEXT => :SQL_STATEMENT_TEXT,
X_ONE_TIME_ONLY_FLAG => :ONE_TIME_ONLY_FLAG,
X_TABLE_NAME => :TABLE_NAME );
end if;
end; "
In the upload you see that the load_row function from the alr_alerts_pkg is used. The load_row function is checking if the row must be inserted or updated.
For each package mentioned above, you should create a define, download and upload section.
Downloaded data
Well, hoes the downloaded data looks like.
Again, I will only look at the downloaded data from alr_alerts.
To download alert data use the following syntax :
FNDLOAD apps/….. 0 Y DOWNLOAD ALERT.LCT ALR_ALERT.UPL ALERTNAME=”TEST_BAS”
When downloading you should always tell FNDLOAD which entity should be downloaded.
BEGIN ALR_ALERTS "XXOF" "101060" "TEST_BAS" "P"
ENABLED_FLAG = "N"
START_DATE_ACTIVE = "1997/12/10 00:00:00"
DESCRIPTION =
"Dagelijkse Nachtverwerking voor BAS, Transfer Ap=>GL, Naportaal AP en Rapportages"
FREQUENCY_TYPE = "C"
DAYS_BETWEEN_CHECKS = "1"
MAINTAIN_HISTORY_DAYS = "0"
CHECK_START_TIME = "900"
CHECK_ONCE_DAILY_FLAG = "Y"
SQL_STATEMENT_TEXT = "select * from global_name"
Upload data
To upload the downloaded into another database, simply use the following command with the settings of the new database.
FNDLOAD apps/…… 0 Y UPLOAD ALR_ALERT.LCT ALR_ALERT.UPL
When uploading data, al entities from the configuration file will be uploaded.
Tuesday, June 20, 2006
First post !!!!
First post is online now !!
In this blog i hope to tell more about me and more about my job as a Oracle EBS Dba.
At this moment i am working for a company called Amis, and my latest project is right now with KPN in den Haag.
I recently created a FNDLOAD config file for downloading and uploading an Oralce Alert...
I will tell you more later.
In this blog i hope to tell more about me and more about my job as a Oracle EBS Dba.
At this moment i am working for a company called Amis, and my latest project is right now with KPN in den Haag.
I recently created a FNDLOAD config file for downloading and uploading an Oralce Alert...
I will tell you more later.
Subscribe to:
Posts (Atom)