Oracle 19c Define and Create Application PDBs

In Oracle 19c, the ability to define and create application PDBs (Pluggable Databases) is a fundamental skill for database administrators. By mastering the process to Create PDBs and Define PDBs, administrators can efficiently manage and scale their database environments. This guide will explore the steps and best practices for creating and defining application PDBs in Oracle 19c.

Create Application PDBs

Creating Application PDBs involves setting up new pluggable databases that can host applications within a multitenant container database (CDB). This process allows for the segregation and efficient management of different applications.

Key Features of Creating PDBs

Isolation: Each PDB operates independently, providing isolation for different applications.

Scalability: Easily add or remove PDBs to scale the database environment.

Resource Management: Allocate resources to PDBs as needed, optimizing performance.

📢 You might also like: Oracle 19c Install, Upgrade and Patch Applications (Category: Oracle Database Admin)

Steps to Create Application PDBs

To create an Application PDB in Oracle 19c, follow these steps:

1. Connect to the CDB:

CONNECT sys@cdb1 AS SYSDBA;

2. Create the PDB:

CREATE PLUGGABLE DATABASE app_pdb ADMIN USER admin IDENTIFIED BY password FILE_NAME_CONVERT = ('/pdbseed/', '/app_pdb/');

This command creates an Application PDB named app_pdb, which will host the application.

3. Open the PDB:

ALTER PLUGGABLE DATABASE app_pdb OPEN;

This command opens the newly created Application PDB for use.

Monitoring Application PDBs

Monitor Application PDBs using Oracle Enterprise Manager or query dynamic performance views such as CDB_PDBS and V$PDBS. These tools provide detailed insights into the status and performance of each PDB.

Example query to monitor PDBs:

SELECT pdb_name, status FROM CDB_PDBS;

This query provides information about the status of all PDBs within the CDB.

Create PDBs – Define Application PDBs

Defining Application PDBs involves configuring the PDBs with the necessary metadata and settings to support applications. This step ensures that the PDBs are properly set up to meet the requirements of the applications they will host.

Key Features of Defining PDBs

Customization: Tailor each PDB to the specific needs of the application it will host.

Consistency: Ensure that all PDBs follow the same configuration standards.

Optimization: Configure settings to optimize performance and resource utilization.

Steps to Define Application PDBs

To define an Application PDB in Oracle 19c, follow these steps:

1. Connect to the PDB:

CONNECT admin@app_pdb;

2. Configure PDB Settings:

ALTER SESSION SET CONTAINER = app_pdb;
ALTER SYSTEM SET SGA_TARGET = 500M;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 200M;

These commands configure the memory settings for the PDB to ensure optimal performance.

3. Create Application Users and Schemas:

CREATE USER app_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO app_user;

This command creates a user for the application and grants necessary privileges.

Create PDBs – Monitoring Defined PDBs

Monitor the defined PDBs using Oracle Enterprise Manager and dynamic performance views such as V$PDBS and DBA_PDBS. These views provide comprehensive data on the configuration and performance of each PDB.

Example query to monitor defined PDBs:

SELECT pdb_name, con_id, open_mode FROM DBA_PDBS WHERE name = 'APP_PDB';

This query provides information about the configuration and status of the defined PDBs.

Benefits of Creating and Defining Application PDBs

Understanding how to Create PDBs and Define PDBs in Oracle 19c provides several benefits:

• Improved Management: Simplifies the management of multiple applications within a single CDB.
• Scalability: Easily scale the database environment by adding or removing PDBs.
• Optimization: Configure PDBs to optimize performance and resource utilization.

Monitoring both the creation and definition processes of Application PDBs involves examining the configurations and statuses to ensure that each PDB operates efficiently and meets the application’s requirements.

Best Practices

To effectively create and define Application PDBs, consider these best practices:

• Regular Monitoring: Continuously monitor PDBs to detect and resolve performance issues early. Use Oracle Enterprise Manager and dynamic performance views to gain insights into the status and performance of PDBs.
• Use Oracle Tools: Utilize Oracle Enterprise Manager and dynamic performance views for comprehensive monitoring. These tools provide real-time data and historical analysis, enabling DBAs to track performance trends and make informed decisions.
• Educate Team: Train the database team in creating and defining Application PDBs. Ensure that team members are familiar with the tools and techniques used to manage PDBs.
• Keep Statistics Updated: Regularly gather and update statistics to help the optimizer make accurate decisions. Accurate statistics are crucial for maintaining the efficiency and consistency of the database environment.

Additional Tips

• Implement Automation: Use automation tools to streamline the creation and definition processes. Automated tools can alert DBAs to performance issues and suggest optimal adjustments.
• Conduct Regular Audits: Periodically audit PDBs to identify long-term trends and areas for improvement.
• Engage in Continuous Learning: Stay updated with the latest Oracle updates and best practices. Continuous learning ensures that the team is equipped with the latest knowledge and techniques.

Conclusion

Oracle 19c’s capability to create and define Application PDBs is crucial for maintaining optimal database performance. By leveraging these tools and strategies, database administrators can enhance SQL performance, ensure efficient resource utilization, and proactively address performance issues. Following best practices in creating and defining PDBs will help maintain a robust and efficient Oracle 19c environment.

Regular monitoring, proactive management, and continuous learning are essential components of effective database administration. By adopting these practices, organizations can ensure that their Oracle 19c databases operate at peak efficiency, delivering reliable performance even under varying workloads.

See more on Oracle’s website!

Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!