Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform