Solved

Managing Datafiles

Posted on 2004-09-27
13
641 Views
Last Modified: 2013-12-03
Hello,

Finishing up my database migration project, I realise that there are extra Datafiles that were defined and some existing datafiles need to be moved to different physical locations.

I tried to invoke the OEM to delete datafiles but I am unable to do so.

1) How can I remove uneeded datafiles from my database ?

PT

PS: I realise that some datafiles where created with reference to wrong tablespaces and now need to be removed.  

Your toughts ?

0
Comment
Question by:ptreves
13 Comments
 
LVL 10

Expert Comment

by:SDutta
Comment Utility
Datafiles cannot be dropped by themselves, the whole tablespace has to be dropped. Drop the affected tablespace and then recreate it with the correct datafiles. Be careful not to drop any data without backing up the database.
0
 

Author Comment

by:ptreves
Comment Utility
Hello,

Can you be more specific ?


PT

PS: I have 2 or 3 datafiles referencing the same tablespace..
In fact, I only need one of the datafiles, the other 2 I should remove.

How can I go about that ?

PS2: I tought that the the Enterprise Manager could do the job ?
0
 

Author Comment

by:ptreves
Comment Utility
Hello,

I guess I should shutdown the database, remove the datafiles that are not needed. Is that the way I should go about it ?

PT
0
 
LVL 10

Expert Comment

by:SDutta
Comment Utility
Hi PT,

If you have 3 datafiles in a tablespace, you CANNOT remove 1 or 2 datafiles from it.
You have to drop the entire tablespace with ALL 3 datafiles.
Then create the tablespace fresh with the required datafile again.

0
 
LVL 12

Expert Comment

by:geotiger
Comment Utility
Here are some details related to your question:

Dropping Datafiles
There is no SQL statement that specifically drops a datafile. The only means of
dropping a datafile is to drop the tablespace that contains the datafile. For example,
if you want to remove a datafile from a tablespace, you could do the following:
1. Create a new tablespace
2. Move the data from the old tablespace to the new one
3. Drop the old tablespace
You can, however, drop a tempfile using the ALTER DATABASE statement. For
example:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
INCLUDING DATAFILES;


To drop a tablespace, use the DROP TABLESPACE statement. The following
statement drops the users tablespace, including the segments in the tablespace:
DROP TABLESPACE users INCLUDING CONTENTS;


Renaming and Relocating Datafiles
You can rename datafiles to either change their names or relocate them. Some
options, and procedures which you can follow, are described in the following
sections:
n Renaming and Relocating Datafiles for a Single Tablespace
For example, renaming filename1 and filename2 in tablespace1, while the rest of
the database is open.
n Renaming and Relocating Datafiles for Multiple Tablespaces
For example, renaming filename1 in tablespace1 and filename2 in tablespace2, while
the database is mounted but closed.
When you rename and relocate datafiles with these procedures, only the pointers to
the datafiles, as recorded in the database’s control file, are changed. The procedures
do not physically rename any operating system files, nor do they copy files at the
operating system level. Renaming and relocating datafiles involves several steps.
Read the steps and examples carefully before performing these procedures.
When you rename and relocate datafiles with these procedures, only the pointers to
datafiles, as recorded in the database’s control file, are changed. The procedures
not physically rename any operating system files, nor do they copy files at the
operating system level. Renaming and relocating datafiles involves several steps.
the steps and examples carefully before performing these procedures.
Note: To rename or relocate datafiles of the SYSTEM tablespace,
you must use the second option, because you cannot take the
SYSTEM tablespace offline.


Renaming and Relocating Datafiles for a Single Tablespace
The section offers some procedures for renaming and relocating datafiles in a single
tablespace. You must have the ALTER TABLESPACE system privilege to rename
datafiles of a single tablespace.
Renaming Datafiles in a Single Tablespace
To rename datafiles from a single tablespace, complete the following steps:
1. Take the non-SYSTEM tablespace that contains the datafiles offline.
For example:
ALTER TABLESPACE users OFFLINE NORMAL;
2. Rename the datafiles using the operating system.
3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to
change the filenames within the database.
For example, the following statement renames the datafiles
/u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf
to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf,
respectively:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
The new files must already exist; this statement does not create the files. Also,
always provide complete filenames (including their paths) to properly identify
the old and new datafiles. In particular, specify the old datafile name exactly as
it appears in the DBA_DATA_FILES view of the data dictionary.
4. Back up the database. After making any structural changes to a database,
always perform an immediate and complete backup.
Relocating and Renaming Datafiles in a Single Tablespace
Here is an example that illustrates the steps involved for relocating a datafile.
Assume the following conditions:
n An open database has a tablespace named users that is made up of datafiles
all located on the same disk.

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:ptreves
Comment Utility
Hello,

When I drop a tablespace, should I reimport the data afterwards ?

PT
0
 
LVL 12

Expert Comment

by:catchmeifuwant
Comment Utility
Before dropping a tablespace, take a backup of the objects you want to reimport

1)Backup the objects using export utility
exp username/password@sid tables=table1,table2,table3...  file=ts_1.dmp

2)Drop the tablespace including the datafiles and contents
drop tablespace <ts_name> including datafiles and contents;

3)Recreate the tablespace with 1 datafile
create tablespace <ts_name> datafile 'location_of_file' size 100m autoextend on

4)Import the tables that you have exported into the new tablespace
imp username/password@sid file=ts_1.dmp full=y


BTW, why do you want to remove a TS just because it has multiple files?Does it afffect you in any way?
0
 

Author Comment

by:ptreves
Comment Utility
Hello,

I can ask you that same question.
I have 3 datafiles referencing the same tablespace, when in fact I only need one. I want to clean thing up.

1) Would keeping all those datafiles effect me in any way down the road ?

PT
0
 

Author Comment

by:ptreves
Comment Utility
Hello,

Ok, I dropped my tablespace and recreated it with the correct datafile names and locations.

Now,I cannot get me tablespace back online.

I get the following errors:

alter tablespace alcieind online;
Error at line 1
ORA-01122: Database file 13 failed verification check
ORA-01110 database '/disk1/oradata/alcieind.ora'
ORA-01250: unknown file header version read for file number 13

1) How can I get my tablespace back online and working normally ?

PT
0
 

Author Comment

by:ptreves
Comment Utility
Hello

1) create tablespaces + users
2) import the export dump file
3) rename the datafiles

When renaming the datafiles, I noticed that their size goes back to 0 + they are no longer online. WHen I try to edit those changes using OEM, I get the following errors:

alter tablespace alcieind online;
Error at line 1
ORA-01122: Database file 11/13 failed verification check
ORA-01110 database '/disk1/oradata/alcieind.ora'
ORA-01250: unknown file header version read for file number 11/13

1) How can I give my new datafiles the size and bring them back online ?

PT


 
 
0
 
LVL 10

Accepted Solution

by:
SDutta earned 500 total points
Comment Utility
While renaming the datafile from OEM it does not change the name of the actual physical file, so it goes offline. You have to then rename the physical file on the OS and then make it online in OEM. It may say that the datafile needs recovery before it can be onlined. Just do : ALTER DATABASE RECOVER DATAFILE 'filename';
It should come up after that.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now