OCA: Oracle 10g Administration I Study Guide (1Z0-042)

OCA: Oracle 10g Administration Study Guide\ \ By Chip Dawes \ John Wiley & Sons\ ISBN: 0-7821-4367-9 \ \ \ \ Chapter One\ Oracle Database 10g Components and Architecture\ ORACLE DATABASE 10G: ADMINISTRATION I EXAM OBJECTIVES COVERED IN THIS CHAPTER:\ \   Installing Oracle Database 10g Software\ \ \ * Identify system requirements.\ * Use Optimal Flexible Architecture.\ * Install software with Oracle Universal Installer.\ * Identify and configure commonly used environment variables.\ \   Creating an Oracle Database\ \ \ * Explain the Oracle database architecture.\ * Explain the instance architecture.\ \   Database Interfaces\ \ \ * Use SQL*Plus and iSQL*Plus to access the Oracle Database 10g.\ * Use SQL*Plus and iSQL*Plus to describe the logical structure of tables.\ * Use SQL to query, manipulate, and define data using SELECT, UPDATE/INSERT/DELETE and CREATE/ALTER/ DROP statements.\ * Identify common database interfaces.\ * Describe a database transaction.\ NOTE\ Exam objectives are subject to change at any time without prior notice and at Oracle's sole discretion. Please visit Oracle's Training and Certification website ( oracle.com/education/certification/) for the most current exam objectives listing.\ With the release of Oracle Database 10g (Oracle 10g), Oracle Corporation has delivered a powerful and feature-rich database that can meet the performance, availability, recoverability, and security requirements of any mission-critical application. As the Oracle DBA, you are responsible for managing and maintaining the Oracle 10g database from initial installation, creation, and configuration to final deployment. Performing these tasks requires a solid understanding of Oracle's product offerings so that you can apply the proper tools and features to the application. You must also use relational database concepts to design, implement, and maintain the tables that store the application data. At the heart of these activities is the need for a thorough understanding of the Oracle architecture and the tools and techniques used to monitor and manage the components of this architecture.\ This chapter introduces you to important concepts associated with the relational nature of Oracle 10g and its architecture. This chapter will also help you learn the specifics of how to install, configure, and manage an Oracle database. You need a solid understanding of these concepts before moving on to subsequent chapters.\ The Oracle Product Family\ Oracle Corporation is generally thought of as a database company-and for good reason. Oracle has been a leader in the development of reliable, scalable, and recoverable database technologies for more than 25 years. With the release of Oracle 10g, Oracle has further enhanced its reputation as an industry leader by producing a feature-rich, yet easy-to-manage database that can handle data from the busiest transactional systems to the largest data warehouses. However, Oracle Corporation also produces many other products that support a variety of data-related business activities. Currently, Oracle's product family consists of the following products and services:\ * Oracle Database 10g\ * Oracle Application Server 10g\ * Oracle Developer Suite\ * Oracle Applications 11i\ * Oracle Collaboration Suite\ * Oracle Services\ Each of these products or services is described in detail in the following sections.\ NOTE\ The following information regarding Oracle's products is included to provide a framework for understanding where Oracle 10g fits within the larger Oracle product line. This information is not part of the exam objectives.\ Oracle 10g\ Oracle 10g was released as version 10.1.0.2 in spring 2004. This release of Oracle's flagship database product introduces many new features, but the three primary thrusts are ease of management, enhanced scalability, and improved performance management.\ The ease of management features include the automatic management of disk storage allocated to the database, proactive monitoring and self-tuning of the database's memory structures, preconfigured database alerts, and enhanced, web-based tools for monitoring and managing the entire Oracle architecture.\ Scalability and performance improvements are largely based on Oracle's grid computing model. Grid computing is intended to allow businesses to move away from the idea of many individual servers, each of which is dedicated to a small number of applications. When configured in this manner, applications often either do not fully utilize the server's available hardware resources such as memory, CPU, and disk or fall short of these resources during peak usage. By comparison, databases running under Oracle's grid computing model can be spread across as few or as many servers as needed so as to make the most efficient use of each of the available hardware resources at all times. At the same time, Oracle 10g's automated performance monitoring and tuning mechanisms dynamically adjust the database's allocation of these resources to improve performance.\ There are five editions of Oracle 10Z\ * Enterprise\ * Standard\ * Standard Edition One\ * Personal\ * Lite\ Table 1.1 compares these versions.\ NOTE\ Most of the examples in this book are based on the Enterprise Edition.\ Oracle Application Server 10g\ Oracle's Application Server 10g is used to deploy web-based applications that, like the database, must be highly reliable and scalable to thousands of users. Like the database, Oracle Application Server 10g is also available in a number of versions, but all versions include full Java functionality and Oracle's HTTP server, with additional components such as portals, forms and reports servers, single sign-on capabilities, and wireless connectivity also available.\ Oracle Developer Suite\ Oracle's Developer Suite consists of several products that can be used to design, develop, and distribute web-based applications. These tools include the following:\ * Oracle Designer for gathering business requirements and designing applications\ * Oracle JDeveloper for creating Java-based applications\ * Oracle Forms and Reports Developer for creating and deploying custom forms and reports\ * Oracle Discoverer for developing and distributing ad hoc reporting capabilities against application data\ * Oracle Warehouse Builder for designing and deploying data marts and warehouses.\ Each of these tools is designed to integrate seamlessly with the Oracle database and application server products to provide a robust application development environment.\ Oracle Applications 11i\ Oracle's database, application server, and developer products make up the core infrastructure of the E-Business Suite of products collectively called Oracle Applications 11i. Oracle Applications 11i is composed of a number of modules that are used to manage the financial, personnel, manufacturing, order management, sales, service, and asset data of both businesses and public sector organizations.\ Oracle Collaboration Suite\ Oracle's Collaboration Suite offers a comprehensive system that integrates all of a business's communication technologies, from e-mail, voice mail, and faxes to wireless connectivity and web conferencing. Like Oracle Applications 11i, the Collaboration Suite also uses Oracle's database and application server technologies as the core technology infrastructure. This provides a scalable and reliable platform for true enterprise-wide collaboration.\ Oracle Services\ In addition to software development, Oracle also offers a variety of technical support and consulting services. Technical support is delivered primarily through Oracle's MetaLink website and is available to all customers with current maintenance agreements. In addition to this support, Oracle Services also offers consulting services to help customers select, install, and configure the Oracle technologies that best meet their needs.\ NOTE\ You can access Oracle's MetaLink support site at metalink.oracle.com. This site provides a wealth of patches, documentation, notes, white papers, and user forums.\ NOTE\ A valid Custom Support Identifier(CSI) is required to create a MetaLink account. A unique CSI number is usually issued for each Oracle product that is purchased.\ Another service offered by Oracle is education. Oracle develops and delivers instructor-led and web-based training courses for all their products. These courses are taught at Oracle University sites and Oracle Approved Education Center locations throughout the world. Oracle Education is also responsible for coordinating all of Oracle's certification programs, including the Oracle Database 10g Oracle Certified Associate (OCA) and Oracle Certified Professional (OCP) certifications for which this book helps you prepare.\ Relational Database Concepts\ At the heart of all the Oracle products discussed in the previous section is the concept of using a database to store, manipulate, retrieve, and secure important business data. The manner in which these three tasks are performed has varied throughout the history of computing. Some early database technologies used flat files or hierarchical file structures to store application data. Others used networks of connections between sets of data to store and locate information.\ Oracle 10g does not use any of these techniques for storing or accessing data. Instead, all releases of Oracle's database products have used a relational model to store application data in the database. This relational model on which Oracle is built is based on the ground-breaking work of Dr. Edgar Codd, which was first published in 1970 in his paper "A Relational Model of Data for Large Shared Data Banks."\ NOTE\ Oracle Corporation (then known as Relational Software, Inc.) released the first commercially available relational database in 1979.\ IBM Corporation was an early adopter of Dr. Codd's model and also helped to develop the computer language that is used to access all relational databases today-Structured Query Language (SQL). Using English-like commands, SQL users can easily interact with relational databases without having to write complex computer programs or needing to know where or how the data is physically stored on disk. Samples of SQL statements are used in examples throughout this book. In general, SQL commands are used to do the following:\ * Display data stored in database tables using the SELECT command\ * Add rows to tables using the INSERT command\ * Remove rows from tables using the DELETE command\ * Modify rows in tables using the UPDATE command\ * Create, modify, or drop tables using the CREATE, ALTER, and DROP commands\ * Grant or revoke user access to tables using the GRANT and REVOKE commands\ * Control transactions using the COMMIT and ROLLBACK commands\ Even though each of the previous commands is an SQL command, each type of SQL statement can be classified into one of four categories:\ * Queries using the SELECT command.\ * Statements using the CREATE, ALTER, or DROP command are classified as Data Definition Language (DDL) commands.\ * Statements using the GRANT or REVOKE commands are classified as Data Control Language (DCL) commands.\ * Statements using the INSERT, UPDATE, and DELETE commands are classified as Data Manipulation Language (DML) commands.\ DML commands are used in transactions. A transaction begins with the first DML command that a user issues and ends when the user either makes their changes permanent by issuing a commit command or undoes their changes using the rollback command.\ NOTE\ Issuing a DDL or a DCL command also ends any prior transactions by causing an implicit commit command to occur. Abnormal terminations of a database connection to a network or a power failure can cause implicit rollbacks to occur.\ Most SQL statements, whether they are queries, DMLs, DDLs, or DCLs, are directed at data stored in one or more Oracle tables. The next section examines important Oracle table concepts in detail.\ Rows, Columns, Tables, and Databases\ At the heart of the relational model is the concept of a table. A table is composed of columns and rows. The intersection of a column and a row is called a field. The collection of tables that store business data are stored within the Oracle 10g database. Figure 1.1 shows an example of a table, a column, a row, and a field for a table called DEPT that stores department data.\ The DEPT table in Figure 1.1 is composed of three columns (DEPTNO, DNAME, and LOC) and contains four rows. Each row contains all the relevant data for a single department. The field at the intersection of the DNAME column and the first row contains the value "Accounting". When a table is created, each column is assigned a name and a datatype. Many datatypes are available in Oracle 10g, but most simply designate whether a column is intended to store characters, numbers, or dates. You can use the following DDL statement to create the DEPT table shown in Figure 1.1.\ SQL> create table DEPT 2 (DEPTNO number(2), 3 DNAME varchar2(14), 4 LOC varchar2(13));\ Table created.\ The DDL command creates a column called DEPTNO to store department numbers of as many as 2 digits, a column called DNAME to store department name data of as many as 14 characters, and a column called LOC to store department location data of as many as 13 characters. By specifying column datatypes in this manner, some basic data controls are automatically in place within the database. These controls prevent a user from storing incorrect data in a table. For example, attempting to insert a record that stores a word in a column that is set up to hold numeric values causes a SQL error. The following example shows an example of an INSERT statement that succeeds because all the data being inserted is of the correct datatype, and it shows another statement that fails because a character datatype was inserted into the numeric DEPTNO column:\ SQL> insert into DEPT (DEPTNO, DNAME, LOC) 2 values (50,'MANUFACTURING','MADISON');\ 1 row created.\ SQL> insert into DEPT (DEPTNO, DNAME, LOC) 2 values ('SIX','SHIPPING','MILWAUKEE'); values ('SIX','SHIPPING','MILWAUKEE')\ * ERROR at line 2: ORA-01722: invalid number\ NOTE\ These are simplified examples. Oracle 10g can accommodate tables that have as many as 1,000 columns and billions of rows.\ NOTE\ See Chapter 3, "Database Storage and Schema Objects," for more information about creating tables and other database objects.\ In addition to tables such as DEPT that store important business data, Oracle databases also contain system tables that store data about the database itself. Examples of the type of information in these system tables include the names of all the tables in the database, the column names and datatypes of those tables, the number of rows those tables contain, and security information about which users are allowed to access those tables. This "data about the database" is referred to as metadata. As a DBA, you will frequently use this metadata when performing your tasks.\ The metadata tables, however, have rather cryptic names such as OBJ$, FILE$, X$KSMSP, and X$KWQSI with unusual column names such as DATAOBJ#, CRSCNWRP, KSMCHCOM, and KWQSINCO. To make it easier to use SQL to examine the contents of metadata tables, Oracle builds views on the tables. A view is similar to a table in that it is made up of columns and rows. However, a view is only a logical structure that contains no data of its own. Instead, a view is like a window that can be used to look at the contents of another table or tables. Views greatly simplify access to the metadata because the names of the views and the columns in them are much more intuitive than the metadata tables on which they are based. An Oracle 10g database contains two types of metadata views:\ * Data dictionary views\ * Dynamic performance views\ Examples of both data dictionary and dynamic performance views are described in the next section.\ Data Dictionary Views\ Depending on which features are installed and configured, an Oracle 10g database can contain more than 1,300 data dictionary views. Data dictionary views have names that begin with DBA_, ALL_, and USER_.\ (Continues...)\ \ \ \ \ Excerpted from OCA: Oracle 10g Administration Study Guide by Chip Dawes Excerpted by permission.\ All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.\ Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site. \ \

Ch. 1Oracle database 10g components and architecture1Ch. 2Creating and controlling a database75Ch. 3Database storage and schema objects135Ch. 4Oracle Net services185Ch. 5Oracle shared server269Ch. 6User administration and security309Ch. 7Managing data with SQL, PL/SQL, and Utilities357Ch. 8Managing consistency and concurrency401Ch. 9Proactive database maintenance and performance monitoring431Ch. 10Implementing database backups499Ch. 11Implementing database recovery543