导入中断导致AWR报告内容为空

导入AWR报告后,命令行迟迟不返回成功的提示,但是log中已经显示成功了,故手动中断命令行。生成AWR报告时可看到DBID,但是生成的报告为空:

 

SQL> @?/rdbms/admin/awrrpti 
输入 report_type 的值: 
Type Specified:  html 
Instances in this Workload Repository schema 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  DB Id     Inst Num DB Name      Instance     Host 
------------ -------- ------------ ------------ ------------ 
  1656146280        1 ACSCNPRD     acscnprd     CNPEKS024 
* 1276130704        1 ORCL         orcl         MARSHALL-PC 
输入 dbid 的值:  1656146280 
Using 1656146280 for database Id 
输入 inst_num 的值:  1 
Using 1 for instance number 
输入 num_days 的值:  1 
Listing the last day's Completed Snapshots 
                                                     Snap 
Instance     DB Name        Snap Id    Snap Started    Level 
------------ ------------ --------- ------------------ ----- 
acscnprd     ACSCNPRD         39147 17 8月  2011 00:00     1 
                              39160 17 8月  2011 13:00     1 
                              39161 17 8月  2011 14:00     1 
                              39162 17 8月  2011 15:00     1 
                              39163 17 8月  2011 16:00     1 
                              39164 17 8月  2011 17:00     1 
                              39165 17 8月  2011 18:00     1 
                              39166 17 8月  2011 19:00     1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
输入 begin_snap 的值:  39165 
Begin Snapshot Id specified: 39165 
输入 end_snap 的值:  39166 
End   Snapshot Id specified: 39166 
Specify the Report Name 
~~~~~~~~~~~~~~~~~~~~~~~ 
The default report file name is awrrpt_1_39165_39166.html.  To use this name,press <return> to ontinue, otherwise enter an alternative. 
输入 report_name 的值: 
Using the report name awrrpt_1_39165_39166.html

 

 

导出日志中,从SYS中的表导出日志:

Starting "SYS"."SYS_EXPORT_TABLE_01":  
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 2.930 GB 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX 
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 
. . exported "SYS"."WRH$_SQL_PLAN"                       1.660 GB 4066194 rows 
. . exported "SYS"."WRH$_SQLTEXT"                        344.6 MB  164756 rows 
. . exported "SYS"."WRH$_SQL_BIND_METADATA"              79.22 MB 1297824 rows

但是导入日志确是导入AWR_STAGE schema。

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_FULL_03" 
启动 "SYS"."SYS_IMPORT_FULL_03":  
处理对象类型 TABLE_EXPORT/TABLE/TABLE 
     已完成 98 TABLE 个对象, 用时 6 秒 
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA 
. . 导入了 "AWR_STAGE"."WRH$_SQL_PLAN"                 1.660 GB 4066194 行 
. . 导入了 "AWR_STAGE"."WRH$_SQLTEXT"                  344.6 MB  164756 行 
. . 导入了 "AWR_STAGE"."WRH$_SQL_BIND_METADATA"        79.22 MB 1297824 行

经过与老杨沟通,AWR_STAGE下的这些表只是中间表,导入显示完成时,仅导入到这些中间表去了,导入后还要将数据复制到SYS下。如果异常中断,AWR_STAGE下的内容应该如何处理?

若AWR_STAGE中的数据没有copy到SYS下,再导入时将提示如下错误:

 

输入 schema_name 的值:
Using the staging schema name: AWR_STAGE 
declare 
* 
第 1 行出现错误: 
ORA-20104: schema name 'AWR_STAGE' already exists 
ORA-06512: 在 line 17
需要级联删除AWR_STAGE用户:
SQL> DROP USER AWR_STAGE 
  2  ; 
DROP USER AWR_STAGE 
* 
第 1 行出现错误: 
ORA-01922: 必须指定 CASCADE 以删除 'AWR_STAGE'
SQL> DROP USER AWR_STAGE CASCADE;
用户已删除。
再导入就可以了:
SQL> @?/rdbms/admin/awrload 
~~~~~~~~~~ 
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 
------------------------------ ------------------------------------------------- 
DATA_FILE_DIR                  H:\app\Marshall\product\11.2.0\dbhome_1\demo\sche 
                               ma\sales_history\
DATA_PUMP_DIR                  H:\app\Marshall\admin\orcl\dpdump\ 
LOG_FILE_DIR                   H:\app\Marshall\product\11.2.0\dbhome_1\demo\sche 
                               ma\log\
MEDIA_DIR                      H:\app\Marshall\product\11.2.0\dbhome_1\demo\sche 
                               ma\product_media\
ORACLE_OCM_CONFIG_DIR          H:\app\Marshall\product\11.2.0\dbhome_1\ccr\state
Directory Name                 Directory Path 
------------------------------ ------------------------------------------------- 
SS_OE_XMLDIR                   H:\app\Marshall\product\11.2.0\dbhome_1\demo\sche 
                               ma\order_entry\
SUBDIR                         H:\app\Marshall\product\11.2.0\dbhome_1\demo\sche 
                               ma\order_entry\/2002/Sep
XMLDIR                         c:\ade\aime_dadvfm0254\oracle\rdbms\xml
Choose a Directory Name from the list above (case-sensitive).
输入 directory_name 的值:  LOG_FILE_DIR
Using the dump directory: LOG_FILE_DIR
Specify the Name of the Dump File to Load 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Please specify the prefix of the dump file (.dmp) to load:
输入 file_name 的值:  AWRDAT_38811_39166
Loading from the file name: AWRDAT_38811_39166.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 <return> to continue, otherwise enter 
an alternative.
输入 schema_name 的值:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Choose the AWR_STAGE users's default tablespace.  This is the 
tablespace in which the AWR data will be staged.
TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE 
------------------------------ --------- ------------------ 
EXAMPLE                        PERMANENT 
MARSHALL                       PERMANENT 
PERFSTAT                       PERMANENT 
SYSAUX                         PERMANENT * 
USERS                          PERMANENT
Pressing <return> will result in the recommended default 
tablespace (identified by *) being used.
输入 default_tablespace 的值:
Using tablespace SYSAUX as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME                CONTENTS  DEFAULT TEMP TABLESPACE 
------------------------------ --------- ----------------------- 
TEMP                           TEMPORARY *
Pressing <return> will result in the database's default temporary 
tablespace (identified by *) being used.
输入 temporary_tablespace 的值:
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
| 
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
|  Loading the AWR data from the following 
|  directory/file: 
|   H:\app\Marshall\product\11.2.0\dbhome_1\demo\schema\log\ 
|   AWRDAT_38811_39166.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: 
|   H:\app\Marshall\product\11.2.0\dbhome_1\demo\schema\log\ 
|   AWRDAT_38811_39166.log 
|

 

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress