Oracle — How to reimport objects and data into the schema

Developing a custom solution on a Data-Driven Application like RedPrairie/JDA WMS may require you to restore data, again and again, to be able to thoroughly test your custom solution. Sending a request daily or even multiple times a day can be frustrating. Even more so, the wait time a developer has to spend to wait, especially if the developer and DBA are in different time zones. I’m a developer who prefers to automate tasks rather than doing things manually again and again — it’s frustrating doing the same thing over and over again manually when you can spend the time to write a script that automates the task for you.

Here’s how you do it! But do remember, I tend to minimize downtime by creating multiple test schemas for developers/testers, this way they can rotate the username/schema in the MOCA Registry and restart the App Server, which barely takes a minute to do so.

Here’s an example:

Multiple test user/schema

Ali, How do you automate the process of dropping and recreating the user/schema in oracle and then importing data to bring it back to the previous state?

You’ll first write a bash script that calls in the SQL script within the bash script. After importing the environment variables, you’ll have the following command in the end:

DATETIME=$(date +%Y%m%d%H%M%S)
echo “$DATETIME”
sqlplus username/password@pdb @/directorytosqlscript/filename.sql

This is how your SQL script will look like…

alter session set “_ORACLE_SCRIPT”=true;
drop user username_testd cascade;
— USER SQL
CREATE USER username_testd IDENTIFIED BY “password”
DEFAULT TABLESPACE “yourTSname_TS”
TEMPORARY TABLESPACE “TEMP”;
--Assign roles and priviliges here and then you'd want to exit out of sqlplus so you can run your import script to restore username/schema objects.
--EXIT
exit;

Next, you’ll have your bash script run in continuation to execute the import script. I generally prefer to keep the scripts dynamic and not hardcode things when I don’t have to:

DATETIME=$(date +%Y%m%d%H%M%S)
echo “$DATETIME”
sqlplus username/password@pdb @/directorytosqlscript/filename.sql
DUMPFILE “”
DUMPFILE=”$(find /directorytodumpslocation/ -name “schemaname*.DMP” -printf “%f\n”)”
if [ -z “$DUMPFILE” ]
then
 echo “\$DUMPFILE is empty. File not found!”
 echo “Exiting…”
else
 echo “\$DUMPFILE found.”
 echo “Executing impdp to import schema”
 impdp impdp_username/password@pdbname DIRECTORY=Directorylocation DUMPFILE=$DUMPFILE LOGFILE=testd_import_schemaname_$DATETIME.log transform=disable_archive_logging:y STATUS=300 REMAP_SCHEMA=schemaname:schemaname_TESTD
fi

Now the script will work with just a simple execute statement using import dump utility and create user and objects through the dump file.

Script Execution screenshot:

Script Started

Script executed Successfully:

Script Executed Successfully