recover database using backup controlfile fails

[Diego Martinez]

Hi,

I'm restoring a offline Oracle 9i backup. Operation is normal:

1. I restore all tree structure
2. I can open the database without problems

But i want to apply all changes (including in the archive redo logs) generated between the backup and now. I've 6 archived redo log (last is ARC00301.LOG).

The steps are:

1. startup database in mount
STARTUP MOUNT
2. RECOVER DATABASE USING BACKUP CONTROLFILE

In this step i've the next problem: The system ask me about a new archive redo log wich don't exists (ARC00302.LOG)

3. I try to open de database

ALTER DATABASE OPEN RESETLOGS;

I've obtain the next error:

Datafile SYSTEM need physical recovery

Why? What's the problem? What's the correct procedure?

Thanks in advance
Diego

[Duncan Edmonstone]

Hi Diego,

try :

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

then enter 'cancel' when it asks for log no 32,

and then

ALTER DATABASE OPEN RESETLOGS

HTH

Duncan

[Diego Martinez]

I'v test the solution and i've the same problem.

Any idea? Procedure is correct?

What's is the procedure to restore a offline backup after aply all archived redo log?

Thanks,
Diego

[lsshiu]

hi,
The ARC00302.LOG does not exist YET, when it ask you to feed this, just give it the path of your current online redo log file location. This will solve the issue.

David

[Michael Schulte zur Sur]

Hi Diego,

is any of the tablespaces perhaps still in backup mode?

Michael

[Stan_17]

Hi Diego,

The steps you had mentioned would end up in incomplete recovery.

For a complete recovery, i would do this.

1. restore offline backups (everything except redologfiles, and controfiles)

2. startup mount;

3. restore database; -- this should automatically apply the new archivelogs since offline backup and apply changes in current redolog.

4. alter database open;

this will bring the database up-to-date.

-
hth,
Stan

[Francisco Pardillo Mart]

Hi,

You could try:

(in mount mode)
select * from v$logfile;

and, with the files you see, (...redo..)

you put it on the recover "database until cancel using backup controlfile;" sentence.

By.

[Volker Borowski]

Hi,

I think Michael has got it.
You did an offline backup, but during the recovery a BEGIN BACKUP has been recovered to some/all datafiles as well.
Beside this your procedure is correct.

- Restore entire structure
- STARTUP MOUNT
- RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

When reached the last log type "CANCEL".

Do a
SELECT distinct status from v$backup;

If you only receive "NOT ACTIVE"
ALTER DATABSE OPEN RESETLOGS;
should work.

If you have datafiles in state ACTIVE, you need to switch this off first, but since the database is not open yet, the logical object TABLESPACE is no accessible yet. So clip the output of this statement

select 'ALTER DATABASE DATAFILE ' || b.filename || ' end backup;'
from v$backup a, v$datafile b
where
a.file# = b.file#
and a.status = 'ACTIVE';

... and execute it.
open resetlogs should work afterwards.

Hope this helps
Volker

[Madhu Sudhan_1]

Looks to me like you are doing cold backup copy/refresh. You should consider Fransisco's suggestion of applying online redologs for recovery.
Use the sql
select * from v$logfile ;

# strings -a /dir1/log1/log1.dbf | grep -i Seq#

The above command will help you identify the sequence number of the online redo log log1.dbf. Otherwise you can apply one after the other in the list given by the sql query.

[Kanwar Plaha]

Hi:
I was getting a similar error and I followed the procedure described of appyling the current redo logs for database recovery. However, after it told me 'media recovery complete' I am unable to open the database.
When i run alter database open resetlogs i get the following error:

ORA-01092: ORACLE instance terminted. Disconnection forced.

Also, if i try to start the database again, it "hangs" after mounting the database.

btw, I am running this on Windows Server 2003.