How to precreate tables for migration?
So, We're taking the leap from 8.0.6 to 8.1.7. For various reasons, we will be using the export/import method of migration. Since I'll have the DB all to myself for the weekend, I want to also partition most of our larger tables (1M-7M rows each -- it's not a huge DB) in the migration. My plan to precreate the tables was to: 1) Export DB. 2) Create new 8.1.7 DB. 3) Create tablespaces. 4) Create partitioned tables. 5) Import DB. (I've left out many migration steps not pertinent to this question) Obviously, step 4 can't go before step 5, as step 5 creates the schemas. So, my questions: Has anyone precreated tables before a full import on a clean DB? Did you just manually precreate the schemas? How does the import deal with this (e.g. the DESTROY arg for imp)? Or would it be OK to skip step 4 and delete/rebuild the tables to be partitioned after the import had rebuilt them? I would prefer not to do the latter, seeing as this will be the first time in three years that the TSs will be nice and contiguous. :) Also, for brevity, we won't be going LMT for most of our TSs. Need too much time to plan for that... :( TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to precreate tables for migration?
As I impose order on our servers, (moving from project-managed to dba-managed db's) I perform this sort of task for individual schemas. The short form is the same as yours except: 3.5) create schemas where tables or tablespace assignments will change. 4.5) create tables that are to have storage parameters different from the source. ... and ... 5) import using IGNORE=Y parameter Imp fills the tables as they exist, or creates them using the info in the export file where they do not exist. After all, if you're migrating all the tables you pre-created should be clean at the beginning, so you won't get duplicate rows and DESTROY is hardly necessary. One caveat -- IIRC you don't need to create all the tables, but you must create all the tablespaces in order to use this method. Or this could be a local effect as my new servers have different data file paths. It is useful to compose the creation in scripts, and compose a script to destroy the structures, in case your new storage parameters are flawed somehow and the import ends badly. That way you can iterate more easily. In my shop we move off the old server and into a test server first, then when the iterations are done I can replicate the refined procedure for production. In a few cases the project managers failed to move the db to production when they went live, so we move to different schemas on the same server first, then drop one and move the data (and client connections) to production. Jesse, Rich wrote: So, We're taking the leap from 8.0.6 to 8.1.7. For various reasons, we will be using the export/import method of migration. Since I'll have the DB all to myself for the weekend, I want to also partition most of our larger tables (1M-7M rows each -- it's not a huge DB) in the migration. My plan to precreate the tables was to: 1) Export DB. 2) Create new 8.1.7 DB. 3) Create tablespaces. 4) Create partitioned tables. 5) Import DB. (I've left out many migration steps not pertinent to this question) Obviously, step 4 can't go before step 5, as step 5 creates the schemas. So, my questions: Has anyone precreated tables before a full import on a clean DB? Did you just manually precreate the schemas? How does the import deal with this (e.g. the DESTROY arg for imp)? Or would it be OK to skip step 4 and delete/rebuild the tables to be partitioned after the import had rebuilt them? I would prefer not to do the latter, seeing as this will be the first time in three years that the TSs will be nice and contiguous. :) Also, for brevity, we won't be going LMT for most of our TSs. Need too much time to plan for that... :( TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin:vcard n:Jerman;Don tel;work:919.508.1886 x-mozilla-html:TRUE org:Database Management Service,Information Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Database Administrator adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA x-mozilla-cpt:;-9536 fn:Don Jerman end:vcard
Re: How to precreate tables for migration?
for precreatng the users, pull the create users statements out of the .dmp file. joe [EMAIL PROTECTED] 07/05/01 02:35PM So,We're taking the leap from 8.0.6 to 8.1.7. For various reasons, we will beusing the export/import method of migration.Since I'll have the DB all to myself for the weekend, I want to alsopartition most of our larger tables (1M-7M rows each -- it's not a huge DB)in the migration. My plan to precreate the tables was to: 1) Export DB. 2) Create new 8.1.7 DB. 3) Create tablespaces. 4) Create partitioned tables. 5) Import DB. (I've left out many migration steps not pertinent to this question)Obviously, step 4 can't go before step 5, as step 5 creates the schemas.So, my questions: Has anyone precreated tables before a full import on aclean DB? Did you just manually precreate the schemas? How does the importdeal with this (e.g. the DESTROY arg for imp)? Or would it be OK to skipstep 4 and delete/rebuild the tables to be partitioned after the import hadrebuilt them? I would prefer not to do the latter, seeing as this will bethe first time in three years that the TSs will be nice and contiguous. :)Also, for brevity, we won't be going LMT for most of our TSs. Need too muchtime to plan for that... :(TIA!Rich Jesse System/Database Administrator[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Jesse, Rich INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: How to precreate tables for migration?
Hey Don! GREAT info! I'm wiping out my test DB right now to try it out. I already have the scripts to create the TSs (I've learned a little in three years), so that's no biggie. I'll letcha know what happens. THANKS! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Thursday, July 05, 2001 14:11 To: Multiple recipients of list ORACLE-L As I impose order on our servers, (moving from project-managed to dba-managed db's) I perform this sort of task for individual schemas. The short form is the same as yours except: 3.5) create schemas where tables or tablespace assignments will change. 4.5) create tables that are to have storage parameters different from the source. ... and ... 5) import using IGNORE=Y parameter Imp fills the tables as they exist, or creates them using the info in the export file where they do not exist. After all, if you're migrating all the tables you pre-created should be clean at the beginning, so you won't get duplicate rows and DESTROY is hardly necessary. One caveat -- IIRC you don't need to create all the tables, but you must create all the tablespaces in order to use this method. Or this could be a local effect as my new servers have different data file paths. It is useful to compose the creation in scripts, and compose a script to destroy the structures, in case your new storage parameters are flawed somehow and the import ends badly. That way you can iterate more easily. In my shop we move off the old server and into a test server first, then when the iterations are done I can replicate the refined procedure for production. In a few cases the project managers failed to move the db to production when they went live, so we move to different schemas on the same server first, then drop one and move the data (and client connections) to production. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to precreate tables for migration?
Since you mentioned partitioned tables, watch out for: Bug 973502 THE PARTITIONED INDEX IS NOT IMPORTED AS A PARTITIONED INDEX If you are happy with the storage parameters, you can skip steps 3 and 4, because step 5 creates everything for you - except the above !! [EMAIL PROTECTED] 07/06/01 06:35AM So, We're taking the leap from 8.0.6 to 8.1.7. For various reasons, we will be using the export/import method of migration. Since I'll have the DB all to myself for the weekend, I want to also partition most of our larger tables (1M-7M rows each -- it's not a huge DB) in the migration. My plan to precreate the tables was to: 1) Export DB. 2) Create new 8.1.7 DB. 3) Create tablespaces. 4) Create partitioned tables. 5) Import DB. (I've left out many migration steps not pertinent to this question) Obviously, step 4 can't go before step 5, as step 5 creates the schemas. So, my questions: Has anyone precreated tables before a full import on a clean DB? Did you just manually precreate the schemas? How does the import deal with this (e.g. the DESTROY arg for imp)? Or would it be OK to skip step 4 and delete/rebuild the tables to be partitioned after the import had rebuilt them? I would prefer not to do the latter, seeing as this will be the first time in three years that the TSs will be nice and contiguous. :) Also, for brevity, we won't be going LMT for most of our TSs. Need too much time to plan for that... :( TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
