Backing up your applications

There have been a few posts on the Application Express forums lately from people who want to either export lots of applications or are looking for a way to automate the task of exporting applications.

Most (if not all) APEX developers are familiar with the application export functionality from within the Application Builder interface itself:

Application Export

However you may not be aware that tucked away in the Application Express install that you downloaded are a couple of utilities that you can also use to export your applications, infact the utilities can do much more than that, you can use them to export all the applications within a workspace or even all the applications within the instance.

If you have already deleted the Application Express installation files after successfully installing it, then you’ll need to download it again (available from here) because I don’t believe these files are available individually anywhere else.

The files can be found in the utilities subdirectory of the root installation directory, note that I’m using a Unix machine here (well actually an Apple powerbook), however due to the ‘portability’ of Java applications they will also work quite happily on other operating system, assuming of course that you follow the directions in the readme.txt file to setup your Java environment which if you already have Java installed should only involve setting up your Java CLASSPATH environment variable like this –

export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/classes12.zip

Obviously however you should take the time to read the full readme.txt file.
Root directory

There are two java utilities available for you to use APEXExport and APEXExportSplitter which both reside in the oracle/apex subdirectory.
APEXExport files

Executing the APEXExport program without any additional parameters will show you some default usage help.
APEXExport Command

As you can see, the usage is pretty intuitive, if I wanted to export application 103, all I would need to do is –

Exporting app 103

If we now look in the current working directory, you will find a file called f103.sql which you may recognise as being the same type of file which is produced by the export functionality in the Application Builder, in other words you can import this f103.sql file by using the Application Builder tools. You can also import the application by running the SQL script file whilst connected as your FLOWS user if you prefer to perform the import at the command line (or perhaps as part of a batch import).

We can also export all of the applications for a particular workspace, unfortunately we need to do a little bit of work here because the APEXExport utility requires the workspace id rather than the workspace name (it would be a nice feature if we could just pass in the workspace name instead), however it’s quite simple to get your workspace id by running the query

select v(‘WORKSPACE_ID’) from dual

in SQL Workshop whilst connected to your Workspace, this query will return a numeric id which you can then use with the APEXExport command, like this –
Exporting workspace apps
As you can see, it took around 2 seconds to export all 5 applications from my workspace, which is certainly quicker than doing it via the Application Builder interface. Also note that each application is exported into its own seperate file, rather than a single file.

All application exports

Lastly, you can also export all the applications in the entire instance, which I won’t show here since it should now be pretty obvious how that works.

You can do so many different things with this sort of commandline export functionality, for example you could quite easily create a Unix cron job to export your applications each night at midnight as an extra backup. Or, if you’re more adventurous you could schedule a job to export the applications, see if they’ve changed (using the Unix ‘diff’ command perhaps) since the last export and if they have then check them into your source control repository, you could also automate an export of your applications and email them automatically to a remote offsite location.

This kind of commandline scripting and control is why I love using Unix so much, with just a few simple commands I can automate so many of the manual tasks to make my life easier and it also means that I can be confident of also having a recent backup of my applications nearby if I should ever need them.

Next post I’ll show the APEXExportSplitter in action.

35 thoughts on “Backing up your applications

  1. Patrick Wolf

    Hi John,

    good intruduction to the export utility. I think there a lot of people who don’t know about that.

    BTW, I noticed in your posting that you have several links in your posting, but they don’t render as links as long as you don’t move the mouse over them. So you really have to be lucky to find this links 🙂

    Greetings from Vienna
    Patrick
    ———————————————————–
    Check out my APEX-Blog: http://inside-apex.blogspot.com

    Like

    Reply
  2. John Scott

    Hi Patrick,

    Thanks for spotting that, I’ve changed the link colours in the CSS now so that links should be slightly easier to spot.

    Thanks,

    John.

    Like

    Reply
  3. Floris Vermeir

    Its quite intresting. I have exported an application, well sort of, just to try out the export function in apex. It does so quite well, yet the location it exports to is a mistery. So with these tools, I could specify that location. Its good to know they exist.

    Like

    Reply
  4. Geert Joosten

    Hi John,

    This is the proof that’s worth to keep old posts available: what a nice tool and clear written explanation.
    Thanks,
    Geert.

    Like

    Reply
  5. Cazzonio

    I put APEXExport.class in the working directory, but when i command “java oracle.apex.APEXExport” i get the following error:
    Exception in thread “main” java.lang.NoClassDefFoundError: oracle/apex/APEXExpor
    t
    Caused by: java.lang.ClassNotFoundException: oracle.apex.APEXExport
    at java.net.URLClassLoader$1.run(Unknown Source)
    at ……

    Like

    Reply
  6. Bill Johnson

    I’m trying to run APEXExport on RHEL4. I have the $CLASSPATH set:
    .:/home/oracle/oracle/product/10.2.0/db_1/jdbc/lib/classes12.zip

    I can run java oracle/apex/APEXExport just fine, and it displays the usage info. If I try to run:
    java oracle/apex/APEXExport -db host:port:SID -user apex -password apex -instance (or any other flag), I get:

    Exception in thread “main” java.lang.AbstractMethodError
    at _Jv_MakeVTable(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
    at _Jv_PrepareClass(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
    at _Jv_WaitForState(java.lang.Class, int) (/usr/lib/libgcj.so.5.0.0)
    at java.lang.VMClassLoader.linkClass0(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
    at java.lang.VMClassLoader.resolveClass(java.lang.Class) (/usr/lib/libgcj.so.5.0.0)
    at java.lang.Class.initializeClass() (/usr/lib/libgcj.so.5.0.0)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.util.Properties) (Unknown Source)
    at oracle.jdbc.driver.OracleDriver.connect(java.lang.String, java.util.Properties) (Unknown Source)
    at java.sql.DriverManager.getConnection(java.lang.String, java.util.Properties) (/usr/lib/libgcj.so.5.0.0)
    at java.sql.DriverManager.getConnection(java.lang.String, java.lang.String, java.lang.String) (/usr/lib/libgcj.so.5.0.0)
    at oracle.apex.APEXExport.main(java.lang.String[]) (Unknown Source)

    What am I missing?

    Thanks for this excellent tutorial BTW. I hope I can make it work for us, for it will really solve a big problem.

    Bill

    Like

    Reply
  7. Bill Johnson

    OK – forget that last post. Discovered I was not using Sun JDK, but rather the open java equivalent. Switched to Sun’s java and I’m getting farther.

    Like

    Reply
  8. smallkoala

    Hi John
    Thanks for your introduction.I am trying this solution.I believe my CLASSPATH is correct.But sometimes(not always) when I run java oracle.apex.APEXExport -db APEXSERVER:1521:apexdb -user apexusr -password apexpwd -applicationid 1111,I get the following error:
    Exception in thread “main” java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:254)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:386)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:419)
    at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:164)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:752)
    at java.sql.DriverManager.getConnection(DriverManager.java:525)
    at java.sql.DriverManager.getConnection(DriverManager.java:171)
    at oracle.apex.APEXExport.main(APEXExport.java:304)

    I know the error happens when programa connect database using jdbc .It is not happened all the time,sometimes I got this error and sometimes it runs very well.I know there must be some issues.Could you give me some advices to fix this problems? Thanks.

    Like

    Reply
  9. Jonathan Babbage

    I have used these features to export my application and move it from development to production. I was wondering if you had any ideas about true concurrent development or code merging. We are in the unfortunate position where there could be some bug fixes on production and we want to integrate some additional features that were done on development. From what I remember, doing a code review or merge on the large export files was a little hard to manage. Any tips?

    Like

    Reply
  10. SriniK

    I’m trying to backup my Apex applications through APEXExport utility as given in this article. I set the CLASSPATH correctly as given in the readme.txt documentation and in this article.

    FLOWS_030100 account is open.

    The database vesion is 10.2.0.3
    Apex version is 3.0.1

    java -version
    java version “1.5.0_19”
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_19-b02)
    Java HotSpot(TM) Server VM (build 1.5.0_19-b02, mixed mode)

    The errors that I’m encountering while using this utility are given below.

    java oracle/apex/APEXExport -db sun-oracle:1521:ORCL -user scott -password tiger -applicationid 100
    Exporting application 100
    Exception in thread “main” java.sql.SQLException: ORA-20001: Application 100 not found logged in as database user scott
    ORA-06512: at “FLOWS_030100.WWV_FLOW_UTILITIES”, line 8014
    ORA-06512: at line 1

    Another error that I’m encountering when I’m trying with workspaceid is as follows:

    java oracle/apex/APEXExport -db sun-oracle:1521:ORCL -user scott -password tiger -workspaceid 1010131433379996
    Invalid Workspace ID ‘1010131433379996’ for User ‘scott>’

    Like

    Reply
  11. Steve Pence

    This was a huge help to us. Regards subversion, I found I could simply strip the timestamp from all the exports via
    # sed ‘/Date and Time/d’ -i *
    Then, I could
    # svn add
    # svn commit -m “nightly update”
    It does what you want: committing just the apps that change – or are new.
    Steve

    Like

    Reply
  12. John Scott

    Steve,

    I think you’ll need to be more selective than just that sed command alone. For example, what if I added a new page item with a label of ‘Date and Time’…that sed command would strip that out of the export file (and potentially giving me an invalid file which I could not import)?

    John.

    Like

    Reply
  13. Andy Potter

    Hi Steve & John,

    Thanks for the suggestions, they really help!

    After a bit of experimenting I think the following sed command should do the trick:

    # sed ‘0,/Date and Time/{//d;}’ -i *

    This will only delete the first line that contains “Date and Time” and hence should prevent the problem that John helpfully highlighted.

    Andy.

    Like

    Reply
  14. Billy Rogers

    Is there a way to combine the export of the Application with the underlying data?

    If not is the appropriate order to import the
    database into the schame before importing the apex application?

    Off topic: Does anyone know of a good diagramatic presentation for our dba that maps workspace, applications, users (schemas) and tablespaces?

    Like

    Reply
  15. Jennifer

    Hi,

    We’re using Oracle 11g and ${ORACLE_HOME}/jdbc/lib/classes12.zip does not exist. Do you happen to know what the CLASSPATH should be in 11g?

    Thanks!

    Like

    Reply
    1. John Scott

      Hi Jennifer,

      Does it not exist anywhere? If you do a ‘find . -name classes12.zip’ (or Windows equivalent) from the top level directory does it not find it at all?

      John.

      Like

      Reply
  16. Gerv Lapira

    Hi, I am in the same boat as Jennifer. We’re using Oracle 11g and I’m getting the error
    “Exception in thread “main” java.lang.NoClassDefFoundError: APEXExport”

    When doing a search through root, I do find two instances of classes12, which one do I use?

    “./opt/oracle/app/oracle/product/11.2.0/dbhome_1/oui/jlib/classes12.jar”

    “./opt/oracle/app/oracle/product/11.2.0/dbhome_1/lib/lclasses12.zip”

    The CLASSPATH I’m currently using is CLASSPATH=/opt/oracle/app/oracle/product/11.2.0/dbhome_1/lib/lclasses12.zip

    Hope anyone can share some insight regarding Oracle 11g and this awesome feature. Thanks!

    Like

    Reply
  17. Gerv Lapira

    In relation to the post above that I just made, I tried setting the CLASSPATH to both locations of classes12 and still get the error Exception in thread “main” java.lang.NoClassDefFoundError: APEXExport”

    Also, after I make the changes to the classpath, the “java oracle.apex.APEXExport” command stops working as well and returns the error
    “Exception in thread “main” java.lang.NoClassDefFoundError: oracle/apex/APEXExport
    Caused by: java.lang.ClassNotFoundException: oracle.apex.APEXExport
    at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
    at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
    Could not find the main class: oracle.apex.APEXExport. Program will exit.

    Like

    Reply
  18. masum

    ORA-20001: GET_BLOCK Error. ORA-20001: GET_STMT error. ORA-20001: Execution of the statement was unsuccessful. ORA-06550: line 79, column 42: PLS-00103: Encountered the symbol "��}X[1]���w: я�AQ�z d���" when expecting one of the following: ( – + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql std

    Like

    Reply
  19. Damir

    Hi!
    Am I missing something but I could not find a way to export other vital Apex app components: images, static files, themes etc.
    from command line as well?
    Any help?
    Rg,
    Damir

    Like

    Reply
  20. John Scott

    Hi Damir

    That’s correct, the APEXExport command does not export those files, however since they were external files originally we usually keep a copy of them in Subversion and check them in every time we update them in the application.

    Hope this helps,

    John.

    Like

    Reply
  21. Pingback: Apex Backup–manual and automatic « HA IT Services Pvt Ltd

  22. Leila

    Hi,
    I modified the script for automatic app backup, but I am not sure where to put it. I made a .bat file and when i manually run it it works just fine, it creates a backup export. But how can I implemenet it to run automatically?

    Regards,
    Leila

    Like

    Reply
  23. Earl

    I didn’t see an answer here for Oracle 11g, and I was running into the same problems described above. Eventually figured out the solution — the CLASSPATH needs to be set differently.

    Try this:

    export CLASSPATH=.:${ORACLE_HOME}/oui/jlib/classes12.jar

    Like

    Reply
  24. Pingback: Deployment van Application Express applicaties

  25. Harsh Shah

    Hi,
    I’m getting following error while exporting all application or exporting only one application.

    [oracle@oraclesvr2 Export]$ java oracle.apex.APEXExport -db 192.168.10.66:1521:tceg -user FLOWS_030100 -password XXXX -instance
    Exporting Application 100:’Sample Application’
    Exception in thread “main” java.sql.SQLException: ORA-06550: line 1, column 13:
    PLS-00306: wrong number or types of arguments in call to ‘EXPORT_APPLICATION_TO_CLOB’
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:214)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:953)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1167)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3389)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4222)
    at oracle.apex.APEXExport.ExportFile(APEXExport.java:173)
    at oracle.apex.APEXExport.ExportFiles(APEXExport.java:79)
    at oracle.apex.APEXExport.main(APEXExport.java:326)
    [oracle@oraclesvr2 Export]$

    Can you please help me our with this one?

    Thank you
    Harsh Shah

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s