How to Refresh an Autonomous Database and Reconfigure APEX

How to Refresh an Autonomous Database and Reconfigure APEX

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.