ORA-01157: cannot
identify/lock data file 201
Missing tempfile on standby database
In today's demonstration
I am going to show how to get rid of missing tempfile issue on standby database.
Below are the error which you
are seeing in alert logfile on standby site when tempfile is missing.
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/PROD/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Lets simulate the scenario for
this demonstration.
Login to standby database and
verify the below details.
SQL*Plus: Release
11.2.0.3.0 Production on Fri Sep 25 12:21:39 2015
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @open
NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE
---------
---------------- -------------------- ----------------
PROD STBY READ ONLY WITH APPLY PHYSICAL
STANDBY
Checking MRP process on standby database
PROCESS STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
---------
------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 152
40960 1471
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 157
81920 1159
ARCH CLOSING 1 158
81920 1158
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 159 927 1
MRP0 APPLYING_LOG 1 159 927
204800
RFS IDLE 0 0 0 0
Checking temp file details on standby
database
SQL> COL FILE_NAME
FOR A50
SQL> SET LINES
150
SQL> SELECT
FILE_NAME, file_id, TABLESPACE_NAME, BYTES/1024/1024 USED_MB
2 FROM
DBA_TEMP_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME USED_MB
-----------------------------------------
---------- -------------- ----------
/u01/app/oracle/oradata/PROD/temp01.dbf 1 TEMP 20
Checking tempfile by OS command on standby
database
SQL> !ls -ltrah
/u01/app/oracle/oradata/PROD/temp01.dbf
-rw-r-----. 1
oracle oinstall 21M Sep 23 23:38 /u01/app/oracle/oradata/PROD/temp01.dbf
Adding new tempfile in existing temp
tablespace (optional) on standby database
SQL> alter tablespace temp add tempfile
'/u01/app/oracle/oradata/PROD/temp02.dbf' size 50m autoextend off ;
Tablespace altered.
SQL> SELECT
FILE_NAME, file_id, TABLESPACE_NAME, BYTES/1024/1024 USED_MB
2 FROM
DBA_TEMP_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME USED_MB
----------------------------------------
---------- --------------- -------
/u01/app/oracle/oradata/PROD/temp01.dbf
1 TEMP 20
/u01/app/oracle/oradata/PROD/temp02.dbf
2 TEMP 50
Lets remove the temp file using
OS command and see what happen.
SQL> !rm
/u01/app/oracle/oradata/PROD/temp01.dbf
Checking MRP process on standby database
after removing, I don't see any effect in standby database because of missing
temp file. But after some time you will
get the alert on alert logfile about missing tempfile on standby database.
PROCESS STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
---------
------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 164 1 1
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 165 1 5
ARCH CLOSING 1 162 1 13
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 166 4 1
MRP0 APPLYING_LOG 1 166 4
204800
RFS IDLE 0 0 0 0
9 rows selected.
Checking missing tempfile by OS command on
standby database
SQL> !ls -ltra
/u01/app/oracle/oradata/PROD/temp01.dbf
ls: cannot access
/u01/app/oracle/oradata/PROD/temp01.dbf: No such file or directory
SQL> !ls -ltrah
/u01/app/oracle/oradata/PROD/temp02.dbf
-rw-r-----. 1
oracle oinstall 51M Sep 25 12:42 /u01/app/oracle/oradata/PROD/temp02.dbf
SQL> SELECT
FILE_NAME, file_id, TABLESPACE_NAME, BYTES/1024/1024 USED_MB
2 FROM
DBA_TEMP_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME USED_MB
----------------------------------------
---------- --------------- -------
/u01/app/oracle/oradata/PROD/temp01.dbf
1 TEMP 20
/u01/app/oracle/oradata/PROD/temp02.dbf
2 TEMP 50
Since tempfile "temp01.dbf"
is no longer exists. To solve this
issue, just bounce the database. It will
automatically recreate the missing tempfile.
Which you can verify after restarting database.
Bounce the standby database and verify the
details
SQL> COL
FILE_NAME FOR A50
SQL> SET LINES
150
SQL> SELECT
FILE_NAME, file_id, TABLESPACE_NAME, BYTES/1024/1024 USED_MB
2 FROM
DBA_TEMP_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME USED_MB
----------------------------------------
--------- -------------- -------
/u01/app/oracle/oradata/PROD/temp01.dbf
1 TEMP 20
/u01/app/oracle/oradata/PROD/temp02.dbf
2 TEMP 50
SQL> SELECT
TABLESPACE_NAME, BYTES_USED/1024/1024/1024 GB_USED, BYTES_FREE/1024/1024/1024
GB_FREE FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME GB_USED GB_FREE
------------------------------
---------- ----------
TEMP .002929688 .016601563
TEMP .000976563
.047851563
SQL> SELECT
TABLESPACE_NAME, SUM(BYTES_USED/1024/1024/1024)
GB_USED, SUM(BYTES_FREE/1024/1024/1024) GB_FREE
2 FROM
V$TEMP_SPACE_HEADER
3
GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME GB_USED GB_FREE
------------------------------
---------- ----------
TEMP .00390625
.064453125
Here we can see missing tempfile
is automatically crated during instance starting.
SQL> !ls
-ltrah /u01/app/oracle/oradata/PROD/temp01.dbf
-rw-r-----.
1 oracle oinstall 21M Sep 25 13:07 /u01/app/oracle/oradata/PROD/temp01.dbf
SQL> !ls -ltrah
/u01/app/oracle/oradata/PROD/temp02.dbf
-rw-r-----. 1
oracle oinstall 51M Sep 25 12:42 /u01/app/oracle/oradata/PROD/temp02.dbf
Hope this help....