BNFD - Your DBA NoteBook On The Net!!!

Tuesday, September 25, 2007

Caution!!! DisClaimer From The Owner of Blog

The views expressed are my own and not necessarily those of my company or Oracle and its affiliates. Also the views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine. I’ve been using Oracle since 1998. This blog opines on issues to share my investigations and experiences about these technologies.Before using any script or contents from this site in your production systems , please test it in a development environment

Welcome To Our Big N Fat DBA Launching

Hi Folks,

The very reason of creating this blog is to have a platform to get in touch with you guys, share the aspects that I have experienced and learned on the job. I will share some of what I have learned in my last 6 years with those of you who are new to the role, or considering it.This needs to be an interactive platform as promised by you buddies!!! Its my pleasure to bring you my team of professionals assisting me in bringing the unique state-of-the art DBA Training program to you. In this journey what you will be looking for :

1.Session Road Map
2.Session Summary
3.Things You did not Get/Feed Back/ Comments...
4.My Questions to You...
5.Posts by Sessions


You are going to be the next Big N Fat DBA.... Here We are........
[Some questions I was asked today in the Big N Fat DBA seminar... ]

How many of you want to be Oracle DBAs ?
All Raised their Hands ..... I counted... it was 86.Thats nice ...

From the center Jane had a question... How to become a DBA ?

BNFD: This is a valid question. Though this can be a tough field to get into. Since DBAs ought to be very very responsible for the Organization's most valuable assets " Their Data". So corporates are definitely careful in choosing the chaps for this position. However, on the other side of the coin, there is a certain demand curve that seems to outstrip the experience supply. That being the case, there is some opportunity for the DBA juniors.

Typically, even the DBA juniors will already have some IT experience, either as a developer or as a system administrator. So, for the truly new beis, finding such a position is a place to get your foot in the door. If you are already at a company that uses Oracle, then it's time to start looking for opportunity within your company. For example, if you know your company is going to have a big Oracle-related development project gearing up, it's time to put your hat in the ring to work on it.

Educating yourself is always a good start too. User events, sessions at Big N FAt DBA for beginning DBAs, and reading will all help you as you search for that Oracle DBA job.

Question: What is the minimum background needed before companies will even consider someone for a DBA role?

BNFD: For a new resource, if you don't have two years of previous DBA experience, then it's going to be difficult. Typically, a minimum of five years of IT experience, in addition, is going to be preferred as well. For the newer DBA, OCP certification is a must if you want to be able to sell yourself in spite of lack of experience. Years ago, I knew of at least two DBA contractors who were hired with no Oracle work experience, little overall IT experience, who graduated with an IT degree and got their OCPs. I don't think you will see that in this day and age.

Something else to consider is that many companies already have experienced DBAs on staff, so they may be looking for a junior type, at a lower salary, to do things like basic monitoring and so on. Often these companies will try to hire from within too. This is the kind of opportunity that a DBA want-to-be needs to be on the look out for. While the short-term salary benefits might not be great, the experience you will gain will eventually give you an opportunity for the salaries that experienced DBAs can demand.

Question:Is certification worthwhile for new DBAs?

BNFD: I think its truly necessary.This gives a direction to DBA thought process!!! I do not think that its just a memorization exercise.How ever it provides the basic orientation about the subjects of Oracle in its entirety. Do and have it.

Question:What's the best way to learn about administering Oracle databases?

BNFD: The best ways to learn how to administer Oracle databases are:

1.Find a Guru who knows Oracle and who knows how to mentor. I had a great Guru when I first started working with Oracle Technology and it makes all the difference in the world.
Training is important and helpful.To start with I can suggest you sit down with Oracle and use if for a while (create a few tables, a few users, and learn basic SQL) before you actually go for the training.

2.Read, read, read. Load up on books.

3. Join Oracle related forums and news groups and read them daily.

4.Forums like http://www.dba-oracle.com/, http://www.quest-pipelines.com/, Oracle-L and LazyDBA all will provide insight and education on a daily basis.

5.User group meetings, like Big N Fat DBA - America, IOUG-A, are invaluable to the learning process. It is in these environments that you can learn from a large number of people, on a large number of topics. In addition, you have the University sessions, such as the one I'm doing for the beginning DBA, that can be most helpful in learning about Oracle.

6.Finally, don't believe everything you hear or read, in fact, don't believe anything you hear. Oracle is about few absolutes. Every system is different, and there are many different ways of doing things. Do not build yourself a box of ideas that can never change, because that does not make for the best of DBA's. There are some best practices, and standards, that are somewhat absolute, but for the most part, it's all fluid and situation-dependent.

7.You may choose to learn with us.We have expertise of giving Big N Fat DBA's to the world of DBAs. After creating 56 DBAs in 2 years we are commited to craft your career with our special and needful inputs to build you taking decisions for the benefits of company.

Question:Is it possible to learn on-the-job DBA tasks without being on-the-job?

BNFD: It is possible to learn on-the-job DBA tasks, but the real question is can you retain what you have learned without having the opportunity to practice it? The job is key because a lot of what the DBA does is on-his-feet thinking and responses to out-of-the-ordinary problems. There are numerous technologies and varying complexities. This is hard experience to acquire in a strictly learning environment.

Question:What are the first few questions new DBAs have when sitting down with Oracle for the first time?
BNFD: That depends on if they have any experience with databases at all. If not, then the first question is, where do I start? Things like using SQL, and SQL*Plus are of prime interest because until you can actually talk to the database, there isn't much to do.

If it's a developer who may have done SQL coding, and had some SQL*Plus experience, then the questions are more of the order of, "How do I do the task I've been asked to do?". Then it's a discussion of how to create a user, a table, a discussion on grants and privileges, and some base information to get them started.

When it's an experienced DBA, the first questions generally are something like, "What's the Oracle account password, what's the SYS password, and where is the bathroom and the Coke machine?". That's the difference between beginners and experienced DBAs. Experienced DBA's have time to go to the bathroom and the Coke machine.

Question:What are the most common mistakes beginners make?

BNFD: Believing that there is a single answer to a given question. While there are standards that one should always use, more often than not, the answer to a given question is, it depends. Another common mistake is thinking you know everything after a while. Just when you think you know everything is the exact moment when you actually should realize that you don't know anything. Always rethink your way of looking at things, and check your reality against the thinking of other professionals in the field. Always ask yourself if you are being closed-minded about things. A DBA is paid to think, in my opinion, and that requires an open mind.

Question:What should the top five priorities be for new DBAs on the job?

BNFD: Be on fire. You have to want to learn everything, do everything, consume everything. So you got the DBA job, now is not the time to rest in your new chair and enjoy your success. List out your goals and what you want to learn. A five year plan is a great idea. Listen! Ask Questions! Be involved! Don't just sit back waiting for the create table requests. When it comes to theory, don't believe anything you hear or read until you have tried it yourself. Database rule number one, in my opinion, is that no rule of thumb applies all the time. If you are solely responsible for a database, make darned sure, before you leave your job on day one, that your database can be recovered. Nothing else matters if you can't get that database back. Document everything

Friday, September 21, 2007

Introducing Oracle for beginners

What is Oracle?
Its a RDBMS.Relational Database Management System.
Oracle - It is a Greek word meaning God- Who fulfils all the Dreams.
Oracle Inc is the world's largest supplier of relational databaseproducts (notably Oracle9i), which are used most of the Fortune 500companies and of course by all the leading E-business and Internetsites.
Oracle's relational database was the world's first to support theStructured Query Language (SQL), now an industry standard.Founder of Oracle Inc: CEO Lawrence J. Ellison and a few associatesformed Oracle in 1977.
What is a RDBMS?
A relational database is a collection of data items organized as a setof formally-described tables from which data can be accessed orreassembled in many different ways without having to reorganize thedatabase tables. The relational database was invented by E. F. Codd at IBM in 1970.The standard user and application program interface to a relationaldatabase is the structured query language (SQL). SQL statements are usedboth for interactive queries for information from a relational databaseand for gathering data for reports.
What Are Codd's Rules?
Dr. E. F. Codd's 12 rules for defining a fully relational database.
Note that based on these rules there is no fully relational databasemanagement system available today. In particular, rules 6, 9, 10, 11 and12 are difficult to satisfy.

  • Foundation Rule:A relational database management system must manage its stored datausing only its relational capabilities.
  • Information Rule All information in the database should be represented in one and onlyone way - as values in a table.
  • Guaranteed Access Rule Each and every datum (atomic value) is guaranteed to be logicallyaccessible by resorting to a combination of table name, primary keyvalue and column name.
  • Systematic Treatment of Null Values Null values (distinct from empty character string or a string of blankcharacters and distinct from zero or any other number) are supported inthe fully relational DBMS for representing missing information in asystematic way, independent of data type.
  • Dynamic On-line Catalog Based on the Relational Model The database description is represented at the logical level in the sameway as ordinary data, so authorized users can apply the same relationallanguage to its interrogation as they apply to regular data.
  • Comprehensive Data Sublanguage Rule A relational system may support several languages and various modes ofterminal use. However, there must be at least one language whosestatements are expressible, per some well-defined syntax, as characterstrings and whose ability to support all of the following iscomprehensible:data definitionview definitiondata manipulation (interactive and by program)integrity constraintsauthorizationtransaction boundaries (begin, commit, and rollback).
  • View Updating Rule All views that are theoretically updateable are also updateable by the system.
  • High-level Insert, Update, and Delete The capability of handling a base relation or a derived relation as asingle operand applies nor only to the retrieval of data but also to theinsertion, update, and deletion of data.
  • Physical Data Independence Application programs and terminal activities remain logically unimpairedwhenever any changes are made in either storage representation or accessmethods.
  • Integrity Independence Integrity constraints specific to a particular relational database mustbe definable in the relational data sublanguage and storable in thecatalog, not in the application programs.
  • Distribution Independence The data manipulation sublanguage of a relational DBMS must enableapplication programs and terminal activities to remain logicallyunimpaired whether and whenever data are physically centralized ordistributed.
  • Nonsubversion Rule If a relational system has or supports a low-level(single-record-at-a-time) language, that low-level language cannot beused to subvert or bypass the integrity rules or constraints expressedin the higher-level (multiple-records-at-a-time) relational language.

Thursday, September 20, 2007

Client/Desktop/Presentation Tier in Apps

In Oracle Applications 11i , Each user logs on to the oracle Applications through the E-Business Suie Home Page on a Desktop client web browser.The E-Business Suite Home Page provides a single point of access to HTML-based applications, Form-based applications and Business Intelleigence applications.
Q. Does OA registers preferences( Languages)?
Ans: O, Yes, OA retains preferences as you navigate through the system.If you have registered "German" as your preferred language, this preference carries over whether you access Forms-based or HTML-based applications.
DESKTOP TIER:
The forms Client Applet:
- Is a general-purpose presentaion applet.
- Supports all OA Form based products.
- The Forms client applet contain all Java classes required to run the presentation layer of OA forms.
Oracle JInitiator
The forms Client Applet must run with in a JVM on the desktop client.The Oracle JInitiator component allows use of the Oracle JVM on web clients, instead of browser's own JVM.
Q. What will happen if the Jinitiator is not installed in a M/C running Oracle application?
Ans: Traditionally Jinitiator was run as a part of standard Applications sign-on process but with the move to a mainly a HTML-based environment , Jinitiator is only invoked when a user chooses to access functions that require it( e.g. Forms).
If the Jinitiator is not installed , the Web browser will prompt the user to download the required installation executables.
Note:
The forms Client Applet and the used JAR files are downloaded from the web server at the beginning of the client's first session.Less commonly used JAR files are downloaded as needed.More importantly all downloaded JAR files are cached locally on the client, ready for future sessions. This eliminates network traffic that would be involved in downloading them.
Q.How do I check that the JAR files are downloaded at the beginning of the client's session?
Ans:Selecting "Show Console" on the "Basic" Jiitiator will allow you to observe downloading of the JAR files , to confirm that they are being downloaded when they should be.
VVIP: All updates to the JAR files are installed on the application tier and downloaded to the client automatically via the caching mechanism.

Some important FND Tables

Here is the list of few important tables.
Concurrent Manager
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Tuesday, September 11, 2007

Oracle 8i & 9i DBA certification

The Oracle Certification Program continues to be the most popular certification among all database vendors.Oracle has completely revamped the DBA Certification track with the release of the Oralce9i database. To best understand the modifications that the DBA track has undergone, it's best to first review the Oracle8i DBA track.
To become an Oracle8i Certified Professional (OCP) DBA, the certification candidate needed to pass five exams:
Introduction to Oracle: SQL and PL/SQL
Oracle8i: Architecture and Administration
Oracle8i: Backup and Recovery
Oracle8i: Performance Tuning
Oracle8i: Network Administration
With Oracle9i, the DBA Certification track is now divided into 2 primary tiers, as well as a third Master-level tier:
Oracle9i Certified Associate (OCA)
Oracle9i Certified Professional (OCP)
Oracle9i Certified Master (OCM)

Oracle9i Certified Associate (OCA) :

Oracle’s 9i OCA DBA (Database Administrator) certification is for “apprentice skill level” IT professionals who have have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. Associate (OCA) requires passing two exams:
Introduction to Oracle9i: SQL
Oracle9i Database: Fundamentals I

Oracle9i Certified Professional (OCP):
The OCP tier was designed for mid to senior-level DBAs with one or more years of focused Oracle DBA experience. The knowledge and skills obtained from completing this tier of the certification program prepare the DBA to provide the full assortment of DBA-related tasks...from Database Design and Development to production tasks such as Performance Tuning and Backup & Recovery.
The Oracle9i Certified Professional (OCP) requires passing the two exams in the OCA Tier, plus two additional exams:
Oracle9i Database: Fundamentals II
Oracle9i Database: Performance Tuning

Oracle9i Certified Master (OCM):
There is a third tier to the Oracle9i DBA Certification Track, the OCM. This track is targeted at very senior level DBAs and Oracle consultants.The OCM designation first requires that you obtain your Oracle9i OCP credential. Once this is secured, Oracle requires that you take two advanced Oracle courses from Oracle University. These are Instructor-led courses

Oracle9i: Program with PL/SQL
Oracle9i: Advanced PL/SQL
Oracle9i: SQL Tuning Workshop
Oracle9i: Database: Real Application Clusters
Oracle9i: Database: Data Guard Administration
Oracle9i: Database: Implement Partitioning
Oracle9i: Data Warehouse Administration
Oracle Net Services: Advanced Administration
Oracle9i: Advanced Replication
Oracle9i: Enterprise Manager
Once you've completed the course requirements, you can then register for and take the "practicum" exam. The practicum is a two-day, hands-on performance exam that requires the candidate to perform a number of tasks using Oracle9i on the Linux platform. Some of the skills that need to be proven include: database configuration, replication, partitioning, parallel operations, diagnostics, troubleshooting, performance tuning, and backup and recovery...all with "real-world" scenarios.