RE: Snapshot Too Old Error on Export !!!

From: Stephen Lee <Stephen.Lee_at_DTAG.Com>
Date: Fri, 21 Feb 2003 08:34:40 -0800
Message-ID: <F001.00554992.20030221083440@fatcity.com>

I would echo a previous post that you can't backup a database with the export utility. I suspect you get your error because you are using consistent=y in the export. The database is trying to give you data as of the time you started the export. The fact that you are getting the snapshot too old message is evidence that the data is changing while you are exporting -- to the extent that the database is eventually unable to keep doing it -- but these data changes will not be in the export file.

A genuine backup using rman or the old alter tablespace begin backup method is the only "proper backup".

If you are trying to get production data to move into a test/development database schema, then export is certainly the most convenient way of doing it. If you are, in fact, trying to backup the database, here is something to get you started.

If the filesystem to which your export file is going is big enough, consider using that filesystem to store a database backup instead of an export. Assuming it is /where/it/goes, try the following:

Make sure sys (or system, if you prefer) has been granted sysdba in the database.
Create two files with text similar to the following:

for file named backup_database.rcv:

-------------- snip -----------------

run {
allocate channel ch1 type disk format '/where/it/goes/%U_DATA'; set command id to 'rman';
backup

   tag backup_db_full
(database include current controlfile);
release channel ch1;
}
------------- snip --------------
This will backup the database.

for file named backup_arch.rcv
------------- snip ------------
run {
allocate channel ch1 type disk format '/where/it/goes/%U_ARCH'; set command id to 'rman';
change archivelog all crosscheck;
backup

(archivelog all delete input);

backup ***This line and the next if you are duplexing archived logs***

(archivelog like '/directory/where/duplexed/archivelogs/are/%' delete
input);
release channel ch1;
allocate channel ch1 type disk format '/where/it/goes/%U_CONTROL'; backup current controlfile tag='backup'; release channel ch1;
sql "ALTER DATABASE BACKUP CONTROLFILE TO ''/where/it/goes/CONTROL_FILE.BAK'' REUSE"; }

------------ snip ----------------

This will backup the archived logs and the control file. Note that the last command tells the database to make a physical copy of the control file. The reason for this is that rman has been writing backup info to the control while the backup is running. So you make a copy of it after the backup has completed in case you lose all copies of your control files. If your database and all control files got completely blown away, you can copy the control file copy back to where it was and start restoring. You might note that I backup the control file ... and back it up ... and back it up. That's just paranoia. You can put the whole thing into one file. The reason for having them separate is in case you need to free up space in the archive_log_dest by backing up just the archived logs.

To run a backup, type in the following commands:

rman nocatalog
connect target sys/qwerty_at_DBNAME
@backup_database.rcv
@backup_arch.rcv
exit

Now, make sure you backup /where/it/goes directory to tape with whatever operating system backup utility you are using.

One thing that can be added, if you want to be extra thorough, is to put in a log switch followed by an archive log current, after you run the archivelog backup. Then you run ANOTHER archivelog backup. In the world of Murphy's Law, you do it this way because your archive_log_dest will, some day, at the worst possible time, be unable to accommodate a log switch and archive log current. So you clean it out first prior to the log switch.

It might be useful to know how to restore the database ... that's just something I saw written on a toilet stall wall. It seems reasonable. ("... He who reads these words of wit, eats those little balls of ... ") I think the subject is probably more extensive than can be covered in a simple e-mail; so I won't try to cover it all. But, in it's simplest form, a recovery looks like:

If the last rman backup has been deleted from /where/it/goes, restore those files from tape.

startup mount the database (assuming the control file is NOT the thing you are restoring)

rman nocatalog
connect target sys/qwerty_at_DBNAME
allocate channel ch1 type disk;
restore database;
restore archivelog all;
recover database;
release channel ch1;

open the database.

See, rman isn't so bad. It's biggest problem is that, if you start to like it, then it can lead to other things such as liking vi, growing a beard, and wearing suspenders.

(Obviously, I have a rare day here where I don't have a lot to do. Hence, the verbose reply.)

> -----Original Message-----
> Could somebody help me here, this is very critical to be running
> production without proper backups .....!
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  INET: Stephen.Lee_at_DTAG.Com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Feb 21 2003 - 10:34:40 CST