ITPub博客

首页 > 数据库 > Oracle > awr的备份和恢复

awr的备份和恢复

原创 Oracle 作者:yangzhangyue 时间:2014-08-18 16:22:34 0 删除 编辑
    在数据库迁移,搬迁,升级后,备份awr有利于搬迁,升级后的对比
--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 to continue, otherwise enter an alternative.

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 without
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 to continue, otherwise enter
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 to continue, otherwise enter
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 will result in the recommended default
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 will result in the database's default temporary
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'));


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-1253289/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 数据文件收缩
请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    219937