Awrload導入數據報ORA-20104、ORA-06512錯誤處理

Awrload導入數據報ORA-20104、ORA-06512錯誤處理

1、Awrload導入數據報ORA-20104、ORA-06512錯誤

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

------------------------------ -------------------------------------------------

IMPDP_DIR_2 /u01/expdp_dir



Choose a Directory Name from the list above (case-sensitive).



Enter value for directory_name: IMPDP_DIR_2



Using the dump directory: IMPDP_DIR_2



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: sbd_39791_39978



Loading from the file name: sbd_39791_39978.dmp



Staging Schema to Load AWR Snapshot Data

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The next step is to create the staging schema

where the AWR snapshot data will be loaded.

After loading the data into the staging schema,

the data will be transferred into the AWR tables

in the SYS schema.



The default staging schema name is AWR_STAGE.

To use this name, press to continue, otherwise enter

an alternative.



Enter value for schema_name:



Using the staging schema name: AWR_STAGE

declare

*

ERROR at line 1:

ORA-20104: schema name 'AWR_STAGE' already exists

ORA-06512: at line 17



Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning option

2、原因分析

AWR_STAGE是awrload.sql導入AWR數據時，默認創建用於臨時存放導入數據的用戶，在數據導入完成後，ORACLE會自動將該用戶DROP掉。

既然報AWR_TAGE用戶已存在，說明以前在導入AWR數據時並未正常結束導致這種問題的出現。

3、處理方法

手工刪除AWR_STAGE用戶

SQL> drop user AWR_STAGE cascade;

User dropped.

4、重新使用awrload.sql導入

成功導入AWR數據。

SQL> @?/rdbms/admin/awrload.sql

~~~~~~~~~~

AWR LOAD

~~~~~~~~~~

Using tablespace TEMP as the temporary tablespace for AWR_STAGE





... Creating AWR_STAGE user



|

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Loading the AWR data from the following

| directory/file:

| /u01/expdp_dir

| sbd_39791_39978.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/expdp_dir

| sbd_39791_39978.log

|

... Dropping AWR_STAGE user



End of AWR Load

SQL>