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.

[Printaporn_1]

Hi Diego,
you procedure is right but now you have 2 options:

1. restore from more older backup.
2. check v$log look for the change number
ex.
change 2421744526 for thread 1 is in sequence #86150
then "select member from v$logfile" and apply the redolog that is same as sequence the recovery ask for.
just put in full path name of that log.

HTH.

[Donald Dzmelyk]

Diego,

I have just worked through is exact scenario. Since I was moving a complete copy of the database to a new location there are no redo logs.

So when you get to that question:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 356238 generated at 06/23/2004 14:10:04 needed for thread 1
ORA-00289: suggestion : /sw/oracle/product/8.1.7.4/dbs/arch1_24.dbf
ORA-00280: change 356238 for thread 1 is in sequence #24


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> exit

And your database is open for business.
drd.

[Indira Aramandla]

Hi Diego,

What you had done is correct. May be try to recover to the lime just before the last archive log by using the unitl time option.

1. Restore from the backup

2. Recover database using backup controlfile unitl time 'CCYY-MM-DD:MM:MI:SS';
the system will prompt as

ORA-00279: Change ?????? generated at dd/mm/yy hh:mi:ss needed for thread 1

ORA-00289: Suggest :path_name for arch log.
ORA-00280: Change ?????? for thread 1 is in sequence # ??????
specify log: {RET>=suggest |filename|AUTO|CANCEL}
and when you press enter
Log applied.

and when the last archivelog is applied, it should as

Lof applied
Media recovery complete.

Then

3. alter database open resetlogs;

If you instance still asks for recovery for system datafile, then

first verify the datafiles to be recovered by quering the views v$recover_file to see the file# and change# and v$recovery_log.

Then try this,

recover datafile 'full_path_datafile_name';

and then it may ask the same recover for the rest of the datafiles. Do the same for all datafiles in question. Then

do alter database open resetlogs.

I hope this helps.


Indira A