ORA-01157: cannot identify/lock data file NN - see DBWR trace file

After creating a DB as copy of another DB via RMAN restore, some applications accessing the DB received the following error (or I, when trying to list the tablespaces via Toad):

ORA-01157: cannot identify/lock data file 1023 - see DBWR trace file

ORA-01110: data file 1023: '+U01'

Remarkable is, that in our DB there is no file with number 1023.

The error from above is also displayed in the alert log.

Performing . . .

ALTER SYSTEM CHECK DATAFILES

. . . also wrote the ORA-01110 error in a trace file:

OS> more /u00/app/oracle/diag/rdbms/<Host>/<SID>/trace/<SID>_ora_<NNNN>.trc

. . .

*** 2017-01-25 08:19:37.976

DDE rules only execution for: ORA 1110

----- START Event Driven Actions Dump ----

---- END Event Driven Actions Dump ----

----- START DDE Actions Dump -----

Executing SYNC actions

----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----

Successfully dispatched

----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----

Executing ASYNC actions

----- END DDE Actions Dump (total 0 csec) -----

DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)

*** 2017-01-25 08:25:50.404

ORA-01110: data file 1023: '+U01'

CAUSE

The original cause of the problem was, that on the target host (where the new DB was created) there was not enough space (for two DBs instead of only one like on the source host).

Because of this, not all files for the temporary tablespaces could be created.

Listing the temporary files:

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME              TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE Tablespace Name

---------- ---------------- ------------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ----------------

         1             3244 17.10.2016 17:21:25          3          1 ONLINE  READ WRITE          0          0   1.3422E+10       8192 +U01

         2       1.7714E+12 18.10.2016 11:32:27         18          1 ONLINE  READ WRITE          0          0   3.4359E+10       8192 +U01

         3       1.7714E+12 18.10.2016 11:32:27         18          2 ONLINE  READ WRITE          0          0   2.1475E+10       8192 +U01

         4       1.7714E+12 18.10.2016 11:32:27         18          3 ONLINE  READ WRITE          0          0   1.8254E+10       8192 +U01

         5       1.7714E+12 18.10.2016 10:39:43          3          2 ONLINE  READ WRITE 2.1475E+10    2621440   2.1475E+10       8192 +U01/<DB_UNIQUE_NAME>/tempfile/temp.382.934186047

         6       1.7714E+12 18.10.2016 11:32:28          3          3 ONLINE  READ WRITE 2.1475E+10    2621440   2.1475E+10       8192 +U01/<DB_UNIQUE_NAME>/tempfile/temp.383.934186047

SOLUTION

Recreate the temporary tablespaces.

Do not forget to create the new tablespaces with the same max. size as the original ones.

(Since my tablespace max. size is bigger than the limit of 32GB for a file, I has to add several files in my example bellow.)

Steps in short

1. Make sure, that no applications can access the DB and disable the jbobs:
   -- Get the original value
   show parameter job_queue_processes
   -- Set the value to 0
   SQL> ALTER SYSTEM SET job_queue_processes=0;
2. When recreating the default temporary tablespace:
   • Create a new temp. temporary tablespace (for example tmp_temp) (i can be small)
   • Set this tmp_temp tablespace as default 
   • Drop the original tablespace (temp)
   • Recreate the original temp tablespace
   • Set the recreated temp tablespace as default 
   • Drop the tmp_temp tablespace
3. When recreating temporary tablespace belonging to an application:
   
   • Drop the original tablespace (for example: ts_app_temp)
   • Recreate the original ts_app_temp tablespace
4. At the end, reset the job_queue_processes

Steps in detail

2. When recreating the default temporary tablespace

Create a new temp. temporary tablespace (for example tmp_temp) (i can be small)

CREATE TEMPORARY TABLESPACE tmp_temp TEMPFILE '+U01' SIZE 100M AUTOEXTEND ON NEXT 512M MAXSIZE 1G;

Set this tmp_temp tablespace as default

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmp_temp;

Drop the original tablespace (temp)

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Recreate the original temp tablespace

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+U01' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

ALTER TABLESPACE TEMP ADD TEMPFILE '+U01' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

ALTER TABLESPACE TEMP ADD TEMPFILE '+U01' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

Set the recreated temp tablespace as default 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Drop the tmp_temp tablespace

DROP TABLESPACE tmp_temp INCLUDING CONTENTS AND DATAFILES;

3 . When recreating temporary tablespace belonging to an application

Perhaps you will need to open the DB in restricted mode, in order to perform the following steps.

(In my case, this was not necessary.)

Drop the original tablespace (for example: ts_app_temp)

DROP TABLESPACE ts_app_tmp INCLUDING CONTENTS AND DATAFILES;

Recreate the original ts_app_temp tablespace

CREATE TEMPORARY TABLESPACE ts_app_temp TEMPFILE '+U01' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;

ALTER TABLESPACE ts_app_temp ADD TEMPFILE '+U01' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;

ALTER TABLESPACE ts_app_temp ADD TEMPFILE '+U01' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;

Tags: Oracle, ASM, Tablespace, Publish, Temporary Tablespace, Data files, ORA-01157
January 25, 2017 at 10:43AM