14 Replies Latest reply: Aug 23, 2012 8:31 AM by Srini Chavali-Oracle RSS

    DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.

    Prashant Dixit
      Hi All,


      While doing upgrade activity on my 'Test" machines receiving some PL/SQL related errors when trying to execute *utlu112i.sql* script:
      I'm trying to update my Oracle 10g Enterprise Edition Release 10.2.0.1.0 to Oracle 11g Enterprise Release 11.2.0
      (I already have the 10g r2 installed on the machine with 11g software installed (not instance for 11g))

      This is what exactly happening when executing Pr-requisite check script which i copied to /tmp directory from ORACLE _BASE -- /u01/app/oracle/oracle/product/product/11.2.0/db_1/rdbms/admin

      [oracle@localhost tmp]$ ls -ltr
      total 320
      -rw-r--r-- 1 oracle oinstall 185744 Jul 23 2009 utlu112i.sql





      [oracle@localhost tmp]$ sql

      SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 24 22:44:30 2012

      Copyright (c) 1982, 2005, Oracle. All rights reserved.


      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options

      *SQL> @utlu112i.sql*
      *DECLARE*
      ***
      *ERROR at line 1:*
      *ORA-06502: PL/SQL: numeric or value error*
      *ORA-06512: at line 3652*

      SQL>


      I tried to open the script in editor to investigate the problem happened at line 3652 as highlighted at SQL prompt.
      And this is what it read and i fail to interpret what exactly does it mean.

      --------------------------------------------------------------------------------------------------------
      IF sum_bytes IS NOT NULL THEN
      idx:=idx + 1;
      *rs_info(idx).tbs_name := p_tsname;* /* <------ Line 3652 */
      rs_info(idx).seg_name := rs.segment_name;
      rs_info(idx).status := rs.status;
      rs_info(idx).next := rs.next_extent/1024;
      rs_info(idx).max_ext := rs.max_extents;
      rs_info(idx).status := rs.status;
      rs_info(idx).inuse := sum_bytes;
      SELECT ROUND(SUM(decode(maxbytes, 0, 0,maxbytes-bytes)/1024))
      INTO rs_info(idx).auto
      FROM sys.dba_data_files
      WHERE tablespace_name=p_tsname;
      END IF;
      END LOOP;
      END IF; -- using undo tablespace, not rollback
      --------------------------------------------------------------------------------------------------------


      I'm struck and have to perform this Upgrade activity before i move ahead and execute some requisite patching.
      Please Assist



      Thanks
      Prash'ant' Dixit
        • 1. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
          Srini Chavali-Oracle
          If you want to upgrade via DBUA or manual scripts, you need to be at version 10.2.0.2

          http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#i1007814

          If you cannot apply the 10.2.0.2 (or later) patch, then your only option is to upgrade using expdp and impdp

          HTH
          Srini
          • 2. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
            AminChavarria
            You can download last version of script utlu11xi from Note: 884522.1

            regards
            amin
            • 3. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
              Prashant Dixit
              Thanks Srini for providing related Oracle Docs.

              But now after applying Patch Number: *4547817* to update current version from Oracle 10.2.0.1 To 10.2.0.2, i'm started getting some alien issues.
              After installing said Patch, now it's fail to recognize "sqlplus" and other commands - "lsnrctl" etc.

              This is what exactly happening when tried to run general commands like sqlplus, lsnrctl
              --------------------------------------------------------------------------------------------------------------------------------
              [oracle@localhost root]$ sqlplus / as sysdba
              [oracle@localhost root]$
              [oracle@localhost root]$ lsnrctl status
              [oracle@localhost root]$
              [oracle@localhost root]$

              All because of this I've failed to upgrade from 10.2.0.1 to 10.2.0.2 using DBUA as not able to reach sqlplus prompt.


              Please Reply.


              Thanks
              Prash 'ant' Dixit
              • 4. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                Prashant Dixit
                Guys,

                Don't know how to ask this, But being a novice user of OTN i think i can.

                How to paste SQL snippets from terminals to our Posts ?

                Example:
                One of user Kapil posted his SQL results in the same way what i'm trying to do:
                11g migraion failed sql queries


                Thanks
                Prash 'ant' Dixit
                • 5. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                  Srini Chavali-Oracle
                  Pl indicate the steps you performed for the upgrade - did you complete all of the steps in the README of the patch without any errors ?

                  >
                  [oracle@localhost root]$ sqlplus / as sysdba
                  >

                  You should never invoke Oracle binaries as root - in fact one should never login to a system as root.

                  HTH
                  Srini
                  • 6. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                    Srini Chavali-Oracle
                    Click on the FAQ link on the top right

                    HTH
                    Srini
                    • 7. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                      Prashant Dixit
                      Srini,

                      No, i was in using one of the OS user account 'oracle'.

                      What raised my eyebrow was the patch size - 4547817. It was around 753 Mb's and when unzipped created a directory named DISK1 with some executable files including .runInstaller.
                      When executed .runInstaller it brings the same old installer widget in front which asks to provide Home and Base.


                      Well, below are the steps i followed as per README file:
                      -----------------------------------------------------------------------------
                      Set Variables in System:

                      $ ORACLE_HOME=oracle_home
                      $ ORACLE_SID=sid
                      $ export ORACLE_HOME ORACLE_SID

                      # Stop listener service and shutdown all instances on the database.

                      # Enter the following commands to start Oracle Universal Installer, where patchset_directory is the directory where you unzipped the patch set software:

                      % cd patchset_directory/Disk1
                      % ./runInstaller

                      # On the Welcome screen, click Next.
                      # On the Specify File Locations screen, click Browse next to the Path field in the Source section.
                      # Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. For example:
                      directory_path/stage/products.xml

                      # In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.

                      # On the Summary screen, click Install.

                      When prompted, run the $ORACLE_HOME/root.sh script as the root user.
                      ----------------------------------------------------------------------------------------------


                      Next step recommended is to start database in NOMOUNT mode but i failed to launch the sqlplus from terminal and it's not yielding to my inputs.
                      SQL> STARTUP NOMOUNT


                      Below is the inside structure of Opatch/Disk1 directory
                      -----------------------------------------------------------------
                      [oracle@localhost OPatch]$ cd Disk1/
                      [oracle@localhost Disk1]$ ls
                      10202_buglist.htm install patchnote.htm response runInstaller stage



                      Thanks
                      Prash 'ant' Dixit
                      • 8. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                        Srini Chavali-Oracle
                        Pl post your exact OS version, along with the log file from the install of patch 4547817. Looks like he patch application was incomplete or incorrect.

                        HTH
                        Srini
                        • 9. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                          Prashant Dixit
                          Srini,


                          Below are the original DB version details (Before patching):

                          SQL> select * from v$version;

                          BANNER
                          ----------------------------------------------------------------
                          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
                          PL/SQL Release 10.2.0.1.0 - Production
                          CORE 10.2.0.1.0 Production
                          TNS for Linux: Version 10.2.0.1.0 - Production
                          NLSRTL Version 10.2.0.1.0 - Production

                          This is what OPatch status reflects:
                          =============================
                          Installed Top-level Products (2):

                          Oracle Database 10g 10.2.0.1.0
                          Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
                          There are 2 products installed in this Oracle Home.

                          There are no Interim patches installed in this Oracle Home.


                          What OPatch Log File Reads:
                          ==========================

                          [oracle@localhost opatch]$ pwd
                          /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch
                          [oracle@localhost opatch]$ vi opatch-30_Jul_27_01-47-02-GMT+05_Fri.log

                          SEVERE:OPatch invoked as follows: 'lsinventory '
                          INFO:
                          Oracle Home : /u01/app/oracle/product/10.2.0/db_1
                          Central Inventory : /u01/app/oraInventory
                          from : /u01/app/oracle/product/10.2.0/db_1/oraInst.loc
                          OPatch version : 10.2.0.2.0
                          OUI version : 10.2.0.2.0
                          OUI location : /u01/app/oracle/product/10.2.0/db_1/oui
                          Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch-30_Jul_27_01-47-02-GMT+05_Fri.log

                          INFO:Starting LsInventorySession at Fri Jul 27 01:47:02 GMT+05:30 2012
                          INFO:Lsinventory Output file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory-30_Jul_27_01-47-02-GMT+05_Fri.txt

                          INFO:--------------------------------------------------------------------------------
                          INFO:Installed Top-level Products (2):

                          INFO:Oracle Database 10g 10.2.0.1.0
                          INFO:Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
                          INFO:There are 2 products installed in this Oracle Home.

                          INFO:
                          INFO:There are no Interim patches installed in this Oracle Home.
                          INFO:
                          INFO:--------------------------------------------------------------------------------
                          INFO:Finishing LsInventorySession at Fri Jul 27 01:47:03 GMT+05:30 2012
                          ~



                          Thanks
                          Prash'ant' Dixit
                          • 10. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                            Srini Chavali-Oracle
                            Pl post your exact OS version and confirm that you have completed all of the steps in the patch README files without errors.

                            HTH
                            Srini
                            • 11. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                              Prashant Dixit
                              Below are the OS details:
                              Yes, I'm following what all provided in README file. Not received any errors while installing the patch.


                              [oracle@localhost ~]$ uname -a
                              Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:54 EDT 2009 i686 i686 i386 GNU/Linux
                              [oracle@localhost ~]$


                              [oracle@localhost ~]$ cat /proc/meminfo
                              MemTotal: 1092032 kB
                              MemFree: 26760 kB
                              Buffers: 44448 kB
                              Cached: 618436 kB
                              SwapCached: 4732 kB
                              Active: 831516 kB
                              Inactive: 170828 kB
                              HighTotal: 188352 kB
                              HighFree: 1204 kB
                              LowTotal: 903680 kB
                              LowFree: 25556 kB
                              SwapTotal: 2195448 kB
                              SwapFree: 2095748 kB
                              Dirty: 280 kB
                              Writeback: 0 kB
                              AnonPages: 334756 kB
                              Mapped: 407400 kB
                              Slab: 36188 kB
                              PageTables: 17600 kB
                              NFS_Unstable: 0 kB
                              Bounce: 0 kB
                              CommitLimit: 2741464 kB
                              Committed_AS: 2415188 kB
                              VmallocTotal: 114680 kB
                              VmallocUsed: 4720 kB
                              VmallocChunk: 109816 kB
                              HugePages_Total: 0
                              HugePages_Free: 0
                              HugePages_Rsvd: 0
                              Hugepagesize: 4096 kB
                              [oracle@localhost ~]$


                              Thanks
                              Prash'ant' Dixit
                              • 12. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                                Srini Chavali-Oracle
                                After you install the patch, you have to upgrade the database, as per steps in the README - has this been done ?

                                HTH
                                Srini
                                • 14. Re: DB Upgrade: ORA-06502: PL/SQL: numeric or value error & ORA-06512.
                                  Srini Chavali-Oracle
                                  Glad to hear - pl post details of your solution as it may help others in the future

                                  Srini