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

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools