Efficient Monitoring and Management of The Oracle Database
Table of Contents
Definition of database
A database is defined as a series of data organized and related to each other, which are collected and exploited by the information systems of a company or business.
Database Management System (DBMS)
The Database Management Systems are a very specific type of software, dedicated to serving as an interface between the database, the user and the applications that use it. It is composed of a data definition language, a data manipulation language, and a query language.
Advantages of databases
Control over data redundancy:
File systems store several copies of the same data in different files. This causes wasted storage space, in addition to causing the lack of data consistency.
In database systems, all these files are integrated, so multiple copies of the same data are not stored. However, in a database, it is not possible to eliminate redundancy completely since it is sometimes necessary to model the relationships between the data.
Data consistency:
Eliminating or controlling data redundancies greatly reduces the risk of inconsistencies. If a data is stored only once, any update must be performed only once, and it is available to all users immediately. If a data is duplicated and the system knows this redundancy, the system itself can be responsible for ensuring that all copies remain consistent.
Share data:
In file systems, the files belong to the people or departments that use them. But in database systems, the database belongs to the company and can be shared by all authorized users.
Maintenance of standards:
Thanks to integration it is easier to respect the necessary standards, both those established at the company level and national and international. These standards can be established on the format of the data to facilitate their exchange, they can be documentation standards, update procedures, and access rules.
Improvement in data integrity:
The integrity of the database refers to the validity and consistency of the stored data. Normally, integrity is expressed through restrictions or rules that cannot be violated. These restrictions can be applied both to the data and to their relationships, and it is the DBMS that should be responsible for maintaining them.
Improvement in security:
The security of the database is the protection of the database against unauthorized users. Without good security measures, the integration of data in database systems makes them more vulnerable than in file systems.
Improvement in data accessibility:
Many DBMS provide query languages or report generators that allow the user to make any kind of query about the data, without it being necessary for a programmer to write an application that performs such a task.
Improvement in productivity:
The DBMS provides many of the standard functions that the programmer needs to write to a file system. At a basic level, the DBMS provides all the file management routines typical of application programs.
The fact of having these functions allows the programmer to better focus on the specific function required by the users, without having to worry about the details of low-level implementation.
Improvement in maintenance:
In the filesystems, the descriptions of the data are immersed in the application programs that handle them.
This makes the programs dependent on the data, so that a change in its structure, or a change in the way it is stored on disk, requires major changes in the programs whose data are affected.
However, DBMS separates descriptions of application data. This is what is known as data independence, thanks to which the maintenance of the applications that access the database is simplified.
Increase in attendance:
In some file systems, if there are several users that can simultaneously access the same file, access may interfere with each other so that information is lost or integrity is lost. Most DBMSs manage concurrent access to the database and ensure that problems of this type do not occur.
Improvement in backup services:
Many file systems allow the user to provide the necessary measures to protect the data against failures in the system or in applications. Users have to make backup copies every day, and if any failure occurs, use these copies to restore them.
In this case, all the work done on the data since the last backup was made is lost and has to be done again. However, current DBMSs work in a way that minimizes the amount of work lost when a failure occurs.
Efficient monitoring and management of the Oracle database
You have already seen the daunting array of tools and components of the governance structure that are designed to monitor and manage Oracle databases. Traditionally, database administrators have used a wide variety of methods to manage and monitor databases, and until now, complaints about the need to work at night and on weekends have been common. Now you can avoid this by using the proactive approach and automated management as widely as possible – Oracle Database 12c and 11g will come to your aid!
The advice is not to reinvent the wheel using old-fashioned Oracle monitoring and management scripts. Here’s how to use a wide variety of Oracle tools with maximum efficiency.
- Make OEM Database Control or Grid Control your primary administration tool. Through it, you can have access to all monitoring tools and performance tuning. Configure the OEM to send you emails or event-based pages.
- Use RMAN as your primary backup and recovery solution.
- Configure a batch recovery area so that backup and restore can be easily automated.
- Use Scheduler to automate your job system.
- Transfer your export and import scenarios to the new Data Pump technology – both to save time and to take advantage of new tools.
- Whenever possible, use Database Configuration Assistant to create new databases and Database Upgrade Assistant (DBUA) to upgrade databases to Oracle Database 12c and 11g from previous versions.
- Let Oracle automatically collect statistics — do not bother with the DBMS_STATS package or the ANALYZE team to manually collect statistics for the optimizer.
- Remember to collect system statistics in addition to the automatic optimizer statistics collected by Oracle.
- Let Oracle automatically manage SGA and PGA using the new automatic memory management tool using the initialization parameter MEMORY_TARGET.
- Use the Oracle warning system to prevent problems with the lack of free space.
- Use the SQL Access Advisor to advise on new indexes, materialized views, and partitioning tables and indexes.
- Allow the Automated Segment Advisor to recommend objects for compression. Compressing objects will free up unused and fragmented space while reducing response time to queries.
- Use SQL Tuning Advisor to pre-configure SQL problem code.
If you want to keep your database safe then you have to keep monitoring the database.