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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vasant.naidu@gmail.com - 25 Feb 2006 07:47 GMT

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.

Reply to this Message

Michel Cadot - 25 Feb 2006 08:52 GMT

ORA-1555 is one of the most examined errors for the past 10 years.
Just make a little search in google and you get hundreds hits.
Have a nice reading.

Regards
Michel Cadot

Reply to this Message

Sybrand Bakker - 25 Feb 2006 09:51 GMT

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

export != backup

--
Sybrand Bakker, Senior Oracle DBA

Reply to this Message

vasant.naidu@gmail.com - 27 Feb 2006 05:52 GMT

Hi sybrand,

then what would you recommend in this situation. There is a BCV option
and I had already mentioned to customer to subscribe to that. But would
you not recommed export as a backup method. Even if it is full
backup..? Please explain to me.

Thanks.

- Vasant.

Reply to this Message

Roman Klesel - 27 Feb 2006 14:46 GMT

Hello,

vasant.naidu@gmail.com schrieb:

> Hi sybrand,
>
> then what would you recommend in this situation. There is a BCV option
> and I had already mentioned to customer to subscribe to that. But would
> you not recommed export as a backup method. Even if it is full
> backup..? Please explain to me.

it's really funny. Some people just seem not to be able to live without exp/imp.
With Oracle 9i one really doesn't want to use exp/imp unless there is no other alternative.

If you have an rman dump, why would you do another exp dump?

I would rather make sure the rman dumps are valid and check if the backupcycle fullfills the requirements of the customer.

If this is not enough security, go for one or more standby databases. (you can make backup of them as well( and validate
those ))

In the past rman got some bad reputation, but from my experiance here (3 RAC with standby, 8 single instance with
standby) Oracle 9i rman is rock stable. It saved my neck several times.

So my conclusion: Forget exp/imp if you run Oracle 9i.

Greetings Roman

Reply to this Message

Matthias Hoys - 27 Feb 2006 16:56 GMT

> it's really funny. Some people just seem not to be able to live without
> exp/imp.
> With Oracle 9i one really doesn't want to use exp/imp unless there is no
> other alternative.
>
> If you have an rman dump, why would you do another exp dump?

Hmmm ... what if you want to restore the contents of 1 single table from 2
weeks ago ? Can you do that with rman ?

Matthias

Reply to this Message

Sybrand Bakker - 27 Feb 2006 20:09 GMT

>Hi sybrand,
>
[quoted text clipped - 6 lines]
>
>- Vasant.

Export is a *logical* dump, of either the full database, or individual
users, or individual tables.
It is nothing more than create table statements followed by insert
statements.
That said: would you like to destroy and recreate your *complete*
database, when you loose one tablespace, or have one single corrupt
block?
I don't think so.

--
Sybrand Bakker, Senior Oracle DBA

Reply to this Message

Joel Garry - 27 Feb 2006 22:27 GMT

> Hi,
>
[quoted text clipped - 56 lines]
> 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=(
[quoted text clipped - 78 lines]
> 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

Reply to this Message