--awr备份
11:13:00 sys@vposdb> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~
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
------------ ------------ ------------
* 3342645735 VPOSDB testdb
* 3342645735 VPOSDB testdb2
* 3342645735 VPOSDB testdb1
* 3342645735 VPOSDB testdb2
Elapsed: 00:00:00.00
The default database id is the local one: '3342645735'. To use this
database id, press
Enter value for dbid: 3342645735
Using 3342645735 for Database ID
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
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
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Elapsed: 00:00:00.00
DB Name Snap Id Snap Started
------------ --------- ------------------
TESTDB 132548 18 Aug 2014 00:00
132549 18 Aug 2014 00:15
132550 18 Aug 2014 00:30
132551 18 Aug 2014 00:45
132552 18 Aug 2014 01:00
132553 18 Aug 2014 01:15
132554 18 Aug 2014 01:30
132555 18 Aug 2014 01:45
132556 18 Aug 2014 02:00
132557 18 Aug 2014 02:15
132558 18 Aug 2014 02:30
132559 18 Aug 2014 02:45
132560 18 Aug 2014 03:00
132561 18 Aug 2014 03:15
132562 18 Aug 2014 03:30
132563 18 Aug 2014 03:45
132564 18 Aug 2014 04:00
132565 18 Aug 2014 04:15
132566 18 Aug 2014 04:30
132567 18 Aug 2014 04:45
132568 18 Aug 2014 05:00
132569 18 Aug 2014 05:15
132570 18 Aug 2014 05:30
132571 18 Aug 2014 05:45
132572 18 Aug 2014 06:00
132573 18 Aug 2014 06:15
132574 18 Aug 2014 06:30
132575 18 Aug 2014 06:45
132576 18 Aug 2014 07:00
132577 18 Aug 2014 07:15
132578 18 Aug 2014 07:30
132579 18 Aug 2014 07:45
132580 18 Aug 2014 08:00
132581 18 Aug 2014 08:15
132582 18 Aug 2014 08:30
132583 18 Aug 2014 08:45
132584 18 Aug 2014 09:00
132585 18 Aug 2014 09:15
132586 18 Aug 2014 09:30
132587 18 Aug 2014 09:45
132588 18 Aug 2014 10:00
132589 18 Aug 2014 10:15
132590 18 Aug 2014 10:30
132591 18 Aug 2014 10:45
132592 18 Aug 2014 11:00
132593 18 Aug 2014 11:15
Elapsed: 00:00:00.01
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 132592
Begin Snapshot Id specified: 132592
Enter value for end_snap: 132593
End Snapshot Id specified: 132593
Elapsed: 00:00:00.00
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
ADMIN_DIR /u01/app/oracle/product/10.2.0/db/md/admin
DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db/rdbms/log/
DMP /u02/david
FILEDIR /home/david/log
MAILDIR /opt/david/log
WORK_DIR /u01/app/oracle/product/10.2.0/db/work
Elapsed: 00:00:00.00
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: DMP
Using the dump directory: DMP
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_132592_132593.
To use this name, press
an alternative.
Enter value for file_name: awrdat_132592_132593
Using the dump file prefix: awrdat_132592_132593
Elapsed: 00:00:00.00
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /u08/david
| awrdat_132592_132593.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:
| /u08/david
| awrdat_132592_132593.log
|
Elapsed: 00:00:00.02
--AWR导入
[11:39:35 oracle@vposdgl david]$ sql
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 18 11:39:45 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~
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 ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
AUTOPART_DIR /u08/exp_dmp
DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db/rdbms/log/
DMP /u08/david
DMPDIR /u01/wangwei
FILEDIR /home/dm/log
MAILDIR /opt/monitor/log
MONITOR_TMP /tmp
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/10.2.0/db/ccr/hosts/vposd
b.99bill.com/state
WORK_DIR /u01/app/oracle/product/10.2.0/db/work
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DMP
Using the dump directory: DMP
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_132592_132593
Loading from the file name: awrdat_132592_132593.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press
an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------- ------------------
DAVID PERMANENT
Pressing
tablespace (identified by *) being used.
Enter value for default_tablespace: DAVID
Using tablespace DAVID as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP TEMPORARY *
Pressing
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /u08/david
| awrdat_132592_132593.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /u08/david
| awrdat_132592_132593.log
--导出awr
@?/rdbms/admin/awrrpti.sql
也可以通过查询WRM$_SNAPSHOT , WRH$_SQLSTAT查询相关sql的信息
select DBID,SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan
where SQL_ID='9xwr3drcv7cmd'
group by DBID,SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')
order by TIMESTAMP;
DBID SQL_ID PLAN_HASH_VALUE TIMESTAMP
---------- ------------- --------------- -------------------
1339788372 0bggjz0dw8uxx 3258046031 2012-06-17 19:52:33
1339788372 0bggjz0dw8uxx 1366142716 2012-11-25 20:12:34
1339788372 0bggjz0dw8uxx 1555622649 2012-12-13 04:12:30
3 rows selected
Executed in 0.094 seconds
select * from table(dbms_xplan.DISPLAY_AWR('0bggjz0dw8uxx','3258046031','1339788372','ALL'));
select * from table(dbms_xplan.DISPLAY_AWR(sql_id,PLAN_HASH_VALUE,DBID,'ALL'));