当前位置:数据库 > Oracle >>

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
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,