Oracle exp/imp parameters

Welcome to LawsonTalk. We hope you enjoy your visit.

You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as customizing your profile, sending personal messages, and voting in polls. Registration is simple, fast, and completely free. After registration, please check your e-mail within 24 hours for an message from us, read it, and reply to it.

Join our community!

If you're already a member please log in to your account to access all of our features:

Oracle exp/imp parameters; checking data refresh
Topic Started: Sep 14 2009, 10:21 AM (600 Views)
Duane	Sep 14 2009, 10:21 AM Post #1
Newbie Posts: 6 Group: Members Member #14,971 Joined: August 12, 2009	oracle 10g R2 - We have a fairly junior Oracle DBA who is now in the hot seat because our senior Oracle DBA has left. We're trying to export and import Lawson and MSCM data to a test system. Although we know that we can use Lawson utilities for the application, we can't do that for MSCM. Can anyone indicate what oracle exp/imp parameters are used for dbcopy or dbdump/dbload? Can any DBA out there help us out with the exp-imp parameters? Thanks! Duane



3monkeys	Sep 18 2009, 04:30 AM Post #2
Super Member Posts: 168 Group: Members Member #240 Joined: January 13, 2006	Duane: Here is our general process to copy the lawson schema/data from our production product line to our test product line. I'm sure there are other ways to do this. 1) Perform a full DB export from the source DB (PROD). You could just export the schema you need to refresh ... but we do the whole thing to be safe. More details below. 2) Copy this FULL export (.dmp file) from the source (PROD) server over to the destination (TEST) server. 3) On the destination DB (TEST) check the current permissions of the user before dropping, you will need to recreate the user exactly the way it was. The method we use is to from the GUI Enterprise Manager, right click on the user (lawson in this case) and choose 'create like'. We give this new user a name like TEMP_LAWSON. Once this user is created we leave it alone. Then once we delete the Lawson user, we can always reference the TEMP user to re-create Lawson using the 'create like' approach again. 4) Drop the user/schema you want to 'refresh' (usually lawson but I suppose it could also be whatever your mscm user is) with the cascading delete option. This will delete the user and all of it's objects (which by default makes the 'schema' go away as well). 5) Re-Create user (lawson) 6) Import the users objects into the destination DB (TEST). More details below. For STEP 1, the following script will get you an entire DB export (ALL schema's): The first two lines just do some house keeping to delete old files from the last time the script was run. The next three lines are just setting some environment variables ensuring that I'm connecting to the correct Oracle instance (if there is more than one on the same server, or if you try to run the script through CRON) The most important line being the last one, the execution of the actual export utility. rm /backup2/HMC10/dmp/HMC10.dmp rm /backup2/HMC10/dmp/HMC10_exp.log ORACLE_HOME=/sfwr01/app/oracle/product/10;export ORACLE_HOME ORACLE_SID=HMC10;export ORACLE_SID PATH=/sfwr01/app/oracle/product/10/bin:/usr/lbin:/usr/ccs/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:.;export PATH exp system/yoursyspassword full=Y file=/destination_for_dmp_file/full.dmp log=/destination_for_log/exp.log The parameters in the export command: full = Y -> specifies to export the ENTIRE DB. file= /somefile.dmp -> is the export/dump file that will be created. log = /somefile.log -> is a log file created during the export that will contain the results of what happened during the export. For STEP 6, to import just one schema (one particular user), the following script will work. Just be sure to specify the same source .dmp file that was used in the above export. imp userid@instance_name fromuser=lawson touser=lawson file=/destination_for_dmp_file/full.dmp log=/destination_for_log/imp.log ignore=y Fromuser = lawson -> we are going to pull Lawson's objects out of the .dmp file that was created earlier Touser = All the objects we are pulling from the .dmp file will be owned by 'Lawson' in the destination (TEST DB in this case) file = this is the source .dmp file that was created from the export from Production, and then copied over to our destination (TEST) server log = again this just creates a log of the import available to view later on. ignore = y -> this ignores errors. I believe errors like "could not create table because it already exists". Lastly, you may need to run the grant_law_perms.sql after this, just to be safe. Good luck. Let me know if there are questions. Edited by 3monkeys, Sep 18 2009, 04:33 AM.


« Previous Topic · Database · Next Topic »