• Arun Kumar

Oracle Database Refresh

Oracle database refresh is the most common activity in any environment. There are multiple ways to perform the database refresh. Here you will find most common methods that are used for refresh activity.


Oracle Data Pump Full DB Refresh (Same Version)


If you have few schemas to refresh, then go with schema level refresh. On source

expdp schemas=QBR directory=DUMPDIR dumpfile=QBR.dmp logfile=export.log

On target, drop all objects owned by the user and then perform impdp

impdp schemas=QBR directory=DUMPDIR dumpfile=QBR.dmp logfile=import.log

Repeat same export and import for each schema you would like to refresh.


If you have lot of schemas to refresh, then opt for full DB refresh is good. On source

expdp full=Y directory=DUMPDIR dumpfile=full.dmp logfile=export.log

On target, drop all non-oracle schemas

Drop user <username> cascade;

Make sure target tablespaces have enough space as source and perform import

impdp full=Y directory=DUMPDIR dumpfile=full.dmp logfile=import.log

Oracle Data Pump Full DB Refresh (Different Version)


Performing lower version to higher version data pump refresh is similar to same version refresh. However, when you perform export from higher version (12c) and import into lower version (11g), then you must user VERSION clause while exporting from 12c

expdp full=Y directory=DUMPDIR dumpfile=full.dmp logfile=export.log version=11.2

Now the above exported dump file is ready to be imported into an 11g database!


381 views

Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against