Wednesday, 13 January 2016

Upgrading APEX on XE without the patch sets

To get APEX patch sets requires a paid support account to get access to the patch sets for minor updates - so upgrading APEX on our XE database installations can be challenging for those of us without a paid support account.

So effectively, what you need to do is remove APEX, and re-install from scratch. APEX also comes with a Java program to perform backups, where you can for example export invididual workspaces, export all workspaces, export invidual applications, export applications by workspace id, or export all applications. I grabbed the idea from this apexbackup project on GitHub: Basically, you need to set your CLASSPATH to point to:

  1. The Oracle Java database driver - typically $ORACLE_HOME/lib/ojdbc5.jar or $ORACLE_HOME/jdbc/lib/ojdbc5.jar
  2. The utilities folder in the APEX installation files

After that has been set,  you can run the program. e.g: 

export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/apex/utilities/
java oracle.apex.APEXExport

With the requires arguments depending on which operation you wish to perform. If you run it without any arguments, output to the screen will be a list of all the options:

Usage APEXExport -db  -user  -password  -applicationid  -workspaceid -instance -expWorkspace -expMinimal -expFiles -skipExportDate -expPubReports -expSavedReports -expIRNotif -expTranslations -expTeamdevdata -expFeedback -deploymentSystem -expFeedbackSince -expOriginalIds -debug  
    -db:               Database connect url in JDBC format 
    -user:             Database username
    -password:         Database password
    -applicationid:    ID for application to be exported
    -workspaceid:      Workspace ID for which all applications to be exported or the workspace to be exported
    -instance:         Export all applications
    -expWorkspace:     Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified
    -expMinimal:       Only export workspace definition, users, and groups
    -expFiles:         Export all workspace files identified by -workspaceid
    -skipExportDate:   Exclude export date from application export files
    -expPubReports:    Export all user saved public interactive reports
    -expSavedReports:  Export all user saved interactive reports
    -expIRNotif:       Export all interactive report notifications
    -expTranslations:  Export the translation mappings and all text from the translation repository
    -expFeedback:      Export team development feedback for all workspaces or identified by -workspaceid to development or deployment
    -expTeamdevdata:   Export team development data for all workspaces or identified by -workspaceid
    -deploymentSystem: Deployment system for exported feedback
    -expFeedbackSince: Export team development feedback since date in the format YYYYMMDD
    -expOriginalIds:   If specified, the application export will emit ids as they were when the application was imported
    Application Example: 
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -applicationid 31500 
    Workspace  Example: 
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 
    Instance Example:
       APEXExport -db localhost:1521:ORCL -user system -password systems_password -instance 
    Export All Workspaces Example:
       APEXExport -db localhost:1521:ORCL -user system -password systems_password -expWorkspace 
    Export Feedback to development environment:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -expFeedback 
    Export Feedback to deployment environment EA2 since 20100308:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -expFeedback -deploymentSystem EA2 -expFeedbackSince 20100308

So with that, to upgrade our APEX instances on minor releases (patches) we can script up a program to export all our workspaces, and restore them after a fresh installation - by getting the latest, full download from OTN.

I started working on a new project over the weekend to do exactly this - It's still in early stages, but to the point it's working for me. Basically, the arguments it accepts (current order being important) is:
  • Path to the apex installation files
  • Database host
  • Database port
  • Database sid
  • System password
  • Sys password
  • Path to where the images are stored
Once you grab the project on your server, you would simply run it like so:

sudo -E ./ /home/trent/apex localhost 1521 xe oracle oracle /ords/apex_images

Using the same order of arguments described above.

That goes without saying, this should be run as a user with permissions to write to the destination image directory, and they should have $ORACLE_HOME set. And you will likely want to take a separate backup of all your applications in case anything breaks in the script after the APEX removal - especially in these early stages of the project (I haven't had any other testers so far).

My script is also taking a backup of the instance configuration to restore it post upgrade (though, I haven't got everything working so far due to apex_instance_admin.set_parameter seemingly not being supported for all parameters). The main thing that will be missing post upgrade is all the users previously set up in the INTERNAL workspace. I have my machine installed in VirtualBox, so I took a snapshot beforehand should anything fall over, so I can easily restore it.

Some immediate enhancements requires are to support named arguments so order isn't important, along with default values if any are omitted; as well as adding some additional ones (namely, for the values passed to the APEX install script).

Even if you don't use my specific script/project, it can be a good example case to base off to develop something for your own business.

Have I left any other crucial bits off my script - that would need backup and restoration?

Here's a little video of my running the script: