Interesting but URGENT Case: Online Backup which never gets consistent

[Eric Tallas]

Hi folks,

I have the (for me) interesting case that I have an online backup where
I can apply all the archived redo logs I have but the database will
never be consistent.
What have done:

1. alter tablespace ... begin backup;

2. cp datafiles to other machine

3. alter tablespace ... end backup;

4. ALTER DATABASE BACKUP CONTROLFILE TO ...;

5. cp backup controlfile, init.ora, archived redo logs etc. to other machine

On the other machine:

1. startup mount;

2. recover database using backup controlfile;

3. applied all the archived redo logs

When I now try to open the database I always get the info that
system.dbf needs more recovery to be consistent.

When I tried to create a new control file I get the following:

SQL> RECOVER DATABASE
ORA-00279: change 566146715 generated at 11/07/2003 04:03:48 needed for
thread
1
ORA-00289: suggestion : /shop/oracle/arch/ISTOY2/arch_1_6424.arc
ORA-00280: change 566146715 for thread 1 is in sequence #6424


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/shop/oracle/arch/ISTOY2/arch_1_6425.arc
ORA-00310: archived log contains sequence 6425; sequence 6424 required
ORA-00334: archived log: '/shop/oracle/arch/ISTOY2/arch_1_6425.arc'

There seems to be a change# which is in none of the archived redo logs.

Does anybody know how I can open this database or what might have gone
wrong during the backup? How can I trace the problem?

Thanks in advance,

Eric

PS: Sorry that I don't user my real email address but I've done this
once and since this day I get loads of spam.

[Richard Foote]

"Eric Tallas" <user@example.net> wrote in message
news:NuKqb.4$_47.746@se2-fa199-9.gva.ch.colt.net...
> Hi folks,
> I have the (for me) interesting case that I have an online backup where
> I can apply all the archived redo logs I have but the database will
> never be consistent.
> What have done:
> 1. alter tablespace ... begin backup;
> 2. cp datafiles to other machine
> 3. alter tablespace ... end backup;
> 4. ALTER DATABASE BACKUP CONTROLFILE TO ...;
> 5. cp backup controlfile, init.ora, archived redo logs etc. to other
machine
> On the other machine:
> 1. startup mount;
> 2. recover database using backup controlfile;
> 3. applied all the archived redo logs
> When I now try to open the database I always get the info that
> system.dbf needs more recovery to be consistent.
> When I tried to create a new control file I get the following:
> SQL> RECOVER DATABASE
> ORA-00279: change 566146715 generated at 11/07/2003 04:03:48 needed for
> thread
> 1
> ORA-00289: suggestion : /shop/oracle/arch/ISTOY2/arch_1_6424.arc
> ORA-00280: change 566146715 for thread 1 is in sequence #6424
> Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
> /shop/oracle/arch/ISTOY2/arch_1_6425.arc
> ORA-00310: archived log contains sequence 6425; sequence 6424 required
> ORA-00334: archived log: '/shop/oracle/arch/ISTOY2/arch_1_6425.arc'
> There seems to be a change# which is in none of the archived redo logs.
> Does anybody know how I can open this database or what might have gone
> wrong during the backup? How can I trace the problem?

Hi Eric

At your first attempt, you're kinda performing an incomplete recovery as
you're using a backup control file. Oracle doesn't know when to "finish" the
recovery as the current control file can't be used to police consistency.

Did you try simply performing a recovery until cancel and opening the
database with resetlogs.

If you truly did copy all your files across, the above should work.

Good luck

Richard

[Richard Foote]

"Richard Foote" <richard.foote@bigpond.com> wrote in message
news:6GMqb.948$aT.588@news-server.bigpond.net.au...
> "Eric Tallas" <user@example.net> wrote in message
> news:NuKqb.4$_47.746@se2-fa199-9.gva.ch.colt.net...
> > Hi folks,
> >
> > I have the (for me) interesting case that I have an online backup where
> > I can apply all the archived redo logs I have but the database will
> > never be consistent.
> > What have done:
> >
> > 1. alter tablespace ... begin backup;
> >
> > 2. cp datafiles to other machine
> >
> > 3. alter tablespace ... end backup;
> >
> > 4. ALTER DATABASE BACKUP CONTROLFILE TO ...;
> >
> > 5. cp backup controlfile, init.ora, archived redo logs etc. to other
> machine
> >
> > On the other machine:
> >
> > 1. startup mount;
> >
> > 2. recover database using backup controlfile;
> >
> > 3. applied all the archived redo logs
> >
> > When I now try to open the database I always get the info that
> > system.dbf needs more recovery to be consistent.
> >
> > When I tried to create a new control file I get the following:
> >
> > SQL> RECOVER DATABASE
> > ORA-00279: change 566146715 generated at 11/07/2003 04:03:48 needed for
> > thread
> > 1
> > ORA-00289: suggestion : /shop/oracle/arch/ISTOY2/arch_1_6424.arc
> > ORA-00280: change 566146715 for thread 1 is in sequence #6424
> >
> >
> > Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
> > /shop/oracle/arch/ISTOY2/arch_1_6425.arc
> > ORA-00310: archived log contains sequence 6425; sequence 6424 required
> > ORA-00334: archived log: '/shop/oracle/arch/ISTOY2/arch_1_6425.arc'
> >
> > There seems to be a change# which is in none of the archived redo logs.
> >
> > Does anybody know how I can open this database or what might have gone
> > wrong during the backup? How can I trace the problem?
> >
> Hi Eric
> At your first attempt, you're kinda performing an incomplete recovery as
> you're using a backup control file. Oracle doesn't know when to "finish"
the
> recovery as the current control file can't be used to police consistency.
> Did you try simply performing a recovery until cancel and opening the
> database with resetlogs.
> If you truly did copy all your files across, the above should work.

Ooooppps, something I forgot to mention.

After you performed your online backup, did you make sure you archived the
current online redo log and copied it across as well ?

You'll need all the redo logs generated during the backup to ensure that the
database can resync itself. And that'll hence include the current online
redo as well.

Cheers

Richard

[Frank]

Eric Tallas wrote:

> Hi folks,
>
> I have the (for me) interesting case that I have an online backup where
> I can apply all the archived redo logs I have but the database will
> never be consistent.
> What have done:
>
> 1. alter tablespace ... begin backup;
>
> 2. cp datafiles to other machine
>
> 3. alter tablespace ... end backup;
>
> 4. ALTER DATABASE BACKUP CONTROLFILE TO ...;
>
> 5. cp backup controlfile, init.ora, archived redo logs etc. to other
> machine
>
> On the other machine:
>
> 1. startup mount;
>
> 2. recover database using backup controlfile;
>
> 3. applied all the archived redo logs
>
> When I now try to open the database I always get the info that
> system.dbf needs more recovery to be consistent.
>
> When I tried to create a new control file I get the following:
>
> SQL> RECOVER DATABASE
> ORA-00279: change 566146715 generated at 11/07/2003 04:03:48 needed for
> thread
> 1
> ORA-00289: suggestion : /shop/oracle/arch/ISTOY2/arch_1_6424.arc
> ORA-00280: change 566146715 for thread 1 is in sequence #6424
>
>
> Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
> /shop/oracle/arch/ISTOY2/arch_1_6425.arc
> ORA-00310: archived log contains sequence 6425; sequence 6424 required
> ORA-00334: archived log: '/shop/oracle/arch/ISTOY2/arch_1_6425.arc'
>
> There seems to be a change# which is in none of the archived redo logs.
>
> Does anybody know how I can open this database or what might have gone
> wrong during the backup? How can I trace the problem?
>
> Thanks in advance,
>
> Eric
>
> PS: Sorry that I don't user my real email address but I've done this
> once and since this day I get loads of spam.
>

1st of all, you run hot backups, so they are never consistent.
By definition.
And why do you offer the 6425 file when Oracle asks for the 6424?

--
Regards, Frank van Bortel

[Eric Tallas]

Frank wrote:

> 1st of all, you run hot backups, so they are never consistent.
> By definition.

ACK, but I think you know what I mean: "I can't open the database after
incomplete recovery".

> And why do you offer the 6425 file when Oracle asks for the 6424?

What I forgot to copy & paste was:

SQL> RECOVER DATABASE
ORA-00279: change 566146715 generated at 11/07/2003 04:03:48 needed for
thread
1
ORA-00289: suggestion : /shop/oracle/arch/ISTOY2/arch_1_6424.arc
ORA-00280: change 566146715 for thread 1 is in sequence #6424


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00328: archived log ends at change 566147250, need later change
281474976710655
ORA-00334: archived log: '/shop/oracle/arch/ISTOY2/arch_1_6424.arc'

After that I offered 6425 to see if change 566146715 was in this file.

Regards,

Eric