Problem solve
Get help with specific problems with your technologies, process and projects.
Finding the sequence of the last full backup
I have an Oracle database working in archive mode. Could you tell me how to find the sequence of the last full backup?
I have an Oracle database working in archive mode. I know that "to recover DB to the time of failure, all archived log files from the last full backup should be available," so I think that means I can delete all archived log files that have log sequence numbers smaller than the sequence at the time of last backup (Do you understand? What I mean is that all archived log files that were performed before the last backup can be removed to gain space.). So could you tell me how to find the sequence of the last full backup?
You are correct that to recover to a specific point in time, you need a backup and archived redo logs up to that point in time. One of the things that many sites do is keep multiple generations of their backups, just in case the most recent backup isn't good for some reason. If you keep multiple generations of your backups, you'll need all of the archived redo logs that go with it.
You can query V$LOG_HISTORY to know the SCN numbers in a particular archived redo log, along with the time that the archived redo log holds its first SCN. A query similar to the following can help you:
SELECT sequence#,first_change#,TO_CHAR(first_time,'MM/DD/YY HH24:MI:SS') AS time FROM v$log_history;
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
