oracle 导入awr dump,ORACLE11gR2导入AWR报错ORA-20115ORA-39126ORA-25153解决方法-Oracle

在测试库上导入其他库的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 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 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 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