Link to home
Start Free TrialLog in
Avatar of novicedbaUK
novicedbaUK

asked on

hot backup cannot recover

I am wondering whether someone can help me

I am testing a hot backup restore from our live system on a test system. I issue alter database switch logfile before starting to take the tablespaces ofline for backup mode.

On the test system, I have issued startup nomount and created a new controlfile. This has run ok. I have then issued alter database open resetlogs but get an error about the system datafile being inconsistent. Issuing a recover database until time using backup controlfile returns an error about needing a further archive log. This archive log does not exist in my backup set. Can someone please tell me how to open the dataabse without it?

Thanks
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

How did you create your "backup set"?  Was this with RMAN, or by manually doing file copies of the data, log and control files?  If you did it manually, I think you need to include the archived redo log files from production also, so that Oracle can use them to put the backed up files into a consistent state even though you don't plan to use use archivelog mode in the test system.
Avatar of novicedbaUK
novicedbaUK

ASKER

It was a hot backup. I copied across the redo logs, datafiled and archive logs only. I then created a new controlfile that uses the reset logs option

Was thing the wrong way to create the controlfile?
You need to apply all the archives that were created during the backup.  Oracle will not allow you to open the database if any of the files are in backup mode.

I usually use RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE.  Apply all the archives from the original database through the last END BACKUP command and then you should be able to open the database with resetlogs.  To find the archives you need, you can usually use the alert log to find the last END BACKUP and get the archive number from there.

After a hot backup, I usually do a switch log file, but it is not necessary.
Tue Oct 30 17:30:00 2007
Thread 1 advanced to log sequence 182
  Current log# 1 seq# 182 mem# 0: /oradata/MCSPRD01/redo01a.log
  Current log# 1 seq# 182 mem# 1: /oradata/MCSPRD01/redo01b.log
Tue Oct 30 17:30:00 2007
alter tablespace SYSTEM begin backup
Tue Oct 30 17:30:00 2007
ARC0: Evaluating archive   log 3 thread 1 sequence 181
ARC0: Beginning to archive log 3 thread 1 sequence 181
Creating archive destination LOG_ARCHIVE_DEST_2: 'MCSPRD01STBY'
Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/MCSPRD01/archive/1_181.dbf'
Tue Oct 30 17:30:00 2007
Completed: alter tablespace SYSTEM begin backup
Tue Oct 30 17:30:06 2007
alter tablespace SYSTEM end backup
Completed: alter tablespace SYSTEM end backup
Tue Oct 30 17:30:06 2007
alter tablespace USERS begin backup
Completed: alter tablespace USERS begin backup
Tue Oct 30 17:30:23 2007
alter tablespace USERS end backup
Tue Oct 30 17:30:23 2007
Completed: alter tablespace USERS end backup
Tue Oct 30 17:30:23 2007
alter tablespace DRSYS begin backup
alter tablespace DRSYS end backup
Completed: alter tablespace DRSYS end backup
Tue Oct 30 17:30:24 2007
alter tablespace EXAMPLE begin backup
Completed: alter tablespace EXAMPLE begin backup
Tue Oct 30 17:30:26 2007
alter tablespace EXAMPLE end backup
Completed: alter tablespace EXAMPLE end backup
Tue Oct 30 17:30:26 2007
alter tablespace GOLDIDX begin backup
Completed: alter tablespace GOLDIDX begin backup
Tue Oct 30 17:31:15 2007
alter tablespace GOLDIDX end backup
Tue Oct 30 17:31:15 2007
Completed: alter tablespace GOLDIDX end backup
Tue Oct 30 17:31:15 2007
alter tablespace GOLDTBS begin backup
Completed: alter tablespace GOLDTBS begin backup
Tue Oct 30 17:31:18 2007
ARC0: Completed archiving  log 3 thread 1 sequence 181
Tue Oct 30 17:31:56 2007
alter tablespace GOLDTBS end backup
Tue Oct 30 17:31:56 2007
Completed: alter tablespace GOLDTBS end backup
Tue Oct 30 17:31:56 2007
alter tablespace INDX begin backup
Completed: alter tablespace INDX begin backup
Tue Oct 30 17:31:56 2007
alter tablespace INDX end backup
Completed: alter tablespace INDX end backup
Tue Oct 30 17:31:56 2007
alter tablespace ODM begin backup
Tue Oct 30 17:31:56 2007
alter tablespace ODM begin backup
Completed: alter tablespace ODM begin backup
Tue Oct 30 17:31:56 2007
alter tablespace ODM end backup
Completed: alter tablespace ODM end backup
Tue Oct 30 17:31:56 2007
alter tablespace TOOLS begin backup
Completed: alter tablespace TOOLS begin backup
Tue Oct 30 17:31:57 2007
alter tablespace TOOLS end backup
Completed: alter tablespace TOOLS end backup
Tue Oct 30 17:31:57 2007
alter tablespace UNDOTBS1 begin backup
Completed: alter tablespace UNDOTBS1 begin backup
Tue Oct 30 17:32:02 2007
alter tablespace UNDOTBS1 end backup
Completed: alter tablespace UNDOTBS1 end backup
Tue Oct 30 17:32:02 2007
alter tablespace XDB begin backup
Completed: alter tablespace XDB begin backup
Tue Oct 30 17:32:03 2007
alter tablespace XDB end backup
Completed: alter tablespace XDB end backup
Tue Oct 30 17:32:45 2007
alter database backup controlfile to '/oraback/hot_backups/ctl/controlfile_301007.bak'
REUSE


My alert log is above. From what I can see, archive 181 was the last one to be archived, and 182 is the current redo log. Why is it asking me for log 182 and yet it has not been archived?
Ive sorted this out myself. Changed the control file to noresetlogs and issued archive log all then recover database and alter database open. Works now
Just a couple of comments:
- Don't get confused with "offlining" a tbsp and "putting it into backup mode". Very different things indeed
- RMAN avoids your needing to get into/out of backup mode, since it avoids fractured blocks (you can read about that problem in the backup and recovery concepts manual). Therefore, you'll be getting less resource usage (especially avoiding redo log overloading). Take a closer look at RMAN and learn to use it as properly as you can, you'll never regret it
- If you insist on putting tablespaces in backup mode, Oracle absolutely MUST apply every single log record generated during the backup mode and until after you end backup mode. These log records are the very least Oracle needs to get every block of the datafiles in sync with each other, so they include the log that was being written to when your backup ended. That online/current redo log file may not be archived yet, but you will never get your DB in sync without it. When you issued "archive log all" you caused those final redo log records to be flushed to an archived log, where the recover command found them and made your life good again
The reason that it is asking for log 182 is that it contains the information up to the last END BACKUP.

That is the reason I do a switch log file after the hot backup.  You will know that all the redo logs you need to recover have been archived.
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial