Autonomous Database Refresh Activity (ADB & APEX)

This document provides a structured approach for performing a Database Refresh Activity using Oracle Autonomous Database (ADB) and reconfiguring Oracle APEX after refresh.

 

Overview of Database Refresh

A Database Refresh typically involves:

• Cloning Production ADB to Dev/Test
• Reconfiguring network settings
• Downloading and updating the wallet
• Resetting passwords
• Updating APEX authentication & roles
• Reconfiguring email credentials
• Reapplying for required grants

This ensures the Dev/Test environment is aligned with Production data and structure.

 

Pre-Refresh Information Collection (Source Database)

Navigate in OCI:

Root Compartment → Dev → Database

Open the existing Dev database and collect:

• Private Endpoint IP
• Private Endpoint URL
• Subnet details
• VCN details
• NSG configuration

If required as part of the refresh policy:

• Stop and terminate the old Dev database.

 

Create a clone from Production (Refresh Process)

Navigate:

Root Compartment → Prod → Database

Open the Production ADB and select:

More Actions → Create Clone

Clone Configuration Steps

Step 1: Select Clone Type

• Full Clone (commonly used for refresh)

Step 2: Choose Target Compartment

• Dev → Database

Step 3: Provide Basic Details

• Display Name
• Database Name
• Workload Type

Step 4: Configure Database Settings

• Database Version
• ECPU Count
• Storage Size
• Backup Retention Period

Step 5: Set ADMIN Password

Step 6: Configure Network

• Select VCN
• Choose Private Endpoint access
• Select Subnet
• Assign Private IP
• Provide Hostname
• Select NSG

Click Create Clone.

Database status flow:

• Provisioning → Available

 

Wallet Download & Configuration

After the database becomes Available:

1. Go to Database Connection
2. Click Download Wallet
3. Extract wallet
4. Update tnsnames.ora if the private endpoint IP needs adjustment
5. Re-zip wallet for application or SQL Developer usage

 

Change APEX Authentication Mode (If Required)

Log in as ADMIN in SQL Developer and execute:

BEGIN

 apex_instance_admin.set_parameter(

   ‘APEX_BUILDER_AUTHENTICATION’,

   ‘DB’

 );

END;

/

COMMIT;

This changes authentication from SSO to Database authentication.

 

Execute Post-Refresh SQL Scripts

Execute:

• Procedure creation SQL
• Job creation SQL

Steps:

1. Copy SQL file path
2. Open SQL Developer
3. Connect as ADMIN
4. Execute scripts

 

Reset Database User Passwords

After refresh:

1. Go to Administration
2. Select Database Users
3. Search user
4. Edit user
5. Update password
6. Save

Repeat for all required application users.

 

APEX Application Reconfiguration

Login to APEX workspace.

Navigate:

App Builder → Application → Shared Components

1. Update Authentication Scheme

• Go to Security → Authentication Schemes
• Open Oracle APEX Accounts
• Click Make Current
• Ensure Is Current = Yes

2. Update Authorization Schemes

Navigate:

Shared Components → Security → Authorization Schemes

For each scheme (Admin / Developer / User):

• Type → Application Role
• Name → Corresponding Role Name
• Click Apply Changes

3. Update Security Attributes

Navigate:

Security → Security Attributes

Set:

Source for Role or Group Schemes 

Click Apply Changes.

4. Access Control User Role Assignments

Configure Application Access Control

Navigate to:

Application Access Control

1. Click Add User Role Assignment
2. Enter username
3. Assign required roles
4. Create Assignment

5.Manage APEX Users

Navigate:

Administration → Manage Users and Groups

To create user:

• Click Create User
• Enter username & email
• Assign Developer role if needed
• Set password
• Save

To update:

• Select user
• Update password
• Save

 

Recreate Email Credential

Log in as the application schema and recreate the credentials:

BEGIN

 DBMS_CLOUD.DROP_CREDENTIAL(‘EMAIL_CRED’);

END;

/

 

BEGIN

 DBMS_CLOUD.CREATE_CREDENTIAL(

   credential_name => ‘EMAIL_CRED’,

   username        => ‘<email-username>’,

   password        => ‘<email-password>’

 );

END;

/

Test email:

BEGIN

 DBMS_CLOUD_NOTIFICATION.SEND_DATA(

   provider        => ’email’,

   credential_name => ‘EMAIL_CRED’,

   query           => ‘SELECT SYSDATE FROM dual’,

   params          => json_object(

     ‘recipient’ VALUE ‘<recipient>’,

     ‘subject’   VALUE ‘Test Subject’,

     ‘message’   VALUE ‘Test Message’

   )

 );

END;

/

Successful execution will generate a confirmation email.

 

Apply Required Grants

Log in as ADMIN:

GRANT EXECUTE ON DBMS_SQL TO <schema_name>;

GRANT SELECT ON DBA_TAB_PRIVS TO <schema_name>;

GRANT SELECT ON DBA_USERS TO <schema_name>;

 

Post-Refresh Validation Checklist

• Database status = Available
• Wallet updated and tested
• APEX authentication verified
• All SQL scripts executed
• User passwords reset
•  Authorisation roles verified
• Access control assignments update
•  Email credential tested
• Required grants applied.