Oracle Cleanup - Where are my controlfiles?

[GregE]

Oracle Cleanup - Where are my controlfiles????

I've run some cleanup on some oracle databases and where I normally have had no problem restoring, now I cannot restore control files due to an error saying they don't exist.

RMAN-03002: failure of restore command at 05/01/2008 10:52:30
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

Database versions are set in RMAN to keep 5. The script to cleanup runs the following:

crosscheck backup;
delete expired backup;

Any idea why my control files are missing? I've never had this problem until now that I've run cleanup this week.

 

[heada]

Have you verified that they exist on the database server? If the Oracle database is up, you can delete the control file and it will remain up. It will not restart if taken down though.

-Aaron

 

[GregE]

Have you verified that they exist on the database server? If the Oracle database is up, you can delete the control file and it will remain up. It will not restart if taken down though.

-Aaron

Are you asking if they were ever backed up? They exist on the database server and have been backed up. This database has been restored, about 6 weeks ago, successfully. But this week I ran this cleanup, simple RMAN commands, and now I am questioning that cleanup and if it has screwed things up. It shouldn't, and this could just be coincidence but it's a little TOO much coincidence. I've tried to restore control files from the last 4 weeks and I get these errors each time.

 

[sgabriel62]

Greg
Your DB requires these control files to be operational. If they done exist, the DB will crash. Are you indeed up and running?
TSM does not have any control over these files - they are all DB managed.

Let us know

 

[GregE]

Greg
Your DB requires these control files to be operational. If they done exist, the DB will crash. Are you indeed up and running?
TSM does not have any control over these files - they are all DB managed.

Let us know

Yep, those files are there and the DB has been running fine. I just attempted another offline backup and it failed with incarnation errors. When go into RMAN and list the incarnation for that database, there is NONE. There's problemo numero UNO in a big way. The DBA is going to have to take a look, but it appears he's going to have to reset the logs and create a new incarnation. Not good but it is what it is.

I also checked another random DB that I've run the same cleanup against and it has an incarnation, so probably fine. I haven't restored it, but I believe that it will be ok as I think this incarnation issue with the one database is the problem with that one.

I know we're not a forum of DBAs but I know some of you are closely involved in backups of Oracle that you may have some insights (and perhaps DBA experience too), which is why I posted the topic here. I appreciate the responses.

 

[sgabriel62]

I would recommend when you do a restore - just do the data files and not the control files - let the DBA make those adjustments.
Whatever this cleanup process is - appears to be destructive - I wouldnt use it anylonger

 

[golfdork]

Those two commands are pretty standard for clearing out old DB backups -- we run them at the end of every full db backup to clear out expired versions. The fact that you only have one db messed up makes me think that either something was wrong with the catalog when you did the deletes, or they are not backing up the control files through RMAN. I think it defaults to backing up control files, but that might have changed for some reason. The fact that you restored the database six weeks ago makes me wonder if the RMAN config file got messed up in that process.

I usually get a backup of control files with the regular TSM client on the filesystem in addition to the RMAN backups. The DBAs put a copy in a place that gets backed up every day. That is so we can do a disaster recovery without having the catalog available (which we have to do at our DR test). If you happen to have one of those, you can restore it first and then the DBA should be able to get the database back.

HTH

Kathi

 

[GregE]

I would recommend when you do a restore - just do the data files and not the control files - let the DBA make those adjustments.
Whatever this cleanup process is - appears to be destructive - I wouldnt use it anylonger

We have only 22 databases. I have checked them all and they look fine. The cleanup has to happen - it's removing expired entries from RMAN and TSM. Without deleting obsolete and expired Oracle files, the only way to remove them from TSM (other than dangerous object by object) is to delete Oracle tape volumes based on last write date, which is what I've done for a long time and need to stop doing.

I just checked the other 21 databases and each has an incarnation. I think it's coincidence or something else happened. The cleanup is common and shouldn't have that effect and I'm pretty sure it didn't.

If you just restore data and not controlfiles, the DBA would have to recreate them from scratch with some not so straightforward methods, from what I understand. Not a pretty thing to be doing. Control file is where everything about a database is stored and is critical.

 

[GregE]

Those two commands are pretty standard for clearing out old DB backups -- we run them at the end of every full db backup to clear out expired versions.

That's an excellent idea I hadn't thought of. Once I get things cleared of their 2 years of having not cleaned up (yes, that's correct), I'll get with the DBA's on that, thanks!

I usually get a backup of control files with the regular TSM client on the filesystem in addition to the RMAN backups. The DBAs put a copy in a place that gets backed up every day.

Another excellent idea, thank you. We are backing them up via RMAN with the rest of the DB but since they're not big its simple enough to do this precautionary step.

As for the cleanup commands, do you run anything else that I may want to look into? (delete obsolete, etc) I think deleting expired covers the obsolete though.

 

[GregE]

****UPDATE**** After re-registering the DB in the RMAN catalog and backing it up for the first time afterwards, I attempted a restore and again have the same control file issue. When I then list for its incarnation, the incarnation doesn't exist, and the restore states that the controlfiles cannot be found. So the issue is something somewhere else and the cleanup was definitely not the problem. Time for DBA to step in with both feet.

 

[golfdork]

I inherited some db backups that did not get cleared through the catalog commands, so we ran a tdposync command to get rid of them (only after the DBA made doubly sure that the catalog entries were correct for what he needed to keep). I think the mess came from DBAs not cleaning up after themselves to start with, and the backups expired out of the catalog, but not out of TSM. Those won't go away without a tdposync. It is well documented in the TDPO manual and is very useful for cleanup.

It sounds like you have some sort of catalog problem with that db. The only other thing I might try is renaming the node in TSM and recreating it. That may help the issue if there is corruption in what the catalog has been sending to TSM.

Good luck!

Kathi