Yes I wouldn't recommend trying data pump, either. Even if you technically
can export it on the version you're on - 11.2.0.3 - it would likely result
in a mess.
APEX is a data dictionary component as Jeffrey has pointed out. You may
well succeed to do the export and import. But you probably will run into
problems later down the line - most likely when you're trying to upgrade to
APEX 5. Think of it like Oracle Text, or Spatial - you similarly can't move
that around to another database by simple exporting / importing the
internal schemas.
Since you do have someone in charge of the web server and application - you
may not need to deal with it at all. If you can, simply ask them to do the
export on the APEX side (APEX offers export and import functionality, which
works cross version) and they can do that purely via the APEX UI. If that
is the case, all you'd need to do is install APEX 5 on the new database and
let them access it via the UI. They can do the rest.
If that's not an option; The difficulty with moving an APEX app like this
is that there's several things that may complicate it.
- APEX uses DBMS_SYS_SQL to run most things and it can technically access
any schema in the database. As long as the schema has been added as a
parsing schema it can access it.
select workspace_name, schema from apex_workspace_schemas;
That'll show you all the schemas involved. All of those will basically need
to be moved - or the app won't have any data.
- Find out how the application authenticates users. Does it use APEX-only
users (the users are stored in the APEX metadata only) or does it use
database users? If the application uses database users you will have to
move those as well. Does it use its own authentication where users exist in
custom tables (in this case you don't care and they will be moved with the
app as long as you move the parsing schema).
There might be a view that has this information but I'm not sure which one.
It's best to get this information from the application guys.
Once you know which schemas exactly you need, you can move those using data
pump.
But for APEX itself, I would:
- Install the new APEX version on the target
- Export the workspace and application on the source
- Import it on the target (this will automatically upgrade the app to the
new APEX version)
You can do this export directly on the shell, something like this:
export
CLASSPATH=/u00/app/oracle/product/apex_5.1.3/apex/utilities:/u00/app/oracle/product/
12.1.0.1/fzydev/jdbc/lib/ojdbc6.jar
/usr/bin/java oracle.apex.APEXExport -db 1.2.3.4:1521:DEV -user
parsing_schema -password foo -applicationid 100 -expTranslations
You can call /usr/bin/java oracle.apex.APEXExport -help to see all the
options it has. To be on the safe side, you may want to export everything
you can:
-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
Then to do the import, simple connect to the new database as the
APEX_050100 user and run the generated SQL script. If you watch the
output during import it will tell you that it's automatically upgrading the
app to the new version.
Stefan
On Wed, Jun 6, 2018 at 9:45 PM, Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
wrote:
You might want to see 12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID
1912162.1)
There’s a separate web server. We don’t allow apps to run on databaseScott Canaan <srcdco@xxxxxxx> 6/6/18 10:36 AM >>>
servers.
I am getting more confident that I can datapump the apex_040100 user and
get what I need to the other database. The web servers will work
themselves out as the app is migrated to new webservers. That is handled
by the vendor as part of their support.
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
srcdco@xxxxxxx | c: (585) 339-8659
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*From:* Stefan Knecht [mailto:knecht.stefan@xxxxxxxxx]
*Sent:* Wednesday, June 06, 2018 10:29 AM
*To:* Scott Canaan; oracle-l-freelist
*Subject:* Re: Oracle Apex Question
Hey Scott
Most welcome.
Based on what you stated - I'm fairly certain there must be a web server
involved, if the only account that's open is the APEX_PUBLIC_USER. It not
having any objects is normal.
It could run locally on the same machine as the database (if you're saying
it's a "package" you got as-is then that's quite likely). You should look
for a dads.conf file, which would tell you where the images directory is
mapped on the server - you will see an entry similar to this in that file:
Alias /i/ "/u00/app/webDomain/fuzzy.co.th/config/fmwconfig/
components/OHS/instances/ohs1/images_apex5/"
The path will look different of course - and likely simpler - if you're on
an older version.
When using APEX with an HTTP server, the entire apex images directory is
copied there and this would also be where any custom javascript or style
sheet files are located. Depending on how the environment was built, the
files may be anywhere within that directory and may be hard to locate. You
may want to download the same apex version, extract it and do a compare on
the images directory contents to determine if there is custom stuff in
there that you will need to bring along for the application to function.
You may also want to try looking at the page source (if you are logged on
to the application, "view page source" and look for links referencing files
in /i/, such as this for example:
<script type="text/javascript" src="/i/select2.min.js?v=5.1.
3.00.05"></script>
<script type="text/javascript" src="/i/zz_user_mgmt.js?v=1.0.48"></script>
Do note however that the default apex files are also referenced this way,
so it may not be immediately obvious.
Another thing is that the files included may vary depending on which page
of the application you're on. So if there are many pages, this may not be
feasible.
One last thing that just came to mind - it's possible that they didn't use
a dads.conf and just put all the settings necessary for APEX in other conf
files (httpd.conf or even something else - if you can't find a dads.conf on
the machine you have to dig a little deeper). But I'd bet you will have
httpd processes running.
Stefan
On Wed, Jun 6, 2018 at 8:34 PM, Scott Canaan <srcdco@xxxxxxx> wrote:
Stefan,
Thank you for the explanations. This is very helpful. What I can tell
you is this:
1) I don’t know how it is used – this is a purchased package.
APEX_PUBLIC_USER is unlocked. It owns nothing.
APEX_040100 owns everything.
ORDS_PUBLIC_USER doesn’t exist.
ANONYMOUS is locked.
I did the query for the http port and it returned 0.
I plan on running through the upgrade / migration process in test at least
a couple of times to make sure I get everything. I’m hoping just using
datapump will work to get what I need over to the new server, where Apex
5.4 will eventually be installed.
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
srcdco@xxxxxxx | c: (585) 339-8659
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*From:* Stefan Knecht [mailto:knecht.stefan@xxxxxxxxx]
*Sent:* Tuesday, June 05, 2018 7:50 PM
*To:* Scott Canaan
*Cc:* jbeckstrom@xxxxxxxxx; oracle-l-freelist;
rjoralist3@xxxxxxxxxxxxxxxxxxxxx
*Subject:* Re: Oracle Apex Question
There are a few things here that need to be looked at if you want to cover
all the possibilities and ensure a smooth migration.
1) How is APEX used by the clients
Currently there's EPG (not likely in production), Oracle HTTP Server (OHS)
with mod_plsql, Oracle REST data services (ORDS - formerly known as APEX
Listener), or even if someone has been very adventurous, mod_owa over
Apache.
To help determine what can be and can't be in use, a good starting point
is to look at the database users you have in that database.
APEX_PUBLIC_USER, if it's unlocked (and particularly if you see
connections) indicates OHS is used.
ORDS_PUBLIC_USER, if it's unlocked indicates ORDS is used.
ANONYMOUS if it's unlocked indicates EPG is used.
It's also worth noting that these aren't mutually exclusive - you can run
them all at the same time. You can run a standard APEX app via OHS and at
the same time offer REST services via ORDS.
A good way to find out is to just connect to the APEX web interface with a
browser and see what pops up on the database end (e.g. which users become
active). Another indicator is the URL that's used to connect to APEX:
http://hostname/ords/apex indicates ORDS is used ;(but again, the file can
be renamed and this would change the URL - the path used in the URL would
have to correspond to the name of the war file you'd see running).
http://hostname/* ;(on port 80) is more likely to be a real web server
(OHS or Apache)
http://hostname:8080/apex is most likely EPG.
You can also look for the processes - for OHS or Apache, you'd see httpd
processes, and for ORDS you'd see a .war file being run by java; usually
it's called ords.war but it can be renamed. Also keep in mind that they can
be offloaded - e.g. you can run OHS or ORDS on a different machine than the
database, they don't need to be local.
For EPG, what MUST be the case if it's being used is that the XDB HTTP
port is open. You can see that by either querying:
SQL> select dbms_xdb.gethttpport from dual;
GETHTTPPORT
-----------
0
.. here, zero means it's closed; or by checking your listener with lsnrctl
status where you'd see an XDB endpoint (usually on port 8080).
2) Depending on 1) what else you need to move
Generally, exporting the workspace and application will work (see here for
example https://ruepprich.wordpress.com/2011/07/15/
exporting-an-apex-application-via-command-line/ )
If you're using EPG, it's likely that all the components the APEX app
needs are inside the database (and stored in XDB).
However, if you're using OHS or ORDS, there may also be files referenced
outside the database.
If you have access to the APEX environment, you can also take a look at
SQL Workshop -> RESTful Services to see if any are configured.
Let us know what you find and if you need more information
Stefan
On Wed, Jun 6, 2018 at 1:25 AM, Scott Canaan <srcdco@xxxxxxx> wrote:
I will be running the install for Apex 5.4, which is the version I
ultimately need to end up with. I am trying to figure out how to get the
Apex 4.1 data migrated over so it isn’t lost when the upgrade to Apex 5.4
is done.
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
srcdco@xxxxxxx | c: (585) 339-8659
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org] *On Behalf Of *Jeffrey Beckstrom
*Sent:* Tuesday, June 05, 2018 2:02 PM
*To:* oracle-l-freelist; rjoralist3@xxxxxxxxxxxxxxxxxxxxx
*Subject:* Re: Oracle Apex Question
I would think you would have to run the install since at least in 11G,
APEX appears in dba_registry.
On 2018/06/05 07:19, Scott Canaan wrote:Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx> 6/5/18 1:54 PM >>>
I have an application that uses Oracle Apex. We are just starting an
application, Oracle, and O/S upgrade. I have to copy the Apex “install”
from Oracle 11.2.0.3 on Red Hat 6 to Oracle 12.1.0.2 on Red Hat 7.
Normally, I do a data pump import across the network to migrate the Oracle
schemas, but I’ve been told that doesn’t work for Apex. I need to preserve
the Apex version at 4.01 until the application upgrade, then it will be
upgraded to the current version.
Is it true that I can’t use Oracle data pump to copy Apex from one server
to another?
The correct answer is "it depends", of course. I suspect that if the
webserver is the builtin EPG and there are no external files (custom
images, CSS, etc), it *may* work. I'm not sure if APEX 4.x will work in an
Oracle 12.1 DB either. If possible, I would upgrade the APEX version
first, then migrate to another DB/server.
I've only attempted migrations by using the APEX export/import
application, but I'm using ORDS on Tomcat for the web and also external
files. As always, test, and YMMV.
GL!
Rich
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/
