Pro Oracle Database 12c Administration by Kuhn, Darl -- Read -- Imperial Library of Trantor

Index

Cover Title Copyright Dedication Contents at a Glance Contents About the Author About the Technical Reviewers Acknowledgments Introduction Chapter 1: Installing the Oracle Binaries

Understanding the OFA

Oracle Inventory Directory Oracle Base Directory Oracle Home Directory Oracle Network Files Directory Automatic Diagnostic Repository

Installing Oracle

Step 1. Create the OS Groups and User Step 2. Ensure That the OS Is Adequately Configured Step 3. Obtain the Oracle Installation Software Step 4. Unzip the Files Step 5: Creating oraInst.loc File Step 6. Configure the Response File, and Run the Installer Step 7. Troubleshoot Any Issues

Installing with a Copy of an Existing Installation

Step 1. Copy the Binaries, Using an OS Utility Step 2. Attach the Oracle Home

Upgrading Oracle Software Reinstalling After Failed Installation Applying Interim Patches Installing Remotely with the Graphical Installer

Step 1. Install X Software and Networking Utilities on the Local PC Step 2. Start an X Session on the Local Computer Step 3. Copy the Oracle Installation Media to the Remote Server Step 4. Run the xhost Command Step 5. Log In to the Remote Computer from X Step 6. Ensure that the DISPLAY Variable Is Set Correctly on the Remote Computer Step 7. Execute the runInstaller Utility Step 8. Troubleshoot

Summary

Chapter 2: Implementing a Database

Setting OS Variables

A Manually Intensive Approach Oracle’s Approach to Setting OS Variables My Approach to Setting OS Variables

Creating a Database

Step 1. Set the OS Variables Step 2: Configure the Initialization File Step 3: Create the Required Directories Step 4: Create the Database Step 5. Create a Data Dictionary

Configuring and Implementing the Listener

Manually Configuring a Listener Implementing a Listener with the Net Configuration Assistant Connecting to a Database through the Network

Creating a Password File Starting and Stopping the Database

Understanding OS Authentication Starting the Database Stopping the Database

Using the dbca to Create a Database Dropping a Database How Many Databases on One Server? Understanding Oracle Architecture Summary

Chapter 3: Configuring an Efficient Environment

Customizing Your OS Command Prompt Customizing Your SQL Prompt Creating Shortcuts for Frequently Used Commands

Using Aliases 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 dba_fcns tbsp_chk.bsh conn.bsh filesp.bsh login.sql top.sql lock.sql users.sql

Organizing Scripts

Step 1: Create Directories Step 2: Copy Files to Directories Step 3: Configure the Startup File

Summary

Chapter 4: Tablespaces and Data Files

Understanding the First Five Understanding the Need for More Creating Tablespaces Renaming a Tablespace Controlling the Generation of Redo Changing a Tablespace’s Write Mode Dropping a Tablespace Using Oracle Managed Files Creating a Bigfile Tablespace Enabling Default Table Compression within a Tablespace Displaying Tablespace Size Altering Tablespace Size Toggling Data Files Offline and Online Renaming or Relocating a Data File

Performing Online Data File Operations Performing Offline Data File Operations

Summary

Chapter 5: Managing Control Files, Online Redo Logs, and Archiving

Managing Control Files

Viewing Control File Names and Locations Adding a Control File Moving a Control File Removing a Control File

Managing Online Redo Logs

Displaying Online Redo Log Information Determining the Optimal Size of Online Redo Log Groups Determining the Optimal Number of Redo Log Groups Adding Online Redo Log Groups Resizing and Dropping Online Redo Log Groups Adding Online Redo Log Files to a Group Removing Online Redo Log Files from a Group Moving or Renaming Redo Log Files

Implementing Archivelog Mode

Making Architectural Decisions Setting the Archive Redo File Location Thinking "Un-Oraclethodox" FRA Thoughts Enabling Archivelog Mode Disabling Archivelog Mode Reacting to a Lack of Disk Space in Your Archive Log Destination Backing Up Archive Redo Log Files

Summary

Chapter 6: Users and Basic Security

Managing Default Users

Locking Accounts and Expiring Passwords Identifying DBA-Created Accounts Checking Default Passwords

Creating Users

Choosing a Username and Authentication Method Assigning Default Permanent and Temporary Tablespaces

Modifying Passwords Logging In as a Different User Modifying Users Dropping Users Enforcing Password Security and Resource Limits

Basic Password Security Password Strength Limiting Database Resource Usage

Managing Privileges

Assigning Database System Privileges Assigning Database Object Privileges Grouping and Assigning Privileges

Summary

Chapter 7: Tables and Constraints

Understanding Table Types Understanding Data Types

Character Numeric Date/Time RAW ROWID LOB Extended Character Types

Creating a Table

Creating a Heap-Organized Table Implementing Virtual Columns Implementing Invisible Columns Making Read-Only Tables Understanding Deferred Segment Creation Creating a Table with an Autoincrementing (Identity) Column Allowing for Default Parallel SQL Execution Compressing Table Data Avoiding Redo Creation Creating a Table from a Query

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

Using DELETE Using TRUNCATE

Viewing and Adjusting the High-Water Mark You need to be aware of a couple of performance-related issues regarding the high-water mark

Tracing to Detect Space Below the High-Water Mark Using DBMS_SPACE to Detect Space Below the High-Water Mark Selecting from Data Dictionary Extents View Lowering the High-Water Mark

Creating a Temporary Table 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

Summary

Chapter 8: 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 Separate Tablespaces for Indexes Creating Portable Scripts Establishing Naming Standards

Creating Indexes

Creating B-tree Indexes Creating Concatenated Indexes 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

Maintaining Indexes

Renaming an Index Displaying Code to Recreate an Index Rebuilding an Index Making Indexes Unusable Monitoring Index Usage Dropping an Index

Indexing Foreign Key Columns

Implementing an Index on a Foreign Key Column Determining if Foreign Key Columns Are Indexed

Summary

Chapter 9: Views, Synonyms, and Sequences

Implementing Views

Creating a View Checking Updates Creating Read-Only Views Updatable Join Views Creating an INSTEAD OF Trigger Implementing an Invisible Column Modifying a View Definition Displaying the SQL Used to Create a View Renaming a View Dropping a View

Managing Synonyms

Creating a Synonym Creating Public Synonyms Dynamically Generating Synonyms Displaying Synonym Metadata Renaming a Synonym Dropping a Synonym

Managing Sequences

Creating a Sequence Using Sequence Pseudocolumns Autoincrementing Columns Implementing Multiple Sequences That Generate Unique Values Creating One Sequence or Many Viewing Sequence Metadata Renaming a Sequence Dropping a Sequence Resetting a Sequence

Summary

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 Displaying Table Row Counts Showing Primary Key and Foreign Key Relationships Displaying Object Dependencies

Summary

Chapter 11: Large Objects

Describing LOB Types Illustrating LOB Locators, Indexes, and Chunks Distinguishing Between BasicFiles and SecureFiles

BasicFiles SecureFiles

Creating a Table with a LOB Column

Creating a BasicFiles LOB Column Implementing a LOB in a Specific Tablespace Creating a SecureFiles 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 Line

Implementing SecureFiles Advanced Features

Compressing LOBs Deduplicating LOBs Encrypting LOBs Migrating BasicFiles to SecureFiles

Loading LOBs

Loading a CLOB Loading a BLOB

Measuring LOB Space Consumed

BasicFiles Space Used SecureFiles Space Used

Reading BFILEs Summary

Chapter 12: Partitioning: Divide and Conquer

What Tables Should Be Partitioned? Creating Partitioned Tables

Partitioning by Range Placing Partitions in Tablespaces Partitioning by List Partitioning by Hash Blending Different Partitioning Methods Creating Partitions on Demand Partitioning to Match a Parent Table Partitioning on a Virtual Column Giving an Application Control over Partitioning

Maintaining Partitions

Viewing Partition Metadata Moving a Partition Automatically Moving Updated Rows Partitioning an Existing Table Adding a Partition Exchanging a Partition with an Existing Table Renaming a Partition Splitting a Partition Merging Partitions Dropping a Partition Generating Statistics for a Partition Removing Rows from a Partition Manipulating Data Within a Partition

Partitioning Indexes

Partitioning an Index to Follow Its Table Partitioning an Index Differently from Its Table Partial Indexes

Partition Pruning Summary

Chapter 13: Data Pump

Data Pump Architecture Getting Started

Taking an Export Importing a Table Using a Parameter File

Exporting and Importing with Granularity

Exporting and Importing an Entire Database Schema Level Table Level Tablespace Level

Transferring Data

Exporting and Importing Directly Across the Network Copying Data Files

Features for Manipulating Storage

Exporting Tablespace Metadata Specifying Different Data File Paths and Names Importing into a Tablespace Different from the Original Changing the Size of Data Files Changing Segment and Storage Attributes

Filtering Data and Objects

Specifying a Query Exporting a Percentage of the Data Excluding Objects from the Export File Excluding Statistics Including Only Specific Objects in an Export File Exporting Table, Index, Constraint, and Trigger DDL Excluding Objects from Import Including Objects in Import

Common Data Pump Tasks

Estimating the Size of Export Jobs Listing the Contents of Dump Files Cloning a User Creating a Consistent Export Importing When Objects Already Exist Renaming a Table Remapping Data Suppressing a Log File Using Parallelism Specifying Additional Dump Files Reusing Output File Names Creating a Daily DDL File Compressing Output Changing Table Compression Characteristics on Import Encrypting Data Exporting Views As Tables Disabling Logging of Redo on Import

Interactive Command Mode

Entering Interactive Command Mode Attaching to a Running Job Stopping and Restarting a Job Terminating a Data Pump Job

Monitoring Data Pump Jobs

Data Pump Log File Data Dictionary Views Database Alert Log Status Table Interactive Command Mode Status OS Utilities

Data Pump Legacy Mode

Data Pump Mapping to the exp Utility Data Pump Mapping to the imp Utility

Summary

Chapter 14: External Tables

SQL*Loader vs. External Tables Loading CSV Files into the Database

Creating a Directory Object and Granting Access Creating an External Table Generating SQL to Create an External Table Viewing External Table Metadata Loading a Regular Table from the External Table

Performing Advanced Transformations Viewing Text Files from SQL Unloading and Loading Data Using an External Table

Enabling Parallelism to Reduce Elapsed Time Compressing a Dump File Encrypting a Dump File

Summary

Chapter 15: Materialized Views

Understanding MVs

MV Terminology Referencing Useful Views

Creating Basic Materialized Views

Creating a Complete Refreshable MV Creating a Fast Refreshable MV

Going Beyond the Basics

Creating MVs and Specifying Tablespace for MVs and Indexes Creating Indexes on MVs Partitioning MVs Compressing an MV Encrypting MV Columns Building an MV on a Prebuilt Table 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 Viewing MV DDL Dropping an MV

Modifying MVs

Modifying Base Table DDL and Propagating to MVs Toggling Redo Logging on an MV Altering Parallelism Moving an MV

Managing MV Logs

Creating an MV Log Indexing MV Log Columns Viewing Space Used by an MV Log Shrinking the Space in an MV Log Checking the Row Count of an MV Log Moving an MV Log Dropping an MV Log

Refreshing MVs

Manually Refreshing MVs from SQL*Plus Automating Refreshes, Using a Shell Script and Scheduling Utility Creating an MV with a Refresh Interval Efficiently Performing a Complete Refresh Handling the ORA-12034 Error

Monitoring MV Refreshes

Viewing MVs’ Last Refresh Times Determining Whether a Refresh Is in Progress Monitoring Real-Time Refresh Progress Checking Whether MVs Are Refreshing Within a Time Period

Creating Remote MV Refreshes

Understanding Remote-Refresh Architectures Viewing MV Base Table Information Determining How Many MVs Reference a Central MV Log

Managing MVs in Groups

Creating an MV Group Altering an MV Refresh Group Refreshing an MV Group DBMS_MVIEW vs. DBMS_REFRESH Determining MVs in a Group Adding an MV to a Refresh Group Removing MVs from a Refresh Group Dropping an MV Refresh Group

Summary

Chapter 16: User-Managed Backup and Recovery

Implementing a Cold-Backup Strategy for a Noarchivelog Mode Database

Making a Cold Backup of a Noarchivelog Mode Database Restoring a Cold Backup in Noarchivelog Mode with Online Redo Logs Restoring a Cold Backup in Noarchivelog Mode Without Online Redo Logs Scripting a Cold Backup and Restore

Making a Cold Backup of an Archivelog Mode Database Implementing a Hot Backup Strategy

Making a Hot Backup Scripting Hot Backups Understanding the Split-Block Issue Understanding the Need for Redo Generated During Backup Understanding that Data Files are Updated

Performing a Complete Recovery of an Archivelog Mode Database

Restoring and Recovering with the Database Offline Restoring and Recovering with a Database Online Restoring Control Files

Performing an Incomplete Recovery of an Archivelog Mode Database Summary

Chapter 17: Configuring RMAN

Understanding RMAN Starting RMAN RMAN Architectural Decisions

Running the RMAN Client Remotely or Locally Specifying the Backup User Using Online or Offline Backups Setting the Archive Redo Log Destination and File Format Configuring the RMAN Backup Location and File Format Setting the Autobackup of the Control File Specifying the Location of the Autobackup of the Control File Backing Up Archive Redo Logs Determining the Location for the Snapshot Control File Using a Recovery Catalog Using a Media Manager Setting the CONTROL_FILE_RECORD_KEEP_TIME Initialization Parameter Configuring RMAN’s Backup Retention Policy Configuring the Archive Redo Logs’ Deletion Policy Setting the Degree of Parallelism Using Backup Sets or Image Copies Using Incremental Backups Using Incrementally Updated Backups Using Block Change Tracking Configuring Binary Compression Configuring Encryption Configuring Miscellaneous Settings Configuring Informational Output

Segueing from Decision to Action Summary

Chapter 18: RMAN Backups and Reporting

Preparing to Run RMAN Backup Commands

Setting NLS_DATE_FORMAT Setting ECHO Setting ECHO Showing Variables

Running Backups

Backing Up the Entire Database Backing Up Tablespaces Backing Up Data Files Backing Up the Control File Backing up the spfile Backing Up Archive Redo Logs Backing Up FRA Excluding Tablespaces from Backups Backing Up Data Files Not Backed Up Skipping Read-Only Tablespaces Skipping Offline or Inaccessible Files Backing Up Large Files in Parallel Adding RMAN Backup Information to the Repository

Taking Backups of Pluggable Databases

While Connected to the Root Container While Connected to a Pluggable Database

Creating Incremental Backups

Taking Incremental-Level Backups Making Incrementally Updating Backups Using Block Change Tracking

Checking for Corruption in Data Files and Backups

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

Logging RMAN Output

Redirecting Output to a File Capturing Output with Linux/Unix Logging Commands Logging Output to a File Querying for Output in the Data Dictionary

RMAN Reporting

Using LIST Using REPORT Using SQL

Summary

Chapter 19: RMAN Restore and Recovery

Determining if Media Recovery Is Required Determining What to Restore

How the Process Works Using Data Recovery Advisor

Using RMAN to Stop/Start Oracle

Shutting Down Starting Up

Complete Recovery

Testing Restore and Recovery Restoring and Recovering the Entire Database Restoring and Recovering Tablespaces Restoring Read-Only Tablespaces Restoring Temporary Tablespaces Restoring and Recovering Data Files Restoring Data Files to Nondefault Locations Performing Block-Level Recovery Restoring a Container Database and Its Associated Pluggable Databases

Restoring Archive Redo Log Files

Restoring to the Default Location Restoring to a Nondefault Location

Restoring a Control File

Using a Recovery Catalog Using an Autobackup Specifying a Backup File Name

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

Flashing Back a Table

FLASHBACK TABLE TO BEFORE DROP Flashing Back a Table to a Previous Point in Time

Flashing Back a Database Restoring and Recovering to Different Server

Step 1. Create an RMAN Backup on the Originating Database Step 2. Copy the RMAN Backup to the Destination Server Step 3. Ensure That Oracle Is Installed Step 4. Source the Required OS Variables Step 5. Create an init.ora File for the Database to Be Restored Step 6. Create Any Required Directories for Data Files, Control Files, and Dump/Trace Files Step 7. Start Up the Database in Nomount Mode Step 8. Restore the Control File from the RMAN Backup Step 9. Start Up the Database in Mount Mode Step 10. Make the Control File Aware of the Location of the RMAN Backups Step 11. Rename and Restore the Data Files to Reflect New Directory Locations Step 12. Recover the Database Step 13. Set the New Location for the Online Redo Logs Step 14. Open the Database Step 15. Add the Temp File Step 16. Rename the Database

Summary

Chapter 20: Oracle Secure Backup

OSB Editions and Features OSB Terminology

OSB Administrative Domain and Servers OSB Interfaces OSB Users and Classes OSB Daemons

Download and Installation Command-Line Access to OSB OSB Configuration

Configuring Users and Classes Configuring Media Families Configuring Database Backup Storage Selector

Database Backup Database Restore Filesystem Backup

Creating Data Set Files Configuring Backup Windows Configuring Backup Schedules and Triggers Performing On-Demand Filesystem Backups

Filesystem Restore

Performing Catalog-Based Restore Performing a Raw Restore Performing an obtar Restore

OSB Job Monitoring

Listing Jobs Showing Job Transcripts Monitoring OSB Logs

Virtual Test Devices Oracle Database Backup in the Cloud OSB Software Upgrades Summary

Chapter 21: Automating Jobs

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

Oracle Scheduler vs. cron Automating Jobs via cron

How cron Works Enabling Access to cron Understanding cron Table Entries Scheduling a Job to Run Automatically Redirecting cron Output Troubleshooting cron

Examples of Automated DBA Jobs

Starting and Stopping the Database and Listener Checking for Archive Redo Destination Fullness Truncating Large Log Files Checking for Locked Production Accounts Checking for Files Older Than a Certain Age Checking for Too Many Processes Verifying the Integrity of RMAN Backups

Summary

Chapter 22: Database Troubleshooting

Quickly Triaging

Checking Database Availability Investigating Disk Fullness Inspecting the Alert Log

Identifying Bottlenecks via OS Utilities

Identifying System Bottlenecks Mapping an Operating System Process to an SQL Statement

Finding Resource-Intensive SQL Statements

Monitoring Real-Time SQL Execution Statistics Running Oracle Diagnostic Utilities

Detecting and Resolving Locking Issues Resolving Open-Cursor Issues Troubleshooting Undo Tablespace Issues

Determining if Undo Is Correctly Sized Viewing SQL That Is Consuming Undo Space

Handling Temporary Tablespace Issues

Determining if Temporary Tablespace Is Sized Correctly Viewing SQL That Is Consuming Temporary Space

Summary

Chapter 23: Pluggable Databases

Understanding Pluggable Architecture

Paradigm Shift B&R Implications Tuning Nuances

Creating a CDB

Creating Manually with SQL Using the DBCA Generating CDB Create Scripts via DBCA Verifying that a CDB was Created

Administrating the Root Container

Connecting to the Root Container Displaying Currently Connected Container Information Starting/Stopping the Root Container Creating Common Users Creating Common Roles Reporting on Container Space Switching Containers

Creating a Pluggable Database within a CDB

Cloning the Seed Database Cloning an Existing Pluggable Database Cloning from a Non-CDB Database Unplugging a Pluggable Database from a CDB Plugging an Unplugged Pluggable Database into a CDB Using the DBCA to Create a Pluggable Database from the Seed Database Checking the Status of Pluggable Databases

Administrating Pluggable Databases

Connecting to a Pluggable Database Managing a Listener in a Pluggable Database Environment Showing the Currently Connected Pluggable Database Starting/Stopping a Pluggable Database Modifying Initialization Parameters Specific to a Pluggable Database Renaming a Pluggable Database Limiting the Amount of Space Consumed by a Pluggable Database Viewing Pluggable Database History Dropping a Pluggable Database

Summary

Index

← Prev
Back
Next →

← Prev
Back
Next →