Hi @Bob Corrigan ,

The easiest way to do this would be to use the remap_schema flag during the database import to the new database using Oracle data pump. in you case the flag would look like “remap_schema=sysiapp:ifsapp”

Cheers

There might also be other places where the app owner is referenced inside the data itself. One such place is under System Parameters → System Settings → Name of the Application Owner.

Since you probably don’t have an application running against your database copy yet, you can change that using SQL after changing the schema during the import:

UPDATE ifsapp.fnd_setting_tab
   SET value = 'IFSAPP'
 WHERE parameter = 'APP_OWNER';

The application owner is also a Foundation1 user, so all those tables will need to be updated too. Some examples:

UPDATE fnd_user_tab SET identity = 'IFSAPP' WHERE identity = 'SYS1APP';
UPDATE fnd_user_property_tab SET identity = 'IFSAPP' WHERE identity = 'SYS1APP';
UPDATE fndrr_client_profile_tab SET owner = 'IFSAPP' WHERE owner = 'SYS1APP';
UPDATE fndrr_user_client_profile_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';
UPDATE fnd_user_role_tab SET identity = 'IFSAPP' WHERE identity = 'SYS1APP';
UPDATE fnd_user_role_runtime_tab SET identity = 'IFSAPP' WHERE identity = 'SYS1APP';
UPDATE user_allowed_site SET userid = 'IFSAPP' WHERE userid = 'SYS1APP';
UPDATE user_finance_tab SET userid = 'IFSAPP' WHERE userid = 'SYS1APP';
UPDATE document_group_members_tab SET person_id = 'IFSAPP' WHERE person_id = 'SYS1APP';
UPDATE pers_tab SET person_id = 'IFSAPP' WHERE person_id = 'SYS1APP';
UPDATE person_info_tab SET person_id = 'IFSAPP' WHERE person_id = 'SYS1APP';
UPDATE person_info_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';
UPDATE gen_led_user_tab SET userid = 'IFSAPP' WHERE userid = 'SYS1APP';;
UPDATE purchase_buyer_tab SET buyer_code = 'IFSAPP' WHERE buyer_code = 'SYS1APP';
UPDATE purchase_requisitioner_tab SET requisitioner_code = 'IFSAPP' WHERE requisitioner_code = 'SYS1APP';
UPDATE report_rule_condition_tab SET expr2 = 'IFSAPP' WHERE expr2 = 'SYS1APP';
UPDATE user_printer_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';
UPDATE report_user_printer_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';
UPDATE company_person_tab SET emp_no = 'IFSAPP' WHERE emp_no = 'SYS1APP';
UPDATE emp_employed_time_tab SET emp_no = 'IFSAPP' WHERE emp_no = 'SYS1APP';
UPDATE employee_tab SET emp_no = 'IFSAPP' WHERE emp_no = 'SYS1APP';
UPDATE employee_tab SET sign = 'IFSAPP' WHERE sign = 'SYS1APP';
UPDATE company_emp_tab SET person_id = 'IFSAPP' WHERE person_id = 'SYS1APP';
UPDATE company_emp_tab SET employee_id = 'IFSAPP' WHERE employee_id = 'SYS1APP';
UPDATE user_group_member_finance_tab SET userid = 'IFSAPP' WHERE userid = 'SYS1APP';
UPDATE invoice_authorizer_tab SET auth_id = 'IFSAPP' WHERE auth_id = 'SYS1APP';
UPDATE invoice_authorizer_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';
UPDATE posting_authorizer_tab SET authorizer_id = 'IFSAPP' WHERE authorizer_id = 'SYS1APP';
UPDATE posting_authorizer_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';
UPDATE purchase_authorizer_tab SET authorize_id = 'IFSAPP' WHERE authorize_id = 'SYS1APP';
UPDATE purchase_authorizer_tab SET userid = 'IFSAPP' WHERE userid = 'SYS1APP';
UPDATE order_coordinator_tab SET authorize_code = 'IFSAPP' WHERE authorize_code = 'SYS1APP';
UPDATE inventory_part_planner_tab SET buyer_code = 'IFSAPP' WHERE buyer_code = 'SYS1APP';
UPDATE user_group_user_tab SET user_id = 'IFSAPP' WHERE user_id = 'SYS1APP';

This might be a good use case for a comprehensive database-wide search of every column on every table. This is the safest way to develop a data repair when you’re not sure about the structures.

First, it queries all the database columns. As it does so, it builds a script that selects those columns to look for a value.

Next, it runs that script and outputs the ROWIDs found.

Analyzing the final output with a text editor, you’ll want to remove all instances of “no rows found” and then look for “rows found” to find the results.

brute_force_database_search.sql

SET ECHO ON

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET TRIMSPOOL ON
SET TAB OFF
SET TIMING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET ARRAYSIZE 5000

SET ECHO OFF
SPOOL _brute_force.sql

SELECT 'SELECT rowid FROM "' || utc.table_name ||
       '" WHERE "' || utc.column_name ||
       '" = ''SYS1APP'' /* rownum = ' ||
       ROW_NUMBER() OVER(ORDER BY utc.table_name, utc.column_name) ||
       '*/' ||
       CHR(13) || CHR(10) ||
       '/' AS sq
  FROM user_tab_cols utc
 INNER JOIN user_tables ut
    ON ut.table_name = utc.table_name
 WHERE utc.data_type IN ('VARCHAR2', 'CLOB', 'CHAR')
 ORDER BY 1;

SPOOL OFF

SET ECHO ON
SET FEEDBACK ON
SET ARRAYSIZE 1

SPOOL _brute_force_out.txt

@_brute_force.sql

SPOOL OFF

EXIT;

Your DataPump import file should remap the schema.

REMAP_SCHEMA=ACME1APP:IFSAPP

Even with the schema remapping and the data repairs, you’ll also run into CONTEXT objects that will need to be recreated.

DROP CONTEXT "ABSCALCALL_CTX";
DROP CONTEXT "ABSENCEACTIONSTATUS_CTX";
DROP CONTEXT "ABSENCEACTIONTYPE_CTX";
DROP CONTEXT "ABSENCEBASE_CTX";

-- (These are just 4 examples of the 2,679 of these in our environment.)

CREATE OR REPLACE CONTEXT "ABSCALCALL_CTX"                USING "IFSAPP"."DOMAIN_SYS" ACCESSED GLOBALLY;
CREATE OR REPLACE CONTEXT "ABSENCEACTIONSTATUS_CTX"       USING "IFSAPP"."DOMAIN_SYS" ACCESSED GLOBALLY;
CREATE OR REPLACE CONTEXT "ABSENCEACTIONTYPE_CTX"         USING "IFSAPP"."DOMAIN_SYS" ACCESSED GLOBALLY;
CREATE OR REPLACE CONTEXT "ABSENCEBASE_CTX"               USING "IFSAPP"."DOMAIN_SYS" ACCESSED GLOBALLY;

@Bob Corrigan , @durette , @Sajith D ,  This originally states that “IFSAPP is needed for Managed Cloud”.  What about Remote (on-prem) installation?  Is this recommendation or a requirement?  What is the impact, long or short term, of not changing?  

I’m trying to weigh the risk to our ongoing migration project, to the risk of long-term supportability.  

Thanks for your advice and expertise.

As far as I know, other application owners are “supported”, but “support” in this context is nothing more than a piece of paper that tells everyone where to point fingers. If you don’t actually get help, supportability is worthless. (Sure, you could take a vendor to court, but how often are you willing to do that, and how helpful will it really be when your system is down in the meantime? In my opinion, you’re better off building good relationships rather than being adversarial and pedantic.)

After I found IFSAPP hard-coded in an early version of Cloud, I made the decision that I didn’t want to be caught having to beg for help for a weird configuration some day. It felt safer to conform, and an upgrade this big, for 9 to Cloud, provides us a rare opportunity to make such a change. (There weren’t enough changes from 8 to 9 to justify something as disruptive as this.)

If you ever have to go shopping for auto parts, even if your local parts store claims to carry every brand, would you rather drive a Studebaker or a Toyota?

Nonstandard application owners were only a common practice in North America, and no new implementations are going that way now.

Amusing anecdote, and point taken.  I like Studebakers, I own a Toyota. :)