Database Administrator’s Guide - Oracle

1 Oracle DatabaseDatabase Administrator s Guide19cE96348-13 December 2021 Oracle Database Database Administrator s Guide , 19cE96348-13 Copyright 1996, 2021, Oracle and/or its Authors: Mark Doran, Padmaja Potineni, Rajesh BhatiyaContributors: A. Agrawal, L. Ashdown, P. Avril, D. Austin, T. Baby, H. Baer, S. Ball, S. Battula, M. Bauer, , E. Belden, J. Byun, L. Carpenter, A. Chaudhry, C. Chang, B. Cheng, H. Chien, T. Chien, , C. C. Chui, L. Clarke, D. Colello, C. Colrain, K. Cook, J. Creighton, A. Dadhich, S. Datta, , M. Dilman, S. Doraiswamy, J. Draaijer, M. Fallen, M. Fuller, D. Gagne, A. Ganesh, GP Gongloor, , V. Goorah, S. Gopalan, S. Gupta, B. Habeck, S. Hase, W. Hu, P. Huey, K. Inoue, M. Ito, C. Iyer, , P. Jaganath, S. Jain, C. Jones, S. Joshi, B. Khaladkar, F. Kobylanski, B. Krishnan, , A. Kruglikov, B. Kuchibhotla, V.

2 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, , K. Mohan, H. Mohankumar, A. Munnolimath, G. Mulagund, P. Murguia, P. Murthy, A. Mylavarapu, , N. Muthukrishnan, S. Muthulingam, L. Nim, S. Panchumarthy, R. Pang, V. Panteleenko, R. Pingte, , 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, , D. Sharma, A. Shen, B. Sinha, J. Spiller, D. Steiner, J. Stern, M. Stewart, S. Stoian, R. Swonger, , N. Sundarappa, M. Susairaj, A. Tran, A. Tsukerman, C. Tuzla, T.

3 Ueda, K. Umamageswaran,D. Utzig, E. Voss, N. Wagner, X. Wang, M. Wei, S. Wertheimer, P. Wheeler, D. Williams, A. Witkowski, , D. M. Wong, Z. Yang, T. F. Yu, W. ZhangThis 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, 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 this is software or related documentation that is delivered to the Government or anyone licensing it onbehalf of the Government, then the following notice is 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 Government endusers are "commercial computer software" or "commercial computer software documentation" pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations.

4 As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation 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 Government s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the software or hardware is developed for general use in a variety of information management is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury.

5 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 , Java, and MySQL are registered trademarks of Oracle and/or its affiliates. Other names may betrademarks of their respective 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 software or hardware and documentation may provide access to or information about content, products,and services from third parties.

6 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 not beresponsible 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 PrefaceAudiencelviiDocumentation AccessibilitylviiRelated DocumentslviiConventionslviii Changes in This Release for Oracle Database Administrator's GuideChanges in Oracle Database Release 19c, Version in Oracle Database Release 19c, Version in Oracle Database Release 18c, Version in Oracle Database 12c Release 2 ( )lxvChanges in Oracle Database 12c Release 1 ( )lxviiChanges in Oracle Database 12c Release 1 ( )lxviiiPart I Basic Database Administration1 Getting Started with Database of Oracle Database of a Database 1: Evaluate the Database Server 2.

7 Install the Oracle Database 3: Plan the 4: Create and Open the 5: Back Up the 6: Enroll System 7: Implement the Database 8: Back Up the Fully Functional 9: Tune Database 10: Download and Install Release Updates and Release 11: Roll Out to Additional Commands and SQL to the SQL* to the Database with SQL* Connecting to the Database with SQL* 1: Open a Command 2: Set Operating System Environment 3: Start SQL* 4: Submit the SQL*Plus CONNECT Your Oracle Database Software Oracle Database Release Your Current Release Database Administrator Security and Database Administrator's Operating System User Administrative User , SYSDG, SYSKM, and DBA Administrator Authorized by Administrative Methods for Database Authentication Methods for Database Remote Connections and Secure Remote Operating System System to Use Operating System Using Operating System password File to Use password File Using password File and Maintaining a Database password Syntax and Command Line Argument a Database password File with and Disabling the Database password Administrator Passwords Synchronized with the Data Users to a Database password and Revoking Administrative Database password File a Database password Utilities1-492 Creating and Configuring an Oracle Creating an Oracle Before Creating the for Database Selecting a Character Configuring an Oracle Home in Read-Only for Database a Database with Creating a

8 Database with Creating a Database with Interactive Creating a Database with Noninteractive/Silent a Database with the CREATE Database Creating a Database with the CREATE Database 1: Specify an Instance Identifier (SID) 2: Ensure That the Required Environment Variables Are 3: Choose a Database Administrator Authentication 4: Create the Initialization Parameter 5: (Windows Only) Create an 6: Connect to the 7: Create a Server Parameter 8: Start the Instance 9: Issue the CREATE Database 10: Create Additional 11: Run Scripts to Build Data Dictionary Views 12: (Optional) Run Scripts to Install Additional 13: Back Up the 14: (Optional) Enable Automatic Instance CREATE Database Statement CREATE Database Statement Your Database : Specifying Passwords for SYS and SYSTEM a Locally Managed SYSTEM Data File Attributes for the SYSAUX the SYSAUX Automatic Undo Management.

9 Creating an Undo a Default Permanent a Default Temporary Oracle Managed Files at Database Bigfile Tablespaces During Database the Default Tablespace the Default Tablespace the Database Time Zone and Time Zone the Database Time the Database Time Zone the Database Time Zone FORCE LOGGING the FORCE LOGGING Considerations of FORCE LOGGING Initialization Initialization Parameters and Initialization Parameter Initialization Parameter Initialization Parameter File the Global Database Initialization Initialization a Fast Recovery Control Files Database Block Initialization Block the Maximum Number of the DDL Lock the Method of Undo Space Initialization Initialization the Database Compatibility the COMPATIBLE Initialization the License Initialization Parameters Using a Server Parameter Is a Server Parameter File?

10 To a Server Parameter Parameter File Default Names and a Server Parameter SPFILE Initialization Initialization Parameter Changing Initialization Parameter or Changing Initialization Parameter Initialization Parameter the Server Parameter Up the Server Parameter a Lost or Damaged Server Parameter for Viewing Parameter Application Workloads with Database Database Services Services and Database Features That Use Database Database Data Service Data Dictionary Standard Edition High Availability for Oracle Standard Edition High for Using Standard Edition High Availability With Standard Edition High Availability for Oracle a Standard Edition High Availability Database to Another a Node to a Standard Edition High Availability a Configured Node from a Standard Edition High and Stopping Standard Edition High Availability Standard Edition High Availability for Oracle After Creating a Data Secure External password Guard and Application System Server Support in the Oracle Database Sample a a Database with CloneDB in a Non-multitenant Cloning a Database with a Database with Cloning a Database with a Database in a Multitenant a Database with Oracle Automatic Storage Management (OracleASM)