Re: Restoring schema/tablespace to a different database

"Gerold Krommer" <g.krommer_at_doremove.fns.co.at> wrote in message news:1117029692.539878_at_newsmaster-03.atnet.at...
> Hi,
>
> I asked aunt google on this but didn't come up with a defintive answer, so
> I thought I asked the gurus.
>
> Our customer has two databases on two servers. Each database has two
> schemas, both using their dedicated tablespace. The schemas are named the
> same,
> so server 1 has schema A and B and server 2 has schema A and B. They have
> a business need to periodically update schema B on server 2 to the same
> state as schema B on server 1, but realtime replication is not neccessary
> and not desired.
>
> Is it possible and supported to make an online backup of the tablespace
> belonging to schema B on server 1, ship it to server 2, set tablespace for
> schema B on server 2 to offline and fully restore the shipped tablespace
> now. Access to the tablespace during restore will not be possible and this
> is not a problem.
>
> I suspect the answer is yes, but wanted to ask before I try in vein in the
> test environment. I guess I'm not sure about how logically and physically
> separate two schemas are.
>
> Thanks for any hints,
>

Hi Gerold

You suspect correctly (depending on Oracle version)

Checkout Transportable tablespace option with export/import. You could simply drop the outdated tablespace (and related objects with including contents clause) in your server 2 and replace it as required with the transported tablespace from server 1. You physically copy across the related datafiles and logically export/import the related objects via the DD definitions.

It's all described in the Utilities doco.

Cheers

Richard Received on Wed May 25 2005 - 09:15:30 CDT