RE: Data Migration - APEX

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

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

From: Arian Stijf <arian_at_stijf.com<mailto:arian_at_stijf.com>> Sent: Thursday, April 25, 2024 1:20 PM
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

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

From: Ricard Martinez <ricard.martinez_at_gmail.com<mailto:ricard.martinez_at_gmail.com>> Sent: Thursday, April 25, 2024 12:14 PM 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

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

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.

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

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?

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

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

Received on Thu Apr 25 2024 - 20:34:20 CEST

This message: [ Message body ]
Next message: Mark W. Farnham: "RE: Data Migration - APEX"
Previous message: Arian Stijf: "Re: Data Migration - APEX"
In reply to: Arian Stijf: "Re: Data Migration - APEX"
Next in thread: Mark W. Farnham: "RE: Data Migration - APEX"
Reply: Mark W. Farnham: "RE: Data Migration - APEX"

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

Original text of this message