Toad World® Forums

How to move all data files, archive, etc

We set up Oracle on a WIndows 2008R2 server and have a drive that we originally set up for data (D:\Oradata). It contains s directory for our Database with 4 subdirectories(ARCHIVELOG, CONTROLFILE, DATAFILE, ONLINELOG). This takes up about 808G. We now have to move the database to a new E: drive.

I wish to maintain the same database name, tables, indexes, PL/SQL, schemas, etc.

What is the easiest/best way to accomplish this?

I do this at least quarterly, as a restore test. First, while the DB is up, I use Toad (to keep this thread relevant!) to write the statements for me to move the datafiles and log files:

SELECT
‘alter database rename file ‘’’||member||’’’ to ‘’’||REPLACE(member,‘C:/’,‘E:/’) ||’’’;’ “NEWNAME”
FROM v$logfile
ORDER BY GROUP#, member;

I then follow Part II of MOS Doc 115424.1, using the output from the above to tell the DB where the datafiles are (step 4 in Part II).

This does require the DB to be down, but I don’t know that it can be done cleanly any other way. A test of your procedure on another DB is always advisable!

HTH! GL!

Rich

I’m following you until you mention “Part II of MOS Doc…”

What/Where is “MOS Doc 115424.1…”?

support.oracle.com/…/SearchDocDisplay

You may have to log in to my oracle support earlier Metalink and search for 115424.1

and you will find it

Oracle Documentation Library, My Oracle Support, Document ID 115424.1

“How to rename or move datafiles and logfiles”

From: gerald.novak [mailto:bounce-geraldnovak@toadworld.com]

Sent: Thursday, January 23, 2014 2:26 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to move all data files, archive, etc

RE: How to move all data files, archive, etc

Reply by gerald.novak

I’m following you until you mention “Part II of MOS Doc…”

What/Where is “MOS Doc 115424.1…”?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Yes, a login is required:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=115424.1

Let us know if you’re not able to get there. While I probably shouldn’t copy the article verbatim, I can transpose/transcribe the pertinent portion. I use my own similar methods anyway, but thought this link would be easier. Doh! :slight_smile:

Rich

lmgtfy.com