Oracle schema migration

In this blog we are telling about How to do oracle schema referesh from production to DEV & SIT environment.

Right now we are taking backup of umar schema after that we can restore the schema to SIT & DEV server.

PREREQUEST:
===========

At source server :

For datapump before taking schema dump you have to check the default tablespace of user & check the objects count of user.

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='umar';

USERNAME             DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE
-------------------- ------------------------ --------------------
umar                      umar_tab            TEMP

SQL> select * from dba_ts_quotas where username='umar';

no rows selected

SQL> select sum(bytes/1024/1024/1024) from dba_segments where owner='umar';

SUM(BYTES/1024/1024/1024)
-------------------------
               2.30578613

SQL> select object_type,count(*) from dba_objects where owner='umar' group by object_type;

OBJECT_TYPE               COUNT(*)
----------------------- ----------
SEQUENCE                         1
LOB                             10
TABLE                          101
INDEX                           21

SQL> select * from dba_directories where directory_name='EXPIMP';

OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
EXPIMP
/nijam/expimp

IMPLEMENTATION :
================

Now Taking schema backup from source server.

expdp “‘ / as sysdba'” directory=EXPIMP dumpfile=schema_cdmintvn.dmp  logfile=schema_cdmintvn.log schemas=umar parallel=3

Destination server :

Before restoring backup on destination server check with file/folder permission and check the dump file is moved right path or not and are u mentioned right path in datapump.

select * from dba_directories where directory_name='EXPIMP';

impdp "' / as sysdba'" directory=EXPIMP dumpfile=schema_cdmintvn.dmp logfile=schema_cdmdeveg.log remap_schema=umar:nijam remap_tablespace=umar_tab:nijam_tab

If schema owned 2 tablespace means script should be below format :

impdp "' / as sysdba'" directory=EXPIMP dumpfile=schema_cdmintvn.dmp logfile=schema_cdmdeveg.log remap_schema=umar:nijam remap_tablespace=umar_tab:nijam_tab remap_tablespace=tab01_01:nijam_tab

POSTREQUEST :
=============
Once restoration is completed ,check the objects count of schema.

SQL> select object_type,count(*) from dba_objects where owner='nijam' group by object_type;

OBJECT_TYPE               COUNT(*)
----------------------- ----------
SEQUENCE                         1
LOB                             10
TABLE                          101
INDEX                           21