My client has certain requirement for their application. I need to know whether the following is possible. If so how to implement. I simplified the situation for understanding.
1. Got two tablespaces: tab1 and tab2
2. Each tablespace has one table: Table1 for tab1 and Table2 for tab2.
3. Data of each tablespace backed up on week1 separately. Now, I have two backup files viz. tab1_wk1.bkp for tab1 and tab2_wk1.bkp for tab2. Bakup is performed with RMAN.
4. Inserted data on both tables, Table1 and Table2
5. Backed up data separately for tab1 and tab2 on week2. Backup files: tab1_wk2.bkp and tab2_wk2.bkp.
6. On week3, I want to restore data of tab1(only) from week1. So the status of the tablespaces should be:
tab1: contains data upto week1 (No data that inserted in step-4)
tab2: contains data upto present time(contain data that inserted in step4)
Is it possible. I tried to do this simulation, but could not do that.
Appreciate your view/tips in advance.
Via "traditional" recovery, no, you can't do that.
However, Tablespace Point-in-time-recovery (TSPITR) is available as of Oracle8. The procedure involved is arduous and involved. A far better solution IF (and a mighty big if) you have a quiet time to export the tables. If this will not be sufficient, check out TSPITR. Or find out why the client needs such a requirement.
Solution could be.. create tab1 in one user, and tab2 in another. and create synonyms for every one to access it. As part of backup procedure, take the backup of all the users. So when client is having the requirement of tab1 of last week and tab2 of present week. import tab1 from last weeks export file and tab2 from present weeks export. Be care ful to import only user level.
Hope this works other wise if u are using 8i may be u can try Transportable tablespace technology
Thanks for your reply.
I can't effort export/import utility becuse it's a datawarehouse application. However, problem has been solved.
Actually the tablesapces I mentioned are different application databases(logically separated by tablespaces) reside in one physical database (one instance). Client some time need to restore one application on certain back date. Now, we are going to have one more identical database which will be used for restore purpose only. No need any restore operation on production database.
I appreciate your views.