Oracle AWR数据导入/导出的步骤

Oracle AWR数据导入/导出的步骤
 

LINUX状态下，连接oracle用户：su - oracle

1.上传采集快照.dmp文件至服务器  (dbid:4292035712)  919219826

2.在服务器端创建目录 (即文件夹awrtest)

  $> mkdir /home/oracle/awrtest 

3.把第一步中上传的.dmp文件，移动到第二步中创建的文件夹中：

  $> cp /var/ftp/AWR_10107.dmp(如果原文件后缀名是.DMP一定要改成小写.dmp) /home/oracle/awrtest 

4.切换至sqlplus状态,并且启动数据库：

     $> sqlplus / as sysdba 

SQL>startup;

5. 创建数据库目录便于数据库查找

   SQL> create directory AWRTEST as '/home/oracle/awrtest'； 

6.加载数据文件：

    SQL> @?/rdbms/admin/awrload.sql

    SQL>Enter value for directory_name: AWRTEST     ////注意：输入directory name 时，字母需要大写 

    Enter value for file_name: AWR_10107       ///注意：此处不要加文件后缀名

    Enter value for schema_name: AWR_STAGE   (一般默认就行)

    Enter value for default_tablespace: USERS 

    Enter value for temporary_tablespace: （回车默认）

 

 

  出现一下文字，就说明导入成功了！

... Creating AWR_STAGE user

 

|

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

|  Loading the AWR data from the following

|  directory/file:

|   /home/oracle/awr

|   AWRDAT_9239_9394.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:

|   /home/oracle/awr

|   AWRDAT_9239_9394.log

|

... Dropping AWR_STAGE user

 

End of AWR Load

 ----------------------------------------------------------------------------------

报错症状：

 

以下是纠结了哥们一个上午的问题，给文件夹复权的问题！

[root@localhost ~]# cd /home/oracle/

[root@localhost oracle]# ls -lt

total 660

drwxr-xr-x 2 root   root       4096 Aug  6 02:07 awr

drwxr-xr-x 2 root   root       4096 Aug  6 02:04 awrtest2

-rw-r--r-- 1 oracle oinstall 326088 Jul  8 22:13 zhongxing.html

-rw-r--r-- 1 oracle oinstall 311525 Jul  1 12:48 HUAYOUSHIJI.html

drwxr-xr-x 3 oracle oinstall   4096 Jul  1 11:32 awrtest1

drwxr-xr-x 2 oracle oinstall   4096 Jul  1 01:10 awrtest

drwxr-xr-x 2 oracle oinstall   4096 Jun 30 12:47 jishubu

drwxr-xr-x 3 oracle oinstall   4096 Jun 22 14:58 Desktop

[root@localhost oracle]# chmod -R 777 awr

[root@localhost oracle]# ls -lt

total 660

drwxrwxrwx 2 root   root       4096 Aug  6 02:07 awr

drwxr-xr-x 2 root   root       4096 Aug  6 02:04 awrtest2

-rw-r--r-- 1 oracle oinstall 326088 Jul  8 22:13 zhongxing.html

-rw-r--r-- 1 oracle oinstall 311525 Jul  1 12:48 HUAYOUSHIJI.html

drwxr-xr-x 3 oracle oinstall   4096 Jul  1 11:32 awrtest1

drwxr-xr-x 2 oracle oinstall   4096 Jul  1 01:10 awrtest

drwxr-xr-x 2 oracle oinstall   4096 Jun 30 12:47 jishubu

drwxr-xr-x 3 oracle oinstall   4096 Jun 22 14:58 Desktop

[root@localhost oracle]#

 

 

其他报错问题解决地址：

    http://space.itpub.net/12129601/viewspace-735524

    http://www.xifenfei.com/3966.html   惜分飞   

    http://blog.163.com/jet_it_life/blog/static/205097083201262431444951/directory  创建不正确导致的ORA-39070

    http://www.douban.com/note/37656300/                      oracle impdp/expdp 的权限问题

    http://space.itpub.net/519536/viewspace-664406    侯圣文老师

 

AWR数据迁移参考地址：

     春风：http://www.dbdream.org/?p=223

      eygle: AWR 与 Statspack 数据的导出与迁移

                  http://www.eygle.com/archives/2010/08/awr_statspack_extract.html

-----------------------------------------------------------------------------------

   

 

7.执行相关脚本：（如逻辑读脚本、db-time脚本等）

   [查看dbid:

       SQL> select dbid from v$database;

      223805804

     setpagesize 500   

    $  cd awrtest/

    ls (查看当前文件名字)

              

               

生成逻辑读-语句： 

SELECT case when to_number(to_char(c.end_interval_time, 'mi'))<15 or to_number(to_char(c.end_interval_time, 'mi'))>=45

                then to_char(round(c.end_interval_time, 'hh24'),'yyyy-mm-dd hh24:mi')

else to_char(c.end_interval_time, 'yyyy-mm-dd hh24')||':30'

      end end_time,

      case when max(decode(a.instance_number, 1, a.value - b.value, null))<0 then null

           else max(decode(a.instance_number, 1, a.value - b.value, null))

      end INST1,

      case when max(decode(a.instance_number, 2, a.value - b.value, null))<0 then null

           else max(decode(a.instance_number, 2, a.value - b.value, null))

      end INST2,

      case when max(decode(a.instance_number, 3, a.value - b.value, null))<0 then null

           else max(decode(a.instance_number, 3, a.value - b.value, null))

      end INST3,

      case when max(decode(a.instance_number, 4, a.value - b.value, null))<0 then null

           else max(decode(a.instance_number, 4, a.value - b.value, null))

      end INST4

FROM sys.wrh$_sysstat a, sys.wrh$_sysstat b, sys.wrm$_snapshot c

WHERE a.stat_id =3143187968

  AND b.stat_id = a.stat_id

  AND a.snap_id = b.snap_id + 1

  AND a.snap_id = c.snap_id

  and a.dbid =&dbid

  and b.dbid = a.dbid

  and c.dbid = a.dbid

  and a.instance_number = b.instance_number

  and a.instance_number = c.instance_number

group by a.snap_id, case when to_number(to_char(c.end_interval_time, 'mi'))<15 or to_number(to_char(c.end_interval_time, 'mi'))>=45

                then to_char(round(c.end_interval_time, 'hh24'),'yyyy-mm-dd hh24:mi')

else to_char(c.end_interval_time, 'yyyy-mm