More stupid RMAN tricks: ORA-01843 From RMAN

So I've got a backup script that runs a BACKUP DATABASE followed by a BACKUP ARCHIVELOG command. The BACKUP ARCHIVELOG FROM TIME command runs to collect all the archived logs created since the beginning of the Level 0.

The BACKUP ARCHIVELOG FROM TIME command fails sporadically with a ORA-01843 "not a valid month". Why does it work sometimes? I have no idea. Seems to be time related but I can't really narrow it down. If the Level 0 takes 5 hours it always works. If it takes 7 hours it always fails. I'm sure there is a 'sweet spot' is somewhere between the two but the reality is that sometimes its just going to take 7 hours or more.

I found some info about RMAN and ORA-01843's on MetaLink. They cautioned to make sure NLS_LANG and NLS_DATE_FORMAT were set. I verified these were set OK and the command works OK most of the time so I was baffled. Opened a TAR and here comes the stupid RMAN trick:

Instead of this:

backup archivelog from time '05/24/08 20:43:02'

Try this:

backup archivelog from time "TO_DATE('05/24/08 20:43:02','mm/dd/yy hh24:mi:ss')"

Yes, force feeding a date to RMAN even when it should know the date format is dumb. But it works reliably now. The syntax is not obvious from the documentation but this may be a trick that can be used other places too. Hopefully this is the last time I have to use it :)

-- Dave



Thanks for this post.
Saved my friday ;-)

Add new comment