2 Day DBA - Oracle
Oracle Database2 Day DBA19cE96197-09July 2020
Oracle Database 2 Day DBA, 19cE96197-09Copyright 2004, 2020, Oracle and/or its affiliates.Primary Author: Roopesh Ashok KumarContributing Authors: Bert Rich, Padmaja Potineni, Kathy Rich, Janet Stern, Steve Fogel, Colin McGregor,Lance Ashdown, Eric Belden, Sushil Kumar, and Douglas WilliamsContributors: Bharat Baddepudi, Prasad Bagal, Timothy Chien, Sudip Datta, Kakali Das, Mark Dilman,Prabhaker Gongloor, Cecilia Grant, Shivani Gupta, Pat Huey, Chaitanya Koratamaddi, Balaji Krishnan,Vasudha Krishnaswamy, Rich Long, Venkat Maddali, Matthew McKerley, Ed Miner, Mughees Minhas,Saurabh Pandey, Kant Patel, Malai Stalin, Xiaofang Wang, and Khaled YagoubThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Governmentend users are "commercial computer software" or “commercial computer software documentation” pursuantto the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such,the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works,and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will notbe responsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
ContentsPrefaceAudiencexivDocumentation AccessibilityxivRelated DocumentationxivConventionsxvChanges in This Release for Oracle Database 2 Day DBA1Changes in Oracle Database Release 19c, Version 19.1xviChanges in Oracle Database Release 18c, Version 18.1xviiChanges in Oracle Database 12c Release 2 (12.2.0.1)xviiChanges in Oracle Database 12c Release 1 (12.1.0.2)xixChanges in Oracle Database 12c Release 1 (12.1.0.1)xixIntroduction1.12About This Guide1-11.1.1What This Guide Is Not1-11.1.2How to Use This Guide with Related Material1-21.2About Oracle Database1-21.3Common Oracle DBA Tasks1-31.4Tools for Administering the Database1-4Installing Oracle Database and Creating a Database2.1Overview of Installing Oracle Database Software and Creating a Database2-12.1.1Checking Oracle Database Installation Prerequisites2-22.1.2Deciding on Oracle Database Installation Choices2-32.1.2.1Install Option for Oracle Database2-32.1.2.2Installation Class for Oracle Database2-42.1.2.3Installation Edition for Oracle Database2-42.1.2.4Software Installation Directories for Oracle Database2-42.1.2.5Database File Location for Oracle Database2-5iii
2.1.2.6Database Identifiers for Oracle Database2-52.1.2.7About Advanced Installation for Oracle Database2-62.2Installing Oracle Database Software2.3Creating and Managing a Database with DBCA2-112.3.1Starting DBCA2-122.3.2Creating a Database Using DBCA2-122.3.2.1DBCA Creation Mode Window2-132.3.2.2DBCA Deployment Type Window2-142.3.2.3DBCA Database Identification Window2-152.3.2.4DBCA Storage Option Window2-162.3.2.5DBCA Fast Recovery Option Window2-162.3.2.6DBCA Network Configuration Window2-182.3.2.7DBCA Database Vault Option Window2-182.3.2.8DBCA Configuration Options Window2-182.3.2.9DBCA Management Options Window2-222.3.2.10DBCA User Credentials Window2-232.3.2.11DBCA Creation Option Window2-232.3.2.12DBCA Summary Window2-232.3.2.13DBCA Progress Window2-242.3.2.14DBCA Finish Window2-242.3.3Changing the Configuration of a Database Using DBCA2-242.3.4Deleting a Database Using DBCA2-242.3.5Managing Templates with DBCA2-252.3.5.1Advantages of Using DBCA Templates2-252.3.5.2Types of DBCA Templates2-252.3.5.3DBCA Templates Provided by Oracle2-262.3.5.4Creating Templates Using DBCA2-272.3.5.5Deleting DBCA Templates2-272.3.6Using DBCA to Manage PDBs2.3.6.132-7Managing PDBs in a CDB using DBCA2-282-282.4Manually Installing the Database Sample Schemas Post-Installation2-292.5Installation: Oracle By Example Series2-29Getting Started with Database Administration3.1Managing Your Database: An Overview3-13.2Configuring the Operating System Environment Variables3-23.3Introduction to Oracle Enterprise Manager Database Express3-33.4Starting EM Express3-53.4.1Starting EM Express for a Non-CDB3-73.4.2Starting EM Express for a CDB3-7iv
3.4.3Starting EM Express for a PDB3-83.5Configuring the HTTPS Port for EM Express3-93.6About EM Express and Database Cloud Service Deployments3-123.7Accessing the Database Home Page3-123.8Granting Access to EM Express for Nonadministrative Users3-143.9Administering the Database with SQL-Based Management Tools3-143.9.1About SQL3-153.9.2About SQL*Plus3-153.9.3Starting SQL*Plus and Connecting to the Database3-163.9.4About SQL Developer3-173.104Getting Started with Database Administration: Oracle by Example SeriesConfiguring the Network Environment4.1Understanding Network Configuration4-14.1.1Oracle Net Listener Configuration4-14.1.2Client Connections4-24.1.3Tools for Network Configuration4-44.2Viewing Listener Configuration4-54.3Starting and Stopping the Listener4-54.4Connecting to an Oracle Database from a Client Computer4-54.4.14.553-18About Easy Connect PlusConfiguring the Network Environment: Oracle by Example Series4-74-8Managing the Oracle Instance5.1Overview of the Oracle Instance and Instance Management5.1.1About Initialization Parameters5-25.1.2About Background Processes5-35.1.3About Server and Client Processes5-55.1.4About Instance Memory Structures5-55.1.4.1System Global Area5-65.1.4.2Program Global Area5-75.1.55.25.35-1About Instance Startup and Shutdown5-85.1.5.1About Administration Privileges for Startup and Shutdown5-85.1.5.2About Instance Startup5-85.1.5.3About Instance Shutdown5-9Shutting Down and Starting Up the Oracle Instance5-105.2.1Shutting Down and Starting Up Using SQL*Plus5-105.2.2Shutting Down and Starting Up Using the Windows Services Program5-11Viewing and Modifying Initialization Parameters5-11v
5.45.56Managing Memory5-155.4.1About Memory Management5-155.4.2Enabling Automatic Memory Management5-175.4.3Modifying Memory Settings – Automatic Memory Management5-205.4.4Enabling Automatic Shared Memory Management5-225.4.5Modifying Memory Settings – Automatic Shared Memory Management5-255.4.6Enabling Manual Shared Memory Management5-265.4.7Modifying Memory Settings - Manual Shared Memory Management5-285.4.8Modifying Memory Settings – Automatic PGA Memory Management5-29Managing the Oracle Instance: Oracle By Example Series5-31Managing Database Storage Structures6.1About Database Storage Structures6.1.1About Control Files6-36.1.2About Online Redo Log Files6-36.1.3About Archived Redo Log Files6-56.1.4About Rollback Segments6-56.1.5About Data Files6-66.1.6About Tablespaces6-76.1.6.1Locally Managed Tablespaces6-86.1.6.2Tablespace Types6-86.1.6.3Tablespace Status6-96.1.6.4Autoextend Tablespace6-96.1.6.5Encrypted Tablespaces6-106.1.76.26.3About Other Storage StructuresViewing Database Storage Structure Information6-106-116.2.1Viewing Control File Information6-126.2.2Viewing Online Redo Log File Information6-136.2.3Viewing Archived Redo Log File Information6-136.2.4Viewing Tablespace and Data File Information6-14Performing Common Database Storage Tasks6-146.3.1Creating a Tablespace6-156.3.2Modifying a Tablespace6-176.3.2.1Setting a Tablespace to Automatically Extend6-176.3.2.2Setting the Datafile for a Smallfile Tablespace to AutomaticallyExtend6-18Taking a Tablespace Offline6-186.3.2.36.3.36.46-1Dropping a TablespaceManaging the Online Redo Log6-196-206.4.1Multiplexing the Online Redo Log6-216.4.2Switching a Log File6-21vi
6.56.67Managing Undo Data6-226.5.1About Undo Data6-226.5.2About Managing Undo Data6-236.5.3Viewing Undo Information6-246.5.4Computing the Minimum Undo Tablespace Size Using the Undo Advisor6-256.5.5Changing the Undo Tablespace to a Fixed Size6-276.5.6Changing the Datafiles for an Undo Tablespace to a Fixed Size6-286.5.7Changing Undo Management Analysis Parameters6-296.5.8Switching Undo Tablespaces6-29Managing Database Storage Structures: Oracle by Example Series6-30Administering User Accounts and Security7.1About User Accounts7.1.17.2Common Users in a CDB7-37.1.1.2Local Users in a CDB7-4About User Privileges and Roles7.5About Common and Local Roles in a CDB7-57-67.2.1.1Common Roles in a CDB7-77.2.1.2Local Roles in a PDB7-77.2.27.47-37.1.1.17.2.17.3About Commonality in a CDB7-1About Privilege and Role Grants in a CDB7-87.2.2.1Privileges and Roles Granted Commonly in a CDB7-97.2.2.2Privileges and Roles Granted Locally in a CDB7-9About Administrative Accounts and Privileges7-97.3.1SYS and SYSTEM Users7-107.3.2SYSDBA and SYSOPER System Privileges7-10Administering Roles7-117.4.1Viewing Roles7-127.4.2Example: Creating a Role7-127.4.3Example: Modifying a Role7-147.4.4Deleting a Role7-14Administering Database User Accounts7-157.5.1Viewing User Accounts7-157.5.2Example: Creating a User Account7-167.5.3Creating a New User Account by Duplicating an Existing User Account7-187.5.4Example: Granting Privileges and Roles to a User Account7-187.5.5Example: Assigning a Tablespace Quota to a User Account7-207.5.6Example: Modifying a User Account7-217.5.7Locking and Unlocking User Accounts7-217.5.8Expiring a User Password7-21vii
7.5.97.67.78Example: Deleting a User AccountSetting the Database Password Policy7-227-227.6.1About Password Policies7-237.6.2Modifying the Default Password Policy7-24Administering User Accounts and Security: Oracle by Example Series7-25Managing Schema Objects8.1About Schema Objects8-18.2About Schema Object Management Privileges8-28.3About SQL Developer8-28.48.3.1Installing and Starting SQL Developer8-38.3.2Understanding the SQL Developer User Interface8-38.3.3Creating a Database Connection Using SQL Developer8-4Managing Tables8.4.18.58-78.4.1.1About Table Types8-88.4.1.2About Table Column Attributes8-88.4.1.3About Table-Level Constraints8-108.4.1.4Other Table Creation Considerations8-118.4.2Viewing Tables8-138.4.3Viewing Table Data8-148.4.4Example: Creating a Table8-148.4.5Modifying Table Attributes8-168.4.5.1Example: Adding Table Columns8-178.4.5.2Example: Deleting a Table Column8-188.4.5.3Example: Adding a New Table Constraint8-198.4.5.4Example: Modifying an Existing Table Constraint8-208.4.5.5Example: Deleting a Table Constraint8-218.4.6Example: Loading Data into a Table8-228.4.7Deleting a Table8-24Managing Indexes8.5.18.6About Tables8-7About Indexes8-258-258.5.1.1Indexes and Performance8-268.5.1.2Index Attributes8-268.5.2Viewing Indexes8-278.5.3Example: Creating an Index8-288.5.4Example: Deleting an Index8-29Managing Views8-308.6.1About Views8-308.6.2Displaying Views8-31viii
8.798.6.3Example: Creating a View8-328.6.4Example: Deleting a View8-33Managing Program Code Stored in the Database8-348.7.1About Program Code Stored in the Database8-348.7.2Validating (Compiling) Invalid Schema Objects8-358.8Working with Other Schema Objects8-378.9Managing Schema Objects: Oracle by Example Series8-38Performing Backup and Recovery9.1Overview of Database Backup and Recovery9.1.19.29.3Overview of Backing Up and Recovering CDBs and PDBs9-19-29.1.1.1Backup and Complete Recovery of CDBs9-39.1.1.2Backup and Complete Recovery of PDBs9-49.1.1.3Point-in-Time Recovery in a Multitenant Environment9-59.1.1.4Flashback Database in a Multitenant Environment9-6Database Backup and Recovery Concepts9-89.2.1ARCHIVELOG and NOARCHIVELOG Mode9-99.2.2RMAN Repository9-99.2.3Image Copies and Backup Sets9-99.2.4Full Backups and Incremental Backups9-109.2.5Consistent and Inconsistent Backups9-109.2.6Media Recovery9-119.2.7Fast Recovery Area9-11Configuring Your Database for Basic Backup and Recovery9.3.1Planning Space Usage and Location for the Fast Recovery Area9-129-129.3.1.1About the Backup Retention Policy and the Fast Recovery Area9-139.3.1.2About the Fast Recovery Area Size9-139.3.2Configuring Users to Perform Backup and Recovery9-149.3.2.1Credentials Required to Perform Backup and Recovery9-149.3.2.2Granting the SYSBACKUP Privilege9-159.3.3Connecting to the Target Database Using RMAN9-159.3.4Configuring Recovery Settings9-169.3.4.1Configuring the Fast Recovery Area9-179.3.4.2Enabling Archiving of Redo Log Files9-189.3.4.3Enabling Flashback Database9-199.3.5Configuring Backup Settings9-209.3.5.1Configuring Backup Device Settings9-219.3.5.2Configuring Backup Policy Settings9-229.3.5.3Configuring Automatic Backups for the Control File and ServerParameter File9-22Enabling Block Change Tracking9-239.3.5.4ix
9.4Backing Up Your Database9.4.1Additional Backup Concepts9.4.1.19.4.1.29.4.29-25Incrementally Updated Backups: Rolling Forward Image Copies ofData Files9-25Backup Tags9-25Performing and Scheduling Backups Using RMAN9-269.4.2.1Performing a Whole Database Backup9-269.4.2.2Using the Oracle Suggested Backup Strategy9-279.4.2.3About the Oracle Suggested Backup Strategy and Retention9-319.4.2.4Scheduling Miscellaneous Backup Tasks9-329.4.3Displaying Backups Stored in the RMAN Repository9-329.4.4Validating Backups and Testing Your Backup Strategy9-339.4.4.1Validating Selected Backups9-349.4.4.2Validating Backups for Restore Operations9-359.5Displaying Backup Reports9-369.6Managing Backups9-379.6.1About Backup Management9-379.6.2Cross-Checking Backups9-389.6.3Deleting Expired Backups9-399.6.4Marking Backups as Available or Unavailable9-409.6.5Deleting Obsolete Backups9-419.6.6Monitoring Fast Recovery Area Space Usage9-429.7Performing Oracle Advised Recovery9-429.7.1About Data Recovery Advisor9-439.7.2Using Data Recovery Advisor9-44Performing User-Directed Recovery9-459.89.8.1Rewinding a Table Using Oracle Flashback Table9-459.8.1.1Enabling Row Movement on a Table9-469.8.1.2Performing a Flashback Table Operation9-469.8.2Recovering a Dropped Table Using Oracle Flashback Drop9-479.8.2.1Dropping a Table9-489.8.2.2Retrieving a Dropped Table9-499.8.3Rewinding a Database Using Oracle Flashback Database9-499.8.4Restoring and Recovering the Database9-509.9109-24Performing Backup and Recovery: Oracle By Example Series9-51Monitoring and Tuning the Database10.1Proactive Database Monitoring10-110.1.1Monitoring General Database State and Workload10-210.1.2Monitoring Performance Using the Performance Hub10-510.1.2.1Specifying the Time Period for Which to Display Statistics10-10x
10.1.3About Viewing Performance Statistics on a Standby Database10.1.4Performance Self-Diagnostics: Automatic Database Diagnostic Monitor10-1210-1310.2Diagnosing Performance Problems Using ADDM10.2.1Viewing a Summary of ADDM Performance Findings10-1510.2.2Responding to ADDM Performance Findings10-1610.2.3Viewing a Summary of Real-Time ADDM Findings10-1710.2.4Responding to Real-Time ADDM Findings10-1810.3Using Advisors to Optimize Database Performance10-1910.3.1About Advisors10-2010.3.2About the SQL Tuning Advisor10-2110.3.3About the Automatic SQL Tuning Advisor10-2210.3.4Configuring the Automatic SQL Tuning Advisor10-2310.3.5Viewing Automatic SQL Tuning Results10-2510.3.6Running the SQL Tuning Advisor10-2910.3.7About Tuning SQL Statements on a Standby Database10-3410.3.7.110.3.810.4Tuning SQL Statements on a Standby DatabaseOptimizing Memory Usage with the Memory Advisors10.3.8.11110-14About the Memory AdvisorsMonitoring and Tuning the Database: Oracle by Example Series10-3510-3710-3710-38Analyzing the Impact of Database Changes on SQL Performance11.1About SPA Quick Check11-111.2About Configuring SPA Quick Check11-211.2.1About SQL Tuning Sets11-211.2.2Specifying Default Values for SPA Quick Check11-311.3About Validating the Impact of an Initialization Parameter Change11.3.111.4Validating the Impact of an Initialization Parameter ChangeAbout Validating the Impact of Implementing a SQL Profile11-411-511-611.4.1Validating the Impact of Implementing a SQL Profile11-611.4.2About Validating the Impact of Implementing Multiple SQL Profiles11-811.4.3Validating the Impact of Implementing Multiple SQL Profiles11-811.5About SQL Performance Analyzer11-1011.6Specifying Default Settings for SQL Performance Analyzer11-1111.6.1Creating a SQL Performance Analyzer Task11-1311.6.2Creating a Pre-Change SQL Trial11-1311.6.3Creating a Post-Change SQL Trial11-1411.6.4Comparing Two SQL Trials11-1611.7Analyzing the Impact of Database Changes on SQL Performance: Oracle byExample Series11-17xi
12Managing Database Resources12.1About Resource Management12-112.1.1About Resource Manager Solutions for a Non-CDB12-212.1.2About Resource Manager Solutions for a CDB12-312.1.3About Consumer Groups12-412.1.4About Plans12-512.1.5About Directives12-612.1.6About Non-CDB Plans12-612.1.7About Managing CDB and PDB Workloads12-712.1.7.1About CDB Plans12-712.1.7.2About PDB Plans12-1312.1.812.2Accessing the Resource Management PageManaging Plans12.2.1Creating Plans12-2212-2212.2.1.1Creating a New Plan using Quick Setup12-2212.2.1.2Creating a New Plan12-2412.2.2Viewing Plans12-2612.2.2.1Viewing All the Plans12-2612.2.2.2Viewing a Plan12-2612.2.3Modifying a Plan12-2712.2.3.1Adding a New Consumer Group to a Plan12-2812.2.3.2Adding an Existing Consumer Group
2 Installing Oracle Database and Creating a Database 2.1 Overview of Installing Oracle Database Software and Creating a Database 2-1 2.1.1 Checking Oracle Database Installation Prerequisites 2-2 2.1.2 Deciding on Oracle Database Installation Choices 2-3 2.1.2.1 Install Option for Oracle Database 2-3 2.1.2.2 Installation Class for Oracle .
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Toad for Oracle Base Edition Toad for Oracle Professional Edition Toad for Oracle Xpert Edition Toad DB Admin Module (add-on) Toad Development Suite for Oracle Toad DBA Suite for Oracle Toad DBA Suite for Oracle - RAC Edition Toad DBA Suite for Oracle - Exadata Edition Convert embedded SQL to other programming languages (Delphi, C, C ,
SQL DBA Training Course Plan Training Module Dur SQL DBA Plan A SQL DBA Plan B SQL DBA Plan C Module 1 Core SQL DBA [HA-DR, AlwaysOn, Errors, Upgrades] 6 W Module 2 Azure SQL DBA [Migrations, HA-DR, PowerShell, Linux] 3 W X Module 3 Power BI Report Design, Cloud & Server Admin 4 W X X
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
An Oracle Database Administrator can earn US 94,000 in the United States – Indeed Being an Oracle Certified Professional (OCP) is crucial to work as an Oracle DBA – Oracle Oracle DBA is associated with
Oracle DBA RAC DBA X X Oracle Cloud X Course Fee INR 20,000/- USD 300 INR 35,000/- USD 500 INR 35,000/- USD 500 ** Resume Preperation, Mock Interviews are included in every plan. ** Chapter wise Mock Interviews are included in every plan. Oracle DBA Course Content Applicable for Plan A, B, C Modules -1- Linux/UNIX Installation & Commands .
No additional words or design features may appear between the logo and the DBA name. When the DBA name includes “KELLER WILLIAMS REALTY,” use the appropiate logo provided, as outlined in section 3.1c. DBA NAME DBA NAME DBA NAME MORE THAN TWELVE CHARACTERS DBA NAME MORE THAN TWELV
7 Messaging Server Oracle Oracle Communications suite Oracle 8 Mail Server Oracle Oracle Communications suite Oracle 9 IDAM Oracle Oracle Access Management Suite Plus / Oracle Identity Manager Connectors Pack / Oracle Identity Governance Suite Oracle 10 Business Intelligence
