Re: Oracle Apex Question

On Thu, 7 Jun 2018, 01:03 Stefan Knecht, <knecht.stefan_at_gmail.com> wrote:

> 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_at_gcrta.org>
> wrote:
>
>> You might want to see 12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID
>> 1912162.1)
>> >>> Scott Canaan <srcdco_at_rit.edu> 6/6/18 10:36 AM >>>
>> There’s a separate web server. We don’t allow apps to run on database
>> 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_at_rit.edu | 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_at_gmail.com]
>> *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_at_rit.edu> 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_at_rit.edu | 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_at_gmail.com]
>> *Sent:* Tuesday, June 05, 2018 7:50 PM
>> *To:* Scott Canaan
>> *Cc:* jbeckstrom_at_gcrta.org; oracle-l-freelist;
>> rjoralist3_at_society.servebeer.com
>>
>> *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_at_rit.edu> 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_at_rit.edu | 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_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jeffrey Beckstrom
>> *Sent:* Tuesday, June 05, 2018 2:02 PM
>> *To:* oracle-l-freelist; rjoralist3_at_society.servebeer.com
>> *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.
>> >>> Rich J <rjoralist3_at_society.servebeer.com> 6/5/18 1:54 PM >>>
>> On 2018/06/05 07:19, Scott Canaan wrote:
>>
>> 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 | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>>
>>
>>
>> --
>> //
>> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
>> Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>>
>
>
>
> --
> //
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
> Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>

--
http://www.freelists.org/webpage/oracle-l