About a year ago I asked a question about cloning Oracle Databases in the APEX section of the Oracle Community. The question is here (no need to click the link, all of the content is also in this blog post):

community.oracle.com/thread/3786014

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.

1. Use RMAN or your favorite technology to backup and restore the production database, but DO NOT START.
2. Change the database SID and name if not done above.
3. 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."
4. Start the database.
5. 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.
6. 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.
7. Navigate to the Manage Instance > Mail Queue and delete anything in the queue. The clone may have happened while things were in the queue.
8. 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.
9. Manage Instance > Session State: Purge all session state. There could be sensitive production data that you don't want left around in session state.
10. Modify any settings specific to your own applications, e.g. web service URLs, lookup values, etc.
11. 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
begin

for irRec in (select notify_id from APEX_APPLICATION_PAGE_IR_SUB) loop

APEX\_IR.DELETE\_SUBSCRIPTION(  
  p\_subscription\_id =>  irRec.notify\_id);  

end loop;

end;

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;