Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Managing Datafiles

Posted on 2004-09-27
13
Medium Priority
?
651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 10

Expert Comment

by:SDutta
ID: 12163205
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
ID: 12163403
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
ID: 12163420
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 10

Expert Comment

by:SDutta
ID: 12163449
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
ID: 12164109
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
 

Author Comment

by:ptreves
ID: 12166338
Hello,

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

PT
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12166616
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
ID: 12182815
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
ID: 12185061
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
ID: 12185555
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 2000 total points
ID: 12190419
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

670 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