Oracle DBMS

Oracle DBMS – Database software is the main software tool for managing large volumes of information today. A database management system (DBMS) must be able to reliably manage large volumes of data in a multi-user environment, have high performance, be protected from unauthorized access and provide effective disaster recovery solutions.

In this article I would like to talk about the features of Oracle DBMS on the example of Oracle 10g, also paying attention to Oracle Real Application Cluster technology.

General information about DBMS Oracle

The Oracle Database server (hereinafter simply Oracle) provides efficient and effective solutions for the main database tools. Let’s take a short look at each of them. Oracle supports the largest databases – up to a potential size of hundreds of gigabytes. To ensure efficient control over the use of expensive disk drives, it provides full control over space allocation. Oracle supports a large number of users simultaneously running a variety of applications that operate on the same data. It minimizes competition for data and ensures data consistency. Oracle supports all the features described above while maintaining a high degree of aggregate system performance.

Database users are not affected by poor processing performance. In many cases, Oracle software must run 24 hours a day with no offload periods that limit database throughput. Normal system operations – such as database rollback and partial computer system crashes – do not interrupt the database. Oracle can selectively manage the availability of data, both at the database level and at lower levels.

For example, an administrator can disable access to a particular application (so that it is possible to reboot that application’s data) without affecting other applications. To get the most out of your existing computer system or network, Oracle allows you to share work between the database server and client applications. The entire burden of shared data management can be concentrated on the DBMS executing computer, while the workstations on which the applications work can concentrate on data interpretation and display.

Oracle software is compatible

Oracle software is compatible with industry standards, including most standard operating systems such as the Microsoft Windows NT family and various versions of Linux. Applications developed for Oracle can be used on any operating system with little or no modifications.

Oracle meets industry standards for data access language, operating systems, user interfaces and network protocols. It is an open system that protects the end customer’s investment. The Oracle server has been certified by the National Institute of Standards and Technology as 100% ANSI/ISO SQL89 compliant.

Oracle fully complies with the US government standard FIPS127-1 and has a marker to highlight non-standard SQL applications. In addition, Oracle has been assessed by the Government National Computer Security Center (NCSC) as being compliant with the Orange Book security criteria; Oracle and Trusted Oracle meet both C2 and B1 levels of the Orange Book, respectively, as well as the comparable European ITSEC security criteria. Oracle is a classic relational DBMS that uses a client-server mechanism.

An important advantage of DBMS Oracle

An important advantage of DBMS Oracle is the confirmation batch processing mechanism. The transactional mechanism allows not being afraid of software and hardware failures. In case of successful completion of the SQL command package processing, the database server generates the so-called commit – confirmation of successful execution, which is sent to the client. In the case of hardware or software failure, rollback – return to the previous checkpoint, so that the integrity of data in any situation will not be compromised.

To be able to cancel a whole set of transactions, there is a mechanism of save points, which can be created manually or automatically. Thus, there is no need for permanent archiving of the database, which can be done only a few times a day, and in other cases, the administrator simply restores the save points. DBMS Oracle uses SQL – a simple, powerful database access language, which is standard for relational DBMS.

SQL implemented by Oracle for Oracle is 100% compliant with the ANSI/ISO standard of the SQL language. In addition, Oracle has implemented an extension of standard SQL functions called PL/SQL. PL/SQL combines ease and flexibility of SQL with procedural possibilities of language of structural programming – such as IF…THEN, WHILE and LOOP.

Availability of a huge number of tools for developing applications interacting with Oracle DBMS, among which there are such well-known solutions as Microsoft Visual Studio, Borland Delphi and JDeveloper, allows most programmers to learn how to write applications using Oracle as a database storage without special time expenditures.

Support of ODBC, BDE and JDBC mechanisms allows migration between different software and hardware platforms with minimum expenses on software modification.

Due to the complete unification of all Oracle DBMS versions in the same network, different Oracle versions for different platforms can work – for example, the database server can have the Oracle 8 for Unix version and the clients can have the Oracle for Windows version, with no interaction problems.

All of the above makes it possible to use Oracle DBMS for automating almost any process where large volumes of data are handled – from the simplest workstations of a small enterprise to nuclear reactor control systems and particle accelerators.

Oracle Real Application Cluster (RAC) technology

High efficiency and availability of information systems is a key requirement for the daily work of a company or scientific laboratory. The growing dependence on stored information over the last couple of decades has led to the accumulation and analysis of large volumes of data.

Requirements for highly efficient databases are constantly growing, while awareness and requirements to maintain such databases in an operational manner are also increasing. With uneven and unpredictable database system loads in many business groups, finding high-performance systems and suitable parallel systems to support complex and large databases has become critical.

Another important property is scalability. As the local area network grows, enterprises are increasingly accumulating and interacting data, and more users and applications are beginning to use database systems that are required to meet the growing demand for data without losing performance or availability.

To address these challenges, Oracle9 has support for Real Application Clusters (RAC) technology. RAC supports a disk partitioning model, and therefore has access to all shared drives and an extensive resource coordination mechanism across multiple nodes. Drive partitioning technology has progressed rapidly over the past few years, providing additional benefits to RAC.

Storage Area Network (SAN) technology hides many of the complexities of hardware modules, controllers, disk drives and server-to-server connections, leaving only storage volumes on the surface. In the same way, a group of servers in a cluster provides a single system image and computing resource. All these advances in hardware only underscore the undeniable success of RAC. At a very high level, RAC represents multiple Oracle instances accessing a single Oracle database.

A database is one physical database stored on a system with shared memory. Each instance is permanently located on a separate host (also called a “host” or “server”). All nodes are gathered in clusters through a private connection and have access to shared memory.

All the nodes simultaneously execute transactions in the same database. As a rule, the cluster manager software vendors provide a single system image, manage the node members and control the node state.

The main components include:

• Nodes/servers
• High-speed private interconnection
• Cluster Manager or Operating System Dependent Layer (OSD)
• Split disk or memory
• Cluster file system or transparent device
• Volume manager
• Public network.

RAC

RAC – database with multiple copies. Multiple instances access the same database in parallel. The structure of an RAC instance is not so different from an offline Oracle instance. Apart from all the usual Oracle processes such as PMON, SM0N, LCWR and BVVR, there are many special processes that are generated to coordinate instance communication and facilitate resource sharing between cluster nodes.

Buffer movement between instances and a new set of Past Image Blocks (preserving data integrity) lead to the use of additional resources from SGA.

LMON

LMON – Global Enqueue Service Monitor (LMON) controls the entire cluster, providing global queue and resource management. LMON manages process and instance validity and recovery for the Global Cache Service. LMD – Global Enqueue Service Daemon – LMD is a locking agent process that manages service manager requests for Global Cache Service queue access to global queues and resources. The LMD process also handles detected interlocks and remote queuing requests.

LMSn

LMSn – These Global Cache Service Processes (LMSn) are processes for the Global Cache service (GCS). The RAC software supports up to ten Global Cache Service processes. The number of LMSn varies depending on the amount of message traffic between cluster nodes.

GCS and GES

GCS and GES – provide a single system image of data, even if multiple instances of data are accessed. GCS and GES are integrated components of Real Application Clusters that coordinate simultaneous access to the shared database and shared resources in the database and database cache.

GES and GCS together support the Global Resource Directory (GRD) to record resource and queue information. The GRD remains in memory and is stored in all instances. Each instance manages a portion of the directory. The distributed nature of management is critical to RAC resilience.

Benefits of Oracle Real Application Cluster

Oracle RAC offers a number of benefits in two areas: increased database performance and increased fault tolerance. Fault tolerance in this case means the ability to smoothly service client applications in the event of a hardware or software failure of one or more nodes of the cluster, called nodes.

Thus it means normal work of the general disk array which can be realised as the network hard disk using mechanism iSCSI, or a disk rack. When connected to RAC, the database client communicates with the listener_name of the database rather than a single listener.

In this case the client receives a list of IP addresses of all nodes in the cluster, and this list will be updated if new nodes are enabled. In case the connection to the current node is broken and the connection fails again, the client establishes a connection to the next node in the list, and this happens until the working node is found.

In this case, the client will also take into account the load on each node, choosing the one that will handle the test request the fastest (this test is optional and should be enabled by the database administrator).

In this way, Oracle RAC will remain operable as long as at least one work node remains in the cluster. Oracle RAC has the concept of a general node, but it only applies to administrative tools. As a rule, the general node is the database administrator’s workstation, from which the cluster is managed. Failure of the general node does not stop the entire cluster.

Hence, there is another important advantage of Oracle RAC – the possibility of decentralization. Let’s assume that there are several powerful workstations in the enterprise or in the laboratory, the computing power of which is not fully used.

If you connect these workstations using a local area network, you can get a highly fault-tolerant database. In this case, if the load on one or more workstations increases on the part of direct users, there will be a gradual decrease in the load on these workstations from the database until the customers are completely disconnected.

In this way, it is possible to achieve a rational use of computer technology and increase the availability of information for employees of the organization.

What’s the reason RAC could go out of business? It can be caused by any bad design or choice. Database maintenance includes many components. In addition to the database itself, RAC can be enabled and run, but not available to customers.

There are intermediate network components between the client machines and the database servers. A failure may occur in them. Natural disasters destroying the hardware – for example, a fire, flood or earthquake – can disable the cluster and the database.

However, if the failure is localized, the RAC property ensures maximum security and continuous database maintenance. Even if many components are lost, a cluster with RAC can still function. But this requires over-development of all the components it contains. The key word here is “development”.

It is not enough to simply install two or more nodes; a reliable Real Application Cluster requires double interconnections, double memory block paths, double power supply, double public network interface, etc. As long as one of the Oracle instances is available in the cluster, client applications have access to data and can run without problems.

Managing Oracle using Oracle Enterprise Manager

One of the core components of Oracle Enterprise Manager (OEM) is the central operator console. Sitting behind this console, the database administrator sees all the DBMSs he or she administrates, even though they are hosted on different computers that can be scattered across different buildings, cities and countries.

He can monitor the status of these DBMS and perform various administrative actions with each DBMS or DBMS group without leaving his workplace. If necessary, he can do it all via the Internet. Apart from DBMS, the administrator can monitor from this console the network nodes (computers), listensers (listeners), application servers (Oracle application servers), Oracle Developer Server, ERP applications (SAP/R3, Oracle Applications) and of course Oracle RAC.

OEM consists of 3 components:

• central consoles run by administrators;
• management servers that implement the entire OEM logic;
• intelligent agents (Intelligent Agents) working on the nodes where the database is located and performing tasks there on behalf of management servers.

To work effectively with Oracle Enterprise Manager you must configure Oracle Management Server, otherwise many useful OEM features will not be available. You should use the Enterprise Manager Configuration Assistant to do this.

This easy to use wizard will allow you to configure the OMS even for inexperienced administrators. The OEM allows you to execute not only those commands that are executed by an instance of Oracle, but also operating system commands, database start and stop.

Therefore, every managed node must have a service that is not bound to the database state. This role is executed by the intelligent agent. It can execute scripts, start the database, execute operating system commands and control the occurrence of ordered OEM events.

Moreover, the execution of these works can take place at predetermined moments of time or at a certain periodicity, and the result will be passed to the managing server when it will have a connection with the agent.

Together with OEM, its web-variant can be installed. It does not require any additional configuration. The administrator simply runs the simplified version of the application server installed on the OEM computer and can work with the console via the Internet/Intranet from any computer with a web browser (however, the first time you need to download and install the Jinitiator package).

Through this web-interface all functions of OEM and DBA Management Pack are available. OEM and all its modules have a graphical interface. Most work is done with the mouse. The helpers (Wizards) help to perform complex operations. They break up a complex operation into parts and engage in a dialogue with the DBA, explaining what to do and how.

This way, even a less experienced administrator can perform complex tasks. In addition, the OEM also trains the Database as it works. You can always look at the text of the scripts and SQL statements it generates and understand what will be done and why.

The OEM includes a set of predefined tasks – such as data analysis, export/import, data upload, copy/recovery, database start/stop, SQLPLUS and Server Manager commands. By changing the parameters of these tasks, you can perform a wide set of functions.

Besides, the stages of one task can be interconnected – for example, they can be executed only if the previous task stage was successfully or unsuccessfully executed. Jobs can be started not only by time, but also automatically when a fixed OEM event occurs in the database or on the node. These are the so-called Fixit jobs.

For example, if the event “There is less than 1 Mb of free space left in xxxxx table space” occurs, the Fixit job will be automatically started increasing this table space. No administrator intervention is required here. He will only be notified when the job is done. In Oracle mode, the RAC OEM can consider the cluster as a single machine, performing maintenance operations on all nodes of the cluster at once.

In this case, if a problem occurs on one of the nodes, the administrator will be able to switch to viewing the Oracle Management Console on the cluster nodes and only work with the “problem node”. Thus, if set up correctly, OEM becomes almost an ideal tool when the database administrator is proactive.

OEM will warn of events before they occur, and the administrator will not have to react in a firefighting manner to constantly occurring problems. In this case the number of problems will be reduced and error probability will be minimal. The benefits of using this tool are clear. It will reduce system downtime and improve performance.

Xpert Quest TOAD as a tool for developing applications that interact with the Oracle database

Quest Toad is the best tool for Oracle DBMS development and administration today. Applying Toad to manage database objects, the user does not need to be qualified as an expert, it has a simple and clear graphical interface that allows you to create and fill tables even without knowledge of SQL.

However, when performing any action, a novice database programmer can view the SQL code executed during certain operations, which allows both working and learning the SQL language. Quest TOAD consists of the following basic modules. Schema Browser module in TOAD allows you to quickly view and manage data dictionary. By clicking on a selected object, the user instantly gets detailed information, bypassing the long hierarchy of objects storage. In the same window, you can manage all objects.

Powerful Toad editors increase developer productivity, eliminate errors and significantly reduce development time. Editors allow users to work simultaneously with source codes in several languages (SQL, PL/SQL, HTML, Java) or with text.

Replacing the traditional way of executing a query at the command line or from a script with a graphical interface, Toad provides a fast and user-friendly development environment that can be easily configured to meet user preferences. SQL Editor improves development productivity through a large number of hotkeys, autocorrection functions, advanced input and color syntax highlighting:

• Convenient bookmarks allow developers to quickly move between multiple areas of program code.
• A full-featured toolbar makes editing and testing easy.
• Pop-up selectors to select table names, column names and function/keyword names.
• Procedure Editor allows users to work with multiple files in parallel using SCC-compatible version control.
• Several objects can be compiled at the same time with a consistent compilation of all dependent objects.

Toad editors are tightly integrated with the PL/SQL Debugger, allowing users to test only certain areas of procedures, execute only the current operator, several operators behind the cursor or only operators before the cursor. SQL Modeler is a tool for quick and easy query building. It is enough to move tables to SQL Modeler, and the module will automatically generate an SQL query.

The convenient integrated environment allows you to specify query criteria, test automatically generated SQL queries, view execution plans and query results, save expressions or copy them to the editor. Using SQL Modeler, even inexperienced users can quickly create complex queries at the expert level.

The PL/SQL Debugger module provides a convenient environment for debugging applications, saving time for developing and testing large projects. By integrating the debugger with Procedure Editor, developers can edit and debug stored procedures line by line – the way they are executed on the server.

The debugger fully controls the execution of the application, setting the monitoring of any number of variables with viewing and changing their values during the execution. Normal execution of the application in this case does not change, thus eliminating the need to write error handlers. Moreover, a debugging session can be executed while programs are running in other sessions.

Toad allows developers to avoid tedious PL/SQL debugging tasks, saving valuable time and improving overall application quality. In addition to all of the above, Quest TOAD can import and export the database structure or all of its contents. Thanks to this ability, Quest TOAD allows you, for example, to convert an existing database into a cluster version with minimal time consumption.

Oracle RAC in action

Solutions based on Oracle DBMS are actively used to automate document flow and science-intensive physical experiments with large amounts of data coming from experimental facilities. Coordination of joint work of a considerable number of scientists (for today about 6500 persons) distributed worldwide, is connected with considerable administrative expenses for which reduction in CERN the corporate system of electronic document circulation (EDH) has been developed and introduced.

The EDH system automates almost all aspects of CERN business activities, including procurement, payments, vacations, business trips, personnel issues, work security, information services, logistics, training requests, etc.

The system performs data verification and automatic passage of documents to all necessary addresses, based on the structure of the organization and nature of documents. Safety and confidentiality of the data is guaranteed by application of the newest workings out in the field of protection of the information and the electronic-digital signature.

EDH system is written in the high-level programming language Java with the use of corporate application development technologies (Java 2 Enterprise Edition – J2EE). The documents are presented in EDH as electronic forms accessible from anywhere in the world through World Wide Web environment. In this case, all business logic is on the server side, and the client is implemented in the HTML (so-called “thin client”) hypertext markup language.

In the process of creating EDH, the concepts used in CERN business processes (“employees”, “cost codes”, “departments”, etc.) were formalized and object libraries for their storage, processing and I/O were developed. All the data is stored in the Oracle relational DBMS, which is accessed via the JDBC (Java Database Connectivity) interface using the SQL (Structured Query Language) language. The basic infrastructure allowing to quickly automate any business processes was developed and implemented.

This infrastructure implements functions common to all types of documents – such as I/O, access control, data integrity checks, functions for saving and sending documents. Routing of electronic documents is implemented with the help of Oracle Workflow system, for which business processes are first formalized with the help of Petri networks.

The developed EDH architecture has been successfully exploited since 2000. During this time more than one million electronic documents have been created and routed in the system. A new accelerator, the Large Hadron Collider (LHC), has been under construction at CERN since 1996.

The LHC is capable of colliding proton beams with the energy of 14 TEV in a 27 km long tunnel. The LHC is the largest superconducting plant in the world with an operating temperature of -270°C. During the construction of the LHC, an EVM system was created to reduce commissioning costs.

The software application developed at CERN earlier in Oracle Designer was adapted to the EVM system. This application has been ported to the Java Enterprise Edition platform with the central Oracle database. A lightweight architecture with a clear separation between model, view and controller was used, as the original architecture of the EJB (Enterprise Java Beans) standard was considered too heavy for this task.

The LHC design engineers were tasked with formatting the central work units so that each task would last no longer than three months. This resulted in the identification of 12,000 work units. Since identifying a large number of interrelated work units via the web interface presents certain difficulties, the EVM system included a module for importing/exporting data into a Microsoft Excel spreadsheet.

For the operative analysis of transactions (Online Transaction Processing (OLTP) and operative analysis of data (Online Analytical Processing (OLAP)) the automated module of total data processing has been developed and introduced, allowing project managers to receive in a real time mode the information on a course of works and providing the analysis of this information in various sections.

Since access to the EVM system was intentionally open to all project participants, it became necessary to track the changes made. For this purpose, all reports on results, both made through the web-interface and received from Excel, are grouped in transactions and recorded in a central database.

Each transaction can then be visualized and if necessary cancelled. The EVM includes an event processing system that monitors the impact of transactions on the plan and costs and notifies the required project participants on a subscription basis.

Switch to new versions of Oracle DBMS

The company’s migration policy does not raise any questions. Since the procedure of migration from an outdated database version to a newer one is a time-consuming process due to testing the work of existing programs in an updated environment, the company, releasing new versions of products, pays great attention to the compatibility of old and new versions.

This makes the transition completely painless. If you need to transfer information from DBMS of other manufacturers to DBMS from Oracle, the company can offer free tools for implementing this procedure. This tool has a friendly user interface and will help you perform the whole data transfer procedure step by step.

The versions that have been released recently are very easy to install. Initial installation is not difficult either. For a specific task, the possibilities for special settings have also increased. And with them you can achieve stunning results when working with the information storage.

Oracle DBMS Versions

The database management system from Oracle comes in four different versions, among which is even a lightweight mobile version, primarily designed for low-power desktop computers. However, each server variant is based on the same software code and is identical in its functions to others. There may be only a few options that are available only for a certain version and are not included in another control system variant.

Enterprise Edition

Enterprise Edition is a full-featured DBMS. Its capabilities are limited only by hardware specifications. This version, in fact, has all the newest developments responsible for secure storage, processing and presentation of data to the user.

A fairly wide range of scaling capabilities ensures uninterrupted work of database servers around the clock, every day, every day of the year. Well developed and backup functions. With their help, you can completely eliminate the risk of losing very important information.

Standard Edition

What is Oracle: Standard Edition? It is a control system that has a slightly reduced functionality compared to the previous version. And it’s reflected in cost. This version is installed on servers that only support up to four processors.

This version is the best solution for deploying an information system in a small organization, a workgroup or a single division of a large enterprise. Due to the fact that the distributed type processing tools are used and the data is replicated, the DBMS can be used to support work in such organizations that have many remote branches.

Personal Edition

The personal version of Oracle DBMS is a database intended for one user. It is used for training purposes and when developing and using programs for classic versions of the operating system from Microsoft. This version includes all features of the Enterprise version. But for the operating systems Windows 95 and 98 they are limited because of the impossibility of their implementation, as the old platforms do not know much.

Lite Edition

Lite is the lightest, mobile version of database management system that allows you to synchronize information stored on weak computers and mobile devices with the corporate database. It can be accessed through a standard interface. This allows you to make programs the usual development tools.

So, we can say that the Java and Oracle virtual machine is very simple. With the help of this bundle, you can manage huge databases with great comfort, and most importantly – safely.

DBMS Oracle – what is this program?

After the above, this question should disappear. It is the best choice both for serious organizations and for small firms. By the way, knowledge of this technology is a huge plus in the moneybox of a job seeker as a programmer. Job requirements often specify knowledge of Java, Oracle. What does it mean? All developers are somehow connected with databases, because all programs work with this very data.

Oracle SQL – Quick overview of Oracle RDBMS Architecture