Re: replace logical exp-imp with transportable tablespace

From: Casey Dyke <caseydyke_at_optusnet.com.au>
Date: Mon, 25 Oct 2004 22:29:20 +1000
Message-ID: <417CF1A0.1020003@optusnet.com.au>

tomi,

going with the perhaps naive assumption that your usage of tablespace in the singular literally means a tablespace - TTS would certainly be feasible. doing a ruddy great number of tablespaces would likely become unruly, but still feasible with careful coding. automating it should be pretty straightforward as well. simply fire off a job (cron, EM, whatever) to manage the export, that may or may not also include an scp of your dmp and datafile(s) to the remote host. you may also do something like update a file on the remote host containing a flag along the lines of "ready to import" and maybe even include a file pointer (you may choose to rename the latest files to include a date for example). that could just as easily be an insert into a table on the remote db ... another job could wake up on the new host, read the flag file (or table), see a new ts is ready for import and bang the new one in. it would, of course, need to do a drop tablespace including contents and datafiles before the import, but this would work in simple terms. actual execution would likely be trickier but you could do it w/out too much pain and distress. easiest thing to do is test in a test environment and when you are done, test again and again and again .. that should give you the confidence to decide whether you are ready for a prod rollout. try it out, i reckon automating it would be fun. if you are dealing w/large amounts of data, your testing will be all the more important. the concept works fine for small tablespaces ...

now, all that said, if you are talking about a number of tablespaces or the entire database, just use dataguard as Carel-Jan says.

in summary, the steps could go something like this:

source host

---

wake up, realise it is time to export
decide on a dmp/df file name for this iteration (ie: include timestamp) ts put into RO mode
export ts
copy df to staging area ts put into RW mode compute checksums on df, dmp
push df, dmp to remote node staging area (bail on error) update flag file or table (include checksums)

remote host

---

wake up, realise it is time to import
check flag file/table
verify dmp/df have not already been imported, checksums ok verify new df will not breach fs capacity {copy file from staging area to df location here if space is not an issue} drop ts including contents and datafiles {copy file from staging area here if space is an issue} (potentially more app downtime during copy if the files are big) import ts
put ts into RW, if necessary

i have probably missed something, but that's likely close enough. your worries about automation failure should hopefully be alleviated by thorough testing (as above) and good error handling w/in your code.

cheers,

casey

tomi wijanto wrote:

>Hi,
>
>We have 2 database that need to be in sync every
>night, also 1 database for user transactions while
>sync process is running.
>
>Current operation is using logical exp-imp and take
>half hour to complete, no manual intervention here.
>But because future space estimation is about 20 times
>more than current, it means at least exp-imp will run
>in 10 hours, and this is untolerable.
>
>So i try to use transportable tablespace instead, but
>i'm not sure if this can be done everytimes without
>manual intervention (the main reason is because i must
>drop existing tablespace in destination, how if it
>failed??).
>
>additional info for current exp-imp process:
>- Related tablespace in source db is set to read only
>when exp-imp.
>- Destination db is idle when exp-imp (no users
>connect to it).
>
>Can transportable tablesplace replace logical exp-imp?
>The main limitation is : no manual intervention
>allowed (except space problem, of course).
>
>ps: i believe probably someone will suggest
>replication instead of exp-imp. But, i'm sorry,
>because i can't change anything related to application
>(we use 3rd party vendor).
>
>regards,
>tomi
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Mail Address AutoComplete - You start. We finish.
>http://promotions.yahoo.com/new_mail
>--
>http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 25 2004 - 07:25:10 CDT