Beta Migration of database

Each 3/4 months, we deliver a new version release of the application.

Prior to production release, we do have a Beta for users. It is done on a different machine. The important task is that I have to set up the database environment as in production box. Then I have to do the migration (DDL and DML operation) for the new release as needed. For one small project's Beta release, I did the following things this mornig -

In Beta box -

1. I created the user tablespace

SQL> CREATE TABLESPACE "COLUMBIA01" DATAFILE '/oracle/oradata/tdevdb/columbia_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2. Created the user and granted access

SQL> create user columbia1 identified by ****** default tablespace columbia01;

SQL> grant connect, resource, create view to columbia1;

SQL> grant read, write on directory DBDUMP to columbia1;

3. Imported the production dump using datapump of 10g

# impdp columbia1/****** directory=DBDUMP dumpfile=db101-columbia-20070910.dmp

4. I ran the migration scripts

Migration scripts for a new release is usually developed by the application developers and the database team members. It includes DDL, DML and small java programs to be run for the new version of application.