|
Home > Archive > Oracle Server > May 2005 > Restoring schema/tablespace to a different database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Restoring schema/tablespace to a different database
|
|
| Gerold Krommer 2005-05-25, 9:23 am |
| 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,
/Gerold
| |
| Frank van Bortel 2005-05-25, 9:23 am |
| Versions of the Oracle?
Possibly you could use transportable tablespaces (if
your version supports that...)
Small export, copy datafile(s), import
--
Regards,
Frank van Bortel
| |
| Richard Foote 2005-05-25, 9:23 am |
|
"Gerold Krommer" <g.krommer@doremove.fns.co.at> wrote in message
news:1117029692.539878@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
| |
| Gerold Krommer 2005-05-25, 9:23 am |
| Absolutely sorry.Figure me kneeling asking for forgiveness. Version is
9.2.0.5 Enterprise Edition. Both servers are W2003 Server.
Thanks for the lightning superfast response.
/Gerold
"Gerold Krommer" <g.krommer@doremove.fns.co.at> schrieb im Newsbeitrag
news:1117029692.539878@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,
>
> /Gerold
>
| |
| nomorekiss@gmail.com 2005-05-25, 11:23 am |
| Perhaps you could do it with a procedure that refreshes data over
dblink.
That also depends of the data capacity, table count etc.
|
|
|
|
|