+15146131498

Oracle DBA Support info@awator.net

Datapump

Oracle Datapump  With Full DB and Schema Refresh Steps

1. Logical backup (exp/imp –> 9i , expdp/impdp –> 10g)
2. Physical backup

2.A) User Managed backup( Cold and Hot backup)
2.B) Server Manager backup( RMAN )

1. Logical backup (Datapump expdp/impdpdp)
We can take expdport(expdp) in four levels.

1A. Full database Level expdp/impdp
1B. Schema(User) Level expdp/impdp
1C. Table Level expdp/impdp
1D. Tablespace Level expdp/impdp

Note: Do the expdport in Source(quelle) database and impdport in target(ziel) database.

Database must be in open and create the directory in both source and target database.

Sourec Database:

SQL> create directory expdp_dir1 as ‘/u01/app/oracle/’;
SQL> grant read,write on directory expdp_dir1 to system;

SQL> select *from dba_directories;

Target Database:

SQL> create directory expdp_dir1 as ‘/u01/app/oracle/’;

SQL> grant read,write on directory expdp_dir1 to system;

SQL> select *from dba_directories;

1A. Full database Level expdp/impdp
=====================================

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y

1B. Schema(User) Level expdp/impdp
====================================
expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=leopold

impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=leopold:leopold

Note: leopold is a user in the database

1C. Table Level expdp/impdp
=============================
expdp system/manager directory=expdp_dir1 dumpfile=ctstab.dmp logfile=ctstab.log tables=leopold.emp

impdp system/manager directory=expdp_dir1 dumpfile=ctstab.dmp logfile=ctstab_impdp.log remap_schema=leopold:leopold tables=emp

DB Refresh steps:
=================

1. take export(expdp) of source database(quelle).

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y

2. move the dumpfile to target database(ziel) using scp.. if both the database running in same server this steps is not required.

scp ctsfull.dmp oracle@<Target server IP>:/u02/app/oracle

3. Drop the application users and dont drop database default users like sys,system,dbsnmb,xdb.

If you give below command it will show username,created date. Mostly database default users will be created in same day.

select username,created from dba_users;

drop user leopold cascade;

4. before doing import(impdp) check the used space of source database and freespace in the target database. tablespaces names should

same between sourec and target database then Target database each tablespace size should be more than or equal to source database tablespace.

Source: select tablespace_name,sum(bytes/1024/1024) from dba_segments group by tablespace_name;

Target: select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the impdport in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y

6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where status=’VALID’ group by object_type;

=============================== end ===========================

Schema Refersh steps:
=====================

1. take expdport of a schema in source database(quelle).

expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=leopold

Note: Dont give semicolon(;) at the end of the above command.

2. move the dumpfile to target database(ziel) using scp.if both the database running in same server this steps is not required.

scp ctsuser.dmp oracle@<Target server IP>:/u02/app/oracle

3. create the new user in target database(if already existed drop and recreate)

select username from dba_users;

drop user leopold cascade;

create user leopold identified by leopold;

4. before doing import(impdp) check the used space of a schema(user) in source database and freespace in the target database then

target database tablespaces should be more than or equal to source database tablespaces.

Source: select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner=’leopold’ group by tablespace_name;

Target: select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the import(impdp) in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=leopold:leopold

6. once completed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where owner=’leopold’ and status=’VALID’ group by object_type;

For More information

contact info@awator.net

SOCIAL LINK

Copyright

Copyright (c) 1998-2014