RE: Data Migration - APEX

From: Scott Canaan <"Scott>
Date: Thu, 25 Apr 2024 16:27:18 +0000
Message-ID: <CH3PR16MB589712F9149D7C57FFFFF674C5172_at_CH3PR16MB5897.namprd16.prod.outlook.com>

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.

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

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

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

Received on Thu Apr 25 2024 - 18:27:18 CEST

This message: [ Message body ]
Next message: Ricard Martinez: "Re: Data Migration - APEX"
Previous message: Ricard Martinez: "Re: Data Migration - APEX"
In reply to: Ricard Martinez: "Re: Data Migration - APEX"
In reply to Scott Canaan: "RE: Data Migration - APEX"
Next in thread: Ricard Martinez: "Re: Data Migration - APEX"
Reply: Ricard Martinez: "Re: Data Migration - APEX"
Reply: Arian Stijf: "Re: Data Migration - APEX"

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

Original text of this message