Creating new ATP instance & Login to APEX

What you will get from this post:

     A complete guidance to create an Autonomous Transaction Processing database and login to the Oracle APEX. Also we'll cover how to connect Cloud DB with local SQL Developer.

Steps to be followed:

After successful signup process, we'll be redirected to the OCI home page

From the home page left side menu go to Identity & Security tab and find Compartments. For creating ATP instance its not necessary to create a compartment. But for the formal way we should not create DB under a root directory.

Under compartments tab create a new Compartment under root.

Then navigate to left side panel and choose Oracle Database & click Autonomous Transaction Processing and select the compartment which you created the above. 

Click Create Autonomous Database & provide required details. Choose Always Free configuration options & network access type as Secure access from everywhere.

These are steps we need to follow for creating the ATP instance. Once you click Create Autonomous database at the end it will start the work request and it'll take some time to create it. After successful completion of that process we'll receive an email like the below.

Note: OCI provides 2 free DB instances only. So please careful with the settings before creating it (We cannot edit some settings after creation). I used one is for APEX instance and another one for ATP instance (with named APPSDB1 under root/CompartmentForAPEX)

After receiving the email we can able to access the DB and APEX instances from that compartment which we created.

Creating USER:

    From Database actions/ Database Users screen we can able to create a new user with GUI.

Else we can use Database action/ SQL section and run the below sql query to creating a USER and granting access.

CREATE USER APPS IDENTIFIED BY Pa55word@01;
GRANT CONNECT TO APPS;
GRANT CONSOLE_DEVELOPER TO APPS;
GRANT DWROLE TO APPS;
GRANT OML_DEVELOPER TO APPS;
GRANT RESOURCE TO APPS;
ALTER USER APPS DEFAULT ROLE CONSOLE_DEVELOPER,DWROLE,OML_DEVELOPER;
-- ENABLE OML
ALTER USER APPS GRANT CONNECT THROUGH OML$PROXY;
ALTER USER APPS QUOTA 500M ON DATA;

Login to APEX:

    After creating the DB USER we can able to login to the APEX instance from Tool configuration section.

After login to APEX we can able to create multiple users from GUI or use the below PL/SQL script for APEX user creation.

begin

apex_util.set_workspace(p_workspace      => 'APPS');
    apex_util.create_user(
        p_user_name                    => 'USERNAME',
        p_web_password                 => 'password',
        p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_email_address                => 'xyz@gmail.com',
        p_default_schema               => 'APPS',
        p_change_password_on_first_use => 'N' );

commit;
end;
/

Connecting Cloud Database with Local SQL Developer:

    For connecting DB with local SQL Developer we need to download the cloud wallet from ATP instance. Navigate to ATP instance and click Database connection button. To download the wallet we need to provide a password. Once we done these steps, the wallet file will downloaded as Wallet_APPSDB1.zip

• Move that zip file to a secure location and open SQL Developer from your local system navigate new connection window.
• Give name anything you want and fill the Username and Password which we created before. (We can able to connect with ADMIN credentials also).
• Choose Connection type as Cloud Wallet and provide the wallet path. 
• Now test the connection. Definitely status will get updated as Success.

Note: I placed my wallet file to C:\wallet

Its really easy to connect Cloud DB to our local SQL Developer.

Things I wondered:

    DB installation and APEX integration in On premises will take lot of time and more complicated. But here any manual integrations between DB and APEX is no longer needed. All things are come with prebuilt setup.

Stay curious, stay inspired and let's explore the OCI more!