on 05-06-2014 11:00 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.