Automating APEX Applications Backup

In a previous post, I discussed that the APEXExport tool may be the best one to use when exporting APEX applications. You may also have experience using this tool. Today, I’d like to present some alternatives to the APEXExport tool.

Plan A

Use the same procedure as discussed in the previous post for exporting all applications in a database to another, keeping in mind, for any backup script, three parts need to be backed up.

1. Application schemas need to be backed up via normal/traditional database export utilities (Data Pump or legacy Export, be aware of the limitations of each) to generate the dump file your APEX applications need to run:

expdp matheusdba schemas=MY_APP_SCHEMA directory=DIR_BKP dumpfile=APEX_APP_SCHEMA.dmp logfile=APEX_APP_SCHEMA.log

2. Run the APEXExport using “-expWorkspace” to export all workspaces (This will generate a w*.sql script for each workspace).

java oracle.apex.APEXExport -db localhost:1521:MYDB -user system -password systems_password -expWorkspace

Note: You may want to download a separate java version to be used on the call or ojdbc, as also referenced here.

3. Run the APEXExport using “-instance” which will generate a f*.sql script for every application and shared component.

java oracle.apex.APEXExport -db localhost:1521:MYDB -user system -password systems_password -instance

Plan B

Take separate backups for each application or workspace.

Here is the interesting part—you can take also backups by applicationId or workspaceId, in the following ways:

1. Exporting by applicationId:

APEXExport -db hostname:listenerport:dbservicename -user <username> -password <password> -applicationid 31500

2. Exporting by workspaceId:

APEXExport -db hostname:listenerport:dbservicename -user <username> -password <password> -workspaceid 9999

But how can you take all the workspaceIds? You can use the following SQL statement in SQL Workshop:

select wwv_flow_api.get_security_group_id from dual;

Or, alternatively, you can list all the workspaces and apps with SQL*Plus with:

SELECT workspace,workspace_id,application_id, application_name FROM apex_applications;

Now that you have all the basic exporting steps, it’s up to you to build the script in the way that works best for you.

I hope this helps!

Additional notes:

• The workspace export should export all of the shared components from the workspaces.
• This does not mention RESTful services but if using the APEXExport from 4.2.4 or higher, they will be included.

Additional Reference:

• APEXExport Gives: Java.lang.NoClassDefFoundError: Oracle/apex/APEXExport (Doc ID 2265534.1).

As always, if you have questions or thoughts, please feel free to leave them in the comments.