This course provides you with the skills necessary to create, maintain and manage an Oracle10g database. In this class you will concentrate on the skills necessary for day-to-day database administration tasks such as security, creating and managing users, creating and managing database objects (tablespaces, tables, indexes, etc.), running utilities, configuring Oracle networking, and simple backup and recovery techniques. An in-depth discussion of database architecture is also provided.
This training course has been completely updated for Oracle10g. This includes training on Data Pump, Server-Generated Alerts, Advisors, Automatic Storage Management (optional lesson), Automatic Statistics Collection and Auditing Enhancements.
Working examples are included throughout the course book. Students have access to electronic versions of all examples, sample scripts and lab solutions.
Approximately 50% of class time is spent on hands-on exercises.
After successfully completing this training, you will be able to:
- Describe the major architectural components of Oracle10g, including the SGA, background processes and files that make up the physical database.
- Use the Oracle data dictionary to to determine the structure and status of the database. This includes gaining a solid understanding of the purpose and content of many V$ and DBA-level views.
- Create administrative users, including granting and using the SYSDBA privilege. Understand the purpose of Oracle supplied users SYS and SYSTEM.
- Connect to the Oracle database as SYSDBA, both from a host server or via the use of password files over a network.
- Start and stop an Oracle database, including the use of various shutdown modes such as IMMEDIATE, TRANSACTIONAL and ABORT.
- View and alter initialization parameter values, create and startup with secondary parameter files. This includes training on the use of the Server Parameter File, introduced with Oracle9i.
- Create new Oracle users, provide tablespace quota and grant them the system or object privileges necessary to perform their work, including the use of roles. Secure the default users provided with database installation.
- Implement the Oracle user password management features such as password expiration, account locking, password reuse prevention and password complexity enforcement.
- Implement Oracle database auditing to track SYSDBA activity and general user activity. Also, code simple triggers to audit table data changes. (Optional lesson.)
- Create and manage locally managed tablespaces including monitoring space usage and adding space
- Implement Automatic Segment Space Management to efficiently manage free and used space within extents.
- Using the Oracle Managed Files feature to control placement of and name the underlying datafiles.
- Manage storage for Oracle objects with disk striping and buffer pool assignment.
- Use Automatic Storage Management (ASM) to control file allocation, placement, striping and mirroring (this is an optional lesson)
- Describe the different types and typical uses of Oracle tables, including heap, index-organized and clustered tables.
- Create and manage heap-tables, including managing concurrency with the INITTRANS and MAXTRANS parameters, moving tables, using DBMS_STATS to collect statistics and increasing and measuring the performance benefits of NOLOGGING and table compression.
- Describe the different types of Oracle indexes including B-tree, bitmap and reverse key.
- Create simple B-Tree indexes on tables. This includes understanding the fundamental rules for when and where to create and when to avoid using indexes. Also implement index options such as NOLOGGING.
- Create constraints on tables including the use of options such as NOVALIDATE to allow existing legacy data with constraint violations.
- Describe the techniques for managing undo, including the Oracle9i Automatic Undo Management feature.
- Implement Automatic Undo Management including creating and enabling new undo tablespaces and monitoring for space-related problems. Use the Oracle10g Undo Advisor to assist with undo management.
- Manage the database redo logs including archiving, multiplexing and adding additional logs. Also, ensure that the database is recoverable by checking the archive log status of the database. Use the Oracle10g Redo Log Advisor to obtain log size advice.
- Understand the role and importance of control files. Add additional control files. Backup and recreate control files.
- Use the basic features of Recovery Manager to perform simple backup and recovery operations in the event of a lost or corrupted datafile.
- Use the 10g Data Pump utility perform logical backups or move or copy objects.
- Use the SQL*Loader utility to load data into the database.
- Configure Oracle Net (SQL*Net) to connect to and listen for connections. Manage the database listener with the listener control utility.
- Create a new database with the Database Configuration Assistant.
- Optional lessons include: Introduction to Auditing, Pro-Active Management with Alerts, Introduction to Partitioning, Other Table Types and Original Import / Export Utility
Audience:Database and System Administrators