Connecting to a Database

y the initialization parameter, SHARED_POOL_SIZE in the parameter file. The components of the shared pool described below are used to parse an SQL statement. Library Cache The library cache stores the following information about the most recently used SQL statements: • Text of the statement • Parse tree, which is the compiled version of the statement • Execution plan, which defines the steps to be followed in running the statement as determined by the optimizer Since the library cache stores this information, if a query is reexecuted before its execution plan is aged out by other statements, the server process does not need to parse the statement. Thus the library cache helps improve the performance of applications. 1-11 Copyright  Oracle Corporation, 1998. All rights reserved. The Shared Pool • Size defined by SHARED_POOL_SIZE • Library cache contains statement text, parsed code, and an execution plan • Data dictionary cache contains table and column definitions and privileges Shared pool Library cache Data dictionary cache 1-16 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components Data Dictionary Cache The data dictionary cache, also known as the dictionary cache or row cache, is a part of the shared pool that stores the most recently used data dictionary information, such as table and column definitions, usernames, passwords, and privileges. During the parse phase, the server process looks for the information in the dictionary cache to resolve the object names specified in the SQL statement and to validate the access privileges. If necessary, the server process initiates the loading of this information from the data files. Oracle8: Database Administration 1-17 ...................................................................................................................................................... ...................................................................................................................................................... Running a Query When a query is processed, the server process looks for the blocks needed in the database buffer cache. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the buffer cache. Subsequent requests for the same block are likely to find the block in memory, and may not require physical reads. Function of the Database Buffer Cache The database buffer cache, or simply the buffer cache, is an area in the SGA that is used to store the most recently used data blocks. The size of each buffer in the buffer cache is equal to the size of a data block, and it is specified by the DB_BLOCK_SIZE parameter. The number of buffers is equal to the value of the DB_BLOCK_BUFFERS parameter. The Oracle server uses a least recently used (LRU) algorithm to age out buffers that have not been accessed recently to make way for new blocks to be accommodated in the buffer cache. 1-12 Copyright  Oracle Corporation 1998 All rights reserved Database Buffer Cache • Number of buffers defined by DB_BLOCK_BUFFERS • Size of a buffer based on DB_BLOCK_SIZE • Stores the most recently used blocks 1-18 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components The Program Global Area (PGA) is a memory region that contains data and controls information for a single server process or a single background process. In contrast to the SGA, which is shared and written to by several processes, the PGA, which is also called the process global area, is an area that is used by only one process. When using the dedicated server configuration, the PGA contains: • Sort area: which is used for any sort that may be necessary before rows are processed or returned to the user • Session information: such as user privileges for the session • Cursor state: which indicates the stage in the processing of various cursors that are currently used by the session • Stack space: containing the session variables The PGA is allocated when a process is created and deallocated when the process is terminated. Note Some of these structures are stored in the SGA when using an MTS configuration. 1-13 Copyright  Oracle Corporation, 1998. All rights reserved. Program Global Area (PGA) • Not shared and not writable • Contains – Sort area – Session information – Cursor state – Stack space PGAServer process Oracle8: Database Administration 1-19 ...................................................................................................................................................... ...................................................................................................................................................... Running a DML Statement Running a DML Statement A data manipulation language (DML) statement requires two phases of processing: • Parse, which is similar to the parse phase used for processing a query • Execute Execute Phase Consider an example in which a user executes an update command of the following form: UPDATE emp SET sal=sal*1.1 WHERE empno=7369; What follows are the steps used in executing the update statement: 1 The server process reads the data and rollback blocks from the data files, if they are not already in the buffer cache. 2 Copies of the blocks that are read are placed in the buffer cache. 3 The server process places locks on the data. 1-14 Copyright  Oracle Corporation, 1998. All rights reserved. Instance SGA Shared pool Library cache Redo log buffer Database buffer cache Data dictionary cache UPDATE emp SET sal=sal*1.1 WHERE empno=7369 Processing a DML Statement 1 Server process Control files Redo log files Data files Database 2 3 4 5 1-20 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components 4 The server process records the changes to be made to the rollback (before-image) and to the data (new value) in the redo log buffer. 5 The server process records the before-image to the rollback block and updates the data block, both in the database buffer cache. Both the changed blocks in the buffer cache are marked as dirty buffers—that is, buffers that are not the same as the corresponding blocks on the disk. Note The processing of a DELETE or INSERT command uses similar steps. The before-image for a delete contains the column values in the deleted row, while inserts just require the row location information to be stored in the rollback. Instructor Note • The slide explaining the processing of a DML command has builds. There are five stages. • You may want to ask the students what the DML commands are. In the current context we are referring to only the INSERT, UPDATE, and DELETE commands. Oracle8: Database Administration 1-21 ...................................................................................................................................................... ...................................................................................................................................................... Running a DML Statement Before making a change, the server process saves the old value into a rollback segment. This image is used to: • Undo the changes if the transaction is rolled back • Ensure that other transactions do not see uncommitted changes made by the DML statement (read consistency) • Recover the database to a consistent state in case of failures Rollback segments, like tables and indexes, exist in data files and parts of them are brought into the database buffer cache when required. 1-15 Copyright  Oracle Corporation, 1998. All rights reserved. Rollback Segment DML statement Old image New image Rollback segment Table 1-22 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components The server process records changes made by an instance in the redo log buffer, which is a part of the SGA. The redo log buffer has the following characteristics: • Its size in bytes is defined by the LOG_BUFFER parameter. • It stores redo records, which record changes—that is, the block that is changed, the location of the change, and the new value. A redo entry registers a change, but it makes no distinction between the type of block that is changed. So it cannot distinguish, for example, a change to a data block from a change to an index or a rollback block. • The redo log buffer is used sequentially, and changes made by one transaction may be interleaved with changes made by other transactions. • It is a circular buffer that is reused after it is filled up, but only after all the old redo entries are recorded in the redo log files. Note The redo log files are covered in more detail in the lesson “Maintaining Redo Log Files.” 1-16 Copyright  Oracle Corporation, 1998. All rights reserved. Redo Log Buffer • Size defined by LOG_BUFFER • Records changes made through the instance • Used sequentially • Circular buffer Oracle8: Database Administration 1-23 ...................................................................................................................................................... ...................................................................................................................................................... Running a DML Statement The server process records changes to rollback and data blocks in the buffer cache. The database writer (DBWR) writes the dirty buffers from the database buffer cache to the data files. It ensures that sufficient number of free buffers—buffers that can be overwritten when server processes need to read in blocks from the data files—are available in the database buffer cache. Database performance is improved because server processes only make changes in the buffer cache, and the DBWR defers writing to the data files until one of the following events occur: • The number of dirty buffers reaches a threshold value • A process scans a specified number of blocks when scanning for free buffers and cannot find any • A timeout occurs • A DBWR checkpoint can be triggered by various events such as closing of the database, (Checkpoint is a means of synchronizing the database buffer cache with the data file.) Note Checkpoints are covered in detail in the lesson “Managing Redo Log Files.” 1-17 Copyright  Oracle Corporation, 1998. All rights reserved. Instance SGA Shared pool DBWR Database buffer cache Database Writer (DBWR) Control files Redo log files Data files 1-24 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components The Log Writer (LGWR) is a background process that writes entries from the redo log buffer into the redo log files. The LGWR performs sequential writes to the redo log file under the following situations: • When the redo log buffer is one-third full • When a timeout occurs (every three seconds) • Before DBWR writes modified blocks in the database buffer cache to the data files • When a transaction commits Instructor Note You may want to inform the students that although there is a timeout, on a busy OLTP system, this may never occur because other events may occur more frequently. 1-18 Copyright  Oracle Corporation, 1998. All rights reserved. Instance SGA Shared pool Redo log buffer LGWR Log Writer (LGWR) Control files Redo log files Data files Oracle8: Database Administration 1-25 ...................................................................................................................................................... ...................................................................................................................................................... COMMIT Processing COMMIT Processing Oracle uses a fast commit mechanism that guarantees that the committed changes can be recovered in case of failures. System Change Number Whenever a transaction commits, the Oracle server assigns a commit System Change Number (SCN) to the transaction, which is monotonically incremented and is unique within the database. The SCN is used by the Oracle server as an internal “time stamp” to synchronize data and to provide read consistency when data is retrieved from the data files. Using the SCN allows the Oracle server to perform consistency checks without depending on the date and time of the operating system. Steps in Processing COMMITs When a COMMIT is issued, the following steps occur: 1 The server process places a commit record, along with the SCN, in the redo log buffer. 2 LGWR performs a contiguous write of all the redo log buffer entries up to and including the commit record to the redo log files. After this point, the Oracle server can guarantee that the changes will not be lost even in case of failures. 3 The user is informed that the COMMIT is complete. 1-19 Copyright  Oracle Corporation, 1998. All rights reserved. Shared pool Redo log buffer LGWR Control files Redo log files Data files Database COMMIT Processing Server process 1 2 User process 3 Database buffer cache4 SGA Instance 1-26 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components 4 The server process records information to indicate that the transaction is complete and that resource locks can be released. Flushing of the dirty buffers to the datafile is performed independently by DBWR, and can take place either before or after the commit. Note Rolling back a transaction does not trigger LGWR to write to disk. The Oracle server always rolls back uncommitted changes when recovering from failures. If there is a failure after a rollback, before the rollback entries are recorded on disk, the absence of a commit record is sufficient to ensure that the changes made by the transaction are rolled back. Advantages of the Fast COMMIT The use of these steps for processing COMMITs has the following advantages: • Sequential writes to the log files are faster than writing to different blocks in the data file. • Only minimal information that is necessary to record changes are written to the log files, whereas writing to the data files would require whole blocks of data to be written. • The database COMMIT piggybacks redo log records from multiple transactions requesting to commit at the same time into a single write. • Unless the redo log buffer is particularly full, only one synchronous write is required per transaction (can be less than one synchronous write per transaction). • The size of the transaction does not affect the amount of time needed for an actual COMMIT operation. Instructor Note • Even though LGWR may write through the operating system buffer cache, synchronous writes are used to ensure that confirmation of a commit is only made after data is written to disk. • The COMMIT processing slide has builds. There are four stages. Oracle8: Database Administration 1-27 ...................................................................................................................................................... ...................................................................................................................................................... Summary Summary Instructor Note • Lessons have a summary showing the following: - A summary slide highlighting the most important objectives for the lesson - A Quick Reference listing the initialization parameters, commands, and packages discussed in the lesson • You may want to ask the students how users specify the database they want to connect to, and emphasize the terms instance and database. 1-20 Copyright  Oracle Corporation, 1998. All rights reserved. Instance SGA Shared pool DBWR LGWR Database Control files Data files Redo logfiles User process Server process PGA Summary Password file Archived log files Parameter file 1-28 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 1: Oracle Architectural Components Quick Reference Context Reference Processes User process Server process Background processes: - DBWR - LGWR Memory structures SGA: - Shared pool - Database buffer cache - Redo log buffer PGA Physical structures (files) Database files: - Data files - Control files - Redo log files Other files: - Parameter file - Password file - Archived redo log files Parameters SHARED_POOL_SIZE DB_BLOCK_SIZE DB_BLOCK_BUFFERS LOG_BUFFER ................................ 2 Using Administration Tools 2-2 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Instructor Note Topic Timing Lecture 60 minutes Practice 30 minutes Total 90 minutes Oracle8: Database Administration 2-3 ...................................................................................................................................................... ...................................................................................................................................................... Objectives Objectives Instructor Note The purpose of this lesson is to ensure that participants get acquainted with the use of the following tools: • Server Manager Line Mode • Oracle Enterprise Manager It is not intended to provide details of all the components of OEM. 2-2 Copyright  Oracle Corporation, 1998. All rights reserved. Objectives • Using the Server Manager Line Mode • Identifying administration applications supplied with the Oracle Enterprise Manager • Using Oracle Enterprise Manager components 2-4 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Overview The table lists the common database administration tools and tasks that a database administrator can perform using the tools. These tools will be covered in this course, but they are only a subset of the utilities supplied by Oracle. The commands for starting up the different tools depend on the platform. This lesson will introduce the use of the following tools that assist a DBA in performing administrative tasks: • Server Manager line mode • Oracle Enterprise Manager Note In addition to these tools, NT-specific utilities like ORADIM80 or Oracle Database Assistant for creation of an Oracle database will be covered in the lessons “Managing an Oracle Instance” and “Creating a Database.” 2-3 Copyright  Oracle Corporation, 1998. All rights reserved. Tool Server Manager Line Mode Oracle Enterprise Manager SQL*Loader Export or Import utility Password File utility Description A line mode utility used for administrative tasks like starting up, shutting down or recovering a database Graphical user interface to administer, monitor, and tune multiple databases. Utility for loading data from external files into Oracle tables Utility for exporting/importing data in Oracle format Utility for creating database password file Database Administration Tools: Examples Oracle8: Database Administration 2-5 ...................................................................................................................................................... ...................................................................................................................................................... Using the Server Manager Using the Server Manager Server Manager line mode is useful for performing unattended operations, such as running nightly batch jobs or scripts. In addition, you can use line mode when a graphical interface is unavailable. Starting Server Manager in Line Mode Start Server Manager in line mode by entering the appropriate command at the operating system prompt. For example, on some systems the command svrmgrl starts up Server Manager in line mode. You can also start Server Manager in line mode and execute a script. Note The exact command for starting Server Manager in line mode depends on your platform. For information about starting Server Manager in line mode, see your operating system–specific Oracle documentation. 2-4 Copyright  Oracle Corporation, 1998. All rights reserved. Starting Server Manager in Line Mode On UNIX svrmgrl Starting and executing a script: svrmgrl command=@credb.sql svrmgrl command=“CONNECT scott/tiger” svrmgr30On NT svrmgr30 command=@u16run.sql On UNIX On NT 2-6 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Using Server Manager in Line Mode Use the line mode to execute the Server Manager commands described in the following section. In addition, you can execute SQL statements and PL/SQL code. Entering Server Manager Commands Single-line Server Manager commands do not require punctuation or terminators. A Server Manager command may span more than one line if you use a backslash (\) at the end of each line to indicate a continuation. SVRMGR> DESCRIBE \ 2> scott.emp Entering SQL or PL/SQL Code You can enter single-line or multiple-line SQL statements in line mode. Continuation characters are not needed in multiple-line SQL statements. To end a SQL statement and execute it, either type a semicolon (;) at the end of the statement, or type a slash (/) by itself on the last input line. To execute PL/SQL code, type a slash (/) by itself on the last input line. Running Scripts In line mode, you can run scripts using the @ command. For example, to run the script credb.sql, issue the statement @CREDB. If you do not specify a script name, Server Manager prompts you for one. Oracle8: Database Administration 2-7 ...................................................................................................................................................... ...................................................................................................................................................... Using the Server Manager 2-5 Copyright  Oracle Corporation, 1998. All rights reserved. Category Command that do not need database connection Commands needing Oracle privileges Commands needing special Oracle privileges Server Manager Commands Commands EXIT REMARK SET SHOW SPOOL CONNECT/DISCONNECT DESCRIBE EXECUTE SHOW ERRORS SHOW PARAMETER SHOW SGA CONNECT… AS SYSDBA CONNECT… AS SYSOPER ARCHIVE LOG RECOVER DATABASE STARTUP/SHUTDOWN 2-6 Copyright  Oracle Corporation, 1998. All rights reserved. Server Manager Commands: Examples SET CHARWIDTH 20 SET NUMWIDTH 8 SHOW ALL SPOOL credb.log SHOW SPOOL Set the column display width for character or numeric data: Spool output to a file: Display current settings: 2-8 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Server Manager Commands The table describes the most important Server Manager commands available in line mode. These commands can also be used in the Oracle Enterprise Manager worksheet. Command Description EXIT Exits Server Manager line mode or closes a SQL Worksheet REMARK Enters a comment, typically in SQL script files SET Sets or changes characteristics of the current command line mode session SHOW Shows settings currently in effect SPOOL Enables or disables spooling of output to a specified file CONNECT/ DISCONNECT Connects to or disconnects from a database DESCRIBE Describes a function, package, package body, procedure, table, object, or view EXECUTE Executes a one-line PL/SQL statement SHOW ERRORS Shows the errors generated from the last compilation of a procedure, package, or function SHOW PARAMETER Displays the current values for one or more initialization parameters SHOW SGA Displays information about the SGA of the current instance CONNECT/AS SYSDBA Enables privileged connection to the database ARCHIVE LOG Starts or stops automatic archiving of online redo log files, manually archives specified redo log files, or displays information about redo log files RECOVER DATABASE Performs media recovery on one or more tablespaces, one or more, or the entire database STARTUP/ SHUTDOWN Starts up or shuts down a currently running Oracle instance Oracle8: Database Administration 2-9 ...................................................................................................................................................... ...................................................................................................................................................... Oracle Enterprise Manager Oracle Enterprise Manager Oracle Enterprise Manager (OEM) consists of a centralized console, intelligent agents, and a package of standard applications that provide database administrators the functionality they need to manage their databases. There is also a separately licensable package of integrated applications, known as the OEM Performance Pack, that provides expert and advanced monitoring, diagnostic, and tuning capabilities. The OEM Console is currently available for Windows NT and Windows 95. OEM Architecture OEM Console The OEM Console (the console) is an application that permits a database administrator to manage several databases from one machine. Besides having a navigator, it provides several services such as job scheduling, event management, discovery of hosts and databases, and security. An overview of console functions and the interface is given in the following section. 2-7 Copyright  Oracle Corporation, 1998. All rights reserved. Oracle Enterprise Manager (OEM) Enterprise Manager Console Event system Job system Security service Integrated customer applications Oracle system management applications Integrated 3rd party applications Discovery service Repository Agent Agent Managed systems 2-10 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Intelligent Agent The intelligent agent is a process that runs on remote nodes in a network. It executes jobs and events sent by the console and communicates results back to the console using Net8. The agent provides autonomy from the console. For example, in the event that a network connection becomes severed or the console is down, the intelligent agent continues to operate remotely, reporting status back to the console once normal communications are restored. OEM Repository The OEM Repository (the repository), a set of database tables that holds information used by OEM, gives the administrator location flexibility. A separate repository exists for each OEM user. When a user logs on using OEM, a connection to that user’s repository is established. Since each repository is associated with an owner (as opposed to a specific client machine), a user can log on using OEM from any client machine. Thus, an off-site administrator can actually log on using OEM from a connected console in a remote city if desired. Oracle8: Database Administration 2-11 ...................................................................................................................................................... ...................................................................................................................................................... OEM Components OEM Components The OEM Console provides a global view of the system. It includes both a hierarchical tree and a graphical representation of the objects in the system. It has the following features: 1 A menu that can be used to initiate other administrative applications and perform various tasks 2 A launch pallette to invoke other applications to perform administrative tasks (It provides an alternative to using the menu.) 3 A navigator or object explorer view which provides a hierarchical view of Oracle services on the network (The navigator permits administrators to browse the different Oracle services such as databases, listeners, nodes, and name-servers, and modify the characteristics of objects; for example, users, and tables that they contain.) 4 A map or topographical view which permits Oracle services to be grouped based on spatial relationships, function, or both (The map view enables the user to focus on managed objects directly within their purview or interest.) 2-8 Copyright  Oracle Corporation, 1998. All rights reserved. Enterprise Manager Console 1 2 3 4 5 6 2-12 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools 5 A job system that permits remote execution of tasks related to listeners, databases, or the host itself (The job system is based on the procedural Tool Control Language (TCL) engine.) 6 An event system that monitors and reports on system status (The event system in conjunction with the job system will take corrective action based on predefined criteria and can advise the administrator by pager or electronic mail that a particular event has occurred.) Oracle8: Database Administration 2-13 ...................................................................................................................................................... ...................................................................................................................................................... OEM Components OEM Services There are three services commonly provided through the OEM console: • Repository • Service Discovery • Security Repository The OEM Repository is a set of tables in any database accessible to the console which is automatically created or upgraded when you start the console. Each administrator uses a specific repository. The repository provides: • A centralized location for storing information about the state of the environment managed by OEM from the perspective of each console user • Information on jobs and events • The preferred credentials for the user • Other information associated with each OEM Console login 2-9 Copyright  Oracle Corporation, 1998. All rights reserved. OEM Common Services • Repository • Service Discovery • Security 2-14 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Note If the repository does not exist, it is automatically created and a dialog box informs you of the operation. If the repository exists, the version must be compatible with the console. If the existing Enterprise Manager Repository version is older than the Oracle Enterprise Manager Console version, the existing repository will be upgraded automatically and a dialog box informs you of the upgrade. Service Discovery The Service Discovery feature provides wizards for identifying network services and populating the Navigator tree. These services, such as databases and listeners, can be administered with OEM components. Note • Use the Discover New Services and Refresh Services wizards for nodes that have the Intelligent Agent release 7.3.3 or higher running. If you add services to these nodes, you must restart the agent on the nodes before discovering the new services with the Discovery wizard option. • If a node is successfully discovered, it is set to automatic refresh mode in the Refresh Nodes page of the Refresh Services wizard. All nodes that are not successfully discovered are automatically retried unless they are removed from the Retry Nodes page. Security Access to Oracle Services on the network is controlled by a set of user defined preferred credentials for each of the managed services. This feature enables you to set up specific usernames, passwords, and roles for different services in your network. These preferences are used when you access a database, listener, or node in the navigator or map. This information is also passed to a DBA tool when a tool is executed on a selected database. OEM caches the user authentication information in an encrypted form in the OEM Repository and provides it as part of the connection request from the OEM Console or applications launched from the console. Note If the references have not been set up for a database, listener, or node, the login information that you entered to access the console is used when accessing that service. If that information is not valid for a selected service, you have to enter new login information each time you access the object. Oracle8: Database Administration 2-15 ...................................................................................................................................................... ...................................................................................................................................................... OEM Components The standard applications that are supplied with OEM include the following: • Instance Manager: used to control database availability and define initialization parameters to specify the characteristics of the instance • Schema Manager: used to create and maintain objects such as tables, indexes, and views • Security Manager: used to manage users and privileges • Storage Manager: used to organize the database files and manage rollback segments • SQL Worksheet: a command line interface that can be used to run SQL commands, PL/SQL code, and Server Manager commands • Backup Manager: used to backup, restore, and recover databases, and to manage redo log files • Data Manager: used to load and reorganize data in databases 2-10 Copyright  Oracle Corporation, 1998. All rights reserved. OEM Database Administration Tools Components needing console 2-16 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Note • All applications except Backup Manager and Data Manager can be invoked either from the console or directly from the operating system. • This lesson discusses the application interface and the use of SQL Worksheet. The use of these applications to perform specific tasks is covered in respective lessons throughout the course. • The use of Backup Manager to backup, restore, and recover databases is discussed in the course Oracle8: Backup and Recovery. Oracle8: Database Administration 2-17 ...................................................................................................................................................... ...................................................................................................................................................... OEM Components A brief description of the components in the OEM Performance Pack are presented in this section. Performance Manager • Provides a graphical view of database and system performance • Charts real time performance data • Records performance data for later analysis • Contains a predefined set of performance charts • Enables you to design your own custom charts Top-Session Monitor • Lists sessions in descending order of system resource use • Displays detailed information for each session, such as session and system statistics, locks held and requested, open cursors, and execution plans • Enables you to kill sessions 2-11 Copyright  Oracle Corporation, 1998. All rights reserved. OEM Performance Pack 2-18 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Lock Manager • Provides a graphical view of the locking activity • Includes information about locks held and requested • Enables you to kill sessions Advanced Events These are a set of space management, resource use, and performance event definitions that extend the OEM Event Management system. Tablespace Manager • Includes a segment map that graphically displays database space usage • Enables you to easily match an object with its extents and a particular extent with its object • Defragments tables by automatically exporting and importing them • Enables you to manually coalesce adjacent free space Trace • Collects performance data for the database, Net8, and applications • Provides a graphical interface for managing Trace collection data Expert • Can tune an existing system • Enables you to evaluate a planned configuration before it is implemented • Enables you to experiment with changes to your existing system • Provides you with detailed information about its tuning recommendations and generates reports for reference and documentation Note The terms tablepace, segment, extent, and free space will be discussed in subsequent lessons. Oracle8: Database Administration 2-19 ...................................................................................................................................................... ...................................................................................................................................................... Using Oracle Enterprise Manager Using Oracle Enterprise Manager Creating the Repository Before you can use the OEM Console, you need to connect to the database that contains the repository. The database can reside on any node that is accessible to the console. The user account that contains the repository should have DBA privileges. The repository gets created automatically when you connect to an account on an Oracle database for the first time. To start the console, use the following steps: 1 Choose Start—>Programs menu. 2 Select Enterprise Manager. You can also start the console (voc.exe) from the MS-DOS prompt: voc.exe user=system password=manager service=U15 2-12 Copyright  Oracle Corporation, 1998. All rights reserved. Starting and Using OEM • Create repository automatically by invoking OEM • Define services – Auto discovery – Manual • Set up security using preferred credentials • Use the navigator and launch palette to invoke applications 2-20 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Define Services After the console is running, the user needs to populate the navigator tree with the nodes and services. This can be performed automatically if the machine running a service has the intelligent agent running. Alternatively the service can be defined manually. All discovered services can be viewed through the navigator tree. Note Service discovery is discussed in detail in the course Oracle Enterprise Manager. Set Up Security The usernames and passwords to access various services in a network can be stored in the repository by setting up preferred credentials. To set up preferred credentials, use the following steps: 1 Choose File—>Preferences. 2 In the User Preferences property sheet, enter the username, password and role for accessing a database, node, or listener. 3 Click OK. Invoke Applications To start an application and connect to a database using preferred credentials, perform the following steps: • In the navigator, expand the Databases folder. • Select the appropriate database. • Click the application icon in the launch palette, or Select Tools—>Applications and the name of the application from the menu. If the connection is successful the application window displays. If a database connection cannot be made, the connect dialog box displays. Instructor Note Invoke OEM Console, and demonstrate the following: • Use Navigator—>Service Discovery and show one of the following: - Auto discovery - Refreshing of a service - Manual definition of a service Auto discovery and Refreshing are possible only if the agent is running on the node. • Set up preferred credentials for the database discovered or defined in the previous step. • Use the launch palette or menu to invoke Schema Manager. Oracle8: Database Administration 2-21 ...................................................................................................................................................... ...................................................................................................................................................... Using Oracle Enterprise Manager When a successful connection to a database is established using one of the applications, the navigator displays the objects managed through the application. Most applications use a standard toolbar containing icons to set options and perform specific actions. Some of the most commonly used objects in the user interface are discussed below: 1 Property Sheet: displays in the pane on the right hand side of the window (The properties displayed depend on the object selected in the navigator. For example, if a table is selected in the navigator of the Schema Manager, the column details and other information are displayed in the property sheet.) 2 Page: Property sheets are organized into pages, and each page displays a set of related properties. For example, the property sheet for a table consists of separate pages for general information (that includes column details) and constraints. 3 Buttons: enable operations such as Create, Create Like, and Delete to be performed 2-13 Copyright  Oracle Corporation, 1998. All rights reserved. Using Application Interface 2 4 3 1 2-22 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Note An Advanced Mode button (see 4 in the screenshot) is a toggle that controls the number of pages displayed. Only the General page is displayed in most cases if the Advanced Mode is not activated. Instructor Note Demonstrate the following using the Schema Manager: • Use Object—>Create in the menu to create a new table. Point to the Create icon, and inform the students that the same action can be performed using the toolbar. • Select the table just created and demonstrate the use of Create Like using the toolbar. • Demonstrate the use of the Advanced Mode button by selecting the table created in the preceding step. • Use the Constraints page to show the creation of a Primary Key constraint. Click on the Show SQL button to display the SQL statement that is generated. Oracle8: Database Administration 2-23 ...................................................................................................................................................... ...................................................................................................................................................... Using Oracle Enterprise Manager SQL Worksheet enables administrators to dynamically enter SQL statements, PL/SQL code, and Server Manager commands. Some of the components of SQL Worksheet, and their uses are outlined below: 1 Output pane: appears at the top of the window and is used to display the results and messages in response to commands 2 Input pane: at the bottom of the window is used by the user to enter commands or PL/SQL code 3 Execute icon: after a command is entered, click this icon to run the command 4 Command history icon: is used to display a list of the most recently executed commands from the buffer, and retrieve, edit, and execute one or more of them SQL Worksheet also has menu options to save commands to scripts, to retrieve, and to run scripts. 2-14 Copyright  Oracle Corporation, 1998. All rights reserved. Using SQL Worksheet 2 4 3 1 2-24 Oracle8: Database Administration ...................................................................................................................................................... ...................................................................................................................................................... Lesson 2: Using Administration Tools Summary Quick Reference Instructor Note (Refer to previous page.) Demonstrate the following using SQL Worksheet: • Invoke SQL Worksheet from the Windows Start menu. • Retrieve the script scott.sql from the LABS subdirectory, and execute it. • Notice that this script sets echo to Off. Show how to set echo to On. • Use SELECT * FROM scott.emp and show the results. • Run a query to retrieve all rows from SCOTT.DEPT. Show the use of Command History. Retrieve and execute the query on SCOTT.EMP. Context Reference Initialization parameters None Dynamic performance views None Data dictionary views None Commands svrmgrl svrmgr30.exe SERVER MANAGER line mode commands voc.exe Packaged procedures and functions None 2-15 Copyright  Oracle Corporation, 1998. All rights reserved. Summary • Using Server Manager in Line Mode • Using Oracle Enterprise Manager components