Oracle has several utilities for
exporting and important Oracle STATSPACK and AWR snapshots. The STATSPACK
utility has a documented method for moving data between different repositories
using Oracle Export/Import utilities.
Remember, in a shop with many databases,
the DBA may choose to export the AWR snapshots from each databases SYSAUX
tablespace and migrate the snapshots into a centralized database repository,
sometimes on a specific instance.
AWR export and import programs
It is easy to move AWR snapshots into a
centralized repository:
1. Export AWR
snapshot data with the awrextr.sql script
2. FTP the resulting
dmp file to the new AWR report server/instance
3. Import the AWR
snapshot with the awrload.sql command.
STATSPACK Export and Import
A special parameter file, spuexp.par, is
delivered with STATSPACK as a sample export parameter file. However, moving
STATSPACK data between databases using the Export/Import approach is a complex
process.
Exporting AWR snapshot data
Create a directory
SQL> CREATE DIRECTORY AWR_DATA AS
2 '/u01/oracle/';
Directory created.
SQL> @?/rdbms/admin/awrextr.sql
AWR EXTRACT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
This script will extract the AWR data for a range of snapshots ~
~ into a
dump file. The script will prompt users for the ~
~ following
information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
Importing AWR snapshot Data from dmp files
On the
repository database, we made a directory and ensured that our DIRECTORY_PATH
corresponds to the directory where the awrexport.dmp file is located on
disk.
Also, a temporary staging schema AWR_STAGE is created. Objects
are first imported into this staging schema and then inserted into the WR$
and DBA_HIST AWR historical tables.
We will need to provide information
about the directory location, dump file and the staging schema name
We use the awrload.sql command to load an AWR snapshot.
@?/rdbms/admin/awrload.sql
AWR LOAD
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This
script will load the AWR data from a dump file. The ~
~ script will
prompt users for the following information: ~
~ (1) name of directory
object ~
~ (2) name of dump file ~
~ (3) staging schema name to load
AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
While specifying the dumpfile name we need to only provide the file name
without the .dmp extension
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of
the dump file (.dmp) to load:
Export import AWR snapshots
For AWR, we have the $ORACLE_HOME/rdbms/admin/awrext.sql and awrload.sql
scripts. The docs note these directions:
To extract AWR data:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrext.sql
A list of the databases in the AWR schema is
displayed.
- Specify the database from which the AWR data
will be extracted:
Enter value for db_id: 1377863381
In this example, the database with the database
identifier of 1377863381 is selected.
- Specify the number of days for which you want to
list snapshot Ids.
Enter value for num_days: 2
A list of existing snapshots for the specified
time range is displayed. In this example, snapshots
captured in the last 2 days are displayed.
- Define the range of snapshots for which AWR data
will be extracted by specifying a beginning and
ending snapshot Id:
Enter value for begin_snap: 30
Enter value for end_snap: 40
In this example, the snapshot with a snapshot Id
of 30 is selected as the beginning snapshot, and the
snapshot with a snapshot Id of 40 is selected as the
ending snapshot.
- A list of directory objects is displayed.
Specify the directory object pointing to the
directory where the export dump file will be stored:
Enter value for directory_name: DATA_PUMP_DIR
In this example, the directory object DATA_PUMP_DIR is selected.
- Specify the prefix for name of the export dump
file (the .dmp suffix will be automatically
appended):
Enter value for file_name: awrdata_30_40
In this example, an export dump file named
awrdata_30_40 will be created in the directory
corresponding to the directory object you specified:
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\ORACLE\PRODUCT\11.1.0.5\DB_1\RDBMS\LOG\AWRDATA_30_40.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:58:20
Depending on the amount of AWR data that needs to
be extracted, the AWR extract operation may take a
while to complete. Once the dump file is created,
you can use Data Pump to transport the file to
another system.
Loading AWR Data
To load AWR data:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrload.sql
- Specify the directory object pointing to the
directory where the export dump file is located:
Enter value for directory_name: DATA_PUMP_DIR
- Specify the prefix for name of the export dump
file (the .dmp suffix will be automatically
appended):
Enter value for file_name: awrdata_30_40
- Specify the name of the staging schema where the
AWR data will be loaded:
Enter value for schema_name: AWR_STAGE
- Specify the default tablespace for the staging
schema:
Enter value for default_tablespace: SYSAUX
- Specify the temporary tablespace for the staging
schema:
Enter value for temporary_tablespace: TEMP
- A staging schema named
AWR_STAGE
will be created where the AWR data will be loaded.
After the AWR data is loaded into the AWR_STAGE schema, the data will be
transferred into the AWR tables in the SYS
schema:Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 113 CONSTRAINT objects in 11 seconds
Depending on the amount of AWR data that needs to
be loaded, the AWR load operation may take a while
to complete. After the AWR data is loaded, the
staging schema will be dropped automatically.
Using Ion for AWR and STATSPACK snapshot migration
The Ion
tool can be used to facilitate the task of moving STATSPACK data to another
database. All the DBA needs to do is specify the source and target databases and
the snapshots to be moved. Thats it! All the remaining work is performed
automatically by the Ion tool.
Configuration of moving STATSPACK data between databases in The Ion tool.
The STATSPACK utility has a documented method for moving data between different
repositories using Oracle Export/Import utilities. A special parameter file,
spuexp.par,
is delivered with STATSPACK as a sample export parameter file. However, moving
STATSPACK data between databases using the Export/Import approach is a complex
process.
While the data is being moved, the Ion tool displays a work progress dialog box
that shows the status of current process as shown in Figure 5.7 below:
Progress
of moving STATSPACK data between databases in The Ion tool.
Furthermore, The Ion tool supports the automatic movement of STATSPACK data.
This job can be scheduled to run on a regular basis for automatic execution and
the Ion tool will move data from the STATSPACK repositories to a single place
without manual intervention.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
