Wednesday 22 August 2018

Problems with Missing tempfiles in Physical Standby ORA-01157 | ORA-01110 | ORA-01187

Temporary File physically missing but Entry exists in Control File | ORA-01157 | ORA-01110 | ORA-01187

Temporary tablespaces or tempfiles doesn't lead to data loss if lost or corrupted but are the major part of database operations. The same space is used in all kind of sort operations whenever data is fetched from the datafiles. In this post, I am basically discussing a scenario where I am working on my Physical Standby database and I am not having my physical files for tempfiles but the entry exists in the Control File and then issues I faced because of that. At the end, I found out the way to resolve the same but the same can give trouble to most of the DBAs so here is the Problem and Solution for the same.


So I was trying to install IFS Applications on my Single Instance Physical Standby Site for which I needed to convert the DR Site to Snapshot Standby (Convert to Snapshot Standby option is available from 11g release and later versions). I got some error regarding temporary tablespace while installing the application so I tried to check if everything is fine. I queried the DBA_TEMP_FILES view to check the Temporary Tablespace and this is what I got

SQL> select * from dba_temp_files;
select * from dba_temp_files
              *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATADG/primdb/tempfile/temp.336.922181727'

Now I am using ASM (Automatic Storage Management) in my 2-Node Primary Database but this Single instance DR Site in Non-ASM. The entry came from the controlfile backup of Primary which I used to create and restore DR. I had created on tempfile manually in the local File System which I remembered so I tried to connect to RMAN to query REPORT SCHEMA and check the complete file structure of my database but again I got the same error.

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 21 14:48:42 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 203: '/data/standb/temp.334.922181725'

Here "/data/standb/temp.334.922181725" was physically existing but the above one mentioned with +DATADG location was physically unavailable so I tried to drop the same from my database and I got this.

SQL> alter database tempfile '+DATADG/primdb/tempfile/temp.336.922181727' drop including                                                                                 datafiles;
alter database tempfile '+DATADG/primdb/tempfile/temp.336.922181727' drop including data                                                                                files
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Rather than dropping them, I thought of putting them in OFFLINE state to see what happens.

SQL> alter database tempfile '+DATADG/primdb/tempfile/temp.336.922181727' offline;

Database altered.

SQL> alter database tempfile '+DATADG/primdb/tempfile/temp.335.922181725' offline;

Database altered.

Successfully done !! but the same error was coming for the tempfile which was physically existing.

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 21 15:13:34 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 203: '/data/standb/temp.334.922181725'

At this point, I added one more tempfile to TEMP tablespace,

SQL> alter tablespace temp add tempfile '/data/standb/TEMP01.DBF' size 1G;

Tablespace altered.

Tried to query the Temporary tablespace/tempfile Views again,

SQL> select * from dba_temp_files;
select * from dba_temp_files
              *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 203: '/data/standb/temp.334.922181725'


SQL> select * from V$tempfile;
select * from V$tempfile
              *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 203: '/data/standb/temp.334.922181725'

ORA-01187 showed verification failure which can be because of corruption issues so I put this tempfile as well to OFFLINE state,

SQL> alter database tempfile '/data/standb/temp.334.922181725' offline;

Database altered.

And all my problems were gone :)

SQL> select * from V$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         3       6.9906E+10 11-DEC-15          3          3 OFFLINE READ WRITE
         0          0    671088640       8192
/data/standb/temp.334.922181725

         1       7.8035E+10 21-AUG-18          3          2 ONLINE  READ WRITE
1073741824     131072   1073741824       8192
/data/standb/TEMP01.DBF

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------


SQL> select * from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/data/standb/temp.334.922181725
         3 TEMP                                                 OFFLINE


/data/standb/TEMP01.DBF
         1 TEMP                           1073741824     131072 ONLINE
           2 NO           0          0            0 1072693248      130944

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------


$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 21 17:25:17 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: IFSPROD (DBID=2807489448)

RMAN>


I hope this helps !!

No comments:

Post a Comment