In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Thursday, September 2, 2010

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

2 comments:

Phil said...

Hi rajeysh,
I recognize a solution I posted on OTN to solve an issue about the loss of a non critical datafile in the sysaux tablespace. To enhance your article it would be interesting to precise a few things.
I mentioned to use "alter user default tablespace ???" but in my head it was to affect a different tablespace from sysaux to "normal" users as a good practice.
After the loss of sysaux doing that for sysman etc. won't solve magically the issues !
Best regards
Phil

RAJESHKUMAR GOVINDARAJAN said...

thanks phil for the update.

regards,
Rajeshkumar Govindarajan.

free counters
 
Share/Bookmark