Development and administration of databases. Oracle Courses Basic Concepts and Abbreviations

Hello my readers. The article is intended for beginners in the field of Oracle databases. For those who are just planning to study this subject area and become Oracle DBMS administrators. So where to start??? Let me note right away that there is no ideal initial level of training to become an Oracle database administrator, but it is highly desirable that there is a true interest in the hardware side of databases, as well as a decent amount of knowledge on operating systems, UNIX and NT servers, as well as issues related to disks and memory.

Having programming or development skills also helps a lot, because you will often have to work with developers. The most commonly used operating system for Oracle databases is the UNIX operating system, namely versions of UNIX produced by Hewlett-Packard (HP) and Sun Microsystems (Sun). IBM supplies the AIX variant of the UNIX operating system, but offers its own proprietary database product called DB2 Universal Database.

If you want to train as a professional Oracle Database 11g database administrator, you need to take the following two training courses from Oracle or some other supplier: Oracle Database 11g:

Administration Workshop I (Oracle Database 11g: Administration Workshop I);
Oracle Database 11g: Administration Workshop II (Oracle Database 11g: Administration Workshop II).

Total exist three levels of certification for Oracle database administrators. The first involves obtaining the OCA (Oracle Certified Associate) certificate, the second - the OCP (Oracle Certified Professional) certificate, which is most often sought after by people professionally involved in Oracle databases, and the third, the latter is an OCM (Oracle Certified Master) certificate, which requires passing a long two-day practical exam.

All candidates for the Oracle Database 11g DBA certification are required to take one physical or online course from a list of approved courses to meet the new practical course requirement. Those whose company uses RAC clusters (Real Application Clusters) real applications) or Oracle distributed databases, must take additional, specialized training courses. For those who use the UNIX operating system in their company and have no experience with it, it may be better to start by going through basic course on UNIX (or Linux) from HP, Sun, Red Hat or another manufacturer.

This course is not required to obtain an Oracle Database Administrator certification, but it will certainly be useful for those who are new to the UNIX or Linux environment. Oracle itself also offers several administration courses Linux systems, and even the opportunity to obtain a certificate for managing Oracle in Linux as part of Oracle programs Certified Expert Program. Of course, those who plan to use Oracle databases in Windows environment, can easily do without taking a long and formal course in Windows management provided that you are fairly familiar with the operating room Windows system, or even work as a Windows system administrator.

On a note! We should not forget that Oracle Corporation is not the only company providing Oracle training. Although Oracle University is a large institution with excellent courses, other private providers offer equally, and in some cases, even more excellent courses. As is the case with all courses, the quality of teaching directly depends on the experience and communication skills of the teacher. And also, we should not forget that going somewhere to seminars is not at all necessary: it is quite possible to purchase self-learning CDs and study on your own, and several (about 15) times cheaper than physical training with an instructor. Even more effective may be to subscribe to Oracle's online training program called Oracle iLearning (http://ilearning.oracle.com). It's cheaper than buying DVDs and gives you access to hundreds of Oracle University courses. If you decide to take these courses, you must be sure to have access to the server with the actual database. Oracle provides very well-designed sample schemas that you can use to hone your SQL skills, either with its own development-stage database on a UNIX server or with the free downloadable Windows version of Oracle Database 11g Enterprise Edition on desktop computer. With this approach, learning will happen much faster.

As you begin working as an Oracle Database 11g DBA, you will discover that the real world of Oracle databases is much broader and more complex than what was covered in the various courses you attended. As each new facet of the database is discovered, it will become clearer and clearer how the software works, why it works, and why it sometimes doesn't work. Only then will it be possible to learn more about databases and the software used to manage them. Once you've read all the material that Oracle and others have to offer, you still shouldn't worry, because there are always new versions coming out, with new functionality and new mechanisms, which virtually guarantees an endless supply of new information.

After working as a database administrator for one or two years, you will already know enough to competently administer databases and troubleshoot common database problems. Those who also continue to improve their programming skills during this time (primarily through writing UNIX shell scripts and working with PL/SQL) can even begin to create complex scripts for monitoring and tuning database performance. Those who move on will then be able to learn much more about their database software and thereby increase their knowledge and their contribution to the organization. Oracle is constantly releasing new tools that you can use to improve the performance of your production databases. Although developers, testers and system administrators are also trying their best for the benefit of the organization, it is the database administrator who will ultimately pave the way for new and effective ways application of new database capabilities.

This article is intended primarily for Oracle DBMS administrators (and those wishing to become one); it will be very useful for application developers, as well as for users of this complex system. An exciting journey awaits us all in the world of Oracle, and I will help you not to get lost in it.

We will start with the very basics, with the simplest things and gradually move further and further. I’ll warn you right away - it won’t be easy. But are you ready to share the difficulties with me? If so, then go ahead!

Why will this be difficult for you? Because you need to not just skim the text, not just enjoy the reading process, not just be satisfied with reading abstruse material, but WORK above the text. Exactly WORK. I will often describe complex relationships, but if you do not depict them on paper, if you do not study each point, then a gap will remain. The gaps will accumulate until huge voids form. Eventually, you will ask yourself, "What have I learned?" I think you already guessed what the answer will be.

Set a goal for yourself - to “understand EVERYTHING”; if something turns out to be incomprehensible, it’s most likely my fault. Write to me, and in the next issues we will look at unclear questions in much more detail.

Why will this be difficult for me? The fact is that I will also learn along with my subscribers. And since I am responsible for your progress forward, since I will have to answer questions and collect material, then I simply have to go one step forward. And while you are studying the current issue, I will need to prepare the next one.

As I promised, we will start with the simplest.

A Brief History of ORACLE.

In 1977 Larry Ellison, Bob Miner, and Ed Oats formed their own business, calling the company Relational Software Incorporated (RSI). It was this company that laid the foundation for the Oracle relational database management system (RDBMS). Ellison, Miner, and Owets decided to develop an RDBMS using the C language and a SQL interface. And soon the first version (prototype) was released. Buyers in 1979 Oracle RDBMS version 2 was introduced, which ran on a Digital PDP-11 running RSX-11 OS. It was then ported to the DEC VAX system.

1983 became the herald of the release of version 3, which brought changes to the SQL language, increased system performance and added some other improvements. Unlike the previous ones, the third version was written entirely in C. From that moment on, RSI changed its name to Oracle Corporation.

Oracle version 4 was introduced in 1984. This version supported both VAX OS and IBM VM. This version provided the ability for multi-user stable data reading. Version 5 appeared in 1985. and became a turning point in the DBMS market, as it was the first to introduce client-server technology using SQL*Net. The fifth version was also one of the first MS DOS programs, have crossed the 640Kb barrier.

In 1988 Oracle introduced version 6. This version introduced low-level locking and many other changes that increased performance and functionality (including sequence generation and lazy writes). Oracle already runs on many platforms and on different operating systems. In 1991 Oracle RDBMS Parallel Processing Server version 6.1 has been released for the DEC VAX system. Soon this version began to support other platforms.

Oracle 7 was released in 1992, and many architectural changes were made in the areas of memory and I/O operations. Oracle 7 is already a full-fledged RDBMS product, which users are accustomed to and has been used for many years.

In 1997 version 8 was released, which introduced an object model, new properties and administration tools.

In 1999 version 8i (Oracle 8.1.5) with built-in Java language was released.

In 2001 version 9i came out. According to the developers, more than 400 changes have been made compared to previous version. Characteristic changes - "intellectualization" automated systems and expanding analytics capabilities.

As you can see, the Oracle product is already 25 years old, and we have to make up for all these “lost” years in a much shorter period of time. Latest version The product includes 75 different server products, but most of them are beyond the scope of our course.

Basic concepts and abbreviations

Before we begin our study of Oracle, it is important that everyone is clear on the terms that will appear in the text. Each issue of the newsletter will have a "Basic Concepts" section so that readers do not waste their time looking for definitions of unfamiliar words.

DB- Database. A collection of data specifically organized to make it easy to retrieve. The database is the actual data.

DBMS- Database Management System. Software Oracle is a DBMS.

RDBMS- Relational Database Management System. Internal data access is carried out in a relational way. Oracle is a RDBMS.

Buffer- this is a certain volume random access memory, used to store data. The buffer contains data that is about to be used, or that has been recently used. In most cases, a buffer is a copy of a block of data that is stored on the hard drive. Data in a buffer can be modified and written to disk, and a buffer can also be created for temporary storage of data. In relation to Oracle, buffers contain those blocks of data that were recently accessed. The collection of buffers makes up the data buffer cache. The buffer also stores temporary activity log entries, which are then written to disk (the activity log buffer).

Cache- memory area for quick access to data. From a hardware point of view, this is a small amount of memory (in terms of RAM) that is significantly faster than main memory. This amount of memory is used to reduce the time required to frequently load data or instructions into the CPU(CPU). The CPU itself contains a built-in cache. In Oracle, a cache is considered to be a set of buffers (that is, blocks of data in RAM) and a shared pool (shared pool), since they serve to store data and instructions that facilitate fast access. Caching is a very useful mechanism that significantly increases the speed of data access. Since it is usually not possible to fit all the buffers in RAM, special algorithms are used (the most common is storing the most frequently used blocks).

Block- the smallest data storage unit in the Oracle DBMS. Contains header information and the block itself (data or PL/SQL code). The block size is configurable from 2 to 16Kb.

Bottleneck- components that limit the performance or efficiency of the system.

Data Dictionary- a set of tables used to maintain information about the database.

Checkpoint- an operation that causes all changed data (data blocks in memory) to be written to disk. This is a key factor in the problem quick recovery database after a failure.

Schema- a collection of database objects.

SGA (System Global Area)- a shared memory area used to store data and control information of an Oracle instance. The SGA is allocated in memory when the Oracle instance starts, and is released when it shuts down. SGA consists of data buffers, a change log buffer, and a shared pool. This is one of the most important concepts and we will look at it in more detail later.

ORACLE Configurations

There are many types of configurations. Let's look at the main ones, analyze and determine their characteristics.

OLTP (Online Transaction Processing)- prompt processing of transactions. This is the most common configuration. An OLTP system consists of users who interact with the system. These systems are usually used for prompt entry of primary information (filling out contracts, checking numbers credit cards, asynchronous transactions, etc.).
Characteristics of OLTP systems: Typically supports a large number of users running the RDBMS. Since users wait for data to be returned to requests, response time is of great importance. OLTP systems are read-write intensive. The read-write rating may vary depending on the application.

DSS (Decision Support System)- decision support systems are used in decision-making processes. These decisions can be based on information such as sales intensity in certain regions, a sample of buyers for a particular product, a sorted list email addresses and etc.
Characteristics of DSS: Long-running queries versus large amounts of data. Users of DSS systems are forced to wait minutes, hours, and sometimes several days for a response to a request. Data is usually collected from various sources and then processed. The DSS system is associated with an intensive read process (write processes
occur much less frequently).

Data Warehouse is a large-scale system that consists of both OLTP and DSS. These systems typically handle hundreds of gigabytes of data and serve huge numbers of users.
Characteristics of a Data Warehouse: has some attributes of DSS systems, i.e. long-running queries, as well as real-time components. These components are often used as data sources for DSS queries.

Data Mart is a smaller version of the data warehouse (focused on solving highly specialized problems), while retaining many of the features of Data Warehouse.
Characteristics of the Information Shop: Typically 100 gigabytes of data or less. Just like a data warehouse supports a large number of users and allows you to generate complex solutions.

Video server: Allows you to support a large number of video streams. These video streams can be used on demand, for entertainment and as educational courses.
Characteristic features of a video server: Must have high bandwidth to support multiple video streams. Also, must be able to handle heavy I/O load. When reading from devices, large blocks of data that are little fragmented are loaded at once.

Web server: designed to work with static and dynamic web pages. These pages can be very simple or complex, generated from a database. Oracle Web Server is typically used for commercial web applications. Such
apps allow shoppers to browse catalogs that contain product images and even video illustrations. The buyer can purchase the product he likes.
Characteristics of Oracle Web Server: usually supports a significant number of users, contains a large amount of data that is accessed frequently, and, at the same time, data that is not accessed very often. A large amount of RAM can improve server performance.

OLAP(Online Analytical Processing)- analytical processing in real time. Typically used in conjunction with multidimensional data. OLAP users are financial analysts or marketing personnel who work with data at a global level.
Characteristic features of OLAP systems: require large amounts of disk memory and powerful computing systems. An OLAP system can only support a small number of users. However, the number of users depends on the specific configuration.

Conclusion

So we met Oracle history Corporation, learned several important concepts and learned the most important configurations. You can congratulate yourself - you have begun your immersion in the wonderful world of Oracle.

As I promised, we will start with the simplest.

A Brief History of ORACLE.

In 1988 Oracle introduced version 6. This version introduced low-level locking and many other changes that increased performance and functionality (including sequence generation and lazy writes). Oracle already runs on many platforms and operating systems. In 1991 Oracle RDBMS Parallel Processing Server version 6.1 has been released for the DEC VAX system. Soon this version began to support other platforms.

In 1997 version 8 was released, which introduced an object model, new properties and administration tools.

In 1999 version 8i (Oracle 8.1.5) with built-in Java language was released.

In 2001 version 9i came out. According to the developers, more than 400 changes have been made compared to the previous version. Characteristic changes are the “intellectualization” of automated systems and the expansion of analytics capabilities.

As you can see, the Oracle product is already 25 years old, and we have to make up for all these “lost” years in a much shorter period of time. The latest version of the product includes 75 different server products, but most of them are beyond the scope of this course.

Basic concepts and abbreviations

DB- Database. A collection of data specifically organized to make it easy to retrieve. The database is the actual data.

DBMS- Database Management System. Oracle software is a DBMS.

RDBMS- Relational Database Management System. Internal data access is carried out in a relational way. Oracle is a RDBMS.

Buffer- this is a certain amount of RAM used to store data. The buffer contains data that is about to be used, or that has been recently used. In most cases, a buffer is a copy of a block of data that is stored on the hard drive. Data in a buffer can be modified and written to disk, and a buffer can also be created for temporary storage of data. In relation to Oracle, buffers contain those blocks of data that were recently accessed. The collection of buffers makes up the data buffer cache. The buffer also stores temporary activity log entries, which are then written to disk (the activity log buffer).

Cache- memory area for quick access to data. From a hardware point of view, this is a small amount of memory (in terms of RAM) that is significantly faster than main memory. This amount of memory is used to reduce the time required to frequently load data or instructions into the central processing unit (CPU). The CPU itself contains a built-in cache. In Oracle, a cache is considered to be a set of buffers (that is, blocks of data in RAM) and a shared pool (shared pool), since they serve to store data and instructions that facilitate fast access. Caching is a very useful mechanism that significantly increases the speed of data access. Since it is usually not possible to fit all the buffers in RAM, special algorithms are used (the most common is storing the most frequently used blocks).

Block- the smallest data storage unit in the Oracle DBMS. Contains header information and the block itself (data or PL/SQL code). The block size is configurable from 2 to 16Kb.

Bottleneck- components that limit the performance or efficiency of the system.

Data Dictionary- a set of tables used to maintain information about the database.

Checkpoint- an operation that causes all changed data (data blocks in memory) to be written to disk. This is a key factor in the problem of quickly recovering a database after a failure.

Schema- a collection of database objects.

ORACLE Configurations

There are many types of configurations. Let's look at the main ones, analyze and determine their characteristics.

OLTP (Online Transaction Processing)- prompt processing of transactions. This is the most common configuration. An OLTP system consists of users who interact with the system. These systems are usually used for prompt entry of primary information (filling out contracts, checking credit card numbers, asynchronous transactions, etc.).
Characteristics of OLTP systems: Typically supports a large number of users running the RDBMS. Since users wait for data to be returned to requests, response time is of great importance. OLTP systems are read-write intensive. The read-write rating may vary depending on the application.

DSS (Decision Support System)- decision support systems are used in decision-making processes. These decisions can be based on information such as sales intensity in certain regions, a sample of buyers for a certain product, a sorted list of email addresses, etc.
Characteristics of DSS: Long-running queries versus large amounts of data. Users of DSS systems are forced to wait minutes, hours, and sometimes several days for a response to a request. Data is usually collected from various sources and then processed. The DSS system is associated with an intensive read process (write processes
occur much less frequently).

Conclusion

So, we got acquainted with the history of Oracle Corporation, learned several important concepts and learned the most important configurations. You can congratulate yourself - you have begun your immersion in the wonderful world of Oracle.