“Need to Know” for Oracle DBA Begineers

 There is more to being a DBA than knowing SQL.  The DBA works at the intersection of the database, server, operations group, and developers.  A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software issues. Below is a list of the ten topics I feel every entry level DBA should understand.  The list I created stems from my experiences working with databases as both a DBA, Developer, and Manager.   When looking to hire entry level DBA’s, the interview questions I ask are drawn from these areas. If you’re looking to start a job as DBA, then you’ll want to be familiar with these topics.

Backup and Restore

Any DBA worth their salt should know the DBMS’ (Database Management System’s) built-in methods to backup and restore data, such as using Oracle Recovery Manager, but in addition to these built-in utilities, it also makes sense to understand what third party offerings exist. Enterprise backup solutions are used in many larger IT shops.  Be familiar with products such as NetBackup or NetApp SnapManager. As a junior DBA it would be impressive if you knew these tools existed and that not all backups are created equal.  That is to say, just because you back up the database files, doesn’t mean get a good backup… in fact, you didn’t.

Basic optimizations

It is important to know when to recommend when an index should be created.  You should know some basic indexing strategies.  When are clustered indexes appropriate?  When should you use a covered index? Also know how your database optimizer works.  Does it rely on special table statistics?  How do you update those? Know what it means to reorganize tables and indexes.  When should they be reorganized, and what can you do to automate the process?

 

Developers

Software developers can make or break a database.  It is important that you can work with others to help them write efficient queries.  You need to help them understand that one call to a database is much more efficient than one thousand!  In most cases it is faster run one query that returns a 1,000 rows, than it is to call 1,000 queries that return one row each. As a DBA you should help them understand when it is better to perform processing on the DBMS rather than in code.  Pulling volumes of data across your network to perform a sum is most likely slower than writing a query with an aggregate function.

Storage Systems

In most databases the main bottle neck is disk access.  Understanding where your database is stored and how the DBMS accesses the physical data is important.  Is your database on local disk or a SAN (Storage Area Network)? If you company has a storage team, get to know them, and understand what tools they use to monitor IOPS (Input/output Operations per Second).

How to read a query plan

As a junior DBA you should know how to generate and read a basic query plan.  I wouldn’t expect you know fully understand all verbiage, but several key phrases, such as “Full Table Scan,” and “Nested Loops” should jump out as red flags. Also, you should understand, when the optimizer recommends a change be made, why that change would work, and what the trade-offs are in making it.  For instance, the optimizer may recommend that an index be created.  It this to compensate for poor programming?  Also, if you add it, could something else suffer, such as a process to insert or update data?

Knowledge of normalization

Normalized tables are the foundation of a well-designed relational database, yet they can also be its bane. A junior DBA should understand and know how to put data into 1st, 2nd, and 3rd normal form.  Why is normalization important and when can it become a liability? Knowing the difference between a Primary, Foreign, and Unique key is important.  Also is understanding and knowing how to enforce one-to-one, one-to-many relationships.

Knowledge of SQL

It might seem obvious, but a DBA should have a really good grasp of both SQL DML (Data Manipulation Language) and DDL (Data Definition Language). DML covers items such as Select, Update, Insert and Delete.  You should understand all the major clauses such as WHERE, GROUP BY, HAVING, and ORDER BY.  In addition you should be comfortable with sub queries and joins. DDL covers items such as CREATE TABLE and ALTER TABLE.  A junior DBA should understand how to create and modify tables and indexes and know the difference between deleting records, truncating a table, or dropping it! And… don’t forget views!

Operating System

As a DBA you need to be familiar with the OS (Operating System) your DBMS lives within.  You should understand nuances of your operating system, such as security settings, integration with Active Directory, LDAP (Lightweight Directory Access Protocol), and naming conventions.  Also, how is your DBMS started?  What scripts are used to start, shutdown, or lock users out of your database?

Scripting

To become efficient it is important to know when OS scripting, such as PowerScript, can help you manage your servers.  Consider having ten or more DB servers?  If you had to shut down all the DBMS on them, would you individually log in and manually shut them down or use a script? In my book, if you want to get an edge on other junior DBA’s you learn scripting.  This will only make you more efficient at your job.

Stored Procedures and Triggers

I made stored procedures and triggers a separate category since they’re more like programming than pumping out SQL statements.  Regardless, you should know when to use them and recommend to your developers their virtues versus writing SQL in code. Also, many third party applications ship with Stored Procedures and Triggers.  Being able to read them and understand their logic can go a long ways when trying to troubleshoot a performance issue. The sooner you can avoid treating them like black boxes, the quicker you’ll understand and become more comfortable with the databases you support.

Oracle Instance

An Oracle Instance
  • Every running Oracle database is associated with an Oracle instance.
  • Every time a database is started on a database server, Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes.
  • The combination of the SGA and the Oracle processes is called an Oracle database instance.
  • The memory and processes of an instance work to manage the database’s data efficiently and serve the one or multiple users of the associated database.
  1. Oracle starts an instance, then mounts a database to the instance.
  2. Multiple instances can execute concurrently on the same machine, each
    accessing its own physical database. 

    Single-Process Oracle Instance

    1. A single-user Oracle instance is a database system in which all Oracle code is executed by one process.
    2. All code of Oracle and the lone user’s database application is executed by a single process. 
      For example, Oracle running under the MS DOS operating system on a PC can only be accessed by a single user because MS DOS is not capable of running multiple processes.

      Multiple-Process Oracle Instance

Multi-user Oracle uses several processes to execute different parts   of oracle  and a separate process for each connected user.

  1. In a multiple-process system, processes can be categorized into two groups: user processes and Oracle processes.
  2. Each process in a multiple-process Oracle instance performs a specific job.
  3. By dividing the work of Oracle and database applications into several processes, multiple users and applications can simultaneously connect to a single database instance while the system maintains adequate performance.
  4. Most database systems are multi-user, because one of the primary benefits of a database is managing data needed by multiple users at the same time. 

GROW THAT DBA CAREER

Introduction:

Over the years, I’ve spent lots of time on various newsgroups and talking with Information Technology professionals who want to know how to get a job as a Database Administrator (DBA), or how to grow as a DBA, now that they have the job. Many individuals have offered many different pieces of advice over the years on how to accomplish these goals. This paper is a culmination of those ideas and can give some suggestions on how to land a rewarding and exciting job as a DBA. This paper will also give ideas on how to grow as a DBA to become even more valuable to your employer. If you already have a job as a DBA, you might wish to skip the first few sections of this paper.

Should I Become a DBA?

One of the questions I’ve been asked to answer is should a person pursue a job as a DBA. This is not an easy question since it depends on the individual. Some individuals have the talent that is required to become a good DBA. Other individuals do not find the duties required of a DBA to be particularly easy to grasp. The DBA position does require that the individual hold a certain skill set. And there are demands on this individual that other IT positions do not necessarily require. So to answer this question, I often give the prospective DBA candidate a picture of what is required of the DBA position. In the paragraphs that follow, I end each paragraph with a question. Please take time to think about and answer the question before proceeding to the next paragraph.

Many people aspire to become a DBA for many reasons. One of the prime reasons is pay. DBA’s are some of the highest paid professionals in the IT industry. Others aspire to become a DBA due to the glory that is often lauded on the DBA, or because it looks cool! I personally find being a DBA to be very rewarding. It is a fun, exciting career. What are your reasons for looking at the DBA position as a possible career choice?

Unless you are prepared for what lies ahead, you may find that a job as a DBA can be full of frustration and headaches. A database sits between the operating system and the end user application. As such, the DBA must be well versed in the OS platform their database runs on. The DBA doesn’t necessarily need to know everything about the OS, but the more he or she knows, the better. The database interacts very closely with the OS. Understanding this interaction is essential. The DBA also needs to know about server hardware and how it impacts or helps the database. The DBA must also understand software applications. The DBA may be asked to help developers create sound, robust database applications. And above all us, and most obviously, the DBA must thoroughly understand the database engine, how it works, how all pieces of the engine fit together, and how to leverage the power of the database engine to deliver data to the end user and the application. The best DBAs I’ve seen in the industry have a very solid understanding of more than just the database itself. They know a thing or two about System Administration and Application Development. Many times, they come from one or both backgrounds before becoming a DBA. No matter what, there is a lot of ground to cover to become an effective, if not excellent DBA. Are you ready to start learning until you feel that you can’t learn anymore?

Most of the people that I talk to who have difficulties starting out in their DBA career, really have an issue trying to absorb the mountainous volumes of information that a DBA needs to know. After all, System Administrators make a whole career out of learning the ins and outs of the OS. Application Developers make a whole career out of learning how to build and code excellent applications. Not only does a DBA have to know a great deal about these two different jobs, but then the DBA needs to spend even more time working on understanding the architecture of the database, and understanding how every piece of everything fits together! Does it sound too daunting of a task? There have been many who think so and after becoming frustrated, have left their DBA job for a completely different job. Then there are those individuals who thrive on disseminating and understanding all of this information, and using that information to make good, sound technical decisions. As I was fond of saying in my early days as a DBA, it all seems to me to be one great puzzle. The challenge is in getting all of the pieces to fit. Which type of person are you?

Many DBAs are “on-call”. They get called at all hours of the day and night to resolve critical problems with their database. The database is the life-blood of the business’s IT infrastructure. Without data, there would be no need to have a computer system. It is the data that drives the business. Where would amazon.com be if their web site couldn’t search the database for products and if no one could place an order for their products? They wouldn’t be in business for very long. There are many companies that lose significant revenues when their database is down, even for the shortest period of time. For this reason, the DBA needs to be available to resolve issues as fast as possible, should they occur. Many shops have a team of DBAs who rotate being on-call. These DBAs support databases for 24×7 applications. Are you ready to be placed on-call if the job requires it?

Some of the DBAs duties include applying patches to software or making database changes. Often times, these changes cannot be done while the company’s employees are at work, expecting that the database be up and running so that they can get their jobs done. This means that the DBA frequently has to come in real early in the morning, or real late at night, or even on the weekends to perform work that can only be done outside of normal business hours. Are you ready to work some strange hours at times, or are you looking for a 9 to 5 job?

One key asset for a DBA to hold is what is commonly referred to as “soft skills”. The DBA needs to be able to work well in a team environment, commonly in diverse teams with System Administrators, Network Administrators, Application Developers, Project Managers and others. DBAs need to be able to explain difficult, technical concepts in plain English that others in the team environment can understand. DBAs need to be able to direct team members on database-related issues. How are your soft skills?

While not an all-inclusive list, typical DBAs perform the following duties:

  • Monitor database instances on a daily basis to ensure availability. Resolve unavailability issues.
  • Collect system statistics and performance data for trending and configuration analysis.
  • Configure and tune dB instances for optimal performance under application specific guidelines.
  • Analyze and administer dB security. Control and monitor user access to dB. Audit database usage when necessary.
  • Monitor backup procedures. Provide recovery when needed. Develop and test backup and recovery procedures.
  • Upgrade RDBMS software and apply patches when needed. Upgrade or migrate database instances as necessary.
  • Support application developers with any and all dB related activities.
  • Keep up with dB trends & technologies. Use new technologies when applicable. Install, test, and evaluate new Oracle related products.
  • Perform storage and physical design. Balance design issues to achieve optimal performance.
  • Create, configure and design new dB instances.
  • Diagnose, troubleshoot and resolve any dB related problems. Work with Oracle Support if necessary to bring problems to a successful resolution.
  • Ensure Oracle networking software (SQL*Net, Net8, Names, OiD) is configured and running properly.
  • Work with System Administrators (Unix & NT) to ensure Oracle related matters are handled properly.
  • Create any necessary scripts for effective and occasionally periodic dB maintenance activities.

Each of the questions in the above paragraphs are designed to get you thinking about some of what a DBA has to deal with, to help you decide if this is the career for you. I do not mean any of this to stop you from becoming a DBA if that is your goal. I am just trying to inject some reality into the picture. I’ve seen a few DBAs become frustrated once the reality of the job hits them. And they’ve spent time, effort, and sometimes money, to obtain their first job as a DBA. Personally, I find this career to be very rewarding. And I can’t imagine doing anything else right now. So hopefully, this section has helped you decide if this is something that you wish to pursue. If it is, then go for it with all you have!

How Do I Get That First DBA Job?

So you’ve read the section above and decided that becoming a DBA is a good career move for you. Congratulations! I hope that your career turns out to be as exciting and rewarding as you want it to be. So how do you go about getting that first DBA job? I hear people ask this question, time and time again.

During the early 1990’s, the Internet was booming. Internet startup companies sprouted like weeds. Existing companies jumped on the bandwagon as well and started creating their own web presence. And almost every single one of these companies had a web site that required a database as a backend to their web application. Unfortunately, there weren’t that many DBAs in the field at that time. There became a great shortage of DBAs in the IT industry. During that time, it seemed that all you needed to land a DBA job was the ability to spell “Oracle” or maybe just one semester’s exposure to a database system in college. And to make life even better, the shortage of DBAs in the field prompted companies to throw very nice salary offers at potential candidates. If you wanted to become a DBA, it was easy; too easy. All you had to do was demonstrate that you knew what a database was and job offers came in faster than you could handle.

Then the Internet bubble burst. Tons of Internet startup companies went out of business. A lot of DBAs who worked for the Internet startups found themselves looking for jobs. A company with a DBA opening could find a candidate with DBA experience much more easily than before. Life hasn’t been any easier in the early part of the 21st century (at least in the United States) now that the economy isn’t very strong. Companies are tightening their budgets. All of this translates into less opportunities and job openings for the DBA candidate.

One of the hardest parts to landing that first DBA job is that every position opening requires some experience. If you look at it from the company’s perspective, you can understand why experience is a must for the DBA position. Would you pay someone a high salary, to operate, maintain, and run one of the biggest, most important pieces of your IT infrastructure if they didn’t have any experience? Could your company afford to struggle with an inexperienced DBA and potentially lose millions of dollars in revenue while you wait for this DBA to get past their learning curve? For most companies, the answer to these questions is a resounding ‘NO’. So without experience, it becomes rather difficult to obtain your first DBA job.

The first DBA job now becomes a “Catch 22” situation. How do I get a job as a DBA if I don’t have any experience? How can I get experience as a DBA if I don’t have a job? This is the hardest obstacle to overcome. The hardest part will be landing that first DBA job. Hopefully, the rest of this section will give you some ideas towards realizing the goal of your first DBA position.

Tip #1: Become educated. – Learn as much as you can you can about a database. This will most likely involve some time and effort on your part, outside of normal working hours. Take a database class at a local college or university. Many training companies offer classes on Database Administration. You may find that you have to pay for these yourself if your employer will not fund your education opportunities. Many DBA positions require at least a Bachelor’s degree in Computer Science or a related field, so you should have at least that credential.

Tip #2: Practice being a DBA. – Many database vendors let you download trial, test, or evaluation copies of their database system. Download a copy and install the software on your own personal computer. Play with the database. Intentionally break the database and try to fix it. Try to perform as many of the DBA functions as you can think of. Test out and hone your skills on your own test platform so that you can be able to demonstrate some level of database administration ability.

Tip #3: Get certified – Many database vendors now offer a certification for their database product. Many companies now look at certification as a measuring stick. One thing to keep in mind is that just being certified is not enough. Passing DBA certification tests do not automatically mean that you know how to administer a database. They just say to the potential employer that you now possess a certain set of skills. Being certified also tells a potential employer that you are serious about your pursuit of a DBA job. I’ve seen many people complain that they are certified with no experience, but still can’t get that first DBA job. Certification alone won’t land you the job, but it doesn’t hurt either. If nothing else, you’ve learned a great deal while trying to get certified. Just don’t rely on the certification to get you that job you are looking for. You will need more than that. But it will help in the end.

Tip #4: Leverage your existing skill set – Many DBAs come from a System Administrator background. Others come from an Application Development background. If possible, see if you can use your existing skill set to get a job. The goal here is to make it a win-win situation for you and your employer. For instance, let’s assume that you are already a SysAdmin looking to break into the DBA field. Maybe you can find a job at a company that will be able to use your SysAdmin skills part of the time, while being able to get your feet wet in Database Administration the rest of the time. If you are already a DBA on one vendor’s platform but wish to move to another vendor’s platform, see if you can land a job which has both platforms. For instance, use your SQL Server DBA skills in a shop that also lets you backup the Oracle DBA. In this way, both the company and you get what you want. After you’ve had exposure to DBA work, you can try to get a position that will let you do it full time, maybe even with the same company.

Tip #5: Take advantage of current opportunities – Sometimes, one gets into the DBA field just by being in the right place at the right time. If your current employer has an opportunity for you to work on any database project, jump at the chance! Any database experience is worth more than no database experience. Let your management know that you are actively seeking any database opportunities that come by. Hopefully, they will think of you when the next one comes along. After working on these database projects and seeing the desire in your eyes to become a DBA, they may decide to train you, and promote you. Many, many people get their first DBA job in exactly this manner, sliding into a Junior DBA position once they have worked on a few database-related projects. Often times, when a DBA leaves the company, that company will look at hiring an internal candidate if they feel that candidate is trainable.

Tip #6: Look for that Junior DBA position – If you look at DBA position opening descriptions, some will say that they are looking for a Senior DBA and others, just for a Junior DBA. So let’s be serious here. You don’ t have the experience for a senior-level position. And we’ve already discussed why companies won’t consider you for such a position. But they may consider you for a junior-level position. Junior DBAs work under the guidance of a Senior DBA. They are learning the ropes. Typically, the Senior DBA assumes the responsibility for the databases, while getting all the glory too! But don’t worry. As your career grows, you will get more and more responsibilities and more and more credit when appropriate. Since you don’t have any experience, start your climb up the ladder here.

That being said, I have heard of some companies looking for a Senior DBA, but in the end, they really want to hire a Junior DBA. You may wish to apply for these positions even though you may not be qualified. They just might decide to hire you anyway. But be upfront and express that you are still learning the ropes and are really only Junior DBA material. Don’t try to snow them into thinking that you are Senior DBA material. That will only hurt your chances of landing the job.

While no guarantee, these tips can help you land that first DBA job. Good luck and happy job-hunting! After you’ve received that first job as a DBA, proceed to the next section to learn where to go next.

I Just Got My First Job! Now What?

Congratulations! You are now an official member of the DBA club! Are you ready for that exciting career that you’ve always dreamed about? Your job has just begun and already you are way behind that learning curve. You will find that there is an enormous amount of material that you must learn to become an effect Database Administrator. Your first year or two will be spent learning more than you may have ever learned in your career. If you find that the amount of information is leading to brain overload, just sit back, take a breather, and come back to it. To help you along the way, you can follow the roadmap below:

Route #1: Relational database theory – For this paper, I’m going to assume that the type of database you will be administering is a “relational” database. Other database models do exist, but the relational model is the dominant one in the industry for the last twenty years. If your database system follows a different model, then learn that theory. Relational database theory is very important. It is the background upon which everything has been built. I’ve seen many people who make the jump to database administration and never bother to learn solid relational database theory. Inevitably, their lack of a solid basis in this theory shows up as a shortcoming many times during their career. If you understand relational database theory well, then you will be able to make smoother transitions to any vendor’s Relational Database Management System (RDBMS). It doesn’t matter if I am using Oracle’s database, or IBM’s DB2, or Microsoft’s SQL Server. All of them are relational database systems. They all do basically the same things. The difference lies in how they do the same things. A solid relational database theory is not essential for a Junior DBA position. But it is vital if you ever want to grow your career past the Junior DBA level. Many college-level textbooks cover relational database theory very well. One of the most widely used textbooks is Fundamentals of Database Systems by Elmasri and Navathe on Bejamin/Cummings Press.

Route #2: Learn the query language inside and out – Databases all have a language that lets you get data from the database, put data into the database, and modify the data that is in the database. For relational databases, that language is Structured Query Language (SQL). This one language is your tool to interface with the database. It is vital that this tool not be a barrier to further learning. In your test database, practice various SQL statements until they become second nature to you. A great book on this subject is Oracle 9i The Complete Reference by Loney and Koch on Oracle Press. Every Oracle DBA should read this book early in their career. The Oracle 9i SQL Reference manual is another great source of information. You can access all of the Oracle documentation online at their TechNet (http://technet.oracle.com) web site. You will have to register for an account, but it is free. Every Oracle DBA should have an account on TechNet.

Route #3: Begin learning basic database administration functions – Isn’t this why you are here in the first place? So why is it third on the list? We are trying to build a pyramid of knowledge and I feel strongly that one needs to know relational database theory and SQL real well as they will become tools that you will use as you learn how to perform basic database administration functions. These functions can include starting and stopping a database, backing up and recovering a database, and creating/dropping/altering database objects. For Oracle database administration, there is a great book on the market that gives you a good taste of what to expect. This book is Oracle 9i DBA Handbook by Loney on Oracle Press. Most DBAs that I know have read this book more than once, early in their careers. At this time, you should also be reading and understanding the Oracle 9i Concepts Guide, the Oracle 9i Administrator’s Guide, and the Oracle 9i Backup and Recovery Guide, all from the Oracle documentation.

Route #4: Read, read, and read – Since you just started your career as a DBA, you are just beginning to build a skill set. It takes a long time to build, absorb, and comprehend all of the information you will be learning. Undoubtedly, your Senior DBA will have work to do, so he or she will not always be able to devote a ton of time to your studies. You will have to learn many things on your own. This is where reading comes in. There are many books on the market, which answer a lot of database related topics. Oracle Press is Oracle Corp’s official publishing company with a large number of Oracle-related books. There are other publishing companies as well, like Wrox Press and O’Reilly Press. You also have the Oracle documentation to read. And there are numerous web sites and newsgroups available as well. Read as much as you can get your hands on. And it’s not a bad idea to read these items more than once to absorb things you may have missed the first time.

Route #5: Create test cases – I often see beginner questions that ask the most basic questions that can easily be answered if the person just took the time to figure it out themselves. Undoubtedly, you will have many questions as you begin your Oracle studies. Decide if these are questions that you can answer yourself. For instance, I once had someone ask me if it was possible to insert NULL values into a column with a UNIQUE constraint. At first, this may not seem to be an easy question to answer. But it is really easy to test! Just create a simple table. On one of your columns, enable a UNIQUE constraint. Try to insert NULL values into that column. Does it work? You should be able to answer this question quite easily. So why create these test cases? One reason is that by doing so, you will be enhancing your problem solving skills. The same skills required to create these test cases are some of the same skills used in problem solving. Problem solving skills will greatly help your DBA career. Another reason is that you will often need to create more complex test cases as your career progresses in order to guarantee database and application success. Even simple test cases are building blocks for more complex database and application analysis in the future.

Route #6: Find a mentor – A mentor can be used to guide, or steer your DBA career (or any career for that matter). They can give pointers, answer questions, and help save some time as you grow your DBA career. Hopefully, this paper will serve as a mentor towards part of growing your career. If you are working in an environment with a Senior DBA, then that person should be responsible for mentoring a good portion of your career. You may choose to look at other mentors as well.

Route #7: Participate in local user groups – Many cities across the nation have local user groups which meet periodically to talk about database-related topics. Join one of these local user groups if possible. This gives you a great way to interact and network with others in your field.

How Do I Go From a Beginner DBA to an Intermediate DBA?

So you’ve been a DBA for a while and you now wish to take your skills up a notch? Where do you turn to next? First, go back to the previous section and make sure that you have completed all of the routes. It is vital that you know the SQL language inside and out. It is vital that you know relational database theory and that you understand the basic database administration tasks. And by now, you should have been reading the documentation and other texts until you are blue in the face. If not, then you are not ready to proceed down the next road, to increasing your skills as a DBA. If you are ready to proceed, then I’ve got a few routes for you to take to increase your skill set.

Route #1: Learn the OS and your server hardware – As I said before, the database sits on top of the operating system and the server hardware. It is essential that you understand how these pieces work. You should understand how to interact with the particular OS. How do you remove or edit files? If your OS is Unix, you should have a grasp of the command line and how Unix commands help you do your job. The same is to be said if you are running Windows or any other OS. You need to have an understanding of server hardware as well. What is the difference between physical and virtual memory? What is RAID and how do the different levels affect things? Why do databases like more physical disks, rather than one large disk volume? You need to know these things so that you can intelligently talk to your System Administrators on how to best configure your server to sufficiently support your database.

Route #2: Learn application design as it relates to databases – As I said before, the database sits between the OS and the database application. You really need to know both ends. How does the SQL language help create good applications? What are bind variables and why are they important? Tom Kyte has written an excellent book that gives great advice on Oracle application design. His Expert One-on-one Oracle book can be found on Wrox Press. I highly suggest reading his book. He talks at length about the things that can make or break an Oracle application. You need to know these things because your application developers are looking to you for guidance and database knowledge. Learn anything you can about application design. It may be beneficial to take a college class in software engineering, operating systems, or data structures.

Route #3: Learn the Oracle architecture – It is important that you fully understand how Oracle works. Without an intimate knowledge of the Oracle architecture, you will not be able to do much work as a Senior DBA. It is this knowledge that is the basis for fixing a broken database. You need to understand how all of the pieces work together.

Route #4: Become certified – It may not be required of your job, but becoming certified will definitely help you. Every day as a DBA, you have been learning new and exciting things. Maybe at this point in your career, you’ve even had a couple of days where you haven’t learned anything new. But you still have a lot to learn. Becoming an Oracle Certified Professional DBA requires you to ensure that you have learned the basics about all areas of database administration. I found that in the course of studying for the OCP exams, I learned areas that my job never would have exposed me to. And once I learned about a particular topic that I had never seen before, I was able to use that knowledge at a later date to solve a problem. If I didn’t study for the OCP exams, I never would have known about that particular solution to the problem. This has happened to me time and time again. Some people may say that certification really isn’t worth it. I say that it doesn’t hurt you and it only helps you. So get certified!

Route #5: Develop a library of resources – In the previous section, I indicated that every DBA should have an account on Technet. This is one of your main resources. But there are many more sources as well. Many people share their Oracle knowledge. If you haven’t started it already, you should be developing quite a bookmark collection in your web browser of Oracle resources.

There are many other very good web sites as well.

Route #6: Begin interacting with various newsgroups and forums – You may have already stumbled on them, but if you haven’t now is the time to start. There are many newsgroups and forums out there designed to answer any Oracle questions that you have. There are many wonderful people out there in the Oracle community willing to share their information with you. All you have to do is ask. The following is a nice list to Internet communities to begin interaction with:

It is a good idea to see how other people are experiencing the trials and tribulations that you are. If you have questions, feel free to make a posting to the groups. If you do make a posting, always include information such as your Oracle version and the platform that you run Oracle on. These can make a big difference in the answer you will receive. If you forget, someone will remind you! Even if you don’t ask questions, you will probably learn a lot just by reading others answers. I can’t tell you the number of times I’ve been able to solve a problem because I remembered someone else asking the about the same issue on a newsgroup.

How Do I Go From an Intermediate to a Senior DBA?

So you’ve been plugging away as a DBA for quite some time now. You feel that you are ready to take the next step. What do you need to learn to become a Senior DBA? The following roadmap can help you along the way.

Route #1: Read all of the documentation – The Oracle documentation is not always the easiest thing to read. Many times, you bounce from document to document just to make sense out of the whole thing. If the documentation were the best thing around, then there wouldn’t be a market for all of those Oracle books that you’ve got sitting on your bookshelf. But the documentation does contain information that is not found anywhere else. For instance, nowhere else will you find details of every single INIT.ORA parameter or V$ view. Books may make reference to a few of them, but the Oracle docs talk about all of them. I have yet to meet a very good Senior DBA who has not read the Oracle docs from top to bottom. This isn’t a coincidence. The Oracle docs are a must read. And when a new Oracle version hits the market, you will have to reread the docs again. You may have read the Oracle Concepts Guide twelve times by now. But when Oracle 10i is released, you will have to read it again. Any new concepts for the 10i release will be noted in the docs. If you truly want to get to the next level, read the docs. There is no escaping it.

Route #2: Become an expert – The Oracle database is a very complex beast. To get to the next level, you will need to master many components of the product. Start with backup and recovery. Become a backup and recovery expert. You do this by intentionally breaking the database and seeing how to recover it. Try to break the database in every possible way and see if recovery is possible. You will soon be able to know backup and recovery principles inside and out. After you have become an expert in backup and recovery, become an expert in another area. You will have endless subjects from which to choose to master. Keep this process up for your entire career. But keep in mind, no matter how much of an expert you become in a certain area, someone, somewhere knows more than you do. Don’t take this personally. Just try to learn as much as possible from that individual.

Route #3: Actively participate in newsgroups, forums, and user groups – Previously, I mentioned how the various newsgroups and forums are a great place to learn new things. Now is the time for you to take the next step and answer any questions that you can. You’ll be amazed at how much you learn in this process!

Route #4: Write white papers and present them – This is similar to the route mentioned above. First, it is important to share the information that you have learned. If your career has made it this far, then it is mostly likely due, in some part, on the contributions of others. So now is your time to contribute to the next person. Second, an amazing thing happens when you attempt to share your information. That information undergoes an amazing process in your brain when you attempt to formulate it into a clear, concise topic that others can use. This process solidifies the information for you that is not done with other methods. So sharing that information in white papers, at conferences, and in newsgroups and forums is a great way to learn and take your abilities to the next level. Along the way, you have to do two things. One, recognize that you will make mistakes. Others will be happy to point out those mistakes, sometimes in a manner that is not very nice. Don’t try to hide from your mistakes. Own up to them and learn from them. Two, learn to say that you don’t know the answer instead of trying to bluff your way through it. People will know when you are trying to pull the proverbial wool over their eyes. Simply tell them that you are unsure of the answer at this time, and you will get back to them when you know the answer. If you keep these two points in mind, your integrity will be intact and you will grow as an IT professional.

Route #5: Become an expert in Oracle troubleshooting – The Senior DBA is often the person who is looked towards to solve those complex Oracle issues. You will have to use all the skills at your disposal, which you have been building your entire career, to solve many of these issues. Everything I’ve mentioned above will be used to troubleshoot problems; documentation, books, newsgroups, test cases, and other DBAs will all become resources to assist you in solving problems.

Route #6: Become an expert in performance tuning – The Senior DBA is often the person who is looked towards to tune database and application performance. If you are the Senior DBA and you cannot analyze performance bottlenecks, then your company will look elsewhere for these services.

Route #7: Become an expert in capacity planning – The Senior DBA is often the person who is looked towards to plan for database capacity in terms of data growth and transaction growth. The Senior DBA needs to be able to spot capacity bottlenecks in the system before they severely impact application performance. For instance, the DBA should know that more disk space needs to be purchased long before the database runs out of available disk space. Not keeping an eye on capacity planning can lead to a downed production database.

Route #8: Keep an eye on newer technologies – The Senior DBA should have a good idea what is going on in the IT community as it relates to database technologies. Are there technologies available now that can help the database? For instance, learn the advantages and disadvantages of Storage Area Networks and how they apply to database system. Are there technologies that will be available in the near future, which can help us? For instance, at the time of writing this paper, Linux operating systems are becoming more and more popular. What does Linux have to offer you for your database OS platform? Will it work for your organization?

Conclusion

I hope that this paper has given you ideas on how to go through all the stages of your career, from landing the first job, to progressing from Junior DBA up to Senior DBA. Use this paper no matter which stage you are currently sitting at as your progress and grow your DBA career.

p.s: The above article was taken from an oracle based site

RMAN BASICS

RMAN is not “too” different from WinZip. Since everybody knows WinZip, it will probably be easier to understand RMAN. Just like compressing a folder using WinZip, RMAN creates a compressed backup of the physical database files, including controlfiles, datafiles, archived logs and stores them somewhere. This somewhere can be a disk or a tape.

Therefore, the first important point to remember is:

“RMAN creates compressed backups of the physical database; if you prefer, RMAN zips the database, the way you like it”.

The usual definition for RMAN is given as,

Recovery Manager is a client/server application that uses database server sessions to perform backup and recovery. It stores metadata about its operations in the control file of the target database and, optionally, in a recovery catalog schema in an Oracle database.

Why Should we use RMAN

Ability to perform incremental backups.Ability to recover one block of a datafile.
Ability to perform the backup and restore with parallelization.
Ability to automatically delete archived redo logs after they are backed up.
Ability to automatically backup the control file and the SPFILE.
Ability to restart a failed backup without having to start from the beginning.
Ability to verify the integrity of the backup.
Ability to test the restore process without having to actually perform the restore.
Comparison of RMAN Automated and User-Managed Procedures
By using operating system commands for User-Managed Backup and Recovery , a DBA manually keeps track of all database files and backups. But RMAN performs these same tasks automatically.

Understanding the RMAN Architecture
An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired even through client side, TARGET DATABASE (This is the database which needs to be backed up) and RECOVERY CATALOG (Recovery catalog is optional otherwise backup details are stored in target database controlfile .)

About the RMAN Repository

The RMAN repository is a set of metadata that RMAN uses to store information about the target database and its backup and recovery operations. RMAN stores information about:

Backup sets and pieces
Image copies (including archived redo logs)
Proxy copies
The target database schema
Persistent configuration settings
If you start RMAN without specifying either CATALOG or NOCATALOG on the command line, then RMAN makes no connection to a repository. If you run a command that requires the repository, and if no CONNECT CATALOG command has been issued yet, then RMAN automatically connects in the default NOCATALOG mode. After that point, the CONNECT CATALOG command is not valid in the session.

Types of Database Connections

You can connect to the following types of databases.
Target database
RMAN connects you to the target database with the SYSDBA privilege. If you do not have this privilege, then the connection fails.

Recovery catalog database

This database is optional: you can also use RMAN with the default NOCATALOG option.

Auxiliary database

You can connect to a standby database, duplicate database, or auxiliary instance (standby instance or tablespace point-in-time recovery instance
Note:
That a SYSDBA privilege is not required when connecting to the recovery catalog. The only requirement is that the RECOVERY_CATALOG_OWNER role be granted to the schema owner.

Using Basic RMAN Commands

After you have learned how to connect to a target database, you can immediately begin performing backup and recovery operations. Use the examples in this section to go through a basic backup and restore scenario using a test database. These examples assume the following:

The test database is in ARCHIVELOG mode.
You are running in the default NOCATALOG mode.
The RMAN executable is running on the same host as the test database.

Connecting to the Target Database

rman TARGET /

If the database is already mounted or open, then RMAN displays output similar to the following:

Recovery Manager: Release 9.2.0.0.0

connected to target database: RMAN (DBID=1237603294)

Reporting the Current Schema of the Target Database
In this example, you generate a report describing the target datafiles. Run the report schema command as follows:

RMAN> REPORT SCHEMA; (RMAN displays the datafiles currently in the target database.

Backing Up the Database

In this task, you back up the database to the default disk location. Because you do not specify the format parameter in this example, RMAN assigns the backup a unique filename.

You can make two basic types of backups: full and incremental.

Making a Full Backup

Run the backup command at the RMAN prompt as follows to make a full backup of the datafiles, control file, and current server parameter file (if the instance is started with a server parameter file) to the default device type:
RMAN> BACKUP DATABASE;

Making an Incremental Backup

Incremental backups are a convenient way to conserve storage space because they back up only database blocks that have changed. RMAN compares the current datafiles to a base backup, also called a level 0 backup, to determine which blocks to back up.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backing Up Archived Logs
Typically, database administrators back up archived logs on disk to a third-party storage medium such as tape. You can also back up archived logs to disk. In either case, you can delete the input logs automatically after the backup completes.To back up all archived logs and delete the input logs (from the primary archiving destination only), run the backup command at the RMAN prompt as follows:

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

Listing Backups and Copies

To list the backup sets and image copies that you have created, run the list command as follows:

RMAN> LIST BACKUP;

To list image copies, run the following command:

RMAN> LIST COPY;

LOG_ARCHIVE_FORMAT in Oracle

If you have enabled archive log mode in your database then LOG_ARCHIVE_FORMAT parameter will come into role. If your database is in archivelog mode then redo log files will be archived and the parameter LOG_ARCHIVE_FORMAT determines the name of the archived log files.

LOG_ARCHIVE_FORMAT uses a text string and variables to specify the format of the archived files.

The following variables can be used with the LOG_ARCHIVE_FORMAT

1) %s : log sequence number

2) %S : log sequence number, zero filled

3) %t : thread number

4) %T : thread number, zero filled

5) %a : activation ID

6) %d : database ID

7) %r : resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros.

Following is an example of how we can set LOG_ARCHIVE_FORMAT in a database.

SQL> ALTER SYSTEM SET log_archive_format=’VSPRODP_%s_%t_%r.arch’ SCOPE=spfile;

System altered.

Note that, neither LOG_ARCHIVE_DEST nor LOG_ARCHIVE_FORMAT have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.

For example, we are setting the following values to log_archive_dest and log_archive_format parameters.

SQL> alter system set log_archive_dest='E:oracle';

System altered.

SQL> alter system set log_archive_format='arju_%s_%t_%r.arch' scope=spfile;

System altered.

SQL> col name for a30
SQL> col value for a30
SQL> select name, value from v$spparameter where name in ('log_archive_dest','log_archive_format');

NAME                           VALUE
------------------------------ ------------------------------
log_archive_dest               E:oracle
log_archive_format             arju_%s_%t_%r.arch 

If we do above settings all our archive log files will go into directory E:oracle and format will be arju_%s_%t_%r.arch.

Note that, in the LOG_ARCHIVE_FORMAT %s, %t and %r are mandatory variables. If we dont specify anyone of them it while starting up oracle it will throw erro

How to Notify or send email Event in Oracle from EM

In this post I will show about how e-mail can be send from oracle if any critical events happen.
With Enterprise Manager the task is very simple. With this you can easily send email if any oracle related problem happens. Like Tablespace full, Any oracle error, listener issue, performance problem etc.

Step 1:
At first step you need to setup sender mail address, your SMTP server address and sender identifier.

i)On the Enterprise Manager Home page click setup link which is on the upper most right corner.
ii)…./em/console/admin/rep/userAdmin window appears. On the Setup tab click on the Notification Methods.
iii)The several boxes appears.
Outgoing Mail (SMTP) Server: Here post your SMTP server address. On my system I gave 192.168.1.1.

Identify Sender As: This is the identity of sender. I gave in this box Arju.

Sender’s E-mail Address: This is thrid box. Define from whom the mail will be sent. I gave here prothoma@….com

iv)On the right side click on Test Mail Servers button A new window will come. If it display message similar like
Test Results
192.168.1.1: Test succeeded – You will also need to verify that a test e-mail has been received by prothoma@ya….com
Then this step is correct.

Step2:
In this step you will assign the mail address of the users to whom notification will be sent. To do this,
i)On the Enterprise Manager page click on preferences link which is on the upper most right corner.

ii)Under general tab type SYS password and confirmed password. Then under E-mail Addresses menu click add another row button and specify the email address to whom notification will be send.

ii)Then click on Test. A new window will appear displaying messge
…..@….com: Test succeeded – You will also need to verify that a test e-mail has been received
Now check the mail address to see whether actually mail is sent or not.

If you got mail to …..@….com address from prothoma@ya….com then you have successfully configured notification.

Step3:
i)On the Enterprise Manager page click on preferences link which is on the upper most right corner.
ii)You then specify rules and schedules in of the notifications.
iii)Click on rules and then select Listener Availability or Host Availability and Critical States or Database Availability and Critical States and then click assign methods button and check the box send me e-mail and click ok.

iv)Notification Schedule
Next, you will need to define your notification schedule. EM will NOT send you email notifications
if you do not have a schedule defined.
A notification schedule is used to represent your on-call schedule. It tells EM two things:
(a) the day and time you should be contacted and
(b) the email addresses to be used during those times.
Any time slot that is left empty in the schedule means that EM should NOT send you email
notifications, even though alert may occur during that time.
It is important to note that the schedule you specify will automatically repeat.

In a nutshell I can say “Setting up email notifications for alerts” needs following steps.

Step I. Setup the mail servers
Step II. Setup EM user accounts for your administrators
Step III. Each EM user should define their own notification settings
a) E-mail addresses -From Preferences
b) Notification Schedule
Step IV. Define and subscribe to Notification Rules

Lost Oracle SYS and SYSTEM password?‎

Method 1: SQLPLUS (Tested on Windows Oracle 9.2.0.1.0)‎
——————————————————————————————————–
‎C:sqlplus “/ as sysdba”‎
SQL> show user
USER is “SYS”‎
SQL> passw system
Changing password for system
New password:‎
Retype new password:‎
Password changed
SQL> quit

Next, we need to change the password of SYS:‎
C:sqlplus system/oracle
SQL> passw sys
Changing password for sys
New password:‎
Retype new password:‎
Password changed
SQL> quit

You should now be able to log on the SYS and SYSTEM users, with the ‎passwords you just typed in.‎

Method 2: Creating pwd file (Tested on Windows Oracle 9.2.0.1.0)‎
——————————————————————————————————-
‎1.‎ Stop the Oracle service of the instance you want to change the passwords ‎of.‎
‎2.‎ Find the PWD###.ora file for this instance, this is usuallly located at ‎C:oracleora81database, where ### is the SID of your database.‎
‎3.‎ Rename the PWD###.ora file to PWD###.ora.bak for obvious safety ‎reasons.‎
‎4.‎ Create a new pwd file by issuing the command:
Orapwd file=C:oracleora81databasePWD###.ora password=XXXXX

Where ### is the SID and XXXXX is the password you would like to use for ‎the SYS and INTERNAL accounts.‎
5.‎ Start the Oracle service for the instance you just fixed. You should be able to ‎get in with the SYS user and change other passwords from there.‎

Login with /@### as sysdba and try this command.‎
SQL> ALTER USER SYS IDENTIFIED BY ORACLE.‎