EBS database upgrade 10gR2

After installing the EBS 11.5.10 multinode, I asked myself what could be next....I decided to upgrade the database (9.2.0.6) to 10gr2 (10.2.0.2.0)
It took me a few days of hard work, but finally the upgrade has finished and I am now performing the last steps to complete the upgrade and if the EBS is still working.

I used Metalink note 362203.1 to upgrade and choose to manually upgrade the database instead of using the database upgrade assistent (DBUA).
More to come...

EBS 11.5.10 install

After installing Oracle Linux and staging the dvd's (using adautostg.pl), I started the install.

On each external hard disk I created two virtual hard disks, so I decided to install the APPL_TOP on it's own disk. This way I want to create a shared APPL_TOP, by installing the APPL_TOP once and then share it with my other node.
When using the shared appl_top feature in 11.5.10, you must also share the technology stack (iAS and 8.0.6 directory's).

I created the shares using the mount command on the machine where the software was not installed. I also had to start the NFS proces.
mount -t nfs machine1:/appl /appl
mount -t nfs machine1:/ebs/prodora/8.0.6 /ebs/prodora/8.0.6
mount -t nfs machine1:/ebs/prodora/iAS /ebs/prodora/iAS

Now, from the second machine I can see and work with the directory's mentioned above...

The install went perfect and completed without errors

Internet Explorer 7 blocker....

After my last automatic update on Windows XP, suddenly Internet Explorer 7.0 was installed instead of IE6. From that moment on, I had some problems when I wanted to start an EBS session...
I decided to uninstall IE7. After the uninstall, IE6 is returned....But also my install updates icon !!! Windows wants to install IE7 again. On Metalink I found the following tool from Microsoft. It's a IE7 blocking tool, so the automatic updates are installed, but IE7 is not.
Download the exe form the following site ....

http://www.microsoft.com/downloads/details.aspx?FamilyId=4516A6F7-5D44-482B-9DBD-869B4A90159C&displaylang=en

After installing the .exe, 4 files are placed on your computer.
When executing the IE70Blocker.cmd, a new registry key is created which prevents IE7 from being installed by the next automatic update...
From the command prompt

C:\>IE70Blocker /B

MICROSOFT TOOL KIT TO DISABLE DELIVERY OFMICROSOFT INTERNET EXPLORER 7
Copyright (C) Microsoft Corporation. All rights reserved.
Blocking deployment of Internet Explorer 7.0 on the local machine
The operation completed successfully

Preparing Oracle Linux....

Before you can begin installing EBS 11.5.10 on the Oracle Linux platform, you have to install the following packages also..

- libaio-0.3.102-1.i386.rpm
- libaio-devel-0.3.102-1.i386.rpm
- openmotif21-2.1.30-11.FC2.1.i386.rpm

Also install the following patch (download from Metalink...)

- p4198954_40_LINUX.zip ( compat-libcwait-2.1-1.i386.rpm + compat-oracle-rhel4-1.0-5.i386.rpm)

And don't forget to set the LD_ASSUME_KERNEL parameter to 2.4.19

Now, you are ready to install the EBS 11.5.10

Oracle Enterprise Linux

Because of my last problem with the 11.5.10 install, I decided the begin all over again. What I didn't mention last time, was that the staging area I used was downloaded from Oracle. Maybe that's why I faced so many problems when installing multi node. So, this time I am creating a staging area from the Oracle EBS dvd's.

I also changed my operating system. Instead of Red Hat ES3, I downloaded the Oracle Linux from OTN and installed on my two external hard disks. I wanted to know if this linux version from Oracle was already ready for an EBS 11.5.10 install.
Well, it isn't. You still have to add packages. A summary of the Oracle Linux install will follow soon.

My blog is worth $564.54.
How much is your blog worth?

Multi node EBS 11.5.10

I have deleted my single instance EBS 11.5.10 environmenr and installed a multi node 11.5.10 environment. I have two nodes, and seperated the database + conc. managers from the application tier. Everything seems to work fine, but when I want to startup a forms session..something goes wrong with the jinitiator. Maybe someone has had the same problem.
In the javaconsole I recieve the following error :

java.net.ConnectException: Operation timed out: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.Socket.(Unknown Source)
at java.net.Socket.(Unknown Source)
at sun.net.NetworkClient.doConnect(Unknown Source)
at sun.plugin.protocol.jdk12.http.HttpClient.doConnect(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.(Unknown Source)
at sun.net.www.http.HttpClient.(Unknown Source)
at sun.plugin.protocol.jdk12.http.HttpClient.(Unknown Source)
at sun.plugin.protocol.jdk12.http.HttpClient.New(Unknown Source)
at sun.plugin.protocol.jdk12.http.HttpURLConnection.createConnection(Unknown Source)
at sun.plugin.protocol.jdk12.http.HttpURLConnection.connect(Unknown Source)
at sun.plugin.protocol.jdk12.http.HttpURLConnection.getInputStream(Unknown Source)
at oracle.jre.protocol.jar.HttpUtils.followRedirects(Unknown Source)
at oracle.jre.protocol.jar.JarCache$CachedJarLoader.download(Unknown Source)
at oracle.jre.protocol.jar.JarCache$CachedJarLoader.load(Unknown Source)
at oracle.jre.protocol.jar.JarCache.get(Unknown Source)
at oracle.jre.protocol.jar.CachedJarURLConnection.connect(Unknown Source)
at oracle.jre.protocol.jar.CachedJarURLConnection.getJarFile(Unknown Source)
at sun.misc.URLClassPath$JarLoader.getJarFile(Unknown Source)
at sun.misc.URLClassPath$JarLoader.(Unknown Source)
at sun.misc.URLClassPath$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.misc.URLClassPath.getLoader(Unknown Source)
at sun.misc.URLClassPath.getLoader(Unknown Source)
at sun.misc.URLClassPath.getResource(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadCode(Unknown Source)
at sun.applet.AppletPanel.createApplet(Unknown Source)
at sun.plugin.AppletViewer.createApplet(Unknown Source)
at sun.applet.AppletPanel.runLoader(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
WARNING: Unable to cache http://14-320-584.localdomain:8000/OA_JAVA/oracle/apps/fnd/jar/fndforms.jar

Has anybody seen this before ?

Oracle E-Business Suite Technology Blog

Yesterday I received an email from Steven Chan, Director Applications Technology Integration. He mentioned my blog again on his site. His message...lets share our EBS knowledge. Couldn't agree more !!!
See his interesting EBS blog !!
http://blogs.oracle.com/schan/

OCP Exam

Last week I signed up for the beta exams OCP EBS Dba. Tommorow is my first exam, and later on this week I will take another exam.
See http://www.oracle.com/global/us/education/certification/ebusiness_appstechnology.html

for more information. I will take the last two exams...

Support status Oracle on vmware...

Following support status from Metalink note 249212.1

Oracle provides support of the Oracle Stack when running on a VMware virtual machine in the following manner. If a problem arises and it is a known Oracle issue, Oracle support will recommend the appropriate solution. If that solution does not work, the issue will be referred back to VMware for support. If the problem is determined to be an unknown Oracle issue when running on a VMware virtual machine, and the issue cannot be reproduced on a physical system by Oracle support, the issue will be referred back to VMware for support. Oracle and VMware have in place a joint customer support agreement to enable customer support issues to be transferred between the two partners. In addition, all versions of RAC are not supported on VMWare by Oracle.

changing applications background

Some practical notes times time. For my own EBS 11.5.10 installation I wanted to change the background in applications. To do this, modify the $OA_HTML/bin/appsweb.cfg
Look for 'background' and give the complete path to the gif you want to use. The gif must be placed in the $JAVA_TOP/oracle/apps/media directory.

When you bounce the webserver, youw own background should be visible.

You can also change or remove the default Oracle splash screen. In the same appsweb.cfg, look for splashscreen and comment it out or change the gif, be sure to place the new gif in the same directory.

Installing 9i database on Suse SLES9

Last week I was asked to install two databases on a Suse linux environment running on vmware. It had to be a 10g datawarehouse database and a 9i (9.2.0.7) database.
Next to the datawarehouse database I also had to install Oracle warehouse builder (owb) 10g.
I started with the owb software and the 10g datawarehouse database. I installed an owb repository in the 10g dwh database and the 10g install was ready. Next database was the 9i database..I first installed the 9.2.0 software and patched it up to 9.2.07. After the 9.2.0.7 patch was installed, I tried to create a database using DBCA. But DBCA did not work !! I have used DBCA before and never experienced any problems with it....I encountered two problems..

1. Starting up DBCA and getting the following error '/oracle/product/9.2.0/db1/bin/dbca line 124: 16015 Killed $JRE_DIR/bin/jre DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS '
Via Metalink note 292278.1 I found out that you have to adjust the dbca script (!!). You have to modify the line #124 in the dbca script. Change it to '$JRE_DIR/bin/jre -native -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS '. After the change, I had to run the root.sh script in the $ORACLE_HOME (as root..) and DBCA started up without errors....

2. During the creation of the database the following error occurred.. 'ORA-27125: unable to create shared memory segment'. I found note 293988.1 on Metalink, and the note claimed it was a kernel problem. But my kernel was not the same as the 'problem' kernel, so I did not see any use in upgrading my kernel. There is also a workaround. As root try the following..
echo 1 > /proc/sys/vm/disable_cap_mlock
After this, I tried to create the database again and this time no problems....

EBS Install problems

During my 11.5.10 install on Redhat ES 3.0, I experienced some problems.
1. After installing the APPL_TOP (almost at the end of the install....), the installer wants to run autoconfig. But the installer hangs and when checking the logfile, the following error occurs..
libdb.so.3: cannot open shared object file: No such file or directory
I searched Metalink for a solution and found out that the compat-db-4.1.25-6.i386.rpm package should be installed. After installaing the package, the problem was not solved...Using google I found out that someone had tried to relink the libdb.so.2 to libdb.so.3. Libdb.so.2 is present in the /usr/lib directory, so lets give it a try. After generating the link using ln -s, I restarted the rapidwiz (./rapidwiz -restart) and this time the installer continued !!!
2. The second problem occured at the post install checks by the installer. According to the installer my *.dbc file in the $FND_TOP/secure was corrupt. Seemed kind a strange, since the rapidwiz did create the dbc file in the first place. When looking at the dbc file, I noticed that the dbc file the installer used to connect to the databaseas in fact no more then a template !!! The rapidwiz never produced a valid dbc file, which I coukd use to connect to my 11i database. But I never saw an error anywhere ?? I decided to run autoconfig myself and find out where autoconfig is going wrong. Autoconfig ended with 'no errors' ??? The autoconfig proces did create a dbc file this time. I ran the post install check again, and everything was fine.
3. The last problem was a FRM-92050 error when trying to connect to the forms server.
This error was solved using the note 299187.1 from Metalink. I had to set two variables, the LD_ASSUME_KERNEL (2.4.19) and the $LANG (empty).

So, now my EBS 11.5.10 is working and I will look for more problems.

EBS 11.5.10

It has been a whule since my last post. I have been busy with al lot of things.
I finally managed to install EBS 11.5.10 on Redhat ES 3.0
Before and during the install of EBS I ran into a lot of problems. The easy ones were mostly corrupted EBS dvd's or in case of a disk download from edelivery, an invalid download.
It took me a few days (!!!) to get the whole stage area complete and working.

What other problems I encountered, I will post later on...

11.5.10 install

I'm trying to install EBS 11.5.10 on Suse Linux.
First I created a staging area on my external hard disk. I found out, a few files (*.zip) could not be copied...Disk7 from the Database1 dvd and disk6 from the first APPL_TOP dvd.
All the other files went well, so I decided to download the bad disk from the Oracle E-delivery site. After downloaded and burned the cd's..the copy to my staging area went fine.
So, today I can begin the install. It's running right now, unzipping files from the RDBMS disk.
To be continued....

Recyclebin 10g

I have looked into the recyclebin feature in 10g.
I have tested it by dropping a table form the user scott, comiited the drop and recovered the table.


SQL> conn scott/tiger
Connected.

SQL> descr recyclebin
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER


SQL> select object_name from user_objects;


OBJECT_NAME

--------------------------------------------------------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
SALGRADE

6 rows selected.

SQL> drop table salgrade;

Table dropped.

SQL> commit;

Commit complete.

SQL> select droptime, original_name from recyclebin;

DROPTIME ORIGINAL_NAME
------------------- --------------------------------
2006-08-29:07:10:28 SALGRADE


SQL> select object_name, original_name, operation from recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
BIN$HCIPm0RKljjgQKjAAV0coQ==$0 SALGRADE DROP


SQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
BIN$HCIPm0RKljjgQKjAAV0coQ==$0

6 rows selected.

SQL> flashback table salgrade to before drop;

Flashback complete.

SQL> select object_name from recyclebin;

no rows selectedSQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
SALGRADE

6 rows selected.

SQL> select * from salgrade;

GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

The recyclebin feature does not work for sys objects
If you realy want to delete the table and release the space, use the purge command.

Patching HP unix

It has been a few days since my last post. I have had a change of project.
I am working on a internal project.
The last two days I have been patching unix....
I have never done this before. I am used to install Oracle patches using adpatch, but now I had to use swinstall and swremove.

10g on vmware

Yesterday I installed a 10gR2 Oracle database on Suse Linux. Nothing special about that off course, but I did it on my Windows XP laptop using vmware workstation.
With vmware you can create virtual machines with their own operating system.
It takes a lot of resources from your laptop, but it was fun to install it.
Just remember that Oracle does not support vmware in a production environment....see also
http://blogs.oracle.com/schan/2006/08/23#a636

EBS 11i laptop/desktop

In the UK there is a company that sells laptops with EBS 11.5.10 already installed. Because of the great demand of these laptops, they now also sell a desktop version.
For more information, check this link..http://www.appsdba.co.uk/products.html

11.5.9 install part III

I have checked everything so far. The most important test is to check whether all the admin tools work ok. So, I tested adpatch and adadmin.
The Oracle Applications Manager is working, and the applications login also. I cannot start up a forms session from the webapplication..nothing happens. I can login forms without the web application, directly into the forms application.
I think it could be a memory problem, when I start all the services..my laptop is running out of memory.

Oracle Application DBA field guide

At this moment I am reading a book called 'Oracle Applications DBA field guide' from Elke Phelps and Paul Jackson. I must say, it's a really good book with al ot of detailed information about Oapps dba work. I suggest every oapps dba to buy and read this book.
ISBN number 1-59059-644-7

11.5.9 Install part II

Ok, I have solved the jsp problem mentioned in my first 11.5.9 Install post. I can login into the EBS with the sysadmin account. But when I want to start a forms session, nothing happens.
Not even an error, so I have to go through the logfiles again.
I must admit, it isn't easy to work with the EBS 11i on a windows platform when you are used to unix/linux. For example, when I set the environment set ORACLE_HOME=E:\oralce\proddb\9.2.0, and after this I try to cd $ORACLE_HOME, nothing happens. Echo $ORACLE_HOME results in $ORACLE_HOME. SO, I found out instead of using the well known $, use %ORACLE_HOME%....
Well, nobody said it was going to be easy, I have some work to do.

More to come in part III

Changing concurrent output name

Someone asked me if the name of the concurrent output could be changed.
There are two ways to name your concurrent output using the parameter APPCPNAM.
This parameter can be found in the applications environment file (in the $APPL_TOP)
The following values are possible..

APPCPNAM="REQID" (o999999.out)
APPCPNAM="USER" (ORAUSER.out)
APPCPNAM="USER.REQID" (ORAUSER.999999)
APPCPNAM=(unset or unrecognized syntax) (o999999.out)

11.5.9 install

Yesterday I successfully installed 11.5.9 on my windows XP laptop. Before I could begin the install I first had to install al kind of tools like cygwin, gnumake etc...Also installed VC2005 (C++). See note 117545.1 on Metalink for these tools.....
After doing this, I finally began the installation. The first problem I encountered was that the rapid installer didn't find any free space on my external disk. , Found a solution on Metalink, a higher version of the installer (patch 3486525, oui version 11.5.9.22 ). Ok, now it reconized my 250 gb of free space, but finds other errors, which the older version did not encounter ?? Tried again with the older version and ......it works !!!! Don't know why, and I don't care.
So lets begin...Oracle calls it the rapidwiz, but I don't know what's rapid about it...
Because I want to install a vision database, a ebs database with data, the installer skips the first database cd's ( I think...). There are 18 database cd's and at 98 % (cd 18) the installer suddenly hangs....it hangs on adsvdcnv.cmd, at least it takes a long time. Just in case it really hangs, lets google..and yes I find some people with the same problem. But none of them satisfy me. Lets find the logfiles....I found them in the $ORACLE_HOME/appsutil/logs
Huh, the installer hangs on the database conversion, but according to the logfile two steps ago it ran into an error ? It seems the database creation did not went very well..I cannot log in as sysdba and neither can the script that should create the controlfiles !!
I can log in as SYSOPER with pwd change_on_install, and from there connect as sysdba (?)
Created the controlfile myself, and also finished the other scripts including the adsvdcnv.cmd script. No errors...it takes 2 minutes to run this command...But still the installer hangs. In the windows taskmanager I find a sql session...lets kill it. Yes, the installer continues and asks for the tools cd nr1.
It took me te whole night to finish the installation, but it did. At the end there was only a problem with JSP pages, I have to figure that out tonight..

I still don't know why the creation of the database went wrong, but I'm glad I was able to solve the problem. Now that I have an 11.5.9 EBS running, I can patch, clone etc...

Installing 11i

I have tried to take the online course at the opn site, but I couldn't. I received an error, so I have to try again. I also tried to install 11.5.9 on my laptop, I bought an external hard disk, but that not as easy as I thought. Before installing 11i on windows, you first have to install all kind of tools, like gnumake etc..It seems the EBS 11i was designed for Unix only, so the install only uses unix shell scripts instead of windows executables....Tomorrow I will continua the install.

Oracle Implementation Champion

On the Oracle Partner Network (OPN), I found a learning path to become an Oracle Implementation Champion for the EBS 11.5.10.
If you successfully pass the tests, you can call yourself a champion !!
I have tried it at work, but it didn't work. I will try it tonight at home....I will let you know.
For OPN you or your company must be a Oracle partner...

http://www.oracle.com/partners/education/roadmaps/global/implementation/impl_11i10appdba.html

EBS 11.5.10

Some nice features when using EBS 11.5.10

* FNDCPASS has a new parameter, ALLORACLE. With this parameter all Oracle Application schema passwords can be changed in one call (apply the latest application technology patch (11i.ATG_PF.H RUP3 )).

* New in 11.5.10 is also the 'forms personalization'. In earlier versions of 11i, forms personalization was only possible by editing the CUSTOM.pll using the Oracle Forms Builder. Now there is form in the EBS to make these personalizations....Using Help-->Diagnostics-->Custom Code-->Personalize , you can change the form.

* To move personalizations between instances, you can use FNDLOAD. Use the $FND_TOP/patch/115/import/affrmcus.lct to download and upload the personalizations from one database to another.

Work

Today I am working at home for a change. I am trying to prepare myself for the performce job next week. I want to work out a few scenarios. Today I want to find out more about the standby database, maybe it's an option... Above you see how a standby database works..


Update : I also ran into the possibility of upgrading your windows 2003 server from 32 bits to 64 bits to gain more memory....but I have no idea if such an upgrade is easy to do....

Performance

Yesterday I visited a customer who is facing performance problems in their Oracle environment. I have to go back next week, because I had to examine some solutions.
Their environment exists of an Oracle 10gR2 database, and they are running discoverer and an a application on the same database. That's what causing the performance problems....Now I have to look for the best options to solve this, and present them to the customer. Together we will have to solve this problem.

Huh ?

This morning I was searching Metalink for another FNDLOAD issue, when I ran into the following note 37689.1 ,dated 14-JUL-2006....

It seems Oracle has its own FNDLOAD config file for an Alert !!!
This note is very recent, when I started te work on the config file there was no documentation on Metalink on this subject.
But I don't understand...
Why is the Alert not mentioned is the documentation of FNDLOAD (note 274667.1) ?
Why is the Alert config file not in the $FND_TOP/patch/115/import directory, just like all the other config files ???

Well, it seems I have done a lot of work for nothing. At least now I know how FNDLOAD works in detail.

I suggest everyone who wants to transfer Alerts via FNDLOAD, uses the Oraclce config file..

Services in Windows XP

I always face the same problem when deleting/uninstall an Oracle database from my laptop. I don't know why, but I have always some Oracle services left. After a while you have an large list of services, you no longer need to see...Since you cannot delete them from the services screen, I found a way to delete them from the command line.

In the command prompt use the following :

to delete a service you no longer use : sc delete 'service name'
After refreshing the screen, you will notice the service is no longer in the list....

Unix commands on windows

Today I have installed cygwin on my laptop. With cygwin, you create an unix environment to work with your local databases. Otherwise you only have the command prompt, with cygwin you get an unix like screen and all the unix commands seem to work. I love using 'ls' instead of 'dir'.....

I have also started to read the "Oracle Applications Dba field guide". As far as I can see, it's a nice book with all the tasks a Oapps dba should perform or must know. When I have finished the book, I will tell you more about it.

Oracle rocker......

I participated a OTN forum on '10g Enterprise Manager' and got the following reply...
The problem Dele is facing, is a typical windows problem when using the Enterprise Manager..


DELE
Posts: 9 Registered: 7/13/06
backup with enterprise manager Posted: Jul 19, 2006 1:43 AM

Reply
Dear Folks,I have oracle 10g running on windows 2003 server. I want to use enterprise manager to schedule backup. I tried testing the backup but I receive an error message that user name or password is not correct. I am very sure the password is correct.I contacted my Network administrator who granted me backup operator privileges on the host of the database. To my surprise the error message persists.I installed oracle on windows xp standalone and tried to schedule the backup again with enterprise manager i get the same error again.Please anyone with a solution kindly contact me. This is very urgent pleasesss.

Bas Klaassen
Posts: 40 From: NetherlandsRegistered: 7/22/05
Re: backup with enterprise manager Posted: Jul 19, 2006 1:54 AM in response to: DELE


Reply
Be sure that the os user has the 'Logon as a batch job' privilege.

DELE
Posts: 9 Registered: 7/13/06
Re: backup with enterprise manager Posted: Jul 19, 2006 2:45 AM in response to: Bas Klaassen


Reply
Cheers mate,Your solution works fine. U are a cool oracle rocker.

Import status

Today a colleague started an import, approximately 9 gb (one scheme).
At a certain moment the import was running for three hours and I wanted to know what was happening.
I found the following sql script, which shows you the current table in which the import is inserting rows. It seemed our import was inserting over more then 18 million rows.

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

The script was published by Nasir Mahmood.
Here is an example output of the script....

TABLE_NAME ROWS_PROCESSED MINUTES ROWS_PER_MINUTE
------------------------------ -------------- ---------- ---------------
INTO "ICE_CVS_FAC_TUSSEN_TAB" 3626392 1436 2525

Especially the table sys.v _$sqlarea is very interesting, I am going to find out more about this table.

Internet

Because of the moving project, I was offline for a few days.
unfortunately, the I also had to cancel the Oracle meeting because of I had to work on the house..I hope there will be a next time. Tuesday I am at work again, so hopefully I have more DBA news then..

Database special

tomorrow evening I will be joining an OGH meeting in Holland.
The meeting is called 'Database special' and will cover the following subjects :
1. Sleepy cat
2. SQL Developer
3. Times ten
4. Oracle database vault

John Abrahams from Oracle will tell us more about these subjects
And I will let you know...

Work....

Todat the second day in our new house....there's a lot of work ot be done.
I'll hope to post some real dba stuff later..

Restore/recover using RMAN

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.

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.

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]'`

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)

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;

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....

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

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.

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/

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.

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.