How to restore a lost nonsystem datafile on a different location while the database is open

In the following scenario I'm going to lose datafiles of a non-system critical tablespace and restore them, while the database is open, to a location other than the original one because I'm experiencing a serious and permanent media failure.
Before proceeding I add another datafile to EXAMPLE tablespace so it is now formed by 2 different datafiles.

SQL> select file_name from dba_data_files
  2  where TABLESPACE_NAME = 'EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example add datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf' size 1M autoextend on next 5M maxsize 50M;

Tablespace altered.

SQL> select file_name from dba_data_files
  2  where TABLESPACE_NAME = 'EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
/home/oracle/app/oracle/oradata/orcl/example02.dbf

Of course to restore a tablespace you need to have a valid backup so I'm going to execute a backup tablespace command for the EXAMPLE tablespace using RMAN:

RMAN> backup tablespace example;

Starting backup at 21-01-2013 08:09:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: starting piece 1 at 21-01-2013 08:09:24
channel ORA_DISK_1: finished piece 1 at 21-01-2013 08:09:50
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_21/o1_mf_nnndf_TAG20130121T080924_8htt1o4b_.bkp tag=TAG20130121T080924 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 21-01-2013 08:09:50

Starting Control File and SPFILE Autobackup at 21-01-2013 08:09:50
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_21/o1_mf_s_805277390_8htt2hst_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-01-2013 08:09:53

The original location of EXAMPLE datafiles is:
/home/oracle/app/oracle/oradata/orcl/

[oracle@localhost orcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ls -l example0*
-rw-rw---- 1 oracle oracle 85991424 Jan 21 08:09 example01.dbf
-rw-rw---- 1 oracle oracle  1056768 Jan 21 08:09 example02.dbf

During the restore operation I will instruct RMAN to recreate them on a new destination: /home/oracle/app/oracle/oradata/orcl/non_default_location

[oracle@localhost orcl]$ cd non_default_location/
[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ls -l
total 0

A media failure happened and I've lost all datafiles belonging to EXAMPLE tablespace:

[oracle@localhost orcl]$ rm example0*
[oracle@localhost orcl]$

I'm not able to use objects created into EXAMPLE tablespace.

SQL> select prod_name, prod_desc from sh.products where prod_id = 1;
select prod_name, prod_desc from sh.products where prod_id = 1
                                    *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

I discover also it is a permanent disk failure and I won't be able to restore EXAMPLE's datafiles on the original location, perhaps in a second moment in the next days, but now I have to solve this issue as soon as possible.
Within RMAN client you can use set newname for datafile command to change the name of multiple files during the restore operation:
after you specify the above command you have to run also switch datafile all command to update your controlfile with the renamed datafiles. If you don't use the switch command RMAN records the restored files as datafile copy in RMAN repository.
An RMAN switch is equivalent to the SQL alter database rename file command.
It's important to note that both commands must be executed inside a run {...} block.

To identify your original datafiles you can use their absolute file numbers, full path or relative file names; to recreate them on a new location you have to specify their full path file names, using eventually some substitution variable like %U to specify a system-generated unique file name and avoid file name collisions.

While connected to your database you can query V$DATAFILE, V$DATAFILE_HEADER or V$DATAFILE_COPY to obtain file number of the missing datafile or run the report schema command from RMAN client.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jan 21 21:59:42 2013

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

connected to target database: ORCL (DBID=1229390655)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    0        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    0        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

My missing datafiles have 5 and 9 as file number. To restore and recover them on a new location I have to execute the following run {...} block:

RMAN> run {
2> sql 'alter database datafile 5,9 offline';
3> set newname for datafile 5 to '/home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf';
4> set newname for datafile 9 to '/home/oracle/app/oracle/oradata/orcl/non_default_location/%U';
5> restore datafile 5,9;
6> switch datafile all;
7> recover datafile 5,9;
8> sql 'alter database datafile 5,9 online';
9> }

sql statement: alter database datafile 5,9 offline

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-01-2013 22:20:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/non_default_location/data_D-ORCL_TS-EXAMPLE_FNO-9
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_21/o1_mf_nnndf_TAG20130121T080924_8htt1o4b_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_21/o1_mf_nnndf_TAG20130121T080924_8htt1o4b_.bkp tag=TAG20130121T080924
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 21-01-2013 22:20:59

datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=805328459 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=37 STAMP=805328459 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/data_D-ORCL_TS-EXAMPLE_FNO-9

Starting recover at 21-01-2013 22:21:00
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 21-01-2013 22:21:01

sql statement: alter database datafile 5,9 online

Some considerations need to be made:
- the restore and recover operations were made while database was open so I needed to put those datafiles offline;
- because all missing datafiles belonged to the same tablespace I could use alter tablespace ... offline immediate, restore tablespace and recover tablespace syntax, but I want to show their uses in another post;
- I could use switch datafile command in place of switch datafile all. 

On the new location are now available two files. File data_D-ORCL_TS-EXAMPLE_FNO-9 is that one created automatically by Oracle using %U as substitution variable.

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ls -l
total 85100
-rw-rw---- 1 oracle oracle  1056768 Jan 21 22:21 data_D-ORCL_TS-EXAMPLE_FNO-9
-rw-rw---- 1 oracle oracle 85991424 Jan 21 22:21 example01.dbf

Have a look at the new output produced by report schema command:

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    1        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/non_default_location/data_D-ORCL_TS-EXAMPLE_FNO-9

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

Few days later we are able to move back our EXAMPLE datafiles on their original location because a new disk is ready to be used.

How can we proceed ? 

I will follow the steps already described in this post.
First thing to do is to copy your datafiles using the format clause, specifying you want to create your datafile copy to the original location.

RMAN> backup as copy datafile 5 format='/home/oracle/app/oracle/oradata/orcl/example01.dbf';

Starting backup at 22-01-2013 06:47:40
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
output file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf tag=TAG20130122T064741 RECID=38 STAMP=805358864
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 22-01-2013 06:47:44

Starting Control File and SPFILE Autobackup at 22-01-2013 06:47:44
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_22/o1_mf_s_805358864_8hx9nkbn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-01-2013 06:47:47

RMAN>  backup as copy datafile 9 format='/home/oracle/app/oracle/oradata/orcl/example02.dbf';

Starting backup at 22-01-2013 06:48:08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/data_D-ORCL_TS-EXAMPLE_FNO-9
output file name=/home/oracle/app/oracle/oradata/orcl/example02.dbf tag=TAG20130122T064808 RECID=39 STAMP=805358889
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-01-2013 06:48:10

Starting Control File and SPFILE Autobackup at 22-01-2013 06:48:10
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_22/o1_mf_s_805358890_8hx9objd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-01-2013 06:48:11

Next step is to put those datafiles offline.

RMAN> sql 'alter database datafile 5,9 offline';

sql statement: alter database datafile 5,9 offline

Switch to your new datafiles location updating your controlfiles.

RMAN> switch datafile 5,9 to copy;

datafile 5 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/example01.dbf"
datafile 9 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/example02.dbf"

Recover your datafiles because some transactions could be occurred between backup as copy datafile command and putting datafiles offline.

RMAN> recover datafile 5,9;

Starting recover at 22-01-2013 06:48:31
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 22-01-2013 06:48:31

Let datafiles be available to all the users, putting them online.

RMAN> sql 'alter database datafile 5,9 online';

sql statement: alter database datafile 5,9 online

report schema command displays the new location of EXAMPLE datafiles.

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    1        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

That's all.

How to export and import Application Contexts using Data Pump

You have to export some schemas used by your latest application:
this new application restricts access by a customer to its own user's data based on a set of name-value pairs that Oracle Database stores in memory, the so called "Application Context".

You have already completed the export and import operations, but you do not find on production database any application context.
This is the export command you executed...

[oracle@vsi03dev log]$ expdp system dumpfile=3schema.dmp schemas=schema_q,schema_t,schema_v exclude=statistics

and the following is the import command:

[oracle@qdb01frm dump]$ impdp system dumpfile=3schema.dmp remap_tablespace=tsd_schema:tsd_schema_new,tsi_schema:tsi_schema_new

... but looking at the export log you discover the application contexts are not exported by Data Pump.
Why ?
On my development database I can see the following application contexts:

[oracle@vsi03dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 19 21:45:58 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 999 
SQL> set linesize 180
SQL> select * from dba_context;

NAMESPACE         SCHEMA         PACKAGE        TYPE
------------------------------ ------------------------------ ------------------------------ ----------------------
REGISTRY$CTX         SYS         DBMS_REGISTRY_SYS       ACCESSED LOCALLY
LT_CTX          WMSYS         LT_CTX_PKG       ACCESSED LOCALLY
DR$APPCTX         CTXSYS         DRIXMD        ACCESSED LOCALLY
EM_GLOBAL_CONTEXT        SYSMAN         SETEMUSERCONTEXT       ACCESSED GLOBALLY
EM_USER_CONTEXT         SYSMAN         SETEMUSERCONTEXT       ACCESSED LOCALLY
SCHEMA_T_USER_CONTEXT        SCHEMA_T               PKG_SCHEMA_T_CONTEXT       ACCESSED LOCALLY

but after the import on my production database I'm not able to locate them:

[oracle@qdb01frm ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 19 21:44:18 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 180
SQL> set pagesize 999
SQL> select * from dba_context;

NAMESPACE         SCHEMA         PACKAGE        TYPE
------------------------------ ------------------------------ ------------------------------ ----------------------
REGISTRY$CTX         SYS         DBMS_REGISTRY_SYS       ACCESSED LOCALLY
LT_CTX          WMSYS         LT_CTX_PKG       ACCESSED LOCALLY
DR$APPCTX         CTXSYS         DRIXMD        ACCESSED LOCALLY
EM_GLOBAL_CONTEXT        SYSMAN         SETEMUSERCONTEXT       ACCESSED GLOBALLY
EM_USER_CONTEXT         SYSMAN         SETEMUSERCONTEXT       ACCESSED LOCALLY

If you query the dba_object view you can discover SCHEMA_T_USER_CONTEXT is not owned by those schema, but it is owned by SYS

SQL> set linesize 180
SQL> set pagesize 999
SQL> column object_name format a30
SQL> select object_name, owner from dba_objects where object_name = 'SCHEMA_T_USER_CONTEXT';

OBJECT_NAME         OWNER
------------------------------ ------------------------------
SCHEMA_T_USER_CONTEXT          SYS

This is a good starting point to understand why the application context are not exported by DataPump.
SCHEMA_T_USER_CONTEXT is not part of exported schemas so it won't be considered during our export and import SCHEMA operations.
To successfully import an application context you have to create and specify a different data pump job using the full database option.
In my case I specify the following command during export operation ...

[oracle@vsi03dev log]$ expdp system dumpfile=context.dmp full=y include=context
;;;
Export: Release 10.2.0.5.0 - 64bit Production on Friday, 07 December, 2012 15:31:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** dumpfile=context.dmp full=y include=context
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/CONTEXT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /opt/app/oracle/product/10.2.0/db_1/rdbms/log/context.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:31:53

... and this command to complete import data pump job of application context (after you have copied context.dmp file from development to production machine):

[oracle@qdb01frm dump]$ impdp system dumpfile=context.dmp include=context full=y
;;;
Import: Release 10.2.0.4.0 - 64bit Production on Monday, 21 January, 2013 8:32:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=context.dmp include=context full=y
Processing object type DATABASE_EXPORT/CONTEXT
ORA-31684: Object type CONTEXT:"REGISTRY$CTX" already exists
ORA-31684: Object type CONTEXT:"LT_CTX" already exists
ORA-31684: Object type CONTEXT:"DR$APPCTX" already exists
ORA-31684: Object type CONTEXT:"EM_GLOBAL_CONTEXT" already exists
ORA-31684: Object type CONTEXT:"EM_USER_CONTEXT" already exists
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 08:32:36

Connecting to the database you can view your application context is now ready to be used:

[oracle@qdb01frm dump]$ sqlplus / as sysdba
SQL> set linesize 180
SQL> col namespace format a25
SQL> col schema format a10
SQL> col package format a25
SQL> SELECT C.*, O.LAST_DDL_TIME, O.CREATED
  2  FROM   SYS.DBA_CONTEXT C, SYS.DBA_OBJECTS O
  3  WHERE  C.NAMESPACE = O.OBJECT_NAME
  4  AND    O.OWNER = 'SYS'
  5  AND    O.OBJECT_TYPE = 'CONTEXT'
  6  AND    O.OBJECT_NAME = 'SCHEMA_T_USER_CONTEXT'
  7  AND    C.SCHEMA = 'SCHEMA_T';

NAMESPACE           SCHEMA     PACKAGE         TYPE        LAST_DDL_ CREATED
------------------------- ---------- ------------------------- ---------------------- --------- ---------
SCHEMA_T_USER_CONTEXT     SCHEMA_T   PKG_SCHEMA_T_CONTEXT      ACCESSED LOCALLY       21-JAN-13 21-JAN-13

That's all.

How to recover from a loss of a nonsystem tablespace on the same location while the database is closed

In a previous post we saw how to proceed when you lose a non-system tablespace while the database is open.
Today I'm going to describe another way to restore it while the database is not open: for some reasons your database crashed and while trying to start it up you are getting "ORA-01157: cannot identify/lock data file %s - see DBWR trace file".

Let's simulate a loss of the EXAMPLE tablespace, in my case formed by only one datafile:

[oracle@localhost ~]$ ll /home/oracle/app/oracle/oradata/orcl/example01*
-rw-rw---- 1 oracle oracle 85991424 Nov 30 02:08 /home/oracle/app/oracle/oradata/orcl/example01.dbf
[oracle@localhost orcl]$ rm example01.dbf 

The database is not open and if I try to execute startup command it signals ORA-01157 error. That error means instance was not able to open the example01.dbf (data)file.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 11 03:15:10 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'

If you look at the alert log the same error and a trace file are generated

...
Fri Jan 11 03:15:44 2013
ALTER DATABASE OPEN
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_12904.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12961.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
...

Even if I know how to solve this kind of problem, I would like to take some of your time and use a new RMAN feature, the Data Recovery Advisor.
It can detect and show current restore and recovery problems occurring in your database, advices you about their resolution and even execute for you all the RMAN commands to fix problems.

When I remember to use Data Recovery Advisor I usually perform the following four commands:

RMAN> list failure; 
RMAN> list failure  detail;
RMAN> advice failure ;
RMAN> repair failure preview;

Let's see what Data Recovery Advisor shows us executing my previous commands. The instance is in MOUNT state and this is the output executing LIST FAILURE command:

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jan 11 05:20:45 2013

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing

As you can see one database failure exists from "11-01-2013 03:15:46" and it has a failure id as 363, it's status is still OPEN having also a HIGH priority.
We want now to have more details on that specific failure id: simply execute the following command to know example01.dbf datafile is missing and because it contains some objects they are not available.

RMAN> list failure 363 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 363
  Failure ID Priority Status    Time Detected       Summary
  ---------- -------- --------- ------------------- -------
  9865       HIGH     OPEN      11-01-2013 03:15:46 Datafile 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf' is missing
    Impact: Some objects in tablespace EXAMPLE might be unavailable

To obtain advices on how to solve your current failure run the following command.
It will show the manual or automated actions required to repair your database. The commands you can use to solve your issue automatically are contained into the repair script.

RMAN> advise failure 363;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /home/oracle/app/oracle/oradata/orcl/example01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4119770863.hm

You can now open with a text editor your repair script to look at the suggested commands, but why not use again RMAN client and the REPAIR FAILURE PREVIEW command ?
As you can see RMAN displays the repair commands without actually running them.

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4119770863.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 5;
   recover datafile 5;
   sql 'alter database datafile 5 online';

So it's time to recover our tablespace executing the restore command and the name of your lost tablespace:

RMAN> restore tablespace example;

Starting restore at 11-01-2013 06:45:05
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=32 STAMP=804375950 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_8gz9rctt_.dbf
destination for restore of datafile 00005: /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf RECID=0 STAMP=0
Finished restore at 11-01-2013 06:45:12

After your tablespace was restored back from your backup pieces it's time to execute the recover command.

RMAN> recover tablespace example;

Starting recover at 11-01-2013 06:45:18
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-01-2013 06:45:19

The tablespace is now recovered we can try to open our database. for your users.

RMAN> alter database open;

database opened

An extract of the alert log taken during the restore and recover process...

...
Fri Jan 11 06:45:10 2013
Restore of datafile copy /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_8gz9rctt_.dbf complete to datafile 5 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  checkpoint is 14769061
Fri Jan 11 06:45:19 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
 tablespace EXAMPLE
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 38 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
  Mem# 1: /home/oracle/app/oracle/oradata/orcl/redo02b.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
 tablespace EXAMPLE
alter database open
...

That's all.

Revoking RESOURCE role on 11gR2 resets all QUOTA previously granted

This post is related to a different behaviour on revoking RESOURCE role between Oracle version 11gR2 and 10gR2. It could happen that revoking the RESOURCE role from a user on 11gR2 generates several errors from user's prospective, especially when he tries to perform his usual DML operations: this situation doesn't happen instead on 10gR2.

Let's go through an example and connect to an Oracle 11gR2 instance.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I'm going to create MARCOV user and granting him two privileges (CREATE SESSION and CREATE TABLE):

SQL> CREATE USER MARCOV
  2    IDENTIFIED BY "MARCOV"
  3    DEFAULT TABLESPACE USERS
  4    TEMPORARY TABLESPACE TEMP
  5    PROFILE DEFAULT
  6    ACCOUNT UNLOCK;

User created.

SQL>   GRANT CREATE SESSION, CREATE TABLE TO MARCOV;

Grant succeeded.

The following query shows privileges granted to the user:

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2    FROM dba_sys_privs dsp
  3   WHERE dsp.grantee = 'MARCOV';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION                           NO  MARCOV
CREATE TABLE     NO  MARCOV

I'm going to give some quota to my user on tablespace "USERS":

SQL> ALTER USER MARCOV QUOTA 50M ON "USERS";

User altered.

I can query dba_ts_quotas to show if my user has quota on some tablespaces:

SQL>  SELECT USERNAME,
  2    tablespace_name, 
  3    MAX_BYTES/1024/1024 MAXMBYTE,
  4    BYTES, 
  5    DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6   from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

USERNAME                       TABLESPACE_NAME                  MAXMBYTE
------------------------------ ------------------------------ ----------
     BYTES PERCENT_USED
---------- ------------
MARCOV                         USERS                                  50
         0            0

Connecting using MARCOV user, I'm able to create T1 table

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T1 (
  2    COL1  NUMBER,
  3    COL2  VARCHAR2(1 BYTE)
  4  ) segment creation immediate TABLESPACE USERS;

Table created.

As sysdba I grant now RESOURCE role to MARCOV user

SQL> connect / as sysdba
Connected.
SQL> GRANT "RESOURCE" TO MARCOV;

Grant succeeded.

RESOURCE role is now granted to MARCOV...

SQL>  Select drp.granted_role, drp.admin_option, drp.default_role, drp.grantee
  2  FROM dba_role_privs drp
  3  WHERE drp.grantee = 'MARCOV'
  4  OR drp.grantee = 'PUBLIC';

GRANTED_ROLE                   ADM DEF GRANTEE
------------------------------ --- --- ------------------------------
RESOURCE                       NO  YES MARCOV

... and that role is formed by the following privileges...

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2    FROM dba_sys_privs dsp
  3   WHERE dsp.grantee = 'RESOURCE';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE TRIGGER                           NO  RESOURCE
CREATE SEQUENCE                          NO  RESOURCE
CREATE TYPE                              NO  RESOURCE
CREATE PROCEDURE                         NO  RESOURCE
CREATE CLUSTER                           NO  RESOURCE
CREATE OPERATOR                          NO  RESOURCE
CREATE INDEXTYPE                         NO  RESOURCE
CREATE TABLE                             NO  RESOURCE

8 rows selected.

Granting RESOURCE role to a user means automatically to add also UNLIMITED TABLESPACE privilege.

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2    FROM dba_sys_privs dsp
  3   WHERE dsp.grantee = 'MARCOV';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
UNLIMITED TABLESPACE                     NO  MARCOV
CREATE SESSION                           NO  MARCOV
CREATE TABLE     NO  MARCOV

After the creation of just one table (using "segment creation immediate" to force Oracle to allocate an extent without waiting to insert the first record) this is the situation of MARCOV's quota:

SQL>  SELECT USERNAME,
  2    tablespace_name, 
  3    MAX_BYTES/1024/1024 MAXMBYTE,
  4    BYTES, 
  5    DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6   from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         65536  .13

Let's see what it happens when I revoke the RESOURCE role:

SQL> REVOKE "RESOURCE" FROM MARCOV;

Revoke succeeded.

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T2 (
  2    COL1  NUMBER,
  3    COL2  VARCHAR2(1 BYTE)
  4  )
  5  TABLESPACE USERS;

Table created.

When Oracle try to allocate an extent for T2 (I didn't use the "segment creation immediate" option) I receive the "ORA-01536: space quota exceeded for tablespace 'USERS'" error. Why ?

SQL> insert into T2 values (1, 'A');
insert into T2 values (1, 'A')
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Same behaviour if I try to create a T3 table allocating just one extent. Again... why ?

SQL> create table t3 (a number) segment creation immediate tablespace users;
create table t3 (a number) segment creation immediate tablespace users
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

As you can see the RESOURCE role is revoked as well as the UNLIMITED TABLESPACE (as expected).

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee  FROM dba_sys_privs dsp
  2  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE                                ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION                           NO  MARCOV
CREATE TABLE                             NO  MARCOV

SQL> Select drp.granted_role, drp.admin_option, drp.default_role, drp.grantee
  2  FROM dba_role_privs drp
  3  WHERE drp.grantee = 'MARCOV'
  4  OR drp.grantee = 'PUBLIC';

no rows selected

Here is the answer. When you revoke RESOURCE role, all quotas assigned to the user are reset!!!

SQL> connect / as sysdba
Connected.
SQL>  SELECT USERNAME,
  2    tablespace_name, 
  3    MAX_BYTES/1024/1024 MAXMBYTE,
  4    BYTES, 
  5    DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6   from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

no rows selected

This behaviour is different compared with Oracle Database Server 10gR2. Let's see it with an example.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Creation of MARCOV user with only two privileges.

SQL> CREATE USER MARCOV
  2  IDENTIFIED BY "MARCOV"
  3  DEFAULT TABLESPACE USERS
  4  TEMPORARY TABLESPACE TEMP01
  5  PROFILE DEFAULT
  6  ACCOUNT UNLOCK;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO MARCOV;

Grant succeeded.

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2  FROM dba_sys_privs dsp
  3  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION     NO  MARCOV
CREATE TABLE     NO  MARCOV

Assigning some quota on USERS tablespace to MARCOV.

SQL> ALTER USER MARCOV QUOTA 50M ON "USERS";

User altered.

Quota is assigned, but still any byte is used.

SQL> SELECT USERNAME,
  2  tablespace_name, 
  3  MAX_BYTES/1024/1024 MAXMBYTE,
  4  BYTES, 
  5  DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  6  from   sys.dba_ts_quotas
  7  WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         0      0

MARCOV user is able to connect and create T1 table on USERS tablespace:

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T1 (
  2  COL1  NUMBER,
  3  COL2  VARCHAR2(1 BYTE)
  4  ) TABLESPACE USERS;

Table created.

Let's see the privileges currently assigned to MARCOV user:

SQL> connect / as sysdba
Connected.
SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee FROM dba_sys_privs dsp
  2  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE SESSION     NO  MARCOV
CREATE TABLE     NO  MARCOV

Grant RESOURCE role to the user:

SQL> GRANT "RESOURCE" TO MARCOV;

Grant succeeded.

You can verify RESOURCE role is granted with the following query:

SQL> Select drp.granted_role, drp.admin_option, drp.default_role, drp.grantee
  2  FROM dba_role_privs drp
  3  WHERE drp.grantee = 'MARCOV'
  4  OR drp.grantee = 'PUBLIC';

GRANTED_ROLE         ADM DEF GRANTEE
------------------------------ --- --- ------------------------------
RESOURCE         NO  YES MARCOV

RESOURCE role is always formed by 8 privileges:

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee
  2  FROM dba_sys_privs dsp
  3  WHERE dsp.grantee = 'RESOURCE';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE TRIGGER     NO  RESOURCE
CREATE SEQUENCE     NO  RESOURCE
CREATE TYPE     NO  RESOURCE
CREATE PROCEDURE    NO  RESOURCE
CREATE CLUSTER     NO  RESOURCE
CREATE OPERATOR     NO  RESOURCE
CREATE INDEXTYPE    NO  RESOURCE
CREATE TABLE     NO  RESOURCE

8 rows selected.

Also in this case UNLIMITED TABLESPACE is generously granted to MARCOV user:

SQL> Select  dsp.privilege, dsp.admin_option, dsp.grantee FROM dba_sys_privs dsp  WHERE dsp.grantee = 'MARCOV';

PRIVILEGE     ADM GRANTEE
---------------------------------------- --- ------------------------------
UNLIMITED TABLESPACE    NO  MARCOV
CREATE SESSION     NO  MARCOV
CREATE TABLE     NO  MARCOV

MARCOV user is consuming some space after the creation of T1 table.

SQL> SELECT USERNAME, tablespace_name, MAX_BYTES/1024/1024 MAXMBYTE,
  2  BYTES, DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used
  3  from   sys.dba_ts_quotas WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         65536  .13

What does it happen on 10gR2 if I revoke RESOURCE role ?

SQL> REVOKE "RESOURCE" FROM MARCOV;

Revoke succeeded.

The user is always able to create its table...

SQL> connect marcov/marcov
Connected.
SQL> CREATE TABLE MARCOV.T2 ( COL1  NUMBER,COL2  VARCHAR2(1 BYTE)) TABLESPACE USERS;

Table created.

... and quota previously assigned are not revoked.

SQL> connect / as sysdba
Connected.
SQL> SELECT USERNAME, tablespace_name, MAX_BYTES/1024/1024 MAXMBYTE, BYTES, DECODE(MAX_BYTES, -1, 0, 0, 0, ROUND((BYTES/MAX_BYTES)*100, 2)) percent_used   from   sys.dba_ts_quotas WHERE username = 'MARCOV';

USERNAME  TABLESPACE_NAME  MAXMBYTE   BYTES  PERCENT_USED
--------- ---------------- ---------- ------ ------------
MARCOV    USERS            50         131072 .25

So this is a big difference: in Oracle 11gR2 revoking RESOURCE role means Oracle will revoke all quotas previously assigned to the user and this doesn't happen in 10gR2.
So under Oracle 11gR2 you have to remember to give the quotas back after the revoke of the RESOURCE role.

That's all.