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

Using sed to replace parts of specific lines in a file

In this example we are using the following test file test.tmp (here listed, part of it) where we will replace the Oracle DB Version (the 11.2.0.4.0 below) between the path (/u00/app/oracle/product/) and . P0_EE in the line for DB  DB00001 by a new version string "--.-.-.-.-"

#SID;ORACLE_HOME;Position;Type;Standby;Cluster;DB-instances

. . .

DB00001;/u00/app/oracle/product/11.2.0.4.0.P0_EE;60;I;N;S;

DB00002;/u00/app/oracle/product/11.2.0.4.0.P0_EE;70;I;N;S;

This tests are performed under Linux in the bash shell.

In order to be flexible, we are using the following variables:

OS> DB=DB00001

OS> V=--.-.-.-.-

Here the sed command with the regular expression:

OS> sed 's|\('${DB}';/u00/app/oracle/product/\).*\(.P0_EE\)|\1'${V}'\2|g' test.tmp

The output:

#SID;ORACLE_HOME;Position;Type;Standby;Cluster;DB-instances

. . .

DB00001;/u00/app/oracle/product/--.-.-.-.-.P0_EE;60;I;N;S;

DB00002;/u00/app/oracle/product/11.2.0.4.0.P0_EE;70;I;N;S;

Explanation of the regular expression:

The first character after "s" (here the "|") defines the parts separator.

(Slashes (/) are commonly used, but since a big part of our expression is a UNIX path containing slashes, I decided to use another character in order to simplify the interpretation.)

The first part of the expression between the first two "|" (i.e. \('${DB}';/u00/app/oracle/product/\)\(.*\)\(. P0_EE\) ) defines what must match.

The escaped brackets "\(" and "\)" define back references that are referenced by \1 and \3 in the second part of the e.

The value of the variable ${DB} is concatenated with ";/u00/app/oracle/product/ ", forming the first "back reference block" that is referenced by the \1 after the "|".

The expression ".* " matches anything in between blocks one and two. It is not referenced, since if will be replaced by "${V}".

The second "back reference block" (.i.e. "\(.P0_EE\)") matches only ".P0_EE". It is referenced by the \2.

So, in our example, only lines matching the regular expression "DB00001;/u00/app/oracle/product/.*P0_EE" are changed and replaced by the string specified through "\1'${V}'\2" (between the second and third "|").

Another variant - more general and simpler - of the command (ignoring the ".P0_EE" part):

OS> sed 's|\('${DB}';/u00/app/oracle/product/\)\([^;]*\)|\1'${V}'|g' test.tmp

Output:

#SID;ORACLE_HOME;Position;Type;Standby;Cluster;DB-instances

. . .

DB00001;/u00/app/oracle/product/--.-.-.-.-;60;I;N;S;

DB00002;/u00/app/oracle/product/11.2.0.4.0.P0_EE;70;I;N;S;

January 20, 2017 at 10:47AM

HowTo Stop/Kill Oracle Data Pump Jobs

When running Oracle Data Pump, the prompt (interactive mode) can be accessed by pressing the Ctrl-C Keys.

Here the import case as an example:

Import> stop_job=IMMEDIATE

If you are not in the interactive mode of Data Pump (you are in the OS level on the console), you can reattach to the export / import job running in background by recalling the correspontent binary (i.e. If you was performing an import, call impdp. If you was performing an export, call expdp.) and give the name of the Data Pump job as parameter to "ATTACH=":

OS> impdp ATTACH=SYS_IMPORT_SCHEMA_01

Import> stop_job=IMMEDIATE

or

Import> kill_job

If you do not know the name of the Data Pump job, you can perform the following query on the DB:

SQL> SELECT * FROM dba_datapump_jobs WHERE state <> 'NOT RUNNING';

=> JOB_NAME: SYS_IMPORT_SCHEMA_01

How To Cleanup Orphaned Data Pump Jobs

List Data Pump jobs:

set lines 999

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

  FROM dba_datapump_jobs

 WHERE job_name NOT LIKE 'BIN$%'

 ORDER BY 1,2;

-- Query 1 --

List the Data Pump master tables:

SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"

  FROM dba_objects o, dba_datapump_jobs j

 WHERE o.owner=j.owner_name AND o.object_name=j.job_name

   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

-- Query 2 --

Drop the correspondent table:

DROP TABLE xxxx;

Re-run the query on dba_datapump_jobs and dba_objects.

If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. (Adapt the highlighted parameter) E.g.:

SET serveroutput on

SET lines 100

DECLARE

   h1 NUMBER;

BEGIN

   h1 := DBMS_DATAPUMP.ATTACH('xxxx','SYSTEM');

   DBMS_DATAPUMP.STOP_JOB(h1);

END;

/

-- PL/SQL 1  --

Killing all Data Pump jobs listed by -- Query 1 -- by using -- PL/SQL 1  --  (But take care!):

DECLARE

   h1    NUMBER;

   l_cur SYS_REFCURSOR;

BEGIN

   FOR l_cur IN (SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

                      FROM dba_datapump_jobs

                     WHERE job_name NOT LIKE 'BIN$%')

   LOOP

     BEGIN

      DBMS_OUTPUT.PUT_LINE('Kill job '||l_cur.job_name||'  owner: '||l_cur.owner_name||'  state: '||l_cur.state);

      h1 := DBMS_DATAPUMP.ATTACH(l_cur.job_name, 'SYS');

      DBMS_DATAPUMP.STOP_JOB(h1);

     EXCEPTION

       WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('EXCEPTION:'||sqlerrm);

     END;

   END LOOP;

END;

/

-- PL/SQL 2  --

Generates UNIX statements to kill Data Pump Linux/UNIX processes:

SELECT 'kill -9 ' ||spid , s .action , s .program, s .machine , s .osuser , s.module

  FROM v$process p, v$session s

WHERE s. paddr = p. addr

  AND (action LIKE 'CP_%' OR UPPER(module) LIKE '%PUMP%');

2017.01.19

Beware booking Michel Residence in Camaguey - Cuba

For once, not a computer science theme.

Last December we traveled around Cuba and visited lots of very picturesque places.

On of these historical places that pleased me most was Trinidad with its beautifully restored colonial buildings. 

On the other hand, Camaguey was not worth visiting it.

One disappointing experience there was Michels Residence (Calle Bembeta 571). Inside is not bad, it has even beautifully decorated rooms in an old building.

The prize for the room was a little bit high (25-. CUC for one night no breakfast) comparing to other casas particulares. (The air conditioner was not usable and the bed was awful.)

What disappointed us more, was the price charged for the meal and breakfast (60.- CUC).
A high price for that kind of meal in Cuba.

(Since this casa belonged to a friend of a relative of friend of us here in Europe, we did not ask for the prices in advance.)