Contents
Chapter 1: Installing the Oracle Binaries
Oracle Network Files Directory
Automatic Diagnostic Repository
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 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
Reinstalling After Failed Installation
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 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
Chapter 2: Implementing a Database
Oracle’s Approach to Setting OS Variables
My Approach to Setting OS Variables
Step 2: Configure the Initialization File
Step 3: Create the Required Directories
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
Starting and Stopping the Database
Understanding OS Authentication
Using the dbca to Create a Database
How Many Databases on One Server?
Understanding Oracle Architecture
Chapter 3: Configuring an Efficient Environment
Customizing Your OS Command Prompt
Creating Shortcuts for Frequently Used Commands
Scrolling with the Up and Down Arrow Keys
Step 2: Copy Files to Directories
Step 3: Configure the Startup File
Chapter 4: Tablespaces and Data Files
Understanding the Need for More
Controlling the Generation of Redo
Changing a Tablespace’s Write Mode
Enabling Default Table Compression within a Tablespace
Toggling Data Files Offline and Online
Renaming or Relocating a Data File
Performing Online Data File Operations
Performing Offline Data File Operations
Chapter 5: Managing Control Files, Online Redo Logs, and Archiving
Viewing Control File Names and Locations
Displaying Online Redo Log Information
Determining the Optimal Size of Online Redo Log Groups
Determining the Optimal Number of 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
Making Architectural Decisions
Setting the Archive Redo File Location
Thinking "Un-Oraclethodox" FRA Thoughts
Reacting to a Lack of Disk Space in Your Archive Log Destination
Backing Up Archive Redo Log Files
Chapter 6: Users and Basic Security
Locking Accounts and Expiring Passwords
Identifying DBA-Created Accounts
Choosing a Username and Authentication Method
Assigning Default Permanent and Temporary Tablespaces
Logging In as a Different User
Enforcing Password Security and Resource Limits
Limiting Database Resource Usage
Assigning Database System Privileges
Assigning Database Object Privileges
Grouping and Assigning Privileges
Chapter 7: Tables and Constraints
Creating a Heap-Organized Table
Implementing Invisible Columns
Understanding Deferred Segment Creation
Creating a Table with an Autoincrementing (Identity) Column
Allowing for Default Parallel SQL Execution
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
Creating an Index-Organized Table
Creating Primary Key Constraints
Creating Foreign Key Constraints
Checking for Specific Data Conditions
Deciding When to Create an Index
Determining Which Type of Index to Use
Estimating the Size of an Index Before Creation
Creating Separate Tablespaces for Indexes
Implementing Function-Based Indexes
Implementing Reverse-Key Indexes
Creating Key-Compressed Indexes
Avoiding Redo Generation When Creating an Index
Implementing Invisible Indexes
Displaying Code to Recreate an Index
Implementing an Index on a Foreign Key Column
Determining if Foreign Key Columns Are Indexed
Chapter 9: Views, Synonyms, and Sequences
Creating an INSTEAD OF Trigger
Implementing an Invisible Column
Displaying the SQL Used to Create a View
Dynamically Generating Synonyms
Implementing Multiple Sequences That Generate Unique Values
Chapter 10: Data Dictionary Fundamentals
A Few Creative Uses of the Data Dictionary
Showing Primary Key and Foreign Key Relationships
Displaying Object Dependencies
Illustrating LOB Locators, Indexes, and Chunks
Distinguishing Between BasicFiles and 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
Storing LOBs In- and Out of Line
Implementing SecureFiles Advanced Features
Migrating BasicFiles to SecureFiles
Chapter 12: Partitioning: Divide and Conquer
What Tables Should Be Partitioned?
Placing Partitions in Tablespaces
Blending Different Partitioning Methods
Partitioning to Match a Parent Table
Partitioning on a Virtual Column
Giving an Application Control over Partitioning
Automatically Moving Updated Rows
Partitioning an Existing Table
Exchanging a Partition with an Existing Table
Generating Statistics for a Partition
Removing Rows from a Partition
Manipulating Data Within a Partition
Partitioning an Index to Follow Its Table
Partitioning an Index Differently from Its Table
Exporting and Importing with Granularity
Exporting and Importing an Entire Database
Exporting and Importing Directly Across the Network
Features for Manipulating Storage
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
Exporting a Percentage of the Data
Excluding Objects from the Export File
Including Only Specific Objects in an Export File
Exporting Table, Index, Constraint, and Trigger DDL
Estimating the Size of Export Jobs
Listing the Contents of Dump Files
Importing When Objects Already Exist
Specifying Additional Dump Files
Changing Table Compression Characteristics on Import
Disabling Logging of Redo on Import
Entering Interactive Command Mode
Interactive Command Mode Status
Data Pump Mapping to the exp Utility
Data Pump Mapping to the imp Utility
SQL*Loader vs. External Tables
Loading CSV Files into the Database
Creating a Directory Object and Granting Access
Generating SQL to Create an External Table
Viewing External Table Metadata
Loading a Regular Table from the External Table
Performing Advanced Transformations
Unloading and Loading Data Using an External Table
Enabling Parallelism to Reduce Elapsed Time
Chapter 15: Materialized Views
Creating Basic Materialized Views
Creating a Complete Refreshable MV
Creating a Fast Refreshable MV
Creating MVs and Specifying Tablespace for MVs and Indexes
Building an MV on a Prebuilt Table
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
Modifying Base Table DDL and Propagating to MVs
Toggling Redo Logging on an MV
Viewing Space Used by an MV Log
Shrinking the Space in an MV Log
Checking the Row Count of an MV Log
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
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
Understanding Remote-Refresh Architectures
Viewing MV Base Table Information
Determining How Many MVs Reference a Central MV Log
Adding an MV to a Refresh Group
Removing MVs from a Refresh Group
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
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
Performing an Incomplete Recovery of an Archivelog Mode Database
Running the RMAN Client Remotely or Locally
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
Determining the Location for the Snapshot Control File
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 Incrementally Updated Backups
Configuring Binary Compression
Configuring Miscellaneous Settings
Configuring Informational Output
Segueing from Decision to Action
Chapter 18: RMAN Backups and Reporting
Preparing to Run RMAN Backup Commands
Backing Up the Entire Database
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
Taking Incremental-Level Backups
Making Incrementally Updating Backups
Checking for Corruption in Data Files and Backups
Backing Up the Recovery Catalog
Synchronizing the Recovery Catalog
Capturing Output with Linux/Unix Logging Commands
Querying for Output in the Data Dictionary
Chapter 19: RMAN Restore and Recovery
Determining if Media Recovery Is Required
Using RMAN to Stop/Start Oracle
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
Determining the Type of Incomplete Recovery
Performing Time-Based Recovery
Performing Log Sequence-Based Recovery
Restoring Tables to a Previous Point
FLASHBACK TABLE TO BEFORE DROP
Flashing Back a Table to a Previous Point in Time
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 13. Set the New Location for the Online Redo Logs
Chapter 20: Oracle Secure Backup
OSB Administrative Domain and Servers
Configuring Database Backup Storage Selector
Configuring Backup Schedules and Triggers
Performing On-Demand Filesystem Backups
Performing Catalog-Based Restore
Oracle Database Backup in the Cloud
Automating Jobs with Oracle Scheduler
Understanding cron Table Entries
Scheduling a Job to Run Automatically
Examples of Automated DBA Jobs
Starting and Stopping the Database and Listener
Checking for Archive Redo Destination Fullness
Checking for Locked Production Accounts
Checking for Files Older Than a Certain Age
Checking for Too Many Processes
Verifying the Integrity of RMAN Backups
Chapter 22: Database Troubleshooting
Checking Database Availability
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
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
Chapter 23: Pluggable Databases
Understanding Pluggable Architecture
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 a Pluggable Database within a CDB
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
Limiting the Amount of Space Consumed by a Pluggable Database