8 Replies Latest reply: Aug 8, 2012 12:03 PM by Eran_Steiner RSS

    ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)

    pvg
      Hi,

      We recently installed 12c.r1 and have it running now form some three weeks. About 100 assets currently in it.

      When trying to add a new discovery profile a received an error message from the BUI, in the cacao log from the EC i found a lot java exceptions caused (probably by : Internal Exception: java.sql.SQLException: ORA-01653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS)

      When looking at the alert log from the database i found its full with ORA-1653 and ORA-1654 messages; (and still those errors are being put in the alert logfile on a continues basis.)
      .
      .
      ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
      ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
      ORA-1653: unable to extend table OC.VDO_SERVICE_INFO by 128 in tablespace OC_DEFAULT_TS
      ORA-1653: unable to extend table OC.VDO_SERVICE_INFO by 128 in tablespace OC_DEFAULT_TS
      ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
      ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
      .
      .

      And

      ORA-1654: unable to extend index OC.VMB_RESOURC_ASSOCIA_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
      ORA-1654: unable to extend index OC.VMB_RESOURC_ASSOCIA_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
      ORA-1654: unable to extend index OC.VMB_RESOURC_ASSOCIA_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
      ORA-1654: unable to extend index OC.VMB_RESOURCE_CAPABIL1_UNQIDX by 128 in tablespace OC_DEFAULT_TS
      ORA-1654: unable to extend index OC.VMB_RESOURCE_CAPABIL1_UNQIDX by 128 in tablespace OC_DEFAULT_TS
      ORA-1654: unable to extend index OC.VMB_RESOURCE_CAPABIL1_UNQIDX by 128 in tablespace OC_DEFAULT_TS
      .
      .

      Only thing i could think of would be a space issue in the filesystem. But there's still some 15G of free space available for the DB to extend.

      Any clues as to where to find the cause of this?

      Thanks in advance

      Kind regards

      Patrick
        • 1. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
          kuljeet singh -
          i found its full with ORA-1653 and ORA-1654 messages
          Space issue ! check space at OS filesystem or ASM DG level if you see space available at database to extend the files.
          its possible that space not available at Diskgroup or filesystem thats why oracle is not able to extend the datafiles.
          • 2. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
            pvg
            Hi,

            Sorry for the late response (wasn't in the office last week)

            I'v extended the zpool with additional LUN's , now there is 168GB of free space (total DB size now 42GB) so, efficient free space should be available. After a restart of the DB unfortunately again the alert file is flooded with ORA-1653 / 64 messages on a continues basis;

            .
            .

            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_ALERT_MONITOR_ST1_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_ALERT_MONITOR_ST1_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            Mon Jul 16 13:56:46 2012
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            Mon Jul 16 13:56:55 2012
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            ORA-1654: unable to extend index OC.VDO_SENSOR_INFO_ID_UNQIDX by 8 in tablespace OC_DEFAULT_TS
            Mon Jul 16 13:57:02 2012
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            ORA-1653: unable to extend table OC.PERSISTENTALERT by 8 in tablespace OC_DEFAULT_TS
            .
            .
            etc,.....etc,......etc,.....

            Unsure what to do.

            Check the PCT_USED with a script and found;

            NAME MBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN

            USERS 5 1.31 3.69 26.25 3.69 32767.98 0 LOCAL AUTO
            OC_INDEX_TS 100 1 99 1 99 32767 0 LOCAL AUTO
            OC_DATA_TS 100 1 99 1 99 32767 0 LOCAL AUTO
            TEMP 174 174 0 100 0 32767.98 .53 LOCAL MANUAL
            SYSTEM 720 711.31 8.69 98.79 8 32767.98 2.17 LOCAL MANUAL
            SYSAUX 1230 1148.44 81.56 93.37 64.44 32767.98 3.5 LOCAL AUTO
            UNDOTBS1 7625 445.75 7179.25 5.85 3656 32767.98 1.36 LOCAL MANUAL
            OC_DEFAULT_TS 32767 32767 0 100 0 32767 100 LOCAL AUTO

            8 rows selected.

            Seems the OC_DEFAULT_TS is 100% full.

            Shouldn't this autoextend?!?

            I'm no DBA, and the OPCenter installation is default 'out-of-the-box' on a new system. Only running for a month now with about 100 assets.

            Any help appreciated

            Thanks

            Patrick

            Edited by: Patrick on Jul 16, 2012 3:13 PM

            Edited by: Patrick on Jul 16, 2012 3:15 PM
            • 3. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
              pvg
              Got some steps further.

              Seems there is some info related to this at another customer. (with a lot more assets than i currently have) The following was documented on that case;

              ------

              Table Space Issues
              OC12c has only one table containing data with virtual partitions into it. as part of the rollup into hourly,daily .. we scan the rm_resource_trend_daily looking for records that fit the timeframe we are looking for. Unfortunately, we only cleanup data once all the pending rollups have been complete. To do a rollup we iterate through all the assets [with a default 1 second between each asset] this timeout is controlled in /opt/sun/n1gc/etc/reportserviceproperties.xml <entry key="repsvc.rollup-pause-millis">1000</entry>

              so if we have > 3600 assets we can not finish the hourly rollup within an hour, hence we never clean out the old data and each rollup takes progressively longer as we scan through a larger and larger table.
              reducing the pause and cleaning out old data should help.. [have action plan to the customer and will update once i hear back] need to reevaluate how we scan the table and see if there is a way to detect / clearup the old data automatically to prevent filling up the whole tablespace.
              To change the MAXSIZE on the tablespace, you have to login as the DBA. The OC user will not have permissions to do this. On an EC with a local db, you can do that like this:

              # su - oracleoc
              $ sqlplus / as sysdba
              SQL> alter database datafile '/var/opt/sun/xvm/oracle/oradata/OCDB/ocdefault01.dbf' autoextend on maxsize unlimited;

              Or, if there is not enough room on that disk and you want to use another disk, add a 2nd datafile to the tablespace. Again, this needs to be done as the DBA, for example:

              # su - oracleoc
              $ sqlplus / as sysdba
              SQL> alter tablespace oc_default_ts add datafile '<path to second_file>/ocdefault02.dbf' size 2G autoextend on maxsize 32G;

              ------

              Now i started by issuing the first command ( SQL> alter database datafile '/var/opt/sun/xvm/oracle/oradata/OCDB/ocdefault01.dbf' autoextend on maxsize unlimited; )
              As such the command completed succesful but een after a restart of the DB nothing changed, still getting loads of ORA-1653/54 errors in the alert file.
              Then tried the second one (adding a datafile) After completed no more error messages in the alertlog.

              Since i'm no DBA i have difficulties understanding why command-1 did not do the trick. There is more than 160G free space within the FS for the DB to grow)

              As opposed to the case with the other customer (with over 3600 assets) i'm also concerned as to what makes the DB grow at such a rate (with only 100 assets in OPSC in my case)

              I will be monitoring closely for the coming period as we are adding ore assets in the coming week.

              Kind regards

              Patrick
              • 4. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
                Eran_Steiner
                There is a known issue which prevents some database cleanup and can lead to what you're seeing (Bug 717282). This is fixed already in Update 1, but until you install update 1 - you have the right workaround.
                • 5. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
                  954384
                  I am seeing this with 12.1.1.0 as well. Update 1 did not fix this issue.
                  • 6. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
                    Eran_Steiner
                    Yes, we have identified another issue around this area and are investigating it right now. Please stay tuned.
                    • 7. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
                      954384
                      Can you give any details on what causes this or a timeline on a solution?
                      • 8. Re: ORA-1653 (unable to extend table) and ORA-1654  (unable to extend index)
                        Eran_Steiner
                        It seems like in certain conditions, the daily monitoring data that is being gathered for each of the agents is gathering a lot more information than expected. For example, if each agent sees all disks of all the other machines - it will incorrectly send the monitoring data for those disks. This causes identical data to be reported by all machines.

                        Please note that this is still work in progress and it is actively being investigated right now. The real root cause may end up being something else.