ORA-01110 error in DR Server

[Former Member]

Hi All,

   i am getting following error while apply log on DR server.

   kindly advise.

SQL> recover standby database;

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 210 is unknown - rename to correct file

ORA-01110: data file 210: '/oracle/PDR/112_64/dbs/UNNAMED00210'

ORA-01157: cannot identify/lock data file 210 - see DBWR trace file

ORA-01111: name for data file 210 is unknown - rename to correct file

ORA-01110: data file 210: '/oracle/PDR/112_64/dbs/UNNAMED00210'

Regards

Sunil

[ACE-SAP · Accepted solution]

Hi

This error can be related to a datafile created on primary without creating the appropriate directory structure on standby.

Dataguard is able to replicate new datafile(s) on standby only if the appropriate directory structure is available.

You should at first identify the datafile that has been created on primary DB

Then on standby suspend recovery, create the appropriate directory and set the datafile to the correct location.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;

ALTER DATABASE CREATE DATAFILE '/oracle/PDR/112_64/dbs/UNNAMED00210' as '/oracle/PRD/sapdataX/<TS_NAME>_Y/<TS_NAME>.dataY';

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

PS : You should post that question at the right place () you would get more & faster answers

Regards

[Former Member · Accepted solution]

Hi All,

  This is my DR server and i all my data files are in /oracle/PDR/sapdata1 , 2, 3 ,4 etc.

  i do not have any of my data file in '/oracle/PDR/112_64/dbs'. even the file 'UNNAMED00210' is there in dbs directory.

we manually apply the logs in our DR server and it was working fine till yesterday. i do not know why all of the sudden i started getting this error.

Thanks

Sunil

[Former Member · Accepted solution]

Hi,

This is because, dataguard was not able to find the FS structure same as primary.

Ex: You have sapdata1 to sapdata10, on primary you have added new datafile sapdata11 and added a datafile in that. But you missed to created a new FS sapdata11 on secondary. In this situation Dataguard creates newly added file system in oracle_home directory by default and it causes to your recover fail.

Follow above steps mentioned by Yves KERVADEC.

Regards,

Nick Loy

[Former Member · Accepted solution]

Hi Nick,

  we have not made any changes in the Primary server. Both the file systems and directory structures are same.

regards

sunil

[Former Member · Accepted solution]

Hi,

   i have performed the following.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

Error Getting:- ORA-01153: an incompatible media recovery is active

SHUTDOWN IMMEDIATE; -----Successfully done.

STARTUP MOUNT; -----Successfully done.

ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;  ----Successfully done.

ALTER DATABASE CREATE DATAFILE '/oracle/PDR/112_64/dbs/UNNAMED00210' as '/oracle/PRD/sapdataX/<TS_NAME>_Y/<TS_NAME>.dataY';                          -----Successfully done.

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH; -----Successfully done.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -----Successfully done.

  now after running 'recover standby database;' i am getting following error.

ORA-01153: an incompatible media recovery is active

Please advise.

Regards

Sunil

[Former Member · Accepted solution]

Hi Sunil,

Can you please try -

• alter database recover managed standby database cancel;
• ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Regards,

Vinay Shrimali

[former_member182657]

Hi,

ORA-01153 indicates a currently running media recovery process.

Try with below commands

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect;

When shutting down physical standby database, firstly turn off media recovery process otherwise you'll get same ora error.

Thanks,

Gaurav

[former_member182657]

Hi Sunil,

It's not an issue of showing wrong path of datafile to you,this usually happens when DB parameter standby_file_management is set to auto & file path which is added to the primary does not exists on standby site,then Standby creates a dummy datafile like as you are getting right now '/oracle/PDR/112_64/dbs/UNNAMED00210'.

To correct is first set parameter standby_file_management to manual & use

SQL > alter database create datafile '/oracle/PDR/112_64/dbs/UNNAMED00210' as '{standby file path}/{data file name}.dbf' size xxxM;

Or follow steps from Yves.

Thanks,

Gaurav

[former_member182657]

Hi,

First check free space left on Secondry Server Side & you can follow below to troubleshoot:

Check for the files needs to be recovered.

SQL> select * from v$recover_file where error like ‘%FILE%’;

Identify on primary of data file 210(Primary Server DB)

SQL>  select file#,name from v$datafile where file#=210;

Identify dummy file name created in (Standby)

SQL> select file#,name from v$datafile where file#=210;

After that follow steps with Yves to correct.

Thanks,

Gaurav

[Former Member]

Hi Gaurav,

  thanks for your reply.

  i have verified the free space and it shows fine. i can't explain why it is showing wrong path of data file  as '/oracle/PDR/112_64/dbs/UNNAMED00210'

  please find the output

SQL> select file#,name from v$datafile where file#=210;

     FILE#

----------

NAME

--------------------------------------------------------------------------------

       210

/oracle/PDR/112_64/dbs/UNNAMED00210

SQL> select file#,name from v$datafile where file#=210;

     FILE#

----------

NAME

--------------------------------------------------------------------------------

       210

/oracle/PDR/112_64/dbs/UNNAMED00210

[former_member182657]

Hi Sunil,

I also agree with both Nick & Yves,while adding tablespace administration (addition of new datafile at primary server side) you forgot to check or maintain the file structure at Standby Server side,and now when you are manually applying logs then getting the ORA-01110 error.

You'll get more details from alert_SID.log file on standby side.So i also suggest you to follow SQL commands at Standby Server side as mentioned by Yves to resolve this issue.

Thanks,

Gaurav