| sysaux datafile accidentally removed using rm command [message #277623] |
Tue, 30 October 2007 21:39  |
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 
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 #277629 is a reply to message #277624] |
Tue, 30 October 2007 23:23  |
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 #278095 is a reply to message #277669] |
Thu, 01 November 2007 15:37 |
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 #278111 is a reply to message #278099] |
Thu, 01 November 2007 18:38 |
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 #278252 is a reply to message #278155] |
Fri, 02 November 2007 10:14 |
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 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #278271 is a reply to message #278265] |
Fri, 02 November 2007 11:09 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #278825 is a reply to message #278714] |
Mon, 05 November 2007 16:06 |
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 #281868 is a reply to message #278848] |
Mon, 19 November 2007 13:44 |
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 #281875 is a reply to message #281872] |
Mon, 19 November 2007 16:02 |
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 |
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)
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 #444251 is a reply to message #443845] |
Fri, 19 February 2010 11:23 |
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:16Thanks 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 #444256 is a reply to message #444252] |
Fri, 19 February 2010 13:07 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
