Backup desired tables in oracle

[Former Member]

How to backup desired tables in oracle?

I guess we can create a new tablespace and move all the desired tables and back it up by using this query

ALTER TABLESPACE <TABLESPACE_NAME> BEGIN BACKUP

please post your suggestions

Regards,

Pavan

[former_member182657 · Accepted solution]

Hi Pavan,

You can go with exp/imp options or you can create a duplicate table likean e.g

create table table_backup as select * from <table_name> ;

Regards,

Gaurav

[Former Member · Accepted solution]

yes gaurav that's what I believe but will there be any conflict or any issue with table dependencies ..?

[janos_mucsi-besze · Accepted solution]

Dear Pavan,

If you have space issues, backink up the table would not help you. Don't you mean reorganization, to gain some more freespace?

By executing the above command (create table ...) you would create the new table (table_backup) in the users own default_tablespace. That means you would double the spacce required for the same data.

Reorganizing the table into a newly created tablespace with BRSPACE would achieve you less extents allocated for the same object, means less space is allocated.

So,l suggest you to use BRSPACE (which internally uses Oracle commands and features) to 'move' the table into a new tablespace.

For more detailed info and to see some example commands, please check the below note:

646681 Reorganization of tables with BRSPACE

[Former Member]

Hello Gurus,

I came to know that the desired tables can be backed up by creating a transport request.

The generated data and cofiles can be copied any where and importing the transport request will get back the tables to database.

I believe this is a better and simple option.

Please suggest your comments..

Regards,

Pavan

[Former Member]

Hi,

As you said earlier - Table size is huge, then you are you expecting a table can be backed up in a transport request?

If you perform such critical activities without online_consistent backup, it leads to data loss which cannot be recovered! Plan proper backups of your systems.

Regards,

Nick Loy

[Former Member]

Hi Nick,

A online backup will be taken every week without fail that's not a problem at all.

Yes, the size of the table is huge and it takes hours to import the transport requests and there should be ample space in the tablespace.

So, I feel this is the simple option to restore desired tables.

Thanks,

Pavan

[Former Member]

Hi,

If you have weekly full online_consistent backup in place, just take an archive log backup before you perform the activity so that you can do full restore and recover your database.

Do not customize backup/restore method whcih is not recommended by SAP, It can be done in specific cased but you need to bear the additional cost (Storage etc.).

I assume you must be using some backup tool for your backups, in this case restore and recovery won't take much time!

Regards,

Nick Loy

[Former Member]

Yes Nick,

We have a backup tool...

Any way all I wanna know is how to embed a DB table in Transport Request.

Please post your comments.

Thanks in advance.

Pavan

[Former Member]

Hi,

Just create a transport request and include objects (there you can add R3TR TABL <TABLE NAME>).

Or go to table an search the menu option "Transport", it automatically creates a transport request.

Regards,

Nick Loy

[Former Member]

Hi,

Safest and recommended option is to take a full online_consistent/offline backup before you perform any critical activity.

If space is the constraint, then you can take backups into tapes which doesn't require space at your storage.

Regards,

Nick Loy

[Former Member]

Hi Nick,

I accept that complete backup should be taken since tables will have dependencies on each other.

but, if the tables are huge and there are cost constraints.. I just want to know if there is any way to do this :-).

[Former Member]

Hi,

Backing up your data is to ensure that it is used as a fall back solution.

In this case even if you take a backup of particular table (by creating a new table), it can't be restored in later stage as online operations will not be captured in your backed up table.

As you said - It is a big table, even to create new table then you need equal amount of space (double in size).

For your situation - If you have equal amount of space on disk, then just create copy table by executing above command (mentioned in above replies) which will take it's own time, then perform your activity (users shouldn't access system during the activity to ensure that no online transactions which hits this particular table) and then release your system. As a worst case solution you can swap the table names if required.

Regards,

Nick Loy

[ACE-SAP]

Hello

Your scenario does not really make sense. You do not need to copy tables to a new tablespace for 'backuping' them.Taking a backup of single tablespace is not really useful either as it cannot be restored on it's own as it is a part of a database and should be aligned with the DB change number (SCN).

What are you willing to do, backup specific tables only, for which reason ?

Regards

[Former Member]

well there are reasons and space constraints as the tables are huge..