1 2 Previous Next 24 Replies Latest reply on Sep 4, 2015 5:04 PM by Marco Gralike

    identifier 'DBMS_SODA_ADMIN' must be declared

    2911303

      Why getting this error?

      Although I have applied oracle recommended patch.

       

      rollback 20831110 -- Completed. (PSU)

       

      apply 21188742 -- Completed (DB System)

       

      apply 21354009 -- Completed. (Merge Patch)

        • 2. Re: identifier 'DBMS_SODA_ADMIN' must be declared
          dmcmahon-Oracle

          Sorry you're having trouble. I've got a 12.1 database with the patch applied and everything looks as it should. DBMS_SODA_ADMIN is a public synonym for PL/SQL package XDB.DBMS_SODA_ADMIN. When I log in to my database using sqlplus and do

           

          describe DBMS_SODA_ADMIN

           

          I see the expected package methods shown.

           

          Note that I've granted RESOURCE to the user I connect as, which includes the SODA_APP privilege needed to actually execute the methods.

          • 3. Re: identifier 'DBMS_SODA_ADMIN' must be declared
            2911303

            I already have latest patch

             

             

            [oracle@prs-lin-281-njfh-dctm 20885778]$ opatch apply

            Oracle Interim Patch Installer version 12.1.0.1.7

            Copyright (c) 2015, Oracle Corporation.  All rights reserved.

             

             

             

             

            Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1

            Central Inventory : /u01/app/oraInventory

               from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc

            OPatch version    : 12.1.0.1.7

            OUI version       : 12.1.0.2.0

            Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/20885778_Aug_22_2015_08_02_54/apply2015-08-22_08-02-54AM_1.log

             

             

            Applying interim patch '20885778' to OH '/u01/app/oracle/product/12.1.0/dbhome_1'

            Verifying environment and performing prerequisite checks...

             

             

            The following patch(es) are duplicate patches with patches installed in the Oracle Home.

            [ 20885778]

            You have already installed better patch(es) with higher UPI(s) or later version(s).

            These patch(es) will be skipped.

             

             

            OPatch system modification phase did not start:

            Log file location: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/20885778_Aug_22_2015_08_02_54/apply2015-08-22_08-02-54AM_1.log

             

             

            OPatch stopped on request.

            [oracle@prs-lin-281-njfh-dctm 20885778]$

            • 4. Re: identifier 'DBMS_SODA_ADMIN' must be declared
              2911303

              I have granted resource role to my schema 'dcat_dev'

               

              but still having issue. when I do -  describe DBMS_SODA_ADMIN

               

              DCAT_DEV_ERROR.png

              • 5. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                dmcmahon-Oracle

                My knowledge of opatch is approximately zero. Did you perform all the post-installation steps for the patch?

                 

                It's been months since I did this so my memory's a bit hazy, but don't some SQL scripts need to be run after applying this patch?

                 

                I believe the SODA SQL objects are created by running $ORACLE_HOME/rdbms/admin/catsodacoll.sql. If after

                re-reading the release notes for the patch, you don't see any steps you've missed out, and your database is a

                dev or test database, you could try running that script as SYS to create the SODA objects.

                • 6. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                  mdrake-Oracle

                  It sound like you did not run the datapatch part of the patch installation process.

                   

                  2.9.1 Run the datapatch Utility

                  Perform the following steps:

                  1. For each separate database running on the Oracle home being patched, run the datapatch utility as described in Table 5. If this is Oracle RAC, run datapatch on only one instance. Table 5 Steps to Run the datapatch Utility for Standalone DB Versus Single/Multitenant (CDB/PDB) DB
                    StepsStandalone DBStepsSingle/Multitenant (CDB/PDB) DB
                    1% sqlplus /nolog1% sqlplus /nolog
                    2SQL> Connect / as sysdba2SQL> Connect / as sysdba
                    3SQL> startup3SQL> startup
                    4SQL> quit4SQL> alter pluggable database all open;Foot 1
                    5% cd $ORACLE_HOME/OPatch5SQL> quit
                    6% ./datapatch -verbose6% cd $ORACLE_HOME/OPatch
                    7% ./datapatch -verbose
                    Footnote 1 It is recommended the Post Install step be run on all pluggable databases; however, the following command (SQL> alter pluggable database PDB_NAME open ) could be substituted to only open certain PDBs in the single/multitenant database. Doing so will result in the Post Install step only being run on the CDB and opened PDB's. To update a pluggable database at a later date (skipped or newly plugged in), open the database using the alter pluggable database command mentioned previously and rerun the datapatch utility. See My Oracle Support Document 1935365.1 Multitenant Unplug/Plug Best Practices for more information about the procedure for unplugging/plugging with different patch releases (in both directions).The datapatch utility will then run the necessary apply scripts to load the modified SQL files into the database. An entry will be added to the dba_registry_sqlpatch view reflecting the patch application. In the dba_registry_sqlpatch view, verify the Status for the APPLY is "SUCCESS". For any other status, refer to the following My Oracle Support note for additional information and actions: Document 1609718.1 Datapatch Known Issues.
                  2. Check the following log files in $ORACLE_HOME/sqlpatch/21125181/ for errors:21125181_rollback_<database SID>_<CDB name>_<timestamp>.log
                  • 7. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                    Marco Gralike

                    dmcmahon-Oracle wrote:

                     

                    My knowledge of opatch is approximately zero. Did you perform all the post-installation steps for the patch?

                     

                     

                    The following might help: Oracle 12.1.0.2.x – JSON Database Patch Bundle(s)

                    • 8. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                      2911303

                      [oracle@prs-lin-281-njfh-dctm OPatch]$ ./datapatch -verbose

                      SQL Patching tool version 12.1.0.2.0 on Fri Sep  4 05:13:35 2015

                      Copyright (c) 2015, Oracle.  All rights reserved.

                       

                       

                      Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_106518_2015_09_04_05_13_35/sqlpatch_invocation.log

                       

                       

                      Connecting to database...OK

                      Note:  Datapatch will only apply or rollback SQL fixes for PDBs

                             that are in an open state, no patches will be applied to closed PDBs.

                             Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

                             (Doc ID 1585822.1)

                      Bootstrapping registry and package to current versions...done

                      Determining current state...done

                       

                       

                      Current state of SQL patches:

                      Patch 21354009 ():

                        Installed in binary and CDB$ROOT DCATXJP1 DCAT_DEV PDB$SEED

                      Bundle series DBBP:

                        ID 10 in the binary registry and ID 10 in PDB CDB$ROOT, ID 10 in PDB PDB$SEED, ID 10 in PDB DCATXJP1, ID 10 in PDB DCAT_DEV

                      Bundle series PSU:

                        Not installed in the binary registry and not installed in any PDB

                       

                       

                      Adding patches to installation queue and performing prereq checks...

                      Installation queue:

                        For the following PDBs: CDB$ROOT PDB$SEED DCATXJP1 DCAT_DEV

                          Nothing to roll back

                          Nothing to apply

                       

                       

                      SQL Patching tool complete on Fri Sep  4 05:14:28 2015

                      [oracle@prs-lin-281-njfh-dctm OPatch]$

                      • 9. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                        2911303

                        executed  $ORACLE_HOME/rdbms/admin/catsodacoll.sql

                        • 10. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                          2911303

                          Still receiving the same error..

                          I wanted to know, what all the minimum permissions needed for the app account.

                           

                          **********************************************

                          I did one experiment, :-) -- Me as a DBA.

                           

                          I have unloked, Oracle own account xdb schema, because xdb schema owns all the object DBMS_SODA_ADMIN. and shared this xdb credential to my development team,

                          They are working with xdb schema, they are not getting any error.


                          I know this is temporary solution, because they may create objects in xdb schema, which it should not be.


                          Please help me identify permanent solution.


                          I wanted to know, what all the minimum permissions needed for the app account. here my app account is 'dcat_dev'

                          • 11. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                            mdrake-Oracle

                            OK

                             

                            First drop the database, it is now in an unsupported state...

                             

                            Next, redownload the patch, unfortunately the first version that was published was not correct.

                             

                            Redo OPatch and data patch,

                            • 12. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                              2911303

                              "First drop the database, it is now in an unsupported state..." Please clarify.


                              Which database version is correct?

                               

                              Can you please call me on

                               

                              +91.9013980869

                              +91.9599055821

                               

                              OR Please share you no. I will call.

                              • 13. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                                mdrake-Oracle

                                Basically by attempting to run the scripts manually you have got yourself into a state where the database is outside of a supported configuration...

                                 

                                If you need direct support please open a support request with Oracle... You may post the SR number here.

                                 

                                Approx what date you download the patch ?

                                • 14. Re: identifier 'DBMS_SODA_ADMIN' must be declared
                                  mdrake-Oracle

                                  Amy database where XDB has been unlocked, and objects other than those supplied by Oracle have been created in the XDB schema is unsupported... At some point something bad will happen during a subsequent upgrade or downgrade operation. The best bet would be to restore from a backup. I assume you took one before starting on a course like the one you have followed.

                                  1 2 Previous Next