Saturday, January 30, 2016

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

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....

No comments:

Post a Comment