Basics of Oracle DBA
The mechanisms of ORACLE execute by using memory structures and processes. All memory structures exist in the main memory of the computers that constitute the database system. Processes are jobs or tasks that work in the memory of these computers.
To become a fully fledged DBA, you need to have an understanding of the fundamental concepts. This article aims to provide the basics of what a DBA is and what a DBA does in their daily role.
The Database Administrator takes care of all of the security, data management and efficient running of an organisations database. Backup and recovery are also an essential part of the role. Frequently the DBA has to work out of hours in order to keep the organisations database operational.
The DBA must frequently make recommendations for improvement and future strategy. The DBA must liaise frequently with co-workers and other departments who rely on the database for information. Developers rely on the database for their working platform. Therefore it not only affects end users but developers of products and applications.
There are basic terms that DBAs must be have a knowledge to understand oracle.
When an Oracle database is running, it is associated with what is known as an “instance”. This occurs when a database is started on a server. The type of computer the database runs on is irrelevant. Oracle allocates memory to an area named the System Global Area (SGA). The processes needed to operate the database are also started. An “instance” refers to the combination of Oracle processes and the SGA. The instance memory and processes are used to manage the database data efficiently and serve one or more multiple users of the database.
Parameter files are used to provide initialisation directives for a particular installation. There are two types of parameter file.
One is Server Parameter Files, which are in binary format and persistent across and instance i.e. persist across instance shutdown and startup.
And the second is Initialization parameter files, which are text based and can be changed. Sample versions of the parameter files are provided for use as an example and so they can be changed with relevant parameters for a specific installation. The sample file is usually fine for a basic setup, but can be changed to improve performance and other settings. Once changed, the settings take effect once the database is restarted.
System Global Area (SGA):
Whilst the database is in operation, a memory area is required for all users to share such as a buffer cache and a shared pool of SQL statements. This is known as the System Global Area (SGA). The SGA is defined in memory once an Oracle instance is started. Changes to the SGA become effective the next time the database is started up.
Program Global Area (PGA):
In addition to the system global area there is another area which comes into the equation, and is intended for the processing of SQL statements and temporary areas for sorting and temporary calculations. This area is allocated for each individual database session. As for the system global area the programmer area can also be changed and new settings would take effect on a new database start-up.
Oracle indexes can be used as an optional method of speeding up access to the database. An SQL query may or may not use an index to access a table. An index provides a pointer to specific data in a table, and saves searching through a whole list of items. However, in the case of small tables or tables with very few dissimilar values an index can also cause an overhead in efficiency. A good DBA will have knowledge of optimisation techniques but this equally applies to Oracle developers.
This tool is provided for developer use mainly. It is installed with every Oracle database installation has three modes of viewing, which are command line, a windows interface, and a web-based version.