首页 > 数据库 > Oracle > Oracle AWR 数据导入/导出的步骤
$> 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 侯圣文老师
春风: http://www.dbdream.org/?p=223
eygle: AWR 与 Statspack 数据的导出与迁移 http://www.eygle.com/archives/2010/08/awr_statspack_extract.html
SQL> select dbid from v$database; 223805804 SQL> setpagesize 500 # su - oracle
$ cd awrtest/ #根据自己的安装目录确认
$ ls (查看当前目录下文件,确认相关文件名)
生成逻辑读-语句:
@?/rdbms/admin/awrrpti
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数据。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12272958/viewspace-2149318/,如需转载,请注明出处,否则将追究法律责任。