Question OE 11.3 and Multi-Tenancy how-to??

[c3rberus]

Hello,

Working on enabling multi-tenancy in our environment, we are running OE 11.3.3.

We have a few databases and for content ill add structure file here to support my question..

Database name: is db1

b /usr4/bi_test/db1.b1
d "Schema Area":6,64;1 /usr4/testdbs/db1.d1
a /usr4/ai_test/db1.a1
a /usr4/ai_test/db1.a2
a /usr4/ai_test/db1.a3
a /usr4/ai_test/db1.a4
a /usr4/ai_test/db1.a5
d "index":99,1;64 /usr4/testdbs/db1_99.d1
d "d64":101,64;64 /usr4/testdbs/db1_101.d1
d "d32":121,32;64 /usr4/testdbs/db1_121.d1
d "d128":131,128;64 /usr4/testdbs/db1_131.d1
d "d256":141,256;64 /usr4/testdbs/db1_141.d1

Within this database we have many tables/indexes/blobs that are spread across the different physical layout based on best practices for RPB, etc. All databases together is about 470 gigabytes.

Now when I enable the database for multi-tenancy, everything continues to stay the same as all tables within the multi-tenant database are not MT enabled and thus are "owned by the database".

This is exactly the same place I would like to be, except all tables/data owned by a "Regular" tenant.

However when I enable a table to be multi-tenant, it is assigned to the "Default" built-in tenant.. the default tenant becomes the owner of the table and its data.

So, now I add my new "Regular" tenant lets call it "Bob" and I want to allocate all the tables that have been multi-tenant enabled and assigned to Default.

What is the process for this? Everything I read points to having to move the data either via ABL or D&L... is there no way to do this kind in a similar way when the table is first enabled for MT and automatically owned by the Default tenant.

So my question is.. when I am about to enable my tables for multi-tenancy, is it possible to right away assign them to a tenant instead of the Default tenant? So that I can enable all the tables and segregate them within a tenant?

 

[Cringer]

Not sure if it answers your question as I don't use multi tenancy, but this presentation is probably worth a read! http://www.pugchallenge.eu/images/2014pdfs/tim_kuehn_migrating_multitenancy.pdf

 

[c3rberus]

Thanks Cringer, I have that one printed sitting by my desk at work It did answer a lot of unknowns; I guess I just didn't want to believe I have to D&L to initially move data from Default to Regular tenant.

 

[Cringer]

Pretty sure D&L is the default answer to most DBA problems...

 

[c3rberus]

I just find it strange that we can't enable a table for MT and have the data owned by someone other than Default from the get-go; instead of assigning the data to Default tenant giving us the option to assign to a Regular tenant to avoid a D&L to move the data as this is time consuming; but then again I don't know the MT internals so this may be very difficult to do than it sounds.

 

[Cringer]

I don't know for certain how it works, but as far as I know the data has to go into new storage areas. If you're changing storage areas then in general a dump and load is necessary, as it not only moves the data into the new areas, it also cleans up the used blocks in the old area. I'm just waffling really but in the mists of my mind I can argue a reason for requiring a D&L. No doubt someone with more experience in how storage areas work will be along shortly to add flesh to my hazy thoughts.

 

[c3rberus]

I can definitely see a reason for a D&L if we are moving data areas; but in our case when I enable a table for MT I set the allocation policy to delayed so that I can choose non tenant default index/blob/data areas that are defined during regular tenant creation.

This allows us to keep the table in its existing data area; so the table stays where it is; just belongs to a different tenant. Maybe I shouldn't be doing this and should be moving the table to a new area, or perhaps like you said some sort of block cleanup has to happen thus the need for a D&L.

 

[c3rberus]

I have found out that we need to do a D&L to move the data where it needs be (or you can do ABL move); no other alternatives so that is fine. Just wanted to answer my own thread in case anyone else stumbles down this path.