recover a datafile
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: recover a datafile

  1. #1
    Join Date
    Feb 2002
    Posts
    59
    I am recovering a datafile. How do I supply the logs that it required? The recover is taking forever...... How do I fix it?

    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 293855392 bytes
    Fixed Size 73888 bytes
    Variable Size 136314880 bytes
    Database Buffers 157286400 bytes
    Redo Buffers 180224 bytes
    Database mounted.
    SQL> alter database recover datafile '/export/home/u01/oradata/ktvw/users01.dbf';
    alter database recover datafile '/export/home/u01/oradata/ktvw/users01.dbf'
    *
    ERROR at line 1:
    ORA-00279: change 479068375 generated at 05/07/2002 11:18:31 needed for thread
    1
    ORA-00289: suggestion : /export/home/u03/arch/ktvw/arch_1_650.arc
    ORA-00280: change 479068375 for thread 1 is in sequence #650


    SQL> recover database;
    ORA-00275: media recovery has already been started


  2. #2
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    Just enter to accept the suggestion else if you want to supply the current log give the full path and name of the active redolog file.

    KN

  3. #3
    Join Date
    Sep 2001
    Posts
    200
    Abongwa,
    email tony_ndisang@yahoo.com now if you can.
    Life is what is happening today while you were planning tomorrow.

  4. #4
    Join Date
    Dec 2001
    Location
    SAN FRANCISCO, CA
    Posts
    306
    abongwa - did u recover the datafile.

  5. #5
    Join Date
    Feb 2002
    Posts
    59
    Originally posted by Knarayan
    Just enter to accept the suggestion else if you want to supply the current log give the full path and name of the active redolog file.

    It won't let me supply ANY log. It just stop.

    If you like, you can re-generate this error by following the scenario and then show me how to fix it.

    1. alter a datafile offline
    2. switch logs 10 times
    3. shutdown abort

    Then, how do you open that db and fix that datfile?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    look

    Code:
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' offline;
    
    Database altered.
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online;
    alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online
    *
    ERROR at line 1:
    ORA-01113: file 8 needs media recovery
    ORA-01110: data file 8: 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF'
    
    
    sys@DEV817>alter system switch logfile;
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>r
      1* alter system switch logfile
    
    System altered.
    
    sys@DEV817>shutdown abort
    ORACLE instance shut down.
    sys@DEV817>startup mount
    ORACLE instance started.
    
    Total System Global Area   41764892 bytes
    Fixed Size                    75804 bytes
    Variable Size              33419264 bytes
    Database Buffers            8192000 bytes
    Redo Buffers                  77824 bytes
    Database mounted.
    sys@DEV817>recover datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF';
    ORA-00279: change 1306769 generated at 05/07/2002 22:54:48 needed for thread 1
    ORA-00289: suggestion : D:\ORACLE\ORADATA\DEV817\ARCHIVE\DEV817T001S02245.ARC
    ORA-00280: change 1306769 for thread 1 is in sequence #2245
    
    
    Specify log: {=suggested | filename | AUTO | CANCEL}
    
    Log applied.
    Media recovery complete.
    sys@DEV817>alter database open;
    
    Database altered.
    
    sys@DEV817>select sysdate from dual;
    
    SYSDATE
    --------------------
    2002-MAY-07 22:58:59
    
    sys@DEV817>select tablespace_name, file_name, status from dba_data_files;
    
    TABLESPACE_NAME                FILE_NAME                                          STATUS
    ------------------------------ -------------------------------------------------- ---------
    DRSYS                          D:\ORACLE\ORADATA\DEV817\DR01.DBF                  AVAILABLE
    INDX                           D:\ORACLE\ORADATA\DEV817\INDX01.DBF                AVAILABLE
    TOOLS                          D:\ORACLE\ORADATA\DEV817\TOOLS01.DBF               AVAILABLE
    TEMP                           D:\ORACLE\ORADATA\DEV817\TEMP01.DBF                AVAILABLE
    USERS                          D:\ORACLE\ORADATA\DEV817\USERS01.DBF               AVAILABLE
    RBS                            D:\ORACLE\ORADATA\DEV817\RBS01.DBF                 AVAILABLE
    SYSTEM                         D:\ORACLE\ORADATA\DEV817\SYSTEM01.DBF              AVAILABLE
    DATA_SMALL                     D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF          AVAILABLE
    
    8 rows selected.
    are you sure it is not your problem?

  7. #7
    Join Date
    Feb 2002
    Posts
    59
    Thank you.

    By using "recover datafile '/export/home/u01/oradata/xxxx/users01.dbf';"

    It fix the problem!!!

    Now, my question is that why I cannot use :

    alter database recover datafile '/export/home/u01/oradata/xxxx/users01.dbf';

    Why?

  8. #8
    Join Date
    Feb 2002
    Posts
    59

    FYI

    SQL> recover datafile '/export/home/u01/oradata/ktvw/users01.dbf';
    ORA-00279: change 479068375 generated at 05/07/2002 11:18:31 needed for thread
    1
    ORA-00289: suggestion : /export/home/u03/arch/ktvw/arch_1_650.arc
    ORA-00280: change 479068375 for thread 1 is in sequence #650


    Specify log: {=suggested | filename | AUTO | CANCEL}

    Log applied.
    Media recovery complete.
    SQL> alter database open;

    Database altered.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    case 1
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' offline;
    
    Database altered.
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online;
    alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online
    *
    ERROR at line 1:
    ORA-01113: file 8 needs media recovery
    ORA-01110: data file 8: 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF'
    
    
    sys@DEV817>alter system switch logfile;
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>/
    
    System altered.
    
    sys@DEV817>shutdown abort
    ORACLE instance shut down.
    sys@DEV817>startup mount
    ORACLE instance started.
    
    Total System Global Area   41764892 bytes
    Fixed Size                    75804 bytes
    Variable Size              33419264 bytes
    Database Buffers            8192000 bytes
    Redo Buffers                  77824 bytes
    Database mounted.
    sys@DEV817>alter database recover datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF';
    alter database recover datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF'
    *
    ERROR at line 1:
    ORA-00279: change 1346944 generated at 05/07/2002 23:16:40 needed for thread 1
    ORA-00289: suggestion : D:\ORACLE\ORADATA\DEV817\ARCHIVE\DEV817T001S02266.ARC
    ORA-00280: change 1346944 for thread 1 is in sequence #2266
    
    
    sys@DEV817>ALTER DATABASE RECOVER LOGFILE 'D:\ORACLE\ORADATA\DEV817\ARCHIVE\DEV817T001S02266.ARC';
    
    Database altered.
    
    sys@DEV817>alter database open;
    
    Database altered.
    Code:
    case 2
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' offline;
    
    Database altered.
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online;
    alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online
    *
    ERROR at line 1:
    ORA-01113: file 8 needs media recovery
    ORA-01110: data file 8: 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF'
    
    
    sys@DEV817>alter database recover datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF';
    
    Database altered.
    do you read the documentation at all?
    this is all documentated, you do recover when mounted, alter database recover with database online

  10. #10
    Join Date
    Feb 2002
    Posts
    59
    Originally posted by pando
    Code:
    case 2
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' offline;
    
    Database altered.
    
    sys@DEV817>alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online;
    alter database datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF' online
    *
    ERROR at line 1:
    ORA-01113: file 8 needs media recovery
    ORA-01110: data file 8: 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF'
    
    
    sys@DEV817>alter database recover datafile 'D:\ORACLE\ORADATA\DEV817\DATA_SMALL01.DBF';
    
    Database altered.
    do you read the documentation at all?
    this is all documentated, you do recover when mounted, alter database recover with database online [/B]
    I am sorry. I might forget some details in the docs. However, is there a hard rule saying:

    Use Recover Datafile when in mount mode
    Use Alter database recover datafile when in open mode

    ??
    I don't think so. (Would u please show me the link to the doc.)

    Also, I made the following test. While the db is still open, I COULD NOT use "alter database recover datafile". Your input is very appreciated.

    SQL> alter database datafile '/export/home/u01/oradata/ktvw/users01.dbf' offline;

    Database altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter database datafile '/export/home/u01/oradata/ktvw/users01.dbf' online;
    alter database datafile '/export/home/u01/oradata/ktvw/users01.dbf' online
    *
    ERROR at line 1:
    ORA-01113: file 5 needs media recovery
    ORA-01110: data file 5: '/export/home/u01/oradata/ktvw/users01.dbf'


    SQL> alter database recover datafile '/export/home/u01/oradata/ktvw/users01.dbf';
    alter database recover datafile '/export/home/u01/oradata/ktvw/users01.dbf'
    *
    ERROR at line 1:
    ORA-00279: change 479108523 generated at 05/07/2002 15:42:09 needed for thread 1
    ORA-00289: suggestion : /export/home/u03/arch/ktvw/arch_1_667.arc
    ORA-00280: change 479108523 for thread 1 is in sequence #667


    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width