Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> RAC Database -> Export Backup Failure - ORA-01555 Snap Shot Too Old

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

From: <vasant.naidu_at_gmail.com>
Date: 24 Feb 2006 23:47:14 -0800
Message-ID: <1140853633.998420.7250@j33g2000cwa.googlegroups.com>


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

Regards,
Vasant Naidu
Mumbai, India. Received on Sat Feb 25 2006 - 01:47:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US