Question Re: Oracle Standby Database Error: ORA-01157: cannot identify/lock data file 201 in Technology Q&A

Oracle Standby Database Error: ORA-01157: cannot identify/lock data file 201

Former Member — Sat, 09 Nov 2013 09:13:55 GMT

Dear All,

We have installed and configured the Oracle Standby Database with Oracle dataguard.

Due to some reasons we are switched our business operations to Standby server.

I checked the dataguard configuration before and after the switchover through DGMGRL>show configuration; the output is success. Based on this confidence we switched over the production operations to Standby server.

Environment: windows server 2008 R2 with Oracle 11G enterprise edition (11.2.0.3)

Now we are facing some issues in standby server. (Now Production Operations running on Standby Server)

We are getting the below error when users trying to print some checks from SAP.

ST22: Runtime Error DBIF_RSQL_SQL_ERROR

Error Text of the Database: "ORA-01157: cannot identify/lock data file 201 -

see DBWR trace file#ORA-01110: data file 201:

'H:\ORACLE\ECP\SAPDATA6\TEMP_1\TEMP.DATA1'"

I observed that the problem with Temp file. When I checked in the standby system the file “'H:\ORACLE\ECP\SAPDATA6\TEMP_1\TEMP.DATA1' “ was not there. I copied the file manually from primary to Standby server.

After copied the tempfile the result of SQL> select * from v$tempfile; is same in both primary and standby databases.

I have following queries in this view: please clarify.

Now the result of SQL> select * from v$tempfile; is same in both the databases, so if we restart the system the problem will get solve?
I guess we can solve this issue by executing the below commands.

SQL> alter database tempfile ‘H:\ORACLE\ECP\SAPDATA6\TEMP_1\TEMP.DATA1’ drop;

SQL> alter tablespace PSAPTEMP add tempfile ‘H:\ORACLE\ECP\SAPDATA6\TEMP_1\TEMP.DATA1’ size 7G;

If we execute this commands will it affect any Dataguard Configuration, I am using the Dataguard with standby_file_management = Auto

Kindly suggest the solution without affecting my Dataguard configuration. now Dataguard configuration is success.

Thanks

Kishore Ch

Re: Oracle Standby Database Error: ORA-01157: cannot identify/lock data file 201

Former Member — Sat, 09 Nov 2013 14:32:16 GMT

Hi Kishore,

As you are dropping the tempfile and later re-creating it , it should not create any issue with data guard configuration.

Please go ahead with the steps.

Hope this helps.

Regards,

Deepak Kori

Re: Oracle Standby Database Error: ORA-01157: cannot identify/lock data file 201

stefan_koehler — Sun, 10 Nov 2013 11:55:40 GMT

Hi Kishore,

> I copied the file manually from primary to Standby server.

There is no need to copy a temporary data file from primary database as it is temporary. You can just recreate / add it on standby site and proceed.

> I checked the dataguard configuration before and after the switchover through DGMGRL

So you are using Data Guard Broker. Unfortunately it seems like you did not follow the "Oracle 11.2 Data Guard Physical Standby Switchover Best Practices using the Broker" guide (MOS note #1305019.1), otherwise you would have noticed that you should check the following point before a switch over. ("For each temporary tablespace on the standby, verify that temporary files associated with that tablespace on the primary database also exist on the standby database.")

> I observed that the problem with Temp file. When I checked in the standby system the file was not there.

Nothing unusual as temporary data files are not handled through redo apply mechanisms in the same way as regular data files, if the parameter standby_file_management is set to AUTO. You have to care about this on your own. Temporary data files are established during a RMAN duplicate operation, but afterwards you have to handle it on your own. If you created your standby database not through RMAN duplicate it is possible that even these initial temporary data files are missing as well.

The detailed handling procedure is described in MOS note #1514588.1 (Data Guard Physical Standby - Managing temporary tablespace tempfiles).

Regards

Stefan

Re: Oracle Standby Database Error: ORA-01157: cannot identify/lock data file 201

Reagan — Sun, 10 Nov 2013 19:43:41 GMT

Hello

You don't need to restart the database.

All you need to do is recreate the Temp file

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'H:\ORACLE\ECP\SAPDATA6\TEMP_1\TEMP.DATA1' SIZE 7000M REUSE AUTOEXTEND OFF;

Regards

Re: Oracle Standby Database Error: ORA-01157: cannot identify/lock data file 201

Former Member — Mon, 11 Nov 2013 11:38:03 GMT

Dear All,

i just restarted the database, the temp file is created automatically. problem solved.

Thanks every one.

Kishore Ch