Home » RDBMS Server » Backup & Recovery » ORA-01190 on alter database open resetlogs after restore (RMAN 10.2.0.3.0, Solaris 10)
icon5.gif  ORA-01190 on alter database open resetlogs after restore [message #318372] Tue, 06 May 2008 11:12 Go to next message
dtora
Messages: 2
Registered: May 2008
Junior Member
I created a development database. In our development environment, all dtabases must be in NOARCHIVELOG mode. I then need to make a baseline backup of the database and save it for periodic restoring after development efforts. This is a RAC database using ASM and I have configured a FRA. There is no rman repository, so the catalog is stored in the controlfile. The problem is, if I restore to baseline once, I cannot restore the database a second time after using rman "delete obsolete" command, apparently because "delete obsolete" deletes a controlfile that is required to open the database after a restore. Is there something I am missing or that I don't understand about rman?

First, the backup:
-bash-3.00$ rman target /
RMAN> configure controlfile autobackup on;
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup database include current controlfile;
RMAN> backup spfile;
RMAN> sql 'alter database backup controlfile to trace';
RMAN> alter database open;


Some development activity occurs, then a restore to baseline is requested. So I do the following:
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore controlfile from '+TEST_FLASH/<sid>/autobackup/2008_05_02/s_653652539.3725.653652747';
Finished restore at 05-MAY-08
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> alter database open resetlogs;
database opened

Okay, the database baseline is restored, and all seems good now. However, list backup shows a few more backupsets, so I want to clean everything up, leaving only the baseline:

RMAN> crosscheck backup;
RMAN> delete obsolete;

More development occurs, and I get another request to restore to baseline. I restore as above, using the only remaining backup sets, and I can't open the database:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/05/2008 14:57:05
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+TEST_DATA/<sid>/datafile/system.720.653567825'

I thought rman saved all files necessary to restore the database under the existing retention policy (REDUNDANCY 1, but all of this occurred on the same day)! So I have to rebuild the database from scratch??
Re: ORA-01190 on alter database open resetlogs after restore [message #318432 is a reply to message #318372] Tue, 06 May 2008 18:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What "other" backups were created and then deleted? DELETE OBSOLETE will delete the older backups and keep the most recent.

What is the output of:

RMAN> list backup of controlfile;

In the future, you should use KEEP FOREVER with your BACKUP DATABASE command. This will ensure the backups is exempt from any retention policy.
Re: ORA-01190 on alter database open resetlogs after restore [message #318673 is a reply to message #318432] Wed, 07 May 2008 09:22 Go to previous messageGo to next message
dtora
Messages: 2
Registered: May 2008
Junior Member
I'm not sure where the other backups came from (I might have done one other backup after the baseline), but none of them should have been expired. I guess OBSOLETE doesn't mean just expired, but also includes unnecessary backups that were not backed up with the "KEEP" clause.

Unfortunately, KEEP FOREVER requires a catalog, which I don't have in this environment.

Even a "KEEP UNTIL" fails, apparently because of the flash recovery area:

RMAN> backup database include current controlfile tag='g_baseline_20080507' keep until time 'SYSDATE+10000' nologs;
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/07/2008 08:44:35
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes


Re: ORA-01190 on alter database open resetlogs after restore [message #318705 is a reply to message #318673] Wed, 07 May 2008 12:22 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Expired & Obsolete mean two different things. Expired means no longer available to RMAN (ie. moved/deleted from filesystem/tape) and Obsolete means no longer required to satisfy a retention policy.

To utilize the KEEP UNTIL, you can simply backup the files outside the FRA.
Previous Topic: Restore database problem
Next Topic: recovery catalog
Goto Forum:
  


Current Time: Wed Dec 07 20:08:58 CST 2016

Total time taken to generate the page: 0.15578 seconds