I haven't received much traction, so I'm reformulating it as a recommendation. Perhaps I'll get more feedback in this form. I'll also point out an element I think is a bug in APEX...you'll have to read to the end to see that.
It is a common practice to clone a production database and utilize the clone for testing or development purposes. This happens a great deal with eBusiness Suite implementations, but also with many other installations. Below is a sample list of steps (with limited guidance) that should be done to avoid side effects with APEX applications.
- Use RMAN or your favorite technology to backup and restore the production database, but DO NOT START.
- Change the database SID and name if not done above.
- Set JOB_QUEUE_PROCESSES to 0. This is a key step to make sure that when you start the database things don't start "happening."
- Start the database.
- Assuming you are running a runtime only environment in Production, you will likely want to install the APEX builder in this new clone. Run apxdvins.sql to upgrade the runtime into a full development environment.
- Log into the INTERNAL workspace and modify instance settings: Instance URL, Image URL, SMTP server settings (if you wish to use a different SMTP server), Print Server settings, any other settings you want.
- Navigate to the Manage Instance > Mail Queue and delete anything in the queue. The clone may have happened while things were in the queue.
- Manage Instance > Interactive Report Descriptions: Delete all of the Interactive Report subscriptions. This is also a key step to ensure that you don't have emails going out to production users from your development or test environment.
- Manage Instance > Session State: Purge all session state. There could be sensitive production data that you don't want left around in session state.
- Modify any settings specific to your own applications, e.g. web service URLs, lookup values, etc.
- Reset JOB_QUEUE_PROCESSES to appropriate value.
It would also be great to have Oracle provide a script that does the above things (with the exception of #10, of course).
I promised to call out a bug. Item #7 should delete all of the interactive report subscriptions, but it doesn't--at least not in APEX v5.0.4.00.12. The list of report subscriptions skips any application
where build_status = 'RUN_AND_HIDDEN'
This is (at least) packaged applications that have not been "unlocked." It turns out that deleting these subscriptions is NOT EASY. I originally thought a script like this might do it:
-- this would need to be done in each workspace because it uses the workspace views
for irRec in (select notify_id from APEX_APPLICATION_PAGE_IR_SUB) loop
p_subscription_id => irRec.notify_id);
Unfortunately, APEX_APPLICATION_PAGE_IR_SUB doesn't see the subscriptions. It has the same issue as the page--it won't show subscriptions for applications with build_status = 'RUN_AND_HIDDEN'.
I tried a few other things, but in the end, the only way I could get rid of these was to just delete them from the underlying table, run as the APEX_050000 schema:
delete from wwv_flow_worksheet_notify;