Ran dbua.sap.sh successfully
When running @?/rdbms/admin/utlrp.sql
I got:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> @?/rdbms/admin/utlu121s.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
CATCTL REPORT = /oracle/ER1/121/cfgtoollogs/ER1/upgrade/upg_summary.log
PL/SQL procedure successfully completed.
Function created.
Oracle Database 12.1 Post-Upgrade Status Tool 04-21-2016 11:56:28
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server VALID 12.1.0.2.0 00:30:18
Oracle XML Database INVALID 12.1.0.2.0 00:01:23
Final Actions 00:21:35
Post Upgrade 00:00:03
Post Compile 00:00:03
Total Upgrade Time: 01:15:05
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Database time zone version is 23. It meets current release needs.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Function dropped.
SQL>
SQL> --
SQL> -- Update Summary Table with con_name and endtime.
SQL> --
SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
2 con_name = SYS_CONTEXT('USERENV','CON_NAME'),
3 endtime = SYSDATE
4 WHERE con_id = -1;
1 row updated.
SQL> commit;
Commit complete.
SQL> @?/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL*PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
SQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem gviswana 06/25/02 - Add documentation
SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem rburns 11/12/01 - validate all components after compiles
SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
SQL> Rem rburns 09/29/01 - use 9.2.0
SQL> Rem rburns 09/20/01 - add check for CATPROC valid
SQL> Rem rburns 07/06/01 - get version from instance view
SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem skabraha 09/25/00 - validate is now a keyword
SQL> Rem kosinski 06/14/00 - Persistent parameters
SQL> Rem skabraha 06/05/00 - validate tables also
SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem rshaikh 09/22/99 - quote name for recompile
SQL> Rem ncramesh 08/04/98 - change for sqlplus
SQL> Rem usundara 06/03/98 - merge from 8.0.5
SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
SQL> Rem Mark Ramacher (mramache) was the original
SQL> Rem author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> @@utlprp.sql 0
SQL> Rem Copyright (c) 2003, 2014, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlprp.sql - Recompile invalid objects in the database
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem This script is typically used to recompile invalid objects
SQL> Rem remaining at the end of a database upgrade or downgrade.
SQL> Rem
SQL> Rem Although invalid objects are automatically recompiled on demand,
SQL> Rem running this script ahead of time will reduce or eliminate
SQL> Rem latencies due to automatic recompilation.
SQL> Rem
SQL> Rem This script is a wrapper based on the UTL_RECOMP package.
SQL> Rem UTL_RECOMP provides a more general recompilation interface,
SQL> Rem including options to recompile objects in a single schema. Please
SQL> Rem see the documentation for package UTL_RECOMP for more details.
SQL> Rem
SQL> Rem INPUTS
SQL> Rem The degree of parallelism for recompilation can be controlled by
SQL> Rem providing a parameter to this script. If this parameter is 0 or
SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate
SQL> Rem level of parallelism based on Oracle parameters cpu_count and
SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential
SQL> Rem recompilation is used. Please see the documentation for package
SQL> Rem UTL_RECOMP for more details.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem pyam 05/12/14 - Backport pyam_bug-18478064 from main
SQL> Rem pyam 04/08/14 - 18478064: factor out to reenable_indexes.sql
SQL> Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL
SQL> Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
SQL> Rem not needed.
SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
SQL> Rem cdilling 01/21/08 - add support for ORA-30552
SQL> Rem cdilling 08/27/07 - check disabled indexes only
SQL> Rem cdilling 05/22/07 - add support for ORA-38301
SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
SQL> Rem rburns 03/17/05 - use dbms_registry_sys
SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics
SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability
SQL> Rem rburns 09/20/04 - fix validate_components
SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here
SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808
SQL> Rem gviswana 05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-04-21 11:57:15
SQL>
SQL> DOC
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>#
SQL>
SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-04-21 11:57:19
SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
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>#
SQL> select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;
OBJECTS WITH ERRORS
-------------------
0
SQL>
SQL>
SQL> DOC
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>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
SQL>
SQL> Rem =====================================================================
SQL> Rem Reenable indexes that may have been disabled, based on the
SQL> Rem table SYS.ENABLED$INDEXES
SQL> Rem =====================================================================
SQL>
SQL> @@?/rdbms/admin/reenable_indexes.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/reenable_indexes.sql /st_rdbms_12.1/1 2014/05/20 10:49:30 pyam Exp $
SQL> Rem
SQL> Rem reenable_indexes.sql
SQL> Rem
SQL> Rem Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem reenable_indexes.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem <short description of component this file declares/defines>
SQL> Rem
SQL> Rem NOTES
SQL> Rem <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/reenable_indexes.sql
SQL> Rem SQL_SHIPPED_FILE:
SQL> Rem SQL_PHASE:
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE:
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem pyam 04/03/14 - Reenable indexes based on sys.enabled$indexes
SQL> Rem (formerly in utlprp.sql)
SQL> Rem pyam 04/03/14 - Created
SQL> Rem
SQL>
SQL> Rem
SQL> Rem Declare function local_enquote_name to pass FALSE
SQL> Rem into underlying dbms_assert.enquote_name function
SQL> Rem
SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
2 return varchar2 is
3 begin
4 return dbms_assert.enquote_name(str, FALSE);
5 end local_enquote_name;
6 /
Function created.
SQL> Rem
SQL> Rem If sys.enabled$index table exists, then re-enable
SQL> Rem list of functional indexes that were enabled prior to upgrade
SQL> Rem The table sys.enabled$index table is created in catupstr.sql
SQL> Rem
SQL> SET serveroutput on
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 p_null CHAR(1);
5 p_schemaname VARCHAR2(30);
6 p_indexname VARCHAR2(30);
7 rebuild_idx_msg BOOLEAN := FALSE;
8 non_existent_index exception;
9 recycle_bin_objs exception;
10 cannot_change_obj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
22 SELECT NULL INTO p_null FROM DBA_OBJECTS
23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
24 object_type = 'TABLE' and rownum <=1;
25
26 -- Select indices to be re-enabled
27 EXECUTE IMMEDIATE q'+
28 SELECT 'ALTER INDEX ' ||
29 local_enquote_name(e.schemaname) || '.' ||
30 local_enquote_name(e.indexname) || ' ENABLE'
31 FROM enabled$indexes e, ind$ i
32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
33 bitand(i.property, 16) != 0+'
34 BULK COLLECT INTO commands;
35
36 IF (commands.count() > 0) THEN
37 FOR i IN 1 .. commands.count() LOOP
38 BEGIN
39 EXECUTE IMMEDIATE commands(i);
40 EXCEPTION
41 WHEN NON_EXISTENT_INDEX THEN NULL;
42 WHEN RECYCLE_BIN_OBJS THEN NULL;
43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
44 END;
45 END LOOP;
46 END IF;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
51 IF rebuild_idx_msg THEN
52 BEGIN
53 DBMS_OUTPUT.PUT_LINE
54 ('The following indexes could not be re-enabled and may need to be rebuilt:');
55
56 OPEN reg_cursor FOR
57 'SELECT e.schemaname, e.indexname
58 FROM enabled$indexes e, ind$ i
59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
60
61 LOOP
62 FETCH reg_cursor INTO p_schemaname, p_indexname;
63 EXIT WHEN reg_cursor%NOTFOUND;
64 DBMS_OUTPUT.PUT_LINE
65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
66 END LOOP;
67 CLOSE reg_cursor;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
72 WHEN OTHERS THEN CLOSE reg_cursor; raise;
73 END;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP function local_enquote_name;
Function dropped.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
Warning: XDB now invalid, invalid objects found:
object_name object_type
-------------------------------------------------------
DBMS_SODA_ADMIN PACKAGE BODY
PL/SQL procedure successfully completed.
+++++++++++++++++++++++++++++++++++++++++++++++
SQL>se lect owner, object_name, object_type, status from dba_objects where status = 'INVALID' and owner in ('SYS', 'XDB');
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
----------------------- -------
XDB
DBMS_SODA_ADMIN
PACKAGE BODY INVALID
SQL> show errors package body DBMS_SODA_ADMIN;
No errors.
SQL> alter package DBMS_SODA_ADMIN compile;
alter package DBMS_SODA_ADMIN compile
*
ERROR at line 1:
ORA-04043: object DBMS_SODA_ADMIN does not exist
SQL> ALTER PACKAGE DBMS_SODA_ADMIN COMPILE ;
ALTER PACKAGE DBMS_SODA_ADMIN COMPILE
*
ERROR at line 1:
ORA-04043: object DBMS_SODA_ADMIN does not exist
SQL> ALTER PACKAGE DBMS_SODA_ADMIN COMPILE BODY;
ALTER PACKAGE DBMS_SODA_ADMIN COMPILE BODY
*
ERROR at line 1:
ORA-04043: object DBMS_SODA_ADMIN does not exist
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
How do I Install the PACKAGE DBMS_SODA_ADMIN which script should I run to install the package .