10 Replies Latest reply: Dec 7, 2012 7:10 AM by EdStevens RSS

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

    978360
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.