Oracle AWR如何实现数据导入/导出

2025-03-10 技术教程

这篇文章主要介绍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/assysdbaSQL>startup;

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

SQL>createdirectoryAWRTESTas'/home/oracle/awrtest'；

6.加载数据文件：

SQL>@?/rdbms/admin/awrload.sqlSQL>Entervaluefordirectory_name:AWRTEST////注意：输入directoryname时，字母需要大写Entervalueforfile_name:AWR_10107///注意：此处不要加文件后缀名Entervalueforschema_name:AWR_STAGE//(一般默认就行)Entervaluefordefault_tablespace:USERSEntervaluefortemporary_tablespace://（回车默认）

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

...CreatingAWR_STAGEuser|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|LoadingtheAWRdatafromthefollowing|directory/file:|/home/oracle/awr|AWRDAT_9239_9394.dmp|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~||***AWRLoadStarted...||Thisoperationwilltakeafewmoments.The|progressoftheAWRloadoperationcanbe|monitoredinthefollowingdirectory/file:|/home/oracle/awr|AWRDAT_9239_9394.log|...DroppingAWR_STAGEuserEndofAWRLoad----------------------------------------------------------------------------------

报错症状：（注意文件夹复权的问题！）

[root@localhost~]#cd/home/oracle/[root@localhostoracle]#ls-lttotal660drwxr-xr-x2rootroot4096Aug602:07awrdrwxr-xr-x2rootroot4096Aug602:04awrtest2-rw-r--r--1oracleoinstall326088Jul822:13zhongxing.html-rw-r--r--1oracleoinstall311525Jul112:48HUAYOUSHIJI.htmldrwxr-xr-x3oracleoinstall4096Jul111:32awrtest1drwxr-xr-x2oracleoinstall4096Jul101:10awrtestdrwxr-xr-x2oracleoinstall4096Jun3012:47jishubudrwxr-xr-x3oracleoinstall4096Jun2214:58Desktop[root@localhostoracle]#chmod-R777awr[root@localhostoracle]#ls-lttotal660drwxrwxrwx2rootroot4096Aug602:07awrdrwxr-xr-x2rootroot4096Aug602:04awrtest2-rw-r--r--1oracleoinstall326088Jul822:13zhongxing.html-rw-r--r--1oracleoinstall311525Jul112:48HUAYOUSHIJI.htmldrwxr-xr-x3oracleoinstall4096Jul111:32awrtest1drwxr-xr-x2oracleoinstall4096Jul101:10awrtestdrwxr-xr-x2oracleoinstall4096Jun3012:47jishubudrwxr-xr-x3oracleoinstall4096Jun2214:58Desktop[root@localhostoracle]#

其他报错问题解决参考地址：

http://space.itpub.net/12129601/viewspace-735524http://www.xifenfei.com/3966.html惜分飞http://blog.163.com/jet_it_life/blog/static/205097083201262431444951/directory创建不正确导致的ORA-39070http://www.douban.com/note/37656300/oracleimpdp/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>selectdbidfromv$database;223805804SQL>setpagesize500#su-oracle$cdawrtest/#根据自己的安装目录确认$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, 'hh34'),'yyyy-mm-dd hh34:mi')
else to_char(c.end_interval_time, 'yyyy-mm-dd hh34')||':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, 'hh34'),'yyyy-mm-dd hh34:mi')
else to_char(c.end_interval_time, 'yyyy-mm-dd hh34')||':30'
end
ORDER BY a.snap_id;

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

生成db-time数据语句：

SELECT TO_CHAR(b.end_interval_time, 'yyyy-mm-dd hh34: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