Moving large amounts of data from one Oracle Server to another
George Purkett
Bob,
Here is how I would do it. I refresh test databases from production databases
using this method.
It only works if you want or can live with the entire instance being copied.
This requires the database to be in archivelogmode. This method copies the
entire instance.
Actual downtime can be 0 minutes., Transfer time can be negligible if automated.
1. Create a backup controlfile to trace.
2. Put all of the tablespaces in hot backup.
3. Copy the files to the second server without taking production out of
service..
4. Take all the tablespaces out of hot backup.
5. copy the archive files generated since the hot backup to the second server.
6. use the controlfile to trace and archive logs to recover the transfered
database and recover until cancel.
And the key is, the database files can be moved a day or two ahead of time and
archive logs applied up til
the time the transition is to occur.
hope it helps,
George.
Bobc wrote:
> Here is the situation:
> There is 10 gb of data on a production server (Oracle 7.3.?.?) that I need
> to move to a second server on a quarterly basis (Same version of Oracle).
> Currently this data is bundled up in a full oracle export, sent to the
> second Unix box, an import is performed and then the quarterly processing
> takes place on the second box. The whole process from the start of the
> export to the end of the import is taking 8-10 hours!!! This is way to
> long. The export processing is taking place on a large parallel siemens box
> the import is being done on a smaller siemens machine.
>
> Does anyone out there have an suggestions on a different approach to moving
> this data to the second machine? I am open to any and all ideas. I can
> only assume, since I have not had a chance to look at the database tuning
> issues that both databases are well tuned, if you know of any tuning issue
> specific to import/export I would really like to hear about them.
>
> One requirement of any suggestion is that it can not heavily impact
> performance on the main production server.
>
> If it is not to much trouble, could you email me directly.
>
> Thanks in advance,
> Bob.
Bobc
MyTwoBits
feature called portable tablespaces. It might be faster just to move the
tablespaces from one server to another.
My25/c.
"Bobc" <bobch...@hotmail.com> wrote in message
news:9vaH4.143011$Hq3.3...@news2.rdc1.on.home.com...
Tiaw Wee Lim
Is it possible to setup multi instances in one oracle db(one unix box)? How to
setup and what's the benefits of it?
thanks for the info.
Regards.
Sybrand Bakker
server most likely won't help you anything, as these instances needs to be
kept in sync, you are running on one server, and that server is going to be
the bottleneck.
I would only head in this direction if your cpu is running at less than 60
percent :)
Regards,
Sybrand Bakker, Oracle DBA
Tiaw Wee Lim <wlt...@ecssin.com.sg> schreef in berichtnieuws
38F2B977...@ecssin.com.sg...
Yuri Ludevig
Bob,
we went thru similar scenario and found out that it would be much faster
to move the data using "create table as select * from ..." and
specifying that the new table is unrecoverable. You'll have to define
the same tablespaces that you have on the master site. After everything
is created you can import all the constraints (without importing the
data). You can recreate indexes yourself or you can leave it to import.
Be careful with the default values - there will "get lost" during the
"create table as .." phase. This is Oracle's bug (they call it feature).
Let me know if you need more info.
BTW it took us less than an hour to move similar amount of data (running
several jobs at the same time).
Yuri
Bob Fazio
planning on staying with 7.3.., this is probably the best approach. If it
is possible to go with 8.1.5, then there is a much better way.
A standby database with 8.1.5 (8i) can be used to query against for
reporting purposes. It is basically the same as the above suggestion, but
it will constantly keep itself in recovery mode, with the ability to query
against the tables.
Another option is to actually do the export/import at the same time. We do
this with some databases that we need to refresh.
Start this on the primary database, on an NFS mounted filesystem.
mknod expdat.dmp p
gzip -c <expdat.dmp >expdat.dmp.gz &
exp system/manager direct=y ... # whatever you need to export.
On the other node/machine start this process shortly after the expdat.dmp.gz
file starts to get filled.
gunzip -c <expdat.dmp.gz >expdat.dmp &
imp system/manager ... # whatever you want to import.
This works because a pipe is only really local to the machine, and the .gz
file will be read until it is closed, plus an import will always take
longer.
The advantage of this process is that you will be able to run both the
import and the export at the same time removing at least
2-3 hours out of your 10 hour process. If the import fails for any reason
you will still have the expdat.dmp.gz file to run it again.
--
Robert Fazio, Oracle DBA
rfa...@home.com
remove nospam from reply address
http://24.8.218.197/
MyTwoBits
plan you application carefully to make use of it. You can have multiple
instances pointing to one set of database tables. This provides protection
against instance failure on one of the nodes. However, the complexity in
setting it up may not make it worth it. I certainly wouldn't set up two
instances on one machine.
There was an article that came out in the Oracle magazine which described
the various scenarios of running fault-tolerant Oracle databases. I wish I
had it handy. Depending on you needs, you might want to take a look at
replication and a stand-by database. If you're worried about supporting a
lot of connections into one database, consider looking at setting up
Mulit-threaded server.
My25/c.
"Tiaw Wee Lim" <wlt...@ecssin.com.sg> wrote in message
news:38F2B977...@ecssin.com.sg...