Home » RDBMS Server » Backup & Recovery » Backup selected data (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Backup selected data [message #546569] Wed, 07 March 2012 07:55 Go to next message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

Hi,

I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance)

I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.

Want expert suggestion to adopt best approach / alternate approach.

Thank you!!
Re: Backup selected data [message #546570 is a reply to message #546569] Wed, 07 March 2012 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A database backup tool (like RMAN) is not the correct tool to backup data.
Use export/data pump to backup data.

Regards
Michel
Re: Backup selected data [message #546573 is a reply to message #546570] Wed, 07 March 2012 08:25 Go to previous messageGo to next message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

Hi,

I think backup/archive is a bit misleading word. Let me again explain, I just want that backup/archived data remains in inside Oracle only (in backup tables, not in form of external tables).

I am trying to do it first time, could you please let me know why to use export/data pump for backing up data. I am not using RMAN for this anyway.

Are you want me to first export selected data, and then insert it to backup schema tables using datapump?

Regards,
Manu
Re: Backup selected data [message #546574 is a reply to message #546573] Wed, 07 March 2012 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to move data inside the database then there are multiple ways:
- Partition your tables and then use exchange partition feature (fastest way)
- Use CTAS to create "backup" tables from selected data
- ...

Regards
Michel
Re: Backup selected data [message #546578 is a reply to message #546574] Wed, 07 March 2012 08:51 Go to previous messageGo to next message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

I am not sure, but partition which tables (orignal tables, backup tables).

If you are talking about orignal tables, can I partition a table having data? And what if the partition contain both the selected and non-selected data?

Well, with respect to partitioning, I was thinking about to create single partitioned index on backed up data.

Can you confirm partitioning thing, or where I can get more info (I already tried to google but no effective solution).

Regards,
Manu
Re: Backup selected data [message #546581 is a reply to message #546578] Wed, 07 March 2012 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you are talking about orignal tables, can I partition a table having data?


Use dbms_redefinition for this.

Quote:
what if the partition contain both the selected and non-selected data?


Partition in a way this does not happen, if you can't then you can't use this method.

Partitioning is interesting here only if you can partition both tables.

Regards
Michel
Re: Backup selected data [message #546582 is a reply to message #546578] Wed, 07 March 2012 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
>Can you confirm partitioning thing, or where I can get more info (I already tried to google but no effective solution).
The whole approach is seriously flawed.
What do you think is actually accomplished by duplicating data within the same database?
If the database becomes damaged to where you can not access the original data,
it is likely that you won't be able to access the duplicated rows either.
Re: Backup selected data [message #546605 is a reply to message #546582] Wed, 07 March 2012 09:59 Go to previous messageGo to next message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

Hi BlackSwan,

Its not actually duplicating, its moving data from original tables to backup tables as larger size of orignal tables causing slower query performance.

Hi Michel,

I got you on partitioning thingy. So next fastest way achieve my objective will be CTAS, right? If right, should I use nologging and parallel feature? or some other features for better performance. If not right, what should be my approach?


Regards,
Manu
Re: Backup selected data [message #546609 is a reply to message #546605] Wed, 07 March 2012 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CTAS is NOLOGGING and yes you can use PARALLEL option on the SELECT part.

Regards
Michel
Re: Backup selected data [message #546618 is a reply to message #546609] Wed, 07 March 2012 13:09 Go to previous messageGo to next message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

Thanks for prompt response. I am getting interest in your first suggestion related to partitioning, as I myself think that backing up data with CTAS nologging gonna take a lot of time and processing power, plus a lot of downtime for application, and I am not sure, if it ever fails in between because of some error.

I have one more question about partitioning.

Suppose, if I partition (list partition, I have not myself created partitioned ever) in such a way that a partition will either contain data that should be moved, or should not be moved, then according to you I can exchange the partition.

Then after partition exchange, if we receive any new value in column on which I have created list partition, so how this value will be entered into partitioned table (Will partition for that new value will gets automatically created, or we need to create it manually, or its not possible).

Thank you!!
Re: Backup selected data [message #546619 is a reply to message #546618] Wed, 07 March 2012 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to create the new partition for the new value (it is not automatic) or it will go to the default partition (if you created one otherwise you'll get an error).
If you have a default partition then you can later split it into several new ones for the new value(s).

Regards
Michel
Re: Backup selected data [message #546888 is a reply to message #546619] Fri, 09 March 2012 12:45 Go to previous message
manubatham20
Messages: 558
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

OK.

Can you give me some practical advise...

I have data like below (Hash partitioned total 128 partitions, have data in 34 partitions, all else Blank).

Partition	Study Rows (Vendor_Data)	Study Rows (Vendor_Record)
SYS_P253	564672				17053
SYS_P172	30608708			2788481
SYS_P198	49582384			4608052
SYS_P200	3499365				330046
SYS_P220	21863961			576365
SYS_P198	19107340			535124
SYS_P213	23211228			717335
SYS_P170	732938				23879
SYS_P245	2060534				71108
SYS_P157	546927				20920
SYS_P267	2375787				87088
SYS_P244	1080627				36845
SYS_P183	374603				13597
SYS_P182	592532				21893
SYS_P263	1024136				35956
SYS_P218	1964144				67247
SYS_P159	683619				23076
SYS_P177	155764277			13477759
SYS_P159	37909389			3422686
SYS_P179	11147038			314958
SYS_P168	812016				79088
SYS_P237	5726134				560084
SYS_P178	4309928				426844
SYS_P258	320014				14449
SYS_P199	503594				31665
SYS_P215	7914892				455617
SYS_P256	1411796				66565
SYS_P227	589022				26848
SYS_P195	631792				29682
SYS_P169	716311				31846
SYS_P259	31136286			1765290
SYS_P163	20778277			1445522
SYS_P208	72349406			3828848
SYS_P261	82158468			4298964
SYS_P244	28252673			1843615
SYS_P216	14952316			966922
SYS_P264	12967656			753803
SYS_P202	22446299			1553975
SYS_P227	5263658				251304
SYS_P150	1052908				50492
SYS_P233	614251				21853


With your experience can you tell me how much time it will take to convert that much data from hash to list partition (First of all, if its possible). I want this because only through list partition I can take backup easily.

I am thinking about implementing list partitioning in such a way as soon as I receive a new value, it automatically create a partition on selected tables and doing this will not affect applicatio coding (procedures and packages), am I thinking right?

Other questions are, Can we exchange hash partition in one table to list partition in another table? I think previosuly I got you right, that we can exchange list partition if we have it in both the tables, can we exchange hash partition in same why if both tabels are hash partitioned?

Please help me...

Regards,
Manu

[Updated on: Sat, 10 March 2012 10:44]

Report message to a moderator

Previous Topic: how to use the incarnation command
Next Topic: RMAN AND ASM
Goto Forum:
  


Current Time: Thu Nov 15 10:29:57 CST 2018