Oraclecloud Schema Migration

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)

# Create Autonomous Database (Transaction Processing workload)
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,           # 1 OCPU (Always Free eligible)
        data_storage_size_in_tbs=1,  # 1 TB (Always Free: 20GB)
        admin_password="SecureP@ss123!",  # Must meet complexity requirements
        db_workload="OLTP",         # OLTP, DW, AJD, or APEX
        is_free_tier=True,          # Always Free if eligible
        is_mtls_connection_required=True,  # Default — use mTLS
    )
).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.

# Wait for ADB to become AVAILABLE
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}")

# Download wallet
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!",  # Wallet password (different from admin)
        generate_type="SINGLE",     # SINGLE for one DB, ALL for region
    ),
)

# Save and extract wallet
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

# Thin mode connection (no Oracle Client needed)
connection = oracledb.connect(
    user="ADMIN",
    password="SecureP@ss123!",
    dsn="appadb_tp",  # From tnsnames.ora in wallet (_tp = Transaction Processing)
    config_dir=os.path.expanduser("~/wallets/appadb"),
    wallet_location=os.path.expanduser("~/wallets/appadb"),
    wallet_password="WalletP@ss456!",
)

# Verify connection
cursor = connection.cursor()
cursor.execute("SELECT banner FROM v$version")
print(f"Connected: {cursor.fetchone()[0]}")

# Check ADB-specific info
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.

# On the SOURCE database server
# Create a directory object pointing to a dump location
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

# Export with ADB-compatible parameters
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

# Upload dump files to Object Storage first
storage = oci.object_storage.ObjectStorageClient(config)
namespace = storage.get_namespace().data

# Upload dump file
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:

-- In ADB SQL worksheet (SQL Developer Web or sqlcl)
-- Step 1: Create credential for Object Storage access
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_CRED',
    username        => '[email protected]',
    password        => 'auth_token_from_console'
  );
END;
/

-- Step 2: Import via Data Pump from Object Storage
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()

# Count tables
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")

# Check for invalid objects
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]}")