1. Administering Oracle Database Classic Cloud Service
  2. Configuring Database Features, Database Options, and Companion Products
  3. Using Oracle Application Express in Database Classic Cloud Service

Using Oracle Application Express in Database Classic Cloud Service

Database deployments on Oracle Database Classic Cloud Service include Oracle Application Express.

Note:

This section does not apply to Database Classic deployments that use Oracle Real Application Clusters. Such deployments do not currently include Oracle Application Express.

Oracle Application Express enables you to design, develop and deploy responsive, database-driven applications using only your web browser. If you are new to Oracle Application Express, see its Overview and Getting Started pages on Oracle Technology Network to learn about its features and get started using it.

This section provides guidance on how to navigate to Oracle Application Express from Oracle Database Classic Cloud Service and how to upgrade Oracle Application Express releases residing in Database Classic Cloud Service.

Topics

Accessing the Oracle Application Express Console

Database deployments of single-instance databases on Oracle Database Classic Cloud Service include Oracle Application Express, which you manage using the Oracle Application Express Instance Administration console.

If you are new to Oracle Application Express, see its Overview and Getting Started pages on Oracle Technology Network to learn about its features and get started using it.

You can access the Oracle Application Express Instance Administration console in the following ways:

Using the “Open Application Express Console” Menu Item to Access the Console

Note:

For database deployments built on Oracle Cloud Infrastructure Classic, the network port to access the Oracle Application Express Instance Administration console is blocked by default. To use the Open Application Express Console menu item, you must unblock port 443, either by enabling the deployment’s ora_p2_httpssl predefined access rule or by creating your own access rule that opens port 443. For instructions, see Enabling Access to a Compute Node Port.

  1. Open the Services page of the Oracle Database Classic Cloud Service console.

    For detailed instructions, see Accessing the Oracle Database Classic Cloud Service Console.

  2. From the Menu icon menu for the deployment, select Open Application Express Console.

  3. If your browser displays a warning that your connection is not secure or not private, use the browser’s advanced option to continue.

    You get this warning because Database Classic Cloud Service database deployments use a self-signed certificate to provide HTTPS (secure HTTP) connectivity, and such certificates are considered suspicious by many web browsers.

  4. When prompted for a workspace, username and password, enter the following information. Then click Sign In.

    • In the workspace box, enter INTERNAL.

    • In the username box, enter ADMIN.

    • In the password box, enter the password specified during the database deployment creation process.

      Note: if the database deployment was created using a QuickStart template, the password is available in the zip file downloaded when the deployment was created.

The Open Application Express Console menu item is also available on the Overview page in the Menu icon menu next to the deployment’s name.

Using a Direct URL to Access the Console

Note:

For database deployments built on Oracle Cloud Infrastructure Classic, the network port to access the Oracle Application Express Instance Administration console is blocked by default. To use a direct URL, you must unblock port 443, either by enabling the deployment’s ora_p2_httpssl predefined access rule or by creating your own access rule that opens port 443. For instructions, see Enabling Access to a Compute Node Port.

  1. In your web browser, go to the URL appropriate to the Oracle Database version on the database deployment:

    • For a PDB in an Oracle Database 12c database: https://node-ip-address/ords/lowercase-pdb-name/

    • For an Oracle Database 11g Release 2 database: https://node-ip-address/apex/

    where compute-node-ip-address is the IP address of the deployment’s compute node as listed on the deployment’s Overview page, and lowercase-pdb-name is the name of the PDB, with all letters in lowercase.

  2. If your browser displays a warning that your connection is not secure or not private, use the browser’s advanced option to continue.

    You get this warning because Database Classic Cloud Service database deployments use a self-signed certificate to provide HTTPS (secure HTTP) connectivity, and such certificates are considered suspicious by many web browsers.

  3. When prompted for a workspace, username and password, enter the following information. Then click Sign In.

    • In the workspace box, enter INTERNAL.

    • In the username box, enter ADMIN.

    • In the password box, enter the password specified during the database deployment creation process.

      Note: if the database deployment was created using a QuickStart template, the password is available in the zip file downloaded when the deployment was created.

Using an SSH Tunnel to Access the Console

  1. Create an SSH tunnel to port 443 on the compute node hosting Oracle Application Express. For information about creating an SSH tunnel, see Creating an SSH Tunnel to a Compute Node Port.

  2. After creating the SSH tunnel, direct your browser to the URL appropriate to the Oracle Database version on the database deployment:

    • For a PDB in an Oracle Database 12c database: https://localhost/ords/lowercase-pdb-name/

    • For an Oracle Database 11g Release 2 database: https://localhost/apex/

    where lowercase-pdb-name is the name of the PDB, with all letters in lowercase.

  3. When prompted for a workspace, username and password, enter the following information. Then click Sign In.

    • In the workspace box, enter INTERNAL.

    • In the username box, enter ADMIN.

    • In the password box, enter the password specified during the database deployment creation process.

      Note: if the database deployment was created using a QuickStart template, the password is available in the zip file downloaded when the deployment was created.

Upgrading from Oracle Application Express 4.2 or 5.0 to 5.1 for Oracle Database 11g

These instructions are applicable if you have an Oracle Database 11g database deployment and want to upgrade Oracle Application Express 4.2 or Oracle Application Express 5.0 to Oracle Application Express 5.1.

To upgrade from Oracle Application Express 4.2 or Oracle Application Express 5.0 to 5.1:
  1. Determine the version of your current Oracle Application Express installation:
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user and query DBA_REGISTRY:
      $ sqlplus / as sysdba
      SQL> SELECT VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
    3. Log out of SQL*Plus.
    4. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+17.3.1.0.0_170605.2102.x86_64. Check the value between the + and _ for the version number. The tooling version must be 17.2.5.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Upload Oracle Application Express 5.1 to the compute node:
    1. Download Oracle Application Express 5.1.0.00.45 from Oracle Technology Network.
    2. Log in to the compute node as the oracle user.
    3. Use scp or sftp to upload the Oracle Application Express 5.1 zip file to the /tmp directory on the compute node.
  4. Unzip the Oracle Application Express 5.1 zip file:
    1. Create the directory required for Oracle Application Express:
      mkdir -p /u01/app/oracle/product/apex/
    2. Change to the /tmp directory where you uploaded the Oracle Application Express zip file.
    3. Unzip the uploaded Oracle Application Express zip file into the apex directory:
      unzip apex_5.1.zip -d /u01/app/oracle/product/apex/
  5. Install Oracle Application Express 5.1:
    1. Move the Oracle Application Express files to the /u01/app/oracle/product/apex/5.1.0.00.45/ directory:
      mv /u01/app/oracle/product/apex/apex/ /u01/app/oracle/product/apex/5.1.0.00.45/
    2. Change to the new 5.1.0.00.45 directory:
      cd /u01/app/oracle/product/apex/5.1.0.00.45
    3. Log in to SQL*Plus as the SYS user and execute the installation script:
      sqlplus / as SYSDBA @apexins.sql SYSAUX SYSAUX TEMP /i/
    4. Log out of the compute node.
  6. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:
    1. Log in to the compute node as the opc user.
    2. Start a root-user shell:
      $ sudo -s
    3. Change to the ords directory:
      cd /var/opt/oracle/ocde/assistants/ords
    4. Execute the ORDS assistant:
      ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="configure_apex"
    5. Restart ORDS:
      /etc/init.d/ords restart
    6. Exit the root-user shell and log out of the compute node.
  7. If you upgraded from Oracle Application Express version 4.2, perform the following steps:
    1. Open the Oracle Database Classic Cloud Service console.
    2. From the Menu icon menu for the deployment, select Open Application Express Console.
      The Oracle Application Express login page is displayed.
    3. Enter the following information to log in. Then click Sign In.

      • In the Workspace box, enter INTERNAL.

      • In the Username box, enter ADMIN.

      • In the Password box, enter the password specified during the database deployment creation process.

      The Oracle Application Express Instance Administration page is displayed.
    4. Locate Instance Settings and click on the pencil icon to edit the settings.
    5. Select Report Printing.
    6. In the Print Server field, select Oracle REST Data Services from the menu.
    7. Click Apply Changes.
    8. Log out of the Oracle Application Express Administration Services application.

Upgrading from Oracle Application Express 4.2 to 5.1 for Oracle Database 12c

These instructions are applicable if you have an Oracle Database 12c Release 1 or Release 2 database deployment and want to upgrade Oracle Application Express 4.2 in the root container (CDB$ROOT) to Oracle Application Express 5.1 in the pluggable databases (PDBs).

To upgrade from Oracle Application Express 4.2 in the root container (CDB$ROOT) to Oracle Application Express 5.1 in the PDBs:
  1. Determine the version of Oracle Application Express installed in the root container (CDB$ROOT):
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user and query DBA_REGISTRY:
      $ sqlplus / as sysdba
      SQL> SELECT VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
      The version should be 4.2.5.00.08. If the version is 5.0.0.00.31 or 5.0.4.00.12, see Upgrading from Oracle Application Express 5.0 to 5.1 for Oracle Database 12c.
    3. Log out of SQL*Plus.
    4. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+17.3.1.0.0_170605.2102.x86_64. Check the value between the + and _ for the version number. The tooling version must be 17.2.5.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Upload Oracle Application Express 5.1 to the compute node:
    1. Download Oracle Application Express 5.1.0.00.45 from Oracle Technology Network.
    2. Log in to the compute node as the oracle user.
    3. Use scp or sftp to upload the Oracle Application Express 5.1 zip file to the /tmp directory on the compute node.
  4. Unzip the Oracle Application Express 5.1 zip file:
    1. Create the directory required for Oracle Application Express:
      mkdir -p /u01/app/oracle/product/apex/
    2. Change to the /tmp directory where you uploaded the Oracle Application Express zip file.
    3. Unzip the uploaded Oracle Application Express zip file into the apex directory:
      unzip apex_5.1.zip -d /u01/app/oracle/product/apex/
    4. Move the Oracle Application Express files to the /u01/app/oracle/product/apex/5.1.0.00.45/ directory:
      mv /u01/app/oracle/product/apex/apex/ /u01/app/oracle/product/apex/5.1.0.00.45/
  5. Determine which PDBs have an APEX instance:
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    3. Execute the query to determine which PDBs have an APEX instance:
      SQL> SELECT P.PDB_NAME, R.VERSION, R.STATUS
      2 FROM SYS.DBA_PDBS P, SYS.CDB_REGISTRY R
      3 WHERE P.PDB_ID = R.CON_ID AND R.COMP_ID = 'APEX'
      4 ORDER BY 1;
  6. In each PDB, identify the APEX workspaces. Be sure to make note of which workspaces belong to which PDBs. You will need this information in a later step.
    1. While still logged in to SQL*Plus, connect to each PDB by using the ALTER SESSION command:
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    2. Query APEX_WORKSPACES to determine the APEX workspace names:
      SQL> SELECT WORKSPACE_ID FROM APEX_WORKSPACES WHERE WORKSPACE_ID > 100;
    3. After connecting to each PDB and compiling a list of workspace IDs, log out of SQL*Plus.
  7. Download a script that will be used to export the artifacts of the workspaces identified in the previous step into zip files:
    1. Create a directory for the workspace zip files:
      $ mkdir -p /home/oracle/workspaces
    2. Using wget, download the workspace_export_4.2.sh file from Oracle Storage Cloud Service.
      wget https://storage.us2.oraclecloud.com/v1/dbcsswlibp-usoracle29538/dbaas_patch/apex_upg5_1/workspace_export_4.2.sh or
      wget https://a88717.storage.oraclecloud.com/v1/Storage-a88717/dbaas_patch/apex_upg5_1/workspace_export_4.2.sh
    3. Move the script to the /home/oracle/workspaces directory.
    4. Change to the workspaces directory:
      $ cd /home/oracle/workspaces
    5. Assign permissions to the workspace_export_4.2.sh file:
      $ chmod 755 /home/oracle/workspaces/workspace_export_4.2.sh
      $ chown oracle:oinstall /home/oracle/workspaces/workspace_export_4.2.sh
  8. Temporarily unlock the APEX_040200 user in the CDB and temporarily set the user’s password to oracle or a password of your choice. The script that you execute in a later step will reset the password.
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Unlock the user and set the password:
      SQL> ALTER USER APEX_040200 ACCOUNT UNLOCK IDENTIFIED BY password CONTAINER = ALL;
    3. Log out of SQL*Plus.
  9. Use Listener Control to determine the service name for each PDB
    $ lsnrctl status
    You should see output similar to:

    Service "pdb1.opcdbaas.oraclecloud.internal" has 1 instance(s).

    Instance "orcl", status READY, has 1 handler(s) for this service...

  10. For each workspace you identified in step 6, execute the workspace_export_4.2.sh script.
    ./workspace_export_5.0.sh //localhost:port_number/PDB_service_name APEX_040200 password workspace_id
    where:

    • port_number is the listener port number you specified when you created the database deployment (default is 1521).

    • PDB_service_name is one of the service names you identified in step 9.

    • password is the password you set for the APEX_040200 user in step 8b.

    • workspace_id is one of the APEX workspaces for the PDB that you identified in step 6b.

    One zip file will be created for each workspace.
  11. Remove APEX 4.2 from the root container (CDB$ROOT).
    1. Change to the directory that contains the APEX 4.2.5.00.08 files.
      cd $ORACLE_HOME/apex/
    2. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    3. Set the _oracle_script parameter to TRUE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
    4. Uninstall APEX from the root container (CDB$ROOT).
      SQL> @apxremov.sql
    5. Log out of SQL*Plus.
  12. Uninstall APEX 4.2 from the CDB seed (PDB$SEED).
    1. Change to the directory that contains the APEX 4.2.5.00.08 files.
      cd $ORACLE_HOME/apex/
    2. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    3. Set the _oracle_script parameter to TRUE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
    4. Close the PDB.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
    5. Open the PDB in READ WRITE mode.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;
    6. Uninstall APEX from the PDB.
      SQL> @apxremov.sql
    7. Set the container to the root container (CDB$ROOT).
      SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
    8. Close the PDB.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
    9. Open the PDB in READ ONLY mode.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
    10. Log out of SQL*Plus.
  13. Uninstall APEX 4.2 from the PDBs identified in step 5.
    1. Change to the directory that contains the APEX 4.2.5.00.08 files.
      cd $ORACLE_HOME/apex/
    2. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    3. Set the _oracle_script parameter to TRUE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
    4. Set the container to the PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    5. Uninstall APEX from the PDB.
      SQL> @apxremov.sql
    6. After repeating steps 13d and 13e for each identified PDB, log out of SQL*Plus.
  14. Stop ORDS.
    /etc/init.d/ords stop
  15. Delete the APEX users for the previous version of APEX installed in the root container (CDB$ROOT).
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Query DBA_USERS.
      SQL> select username from dba_users where username like '%APEX_0%';
    3. Exit from SQL*Plus.
    4. Drop the users identified in step 13b.
      $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_user -- --x'drop user apex_user cascade'
      where apex_user is the value in the USERNAME column from step 15b.
  16. Remove the APEX users from all containers.
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_listener -- --x'drop user apex_listener cascade'
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_rest_public_user -- --x'drop user apex_rest_public_user cascade'
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_public_user -- --x'drop user apex_public_user cascade'
  17. Change to the new 5.1.0.00.45 directory
    cd /u01/app/oracle/product/apex/5.1.0.00.45
  18. Install APEX in the CDB seed (PDB$SEED).
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Set the mode of PDB$SEED to OPEN READ WRITE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;
    3. Connect to PDB$SEED.
      SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
    4. Install APEX 5.1 in the PDB$SEED container
      SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
      SQL> exit
    5. Again, log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    6. Set the mode of PDB$SEED back to OPEN READ ONLY.
      SQL> ALTER SESSION SET "_oracle_script"=true;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
    7. Exit from SQL*Plus.
  19. Install APEX in each PDB you identified in step 5c.
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Connect to the PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    3. Install APEX 5.1 in the PDB.
      SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
    4. After repeating steps 19b and 19c for each PDB you identified in step 5c, exit from SQL*Plus.
    5. Log out of the compute node.
  20. Reconstruct public synonyms in the CDB seed (PDB$SEED).
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Set the _oracle_script parameter to TRUE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
    3. Close the PDB.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
    4. Open the PDB in READ WRITE mode.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;
    5. Set the container to PDB$SEED.
      SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
    6. Set CURRENT_SCHEMA to APEX_050100.
      SQL> alter session set current_schema = APEX_050100;
    7. Execute the procedure to drop the public synonyms.
      SQL> exec wwv_flow_upgrade.drop_public_synonyms(p_drop_all => true);
    8. Execute the procedure to re-create the public synonyms.
      SQL> exec wwv_flow_upgrade.recreate_public_synonyms('APEX_050100');
    9. Set the container to the root container (CDB$ROOT).
      SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
    10. Close the PDB.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
    11. Open the PDB in READ ONLY mode.
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
    12. Log out of SQL*Plus.
  21. Reconstruct public synonyms for each PDB you identified in step 5
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Set the container to the PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    3. Set CURRENT_SCHEMA to APEX_050100.
      SQL> alter session set current_schema = APEX_050100;
    4. Execute the procedure to drop the public synonyms.
      SQL> exec wwv_flow_upgrade.drop_public_synonyms(p_drop_all => true);
    5. Execute the procedure to re-create the public synonyms.
      SQL> exec wwv_flow_upgrade.recreate_public_synonyms('APEX_050100');
    6. Log out of SQL*Plus.
  22. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:
    1. Log in to the compute node as the opc user.
    2. Start a root-user shell:
      $ sudo -s
    3. Change to the ords directory:
      cd /var/opt/oracle/ocde/assistants/ords
    4. Execute the ORDS assistant:
      ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="install"
    5. Exit the root-user shell and log out of the compute node.
  23. Install each APEX workspace into a PDB.
    1. Log in to the compute node as the oracle user.
    2. Change to the workspaces directory where the zip files from step 10 are located.
      cd /home/oracle/workspaces
    3. Unzip each of the workspace zip files into its corresponding directory.
      unzip install_workspace_id_date.zip -d workspace_id
    4. Change to one of the newly created /home/oracle/workspaces/workspace_id directories.
    5. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    6. Connect to the PDB where the workspace should be installed. Refer to the list you compiled in step 6 to ensure you install each workspace in the correct PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    7. Execute the installation script to install the APEX workspace in the PDB.
      @install_workspace_id.sql
    8. After executing steps 23d through 23g for each APEX workspace, exit SQL*Plus.
    9. Log out of the compute node.

Upgrading from Oracle Application Express 5.0 to 5.1 for Oracle Database 12c

These instructions are applicable if you have an Oracle Database 12c Release 1 or Release 2 database deployment and want to upgrade Oracle Application Express 5.0 in the root container (CDB$ROOT) to Oracle Application Express 5.1 in the pluggable databases (PDBs).

To upgrade from Oracle Application Express 5.0 in the root container (CDB$ROOT) to Oracle Application Express 5.1 in the PDBs:
  1. Determine the version of Oracle Application Express installed in the root container (CDB$ROOT):
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user and query DBA_REGISTRY:
      $ sqlplus / as sysdba
      SQL> SELECT VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
      The version should be 5.0.0.00.31 or 5.0.4.00.12. If the version is 4.2.5.00.08, see Upgrading from Oracle Application Express 4.2 to 5.1 for Oracle Database 12c.
    3. Log out of SQL*Plus.
    4. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+17.3.1.0.0_170605.2102.x86_64. Check the value between the + and _ for the version number. The tooling version must be 17.2.5.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Upload Oracle Application Express 5.1 to the compute node:
    1. Download Oracle Application Express 5.1.0.00.45 from Oracle Technology Network.
    2. Log in to the compute node as the oracle user.
    3. Use scp or sftp to upload the Oracle Application Express 5.1 zip file to the /tmp directory on the compute node.
  4. Unzip the Oracle Application Express 5.1 zip file:
    1. Create the directory required for Oracle Application Express:
      mkdir -p /u01/app/oracle/product/apex/
    2. Change to the /tmp directory where you uploaded the Oracle Application Express zip file.
    3. Unzip the uploaded Oracle Application Express zip file into the apex directory:
      unzip apex_5.1.zip -d /u01/app/oracle/product/apex/
    4. Move the Oracle Application Express files to the /u01/app/oracle/product/apex/5.1.0.00.45/ directory:
      mv /u01/app/oracle/product/apex/apex/ /u01/app/oracle/product/apex/5.1.0.00.45/
  5. Determine which PDBs have an APEX instance:
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    3. Execute the query to determine which PDBs have an APEX instance:
      SQL> SELECT P.PDB_NAME, R.VERSION, R.STATUS
      2 FROM SYS.DBA_PDBS P, SYS.CDB_REGISTRY R
      3 WHERE P.PDB_ID = R.CON_ID AND R.COMP_ID = 'APEX'
      4 ORDER BY 1;
  6. In each PDB, identify the APEX workspaces. Be sure to make note of which workspaces belong to which PDBs. You will need this information in a later step.
    1. While still logged in to SQL*Plus, connect to each PDB by using the ALTER SESSION command:
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    2. Query APEX_WORKSPACES to determine the APEX workspace names:
      SQL> SELECT WORKSPACE_ID FROM APEX_WORKSPACES WHERE WORKSPACE_ID > 100;
    3. After connecting to each PDB and compiling a list of workspace IDs, log out of SQL*Plus.
  7. Download a script that will be used to export the artifacts of the workspaces identified in the previous step into zip files:
    1. Create a directory for the workspace zip files:
      $ mkdir -p /home/oracle/workspaces
    2. Using wget, download the workspace_export_5.0.sh file from Oracle Storage Cloud Service.
      wget https://storage.us2.oraclecloud.com/v1/dbcsswlibp-usoracle29538/dbaas_patch/apex_upg5_1/workspace_export_5.0.sh or
      wget https://a88717.storage.oraclecloud.com/v1/Storage-a88717/dbaas_patch/apex_upg5_1/workspace_export_5.0.sh
    3. Move the script to the /home/oracle/workspaces directory.
    4. Change to the workspaces directory:
      $ cd /home/oracle/workspaces
    5. Assign permissions to the workspace_export_5.0.sh file:
      $ chmod 755 /home/oracle/workspaces/workspace_export_5.0.sh
      $ chown oracle:oinstall /home/oracle/workspaces/workspace_export_5.0.sh
  8. Temporarily unlock the APEX_050000 user and temporarily set the user’s password to oracle or a password of your choice. The script that you execute in a later step will reset the password.
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Unlock the user and set the password:
      SQL> ALTER USER APEX_050000 ACCOUNT UNLOCK IDENTIFIED BY password CONTAINER = ALL;
    3. Log out of SQL*Plus.
  9. Use Listener Control to determine the service name for each PDB
    $ lsnrctl status
    You should see output similar to:

    Service "pdb1.opcdbaas.oraclecloud.internal" has 1 instance(s).

    Instance "orcl", status READY, has 1 handler(s) for this service...

  10. For each workspace you identified in step 6, execute the workspace_export_5.0.sh script.
    ./workspace_export_5.0.sh //localhost:port_number/PDB_service_name APEX_050000 password workspace_id
    where:

    • port_number is the listener port number you specified when you created the database deployment (default is 1521).

    • PDB_service_name is one of the service names you identified in step 9.

    • password is the password you set for the APEX_050000 user in step 8b.

    • workspace_id is one of the APEX workspaces for the PDB that you identified in step 6b.

    One zip file will be created for each workspace.
  11. Remove APEX 5.0 from the root container (CDB$ROOT).
    1. Change to the directory that contains the APEX 5.0.0.00.31 or 5.0.4.00.12 files.
      cd $ORACLE_HOME/apex/
    2. Uninstall APEX.
      sqlplus / as SYSDBA @apxremov.sql
  12. Stop ORDS.
    /etc/init.d/ords stop
  13. Delete the APEX users for the previous version of APEX installed in the root container (CDB$ROOT).
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Query DBA_USERS.
      SQL> select username from dba_users where username like '%APEX_0%';
    3. Exit from SQL*Plus.
    4. Drop the users identified in step 13b.
      $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_user -- --x'drop user apex_user cascade'
      where apex_user is the value in the USERNAME column from step 13b.
  14. Remove the APEX users from all containers.
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_listener -- --x'drop user apex_listener cascade'
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_rest_public_user -- --x'drop user apex_rest_public_user cascade'
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_public_user -- --x'drop user apex_public_user cascade'
  15. Change to the new 5.1.0.00.45 directory
    cd /u01/app/oracle/product/apex/5.1.0.00.45
  16. Install APEX in the CDB seed (PDB$SEED).
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Set the mode of the CDB seed (PDB$SEED) to OPEN READ WRITE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;
    3. Connect to PDB$SEED.
      SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
    4. Install APEX 5.1 in the CDB seed (PDB$SEED) container
      SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
      SQL> exit
    5. Again, log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    6. Set the mode of the CDB seed (PDB$SEED) back to OPEN READ ONLY.
      SQL> ALTER SESSION SET "_oracle_script"=true;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
    7. Exit from SQL*Plus.
  17. Install APEX in each PDB you identified in step 5c.
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Connect to the PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    3. Install APEX 5.1 in the PDB.
      SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
    4. After repeating steps 17b and 17c for each PDB you identified in step 5c, exit from SQL*Plus.
    5. Log out of the compute node.
  18. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:
    1. Log in to the compute node as the opc user.
    2. Start a root-user shell:
      $ sudo -s
    3. Change to the ords directory:
      cd /var/opt/oracle/ocde/assistants/ords
    4. Execute the ORDS assistant:
      ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="install"
    5. Exit the root-user shell and log out of the compute node.
  19. Install each APEX workspace into a PDB.
    1. Log in to the compute node as the oracle user.
    2. Change to the workspaces directory where the zip files from step 10 are located.
      cd /home/oracle/workspaces
    3. Unzip each of the workspace zip files into its corresponding directory.
      unzip install_workspace_id_date.zip -d workspace_id
    4. Change to one of the newly created /home/oracle/workspaces/workspace_id directories.
    5. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    6. Connect to the PDB where the workspace should be installed. Refer to the list you compiled in step 6 to ensure you install each workspace in the correct PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    7. Execute the installation script to install the APEX workspace in the PDB.
      @install_workspace_id.sql
    8. After executing steps 19d through 19g for each APEX workspace, exit SQL*Plus.
    9. Log out of the compute node.

Upgrading from Oracle Application Express 5.1.0 or 5.1.3 to 5.1.4 for Oracle Database 11g

These instructions are applicable if you have an Oracle Database 11g database deployment and want to upgrade Oracle Application Express 5.1.0.00.45 or Oracle Application Express 5.1.3.00.05 to Oracle Application Express 5.1.4.00.08.

To upgrade from Oracle Application Express 5.1.0.00.45 or Oracle Application Express 5.1.3.00.05 to Oracle Application Express 5.1.4.00.08:
  1. Determine the version of your current Oracle Application Express installation:
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user and query DBA_REGISTRY:
      $ sqlplus / as sysdba
      SQL> SELECT VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
      If the APEX version is lower than 5.1.0.00.45, you must first upgrade to APEX 5.1. See Upgrading from Oracle Application Express 4.2 or 5.0 to 5.1 for Oracle Database 11g for details.
    3. Log out of SQL*Plus.
    4. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      $ rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+18.2.1.0.0_xxxxxx.xxxx.x86_64. Check the value between the + and _ for the version number. The tooling version must be 18.2.1.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Upload Oracle Application Express 5.1.4.00.08 to the compute node:
    1. Download Oracle Application Express 5.1.4.00.08 from Oracle Technology Network.
    2. Download Patch 26795231: PATCH SET FOR APPLICATION EXPRESS (PATCH SET VERSION 5.1.4).
    3. Log in to the compute node as the oracle user.
    4. Upload the Oracle Application Express 5.1.4 zip file to the /tmp directory on the compute node. See Copying Files to or from a Database Classic Cloud Service Database Deployment for instructions on how to copy files to the compute node.
    5. Upload the patch zip file to the /tmp directory on the compute node. See Copying Files to or from a Database Classic Cloud Service Database Deployment for instructions on how to copy files to the compute node.
  4. Unzip the patch zip file and the Oracle Application Express 5.1.4 zip file:
    1. Unzip the patch zip file.
      $ unzip p26795231_514_Generic.zip -d ./
    2. Create the directory required for Oracle Application Express:
      $ mkdir -p /u01/app/oracle/product/apex/
    3. Change to the /tmp directory where you uploaded the Oracle Application Express zip file.
    4. Unzip the uploaded Oracle Application Express zip file into the apex directory:
      $ unzip apex_5.1.4.zip -d /u01/app/oracle/product/apex/
  5. Install Oracle Application Express 5.1.4:
    1. Move the Oracle Application Express files to the /u01/app/oracle/product/apex/5.1.4.00.08/ directory:
      $ mv /u01/app/oracle/product/apex/apex/ /u01/app/oracle/product/apex/5.1.4.00.08/
    2. Optionally, delete the zip files:
      $ rm -f p26795231_514_Generic.zip
      $ rm -f apex_5.1.4.zip
    3. Change to the patch directory:
      $ cd ./patch
    4. Log in to SQL*Plus as the SYS user install the patch:
      $ sqlplus / as SYSDBA
      SQL> @apxpatch.sql
    5. Verify the version of APEX.
      SQL> SELECT VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
    6. Exit from SQL*Plus and log out of the compute node.
  6. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:
    1. Log in to the compute node as the opc user.
    2. Start a root-user shell:
      $ sudo -s
    3. Change to the ords directory:
      # cd /var/opt/oracle/ocde/assistants/ords
    4. Execute the ORDS assistant:
      # ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="configure_apex"
    5. Restart ORDS:
      # /etc/init.d/ords restart
    6. Exit the root-user shell and log out of the compute node.

Upgrading from Oracle Application Express 5.1.0 or 5.1.3 to 5.1.4 for Oracle Database 12c and Oracle Database 18c

These instructions are applicable if you have an Oracle Database 12c or Oracle Database 18c database deployment and want to upgrade Oracle Application Express 5.1.0.00.45 or Oracle Application Express 5.1.3.00.05 to Oracle Application Express 5.1.4.00.08.

To upgrade from Oracle Application Express 5.1.0.00.45 or Oracle Application Express 5.1.3.00.05 to Oracle Application Express 5.1.4.00.08:
  1. Determine the version of your current Oracle Application Express installation:
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    3. If the Oracle Database version is 12.1.0.2, execute the following command to include PDB$SEED in queries:
      SQL> ALTER SESSION SET "EXCLUDE_SEED_CDB_VIEW"=FALSE;
    4. If the Oracle Database version is 12.2.0.1 or 18.1.0.0.0, execute the following command to include PDB$SEED in queries:
      SQL> ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW"=FALSE;
    5. Execute the following query:
      SQL> SELECT R.CON_ID, P.NAME, R.VERSION, R.STATUS
  2  FROM SYS.CDB_REGISTRY R INNER JOIN V$PDBS P ON R.CON_ID = P.CON_ID
  3  WHERE R.COMP_ID = 'APEX' ORDER BY 1;
      If the APEX version is lower than 5.1.0.00.45, you must first upgrade to APEX 5.1. See Upgrading from Oracle Application Express 5.0 to 5.1 for Oracle Database 12c for details.
    6. Log out of SQL*Plus.
    7. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      $ rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+18.2.1.0.0_xxxxxx.xxxx.x86_64. Check the value between the + and _ for the version number. The tooling version must be 18.2.1.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Upload Oracle Application Express 5.1.4.00.08 to the compute node:
    1. Download Oracle Application Express 5.1.4.00.08 from Oracle Technology Network.
    2. Download Patch 26795231: PATCH SET FOR APPLICATION EXPRESS (PATCH SET VERSION 5.1.4).
    3. Log in to the compute node as the oracle user.
    4. Upload the Oracle Application Express 5.1.4 zip file to the /tmp directory on the compute node. See Copying Files to or from a Database Classic Cloud Service Database Deployment for instructions on how to copy files to the compute node.
    5. Upload the patch zip file to the /tmp directory on the compute node. See Copying Files to or from a Database Classic Cloud Service Database Deployment for instructions on how to copy files to the compute node.
  4. Unzip the patch zip file and the Oracle Application Express 5.1.4 zip file:
    1. Unzip the patch zip file.
      $ unzip p26795231_514_Generic.zip -d ./
    2. Create the directory required for Oracle Application Express:
      $ mkdir -p /u01/app/oracle/product/apex/
    3. Change to the /tmp directory where you uploaded the Oracle Application Express zip file.
    4. Unzip the uploaded Oracle Application Express zip file into the apex directory:
      $ unzip apex_5.1.4.zip -d /u01/app/oracle/product/apex/
  5. Install Oracle Application Express 5.1.4:
    1. Move the Oracle Application Express files to the /u01/app/oracle/product/apex/5.1.4.00.08/ directory:
      $ mv /u01/app/oracle/product/apex/apex/ /u01/app/oracle/product/apex/5.1.4.00.08/
    2. Optionally, delete the zip files:
      $ rm -f p26795231_514_Generic.zip
$ rm -f apex_5.1.4.zip
    3. Change to the patch directory:
      $ cd ./patch
    4. Log in to SQL*Plus as the SYS user to install the patch in PDB$SEED:
      $ sqlplus / as SYSDBA 
SQL> ALTER SESSION SET "_oracle_script"=true;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;
SQL> ALTER SESSION SET CONTAINER = pdb$seed;
SQL> @apxpatch.sql
SQL> ALTER SESSION SET CONTAINER = cdb$root;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
    5. Install the patch in each PDB you identified in step 1.e:
      SQL> ALTER SESSION SET CONTAINER = PDB_NAME;
      SQL> @apxpatch.sql
  6. Verify that the version of APEX installed in each PDB is 5.1.4.00.08.
    1. If the Oracle Database version is 12.1.0.2, execute the following command to include PDB$SEED in queries:
      SQL> ALTER SESSION SET "EXCLUDE_SEED_CDB_VIEW"=FALSE;
    2. If the Oracle Database version is 12.2.0.1 or 18.1.0.0.0, execute the following command to include PDB$SEED in queries:
      SQL> ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW"=FALSE;
    3. Execute the following query:
      SQL> SELECT R.CON_ID, P.NAME, R.VERSION, R.STATUS
  2  FROM SYS.CDB_REGISTRY R INNER JOIN V$PDBS P ON R.CON_ID = P.CON_ID
  3  WHERE R.COMP_ID = 'APEX' ORDER BY 1;
    4. Log out of SQL*Plus.
  7. Optionally, delete the patch files:
    $ cd ..
    $ rm -rf ./patch
  8. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:
    1. Log in to the compute node as the opc user.
    2. Start a root-user shell:
      $ sudo -s
    3. Change to the ords directory:
      # cd /var/opt/oracle/ocde/assistants/ords
    4. Execute the ORDS assistant:
      # ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="configure_apex"
    5. Restart ORDS:
      # /etc/init.d/ords restart
    6. Exit the root-user shell and log out of the compute node.

Upgrading from Oracle Application Express 5.1.0 or 5.1.3 or 5.1.4 to 18.1.0 for Oracle Database 12.2 and Oracle Database 18c

These instructions are applicable if you have an Oracle Database 12.2 or Oracle Database 18c database deployment and want to upgrade Oracle Application Express 5.1.0.00.45 or Oracle Application Express 5.1.3.00.05 or Oracle Application Express 5.1.4.00.08 to Oracle Application Express 18.1.0.00.45.

To upgrade from Oracle Application Express 5.1.0.00.45 or Oracle Application Express 5.1.3.00.05 or Oracle Application Express 5.1.4.00.08 to Oracle Application Express 18.1.0.00.45:
  1. Determine the version of your current Oracle Application Express installation:
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    3. If the Oracle Database version is 12.2.0.1 or 18.1.0.0.0, execute the following command to include PDB$SEED in queries:
      SQL> ALTER SESSION SET "_EXCLUDE_SEED_CDB_VIEW"=FALSE;
    4. Execute the following query:
      SQL> SELECT CON_ID, VERSION, STATUS, 
  2  SCHEMA FROM CDB_REGISTRY
  3  WHERE COMP_ID = 'APEX' ORDER BY CON_ID ASC;
      Sample output:
      CON_ID VERSION          STATUS      SCHEMA
     ----------------------------------------------------     
     2 5.1.4.00.08           VALID       APEX_050100 
     3 5.1.4.00.08           VALID       APEX_050100
      If the APEX version is lower than 5.1.0.00.45, you must first upgrade to APEX 5.1. See Upgrading from Oracle Application Express 5.0 to 5.1 for Oracle Database 12c for details.
    5. Log out of SQL*Plus.
    6. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      $ rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+18.2.1.0.0_xxxxxx.xxxx.x86_64. Check the value between the + and _ for the version number. The tooling version must be 18.2.1.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Upload Oracle Application Express 18.1.0.00.45 to the compute node:
    1. Download Oracle Application Express 18.1.0.00.45 from Oracle Technology Network.
    2. Download the APEX 18.1 Archive.
    3. Log in to the compute node as the oracle user.
    4. Upload the Oracle Application Express 18.1.0.00.45 zip file to the /tmp directory on the compute node. See Copying Files to or from a Database Classic Cloud Service Database Deployment for instructions on how to copy files to the compute node.
    5. Upload the patch zip file to the /tmp directory on the compute node. See Copying Files to or from a Database Classic Cloud Service Database Deployment for instructions on how to copy files to the compute node.
  4. Unzip the Oracle Application Express 18.1.0.00.45 zip file:
    1. Create the directory required for Oracle Application Express:
      $ mkdir -p /u01/app/oracle/product/apex/
    2. Change to the /tmp directory where you uploaded the Oracle Application Express zip file.
    3. Unzip the uploaded Oracle Application Express zip file into the apex directory:
      $ unzip apex_18.1.zip -d /u01/app/oracle/product/apex/
    4. Move the files into the Oracle Application Express 18.1.0.00.45:
      $ mv /u01/app/oracle/product/apex/apex/ /u01/app/oracle/product/apex/18.1.0.00.45/
    5. If the Oracle Application Express you are installing is the latest version you have on your system, run the following command as the oracle user:
      $ ln -sfn /u01/app/oracle/product/apex/18.1.0.00.45 /u01/app/oracle/product/apex/.latest_provisioned
    6. Optionally, delete the zip file:
      $ rm -f apex_18.1.zip
    7. Change to the directory where you moved the zip files:
      $ cd /u01/app/oracle/product/apex/18.1.0.00.45
  5. Use SQL*Plus to make sure you can log into the ORDS_PUBLIC_USER schema.
    In the sample session below, use the password for the ORDS_PUBLIC_USER schema. By default, this password should be the same as the password you entered for the SYS user when you deployed your DBCS instance. Make sure you can login with this password by executing the following as the oracle user.
    $ sqlplus /nolog
SQL> CONNECT ORDS_PUBLIC_USER
Password:
Connected.
SQL>
    In the following commands, <ORDS_PUBLIC_USER_PASSWORD> refers to the password you used here.
  6. Install Oracle Application Express: :
    1. Execute the following command with SQL*Plus
      $ ALTER SESSION SET CONTAINER=<CONTAINER_NAME>;
$@dbcsins.sql SYSAUX SYSAUX TEMP /i/18.1.0.00.45/ <ORDS_PUBLIC_USER_PASSWORD>
    2. Verify that the installation went fine with the following command, using the same SQL*Plus session or running the command in the same container you installed Oracle Application Express.
      $ SELECT VERSION, STATUS, SCHEMA FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
  7. Create a link to the APEX images in ORDS docroot by executing the below command as the oracle user:
    ALTER SESSION SET CONTAINER=CDB$ROOT;
    ALTER SESSION SET "_oracle_script"=true;
    ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
    ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;

Moving Oracle Application Express 5.1 from CDB$ROOT to PDBs

These instructions are applicable if you have an Oracle Database 12c Release 1 or Release 2 database deployment and you have previously upgraded to Oracle Application Express 5.1 in the root container (CDB$ROOT). These instructions tell you how to move Oracle Application Express to the pluggable databases (PDBs).

To move Oracle Application Express 5.1 from the root container (CDB$ROOT) to the pluggable databases (PDBs):
  1. Determine the version of Oracle Application Express installed in the root container (CDB$ROOT):
    1. Log in to the compute node as the oracle user.
    2. Log in to SQL*Plus as the SYS user and query DBA_REGISTRY:
      $ sqlplus / as sysdba
      SQL> SELECT VERSION FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
      The version should be 5.1.0.00.45. If the version is 5.0.0.00.31 or 5.0.4.00.12, see Upgrading from Oracle Application Express 5.0 to 5.1 for Oracle Database 12c. If the version is 4.2.5.00.08, see Upgrading from Oracle Application Express 4.2 to 5.1 for Oracle Database 12c.
    3. Log out of SQL*Plus.
    4. Log out of the compute node.
  2. Ensure that you have the required version of the cloud tooling:
    1. Log in to the compute node as the opc user.
    2. Check the version of the cloud tooling:
      rpm -q dbaastools
      You should see something similar to this: dbaastools-1.0-1+17.3.1.0.0_170605.2102.x86_64. Check the value between the + and _ for the version number. The tooling version must be 17.2.5.0.0 or higher. If your cloud tooling is at a lower version, see Updating the Cloud Tooling on Database Classic Cloud Service for the steps to update to a later version.
  3. Ensure the APEX 5.1.0.00.45 files are located in either the $ORACLE_HOME/apex directory or in the /u01/app/oracle/product/apex/5.1.0.00.45 directory.
    $ cat $ORACLE_HOME/apex/images/apex_version.txt
    $ cat /u01/app/oracle/product/apex/5.1.0.00.45/images/apex_version.txt
    The output from one of the commands should be Application Express Version: 5.1.
  4. Determine which PDBs have an APEX instance:
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Execute the query to determine which PDBs have an APEX instance:
      SQL> SELECT P.PDB_NAME, R.VERSION, R.STATUS
      2 FROM SYS.DBA_PDBS P, SYS.CDB_REGISTRY R
      3 WHERE P.PDB_ID = R.CON_ID AND R.COMP_ID = 'APEX'
      4 ORDER BY 1;
  5. In each PDB, identify the APEX workspaces. Be sure to make note of which workspaces belong to which PDBs. You will need this information in a later step.
    1. While still logged in to SQL*Plus, connect to each PDB by using the ALTER SESSION command:
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    2. Query APEX_WORKSPACES to determine the APEX workspace names:
      SQL> SELECT WORKSPACE_ID FROM APEX_WORKSPACES WHERE WORKSPACE_ID > 100;
    3. After connecting to each PDB and compiling a list of workspace IDs, log out of SQL*Plus.
  6. Download a script that will be used to export the artifacts of the workspaces identified in the previous step into zip files:
    1. Create a directory for the workspace zip files:
      $ mkdir -p /home/oracle/workspaces
    2. Using wget, download the workspace_export_5.1.sh file from Oracle Storage Cloud Service.
      wget https://storage.us2.oraclecloud.com/v1/dbcsswlibp-usoracle29538/dbaas_patch/apex_upg5_1/workspace_export_5.1.sh or
      wget https://a88717.storage.oraclecloud.com/v1/Storage-a88717/dbaas_patch/apex_upg5_1/workspace_export_5.1.sh
    3. Move the script to the /home/oracle/workspaces directory.
    4. Change to the workspaces directory:
      $ cd /home/oracle/workspaces
    5. Assign permissions to the workspace_export_5.1.sh file:
      $ chmod 755 /home/oracle/workspaces/workspace_export_5.1.sh
      $ chown oracle:oinstall /home/oracle/workspaces/workspace_export_5.1.sh
  7. Temporarily unlock the APEX_050100 user and temporarily set the user’s password to oracle or a password of your choice. The script that you execute in a later step will reset the password.
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Unlock the user and set the password:
      SQL> ALTER USER APEX_050100 ACCOUNT UNLOCK IDENTIFIED BY password CONTAINER = ALL;
    3. Log out of SQL*Plus.
  8. Use Listener Control to determine the service name for each PDB
    $ lsnrctl status
    You should see output similar to:

    Service "pdb1.opcdbaas.oraclecloud.internal" has 1 instance(s).

    Instance "orcl", status READY, has 1 handler(s) for this service...

  9. For each workspace you identified in step 5, execute the workspace_export_5.1.sh script.
    ./workspace_export_5.1.sh //localhost:port_number/PDB_service_name APEX_050000 password workspace_id
    where:

    • port_number is the listener port number you specified when you created the database deployment (default is 1521).

    • PDB_service_name is one of the service names you identified in step 8.

    • password is the password you set for the APEX_050100 user in step 7b.

    • workspace_id is one of the APEX workspaces for the PDB that you identified in step 5b.

    One zip file will be created for each workspace.
  10. Change to the directory where the APEX 5.1.0.00.45 files are located, as you determined in step 3.

    • If the files are located in $ORACLE_HOME/apex/: cd $ORACLE_HOME/apex/

    • If the files are located in /u01/app/oracle/product/apex/5.1.0.00.45/: cd /u01/app/oracle/product/apex/5.1.0.00.45/

  11. Delete the APEX users for the previous version of APEX installed in the root container (CDB$ROOT).
    1. Log in to SQL*Plus as the SYS user:
      $ sqlplus / as sysdba
    2. Query DBA_USERS.
      SQL> select username from dba_users where username like '%APEX_0%';
    3. Exit from SQL*Plus.
    4. Drop all of the users identified in step 11b except for the APEX_050100 user.
      $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_user -- --x'drop user apex_user cascade'
      where apex_user is the value in the USERNAME column from step 11b. Do not drop the APEX_050100 user.
  12. Remove APEX from the root container (CDB$ROOT).
    1. Execute the script to uninstall APEX.
      sqlplus / as SYSDBA @apxremov.sql
  13. Stop ORDS.
    /etc/init.d/ords stop
  14. Remove the APEX users from all containers.
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_listener -- --x'drop user apex_listener cascade'
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_rest_public_user -- --x'drop user apex_rest_public_user cascade'
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex_public_user -- --x'drop user apex_public_user cascade'
  15. Install APEX in the CDB seed (PDB$SEED).
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Set the mode of the CDB seed (PDB$SEED) to OPEN READ WRITE.
      SQL> ALTER SESSION SET "_oracle_script"=true;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE;
    3. Connect to PDB$SEED.
      SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
    4. Install APEX 5.1 in the CDB seed (PDB$SEED) container
      SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
      SQL> exit
    5. Again, log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    6. Set the mode of the CDB seed (PDB$SEED) back to OPEN READ ONLY.
      SQL> ALTER SESSION SET "_oracle_script"=true;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
      SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;
    7. Exit from SQL*Plus.
  16. Install APEX in each PDB you identified in step 4c.
    1. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    2. Connect to the PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    3. Install APEX 5.1 in the PDB.
      SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
    4. After repeating steps 16b and 16c for each PDB you identified in step 4b, exit from SQL*Plus.
    5. Log out of the compute node.
  17. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:
    1. Log in to the compute node as the opc user.
    2. Start a root-user shell:
      $ sudo -s
    3. Change to the ords directory:
      cd /var/opt/oracle/ocde/assistants/ords
    4. Execute the ORDS assistant:
      ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="install"
    5. Exit the root-user shell and log out of the compute node.
  18. Install each APEX workspace into a PDB.
    1. Log in to the compute node as the oracle user.
    2. Change to the workspaces directory where the zip files from step 9 are located.
      cd /home/oracle/workspaces
    3. Unzip each of the workspace zip files into its corresponding directory.
      unzip install_workspace_id_date.zip -d workspace_id
    4. Change to one of the newly created /home/oracle/workspaces/workspace_id directories.
    5. Log in to SQL*Plus as the SYS user.
      $ sqlplus / as sysdba
    6. Connect to the PDB where the workspace should be installed. Refer to the list you compiled in step 5 to ensure you install each workspace in the correct PDB.
      SQL> ALTER SESSION SET CONTAINER = PDB_name;
    7. Execute the installation script to install the APEX workspace in the PDB.
      @install_workspace_id.sql
    8. After executing steps 19d through 19g for each APEX workspace, exit SQL*Plus.
    9. Log out of the compute node.