| Recover sysaux datafile? [message #258890] |
Mon, 13 August 2007 13:44  |
|
|
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  |
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 |
|
|
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 #258940 is a reply to message #258910] |
Mon, 13 August 2007 17:34 |
|
|
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 #436446 is a reply to message #258890] |
Wed, 23 December 2009 11:28 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
