standby_file_management=auto & Oracle Error = 1111

[Sanjay Kumar Suri]

We have primary and standby database running on Oracle 10g (10.2) and log replication between primary and DR site is being done on high speed networks.

We have set standby_file_management=auto in both the sides.

So whenever a datafile is added at primary it gets created automatically in the same path on the secondary site and log application using UNIX scripts continues.

Yesterday we added a datafile in primary site. However exactly same directory /oracle//sapdata1 on the secondary site was not having proper owner/group set as ora/dba leading to log application failing for want of required permissions.

We change the permission of the directory to ora/dba. Now log application is failing to go forward and gives the following error.

Media Recovery failed with error 1111
ORA-283 signaled during: ALTER DATABASE RECOVER automatic standby database parallel 16 ...

Based on our earlier experience we are sure that this problem will be sorted out if we bring fresh standby control file from the primary side and newly added datafile.

My Question is:
Why oracle is not continuing the log application when impediments in the process are sorted out?

Is there solution to sort out this problem?

sks

[Volker Borowski]

Hi,

may be the actual creation of the new datafile failed effectively because of lack of permission.

Crosscheck V$DATAFILE and "ls -l" first.

Did you try a

alter database create datafile 'the_one_that_did_no_get_created';

on the standby and retry the recovery ?

Or does this not work with "auto"?

Volker

[Volker Borowski]

Ahhh, this is SAP !

below sapdataN SAP has always an additional subdir for the datafile. So even if effective rights are in progress, oracle does not create the datafile, because the additional subdir is missing

i.E.
/oracle/SID/sapdata1/undo_2/undo.data2
will fail,
because "undo_2" does not exist yet on the standby.

Create the directory first.

Volker

[Sanjay Kumar Suri]

Thanks Volker.

This is not the solution I am looking for. I am looking for solution (not by taking standby control file and datafiles from primary site) so that the recovery process starts (now when the permission are set correctly).

sks

[Volker Borowski]

I do not think you need to bring anything over from primary.

The recover enters the new datafile in v$datafile. You should be able to verify, if it exists in v$datafile (on standby).
If it is in v$datafile and the coresponding
create datafile failed, I think it will not
be possible to recover it again (Because
the entry in v$datafile already exists, it considers the action already being done).

BUT: Every piece of information is already available in the controlfile, so you can
create the file on standby,
CREATE DATAFILE '....';
should work, without copying anything over.

http://www.pythian.com/news/484/oracle-standby-automatic-file-management
http://www.lazydba.com/oracle/0__68413.html

After creating it, the recovery should be able to proceed.

Volker

[Sanjay Kumar Suri]

I feel it is still off the mark. standby_file_management=auto ensure that nothing has to be done at DR site. Everything (inlcluding adding of datafile to database is taken care by Oracle).

Now if for some reason (say inadequate permission in this case) datafile creation and subsequent log application gets stalled.

Now after removing these roadblock why Oracle is not able to continue on its own. It should be smart enough. Or is there a way out with minimal interuption by DBA.

sks

[Duncan Edmonstone · Accepted solution]

>> It should be smart enough

Yep, but it's not...

that's why although Dataguard looks like a cheap option for replciation, you need to be prepared for many complex manual operations (suddenly it's TCO doesn't look so good)

If you want hassle free replication, use disk array based replication like HP Continuous Access, or Hitachi TruCopy.

As for your specific issue - this thread seems to describe your situation fairly accurately:

http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1302327,00.html#

HTH

Duncan

[Sanjay Kumar Suri]

Thanks Duncan.

This looks to be the solution (although lengthy) we were looking for. We are not using Oracle Data guard but archive files are replicated using CA.

Best Wishes

sks

[njn]

Hi,

We are also facing same problem in our DR center.

Our DR Center is using Oracle Standby db and log is shipped using HP CA.

Whenever at DC we add datafile with a sub directory, the same has failed at DR because that subdirectory is not there.

How did you solve this problem ?
Is it still a manual creation of subdir and datafile ?

Thanks & regards.

Naba J Neog

[Sanjay Kumar Suri]

Manual creation is not carried out. Setting the parameter standby_file_management=auto at both primary and DR should make it work.

What is your Oracle version? Check the current value set at both side.

Best wishes!!!

sks