Recover accidentally deleted APEX components: Flashback Export

Accidentally deleting an Application Express page or shared component (or even a complete application) can be a real pain for the developer. Even a simple component, like an LOV, might rely on a complex SQL query, which is hard to recover manually.

When you now think "Oh, no problem, the operations department regularly backs up the APEX database", hold on a moment: What would that mean? The DBA would have to recover the (physical) database backup (to another system, because the APEX database must remain up and running). Then this temporary database has to be started, one has to log into the Application Express workspace and export the application. It's obvious that this is not only a lengthy but also a very expensive process.

The good news is: If you act quickly, you can recover your application or component yourself. The Flashback Option within the Application Express export functionality, helps you with this. You can use it for Application, Page or Component exports.

Use the "Flashback Option" while exporting an APEX application or component

This functionality relies on the Flashback Query technology, which is part of the Oracle database since Oracle9i back in 2001. Flashback Query allows to use the AS OF clause in a SQL query executing that query "in the past". Flashback Query gets this history data from the Undo Tablespace - more detailed information about the technical background can be found in the Oracle documentation: Overview of Oracle Flashback Technology.

Flashback query is limited to the historic data in the Undo Tablespace - on a database installation on a private laptop, flashback queries can get back several hours into the past - on a production system or on a central development instance, one should not expect more than five to 15 minutes. So, to recover your accidentally deleted component using that technology, you should act quickly.

The first thought, after the page has been deleted, is to navigate to another page and, from there, navigate to Export Page. But as the following screenshot shows, the page to be exported is chosen from a select list. The deleted page is, of course, not part of that list any more.

A select list allows to choose the page to be exported

But there is a simple trick for that: Just create a new page with the same page number as the deleted one. Create it as a Blank Page and accept all defaults - you want to get through this wizard as fast as possible. When the page has been created, navigate to Export Page again, choose the new "dummy" page, set the Flashback Time and export.

Export the new dummy page with the flashback option

After that, navigate to Import and import the page you just have exported, back into the application.

Import the exported page, back into the application

Application Express will then tell you that the page you are about to import already exists. Confirm to replace it.

Confirm to replace the dummy page with the imported page

After that, you have got your old page back - all regions are recovered.

If you also deleted Shared Components (list or breadcrumb entries) together with your application page, these will not be recovered with this approach. To recover these as well, it's best to export the whole application with the flashback option - not just one page.

Export the whole application with the flashback option

You have two alternatives for importing your application back: One option is to import with the ID of the existing application. You will again be prompted to confirm the application replacement. After that, your application will be reset to the state it had at the time you have chosen with the flashback option. All lost components are recovered. But intended changes will also be rolled back - which might not be exactly what you wanted.

The second option is to import the application with a new ID. You will then have your original application and a copy, as it was at flashback time - the "flashback application". Components can be looked up in the flashback application, and re-created in the original application - either manually or by creating components as a copy of an existing component.

Create a component - as a copy from the flashback application

Accidentally deleting a component in Application Express is not necessarily a disaster. If you recognize your error quickly, it's very easy to get your work back - all you have to do is an application or component export with the flashback option.

The optimal solution is, of course, to do application exports automatically at a regular schedule. A daily job could export all applications and check the files into a source control system like Subversion, CVS or Git. Developers then even don't need to be quick when they lost some work; they just look up the last version from source control and import it back. That would allow to recover components which have been deleted days or even months before.

blogs.oracle.com/apex