This discussion is archived
10 Replies Latest reply: Dec 7, 2012 5:10 AM by EdStevens RSS

UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1

978360 Newbie
Currently Being Moderated
SQL> sho parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs where tablespace_name like '%UND%';

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU1$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU2$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU3$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU4$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU5$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU6$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU7$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU8$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU9$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU10$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU11$                     PUBLIC UNDOTBS1                       OFFLINE

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU12$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU13$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU14$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU15$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU16$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU17$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU18$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU19$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU20$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU21$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU22$                     PUBLIC UNDOTBS2                       ONLINE

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU23$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU24$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU25$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU26$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU27$                     PUBLIC UNDOTBS2                       ONLINE

27 rows selected.

I am getting error when I am making UNDOTBS1 offline.

ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracleas/oradata/dvctst/undotbs01.dbf'

I want to make UNDOTBS1 offline and then after some days of observing the alert log, I want to drop UNDOTBS1 tablespace. How do I do that?

Edited by: 975357 on Dec 6, 2012 5:50 PM
  • 1. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    as per you output the tablespace you required is allready offline then how will make it offline again.
  • 2. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    978360 Newbie
    Currently Being Moderated
    currently undotbs1 is not offline

    Tablespace Used MB Free MB Total MB Pct. Free
    ------------------------------ ---------- ---------- ---------- ----------
    UNDOTBS1 721 779 1500 52


    TABLESPACE_NAME STATUS
    ------------------------------ ---------
    UNDOTBS1 ONLINE

    If I keep it offline, then I am getting

    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: '/oracle/app/oracleas/oradata/dvctst/undotbs01.dbf'
  • 3. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    978360 Newbie
    Currently Being Moderated
    SQL> SELECT a.name,b.status
    FROM v$rollname a,v$rollstat b
    WHERE a.usn = b.usn
    AND a.name IN (
    SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDOTBS1'
    ); 2 3 4 5 6 7 8

    no rows selected

    SQL>
  • 4. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    EdStevens Guru
    Currently Being Moderated
    975357 wrote:
    currently undotbs1 is not offline

    Tablespace Used MB Free MB Total MB Pct. Free
    ------------------------------ ---------- ---------- ---------- ----------
    UNDOTBS1 721 779 1500 52


    TABLESPACE_NAME STATUS
    ------------------------------ ---------
    UNDOTBS1 ONLINE

    If I keep it offline, then I am getting

    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: '/oracle/app/oracleas/oradata/dvctst/undotbs01.dbf'
    So what did you find when you googled "ORA-00376" ????

    The error messages are pretty informative, if you just work them.
  • 5. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    978360 Newbie
    Currently Being Moderated
    when I make the undotbs1 online, the error doesn't come. But my point is I want to drop this tablespace as undotbs2 is the default. my question is why it is still using undotbs1?
  • 6. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    EdStevens Guru
    Currently Being Moderated
    975357 wrote:
    when I make the undotbs1 online, the error doesn't come. But my point is I want to drop this tablespace as undotbs2 is the default. my question is why it is still using undotbs1?
    I don't see any evidence that it is "using" undotbs1. Your own postings indicated no segments in that TBS. What evidence do you see that it is being used?

    All I see is an error message indicating there is a problem with the file itself. Work the error message.
  • 7. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    978360 Newbie
    Currently Being Moderated
    I am getting this error msg when I make undotbs offline, when I make that online, the error get resolved automatically
  • 8. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    EdStevens Guru
    Currently Being Moderated
    975357 wrote:
    I am getting this error msg when I make undotbs offline, when I make that online, the error get resolved automatically
    Except that it appears that due to the error you don't really take the TS offline. You just issued the command, got an error, so no change made. The error doesn't "resolved automatically" when you make it online, because that action doesn't hit the condition that triggers the error. Probably because you are trying to 'online' a TS that is already online.
  • 9. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    978360 Newbie
    Currently Being Moderated
    my question is can I drop this tablespace UNDOTBS1? will that effect database operation in any way?

    Tablespace Used MB Free MB Total MB Pct. Free
    ------------------------------ ---------- ---------- ---------- ----------
    UNDOTBS1 721 779 1500 52
    UNDOTBS2 66 958 1024 94


    SQL> show parameter undo

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS2
    SQL>

    SQL> SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS='ACTIVE' ;

    no rows selected

    SQL>

    SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
    ------------------------------ ------ ------------------------------ ----------------
    _SYSSMU1$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU2$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU3$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU4$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU5$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU6$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU7$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU8$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU9$                      PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU10$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU11$                     PUBLIC UNDOTBS1                       OFFLINE

    SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
    ------------------------------ ------ ------------------------------ ----------------
    _SYSSMU12$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU13$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU14$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU15$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU16$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU17$                     PUBLIC UNDOTBS1                       OFFLINE
    _SYSSMU18$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU19$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU20$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU21$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU22$                     PUBLIC UNDOTBS2                       ONLINE

    SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
    ------------------------------ ------ ------------------------------ ----------------
    _SYSSMU23$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU24$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU25$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU26$                     PUBLIC UNDOTBS2                       ONLINE
    _SYSSMU27$                     PUBLIC UNDOTBS2                       ONLINE

    SQL> SELECT DISTINCT tablespace_name, STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS;

    TABLESPACE_NAME STATUS SUM(BYTES) COUNT(*)
    ------------------------------ --------- ---------- ----------
    UNDOTBS1 UNEXPIRED 691929088 463
    UNDOTBS2 UNEXPIRED 72351744 219
    UNDOTBS1 EXPIRED 63963136 84

    SQL>

    Edited by: 975357 on Dec 7, 2012 11:24 AM

    Edited by: 975357 on Dec 7, 2012 2:00 PM
  • 10. Re: UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
    EdStevens Guru
    Currently Being Moderated
    975357 wrote:
    my question is can I drop this tablespace UNDOTBS1? will that effect database operation in any way?
    <snip>

    You should be able to drop it, but you obviously get an error message when you try to do so. And the very presence of that error message indicates there is some back story you havne't told us .. like why you are trying to change the default undo TS in the first place.

    So I ask again .... have you researched the error message? Oracle is too dumb to lie. If it throws an error, it is because that error occurred and your VERY FIRST course of action should be to read everything you can find about that error message.

Legend

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