Important Note:
In
this Note we will focus Only Database upgrade. If you are using oracle
E-Business Suite and you want to upgrade your database so you can fist
identified that what is your oracle E-Business Suite Version for database
upgrade it is related to each other If you were use oracle E-business Suite 11i
11.5.10.2 with RDBSM 10gR2 and you wanted to upgrade in E-business Suite R12
12.1.3 with RDBSM 11gR2. During database upgrade you will drop fist Oracle Data
Mining Feature in 10g database after then you will upgrade database 11Gr2
because ODM feature is not support in Oracle 11gR2 and if you forgotten this
step or you did not know after upgrade in 11gR2 this feature is not working but
still there and then you want to upgrade in 12cR1 this feature is only migrate
here with previous version like you can see in below query.
Cause of Issue:
Oracle Data mining Component is
not upgrading in 12cR1 when we were upgrade 11gr2 to 12cr1.
Solution:
We will upgrade ODM because we cannot
de-install this component in dba_registy.
Step-1:
A) Source
Database environment.
B) Connect
with sqlplus as a sys User.
C) Run
Query below mention.
SQL> set lines
133 pages 133
SQL> col
comp_name format a40
SQL>
SQL> select
comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
--------------------------------------------------
----------------------------
-----------
Oracle
Machine Generated Data 12.1.0.2.0 VALID
OLAP
Catalog 11.2.0.4.0 OPTION OFF
Oracle XML
Database 12.1.0.2.0 VALID
Oracle
Text 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle
Multimedia 12.1.0.2.0 VALID
Oracle
Database Catalog Views 12.1.0.2.0 VALID
Oracle
Database Packages and Types 12.1.0.2.0 VALID
Oracle Real
Application Clusters 12.1.0.2.0 OPTION OFF
JServer JAVA
Virtual Machine 12.1.0.2.0 VALID
Oracle
XDK 12.1.0.2.0 VALID
Oracle
Database Java Packages 12.1.0.2.0 VALID
OLAP
Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP
API 12.1.0.2.0 VALID
Oracle Data Mining
11.2.0.4.0 VALID
15 rows
selected.
Note: You can see Oracle Data Mining
Version not Upgrade in 12cR1.
Step-2
A) We
will run Oracle Data Mining Upgrade script for ODM Upgrade.
B) Go
to odmpatch.sql location “/u01/mmdb/db/tech_st/12.1.0/rdbms/admin/odmpatch.sql”.
C) Then
connection with sqlplus as a sys User and run this SQL.
[oracle@ebstest
admin]$ cd /u01/mmdb/db/tech_st/12.1.0/rdbms/admin
[oracle@ebstest
admin]$ ls odmpatch.sql
odmpatch.sql
[oracle@ebstest
admin]$ pwd
/u01/mmdb/db/tech_st/12.1.0/rdbms/admin
[oracle@ebstest
admin]$ Sqlplus sys / as
sysdba
SQL> @odmpatch.sql
ERROR:
ORA-01435: user does not exist
Model Util Package, qgen
Session altered.
Package created.
No errors.
Package created.
Package created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Library created.
No errors.
Package body created.
No errors.
Session altered.
Mining Transform (open source)
Session altered.
Package created.
Synonym created.
Grant succeeded.
Session altered.
Session altered.
Package created.
Synonym created.
Grant succeeded.
No errors.
Session altered.
Meta Code package (adaptor,sys,sec,exp code,superh/b)
Session altered.
Package created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Synonym created.
No errors.
Package body created.
No errors.
Package created.
Package body created.
No errors.
Package body created.
No errors.
Package body created.
Package created.
Grant succeeded.
Package created.
Synonym created.
Grant succeeded.
Package created.
No errors.
Package body created.
No errors.
Package body created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Session altered.
Association Rules
Session altered.
Package created.
No errors.
Package body created.
No errors.
Session altered.
O-Cluster
Session altered.
Package created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Session altered.
KM,SVM,NMF Trusted code
Session altered.
CREATE LIBRARY DMSVM_LIB wrapped
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE LIBRARY DMSVMA_LIB wrapped
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE OR REPLACE TYPE dmsvmbo wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmsvmbos AS TABLE OF dmsvmbo;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmsvmao wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmsvmaos AS TABLE OF dmsvmao;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
Package created.
No errors.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
CREATE LIBRARY DMNMF_LIB wrapped
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE OR REPLACE TYPE dmnmfbo wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmnmfbos AS TABLE OF dmnmfbo
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
Package created.
No errors.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
"-----------------------DMMODB
START--------------------------"
Library dropped.
Type dropped.
Type dropped.
Library created.
Type created.
Grant succeeded.
Type created.
Grant succeeded.
Package created.
No errors.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
"-----------------------DMMMODB
END---------------------------"
CREATE LIBRARY DMCL_LIB wrapped
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE OR REPLACE TYPE dmclbo wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmclbos AS TABLE OF dmclbo;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmclao wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
CREATE OR REPLACE TYPE dmclaos AS TABLE OF dmclao;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
Package created.
No errors.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
CREATE LIBRARY DMGLM_LIB wrapped
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE OR REPLACE TYPE dmglmbo wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmglmbos AS TABLE OF dmglmbo;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
Package created.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
Synonym created.
CREATE LIBRARY DMFE_LIB wrapped
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
CREATE OR REPLACE TYPE dmfebo wrapped
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
CREATE OR REPLACE TYPE dmfebos AS TABLE OF dmfebo;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or
table dependents
Grant succeeded.
Package created.
Type created.
No errors.
Type body created.
No errors.
Function created.
No errors.
Grant succeeded.
Synonym created.
Synonym created.
Session altered.
DBMS DM Internal, DBMS DM
Session altered.
Package created.
No errors.
Package body created.
No errors.
Package body created.
No errors.
Session altered.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Grant succeeded.
SP2-0310: unable to open file "dbmsdmbl.sql"
Session altered.
Package created.
Synonym created.
Grant succeeded.
No errors.
Session altered.
Session altered.
Package body created.
No errors.
Session altered.
SP2-0310: unable to open file "prvtdmj.plb"
Session altered.
PL/SQL procedure successfully completed.
Procedure created.
Commit complete.
PL/SQL procedure successfully completed.
Note: we can
ignore all above errors safely.
Sept-3
A) After
then we will run utlrp.sql for Validate all Invalid objects.
B) Go
to utlrp.sql location “/u01/mmdb/db/tech_st/12.1.0/rdbms/admin/utlrp.sql”.
C) Then
connection with sqlplus as a sys User and run this SQL.
SQL>!pwd
/u01/mmdb/db/tech_st/12.1.0/rdbms/admin
SQL>
SQL> @utlrp.sql
TIMESTAMP
-------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP
UTLRP_BGN 2020-01-16 18:52:18
DOC> The following PL/SQL block invokes
UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time
is proportional to the
DOC> number of invalid objects in the database,
so this command may take
DOC> a long time to execute on a database with a
large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track
recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid
objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status
IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects
compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or
parallel recompilation
DOC> based on the number of CPUs available
(parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter
parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC
nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create
jobs for parallel
DOC> recompilation. Jobs are created without
instance affinity so that they
DOC> can migrate across RAC nodes. Use the
following queries to verify
DOC> whether UTL_RECOMP jobs are being created
and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM
dba_scheduler_jobs
DOC> WHERE job_name like
'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are
running
DOC> SELECT job_name FROM
dba_scheduler_running_jobs
DOC> WHERE job_name like
'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL
procedure successfully completed.
TIMESTAMP
-------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP
UTLRP_END 2020-01-16 18:54:07
DOC> The
following query reports the number of objects that have compiled
DOC> with
errors.
DOC>
DOC> If the
number is higher than expected, please examine the error
DOC>
messages reported with each object (using SHOW ERRORS) to see if they
DOC>
point to system misconfiguration or resource constraints that must be
DOC>
fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH
ERRORS
-------------------
5
DOC> The
following query reports the number of errors caught during
DOC>
recompilation. If this number is non-zero, please query the error
DOC>
messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are
due to misconfiguration or resource constraints that must be
DOC>
fixed before objects can compile successfully.
DOC>#
ERRORS
DURING RECOMPILATION
---------------------------
5
Function
created.
PL/SQL
procedure successfully completed.
Function
dropped.
PL/SQL
procedure successfully completed.
Step-4
A)
Connection Sqlplus with sys / as sysdba user.
B)
Run Below mention Query and check Oracle Data Mining
version has upgraded in 12cR1.
SQL> set lines
133 pages 133
SQL> col
comp_name format a40
SQL>
SQL> select
comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
--------------------------------------------------
----------------------------
-----------
Oracle
Machine Generated Data 12.1.0.2.0 VALID
OLAP
Catalog 11.2.0.4.0 OPTION OFF
Oracle XML Database 12.1.0.2.0 VALID
Oracle
Text 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle
Multimedia 12.1.0.2.0 VALID
Oracle
Database Catalog Views 12.1.0.2.0 VALID
Oracle
Database Packages and Types 12.1.0.2.0 VALID
Oracle Real
Application Clusters 12.1.0.2.0 OPTION OFF
JServer JAVA
Virtual Machine 12.1.0.2.0 VALID
Oracle
XDK 12.1.0.2.0 VALID
Oracle
Database Java Packages 12.1.0.2.0 VALID
OLAP
Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP
API 12.1.0.2.0 VALID
Oracle Data Mining 12.1.0.2.0 VALID
15 rows
selected.
Source: Internal Research
