Rebuilding Your Local Oracle XE Database from Production
In a recent article, I discussed how you can use Oracle XE to build a local development database. I have also built a script that will recreate my staging database (which is just another local database) from production whenever I need to. This will allow me the ability to easily refresh my database when needed, then run my test scripts against it to make sure it works.
This requires the Oracle Client installed on your local machine, along with the imp.exe and exp.exe tools that come with it (may require additional installation).
First, there is the batch file that calls it all:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | @echo off cls echo ============================================================== echo = RESTARTING ORACLE XE (CLEAN / SESSION FREE!) = echo ============================================================== echo. net stop oracleservicexe net start oracleservicexe cls echo ============================================================== echo = DROPPING EXISTING STAGING USER = echo ============================================================== echo. sqlplus -s system@xe.world/password @DropStagingUser.sql cls echo ============================================================== echo = RECREATING THE STAGING USER = echo ============================================================== echo. sqlplus -s system@xe.world/password @CreateStagingUser.sql cls echo ============================================================== echo = EXPORTING dbname@prod.world = echo ============================================================== echo. del c:\newstaging\prod.dmp c:\oracle\ora92\bin\exp.exe dbname@prod.world buffer=4096 file=c:\newstaging\prod.dmp grants=n indexes=y owner=(database_owner) triggers=y constraints=y statistics=none cls echo ============================================================== echo = IMPORTING DUMP INTO staging_user@XE.WORLD = echo ============================================================== echo. c:\oraclexe\app\oracle\product\10.2.0\server\BIN\imp.exe staging_user@xe.world file=C:\newstaging\prod.dmp full=y del c:\newstaging\prod.dmp cls |
You will need to tweak the database names, folders, passwords, etc. I have included below the SQL files that are being referenced in the batch file above.
Here is DropStagingUser.sql. I use this file to blow away the user (and database) from my local database server:
1 2 | DROP user staging_user cascade;
exit |
Simple enough! Then I use CreateStagingUser.sql to recreate the user:
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE user staging_user IDENTIFIED BY password; GRANT connect TO staging_user; GRANT resource TO staging_user; GRANT CREATE DATABASE LINK TO staging_user; GRANT CREATE MATERIALIZED VIEW staging_user; GRANT CREATE PROCEDURE TO staging_user; GRANT CREATE SEQUENCE TO staging_user; GRANT CREATE TABLE TO staging_user; GRANT CREATE TRIGGER TO staging_user; GRANT CREATE TYPE TO staging_user; GRANT CREATE VIEW TO staging_user; exit; |
You can add any grants that you think your staging database user will need. Those above have been good enough for what I use it for. Try and run them individually (dropping the user, creating the user, etc) before trying to tie it all together so that you can see how it works, and where it might break. Good luck, and please feel free to post any comments if you can think of better way to do things.
