What is Oracle? Oracle Corporation are the developers of the Oracle database. They provide applications and consulting services and are involved in a range of other developments of relevance to database users. This article will provide you with a grounding in database essentials and the current Oracle database release as well as describing some of the other aspects of the company. The Database Every organisation has at least some need to store and process data. Early systems stored data in files as a conventional or flat file database. The database developer had to create their own set of routines to access and manage the information being stored. Modern systems use a Database Management System (DBMS) which provides a means to keep information safe and easily accessible. Applications for the DBMS can be developed relatively quickly and cheaply because the DBMS will handle storing the data while the developer concentrates on the business processes to be fulfilled. Changes to requirements may also be implemented easily. A DBMS is a general purpose tool, having no knowledge at the outset of what data will be stored in it or how the data will inter-relate. It provides functions to define data and access it along with multi-user security management, transaction control, resource management and backup and restore facilities. Tools to develop and maintain applications using the DBMS are also usually provided. There are three main types of DBMS: Hierarchical, Network and Relational. Oracle is an example of the last type. Simply, the first two types store what are in effect physical links between data items, an efficient but inflexible approach. For example there would be a direct link between a part and an order for that part. In a relational DBMS (RDBMS) relationships are not stored but are created by joining data based on a common value. For example, a part number might be stored in both the part details and the order for the part. The common value (part number) acts as a logical link between both sides (part and order) in both directions. In theory a RDBMS cannot maintain the integrity of the data directly (e.g. delete a part and all the orders for it become invalid) but in practise the RDBMS will provide features to take care of this. RDBMS products are typically used for on-line transaction processing (OLTP), on-line analytical processing (OLAP) and data warehousing. OLTP systems work with operational data, the information used to run the business on a day to day basis. This could include accounts, stock control, ordering and reservations systems. A data warehouse contains historical and current snapshot informational data which may have been collected from a wide variety of operational systems and then been transformed in to a format which is easier to analyse. A RDBMS stores data two dimensionally in rows and columns. OLAP tools enable multi-dimensional analysis of the data stored to make it easier to spot trends or extract other information without users having to deal with complex reports. The multi-dimensional structures may be implemented in a number of ways. Another related term which also appears is that of a data cube which is another method of arranging data for rapid analysis, possibly using a more specialised database. The major vendors of RDBMS products are Oracle, IBM, Informix, Ingres, Sybase and Microsoft. The RDBMS is administered by a Database Administrator (DBA) who should have a detailed knowledge of relational database design as well as the specific product in use. Implementation From a development point of view, Oracle uses a number of logical databases to store information. These logical databases are independent of how Oracle actually stores the data on disk. The design of the logical database will include a number of entities which represent real or abstract objects, e.g. a customer or a part. Each entity will have a number of attributes which are pieces of information about the entity such as a customer phone number or a part number. When implemented, entities become tables, attributes become columns and entity occurrences become rows. Rules or constraints may be applied to columns and these will be enforced by Oracle to ensure the integrity of the information. For example a rule might dictate that the column must have a value in and that the value must be unique. The design of a database will probably also include data normalisation which is the process of organising data in to entities with specific properties. There are several levels of normalisation but the most frequently used is the 3rd Normal Form (3NF) which means that there should be no data redundancy (data items only appear once) and no inferred values are stored (e.g. an order value can be calculated from the unit price and number of units ordered and so would not be stored but calculated when required instead). Data being transformed in to a data warehouse might be de-normalised in order to simplify or speed up analysis. The interface between the logical database and the actual files used to store the physical database on disk is the tablespace. A database must have at least one tablespace. The first one is called SYSTEM and is used to store the data dictionary and possibly user objects. Although only one tablespace is required, using more than one may provide advantages including reduced disk contention, better control of physical resources and the ability to perform online backups. Data in a database is accessed and managed using Structured Query Language, pronounced either "S.Q.L." or "sequel" depending on who you listen to. There are three types of SQL command. The Data Definition Language (DDL) is used to define objects in the database. The Data Manipulation Language (DML) has commands to retrieve and manipulate data. The Data Control Language (DCL) controls access to objects in the database and controls the ability to execute SQL commands. SQL is a query language, not a programming language - it doesn't contain commands for recursion, iteration or conditional processing. It is like English in as much as the commands used are English words but there is a rigid syntax to the commands which can be issued rather than accepting natural language. For example "SELECT description FROM part WHERE partid = 'wd423z' ;" would get the description attribute, stored in the table called part, for the part with an id of wd423z. Requests are passed to the database server which checks the syntax of the request before trying to carry it out. Oracle uses an optimiser to try and execute any given command as efficiently as possible to improve performance. The SQL language standard is set by the American National Standards Institute (ANSI). The standard has three compliance levels: Entry, Intermediate and Full. Oracle8 conforms fully to Entry level compliance and to Intermediate or Full level in many parts as well. There are two main types of database application development, programs which run on the client and processes which are carried out by the server. Most current development languages will allow you develop client-side programs which interact with almost any database. Extensions to Oracle are also available which allow you to run programs as part of the database. Interfaces allow development in a range of languages such as C and COBOL. Until recently, the primary development language for Oracle was PL/SQL but Java and SQLJ (which allows SQL to be used within Java code) are now being promoted as well. Rather than develop an application from scratch there are many commercial off-the-shelf (COTS) applications available for Oracle which may be bought and implemented either as-is or with some modification to suit the specific requirements of an organisation. Backups As with any other data on the system such as word processor or spreadsheet files, a valid backup set provides a safety net against system failure or other problems which result in data loss or corruption. The type of backup you take will often depend on what you use the database for. If it is possible to shut down the database, perhaps overnight out of office hours then you can perform an offline backup. Check with the DBA to ensure that the system is closed down with a SHUTDOWN NORMAL to commit all work to the database. If the database has to be kept up all the time, or if the quantity of data means you can't conveniently back it up in the time available, you will need to look at online backup options. In this situation you need to be aware that data which is to be written to the database is first written to a log file. Simply backing up the database is not sufficient since it could be that not everything which should be written to the database has been. Oracle has two modes it can operate in, known as ARCHIVELOG and NOARCHIVELOG and the former is required to be able to do online backups successfully. To perform an online backup you will need to store all the data files, a control file, the INIT.ORA (Oracle initialisation file), the password file and the archive logs. Before and after this there are a number of Oracle commands which need to be executed by the DBA. The DBA should be aware of what is required, which files need to be backed up and in what order. (If not, get a new DBA!) They should also be aware of how to carry out an offline or online complete (full or partial) or incomplete recovery. An incomplete recovery means that not all the transactions stored in the log files will be applied to the database and recovery will stop sometime before the last archive log file is processed. This may be desirable if one of the archive logs would otherwise introduce corrupt data to the database. Security Oracle provides the ability to define users and roles so make sure this facility is being used. User authentication may be inherited from an external source such as Novell Directory Services (NDS) so users don't actually have to sign in to Oracle as well. Auditing facilities enable you to check who is doing what but this is useless if everyone is using the same username (not unheard of, sadly). Take particular care if you are running an application developed by an external software house since they are often given dial-in or other access to apply bug fixes or updates to their applications. Review what access rights they have not only to your database but also to the rest of your network. Consider what access they may have to information which should remain confidential to your organisation. Ensure that any external organisation complies with your internal security policies. Oracle8i With the rise of the Internet many application vendors have sought to connect their products with the Internet in some way and Oracle is no exception, describing Oracle8i as "the database for Internet computing". Oracle8i is actually a family of products. These are the standard Oracle8i, Oracle8i Enterprise Edition and Oracle8i Personal Edition and Oracle8i Lite. All editions are based on the same database engine architecture so that database applications developed using the Personal Edition can be moved to the Enterprise Edition without needing any changes with support claimed for over 90 platforms (including many UNIX variants). For application development, Oracle8i includes a Java Virtual Machine (VM) and support for SQLJ which is allows a developer to embed SQL in Java code. The Java code may be run either directly on the server or by a client which will create Java programs based on Java Database Connectivity (JDBC). Java may be used instead of PL/SQL which is normally the alternative for server-side programming. Oracle WebDB enables the development of applications which only require a Web browser to use them. The applications and web sites so developed are stored entirely within the Oracle database. Clients do not need any additional software to be installed and of particular importance to those who have to manage them, no client-side changes are required if and when the Oracle application has bug fixes or upgrades applied. Because only a Web browser is required, the hardware requirements for user workstations are reduced leading, in suitable environments, to thin-client network model. Internet File System (iFS) is a Java application running on the server which makes files and other data in the database appear as ordinary files to clients using Windows Explorer (including support for drag and drop between the workstation and database server), a Web browser, a FTP client or an email client. Because a single application handles many kinds of client, administration and resource access should be simplified. interMedia allows Oracle to provide better support for multimedia data such as images, audio and video in a variety of formats. The new and improved version of Oracle Enterprise Manager is Java-based and can also allow multiple administrators to share real-time management information as well as improving scalability and reliability, including load-balancing and failover. There are a number of enhancements intended to improve Oracle in data warehouse applications. These include improvements to indexing such as the ability to rebuild an index while the database is still online. User monitoring of lengthy operations means that they don't need to bother the DBA to find out when a job will be complete. Standby Database facilities first included with Oracle7 are improved, with automation of updates to the standby database and easier and quicker recovery if there is a failure. Oracle Parallel Server provides clustering and caching to enable the construction of balanced, high performance, reliable systems which are relatively easy to administer. A new LogMiner utility provides tools to view the contents of archive (transaction) log files and backout (reverse) operations which should not have been performed. Training and Certification Oracle Corporation, in common with a number of other vendors, provide certification schemes to allow those who use their products prove their knowledge. There are Oracle Certified Professional (OCP) schemes (or tracks) for the Database Administrator (DBA), Database Operator, Application Developer and Oracle Designer. Continuing on from the OCP programme, there is also the Oracle Master Program which is intended is intended to demonstrate an even higher level of Oracle product knowledge. As with many other certification schemes you can take the OCP tests just by using easily available self-study material. In order to gain the Oracle Master qualification however you must have attended all the instructor-led courses required for the qualification track you are following. Oracle are also involved with a number of other vendors in a collaboration which aims to provide certification for developers of Java applications. Raw Iron The Oracle8i Appliance, originally code-named Raw Iron, is a version on Oracle8i designed to run on Intel processor-based hardware without needing a separate operating system such as Windows NT or UNIX. This is intended to lower the cost of implementing Oracle8i by removing the cost of an operating system and simplify installation and configuration by tightly controlling the environment in use. Using an Oracle8i Appliance should mean that only a single vendor is responsible for the entire system, simplifying support and avoiding attempts by vendors to blame other parts of the system which are not their responsibility. A thin-client network model, made possible by the use of WebDB and Java for application development, should make it possible to reduce the total cost of ownership (TCO) of the entire system. Existing Oracle administrators are expected to require little additional knowledge to be able to run an Appliance and would not need to be able to administer a separate operating system. In the future you can probably expect to see greater use of Java and the thin-client model, whether or not the Oracle8i Appliance becomes popular. Other changes to the Oracle database family are likely to be gradual improvements to reliability, availability and scalability both to larger and much smaller systems including handheld computers.