Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Jerry.

Asked: October 31, 2024 - 8:39 pm UTC

Last updated: November 01, 2024 - 7:32 am UTC

Version: 19c

Viewed 100+ times

You Asked

I have consistent storage snapshot for oracle database. I want to restore the snapshot to a mount host and roll the archivelogs to a specific point-in-time or SCN. I always got error when trying to open the database on mount host.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DATA/ORCL/DATAFILE/system.256.1183393031'

What I have done:
Production Host
- Take consistent storage snapshot of online database including DATA,REDO,ARCHIVE
- Make database changes
- Backup archivelogs

Mount Host:
- Restore storage snapshot
- Attach ASM disk groups
- Restore archivelogs taken after storage snapshot was made.
- Mount database, trying to recover database to SCN or specific time or just until cacel

Always got ORA-01113 and ORA-01110. Tried to recover using backup controlfile, same result.

What should the correct steps to roll the archivelogs on top of storage snapshots? Thanks!

and Connor said...

Did you put your database in backup mode before taking the snapshot? We need that to grab a little more redo, and also (and what sounds like it might the case here) keep the checkpoints in sync on the datafiles.

Then it should just be case of RMAN:

RECOVER DATABASE SNAPSHOT TIME '...';


Ace Director Ron Ekins did a nice post on this a while back, so I wont repeat all of that here

https://ronekins.com/2021/09/13/oracle-database-recovery-using-storage-snapshot-optimization/

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.