How do you change the DB appowner name?

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

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. :)