Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: Filesystem got full, i cannot start the Oracle database

    Hey guys,

    I am here in a deadlock situation.

    Because i set my tablespaces with autoextend. They consumed all the space from my filesystem and my database went down.

    vanna04-stg-298-1:/vans000082dbdata
    255G 255G 14M 100% /data_raidsit

    I am using Oracle 11.2.0 in a solaris server running SunOS 5.10 Generic_147441-09 i86pc i386 i86pc

    The actual tablespaces are being managed by Oracle.

    raid@vans000082:/data_sit/RAIDSIT> ls -ltr
    total 534550104
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_sit_08
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_idsit_09
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_12
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_07
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_11
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_10
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_04
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_02
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_06
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_13
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_03
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_05
    -rwx------ 1 oracle oinstall 53687091200 Sep 25 16:45 data_raidsit_14
    -rwx------ 1 oracle oinstall 53687091200 Sep 26 10:56 data_raidsit_01


    My question is, exists some way to free space from the tablespaces with the database down, in order to get enough space in the filesystem to start the database? Because i dont have the option to increase the filesystem and is not possible to start the Oracle server with the filesystem full.

    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post tail end of alert_SID.log file that shows DB startup failure
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2012
    Posts
    5

    tail -100 /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/alert_RAIDSIT.log

    root@vans000082-app#>tail -100 /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/alert_RAIDSIT.log

    WARNING: Write Failed. group:1 disk:1 AU:6803 offset:532480 size:57344
    WARNING: failed to write mirror side 1 of virtual extent 1346 logical extent 0 of file 257 in group 1 on disk 3 allocation unit 6778
    WARNING: failed to write mirror side 1 of virtual extent 38 logical extent 0 of file 257 in group 1 on disk 0 allocation unit 58
    error=15081 txt: ''
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw7_23400.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw5_23396.trc:
    ORA-27063: number of bytes read/written is incorrect
    Solaris-AMD64 Error: 28: No space left on device
    Additional information: -1
    Additional information: 57344
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw2_23390.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw4_23394.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: failed to write mirror side 1 of virtual extent 1660 logical extent 0 of file 257 in group 1 on disk 1 allocation unit 6802
    WARNING: Write Failed. group:1 disk:1 AU:6803 offset:663552 size:57344
    WARNING: failed to write mirror side 1 of virtual extent 2684 logical extent 0 of file 257 in group 1 on disk 0 allocation unit 6872
    WARNING: failed to write mirror side 1 of virtual extent 921 logical extent 0 of file 257 in group 1 on disk 11 allocation unit 6746
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw7_23400.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw5_23396.trc:
    ORA-27063: number of bytes read/written is incorrect
    Solaris-AMD64 Error: 28: No space left on device
    Additional information: -1
    Additional information: 40960
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw2_23390.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw4_23394.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: Write Failed. group:1 disk:12 AU:7356 offset:892928 size:40960
    WARNING: failed to write mirror side 1 of virtual extent 1659 logical extent 0 of file 257 in group 1 on disk 9 allocation unit 6801
    WARNING: failed to write mirror side 1 of virtual extent 2 logical extent 0 of file 258 in group 1 on disk 8 allocation unit 90
    WARNING: failed to write mirror side 1 of virtual extent 5243 logical extent 0 of file 257 in group 1 on disk 9 allocation unit 7060
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw5_23396.trc:
    ORA-27063: number of bytes read/written is incorrect
    Solaris-AMD64 Error: 28: No space left on device
    Additional information: -1
    Additional information: 57344
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw7_23400.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw2_23390.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw4_23394.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: Write Failed. group:1 disk:8 AU:7358 offset:688128 size:57344
    WARNING: failed to write mirror side 1 of virtual extent 3072 logical extent 0 of file 257 in group 1 on disk 8 allocation unit 6905
    WARNING: failed to write mirror side 1 of virtual extent 1673 logical extent 0 of file 257 in group 1 on disk 9 allocation unit 6802
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw5_23396.trc:
    ORA-27063: number of bytes read/written is incorrect
    Solaris-AMD64 Error: 28: No space left on device
    Additional information: -1
    Additional information: 155648
    WARNING: failed to write mirror side 1 of virtual extent 1660 logical extent 0 of file 257 in group 1 on disk 1 allocation unit 6802
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw7_23400.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw2_23390.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: Write Failed. group:1 disk:7 AU:7357 offset:524288 size:155648
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw4_23394.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: failed to write mirror side 1 of virtual extent 1659 logical extent 0 of file 257 in group 1 on disk 9 allocation unit 6801
    WARNING: failed to write mirror side 1 of virtual extent 1665 logical extent 0 of file 257 in group 1 on disk 12 allocation unit 6800
    WARNING: failed to write mirror side 1 of virtual extent 5242 logical extent 0 of file 257 in group 1 on disk 8 allocation unit 7060
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw5_23396.trc:
    ORA-27063: number of bytes read/written is incorrect
    Solaris-AMD64 Error: 28: No space left on device
    Additional information: -1
    Additional information: 57344
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw7_23400.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw2_23390.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: Write Failed. group:1 disk:7 AU:7357 offset:32768 size:57344
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw4_23394.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: failed to write mirror side 1 of virtual extent 1241 logical extent 0 of file 257 in group 1 on disk 6 allocation unit 6768
    WARNING: failed to write mirror side 1 of virtual extent 481 logical extent 0 of file 257 in group 1 on disk 10 allocation unit 128
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw5_23396.trc:
    ORA-27063: number of bytes read/written is incorrect
    Solaris-AMD64 Error: 28: No space left on device
    Additional information: -1
    Additional information: 57344
    WARNING: failed to write mirror side 1 of virtual extent 5242 logical extent 0 of file 257 in group 1 on disk 8 allocation unit 7060
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw2_23390.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    Errors in file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_dbw7_23400.trc:
    ORA-15080: synchronous I/O operation to a disk failed
    WARNING: Write Failed. group:1 disk:9 AU:6802 offset:532480 size:57344
    Tue Sep 25 17:45:50 2012
    System state dump requested by (instance=1, osid=23392 (DBW3)), summary=[abnormal instance termination].
    System State dumped to trace file /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_diag_23378.trc
    Dumping diagnostic data in directory=[cdmp_20120925174550], requested by (instance=1, osid=23392 (DBW3)), summary=[abnormal instance termination].
    Tue Sep 25 17:45:57 2012
    ORA-1092 : opitsk aborting process
    Tue Sep 25 17:45:58 2012
    License high water mark = 33
    Instance terminated by DBW3, pid = 23392
    USER (ospid: 23521): terminating the instance
    Instance terminated by USER, pid = 23521

  4. #4
    Join Date
    Sep 2012
    Posts
    5

    tail -100 /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_diag_23378.trc

    root@vans000082-app#>tail -100 /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/RAIDSIT_diag_23378.trc
    (post info) last post received: 0 0 0
    last post received-location: No post
    last process to post me: none
    last post sent: 0 0 0
    last post sent-location: No post
    last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x83ca22f20
    O/S info: user: , term: , ospid: (DEAD)
    OSD pid info: Unix process pid: 0, image: PSEUDO
    PSO child state object changes :
    Dump of memory from 0x000000083C8D8410 to 0x000000083C8D8618
    83C8D8410 00000000 00000000 00000000 00000000 [................]
    Repeat 31 times
    83C8D8610 00000000 00000000 [........]
    BEGIN DUMP RESERVED PROCESSES
    Process 65: rsv=1, in use=0
    reservation idx=65, number=1, time=3972128077
    time reserved=0 secs, req=0x84ffe5b98
    (osp req) type=2(BACKGROUND) flags=0x30001(STATIC/RESERVE) state=3(SPAWNED) err=0
    pname=M000 pg=129(default) arg1=0 arg2=(null) reply=(null)
    pid=0 parent=0x80f5f8148 fulfill=0x0
    rsv idx=65 num=1 time=3972128077
    requestor name=oracle@vans000082-app (MMON) pid=23414 opid=24
    END DUMP RESERVED PROCESSES
    NO DETACHED BRANCHES.
    NO DETACHED NETWORK CONNECTIONS.
    CLEANUP STATE OBJECTS:
    ----------------------------------------
    SO: 0x828334020, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: instance enqueue anchor state
    latch: 0x6000d3f0
    ----------------------------------------
    SO: 0x8283340c0, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: switchable channel handle anch
    latch: 0x6000ed38
    ----------------------------------------
    SO: 0x828334160, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: ksv remote reaper holder
    latch: 0x6000efc0
    ----------------------------------------
    SO: 0x828334200, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: ksv remote slave holder
    latch: 0x6000f408
    ----------------------------------------
    SO: 0x8283342a0, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: ksv local slave morg
    latch: 0x6000f408
    ----------------------------------------
    SO: 0x828334340, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: TT shared object cleanup SO
    latch: 0x6002aa10
    ----------------------------------------
    SO: 0x8283343e0, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: SS shared object cleanup SO
    latch: 0x6002db78
    ----------------------------------------
    SO: 0x828334480, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: FSO deferred cleanup SO
    latch: 0x60031cf8
    ----------------------------------------
    SO: 0x828334520, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: instance cp pool holder
    latch: 0x600396f8
    ----------------------------------------
    SO: 0x8283345c0, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: cp cleanup
    latch: 0x600397a8
    ----------------------------------------
    SO: 0x828334660, type: 1, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    proc=0x0, name=cleanup state object, file=kss.h LINE:2117 ID:, pg=0
    (cleanup state object) description: ASM file cleanup
    latch: 0x60049f10

    -- XS Principal State --
    1 : Role Graph Hash Table
    2 : User Hash Table with Direct Roles Grated
    3 : Principal Row Cache State
    -- XS Principal State End --
    END OF SYSTEM STATE
    Performing trace dump for this instance

    *** 2012-09-25 17:45:51.132
    REQUEST:trace dump in directory=[cdmp_20120925174550], requested by (instance=1, osid=23392 (DBW3)), summary=[abnormal instance termination].

    *** 2012-09-25 17:45:51.133
    Trace dumping is performing id=[cdmp_20120925174550]....

    *** 2012-09-25 17:45:58.118
    Trace dumping is done

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    Anacedent,

    Thank you very much for your help.

    Regards

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > /u00/app/oracle/diag/rdbms/raidsit/RAIDSIT/trace/

    at a minimum you can delete all file in directory above in attempt to obtain enough free disk space to start the DB
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Then shrink the tablespaces and use autoextend but with an upper limit.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Sep 2012
    Posts
    5
    Hi beilstwh and anacedent,

    The file system /u00 is plenty of free space, around 19GB.

    vanna04-stg-298-1:/vans000082dbu00
    28G 8.8G 19G 32% /u00

    The issue is /data_raidsit that is 100% full, where Oracle stores and manages the tablaspaces.

    vanna04-stg-298-1:/vans000082dbdata
    255G 255G 14M 100% /data_raidsit


    It is possible to change the configurations of the tablespaces with the database down?

    Regards

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Tue Sep 25 17:45:57 2012
    >ORA-1092 : opitsk aborting process
    >Tue Sep 25 17:45:58 2012
    >License high water mark = 33
    >Instance terminated by DBW3, pid = 23392
    >USER (ospid: 23521): terminating the instance
    >Instance terminated by USER, pid = 23521

    above shows the DB going DOWN
    What I requested previously is below
    post tail end of alert_SID.log file that shows DB startup FAILURE

    What happens when you do as below?

    sqlplus
    / as sysdba
    startup
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Actually there is an easy fix. If you have space on another volume. shutdown the database, move one of the datafiles to another volume. Startup Mount,
    alter tablespace <ts name> rename datafile 'full path and filename' to 'new path and filename';

    open the database
    shrink the tablespaces
    shutdown normal
    move the datafile back if you want.
    Last edited by beilstwh; 09-27-12 at 10:12.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •