This book, “Beginning Oracle Database 12c Administration, 2nd Edition,” is a comprehensive guide to Oracle database administration. It covers fundamental database concepts, SQL and PL/SQL, Oracle architecture, and essential administrative tasks such as user management, data loading, backups, and recovery. The text also emphasizes practical work practices and problem-solving methodologies, including the importance of proper planning and licensing. Finally, it highlights the broader IT context of database administration, emphasizing communication and the role of the DBA within an organization.

Oracle Database Administration Study Guide

SQL and PL/SQL

Subqueries

A subquery is a SELECT statement that is embedded within another DML statement (SELECT, INSERT, UPDATE, or DELETE) or within another subquery. Subqueries are always enclosed in parentheses and can return a single value, a single row, or multiple rows of data.

There are three main types of subqueries:

1. Inline view: This type of subquery appears in the FROM clause of a SELECT statement. It acts like a temporary table, allowing you to select from the results of the subquery.
2. Scalar subquery: This type of subquery returns exactly one data item from one row. It can be used wherever a single value is expected, such as in a SELECT list, a WHERE clause, or a HAVING clause.
3. Correlated subquery: This type of subquery depends on the outer query for its values. It is executed repeatedly, once for each row processed by the outer query.

Types of SQL

SQL is a powerful language for managing and manipulating relational databases. It is divided into two main categories:

1. Data Manipulation Language (DML): Used to retrieve, insert, update, and delete data in a database.

• SELECT: Retrieves data from one or more tables
• INSERT: Adds new rows into a table
• UPDATE: Modifies existing data in a table
• MERGE: Combines INSERT and UPDATE operations based on a condition
• DELETE: Removes rows from a table

1. Data Definition Language (DDL): Used to define the structure of the database, including creating, altering, and dropping database objects like tables, views, indexes, and users.

• CREATE: Creates a new database object
• ALTER: Modifies the structure of an existing object
• DROP: Removes an existing object

Railroad Diagrams

Oracle uses railroad diagrams to illustrate the syntax of SQL commands. These diagrams provide a visual representation of the different clauses and options available for each command, showing both mandatory and optional elements.

Database Architecture

Data Files

Data files are the physical files that store the actual data of an Oracle database. They are organized into logical units called tablespaces.

Key points about data files:

• Each data file belongs to one tablespace.
• Data files are typically named with a descriptive name and a .dbf or .ora extension.
• Space within data files is divided into data blocks, also called pages.
• Each data block contains data from only one table.
• A contiguous range of data blocks allocated to a table is called an extent.

Server Processes

Oracle uses server processes to manage connections and execute user requests. There are two main types of server architectures:

1. Dedicated Server Architecture: A dedicated server process is created for each user connection. This process handles all requests from the connected user.
2. Multithreaded Server (MTS) Architecture: A pool of shared server processes is used to handle user connections. Dispatcher processes route user requests to available shared servers. MTS is less commonly used than the dedicated server architecture.

Software Installation

The software installation process involves setting up the operating system environment, installing the Oracle software, and configuring the listener.

Key considerations:

• Setting up appropriate user accounts and permissions
• Configuring the network listener to allow client connections
• Setting up firewalls to secure the database server

Database Creation

The Database Configuration Assistant (DBCA) is a graphical tool that simplifies the process of creating and configuring an Oracle database.

Key parameters:

• db_block_size: Specifies the size of data blocks
• db_name: Defines the name of the database
• db_recovery_file_dest: Sets the location for recovery files
• memory_target: Sets the total amount of memory allocated to the SGA and PGA
• processes: Defines the maximum number of processes that can connect to the database

Physical Database Design

Physical database design focuses on the efficient storage and retrieval of data within the database.

Partitioning

Partitioning is a technique for dividing large tables and indexes into smaller, more manageable pieces called partitions.

Types of partitioning:

• List partitioning: Divides data based on a list of discrete values.
• Range partitioning: Divides data based on ranges of values.
• Interval partitioning: Automatically creates new partitions based on specified intervals.
• Hash partitioning: Distributes data randomly across partitions using a hashing function.
• Reference partitioning: Partitions a child table based on the partitioning scheme of its parent table.
• Composite partitioning: Combines different partitioning methods to create subpartitions within a partition.

Partition Views

Partition views combine data from multiple partitioned tables to present a unified view of the data to the user. They provide transparency to the user, hiding the underlying partitioning scheme.

User Management and Data Loading

User Management

Key commands for managing user accounts:

• CREATE USER: Creates a new user account in the database.
• ALTER USER: Modifies an existing user account, such as changing passwords, assigning quotas, or setting default and temporary tablespaces.
• DROP USER: Removes a user account from the database.
• GRANT: Assigns privileges to a user, allowing them to perform specific actions in the database.
• REVOKE: Removes privileges from a user.

Data Loading

Key methods for loading data into an Oracle database:

• Data Pump: A high-speed utility for exporting and importing data. The expdp and impdp commands provide a wide range of options for controlling the data loading process.
• Export/Import: An older utility for data loading. The exp and imp commands are still available but are less efficient than Data Pump.
• SQL*Loader: A command-line utility for loading data from external files. It uses a control file to define the format of the input data and map it to the database columns.

Quiz

Instructions: Answer the following questions in 2-3 sentences each.

1. What are the three main types of subqueries, and how do they differ?
2. Explain the difference between DML and DDL and provide examples of each.
3. How do railroad diagrams help in understanding SQL syntax?
4. What are data blocks and extents in the context of data files?
5. Compare and contrast the dedicated server and multithreaded server architectures.
6. What are some key considerations during the software installation process for Oracle Database?
7. Explain the concept of database partitioning and list at least three different partitioning methods.
8. What is the purpose of a partition view?
9. Describe the steps involved in creating a new user account and granting them privileges to access database objects.
10. List and briefly explain three different methods for loading data into an Oracle database.

Answer Key

1. The three main types of subqueries are inline views, scalar subqueries, and correlated subqueries. Inline views act like temporary tables in the FROM clause, scalar subqueries return a single value, and correlated subqueries depend on the outer query for their values.
2. DML (Data Manipulation Language) is used for manipulating data within a database, while DDL (Data Definition Language) is used for defining the database structure. Examples of DML include SELECT, INSERT, UPDATE, and DELETE, while examples of DDL include CREATE, ALTER, and DROP.
3. Railroad diagrams provide a visual representation of the syntax of SQL commands, showing both mandatory and optional elements. They help to understand the order and relationships between different clauses and options.
4. Data blocks (also called pages) are the units of storage within data files, with a fixed size. Extents are contiguous ranges of data blocks allocated to a specific table.
5. A dedicated server architecture assigns a separate process to each user connection, while a multithreaded server (MTS) architecture uses a pool of shared server processes to handle multiple connections. MTS can be more efficient for handling many concurrent connections but is less commonly used than the dedicated server architecture.
6. Key considerations during Oracle Database software installation include setting up appropriate user accounts and permissions, configuring the network listener, and setting up firewalls. These steps ensure security and allow clients to connect to the database server.
7. Database partitioning involves dividing large tables and indexes into smaller pieces called partitions. This improves manageability and performance. Different partitioning methods include list partitioning (based on discrete values), range partitioning (based on value ranges), and hash partitioning (based on a hashing function).
8. A partition view combines data from multiple partitioned tables into a single logical view. This allows users to query the data transparently without needing to know about the underlying partitioning scheme.
9. To create a new user account, use the CREATE USER command, specifying a username and password. Use the GRANT command to assign privileges to the user, allowing them to perform actions like creating tables, selecting data, or modifying data.
10. Three methods for loading data into Oracle Database are Data Pump (using expdp and impdp commands), Export/Import (using exp and imp commands), and SQL*Loader (using a control file to define the data format). Data Pump is the most efficient method for large datasets.

Essay Questions

1. Discuss the advantages and disadvantages of using different partitioning methods in Oracle Database. Provide real-world scenarios where each method would be most appropriate.
2. Explain the concept of read consistency in Oracle Database. How is it achieved, and what are its benefits and limitations?
3. Describe the different types of database backups available in Oracle Database. Discuss best practices for implementing a comprehensive backup and recovery strategy.
4. Explain the importance of database monitoring and performance tuning. Describe the tools and techniques available in Oracle Database for monitoring performance and identifying bottlenecks.
5. Discuss the role of the Oracle Data Dictionary in database administration. How can the Data Dictionary be used to obtain information about database objects, users, and privileges?

Glossary of Key Terms

• Data Block: The fundamental unit of storage within an Oracle data file, with a fixed size. Also called a page.
• Extent: A contiguous range of data blocks allocated to a table or index.
• Tablespace: A logical grouping of data files. Tablespaces help to organize and manage database storage.
• Dedicated Server Process: A server process dedicated to handling requests from a single user connection.
• Multithreaded Server (MTS): A server architecture that uses a pool of shared server processes to handle multiple user connections.
• Partitioning: A technique for dividing large tables and indexes into smaller, more manageable pieces called partitions.
• Partition View: A logical view that combines data from multiple partitioned tables, providing a unified view of the data.
• Data Pump: A high-speed utility for exporting and importing data in Oracle Database.
• SQL*Loader: A command-line utility for loading data into Oracle Database from external files.
• Read Consistency: A feature of Oracle Database that ensures that all data read during a transaction is consistent with the state of the database when the transaction started.
• Data Dictionary: A collection of metadata tables and views that store information about the structure and contents of an Oracle database.
• System Global Area (SGA): A shared memory area used by all Oracle processes to store database data and control information.
• Program Global Area (PGA): A private memory area allocated to each Oracle server process for its own use.
• SQL Tuning Advisor: A tool that analyzes SQL statements and recommends changes to improve their performance.
• Automatic Workload Repository (AWR): A repository that stores historical performance data about an Oracle database.
• Statspack: An older tool that collects and reports performance statistics for Oracle databases.
• Wait Interface: A set of dynamic performance views that provide information about the wait events experienced by Oracle processes.

Briefing Document: Oracle Database 12c Administration

This document reviews key themes and insights from excerpts of “Beginning Oracle Database 12c Administration, 2nd Edition,” focusing on database architecture, administration, maintenance, and tuning.

I. Database Architecture

• Data Storage: Oracle databases utilize data files organized into tablespaces. Data within these files is structured into equal-sized data blocks, typically 8KB. An extent is a contiguous range of data blocks allocated to a table when it requires more space.
• “The space within data files is organized into data blocks (sometimes called pages) of equal size… Each block contains data from just one table… When a table needs more space, it grabs a contiguous range of data blocks called an extent” (Chapter 2).
• Server Processes: Oracle employs a dedicated server process for each user connection. This process handles tasks like permission checks, query plan generation, and data retrieval.
• “A dedicated server process is typically started whenever a user connects to the database—it performs all the work requested by the user” (Chapter 2).
• Memory Structures: The System Global Area (SGA) is a shared memory region crucial for database operations. It includes the database buffer cache for storing frequently accessed data blocks, the redo log buffer for transaction logging, and the shared pool for storing parsed SQL statements and execution plans.
• Background Processes: Essential for database functionality, background processes include:
• DBWn (Database Writer): Writes modified data blocks from the buffer cache to data files.
• LGWR (Log Writer): Writes redo log entries from the redo log buffer to redo log files.
• CKPT (Checkpoint): Synchronizes data files and control files with the database’s current state.
• SMON (System Monitor): Performs instance recovery after a system crash and coalesces free space in tablespaces.

II. Database Administration

• SQL Language: Oracle utilizes SQL for both data manipulation (DML) and data definition (DDL). Railroad diagrams, often recursive, are used to explain the syntax and structure of SQL statements. Subqueries, particularly inline views and scalar subqueries, play significant roles in complex queries.
• User Management: The CREATE USER statement creates new users, defining their authentication, default and temporary tablespaces, and initial profile. ALTER USER modifies user attributes like passwords and tablespace quotas. GRANT and REVOKE commands control access privileges on database objects.
• “The CREATE USER statement should typically specify a value for DEFAULT TABLESPACE… and TEMPORARY TABLESPACE” (Chapter 8).
• Data Loading: Oracle provides several methods for importing data:
• SQL*Loader: A powerful utility for loading data from external files.
• Data Pump Export (expdp) and Import (impdp): Introduced in Oracle 10g, these utilities offer features like parallelism, compression, and encryption for efficient data transfer.

III. Physical Database Design

• Partitioning: A technique for dividing large tables into smaller, manageable pieces. Different partitioning strategies include range, list, hash, composite, and reference partitioning. Partitioning enhances query performance, backup and recovery, and data management.
• Indexes: Data structures that speed up data retrieval. B*tree indexes are commonly used in OLTP environments, while bitmap indexes are suitable for data warehousing.
• “Most indexes are of the btree (balanced tree) type and are best suited for online transaction-processing environments”* (Chapter 17).

IV. Database Maintenance

• Backups: Regular backups are vital for data protection and recovery. RMAN (Recovery Manager) is Oracle’s recommended tool for performing backups and managing backup sets. Strategies include full, incremental, and cumulative backups.
• Recovery: Techniques for restoring a database to a consistent state after failures. Options include:
• Data Recovery Advisor (DRA): An automated tool for diagnosing and repairing database corruption.
• Flashback Technologies: Allow for quick recovery from logical errors or unintentional data modifications.
• LogMiner: Enables analysis of archived redo logs to recover specific data changes.
• Space Management: Monitoring tablespace usage and free space is crucial. Techniques like segment shrinking and coalescing free space can help optimize storage utilization.

V. Database Tuning

• Performance Monitoring: Tools like Statspack, AWR (Automatic Workload Repository), and dynamic performance views provide insights into database performance.
• Statspack: Collects performance snapshots for analysis.
• “Note that Statspack is not documented in the reference guides for Oracle Database 10g, 11g, and 12c, even though it has been upgraded for all these versions” (Chapter 16).
• AWR: A more comprehensive and automated performance monitoring framework.
• SQL Tuning: Identifying and optimizing inefficient SQL statements is crucial for improving overall database performance. Techniques include index creation and tuning, hint usage, and utilizing the SQL Tuning Advisor.
• Wait Interface: Analyzing wait events helps pinpoint performance bottlenecks. Common wait events like db file sequential read and log file sync provide clues for optimization.

VI. Key Takeaways

• Understanding Oracle’s architectural components is fundamental for effective administration.
• Proper planning for licensing, hardware sizing, and configuration is essential for a successful deployment.
• Regular maintenance tasks like backups, recovery drills, and space management ensure database health and data integrity.
• Proactive performance monitoring and SQL tuning are critical for achieving optimal database performance.
• Utilizing Oracle’s various tools and features like RMAN, Data Pump, and the SQL Tuning Advisor simplifies administrative tasks and enhances efficiency.

Oracle Database Administration FAQ

What are the different types of subqueries in Oracle SQL?

There are three main types of subqueries:

• Inline views: These are subqueries used in the FROM clause as a table reference. They act like temporary views within a larger query.
• Scalar subqueries: These subqueries return a single value and can be used wherever a single value is expected, such as in a SELECT list or WHERE clause.
• Correlated subqueries: These subqueries depend on values from the outer query and are executed repeatedly for each row of the outer query.

How is space organized within Oracle data files?

Space in data files is structured in data blocks, also known as pages. Each data file has a fixed block size (e.g., 8KB) defined at the tablespace level. A block holds data for a single table. To accommodate growth, tables claim a contiguous series of data blocks, forming an extent.

What are the main types of server processes in Oracle?

Oracle primarily uses two types of server processes:

• Dedicated server processes: A dedicated server process handles requests for a single user connection. This is the typical model.
• Shared server processes (Multithreaded Server – MTS): In this model, a pool of shared server processes handles requests from multiple users. This approach can be more efficient for environments with many concurrent but mostly idle connections.

What are the different types of partitioning available in Oracle?

Oracle offers several partitioning methods:

• Range partitioning: Data is divided into partitions based on a range of values for a specific column, typically a date or number.
• List partitioning: Partitions are created based on lists of discrete values for a specific column.
• Hash partitioning: A hashing function distributes data across partitions, aiming for even data distribution.
• Interval partitioning: This is an extension of range partitioning where new partitions are automatically created based on a defined interval.
• Reference partitioning: This method partitions a child table based on the partitioning key of a referenced parent table.
• Composite partitioning: This approach combines multiple partitioning methods, allowing for partitions to be further divided into subpartitions.

How can I export and import data in Oracle?

Oracle provides multiple utilities for data export and import:

• Data Pump (expdp and impdp): This is the preferred method in modern Oracle versions, offering features like parallelism, compression, and encryption.
• Original Export/Import (exp and imp): Although less commonly used now, these utilities are still available and offer various options for data export and import.
• SQL*Loader: This utility loads data from external files into Oracle tables, using a control file to define the data format and loading rules.

What is the purpose of the Oracle Data Dictionary?

The Data Dictionary is a collection of metadata tables and views containing information about the structure and objects within an Oracle database. It stores details about tables, indexes, users, privileges, and other database components. It is crucial for understanding the database’s structure and troubleshooting issues.

What are some tools for monitoring an Oracle database?

Several tools help monitor an Oracle database:

• Oracle Enterprise Manager: A comprehensive suite with web-based interfaces for monitoring and managing various aspects of the database.
• Statspack: A lightweight performance monitoring tool capturing snapshots of database activity for analysis.
• Automatic Workload Repository (AWR): Built into the database, AWR automatically collects performance data and generates reports.
• Dynamic Performance Views: Real-time views providing detailed information about database activity.
• Third-party tools: Tools like Toad and DBArtisan provide extensive monitoring and management features.

What are some techniques for tuning SQL queries in Oracle?

Effective SQL tuning involves a multi-faceted approach:

• Understanding the Execution Plan: Analyze the query plan to identify bottlenecks and areas for optimization.
• Using Indexes Appropriately: Create and utilize indexes effectively to speed up data retrieval.
• Rewriting Queries for Efficiency: Optimize query structure, consider using hints, and avoid unnecessary operations.
• Collecting Statistics: Ensure up-to-date statistics are available for the optimizer to make informed decisions.
• Using the SQL Tuning Advisor: Employ the advisor to identify and implement potential optimizations.
• Considering Materialized Views: Pre-calculate and store query results to improve performance for frequently used complex queries.

Oracle 12c Database Administration

Timeline of Events:

This text excerpt does not present a narrative with a sequence of events. Instead, it offers technical information and instructions related to Oracle Database 12c administration. The provided content focuses on aspects like:

• SQL fundamentals: Introduction to SQL language, different types of SQL statements (DML and DDL), and the use of railroad diagrams for understanding SQL syntax.
• Database Structure: Explanation of data files, tablespaces, data blocks, and extents within Oracle databases.
• Server Processes: Description of dedicated server processes and the multithreaded server model.
• Software Installation: Instructions for software installation including setting up iptables firewall rules.
• Database Creation: Details about setting database parameters, data files, and tablespace sizes during database creation.
• Physical Database Design: Exploration of different partitioning techniques like list, range, interval, hash, reference, and composite partitioning for efficient data organization.
• User Management and Data Loading: Guidance on user creation, granting and revoking privileges, managing tablespaces, and using utilities like exp/imp and expdp/impdp for data loading and export.
• Database Support: Introduction to data dictionary views and their importance in database administration, and brief mention of third-party tools.
• Monitoring: Overview of monitoring database activity through alert logs, checking CPU and load average, understanding listener issues, and using tools like AWR and Statspack for performance monitoring.
• Fixing Problems: Troubleshooting scenarios related to unresponsive listeners and data corruption using tools like DRA and RMAN.
• Database Maintenance: Tasks like archiving, auditing, backups, purging, rebuilding, statistics gathering, and user management as part of regular database maintenance.
• SQL Tuning: Understanding the role of indexes, interpreting query execution plans, and utilizing tools like SQL Tuning Advisor for optimizing SQL statement performance.

Therefore, it’s not feasible to create a timeline based on the provided content.

Cast of Characters:

This technical text excerpt doesn’t feature individual characters in a narrative sense. It primarily focuses on technical concepts and instructions related to Oracle Database 12c administration.

However, we can identify some key entities mentioned:

• Oracle: The company developing and providing the Oracle Database software.
• DBA (Database Administrator): The individual responsible for managing and maintaining the Oracle database.
• Users: Individuals accessing and utilizing the Oracle database. Specific users like “ifernand,” “hr,” and “clerical_role” are mentioned as examples in user management and data loading sections.

Instead of character bios, we can highlight their roles:

• Oracle: Provides the software, documentation, and support for Oracle Database.
• DBA: Performs tasks like installation, configuration, security management, performance tuning, backup and recovery, and user management.
• Users: Utilize the database for various purposes, depending on their assigned roles and privileges.

This information clarifies the roles of entities involved in Oracle database administration, even though traditional character bios are not applicable in this context.

Oracle Database Administration

The most concrete aspect of a database is the files on the storage disks connected to the database host [1]. The location of the database software is called the Oracle home [1]. The path to that location is usually stored in the environment variable ORACLE_HOME [1]. There are two types of database software: server and client software [1]. Server software is necessary to create and manage the database and is required only on the database host [1]. **Client software is necessary to utilize the database and is required on every user’s computer. The most common example is the SQL*Plus command-line tool** [1].

Well-known configuration files include init.ora, listener.ora, and tnsnames.ora [2]. Data files are logically grouped into tablespaces [2]. Each Oracle table or index is assigned to one tablespace and shares the space with other tables assigned to the same tablespace [2]. Data files can grow automatically if the database administrator wishes [2]. The space within data files is organized into equally sized blocks; all data files belonging to a tablespace use the same block size [2]. When a data table needs more space, it grabs a contiguous range of data blocks called an extent [2]. It is conventional to use the same extent size for all tables in a tablespace [2].

Oracle records important events and errors in the alert log [3]. A detailed trace file is created when a severe error occurs [3]. Oracle Database administrators need to understand SQL in all its forms [4]. All database activity, including database administration activities, is transacted in SQL [4]. Oracle reference works use railroad diagrams to teach the SQL language [5]. SQL is divided into Data Manipulation Language (DML) and Data Definition Language (DDL) [5]. DML includes the SELECT, INSERT, UPDATE, MERGE, and DELETE statements [5]. DDL includes the CREATE, ALTER, and DROP statements for the different classes of objects in an Oracle database [5]. The SQL reference manual also describes commands that can be used to perform database administration activities such as stopping and starting databases [5].

Programs written in PL/SQL can be stored in an Oracle database [6]. Using these programs has many advantages, including efficiency, control, and flexibility [6]. PL/SQL offers a full complement of structured programming mechanisms such as condition checking, loops, and subroutines [6].

When you stop thinking in terms of command-line syntax such as create database and GUI tools such as the Database Creation Assistant (dbca) and start thinking in terms such as:

• security management
• availability management
• continuity management
• change management
• incident management
• problem management
• configuration management
• release management
• and capacity management,

the business of database administration begins to make coherent sense, and you become a more effective database administrator [7]. These terms are part of the standard jargon of the IT Infrastructure Library (ITIL), a suite of best practices used by IT organizations throughout the world [7].

Every object in a database is explicitly owned by a single owner, and the owner of an object must explicitly authorize its use by anybody else. The collection of objects owned by a user is called a schema [8, 9]. The terms user, schema, schema owner, and account are used interchangeably [8].

A database is an information repository that must be competently administered using the principles laid out in the IT Infrastructure Library (ITIL), including:

• security management
• availability management
• continuity management
• change management
• incident management
• problem management
• configuration management
• release management
• and capacity management [10].

The five commands required for user management are CREATE USER, ALTER USER, DROP USER, GRANT, and REVOKE [9].

Form-based tools also simplify the task of database administration [11]. A workman is as good as his tools [11].

Enterprise Manager comes in two flavors: Database Express and Cloud Control. Both are web-based tools. Database Express is used to manage a single database, whereas Grid Control is used to manage multiple databases [12]. You can accomplish most DBA tasks—from mundane tasks such as password resets and creating indexes to complex tasks such as backup and recovery—by using Enterprise Manager instead of command-line tools such as SQL*Plus [12].

SQL Developer is primarily a tool for software developers, but database administrators will find it very useful. Common uses are examining the structure of a table and checking the execution plan for a query [13]. It can also be used to perform some typical database administration tasks such as identifying and terminating blocking sessions [13].

Remote Diagnostic Agent (RDA) is a tool provided by Oracle Support to collect information about a database and its host system. RDA organizes the information it gathers into an HTML framework for easy viewing [13]. It is a wonderful way to document all aspects of a database system [13].

Oracle stores database metadata—data about data—in tables, just as in the case of user data. This collection of tables is called the data dictionary. The information in the data dictionary tables is very cryptic and condensed for maximum efficiency during database operation. The data dictionary views are provided to make the information more comprehensible to the database administrator [14].

The alert log contains error messages and informational messages. The location of the alert log is listed in the V$DIAG_INFO view. The name of the alert log is alert_SID.log, where SID is the name of your database instance [15]. Enterprise Manager monitors the database and sends e-mail messages when problems are detected [16]. The command AUDIT ALL enables auditing for a wide variety of actions that modify the database and objects in it, such as ALTER SYSTEM, ALTER TABLESPACE, ALTER TABLE, and ALTER INDEX [16]. The AUDIT CREATE SESSION command causes all connections and disconnections to be recorded [16]. Recovery Manager (RMAN) maintains detailed history information about backups. RMAN commands such as list backup, report need backup, and report unrecoverable can be used to review backups. Enterprise Manager can also be used to review backups [16].

Database maintenance is required to keep the database in peak operating condition. Most aspects of database maintenance can be automated. Oracle performs some maintenance automatically: collecting statistics for the query optimizer to use [17].

Competency in Oracle technology is only half of the challenge of being a DBA. If you had very little knowledge of Oracle technology but knew exactly “which” needed to be done, you could always find out how to do it—there is Google, and there are online manuals aplenty [18]. Too many Oracle DBAs don’t know “which” to do, and what they have when they are through is “just a mess without a clue” [18].

Any database administration task that is done repeatedly should be codified into an SOP. Using a written SOP has many benefits, including efficiency, quality, and consistency [19].

The free Oracle Database 12c Performance Tuning Guide offers a detailed and comprehensive treatment of performance-tuning methods [20].

Perhaps the most complex problem in database administration is SQL tuning. The paucity of books devoted to SQL tuning is perhaps further evidence of the difficulty of the topic [21]. The only way to interact with Oracle, to retrieve data, to change data, and to administer the database is SQL [21]. Oracle itself uses SQL to perform all the work that it does behind the scenes. SQL performance is, therefore, the key to database performance; all database performance problems are really SQL performance problems, even if they express themselves as contention for resources [21].

Relational Databases and SQL

A relational database is a database in which the data is perceived by the user as tables, and the operators available to the user are operators that generate “new” tables from “old” ones. [1] Relational database theory was developed as an alternative to the “programmer as navigator” paradigm prevalent in pre-relational databases. [2] In these databases, records were connected using pointers. To access data, you would have to navigate to a specific record and then follow a chain of records. [2] This approach required programmers to be aware of the database’s physical structure, which made applications difficult to develop and maintain. [3]

Relational databases address these problems by using relational algebra, a collection of operations used to combine tables. [4] These operations include:

• Selection: Creating a new table by extracting a subset of rows from a table based on specific criteria. [5]
• Projection: Creating a new table by extracting a subset of columns from a table. [5]
• Union: Creating a new table by combining all rows from two tables. [5]
• Difference: Creating a new table by extracting rows from one table that do not exist in another table. [6]
• Join: Creating a new table by concatenating records from two tables. [6]

One of the significant advantages of relational databases is that they allow users to interact with the data without needing to know the database’s physical structure. [3] The database management system is responsible for determining the most efficient way to execute queries. [7] This separation between the logical and physical aspects of the database is known as physical data independence. [8]

SQL (Structured Query Language) is the standard language used to interact with relational databases. [9] SQL allows users to perform various operations, including:

• Retrieving data.
• Inserting, updating, and deleting data.
• Managing database objects such as tables and indexes.

Despite its widespread adoption, SQL has been criticized for some of its features, including the allowance of duplicate rows and the use of nullable data items. [10, 11] However, SQL remains the most widely used language for interacting with relational databases, and it is an essential skill for database administrators. [11]

SQL and PL/SQL in Oracle Databases

SQL (Structured Query Language) is the primary language used to interact with Oracle databases, encompassing all database activities, including administration. [1] Database administrators need to be well-versed in SQL due to its extensive capabilities and functionalities. [1] The significance of SQL is evident in the sheer volume of the Oracle Database 12c SQL Language Reference, which spans nearly 2,000 pages. [1]

SQL offers a powerful set of features, including:

• Data Manipulation Language (DML): This subset of SQL focuses on modifying data within the database. DML statements include SELECT, INSERT, UPDATE, MERGE, and DELETE. [2, 3]
• Data Definition Language (DDL): DDL statements handle the creation, modification, and removal of database objects, such as tables and indexes. Common DDL statements include CREATE, ALTER, and DROP. [2, 4]

Oracle’s reference manuals utilize railroad diagrams to illustrate the syntax and numerous optional clauses of SQL statements. [5] These diagrams provide a visual representation of the structure and flow of SQL commands. [5] A notable aspect of railroad diagrams is their ability to incorporate subdiagrams and even reference themselves recursively, adding to the complexity and power of SQL. [6]

PL/SQL (Procedural Language/SQL) extends the capabilities of SQL by providing procedural programming constructs within the Oracle database. [7] PL/SQL empowers developers to create sophisticated programs that interact with the database, leveraging features such as:

• Condition checking: Implementing decision-making logic within PL/SQL programs. [7]
• Loops: Enabling repetitive execution of code blocks for efficient processing. [7]
• Subroutines: Encapsulating reusable code segments for modularity and code organization. [7]

One of the prominent applications of PL/SQL is the creation of triggers, which automatically execute predefined actions in response to specific database events. [7] For instance, the HR schema employs a trigger to log historical job changes whenever the job_id in the employees table is modified. [8] Triggers enhance data integrity, security, and auditing capabilities within the database. [9]

Storing PL/SQL programs within the database offers several advantages, such as:

• Enhanced efficiency: Reduced communication overhead between client and server, resulting in improved performance. [9]
• Improved control: Streamlined enforcement of business rules through triggers. [9]
• Increased flexibility: Empowering SQL statements with the added power and versatility of PL/SQL functions. [9]

The combined capabilities of SQL and PL/SQL make them essential tools for Oracle database administrators, enabling them to manage data, enforce rules, and optimize database operations effectively.

Database Backup and Recovery Strategies

Database backups are crucial for protecting against data loss due to user error, operator error, or hardware failure. Backups are essentially snapshots of a database or a portion of a database taken at a specific point in time. If a database is damaged, these backups can be used to restore it to a functional state. Additionally, archived logs, which contain records of all transactions performed on the database, can be used in conjunction with backups to replay modifications made after the backup was created, ensuring a complete recovery. [1]

Determining the appropriate backup strategy requires careful consideration of various factors, including the business needs, cost-effectiveness, and available resources. Several key decisions need to be made: [2]

• Storage Medium: Backups can be stored on tape or disk. Tapes offer advantages in terms of cost and reliability, while disks provide faster access and ease of management. A common approach is to create backups on disks initially and then copy them to tapes for long-term storage. [2-4]
• Backup Scope: Full backups capture the entire database, while partial backups focus on specific portions, such as changed data blocks or read-only tablespaces. [5]
• Backup Level: Level 0 backups are full backups, while level 1 backups, also known as incremental backups, only include data blocks that have changed since the last level 0 backup. This approach balances backup frequency with resource consumption. [6]
• Backup Type: Physical backups create exact copies of data blocks and files, while logical backups represent a structured copy of table data. Logical backups are generally smaller but cannot be used to restore the entire database. [7]
• Backup Consistency: Consistent backups guarantee a point-in-time representation of the database, while inconsistent backups may contain inconsistencies due to ongoing modifications during the backup process. The use of redo logs can address inconsistencies in physical backups. [8]
• Backup Mode: Hot backups, or online backups, allow database access and modifications during the backup operation, while cold backups, or offline backups, require the database to be unavailable. [9]
• Backup Management: Oracle-managed backups utilize Recovery Manager (RMAN), which offers numerous advantages such as ease of use, history data storage, and advanced features like incremental backups and corruption detection. User-managed backups employ alternative methods, such as snapshot technology, which can be integrated with RMAN for enhanced capabilities. [10-12]

Recovery, the process of repairing a damaged database, often follows a restore operation, which involves replacing damaged or missing files from backup copies. Different types of recovery cater to specific situations: [13, 14]

• Full Recovery: Restoring the entire database to a functional state. [14]
• Partial Recovery: Repairing only the affected parts of the database without impacting the availability of other parts. [14]
• Complete Recovery: Recovering all transactions up to the latest point in time. [15]
• Incomplete Recovery: Intentionally stopping the recovery process at a specific point in time, often used to reverse user errors. [15]
• Traditional Recovery: Using archived redo logs to replay transactions. [16]
• Flashback Recovery: Utilizing flashback logs to quickly unwind transactions, offering faster recovery times than traditional methods. [16]

Data Recovery Advisor (DRA) simplifies the database repair process by automating tasks and providing recommendations. By analyzing failures and generating RMAN scripts, DRA streamlines the recovery process for DBAs. [17]

Testing recovery procedures is crucial for ensuring their effectiveness and validating backup usability. RMAN offers the DUPLICATE DATABASE command, allowing DBAs to create a copy of the database for testing purposes without affecting the live environment. [18]

Documenting recovery procedures in standard operating procedures (SOPs) is vital for consistent and efficient execution, especially in stressful situations. SOPs should outline the steps involved in backups, recovery, and other critical database management tasks. [18, 19]

Database Performance Tuning: A Five-Step Approach

Database performance tuning is a critical aspect of database administration, aimed at optimizing the database’s efficiency and responsiveness in handling workloads. Tuning involves a systematic approach to identify performance bottlenecks, analyze their root causes, and implement solutions to improve overall performance.

One of the primary focuses of database tuning is on DB time, which represents the total time the database spends actively working on user requests. Analyzing DB time allows administrators to pinpoint areas where the database is spending excessive time and identify potential bottlenecks. The Statspack and AWR reports provide comprehensive insights into DB time distribution across various database operations, helping to isolate performance issues. [1, 2]

A widely recognized method for database tuning is the five-step approach, encompassing: [1, 3]

1. Define the problem: This crucial initial step involves gathering detailed information about the perceived performance issue, including specific symptoms, affected users, and any recent changes in the environment that might have contributed to the problem. Accurately defining the problem sets the foundation for effective investigation and analysis.
2. Investigate the problem: Once the problem is clearly defined, a thorough investigation is conducted to gather relevant evidence, such as Statspack reports, workload graphs, and session traces. This step aims to delve deeper into the problem’s nature and collect data for analysis.
3. Analyze the collected data: The evidence collected during the investigation is scrutinized to identify patterns, trends, and potential root causes of the performance issue. For example, examining the “Top 5 Timed Events” section of a Statspack report can reveal specific database operations consuming significant DB time. [4]
4. Solve the problem: Based on the analysis, solutions are formulated to address the identified performance bottlenecks. This step may involve adjusting database configuration parameters, implementing indexing strategies, optimizing SQL queries, or considering hardware upgrades.
5. Implement and validate the solution: The proposed solutions are implemented in the database environment, and their impact on performance is carefully monitored and validated. This step ensures the effectiveness of the implemented changes and verifies the desired performance improvements.

Tools like Statspack and AWR play a crucial role in database performance tuning, providing rich data for analysis and insights into database behavior. These tools offer comprehensive reports, customizable queries, and historical data collection, enabling DBAs to track performance trends over time and identify areas for improvement. [1] SQL Developer, another essential tool, enables DBAs to examine table structures, check the execution plan for queries, and even pinpoint blocking sessions that may be hindering performance. [5, 6]

Database tuning often involves addressing various factors contributing to performance issues. Some common areas of focus include:

• I/O Performance: Optimizing disk I/O operations can significantly impact database performance. Techniques may involve using faster disks, configuring RAID arrays for optimal performance, or tuning the database buffer cache to minimize disk reads. [7]
• Memory Management: Efficient memory allocation and utilization are essential for database performance. Tuning may involve adjusting the sizes of the shared pool, buffer cache, and other memory structures to optimize resource allocation. [8, 9]
• SQL Tuning: SQL queries that consume excessive resources can significantly degrade performance. SQL tuning involves analyzing query execution plans, identifying inefficient operations, and optimizing queries through techniques like indexing, rewriting queries, or using hints to influence the optimizer’s choices. [10-12]
• Contention: When multiple processes compete for the same resources, such as locks or latches, performance can suffer. Identifying and resolving contention issues may involve optimizing application logic, tuning concurrency settings, or implementing appropriate locking strategies.
• Workload Management: Analyzing and managing the database workload can help distribute resources effectively and prioritize critical operations. Techniques may include scheduling resource-intensive tasks during off-peak hours, implementing resource limits, or using features like Oracle Resource Manager to control resource allocation.

Monitoring database performance is an ongoing process, crucial for detecting and addressing performance degradation proactively. Tools like Enterprise Manager provide dashboards and alerts, enabling DBAs to stay informed about database health and performance metrics. By regularly reviewing performance data and identifying trends, DBAs can anticipate potential issues and take corrective actions before they impact users. [13-15]

Effective database performance tuning requires a deep understanding of database concepts, available tools, and a systematic approach to problem-solving. By leveraging these elements, DBAs can ensure that databases operate optimally, meeting the performance demands of their users and supporting business objectives.

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!