RE: Data Migration - APEX

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 25 Apr 2024 15:07:34 -0400
Message-ID: <05ca01da9743$d4fd1610$7ef74230$_at_rsiz.com>

Yet it IS disappointing that there is no automagic push button wizard to walk through this just by the DBA.

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.

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_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: Arian Stijf <arian_at_stijf.com>
Sent: Thursday, April 25, 2024 1:51 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: Oracle-L Freelists <oracle-l_at_freelists.org> 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_at_rit.edu | c: (585) 339-8659

From: Arian Stijf <arian_at_stijf.com>
Sent: Thursday, April 25, 2024 1:20 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: Oracle-L Freelists <oracle-l_at_freelists.org> 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_at_rit.edu | c: (585) 339-8659

From: Ricard Martinez <ricard.martinez_at_gmail.com> Sent: Thursday, April 25, 2024 12:14 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: Oracle-L Freelists <oracle-l_at_freelists.org> 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_at_rit.edu> 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_at_rit.edu | c: (585) 339-8659

From: Ricard Martinez <ricard.martinez_at_gmail.com> Sent: Thursday, April 25, 2024 9:08 AM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: Oracle-L Freelists <oracle-l_at_freelists.org> 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_at_rit.edu> 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> _at_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 _at_ /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_at_rit.edu',

  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_at_rit.edu | c: (585) 339-8659

From: Ricard Martinez <ricard.martinez_at_gmail.com> Sent: Wednesday, April 24, 2024 4:30 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: oracle-l_at_freelists.org
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_at_freelists.org> 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_at_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

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_at_rit.edu | c: (585) 339-8659

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

Received on Thu Apr 25 2024 - 21:07:34 CEST

This message: [ Message body ]
Next message: arian: "RE: Data Migration - APEX"
Previous message: Scott Canaan: "RE: Data Migration - APEX"
In reply to: Scott Canaan: "RE: Data Migration - APEX"
Next in thread: arian: "RE: Data Migration - APEX"
Reply: arian: "RE: Data Migration - APEX"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message