ORACLE 11gR2 导入AWR 报错 ORA-20115 ORA-39126 ORA-25153 解决方法
在测试库上导入其他库的AWR 记录：
--AWR导出没有问题：
SQL> @?/rdbms/admin/awrextr.sql
--但是导入的时候，报错了：
SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
-------------------------------------------------------------------------------
BACKUP /u01/backup
DATA_PUMP_DIR /u01/app/oracle/11.2.0/db_1/rdbms/log/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/11.2.0/db_1/ccr/state
XMLDIR /u01/app/oracle/11.2.0/db_1/rdbms/xml
Choose a Directory Name from the list above(case-sensitive).
Enter value for directory_name: BACKUP
Using the dump directory: BACKUP
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file(.dmp) to load:
Enter value for file_name: awrdat_160_192
Loading from the file name:awrdat_160_192.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the stagingschema
where the AWR snapshot data will be loaded.
After loading the data into the stagingschema,
the data will be transferred into the AWRtables
in the SYS schema.
The default staging schema name isAWR_STAGE.
To use this name, press <return> tocontinue, otherwise enter
an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGEuser
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's defaulttablespace. This is the
tablespace in which the AWR data will bestaged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ ---------------------------
DAVE PERMANENT
DAVE2 PERMANENT
DAVE3 PERMANENT
DAVE4 PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressing <return> will result in therecommended default
tablespace (identified by *) being used.
Enter value for default_tablespace:
Using tablespace SYSAUX as the defaulttablespace for the AWR_STAGE
Choose the Temporary tablespace for theAWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporarytablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------------------------------
TEMP TEMPORARY *
Pressing <return> will result in thedatabase's default temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporarytablespace for AWR_STAGE
... Creating AWR_STAGE user
|
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /u01/backup
| awrdat_160_192.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:
| /u01/backup
| awrdat_160_192.log
|
Data Pump job startfailed
ORA-39002: invalidoperation
Exception encountered inAWR_LOAD
begin
*
ERROR at line 1:
ORA-20115: datapumpimport encountered error:
ORA-39002: invalidoperation
ORA-39126: Workerunexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [SELECT old_valueFROM
"SYS"."SYS_IMPORT_FULL_02"WHERE process_order = :1]
ORA-25153: TemporaryTablespace is Empty
ORA-06512: at"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at"SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
object line object
handle number name
0x8a919ab0 20462 package body SYS.KUPW$WORKER
0x8a919ab0 9028 package body SYS.KUPW$WORKER
0x8a919ab0 9831 package body SYS.KUPW$WORKER
0x8a919ab0 1775 package body SYS.KUPW$WORKER
0x8394b288 2 anonymous block
ORA-39126: Worker unexpected fatal error inKUPW$WORKER.DISPATCH_WORK_ITEMS [SELECT old_value FROM
"SYS"."SYS_IMPORT_FULL_02"WHERE process_order = :1]
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SYS_ERROR",line 95
ORA-06512: at "SYS.KUPW$WORKER",line 9001
----- PL/SQL Call Stack -----
object line object
handle number name
0x8a919ab0 20462 package body SYS.KUPW$WORKER
0x8a919ab0 9028 package body SYS.KUPW$WORKER
0x8a919ab0 9831 package body SYS.KUPW$WORKER
0x8a919ab0 1775 package body SYS.KUPW$WORKER
0x8394b288 2 anonymous block
ORA-06512: at"SYS.DBMS_SWRF_INTERNAL", line 1717
ORA-06512: at line 3
begin
*
ERROR at line 1:
ORA-20106: AWR tables do not exist for the'AWR_STAGE' user
ORA-06512: at"SYS.DBMS_SWRF_INTERNAL", line 2920
ORA-00942: table or view does not exist
ORA-06512: at line 3
... Dropping AWR_STAGE user
End of AWR Load
在MOS 上搜了一下，N多相关的bug，看了一下，都于我这里的情况不一样：
SQL> select grantee, privilege
2 from dba_tab_privs
3 where table_name='DBMS_METADATA';
PUBLIC EXECUTE
SQL>
SQL> select tablespace_name,file_namefrom dba_temp_files;
SQL>
仔细看一下错误提示，提示Temp 表空间为空：
ORA-25153: TemporaryTablespace is Empty
检查一下：
SQL> select tablespace_name,status fromdba_tablespaces;
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
DAVE ONLINE
UNDO_DAVE ONLINE
DAVE2 ONLINE
DAVE3 ONLINE
DAVE4 ONLINE
SQL>
表空间存在，但是查询数据文件：
SQL> select tablespace_name,file_namefrom dba_temp_files;
没有结果。
[oracle@dave dave]$ pwd
/u01/app/oracle/oradata/dave
[oracle@dave dave]$ ls
ANQING example.299.819454355 group_3.263.819459415 sysaux.257.816661033 thread_2_seq_11.303.819501417
anqing.297.819454405 fda1.269.819454467 group_4.261.816662239 sysaux.285.819454151 ts1.291.819454507
assm.295.819454467 fda2.270.819454491 group_4.266.819459423 system.256.816661027 ts2.288.819454533
control01.ctl fda3.293.819454493 group_5.259.816661313 system.290.819454153 undo_dave.dbf
dave01.dbf fda4.292.819454507 mssm.296.819454441 temp01.dbf undotbs1.258.816661037
dave02.dbf group_1.257.816661301 stdredo10.log temp.262.819462677 undotbs1.268.819454441
dave03.dbf group_1.286.819454681 stdredo11.log temp.264.816661353 undotbs1.dbf
dave04.dbf group_1.311.819454689 stdredo6.log temp.289.819454715 undotbs2.265.816661787
dave05.dbf group_2.294.819454693 stdredo7.log thread_1_seq_10.302.819501057 users.259.816661039
dave.298.819454401 group_2.310.819454695 stdredo8.log thread_1_seq_9.300.819500889 users.287.819454533
DAVE_ST group_3.260.816662233 stdredo9.log thread_2_seq_10.304.819501075
[oracle@dave dave]$
但实际上，物理有这个文件。 应该是之前做测试的时候，忘记做这个操作了，按照dave的习惯，在数据迁移完成后，我都会重建Temp 表空间。 这个在我们重建控制的时候也会提示我们做这个操作。
SQL> alter tablespace temp add tempfile'/u01/app/oracle/oradata/dave/temp01.dbf' size 51M reuse;
Tablespace altered.
SQL> select tablespace_name,file_namefrom dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------ ------------------------------
TEMP /u01/app/oracle/oradata/dave/temp01.dbf
再次导入AWR 快照：
这次成功：
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /u01/backup
| awrdat_160_192.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:
| /u01/backup
| awrdat_160_192.log
|
... Dropping AWR_STAGE user
End of AWR Load
SQL> select dbid, retention fromdba_hist_wr_control;
DBID RETENTION
---------- ---------------------------------------------------------------------
877621333 +40150 00:00:00.0
879543530 +00008 00:00:00.0
--------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
QQ: 251097186
Skype: tianlesoftware
Email: tianlesoftware@gmail.com
Blog: http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
