cancel
Showing results for 
Search instead for 
Did you mean: 

Duplicate remote database

Gyankr
Frequent Advisor

Duplicate remote database

Hi,

I want to transfer the entire shema from a remote oracle 10G database to my local database.I thought of using some gui utility like toad but sine the data is about 50GB i dont think this GUI can withstand it.

My another problem is the server in which the remote database is installed has limited space and hence i cannot create a dump there.

Is there any tool which can directly transfer the schema objects and the data to my local instance? I tried a google and got a software by name easydump but that did not yield any positive results.

Regards,
Gyan
8 REPLIES

Re: Duplicate remote database

Why not ask Oracle themselves?
My house is the bank's, my money the wife's, But my opinions belong to me, not HP!
Jeeshan
Honored Contributor

Re: Duplicate remote database

did you heard about dblink?
a warrior never quits
Jeeshan
Honored Contributor

Re: Duplicate remote database

another way you can export it.

Connect from local database to remote database and try export. The dump will be create in local database server.
a warrior never quits
Yogeeraj_1
Honored Contributor

Re: Duplicate remote database

hi Gyan,

Is it possible to get an indication on the size of the different table indexes in the schema?

e.g.
yogeeraj@mydb.mu>r
1 select * from (
2 SELECT DECODE (partition_name,
3 NULL, segment_name,
4 segment_name || ':' || partition_name
5 ) objectname,
6 segment_type object_type, ROUND (BYTES / 1024 / 1024, 2) mb
7 FROM dba_segments
8 where owner in ('MYSCHEMA')
9 order by mb desc
10 )
11* where rownum <11

OBJECTNAME OBJECT_TYPE MB
______________________________ ________
TAB511 TABLE 1460
TAB572 TABLE 1189
TAB230 TABLE 1180
TAB200 TABLE 860
TAB600 TABLE 845
TAB201 TABLE 610
TAB320 TABLE 562
TAB151 TABLE 545
PK_TAB600 INDEX 525
TAB101 TABLE 515

10 rows selected.

Elapsed: 00:00:29.40
yogeeraj@mydb.mu>


If Pareto's rule apply, maybe we can split the transfer by doing partial exports before moving the data.

revert.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Duplicate remote database

What is the HPUX component in this?
Sounds like an Oracle problem... ask Oracle.

Anyway, folks here (have to) know a bit of oracle also.

An exported DB may be a lot smallr than the DB itself.

Also with a little trikery (mknod) you can export to a pipe striagh over the network without files needed on the source.

And... why not jusdt access teh remote DB from the target?

Good Luck!
Hein.
Yogeeraj_1
Honored Contributor

Re: Duplicate remote database

hi again,

In case, you have a sufficiently fast network between the local database and the remote database, you can as well initiate the database export on the local database server itself.

e.g.

$ exp system/@mydb file=myschem.dmp owner=myschem

Export: Release 10.1.0.4.2 - Production on Mon Mar 10 15:01:42 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MYSCHEM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MYSCHEM
About to export MYSCHEM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions..
...
Export terminated successfully without warnings.
$

hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)

Re: Duplicate remote database

A couple of thoughts came to mind. First of all look at the Oracle support site. metalink.oracle.com
You will need a valid support contract.

1) setup an NFS file system, mount it to the remote server and export to that mount point. Will need the SA to create the NFS mount.

2) Clone the database. You can use Oracle Enterprise Manager 10g Grid Control to clone the database. I have never used this method.
I used the modifed control file clone on 8i and 9i databases. See oracle note: 375672.1
This method allows you to change the SID and the location of the database files.

4) Use RMAN. (Metalink Note: 259694.1)
Volker Borowski
Honored Contributor

Re: Duplicate remote database

Hi,

if you do not have the space for a dump, export to a pipe and start the import concurrently, reading from the pipe.

Volker