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

[Former Member]

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.

1. 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?
2. 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

[Former Member · Accepted solution]

Dear All,

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

Thanks every one.

Kishore Ch