CERTguide Oracle 9i Database Fundamental 1 Study Guide
What is this Oracle exam all about?
Exam #1Z0-031: Oracle9i: DBA Fundamentals I is part of the requirement of the OCA designation.
"The Oracle Certification Program begins with the Associate level. At this apprentice skill level, Oracle Certified Associates have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. Earning your Oracle Certified Associate credential as a beginning IT professional will give you recognition for your foundation of knowledge using Oracle technologies. This recognition will differentiate you from those without any credentials, and can help you build your career by obtaining access to entry-level opportunities." http://www.oracle.com/education/certification/index.html?dba9i_oca.html
What does it take to become a DBA?
The new Oracle database certification requirement is significantly different from the previous DBA track. Under 9i, OCA + OCP = 9i DBA:
"The first step toward earning the highly respected Oracle Certified Professional (OCP) status is to earn your Oracle Certified Associate (OCA) credential. When you are ready to advance your Oracle9i database expertise then it's time to take the next step toward becoming an OCP." http://www.oracle.com/education/certification/index.html?dba9i_ocp.html
In the past, you need to take and complete 5 exams. Now you only need to take 4 in total (2 exams for OCA and 2 for OCP).
What does it take to earn the OCA designation?
"To become an Oracle Certified Associate, you must pass the exams required for the Oracle Certified Associate level of your selected job role. Typically, two exams are required; the first one can be taken via the Internet, while subsequent exams must be taken in a proctored environment." http://www.oracle.com/education/certification/index.html?dba9i_oca.html
Let’s review the official testing objectives:
Oracle Architectural Components
Describe the Oracle architecture and its main components
Describe the structures involved in connecting a user to an Oracle instance
ORACLE Architecture and Terminology:
http://ugweb.cs.ualberta.ca/~c391/manual/chapt2.html
Getting Started With the Oracle Server
Identify common database administrative tools available to a DBA
Identify the features of the Oracle Universal Installer
Explain the benefits of Optimal Flexible Architecture
Set up password file authentication
List the main components of the Oracle Enterprise Manager and their uses
"A database server is the key to solving the problems of information management. In general, a server must reliably manage a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this must be accomplished while delivering high performance. A database server must also prevent unauthorized access and provide efficient solutions for failure recovery." http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a88856/c01intro.htm#20385
Oracle9i Database Administrator's Guide
Release 1 (9.0.1) for Windows
http://download-west.oracle.com/otndoc/oracle9i/901_doc/win.901/a90164/toc.htm
Oracle Enterprise Manager Administrator's Guide
Release 9.0.1
http://download-west.oracle.com/otndoc/oracle9i/901_doc/em.901/a88767/toc.htm
Know the GUI as well as the command line.
Managing an Oracle Instance
Create and manage initialization parameter files
Configure OMF
Start up and shut down an instance
Monitor the use of diagnostic files
"
When you start up a database, you create an instance of that database, and you choose the state in which the database starts. Normally, you would start up an instance by mounting and opening the database, thus making it available for any valid user to connect to and perform typical data access operations. However, there are other options and these are also discussed in this section.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/start.htm#8918
Creating a Database
Describe the prerequisites necessary for database creation
Create a database using Oracle Database Configuration Assistant
Create a database manually
"
Database creation prepares several operating system files to work together as an Oracle database. You need only create a database once, regardless of how many datafiles it has or how many instances access it. Creating a database can also erase information in an existing database and create a new database with the same name and physical structure.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/create.htm#1656
Data Dictionary Content and Usage
Identify key data dictionary components
Identify the contents and uses of the data dictionary
Query the data dictionary
According to webopedia.com:
“In database management systems, a file that defines the basic organization of a database. A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field. Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents. Data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it. Without a data dictionary, however, a database management system cannot access data from the database.” http://www.webopedia.com/TERM/d/data_dictionary.html
"
The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database's SYSTEM tablespace.
Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. To access the data dictionary, use SQL statements. Because the data dictionary is read-only, you can issue only queries (SELECT statements) against the tables and views of the data dictionary.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a88856/c05dicti.htm#233
Maintaining the Control File
Explain the uses of the control file
Describe the contents of the control file
Multiplex and manage the control file
Manage the control file with Oracle Managed Files
Obtain control file information
"
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
The database name
Names and locations of associated datafiles and online redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/control.htm#5999
Maintaining Redo Log Files
Explain the purpose of online redo log files
Describe the structure of online redo log files
Control log switches and checkpoints
Multiplex and maintain online redo log files
Manage online redo log files with OMF
"
The most crucial structure for recovery operations is the online redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/onlinere.htm#273
"Oracle enables you to save filled groups of online redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply archive logs. The process of turning online redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving."
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/archredo.htm#4972
Managing Tablespaces and Data files
Describe the logical structure of tablespaces within the database
Create tablespaces
Change the size of the tablespace
Allocate space for temporary segments
Change the status of tablespaces
Change the storage settings of tablespaces
"
Using multiple tablespaces allows you more flexibility in performing database operations. For example, when a database has multiple tablespaces, you can perform the following tasks:
Separate user data from data dictionary data to reduce contention among dictionary objects and schema objects for the same datafiles.
Separate one application's data from another's to prevent multiple applications from being affected if a tablespace must to be taken offline.
Store different tablespaces' datafiles on separate disk drives to reduce I/O contention.
Separate rollback segment data from user data, preventing a single disk failure from causing permanent loss of data.
Take individual tablespaces offline while others remain online, providing better overall availability.
Reserve a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage. This enables you to optimize usage of the tablespace.
Back up individual tablespaces.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/tspaces.htm#544
Implement Oracle Managed Files
Study Resource Link:
“
Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames. Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:
Tablespaces
Online redo log files
Control files
“
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/omf.htm#1656
Storage Structure and Relationships
Describe the logical structure of segments within the database
Describe the segment types and their uses
List the keywords that control block space usage
Obtain information about storage structures from the data dictionary
"
This section describes aspects of managing space in data blocks. Data blocks are the finest level of granularity of the structure in which database data is stored on disk. The size of a data block is specified (or defaulted) at database creation.
The PCTFREE and PCTUSED parameters are physical attributes that can be specified when a schema object is created or altered. These parameters allow you to control the use of the free space within a data block. This free space is available for inserts and updates of rows of data.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/schema.htm#949
Managing Undo Data
Describe the purpose of undo data
Implement Automatic Undo Management
"
Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/undo.htm#8888
Managing Tables
Identify the various methods of storing data
Describe Oracle data types
Distinguish between an extended versus a restricted ROWID
Describe the structure of a row
Create regular and temporary tables
Manage storage structures within a table
Reorganize, truncate, drop a table
Drop a column within a table
"
Usually, the application developer is responsible for designing the elements of an application, including the tables. Database administrators are responsible for setting storage parameters and defining clusters for tables, based on information from the application developer about how the application works and the types of data expected.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/tables.htm#17307
Managing Indexes
Describe the different types of indexes and their uses
Create various types of indexes
Reorganize indexes Drop indexes
Get index information from the data dictionary
Monitor the usage of an index
"
Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/indexes.htm#10473
Maintaining Data Integrity
Implement data integrity constraints
Maintain integrity constraints
Obtain constraint information from the data dictionary
Managing Password Security and Resources
Manage passwords using profiles
Administer profiles
Control use of resources using profiles
Obtain information about profiles, password management and resources
"
Database security systems that are dependent on passwords require that passwords be kept secret at all times. But, passwords are vulnerable to theft, forgery, and misuse. To allow for greater control over database security, Oracle's password management policy is controlled by DBAs and security officers through user profiles.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/secure.htm#2185
Managing Users
Create new database users
Alter and drop existing database users
Monitor information about existing users
"
This chapter describes how to control access to an Oracle database, and contains the following topics:
Session and User Licensing
User Authentication Methods
Managing Oracle Users
Managing Resources with Profiles
Viewing Information About Database Users and Profiles
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/users.htm#14699
Managing Privileges
Identify system and object privileges
Grant and revoke privileges
Identify auditing capabilities
"
A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. Oracle also provides shortcuts for grouping privileges that are commonly granted or revoked together.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/privs.htm#14881
Managing Roles
Create and modify roles
Control availability of roles
Remove roles
Use predefined roles
Display role information from the data dictionary
"
The types of users and their roles and responsibilities at a site can vary. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people, and among several areas of specialization.
"
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/dba.htm#4755
Using Globalization Support
Choose database character set and national character set for a database
Specify the language-dependent behavior using initialization parameters, environment variables and the ALTER SESSION command
Use the different types of National Language Support (NLS) parameters
Explain the influence on language-dependent application behavior
Obtain information about Globalization Support usage
"Oracle's Globalization Support architecture allows you to store, process, and retrieve data in native languages. It ensures that database utilities, error messages, sort order, date, time, monetary, numeric, and calendar conventions automatically adapt to any native language and locale. In the past, Oracle's Globalization Support capabilities were referred to as National Language Support (NLS) features. National Language Support is a subset of Globalization Support because the Oracle database can do more than handle one national language or store data in one character set. Globalization Support allows you to develop multilingual applications and software products that can be accessed and run from anywhere in the world simultaneously without modification. The applications can render content in native users' languages and locale preferences."
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90236/ch1.htm#45875
You should make yourself completely familiar with the technical side of SQL and RDBMS.
As of the time of this writing the exam does not test anything on the Oracle application development technology.
Do NOT ignore SQL. SQL DDL and DML are the important aspects of database operation!
Practical resources:
You should download an evaluation copy of Oracle 9i and play with it. The Windows version will suffice.
Download is FREE, but you need to join OTN Oracle Technology Network (again, at no cost) first. To download, visit the following URL: http://otn.oracle.com/software/content.html
There are different versions of the 9i database available. As of the time of this writing Release 2 of 9i is available for Windows, Linux, Solaris and other Unix flavors:
n Oracle9i Database Release 2 Enterprise/Standard Edition for Linux
n Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XP Pro
n Oracle9i Database Release 2 Enterprise/Standard Edition for Sun SPARC Solaris (32-bit)
n Oracle9i Database Release 2 Enterprise Edition for Sun SPARC Solaris (64-bit)
n Oracle9i Database Release 2 Enterprise/Standard Edition for HP-UX
n Oracle9i Database Release 2 Enterprise/Standard Edition for Compaq Tru64
n Oracle9i Database Release 2 Enterprise/Standard Edition for AIX
n Oracle9i Database Release 2 Enterprise/Standard Edition for AIX-Based 5L Systems
n Oracle9i Database Release 2 Client for Windows 98/NT/2000/XP
Reference Books:
Oracle 9i New Features
by Robert G. Freeman (Paperback)
Expert One on One: Oracle
by Thomas Kyte; Perfect Paperback
Oracle 9i Complete: A Comprehensive Reference to Oracle 9
by Robert J. Muller (Paperback - October 2002) COMING SOON
This study guide is developed by Michael Yu Chak Tin. He can be reached at Michael@examreview.net.