SYSAUX datafile or tablespace corrupted or lost no backup available

Datafiles of the sysaux tablespace are not as critical as datafiles in the system tablespace but they are part of the Oracle configuration. Anyway you should never delete any datafile directly from the OS.
rman target /

report schema; -- identify number of your datafile
sql 'alter database datafile ? offline';
sql 'alter database open';


You should be able to open the database but you could have problems (depends on Oracle options you use)... If you don't want to experience/solve them export what you need and import in another database.

you can create a tablespace then :

alter user ??? default tablespace ???;


You should also give him quota on this tablespace

alter user ??? quota unlimited on ???;

Replace ??? by adequate values (username , tablespace name)

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             197132992 bytes
Database Buffers          243269632 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             201327296 bytes
Database Buffers          239075328 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/jay/sysaux01.dbf'

[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 2 11:45:07 2010

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

connected to target database: JAY (DBID=3148849783, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /u01/app/oracle/oradata/jay/system01.dbf
2    25       UNDOTBS1             ***     /u01/app/oracle/oradata/jay/undotbs01.dbf
3    0        SYSAUX               ***     /u01/app/oracle/oradata/jay/sysaux01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/jay/users01.dbf
5    5        SAMPLE               ***     /u01/app/oracle/oradata/jay/sam01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    5        TEMP                 5           /u01/app/oracle/oradata/jay/newtemp1.dbf

RMAN> sql 'alter database datafile 3 offline';

sql statement: alter database datafile 3 offline

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>



SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
jay              OPEN

SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/jay/system01.dbf           SYSTEM
/u01/app/oracle/oradata/jay/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/jay/sysaux01.dbf           OFFLINE
/u01/app/oracle/oradata/jay/users01.dbf            ONLINE
/u01/app/oracle/oradata/jay/sam01.dbf              ONLINE


SQL> select username from dba_users
  2  where default_tablespace='SYSAUX';

USERNAME
------------------------------
SYSMAN
DBSNMP
OLAPSYS
SI_INFORMTN_SCHEMA
ORDPLUGINS
XDB
WMSYS
DMSYS
EXFSYS
ANONYMOUS
CTXSYS

USERNAME
------------------------------
ORDSYS
MDSYS
RAJESH

14 rows selected.


SQL> ALTER USER RAJESH DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

User altered.

reference: from otn