OCI Autonomous Database — Migration & Connection
Overview
Migrate to and connect with OCI Autonomous Database (ADB) using the Python SDK and python-oracledb. Autonomous Database is OCI's crown jewel but migrating to it from standard Oracle DB or other databases is full of gotchas — wallet downloads require SDK calls (not just console clicks), mTLS is mandatory by default, connection strings use a different format than standard Oracle, and Data Pump exports need specific parameter adjustments for ADB compatibility.
Purpose: Provision an Autonomous Database, download the wallet, establish a connection, and migrate data using Data Pump.
Prerequisites
- OCI Python SDK —
pip install oci
- Oracle DB driver —
pip install oracledb
- Config file at
~/.oci/config with fields: user, fingerprint, tenancy, region, key_file
- IAM policy —
Allow group Developers to manage autonomous-databases in compartment <name>
- Python 3.8+
- For Data Pump: access to the source Oracle database with DBA privileges
Instructions
Step 1: Provision an Autonomous Database
import oci
import base64
import zipfile
import os
config = oci.config.from_file("~/.oci/config")
db_client = oci.database.DatabaseClient(config)
adb = db_client.create_autonomous_database(
oci.database.models.CreateAutonomousDatabaseDetails(
compartment_id=config["tenancy"],
display_name="app-adb",
db_name="appadb",
cpu_core_count=1,
data_storage_size_in_tbs=1,
admin_password="SecureP@ss123!",
db_workload="OLTP",
is_free_tier=True,
is_mtls_connection_required=True,
)
).data
print(f"ADB provisioning: {adb.id}")
print(f"State: {adb.lifecycle_state}")
Step 2: Wait for Provisioning and Download Wallet
The wallet contains certificates and connection descriptors needed for mTLS. You must download it via the SDK — the wallet password is set at download time, not during provisioning.
waiter = oci.wait_until(
db_client,
db_client.get_autonomous_database(adb.id),
"lifecycle_state",
"AVAILABLE",
max_wait_seconds=600,
)
print(f"ADB ready: {waiter.data.lifecycle_state}")
wallet_response = db_client.generate_autonomous_database_wallet(
autonomous_database_id=adb.id,
generate_autonomous_database_wallet_details=oci.database.models.GenerateAutonomousDatabaseWalletDetails(
password="WalletP@ss456!",
generate_type="SINGLE",
),
)
wallet_dir = os.path.expanduser("~/wallets/appadb")
os.makedirs(wallet_dir, exist_ok=True)
wallet_path = os.path.join(wallet_dir, "wallet.zip")
with open(wallet_path, "wb") as f:
f.write(wallet_response.data.content)
with zipfile.ZipFile(wallet_path, "r") as z:
z.extractall(wallet_dir)
print(f"Wallet extracted to: {wallet_dir}")
print(f"Files: {os.listdir(wallet_dir)}")
Step 3: Connect Using python-oracledb (Thin Mode)
python-oracledb thin mode does not require Oracle Client libraries. It connects directly using the wallet files.
import oracledb
connection = oracledb.connect(
user="ADMIN",
password="SecureP@ss123!",
dsn="appadb_tp",
config_dir=os.path.expanduser("~/wallets/appadb"),
wallet_location=os.path.expanduser("~/wallets/appadb"),
wallet_password="WalletP@ss456!",
)
cursor = connection.cursor()
cursor.execute("SELECT banner FROM v$version")
print(f"Connected: {cursor.fetchone()[0]}")
cursor.execute("SELECT * FROM v$pdbs")
print(f"PDB: {cursor.fetchone()}")
cursor.close()
connection.close()
Step 4: Data Pump Export from Source Database
Data Pump is the standard Oracle tool for bulk data migration. These parameters ensure ADB compatibility.
sqlplus sys/password@source_db as sysdba <<EOF
CREATE OR REPLACE DIRECTORY export_dir AS '/tmp/datapump';
GRANT READ, WRITE ON DIRECTORY export_dir TO schema_owner;
EOF
expdp schema_owner/password@source_db \
directory=export_dir \
dumpfile=migration_%U.dmp \
logfile=export.log \
schemas=APP_SCHEMA \
exclude=INDEX,CLUSTER,INDEXTYPE,MATERIALIZED_VIEW,MATERIALIZED_VIEW_LOG,MATERIALIZED_ZONEMAP,DB_LINK \
version=19 \
parallel=4
Critical ADB exclusions: ADB manages its own indexes, clusters, and materialized views. Including them causes import failures.
Step 5: Import into Autonomous Database
storage = oci.object_storage.ObjectStorageClient(config)
namespace = storage.get_namespace().data
with open("/tmp/datapump/migration_01.dmp", "rb") as f:
storage.put_object(
namespace_name=namespace,
bucket_name="migration-dumps",
object_name="migration_01.dmp",
put_object_body=f,
)
print("Dump file uploaded to Object Storage")
Then run the import from SQL Developer Web or via credential + DBMS_CLOUD:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => 'oraclecloud_user@example.com',
password => 'auth_token_from_console'
);
END;
/
DECLARE
h NUMBER;
BEGIN
h := DBMS_DATAPUMP.OPEN('IMPORT', 'SCHEMA', NULL, 'MIGRATION_IMPORT');
DBMS_DATAPUMP.ADD_FILE(h, 'migration_01.dmp', 'DATA_PUMP_DIR');
DBMS_DATAPUMP.SET_PARAMETER(h, 'TABLE_EXISTS_ACTION', 'REPLACE');
DBMS_DATAPUMP.START_JOB(h);
END;
/
Step 6: Verify Migration
connection = oracledb.connect(
user="ADMIN",
password="SecureP@ss123!",
dsn="appadb_tp",
config_dir=os.path.expanduser("~/wallets/appadb"),
wallet_location=os.path.expanduser("~/wallets/appadb"),
wallet_password="WalletP@ss456!",
)
cursor = connection.cursor()
cursor.execute("""
SELECT table_name, num_rows
FROM all_tables
WHERE owner = 'APP_SCHEMA'
ORDER BY num_rows DESC
""")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]} rows")
cursor.execute("""
SELECT object_name, object_type, status
FROM all_objects
WHERE owner = 'APP_SCHEMA' AND status = 'INVALID'
""")
invalid = cursor.fetchall()
if invalid:
print(f"\nWARNING: {len(invalid)} invalid objects found:")
for obj in invalid:
print(f" {obj[1]} {obj[0]}: {obj[2]}")
else:
print("\nAll objects valid — migration successful")
cursor.close()
connection.close()
Output
Successful completion produces:
- A provisioned Autonomous Database with mTLS wallet downloaded and extracted
- A working python-oracledb connection in thin mode (no Oracle Client required)
- Data Pump export with ADB-compatible exclusions
- Object Storage upload and DBMS_CLOUD import workflow
- Migration verification with table counts and invalid object checks
Error Handling
| Error | Code | Cause | Solution |
|---|
| Wallet download fails | 404 NotAuthorizedOrNotFound | ADB not in AVAILABLE state | Wait for provisioning to complete (Step 2) |
| DPY-6005: cannot connect | N/A | Wrong wallet path or password | Verify config_dir and wallet_location paths, check wallet password |
| ORA-01017: invalid password | N/A | Wrong admin password | Reset via db_client.update_autonomous_database() with new admin_password |
| Data Pump ORA-39083 | N/A | Unsupported object type in ADB | Add object type to exclude= list in expdp command |
| Not authenticated | 401 NotAuthenticated | Bad API key or config | Verify ~/.oci/config key_file and fingerprint |
| Rate limited | 429 TooManyRequests | Too many API calls | Add backoff; OCI does not return Retry-After header |
| mTLS handshake failure | N/A CERTIFICATE_VERIFY_FAILED | Expired or wrong wallet certificates | Re-download wallet; check wallet expiry (180 days default) |
Examples
Quick ADB list via CLI:
oci db autonomous-database list \
--compartment-id <OCID> \
--query "data[*].{Name:\"display-name\",State:\"lifecycle-state\",OCPUs:\"cpu-core-count\"}" \
--output table
Test connection with one-liner:
import oracledb
conn = oracledb.connect(user="ADMIN", password="P@ss!", dsn="appadb_tp",
config_dir="~/wallets/appadb", wallet_location="~/wallets/appadb",
wallet_password="WalletP@ss!")
print(conn.version)
conn.close()
Resources
Next Steps
After migration, see oraclecloud-query-transform to set up monitoring alarms on ADB metrics (CPU, storage, session count), or oraclecloud-data-handling to manage Object Storage buckets used for Data Pump dumps.
