I posted recently about best practices for prod / dev environments with APEX, and this grows from that. I have reviewed the suggested solutions with developers and met with some, shall we say, resistance. So, here is what they want to do. Please help me understand whether it's possible, and how I might accomplish it.
Our test/development server contains APEX 4.2 and Banner (our ERP) data, all in a database called DALY11G.
Developers want to to develop daily on this server, and they need current data in order to do so.
I recreate this database nightly via RMAN duplicate, and so the entire database is dropped, and recreated from production backups.
They want me to export the APEX environment nightly, before I drop and re-clone the DALY11G database, thus preserving any development they've done during the day. Then, after the clone (RMAN duplicate) process runs, they want the APEX environment dropped and reimported from that previous night's export. Once this is done they will have the up to date data they want, along with the in process development that they don't want to lose nightly.
I realize the supported method for doing this sort of thing is to export the apps and reimport them through the GUI development tool, but my users refuse this method, saying it will add too much time to their work load.
SO... is what they ask possible? And if so, how might I accomplish it? I have tried exporting (before the DALY11G database gets re-cloned) the APEX_040200, APEX_DOC, APEX_WORK, APEX_DATA, and APEX_READONLY schemas (I realize some of those are specific to us). After the clone, then, I drop the production versions of those schemas, recreate them, and then import from the exported file. But the exports throw errors, and I end up with invalid objects. Is the means of importing / exporting the entire 4.2 environment like this documented somewhere? I've been unable to locate it.
Thanks for any advice. And if you have any suggestions for how I can convince the developers to export / import their apps through the GUI, let me know that, too!
I've never known an example of being able to move or recreate an Apex installation by exporting and importing the schemas associated with Apex. I think the relationships between schemas and objects are just too complicated for this to work easily.
I would have thought it would be easier to move the application you are developing against, but given I know nothing about Banner ERP, this may also not be an option. Also what I don't understand, given that you are trying to save any changes the developers make to their application in development, if you are refreshing the whole environment, aren't you also losing any changes the developers make at the database level. What about custom versions of DB objects they may be using for development purposes? How do you allow for this?
My preference for overcoming this would be rather than dropping the refreshed Apex environment at the schema level, but to do it at the Apex level. In order to automate this, and please you developers, there are command line options for exporting and importing applications that you can use to automate this process.
Applications can be exported through a tool Oracle supplies with the Apex download called APEXExport.class and as applications are simply sqlplus scripts, they can be imported using sqlplus command line. I haven't done this personally, but I think it would be possible.
Interesting problem and even more interesting suggestions.
It's a bit difficult and certainly not documented how you import an entire APEX environment using impdp. Not only are there multiple schemas, but there are cross-schema grants, grants on SYS objects, a handful of objects actually installed in SYS, and a number of system privileges.
As Andre suggests above, exporting the applications via the APEXExport command-line utility would be something to consider. You could entirely automate and script the export and import process. But this would not "recreate their development environment". This would miss any uploaded scripts, SQL Command history, uploaded files, etc.
Is it possible to do the reverse? That is - is it feasible to import the Banner data into your development environment? Sorry if this is a silly question.