Dump the data of AWR with AWREXTR.SQL script

1. Create a directory for generating the dump file

SQL> create directory awr_exttest as 'C:\app\awrtest\'
;

2. Generate the AWR dump with AWREXTR.SQL as follows:

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                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 3000232999 XE           ORACLEHOST

The default database id is the local one: '3000232999'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid:
Using 3000232999 for Database ID

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
XE                 698 25 Apr 2023 00:28
                   699 25 Apr 2023 01:28
                   700 25 Apr 2023 02:28
                   701 25 Apr 2023 03:28
                   702 25 Apr 2023 04:28
                   703 25 Apr 2023 05:28
                   704 25 Apr 2023 06:28
                   705 25 Apr 2023 07:28
                   706 25 Apr 2023 08:28
                   707 25 Apr 2023 09:28
                   708 25 Apr 2023 10:28


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 700
Begin Snapshot Id specified: 700

Enter value for end_snap: 706
End Snapshot Id specified: 706

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ ------------------------------------------------
AWR_EXTTEST                    C:\app\awrtest\

DATA_PUMP_DIR                  C:\app\product\21c\admin\xe\dpdump/
DBMS_OPTIM_ADMINDIR            C:\app\product\21c\dbhomeXE/rdbms/admin
DBMS_OPTIM_LOGDIR              C:\app\product\21c\dbhomeXE/cfgtoollogs
JAVA$JOX$CUJS$DIRECTORY$       C:\APP\PRODUCT\21C\DBHOMEXE\JAVAVM\ADMIN
OPATCH_INST_DIR                C:\app\product\21c\dbhomeXE\OPatch
OPATCH_LOG_DIR                 C:\app\product\21c\homes\OraDB21Home1\rd
                               bms\log

Directory Name                 Directory Path
------------------------------ ------------------------------------------------
OPATCH_SCRIPT_DIR              C:\app\product\21c\dbhomeXE\QOpatch
ORACLECLRDIR                   C:\app\product\21c\dbhomeXE\bin\clr
ORACLE_BASE                    C:\app\product\21c
ORACLE_HOME                    C:\app\product\21c\dbhomeXE
ORACLE_OCM_CONFIG_DIR          C:\app\product\21c\homes\OraDB21Home1\cc
                               r\state

ORACLE_OCM_CONFIG_DIR2         C:\app\product\21c\homes\OraDB21Home1\cc
                               r\state


Directory Name                 Directory Path
------------------------------ ------------------------------------------------
SDO_DIR_ADMIN                  C:\app\product\21c\dbhomeXE/md/admin
XMLDIR                         C:\app\product\21c\dbhomeXE\rdbms\xml
XSDDIR                         C:\app\product\21c\dbhomeXE\rdbms\xml\sc
                               hema


Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: AWR_EXTTEST

Using the dump directory: AWR_EXTTEST

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_700_706.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: awrdat_700_706

Using the dump file prefix: awrdat_700_706
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   C:\app\awrtest\
|   awrdat_700_706.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  *** AWR Extract Started ...
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   C:\app\awrtest\
|   awrdat_700_706.log

End of AWR Extract