BRTools or SQLPlus to restore table after unfortunate tablespace drop?

[KKilhavn]

We have had an unfortunate incident after an upgrade here.

I assume something went wrong during the upgrade, which left a lot of tables assigned to a table type which can not be selected and has description «Repositoryswitch tablespace 700». There are four table types with this description; SLDEF, SLEXC, SSDEF and SSEXC.

The table space was dropped a couple of days after the upgrade. However, there were tables in this table space, and some of them had content too, so recreating the tables is not sufficient, we have to get the content back as well. Among the tables were E071K which is vital for the transport system.

Now our basis people have recreated the affected tables in production and restored a backup from before the tablespace drop to our sandbox system. Unfortunately I have been given the task of figuring out how we best can restore data (table contents) from the affected tables into production.

SQLPlus is an option, and currently the one I am leaning towards. However, since BRTools is a dedicated database management tool I have tried to figure out whether it would be possible to do this using BRTools and whether BRTools does some additional administration which will not be done by SQLPlus - giving us new headaches.

To make matters worse they did a full restore of our test system so we don't have any systems where we can test our approach!

[sunny_pahuja2 · Accepted solution]

Hi,

First of all how you deleted the tablespace because if tablespace has some data then BRtools should have given you some warning.

Anyway, you can use BRtools or sqlplus, there is no different between them as brtools also uses SQL command in background. So, you can export and import the tables using brtools.

Thanks

Sunny

[KKilhavn · Accepted solution]

> First of all how you deleted the tablespace because if tablespace has some data then BRtools should have given you some warning.

Let me just state for the record that it wasn't me

I'm an ABAP and Business Workflow developer, not a basis person.

I've talked to one of the DB experts here, and she will use SQLPlus and select data from the restored tablespace into the production table space.

> So it is very easy to recover without any additional hacks by using Transporting Tablespaces. 

That would probably be our preferred solution if we could be sure that no new data had been created in production after the tables were restored there to reduce the problems for users (program crashes). The mentioned DB expert said this option would overwrite the entire table contents, like a binary copy, so we decided to go for the other solution using selection from one database into another.

Thank you both for your help. Peers are invaluable!

[sunny_pahuja2 · Accepted solution]

> > First of all how you deleted the tablespace because if tablespace has some data then BRtools should have given you some warning.
> 

> Let me just state for the record that it wasn't me

> I'm an ABAP and Business Workflow developer, not a basis person.

Sorry, I never meant you...

Thanks

Sunny

[KKilhavn · Accepted solution]

>

> > > First of all how you deleted the tablespace because if tablespace has some data then BRtools should have given you some warning.
> > 

> > Let me just state for the record that it wasn't me

> > I'm an ABAP and Business Workflow developer, not a basis person.

>

> Sorry, I never meant you...

>

> Thanks

> Sunny

No offense taken, but we haven't spent much time discussing how it could happen. If there was a warning, ... not good!

If the subject receives any attention at all it will be after we have solved the issue. I am currently going through the tables to see which of them have contents so the DB expert doesn't have to spend time on the others.

[stefan_koehler]

Hello Kjetil,

all the data were located in an independent tablespace and you already have a second restored/recoverd system. So it is very easy to recover without any additional hacks by using Transporting Tablespaces.

Check the official documentation about this feature:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces013.htm

BR*Tools also covering the "Transporting Tablespaces" feature - check sapnote #1003028 for details.

Regards

Stefan