Scenario: two networked servers. One server currently houses a 9i
database. Database is to be moved to the new server. (Both servers are
Solaris). Database is 24x7, so downtime must be minimal. Database is
only 20GB in size.

The two servers cannot share storage of any kind, so my original idea of
creating new tablespaces within the same database but on the new box's
storage and then moving tables/indexes across to the new tablespaces is
not a go-er (unless maybe an NFS mount would do the trick...??). For
the same reason, my other idea of using dbms_redefinition to move the
data across and incurring minimal table locking is not a flyer.

There is a possibility of creating a new database on the new server,
populating it with empty copies of the source tables, and then
populating the new tables via dblinks... but the concern is that clients
will modify data in the source tables as the move takes place (thanks to
the 24x7 ruke0, and therefore another possibility has been discussed of
setting up replication between the two databases to capture
post-initial-setup DML... but replication to handle the move of a 20GB
database strikes me as absurd.

Basically, I'm just casting around for suggestions as to how you would
approach the issue.

Anyone got any timings on a 20GB export and import?

Regards
HJR

How using standby database methods ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Howard J. Rogers" <hjr@dizwell.com> wrote in message
news:4083b4b2$0$442$afc38c87@news.optusnet.com.au. ..
> Scenario: two networked servers. One server currently houses a 9i
> database. Database is to be moved to the new server. (Both servers are
> Solaris). Database is 24x7, so downtime must be minimal. Database is
> only 20GB in size.
>
> The two servers cannot share storage of any kind, so my original idea of
> creating new tablespaces within the same database but on the new box's
> storage and then moving tables/indexes across to the new tablespaces is
> not a go-er (unless maybe an NFS mount would do the trick...??). For
> the same reason, my other idea of using dbms_redefinition to move the
> data across and incurring minimal table locking is not a flyer.
>
> There is a possibility of creating a new database on the new server,
> populating it with empty copies of the source tables, and then
> populating the new tables via dblinks... but the concern is that clients
> will modify data in the source tables as the move takes place (thanks to
> the 24x7 ruke0, and therefore another possibility has been discussed of
> setting up replication between the two databases to capture
> post-initial-setup DML... but replication to handle the move of a 20GB
> database strikes me as absurd.
>
> Basically, I'm just casting around for suggestions as to how you would
> approach the issue.
>
> Anyone got any timings on a 20GB export and import?
>
> Regards
> HJR

Jonathan Lewis wrote:

> How using standby database methods ?
>

I thought about that (well, I had the idea of using logical standby a la
Data Guard). Still seems overkill.... but if it gets your vote, it's
worth pursuing.

Regards
HJR

It seems to be a low-complexity option that
gives a very short down-time.

Take hot backup
Ship outstanding archived logs
Start recovery
Stop primary
Ship last bit of redo log
Startup.

I don't think I'd even bother to fiddle about
with a 'proper' standby for just 20 GB.
e.g. I'd probably do the hot backup by:
alter tablespace begin backup
ftp files in tablespace
alter tablespace end backup
(generated from the db of course)

I might practise the steps a couple of times,
before doing the production switch, though.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Howard J. Rogers" <hjr@dizwell.com> wrote in message
news:4083b94e$0$4544$afc38c87@news.optusnet.com.au ...
> Jonathan Lewis wrote:
>
> > How using standby database methods ?
> >
>
> I thought about that (well, I had the idea of using logical standby a la
> Data Guard). Still seems overkill.... but if it gets your vote, it's
> worth pursuing.
>
> Regards
> HJR

Howard J. Rogers wrote:
> Scenario: two networked servers. One server currently houses a 9i
> database. Database is to be moved to the new server. (Both servers are
> Solaris). Database is 24x7, so downtime must be minimal. Database is
> only 20GB in size.
>
> The two servers cannot share storage of any kind, so my original idea of
> creating new tablespaces within the same database but on the new box's
> storage and then moving tables/indexes across to the new tablespaces is
> not a go-er (unless maybe an NFS mount would do the trick...??). For
> the same reason, my other idea of using dbms_redefinition to move the
> data across and incurring minimal table locking is not a flyer.

If NFS is possible, would using rman to duplicate the db be an option?

Another possibility, although I personally never did this would be
to export to a named pipe, gzip from that pipe into another pipe
and feed a network transfer (e.g. ssh) from the second pipe to the
target host, uncompress & import to the target.

On the optimistic assumption that the network and target host can
keep up with the data rate, 20 GB even with 100 MBit/s should get
transfered within 3 Minutes and a little, so I'd expect a downtime
around 10 Minutes. However, this is pure mathematics backed up
by no experience whatsoever. So it's up to you what you make of this
(I'd probably make a mess ;-) )

Cheers,

Holger

> The two servers cannot share storage of any kind, so my original idea of
> creating new tablespaces within the same database but on the new box's
> storage and then moving tables/indexes across to the new tablespaces is
> not a go-er (unless maybe an NFS mount would do the trick...??). For
> the same reason, my other idea of using dbms_redefinition to move the
> data across and incurring minimal table locking is not a flyer.

How about a full backup of server-one, and restore it on server-two ??
Non oracle-backup only filestystem backup.
Its like a 'copy' of server-one on server-two.
A full restore of 20 GB database took 2 hours...


> Anyone got any timings on a 20GB export and import?

It depends, i have expierience with export/import 5 GB database.
It took about 4 hours on my stand-alone PC Athlon 1,7, while export took
about 1,5 hour.
--
TomekB

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> a écrit dans le message de
news:c60g55$o8q$1@hercules.btinternet.com...
>
>
> It seems to be a low-complexity option that
> gives a very short down-time.
>
> Take hot backup
> Ship outstanding archived logs
> Start recovery
> Stop primary
> Ship last bit of redo log
> Startup.
>

I know the OP said that both databases are on Solaris. He did not say wether
oracle was the same release on both server but I presume it is.

This raises a general question in my mind:

To what extent is a database file, copied from a server to another server,
"compatible" ?

I mean:

Can you use a file created on solaris 2.6 on solaris 2.7 ? On solaris 2.8 ?
2.9 ? What about minor revisions of the O/S (Eg: Solaris 9 original, solaris
9 + recommended patches) ? What about minor revisions of the RDBMS ? (Eg:
8.1.7.3 and 8.1.7.4)

I don't recall reading anything in the docs about datafile compatibility
accross OS versions or RDBMS versions.

Any pointers/clarification appreciated.

"Howard J. Rogers" wrote:
>
> Scenario: two networked servers. One server currently houses a 9i
> database. Database is to be moved to the new server. (Both servers are
> Solaris). Database is 24x7, so downtime must be minimal. Database is
> only 20GB in size.
>
> The two servers cannot share storage of any kind, so my original idea of
> creating new tablespaces within the same database but on the new box's
> storage and then moving tables/indexes across to the new tablespaces is
> not a go-er (unless maybe an NFS mount would do the trick...??). For
> the same reason, my other idea of using dbms_redefinition to move the
> data across and incurring minimal table locking is not a flyer.
>
> There is a possibility of creating a new database on the new server,
> populating it with empty copies of the source tables, and then
> populating the new tables via dblinks... but the concern is that clients
> will modify data in the source tables as the move takes place (thanks to
> the 24x7 ruke0, and therefore another possibility has been discussed of
> setting up replication between the two databases to capture
> post-initial-setup DML... but replication to handle the move of a 20GB
> database strikes me as absurd.
>
> Basically, I'm just casting around for suggestions as to how you would
> approach the issue.
>
> Anyone got any timings on a 20GB export and import?
>
> Regards
> HJR

How about using Transportable Tablespaces? You could crossmount a
filesystem and then transport the datafiles to the new server that way.
Or, you could even use rcp (or scp) to copy the datafiles. The most time
consuming task in this operation would be to copy the datafiles to the
new server. Obviously plugging them in won't take too long, especially
if you script the whole thing before you start. And you'll need to move
non-segment objects over with SQL, but DBMS_METADATA should help
there...

HTH,
Brian
--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

If OS + Oracle version are the same I would go for a clone - apply logs and
make the switch ...

If you like exp/imp : I did 30Gb data + 20Gb index from one box to another
over 1 gigabit
using exp/dd/rsh/imp (4x parallel) in just under 4hours ... would have been
faster if
I had more disks to play with ...

"Howard J. Rogers" <hjr@dizwell.com> wrote in message
news:4083b4b2$0$442$afc38c87@news.optusnet.com.au. ..
> Scenario: two networked servers. One server currently houses a 9i
> database. Database is to be moved to the new server. (Both servers are
> Solaris). Database is 24x7, so downtime must be minimal. Database is
> only 20GB in size.
>
> The two servers cannot share storage of any kind, so my original idea of
> creating new tablespaces within the same database but on the new box's
> storage and then moving tables/indexes across to the new tablespaces is
> not a go-er (unless maybe an NFS mount would do the trick...??). For
> the same reason, my other idea of using dbms_redefinition to move the
> data across and incurring minimal table locking is not a flyer.
>
> There is a possibility of creating a new database on the new server,
> populating it with empty copies of the source tables, and then
> populating the new tables via dblinks... but the concern is that clients
> will modify data in the source tables as the move takes place (thanks to
> the 24x7 ruke0, and therefore another possibility has been discussed of
> setting up replication between the two databases to capture
> post-initial-setup DML... but replication to handle the move of a 20GB
> database strikes me as absurd.
>
> Basically, I'm just casting around for suggestions as to how you would
> approach the issue.
>
> Anyone got any timings on a 20GB export and import?
>
> Regards
> HJR

G Dahler wrote:
> This raises a general question in my mind:
>
> To what extent is a database file, copied from a server to another server,
> "compatible" ?
>
> I mean:
>
> Can you use a file created on solaris 2.6 on solaris 2.7 ? On solaris 2.8 ?
> 2.9 ? What about minor revisions of the O/S (Eg: Solaris 9 original, solaris
> 9 + recommended patches) ? What about minor revisions of the RDBMS ? (Eg:
> 8.1.7.3 and 8.1.7.4)
>
> I don't recall reading anything in the docs about datafile compatibility
> accross OS versions or RDBMS versions.
>
> Any pointers/clarification appreciated.

The exact release of the operating system are (in theory) not affecting
the portability of datafiles. As long as

- operating system (Solaris, HP-UX, Linux...)
- CPU platform (32/64 Bit, SPARC/x86/HPPA...)
- Oracle Release

are the same, you should be able to just copy the files, because the
headers are identical in those cases. Reason: the Oracle-feature
"transportable tablespace" relies on that and defines the above criteria.

Regards
Maik