This discussion is archived
8 Replies Latest reply: Aug 8, 2012 10:03 AM by Eran_Steiner RSS

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

pvg Newbie
Currently Being Moderated
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)
    KuljeetPalSingh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points