Yet it IS disappointing that there is no automagic push button wizard to walk
through this just by the DBA.
<soapbox mode on>
I envision that as:
Start wizard.
Provide location (database and instance) for source and credentials (works or
fails with a reasonable message of how to repair any failure).
Provide location for destination and credentials.
Make sure APEX is installed (or install it, the right one to match the source).
Walk through checking apex users and creating them in the destination if needed
with the correct credentials.
Possibly ask about destination tablespaces and so forth in case that is not
identical to the source.
Check the destination space.
Copy everything else you need from source to destination.
Smile and rejoice that Oracle successfully automated this task and produced a
transcript of the questions, answers, and success of all the operations with
times.
Dear product manager lurkers: This is the sort of thing that makes a tool not
merely the neatest thing since sliced bread and the think the cool kids want to
use (and APEX is rightly both of those), but also underscores that it is
commerce ready.
<soapbox mode off>
Even if the wizard needs to dump out some files (with a verification hash code,
please) and pause for the DBA operating the wizard to move those files to a
(configurable, please) place on the destination, this should be routine for a
novice DBA and not need between servers knowledge and work by the APEX
user/developers nor APEX inner workings knowledge by the DBA.
Yet in fact it is a head scratching ordeal for a very smart DBA with decades of
experience. Oracle can and should do better.
Sigh. Upgrading forms 2.3 to modern forms with graphics just couldn’t be done
despite Oracle trying very hard for about 3 years. The change in metaphor from
question and response in the underlying code to true graphics forms was just
too much. There was a similar debacle with the shift in webserver stuff where
you just couldn’t automate preserving the existing code.
But this is current Oracle technology to current Oracle technology. And to
facilitate development with this tool cloning to another instance should be
easy and automated.
now if “How to Migrate APEX Applications and Their Supporting Objects from One
APEX Instance to Another (Doc ID 758216.1)” handles all that simply, that is
fine, but when you are migrating an entire database it should just come along,
right?
Good luck,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Scott Canaan ("srcdco")
Sent: Thursday, April 25, 2024 2:34 PM
To: Arian Stijf
Cc: Oracle-L Freelists
Subject: RE: Data Migration - APEX
There are a couple of people that do manage Apex. They don’t want to do the
migration, they want me to do all of it. We just talked and came to the
conclusion that I can set up the new database, install Apex, and do the other
configuration that they need (wallet, email, etc.), but they will have to
export the workspace and applications, copy the files to the new server, then
import them. Now I just need buy-in from those in charge.
We certainly can coordinate that work. None of it takes much time.
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: Arian Stijf <arian@xxxxxxxxx>
Sent: Thursday, April 25, 2024 1:51 PM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Data Migration - APEX
Ouch.... I was assuming that somebody in your organization is managing the apex
application and could do this.
With no access at all, you're running out of options very fast. Is upgrading
Apex to 23c an option?
Regards,
Arian
On 2024-04-25 19:28, Scott Canaan wrote:
I don't have access to the application. That's probably the real issue here,
not that I'd know what to do once I get into it.
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: Arian Stijf <arian@xxxxxxxxx>
Sent: Thursday, April 25, 2024 1:20 PM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Data Migration - APEX
Hi Scott,
Note 1992236.1 gives the following workaround:
---------------
Remove all developers from pre-created user groups before exporting the
workspace.
ie., Goto Workspace Administration--> Manage users and groups --> Group
Assignments --> Move all the Roles to RIGHT box from LEFT box.
Save it and then export the workspace and import it in another server. This
will not raise the error said in this note.
After importing, you can Goto Workspace Administration--> Manage users and
groups --> Group Assignments --> Move all the Roles to LEFT box from RIGHT box.
---------------
HTH
Arian
On 2024-04-25 18:27, Scott Canaan wrote:
Yeah, but I know nothing about Apex and how it's put together. For some
reason, everyone assumes that because a product has Oracle in its name, I'm
supposed to know everything about it.
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: Ricard Martinez <ricard.martinez@xxxxxxxxx>
Sent: Thursday, April 25, 2024 12:14 PM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Data Migration - APEX
As far I can see on the log the error is related to PK/FK issue or I'm missing
something?
Error report -
ORA-02291: integrity constraint (APEX_210200.WWV_FLOW_FND_GU_INT_G_FK) violated
- parent key not found
ORA-06512: at "APEX_210200.WWV_FLOW_FND_USER_INT", line 1926
ORA-06512: at "APEX_210200.WWV_FLOW_FND_USER_API", line 347
On Thu, Apr 25, 2024 at 2:25 PM Scott Canaan <srcdco@xxxxxxx> wrote:
I'm not seeing how that doc can help since it's about time outs logging into
cloud database.
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: Ricard Martinez <ricard.martinez@xxxxxxxxx>
Sent: Thursday, April 25, 2024 9:08 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Data Migration - APEX
Note 2876088.1 should probably help. Apex can be confusing if you not used to
using it, but I'm sure you will manage :)
On Thu, 25 Apr 2024, 12:53 Scott Canaan, <srcdco@xxxxxxx> wrote:
Well, I'm getting nowhere fast. I tried the APEXExport route, but it can't be
found:
Error: Could not find or load main class oracle.apex.APEXExport
So I tried the recommended new method, using SQLcl. I was able to export the
workspace, but when I try to import it, it fails with:
SQL> @w3401988816267991.sql
--application/set_environment
WORKSPACE 3401988816267991
Creating workspace APEX_EBS_EXTENSION...
Creating Groups...
Creating group grants...
Creating Users...
Error starting at line : 107 File @ /oracle/app/w3401988816267991.sql
In command -
begin
wwv_flow_fnd_user_api.create_fnd_user (
p_user_id => '3402885266277091',
p_user_name => 'AXCSRS',
p_first_name => 'Adriana',
p_last_name => 'Capobianco',
p_description => '',
p_email_address => 'arcsrs@xxxxxxx',
p_web_password =>
'4566C93F697716613AB002F422E0CEEBCD43D2B574E3C464C5A7E0E072787DF23D9CB82076FB8426C01FC55BC6FA142A5F36C602FB5CEB4B12913EC7270402A4',
p_web_password_format => '5;5;10000',
p_group_ids =>
'1980130621345243:1980290403345247:1980342113345247:',
p_developer_privs =>
'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_default_schema => 'APEX_EBA',
p_account_locked => 'N',
p_account_expiry => to_date('201812111606','YYYYMMDDHH24MI'),
p_failed_access_attempts => 0,
p_change_password_on_first_use => 'Y',
p_first_password_use_occurred => 'Y',
p_allow_app_building_yn => 'Y',
p_allow_sql_workshop_yn => 'Y',
p_allow_websheet_dev_yn => 'Y',
p_allow_team_development_yn => 'Y',
p_allow_access_to_schemas => '');
end;
Error report -
ORA-02291: integrity constraint (APEX_210200.WWV_FLOW_FND_GU_INT_G_FK) violated
- parent key not found
ORA-06512: at "APEX_210200.WWV_FLOW_FND_USER_INT", line 1926
ORA-06512: at "APEX_210200.WWV_FLOW_FND_USER_API", line 347
ORA-06512: at line 2
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.21.0.0.0
At this point, I'm ready to go in and change the field in the system telling it
not to use expdp/impdp and just do it that way. This is very frustrating.
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: Ricard Martinez <ricard.martinez@xxxxxxxxx>
Sent: Wednesday, April 24, 2024 4:30 PM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Data Migration - APEX
Hi,
You can't export/import the APEX application using datapump, take a look to
this note for some info how to do it:
How to Migrate APEX Applications and Their Supporting Objects from One APEX
Instance to Another (Doc ID 758216.1)
Hope it helps.
On Wed, Apr 24, 2024 at 8:49 PM Scott Canaan <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
We are in the process of migrating databases from Red Hat 7 to Red Hat 8. One
of the databases has APEX installed. I installed the same version of APEX in
the new database and migrated the data using impdp over the network.
Once I was done, I learned that there is custom data in the APEX_210200 tables.
I now need to migrate those over. When I try to migrate those tables, using
the table_exists_action=replace (or truncate), nothing happens. So I tried to
do an export on the old database and this is what I get:
oracle@ebsadevl1:EBSADEVL>expdp schemas=APEX_210200 dumpfile=apex_210200.dmp
include=table
Export: Release 19.0.0.0.0 - Production on Wed Apr 24 15:42:00 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=APEX_210200
dumpfile=apex_210200.dmp include=table
ORA-39165: Schema APEX_210200 was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Wed Apr 24
15:42:09 2024 elapsed 0 00:00:04
If I query dba_users, the APEX_210200 user exists. Why can't expdp find that
schema? How do I get it to recognize it?
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.
