My 11i Apps DBA OCP

After I passed my beta exam 1Z0-233 Oracle 11i Install, Patch and Maintain Applications and in order to be an Oracle 11i Apps DBA OCP, I had to be either 10g DBA OCP or 9i DBA OCP or the new 2 exams (apps fundamentals 1 & 2), so I applied for 10g and I passed the OCP. So far I am still waiting my 11i beta scoring sheet to see my mark but I guess I did very good on that beta exam I regret that I did not apply the other 2 beta exams (workflow and sysadmin) every one took any one of them (pass or fail) got a free voucher that can be used for the 11i certification program. I did not believe when they were on the path to be an apps dba specially the workflow and then oracle changed the 11i apps dba path (which is a correct step they took). No the path for apps dbas is more clear and more technical.

Have a good day
Fadi

Small issue after cloning

After I cloned my production system I faced the following error when I run adadmin or adpatch

"
AD code level : [11i.AD.I.4]


The Applications System names per the APPL_TOP and the database are different.
Applications System name per the APPL_TOP: clone
Applications System name per the database: prod

If you continue, the Applications System name per the APPL_TOP will be ignored.

Do you wish to continue [No] ?
"
To solve it I had to run the following update statement on my clone database
update FND_PRODUCT_GROUPSset APPLICATIONS_SYSTEM_NAME ='clone' ;commit;


Hope that helped

fadi

Oracle Applications SQL scripts

Oracle Applications has useful collection of ready SQL scripts under $AD_TOP/sql. The following list shows the description of each script. For more details like if a specific script is available on a specific Apps version you need to check metalink note 108207.1 you will find the script and its description below.

adcompsc.pls
Compile objects in a given schema

adcpresp.sql
The script duplicates rows in FND_RESPONSIBILITY in the following way: Find data_group_id dg_id for the given data_group_name. For each row with data_group_id 0 in FND_RESPONSIBILITY, look for a corresponding row with data_group_id dg_id, with the same application_id, and responsibility_name that only differs in the given suffix string suffix_string. If such a row does not exist for the data_group_id dg_id, insert it.

aderrch2.sql
Reports all compilation errors for a given schema.

aderrchk.sql
Same as aderrch2.sql plus it fails if there are any errors

adtresp.sql
A fix for customers who have more than one set of books and they installed languages other than AMERICAN English. The symptom of the bug is that responsibility names are not translated properly for non-Standard data groups.

adutcobj.sql
Count objects by object type in schema

adutconf.sql
Utility script to display configuration of Applications

adutfip.sql
Utility script to display worker information

adutfpd.sql
Utility script to display product dependency information

ADXANLYZ.sql
Analyze all tables in an ORACLE ID with estimate sample 20%

ADXCKPIN.sql
Query the shared_pool area to determine space used by PL/SQL objects and whether they have been pinned.

ADXGNPIN.sql
Creates and runs a "pin" script for all packages and functions in a give schema

ADXGNPNS.sql
Creates and runs a "pin" script for all sequences in a give schema

ADXINMAI.sql
Install tables and views used by the Applications*DBA sql scripts.

adxirc.sql
AD - index - report columns

ADXLMCBC.sql
Live Monitor, Categorize Block Contention

ADXLMLSO.sql
Live Monitor, List Session Objects

ADXLMQMS.sql
Live Monitor, Query Monitor Statistics

ADXLPFLS.sql
Lock Problem, Find Lock Source

ADXLPSLU.sql
Lock Problem, Show Lock Users

adxpriv7.sql
grant privileges to a user

ADXRCSDC.sql
Report Configuration, Show Database Configuration formerly, config.sql (rollback, tablespace, data files)

ADXRCSTG.sql
Report Configuration, Select Table Grants

ADXRSEBH.sql
Estimate the effect of a bigger SGA cache on cache hit rate.

ADXRSESH.sql
Estimate the effect of a smaller SGA cache on cache hit rate.

ADXRSFIS.sql
Find the size (blocks, extents, extpct) of the given index.

ADXRSFTS.sql
Find the size (blocks, extents, extpct) of the given table.

ADXRSFUA.sql
Report the number of blocks used and the number of extents used for every table or index in every user in the database.

ADXRSLFS.sql
Report free extents in each tablespace.

ADXRSQDP.sql
Check for cache effectiveness for dc_xxxxx parameters' values.

ADXRSRTS.sql
Produce a brief database used space report.

ADXRSSIE.sql
Generate a list of tables and indexes whose next extent to be grabbed would be too large to be allocated in their corresponding tablespaces.

ADXRSSMF.sql
List tables and indexes with a number of allocated extents close to their max_extents.

ADXRSSMS.sql
Find space used for one's own segments.

ADXRSSRS.sql
Show v$rollstat statistics.

ADXRSSSU.sql
For a username, report the number of blocks used and the number of extents used for every table or index in that username.

ADXRSSTF.sql
Produce a brief report of database fragmentation by tablespace.

ADXRSSUS.sql
Report how much space each user has.

ADXUPLUP.sql
Generate a list of processes which the given user (NOT the database account's username) owns.

ADXUPSRU.sql
Show all users that have active transactions per Rollback Segment that they are writing to.

Shay's 10 Commandments for the OTN forums

I was reading a funny forum it's titled is "URGENT URGENT PLZ READ B4 OTHERS VERY URGENT NO TIME WASTERS" you should read it when you have time and one of the forum users replied asking to read Shay's Shmeltzer post about the (10 Commandments for the OTN Forums Member) and below I am copying and pasting from shay's blog you can go to his blog to read the full article

"Five rules to getting a better solution
1. Have a meaningful subject line
I'm tired of seeing a subject line like "I need urgent help". Your subject should specify what the problem core is and in what area
2. Give details about versions and technologies
Are you using JDeveloper 10.1.3.1 or JDeveloper 9.0.4? Are you using ADF BC or EJB?
3. Give detailed error messages
Don't say - it throws an error - tell us what the error is and give the stack trace.
4. If possible provide steps to reproduce the problem
If something doesn't work for you - I can't know why if you don't tell me what you did. Sometime you can even post code that reproduce the problem
5. Search before you post
It's for your own good - it brings you the solution faster. Search both the forum and Google for your question - you'll be surprised how many times you'll find a solution that someone else got.

Five rules to become a better member of the community
6. Get a name
In your forums preferences you can set up a name or handle that will help us recognize you (and even a link to your blog). Do you really want to be known as user5667847? Using your real name is even better - then when someone meets you in a conference or an event - They'll already know who you are.
7. Post the solution
If your issue was solved post the solution on the thread so others that run into the same error can fix it faster. Just writing "I fixed it" doesn't count.
8. Mark solved problems with [Solved]
Once you found a solution to a problem edit the thread and add [SOLVED] to the subject line. This will help people identify solved problems.
9. Track your threads
In your forum preferences check the option that alerts/emails you when your thread has been updated. This way you'll be able to get better response time and also answer follow up questions. 10. Don't just ask question
give some answers as wellThe forum is a community - the community will only grow if you'll be an active participant. Posting question is just one side of the coin - actually answering other people questions is even more important. So go ahead and show off the knowledge you have gathered - it is the first step to take if you want to become an Oracle ACE.
"

It’s a great commandment and again I copied/pasted them from his blog so the credit goes to him.
Thank you Shay

I would like to add number 11. is to be gentle at your replies to the users even if they made mistakes since no one knows every thing and we all learn from our mistakes, so try to be gentle as much as you can when you are trying to correct someone mistake and don’t try to embarrass him.

fadi

increment date paramters each run is not working

i schedualed concurrent request that take a date prameter to run daily with the option "increment date paramters each run" but it runs daily without incremeting the date what could be worng i thought the issue was on the date format so i change it but still, i opened a forum under oracle forums with the issue and my friend dgood76 gave me a metalink note with a hint on how to work around this issue. the note number is 339849.1(Unexpected Behavior Using Increment Date Parameter Each Run)

in this note the workaround suggested by oracle development is to make use of a wrapper Concurrent Program which submits the actual Concurrent Request. Any Default Parameter that needs to be reevaluate for each run needs to behandled within the wrapper script.

then note has an examble on how to do that. You basically need to have 2 concurrent programs one that take that date parameter and other concurrent program calculated the date parameter value and submit the first concurrent request with the correct value.

so thank you dgood76 for your support ;-)

fadi

CM priority vs. new manager

At my production environment we have a complete customized module called PoS (Point of Sale) which we use to sale our products and it is integrated with oracle apps financials and CRM modules, among of this customized module we have a lot of customized concurrent requests related to sale process and printing contracts. Those requests mainly related to customer service for ex. If the customer buys a product a concurrent request to print the contract will run when the user submit the sale. we faces an issue at the begging of each month that when the financial users run the journal import and end of month requests which they are many requests by the way. Those requests took a lot of time to run and in turn affect the customer which has to wait until his contract got processed by the CM which is busy handling other request. I hope I described the issue clearly since it is difficult to write.

Anyway to solve this issue I create a new stander manger just for the point of sales module, but I found out that when there is no point of sale requests this manager will stay ideal while the other manger that handle all the other request is busy which is not correct. So I deleted this new stander manager and changed all the PoS request priority to a higher priority, now when every a PoS request run it run first because it has higher priority than the stander request and by this I managed to solve my issue.

To do that I went to system administrator->concurrent->program->define->query the requests and change all its priority to 30.

(0 is highest priority and 100 is the lowest and the default priority value 50)

Please leave a comment if you have better way to solve the issue.

Small issue with workflow email notifications

Well been busy last week and couldn't update the blog, before a couple of days tow users called me complaining that they stopped receiving emails informing them that they have a new notifications that needs their approval, well to be honest I thought I have a serious issue because both users called in different time at the same day, I check the workflow email server setup and it is working fine, after a little investigation I find out that in some how both users preferences got changed, so I change it back as follow

-Once logged into Applications, go to preferences link in the Home page . - At the bottom of that page is the Header - Notifications, Email Type - Select HTML Mail (It was empty)

And every thing back to normal.

Fadi

New credentials for oracle applications

Oracle has lunched a new certification programs after they have changed their apps dba OCP program then new certifications are as follow:

-Oracle 11i System Administrator Certified Expert. (1Z0-232)
Exam Number:
1Z0-232
Duration:
120 minutes
Associated Certifications:
TBD
Number of Questions:
87
Exam Price:
$195 USD
Passing Score:
60%

-Oracle 11i Workflow Certified Expert. (1Z0-231)
Exam Number:
1Z0-231
Duration:
90 minutes
Associated Certifications:
TBD
Number of Questions:
72
Exam Price:
$195 USD
Passing Score:
59%


If you pass any of the above exams you will consider an expert neither an ocp nor oca. Also both exams doesn't require any pre-requests exames.

Fadi

To be a good oracle dba qustion

Well, I recived the following email through an oracle email list the qusiton is quoted below

" Gurus, We all hear about Oracle books and manuals that we should read. What about non Oracle things we need to know to be a DBA? For eg couple of days ago David Litchfield posted a link to an Oracle paper on log buffer internals. The paper had lots of C code in it. Do I have to learn C to become a good DBA? What is the best place to start?
"
And I was reading the answerer for that question and I had to say that one of the users on the list replied with a great answer and apply for every thing in this life, the answer is quoted below
"
Not a guru, but I think one of the most important things for a DBA to know is how to learn - quickly, and just as important for long term success is to have a natural desire or drive to learn. Databases and all the interrelated technologies change so fast that you have to be willing and able to constantly learn, and to be happy with life as a DBA, you have to enjoy that challenge. By knowing how to learn, I mean being able to quickly identify what it is you need to know, where to go to get the necessary knowledge, how to quickly sort through all the BS to zero in on the key concepts that you need, and then how to apply the knowledge to your specific situation. There certainly isn't any fixed set of topics that a DBA needs to know. The label "DBA" describes many different roles in real life and in my particular position, knowing C doesn't really do me any good because I never look at C code. Rather than knowing any specific language, it is more important to understand the concepts of coding and then you can take that skill and pick up the specifics of any language as needed. Obviously you need to be comfortable with the SQL syntax and at least familiar with the procedural code for the RDBMS you are working on. You just have to be as intimate as you can with all the pieces of whatever environment you find yourself responsible for. It is a huge plus to have as much understanding as you can of the operating system and all applications running against your databases, as well as anything else running on the same server. Even an understanding of the hardware and network you are running on can be very helpful. A good understanding of the business you are supporting is always useful too. I think you really have to be a jack of all trades and master of at least one in order to be a really good DBA.

Regarding where to start - there are many paths to enlightenment :-) so just take your pick. Some start as application admins (like me), some as sys (OS) admins, some as developers - all tend to end up with different strengths and weaknesses but I don't think one is inherently better than the others, just different. Whichever path you pick, just try to pick up as much of the others as you can along the way.

Regards,
Brandon
"
To be honest I liked his answer and it gave me a motive

Fadi

Easy tool for building ER diagrams

I was searching for an easy free tool for building ER diagram, and found this amazing tool (DBDesigner 4) below is a screenshot, you can download it from here and it also build oracle scripts for you.

Starting on apps field (my personal theory)

I receive this question (how can I start on apps technical filed? How can I be an apps dba?...etc)
Well I am going to give some hints from my point of view, those hint may not be the ideal therefore any reader of this post can leave any suggestions to make the steps better and easier.

-First of all you need to have instance (vision demo database) so you can test things on it, by this I mean that you should read the installation guide which will guide you on how to build the stage area and what are the pre-requisites (hardware/software) for the installation and the installation process using rapidwiz
The installation will not finish successfully form the first time unless you are so lucky, and from the installation you will get a good experience.
-after the installation you need to understand the applications file system structure.
- also you have to read oracle applications concepts guide.
Then you can start reading about applications administration and maintaining procedures, you need to understand both adpatch and adadmin.
- By now if you manage to install oracle applications and to apply patches on it then you are in the right track.
-then after that you need to read about backup and recovery.
-you need also to monitor oracle forums and oracle blogs.
-keep metalink as your friend.
-when you see an interesting issue in forum or blog try to track it and maybe try to solve it if you can this will give you an add experience.
-try to participate on oracle applications email lists like ittoolbox …etc.
-try to get oracle applications administration books like dba filed guide and others.

finaly keep in mind that (no pain no gain)

Please leave comments on what you think the best way to be an apps dba

Fadi
P.S all the guides mentioned above can be found under (for apps 11i) http://download-east.oracle.com/docs/cd/B25516_16/current/html/docset.html

When seniority is the problem

Day after day and when you get more experience, you start to analyze the problems you faces differently (maybe in a way you are trying to make an easy problems difficult), its kind of weird though, before 2 days I faced an issue with one of our databases the issue was that when you set a dbms job it only run once, but it never rerun the job again, to make long story short I spent around 2 house trying to figure out what the issue was, restarting the database is not an option for me (it is not windows if you get what I mean) anyway finally my colleague restarted the database and voala all the jobs are working normally the problem was that one of oracle's ora_j00 was not running.

Learned lessons
-maybe in some how oracle database is like windows after all ;-)
-the problem might seem difficult but in reality it is easier than you think.
-try to search for the easies way to solve the problem not the difficult one.

Fadi

Virtual Consulting

It's all about sharing
I received the following email and I thought that it worth sharing
"
ERPstuff Virtual Consulting
All the members of ERPstuff are just like a family and good friends. I was thinking that there should be more challenging tasks in addition to routine tasks of members and those students who are struggling for jobs but due to no experience are getting any chance. So I have developed a business plan in which all the member can work as employee with many benefits like share in the individual project profit on the basis of his/her participation. At any time the member can get an experience letter of projects he/she completed which will help him is finding attractive job.
ERPstuff Virtual Consulting Services
Oracle E-Business Suite
Implementation
- Troubleshooting (Support)
- Training
- Database Administration
- System Administration
- Quality Assurance
- Customization (Forms & Reports)

Development
- Software Development & Customization
- Data Warehousing
- Industrial Automation
- Web Development
- Embedded Device Application
- Microcontroller Programming

Others – In-house Developments
- ERP - Enterprise Resource Planning
- Hotel Management System
- Point-of-Sale
- Embedded Device Applications
- Time Attendance and Management
- Networking Solutions
- Graphic Designing

Please send the following detail at business@erpstuff.com for mentioned positions,No need to send detailed cv right now just send the following detail in the email.
- Name
- Position
- City & Country
- Mailing Address
- Contact Numbers
- Education
- Experience (years if any)

Positions
- Marketing Executives
- Analysts
- Functional Consultants
- Technical Consultants
- Quality Control Consultants
- Software Developers
- Hardware Engineers

Your suggestions will be appreciated.
Thanks
Administrator
"
Nice idea isn’t it?
I sent my details direclty

Fadi

Testing Restore

I have been busy all the last week with testing our backup/restore software if its really working, we are using hp data protector and it is really a great product for backup and recovery, we were able to restore (applications and Database around 350GB) successfully, we also restored our other 4 production databases (each one around 300 GB) successfully, also our network guys were able to restore our email/file system servers successfully. The only issue we faced was the time, it needs a lot of time to restore for ex. to restore the apps and db (350GB) it took around 6-8 hours, so the management decided that we should have a disaster recovery site (just incase we needed it) and that will be our next project.

Fadi

What a mess

Today when the purchasing consultant tried to enter employees through the Purchasing Setup the following error occurs
ERROR
Error message is APP-PAY-06041, you cannot enter person form because HR is fully installed.

I felt like reinstallation is coming on the way, as far as I know you cannot do unlicensed a registered products (please leave comment if you know a way to do that).
Any way after searching on metalink I found note 360552.1 and the note says that to solve this issue I have to 2 options
-we should use Human resource to enter employee not purchasing.
Or (this is the first time I like or ;-)
- using sql

update FND_PRODUCT_INSTALLATIONSset STATUS ='S' where application_id= 800;

commit;

Now every is working fine enjoy consultants
fadi

R12 Vision is available now online

I received the following from solution beacon

"Solution beacon has installed R12 Vision instance which is now available on their website.
Check it out at http://www.solutionbeacon.com/tools_vision.htm

Please note that there are some steps to install the desktop plug-in differently from Release 11i. Please also note that Release 12 requires a user to enable ActiveX and download the Sun JRE instead of the JInitiator the first time the Applications are accessed."

Thank you Solution Beacon Team

Fadi

Upgrade journey to R12

Well at the beginning I though it will be a difficult task but when you get in to it, you will find out that it's kind of clear task so far ;-)

My plan was to clone my production system to a new server just for the upgrade purpose so I had to do a cloning from multi-node to single node based on note 233428.1 and of course with a great help from sam's post (Multi Node to Single Node Cloning) thank you sam.

After that I started the upgrade journey to R12 I just finished the Database upgrade path acutely I am running the utlrp.sql script while I am writing this post, every thing is clear so far.
I will keep you posted in case of any issue and incase I reach the R12 successfully or not (I hope I will).

I need to major the time needed to do the upgrade and to know if there any issues, my next step will be the upgrade to R12 on my Test servers.

Fadi

Apps 11i with Windows vista

(just an update, As a workaround for IE on Vista issue, Apps 11.5.10 works fine on Vista with firefox)
Our network department has a plan to go with windows vista and they asked me to open a TAR with oracle to get their official response.
Below is oracle response

QUESTION
=========
Summarize customer's question here. One question per heading.
Unable to connect from MS-Vista Client on e-Business suite 11.5.10
RESEARCH
=========
Steven's Blog
ANSWER
=======
Currently Jinitiator is not certified on MS-VistaDevelepemt is working on it but we don't have any ETA for it So, please don't upgrade to MS-Vista Sorry for that
KNOWLEDGE CONTENT
=================
Platform / Port Specific? = Not certified for the moment

Cloning after Appling Patch 4676589 ATG_PF.RUP 4

If you applied the ATG_PF.RUP 4 patch you will face the following error when running the perl command
perl adpreclone.pl dbTier or appsTier

ERROR
Global symbol "$resultclone" requires explicit package name at adpreclone.pl
line 525.
syntax error at adpreclone.pl line 527, near "$resultclone !"
syntax error at adpreclone.pl line 530, near "}"
Execution of adpreclone.pl aborted due to compilation errors

There are 2 solutions to this error:
-applying one of the patches (5132155,5225940,5235401).
Or
- You can make the following changes in the file adpreclone.pl 115.49, replace the following and retest the issue.
Replace:

$resultclone = system "$syscmd";
# added for bug #4448838
if($resultclone ! = 0 )

With:

my $resultclone = system "$syscmd";
# added for bug #4448838
if($resultclone != 0 )


enjoy cloning
fadi

Journey of a Thousand Miles

I was reading Steven's article Journey of a Thousand Miles, I liked the article and I am quoting from it the following:-

"In the blogosphere, it's not uncommon to see blogs die after a few postings. That's unsurprising. Writing meaningless blather is easy, but writing well is hard. No one's going to expend valuable time in blogging without feedback, and I'm no exception. So, your comments will be vital in keeping this blog fresh and alive. I look forward to hearing from you."

And I would like to tell you the same; I look forward to hear form you, so please leave your comments.

Finally, Oracle is not Magic, it just takes years of experience ;-)
Fadi