Home » RDBMS Server » Backup & Recovery » sysaux datafile accidentally removed using rm command
sysaux datafile accidentally removed using rm command [message #277623] Tue, 30 October 2007 21:39 Go to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
10.2 Oracle database on Linux

Someone(a non DBA) removed the only sysaux datafile from the server using the rm command Smile

Obviously, the DB is still up and none of the schemas in the database have their objects right now.

This is not a critical database as you can see when I say that I have NO backup whatsoever. Even though I have the option of deleting and recreating the database, i want to know if I can avoid doing that.

? Creating a file on the server with the same name OR creating a new datafile for the tablespace OR recreate the sysaux tablespace ?
But how to do any of these? What else should be done ?

Can someone please give me a lead on this? Appreciate it.
Re: sysaux datafile accidentally removed using rm command [message #277624 is a reply to message #277623] Tue, 30 October 2007 22:31 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Sysaux Tablespace contains some of these objects and due to unavailability these objects stop working Wink

As you know we can take sysaux tablespace offline but occupants of sysaux wouldn't work.
execute it
==========
select occupant_name from v$sysaux_occupants
;
Re: sysaux datafile accidentally removed using rm command [message #277629 is a reply to message #277624] Tue, 30 October 2007 23:23 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Thanks for the response.

I am not using OEM or any of those good stuff. And I know what objects I am missing and I can exp/imp all those objects I miss because of this datafile loss.

My only concern/objective now is to have the datafile for sysaux TS. Bacause, a shutdown/startup of the DB will have issues because of the missing datafile.

I read a bunch of whitepapers and documentations and this is what I understand. These MAY be the possible options.
1. Creating a file on the server with the same name/size as the deleted file and doing a database restore.
2. Creating a new datafile for the tablespace and removing all reference to the deleted datafile from control/parameter file. The new datafile will be the datafile for the tablespace.
3. Recreate the tablespace. The database should be in 'startup migrate' mode to do this.
4. Delete and recreate the database

First 3 options, from what I read so far, is definitely possible on any other user tablespaces. But I don't see any useful documentation to confirm that the same can be done for the SYSAUX tablespace too. If any one of these are possible I can avoid going with the 4th option.

So, of all the options I see , which is best/possible/doable and how to actually do it?


Re: sysaux datafile accidentally removed using rm command [message #277661 is a reply to message #277623] Wed, 31 October 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Export all schemas, recreate the database and reimport your schemas.

Regards
Michel
Re: sysaux datafile accidentally removed using rm command [message #277664 is a reply to message #277661] Wed, 31 October 2007 01:56 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Thanks Michel.

I thought I will have this as the last option if I cannot "(re)create" a datafile for this tablespace.

To be more clear, I have to anyways exp/imp the schemas from a different database. So, if I can fix this sysaux TS issue and exp/imp the schemas, I thought I don't have to recreate the entire database.

Thoughts?

[Updated on: Wed, 31 October 2007 01:56]

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #277669 is a reply to message #277664] Wed, 31 October 2007 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To be able to "fix" you need a backup and you said you have none.

Regards
Michel
Re: sysaux datafile accidentally removed using rm command [message #277722 is a reply to message #277669] Wed, 31 October 2007 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can try the following but it is far from being safe.

- Shutdown the database (make a backup)
- startup migrate
- drop sysaux tablespace
- recreate sysaux tablespace
(CREATE TABLESPACE SYSAUX DATAFILE '...' SIZE xxxM
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; )
- reexecute catalog and all optional scripts you used at database creation
- shutdown immediate
- startup

Regards
Michel

Re: sysaux datafile accidentally removed using rm command [message #277790 is a reply to message #277722] Wed, 31 October 2007 10:18 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Thanks Michel.

I will try this out today. Will post the results. Appreciate it.
Re: sysaux datafile accidentally removed using rm command [message #278095 is a reply to message #277669] Thu, 01 November 2007 15:37 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Michel:

Let me correct my last update...

What you mentioned was one of the options I listed in one of my post here. (recreating the TS). But the reason for my post here is to know if there is any way to just create another datafile for this TS and make the DB use that, which obviously means removing references/traces of the deleted file from the database.

or may be something like, creating a file (in OS level) a file with the same file name as the deleted file first and recover.

I am not sure if what I said made sense (coz I have a vague idea of what I am talking but not 100%).

What I am trying to do is to see if I can avoid the situation of shutting down the DB and ending up not being able to bring it up at all. Then I have to go with recreating the database.

If you feel this is the best option I got, then I will try this out right away.
Re: sysaux datafile accidentally removed using rm command [message #278099 is a reply to message #278095] Thu, 01 November 2007 15:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't remove the reference to the previous file if there were objects in it. You have to recover it first which requires you are in archive log mode and have all the archived logs since the file creation.

Regards
Michel

[Updated on: Fri, 02 November 2007 02:11]

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #278111 is a reply to message #278099] Thu, 01 November 2007 18:38 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
1.
I see that theorotically, these are the occupants of sysaux TS:

Occupant Original Tablespace
Text, Ultra search DRSYS
Intermedia, Spatial SYSTEM
OLAP CWMLITE
Workspace Manager SYSTEM
Data Mining ODM
Recovery Catalog TOOLS
EM Repository OEM_REPOSITORY

I am not using any of these. So, if I make it offline, I should still be just fine. (You mentioned about taking it offline too earlier).

But one question. What are the other cons of having this TS offline, other than missing these functionalities?
And also, does the non system object's metadata go to the SYSTEM tablespace?

2.
And one more question. (this may be basic...)
Now that I lost all the (metadata of ) all tables in the schemas, what happened to the space in the data tablespace that had all those million rows.
I am thinking I just lost the metadata because that is what sysaux TS holds and my data TS which is the default tablespace of all these users is still intact.

3. I saw this ML note 301186.1
Will I be able to do what this note says when I dont even have the datafile in the OS level?

[Updated on: Thu, 01 November 2007 18:58]

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #278155 is a reply to message #278111] Fri, 02 November 2007 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm lost with your questions.

SYSAUX is mandatory and must be there and not corrupted. The fact that your database still work does not mean it is in a good state. Some SYS components use SYSAUX.
You don't lost metadata, metadata are in SYS tables in SYSTEM tablespace, no relation with SYSAUX.

I don't know in which state you are exactly, so can't help you more by mail. Now you have to try the different options yourself.

Regards
Michel
Re: sysaux datafile accidentally removed using rm command [message #278252 is a reply to message #278155] Fri, 02 November 2007 10:14 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Thanks Michel. Apologize if I confused you in any way.

This is what I thought:
The SYSAUX tablespace provides storage of non-sys-related tables and indexes that traditionally were placed in the SYSTEM tablespace.

This is the exact scenario that happened in my database:
1) All schemas are fine and intact in the database
2) sysaux datafile removed at OS level
3) All schema objects missing. Just the users are present and none of their tables are there now.

About the database environment:
No archivelog mode
No backup
Re: sysaux datafile accidentally removed using rm command [message #278257 is a reply to message #278252] Fri, 02 November 2007 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The SYSAUX tablespace provides storage of non-sys-related tables and indexes

some SYS components are in SYSAUX. Here's mine:
Occupant                                             Space used Schema    
---------------------------------------------------- ---------- ----------
Enterprise Manager Repository                            52,800 SYSMAN    
Server Manageability - Automatic Workload Repository     45,632 SYS       
XDB                                                      39,488 XDB       
Oracle Spatial                                           22,272 MDSYS     
Server Manageability - Other Components                   8,192 SYS       
Server Manageability - Advisor Framework                  8,000 SYS       
Workspace Manager                                         7,040 WMSYS     
Server Manageability - Optimizer Statistics History       6,720 SYS       
LogMiner                                                  6,080 SYSTEM    
Expression Filter System                                  3,712 EXFSYS    
Enterprise Manager Monitoring User                        1,600 DBSNMP    
Logical Standby                                             896 SYSTEM    
Analytical Workspace Object Table                           768 SYS       
OLAP API History Tables                                     768 SYS       
Oracle Streams                                              512 SYS       
Oracle interMedia ORDSYS Components                         512 ORDSYS    
Oracle Data Mining                                          384 DMSYS     
Unified Job Scheduler                                       384 SYS       
Oracle Transparent Session Migration User                   256 TSMSYS    


Given what you said, sooner or later you'll encounter a (big) problem. It is better to handle the situation before it happens.

First, did you shutdown the database since file drop? If no, above all, don't shut it down.

Regards
Michel

Re: sysaux datafile accidentally removed using rm command [message #278265 is a reply to message #278257] Fri, 02 November 2007 10:55 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
No, I did not shutdown the database. And that is what I am trying to avoid.
The information you gave about recreating the tablespace demands a shutdown abort and a startup migrate and i was trying to find out if there is a different "solution".

Like in Metalink note 301186.1 ....
(But I read some forum posts about this note and people said that it cannot be done for sysaux (?) ). So, I wanted to see if you can guide me through it

Re: sysaux datafile accidentally removed using rm command [message #278267 is a reply to message #278265] Fri, 02 November 2007 10:58 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
May be of no use in this case, but stumbled across this. Thought I will share it.

[Remove by MC due to copyrighted content, don't post Metalink material, this is a violation of your license agreement.]

[Updated on: Fri, 02 November 2007 11:11] by Moderator

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #278271 is a reply to message #278265] Fri, 02 November 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

No, I did not shutdown the database. And that is what I am trying to avoid.

This is the reason why you still don't have problem.
Actually the file is NOT removed until Oracle close the database. So for the database and Oracle it is still there.

You can use a shutdown immediate, you don't need a shutdown abort. But as soon as you'll want to restart a restore/recovery will be asked that you can't do as you are in noarchivle log mode and have no backup.
This is why I tried to find a workaround.

Metalink note 301186.1 is irrelevant, it is just for SYSAUX relocation and not recovery.

Regards
Michel

[Edit: missing word]

[Updated on: Fri, 02 November 2007 12:55]

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #278284 is a reply to message #278271] Fri, 02 November 2007 12:46 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
I am sorry Michel. I may have missed something you said earlier.

I see that you suggested doing this:
Shutdown the database (make a backup)
- startup migrate
- drop sysaux tablespace
- recreate sysaux tablespace
(CREATE TABLESPACE SYSAUX DATAFILE '...' SIZE xxxM
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; )
- reexecute catalog and all optional scripts you used at database creation
- shutdown immediate
- startup

Did I miss any workaround you said? That is what I am looking for too. Thanks again
Re: sysaux datafile accidentally removed using rm command [message #278412 is a reply to message #278284] Sat, 03 November 2007 23:09 Go to previous messageGo to next message
jzdzg
Messages: 4
Registered: March 2005
Junior Member
according to your discription, you don't have any user data in the sysaux tablespace. you can export those needed user data within other schemas resided in other tablespaces using exp schema=xxx first. then it is safe for to test anyting you like.
Smile
Re: sysaux datafile accidentally removed using rm command [message #278420 is a reply to message #278412] Sun, 04 November 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does it about the same of one of my answer:
Michel Cadot wrote on Wed, 31 October 2007 07:49

Export all schemas, recreate the database and reimport your schemas.

Regards
Michel

Re: sysaux datafile accidentally removed using rm command [message #278685 is a reply to message #278420] Mon, 05 November 2007 07:03 Go to previous messageGo to next message
jzdzg
Messages: 4
Registered: March 2005
Junior Member
since the datafile of sysaux tablespace has been removed , so you can not export those schema using the sysaux tablespace such as SYSMAN, MDSYS, WMSYS, DBSNMP etc. and it is also unnecessary .

I mean you only need to export your OWN schemas . not ALL schemas.

Regards,
Re: sysaux datafile accidentally removed using rm command [message #278704 is a reply to message #278685] Mon, 05 November 2007 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course I meant that and everyone in this topic understood it as it since now.

Regards
Michel
icon6.gif  Re: sysaux datafile accidentally removed using rm command [message #278714 is a reply to message #277623] Mon, 05 November 2007 08:02 Go to previous messageGo to next message
jzdzg
Messages: 4
Registered: March 2005
Junior Member
ok, that is fine.
Re: sysaux datafile accidentally removed using rm command [message #278825 is a reply to message #278714] Mon, 05 November 2007 16:06 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Thanks everyone. But, I think I am totally lost here.

I don't care about any of the user/schema data/objects here. The only thing I am looking for is to find a way to get a datafile for sysaux datafile if possible.

Once my sysaux TS has a datafile, I will do an exp/imp of ALL users from a totally different database. I am covered there.
Re: sysaux datafile accidentally removed using rm command [message #278848 is a reply to message #278825] Mon, 05 November 2007 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was a reference to my very first answer and nothing you have to care about. Say it is a discussion apart on another point. Don't bother and do as we said.

Regards
Michel
Re: sysaux datafile accidentally removed using rm command [message #281868 is a reply to message #278848] Mon, 19 November 2007 13:44 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Michel:

I see that you listed the steps to create the sysaux tablespace which requires a shutdown. But in the next post, you mentioned that I should NOT shutdown the database. SO, I was a little confused. Thats why I asked you the question again...

Anyways, I started doing the steps you mentioned:
Shutdown the database (make a backup)
- startup migrate
- drop sysaux tablespace
- recreate sysaux tablespace
(CREATE TABLESPACE SYSAUX DATAFILE '...' SIZE xxxM
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; )
- reexecute catalog and all optional scripts you used at database creation
- shutdown immediate
- startup


Please see the outcome:
SQL> Shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/opt/oracle/oradata/MYDB/mydb_sysaux1.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> select status from v$instance;

STATUS
------------------------------------
OPEN

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup migrate
ORACLE instance started.

Total System Global Area 188743680 bytes
Fixed Size 1977336 bytes
Variable Size 163582984 bytes
Database Buffers 16777216 bytes
Redo Buffers 6406144 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/MYDB/mydb_sysaux1.dbf'

SQL>
SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

Anyways, just to see output, I tried:
SQL> drop tablespace SYSAUX including contents and datafiles;
drop tablespace SYSAUX including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open


The message says "database not open" to drop sysaux TS???

Comments?
Re: sysaux datafile accidentally removed using rm command [message #281872 is a reply to message #281868] Mon, 19 November 2007 14:51 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Take the tablespace Off line and then try again.
Re: sysaux datafile accidentally removed using rm command [message #281873 is a reply to message #281872] Mon, 19 November 2007 15:08 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01109: database not open
Re: sysaux datafile accidentally removed using rm command [message #281875 is a reply to message #281872] Mon, 19 November 2007 16:02 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Is recreating controlfile (after removing the reference to sysaux datafile) an option by any chance? If yes, what/how should I do it?

I did a 'backup controlfile to trace' and for my case I should be using NORESETLOGS NOARCHIVELOG .
If I recreate controlfile and open the database, will I be able to drop and recreate sysaux TS. Will this work?
Re: sysaux datafile accidentally removed using rm command [message #281879 is a reply to message #281875] Mon, 19 November 2007 17:09 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
OK. I realized that I don't have to re-create controlfile and go that path.

This is where I am right now: (much better position now) Smile
SQL> alter database datafile '/opt/oracle/oradata/MYDB/mydb_sysaux1.dbf' offline drop;
Database altered.

SQL>alter database open;
Database altered.

SQL> select status from v$instance;
STATUS
------
OPEN

SQL> drop tablespace SYSAUX including contents and datafiles;
drop tablespace SYSAUX including contents and datafiles
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup migrate
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 1977336 bytes
Variable Size 159388680 bytes
Database Buffers 20971520 bytes
Redo Buffers 6406144 bytes
Database mounted.
Database opened.

SQL>select status from v$instance;
STATUS
------------------------------------
OPEN MIGRATE

SQL> drop tablespace SYSAUX including contents and datafiles;
drop tablespace SYSAUX including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

SQL> select distinct(index_type) from dba_indexes where tablespace_name='SYSAUX';
INDEX_TYPE
---------------------
NORMAL
FUNCTION-BASED NORMAL
IOT - TOP
LOB

SQL> select index_name, table_owner, table_name from dba_indexes where tablespace_name='SYSAUX' and index_type like 'IOT%';
67 rows selected.


How do I deal with ORA-29857 now. I understand that I have to drop all those domain indexes and/or secondary objects that exist in the tablespace, but how? There are 67 IOT-TOP indexes in the TS.

read some forum which suggested that the user owning these indexes be dropped first and then to drop the TS. The user that was dropped in that post was XDB.
But in my case,
SQL> select distinct(table_owner) from dba_indexes where tablespace_name='SYSAUX' and index_type like 'IOT%';
TABLE_OWNER
------------------------------------------------------------------------------------------
XDB
SYS
SYSMAN
EXFSYS
WMSYS
DBSNMP
6 rows selected.

Suggestions?

[Updated on: Mon, 19 November 2007 17:15]

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #281880 is a reply to message #277623] Mon, 19 November 2007 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 22513
Registered: January 2009
Senior Member
29857, 00000, "domain indexes and/or secondary objects exist in the tablespace"
// *Cause: An attempt was made to drop a tablespace which contains secondary
//         objects and/or domain indexes.
// *Action: Drop the domain indexes in his tablespace. Also, find the domain
//          indexes which created secondary objects in this tablespace and
//          drop them. Then try dropping the tablespace.

Re: sysaux datafile accidentally removed using rm command [message #281882 is a reply to message #281880] Mon, 19 November 2007 17:16 Go to previous messageGo to next message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
Thanks for the reply.
I saw this error description everywhere. But I need some lead on doing this.

I updated my previous post, please check.

[Updated on: Mon, 19 November 2007 17:17]

Report message to a moderator

Re: sysaux datafile accidentally removed using rm command [message #443842 is a reply to message #277623] Wed, 17 February 2010 10:48 Go to previous messageGo to next message
cyberscape
Messages: 4
Registered: December 2009
Location: Lafayette, Louisiana
Junior Member
It's amazing how this issue still applies today. Surprised

I have this exact same problem and I need to see a conclusion to this story!

I'm using Oracle 10g and what should be our SYSAUX datafile comes up instead as:

NAME
--------------------------------------------------------------------------------
STATUS
------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00011
RECOVER

When trying to clone the database, it demands recovery and it wants a log file that is months old and is no longer in our storage. SYSAUX is in a state of RECOVER

I tried using a NETWORK_LINK Oracle Data Pump method with DATA_ONLY=Y to copy our data to a fresh instance of our schema architecture, but certain LONG data could not be transferred in that way so I resorted to trying to create a dump file (.dmp) with the Data Pump. This would not work because package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors.

It's ORA-04063 followed by ORA-06508

And according to this one website, you can't do an export without an intact SYSAUX:
sixsistic.wordpress.com/2008/02/27/oracle-10g-and-sysaux/

"After reviewing many posts and then finally opening up a TAR with oracle, we realized that the only way to fix this issue is to recreate the database. Export the database and import it to the new instance. The only problem is that you can't even export because it uses the sysaux tablespace. Well that sucks too man. So finally we ended up exporting each of the tablespaces indivudually and importing them in to the new instance and trashed the old one."

Exporting tablespaces 'individually' ???
If Data Pump isn't working, how is one supposed to accomplish that, and is this really a solution? Confused
Re: sysaux datafile accidentally removed using rm command [message #443845 is a reply to message #443842] Wed, 17 February 2010 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 22513
Registered: January 2009
Senior Member
  1  select owner , count(*)
  2  from dba_segments
  3  where tablespace_name = 'SYSAUX'
  4  group by owner
  5* ORDER BY 1
SQL> /

OWNER				 COUNT(*)
------------------------------ ----------
CTXSYS				       74
DBSNMP				       25
DMSYS					4
EXFSYS				       58
MDSYS				      125
OLAPSYS 			      249
ORDSYS					8
SYS				      698
SYSMAN				      760
SYSTEM				      109
TSMSYS					4
WMSYS				      110
XDB				      753

13 rows selected.


I am not sure exactly how well this would work out, but a possible solution would be to

DROP TABLESPACE SYSAUX INCLUDING CONTENTS AND DATAFILES;

With free advice, you get what you paid for it.
I suggest you have a good cold backup before attempting this.
Re: sysaux datafile accidentally removed using rm command [message #444251 is a reply to message #443845] Fri, 19 February 2010 11:23 Go to previous messageGo to next message
cyberscape
Messages: 4
Registered: December 2009
Location: Lafayette, Louisiana
Junior Member
anacedent wrote on Mon, 19 November 2007 17:10
29857, 00000, "domain indexes and/or secondary objects exist in the tablespace"
// *Cause: An attempt was made to drop a tablespace which contains secondary
//         objects and/or domain indexes.
// *Action: Drop the domain indexes in his tablespace. Also, find the domain
//          indexes which created secondary objects in this tablespace and
//          drop them. Then try dropping the tablespace.




oracle_scorpiongirl wrote on Mon, 19 November 2007 17:16
Thanks for the reply.
I saw this error description everywhere. But I need some lead on doing this.

I updated my previous post, please check.


Thanks and welcome, Black Swan.

I'm having the same problem as oracle_scorpiongirl

Trying to drop SYSAUX in Migrate mode is like trying to pull a tree out of the ground with your bare hands and trying to avoid breaking any water lines that might be threaded through the roots of the tree in the ground. This is not something I think a junior DBA should have to deal with, but a junior DBA is all my workplace has. If anyone knows anything more I'd be extremely appreciative to hear it.
Re: sysaux datafile accidentally removed using rm command [message #444252 is a reply to message #444251] Fri, 19 February 2010 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 22513
Registered: January 2009
Senior Member
SELECT   de1.owner,
         de2.segment_name,
         Max(de1.block_id)
FROM     dba_extents de1,
         dba_extents de2
WHERE    de1.block_id > de2.block_id
         AND de1.owner = de2.owner
         AND de1.segment_name = de2.segment_name
         AND de1.tablespace_name = 'SYSAUX'
GROUP BY de1.owner,
         de2.segment_name
ORDER BY 3 


>Trying to drop SYSAUX in Migrate mode

I do not understand or recognize what is meant by "Migrate mode".
Re: sysaux datafile accidentally removed using rm command [message #444256 is a reply to message #444252] Fri, 19 February 2010 13:07 Go to previous messageGo to next message
cyberscape
Messages: 4
Registered: December 2009
Location: Lafayette, Louisiana
Junior Member
According to this website:

stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_9004.htm

"You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode."

I'm a bit fuzzy on segments and indexes, so I'm going to review the pl/sql you posted and get back to you on that.
Re: sysaux datafile accidentally removed using rm command [message #570459 is a reply to message #277623] Fri, 09 November 2012 23:29 Go to previous messageGo to next message
mrdba
Messages: 1
Registered: November 2012
Junior Member
Hi All,

I am also looking for a definite resolution for a corrupted SYSAUX Tablespaces currently offline.

Sysaux tablespace of one smallsize production database(10.2.0.5) is in offline mode since three months.

1)Storage team confirmed that all archives since SYSAUX TB went offline, are not present so recovery is not possible.
2)EXP ,EXPDP are not working since SYSAUX is offline.
3)Cannot RECREATE/DROP SYSAUX as recommended in oracle notes.

What options we are left with to save data?


Early recommendation would be welcomed.
Tonns of thanks in advance.
Re: sysaux datafile accidentally removed using rm command [message #570476 is a reply to message #570459] Sat, 10 November 2012 11:10 Go to previous message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Export user schemas, recreate the database, import.

Regards
Michel
Previous Topic: dbms_snapshot.refresh
Next Topic: Recovery from 11.2.0.3 PSU2 RAC to non-RAC
Goto Forum:
  


Current Time: Mon Jul 28 10:20:32 CDT 2014

Total time taken to generate the page: 0.05696 seconds