Run Sharding Advisor on a Non-Production System

To minimize the impact on a live production system, you can run the Sharding Advisor on a copy of the database schema and workload, located on a different server than the production system.

To get the same results as if it were the live production system, the production database schema and workload can be exported using the Oracle Data Pump utilities and copied to a different server. Then you can run Sharding Advisor on the imported schema.

You only export the database schema and system tables. There is no need to export the actual data.

The following procedure uses the HR schema as an example.

Do the following steps on the source (production) database server.

Export the schema using Data Pump Export.

> expdp system/password SCHEMAS=HR DIRECTORY=HR_DIR CONTENT=METADATA_ONLY
 DUMPFILE=hr_metadata.dmp LOGFILE=hr_exp.lst

Export the Automatic Work Repository (AWR) snapshot.
```
SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql
```

Do the following steps on the target database server.

Copy the dump files from the source to the target.

For example, copy the dump files to /scratch/dump.
Create a user that can run Sharding Advisor on the schema.
```
SQL> CREATE USER hr IDENTIFIED BY password;
```
Create (or replace) the dump file directory variable that Data Pump Import can reference.
```
SQL> CREATE DIRECTORY HR_DIR AS '/scratch/dump'

SQL> CREATE OR REPLACE DIRECTORY  HR_DIR AS '/scratch/dump'
```

Import the schema.

> impdp system/password DIRECTORY=HR_DIR DUMPFILE=hr.dmp LOGFILE=imp.lst SCHEMAS=HR

Load the AWR data.

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

Now you can run Sharding Advisor on the target, non-production, copy of the database with the user you created.
```
> gwsadv –u hr –p password –c -awr_snap_begin begin_timestamp –awr_snap_end end_timestamp -w
```