Database Administrator’s Guide - Oracle

Oracle? Database

Database Administrator��s Guide

19c

E96348-17

August 2023

Oracle Database Database Administrator��s Guide, 19c

E96348-17

Copyright ? 1996, 2023, Oracle and/or its affiliates.

Primary Authors: Mark Doran, Padmaja Potineni, Rajesh Bhatiya

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, software documentation, data (as defined in the Federal Acquisition Regulation), 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," "commercial computer software documentation," or "limited rights

data" 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?, Java, and MySQL 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

lvii

Documentation Accessibility

lvii

Related Documents

lvii

Conventions

lviii

Changes in This Release for Oracle Database Administrator's Guide

Changes in Oracle Database Release 19c, Version 19.7

lix

Changes in Oracle Database Release 19c, Version 19.1

lix

Changes in Oracle Database Release 18c, Version 18.1

lxii

Changes in Oracle Database 12c Release 2 (12.2)

lxv

Changes in Oracle Database 12c Release 1 (12.1.0.2)

lxvii

Changes in Oracle Database 12c Release 1 (12.1.0.1)

lxviii

Part I

1

Basic Database Administration

Getting Started with Database Administration

1.1

1.2

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-3

1.1.6

Database Users

1-4

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-6

iii

1.2.6

Task 6: Enroll System Users

1-7

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

1.2.11

1.3

1.4

1.5

1.6

Task 10: Download and Install Release Updates and Release Update

Revisions

1-8

Task 11: Roll Out to Additional Hosts

1-8

SQL Statements

1-9

1.3.1

Submitting Commands and SQL to the Database

1-9

1.3.2

About SQL*Plus

1-9

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-10

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

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

About Database Administrator Security and Privileges

1-19

1.5.1

The Database Administrator's Operating System Account

1-20

1.5.2

Administrative User Accounts

1-20

1.5.2.1

About Administrative User Accounts

1-21

1.5.2.2

SYS

1-21

1.5.2.3

SYSTEM

1-22

1.5.2.4

SYSBACKUP, SYSDG, SYSKM, and SYSRAC

1-22

1.5.2.5

The DBA Role

1-23

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-30

1.6.4.1

Operating System Groups

1-30

1.6.4.2

Preparing to Use Operating System Authentication

1-32

1.6.4.3

Connecting Using Operating System Authentication

1-32

1.6.5

Using Password File Authentication

1-33

1.6.5.1

Preparing to Use Password File Authentication

1-33

1.6.5.2

Connecting Using Password File Authentication

1-35

iv

1.7

1.8

2

Creating and Maintaining a Database Password File

1-36

1.7.1

ORAPWD Syntax and Command Line Argument Descriptions

1-37

1.7.2

Creating a Database Password File with ORAPWD

1-43

1.7.3

Sharing and Disabling the Database Password File

1-44

1.7.4

Keeping Administrator Passwords Synchronized with the Data Dictionary

1-45

1.7.5

Adding Users to a Database Password File

1-46

1.7.6

Granting and Revoking Administrative Privileges

1-47

1.7.7

Viewing Database Password File Members

1-48

1.7.8

Removing a Database Password File

1-48

Data Utilities

1-49

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.3

2.4

2.5

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

Creating a Database with DBCA

2-7

2.3.1

About Creating a Database with DBCA

2-7

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

Creating a Database with the CREATE DATABASE Statement

2-8

2.4.1

About Creating a Database with the CREATE DATABASE Statement

2-9

2.4.2

Step 1: Specify an Instance Identifier (SID)

2-10

2.4.3

Step 2: Ensure That the Required Environment Variables Are Set

2-11

2.4.4

Step 3: Choose a Database Administrator Authentication Method

2-12

2.4.5

Step 4: Create the Initialization Parameter File

2-12

2.4.6

Step 5: (Windows Only) Create an Instance

2-13

2.4.7

Step 6: Connect to the Instance

2-14

2.4.8

Step 7: Create a Server Parameter File

2-15

2.4.9

Step 8: Start the Instance

2-16

2.4.10

Step 9: Issue the CREATE DATABASE Statement

2-16

2.4.11

Step 10: Create Additional Tablespaces

2-20

2.4.12

Step 11: Run Scripts to Build Data Dictionary Views

2-20

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

Specifying CREATE DATABASE Statement Clauses

2.5.1

About CREATE DATABASE Statement Clauses

2-23

2-24

v

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download