OCI: Oracle Base Database Service Training

                                    OCI:  Oracle Base Database Service Training

• Oracle Base Database Service overview
• Create an Base Database Service
• Manage an Oracle Base Database Service
• Database lifecycle management on a virtual machine DB system
• Database metrics and management interfaces
• Demo: Create a VCN
• Demo: Create a VM DB system
• Demo: Create a custom database software image
• Demo: Changing the shape of a VM DB system
• Demo: Scale the storage for a VM DB system
• Demo: Patch a virtual machine DB system
• Demo: Changing the license type of a VM DB system
• Demo: Scale the storage for a VM DB system
• Demo: Patch a virtual machine DB system
• Demo: Changing the license type of a VM DB system
• Demo: Create PDB in an existing DB system
• Demo: Configuring automatic backups for a database on a VM DB system
• Demo: Enable Data Guard for a VM DB system

https://docs.oracle.com/en/cloud/paas/base-database/data-guard/index.html

https://docs.public.content.oci.oraclecloud.com/en-us/iaas/releasenotes/database/basedb-cross-region-dg-virtual-vault.htm

Issue while precheck on Oracle Base Database Service and observed Object storage connectivity issue on DB system in OCI

 Subject : Issue while precheck on Oracle Base Database Service and observed Object storage connectivity issue on DB system 

Summary : It is observed that Grid and Database patching precheck is failing on a DB system or two-node RAC DB system due to Object Storage connectivity issues while downloading artifacts . 

         

          Expected error : 1) Error : Failed to download patchmetadata from objectstore!

   2) DCS-10406:Failed to connect to Object Storage while downloading /latest/gicsControl.json

if it is happening on two-node RAC DB system  ,We need to verify OSS connectivity on both node

Check Each Node Individually:

1) Log in to each node of your 2-node RAC system individually.

2) On each node, run the below command to verify connectivity to Oracle Cloud Infrastructure (OCI) Object Storage.

   

cd /opt/oracle/dcs/bin 

/opt/oracle/dcs/bin/dbcli describe-component 

/opt/oracle/dcs/bin/dbcli describe-latestpatch 

  

An example of a successful output would include a list of available patches.

A failure output will display an error like: "DCS-10032:Resource patch metadata is not found.Failed to download patchmetadata from objectstore".

Diagnose and Troubleshoot Connectivity Issues

-----------------------------------------------

To check if your Oracle Database (DB) system in Oracle Cloud Infrastructure (OCI) can connect to OCI Object Storage, 

consider the following methods:

1. Network connectivity validation

Test with curl: Access your DB system via SSH and run a curl command to the Object Storage API endpoint for the region your DB system is in.

For example, 

if your DB system is in the Ashburn region, use: 

curl https://objectstorage.<region>.oraclecloud.com

eg

curl https://objectstorage.us-ashburn-1.oraclecloud.com

curl https://objectstorage.me-abudhabi-3.oraclecloud26.com

If the connection is successful, 

you'll receive a JSON object response, like: {"code":"NotAuthorizedOrNotFound","message":"Authorization failed or requested resource not found."}.

A timeout or hanging SSH session indicates a network connectivity issue.

Possible causes

----------------

Network Configuration Problems:

Incorrectly configured Virtual Cloud Network (VCN) or subnet settings, preventing access to the OCI Services Network.

Firewall rules (either within the DB system's host OS or VCN security lists) blocking Object Storage traffic.

DNS resolution issues for Object Storage endpoints.

VPNs or web proxy servers interfering with connectivity.

Recommendation

---------------

Please verify below configuration to fix connectivity issue for Object Storage:

- Ensure Service gateway points to all services.

- Route rule has service gateway and all services.

- Egress has rule to all services.

- DNS (if custom), test with internet DNS.( if two node verify from both node)

  ls -ltr /etc/resolve.conf

  cat  /etc/resolve.conf 

- Please check if dns is also working fine ( if two node verify from both node)

  eg nslookup objectstorage.me-abudhabi-3.oraclecloud26.com 

- Please check if you able to connect to objectstorage from dbsystem using IP address?( if two node verify from both node)

- Please verify ( if two node verify from both node)

  

 (a) vi /etc/hosts

(b) nslookup to objectstorage

(c) tcpdump from both nodes while you test connecting to object storage and perform nslookup

- Firewall Configuration: Ensure firewalls (both operating system and network) are not blocking outgoing connections to the Object Storage endpoints.

    systemctl status iptables

    systemctl status firewalld

Please refer below doc

=======================

Troubleshoot Network Connectivity Failures

-----------------------------------------

https://docs.oracle.com/en/cloud/paas/base-database/troubleshoot-network/index.html#articletitle

https://docs.oracle.com/en/cloud/paas/base-database/vcn-subnets/index.html#GUID-28682953-F86D-41DA-9FDF-7B53D1E5BE68

Regions and Availability Domains

-------------------------------

https://docs.oracle.com/en-us/iaas/Content/General/Concepts/regions.htm

Validate VCN&Subnet and Security list

------------------------------------

https://docs.oracle.com/en-us/iaas/dbcs/doc/vcn-and-subnets.html

https://docs.oracle.com/en-us/iaas/dbcs/doc/security-rules-db-system.html

Update the Operating System of a DB System in Oracle Cloud Infrastructure (OCI) using DBCLI Command Step By Step

 

Update the Operating System of a DB System in Oracle Cloud Infrastructure (OCI) using DBCLI Command

 

In this Article, we will discuss about how to update the Operating System of Two node DB System in Oracle Cloud Infrastructure (OCI) using DBCLI Command in Rolling Fashion (One by One)

The current configuration is Two Node DB System

EASYDB1 And EASYDB2

Note:

1)    Some OS update operations require a reboot after update is complete. Use the dbcli get-availableospatches Command to confirm it

2)    You can use the -l (--local) flag to update the server components only in the current node(One By One):

3)    Oracle recommends rebooting the DB system if any kernel update is present in OS update.

4)    Oracle does not recommend installing OS packages or dependencies that are not part of the version lock that Oracle provides

 

Prerequisites

 

• Back up the database in the DB system prior to attempting an OS update.
• Do not remove packages from a DB system. However, you might have to remove custom RPMs (packages that were installed after the system was provisioned) for the update to complete successfully.

• Oracle recommends that you test any updates thoroughly on a non-production system before updating a production system.

 

Apply OS Patch on EASYDB1(Node1)

 

Login DB System using Root User

1)     check installed update versions a

Sudo su –

Cd /opt/oracle/dcs/bin/dbcli

dbcli describe-component

2)     Check system status before patching

dbcli describe-system

3)     identify updates you want to apply to the OS.

Note that if the rebootIsRequired field is "true", you must reboot the DB system

dbcli get-availableospatches

4)     to get the output in JSON, use the following command.

dbcli get-availableospatches -j

5)    run a precheck on Node1

dbcli update-server -c os -p -l

6)    Check job details.

dbcli describe-job -i <Job_id>

If the precheck is successful and uncovers no issues that prevent a successful update operation, you can update the OS. If the precheck is not successful, address the issues identified by the precheck before trying to update the OS.

 

Note:

You can use the -l (--local) flag to update the server components only in the current node.

7) update the OS on Node1

dbcli update-server -c os -l

Note:
You can use the -l (--local) flag to update the server components only in the current node.

Note:

f the OS update requires a reboot, reboot the server after the update operation is complete.

 

Verification

dbcli describe-component

dbcli describe-system

crsctl stat res -t

Ensure:

- All resources are ONLINE

- System status is Ready

- No failed components

 

 

Apply OS Patch on EASYDB2(Node2)

 

1) Check system status before patching

dbcli describe-system

 

 pre-check and Apply OS Patch as Node2 and do verification as we have done for node1.

2) Node wise pre check

dbcli update-server -c os -p -l

 

3) Node wise Os patch apply

dbcli update-server -c os -l

 

After Node 2 Reboots, Validate

 

 Verify patch success on Node2 as below

dbcli describe-component

dbcli describe-system

crsctl stat res -t

Optional: Verify Kernel Version After Patching (Both Nodes)

 

uname -r

You should see an updated kernel version (if a new UEK patch was applied).

Please refer below for more Details

https://docs.oracle.com/en/cloud/paas/base-database/cli-reference/index.html#GUID-1B083322-AAE3-47AB-A5E6-FF19EA81D8B3

https://docs.oracle.com/en/cloud/paas/base-database/update-dbcli/index.html#GUID-2E59FE04-7716-4A66-B6F1-9AA622CDD33C

https://docs.oracle.com/en/cloud/paas/base-database/cli-reference/index.html#GUID-A6FA643C-99E2-4CCD-B376-A0D4CC508C4B

https://docs.oracle.com/en/cloud/paas/base-database/update-dbcli/#articletitle

oracle database 23ai new features for administrators

Oracle Database 23ai brings over 300 new features, focusing heavily on AI integration, simplifying developer operations, and strengthening database administration. Key administration highlights include built-in AI SQL firewalls, True Cache for application scaling, AI-driven auto-indexing, and simplified Schema-level privilege

The most impactful enhancements for Database Administrators (DBAs) are detailed below:

Security & Governance

• Oracle SQL Firewall: Built directly into the database kernel, this inspects incoming connections and SQL statements against an allowlist of approved commands, actively blocking unauthorized execution and potential SQL injection attacks.
• Schema Privileges: You can now grant access to all existing and future objects within a single schema using a single command, vastly reducing the administrative overhead of individual privilege management.
• Passwordless Authentication: Streamlines security compliance by supporting biometric logins (Face ID, fingerprints), hardware security keys (YubiKey), and OAuth-based logins.
• Immutable Backups: RMAN now supports immutable storage integration with OCI Object Storage, preventing backups from being deleted or modified during a ransomware event

 Performance & Caching

• Oracle True Cache: A diskless, read-only cache layer you can deploy at the middle tier. It automatically maintains data consistency with the primary database, improving application response times and reducing the workload on database servers.
• AI-Driven Auto-Index Optimization: The database can now learn query patterns and dynamically create or drop indexes based on workload, mitigating under-indexing (which slows queries) and over-indexing (which slows updates).

Operational Management

• Data Pump Enhancements: Troubleshooting is much faster with three new views (V$DATAPUMP_PROCESS_INFO, V$DATAPUMP_PROCESSWAIT_INFO, and V$DATAPUMP_SESSIONWAIT_INFO) which expose Data Pump processes and wait events to identify bottlenecks quickly.
• Read-Only PDBs: Administrators can make individual Pluggable Database (PDB) users or entire connected sessions strictly read-only, preventing unwanted write operations.
• AutoUpgrade Utility Enhancements: Upgrading to 23ai from versions 19c or 21c is highly streamlined using the updated AutoUpgrade tool, and patching is simplified with proactively provided monthly updates

 AI Workloads & Operations

• AI Vector Search: Allows the database to store and query semantic meanings of unstructured data (text, images) alongside relational predicates, all without needing an external vector database.
• SQL AI Syntax: You can interact with and query the database directly using natural language queries, speeding up day-to-day administrative exploration

Oracle Database 23ai brings critical enhancements to Real Application Clusters (RAC) administration, focusing on patching, containerization, and automated disaster recovery. For database administrators, these updates significantly streamline cluster operations and improve high availability without requiring major infrastructure overhauls

Key RAC features for administrators in 23ai include:

• Rolling Patching for Complex Changes: Administrators can now perform rolling patches for complex grid infrastructure modifications, minimizing downtime and human intervention.
• DBMS_ROLLING Integration with Transparent Application Continuity (TAC): This integration allows for completely seamless, uninterrupted database operations during rolling upgrades or patching, routing sessions transparently without application disruption.
• Reduced Grid Infrastructure Disk Footprint: The GI disk footprint has been reduced by more than 50% compared to previous versions. This enables up to 33% faster initial installations and significantly quicker "add node" operations.
• Lightweight Container Support: The highly optimized disk footprint makes deploying RAC environments in containers—such as Docker or Podman—faster, lighter, and much more practical for testing and dev or microservices architectures.
• RAC Fast Pluggable Database Open: Administrators can now manage pluggable database (PDB) opening and closing operations in RAC environments much faster, localizing PDB status changes without impacting the whole CDB instance.
• True Cache Integration: For read-heavy, latency-sensitive applications, administrators can leverage Oracle True Cache, a middle-tier, in-memory cache that automatically scales with RAC environments without requiring complex application change

Oracle Database 23ai introduces several architectural changes and features specifically related to memory structures that assist administrators in optimizing performance for AI and mission-critical workloads. 

New Memory Structures & Frameworks

• Managed Global Area (MGA): This is a significant addition to the Oracle Database Architecture. The MGA is a memory framework designed to share and coordinate memory across a set of trusted Oracle processes, improving efficiency in modern, high-concurrency environments.
• Oracle True Cache: A new, high-performance, middle-tier caching solution. It functions as a read-only, memory-consistent replica of the primary database, satisfying queries directly from its own buffer cache to reduce the load on the main database server.
• AI Vector Search Memory speed: Support for high-speed vector searches in memory. Specifically on Exadata platforms, this includes "Memory Speed Vector Search," which leverages memory-optimized structures to handle multi-dimensional vector mappings for AI similarity searches

Memory-Related Performance Improvements 

• In-Memory Columnar Speed for JSON: The database now provides In-Memory Columnar speed for JSON queries, allowing JSON data to be stored and processed in the In-Memory Column Store using optimized columnar formats.
• Fast Ingest (Memoptimized Rowstore) Enhancements: Improvements to the Memoptimized Rowstore now include support for direct in-memory column store population and fast flush using direct writes, which streamlines high-frequency data ingestion.
• Large Column Fetch Efficiency: Internal protocol changes have made fetching large columns more efficient for modern drivers, reducing the memory overhead during large-scale data transfer

Administrative Controls

• Automatic KEEP Object Load: On Exadata systems, administrators can benefit from the automatic loading of "KEEP" objects into Flash Cache, ensuring critical data remains in the fastest memory-adjacent storage.
• Lock-Free Column Value Reservations: This feature allows for the creation of rules for data row locking, which improves memory throughput and reduces contention in high-volume transaction environments.
• Memory Usage Monitoring: New administrative scripts and documentation are available to monitor and manage the System Global Area (SGA) and Program Global Area (PGA) more effectively

 

1. AI & Performance Management

• AI Vector Search: Allows direct storage, indexing, and querying of vector data alongside relational data, enabling semantic search capabilities without moving data to specialized vector databases.
• Automatic SQL Plan Management (SPM): The database actively monitors and auto-repairs suboptimal query execution plans to improve stability.
• True Cache: A memory-only, read-only cache that offloads read workloads and satisfies queries locally

Upgrading to Oracle Database 23ai requires a source version of Oracle 19c or 21c. The upgrade process is streamlined using the Oracle AutoUpgrade Utility, which automates pre-checks, fixups, and post-upgrade steps, replacing the older DBUA tool

Upgrade Considerations

• Direct Path: You can upgrade directly from Oracle 19c or 21c to 23ai. Direct upgrades from older releases (like 11g or 12c) require an intermediate upgrade step.
• AutoUpgrade Process: The recommended method involves executing the AutoUpgrade Utility in -mode analyze to generate reports and fix potential pre-upgrade issues before pushing to deployment.
• Client and Grid Infrastructure: If running RAC, Grid Infrastructure must be upgraded prior to the database upgrade. It is also highly recommended to upgrade client software for full interoperability

Oracle Database 23ai introduces several performance monitoring and administrative enhancements focused on AI-driven automation, real-time diagnostics, and streamlined data management. 

Key Performance Monitoring Features

• Automatic SQL Plan Management (SPM): The system can now automatically detect and correct suboptimal SQL plans by providing real-time optimization.
• Real-Time SQL Monitoring & Statistics: Enhanced tools for tracking query performance as it happens, including improved diagnostics and real-time statistics to identify bottlenecks quickly.
• SQL Performance Watch: A new feature to help DBAs monitor and maintain consistent performance during and after database upgrades.
• True Cache: A middle-tier caching solution that satisfies queries using a read-only replication of the primary database's buffer cache, significantly improving application response times and reducing server load.
• Enhanced Observability: New observability dashboards and telemetry provide proactive alerting and resource monitoring

Administrative & Performance Enhancements

• Lock-Free Column Value Reservations: Allows developers and administrators to create rules for row locking, improving throughput and end-user experience for high-concurrency workloads.
• Priority Transactions: Enables automatic prioritization of urgent transactions to prevent them from being blocked by lower-priority row access.
• Data Pump Performance Views: Three new views—(G)V$DATAPUMP_PROCESS_INFO, (G)V$DATAPUMP_PROCESSWAIT_INFO, and (G)V$DATAPUMP_SESSIONWAIT_INFO—help administrators identify bottlenecks and troubleshoot Data Pump operations more effectively.
• Automatic Transaction Quarantine: Automatically handles problematic transactions that might otherwise impact system stability.
• Unrestricted Parallel DMLs: Improvements to parallel data manipulation language (DML) and direct loads to enhance data processing speed.Flashback Log Placement: Administrators can now manage Flashback database logs outside the Fast Recovery Area (FRA) for better storage flexibility

To compare performance and resolve issues after an upgrade to Oracle Database 23ai,

To compare performance and resolve issues after an upgrade to Oracle Database 23ai, you should use Oracle's built-in performance validation tools. These tools allow you to compare a "before" (pre-upgrade) baseline with an "after" (post-upgrade) trial to pinpoint regressions

Core Tools for Comparison

• SQL Performance Analyzer (SPA): This is the primary tool for identifying SQL regressions. It captures a representative workload into a SQL Tuning Set (STS) from your pre-upgrade environment and re-executes it in 23ai to generate a comparison report.
• SQL Performance Watch: A feature within OCI Database Management and Enterprise Manager that helps automate the comparison of workload performance across two time periods (pre- and post-upgrade).
• Database Replay: For high-load environments, this tool captures actual production concurrency and re-plays it on the upgraded 23ai system to identify system-level bottlenecks that single SQL execution might miss

Comparison Workflow

1. Capture Baseline: Use your pre-upgrade system (e.g., 19c) to capture a representative workload during peak time into an STS.
2. Execute Post-Upgrade Trial: Import the STS into the 23ai database and run a "trial" execution. Tools like SQL Performance Analyzer will generate the execution plans and statistics for the new version.
3. Generate Comparison Report: Run a comparison task to view the net impact. Focus on metrics like Elapsed Time, CPU time, and changes in execution plan hash values.
4. Identify Regressions: Review the report for SQL statements with "Regressed" status. These are queries that performed better in the previous version

Post-Upgrade Performance Fixes

If you find performance issues after the move to 23ai:

• Gather Fixed Object Statistics: Run DBMS_STATS.GATHER_FIXED_OBJECTS_STATS after the system has run a representative workload in 23ai. This helps the optimizer understand the internal memory structures of the new version.
• Gather Dictionary Statistics: Use DBMS_STATS.GATHER_DICTIONARY_STATS to ensure the data dictionary metadata is accurately represented for the 23ai optimizer.
• SQL Tuning: Use the SQL Tuning Advisor or SQL Plan Management (SPM) to force the 23ai engine to use a better-performing plan if a regression is found.
• Check Initialization Parameters: Ensure parameters that affect performance haven't changed defaults significantly during the upgrade

How to transfer optimizer statistics and execution plans from your source database to your 23ai target database

 you can absolutely transfer both optimizer statistics and execution plans from your source database to your 23ai target database. This is a standard and highly recommended practice to prevent performance regressions following an upgrade

Method 1: Transferring Optimizer Statistics

You can move statistics by exporting them from the source data dictionary to a temporary staging table, moving that table via Data Pump, and importing it into the target.

1. Create a staging table on the Source:

EXEC DBMS_STATS.CREATE_STAT_TABLE('SYSTEM', 'MY_STAT_TABLE');

2. Export statistics from the Source (Schema Level):
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>'YOUR_SCHEMA', stattab=>'MY_STAT_TABLE', statid=>'SOURCE_STATS', ownname_old=>'SYSTEM');

(Note: You can also export by table or database using EXPORT_TABLE_STATS or EXPORT_DATABASE_STATS.)
3. Move and Import to Target:
Use Oracle Data Pump (expdp/impdp) to export MY_STAT_TABLE from the source, transfer the .dmp file, and import it into the target database.
Once imported, apply the statistics to your 23ai target's data dictionary

sql

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname=>'YOUR_SCHEMA', stattab=>'MY_STAT_TABLE', statid=>'SOURCE_STATS', ownname_old=>'SYSTEM');

Method 2: Transferring Execution Plans (SQL Plan Management)
To lock in exact execution plans that were performing well on your source version, you can capture them as SQL Plan Baselines and migrate them. [1]
1. Load plans into a SQL Tuning Set (STS) on the Source: 

sql
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'GOOD_PLANS_STS');
END;
/

DECLARE
  l_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''YOUR_SQL_ID''')) p;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'GOOD_PLANS_STS', populate_cursor => l_cursor);
END;
/

2. Transport and Unpack:
Package the STS into a staging table using DBMS_SQLTUNE.PACK_STGTAB_SQLSET and export it using Data Pump.
Import the Data Pump file into your 23ai target database and unpack it using DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET. [1]
3. Evolve into Baselines on the Target:
Once the SQL Tuning Set is on your 23ai target, you can easily load those historical plans as active baselines: [1]

DECLARE l_plans PLS_INTEGER; BEGIN l_plans := DBMS_SPM.LOAD_COV_SQLSET(sqlset_name => 'GOOD_PLANS_STS'); END; /

Pro-Tip for 23ai Post-Upgrade Performance

Before importing your source statistics, ensure that your 23ai database has its fixed object statistics properly gathered. Because the 23ai data dictionary and internal memory architectures (fixed tables) differ from older releases, running this after upgrading prevents critical performance degradation

sql

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

For more details

https://learn.oracle.com/ols/course/oracle-database-23ai-new-features-for-administrators/127088/133283