Database testing

How to Test and Validate Your Database Backup and Recovery?

In this article we shall discuss about verifying and testing your database backups. We will be explaining concepts such as what, why and how about Database backups and methods to test the backup.

Oracle Database Testing – This shall be one of the 3 parts of Oracle Database testing series. We will cover following 3 parts in this database testing series:

Let’s start with the Introduction to Oracle Database Testing:

We shall cover the following topics in this part:

What is a database?

Why Oracle?

Why the need to test a database?

How do you test one?

#1) What is a database?

Also read => All about Database testing

A database is collection of data structured methodically which helps manage, manipulate and retrieve data as and when required.

A database is managed by making use of database software named the Database Management System (DBMS) which provides an interface for end users to access the data stored in the database. We access, manipulate this data by SQL statements (Structured Query Language) which was designed for this specific purpose.

For a layman who isn’t aware of the term “database” consider this – We have a desktop/laptop assuming most of us have windows software installed on it, we open a Microsoft excel sheet, store couple of names and addresses of individuals, save and close it. This data is stored on your hard disk of your desktop/laptop. Next time we want to look up the address of a person which we have in the excel sheet, we open this excel sheet and look it up. Consider this excel sheet as the “database” and the windows operating system as the “DBMS” software which knows how to access and open the file so that the requested piece of information is available to us.

The DBMS software is much more complex, evolved and integrated and is responsible for performing various tasks and activities to ensure smooth functioning of the database.

#2) Why Oracle?

Oracle is the corporation which has built the software to manage a database. Oracle database is one of the most sophisticated, robust, scalable, secure databases available in the market due to the exquisite features all bundled and available to us.

Data in this database is termed relational due to the fact that data is stored and sorted in tables and each of these tables has a relationship established among them. Hence the name- Relational Database Management System (RDBMS).

Real example: Consider a class which consists of 50 students and each of them having residential address, class that they have opted, their phone numbers.

Each student’s address could be grouped and stored in one table, phone numbers in an another table. If I have to query details of a particular student I look up each of these tables and get the respective information since each of these tables seem “related”.

Managing data in Oracle is particularly favored due to simplicity in managing and creating them. Since today’s competitive world demands uninterrupted availability, Oracle database helps deliver it at lower costs.

You could skip the first two sections discussed above if you already have a faint idea about databases, relational models and Oracle.

Now, comes the interesting part:

#3) Why the need to test a database?

Data is one of the most integral constituents of an organization which is deemed extremely sensitive and we demand the database which manages them to make sure they are highly impregnable.

A Database can be considered as an application that runs among various other applications running on your server. Database does have to be installed just like any other application and interacts with the hardware through the operations system (windows, unix) that runs on your server.

We have to make sure we get the optimal performance and benefits out of the Oracle database. The database will not manage itself and perform if you aren’t aware of its capability.

We have various methods to test a database in terms of load, stress, I/O, performance.

Functioning of a database depends on several parameters such as the memory, CPU processing speed, I/O subsystem, filesystem, hardware and applications that connect to the database.

So, to make sure the database performs optimally and avoids the risk of instabilities we need to make sure all the above parameters are well tuned. Most importantly tune them with respect to your environment. Database in every organization is different – handled, managed and backed up differently. A telecom industry will want a database which is highly responsive and accurate since millions of user might be making use of the database. A database for a chemical factory comparatively wouldn’t need such a high responsive database since they would focus mostly in reporting and retrieving data as they would have much lesser traffic to handle.

#4) How do you test your database?

We usually have the following environments in an organization:

UAT (User Acceptance Testing)

DEV (Development environments)

Pre-Prod or TEST environments

Production

We usually perform testing on a considerable amount of datawhich we perceive to be eventually moved to production. This testing is performed in the pre-prod, test or UAT environments.

Before deploying a production database we need to validate the data to make sure that the production is not hampered with any adverse effects. We need to make sure that the database is tested with full workload just as you expect it to be run against the production database and monitor to ensure the operational and functional requirements are met. We need to test each of the SQL statements thoroughly before they would be deployed in production.

Set a benchmark which you feel is acceptable; this again involves several cycles of testing. If there are any third party applications that might be part of the organizational infrastructure, make sure the third party vendors are involved and obtain their feedback regarding the licensing, functioning of their application with the database.

If they do have certain parameters that need to be set to handle a particular amount of workload, set them and make sure they are accepted by the organization and the vendor. If they do have certain tools to test their application performance make sure they are validated since most databases, including Oracle, do not support third party applications and fixing an issue which creeps up after having the application deployed in production would be very tedious and could have a huge impact on the organization at which the database server resides.

Points to Consider for Testing Oracle DB:

Consider the amount of users

SQL statements that they might execute

The Memory used for each of the users for each of the statements

The number of transactions that a user would normally execute to retrieve the desired information from the database

Test if users are receiving the desired response from the database within the expected stipulated time.

1) Memory Test

In terms of Memory, depends on the number of processes that are running in the database as the database system constitutes of user, server and background processes.

User processes are the processes which service user requests from the application. Server processes interpret the request from the application users and processes the SQL statements sent from the user.

Background processes help with the functioning of database and perform various tasks during the life of the database. The advantage of Oracle is that the memory can be automatically managed by database and it can tune it accordingly to the load. But, despite that we need to make sure we have enough memory on the server and certain amount of memory is always dedicated for the Oracle database.

2) Space Test

In terms of Space, we need to consider the amount of data which would be worked on daily, weekly, monthly, yearly and how much of it would you want stored permanently or for a longer period of time.

We should get this valuable piece of information from the Business intelligence who has designed the infrastructure. Another crucial piece of information is to know if we need to store unlimited amounts of data. Define “unlimited” should be your first question. We can have terabytes of data stored as long as you have enough resources to support it. We would have to manage them with data requirements which have been ever growing.

3) Processing Test

In terms of Processing, how many CPU cores would we need? Remember a core is a physical entity and with the advent of CPU subsystem technology, we have various methods to employ and utilize multiple CPUs cores and multiple threads per core.

Suppose you have 8 CPUs at your disposal, are we sure we are utilizing all of them. Do we really need 8 CPUs? Can we manage them with 4 or 6? Estimating the amount of CPU cores that you would require has become more and more imperative to understand processor requirements.

4) Application Testing

In terms of Application testing, we need to understand the type of application – Is it a Pro*C, OCI, JDBC application.

The mechanism of connection which depends on the type of client software used and is connection pooling employed. An approximate number of processes and sessions that are required to accommodate the users connecting to the database simultaneously.

For versions higher than Oracle 11g we also have a very good feature called the Oracle Real Application testing with several enhancements which makes use of SQL Performance analyzer,Database replay and Test Data management which helps you diagnose the changes to data and manage them. More details on this in the next part of this series.

Conclusion

To conclude, we have dealt with the definition of a database, introduction to oracle database, the need and aspects of testing oracle database.

In the next part of this series, we shall discuss more on each of the aspects of Oracle Database testing to make sure the design and functioning of the database is broadly met.

Till then put your all question related to Oracle database testing, in comments below.

About the author: This is a guest post by Suntrupth Yadav. He is an Oracle Certified Professional in 10g and 11g with over 8.5 years of total experience in software industry. He is an expert handling all kinds of Oracle database issues.