impdp报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier

发表于 2012 年 4 月 27 日 由 惜分飞

发现问题
impdp导入数据库不成功,一直在报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier错误

[oracle@back1 backup]$ impdp username/password  schemas=center_admin dumpfile=center_admin20120427.dmp 
> logfile=center_admin20120427.log directory=impdir parallel=10 job_name=center_admin08;

Import: Release 11.1.0.6.0 - 64bit Production on Friday, 27 April, 2012 21:35:06

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-31694: master table "USERNAME"."CENTER_ADMIN08" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "USERNAME"."CENTER_ADMIN08".
ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

分析问题
看到这个错误,我第一个感觉根据ORA-31694,怀疑是没有创建相关用户,或者是该用户无权限权限CENTER_ADMIN08表.等我登陆目标数据库查看时候发现该用户存在,并且已经授予了DBA权限,所以不存在是用户相关问题导致.ORA-02354错误我怀疑是expdp导出来的文件在传输过程中发生意外(如使用ftp传输未使用二进制模式),当我使用md5sum命令检查发现两边一致,证明该文件传输正常.目标端不能检查明显故障,怀疑导出文件本身存在问题检查导出文件日志

[oracle@fcdb2 backup]$ more center_admin20120427.log
;;; 
Export: Release 11.1.0.7.0 - 64bit Production on Friday, 27 April, 2012 17:32:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "USERNAME"."CENTER_ADMIN08":USERNAME/**** schemas=center_admin directory=expdir dumpfile=center_admin20120427.dmp
er_admin20120427.log parallel=10 job_name=center_admin08 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 158.2 GB

发现新大陆,两边的数据库版本不一致,源端版本是11.1.0.7,目标端版本是11.1.0.6.这个时候我对问题的定位是可能版本兼用问题(毕竟是高版本到低版本)或者是bug.先查询datapump版本之间兼容性列表

     Version      Written by     Can be imported into Target:  
   Data Pump   database with      10gR1      10gR2      11gR1      11gR2  
Dumpfile Set   compatibility   10.1.0.x   10.2.0.x   11.1.0.x   11.2.0.x
------------ --------------- ---------- ---------- ---------- ----------
         0.1          10.1.x  supported  supported  supported  supported
         1.1          10.2.x         no  supported  supported  supported
         2.1          11.1.x         no         no  supported  supported 
         3.1          11.2.x         no         no         no  supported

这里可以看出11.1.0.7和11.1.0.6之间是相互兼容的,不应该会存在上述问题,那么现在对于该问题的解释很可能是bug导致,继续查询资料发现[ID 752374.1]描述的正是该问题.

解决问题

1. Apply 11.1.0.7 Patch:6890831 on the target database.

2. Workaround this issue by re-running an expdp from the 11.1.0.7 database with an additional 
parameter VERSION=10.2. This will create a new dump file compatible to be imported into 10gR2, 
which will also import successfully into 11.1.0.6.

Unfortunately, please be aware that the new 11g specific features will not be 
exported if expdp is run from 11.1.0.7 with the parameter VERSION=10.2. 
If only normal objects and features are involved, this could be a good workaround.
发表在 逻辑备份/恢复 | 一条评论

ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated

发表于 2012 年 4 月 26 日 由 惜分飞

IMPDP导入数据发现ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated错误

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
Failing sql is:
 BEGIN DBMS_JOB.ISUBMIT( JOB=> 63, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', 
NO_PARSE=> TRUE); END;   
 
Job "GBEAS3"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 16:05:58

错误原因(该job=63已经存在数据库中)

select job, what from   dba_jobs where job=63; 

JOB     WHAT 
-----   -------- 
63      proc_xifenfei

注意:如果该job正在运行,可能需要查询DBA_JOBS_RUNNING

解决办法

1.手工创建job,指定一个不存在的job 号
declare
  m_job number;

begin
  select max (job) + 1 
  into   m_job 
  from   dba_jobs; 

BEGIN DBMS_JOB.ISUBMIT( JOB=> m_job, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 
'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', 
NO_PARSE=> TRUE); END; 
end;
/

2.删除原存在job
exec dbms_job.remove (63);

这样的情况，一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致

发表在 ORA-xxxxx | 一条评论

awr导出/导入/分析

发表于 2012 年 4 月 26 日 由 惜分飞

很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据

SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 1393262699 XIFENFEI     XIFENFEI-PC
  3753332923 FDJDB        ora1
  3753332923 FDJDB        ora2

The default database id is the local one: '1393262699'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

输入 dbid 的值:  3753332923    <--需要输入

Using 3753332923 for Database ID


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


输入 num_days 的值:  1    <--需要输入

Listing the last day's Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
FDJDB              906 23 4月  2012 00:00
                   907 23 4月  2012 01:00
                   908 23 4月  2012 02:00
                   909 23 4月  2012 03:00
                   910 23 4月  2012 04:00
                   911 23 4月  2012 05:00
                   912 23 4月  2012 06:00
                   913 23 4月  2012 07:00
                   914 23 4月  2012 08:00
                   915 23 4月  2012 09:00
                   916 23 4月  2012 10:00
                   917 23 4月  2012 11:00
                   918 23 4月  2012 12:00
                   919 23 4月  2012 13:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  906  <--需要输入
Begin Snapshot Id specified: 906

输入 end_snap 的值:  907    <--需要输入
End   Snapshot Id specified: 907


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------

DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\ord er_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml

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

输入 directory_name 的值:  DATA_PUMP_DIR  <--需要输入(注意大小写)

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_906_907.
To use this name, press <return> to continue, otherwise enter
an alternative.

输入 file_name 的值:  xifenfei_awr  <--需要输入

Using the dump file prefix: xifenfei_awr
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.log
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log
|  监控导出awr数据进度

End of AWR Extract

导入awr数据

SQL> @E:\oracle\product\11.2.0\dbhome_1\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
------------------------------ -------------------------------------------------

DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml

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

输入 directory_name 的值:  DATA_PUMP_DIR  <--需要输入(注意大小写)

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

输入 file_name 的值:  awrdat_751_919 <--需要输入(文件后缀名一定要是.dmp)

Loading from the file name: awrdat_751_919.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 的值:  XFF_AWR  <--需要输入(临时创建用户)

Using the staging schema name: XFF_AWR

Choose the Default tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
EXAMPLE                        PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

输入 default_tablespace 的值:  EXAMPLE  <--需要输入

Using tablespace EXAMPLE as the default tablespace for the XFF_AWR


Choose the Temporary tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR 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 的值:  TEMP  <--需要输入

Using tablespace TEMP as the temporary tablespace for XFF_AWR


... Creating XFF_AWR user  (临时用户创建)

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.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:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.log
|
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log
|  监控导出awr数据进度

... Dropping XFF_AWR user  (临时用户被删除)

End of AWR Load

查看awr报告

SQL> @?/RDBMS/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:  html   <--需要输入

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  3753332923        2 FDJDB        fdjdb2       ora2
  3753332923        1 FDJDB        fdjdb1       ora1
* 1393262699        1 XIFENFEI     xff          XIFENFEI-PC

输入 dbid 的值:   3753332923  <--需要输入
Using  3753332923 for database Id
输入 inst_num 的值:  1        <--需要输入
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


输入 num_days 的值:  1  <--需要输入

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
fdjdb1       FDJDB              906 23 4月  2012 00:00     1
                                907 23 4月  2012 01:00     1
                                908 23 4月  2012 02:00     1
                                909 23 4月  2012 03:00     1
                                910 23 4月  2012 04:00     1
                                911 23 4月  2012 05:00     1
                                912 23 4月  2012 06:00     1
                                913 23 4月  2012 07:00     1
                                914 23 4月  2012 08:00     1
                                915 23 4月  2012 09:00     1
                                916 23 4月  2012 10:00     1
                                917 23 4月  2012 11:00     1
                                918 23 4月  2012 12:00     1
                                919 23 4月  2012 13:00     1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  917  <--需要输入
Begin Snapshot Id specified: 917

输入 end_snap 的值:  918    <--需要输入
End   Snapshot Id specified: 918



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_917_918.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

输入 report_name 的值:xifenfei_awr.html  <--需要输入
发表在 Oracle性能优化 | 3 条评论