Home » RDBMS Server » Backup & Recovery » Recover sysaux datafile?
Recover sysaux datafile? [message #258890] Mon, 13 August 2007 13:44 Go to next message
krblr
Messages: 24
Registered: April 2007
Location: MD
Junior Member

Hi all,
I don't know how this all happened, but I just noticed something strange, the file_name says it is missing, the database is currently up and running, how do I safely recover this file? I am scared to shutdown fearing that it may not come back up.

Thanks in advance,
krblr

/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00004,


sys@DIGDB> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------------------------------------------------
/san/db22/TOTAL_BACKUP/DATAFILES/o1_mf_brtindex_2282sc2s_.dbf
/san/db22/TOTAL_BACKUP/DATAFILES/o1_mf_brt_2280fs5f_.dbf
/san/db22/TOTAL_BACKUP/DATAFILES/o1_mf_users_20g5dq3c_.dbf
/san/db22/TOTAL_BACKUP/DATAFILES/usesrindex02.dbf
/san/db22/TOTAL_BACKUP/DATAFILES/brt2
/san/db22/TOTAL_BACKUP/DATAFILES/o1_mf_brt2_0omh0m1c_.dbf
/san/db22/TOTAL_BACKUP/DATAFILES/o1_mf_brtindex_0om1xfvf_.dbf
/san/db22/TOTAL_BACKUP/DATAFILES/o1_mf_users_zh3sllc8_2.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/o1_mf_brtindex_zh3sqk7l_.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/o1_mf_usersind_zh3spswp_.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/users_01.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/o1_mf_brt2_zh3sktw2_.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/o1_mf_brt_zh3sgccg_.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/xdb01.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/o1_mf_brt_zh3sbopl_.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/tools01.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/indx01.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/drsys01.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/undotbs01.dbf
/san/db21/TOTAL_BACKUP/DATAFILES/system01.dbf
/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00004
/san/db22/TOTAL_BACKUP/DATAFILES/sysaux_02.dbf

22 rows selected.

sys@DIGDB> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01191: file 4 is already offline - cannot do a normal offline
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: '/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00004'

sys@DIGDB> select FILE#, status from v$datafile where file#=4;

FILE# STATUS
---------- -------
4 RECOVER

--> ls -l
/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00004*: No such file or directory


Re: Recover sysaux datafile? [message #258895 is a reply to message #258890] Mon, 13 August 2007 14:23 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Did you issue REanme command against this file?

post the output for thsi querry.
SQL> ;
  1  select TABLESPACE_NAME,STATUS
  2* from dba_tablespaces
SQL>
Re: Recover sysaux datafile? [message #258902 is a reply to message #258895] Mon, 13 August 2007 14:39 Go to previous messageGo to next message
krblr
Messages: 24
Registered: April 2007
Location: MD
Junior Member

sys@DIGDB> select TABLESPACE_NAME,STATUS
2 from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
DRSYS ONLINE
INDX ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
BRT ONLINE
BRT2 ONLINE
USERSINDEX ONLINE
BRTINDEX ONLINE
SYSAUX ONLINE

13 rows selected.
Re: Recover sysaux datafile? [message #258910 is a reply to message #258902] Mon, 13 August 2007 15:04 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Use
recover tablespace tablespacename
command and then try
alter tablespace tablespacename  online;


http://www.ordba.net/Articles/Backup.htm

[Updated on: Mon, 13 August 2007 15:05]

Report message to a moderator

Re: Recover sysaux datafile? [message #258940 is a reply to message #258910] Mon, 13 August 2007 17:34 Go to previous messageGo to next message
krblr
Messages: 24
Registered: April 2007
Location: MD
Junior Member

Thanks dreamz, I may have done something similar, the problem is resolved, here is what I did

1) Went to the OS level, did a "find / -name "*sysaux*", I saw a file sitting there salled sysaux_something.
2) Then I did an
alter tablespace sysaux rename datafile
'..../missing_data_file_name'
to '.../the_file_I_found...';
3) Then I tried to online sysaux, it refused to do so, because it said it needed recovery
4) Then I did, alter database recover datafile 4;'
5) Then it applied about 2 archive logs to it, and was happy
6) The I did an, 'alter tablespace sysaux online'
No complaints, it all worked out

Thanks for your help.

Re: Recover sysaux datafile? [message #258941 is a reply to message #258940] Mon, 13 August 2007 17:58 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You Welcome and thanks for sharing OUTPUT.!!!!!
Re: Recover sysaux datafile? [message #436446 is a reply to message #258890] Wed, 23 December 2009 11:28 Go to previous message
cyberscape
Messages: 4
Registered: December 2009
Location: Lafayette, Louisiana
Junior Member
I have almost the same situation, but I'm not in archivelog mode.

OS: Win XP, 32bit
Oracle Version: 10g R2

Status of production database: Database is running, server was restarted yesterday and database still boots up, oracle enterprise manager not working. SYSAUX is "missing".

Status of test database: Files fail to work as cold backups due to SYSAUX "missing". Must perform resetlogs option because there's a conflict in sysaux names within header(can't rename database without resetlogs option). Cannot recreate control files without recovery. Recovery doesn't seem to work. Keeps asking for log files 1,2,3 when I only have online redo logs 4,5,6.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
DRSYS ONLINE
MYDATA_DATA ONLINE
MYDATA_INDEX ONLINE
INDX ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
TEMP ONLINE
SYSAUX ONLINE

11 rows selected.

SQL>
SQL> select status, file#, name from v$datafile;

STATUS FILE#
------- ----------
NAME
----
SYSTEM 1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\SYSTEM01.DBF

ONLINE 2
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\UNDOTBS01.DBF

ONLINE 3
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\DRSYS01.DBF


STATUS FILE#
------- ----------
NAME
------
ONLINE 4
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\MYDATA_DATA.DBF

ONLINE 5
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\MYDATA_INDEX.DBF

ONLINE 6
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\INDX01.DBF


STATUS FILE#
------- ----------
NAME
------
ONLINE 7
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\TOOLS01.DBF

ONLINE 8
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\USERS01.DBF

ONLINE 9
C:\ORACLE\PRODUCT\10.2.0\ORADATA\mydbname\XDB01.DBF


STATUS FILE#
------- ----------
NAME
------
RECOVER 10
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00010


10 rows selected.




The "missing" file is SYSAUX.
Previous Topic: Apply archive log in physical standby slow
Next Topic: Moving Oracle DB from one server to another (different versions)
Goto Forum:
  


Current Time: Wed Sep 28 07:23:32 CDT 2016

Total time taken to generate the page: 0.13629 seconds