Home » RDBMS Server » Server Utilities » Using Data Pump in Oracle 10g?
Using Data Pump in Oracle 10g? [message #207494] Tue, 05 December 2006 15:10 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
Is anyone familiar with using the Oracle 10g data pump tool? I'm setting up a back up database on another system and would like to transfer data from my main system to my back up.

If i only want to transfer the changes that occured since the last back up is there a method of using the data pump to only extract the delta? Is it possible to do this using the system change numbers?

thanks!
Re: Using Data Pump in Oracle 10g? [message #207495 is a reply to message #207494] Tue, 05 December 2006 15:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nope. Infact incremental export was never been properly implemented properly (it does not function the way it literally means) in any oracle release till now.
>> only extract the delta?
We call it as STREAMS (previously called Change-data-capture)
Please google about it.
If all you are looking into is just to duplicate/clone the production database, a much flexible mechanism like RMAN duplication could be used.
In a certain cases, you can also use materialized views to refresh the database.
Re: Using Data Pump in Oracle 10g? [message #208226 is a reply to message #207494] Fri, 08 December 2006 13:04 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Can you do filtering on what RMAN backs up?
Re: Using Data Pump in Oracle 10g? [message #208241 is a reply to message #208226] Fri, 08 December 2006 14:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What kind of filtering?
You can exclude/include tablespaces and even backup incrementally.
Only if treat the other database as a standby, you can just apply that incremental backup to standby and synchronize it.
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb008.htm#sthref1320
changes.
Re: Using Data Pump in Oracle 10g? [message #208958 is a reply to message #208241] Tue, 12 December 2006 13:15 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
On the Orcle Data Pump info page it said that Data pump supported "Fine-Grained Object Selection". LINK: http://www.oracle.com/technology/products/database/utilities/htdocs/data_pump_overview.html

In particular i was interested in

Quote:
The QUERY parameter filters data by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table.


I was thinking of using Data Pump with a QUERY parameter filter to select particular data that i wanted to move to my back up database. Like all the records where customer_id = 12345. Or if i can use the database (SCN) status change numbers to get all the records after a certain date.
Re: Using Data Pump in Oracle 10g? [message #208959 is a reply to message #208958] Tue, 12 December 2006 13:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Like all the records where customer_id = 12345
That is possible. But you have to do it for every table. Cannot 'filter' based on date/scn (unless you have a date as column in table).

>>if i can use the database (SCN) status change numbers to get all the records after a certain date.
Exp/imp/expd/impdp are not capabale of doing this.
RMAN can do this.

[Updated on: Tue, 12 December 2006 13:19]

Report message to a moderator

Re: Using Data Pump in Oracle 10g? [message #209150 is a reply to message #208959] Wed, 13 December 2006 09:13 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
[quote title=Mahesh Rajendran wrote on Tue, 12 December 2006 13:19]>>Like all the records where customer_id = 12345
That is possible. But you have to do it for every table. Cannot 'filter' based on date/scn (unless you have a date as column in table).[quote]


Will RMAN let me do filtering against the database where customer_id = 12345? Basically i want to replicate a subset of data from my production to my backup server and continue to update it incrementally.
Re: Using Data Pump in Oracle 10g? [message #209152 is a reply to message #209150] Wed, 13 December 2006 09:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You seem to be asking the same question again and again with a different touch.
>> Will RMAN let me do filtering against the database where customer_id = 12345?

NO.
RMAN will not see any 'data'. All it cares about is a timestamp/SCN.
Export/import cannot see timestamp/SCN. It is logical. You can selectively export data using a where condition based on 'data'.
Re: Using Data Pump in Oracle 10g? [message #209158 is a reply to message #209152] Wed, 13 December 2006 09:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As we already said, you are looking for propogation of delta changes.
STREAMS does that logically. You can do it manually by materialiized views.
Using RMAN you can create a standby database and apply the incremental backups.
export/import can only filter data based on where condition.
Another option is to make use of database links and you have to write extensive sql using MERGE and design your own 'upsert' (which is equalent to update when found, insert when not found) statements.
Regards~

Re: Using Data Pump in Oracle 10g? [message #209971 is a reply to message #209158] Mon, 18 December 2006 16:07 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
I did some reading up on Oracle streams on the Oracle site here: http://www.oracle.com/technology/products/dataint/index.html

I was wondering if there is a good book on the subject that you can recommend with or a tutorial online somethere.

I'm using the Oracle Enterprise 10g edition.

Thanks

[Updated on: Mon, 18 December 2006 16:08]

Report message to a moderator

Re: Using Data Pump in Oracle 10g? [message #209974 is a reply to message #209971] Mon, 18 December 2006 17:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Oracle documentation is at its best.
http://www.oracle.com/pls/db102/portal.portal_db?selected=3
Re: Using Data Pump in Oracle 10g? [message #211823 is a reply to message #209974] Tue, 02 January 2007 08:22 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
I'm currently attempting to set up streams for my production database and am using the Oracle enterprise Manager to do so.

I've set up ARCHIVELOG mode on my database. No i'm using the 'Streams Global, Schema, Table and Subset Replication Wizard' in the Oracle enterprise Manager.
I'm at the point where it asks for my destination database which i assume is my back up database. But my back up database is not connected to any network so i'm not sure how to get past that.

I just want the streams to capture changes that i can export to the datapump or RMAN. Not exactly sure how to set it up.

I'm following the guide below using the 'Streams Tablespace Replication Wizard': http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14228/config_simple.htm#CACJHBBI

Do you have any experience with this? Thanks.


Mahesh Rajendran wrote on Mon, 18 December 2006 17:13
Oracle documentation is at its best.
http://www.oracle.com/pls/db102/portal.portal_db?selected=3

Re: Using Data Pump in Oracle 10g? [message #211827 is a reply to message #211823] Tue, 02 January 2007 08:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> But my back up database is not connected to any network
None of the options we discussed would work then.
In that case, export/import is the only option.
As we said before the only other option is to setup a remote standby database.
You can just copy the archivedlogs/backupsets and apply the changes there.
Re: Using Data Pump in Oracle 10g? [message #211828 is a reply to message #211827] Tue, 02 January 2007 08:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Meanwhile, raise a support request with oracle. May be the OSS can give a smarter solution.
Re: Using Data Pump in Oracle 10g? [message #213196 is a reply to message #211827] Tue, 09 January 2007 14:54 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Mahesh Rajendran wrote on Tue, 02 January 2007 08:41
>> But my back up database is not connected to any network
None of the options we discussed would work then.
In that case, export/import is the only option.
As we said before the only other option is to setup a remote standby database.
You can just copy the archivedlogs/backupsets and apply the changes there.



So can i set up a remote to my production server, use the streams to filter the data i need to the remote server. Then use RMAN to get the export of the remote to load my back up server?
Re: Using Data Pump in Oracle 10g? [message #213198 is a reply to message #207494] Tue, 09 January 2007 15:14 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Change Data Capture
http://searchoracle.techtarget.com/tip/0,289483,sid41_gci961925,00.html#9
Previous Topic: Imoprt and Export
Next Topic: query regarding sqlldr
Goto Forum:
  


Current Time: Thu Dec 12 07:57:39 CST 2024