Re: RAC Database -> Export Backup Failure - ORA-01555 Snap Shot Too Old

vasant.naidu_at_gmail.com wrote:
> Hi,
>
> We have a database running on RAC. The below is the problem summary I
> am facing.
>
> Previously we used to do a full export and the backup used to fail
> regularly with the following error:
>
> EXP-00008: ORACLE error 1555 encountered
> ORA-01555: snapshot too old: rollback segment number 46 with name
> "_SYSSMU46$" too small
> EXP-00000: Export terminated unsuccessfully
>
> This database is not having any off-peak time. So we planned to export
> of a selective list of tables, but still it gave the same error. We
> have tried resizing the undo_retention but to no-avail. It is
> constantly giving the same error. I have given some of our
> database/server related facts. Please help me in resolving this issue.
>
> Just for the info: We have one full rman backup scheduled, but for such
> important databases it is better to have another backup mode also. So
> we run this export backup.
>
>
> Some facts about our database:
> =============================
> 1.) Operating System : SunOS P190XNPE01 5.9 Generic_117171-12 sun4u
> sparc SUNW,Sun-Fire-V440
>
> 2.) Database
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
> With the Partitioning, Real Application Clusters, OLAP and Oracle Data
> Mining options
>
> 3.) Cluster Information
> The database instance runs on 4 clusters: RFMDB01, RFMDB02, RFMDB03,
> RFMDB04
> Memory size: 16384 Megabytes on all the 4 nodes
>
> 4.) Database Size : 117,714,927,616.00 Bytes (110 GB approx.)
>
> 5.) Selected Tables Sizes (The tot size of the selected tables for the
> export backup)
> -> Sum of Bytes : 45,930,774,528.00 (43 GB approx.)
> -> Sum of Blocks: 2,803,392.00
> -> db_block_size: 16384
>
> 6.) SGA
> Total System Global Area 1698664856 bytes
> Fixed Size 733592 bytes
> Variable Size 620756992 bytes
> Database Buffers 1073741824 bytes
> Redo Buffers 3432448 bytes
>
> 7.) Export parameters
> userid="/ as sysdba"
> rows=y
> grants=y
> direct=y
> buffer=5000000
> consistent=y

You need a rollback segment big enough to handle your 110G db with this. Try either consistent=N or just export the tables singly or in groups (or perhaps with the query parameter) that you would really need to get a small bit of data out of, or need to migrate to another platform. You might need to get some recent transaction, but not old transactions, for example.

Personally, I have found it very handy to have a logical backup supplementing the physical backup. I usually have more logical backup than database hanging around - and the database has lots more unused space and non-data space like undo and temp. Plus a couple of RMAN backups, at least, although I tend to compress all but the latest. I used to keep flat files too, but exp seems good enough these days.

The important point is to have a written service level agreement specifying what you need to be able to do.

> statistics=n
> tables=(
> FM.USERWISEREPCONTROL, ...
>
> . UNDO parameters which have been set
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> undo_management string AUTO
> undo_retention integer 5400
> undo_suppress_errors boolean FALSE
> undo_tablespace string UNDO1
>
> There are 4 tablespaces for the rollback segments, but the details of
> UNDO1 are as below:
>
> SQL> select distinct tablespace_name from dba_rollback_segs;
>
> TABLESPACE_NAME
> ------------------------------
> SYSTEM
> UNDO1
> UNDO2
> UNDO3
> UNDO4
>
> SQL> break on tablespace_name on report
>
> 1 select tablespace_name, segment_name,
> INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,STATUS
> 2 from dba_rollback_segs
> 3 where status='ONLINE'
> 4* and tablespace_name='UNDO1'
> SQL> /
>
> TABLESPACE_NAME SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT
> MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
> --------------- -------------------- -------------- -----------
> ----------- ----------- ------------ ----------------
> UNDO1 _SYSSMU1$ 131072
> 2 32765 ONLINE
> _SYSSMU2$ 131072
> 2 32765 ONLINE
> _SYSSMU3$ 131072
> 2 32765 ONLINE
> _SYSSMU4$ 131072
> 2 32765 ONLINE
> _SYSSMU5$ 131072
> 2 32765 ONLINE
> _SYSSMU6$ 131072
> 2 32765 ONLINE
> _SYSSMU7$ 131072
> 2 32765 ONLINE
> _SYSSMU8$ 131072
> 2 32765 ONLINE
> _SYSSMU9$ 131072
> 2 32765 ONLINE
> _SYSSMU10$ 131072
> 2 32765 ONLINE
> _SYSSMU41$ 131072
> 2 32765 ONLINE
> _SYSSMU42$ 131072
> 2 32765 ONLINE
> _SYSSMU44$ 131072
> 2 32765 ONLINE
> _SYSSMU52$ 131072
> 2 32765 ONLINE
> _SYSSMU53$ 131072
> 2 32765 ONLINE
> _SYSSMU55$ 131072
> 2 32765 ONLINE
> _SYSSMU56$ 131072
> 2 32765 ONLINE
> _SYSSMU57$ 131072
> 2 32765 ONLINE
> _SYSSMU77$ 131072
> 2 32765 ONLINE
>
> Hope the above information is enough for analysis...?
>
> Hoping for some solutions, as I have been breaking my head for a long
> time now...!

Just wait till you try to _import_ all that stuff.

jg

--
@home.com is bogus.
 "On a guy, gray hair says, 'I'm mature, stable. I can be relied on.'
 I can't honestly say it's done me any harm... Give in to gray. Make
the most of it while you're still young." - Anderson Cooper