Oracle Autonomous Database Professional cheat sheet

Autonomous Database (ADB) comes in two workload flavors: Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP); both share the same automation stack but differ in default indexing, parallelism, and optimization goals.

Deployment options are Serverless (shared Exadata infrastructure, billed per second of compute and per TB of storage) and Dedicated (Exadata Cloud Infrastructure with exclusive resources, needed for isolation/compliance).

ADB automatically performs patching, upgrades, backups (daily incremental plus point-in-time restore within the retention window), and index creation, so DBAs do not schedule these manually.

Auto Scaling lets a Serverless ADB temporarily use up to 3x its base OCPU count when load spikes, then return to base; it is enabled by default and does not change the base cost.

Scale-up/down changes the base OCPU count; scale-out (adding read-only instances) is a separate operation that adds compute nodes for read scaling, not more CPUs on the primary.

Built-in features include Automatic Indexing, Auto Partitioning, SQL Plan Management, Real-Time Statistics, and Automatic Materialized Views; these run inside the database without user scheduling.

ADB supports the Autonomous AI Database features: Select AI (natural-language to SQL), AI Vector Search, and in-database machine learning via OML4SQL/OML notebooks.

Resource management uses consumer groups mapped to services (HIGH, MEDIUM, LOW, TP, TPURGENT); the connection service you choose sets the parallelism and priority of the session.

Access control combines IAM policies (who can manage the resource) with database users (who can query data); the ADMIN user is created at provisioning and cannot be dropped.

Provisioning requires a compartment, a workload type (ADW/ATP/AJD/APEX/JSON), a database name (max 30 characters, alphanumeric), and a compute model (ECPU or OCPU) with a base compute count.

Storage auto-scales up to 384 TB by default on Serverless; you set an initial storage value and ADB grows it automatically without downtime.

Network access is controlled by an access control list (ACL) of allowed CIDR blocks, or by a private endpoint in a VCN subnet; choosing private endpoint makes the public ACL irrelevant.

Mutual TLS (mTLS) requires a client wallet containing ewallet.pem, cwallet.sso, tnsnames.ora, sqlnet.ora, and ojdbc.properties; without mTLS you must still use TLS but may authenticate with a username/password over a secure connection.

Connection services: _high (parallel, resource-heavy), _medium (balanced), _low (serial, long-running), _tp and _tpurgent (transaction processing), plus _low/_high variants for read-only instances.

The tnsnames.ora in the wallet contains one entry per service per region; connect with sqlplus or a JDBC thin URL of the form jdbc:oracle:thin:@<tns_alias>?TNS_ADMIN=<wallet_dir>.

Always Free ADBs provide 2 ECPU (or 1 OCPU) and 20 GB storage, limited to two instances per tenancy, and can be upgraded in place to paid.

Clone operations can be full (copies data) or metadata-only (copies schema, no rows), and a clone is a point-in-time copy that can be refreshed from the source.

A private endpoint requires a VCN, a subnet, and optionally a network security group; after creation you cannot switch an existing ADB between public and private endpoint without recreating it.

Key OCI metrics include CPU Utilization, Storage Utilization, Session Count, Execute Count, Transaction Count, Running Statements, and Failed Connections; alarm on CPU and storage thresholds.

Performance Hub shows ASH analytics, SQL monitoring, wait events, and AWR-style reports; it is the first stop for diagnosing a slow statement.

Automatic Indexing creates, validates, and (if performance regresses) rolls back indexes; review new indexes in the Automatic Indexing section before they are made permanent.

Backups are automatic; you can also create manual backups and restore to a point in time within the retention period (default 60 days for Serverless, configurable).

Scale operations (scale up/down compute, scale storage) are online for Serverless; changing the compute model between ECPU and OCPU is a separate supported operation.

Refreshable clones let you create a read-only clone that periodically refreshes from the source, useful for reporting without impacting production.

The ADMIN user manages database users, roles, and quotas; use Database Actions (SQL, Data Studio, Model, APEX) for browser-based administration.

Data Safe integration provides security assessment, user assessment, activity auditing, and sensitive data discovery for ADB.

Long-running operations appear in the OCI Work Requests; check the work request status if a provisioning or scaling action seems stuck.

Data Pump (expdp/impdp) is the standard for moving schemas into ADB; use the DATA_PUMP_DIR directory and a credential, or the OCI Console import wizard which handles the dump file in Object Storage.

For cross-database migration use Oracle Zero Downtime Migration (ZDM) or GoldenGate; for one-time moves, Data Pump export from source and import into ADB is simplest.

Data Studio in Database Actions loads CSV/JSON/XLSX files: upload to Object Storage or local file, preview, map columns, and load into a new or existing table.

External tables over Object Storage (DBMS_CLOUD.CREATE_EXTERNAL_TABLE) let you query files in a bucket without loading; use DBMS_CLOUD.CREATE_CREDENTIAL for the auth token.

DBMS_CLOUD.COPY_DATA loads from Object Storage, and COPY_COLLECTION loads JSON documents; both require a credential and a format specification.

SQL*Loader is not the preferred path on ADB; use Data Pump, Data Studio, or DBMS_CLOUD instead, because direct path loads and OS access are restricted.

Before import, create the target user with the needed quota and grants; Data Pump remap_schema can move objects from the source schema to the ADB schema.

Use DBMS_CLOUD.GET_OBJECT to download a dump file to the DATA_PUMP_DIR, then impdp with the directory; large dumps should be split or parallelized.

Validate row counts and check for invalid objects after migration; ADB may reject unsupported features, so review the migration advisor output first.