Oracle Datafile in Revover Status and offline, set online?

[Former Member]

Hello,

is it recommended to set a datafile online if it´s in the status "revover" and currently offline?

If added on our system to each Tablespaces PSAPSR3 and PSAPSR3701 a new datafile.

Since this event, I reveive in the backuplog the follwing warning:

BR0334E Status of database file F:ORACLEC12SAPDATA2SR3_24SR3.DATA24 is RECOVER

DB13:

BR0970W Database administration alert - level: WARNING, type: CRITICAL_TABLESPACE, object: PSAPSR3, value: (max_extn of 23 files on disk 5) 157102080 KB (> 34372260 KB)

BR0970W Database administration alert - level: WARNING, type: CRITICAL_TABLESPACE, object: PSAPSR3701, value: (max_extn of 11 files on disk 5) 64819200 KB (> 34372260 KB)

BR0970W Database administration alert - level: ERROR, type: FILE_OFFLINE, object: F:ORACLEC12SAPDATA2SR3_24SR3.DATA24

BR0970W Database administration alert - level: ERROR, type: FILE_OFFLINE, object: F:ORACLEC12SAPDATA4SR3701_12SR3701.DATA12

I want to make the following workaround to get the two datafiles online again:

ALTER DATABASE DATAFILE SR3.DATA24 ONLINE;

ALTER DATABASE DATAFILE SR3701.DATA12 ONLINE;

Sincerely,

Martin

[Former Member]

Dear Martin,

Suggetion: Please close this thread if your isue resolved, help experts in replying open issues

Regards,

Nick Loy

[Former Member]

Read and Proceed as mentioned in SAP Note 4162 and SAP Note 4161.

Regards,

Nick Loy

[Former Member]

Hello,

Thank you all for posting ideas!

The final solution was to revoer each datafile, check the status again and after succesfull recovering putting them online again.

Reason for this inconsistence datafiles probably were some disk erros which are occured in the system log of the w2k system.

Sincerely,

Martin

[stefan_koehler]

Hello Martin,

is it recommended to set a datafile online if it´s in the status "revover" and currently offline?

It depends on your needs, why and how you did that or in which situation it occurs.

We already have discussed something like this in this thread:

In addition please read this blog entry "Recovery of Offline Data File" and its later influence in some cases:

http://esemrick.blogspot.com/2006_03_01_archive.html

So it all depends on how the files gets their status and what you want.

Regards

Stefan

[Former Member]

You can just set the datafiles online. If that is not possible at the time, Oracle will give a warning about this..

Kind regards,

Mark

[Former Member]

Hello,

thank you very much I`ll try this solution.

Sincerely,

Martin

[Former Member]

Hello,

I´ve tried to set one datafile online with brtools and i get the follwoing error:

Options for alter of data file F:ORACLEC12SAPDATA2SR3_24SR3.DATA24

1 * Current data file status (status) .......

2 * Current data file size in MB (currsize) .

3 * Alter data file action (action) .........

4 # Maximum file size in MB (maxsize) ....... []

5 # File increment size in MB (incrsize) .... []

6 # New data file size in MB (size) ......... []

7 # New data file name (name) ............... []

8 # Force data file alter (force) ...........

9 - SQL command (command) ................... [alter database datafile 'F:ORAC

LEC12SAPDATA2SR3_24SR3.DATA24' online]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

-

---

BR0662I Enter your choice:

c

BR0280I BRSPACE time stamp: 2010-04-19 11.59.22

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2010-04-19 11.59.22

BR0370I Directory D:oracleC12sapreorgsedausiu created

BR0280I BRSPACE time stamp: 2010-04-19 11.59.23

BR0319I Control file copy created: D:oracleC12sapreorgsedausiuCNTRLC12.OLD

12861440

BR0280I BRSPACE time stamp: 2010-04-19 11.59.23

BR0301E SQL error -1113 at location BrSqlExecute-1, SQL statement:

'/* BRSPACE */ alter database datafile 'F:ORACLEC12SAPDATA2SR3_24SR3.DATA24

' online'

ORA-01113: file 42 needs media recovery

ORA-01110: data file 42: 'F:ORACLEC12SAPDATA2SR3_24SR3.DATA24'

BR1017E Execution of SQL statement 'alter database datafile 'F:ORACLEC12SAPDA

TA2SR3_24SR3.DATA24' online' failed

BR0669I Cannot continue due to previous warnings or errors - you can go back to

repeat the last action

BR0280I BRSPACE time stamp: 2010-04-19 11.59.23

BR0671I Enter 'b[ack]' to go back, 's[top]' to abort:

Serarching for "BR1017E" on google won´t bring a soloution.

[Former Member]

Have you already restarted the database after you have added these datafiles?

I would expect that after the start of the database, it is requesting for recovery. So if possible, restart the database, and after you get the message that these files need recovery, just enter "recover database" from sqlplus.

Kind regards,

Mark