Able to open database with missing datafiles

[Former Member]

Hi Techies,

Now we are on very strange issue, We are testing DR functionality with validating data etc.

1) We have taken a full offline backup of DR (just to restore post testing)

2) Recovered the DB on DR till particular time

3) Switched it to primary role

4) Opened the DB on DR

5) Started SAP on DR and given the server to users

6) User's tried to validate the data

Here users highlighted that they are facing update delay error, I found the root cause of the error from dumps and system logs that some of the datafiles are missing at OS level (sapdataXX contents) this is causing unable to access file errors.

Then I have tried to restore missed files from backup which i have taken in step 1, But no use. These files are not there means files were not there from initial setup of DR.

I checked the controlfile, the details of missed files are there.

But now also I am able to start/Stop DB on DR, how recovery was happened and how system is allowing me to start/Stop?

Any CLUEs....

*We are using Oracle 10g database with Dataguard funcitionality.

Regards,

Nick Loy

[Former Member]

On Primary:

1167 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/btabi_335/btabi.data335

1168 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/btabi_336/btabi.data336

1169 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/btabi_337/btabi.data337

1170 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/undo_36/undo.data36

1171 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/undo_37/undo.data37

1172 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/btabd_499/btabd.data499

1173 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/btabd_500/btabd.data500

1174 ONLINE READ WRITE 0 0 /oracle/PRO/sapdata27/clud_77/clud.data77

And for missed file of DR on Primary:

STATUS ENABLED LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# NAME

-

---

-

---

-

---

-

---

-

---

-

---

ONLINE READ WRITE 0 0 /oracle/PRO/sapdata23/btabi_264/btabi.data264

Regards,

Nick Loy

[volker_borowski2]

Hi Nick,

No idea about the exact error, but I throw in a couple of thoughts ...

Spool a "select name from v$datafile" to an outputfile and check the file with "cat -v"

-> I had systems with datafiles from bad terminalemulations including backspace chars, which you might can not see

First impression was "Why do I have two files with the same name?"

but it was in fact "btabd.data23" and "btabd.data21<backspace>3" which resolved to "23" on screen.

If you have it this way, several strange things might happen, even to backups.

Second: The missing file is set OFFLINE An offline file is not opened upon startup, so the instance gets open normaly,

unless the datafile contains system or active undo. Pretty straight.

The problem is, why did it happen this way, and who did it?

I have seen this on windows, when ntbackup was still backing up a datafile and the databases

was started. Due to the filehandle belonging to ntbackup, oracle could not open the datafile, and

did set it offline within the startup (was on 8.1.something, never rechecked later releases).

But I can not imagine why this should happen on unix, as the locks are quite cooperative on filelevel.

May be it was the switch to primary which caused that, but you should find a message

in the alert-log when the file has been set offline.

Did you crosscheck v$datafile against the filesystem on both nodes?

select "ls -l " || name from v$datafile;

can help you to generate a script for that.

Confusing indeed

Volker

[stefan_koehler]

Hello Volker,

But I can not imagine why this should happen on unix, as the locks are quite cooperative on filelevel.

As already mentioned - metalinknote #472813.1 explains a common case.

Regards

Stefan

[volker_borowski2]

Thanks Stefan,

I overread this note in your above post. Sorry.

Pretty amazing release intervall for that note.

Now since all file on a specific mountpoint seem to be affected,

that suggests hitting userquotas enabled or largefiles not enabled on this specific mountpoint.

Or was it only several files on that mountpoint? May be the small ones did work, but only big ones offlined?

Volker

[Former Member]

@Joe

Which OS?

HP-UX 11.11, yes I found all files in particular mount point (As far)

@Stefan

How was the backup taken? With RMAN? Manually by copying files, etc.?

We are using HP Data Protector, And offline backup is pure file system level

Ok, but how do you administrate your DG environment? Manually or with DataGuard Broker?

Manually

Please execute the following query after you have opened the database (on DR site) and post the output:

There are so many files showing as below:

1156 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/btabd_498/btabd.data498

1157 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/proti_24/proti.data24

1158 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/sourcei_3/sourcei.data3

1159 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/stabd_23/stabd.data23

1160 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/protd_21/protd.data21

1161 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/stabi_12/stabi.data12

1162 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/poold_31/poold.data31

1163 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/undo_34/undo.data34

1164 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/undo_35/undo.data35

1165 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/poold_32/poold.data32

1166 ONLINE READ WRITE 7252233871 7252233872 /oracle/PRO/sapdata27/poold_33/poold.data33

And below has the output of one particular file which is missed (so many missed in fact).

STATUS ENABLED LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# NAME

-

---

-

---

-

---

-

---

-

---

-

---

OFFLINE READ WRITE 7252233872 0 0 /oracle/PRO/sapdata23/clud_63/clud.data63

Regards,

Nick Loy

[stefan_koehler]

Hello Nick,

thanks for the query output.

Could you please also execute the query on the original primary site and post the output for the same data files as above?

By the way - do you have set the hidden parameter "_ALLOW_RESETLOGS_CORRUPTION" on standby site?

You also maybe hit issue from metalink note #472813.1.

Thanks and Regards

Stefan

[Former Member]

Hi Nick,

Can you paste some extracts from your logs..

Mark

[stefan_koehler]

Hello Nick,

1) We have taken a full offline backup of DR (just to restore post testing)

How was the backup taken? With RMAN? Manually by copying files, etc.?

We are using Oracle 10g database with Dataguard funcitionality.

Ok, but how do you administrate your DG environment? Manually or with DataGuard Broker?

Please execute the following query after you have opened the database (on DR site) and post the output:

shell> sqlplus / as sysdba
SQL> set linesize 900
SQL> SELECT FILE#, STATUS, ENABLED, LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#, NAME 
     FROM V$DATAFILE

Please also tell us which files (name or number) are missing.

Regards

Stefan

[Former Member]

Which OS?

And have the missing files anything in common, like same location on a certain disk or NFS mount?