Lesson 1. Course Introduction
*Oracle9i Database Fundamentals SeriesThe Oracle9i Database Fundamentals series is designed to help those who have experience with relational databases, SQL, and networking prepare for the Oracle 1Z1-031 exam.
You will be introduced to the major components of an Oracle server and the database structure and will learn how to create and manage such components as databases, instances, tablespaces, tables, datafiles, storage units, undo data, indexes, data integrity, users, profiles, privileges, and roles.
*Course 1: Oracle Architecture and Tools
In this course, you will learn about the components that make up the Oracle server architecture. You will learn the structures that connect users to Oracle servers and the stages in processing queries, changes, and commits.
You will also learn about the common database administrative tools available to DBAs. You will learn how to use the Oracle Universal Installer to install Oracle. You will learn about Optimal Flexible Architecture and the benefits it brings to DBAs. You will learn to set up password file authentication. Finally, you will learn how to use the Oracle Enterprise Manager and its components.
This course presents information relevant to the following Oracle 1Z1-031: Oracle9i DBA Fundamentals I exam objectives:
Oracle Architectural Components
- Describe the Oracle architecture and its main components
- Describe the structures involved in connecting a user to an Oracle instance
- Identify common database administrative tools available to a DBA
- Identify the features of Oracle Universal Installer
- Explain the benefits of Optimal Flexible Architecture
- Set up password file authentication
- List the main components of OEM and their uses
Lesson 2. Oracle Server Architecture
An Oracle instance is made up of memory structures and background processes.After completing this lesson, you should be able to:
- Differentiate an Oracle instance from an Oracle database
- Identify the two memory components of an Oracle instance
- Explain the structure of the SGA and the PGA
To be a successful Oracle DBA and to pass the Oracle9i DBA Fundamentals I exam, you must understand the Oracle database architecture. An Oracle database in action consists of several elements, including memory structures, special processes that make things run faster, and recovery mechanisms that enable the DBA to restore systems after seemingly unrecoverable problems.
*Oracle Architecture Components
The Oracle database server consists of many different components. Some of these components are memory structures, whereas others are background processes that execute certain tasks behind the scenes. There are also disk resources that store the data that applications use to track data for an entire organization, and special resources designed to allow for recovering data from problems ranging from incorrect entry to disk failure.
The memory structures and the background processes constitute an Oracle instance, whereas the Oracle instance with the remaining structures constitutes an Oracle database.
Every Oracle database, from the smallest Oracle application running on a hand-held device to terabyte data warehouses that run on mainframes and supercomputers, has various disk, memory, and process components working together to manage data. They allow for applications, ranging from online transaction processing (OLTP) apps to N-tier apps to data marts to data warehouses, to process their data efficiently and effectively.
*System Global Area
Focus first on the memory components of the Oracle instance. There are two basic memory structures in Oracle: the SGA and the PGA.
When DBAs talk about most things related to memory, they usually mean the SGA or System Global Area. The SGA consists of several different items: the buffer cache, shared pool, and redo log buffer, as well as a few other items such as the Java pool and large pool.
The buffer cache consists of buffers, each the size of a database block, that store data needed by SQL statements issued by user processes. You can imagine buffer cache as a beehive with each unit in it as a buffer. You can set the size of the buffer cache in bytes. Sizing the buffer cache is important because a large cache can improve performance. However, a larger cache can take up too much memory. A database block is the most granular unit of information storage in Oracle, in which Oracle can place several rows of table data.
The buffer cache has two purposes: to improve performance for subsequent repeated select statements on the same data and to enable Oracle users to make data changes quickly in memory. Oracle writes those data changes to disk later.
The Oracle shared pool includes two mandatory structures and one optional structure. The first required component is the library cache, which is used for storing parsed SQL statement text and the statement's execution plan for reuse.
The second is the dictionary cache, sometimes also referred to as the row cache, which is used for storing recently accessed information from the Oracle data dictionary, such as table and column definitions, usernames, passwords, and privileges. (The data dictionary will be discussed in another lesson.)
These two components are designed to improve overall Oracle performance in multiuser environments. There is also an optional shared pool structure that contains session information about user processes connected to Oracle.
The redo log buffer is an SGA component that temporarily stores in memory the redo entry information generated by DML statements run in user sessions until Oracle writes the information to disk.
DML statements include update, delete, and insert statements run by users.
A redo entry is a small amount of information produced and saved by Oracle to reconstruct, or redo, changes made to the database by insert, update, delete, create, alter, and drop statements. If some sort of failure occurs, the DBA can use redo information to recover the Oracle database to the point of database failure.
*Program Global Area
The other memory structure in the Oracle instance is called the Program Global Area (PGA). The PGA helps user processes execute by storing information like bind variable values, sort areas, and other aspects of cursor handling.
Why do users need their own area to execute? Even though the parse information for SQL or PL/SQL may already be available in the library cache of the shared pool, the values upon which the user wants to execute the select or update statement cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.
In this lesson, you learned that an Oracle instance is the memory and background processes of a running Oracle server, whereas a database consists of an instance and files on disk. You learned that the two basic memory structures of an instance are the System Global Area (SGA) and the Program Global Area (PGA).
You also learned that the primary components of the SGA are the buffer cache, the shared pool, and the redo log buffer, and you learned the function of each. Finally, you learned that the PGA stores user-level information like bind variable values, sort area, and other aspects of cursor handling.
Lesson 3. The Server Process
The server process pulls Oracle data from disk so that a user can manipulate it. There are two ways to configure Oracle to run server processes.After completing this lesson, you should be able to:
- Describe how the Oracle server process is used
- Identify the two ways Oracle can be configured to run server processes
- Explain the difference between shared servers and dedicated servers
There are several types of processes running all the time in Oracle. These types are background, server, and network processes. The most important one from a user perspective is the server process.
The server process acts on the user's behalf to pull Oracle data from disk into the buffer cache, where the user can manipulate it. Think of the Oracle server process as a genie — the magical being from the story of Aladdin — because your want for Oracle data is the server process command!
There are two ways DBAs can set up Oracle to run server processes: dedicated servers and shared servers. The following pages identify the primary differences between these two configurations.
*Dedicated Servers
With dedicated servers, every single user connecting to Oracle will have a personal genie handling data retrieval from disk into the buffer cache. If there are 150 users connected to Oracle, there will also be 150 genies out there grabbing data from disk and putting it in the buffer cache for those users.
The dedicated architectural setup means that every user gets his or her data retrieval requests acted upon immediately. It also means there will be additional memory and CPU overhead on the machine running the Oracle database, and that each dedicated server process will, depending on the workload and the access method, sit idle most of the time. Still, this is the setup chosen by many DBAs for overall performance reasons when hardware resources are readily available.
*Shared Servers
With a shared server setup, there is a small pool of server processes running in Oracle that support data retrieval requests for a large number of users. Several users are served by one server process.
Oracle manages this utilization by means of a network process called the dispatcher. User processes are assigned to a dispatcher, and the dispatcher puts the user requests for data into one queue. The shared server processes then fulfill all the requests, one at a time.
This does not mean that there will be only one dispatcher process for the entire database. You will be able to configure the database to have many dispatchers and many server processes.
The shared server configuration can reduce memory and CPU burden on the machine that hosts Oracle, as well as limit server process idle time. In dedicated server mode, the user process has to take time to create a dedicated server process for each connection that comes through, whereas in shared server mode, they save time by being served by an existing dispatcher process and server process. That is why multithreaded server architecture is preferred in cases where there are a large number of users in the database as well as when a large number of users connect and disconnect from the database (Internet databases).
Oracle will store session information in the shared pool only if the DBA configures Oracle to use shared server processes to handle user data retrieval requests. This option is known as the multithreaded server (MTS) architecture. Otherwise, if dedicated server processes are used, user session information is housed in the PGA.
In addition to server processes, Oracle uses background processes for a multitude of operations.
These processes include
- DBW0 (or database writer)
- LGWR (or log writer)
- CKPT (or checkpoint)
- SMON (or system monitor)
- PMON (or process monitor)
In this lesson, you learned that server processes act on the user's behalf to pull Oracle data from disk into the buffer cache, where the user can manipulate it. There are two ways to configure Oracle to run server processes: dedicated servers and shared servers.
You learned that dedicated servers ensure that each user gets his or her data requests acted on immediately. However, there will be additional memory and CPU overhead on the machine running the database and each server process may sit idle for much of the time.
You also learned that in the shared server configuration, a small pool of server processes supports data requests for a large number of users. This is managed by a dispatcher and can reduce the CPU and memory burden.
Finally, you learned that Oracle will store session information in the shared pool if the DBA configures Oracle to use shared servers to handle user data retrieval requests. This option is known as multithreaded server (MTS) architecture.
Lesson 4. Connecting to Oracle Servers
Oracle network processes are used to connect users to Oracle servers.After completing this lesson, you should be able to:
- Describe how the listener process is used
- Identify when the dispatcher process is used
- Explain the events that occur when a user makes a call
Let's spend a moment covering a few other important Oracle network processes. The first is called the listener process. The Oracle listener process does just that — it listens for users trying to connect to the Oracle database via the network.
When a user connects to the machine hosting the Oracle database, the listener process does one of two things. If dedicated server processes are being used, the listener tells Oracle to generate a new dedicated server and then assigns the user process to that dedicated server.
If MTS is being used, the listener sends the user process to another process called the dispatcher process. Once the listener hands over the user connection to either a dedicated server or a dispatcher, it is no longer involved in that connection. It will return to servicing new incoming connections.
The term "request from a user" is actually more precise than it sounds. It is a single program-interface call that is part of the user's SQL statement. When the database is operating in MTS mode and a user makes a call, the dispatcher servicing the user process places the request in the request queue, where the next available shared server process picks it up. The request queue is in the SGA and is shared by all dispatcher processes of an instance.
The shared server processes check the common request queue for new requests, picking up new requests on a first-in-first-out basis. One shared server process picks up one request in the queue and makes all the necessary calls to the database to complete that request. When the server completes the request, it places the response on the calling dispatcher's response queue.
Each dispatcher has its own response queue in the SGA. The dispatcher then returns the completed request to the appropriate user process. That is the magic of how users are connected to an Oracle server.
Here's a quick summary of server, background, and network processes.
- The server process handles user requests for data.
- Background processes are Oracle processes that handle certain aspects of database operation behind the scenes.
- Network processes are used for network connectivity between user processes running on other machines to server processes running on the machine hosting the Oracle database.
In this lesson, you learned that the listener process is an Oracle network process that listens for users trying to connect to the Oracle database via the network. You learned that if dedicated server processes are being used, the listener tells Oracle to generate a new dedicated server and assigns the user to that server. You learned that if MTS is being used, the listener sends the user to the dispatcher process.
You also learned the sequence of events that occurs when a user makes a call to a database that is operating in MTS mode. The request passes from the dispatcher to the request queue, where it is picked up by a shared server process, completed and sent back to the dispatcher, which returns it to the user process.
Lesson 5. Processing Queries
The RDBMS translates your SQL statement into a plan for Oracle to execute and obtain data.After completing this lesson, you should be able to:
- Identify the function of the RDBMS
- Describe how Oracle processes queries
Another lesson covers how Oracle connects a user process with a server process. Now it's time for you to learn how Oracle behaves when the user wants to do something with the server, such as selecting Oracle data. You already know most of the main players, including the server process, user process, buffer cache, and library cache of the shared pool. You know all players, that is, except one — the Oracle relational database management system (RDBMS).
SQL is a functional programming language, as opposed to a procedural language like COBOL or C. You write your code in terms of your desired outcome, not the process by which Oracle should get there. The RDBMS translates the outcome defined in your SQL statement into a process by which Oracle will obtain it.
With all components established in the world of processing Oracle queries, let's look now at how Oracle processes queries.
There are several steps in processing an Oracle select statement. The operations involved in executing both select statements and DML statements fall into a general pattern, which is shown here.
*Processing a select Statement
The specific flow of operation in processing a select statement is as follows:
*Step 1: Search Shared Pool
The RDBMS will first attempt to determine if a copy of this parsed SQL statement exists in the library cache.
*Step 2: Validate Statement
The RDBMS accomplishes this step by checking SQL statement syntax.
*Step 3: Validate Data Sources
The RDBMS ensures that all columns and tables referenced in this statement exist.
*Step 4: Acquire Locks
The RDBMS acquires parse locks on objects referenced in this statement so that their definitions don't change while the statement is being parsed.
*Step 5: Check Privileges
The RDBMS ensures that the user attempting to execute this SQL statement has enough privileges in the database to do so.
*Step 6: Parse Statement
The RDBMS creates a parse tree, or execution plan, for the statement and places it in the library cache, based on what Oracle believes is the optimal method for executing the SQL statement. This is a list of operations the RDBMS uses to obtain data.
If a parse tree already exists for this statement, the RDBMS can omit this step.
*Step 7: Execute Statement
The RDBMS performs all processing to execute the select statement. At this point, the server process will retrieve data from disk into the buffer cache.
*Step 8: Fetch Values from Cursor
Once the select statement has been executed, all data returned from Oracle is stored in the cursor. That data is then placed into bind variables, row-by-row, and returned to the user process.
When complete, both the statement execution plan and the data in blocks retrieved from the disk stick around in the library cache and buffer cache, respectively, for a variable length of time, just in case that user or another user wants to execute the same select statement.
In multiuser application environments, a performance gain is achieved every time user processes execute the same select statement because the RDBMS spends less time parsing the statement, and the server process spends less time retrieving data.
In this lesson, you learned that the Oracle relational database management system (RDBMS) is used to translate the outcome defined in a SQL statement into a process by which Oracle will obtain the outcome.
You also learned the specific flow of operation in processing a select statement. This involves the RDBMS ensuring that the SQL statement is valid, references existing data, and can be executed by the user. The RDBMS creates an execution plan for the statement, which is then executed, and data is returned to the user process.
Finally, you learned that the execution plan and the retrieved data stay in the library and buffer cache in case it is needed again.
Lesson 6. Processing DML Statements
The operations used to execute DML statements are generally the same used to execute select statements. However, there are a few differences that you should know.After completing this lesson, you should be able to:
- Define the undo segment
- Explain how DML statements are processed
- Describe how background processes are used to write data changes to disk
At this point, meet yet another behind-the-scenes player in Oracle transaction processing — the undo segment. The undo segment is a database object in Oracle that stores old versions of data being changed by DML statements issued by the user process. Undo segments only store the old values, not the new values — the new values are stored in the object itself.
*Processing DML Statements
With this in mind, let's return to the processing of Data Manipulation Language (DML) statements. There are several differences between how Oracle processes select statements and how it processes DML statements such as update, insert, and delete.
Although the operations involved in executing DML statements fall into the same general pattern as those for select statements, the specific flow of operation in processing DML statements is explained on the following pages.
*Step 1: Parse Statement
The RDBMS creates a parse tree, or execution plan, for the statement and places it in the library cache.
This is a list of operations the RDBMS uses to process the data change. If a parse tree already exists for this statement, the RDBMS can omit this step.
*Step 2: Execute Statement
The RDBMS performs all processing to execute the DML statement. For update or delete statements, the server process will retrieve the data from disk into the buffer cache, implicitly acquire a lock on the data to be changed, and then make the specified data change in the buffer cache.
A lock is an Oracle internal resource that one user process acquires before updating or deleting existing data to prevent other users from doing the same thing. For insert statements, the server process retrieves a block from a disk that has enough space available to house the new row of data, and places that new row into the block. Also, part of executing the DML statement is writing the data to the undo segment acquired for that transaction. A lock must be acquired on the undo segment to write changes to an undo segment as well.
Acquiring a lock is how one Oracle user says to the other users, "Hey! Hands off this data! I'm changing it now, so that means you can't have it until I let go of my lock!" Locks can be acquired at the row level implicitly as part of update or delete statements, or at the table level through explicit methods.
*Step 3: Generate Redo Information
The redo log buffer stores redo or data-change information produced as the result of DML operations running in user sessions. After issuing DML statements, the user process must write a redo entry to the redo log buffer. In this way, Oracle can recover a data change if damage is later done to the disk files containing Oracle data.
*Moving Data Changes
Once the DML statement has been executed, there is no further need to fetch values, as there was for select statements. However, as with select statements, the execution plan for the DML statement sticks around in the library cache for a variable period of time in case another user tries to execute the same statement. The changed blocks in the buffer cache are now considered "dirty" because the versions in the buffer cache and on disk are no longer identical.
Those dirty buffers stick around in the buffer cache as well, but they will need to be copied to disk eventually in order for Oracle not to lose the data changes made. Also, new information appears in the redo log buffer as a result of the data changes made by the DML statement. By making all the data changes in memory, Oracle is able to achieve superior response to DML statements.
This is achieved because it is faster to change and manipulate data in memory rather than on the disk.
Secondly, the user does not have to wait for the changed data to be written back to the disk.
Oracle runs two background processes called DBW0 and LGWR that write the data changes from buffer cache and redo log buffer to disk; these processes are asynchronous, meaning that they occur sometime after the user actually made the change. The following pages explain the role of each background process.
*DBW0 Background Process
Called the database writer process, the DBW0 background process writes dirty data blocks from buffer cache to disk. Historically, this process is also called DBWR, but in more recent versions of Oracle, this term has become somewhat obsolete because Oracle now supports use of more than one database writer process.
The writes are done for any of the following reasons:
- The server process needs to make room in the buffer cache to read more data in for user processes.
- DBW0 is told to write data to disk.
- Every three seconds due to a timeout.
- The number of dirty buffers reaches a threshold value.
The event that causes LGWR to write to disk is called a checkpoint. You will learn more about checkpoints in another lesson. Because Oracle enables multiple database writers to run on the host machine, the database writing process is referred to as DBW0, where 0 can be any digit between zero and nine. Oracle accepts a maximum of ten database writer processes for a single instance.
*LGWR Background Process
Called the log writer process, the LGWR background process writes redo log entries from the redo log buffer in memory to online redo log files on disk. LGWR has some other specialized functions related to the management of redo information that you will learn about in another course.
The redo log buffer writes to the redo log file under the following situations:
- When a transaction commits
- When the redo log buffer is one-third full
- When there is more than a megabyte of changes recorded in the redo log buffer
- Before DBW0 writes modified blocks in the database buffer cache to the datafiles
In this lesson, you learned that the undo segment is a database object that stores old versions of data being changed by DML statements issued by the user process.
You also learned that while the operations used to execute DML statements follow the same pattern as those for select statements, there are differences, especially when an update, delete, or insert statement is used.
Finally, you learned how the DBW0 and LGWR background processes are used to move data changes from memory to disk.
Lesson 7. Processing Commit Statements
The commit statement makes permanent any data change the user process issued to the Oracle database.After completing this lesson, you should be able to:
- Identify the function of the commit statement
- Describe how a commit statement is processed
- Explain how SCNs are used in Oracle
Issuing a commit statement ends the current transaction by making permanent any data change the user process may have issued to the Oracle database. A rollback statement discards the data change in favor of how the data appeared before the change was made.
The undo segment is how Oracle manages to offer this functionality. By keeping a copy of the old data in the undo segment for the duration of the transaction, Oracle is able to discard any change made by the transaction until the commit statement is issued.
Before proceeding any further, make sure you understand the following important point — issuing commit does not imply that the data modified by the user process is safely written into the disk by the DBW0 process. Only a checkpoint, a timeout, or a need for room in the buffer cache for blocks requested by users will make DBW0 write dirty blocks to disk. With that fact in mind, what exactly does processing a commit statement consist of?
*Release Table/Row Locks
The commit statement releases all row locks (or even table locks, if any were acquired) held by the user transaction issuing the commit statement. Other users can then modify the rows (or tables) previously locked by this user.
*Release Undo Segment Locks
Changes to undo segments are subject to the same locking mechanisms as other objects. Once the change is committed, the space to hold data for that transaction in the undo segment is available for another user's transaction. However, Oracle is lazy in that it does not actually discard or remove any information from the undo segment. Instead, Oracle merely overwrites the undo segment contents when the space is needed by another transaction.
*Generate Redo for Committed Transaction
Once the commit takes place, a redo entry is generated by the user process stating that all the changes associated with that transaction have now been committed by the user. A commit also results in flushing the content of the redo log buffer into redo log files — committed as well as uncommitted statements.
An interesting question remains: how does Oracle know which DML statement redo entries to associate with each transaction? The answer is the system change numbers (SCNs).
An SCN is an ID that Oracle generates for each and every transaction that a user process engages.
Every redo entry for every data change lists the change made and the SCN the change is associated with. The redo entry for the commit also identifies the SCN and simply notes that this SCN has been committed. Thus, Oracle can keep easy track of the status of every transaction via the SCN.
In this lesson, you learned that the commit statement ends the current transaction by making permanent any data change the user process may have issued to the Oracle database. However, this does not necessarily mean that the data has been written to the disk. That happens only at a checkpoint, a timeout, or when DBW0 writes dirty blocks to disk.
You also learned the stages of processing a commit statement: table/row locks are released; undo segment locks are released; and a redo entry is generated.
Finally, you learned that Oracle uses SCNs to track the status of each and every transaction that a user process engages.
Lesson 8. Common Database Administration Tools
In the past few years, there has been an explosion in the use of administrative tools for Oracle databases. The tools can be used by DBAs to make their jobs much easier.After completing this lesson, you should be able to:
- Identify common database administrative tools
Administrative tools are designed to simplify many aspects of Oracle database administration, including tablespace, instance, storage, object, and backup and recovery management.
Administrative tools such as Oracle Enterprise Manager (shown here) have matured and expanded their functionality, and these tools have become important in the repertoire of every DBA.
Therefore, be prepared to see some questions on administrative tools on the Oracle9i DBA Fundamentals I exam.
The most common database administration tools that you will use as DBA of an Oracle9i database are the various configuration assistants. These assistants are used to create databases, configure Oracle networking and migrate databases from previous versions to Oracle9i.
The Database Configuration Assistant is used to create the databases and/or database templates.
Templates are definitions of databases that you can create and store and later use in order to create similar databases. This tool has been enhanced to handle most aspects of database configuration.
Net Configuration Assistant is another tool that you can use to configure and administer Oracle networking. This tool helps you to create listener.ora or tnsnames.ora files.
Oracle Data Migration Assistant can be used to migrate databases from lower releases to Oracle9i. Where appropriate, we'll discuss the use of these tools in addition to covering the command-line options you may need to execute in order to accomplish administrative tasks.
The final tools to be used by DBAs to manage the Oracle database include Oracle Enterprise Manager and SQL*Plus. These tools are used for common administrative tasks.
In addition, SQL*Plus can be used by end users for accessing and manipulating data in the Oracle database.
For the most part, we'll emphasize how to execute common administrative tasks from within SQL*Plus using command-line interface (CLI) statements rather than graphical user interface (GUI) processes.
In this lesson, you learned about the most common database administrative tools used by DBAs of Oracle9i databases.
You learned that the Database Configuration Assistant can be used to create databases. The Net Configuration Assistant is used to configure and administer Oracle networking. And the Oracle Migration Assistant can be used to migrate databases to Oracle9i.
Finally, you learned that other database tools, such as Oracle Enterprise Manager and SQL*Plus, exist for common administrative tasks.
Lesson 9. Oracle Universal Installer
The Oracle Universal Installer is a versatile tool that Oracle provides in order to make the installation of Oracle software simple, interactive, and wizard-driven.After completing this lesson, you should be able to:
- Identify features of the Oracle Universal Installer
The Oracle Universal Installer (OUI) is a wizard-driven tool that simplifies the installation of Oracle.
The OUI interface is the same in all operating system platforms because it uses the Java run-time environment, enabling it to be used on multiple platforms.
The first thing you will undoubtedly notice when installing your Oracle database is that many versions of Oracle for various host systems now come with the Oracle Universal Installer.
Here, the Oracle Universal Installer is shown running in Windows environments. However, it is important to note that the same version of Oracle Universal Installer used in Windows environments also works for Sun Solaris.
There won't likely be too many questions about OUI on the exam, but you should understand its capabilities nevertheless.
The following pages list some of the features of OUI.
The look and feel of Oracle Universal Installer and Packager is nice, and at any step of the way, you can obtain help with installing your Oracle software.
Some of the new features of this interface include the capability to install Oracle database software, client software, and management and integration software unattended.
OUI not only installs the database software, but it also provides you with an option to create different types of databases using Configuration Assistants. Other lessons cover more details.
OUI accepts automated software installation if you define a response file for noninteractive installation of Oracle products on a machine. The response file contains information and variable settings that the Universal Installer needs in order to complete an Oracle software installation.
OUI tracks its own activities with a log file, showing the activities of the Universal Installer and Packager.
In this lesson, you learned that the Oracle Universal Installer is a tool provided by Oracle to make the installation of the Oracle software a simple process for the DBA.
You also learned that the OUI interface uses the Java run-time environment, enabling it to look the same in all operating system platforms. Finally, you learned about some of the features of the OUI, including the fact that it accepts automated installation if you define a response file.
Lesson 10. Installing Oracle9i Database
There are a number of different options you have when installing Oracle9i to your hard drive. Remember that Oracle9i is an application requiring over one gigabyte of space just for the software.After completing this lesson, you should be able to:
- Describe how Oracle is installed on a UNIX or Windows system
- Express the requirements necessary to install Oracle
- Install Oracle using the Oracle Universal Installer
- Explain how Oracle can be installed unattended
You should be aware of a few issues regarding software installation. Oracle9i is a large application that comes on three CDs, and it requires well over 1GB of space on your hard drive just for the application software. In addition, on UNIX systems you will be required to enter mount and umount commands for your CD-ROM drive repeatedly as Oracle switches from one CD for installation to another.
*Mounting the CD-ROM Drive
The booklet that comes with your Oracle9i software distribution CDs has an explanation of how this process is executed. For example, here you see the command used for several operating systems.
# HP-UX
nohup /usr/sbin/pfs_mountd &
nohup /usr/sbin/pfsd &
/usr/sbin/pfs_mount -t rrip -x unix /def/dsk/c5t2d0/SD_CDROM
# IBM AIX
mount -r -v cdrfs /dev/cd0 /cdrom
# SuSE Linux
mount -t iso9660 /dev/cdrom /cdrom
# SPARC/Solaris
mount -r -F hsfs /dev/dsk/c0t6d0s2 /cdrom
Additionally, you should be aware of the following steps to unmount the CD-ROM drive in order to eject the current disk and replace it with the next one Oracle requires:
1. In your telnet or xterm window, press the Enter key to return to the shell prompt.
2. Type cd / to return to the root directory.
3. Type su and press Enter. This will give you the root privileges you need to unmount the CD-ROM before ejecting. You'll need the password for root in order to become superuser. Involve your system administrator if you don't already have this privilege in your organization.
4. Type unmount/cdrom to unmount the CD-ROM drive. If you try to do this while logged in as oracle, you'll likely get errors indicating the device is busy. If so, return to step 3.
5. Eject the current CD from the CD-ROM drive and replace it with the disk OUI requested.
6. Enter the mount command appropriate for your system.
7. Return to OUI and click OK to continue.
Windows users of Oracle won't need to worry, as changing CDs for installation is more common in those environments. As with all Oracle databases, you should try to have at least three separate disk devices available to dedicate to Oracle resources. For large enterprise installations, you will definitely want at least six or seven, and perhaps as many as 20 to 30, depending on what sort of operation you plan to use your Oracle database for.
*Memory Constraints
For smaller machines, you may find Oracle9i Enterprise Edition difficult to install due to memory constraints, even if you follow Oracle's recommendation of having 128MB of RAM at a minimum.
In reality, you should have 256 to 512MB of RAM available on the host machine, and even then, the installation process will run slowly because OUI runs completely within a Java Virtual Machine environment.
However, you can tailor your Oracle configuration later to minimize the amount of memory the database will require.
We can offer a couple of additional suggestions to reduce problems when installing Oracle9i on smaller machines.
You can choose the Custom Installation option in the wizard step for defining the type of Oracle9i installation you want to perform, which requires less memory for the installation. This is a good option for installing Oracle9i on machines with 256 to 384MB of RAM.
Otherwise, if you want to run the typical installation, you should increase the available real memory on the machine hosting the Oracle database to 512MB of RAM or more.
*Using the OUI
On starting the OUI, the following occurs:
You will be prompted whether you want to install the Enterprise or Standard Edition of the software or custom install the software.
You will then be prompted whether you want to install just the software or you want to have a general installation (install software and preconfigured database) or an installation with a specific type of database.
The databases are created during the installation by using the Configuration Assistant tool.
You will be asked questions related to the software installation location, and if you choose to create a database, you will be prompted for the database name and the location for the database files.
If you have chosen custom installation, you will be prompted to select the components you want to install. Make sure you include OUI in your selection.
Database Configuration Assistant will be invoked to assist in creating the database. Then the Oracle Net Configuration Assistant will be invoked to create necessary network configuration files and start the listener process. If you are installing in UNIX environment, you will be prompted to run root.sh file to handle configuration.
OUI creates the oraInventory directory the first time it is run to keep an inventory of products that it installs. OUI creates a file in this directory called installActions.log that stores the log of the recent installation. In UNIX, the location of this directory is stored in a file called oraInst.loc.
*Unattended Installation
You may perform a noninteractive installation of the Oracle9i software by supplying OUI with a response file. OUI uses the information provided in the response file to provide answers to the Installer prompts and completes the installation. For further details, refer to Oracle installation documents provided with your distribution software.
Operating system-specific information for installing Oracle is not tested on the exam. Nevertheless, you should attempt installation of Oracle9i at least once before taking the Oracle9i DBA Fundamentals I exam to give yourself the hands-on experience necessary for practice of database administration in real life.
Topic 10.1 Exercises
* Exercise 1
Try installing Oracle9i on your system.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Determine whether your system has the disk space and memory to install Oracle9i. |
| 2 | Use the Oracle Universal Installer to install Oracle9i. |
| 3 | Choose the type of installation (general or custom). |
| 4 | View the installation log. |
In this lesson, you learned how Oracle9i is installed on UNIX and Windows systems. On UNIX systems, the DBA will be required to enter mount and unmount commands for the CD-ROM drive. Windows systems will just require the DBA to change disks. On both systems, 1GB of space is needed on the hard drive for the software.
You also learned that Oracle recommends 128MB of RAM, but 256 to 512MB of RAM should be used when possible. You learned some tips for installation when installing Oracle9i on smaller machines.
Finally, you learned how to use the Oracle Universal Installer to install Oracle. You learned that by providing the OUI with a response file, a noninteractive installation of the software may be performed.
Lesson 11. Optimal Flexible Architecture
Optimal Flexible Architecture (OFA) is an industry standard that defines how Oracle databases should be set up, minimizing support headaches for DBAs.After completing this lesson, you should be able to:
- Explain why an industry standard for setting up Oracle databases was necessary
- Define Optimal Flexible Architecture
- Describe the benefits of using Optimal Flexible Architecture
Oracle databases consist of many different files residing within a host system. Some of these files pertain to the application software, whereas others are used for storing actual data in your database. Still others are used for administering your database. As Oracle evolved into a complex and powerful software product, DBAs faced numerous challenges in determining the best way to lay out the Oracle software, database, and administrative files on their host systems in order to effectively manage the task of administering Oracle databases.
In response to this task, the Oracle Corporation published a specification identifying a standard filesystem configuration that DBAs could use in order to lay out the files of a working Oracle database system. The underlying idea was that if DBAs followed this standard for every database used in the organization, it would be much easier for others to find where the filesystem components were located, rather than forcing others to dig around for each component. The result was an Oracle standard called Optimal Flexible Architecture (OFA). Let's now explore OFA in more detail.
*Defining OFA
Optimal Flexible Architecture (OFA) is an industry standard that defines how to set up Oracle software and Oracle databases.
OFA provides maximum flexibility in supporting multiple versions of Oracle software and a single listener process to support oracle instances that may be running under different versions of Oracle Software.
This brings us to a concept known as ORACLE_HOME. ORACLE_HOME corresponds to the environment in which Oracle products run.
The environment could be the location of the installed products files (/u01/app/oracle/product/9.0.1 for UNIX, or C:\oracle\ora90 in Windows) or the PATH variable pointing to the location of the binary files and, in the case of Windows NT, the registry entries, service names, and program groups.
*OFA Benefits
OFA has been a boon to many DBAs looking to minimize support headaches. Let's now explore why we might want to use OFA rather than just simply come up with our own organizational standards for database layout.
OFA is designed to organize large amounts of database data files and Oracle software on disks to improve the performance of the database and minimize I/O contention among many disks that house the databases. OFA is designed to be flexible enough to facilitate the growth of the databases.
OFA encourages consistent database file-naming conventions. This enables the DBA to clearly distinguish the data files, control files, and other files that belong to one database from the other. OFA also helps in associating data files to their corresponding tablespaces.
By keeping the contents of the tablespace separate, OFA minimizes fragmentation and I/O contention. Take for example the separation of tables and indexes in different tablespaces. This gives the flexibility to move the tablespaces to different disk drives in the event the I/O contention goes up.
OFA supports multiple ORACLE_HOME locations. This enables you to execute multiple releases of Oracle concurrently. For example, you could have a database instance running on 8.1.7 while at the same time have another database instance running on 9.0.1 without causing any disruption to each other.
OFA enables you to have one listener spawning connections to databases of multiple Oracle software home directories. OFA keeps the administration information of the each of the databases separate.
In this lesson, you learned that Optimal Flexible Architecture (OFA) was designed as a response to the challenges that DBAs faced in determining the best way to lay out the Oracle software, database, and administrative files on their host systems to facilitate effective administration.
You learned that OFA is an industry standard that defines how to set up Oracle software and Oracle databases.
Finally, you learned some of the benefits of using OFA. Specifically, OFA organizes large amounts of files, is flexible enough to allow growth, offers consistent database file-naming conventions, and enables the use of one listener with multiple Oracle products.
Lesson 12. Oracle Authentication
In Oracle9i, there are two methods of providing administrative authentication: operating system authentication and password file authentication. Both types of authentication require the DBA to provide a password to gain access for administrative tasks.After completing this lesson, you should be able to:
- Identify the two administrative authentication options in Oracle9i
- Explain the SYS and SYSTEM users
- Define the data dictionary
How you plan to support the Oracle database you create determines to a large extent how you will set up Oracle to handle administrative authentication. Authentication requires the DBA who wants to perform administrative tasks to provide a password in order to gain entry onto the machine hosting Oracle, the database itself, or both.
There are two methods of providing administrative authentication: operating system and password file authentication. If you plan to connect to the machine hosting the Oracle database via telnet, xterm, or a Windows client such as Citrix Metaframe in order to administer the database, operating system authentication might be acceptable. But realistically, if you plan to manage the site from software running on your desktop computer, such as Enterprise Manager, then you should set up a password file for authentication.
Another nice feature about a password file is that it enables many DBAs to manage databases, each with varying levels of control. For example, the organization might want the junior DBA to handle backups and user creation, but not the startup and shutdown of the instance. Password files work well to support organizations wanting a team of DBAs to have a range of capabilities on the machine.
Another round of introductions is in order. SYS and SYSTEM are two users Oracle creates when you install your database. Each has its own default password. The default password for SYS is change_on_install, and for SYSTEM it is manager. Be careful to protect the passwords for both these users by changing them after installing Oracle. These two privileged users have the power to administer most any feature of the Oracle database. SYS is more important than SYSTEM because SYS will wind up owning all Oracle system tables from which the data dictionary is derived.
The Oracle data dictionary is the system resource you will turn to in order to find out just about anything about your database, from which users own what objects, to the initialization parameter settings, to performance monitoring, and more.
There are two basic categories for Oracle database views: those that show information about database objects and those that show dynamic performance. The views showing information about objects are the data dictionary views. The views showing information about performance are dynamic performance views. You'll learn more about setting up and using the data dictionary.
In this lesson, you learned that administrative authentication requires the DBA to provide a password in order to gain entry for administrative tasks onto the machine hosting Oracle, the database itself, or both. In Oracle9i, there are two methods of administrative authentication: operating system and password file authentication.
You also learned that SYS and SYSTEM are two users Oracle creates when you install your database. These users have the power to administer most features of the Oracle database.
Finally, you learned that the data dictionary is a system resource that you can use to find out just about anything about your database.
Lesson 13. Using Operating System Authentication
If you plan to connect to the machine hosting the Oracle database via telnet, xterm, or a Windows client, you can use operating system authentication for authenticating administrators.After completing this lesson, you should be able to:
- Identify advantages and disadvantages of operating system authentication
- Implement operating system authentication in Oracle
- Execute steps for implementing operating system authentication for Windows
Operating system authentication offers the comfort of a familiar face to old-school UNIX folks. Because of this, the discussion of operating system authentication will focus primarily on its implementation in UNIX. However, operating system authentication has few real advantages and many disadvantages compared to the password-file method of authentication.
The main benefit operating system authentication offers is easy login to Oracle via the slash (/) character, as shown below.
UNIX(r) SYSTEM V TTYP01 (23.45.67.98)
Login: bobcat
Password:
User connected. Today is 12/17/99 14:15:34
[companyx] /home/bobcat/> sqlplus /
SQL*PLUS Version 8.1.7.0.0
(c) 2001 Oracle Corporation(c) All rights reserved.
Connected to Oracle9i Enterprise Edition 9.0.1 - Production
With the Java option.
SQL>
The disadvantages to operating system authentication are many. For one thing, you must have a machine login to use Oracle. When might this pose a problem? For example, you may not want to make the host machine's command prompt accessible to your 10,000+ user base for a production system. For development and test environments, however, operating system authentication may be fine.
*Using OS Authentication
To use operating system authentication, a special group called dba must be created on the operating system before you even install your Oracle software.
Later, when Oracle is installed and configured, you can log into the operating system via telnet as a user belonging to the dba group (such as the Oracle software owner).
From there, you run SQL*Plus in line mode and perform startup and shutdown operations after issuing the connect name as sysdba command and then providing the appropriate password.
The sysdba keyword denotes a collection of privileges that are used for the administration of Oracle databases, including the capability to start and stop the database. Simple usage is shown here:
SQL> connect sys as sysdba
Password:
Connected.
Those old-school Oracle DBAs who are familiar with the connect internal command should know that connect internal is gradually being desupported along with Server Manager in Oracle8i and later releases. Use connect sys as sysdba instead.
Oracle creates some other operating system roles as part of its UNIX installation that must be granted to the DBA, such as osdba and osoper.
These operating system roles are given to the Oracle software owner and must be granted via operating system commands to other operating system users who would be DBAs. These roles cannot be revoked or granted from within Oracle.
However, there are two equivalent Oracle privileges used when you authenticate with a password file — sysdba and sysoper, respectively.
There are some small differences between osoper/sysoper and osdba/sysdba, which you may use to your advantage for breaking out DBA roles and responsibilities. The osoper role and sysoper privilege enable you to start and stop the instance, mount or open the database, back up the database, initiate archiving redo logs, initiate database recovery, and change database access to restricted session mode.
The osdba and sysdba roles offer the same privileges as osoper and sysoper, and add the capability to execute and administer all Oracle system privileges, the create database privilege, and all privileges required for time-based incomplete database recovery. Obviously, osdba or sysdba is given to the DBA to ultimately be responsible for the operation of the database.
The implementation of operating system authentication in Oracle depends heavily on the operating system you use. Because operating system-specific issues are not part of the Oracle9i DBA Fundamentals I exam, they will not be covered here. If you need more information on operating system authentication, consult the appropriate operating system-specific Oracle administrative manual.
You need to set the REMOTE_LOGIN_PASSWORDFILE = NONE when your database is initially created in order to configure operating system authentication. This ensures that you can only start and stop your database from a terminal session on the actual machine hosting the Oracle database or from the console for that machine. In Oracle9i, the default value for this parameter is NONE.
*Windows Operating System Authentication
When setting up operating system authentication on Windows, you must execute the following additional steps:
- Create two new local Windows NT users' groups called ORA_SID_DBA and ORA_SID_OPER that are specific to an instance, or ORA_DBA and ORA_OPER that are not specific to an instance.
- Add a Windows NT operating system user to that group. Once you access this domain, you are automatically validated as an authorized DBA.
- Ensure that you have the following line in your sqlnet.ora file: SQLNET.AUTHENTICATION_SERVICES = (NTS).
- Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE in your init.ora file.
- Connect to the database with the privilege SYSDBA or SYSOPER.
SQL> CONNECT JASON AS SYSDBA
Password:
Connected.
Question 35
Question 36
Question 37
Question 38
In this lesson, you learned that operating system authentication, which will be familiar to UNIX users, has few advantages compared to password file authentication. The main benefit operating system authentication offers is easy login to Oracle via the slash (/) character.
You also learned how to implement operating system authentication in UNIX. A special group, called dba, must be created on the operating system before the Oracle software is installed. You learned some of the operating system roles that Oracle creates as part of its UNIX installation.
Finally, you learned the additional steps to take to set up operating system authentication on Windows.
Lesson 14. Using Password File Authentication
Password file authentication enables many DBAs to manage databases, each with varying levels of control.After completing this lesson, you should be able to:
- Create the password file for authenticating users
- Provide administrative access to the database
- Connect to the database as a user
- Identify password file default locations
Oracle's other method of authenticating DBAs is the password file. It is far more important that you understand this option than operating system authentication for the Oracle9i DBA Fundamentals I exam.
*Creating the Password File
The DBA creates the password file, and passwords for all others permitted to administer Oracle are stored in the file.
The password file is created with the ORAPWD utility. The name of this executable varies by operating system. For example, it is orapwd on both UNIX and on Windows.
When executing ORAPWD, you will pass three parameters: FILE, PASSWORD, and ENTRIES.
To determine what to specify for FILE, you usually place the password file in $ORACLE_HOME/dbs and name it orapwsid.pwd, substituting the name of your database for sid.
For PASSWORD, be aware that as you define the password for your password file, you are also simultaneously assigning the password for logging into Oracle as SYS.
Later, if the DBA connects as SYS and issues the alter user name identified by password command, the password for SYS, and the password file are all changed.
The final parameter is ENTRIES, specifying the number of user entries allowed for the password file.
Be careful, because you can't add more later without deleting and re-creating the password file, which is risky.
The actual execution of ORAPWD in Windows may look something like this, from the command line:
D:\oracle\bin\>orapwd FILE=D:\oracle\dbs\orapworgdb01.pwd
PASSWORD=jason ENTRIES=5
In UNIX, it may look something like this:
/home/oracle> orapwd \
FILE=/u01/app/oracle/product/9.0.1/dbs/orapwdorgdb01.pwd \
PASSWORD=jason ENTRIES=5
*Providing Access
After creating the password file, you must do a few other things to provide administrative access to the database.
First, set the value for the REMOTE_LOGIN_PASSWORDFILE parameter in the initsid.ora parameter file. This parameter accepts none, shared, and exclusive as its values.
The none setting means the database won't allow privileged sessions over nonsecure connections.
When operating system authentication is used, the REMOTE_LOGIN_PASSWORDFILE is set to none to disallow remote database administration.
Setting REMOTE_LOGIN_PASSWORDFILE to shared means that only SYS can log into Oracle to perform administrative functions remotely.
Finally, setting REMOTE_LOGIN_PASSWORDFILE to exclusive means that a password file exists and any user/password combination in the password file can log into Oracle remotely and administer that instance.
If the exclusive setting is used, the DBA may use the create user command in Oracle to create the users who are added to the password file, and grant sysoper and/or sysdba system privileges to those users. After that, users can log into the database as themselves with all administrator privileges.
In addition, exclusive indicates that only one instance can use the password file and that the password file contains names other than SYS. shared indicates that more than one instance can use the password file (but the only user recognized by the password file is SYS).
*Connecting to the Database
After creating the password file with the ORAPWD utility and setting the REMOTE_LOGIN_PASSWORDFILE parameter to exclusive in order to administer a database remotely, the DBA can then connect to the database as a user with sysdba privileges as shown here.
SQL> CONNECT sys AS SYSDBA;
Password:
Connected.
Remember two important points about password files. First, to find out which users are in the database password file, use the V$PWFILE_USERS dynamic performance view. (More on the data dictionary will be presented in another course.) Second, any object created by anyone logging in as sysdba or sysoper will be owned by SYS.
*Default Locations
Password file default locations depend on the operating system hosting the Oracle database.
On UNIX, the password files are usually located in the $ORACLE_HOME/dbs directory.
On Windows, the password file is usually located in the %ORACLE_HOME%\DATABASE directory.
You can specify a nondefault location of the password file in the Windows registry with the key ORA_SID_PWFILE. You can also set the password during installation by using the Custom Installation option.
In this lesson, you learned how to create the password file using the ORAPWD utility. You learned how to pass three parameters, FILE, PASSWORD, and ENTRIES, using ORAPWD. You learned how to perform tasks to allow administrative access to the database.
You also learned how to connect to the database as a user after creating the password file and setting the REMOTE_LOGIN_PASSWORDFILE parameter to exclusive.
Finally, you learned that the default location of the password file depended on the operating system hosting Oracle. You also learned how to specify a nondefault location of the password file.
Lesson 15. Oracle Enterprise Manager Components
The basic purpose of the Oracle Enterprise Manager is to assist DBAs in support of Oracle databases by providing a GUI interface for handling most administrative tasks.After completing this lesson, you should be able to:
- Describe the Oracle Enterprise Manager (OEM)
- Identify the applications available within the OEM
- Explain the OEM architecture types
- Use the SQL*Plus Worksheet and Instance Management tools
Oracle Enterprise Manager (OEM) is a suite of applications that enable you to manage your Oracle database in a GUI. Almost anything you can do from SQL*Plus, you can do from the OEM console, provided you have set up a password file for remote database administration. If you do not have a password file set up for administering your Oracle database remotely, then you cannot start up and shut down the Oracle database using OEM, but you can do most anything else.
There is no such thing as easy database administration, but using the administrative tools available in the OEM console can simplify many areas of managing your database. OEM is usually run from your desktop. Assuming you use Windows, the location of OEM under your Start button can vary.
*OEM Applications
Many applications and functions that were separate from the OEM console in previous releases have been integrated into the 9.0 release. The following list identifies the application functions available within OEM, along with a brief description of their use:
Instance Management Handles management of an Oracle instance, including session, in-doubt transaction, and initialization parameter information
Schema Management Manages table, index, cluster, and other object creation and management in an Oracle database
Security Management Handles user access privileges and role administration
Storage Management Handles configuration and management of logical and physical disk resources for the Oracle database
Workspace Management Manages one or more workspaces, environments that users share to make changes to data
Backup Management Tools Manages the backup, restoration, and recovery of databases, and manages redo log files
Data Management Tools Automates the transfer of data (loading, importing, and exporting) to and from databases
SQL*Plus Worksheet Executes SQL statements from scripts in a GUI — more advanced than SQL*Plus
In addition to the administrator tools listed previously, other applications are available that you may install on your Oracle database. The following list describes these management packs:
Diagnostic Pack Includes tools to help DBAs monitor, diagnose, and plan databases
Tuning Pack Includes tools to help DBAs tune databases for higher performance
Change Management Pack Includes the Change Manager Application and advanced tools to modify, reproduce, and compare database objects
Management Pack for Oracle Applications Automates monitoring and administration in Oracle environments
Management Pack for SAP R/3 Automates monitoring and administration of SAP R/3 systems
Note: These management packs require a separate license and are for use with Oracle9i Enterprise edition only. A Standard Management Pack that includes tools and tests from more specialized packs is available for the Standard edition without an additional license.
*OEM Architecture
There are two main types of deployment models for Oracle Enterprise Manager: a three-tier deployment and a client/server deployment. Selecting Launch standalone from the Enterprise Manager login prompt deploys the client/server architecture, while selecting Login to the Oracle Management Server deploys the three-tier architecture.
In the three-tier deployment model, the first tier is the Java-based console and integrated applications that can be installed or run from a Web browser. The second tier is the Oracle Management Server (OMS) and repository. The function of the OMS is to provide centralized intelligence and distributed control between clients and managed nodes. The third tier is composed of targets such as databases, nodes, or other managed services.
The three-tier model should be used when you need to manage multiple target types, share administrative data, automate administrative tasks, use a browser-based console, or require proactive notification of potential issues.
In the client/server deployment model, only the console and management application is deployed. With the Enterprise Manager 9.0 release, the client connects directly to the database.
This model should be used when you only need to administer Oracle databases.
*Using SQL*Plus Worksheet
The SQL*Plus Worksheet is fairly easy to use, and you can start it from within the OEM console by opening the Tools menu, pointing to Database Applications, and choosing SQL*Plus Worksheet. The regular SQL*Plus is available by opening the Start menu, pointing to Programs and then Oracle-oraHome90, and choosing a command from the Application Development submenu.
Once you've started the tool and logged into Oracle, you should see two windows. The top window is where you enter your SQL statements as you would in SQL*Plus.
The bottom window is where you see the output generated by Oracle in response to your SQL query.
*Using Instance Management
Because so much of your effort will focus on managing the Oracle instance and opening and closing the Oracle database, we will focus on Instance Management. The basic purpose of Instance Management is managing the Oracle instance. You can start and stop the instance with this tool (provided you've set up your password file — more information on how to do this in a moment), view and modify initsid.ora parameters, view current sessions and in-doubt transactions, and apply database configuration information you have available on your desktop.
To manage an instance, click on the Instance node in the navigator window on the left-hand side of the OEM console. The login information you provided when you started OEM is used to log in.
On the left-hand window is the navigator window where there are several nodes you can drill down into to find information. You drill into each node by clicking on the plus sign (+) to the left of the node. The names of each node are self-explanatory.
For example, if you drill into the Sessions node, you will see all the sessions currently happening in Oracle listed below the node.
On the right side is the work interface. If you click on the name of the node or the File Folder icon to the left of that name, the relevant information will be displayed in the work window. As another example, if you click on the name of one of the connected sessions in the navigator window, you will see some additional information about that session appearing in the work window.
Along the top of the interface is a set of several menus. From left to right, they are File, Navigator, Object, Tools, Configuration, and Help. The options under these menus may change depending on what node you are in. The Help menu is always available to give you access to online help.
In this lesson, you learned that the Oracle Enterprise Manager (OEM) enables you as a DBA to handle most administrative tasks through a GUI interface. You learned about the applications available for OEM and their uses.
You also learned that there are two main types of deployment models for OEM: a three-tiered structure and a client/server structure that can only be used to administer Oracle databases.
Finally, you learned how to use the OEM console, SQL*Plus Worksheet, and Instance Management. You learned how to use the Instance Management interface which enables you to start and stop the instance, view and modify initsid.ora parameters, view current sessions and in-doubt transactions, and apply database configuration information, among other tasks.
Lesson 16. Course in Review
This lesson includes questions and material covering the entire course. You can use it to review and study for the exam.Topic 16.1 Review Notes
*Review Notes| 1. | Be sure you understand the difference between an Oracle instance and the Oracle database. |
| 2. | Know the main memory structures in Oracle. Be sure you can list the memory components for the exam. Also, understand the components of the PGA. |
| 3. | Understand the purpose of background processes in the Oracle database. At this point, you should be able to name at least two and describe their function. LGWR writes redo information to disk in the background, whereas DBW0 writes data blocks from the buffer cache to disk periodically. |
| 4. | Be sure you can distinguish between background, server, and network processes that operate in conjunction with the Oracle database. |
| 5. | Be sure you understand the different procedures Oracle undertakes to connect users to servers when the dedicated server or MTS architecture is being used. |
| 6. | Know the performance implications for using shared versus dedicated servers. Understand the circumstances where it is appropriate to use MTS versus dedicated servers as well. |
| 7. | Understand that the loss of a listener process will not affect existing connections to the database, but that it will prevent new users from connecting until the listener gets restarted. |
| 8. | Be sure you understand that SQL is a functional programming language, not a procedural language. Oracle has its own underlying mechanisms for data retrieval — you should be sure you understand these mechanisms for the exam. |
| 9. | Know the point at which Oracle server processes actually retrieve data into the buffer cache. |
| 10. | Be sure you can describe the function and purpose of undo segments, locks, and system change numbers in Oracle. |
| 11. | Be sure you can describe the process that a Oracle server executes in order to support DML changes to data, including how changes in actual data and redo entries are moved from memory to disk. |
| 12. | Be sure you can identify the administrative tools used by Oracle DBAs in order to administer the Oracle database. |
| 13. | Understand that the primary administrative tools for handling common DBA tasks are Oracle Enterprise Manager (GUI) and SQL*Plus (CLI). |
| 14. | Understand the purpose of Oracle Universal Installer (OUI) and how it differs from versions of Oracle Installer you may have used in Oracle8i or earlier editions of the Oracle database. |
| 15. | Be sure you understand the host system requirements Oracle9i imposes on your organization before upgrading to Oracle9i in your company. |
| 16. | Understand the basic OUI components required for noninteractive installation and where to look for information Oracle logged during installation. |
| 17. | Be sure you can define what OFA is and why it is beneficial to you as the DBA and to your organization. |
| 18. | Know what ORACLE_HOME means with respect to Oracle database running environments. |
| 19. | Understand that there are two methods for administrative authentication in Oracle: operating system and password file. In general, you should understand how to set up and use password file authentication. |
| 20. | Know that the Oracle9i DBA Fundamentals I exam will focus on use of password file authentication. |
| 21. | Remember that the REMOTE_LOGIN_PASSWORDFILE init.ora parameter is used for configuring use of password file authentication, whereas the ORAPWD utility is used for actually creating the password file. |
| 22. | Understand the SYS and SYSTEM users and their purpose in the Oracle database architecture. |
| 23. | Be sure you understand the basic purpose of Enterprise Manager, namely to assist DBAs in support of Oracle databases by providing a GUI interface for handling most administrative tasks. |
| 24. | Know the different categories of tools OEM provides. It is not important to know how to use them for the exam, but you should be able to identify the existence of tools identified in this discussion. |
| 25. | Be able to describe the role an Oracle Management Server plays within Enterprise Manager. |
Topic 16.2 Exam Preparation
This page contains a bulleted list of fast facts to review, or crib notes for the days leading up to the exam.- Several structures are used to connect users to an Oracle server. They include memory structures like the System Global Area (SGA) and Program Global Area (PGA), network processes like listeners and dispatchers, shared or dedicated server processes, and background processes like DBW0 and LGWR.
- The SGA consists of the buffer cache for storing recently accessed data blocks, the redo log buffer for storing redo entries until they can be written to disk, and the shared pool for storing parsed information about recently executed SQL for code sharing.
- The fundamental unit of storage in Oracle is the data block.
- SQL select statements are processed in the following way: A cursor or address in memory is opened, the statement is parsed, bind variables are created, the statement is executed, and values are fetched.
- SQL DML statements such as update, delete, and insert are processed in the following way: A cursor or address in memory is opened, the statement is parsed, and the statement is executed.
- Several background processes manage Oracle's capability to write data from the buffer cache and redo log buffer to appropriate areas on disk. They are DBW0 for writing data between disk and buffer cache, and LGWR for writing redo log entries between the redo log buffer and the online redo log on disk.
- DBW0 writes data to disk in three cases. They are: every three seconds (when a timeout occurs), during a checkpoint, or when the buffer cache is full or a server process needs to make room for buffers required by user processes.
- Server processes are like genies from the story of Aladdin because they retrieve data from disk into the buffer cache according to the user's command.
- There are two configurations for server processes: shared servers and dedicated servers. In dedicated servers, a listener process listens for users connecting to Oracle. When a listener hears a user, the listener tells Oracle to spawn a dedicated server. Each user process has its own server process available for retrieving data from disk.
- In shared server configurations (also called multithreaded server, or MTS), a user process attempts to connect to Oracle. The listener hears the connection and passes the user process to a dispatcher process. A limited number of server processes, each handling multiple user requests, is monitored by a dispatcher, which assigns user processes to a shared server based on which has the lightest load at the time of user connection.
- The commit statement may trigger Oracle to write changed data in the buffer cache to disk, but not necessarily. It only makes a redo log buffer entry that says all data changes associated with a particular transaction are now committed.
- Oracle Universal Installer is the software installer for Oracle products. It is written in Java and runs on multiple platforms.
- Universal Installer permits automated, noninteractive software installation through the use of a response file.
- When installing Oracle9i on certain platforms, you need to make sure that you install the software to a separate home directory. This is a requirement of the new version of Universal Installer.
- You can have Universal Installer install a preconfigured database for you with minimal user interaction. In this case, all scripts, such as catalog.sql and catproc.sql, are run automatically, and a few basic tablespaces, such as DATA, INDEX, and UNDOTBS, are created with the following information:
SYS password is change_on_install.
SYS as SYSDBA password is oracle.
SYSTEM password is manager.
- Two user authentication methods exist in Oracle: operating system authentication and password authentication.
- There are two privileges DBAs require to perform their function on the database. In password authentication environments, they are called sysdba and sysoper.
- To use password authentication, the DBA must create a password file using the ORAPWD utility.
- To start and stop a database, the DBA must connect as internal or sysdba. But note that though it is supported, internal is obsolete in Oracle9i.
- The tool used to start and stop the database in Oracle9i is SQL*Plus.
- Another tool for managing database administration activity is Oracle Enterprise Manager (OEM). OEM has many administrative tools available.
No comments:
Post a Comment