Moving oracle table structure

[acl03]

Is there a way to have a windows service access a mapped network drive?

We have oracle running on c:\. The actual table structure is on d:\.

D:\ is failing, so we want to move it's contents, directory structure in tact, to another server. Then I'd remove d:, and map d: to the remote server. I figured oracle would be none the wiser.

Turns out since oracle is running as a service (and no the currently logged on user who has the new mapped d: drive) it can't see that drive letter.

So - is there any way to have a windows service get a mapped network drive?

PS. I am obviously not an oracle dba. I am a server admin, our DBA is on sick leave.

Thanks,
Andrew





Thanks,
Andrew

 

[SantaMufasa]

Andrew,

It would help us help you better if you can please post the results of this query on the Oracle instance upon which you are focussing:

select file_name from dba_data_files;

The actual table structure is on d:\.

I presume that you mean that you have an important production table that resides inside one of the files that will appear on the results of the query I'm asking you to run, above...correct?

So - is there any way to have a windows service get a mapped network drive?

In short answer to your question, any of the files that appear in the results of the query I'm asking you to run and post, you can re-locate to any drive that the host machine can "see", whether it is a local or a mapped drive. Once you post the results to the query, I can then tell you the steps to re-locate the "D:" files to some other location. Please indicate the fully qualified path to where you want to re-locate the "D:" files.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acl03]

I will try to do that this afternoon. But when you say this:

you can re-locate to any drive that the host machine can "see", whether it is a local or a mapped drive.

If oracle is running as a service...how does it "see" the mapped drive. For example, if nobody is logged into the server.

Could we just use UNCs instead?



Thanks,
Andrew

 

[acl03]

They are on F: and H:, actually - not D:. That was just for my example.

FILE_NAME
------------------------------
H:\ORAONB2\DBONB02\SYSTEM\SYS1ONB2.ORA

F:\DBONB02\ROLLBACK\RB01.RBS
F:\DBONB02\TEMP\OBTMP.DBF
H:\ORAONB2\DBONB02\DATA\OBDEFLT.DBF

H:\ORAONB2\DBONB02\DATA\DB1.DB
H:\ORAONB2\DBONB02\DATA\DB2.DB
H:\ORAONB2\DBONB02\DATA\DB3.DB
H:\ORAONB2\DBONB02\DATA\DB4.DB
H:\ORAONB2\DBONB02\DATA\DB5.DB
H:\ORAONB2\DBONB02\DATA\DB6.DB
H:\ORAONB2\DBONB02\DATA\DB7.DB
H:\ORAONB2\DBONB02\DATA\DB8.DB
H:\ORAONB2\DBONB02\DATA\DB9.DB
H:\ORAONB2\DBONB02\DATA\DB10.DB

F:\DBONB02\INDEX\DB11.DB
F:\DBONB02\INDEX\DB12.DB
F:\DBONB02\INDEX\DB13.DB
F:\DBONB02\INDEX\DB14.DB
H:\ORAONB2\DBONB02\DATA\DB01.DB

F:\DBONB02\INDEX\DB13_01.ORA

20 rows selected.

Thanks,
Andrew

 

[SantaMufasa]

Andrew,

Running the query I gave you will clarify much regarding your situation, and the results will give us a specific framework within which to discuss specifics in your case.

Without the query results for context, there would be a disconnect between your questions and my responses here, so the least confusion will occur if I postpone my responses or suggestions until after you post the results of the query.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acl03]

I posted the query results above



Thanks,
Andrew

 

[SantaMufasa]

Sorry...I didn't refresh before my posting to see that you had posted the results.

So, is it correct that you want everything moved from drives "F:" and "H:" to some other drive? If so, what is the name of the other drive(s)? (Once you post that detail, I can give you specifics about how to move those files.)

BTW, to ensure that we are not missing any other files that you need to move, please also run the following two queries and post their results:

select name from v$controlfile;

select member from v$logfile;

Also, do you know the location of your ORACLE_HOME drive/directory?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acl03]

Well F: and H: are local drives to that server (SAN Attached).

We want to migrate all data from F: and H: as file shares on another server. They can still use the F: and H: drive letters if necessary.

ORACLE_HOME is C:\oracle\ora81

We see drive letters referenced in init.ora. Is that the only place where we need to make changes?


Here are the results of the queries:

sys@ONB2.CEHNT3> select name from v$controlfile;

NAME
------------------------------
H:\ORAONB2\DBONB02\CONTROL\CTL
1ONB2.ORA

F:\DBONB02\CONTROL2\CTL2ONB2.O
RA


sys@ONB2.CEHNT3> select member from v$logfile;

MEMBER
-------------------------------------------------------
---------------------------------------------------
F:\DBONB02\LOGS\LOG1ONB2.ORA
F:\DBONB02\LOGS\LOG2ONB2.ORA
F:\DBONB02\LOGS\LOG3ONB2.ORA
F:\DBONB02\LOGS\LOG4ONB2.ORA
F:\DBONB02\LOGS\LOG5ONB2.ORA
F:\DBONB02\LOGS\LOG6ONB2.ORA

6 rows selected.

Thanks,
Andrew

 

[SantaMufasa]

They can still use the F: and H: drive letters if necessary.

If the "new" drive names can still be "F:" and "H:", then you have enormously simplified the migration process...All you need to do is to [ul][li]Gracefully shutdown Oracle[/li][li]Copy all of the Oracle-related files from the "old" F: and H: drives to the "new" F: and H: drives (using the same directory and sub-directory structures)[/li][li]Re-start Oracle[/li][/ul]Let us know if I have presumed anything that is not correct.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acl03]

well, that we already did - which prompted the post. The problem is that these "mapped network drives" (which are still f: and h cannot be seen by the oracle process.

Since it runs as a windows service (and not as the currently logged on user) it doesnt know about a network drive that I have mapped within windows....

The easiest way i think would be to change the paths to use UNC's instead of drive letters...



Thanks,
Andrew

 

[SantaMufasa]

The easiest way i think would be to change the paths to use UNC's instead of drive letters...

That would certainly be worth a try. Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acl03]

Well - i still have no idea how to do that



Thanks,
Andrew

 

[SantaMufasa]

Okay, let's backtrack just a bit...When you ran the query, above, were drives F: and H: non-local, mapped drives?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acl03]

nope, they are currently local drives to the server.



Thanks,
Andrew