Database Administrator's Guide

1610 Pages·2017·10.77 MB·English

Oracle® Database
Database Administrator’s Guide
18c
E80561-07
May 2020
Oracle Database Database Administrator’s Guide, 18c
E80561-07
Copyright © 1996, 2020, Oracle and/or its affiliates.
Primary Author: Rajesh Bhatiya, Padmaja Potineni
Contributing Authors: Virginia Beecher, Leo Cloutier, Steve Fogel, Chuck Murray, Randy Urbano
Contributors: A. Agrawal, L. Ashdown, P. Avril, D. Austin, T. Baby, H. Baer, S. Ball, S. Battula, M. Bauer, T.
Bednar, E. Belden, J. Byun, L. Carpenter, A. Chaudhry, C. Chang, B. Cheng, H. Chien, T. Chien, G.
Christman, C. C. Chui, L. Clarke, D. Colello, C. Colrain, K. Cook, J. Creighton, A. Dadhich, S. Datta, S.
Davidson, M. Dilman, S. Doraiswamy, J. Draaijer, M. Fallen, M. Fuller, D. Gagne, A. Ganesh, GP Gongloor,
J. Gonzalez, V. Goorah, S. Gopalan, S. Gupta, B. Habeck, S. Hase, W. Hu, P. Huey, K. Inoue, M. Ito, C. Iyer,
K. Itikarlapalli, P. Jaganath, S. Jain, C. Jones, S. Joshi, B. Khaladkar, F. Kobylanski, B. Krishnan, V.
Krishnaswamy, A. Kruglikov, B. Kuchibhotla, V. Kuhr, R. Kumar, S. Kumar, V. Kumar, H. Lakshmanan, A.
Lee, B. Lee, J. Lee, S. K. Lee, T. Lee, C. Lei, B. Leung, Y. Li, I. Listvinsky, B. Llewellyn, H. Lombera, B.
Lundhild, S. Lynn, R. Mani, V. Marwah, C. McGregor, J. McDonnell, J. McHugh, B. McGuirk, J. Meeks, K.
Mensah, M. Minhas, K. Mohan, H. Mohankumar, A. Munnolimath, G. Mulagund, P. Murguia, P. Murthy, A.
Mylavarapu, V. Moore, N. Muthukrishnan, S. Muthulingam, L. Nim, S. Panchumarthy, R. Pang, V.
Panteleenko, R. Pingte, K. Rajamani, A. Raghavan, M. Ramacher, R. Ramkissoon, S. Ravindhran, G.
Ravipati, A. Ray, W. Ren, K. Rich, J. Rivera, C. A. L. Rueda, R. Rungta, S. Sahu, P. Shanthaveerappa, S.
Sonawane, Y. Sarig, M. Savanur, S. Shankar, D. Sharma, A. Shen, B. Sinha, J. Spiller, D. Steiner, J. Stern,
M. Stewart, S. Stoian, R. Swonger, M. Subramaniam, N. Sundarappa, M. Susairaj, A. Tran, A. Tsukerman, C.
Tuzla, T. Ueda, K. Umamageswaran, D. Utzig, E. Voss, N. Wagner, X. Wang, M. Wei, S. Wertheimer, P.
Wheeler, D. Williams, A. Witkowski, S. Wolicki, D. M. Wong, Z. Yang, T. F. Yu, W. Zhang
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license 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.
Reverse engineering, disassembly, or decompilation of this software, unless required by law for
interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you 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 on
behalf 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. Government end
users are "commercial computer software" or “commercial computer software documentation” pursuant to 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 programs
embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle
computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the
license contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloud
services 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 that
may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you
shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its
safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this
software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of
their respective owners.
Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are
used 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 registered
trademark 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 expressly
disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise
set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be
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.
Contents
Preface
Audience lvi
Documentation Accessibility lvi
Related Documents lvi
Conventions lvii
Changes in This Release for Oracle Database Administrator's
Guide
Changes in Oracle Database 18c lviii
Changes in Oracle Database 12c Release 2 (12.2) lxi
Changes in Oracle Database 12c Release 1 (12.1.0.2) lxiii
Changes in Oracle Database 12c Release 1 (12.1.0.1) lxiv
Part I
Basic Database Administration
1 Getting Started with Database Administration
1.1 Types of Oracle Database Users 1-1
1.1.1 Database Administrators 1-2
1.1.2 Security Officers 1-3
1.1.3 Network Administrators 1-3
1.1.4 Application Developers 1-3
1.1.5 Application Administrators 1-4
1.1.6 Database Users 1-4
1.2 Tasks of a Database Administrator 1-4
1.2.1 Task 1: Evaluate the Database Server Hardware 1-5
1.2.2 Task 2: Install the Oracle Database Software 1-5
1.2.3 Task 3: Plan the Database 1-6
1.2.4 Task 4: Create and Open the Database 1-6
1.2.5 Task 5: Back Up the Database 1-7
1.2.6 Task 6: Enroll System Users 1-7
iii
1.2.7 Task 7: Implement the Database Design 1-7
1.2.8 Task 8: Back Up the Fully Functional Database 1-7
1.2.9 Task 9: Tune Database Performance 1-7
1.2.10 Task 10: Download and Install Release Updates and Release Update
Revisions 1-8
1.2.11 Task 11: Roll Out to Additional Hosts 1-8
1.3 SQL Statements 1-9
1.3.1 Submitting Commands and SQL to the Database 1-9
1.3.2 About SQL*Plus 1-10
1.3.3 Connecting to the Database with SQL*Plus 1-10
1.3.3.1 About Connecting to the Database with SQL*Plus 1-11
1.3.3.2 Step 1: Open a Command Window 1-11
1.3.3.3 Step 2: Set Operating System Environment Variables 1-11
1.3.3.4 Step 3: Start SQL*Plus 1-12
1.3.3.5 Step 4: Submit the SQL*Plus CONNECT Command 1-12
1.4 Identifying Your Oracle Database Software Release 1-17
1.4.1 About Oracle Database Release Numbers 1-17
1.4.2 Checking Your Current Release Number 1-19
1.5 About Database Administrator Security and Privileges 1-19
1.5.1 The Database Administrator's Operating System Account 1-19
1.5.2 Administrative User Accounts 1-20
1.5.2.1 About Administrative User Accounts 1-20
1.5.2.2 SYS 1-21
1.5.2.3 SYSTEM 1-21
1.5.2.4 SYSBACKUP, SYSDG, SYSKM, and SYSRAC 1-22
1.5.2.5 The DBA Role 1-23
1.6 Database Administrator Authentication 1-23
1.6.1 Administrative Privileges 1-24
1.6.2 Operations Authorized by Administrative Privileges 1-25
1.6.3 Authentication Methods for Database Administrators 1-27
1.6.3.1 About Authentication Methods for Database Administrators 1-27
1.6.3.2 Nonsecure Remote Connections 1-29
1.6.3.3 Local Connections and Secure Remote Connections 1-29
1.6.4 Using Operating System Authentication 1-29
1.6.4.1 Operating System Groups 1-29
1.6.4.2 Preparing to Use Operating System Authentication 1-31
1.6.4.3 Connecting Using Operating System Authentication 1-31
1.6.5 Using Password File Authentication 1-32
1.6.5.1 Preparing to Use Password File Authentication 1-32
1.6.5.2 Connecting Using Password File Authentication 1-34
1.7 Creating and Maintaining a Database Password File 1-35
iv
1.7.1 ORAPWD Syntax and Command Line Argument Descriptions 1-35
1.7.2 Creating a Database Password File with ORAPWD 1-42
1.7.3 Sharing and Disabling the Database Password File 1-43
1.7.4 Keeping Administrator Passwords Synchronized with the Data
Dictionary 1-44
1.7.5 Adding Users to a Database Password File 1-45
1.7.6 Granting and Revoking Administrative Privileges 1-46
1.7.7 Viewing Database Password File Members 1-46
1.7.8 Removing a Database Password File 1-47
1.8 Data Utilities 1-47
2 Creating and Configuring an Oracle Database
2.1 About Creating an Oracle Database 2-2
2.2 Considerations Before Creating the Database 2-3
2.2.1 Planning for Database Creation 2-3
2.2.2 About Selecting a Character Set 2-5
2.2.3 About Configuring an Oracle Home in Read-Only Mode 2-6
2.2.4 Prerequisites for Database Creation 2-7
2.3 Creating a Database with DBCA 2-7
2.3.1 About Creating a Database with DBCA 2-8
2.3.2 About Creating a Database with Interactive DBCA 2-8
2.3.3 About Creating a Database with Noninteractive/Silent DBCA 2-8
2.3.3.1 DBCA Examples 2-8
2.4 Creating a Database with the CREATE DATABASE Statement 2-9
2.4.1 About Creating a Database with the CREATE DATABASE Statement 2-10
2.4.2 Step 1: Specify an Instance Identifier (SID) 2-11
2.4.3 Step 2: Ensure That the Required Environment Variables Are Set 2-12
2.4.4 Step 3: Choose a Database Administrator Authentication Method 2-13
2.4.5 Step 4: Create the Initialization Parameter File 2-13
2.4.6 Step 5: (Windows Only) Create an Instance 2-14
2.4.7 Step 6: Connect to the Instance 2-15
2.4.8 Step 7: Create a Server Parameter File 2-16
2.4.9 Step 8: Start the Instance 2-17
2.4.10 Step 9: Issue the CREATE DATABASE Statement 2-17
2.4.11 Step 10: Create Additional Tablespaces 2-21
2.4.12 Step 11: Run Scripts to Build Data Dictionary Views 2-21
2.4.13 Step 12: (Optional) Run Scripts to Install Additional Options 2-22
2.4.14 Step 13: Back Up the Database 2-22
2.4.15 Step 14: (Optional) Enable Automatic Instance Startup 2-22
2.5 Specifying CREATE DATABASE Statement Clauses 2-23
2.5.1 About CREATE DATABASE Statement Clauses 2-24
v
2.5.2 Protecting Your Database: Specifying Passwords for Users SYS and
SYSTEM 2-24
2.5.3 Creating a Locally Managed SYSTEM Tablespace 2-25
2.5.4 Specify Data File Attributes for the SYSAUX Tablespace 2-26
2.5.4.1 About the SYSAUX Tablespace 2-26
2.5.5 Using Automatic Undo Management: Creating an Undo Tablespace 2-27
2.5.6 Creating a Default Permanent Tablespace 2-27
2.5.7 Creating a Default Temporary Tablespace 2-28
2.5.8 Specifying Oracle Managed Files at Database Creation 2-29
2.5.9 Supporting Bigfile Tablespaces During Database Creation 2-30
2.5.9.1 Specifying the Default Tablespace Type 2-31
2.5.9.2 Overriding the Default Tablespace Type 2-31
2.5.10 Specifying the Database Time Zone and Time Zone File 2-32
2.5.10.1 Setting the Database Time Zone 2-32
2.5.10.2 About the Database Time Zone Files 2-32
2.5.10.3 Specifying the Database Time Zone File 2-33
2.5.11 Specifying FORCE LOGGING Mode 2-33
2.5.11.1 Using the FORCE LOGGING Clause 2-34
2.5.11.2 Performance Considerations of FORCE LOGGING Mode 2-34
2.6 Specifying Initialization Parameters 2-35
2.6.1 About Initialization Parameters and Initialization Parameter Files 2-36
2.6.1.1 Sample Initialization Parameter File 2-37
2.6.1.2 Text Initialization Parameter File Format 2-38
2.6.2 Determining the Global Database Name 2-39
2.6.2.1 DB_NAME Initialization Parameter 2-39
2.6.2.2 DB_DOMAIN Initialization Parameter 2-40
2.6.3 Specifying a Fast Recovery Area 2-40
2.6.4 Specifying Control Files 2-41
2.6.5 Specifying Database Block Sizes 2-41
2.6.5.1 DB_BLOCK_SIZE Initialization Parameter 2-42
2.6.5.2 Nonstandard Block Sizes 2-42
2.6.6 Specifying the Maximum Number of Processes 2-43
2.6.7 Specifying the DDL Lock Timeout 2-43
2.6.8 Specifying the Method of Undo Space Management 2-44
2.6.8.1 UNDO_MANAGEMENT Initialization Parameter 2-45
2.6.8.2 UNDO_TABLESPACE Initialization Parameter 2-45
2.6.9 Specifying the Database Compatibility Level 2-45
2.6.9.1 About The COMPATIBLE Initialization Parameter 2-46
2.6.10 Setting the License Parameter 2-47
2.7 Managing Initialization Parameters Using a Server Parameter File 2-47
2.7.1 What Is a Server Parameter File? 2-48
vi
2.7.2 Migrating to a Server Parameter File 2-49
2.7.3 Server Parameter File Default Names and Locations 2-50
2.7.4 Creating a Server Parameter File 2-51
2.7.5 The SPFILE Initialization Parameter 2-51
2.7.6 Changing Initialization Parameter Values 2-52
2.7.6.1 About Changing Initialization Parameter Values 2-52
2.7.6.2 Setting or Changing Initialization Parameter Values 2-52
2.7.7 Clearing Initialization Parameter Values 2-54
2.7.8 Exporting the Server Parameter File 2-55
2.7.9 Backing Up the Server Parameter File 2-56
2.7.10 Recovering a Lost or Damaged Server Parameter File 2-56
2.7.11 Methods for Viewing Parameter Settings 2-57
2.8 Managing Application Workloads with Database Services 2-58
2.8.1 Database Services 2-58
2.8.1.1 About Database Services 2-59
2.8.1.2 Database Services and Performance 2-60
2.8.1.3 Oracle Database Features That Use Database Services 2-60
2.8.1.4 Creating Database Services 2-61
2.8.2 Global Data Services 2-62
2.8.3 Database Service Data Dictionary Views 2-63
2.9 Considerations After Creating a Database 2-63
2.9.1 Some Security Considerations 2-64
2.9.2 Transparent Data Encryption 2-65
2.9.3 A Secure External Password Store 2-65
2.9.4 Transaction Guard and Application Continuity 2-66
2.9.5 File System Server Support in the Database 2-67
2.9.6 The Oracle Database Sample Schemas 2-68
2.10 Cloning a Database 2-68
2.10.1 Cloning a Database with CloneDB 2-68
2.10.1.1 About Cloning a Database with CloneDB 2-68
2.10.1.2 Cloning a Database with CloneDB 2-69
2.10.1.3 After Cloning a Database with CloneDB 2-74
2.10.2 Cloning a Database in a Multitenant Environment 2-75
2.11 Dropping a Database 2-75
2.12 Database Data Dictionary Views 2-76
2.13 Database Configuration Assistant Command Reference for Silent Mode 2-76
2.13.1 DBCA Command-Line Syntax Overview 2-77
2.13.2 About DBCA Templates 2-78
2.13.3 Database User Authentication in DBCA Commands Using Oracle
Wallet 2-79
2.13.4 DBCA Silent Mode Commands 2-80
vii
2.13.4.1 createDatabase 2-81
2.13.4.2 createDuplicateDB 2-88
2.13.4.3 configureDatabase 2-91
2.13.4.4 createTemplateFromDB 2-95
2.13.4.5 createTemplateFromTemplate 2-96
2.13.4.6 createCloneTemplate 2-99
2.13.4.7 deleteTemplate 2-100
2.13.4.8 generateScripts 2-101
2.13.4.9 deleteDatabase 2-108
2.13.4.10 createPluggableDatabase 2-110
2.13.4.11 unplugDatabase 2-115
2.13.4.12 deletePluggableDatabase 2-116
2.13.4.13 configurePluggableDatabase 2-117
2.13.4.14 addInstance 2-119
2.13.4.15 deleteInstance 2-120
2.13.4.16 executePrereqs 2-122
2.13.5 DBCA Exit Codes 2-122
3 Starting Up and Shutting Down
3.1 Starting Up a Database 3-1
3.1.1 About Database Startup Options 3-2
3.1.1.1 Starting Up a Database Using SQL*Plus 3-3
3.1.1.2 Starting Up a Database Using Recovery Manager 3-3
3.1.1.3 Starting Up a Database Using Cloud Control 3-3
3.1.1.4 Starting Up a Database Using SRVCTL 3-3
3.1.2 Specifying Initialization Parameters at Startup 3-4
3.1.2.1 About Initialization Parameter Files and Startup 3-4
3.1.2.2 Starting Up with SQL*Plus with a Nondefault Server Parameter
File 3-6
3.1.2.3 Starting Up with SRVCTL with a Nondefault Server Parameter
File 3-6
3.1.3 About Automatic Startup of Database Services 3-7
3.1.4 Preparing to Start Up an Instance 3-7
3.1.5 Starting Up an Instance 3-8
3.1.5.1 About Starting Up an Instance 3-9
3.1.5.2 Starting an Instance, and Mounting and Opening a Database 3-10
3.1.5.3 Starting an Instance Without Mounting a Database 3-10
3.1.5.4 Starting an Instance and Mounting a Database 3-11
3.1.5.5 Restricting Access to an Instance at Startup 3-11
3.1.5.6 Forcing an Instance to Start 3-12
viii
3.1.5.7 Starting an Instance, Mounting a Database, and Starting
Complete Media Recovery 3-13
3.1.5.8 Automatic Database Startup at Operating System Start 3-13
3.1.5.9 Starting Remote Instances 3-13
3.2 Altering Database Availability 3-13
3.2.1 Mounting a Database to an Instance 3-14
3.2.2 Opening a Closed Database 3-14
3.2.3 Opening a Database in Read-Only Mode 3-14
3.2.4 Restricting Access to an Open Database 3-16
3.3 Shutting Down a Database 3-16
3.3.1 About Shutting Down the Database 3-17
3.3.2 Shutting Down with the Normal Mode 3-17
3.3.3 Shutting Down with the Immediate Mode 3-18
3.3.4 Shutting Down with the Transactional Mode 3-18
3.3.5 Shutting Down with the Abort Mode 3-19
3.3.6 Shutdown Timeout 3-20
3.4 Quiescing a Database 3-20
3.4.1 About Quiescing a Database 3-20
3.4.2 Placing a Database into a Quiesced State 3-21
3.4.3 Restoring the System to Normal Operation 3-22
3.4.4 Viewing the Quiesce State of an Instance 3-23
3.5 Suspending and Resuming a Database 3-23
3.6 Delaying Instance Abort 3-24
4 Configuring Automatic Restart of an Oracle Database
4.1 About Oracle Restart 4-1
4.1.1 Oracle Restart Overview 4-2
4.1.2 About Startup Dependencies 4-3
4.1.3 About Starting and Stopping Components with Oracle Restart 4-3
4.1.4 About Starting and Stopping Oracle Restart 4-4
4.1.5 Oracle Restart Configuration 4-4
4.1.6 Oracle Restart Integration with Oracle Data Guard 4-6
4.1.7 Fast Application Notification with Oracle Restart 4-7
4.1.7.1 Overview of Fast Application Notification 4-7
4.1.7.2 Application High Availability with Services and FAN 4-8
4.2 Configuring Oracle Restart 4-12
4.2.1 About Configuring Oracle Restart 4-13
4.2.2 Preparing to Run SRVCTL 4-14
4.2.3 Obtaining Help for SRVCTL 4-15
4.2.4 Adding Components to the Oracle Restart Configuration 4-15
4.2.5 Removing Components from the Oracle Restart Configuration 4-17
ix
4.2.6 Disabling and Enabling Oracle Restart Management for a Component 4-18
4.2.7 Viewing Component Status 4-19
4.2.8 Viewing the Oracle Restart Configuration for a Component 4-19
4.2.9 Modifying the Oracle Restart Configuration for a Component 4-20
4.2.10 Managing Environment Variables in the Oracle Restart Configuration 4-21
4.2.10.1 About Environment Variables in the Oracle Restart Configuration 4-21
4.2.10.2 Setting and Unsetting Environment Variables 4-22
4.2.10.3 Viewing Environment Variables 4-22
4.2.11 Creating and Deleting Database Services with SRVCTL 4-23
4.2.12 Enabling FAN Events in an Oracle Restart Environment 4-24
4.2.13 Automating the Failover of Connections Between Primary and Standby
Databases 4-25
4.2.14 Enabling Clients for Fast Connection Failover 4-26
4.2.14.1 About Enabling Clients for Fast Connection Failover 4-26
4.2.14.2 Enabling Fast Connection Failover for JDBC Clients 4-27
4.2.14.3 Enabling Fast Connection Failover for Oracle Call Interface
Clients 4-28
4.2.14.4 Enabling Fast Connection Failover for ODP.NET Clients 4-29
4.3 Starting and Stopping Components Managed by Oracle Restart 4-30
4.4 Stopping and Restarting Oracle Restart for Maintenance Operations 4-33
4.5 SRVCTL Command Reference for Oracle Restart 4-34
4.5.1 add 4-37
4.5.1.1 srvctl add asm 4-38
4.5.1.2 srvctl add database 4-39
4.5.1.3 srvctl add listener 4-41
4.5.1.4 srvctl add ons 4-42
4.5.1.5 srvctl add service 4-43
4.5.2 config 4-47
4.5.2.1 srvctl config asm 4-48
4.5.2.2 srvctl config database 4-48
4.5.2.3 srvctl config listener 4-49
4.5.2.4 srvctl config ons 4-50
4.5.2.5 srvctl config service 4-50
4.5.3 disable 4-51
4.5.3.1 srvctl disable asm 4-52
4.5.3.2 srvctl disable database 4-52
4.5.3.3 srvctl disable diskgroup 4-53
4.5.3.4 srvctl disable listener 4-53
4.5.3.5 srvctl disable ons 4-54
4.5.3.6 srvctl disable service 4-54
4.5.4 downgrade 4-55
4.5.4.1 srvctl downgrade database 4-55
x