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
SQL> setpagesize 500  
# su - oracle 
$  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-dd hh24')||':30'
      end
ORDER BY a.snap_id;      

注意：結尾的分号不能少。

生成db-time資料語句： 

SELECT TO_CHAR(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snap_time,
  --'DB Time(s)',
  CASE
    WHEN (ROUND((a.VALUE - lag(a.VALUE, 1) OVER(ORDER BY a.snap_id)) / 1000000))<0
    THEN 0
    ELSE ROUND((a.VALUE - lag(a.VALUE, 1) OVER(ORDER BY a.snap_id)) / 1000000)
  END AS "db time(s)"
FROM
  (SELECT snap_id,
    VALUE
  FROM dba_hist_sys_time_model
  WHERE stat_name     = 'DB time'
  and dbid=&db_id
  AND INSTANCE_NUMBER = &instance_id) a,
  dba_hist_snapshot b
WHERE b.dbid=&db_id
AND a.snap_id       = b.snap_id
AND b.INSTANCE_NUMBER = &instance_id
--AND b.end_interval_time > sysdate-30
ORDER BY 1;
 
Enter value for db_id: 4292035712
Enter value for instance_id: 1  ///(單執行個體輸入1，RAC 的根據實際情況輸入相應的值)
Enter value for db_id: 4292035712
Enter value for instance_id: 1      

8.導入excel表 ，生成趨勢圖

   excel裡邊選擇資料 -導入-自文本

   選擇導入的資料-生成曲線圖-輕按兩下橫坐标選擇文本格式

9. 觀察趨勢圖高峰值，導出AWR報告，Oracle使用者下執行如下指令：

@?/rdbms/admin/awrrpti      

導出的AWR報告有兩種類型格式： .txt 和 .html

系統預設導出是.html      一般輸入時間間隔天數：7 

按照步驟，連續操作就行。

Enter value for dbid: 4292035712
Using 4292035712 for database Id
Enter value for inst_num: 1(輸入執行個體個數)
Using 1 for instance number      

例如：導出時間點為：7月1日 11點 12點 負載高峰時段的AWR資料。

注意：在導出報告時，檔案起名時要手動添加字尾名.html