Cloning oracle databases from production to test environments is a common practice, but it comes with its own set of challenges, particularly in handling critical APEX user passwords. Encountering an “ORDS 404 Not Found” error can be a perplexing experience for those using Oracle REST Data Services (ORDS) for APEX. The message showing that the “requested resource could not be located”, leaves users and administrators in search of a solution and ending up with re-installing APEX and ORDS.
In this blog, we explore two scenarios encountered during the post-clone phase and provide practical steps to ensure a seamless cloning of APEX environment. From maintaining identical passwords to resetting them in the test environment, let’s delve into the best practices and considerations for seamless APEX cloning.
Scenario 1:
If both the APEX software and ORDS configuration directories remain unchanged in the target environment, and only the database (containing the APEX schema and its applications) has been restored from PROD to TEST, your intention is to maintain identical critical APEX passwords in the TEST environment.
Post clone steps in Target:
As the database was cloned from the production environment to the test environment, it retains the production passwords for critical APEX users at the database level. This results in a disparity between the APEX passwords stored in the Oracle database and those hashed in the ORDS configuration directory (apex.xml, apex_pu.xml, apex_rt.xml, apex_al.xml).
If your intention is to maintain the same passwords in the target environment, follow the steps below to reset the usernames. Avoid making any changes or modifications to the ORDS configuration directory and its XML contents.
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_200100 IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_LISTENER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_INSTANCE_ADMIN_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK;
This will complete the apex cloning, as now the TEST APEX passwords are same with respect to database and ORDS Conf directory
Scenario 2:
If both the APEX software and ORDS configuration directories are unchanged in the target environment, and only the database (containing the APEX schema and its applications) has been restored from PROD to TEST, your intention is to reset the critical APEX passwords in the TEST environment.
Post clone steps in Target:
As the database was cloned from the production environment to the test environment, it retains the production passwords for critical APEX users at the database level. Now, the intention is to establish new passwords for all Critical Apex usernames in the cloned target. This configuration should be synchronized with the physical configuration files (apex.xml, apex_pu.xml, apex_rt.xml, apex_al.xml).
In this case, reset the passwords using the below command.
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_200100 IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_LISTENER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER APEX_INSTANCE_ADMIN_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK; ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY "myprdap3x" ACCOUNT UNLOCK;
And then passwords needs to be updated in ORDS configuration directory.
Find the conf directory of ORDS (from the tomcat log file). Ex: /u01/app/ords-3.0.9/ords-3.0.9-conf/apex/conf
Run the below command and answer the prompted values. This will create the fresh new xml files ((apex.xml,apex_pu.xml,apex_rt.xml,apex_al.xml) in ORDS conf directory.
username@apexhost:/u01/app/ords-3.0.9 username@apexhost$ java -jar ords.war setup
Conclusion:
We discussed the importance of aligning database-level passwords with those hashed in the ORDS configuration directory and provided step-by-step guidance on resetting passwords in the test environment. By following these steps and avoiding unnecessary modifications to configuration files, you can streamline the process of APEX with ORDS cloning from Source to Target.
