Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

recover database using backup controlfile fails

Diego Martinez
Occasional Advisor

recover database using backup controlfile fails

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
12 REPLIES
Duncan Edmonstone
Honored Contributor

Re: recover database using backup controlfile fails

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

HTH

Duncan
Diego Martinez
Occasional Advisor

Re: recover database using backup controlfile fails

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
Occasional Visitor

Re: recover database using backup controlfile fails

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
Honored Contributor

Re: recover database using backup controlfile fails

Hi Diego,

is any of the tablespaces perhaps still in backup mode?

Michael
Stan_17
Valued Contributor

Re: recover database using backup controlfile fails

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
Occasional Contributor

Re: recover database using backup controlfile fails

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.
Frank
Volker Borowski
Honored Contributor

Re: recover database using backup controlfile fails

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
Respected Contributor

Re: recover database using backup controlfile fails

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.
Think Positive
Kanwar Plaha
Occasional Visitor

Re: recover database using backup controlfile fails

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
Esteemed Contributor

Re: recover database using backup controlfile fails

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.

enjoy any little thing in my life
Donald Dzmelyk
Occasional Visitor

Re: recover database using backup controlfile fails

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
Honored Contributor

Re: recover database using backup controlfile fails

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

Never give up, Keep Trying