by Well, I didn’t expect it to take this long to get to the point of making this blog entry. Busy-ness is happy-ness, or something schmarmy like that…
So, I had this Oracle database with 9 independant tablespaces – each one for a different library in this case. The challenge, I am moving them all to a new install on a new server, but not all at once. And, after I migrate one, I ‘upgrade’ the application running on it. It’s been fun. What I’ve found about tablespace migrations…
I’ve got two methods of doing it really. The first one is the one I like better at this point, though it’s not the one recommended by the vendor. It seems to work just as well though. Basically, their tablespaces are independent enough that they’re “Transportable”. I.e. I can transport one tablespace, and run the upgrade process which cleans up some Invalid objects, and at the end it’s just as good.
Transportable Tablespace exports allow you to export a small file that contains the metadata for the tablespace, and copy the actual data files where you want it to go. Though, you’ve got to get your export done and the tablespace data files copied while the tablespace is set to read only. I’m using storage with remote mirror capability that has been scripted so I can easily take splits – so I put the tablespace into read only mode (alter tablespace TS read only;), run the export which creates a very small dump file, and then split the remote mirror. I then set the tablespace back (alter tablespace TS read write;), mount up the remote mirror on another server, and copy the datafiles from SAN to SAN local a single server. I get the files in place, do an import command with the metadata file, and the new data filenames, and away it goes.
Now, the first set of tablespaces I brought over, I actually brought over the entire database (using the remote mirror split, mount, copy trick again), so I have to drop the tablespace before I import it again. This rudely introduced me to domain indexes, which you have to drop before you can drop a tablespace. How to find them? Well… google found me this –
select index_name, table_owner, table_name, tablespace_name from user_indexes where index_type=’DOMAIN’;
This gives you all of your domain indexes in your instance, and has to be run as sys_dba I believe (at least I have been). I hunted through the results a bit and filtered to the tablespace I wanted, and dropped the index, dropped the tablespace, and done. I didn’t drop the associated user accounts, because after the metadata import, and the upgrade, it was all put back together again just fine.
The second option was to use export/import scripts delivered by the vendor. Their application upgrade scripts automatically generated ‘migration’ scripts specfically for moving their databases from one server to another. I’ve been using these to run parallel exports and saving off the export files in case the vendor comes back at me angrily that I didn’t use their method, even though the results are the same. Also, it’s nice to have an extra backup. I did also do the import once, and found that their scripts also recreate the entire tablespace and all associated users and data files. They’re okay, and they work, but they are slower than the transportable tablespaces option, and they load down the source database server for longer while they’re exporting.
In either case, they needed all their indexes rebuilt, and various other Oracle objects regenerated as everything doesn’t come over, regardless of the type of import you do, at least as far as I’ve seen. Fortunately the upgrade scripts handle all of this for me, as I’m (obviously 🙂 not a real Oracle DBA, and it would take me a little while to figure out how to script their regeneration myself.
Incidentally, running Oracle databases in parallel Solaris 10 zones seems to be working fine. I even had one of the databases have an issue with processes today, and took a look at the other one and it was sailing along just fine. Basically, I broke off one of the tablespace instances/libraries separate from the rest, and put it in it’s own Oracle instance and Solaris 10 zone on the same box. That broken off instance/library had major issues today that lead back to insufficient number of Oracle processes allowed. It was nice to see it only affecting them, since I also tracked back to where the connections came from, and helped them find something that wasn’t dropping connections properly after they were done being used.
That’s all for now… two more migrations left this week, and then maybe I’ll get back into some Dtrace scripting so I can pull apart this application from it’s component parts.
Useful links googled in my learning –
http://dba.ipbhost.com/lofiversion/index.php/t1592.html
http://ist.uwaterloo.ca/~baumbach/ORACLE10g/transportable_tablespace.html
I also used the following books –
Oracle9i: The Complete Reference (Paperback) by Kevin Loney, George Koch
Oracle Database Foundations: Technology Fundamentals for IT Success (Paperback)
by Bob Bryla
I wouldn’t call this an expert endorsement of these books really as I’m hardly an expert in the Oracle DBA field (I’m pretty sure a real Oracle DBA would show up and smite me if I ever claimed to be :). I will say they were very useful to me, specifically for learning how to do what I needed to do here, and I will also say I actually intend to sit down and read the Bryla book cover-to-cover for my own personal improvement. (The Complete Reference is too much of a reference for that kind of reading for me)
The Complete Reference was useful for back and forth reference, especially as it came in PDF form in an included CDROM that I now have it handy whenever I need it. The Foundations book had the biggest section on Transportable Tablespaces out of all the Oracle books I found at my local Borders.
cheers,
-b