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

Oracle Database Auditing

There are two distinct type of auditing

Standard: Auditing based on statement, Privileges and object level.

Fine-grained: Auditing on a finer granule which is based on content i.e value > 10,000

The standard auditing having 3 levels of auditing:

Statement: Audit all action at any type of objects.

Privilege: audit action on system level privileges

Object_level: Specific audit action lie select, update, insert or delete.

For all the 3 level of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also audit on if the access was successful (whenever successful) or not (whenever not successful)

DB: Enables database auditing and directs all audit records to the database audit trail, except for records that are always written to the operating system audit trail.

DB, extended: As per the DB value but also populate sqlbind and sqltext clob columns

OS: Enables database auditing and directs all audit records to an operating system file

NONE: Disables auditing (This value is the default.)

Note: You must bounce back the instance to activate audit parameter.

Setting Audit Trail:

SQL>alter system set audit_trail = db scope=spfile;

SQL>alter system set audit_file_dest = ‘c:oracleauditing’;

SQL>alter system set audit_trail = os scope=spfile;

Note: Note: if the audit_file_dest is not set then the default location is $oracle_home/rdbms/audit/

SQL>alter system set audit_sys_operations = true scope = spfile;

Note: this will audit all sys operations regardless ifaudit_ trail is set.

SQL>alter system set audit_trail = none scope=spfile;

Setup Audit Trail through Initialization Parameter

  1. Set “audit_trail = true” in the init.ora file.
  2. Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS

Audit Options

BY SESSION/BY ACCESS

BY SESSION causes Oracle to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.

WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

Auditing Example:

Audit Session By Scott, Lori;

Audit Delete Any Table By Access Whenever Not Successful; Audit Delete Any Table; Audit Select Table, Insert Table, Delete Table, Execute Procedure By Access Whenever Not Successful; Audit Delete On Scott.Emp; Audit Select, Insert, Delete On Jward.Dept By Access Whenever Successful; Audit Select On Default Whenever Not Successful; Audit Select Table By Appserve On Behalf Of Jackson; Audit Alter, Index, Rename On Default By Session; Audit Alter User; Audit Lock Table By Access Whenever Successful; Audit Delete On Scott.Emp By Access Whenever Successful; Audit Delete Table, Update Table By Hr By Access; Audit All By Hr By Access; Audit Execute Procedure By Hr By Access;

Disabling Audit

Noaudit Table;

Noaudit All Privileges;

Turn Off All Auditing

Noaudit All;

Noaudit All Privileges;

Noaudit All On Default;

Purge Auditing

Delete From Sys.Aud$;

Truncate From Sys.Aud$

Delete From Sys.Aud$;

Delete From Sys.Aud$ Where Obj$Name=’Emp’;

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. It’s contents can be viewed directly or via the following views:

Dba_Audit_Exists, Dba_Audit_Object, Dba_Audit_Session, Dba_Audit_Statement, Dba_Audit_Trail, Dba_Obj_Audit_Opts, Dba_Priv_Audit_Opts, Dba_Stmt_Audit_Opts

Auditing Via Trigger

It is possible to audit the system by using triggers, there are a number of system-level triggers that can be fired such as database startup, logon, logoff, ddl, server error

Example Trigger Auditing:
create or replace trigger audit_insert after insert on vallep.employees for each row
insert into employees_table_audit
values (user, sysdate);
create or replace trigger logon_audit_trig
after logon on database
begin
insert into logon_audit values (user, sys_context(‘userenv’, ‘sessionid’), sysdate, null,
sys_context(‘userenv’, ‘host’));end;

Fine-Grain Auditing

Fine-grain auditing (FGA) allows you to audit users accessing data of a certain criteria. As per standard auditing you can audit select, insert, update and delete operations. You use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that you can attach handlers (like a trigger) to the policies which can execute procedures which could email or page you. There are many options that can be applied to the dbms_fga package, so best to look up the oracle man pages but here are some simple example

Privilege: grant execute on dbms_fga to vallep;

Creating Auditing:
dbms_fga.add_policy (object_schema => ‘vallep’,
object_name => ’employees’,policy_name => ‘compensation_aud’,
audit_columns => ‘salary,commission_pct’,
enable => false, statement_types => ‘select’);

Creating (handler):
dbms_fga.add_policy (object_schema => ‘vallep’,
object_name => ’employees’,policy_name => ‘compensation_aud’,
audit_columns => ‘salary,commission_pct’,
enable => false,statement_types => ‘select’
handler_schema => ‘vallep’
handler_module => ‘log_id’);
create procedure vallep.log_id (schema1 varchar2, table1 varchar2, policy1
varchar2) as
beginutil_alert_pager(schema1, table1, policy1);/* send an alert via a pager */ end;

Removing auditing:
dbms_fga.drop_policy (object_schema => ‘vallep’,
object_name => ’employees’, policy_name => ‘compensation_aud’);
Enabling auditing:
dbms_fga.enable_policy (object_schema => ‘vallep’,
object_name => ’employees’,
policy_name => ‘compensation_aud’);

Disabling auditing:

dbms_fga.edisable_policy ( object_schema => ‘vallep’,object_name => ’employees’, policy_name => ‘compensation_aud’);

Usful Tables:

DBA_AUDIT_POLICIES, DBA_FGA_AUDIT_TRAIL, DBA_COMMON_AUDIT_TRAIL

Maintenance

The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size

Security

Only DBAs should have maintenance access to the audit trail. If SELECT access is required by any applications this can be granted to any users, or alternatively a specific user may be created for this. Auditing modifications of the data in the audit trail itself can be achieved as follows:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

User Privileges and Roles in Oracle

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user’s object. The types of privileges are defined by Oracle.

Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles.

Restricting System Privileges

O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to FALSE.

When this parameter is not set to FALSE, the ANY privilege applies to the data dictionary, and a malicious user with ANYprivilege could access or alter data dictionary tables. The default for O7_DICTIONARY_ACCESSIBILITY is FALSE.

Creating a Role

A Roles can be specified to be authorized by:‎

– The database using a password

CREATE ROLE SHAHID_ROLE1 IDENTIFIED BY AHMED;‎

– An application using a specified package

CREATE ROLE admin_role IDENTIFIED USING hr.admin;‎

– Externally by the operating system, network, or other external source

ALTER ROLE clerk IDENTIFIED EXTERNALLY;‎

– Globally by an enterprise directory service

CREATE ROLE supervisor IDENTIFIED GLOBALLY;‎

Dropping Roles

DROP ROLE clerk;

Granting System Privileges and Roles

GRANT CREATE SESSION, accts_pay TO SHAHID1; GRANT new_dba TO SHAHID1 WITH ADMIN OPTION;

Here in above to grant option first statement granting system privilege along with a role accts_pay to user SHAHID1 where second statement granting a role new_dba to user SHAHID with admin option that means the user SHAHID1 cannot only use all the privilege implicit in new_dba role but can grant, revoke, or drop the new_dba too.

The SET ROLE Statement

During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. You can retrieve the current roles that are active for a user in a session by using SESSION_ROLES

SQL> connect hrms/hrms@orcl3;

SQL> select * from session_roles;

ROLE

——————————

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

You can change the roles active in the current session by “SET ROLE”

SQL> select * from session_roles;

ROLE

——————————

SHAHID_ROLE1

Enable specific set of roles in the current session (SHAHID_ROLE1 is created)

SQL> set role SHAHID_ROLE1, EXECUTE_CATALOG_ROLE

Role set.

SQL> select * from session_roles;

ROLE

——————————

EXECUTE_CATALOG_ROLE

SHAHID_ROLE1

Enable all roles in the current session

SQL> set role all;

Role set.

You can disable all roles with the following statement:

SET ROLE NONE;

Note: A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES.

Using OS Role Management

OS_ROLES = TRUE

If OS_ROLES is set to TRUE, the operating system completely manages the grants and revokes of roles to users. Any previous grants of roles to users using GRANT statements do not apply; however, they are still listed in the data dictionary. Only the role grants made at the operating system level to users apply. Users can still grant privileges to roles and users.

Note: If you choose to have the operating system to manage roles, by default users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over a non-secure connection.

SELECT * FROM DBA_SYS_PRIVS; Listing all system Grants SELECT * FROM DBA_ROLE_PRIVS; Listing all Role Grants SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘JWARD’; Listing object privilege Granted to user SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS; listing all column specific privilege that have been granted SELECT * FROM SESSION_ROLES; Listing all role currently enabled.‎ SELECT * FROM SESSION_PRIVS; Listing all system privilege currently available.‎ SELECT * FROM DBA_ROLES; listing Role of database SELECT GRANTED_ROLE, ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE = ‘SYSTEM_ADMIN’;‎ The above query will display information about the privilege domains of role.‎

How to find the process id (Status of Server) listening on a port?

On Windows:
C:netstat at ; netstat /?
It will display protocol statistics and current TCP/IP network connections.
C:netstat -a -n
It will display all connection and listening port along with address and port number in numeric form.
C:> netstat -ano | find /I “1521” | find /I “LISTEN”
Trying to find which process is listening on particular port 1521. It will Show if any process is listening on port 1521 such as:
Proto Local Address Foreign Address State PID
TCP 0.0.0.0:1521 0.0.0.0:0 LISTENING 2164
C:netstat -e -s
It will display your Ethernet statistics.
C:netstate -a -o
It will display process id associated with each process.
C:netstate -v -b
It will display component involved in creating the connection and listening port for all executable.
——————————————————————————————————————————————————————————————————————-
On Linux:
Using netsat find if any process is listening to any port.
$ netstat -anp | grep <port number>
$ netstat -anp | grep <port number>
This shows the PID and the program name that uses the port. The command must be run as root.
Example:
# netstat -anp | grep 12345
# netstat -anp | grep 12345
tcp    0   0 127.0.0.1:12345   0.0.0.0:*    LISTEN   6629/ssh
tcp    0   0 ::1:12345              :::*    LISTEN   6629/ssh
ssh with the PID 6629 is using the port. Find more info about it.
# ps -efl | grep 6629
4 S root      6629 29716  0  75   0 –  6976 –      14:05 pts/4    00:00:00 ssh testserver -D 12345 -l db2inst1
0 S root      7648  7302  0  78   0 –   742 pipe_w 14:07 pts/7    00:00:00 grep 6629

In this case, the user db2inst1 is deliberately using the port 12345 by specifying -D option of ssh.

[root@localhost ~]# fuser -n tcp <port number>
Using fuser you can find the process id listening on the port.
——————————————————————————————————————————————————————————————————————–
On AIX:
1. netstat -Aan | grep <port number>
This shows if the specified <port number> is being used. The hex number in the first column is the address of protocol control block (PCB)
2. rmsock <addr of PCB> tcpcb
This shows the process who is holding the socket. Note: This command must be run as root..
Example:
$ netstat -Aan | grep 30542
f10000f303321b58 tcp4     0     0  *.30542     *.*     LISTEN

For more information visit the link: https://www-304.ibm.com/support/docview.wss?uid=swg21264632

Oracle Database User Management

CREATE AND DROP USER:

create user shaan identified by moon

default tablespace rtbs

temporary tablespace temp

quota 30m on rtbs;

Above command creates a user shaan with password moon. Consider the tablespace you have in which shaan will store his data is “RTBS”. The tablespace used for storing temporary segments will be “TEMP” and the amount of space which the user shaan can use on “RTBS” tablespace is 30M.
SQL> DROP USER SHAAN CASCADE;
ALTERING/UNLOCKING ACCOUNT:

SQL> alter user shaan identified by moon account unlock;
The above command alters or unlocks the “SHAAN” user with password “moon”.
SQL> select username, account_status, default_tablespace,

temporary_tablespace, profile from dba_users

where username = ‘HRMS’;
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE

—— ————– —————— ——————– ———

HRMS      OPEN           MUJ_HRMS_DBF      TEMP                 DEFAULT

The above query shows the account information related to “HRMS”.
ALTERING TABLEPSACE QUOTA:
SQL> select * from dba_ts_quotas where username = ‘HRMS’;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS

————— ——– —– ——— —— ———-

MUJ_HRMS_DBF    HRMS     198901760 -1    24280  -1

SQL> alter user shaan quota 40m on RTBS;‎
GRANTING AND REVOKING PRIVILEGES:
SQL> GRANT create table to SHAAN;‎
SQL> GRANT create session to SHAAN;
SQL> GRANT create any table, create tablespace to SHAAN;
SQL> REVOKE create any table from SHAAN;
SQL> REVOKE create tablespace from SHAAN;
SQL> GRANT select, insert, update, delete on HRSM.PAY_PAYMENT_MASTER to HRMS;
SQL> REVOKE update,delete on HRMS.PAY_PAYMENT_MASTER from HRMS;
ROLES:
SQL>create role MY_ROLE;

SQL> GRANT create any table, alter any table, drop any table, select any table, update any table, delete any table to MY_ROLE;

SQL> GRANT create any index, alter any index, drop any index to MY_ROLE
SQL> GRANT alter session, restricted session to MY_ROLE;

SQL> GRANT create tablespace, alter tablespace, drop tablespace, unlimited tablespace to MY_ROLE;
SQL> GRANT select, insert, update, delete on HRMS.PAY_PAYMENT_MASTER to MY_ROLE;

SQL> GRANT MY_ROLE to SHAAN;
SQL> select * from dba_sys_privs where grantee = ‘SHAAN’;

GRANTEE                        PRIVILEGE            ADM

———————-         ——————– —

SHAAN                          CREATE TABLE         NO

SHAAN                          CREATE SESSION       NO

SQL> select * from dba_sys_privs where grantee = ‘MY_ROLE’‎

GRANTEE                        PRIVILEGE                   ADM

———————-         ————————— —

MY_ROLE                        DELETE ANY TABLE            NO

MY_ROLE                        CREATE ANY TABLE            NO

MY_ROLE                        DROP TABLESPACE             NO

MY_ROLE                        ALTER TABLESPACE            NO

MY_ROLE                        ALTER ANY INDEX             NO

MY_ROLE                        DROP ANY TABLE              NO

MY_ROLE                        DROP ANY INDEX              NO

MY_ROLE                        UPDATE ANY TABLE            NO

MY_ROLE                        ALTER SESSION               NO

MY_ROLE                        SELECT ANY TABLE            NO

MY_ROLE                        RESTRICTED SESSION          NO

MY_ROLE                        CREATE ANY INDEX            NO

MY_ROLE                        ALTER ANY TABLE             NO

MY_ROLE                        UNLIMITED TABLESPACE        NO

MY_ROLE                        CREATE TABLESPACE           NO

SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs

‎where grantee = ‘SHAAN’;‎

GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE

——– —— ———— ———  ———

HRMS     HRMS  PAY_PAYMENT_MASTER HRMS  SELECT

HRMS     HRMS  PAY_PAYMENT_MASTER HRMS  INSERT

SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs

where grantee = ‘MY_ROLE’‎;

GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE

——– —— ———— ———  ———

MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  UPDATE

MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  SELECT

MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  INSERT

MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  DELETE

SQL> select * from dba_roles where role = ‘MY_ROLE’;‎

ROLE                  PASSWORD AUTHENTICAT

——————— ——– ———–

MY_ROLE               NO       NONE

SQL> select * from dba_role_privs where grantee = ‘SHAAN’;‎

GRANTEE                        GRANTED_ROLE    ADM DEF

————————       ————— — —

SHAAN                          MY_ROLE         NO  YES

SQL> select * from role_sys_privs where role = ‘MY_ROLE’;‎

ROLE                  PRIVILEGE                       ADM

——————— ——————————- —

MY_ROLE               DROP TABLESPACE                 NO

MY_ROLE               CREATE ANY TABLE                NO

MY_ROLE               DELETE ANY TABLE                NO

MY_ROLE               ALTER TABLESPACE                NO

MY_ROLE               DROP ANY TABLE                  NO

MY_ROLE               ALTER ANY INDEX                 NO

MY_ROLE               UPDATE ANY TABLE                NO

MY_ROLE               DROP ANY INDEX                  NO

MY_ROLE               ALTER SESSION                   NO

MY_ROLE               RESTRICTED SESSION              NO

MY_ROLE               SELECT ANY TABLE                NO

MY_ROLE               CREATE TABLESPACE               NO

MY_ROLE               UNLIMITED TABLESPACE            NO

MY_ROLE               ALTER ANY TABLE                 NO

MY_ROLE               CREATE ANY INDEX                NO

SQL> select * from role_tab_privs where role = ‘MY_ROLE’;‎Code:

ROLE     OWNER   TABLE_NAME   COLUMN_NAME   PRIVILEGE   GRA

——– ——- ———— ————- ———– —

MY_ROLE  HRMS   PAY_PAYMENT_MASTER          DELETE       NO

MY_ROLE  HRMS   PAY_PAYMENT_MASTER          UPDATE       NO

MY_ROLE  HRMS   PAY_PAYMENT_MASTER          SELECT       NO

MY_ROLE  HRMS   PAY_PAYMENT_MASTER          INSERT       NO

SQL> revoke MY_ROLE from SHAAN;
PROFILES:
SQL> create profile DEVELOPER limit

failed_login_attempts 3

password_lock_time unlimited

password_life_time 30

password_reuse_time 30

password_grace_time 5

idle_time 30;
SQL> alter user SHAAN profile developer;
SQL> select * from dba_profiles where profile = ‘DEVELOPER’;

PROFILE     RESOURCE_NAME                    RESOURCE LIMIT

———– ————–                   ——– —–

DEVELOPER   COMPOSITE_LIMIT                  KERNEL   DEFAULT

DEVELOPER   SESSIONS_PER_USER                KERNEL   DEFAULT

DEVELOPER   CPU_PER_SESSION                  KERNEL   DEFAULT

DEVELOPER   CPU_PER_CALL                     KERNEL   DEFAULT

DEVELOPER   LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

DEVELOPER   LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

DEVELOPER   IDLE_TIME                        KERNEL   30

DEVELOPER   CONNECT_TIME                     KERNEL   DEFAULT

DEVELOPER   PRIVATE_SGA                      KERNEL   DEFAULT

DEVELOPER   FAILED_LOGIN_ATTEMPTS            PASSWORD 3

DEVELOPER   PASSWORD_LIFE_TIME               PASSWORD 30

DEVELOPER   PASSWORD_REUSE_TIME              PASSWORD 30

DEVELOPER   PASSWORD_REUSE_MAX               PASSWORD DEFAULT

DEVELOPER   PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT

DEVELOPER   PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

DEVELOPER   PASSWORD_GRACE_TIME              PASSWORD 5

SQL> drop profile developer cascade;

How to Stop access using “/ as sysdba”

How to Stop access using “/ as sysdba”

You can stop and secure your client database access by restricting the DBA’s connecting to as SYS user by using “/ as sysdba”.

In the file sqlnet.ora located in $ORACLE_HOME/network/admin folder, modify the following line:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

Instead of above modify it as:

SQLNET.AUTHENTICATION_SERVICES=(NONE)

NONE for no authentication method, including windows native operating system authentication (to use windows native OS set this parameter to NTS. When it is set to ‘NONE’ a valid username and password can be used to access the database.

This will prevent the access of “/ as sysdba” when connected as the ‘oracle user (oracle owner account) but the DBA can easily modify the configuration parameter in SQLNET.ORA, if he has the required permission on the particular file.

As we know that connect / as sysdba would not use the password file and it uses OS authentication only. Thus setting the NONE requires valid OS authentication.

To avoid this change the ownership of the SQLNET.ORA file to ‘root’ or any other functional OS user, and provide a read permission to dba/oinstall group.

chown root:oinstall sqlnet.ora

chmod 640 sqlnet.ora

You can also use the parameter SQLNET.CLIENT_REGISTRATION to set a unique identifier for this client computer. The identifier is passed to the listener with any connection request and is included in the Audit Trail. The identifier can be any alphanumeric up to 128 character long.

SQLNET.CLIENT_REGISTRATION=1432

Use the SQLNET.ALLOWED_LOGON_VERSION parameter to define the minimum Oracle Database client version that is allowed to attempt connections to Oracle database instances under the control of the given code tree.

If the client version does not meet or exceed the version defined by this parameter, then authentication fails with an ORA-28040 error.

If both Oracle8i and Oracle9i databases are present, then set the parameter as follows:

SQLNET.ALLOWED_LOGON_VERSION=8