1 / 12

1 Introduction Objectives 1-2 Course Objectives 1-3 Oracle Products 1-4

Contents. 1 Introduction Objectives 1-2 Course Objectives 1-3 Oracle Products 1-4 Relational Database Systems 1-5 How the Data Is Organized 1-6 Integrity Constraints 1-7 Structured Query Language 1-8 Tasks of an Oracle Database Administrator 1-9 Summary 1-10

vivien-wade
Download Presentation

1 Introduction Objectives 1-2 Course Objectives 1-3 Oracle Products 1-4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Contents • 1 Introduction • Objectives 1-2 • Course Objectives 1-3 • Oracle Products 1-4 • Relational Database Systems 1-5 • How the Data Is Organized 1-6 • Integrity Constraints 1-7 • Structured Query Language 1-8 • Tasks of an Oracle Database Administrator 1-9 • Summary 1-10 • 2 Installing Oracle Database 10g Software • Objectives 2-2 • System Requirements 2-3 • Optimal Flexible Architecture (OFA) 2-4 • Using Optimal Flexible Architecture 2-5 • Setting Environment Variables 2-7 • Preinstallation Checks 2-9 • Oracle Universal Installer 2-10 • Inventory and UNIX Group Name 2-11 • orainstRoot.sh 2-12 • File Locations 2-14 • Install Type 2-15 • Prerequisite Checks 2-16 • Starter Database 2-17 • Configuration and Management 2-18 • File Storage and Backup Recovery 2-19 • Passwords and Summary 2-20 • Installation 2-21 • Configuration Assistants 2-22 • Summary 2-24 • Practice 2: Installing the Oracle Software 2-25 • 3 Creating an Oracle Database • Objectives 3-2 • Database Architecture 3-3 • Exploring the Storage Structure 3-4 • Control Files 3-5 • Redo Log Files 3-6 • Tablespaces and Datafiles 3-7 • Segments, Extents, and Blocks 3-8 i

  2. Oracle Instance Management 3-9 • Oracle Memory Structures 3-10 • Oracle Processes 3-12 • Data Dictionary 3-13 • Database Control 3-14 • Grid Control 3-15 • Database Configuration Assistant (DBCA) Overview 3-16 • Creating a Database 3-17 • Database Identification 3-18 • Management Options 3-19 • Passwords and Storage 3-20 • File Locations and Backup Recovery 3-21 • File Location Variables 3-22 • Content and Initialization Parameters 3-23 • Database Storage 3-25 • Creation Options and Create 3-26 • Other Actions with DBCA 3-27 • Summary 3-28 • Practice 3: Creating an Oracle Database 3-29 • 4 Database Interfaces • Objectives 4-2 • What Is SQL? 4-3 • Using SQL 4-4 • Enterprise Manager: Seeing the SQL 4-5 • What Is SQL*Plus? 4-6 • What Is iSQL*Plus? 4-7 • Using iSQL*Plus 4-9 • Describing Data 4-10 • Querying Data 4-11 • Sorting the Data 4-12 • Joining Tables 4-13 • Manipulating Data 4-15 • Defining Data 4-16 • Overview of Transactions 4-17 • Transaction Control Statements 4-18 • Locking Data 4-19 • Other Statement Categories 4-20 • What Is PL/SQL? 4-21 • Example PL/SQL Block 4-22 • Uses of PL/SQL 4-23 • What Is Java? 4-24 • Oracle and Java 4-25 ii

  3. What Is OCI? 4-26 • Other APIs 4-27 • Summary 4-29 • Practice 4: Using SQL 4-30 • 5 Controlling the Database • Objectives 5-2 • Starting and Stopping iSQL*Plus 5-3 • Management Framework 5-4 • Starting and Stopping Database Control 5-5 • Accessing Database Control 5-6 • SYSOPER and SYSDBA 5-7 • Database Home Page 5-8 • Changing the Listener Status 5-9 • Startup and Shutdown 5-10 • Starting Up a Database NOMOUNT 5-11 • Starting Up a Database MOUNT 5-12 • Starting Up a Database OPEN 5-13 • Shutting Down the Database 5-14 • SHUTDOWN Options 5-15 • Initialization Parameter Files 5-18 • Viewing Initialization Parameters 5-19 • Viewing the Alert Log 5-20 • Summary 5-21 • Practice 5: Controlling the Database 5-22 • 6 Storage Structures • Objectives 6-2 • Tablespaces and Data Files 6-3 • Space Management in Tablespaces 6-4 • Creating a New Tablespace 6-5 • Storage for Locally Managed Tablespaces 6-7 • Tablespaces in the Preconfigured Database 6-9 • Altering a Tablespace 6-11 • Actions with Tablespaces 6-13 • Dropping Tablespaces 6-15 • Viewing Tablespace Information 6-16 • Summary 6-17 • Practice 6: Working with Tablespaces 6-18 iii

  4. 7 Administering Users • Objectives 7-2 • Database User Accounts 7-3 • Creating a User 7-4 • Profiles and Users 7-5 • Authenticating Users 7-7 • Default and Temporary Tablespaces and Locking 7-9 • Database Users and Schemas 7-10 • Checklist for Creating Users 7-11 • Privileges 7-12 • System Privileges 7-13 • Object Privileges 7-15 • Assigning Quota to Users 7-16 • Assigning Roles to Users 7-17 • Roles 7-18 • Benefits of Roles 7-19 • Predefined Roles 7-20 • Secure Roles 7-21 • Summary 7-22 • Practice 7: Administering Users 7-23 • 8 Managing Schema Objects • Objectives 8-2 • What Is a Schema? 8-3 • Schemas 8-4 • Accessing Schema Objects 8-6 • Naming Database Objects 8-7 • Schema Object Namespaces 8-9 • Specifying Data Types in Tables 8-10 • Other Data Types 8-11 • Creating and Modifying Tables 8-13 • Understanding Data Integrity 8-15 • Defining Constraints 8-17 • Viewing the Attributes of a Table 8-18 • Viewing the Contents of a Table 8-19 • Actions with Tables 8-20 • Creating Indexes 8-22 • What Is a View? 8-23 • Creating Views 8-24 iv

  5. What Is a Sequence? 8-25 • Using a Sequence 8-27 • Summary 8-28 • Practice 8: Working with Tables 8-29 • 9 Managing Data • Objectives 9-2 • Manipulating Data Through SQL 9-3 • The INSERT Command 9-4 • The UPDATE Command 9-5 • The DELETE Command 9-6 • The COMMIT and ROLLBACK Commands 9-7 • Integrity Constraints and DML 9-8 • Data Pump Export 9-9 • Data Pump Import 9-11 • DIRECTORY Objects 9-13 • SQL*Loader 9-14 • The SQL*Loader Control File 9-16 • Control File Syntax Considerations 9-19 • Input Data and Data Files 9-20 • Loading Methods 9-23 • Comparing Direct and Conventional Path Loads 9-25 • Loading Data with SQL*Loader 9-27 • Summary 9-29 • Practice 9: Using Data Pump Export and Import 9-30 • 10 PL/SQL • Objectives 10-2 • PL/SQL 10-3 • Administering PL/SQL Objects 10-4 • PL/SQL Objects 10-5 • Functions 10-6 • Procedures 10-7 • Packages 10-8 • Package Body 10-9 • Built-In Packages 10-11 • Triggers 10-12 • PL/SQL Configuration Options 10-14 • Summary 10-15 • Practice Overview 10-16 v

  6. 11 Oracle Database Security • Objectives 11-2 • Database Security 11-3 • Apply the Principle of Least Privilege 11-5 • Protect the Data Dictionary 11-6 • Revoke Unnecessary Privileges from PUBLIC 11-7 • Restrict the Operating System Directories Accessible by the User 11-9 • Limit Users with Administrative Privileges 11-10 • Disable Remote Operating System Authentication 11-11 • Manage Default User Accounts 11-12 • Implement Standard Password Security Features 11-13 • Password Account Locking 11-14 • Password Expiration and Aging 11-15 • Password History 11-16 • Password Verification 11-17 • Supplied Password Verification Function: VERIFY_FUNCTION 11-18 • Creating a Password Profile 11-19 • Assigning Users to a Password Profile 11-20 • Monitoring for Suspicious Activity 11-21 • Audit Tool Comparisons 11-22 • Standard Database Auditing 11-23 • Specifying Audit Options 11-24 • Viewing Auditing Options 11-26 • Standard Database Auditing 11-27 • Viewing Auditing Results 11-28 • Value-Based Auditing 11-29 • Fine-Grained Auditing (FGA) 11-31 • FGA Policy 11-32 • DBMS_FGA Package 11-34 • Enabling and Disabling an FGA Policy 11-35 • Dropping an FGA Policy 11-36 • Triggering Audit Events 11-37 • Data Dictionary Views 11-38 • DBA_FGA_AUDIT_TRAIL 11-39 • FGA Guidelines 11-41 • Auditing SYSDBA and SYSOPER Users 11-43 • Security Updates 11-44 • Summary 11-45 • Practice 11-1 Overview: Database Security (Part 1) 11-46 • Practice 11-2 Overview: Database Security (Part 2) 11-49 vi

  7. 12 Oracle Net Services • Objectives 12-2 • Oracle Net Services 12-3 • Oracle Net Listener 12-4 • Monitoring the Listener 12-5 • Creating a Listener 12-6 • Listening Addresses 12-7 • Configuring Optional Parameters 12-8 • Static Database Registration 12-9 • Listener Control Utility 12-10 • Listener Control Utility Syntax 12-11 • Monitoring with Listener Control 12-13 • Oracle Net Connections 12-15 • Names Resolution 12-16 • Easy Connect 12-17 • Local Naming 12-18 • Directory Naming 12-19 • Configuring Service Aliases 12-20 • Advanced Connection Options 12-21 • Oracle Net Manager 12-23 • Choosing Naming Methods 12-24 • Configuring Service Aliases with Net Manager 12-25 • Advanced Connection Options Using Oracle Net Manager 12-26 • Testing Oracle Net Connectivity 12-27 • Summary 12-28 • Practice 12 Overview: Oracle Net Services 12-29 • 13 Oracle Shared Servers • Objectives 13-2 • Establishing a Connection 13-3 • Dedicated Server Process 13-4 • User Sessions 13-5 • User Sessions: Dedicated Server 13-6 • User Sessions: Shared Server 13-7 • Processing a Request 13-8 • The SGA and PGA 13-9 • Configuring Oracle Shared Server 13-10 • DISPATCHERS 13-11 • SHARED_SERVERS 13-13 • MAX_SHARED_SERVERS 13-14 • CIRCUITS 13-15 • SHARED_SERVER_SESSIONS 13-16 • Related Parameters 13-17 vii

  8. Verifying Shared Server Setup 13-18 • Data Dictionary Views 13-20 • Choosing a Connection Type 13-21 • When Not to Use Shared Server 13-22 • Summary 13-23 • Practice 13 Overview: Oracle Shared Servers 13-24 • Practice Lesson 13 13-25 • 14 Performance Monitoring • Objectives 14-2 • Performance Monitoring 14-3 • Monitoring Methodologies 14-4 • Database and Instance Metrics 14-5 • Data Dictionary Metrics 14-7 • Invalid and Unusable Objects 14-8 • Optimizer Statistics 14-10 • Manually Gather Optimizer Statistics 14-12 • Automate Optimizer Statistics Collection 14-13 • Schedule Optimizer Statistics Collection 14-14 • Dynamic Performance Views 14-15 • Viewing Metric Information 14-16 • Reacting to Performance Issues 14-17 • Reacting to Performance Issues 14-18 • Summary 14-19 • Practice 14: Monitoring Performance 14-20 • 15 Proactive Maintenance • Objectives 15-2 • Server Generated Alerts 15-3 • Thresholds 15-4 • Setting Thresholds 15-5 • Baseline Measurements 15-6 • Using Baselines 15-7 • Tuning and Diagnostic Advisors 15-8 • SQL Tuning and Access Advisors 15-10 • Memory Advisors 15-11 • Segment Advisor 15-12 • Automatic Workload Repository (AWR) 15-13 • Managing the AWR 15-14 • Automatic Database Diagnostic Monitor (ADDM) 15-15 • ADDM Findings 15-16 • ADDM Recommendations 15-17 • Summary 15-18 • Practice 15: Proactive Maintenance 15-19 viii

  9. 16 Undo Management • Objectives 16-2 • Undo Data 16-3 • Transactions and Undo Data 16-5 • Storing Undo Information 16-6 • Monitoring Undo 16-7 • Administering Undo 16-9 • Configuring Undo Retention 16-10 • Guaranteeing Undo Retention 16-12 • Sizing the Undo Tablespace 16-13 • Using the Undo Advisor 16-14 • Summary 16-15 • Practice 16: Managing Undo 16-16 • 17 Monitoring and Resolving Lock Conflicts • Objectives 17-2 • Locks 17-3 • Locking Mechanism 17-4 • Data Concurrency 17-5 • DML Locks 17-7 • Enqueue Mechanism 17-8 • Lock Conflicts 17-9 • Possible Causes of Lock Conflicts 17-10 • Detecting Lock Conflicts 17-11 • Resolving Lock Conflicts 17-12 • Deadlocks 17-13 • Summary 17-14 • Practice 17: Locks in the Oracle Database 17-15 • 18 Backup and Recovery Concepts • Objectives 18-2 • Backup and Recovery Issues 18-3 • Categories of Failures 18-4 • Statement Failures 18-5 • User Process Failure 18-6 • Network Failure 18-7 • User Errors 18-8 • Instance Failure 18-10 • Instance Recovery 18-11 • Phases of Instance Recovery 18-12 • Tuning Instance Recovery 18-13 ix

  10. Using the MTTR Advisor 18-14 • Media Failure 18-15 • Configuring for Recoverability 18-16 • Control Files 18-17 • Redo Log Files 18-18 • Multiplexing the Redo Log 18-19 • Archived Log Files 18-20 • Archive Log File Naming and Destinations 18-21 • ARCHIVELOG Mode 18-23 • Summary 18-24 • Practice 18: Backup and Recovery Concepts 18-25 • 19 Database Backups • Objectives 19-2 • Terminology 19-3 • Recovery Manager (RMAN) 19-5 • Configuring Backup Settings 19-6 • Scheduling Backups: Strategy 19-8 • Scheduling Backups: Options 19-9 • Scheduling Backups: Settings 19-10 • Scheduling Backups: Schedule 19-11 • Scheduling Backups: Review 19-12 • Backup Control File to Trace 19-13 • Manage Backups 19-15 • Flash Recovery Area 19-16 • Summary 19-17 • Practice 19: Database Backups 19-18 • 20 Database Recovery • Objectives 20-2 • Opening a Database 20-3 • Changing Instance Status 20-5 • Keeping a Database Open 20-6 • Loss of a Control File 20-7 • Loss of a Redo Log File 20-8 • Loss of a Data File in NOARCHIVELOG Mode 20-9 • Loss of a Noncritical Data File in ARCHIVELOG Mode 20-10 • Loss of a System-Critical Data File in ARCHIVELOG Mode 20-11 • Summary 20-12 • Practice Overview: Database Recovery 20-13 x

  11. Appendix A: Practice Solutions • Appendix B: Basic Linux and vi Commands • Appendix C: SQL Statement Syntax • Appendix D: Acronyms and Terms • Appendix E Next Steps: Continuing Your Education • Where Do You Go from Here? E-2 • Continuing Education Resources E-3 • Oracle University E-4 • Oracle University Online Library E-5 • Oracle Technology Network E-6 • Oracle AppsNet E-7 • Oracle MetaLink E-8 • Thank You! E-9 xi

More Related