Solved

How do you change the DB appowner name?

  • 29 July 2021
  • 5 replies
  • 389 views

Userlevel 7
Badge +13

During the initial installation the DB appowner name was changed from IFSAPP to SYS1APP.

Since IFSAPP is needed for IFS Managed Cloud, what needs to be done to change it back to IFSAPP?

icon

Best answer by durette 30 July 2021, 18:49

View original

5 replies

Userlevel 7
Badge +21

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

Userlevel 7
Badge +18

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';

 

Userlevel 7
Badge +18

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;

 

Userlevel 7
Badge +18

Your DataPump import file should remap the schema.

REMAP_SCHEMA=ACME1APP:IFSAPP

 

Userlevel 7
Badge +18

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;

 

Reply