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
查看更多关于Oracle AWR数据导入/导出的步骤的详细内容...