• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

automate a refresh from export everyday

Hi ,I have make a export a schema in production and import in 4 different environment every day .

How would I do that ?

export in production

import in production

Any help is appreciated .
thanks,gyans
0
gyans
Asked:
gyans
  • 3
  • 3
  • 3
1 Solution
 
actonwangCommented:
when you say "export" or "import" a schema, what do you mean exactly?

Do you try oracle JOB? cron or scheduler for scripts if any?
0
 
gyansAuthor Commented:
I would like to export a schema in oracle and then import it ito another database  every day .

What is ORACLE JOB .I am not good in scripts so am asking for help ,

thanks.
0
 
actonwangCommented:
>> export a schema in oracle
      I don't quite get it. You mean like any table change or data change?
     
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
actonwangCommented:
If you are looking for sync between 2 dbs, you might consider using replication:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96567/repoverview.htm#14080
0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, it is possible (and quite easy) to export a particular schema from an Oracle database, then import that into one (or more) schema(s) in the same or another database.  You will likely need to truncate all of the tables though in the target schemas before running import, or you will get duplicates loaded, unless all tables have unique or primary keys (but if they do, and you don't truncate the tables first, the import will be slow since most inserts will fail with an ORA-00001 error, and that will slow down the process).  You will also need to disable any/all foreign keys and triggers before the import.

I use a process like that each weekend to reload our test system with current data from production.  It can all be scripted and automated.  If you need help with that, please tell us:
1. which version of Oracle you have (if they are not all the same, the export source may not be higher than the target, but the targets where yoiu run import may be higher).
2. which O/S you have
3. are all of the databases on the same server or network?
4. how large is your export (in MB or GB)?
0
 
gyansAuthor Commented:

Hello Markgeer,

Thank you .
I want to copy database from production and import to the test systems on the different servers.
I would need help with the scripting:
1. Oracle version is 9.2.0.6
2. Sun Solaris 5.6
3 .databases are on different servers.
4. Export is very small 400 Megs so far ,its only a small schema .

I would like to come up with the script and automate it .
thank you so much
0
 
Mark GeerlingsDatabase AdministratorCommented:
I have very little experience on UNIX (no Sun experience) and only limited Linux experience, so I may not be able to help you much with the shell scripts.  But I can help you with the Oracle parts.

The first step is to create a control file for export that will run on your production server.  You can test that manually from a command prompt like this:
exp parfile=[your controlfile name]

When that is working as intended, then write a shell script to do that for you and schedule the shell script with whatever O/S job scheduler you use.  You may want this same shell script to copy the results dump file(s) to each of the other servers after it finishes the export.  For this, you will probably use either mounted partitions, or "scp" or "ftp".

Then on each target machine, you will first need to create a *.SQL file that contains the commands to disable any foreign key constraints and triggers, then truncate each table.  Test that in SQL*Plus to confirm that it works as intended. Second, you need to write a controlfile for import that will use the dump file(s) from your production server and repopulate the truncated tables, then re-enable the constraints and triggers. Then write a shell script that first calls SQL*Plus and runs your *.SQL file, then calls import and gives it the name of the controlfile for import.
0
 
gyansAuthor Commented:
The shell script is what I was looking for ?
0
 
Mark GeerlingsDatabase AdministratorCommented:
My shell scripts (in the bash shell in Linux) are very simple and basic, because I don't know Linux very well, and because almost all of the action happens in *.sql files or *.ctl files.  I can test the *.sql files in SQL*Plus to confirm that they are working as intended, and I can test the *.ctl files by running either "exp" or "imp" from the command prompt and passing the name of the controlfile to confirm that they work as intended, then the shell script just needs to contain the same commands I had just executed manually at the command prompt.

For example here is a simple bash shell script to run an Oracle export on a Linux server:
#!/bin/bash
# Since this will run via cron (without oracle's environment variables) call a script to set the environment variables:
. /home/oracle/set_env.sh
# Now run export and pass it the controlfile to use:
$ORACLE_HOME/bin/exp /home/oracle/exp_full.ctl
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now