Re: error when recover datafile : ORA-00279

Hi Violin,

OK, good news everything is 'Hunky Dory', so a couple of deep breaths.

This is how a recovery 'works' when you use the ALTER DATABASE command (which is why I never use it).

Shutdown the instance and restart mount (just to be safe).

Then issue:

RECOVER DATAFILE '/u03/proddata/itgx01.dbf';

Oracle will now give you the same messages but with the prompt for you to apply the requested redo log.

Press enter to apply the log and get the message to apply the next log or simply type AUTO to automatically apply all the logs.

Media Recovery Complete will finally be displayed.

Get the thing recovered and email me later to you want to know how to recover the database with the alter database command.

Good Luck !!

Richard
"Violin" <violin.hsiao_at_mail.pouchen.com.tw> wrote in message news:d22954a4.0209250301.edb14a8_at_posting.google.com...
> Hello,
>
> Sorry for cross posting , since it's urgent for me.
> Our PROD database has one datafile needed recovery.
>
> SQL> select * from v$recover_file;
>
> FILE# ONLINE ERROR CHANGE# TIME
> --------- -------------- ------------------------------ --------- --------
-
> 297 OFFLINE 5.965E+12
23-SEP-02
>
> SQL> select name , status from v$datafile where file# = 297;
>
> NAME STATUS
> ------------------------------ --------------
> /u03/proddata/itgx01.dbf RECOVER
>
> SQL>
> SQL> select min(SEQUENCE#) , max(SEQUENCE#) from v$recovery_log;
>
> MIN(SEQUENCE#) MAX(SEQUENCE#)
> -------------- --------------
> 6977 7016
>
> SQL> select * from v$recovery_log;
>
> THREAD# SEQUENCE# TIME ARCHIVE_NAME
> ---------- ---------- --------------- ------------------------------
> 1 6977 23-SEP-02 /u03/prodarch/prod6977.arc
> 1 6978 23-SEP-02 /u03/prodarch/prod6978.arc

> 1 6979 23-SEP-02 /u03/prodarch/prod6979.arc
> 1 6980 23-SEP-02 /u03/prodarch/prod6980.arc
> 1 6981 23-SEP-02 /u03/prodarch/prod6981.arc
> 1 6982 23-SEP-02 /u03/prodarch/prod6982.arc
> 1 6983 23-SEP-02 /u03/prodarch/prod6983.arc
> 1 6984 23-SEP-02 /u03/prodarch/prod6984.arc
> 1 6985 23-SEP-02 /u03/prodarch/prod6985.arc
> 1 6986 23-SEP-02 /u03/prodarch/prod6986.arc
> 1 6987 23-SEP-02 /u03/prodarch/prod6987.arc
>
> THREAD# SEQUENCE# TIME ARCHIVE_NAME
> ---------- ---------- --------------- ------------------------------
> 1 6988 23-SEP-02 /u03/prodarch/prod6988.arc
> 1 6989 23-SEP-02 /u03/prodarch/prod6989.arc
> 1 6990 23-SEP-02 /u03/prodarch/prod6990.arc
> 1 6991 23-SEP-02 /u03/prodarch/prod6991.arc
> 1 6992 23-SEP-02 /u03/prodarch/prod6992.arc
> 1 6993 23-SEP-02 /u03/prodarch/prod6993.arc
> 1 6994 23-SEP-02 /u03/prodarch/prod6994.arc
> 1 6995 23-SEP-02 /u03/prodarch/prod6995.arc
> 1 6996 23-SEP-02 /u03/prodarch/prod6996.arc
> 1 6997 23-SEP-02 /u03/prodarch/prod6997.arc
> 1 6998 23-SEP-02 /u03/prodarch/prod6998.arc
>
> THREAD# SEQUENCE# TIME ARCHIVE_NAME
> ---------- ---------- --------------- ------------------------------
> 1 6999 23-SEP-02 /u03/prodarch/prod6999.arc
> 1 7000 23-SEP-02 /u03/prodarch/prod7000.arc
> 1 7001 23-SEP-02 /u03/prodarch/prod7001.arc
> 1 7002 23-SEP-02 /u03/prodarch/prod7002.arc
> 1 7003 23-SEP-02 /u03/prodarch/prod7003.arc
> 1 7004 23-SEP-02 /u03/prodarch/prod7004.arc
> 1 7005 23-SEP-02 /u03/prodarch/prod7005.arc
> 1 7006 23-SEP-02 /u03/prodarch/prod7006.arc
> 1 7007 23-SEP-02 /u03/prodarch/prod7007.arc
> 1 7008 23-SEP-02 /u03/prodarch/prod7008.arc
> 1 7009 23-SEP-02 /u03/prodarch/prod7009.arc
>
> THREAD# SEQUENCE# TIME ARCHIVE_NAME
> ---------- ---------- --------------- ------------------------------
> 1 7010 23-SEP-02 /u03/prodarch/prod7010.arc
> 1 7011 23-SEP-02 /u03/prodarch/prod7011.arc
> 1 7012 23-SEP-02 /u03/prodarch/prod7012.arc
> 1 7013 23-SEP-02 /u03/prodarch/prod7013.arc
> 1 7014 23-SEP-02 /u03/prodarch/prod7014.arc
> 1 7015 23-SEP-02 /u03/prodarch/prod7015.arc
> 1 7016 23-SEP-02 /u03/prodarch/prod7016.arc
>
> 40 rows selected.
>
> SQL>
>
> I tried to shutdown db and restore 23-SEP online backup of itgx01.dbf
> and startup mount for recover :
>
> # sqlplus internal
> SQL> startup mount
> SQL> alter database recover datafile '/u03/proddata/itgx01.dbf';
> alter database recover datafile '/u03/proddata/itgx01.dbf'
> *
> ERROR at line 1:
> ORA-00279: change 5965070658351 generated at 09/23/2002 01:54:48 needed
for
> thread 1
> ORA-00289: suggestion : /u03/prodarch/prod6977.arc
> ORA-00280: change 5965070658351 for thread 1 is in sequence #6977
>
>
> SQL>
>
> BUT WHY? I had all archived logfiles in /u03/prodarch
>
> gbm2:/u03/prodarch #ls *697*
> prod6970.arc.gz prod6972.arc.gz prod6974.arc.gz prod6976.arc.gz
prod6978.arc
> prod6971.arc.gz prod6973.arc.gz prod6975.arc.gz prod6977.arc
prod6979.arc
> gbm2:/u03/prodarch #ls *698*
> prod6980.arc prod6982.arc prod6984.arc prod6986.arc prod6988.arc
> prod6981.arc prod6983.arc prod6985.arc prod6987.arc prod6989.arc
>
> Could somebody help me? It happened at our production site ,
> Appreciate for any helps & thanks billion.
>
> Violin.
> violin.hsiao_at_mail.pouchen.com.tw
Received on Wed Sep 25 2002 - 06:44:47 CDT