:

Oracle 11g

oracle 11g database

In this article I would like to give a high level overview about what the Oracle Relational Database Management System (RDBMS) is; its history, what the Oracle database does, when the Oracle 11g database was released and the kinds of administrative tasks that a database administrator can expect to perform when using an the database. So, let’s start with a little history lesson.

History of the DBMS

The Oracle database is a Relational DataBase Management System (RDBMS). It’s called an RDBMS because the way in which it works is designed around the relational model which was developed by Edgar F Codd in 1969.

There are other types of database, such as the object-oriented design, which other database systems have been built upon but the relational model is much more common and is home to many large scale systems in use worldwide today.

In addition to the Oracle database there are other vendors, such as IBM and Microsoft who also make their own flavours. It should be said that although each system has been implemented in a different way, the same underlying principles are the same for each one. They are all based around the Relational database model and as such do have many similarities.

The next question that you might ask is what the database is for. Good question, let’s take a look.

The Database – Simplified

There are many reasons to have a database and in fact most websites that you probably use every day have a database storing all of the information which is displayed to you on screen. Some examples are Amazon, Ebay and all large banks. Here is a list of some of the reasons why you would want to use an RDBMS to store your data:

  • Store data efficiently
  • Allow it to be retrieved quickly
  • Reduce duplication of data
  • Provides a mechanism to manipulate the data
  • Facilitates a multi user environment, allowing concurrent access to data
  • Shows the relationship between the data
  • Constraints provide a way to implement business logic and rules

Without a database many large organisations would have a hard time storing, organising and using their data. If you think about it you could say that a spreadsheet is a form of database, because it stores data. The difference between something like a spreadsheet and an RDBMS is that there is the data, which is stored on disk, and then an instance (just a name of memory, RAM, and server processes) which uses processes and memory structures to store, manipulate and manage the data more effectively.

Another benefit of the database is that it can be used to store and retrieve database very efficiently. Retrieving data from disk is slow and resource consuming whereas getting that data from memory is fast. This is why the database allows data to be stored in memory and shared. This way, when different users request the same information only one of them has to do the costly process of taking it from disk, and only on the first time they access that piece of data; all subsequent requests for the same piece of information can access the data in memory.

Because of the way in which the data is stored and how the data is linked it is possible to remove the amount of duplicated data. For example, imagine that you have customer and they make an order on your system. If you stored all of this information in spreadsheets you would have to add a line into the orders spreadsheet containing all of the customer’s data and the order information. You would have to do this for every order, resulting in a large amount of duplicate data.

In the Oracle database this is handled differently. You have one location (table) for the customer data which is stored just once, and another location (table) for the order information which contains only the order information. So, how do you know which order is for which customer? Well, you have one more column in the order table which links back to the customer table so that you can easily identify which customer the order is for.

If you were updating all of this information on a spreadsheet then you’d only be able to have one user accessing the spreadsheet at a time because a lock is placed at the document level regardless of which piece of data is being updated. Within the RDBMS, however, the lock is placed at the lowest level possible. For example, in the Oracle database if a user is updating one row then the lock will be placed only upon that row and nothing else. This ensures that as many users can access the data at the same time as possible. This gives the impression of concurrent access to the data. This allows thousands of users to use the same database at the same time without any problems – until they want to manipulate the exact same data set that is!

Another nice feature is the use of constraints to enforce business logic. For example, if you decided that a specific column had to contain data then you can tell the database to add a constraint which doesn’t allow anyone to add or update the data in that row to not have a value – also known as a NOT NULL constraint.

Now that we have found out a little bit more about the database at a very high level, let us look at Oracle.

When did Oracle Join the Party?

Oracle were one of the early adopters of the RDBMS in the late 70s several guys, one of them being Larry Elison, got together and released a version of software called version 2 in 1979.

Since that time Oracle have continued to grow and are now one of the major players in the database market, arguably the market leaders. Since its inception, the Oracle database has undergone many changes, revisions and additions to make it what it is today.

Oracle 11g

The latest version of the database software being Oracle 11g, which was released in 2007.

Over the years Oracle have had to create new and innovative ways to keep ahead of their competitors, such as Microsoft. It could be said that one of the disadvantages of being one of the first companies in the database market is that their fundamental design decisions made back then are difficult to change now, whereas the relatively new software houses have the benefit of hindsight.

I would suggest that one of the biggest reasons Oracle are as popular today in large businesses as they were many years ago, even with the other software vendors making large steps forward, is because none of the large companies want to risk a migration off of Oracle because there is too much risk involved. If a large bank were to think about migrating from Oracle to SQL Server (Microsoft’s RDBMS) there would be a high chance of disruption with little gain. This is also why lots of the larger banks are not on the latest release of the software, because the upgrade risk is too high and outweighs the benefits.

I hope by now you get an understanding as to what the Oracle 11g database is, but just to be sure I will cover a few of the fundamentals about the Oracle database.

What is Oracle Database 11g?

The Oracle database is a Relational DataBase Management System (RDBMS), as stated above. It abides by the rules of the relational model, but with its own implementation which is specific to Oracle. If you are not familiar with the fundamentals of the relational database model then you should take a bit of time to understand the fundamentals. A quick Google search will give you more than enough information to get you started.

Over the years, from version to through to the latest version of Oracle which is version 11, Oracle have developed, tweaked and tuned the system. The product out at the moment is designed for large scale, enterprise level data storage and access. There are an abundance of optional extras you can get, such as partitioning so you can split up your data into more manageable chunks, replication so that you can have multiple copies of the same database spread out thousands of miles apart and still be updated all at the same time and many more great features.

Over the years the Oracle database software has tried to become more automatic, meaning that the smooth running of the database system should require less intervention by an Oracle DBA. This is partly true, but with such a complex piece of software it’s hard to get it working without the knowledge of a good Oracle DBA and things do need checking, tweaking and tuning as time passes. That being said, Oracle have done a good job of removing some of the more banal jobs by making the automatically handled by the database system.

In addition, the performance monitoring tools that are supplied with the database are fantastic. They allow you to get some a fine level of detail out of the system so you can track down exactly what the problem is.

So, now that you know what Oracle database 11g is we can have a look at what kinds of maintenance and administration tasks are involved when working as an Oracle database administrator.

Database Administration

Oracle database administration is very similar regardless of which version of Oracle you are running, be it Oracle 9i, 10g or 11g. The fundamentals don’t change that often and your core skill set will be of value when working with most versions of Oracle. There are, of course, some basic skills and more advanced skills that you need to acquire to administer an Oracle database.

I thought I would start with a list of the basic areas in which you will need to develop your knowledge and understanding in order to administer an Oracle 11g database.

  • Installation and configuration
  • User administration
  • Space management
  • Memory management
  • Parameter tuning
  • Backup and Recovery – Recovery Manager (RMAN)
  • Database performance monitoring
  • Operating system performance monitoring

 

  • Database design
  • Operating system administration
  • SQL tuning
  • Replication (E.g. Data Guard)
  • High Availability (HA) solutions

Now let’s go through the items on the list in a little more detail. I’ve split the list into two to distinguish between absolutely essential skills and ones which are definitely necessary but not instead of the essential ones.

I think the essential ones are all pretty self explanatory. Tasks like installing and configuring go hand-in-hand with the parameter tuning, space management, memory management and user administration. You need to know what kind of system profile you will have in order to tune the database parameters. For example, if you have an Online Transaction Processing (OLTP) type system (which is a system like an orders database for an online shop where you know exactly what actions will take place and they will be small and quick) you will use different settings for parameters like the block size and multiblock read count when compared to a data warehouse (a system which stores lots of data and usually has a random data access pattern where users can run any kind of adhoc query) type of system.

Once you have installed and configured the system, and data has been added, you will need to backup that data because I’m sure that it is important to your business. To do this I would recommend using Oracle’s RMAN facility which is great for this job. If you have the database in archivelog mode you can leave the database open and in use while you perform a “hot” backup. You can also compress the datafiles so they will only take up about 20% of their original size.

Database and operating system performance monitoring are very closely linked. You need to be able to see what the CPU usage is like, how much disk activity there is, whether there are queues on the disk or CPU and the general health of the system.

Oracle’s Enterprise Manager is a fantastic tool for monitoring and provides a very easy to use interface which shows issues straight away, both in the database and the operating system. I would highly recommend using this if you can. If not, you will have to learn how to look in the database to find out performance issues and use the AWR reporting functionality.

You should always know how to work without the Enterprise Manager being available because you might find that one day you don’t have it and then you would be stuck. So, get used to using the dynamic performance views in the database, like v$session_longops, v$session_event, v$session_wait, v$sysstat and also familiarise yourself with the AWR reports. The AWR reports are great for finding performance issues.

Once you have mastered the basics it’s time to learn some more advanced Oracle database administration skills. I’ve only added a few items to the list but there are so many that could be on it.

Learning how to design a database takes a lot of experience and is an advanced skill. You need to appreciate how other parts of the system work, the business requirements and how the users are going to be using the system. Only with experience of all the different parts of the system will you be able to design a well performing system. So, you need to understand about the storage (most likely some SAN technology), about application design and being able to capture the requirements of the client. This skill is more the level of an architect but it’s good to know how to design the database tables and schemas as it will aid in your learning.

SQL tuning is something that a lot of developers will know how to do so you can work closely with them to learn how to do it. It’s a great skill to have because you will find that if you tune the SQL statements it will dramatically reduce the resource usage on the system as a whole. I was once administering an Oracle database where the CPU on the server was sitting at around 85% at peak load times during the day. We used an AWR report to identify that there were two SQL statements consuming the most CPU and notified the developers. They were able to change the statements and we reduced the CPU usage on the server to 45%! These statements were being executed millions of times a day because they were core to the functioning of the system.

If you work for any large company they are pretty much guaranteed to have some kind or replication going on. It’s good to use it as a high availability solution in case your main production system goes down, and is an ideal way to off-site your backups and reduce load on your primary database. For example, you could have a main production site in London and a secondary site, which would be an identical copy of the primary one, sitting over in New York. It could be updating real time as well. So, it’s a good idea to become familiar with high availability and replication technologies. You can start by reading the Oracle white papers on Maximum Availability Architecture (MAA).

I hope that I’ve covered enough to get you thinking about Oracle database administration and what it involves. I haven’t gone into that much detail because I wanted to keep it relatively short…There are so many subjects to talk about when it comes to administering an Oracle database that there are many books written about the subject. You might be well advised to buy one. In addition, make sure that you read the Oracle documentation as it contains a wealth of knowledge.

Good luck with administering your Oracle database!

Rob

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *