【故障處理】因授權資訊丟失導致IMP時出現IMP-00041錯誤的模擬與分析

secooler發表於2010-02-26
問題現象是這樣的，在IMP資料的過程中出現“IMP-00041: Warning: object created with compilation warnings”錯誤，因這個錯誤導致檢視匯入後無法使用。經分析，發現此問題與“授權資訊丟失”有關。
為避免朋友們走彎路，我模擬再現一下這個問題，同時給出一個分析和解決問題的思路。

1.建立兩個使用者sec1和sec2
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec2;

Grant succeeded.

2.在第一個使用者sec1中建立表T_SEC1
sys@ora10g> conn sec1/sec1
Connected.
sec1@ora10g> create table t_sec1 (x int);

Table created.

sec1@ora10g> insert into t_sec1 values (1);

1 row created.

3.在第二個使用者sec2中建立表T_SEC2
sec1@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> create table t_sec2 (x int);

Table created.

sec2@ora10g> insert into t_sec2 values (2);

1 row created.

4.在sec2中建立sec1使用者中t_sec1表的同名
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g> create synonym sec2.syn_t_sec1 for sec1.t_sec1;

Synonym created.

sys@ora10g> grant insert,delete,update,select on sec1.t_sec1 to sec2;

Grant succeeded.

5.在sec2使用者下建立檢視v_sec2
該檢視同時使用到剛剛在sec2使用者下建立的同名SYN_T_SEC1和表T_SEC2。
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g>
sys@ora10g> grant create view to sec2;

Grant succeeded.

sys@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> create view v_sec2 as select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2;

View created.

6.分別看一下各個使用者下的資料庫物件
sec2@ora10g> conn sec1/sec1
Connected.
sec1@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T_SEC1                         TABLE


sec2@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T_SEC2                         TABLE
SYN_T_SEC1                     SYNONYM
V_SEC2                         VIEW

7.我們這裡分別生成sec1和sec2使用者的備份
ora10g@secDB1 /exp$ exp sec1/sec1 file=sec1.dmp log=sec1.log

Export: Release 10.2.0.3.0 - Production on Fri Feb 26 03:01:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC1
About to export SEC1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC1's tables via Conventional Path ...
. . exporting table                         T_SEC1          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ora10g@secDB1 /exp$ exp sec2/sec2 file=sec2.dmp log=sec2.log

Export: Release 10.2.0.3.0 - Production on Fri Feb 26 03:01:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC2
About to export SEC2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC2's tables via Conventional Path ...
. . exporting table                         T_SEC2          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

8.將備份檔案傳送到待匯入的伺服器secDB2
ora10g@secDB1 /exp$ scp sec1.dmp sec2.dmp 172.17.193.201:/imp
oracle@172.17.193.201's password:
sec1.dmp  100%   16KB  16.0KB/s   00:00
sec2.dmp  100%   16KB  16.0KB/s   00:00

9.在secDB2伺服器上建立同樣的使用者，並進行匯入測試
1）建立使用者並授權
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource,dba to sec2;

Grant succeeded.

2）匯入測試
（1）先匯入sec1使用者
ora10g@secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 11:58:09 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
. . importing table                       "T_SEC1"          1 rows imported
Import terminated successfully without warnings.

（2）匯入sec2使用者
ora10g@secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:14:59 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
Import terminated successfully without warnings.

此時是匯入成功的！演示還未結束，請繼續。

（3）假如此時刪除sec2使用者（刪除的目的可能有很多，比如之前授予的許可權不足等），對其進行重新建立後再完成資料匯入
sys@ora10g> drop user sec2 cascade;

User dropped.

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource,dba to sec2;

Grant succeeded.

（4）再重新完成sec2使用者的IMP匯入
ora10g@secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:20:48 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "SEC2"."V_SEC2"                            ("SEC1_X","SEC"
 "2_X") AS "
 "select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2"
Import terminated successfully with warnings.

此時，問題出現了，此時的“IMP-00041”錯誤提示內容是建立的檢視存在編譯錯誤（這個錯誤沒有太大的指導意義）。

10.驗證被匯入的內容是否可用
1）匯入了三個物件，沒有問題。
sec2@ora10g> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
SYN_T_SEC1                     SYNONYM
T_SEC2                         TABLE
V_SEC2                         VIEW

2）T_SEC2表可用
sec2@ora10g> select * from T_SEC2;

         X
----------
         2

3）SYN_T_SEC1同名可用
sec2@ora10g> select * from SYN_T_SEC1;

         X
----------
         1

4）此時檢視不可用，提示存在錯誤。原因不詳細。
sec2@ora10g> select * from V_SEC2;
select * from V_SEC2
              *
ERROR at line 1:
ORA-04063: view "SEC2.V_SEC2" has errors

5）嘗試重新編譯，無效。
sec2@ora10g> alter view V_SEC2 compile;

Warning: View altered with compilation errors.

6）沒有具體的錯誤提示資訊
sec2@ora10g> show errors;
No errors.

11.問題原因
根本原因在於，當刪除sec2使用者重新建立後，sec2使用者原來具有的sec1使用者下T_SEC1表授權資訊丟失了。
不要著急，我們來分析一下。
在完成sec1使用者匯入後，其實sec1使用者的dmp檔案中包含的授權資訊已經完成對sec2使用者的授權。我們使用“show=y”選項檢視一下sec1使用者的dmp檔案內容。
ora10g@secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y show=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:24:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'ORA10G', inst_scn=>'36699433');"
 "COMMIT; END;"
 "CREATE TABLE "T_SEC1" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 M"
 "AXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"
 " DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T_SEC1"

 "GRANT DELETE ON "T_SEC1" TO "SEC2""
 "GRANT INSERT ON "T_SEC1" TO "SEC2""
 "GRANT SELECT ON "T_SEC1" TO "SEC2""
 "GRANT UPDATE ON "T_SEC1" TO "SEC2""
Import terminated successfully without warnings.

注意最後四行的授權資訊。這些授權資訊是在sec1使用者資料匯入過程中同時完成的。

12.問題處理
既然知道了問題的出處，處理就簡單了。以sys使用者顯示的將sec1使用者下t_sec1表的操作許可權授予sec2使用者，然後再重新對問題檢視進行編譯（當然，重新建立這個檢視亦可）。
sec2@ora10g> conn / as sysdba
Connected.
sys@ora10g> grant insert,delete,update,select on sec1.t_sec1 to sec2;

Grant succeeded.

sys@ora10g> conn sec2/sec2
Connected.
sec2@ora10g> alter view V_SEC2 compile;

View altered.

sec2@ora10g> select * from V_SEC2;

    SEC1_X     SEC2_X
---------- ----------
         1          2

1 row selected.

OK，問題到此處理完畢。

13.另外一種導致這個問題的場景演示
還用一種可能出現這種問題的可能性，如果是按照下面的順序在secDB2伺服器上完成使用者的建立和匯入，一樣會報上面的錯誤。
建立sec1使用者，完成對sec1使用者的匯入；
建立sec2使用者，完成對sec2使用者的匯入。

因為在完成sec1使用者匯入後，授權資訊的授予物件sec2還不存在！

為保證資訊的完整性和正確性，贅述在此。
1）刪除sec1和sec2使用者
sys@ora10g> drop user sec1 cascade;

User dropped.

sys@ora10g> drop user sec2 cascade;

User dropped.

2）建立sec1使用者，完成對sec1使用者的匯入
sys@ora10g> create user sec1 identified by sec1 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource to sec1;

Grant succeeded.

sys@ora10g> exit

ora10g@secDB2 /imp$ imp sec1/sec1 file=sec1.dmp log=sec1.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:47:11 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC1's objects into SEC1
. . importing table                       "T_SEC1"          1 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT DELETE ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT INSERT ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT UPDATE ON "T_SEC1" TO "SEC2""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SEC2' does not exist
Import terminated successfully with warnings.

此處的授權資訊執行失敗的提示資訊已經說明了問題。

3）建立sec2使用者，完成對sec2使用者的匯入
ora10g@secDB2 /imp$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 26 21:47:43 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

sys@ora10g> create user sec2 identified by sec2 default tablespace TBS_SEC_D;

User created.

sys@ora10g> grant connect,resource,dba to sec2;

Grant succeeded.

sys@ora10g> exit

ora10g@secDB2 /imp$ imp sec2/sec2 file=sec2.dmp log=sec2.log ignore=y full=y

Import: Release 10.2.0.3.0 - Production on Fri Feb 26 21:48:17 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC2's objects into SEC2
. . importing table                       "T_SEC2"          1 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "SEC2"."V_SEC2"                            ("SEC1_X","SEC"
 "2_X") AS "
 "select SYN_T_SEC1.x sec1_x, T_SEC2.x sec2_x from SYN_T_SEC1,T_SEC2"
Import terminated successfully with warnings.

問題又一次再現，錯誤原因與我們前面分析的結果相同。

14.小結
我們使用EXP/IMP工具在不同使用者間存在較複雜的授權關係的情況下完成資料遷移時，需要特別注意他們的先後順序。
為避免此類錯誤的發生，建議在多使用者資料遷移場景下，使用sys使用者一次性完成（使用OWNER引數）資料遷移工作。

Good luck.

secooler
10.02.26

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-628019/，如需轉載，請註明出處，否則將追究法律責任。

相關文章