Re: Attach a database in Oracle 10g

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 10 Dec 2004 12:42:29 +1100
Message-ID: <41b8ff04$0$1080$afc38c87@news.optusnet.com.au>

Greg Doherty wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:41b8d60f$0$1293$afc38c87_at_news.optusnet.com.au...
>

>>Greg Doherty wrote:
>>
>>>Sorry about being vague.
>>>
>>>Here are the specifics:
>>>
>>>The database was originally called asfserver located at
>>>E:\Oracle\oradata\asfserver\
>>>It is a version 8.1 database.
>>>
>>>I would like to set up this database on a new Oracle 10g (ver 10.1)

>
> server
>

>>>at C:\Oracle\product\10.1.0\oradata\asfserver
>>>I have the CTL, DBF and LOG files.
>>>
>>>What actions/commands do I need to perform to mount this database on my
>>>server so that I can access it via SQLPlus, ODBC, etc.
>>>
>>>TIA,
>>>- Greg
>>
>>
>>Right. That puts rather a different perspective on it. There's no easy
>>way of doing it. The steps I outlined before assumed E: was still E: and
>>(rather more importantly) 8i was still 8i.
>>
>>You can't simply attach a 10g instance/service to an 8i database. Not
>>without fireworks, anyway. You'll have to upgrade the thing properly,
>>following all the steps in the Oracle 10g upgrade documentation (see
>>http://tahiti.oracle.com).
>>
>>The move from E: to C: is trivial: you get to the mount state, and you
>>issue lots of
>>
>>alter database rename file 'e:\wherever\filename' to

>
> 'c:\wherever\filename';
>

>>commands. Use select name from v$datafile to see where the control file
>>*thinks* the files are, and to check that you've correctly told the
>>controlfile about their en masse relocation. Then you alter database open.
>>
>>But you'll have to do all of that with an 8i instance. Once you've got
>>it working as an 8i database, then think about upgrading.
>>
>>Regards
>>HJR

>
>
> OK, that throws a spanner in the works.
>
> I have tracked down Oracle 8i and am in the process of installing it.
>
> Once I have it installed, how do I go about installing and mounting my
> database, so I can begin with the "alter database" commands?

Same as I told you earlier. You create a service using oradim. You edit the init.ora, so that it sets CONTROL_FILES to an appropriate value. You may have to create a new password file in ORACLE_HOME\dbs, using orapwd (depends whether it was included in the original backup, or what your O/S privileges are). Then you connect and startup MOUNT, select name from v$datafile;, and then start issuing alter database commands.

This does, I'm afraid, rather assume you know a reasonable amount about the Oracle architecture and how all the bits fit together...

If you can't get that far, write back with the exact error messages.

> Also, for future reference, is there a better way to transfer databases?

Not really. The reason for the transfer makes all the difference. If I just wanted to let you have sight of some of my data, I could do a transportable tablespace operation, and you could plug my dbfs into your database.... but you again can't expect to plug an 8i file into a 10g database. RMAN does a nice line in cloning a database, but it's version-specific again.

There is no *easy* way of transferring a database across operating systems or across versions.

> Is
> there some procedure that I can ask the owners of the database to perform so
> that what they send to me is in a simpler format for me to install?

Again, it depend son why you want it, why they're giving it to you, and what you expect to do with it. How about a simple export? You can then import tables and their rows, without actually having to ship physical bits of a database around, and you can import a table into a 10g database regardless of the version of the database it came from (though there are nevertheless version-specifc gotchas). But an imported set of data cannot be recovered (ie, rolled forward), for example... so it comes down again to what use you are expecting to put the data to.

> e.g.
> Some sort of backup at their end that I can simply restore here, perhaps?

That's what they gave you already, of course, isn't it?!

When you're going cross-versions, there are no easy answers except for export/import, and that may or may not be appropriate depending on the quantity of data to be transferred, and what use you're expecting to make of it.

Regards
HJR
> Thanks,
> - Greg
>
>
Received on Thu Dec 09 2004 - 19:42:29 CST