Upgrade ebsR12.1.3 database 11.1.0.7 to 11.2.0.4

[satish]

Hi erman,

Need your help.

Pre upgrade step:

Clean Up Duplicate Objects Owned by SYS and
SYSTEM schema


Refer > Note 1030426.6 How to Clean Up
Duplicate Objects Owned by SYS and SYSTEM schema

SQL> column obiect name format a30
select object_name, object_type from dba_objects where object_name||object_type in (select object_name object_type from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';


OBJECT_NAME



AQ$_SCHEDULES
AQ$_SCHEDULES_PRIMARY
DBMS_REPCAT_AUTH DBMS_REPCAT_AUTH
WPG_DOCLOAD WPG_DOCLOAD
6 rows selected.


OBJECT_TYPE

TABLE
INDEX
PACKAGE
PACKAGE BODY
PACKAGE
PACKAGE BODY


Our concern is can we take backup and drop the below package owned by system before upgrade?

WPG_DOCLOAD

PACKAGE
PACKAGE BODY


Thank you

[ErmanArslansOracleBlog]

Just follow the MOS note; it tells you what to remove and what to keep... WPG_DOCLOAD is not there in the list of the object which should not be removed.. That Mos note also gives you a script to generate the drop commands.

Read MOS note carefully, especially the section starting with "Exceptions to the rule:"

->

1. The replication scripts (xxx) correctly creates objects with the same name in the sys and system accounts. Listed below are the objects used by replication that should be created in both accounts. Do not drop these objects from the SYSTEM account if you are using replication. Doing so will cause replication to fail!

The following objects are duplicates that will show up (and should not be removed) when running this script in 8.1.x and higher.

Without replication installed:
INDEX           AQ$_SCHEDULES_PRIMARY
TABLE           AQ$_SCHEDULES

If replication is installed by running catrep.sql:
INDEX           AQ$_SCHEDULES_PRIMARY
PACKAGE         DBMS_REPCAT_AUTH
PACKAGE BODY    DBMS_REPCAT_AUTH


**ALSO take the following into consideration ->

"ensure to take a full database backup before dropping the duplicate objects. "

**Oracle also provides you a script for easing your job .. In the same note .. It just spools to the dropsys.sql.. You can check and run that script --if you prefer.

set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name not in ('AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','AQ$_SCHEDULES','PRODUCT_USER_PROFILE','SQLPLUS_PRODUCT_PROFILE','PRODUCT_PRIVS','HELP','HELP_TOPIC_SEQ') and object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';

spool off
exit

[satish]

Thanks for the update.We have dropped the package.we faced fee invalids but we compiled them and they became valid.