Re: backup UNDO Tablespace (dba_undo_extents)

From: Roger Xu <wellmetus_at_gmail.com>
Date: Wed, 16 Dec 2009 11:35:08 -0600
Message-ID: <eb64345d0912160935s52c6c94fg96b352ebfb202c4a_at_mail.gmail.com>

10R2 and I am using backupset (backup datafile #;)

SQL> select * from v$version;

BANNER

---

Oracle Database 10g Release 10.2.0.1.0 - Production PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

SQL> show parameter undo

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

SQL> select bytes/1024/1024 from dba_data_files   2 where TABLESPACE_NAME = 'UNDOTBS1';

BYTES/1024/1024

---

          24545

SQL> select sum(bytes)/1024/1024 from dba_free_space   2 where TABLESPACE_NAME = 'UNDOTBS1';

SUM(BYTES)/1024/1024

---

               24474

SQL> select 24474-24545 from dual;

24474-24545

---

        -71
SQL> (Sorry, it's 24GB not 20GB.)

Thanks,

Roger

On Wed, Dec 16, 2009 at 9:43 AM, Yong Huang <yong321_at_yahoo.com> wrote:

> > I think RMAN will back up all blocks that have ever been part of
> > any segment.
>
> Jeremiah,
>
> That's the case up to 10gR1. Beginning with 10gR2, unused blocks in
> LMT are ignored, unused in the sense that the segment is dropped or
> truncated. See "Unused Block Compression" at
> https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=360443.1
>
> > Oracle 10g
> > We have only one datafile (20 GB) in the undo tablespace.
> > When I ran RMAN backup of this datafile, it created a 20GB backupset
> > file.
> > I query dba_undo_extents and it shows less than 80 MB used.
>
> Roger,
>
> Tell us the exact version of Oracle. Could it be 10gR1? Your total
> undo tablespace is 20 GB. Is total free space (sum(bytes) from
> dba_free_space) for undo more than 19 GB? And I suppose you're not
> doing datafile image copy.
>
> Yong Huang
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 16 2009 - 11:35:08 CST