Bookmark Fixed font Go to End
Doc ID: Note:115424.1
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 18-JUL-2000
Last Revision Date: 27-APR-2001
PURPOSE
In many situations, a datafile or logfile must be renamed inside Oracle.
Whereas the contents of the file remain valid, you need to define a new
physical name or location for it. For example:
- You want to move a database file to a different disk for performance or
maintenance reasons.
- You have restored a datafile from backup, but the disk where it should
normally be placed has crashed and you need to relocate it to a different
disk.
- You have moved or renamed a datafile at operating system level but forgot
to
rename it within Oracle. At startup, you get ORA-01157 and ORA-01110. If
the
database is up and you try to shut it down normal or immediate, you get
ORA-01116 ad ORA-01110.
- You have multiple databases on the same machine and you need to rename
certain database files to prevent collision and confusion.
SCOPE & APPLICATION
This bulletin gives instructions to:
I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN
II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN
III. RENAME AND OR MOVE A LOGFILE
How to Rename or Move Datafiles and Logfiles:
=============================================
NOTE: To rename or relocate datafiles in the SYSTEM tablespace you must use
option II, 'Renaming or Moving a Datafile with the Database Shut
Down',
because you cannot take the SYSTEM tablespace offline.
I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN
----------------------------------------------------
Datafiles can be renamed or moved while the database is open. However, the
tablespace must be made READ-ONLY. This will allow users to select from the
tablespace, but prevents them from doing inserts, updates, and deletes. The
amount of time the tablespace is required to be read only will depend on how
large the datafile(s) are and how long it takes to copy the datafile(s) to
the
new location.
Making the tablespace read only freezes the file header, preventing updates
from being made to the file header. Since this datafile is then at a read
only state, it is possible to copy the file while the database is open.
To do this you must follow these steps:
1. Determine how many datafiles are associated with the tablespace.
SVRMGR> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '<YOUR_TABLESPACE_NAME>';
2. Make sure that all datafiles returned have the status AVAILABLE.
3. Make the tablespace is read only.
SVRMGR> ALTER TABLESPACE <YOUR_TABLESPACE_NAME> READ ONLY;
4. Make sure that the tablespace is defined as read only in the data
dictionary.
SVRMGR> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '<YOUR_TABLESPACE_NAME>';
TABLESPACE_NAME STATUS
------------------------------ ---------
<YOUR_TABLESPACE_NAME> READ ONLY
5. Copy the datafile(s) to the new location using the operating system copy
command. Once the datafile(s) have been copied to the new location
compare
the sizes of the datafiles. Make sure that the sizes match.
6. Once the datafiles have been copied to the new location alter the
tablespace offline.
SVRMGR> ALTER TABLESPACE <YOUR_TABLESPACE_NAME> OFFLINE;
* At this point the tablespace is not accessible to users.
7. Once the tablespace is offline you will need to rename the datafile(s) to
the new location. This updates the entry for the datafile(s) in the
controlfile.
SVRMGR> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
* You will need to do this for all datafiles associated with
this tablespace.
8. Once the alter database statement has been processed for the datafile(s)
you can bring the tablespace online.
SVRMGR> ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;
9. After you bring the tablespace back online you can make the tablespace
read/write again.
SVRMGR> ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE;
10. You can check the status of the tablespace to make sure it is
read/write.
You can also verify that the controlfile has been updated by doing the
following:
SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This will produce a readable copy of the contents of your controlfile
which will be placed in your user_dump_dest directory.
11. Remove the datafile(s) from the old location at the O/S level.
II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN
--------------------------------------------------------------
1. If the database is up, shut it down.
2. Copy the datafile to the new name/location at operating system level.
3. Mount the database.
SVRMGR> STARTUP MOUNT
This command will read the control file but will not mount the datafiles.
4. Rename the file inside Oracle.
SVRMGR> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
Do this for all the datafiles that were renamed or moved at the operating
system level.
5. Open the database.
SVRMGR> ALTER DATABASE OPEN;
6. Query v$dbfile to confirm that the changes made were correct.
SVRMGR> SELECT * FROM V$DBFILE;
7. Remove the datafile(s) from the old location at the operating system
level.
III. RENAME AND OR MOVE A LOGFILE
----------------------------------
1. Shutdown the database.
2. Copy the logfile to the new name/location at operating system level.
3. Mount the database.
SVRMGR> STARTUP MOUNT
4. Rename the file.
SVRMGR> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG';
5. Open the database.
SVRMGR> ALTER DATABASE OPEN;
6. Remove the logfile(s) from the old location at the operating system
level.
Search Words:
=============
ORA-1157 ORA-1116 ORA-1110
.
----------------------------------------------------------------------------
----
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
Notices and Terms of Use.
-----Original Message-----
LaCascio
Sent: Wednesday, January 30, 2002 5:05 PM
To: Multiple recipients of list ORACLE-L
Okay, I don't have the manuals with me and can't for the life of me
remember the correct syntax to do this...
Is it:
shutdown database
startup no noumt (or startup mount)
alter tablesapce evisions
rename datafile '/full_path/file_name' to '/full_path/new_name';
I'm on 8.1.6 and this complains saying the database isn't open.
It can't be open to rename a datafile...
Thanks,
Joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe LaCascio
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kimberly Smith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
