Dba 9i New Features

Oracle 9i : New Features for Administrators 1Z1-030 __________________________________________________________________________________

Oracle Certified Database Administrator Track Oracle 9i: New Features for Administrators Test 1Z1-030

Author: Peter N. Crompton Date: February 2002 Version: 1.0 Ref: DBA_9I_new_features.doc

1

Oracle 9i : New Features for Administrators 1Z1-030 __________________________________________________________________________________

Version 1.00

Author Peter N. Crompton

Date February 2002

Description of Changes First version of document.

Preface This document contains summary notes that I have compiled whilst studying for the Oracle 9i New features for DBA’s exam. I have taken the Oracle supplied syllabus and documented what I consider to be the answers for each section. This document covers the version of the syllabus dated December 2001.

I have tested all of the code examples listed against the following environment:Oracle 9.0.1, running under Windows 2000, SP 2.

For other documents available in the series check http://www.pitchmark-ltd.co.uk Copyright © 2002 Pitchmark Limited, UK. All rights reserved. This document may be freely distributed, but only in its entirety.

The story so far … The Oracle 9i: New Features for Administrators syllabus became finalized in December 2001.As I’ve said before its always best to wait until this happens as Oracle have dramatically changed them in the past. This can waste a lot of your study time. Oracle TechNet subscriptions seem to have been suspended so you’ll need to buy a copy of the trial License and a copy of the generic documentation disk from the Oracle store. They don’t cost must (the cost depends on your country), but navigating the dreadful oracle store to buy them is a real pain. When I tried I found the system full of syntax errors and had to phone an operator to put the order through. I’ve settled on two main textbooks, which I’ll be using. Both are available from AMAZON.COM. One does not become available until the end of February. I’ve listed a few other books, but I suspect that the two books plus the CD-ROM ‘Oracle9i Online Generic Documentation CD-ROM Release 1 (9.0.1) CD pack’ should be enough material for study. I’ve listed the headings for study below and will be filling them in over time. You’ll need to check back from time-to-time for the completed document.

2

Oracle 9i : New Features for Administrators 1Z1-030 __________________________________________________________________________________

Recommended Reading specific for this exam. Oracle 9i New Features by Robert Freeman Paperback (January 2002) ISBN: 0072223855 This is not specifically written for the exam. However its compact and covers a about one-third of the material needed. It’s a handy manual to have as its very readable and will server as a good introduction to the new features.

OCP Oracle9i Database: New Features for Administrators Exam Guide (Osborne ORACLE Press Series) by Jason Couchman, Daniel K. Benjamin Hardcover - 512 pages 1st edition (February 28, 2002) Osborne McGraw-Hill; ISBN: 0072195304 Prepare to pass the Oracle9i Certified Professional DBA New Features for Administrators exam using this Oracle Press study guide. You'll get complete coverage of all exam topics followed by practice questions and chapter summaries. The CD-ROM contains hundreds of practice exam questions in an adaptive format.

Oracle 9i: For Windows NT and 2000 Handbook by Rama Velpuri, Anand Adkoli Paperback - 592 pages (January 2002) Osborne McGraw-Hill; ISBN: 0072190922 If you need extra help with the Oracle under windows 2000, side of things give this one a go.

Oracle9i DBA Handbook (Osborne ORACLE Press Series) by Kevin Loney, Marlene Theriault Paperback - 1072 pages (December 10, 2001) McGraw-Hill Professional Publishing; ISBN: 0072193743

For alternative titles search Amazon.com

3

Oracle 9i : New Features for Administrators 1Z1-030 __________________________________________________________________________________

1.0 Oracle Server Security 1.1 Explain the new privileged connection options 1.2 Describe the new security features and their application: 1.2.1 Secure Application Role 1.2.2 Global Context 1.2.3 Partitioned Fine Grain Access Control 1.2.4 Fine Grained Auditing 1.3 Describe the optional security Products 2.0 General High Availability Technology 2.1 Explain new features designed to harden the database against unplanned downtime 2.2 Describe minimal I/O recovery 2.3 Describe fast-start time-based recovery limit 2.4 Explain the new Oracle Flashback feature 2.5 Describe Resumable space allocation 2.6 Describe the new Export/Import Features 3.0 Oracle9i LogMiner Enhancements 3.1 Explain LogMiner new features: 3.1.1 DDL statement support 3.1.2 dictionary staleness detection 3.1.3 ability to use an online dictionary, 3.1.4 ability to skip log corruptions 3.2 Describe the LogMiner Viewer 4.0 Backup and Recovery 4.1 Describe new RMAN manageability features: 4.1.1 new backup enhancements 4.1.2 new restore/recovery enhancements 4.2 Describe new RMAN reliability features: 4.2.1 Block media recovery (BMR) 4.2.2 Trial Recovery 4.2 Describe other RMAN improvements 5.0 Oracle9i Data Guard 5.1 Explain the Oracle9i Data Guard Architecture 5.2 Configure the physical Standby Database in no-data-loss mode 5.3 Initiate a Database Switchover operation 5.4 Setup automatic archive gaps detection 5.5 Launch managed recovery mode in the background 5.6 Apply a delay to redo application on the standby site 6.0 Database Resource Manager Enhancements 6.1 Automatically detect long running operations 6.2 Automatically limit resource consumption other than CPU and DOP 7.0 Online Operations 7.1 Discuss new features designed to reduce planned downtime 7.2 Explain online index rebuild new functionality 7.3 Explain online functionality for index-organized tables (IOTs) 7.4 Describe online table redefinitions 7.5 Explain online analyze validate 7.6 Describe the use of the Server Parameter File (SPFILE)

4

Oracle 9i : New Features for Administrators 1Z1-030 __________________________________________________________________________________

8.0 Segment Management (Part I) 8.1 Use the automatic global index maintenance feature 8.2 Explain the use of external tables 8.3 Use the new LIST partitioning method 8.4 Explain the basics of Meta data Application Programming Interface 9.0 Segment Management (Part II) 9.1 Explain and use the new Automatic Segment-Space Management functionality 9.2 Create and use bitmap join indexes 10.0 Performance Improvements 10.1 Use the indexes monitoring feature 10.2 Describe skip scan index access 10.3 Describe the cursor sharing enhancements 10.4 Identify cached execution plans 10.5 Use the new first rows optimization 10.6 Gather system statistics 11.0 Scalable Session Management 11.1 Describe and manage Oracle Shared Server 11.2 Invoke dedicated external procedure agents 11.3 Manage multithreaded heterogeneous service agents 11.4 Implement connection pooling with Oracle Call Interface (OCI) 11.5 List the benefits of core library Improvements 12.0 Real Application Clusters 12.1 Explain cash fusion 12.2 Configure a shared server-side initialization parameter file 13.0 File Management 13.1 Explain the concept and benefits behind Oracle-Managed Files (OMF) 13.2 Create and manage OMF files 13.3 Use SQL syntax to remove associated OS files when removing a non-OMF tablespace from the database 13.4 Create and alter default temporary tablespaces 14.0 Tablespace Management 14.1 Explain the concept of Automatic 14.2 Undo Management 14.3 Create and maintain UNDO table-spaces 14.4 Create and properly use multiple block sizes within a database 15.0 Memory Management 15.1 Set parameters to enable automatic and dynamic sizing of SQL working areas 15.2 Use new columns and views to gather information regarding SQL execution memory management 15.3 Describe the allocation and tracking of memory behind a dynamic SGA 16.0 Enterprise Manager Enhancements 16.1 Describe the new look and feel of the Console 16.2 Use the Console in Standalone mode 16.3 Explain Enterprise Manager functionality that supports Oracle9i Database features 16.4 Generate HTML Reports 16.5 Create User defined events

5

Oracle 9i : New Features for Administrators 1Z1-030 __________________________________________________________________________________

17.0 SQL Enhancements 17.1 Use ISO/ANSI standard SQL syntax, such as: 17.1.1 Joins 17.1.2 CASE expressions 17.1.3 NULLIF 17.1.4 COALESCE 17.1.5 scalar sub-queries 17.1.6 MERGE 17.1.7 analytical functions 17.2 Identify other SQL enhancements, such as: 17.2.1 constraint enhancements, 17.2.2 FOR UPDATE WAIT 17.2.3 Use the enhancements to LOBs and PL/SQL 18.0 Globalization Support 18.1 Describe the new date and time data types 18.2 Describe the Unicode enhancements 18.3 Describe the enhanced sorting functionality 18.4 Use the Character Set Scanner 18.5 Explain the new byte and character semantics 18.6 Use the Locale Builder 19.0 Database Workspaces 19.1 Identify the Workspace Manager role 19.2 Version-enable a table 19.3 Disable workspace participation for a table 19.4 Create and assign a workspace 19.5 Explain Import and Export considerations 20.0 Advanced Replication 20.1 Add a new master site without quiescing 20.2 Decide when to use row level system change numbers 20.3 Use new materialized view fast refresh abilities 20.4 Replicate objects

End of document

6