Pro Oracle Database 23ai Administration: Manage and Safeguard Your Organization’s Data

Table of Contents
About the Authors
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Installing the Oracle Binaries
	Understanding the OFA
		Oracle Inventory Directory
		Oracle Base Directory
		Oracle Home and Grid Directories
		Oracle Network Files Directory
		Fast Recovery Area Directory
	Installing Oracle
		Step 1: Create the OS Groups and Users
		Step 2: Ensure That the OS Is Adequately Configured
		Step 3: Obtain the Oracle Installation Software
		Step 4: Unzip the Files
			Installing Remotely with the Graphical Installer
		Step 5: Run the Installer for Grid
		Step 6: Run the Installer for Database
		Step 7: Save the Response File for Additional Installations
		Step 8: Troubleshoot Any Issues
		Step 9: Apply Any Additional Patches
	Installing in the Cloud
Chapter 2: Creating a Database
	Using the Database Configuration Assistant
		Setting OS Variables
		Understanding oratab
		Using oraenv
			Taking Another Approach to oraenv
		Creating a Database
		Creating a Database Using a SQL Statement
	Database vs. Instance
	Stopping and Starting the Database
		Understanding OS Authentication
		Starting the Database
		Stopping the Database
	Configuring the LISTENER
		Using the Net Configuration Assistant
		Connecting to a Database Through the Network
		Creating a Password File
	How Many Database Instances on One Server?
		Understanding Oracle Architecture
	Dropping a Database
		Exercising Caution
	Creating the Database in the Cloud
		Autonomous Shared/Serverless
		Autonomous Dedicated
	Summary
Chapter 3: Configuring an Efficient Environment
	Customizing Your OS Command Prompt
	Customizing Your SQL Prompt
	Creating Shortcuts for Frequently Used Commands
		Using Aliases
		Locating the Alert Log
		Using a Function
	Rerunning Commands Quickly
		Scrolling with the Up and Down Arrow Keys
		Using Ctrl+P and Ctrl+N
		Listing the Command History
		Searching in Reverse
		Setting the Command Editor
	Developing Standard Scripts
		dba_setup
		conn.bsh
		filesp.bsh
		top.sql
		lock.sql
		users.sql
	Organizing Scripts
		Step 1: Create Directories
		Step 2: Copy Files to Directories
		Step 3: Configure the Startup File
	Automating Scripts
	Leveraging Tools
Chapter 4: Tablespaces and Data Files
	Understanding the First Five
	Need for More Tablespaces
	Container Tablespaces
	Creating Tablespaces
		Creating a Bigfile Tablespace
		Shrink Bigfile Tablespace
		Renaming a Tablespace
		Changing the Write Mode
		Dropping a Tablespace
	Using Oracle Managed Files
	Displaying the Tablespace Size
		Displaying Oracle Error Messages and Actions
	Altering Tablespace Size
	Additional Data File Operations
		Move SYSTEM and UNDO
	Using ASM for Tablespaces
Chapter 5: Managing Control Files, Online Redo Logs, and Archivelogs
	Managing Control Files
		Displaying the Contents of a Control File
		Viewing Names and Locations of Control Files
		Adding a Control File
		Moving a Control File
		Removing a Control File
	Online Redo Logs
		Displaying Online Redo Log Information
		Determining the Optimal Size of Redo Logs
		Determining the Optimal Number of Redo Log Groups
		Adding Online Redo Log Groups
		Resizing and Dropping Online Redo Log Groups
	Controlling the Generation of Redo
	Implementing Archivelog Mode
		Marking Architectural Decisions
		Setting the Archive Redo File Locations
		Using the FRA for Archive Log Files
		Enabling Archivelog Mode
		Disabling Archivelog Mode
		Reacting to Lack of Disk Space for Archive Logs
		Backing up Archive Redo Log Files
	Summary
Chapter 6: Users and Basic Security
	Types of Users
	Managing Default Users
		Read-Only Users
		Default Accounts as Schema-Only
		SYS vs. SYSTEM
			Passwords
	Creating Users
		Choosing a Username and Authentication Method
			Creating a User with Database Authentication
			Creating a User with OS Authentication
			Configuring a Centrally Managed User
			Common and Local Users
		Understanding Schemas vs. Users
			Schema-Only Accounts
		Assigning Default Permanent and Temporary Tablespaces
		Modifying Users
			Dropping Users
		Profiles
		Limiting Database Resource Usage
	Managing Privileges
		Assigning Database System Privileges
		Assigning Database Object Privileges
		Grouping and Assigning Privileges
		Schema Privileges
			PL/SQL and Roles
Chapter 7: Tables and Constraints
	Understanding Table Types
	Understanding Data Types
		Character
			VARCHAR
			CHAR
			NVARCHAR2 and NCHAR
			Numeric
			Vector
			JSON
			Date/Time
			INTERVAL
			RAW
			ROWID
			LOB
	Creating a Table
		Creating a Heap-Organized Table
			Table Recommendations
		Implementing Virtual Columns
		Implementing Invisible Columns
		Creating Blockchain Tables
		Making Read-Only Tables
		Using an Identity Column
		Default Parallel SQL Execution
		Compressing Table Data
		Avoiding Redo Creation
		Creating a Table from a Query
		Enabling DDL Logging
	Modifying a Table
		Obtaining the Needed Lock
		Renaming a Table
		Adding a Column
		Altering a Column
		Renaming a Column
		Dropping a Column
		Displaying Table DDL
		Dropping a Table
		Undropping a Table
		Removing Data from a Table
		Moving a Table
			Oracle ROWID
		Creating a Global Temporary Table
			Temporary Table Redo
		Private Temporary Tables
		Creating an Index-Organized Table
		Managing Constraints
			Creating Primary Key Constraints
			Enforcing Unique Key Values
			Creating Foreign Key Constraints
			Checking for Specific Data Conditions
			Enforcing NOT NULL Conditions
			Disabling Constraints
			Enabling Constraints
Chapter 8: Indexes
	Vector Indexes
	Deciding When to Create an Index
		Proactively Creating Indexes
		Reactively Creating Indexes
	Planning for Robustness
		Determining Which Type of Index to Use
		Estimating the Size of an Index Before Creation
		Creating Indexes and Temporary Tablespace Space
		Creating Separate Tablespaces for Indexes
		Establishing Naming Standards
	Creating Indexes
		Creating B-tree Indexes
		Viewing Index Metadata
		Creating Concatenated Indexes
		Creating Multiple Indexes on the Same Set of Columns
		Implementing Function-Based Indexes
		Creating Unique Indexes
		Implementing Bitmap Indexes
		Creating Bitmap Join Indexes
		Implementing Reverse-Key Indexes
		Creating Key-Compressed Indexes
		Parallelizing Index Creation
		Avoiding Redo Generation When Creating an Index
		Implementing Invisible Indexes
		Making an Existing Index Invisible
		Guaranteeing Application Behavior Is Unchanged When You Add an Index
	Maintaining Indexes
		Renaming an Index
		Displaying Code to Re-create an Index
		Rebuilding an Index
		Making Indexes Unusable
		Dropping an Index
		Indexing Foreign Key Columns
		Implementing an Index on a Foreign Key Column
		Determining Whether Foreign Key Columns Are Indexed
			Table Locks and Foreign Keys
Chapter 9: Views, Duality Views, and Materialized Views
	Implementing Views
		Creating a View
		Updatable Join Views
		Modifying and Dropping a View
	JSON Relational Duality Views
	Materialized Views
		MV Terminology
		Creating Basic Materialized Views
		Going Beyond the Basics
			Creating an Unpopulated MV
			Creating an MV Refreshed on Commit
			Creating a Never Refreshable MV
			Creating MVs for Query Rewrite
			Creating a Fast Refreshable MV Based on a Complex Query
			Real-Time Materialized Views
	Oracle Views
Chapter 10: Data Dictionary Fundamentals
	Data Dictionary Architecture
		Static Views
		Dynamic Performance Views
	A Different View of Metadata
	A Few Creative Uses of the Data Dictionary
		Derivable Documentation
		Displaying User Information
		Determining Your Environment’s Details
		Displaying Table Row Counts
		Showing Primary Key and Foreign Key Relationships
		Displaying Object Dependencies
	The Dual Table
	Annotations
Chapter 11: Large Objects
	Describing LOB Types
		Illustrating LOBs, Locators, Indexes, and Chunks
		SecureFiles
	Creating a Table with a LOB Column
		Implementing a Partitioned LOB
	Maintaining LOB Columns
		Moving a LOB Column
		Adding a LOB Column
		Removing a LOB Column
		Caching LOBs
		Storing LOBs In and Out of a Row
		Viewing LOB Metadata
	Loading LOBs
		Loading a CLOB
		Reading BFILEs
Chapter 12: Containers and Pluggables
	Paradigm Shift
	Administrating the Root Container (CDB)
		Connecting to the Root Container
		Network Connections
		Displaying Currently Connected Container Information
		Starting/Stopping the Root Container
			Create Common Users
		Creating Common Roles
		Creating Local Users and Roles
		Switching Containers
	PDB Open Modes
		Open Order for PDBs
		Starting/Stopping a PDB
	Creating a Pluggable Database Within a CDB
		Cloning the Seed Database
		Cloning an Existing PDB
			Local
			Remote
		Refreshable Clone
		Cloning from a Non-CDB Database
			Unplugging a PDB from a CDB
		Plugging an Unplugged PDB into a CDB
		Relocating a PDB
		Checking the Status of Pluggable Databases
	Administrating Pluggable Databases
		Connecting to a PDB
		Managing a Listener in a PDB Environment
		Modifying Initialization Parameters Specific to a PDB
			Renaming a PDB
		Limiting the Amount of Space Consumed by PDB
		Restricting Changes to SYSTEM at PDB
		Viewing PDB History
			Dropping a PDB
Chapter 13: RMAN Backups and Reporting
	Understanding RMAN
		Types of Backups with RMAN
	Starting RMAN
	RMAN Architectural Decisions
		Run RMAN Remotely or Locally
		Specify the Backup User
		Use Online Backups
		Set the Archivelog Destination and File Format
		Configure Channel Format
		Set the Autobackup of the Control File
			Back up Archivelogs
		Determine the Location for the Snapshot Control File
		Use a Recovery Catalog
		Configure RMAN’s Backup Retention Policy
		Delete Backups, Based on Retention Policy
		Use Backup Sets or Image Copies
		Use Incremental Backups
		Use Block Change Tracking
		Configure Informational Output
		Put It Together in a Script
	Check for Corruption
		Using VALIDATE
		Using BACKUP ... VALIDATE
		Using RESTORE ... VALIDATE
	Using a Recovery Catalog
		Creating a Recovery Catalog
		Registering a Target Database
		Backing Up the Recovery Catalog
		Synchronizing the Recovery Catalog
			Recovery Catalog Versions
			Dropping a Recovery Catalog
	Querying for Output in the Data Dictionary
	RMAN Reporting
		Using LIST
			Using REPORT
			Using SQL
		Migration using RMAN
Chapter 14: RMAN Restore and Recovery
	Determining Whether Media Recovery Is Required
	Determining What to Restore
		How the Process Works
	Using Data Recovery Advisor
		Listing Failures
		Suggesting Corrective Action
		Repairing Failures
		Changing the Status of a Failure
	Complete Recovery
		Previewing Backups Used for Recovery
		Validating Backup Files Before Restoring
		Testing Media Recovery
	Restoring and Recovering the Entire Database
		Using the Backup Control File
		Restoring and Recovering Tablespaces
		Restoring Tablespaces While the Database Is Open
		Restoring Tablespaces While the Database Is in Mount Mode
		Restoring Read-Only Tablespaces
		Restoring Temporary Tablespaces
	Restoring and Recovering Data Files
		Restoring and Recovering Data Files While the Database Is Open
		Restoring and Recovering Data Files While the Database Is Not Open
		Restoring Data Files to Nondefault Locations
	Performing Block-Level Recovery
	Restoring a Container Database and Its Pluggable Databases
		Restoring and Recovering Root Container Data Files
		Restoring and Recovering a Pluggable Database
	Restoring Archivelog Files
		Restoring to the Default Location
		Restoring to a Nondefault Location
	Restoring a Control File
	Using an Autobackup
		Specifying a Backup Filename
	Restoring the Spfile
	Incomplete Recovery
		Determining the Type of Incomplete Recovery
		Performing Time-Based Recovery
			Performing Log Sequence–Based Recovery
			Performing SCN-Based Recovery
		Restoring to a Restore Point
		Restoring Tables to a Previous Point
	Flashback
		Flashing Back a Table
			FLASHBACK TABLE TO BEFORE DROP
		Flashing Back a Table to a Previous Point in Time
			FLASHBACK TABLE TO SCN
			FLASHBACK TABLE TO TIMESTAMP
			FLASHBACK TABLE TO RESTORE POINT
			FLASHBACK DATABASE
	Restoring and Recovering to a Different Server
Chapter 15: External Tables
	SQL*Loader vs. External Tables
	External Table Types
	Creating External Tables
		Loading CSV Files into the Database
			Create a Directory Object and Granting Access
			Create Table
			Generating SQL to Create an External Table
		Viewing External Table Metadata
		Loading a Regular Table from the External Table
		External Tables with Oracle Cloud Database
			DBMS_CLOUD Create Table
	Inline SQL from External Table
	Unloading and Loading Data Using an External Table
	Oracle Data Pump
		Data Pump Architecture
		Taking an Export
		Import a Table
		Use a Parameter File
		Export and Import an Entire Database
			Schema Level
			Table Level
			Tablespace Level
		Export Tablespace Metadata
		Specifying a Query
			Exclude Objects from Export or Import
		List Contents of Dump Files
		Monitoring Data Pump Jobs
Chapter 16: Automation and Troubleshooting
	Automating Jobs with Oracle Scheduler
		Creating and Scheduling a Job
			Viewing Job Details
			Modifying Job Logging History
		Modifying a Job
		Stopping a Job
		Disabling a Job
		Enabling a Job
		Copying a Job
		Running a Job Manually
		Deleting a Job
	Examples of Automated DBA Jobs
		Starting and Stopping the Database and Listener
		Checking for Archivelog Destination Fullness
		Enhanced Error Messages
		Checking for Locked Production Accounts
			Checking for Too Many Processes
		Verifying the Integrity of RMAN Backups
	Autonomous Database
	Database Troubleshooting
		Quickly Triaging
		Checking Database Availability
		Locating the Alert Log and Trace Files
			Inspecting the Alert Log
			Viewing the Alert Log via OS Tools
		Identifying Bottlenecks via OS Utilities
		Identifying System Bottlenecks
			Using vmstat
			Using top
		Mapping an Operating System Process to a SQL Statement
		Finding Resource-Intensive SQL Statements
		SQL Analysis Report
		SQL Plan Management
		Running Oracle Diagnostic Utilities
			Using AWR
			Using ADDM
			Using ASH
			Using Statspack
		Detecting and Resolving Locking Issues
		Resolving Open-Cursor Issues
		Troubleshooting Undo Tablespace Issues
			Determining Whether the Undo Is Correctly Sized
		Handling Temporary Tablespace Issues
			Determining Whether Temporary Tablespace Is Sized Correctly
Chapter 17: Migration to Multitenant and Fleet Management
	Migration to Multitenant
		Plug-In Method
			No-Copy or Copy Options
	Fleet Management
		Oracle Fleet Patching and Provisioning
			Types of Patching
			FPP Steps
			User Groups
			Methods of Patching
		Fleet Management with Autonomous
			Provisioning
			Policies
			Network
			Exadata Infrastructure
			Monitoring
Chapter 18: Data Management
	Models
	JSON
	Graph
	Machine Learning and AI
	Data Studio
	DBMS_CLOUD
	DBMS_CLOUD_AI
	REST and ORDS
	Data Sharing
Index