This post is part of a series of blog posts on the Oracle Autonomous Database.

Patching

For ADB-Shared:

• Oracle patches the database during scheduled maintenance windows
• The database remains available throughout the maintenance operation (usually 2 hours)
• The date/time for the next scheduled maintenance is shown on the OCI Console page for each instance
• Currently, users cannot change the patching schedule

For ADB-Dedicated:

• Oracle is responsible for all software update operations
• Database is continuously available to application
  –> Updates applied rolling across RAC nodes and Exadata storage servers
  –> Applications using Application Continuity best practices run without interruption
• Quarterly updates of all components (on-demand for critical security issue)
  –> Firmware, OS, Storage, Network, Hypervisor, Clusterware, Database
• Updates are automatically scheduled based on customer preferences
  –> Customers can adjust timing to accommodate critical business periods
  –> Deploy new versions on !PRD before PRD
  –> Avoid updates during peak periods (e.g. month-end)
  –> Control upgrade to confirm to application certified version
  –> Customer chooses target software (current or previous RU quarterly update)
  –> Can skip up to 2 quarterely patches
  –> Release Update (RU) is set at the CDB level
  –> Release Update Revision (RUR) is not currently supported

Database features and behaviour

• Administrative interfaces and utilities such as RMAN are not available
• The default data and temporary tablespaces for the database are configured automatically
• Adding, removing, or modifying tablespaces is not allowed
• Parallelism is enabled by default. Degree of parallelism for SQL statements is set based on the number of OCPU’s in the system and the database service the user is connecting to. Parallel DML is enabled by default. It is possible to disable parallel DML in your session: ALTER SESSION DISABLE PARALLEL DML;
• Compression is enabled by default. ADB uses Hybrid Columnar Compression for all tables by default. You can also use different compression methods for your tables by specifying the compression clause in your CREATE TABLE or ALTER TABLE commands
• Oracle Database Result Cache is enabled by default for all SQL statements. Changing the result cache mode is not allowed
• Direct access to the database node and local file systems are not allowed
• Database links are supported, both to and from other databases to ADB are allowed
• Calling PL/SQL programs using database links is not supported
• The Java VM (JVM) is not installed in ADB
• ADB uses Unicode AL32UTF8 as the database character set
• Database initialisation parameters are based on the compute and storage capacity you provision. You do not need to set any init parameters to start using your service. But, you can modify some parameters if required.
  • APPROX*
  • NLS*
  • OPTIMIZER* (ADW ignores HINTS by default, ATP honours them)
  • PLSQL_*
  • STATISTICS_LEVEL (only with ALTER SESSION)

ADB Consumer Groups

Run the following SQL to check the resource allocations of your connection session:

SELECT plan, group_or_subplan, mgmt_pl, parallel_degree_limit_pl, parallel_server_limit
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE group_or_subplan IN ( SELECT resource_consumer_group
FROM v$session
WHERE sid = SYS_CONTEXT(‘USERENV’,’SID’));

Run the following SQL to check the resource allocations of other consumer groups:

SELECT plan, group_or_subplan, mgmt_pl, parallel_degree_limit_pl, parallel_server_limit
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE group_or_subplan IN (‘TP’,’TPURGENT’,’LOW’,’MEDIUM’,’HIGH’);

Run-Away Query Management

Run-away query limits can be set using ADB Service Console or using CS_RESOURCE_MANAGER package.

Users can specify the following limits for Consumer Groups:
– Statement runtime is greater than
– Amount of I/O is greater than

Statements exceeding the limits will be cancelled, but the session stays connected

By default a pre-defined session idle time limit of 5 minutes is set to prevent idle sessions from holding system resources for a long period of time.

License Management

BYOL Licensing: Provides a significant discount on OCPU/Hour rate

If you run Oracle Database Enterprise Edition and the Multitenant option then your BYOL requirements are as follows:

• For 1-16 OCPUs of Oracle Autonomous Database:
  • For each supported Processor license of Oracle Database Enterprise Edition plus Options: Multitenant, you may activate up to 2 OCPUs of the BYOL Cloud Service
    
  • For every 25 supported Named User Plus licenses of Oracle Database Enterprise Edition plus Options: Multitenant, you may activate 1 OCPU of the BYOL Cloud Service
    
• For 17 OCPUs or more of Oracle Autonomous Database:
  • For each supported Processor license of Oracle Database Enterprise Edition plus Options: Multitenant and Real Application Clusters, you may activate up to 2 OCPUs of the BYOL Cloud Service
    
  • For every 25 supported Named User Plus licenses of Oracle Database Enterprise Edition plus Options: Multitenant and Real Application Clusters, you may activate 1 OCPU of the BYOL Cloud Service

Application Continuity

Note: By default Application Continuity is disabled.

Application Continuity masks outages from end users and applications by recovering the in-flight work for impacted database sessions following outages. Application Continuity performs this recovery beneath the application so that the outage appears to the application as a slightly delayed execution.

Change the failover type on Autonomous Transaction Processing using the DBMS_CLOUD_ADMIN procedures to enable or to disable Application Continuity. New sessions use the new failover type from the time when you modify the current value.

Enabling Application Continuity requires Oracle Client software version 18.3 (or higher).

If DRAIN_TIMEOUT (in V$SERVICES) has the value 0, this indicates that Application Continuity is disabled.

DBMS_CLOUD_ADMIN.ENABLE_APP_CONT( service_name => ‘nvt21_adb1_high.atp.oraclecloud.com’ );
DBMS_CLOUD_ADMIN.DISABLE_APP_CONT( service_name => ‘nvt21_adb1_high.atp.oraclecloud.com’ );

Use Database Resident Connection Pooling with Autonomous Database

Database Resident Connection Pool (DRCP) in Autonomous Database supports easier and more efficient management of open connections. Using DRCP provides you with access to a connection pool in your Autonomous Transaction Processing database that enables a significant reduction in key database resources required to support many client connections and when the database needs to scale for many simultaneous connections.

When you connect to Autonomous Database you choose one of the following depending on values specified in the tnsnames.ora configuration file:

• A dedicated server process, which services only one user process.
• A pooled server process, obtained from DRCP, which can service multiple user processes.

DRCP is enabled by default; however using DRCP is optional. To choose a pooled connection specify SERVER=POOLED in tnsnames.ora. If you do not specify SERVER=POOLED, you connect with a dedicated connection.