10g - FULL DB Reorg to LMTS using Data Guard SQL Apply

[richard_howard]

We upgraded to 10g but are not making use of Logically Managed Tablespaces. To get there, we have to reorg all of our Dictionary Managed Tablespaces to LMTS. For a 6TB database, that's a lot of long-running online reorgs over a long period of time. Right now, we just have a few individual tables that we reorganized into their own tablespaces, which are LMTS.

Has anyone ever considered doing a full export/import reorg of their Standby Database along with Data Guard SQL Apply to bring it back full in sync with the Production Database? Follow that with a full recovery from your Standby Database and you end up with a fully reorganized production database that is all LMTS.

I like the idea of doing a Big Bang approach for a full DB reorg to LMTS. If I could do this in my Standby Database, I could work on it for a month and not have any impact at all in Production. Then, the recovery from my Standby is the only downtime I have to plan. If I'm using some kind of SAN mirroring, I could probably even minimize that, as well.

Just curious what some of the Expert DBA's might caution against this approach. Let me know your thoughts.

Thanks.

[stefan_koehler · Accepted solution]

Hello Richard,

your question is answered very easily.

My official reply:

If you are talking about "Data Guard SQL Apply" .. you suppose to run a logical standby database.

Refering to sapnote #105047 (Point "14. Data Guard") ... this is NOT supported.

My unofficial reply:

Technically it should work, but you really need good know-how and test it carefully.

Regards

Stefan

[richard_howard]

The options are endless but if the Data Guard Logical Standby is not supported as Stefan pointed out, we won't go that route. Who knows. Maybe I can get a business case approved for a Unicode conversion and that will handle it all.

Thanks to everyone for their feedback.

[Former Member]

Hello Richard,

First of all, I would like to completely agree to both Stephan and Eric that its a very time consuming job and would require proper testing.

However, I have a plan which might help you put this action into perspective by allowing to estimate necessary details.

The action plan is as below :

1) Perform a DB Copy from your Production Server to a Test Server.

2) Ensure that the hardware specifications of your Test Server match exactly with your Production server. (This will be very helpful in time estimation required for the Reorg)

3) Perform a DMTS to LMTS conversion on the Test Server.

4) Perform testing and necessary checks on both DB level and SAP level.

This way you will get a clear idea on how much time will be required for the Production DB DMTS to LMTS conversion.

Moreover, if you put a change freeze on your Production Server for one month (which should be sufficient for this activity), I suppose you can utilize the standby concept too whereby, instead of the aforesaid test server you can use your standby server.

Once the conversion is successfully completed, switch your Production system to the standby DB (like in case of a DR). In the mean time, restore your standby DB on the production server and bring it in sync with the standby and then open it. Later you can move the transports and other changes which were not moved to Production during the change freeze.

Perform the necessary testing and checks to see if everything works fine.

Although this is not the suggested process by experts, this method will ensure the conversion of your DB while ensuring the availablity of the production server. However, keep in mind you may have to bear some loss of data. Therefore please try this procedure only if you think that the above proedure is suitable for your scenario and that the loss of data is acceptable.

Regards,

Deoraj Alok.

[former_member204746]

I converted a 1 TB database to LMTS with ASSM. It took me roughly 60 hours to do the work.

I would test out with smaller tablespaces and see how it reacts. Also, you will need to convert LONG RAW to BLOB as per SAP note 646681. This is the longest part.

Good luck.