Managing Storage   « Prev  Next»

Lesson 3 Features for Tablespace and resource management
Objective Understand Oracle tablespace and resource management.

Tablespace and Resource Management in Oracle

The tablespace features give you greater flexibility in controlling the physical location and availability of specific portions of the database. Tablespace improvements allow faster and easier backups, better capabilities to change the status of tablespaces, and more choices for tablespace management.
  • Database Administrators
    Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.A database administrator's responsibilities can include the following tasks:
    1. Installing and upgrading the Oracle Database server and application tools
    2. Allocating system storage and planning future storage requirements for the database system
    3. Creating primary database storage structures (tablespaces) after application developers have designed an application
    4. Creating primary objects (tables, views, indexes) once application developers have designed an application
    5. Modifying the database structure, as necessary, from information given by application developers
    6. Enrolling users and maintaining system security
    7. Ensuring compliance with Oracle license agreements
    8. Controlling and monitoring user access to the database
    9. Monitoring and optimizing the performance of the database
    10. Planning for backup and recovery of database information
    11. Maintaining archived data on tape
    12. Backing up and restoring the database
    13. Contacting Oracle for technical support
    Look at the series of images below to see an illustration of the new tablespace enhancements now available with Oracle.

Tablespace Types
1) To begin exploring new tablespace types, let us start with an example involving two databases, A and B.
1) To begin exploring new tablespace types, let us start with an example involving two databases, A and B. Each one has two tablespaces, shown by the colored rectangles (T-1 through T-4) inside each database. Tablespace T-1 and T-2 are defined as transportable tablespaces. (Transporting tablespaces is an alternative way of moving or copying data from one database to another).

2) This image shows the same two database after the two transportable tablespaces have been transported.
2) This image shows the same two database after the two transportable tablespaces have been transported. Tablespace T-1 was transported from Database A to Database B. Tablespace T-2 was cloned (copied) and then transported from Database A to Database B. Transporting a tablespace is much faster than exporting or replicating it. Transporting moves the associated data files and then builds the tablespace meta-data in the target database. It preserves indexes intact, as long as they are stored within the tablespace being transported.

3) Here is the second example. Database A contains two tablespaces, T-1 and T-2. User A has an active transaction using tablepsace T-1.
3) Here is the second example. Database A contains two tablespaces, T-1 and T-2. User A has an active transaction using tablespace T-1. User B has an active transaction using tablespace T-2. You wish to modify tablespace T-1 to read only status so that you can perform a hot backup of the tablespace. Prior to Oracle8i you would have to wait for all outstanding transactions within the database to complete before this action could be completed.

4) With Oracle 8i, you can perform this change to READ ONLY status as soon as there are no outstanding transactions in the target tablespace.
4) With Oracle 8i, you can perform this change to READ ONLY status as soon as there are no outstanding transactions in the target tablespace. This enhancement makes it easier and faster to change a tablespace's status from updateable to read-only.

5) In this third and final example, Database A contains the same two tablespaces, T-1 and T-2.
5) In this third and final example, Database A contains the same two tablespaces, T-1 and T-2. The blue rectangle marked DD represents the database's data dictionary tables. Prior to Oracle 8i, inserting data into a table required use of the data dictionary to find available space in the tablespace, as shown in the steps listed here.

6) Oracle 8i has enhanced tablespace management to allow for locally managed tablespaces.
6) Oracle has enhanced tablespace management to allow for locally managed tablespaces. A locally managed tablespace does not use the data dictionary to track its open and used space. Instead, the tablespace itself tracks and maintains its usage of space. This means that an insert into the tablespace requires fewer steps. In addition, there is less fragmentation and wasted space in the locally managed tablespace.

Managing the SYSAUX Tablespace in Oracle 19c

Details regarding managing the "SYSAUX tablespace" are fully applicable to Oracle 19c. Here’s a breakdown to validate its use in Oracle 19c:
Key Details and Applicability to Oracle 19c
  1. SYSAUX Tablespace is Mandatory:
    • Just like earlier versions of Oracle, SYSAUX is a mandatory tablespace in Oracle 19c. It is used by several Oracle features (e.g., AWR, Data Mining, Streams, etc.), making it essential for database operation.
    • SYSAUX cannot be dropped, renamed, or transported, which remains true for Oracle 19c.
  2. Creation of SYSAUX:
    • During DBCA (Database Configuration Assistant) usage, Oracle 19c automatically creates the SYSAUX tablespace and provides options for configuration.
    • For manual database creation, the process you described is valid. The SYSAUX tablespace can be included as shown: 
      CREATE DATABASE mydb
USER SYS IDENTIFIED BY mysys
USER SYSTEM IDENTIFIED BY mysystem
SYSAUX DATAFILE 'c:\..\sysaux01.dbf' SIZE 500M;
    • The DATAFILE clause for SYSAUX is required when using a DATAFILE clause for the SYSTEM tablespace (unless Oracle Managed Files (OMF) is enabled).
  3. OMF (Oracle Managed Files):
    • If OMF is configured, the SYSAUX tablespace and its datafiles will automatically be created in the location specified by the OMF configuration (e.g., DB_CREATE_FILE_DEST or DB_RECOVERY_FILE_DEST).
    • If OMF is not configured, Oracle 19c determines the default location for the datafile.
  4. Recommended Minimum Size:
    • Oracle recommends a minimum size of 240MB for the SYSAUX tablespace. For practical purposes and depending on the database workload, allocating a larger size (e.g., 500MB or more) is advisable, as shown in your example (SIZE 500M).

Additional Notes for Oracle 19c
  • Purpose of SYSAUX: Oracle 19c relies heavily on the SYSAUX tablespace for auxiliary database components, such as:
    • Automatic Workload Repository (AWR)
    • Oracle Text
    • Database Vault
    • Enterprise Manager (if installed)
    • Spatial and Graph data storage
  • Management of SYSAUX:
    • Periodic monitoring of the SYSAUX tablespace usage is recommended. You can query the V$SYSAUX_OCCUPANTS view to determine which components are consuming space.
    • Example: 
      SELECT occupant_name, schema_name, space_usage_kbytes
FROM v$sysaux_occupants;
  • Backup and Recovery:
    • Ensure that SYSAUX is included in your database backup strategy. It is critical for restoring certain database components during recovery.
Conclusion The details you provided align perfectly with the features and requirements of Oracle 19c. You can confidently use these guidelines when managing the SYSAUX tablespace or creating a new database in Oracle 19c. If you have further questions about its management or monitoring, let me know!

Enhancements to the Resource Manager

The Database Resource Manager (DBRM) introduced in Oracle 8i continues to exist and is actively supported in Oracle 19c. In fact, its functionality has been significantly enhanced since its initial introduction. It remains a vital feature for managing and optimizing system resources in Oracle databases.
Key Features of Database Resource Manager in Oracle 19c
  1. Purpose:
    • DBRM allows you to control the allocation of resources (CPU, I/O, parallel execution servers, etc.) to database sessions and users to ensure system stability and performance.
    • It prevents resource-intensive sessions from monopolizing database resources, ensuring fair distribution.
  2. Core Capabilities in Oracle 19c:
    • CPU Resource Management:
      • Allocates CPU resources among database sessions based on resource consumer groups and plans.
    • I/O Resource Management:
      • Controls the priority of I/O operations at the session level, particularly in Exadata environments.
    • Memory Management:
      • Allocates and limits the memory used by specific workloads.
    • Parallel Execution Control:
      • Restricts the number of parallel execution servers that specific sessions or workloads can use.
    • Automatic Switching of Resource Plans:
      • Dynamically adjusts resource plans based on changing workload conditions.
  3. Enhancements in Oracle 19c

    Oracle 19c introduces several enhancements to the Database Resource Manager to better support modern database operations:

    • CDB and PDB Support:
      • DBRM can manage resource allocation between containers in a multi-tenant architecture, ensuring fair distribution across pluggable databases (PDBs).
      • Specific resource plans can be set for each PDB, and Oracle can switch between these based on predefined criteria.
    • Integration with Autonomous Database:
      • For cloud-enabled autonomous databases, DBRM helps allocate resources dynamically and in alignment with service-level agreements (SLAs).
    • Extended Support for Exadata Systems:
      • Enhanced I/O resource management capabilities for Oracle Exadata ensure that resource-intensive operations don’t impact critical workloads.
  4. Use in Oracle 19c:
    • Creating a Resource Plan:
      • DBRM still uses the same basic constructs (resource plans, consumer groups, directives).
      • Example of creating a simple resource plan: 
        BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(
    simple_plan => 'my_plan',
    consumer_group1 => 'group1',
    group1_cpu => 80,
    consumer_group2 => 'group2',
    group2_cpu => 20);
END;
/
    • Switching Plans Automatically:
      • In Oracle 19c, DBRM can automate switching between plans based on database load or time of day.
    • Monitoring and Tuning:
      • The V$RESOURCE_MANAGER_PLAN and related views allow you to monitor active resource plans and adjust them as needed.

Conclusion The Database Resource Manager is still a powerful and relevant feature in Oracle 19c, with enhanced capabilities to meet modern database demands, especially in cloud and multi-tenant environments. It remains essential for resource allocation, workload management, and ensuring predictable database performance. If you have specific use cases or require help setting it up, feel free to ask!

Database Resource Manage

In addition to these features for tablespace management, the tool called the Database Resource Manager has been enhanced to allow the database administrator to control operating system resources. For example, the DBA can limit a user so the user is not allowed to use more than 40 percent of the CPU at any time. This prevents one user from "hogging" the CPU and causing other users to experience long waits for services from the database. We will look into these features and improvements in a later module of this course.
About the Elements of Resource Manager The elements of the Resource Manager include resource consumer groups, resource plans, and resource plan directives.
Element Description
Resource consumer group A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.
Resource plan A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
Resource plan directive Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.

You use the DBMS_RESOURCE_MANAGER PL/SQL package to create and maintain these elements. The elements are stored in tables in the data dictionary. You can view information about them with data dictionary views. The next lesson describes storage-handling and space-handling efficiencies that have been added in Oracle.

SEMrush Software 3 SEMrush Banner 3