recover a datafile

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

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.

Abongwa,
email tony_ndisang@yahoo.com now if you can.

abongwa - did u recover the datafile.

Quote:

---

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?

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?

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?

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.

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

Quote:

---

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>

Here, I am showing that I can use "Recover datafile" while the db is open. Did I get you wrong? Or, did the DOC actually say that?

My conclusion is here:

1. If the log required for recovering the datafile is still online, then you can use both "reover datafile" and "alter database"

2. If the log is now archived, you have to use "recover datafile"

Please challenge me. Thanks!

----------------------------------
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
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.
Database opened.
SQL> recover datafile '/export/home/u01/oradata/ktvw/users01.dbf';
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


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

Log applied.
Media recovery complete.
SQL>

BTW CAN SOMEONE PLS EXPLAIN ME WHY LOGFILE HAS TO BE SWITCHED "10" TIMES. PLS EXPLAIN.

Hi,

That's just for simulating dml activities.

Vijay.

Quote:

---

Originally posted by abongwa
Here, I am showing that I can use "Recover datafile" while the db is open. Did I get you wrong? Or, did the DOC actually say that?

Please challenge me. Thanks!

---

Very well said. I am not aware whos right here. But I liked your attitude. I had a similar phase with Pando, I stopped after a while. Maybe he was right, it just that the "tone" in which Pando speaks is what ulteriorly mattered in my opinion.

Quote:

---

Originally posted by oravijay
Hi,

That's just for simulating dml activities.

Vijay.

---

Could you please expalin a little., I am thinking why "10" times? And why at all any number of times, if not 10.

Abongwa

I agree with you : Here is the proof

*** Without Log switches ***

SVRMGR> alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' offline;
Statement processed.
SVRMGR> alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' online;
alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' online
*
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u06/oradata/testDB/users_TESTDB.dbf'
SVRMGR> shutdown abort;
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 25444776 bytes
Fixed Size 94632 bytes
Variable Size 24989696 bytes
Database Buffers 204800 bytes
Redo Buffers 155648 bytes
Database mounted.
SVRMGR> alter database recover datafile '/u06/oradata/testDB/users_TESTDB.dbf';
Statement processed.
SVRMGR> alter database open;
Statement processed.
SVRMGR>


Case 2: *** With Logswitches ....
SVRMGR> alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' offline;
Statement processed.
SVRMGR> alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' online;
alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' online
*
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u06/oradata/testDB/users_TESTDB.dbf'
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> recover datafile '/u06/oradata/testDB/users_TESTDB.dbf';
ORA-00279: change 337664 generated at 05/08/2002 15:14:22 needed for thread 1
ORA-00289: suggestion : /u06/oradata/testDB/arch/arch_1_798.arc
ORA-00280: change 337664 for thread 1 is in sequence #798
Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.


Case 3: With Log Switches...
SVRMGR> alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' offline;
Statement processed.
SVRMGR> alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' online;
alter database datafile '/u06/oradata/testDB/users_TESTDB.dbf' online
*
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u06/oradata/testDB/users_TESTDB.dbf'
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> shutdown abort;
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 25444776 bytes
Fixed Size 94632 bytes
Variable Size 24989696 bytes
Database Buffers 204800 bytes
Redo Buffers 155648 bytes
Database mounted.
SVRMGR> alter database recover datafile '/u06/oradata/testDB/users_TESTDB.dbf';
alter database recover datafile '/u06/oradata/testDB/users_TESTDB.dbf'
*
ORA-00279: change 337678 generated at 05/08/2002 15:21:07 needed for thread 1
ORA-00289: suggestion : /u06/oradata/testDB/arch/arch_1_808.arc
ORA-00280: change 337678 for thread 1 is in sequence #808
SVRMGR> recover datafile '/u06/oradata/testDB/users_TESTDB.dbf';
ORA-00275: media recovery has already been started
SVRMGR> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 25444776 bytes
Fixed Size 94632 bytes
Variable Size 24989696 bytes
Database Buffers 204800 bytes
Redo Buffers 155648 bytes
Database mounted.
SVRMGR> recover datafile '/u06/oradata/testDB/users_TESTDB.dbf';
ORA-00279: change 337678 generated at 05/08/2002 15:21:07 needed for thread 1
ORA-00289: suggestion : /u06/oradata/testDB/arch/arch_1_808.arc
ORA-00280: change 337678 for thread 1 is in sequence #808
Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SVRMGR>

cut & pasted from Oracle 8i ILT PDF

Code:

---

Recover Syntax

Recover a mounted database:
SQL> recover database;
SQL> recover datafile ‘/disk1/data/df2.dbf’;
SQL> alter database recover database;

Recover an opened database:
SQL> recover tablespace USER_DATA;
SQL> recover datafile 2;
SQL> alter database recover datafile 2;


Recover Syntax
One of the following commands may be issued to recover the database:
• RECOVER [AUTOMATIC] DATABASE
Can only be used for a closed database recovery.
• RECOVER [AUTOMATIC] TABLESPACE |
Can only be used for an opened database recovery.
• RECOVER [AUTOMATIC] DATAFILE |
Can only used for both an opened and closed database recovery.
where: automatic automatically applies archived and
redo log files.
Note: ALTER DATABASE may be placed in front of the RECOVER command. This
is not recommended because some error messages get suppressed and do not show up
on screen during recovery.

---

regards

[Edited by pando on 05-08-2002 at 04:17 AM]

Quote:

---

Originally posted by dbafreak

Quote:

---

Originally posted by oravijay
Hi,

That's just for simulating dml activities.

Vijay.

---

Could you please expalin a little., I am thinking why "10" times? And why at all any number of times, if not 10.

---

To simitulate the situation, the number of log switches depends on how many online log groups you have. Let's say you have 5 groups. Then, switch 5 times will definitely push the log required for recovery into archived logs, assuming you are in auto arch mode.

Any more questions?

Alter database recover logfile '/export/home/u03/arch/ktvw/arch_1_667.arc'

Tell me what happens

This is impossible.

Quote:

---

Originally posted by abongwa

Quote:

---

Originally posted by dbafreak

Quote:

---

Originally posted by oravijay
Hi,

That's just for simulating dml activities.

Vijay.

---

Could you please expalin a little., I am thinking why "10" times? And why at all any number of times, if not 10.

---

To simitulate the situation, the number of log switches depends on how many online log groups you have. Let's say you have 5 groups. Then, switch 5 times will definitely push the log required for recovery into archived logs, assuming you are in auto arch mode.

Any more questions?

---

Thanks. That answered my Q.