【問題處理】Windows環境下exp備份資料ORA-00904錯誤處理一例
在Windows7的Oracle客戶端使用exp工具備份伺服器資料遭遇“ORA-00904: "POLTYP": invalid identifier”報錯。
1.使用exp工具備份過程中的報錯資訊如下
EXP-00008: ORACLE error 904 encountered
ORA-00904: "POLTYP": invalid identifier
EXP-00000: Export terminated unsuccessfully
2.問題原因
這個問題歸結於Oracle Bug，Bug號是7568350。
Since there was no code added in export to extract the RLS policy type, the fix for Bug 7568350 introduces
a new column in EXU9RLS view that is associated with the rls policy type.
Before installing the Patch 7568350, the EXU9RLS view definition was:
desc exu9rls Name Null? Type
-----------------------------------------
OBJOWN NOT NULL VARCHAR2(30)
OBJNAM NOT NULL VARCHAR2(30)
POLGRP NOT NULL VARCHAR2(30)
POLICY NOT NULL VARCHAR2(30)
POLOWN NOT NULL VARCHAR2(30)
POLSCH VARCHAR2(30)
POLFUN NOT NULL VARCHAR2(30)
STMT VARCHAR2(28)
CHKOPT NOT NULL NUMBER
ENABLED NOT NULL NUMBER
SPOLICY NUMBER
After installing the Patch 7568350, the EXU9RLS view has a new column added:
desc exu9rls Name Null? Type
-----------------------------------------
OBJOWN NOT NULL VARCHAR2(30)
OBJNAM NOT NULL VARCHAR2(30)
POLGRP NOT NULL VARCHAR2(30)
POLICY NOT NULL VARCHAR2(30)
POLOWN NOT NULL VARCHAR2(30)
POLSCH VARCHAR2(30)
POLFUN NOT NULL VARCHAR2(30)
STMT VARCHAR2(28)
CHKOPT NOT NULL NUMBER
ENABLED NOT NULL NUMBER
SPOLICY NUMBER
POLTYP VARCHAR2(33)
POLTYP column contains the RLS policy type.
So, this problem was introduced due to a change in the Patch 7568350, where the EXU9RLS view definition changed.
3.處理方法
我這裡沒有按照Bug說明的方法處理。而是在Windows7作業系統上重新安裝了Vista版本的Oracle 10.2.0.3版本軟體解決的問題。供大家參考。
4.Bug 7568350說明
Bug 7568350: POLICY_TYPE CHANGED FROM CONTEXT_SENSITIVE TO DYNAMIC AFTER IMPORTING WITH IMP
Show Bug Attributes Bug Attributes
Type B - Defect Fixed in Product Version 11.2
Severity 2 - Severe Loss of Service Product Version 10.2.0.3
Status 80 - Development to Q/A Platform. 912 - Microsoft Windows (32-bit)
Created 13-Nov-2008 Platform. Version -
Updated 24-Jun-2011 Base Bug -
Database Version 10.2.0.3
Affects Platforms Generic
Product Source Oracle
Show Related Products Related Products
Line Oracle Database Products Family Oracle Database
Area Oracle Database Product 5 - Oracle Server - Enterprise Edition
Hdr: 7568350 10.2.0.3 RDBMS 10.2.0.3 EXPORT PRODID-5 PORTID-912
Abstract: POLICY_TYPE CHANGED FROM CONTEXT_SENSITIVE TO DYNAMIC AFTER IMPORTING WITH IMP
*** 11/13/08 07:16 am ***
TAR:
----
7182827.994
PROBLEM:
--------
When a table which is protected by a RLS policy is exported from database and
is imported in another using the classic export and import utility, the
policy type is getting
changed to dynamic from context sensitive.
DIAGNOSTIC ANALYSIS:
--------------------
The following is a simple test case which reproduces the issue :
The test is done on 10.2 windows machine.
SQL> conn / as sysdba
SQL>create user MYSOURCE identified by MYSOURCE;
SQL>create user MYIMP identified by MYIMP;
SQL>grant create session to MYSOURCE,MYIMP;
SQL>GRANT CREATE TABLE TO MYSOURCE,MYIMP;
SQL>GRANT EXECUTE ON DBMS_RLS TO MYSOURCE,MYIMP;
SQL>GRANT UNLIMITED TABLESPACE TO MYSOURCE,MYIMP;
SQL>CONN MYSOURCE/MYSOURCE
SQL>CREATE TABLE TEST(SSN NUMBER);
SQL>begin
dbms_rls.add_policy
(object_name => 'TEST',
policy_name => 'TEST_POLICY',
policy_function => 'SSN>1',
policy_type => dbms_rls.context_sensitive);
end;
/
SQL> SELECT policy_type,OBJECT_NAME,OBJECT_OWNER FROM DBA_POLICIES A WHERE
A.object_owner IN ('MYIMP' ,'MYSOURCE');
POLICY_TYPE OBJECT_NAME OBJECT_OWNER
-----------------------------------------------------
CONTEXT_SENSITIVE TEST MYSOURCE
Now export the schem using the below command :
>exp mysource/mysource FILE=D:\exp_mysource_as_mysource.dmp
LOG=D:\exp_mysource_as_mysource.log OBJECT_CONSISTENT=y
Import it using the below command :
>imp system/oracle commit=y compile=y GRANTS=y
FILE=D:\exp_mysource_as_mysource.dmp LOG=D:\imp_mysource_as_mysource.log
FROMUSER=MYSOURCE TOUSER=MYIMP
SQL>conn / as sysbda
SQL> SELECT policy_type,OBJECT_NAME,OBJECT_OWNER FROM DBA_POLICIES A WHERE
A.object_owner IN ('MYIMP' ,'MYSOURCE');
POLICY_TYPE OBJECT_NAME OBJECT_OWNER
--------------------------------------------------
DYNAMIC TEST MYIMP <
change
CONTEXT_SENSITIVE TEST MYSOURCE
WORKAROUND:
-----------
none
RELATED BUGS:
-------------
REPRODUCIBILITY:
----------------
TEST CASE:
----------
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 11/13/08 07:16 am *** (CHG: Sta->16)
*** 11/13/08 07:51 am ***
*** 11/13/08 08:07 am *** (CHG: Sta->10)
*** 11/13/08 08:07 am ***
*** 11/17/08 02:36 am *** (CHG: Sta->16)
*** 11/17/08 02:36 am ***
*** 11/17/08 07:27 am ***
*** 11/18/08 06:41 am ***
*** 11/18/08 06:55 am *** (CHG: Sta->10)
*** 11/18/08 06:55 am ***
*** 11/18/08 06:59 pm *** (CHG: Sta->16)
*** 11/18/08 06:59 pm ***
*** 11/19/08 07:10 am ***
*** 11/19/08 07:59 am *** (CHG: Sta->11 SubComp->EXPORT)
*** 11/19/08 07:59 am ***
*** 11/19/08 07:59 am ***
*** 11/19/08 07:59 am ***
*** 11/20/08 10:31 pm ***
*** 11/20/08 10:31 pm ***
*** 11/20/08 10:31 pm ***
*** 11/20/08 10:31 pm ***
*** 11/26/08 07:01 pm ***
RELEASE NOTES:
]]RLS policy is getting changed to dynamic from context sensitive after export/
]]import.
REDISCOVERY INFORMATION:
If RLS policy is getting changed to dynamic from context sensitive after export
/import then that may be this bug.
WORKAROUND:
None
*** 11/26/08 07:06 pm ***
*** 11/26/08 07:06 pm *** (CHG: Sta->80)
*** 11/27/08 05:10 am ***
*** 12/01/08 06:29 am ***
*** 12/01/08 01:26 pm ***
*** 12/02/08 06:21 am ***
*** 12/03/08 05:08 am ***
*** 12/03/08 12:16 pm ***
*** 12/03/08 12:16 pm ***
*** 12/03/08 12:16 pm ***
*** 12/04/08 01:51 am ***
*** 12/04/08 06:07 am ***
*** 12/05/08 12:39 am ***
*** 12/05/08 04:47 am ***
*** 12/05/08 06:35 am ***
*** 12/05/08 07:34 am ***
*** 12/05/08 01:01 pm ***
*** 12/05/08 01:02 pm ***
*** 12/07/08 09:28 pm ***
*** 12/09/08 05:03 am ***
*** 12/09/08 04:08 pm ***
*** 12/10/08 01:45 pm ***
*** 12/17/08 08:12 am ***
*** 12/22/08 05:05 pm ***
*** 12/24/08 12:09 am ***
*** 12/26/08 04:29 am ***
*** 12/27/08 04:06 pm ***
*** 12/29/08 09:27 pm ***
*** 12/31/08 03:30 am ***
*** 01/05/09 10:29 am ***
*** 01/20/09 10:01 am ***
*** 01/21/09 12:26 pm ***
*** 04/16/09 02:01 pm ***
*** 04/22/09 01:37 pm ***
*** 05/11/09 05:26 am ***
*** 05/11/09 06:36 am ***
*** 04/12/10 08:00 am ***
*** 04/12/10 08:00 am ***
*** 04/17/10 02:52 am ***
*** 04/17/10 07:56 pm ***
*** 06/14/10 05:54 pm ***
*** 06/14/10 05:54 pm ***
*** 06/20/10 10:11 pm ***
*** 06/20/10 11:32 pm ***
*** 06/21/10 01:18 pm ***
*** 07/07/10 05:18 pm ***
*** 07/13/10 09:07 am ***
*** 08/16/10 09:49 pm ***
*** 08/18/10 12:22 am ***
*** 09/03/10 06:07 am ***
*** 09/07/10 07:17 am ***
*** 09/15/10 11:11 pm ***
*** 09/16/10 07:40 am ***
*** 12/01/10 12:01 am ***
*** 12/15/10 04:51 pm ***
*** 12/22/10 08:35 am ***
*** 01/04/11 01:49 am ***
*** 01/27/11 12:22 am ***
*** 02/14/11 07:15 am ***
*** 02/23/11 01:57 am ***
*** 02/25/11 07:17 am ***
*** 05/09/11 02:34 am ***
*** 05/09/11 02:35 am ***
*** 05/19/11 02:07 am ***
*** 06/06/11 01:34 pm ***
*** 06/13/11 08:38 pm ***
*** 06/24/11 03:59 am ***
*** 06/24/11 04:44 am ***
5.小結
作為Windows作業系統的Oracle客戶端軟體，這裡建議選擇正確的版本，同時儘量使用較高的Oracle軟體，這樣可以有效的減少遭遇Oracle Bug的機率。
Good luck.
secooler
11.06.23
-- The End --
1.使用exp工具備份過程中的報錯資訊如下
EXP-00008: ORACLE error 904 encountered
ORA-00904: "POLTYP": invalid identifier
EXP-00000: Export terminated unsuccessfully
2.問題原因
這個問題歸結於Oracle Bug，Bug號是7568350。
Since there was no code added in export to extract the RLS policy type, the fix for Bug 7568350 introduces
a new column in EXU9RLS view that is associated with the rls policy type.
Before installing the Patch 7568350, the EXU9RLS view definition was:
desc exu9rls Name Null? Type
-----------------------------------------
OBJOWN NOT NULL VARCHAR2(30)
OBJNAM NOT NULL VARCHAR2(30)
POLGRP NOT NULL VARCHAR2(30)
POLICY NOT NULL VARCHAR2(30)
POLOWN NOT NULL VARCHAR2(30)
POLSCH VARCHAR2(30)
POLFUN NOT NULL VARCHAR2(30)
STMT VARCHAR2(28)
CHKOPT NOT NULL NUMBER
ENABLED NOT NULL NUMBER
SPOLICY NUMBER
After installing the Patch 7568350, the EXU9RLS view has a new column added:
desc exu9rls Name Null? Type
-----------------------------------------
OBJOWN NOT NULL VARCHAR2(30)
OBJNAM NOT NULL VARCHAR2(30)
POLGRP NOT NULL VARCHAR2(30)
POLICY NOT NULL VARCHAR2(30)
POLOWN NOT NULL VARCHAR2(30)
POLSCH VARCHAR2(30)
POLFUN NOT NULL VARCHAR2(30)
STMT VARCHAR2(28)
CHKOPT NOT NULL NUMBER
ENABLED NOT NULL NUMBER
SPOLICY NUMBER
POLTYP VARCHAR2(33)
POLTYP column contains the RLS policy type.
So, this problem was introduced due to a change in the Patch 7568350, where the EXU9RLS view definition changed.
3.處理方法
我這裡沒有按照Bug說明的方法處理。而是在Windows7作業系統上重新安裝了Vista版本的Oracle 10.2.0.3版本軟體解決的問題。供大家參考。
4.Bug 7568350說明
Bug 7568350: POLICY_TYPE CHANGED FROM CONTEXT_SENSITIVE TO DYNAMIC AFTER IMPORTING WITH IMP
Show Bug Attributes Bug Attributes
Type B - Defect Fixed in Product Version 11.2
Severity 2 - Severe Loss of Service Product Version 10.2.0.3
Status 80 - Development to Q/A Platform. 912 - Microsoft Windows (32-bit)
Created 13-Nov-2008 Platform. Version -
Updated 24-Jun-2011 Base Bug -
Database Version 10.2.0.3
Affects Platforms Generic
Product Source Oracle
Show Related Products Related Products
Line Oracle Database Products Family Oracle Database
Area Oracle Database Product 5 - Oracle Server - Enterprise Edition
Hdr: 7568350 10.2.0.3 RDBMS 10.2.0.3 EXPORT PRODID-5 PORTID-912
Abstract: POLICY_TYPE CHANGED FROM CONTEXT_SENSITIVE TO DYNAMIC AFTER IMPORTING WITH IMP
*** 11/13/08 07:16 am ***
TAR:
----
7182827.994
PROBLEM:
--------
When a table which is protected by a RLS policy is exported from database and
is imported in another using the classic export and import utility, the
policy type is getting
changed to dynamic from context sensitive.
DIAGNOSTIC ANALYSIS:
--------------------
The following is a simple test case which reproduces the issue :
The test is done on 10.2 windows machine.
SQL> conn / as sysdba
SQL>create user MYSOURCE identified by MYSOURCE;
SQL>create user MYIMP identified by MYIMP;
SQL>grant create session to MYSOURCE,MYIMP;
SQL>GRANT CREATE TABLE TO MYSOURCE,MYIMP;
SQL>GRANT EXECUTE ON DBMS_RLS TO MYSOURCE,MYIMP;
SQL>GRANT UNLIMITED TABLESPACE TO MYSOURCE,MYIMP;
SQL>CONN MYSOURCE/MYSOURCE
SQL>CREATE TABLE TEST(SSN NUMBER);
SQL>begin
dbms_rls.add_policy
(object_name => 'TEST',
policy_name => 'TEST_POLICY',
policy_function => 'SSN>1',
policy_type => dbms_rls.context_sensitive);
end;
/
SQL> SELECT policy_type,OBJECT_NAME,OBJECT_OWNER FROM DBA_POLICIES A WHERE
A.object_owner IN ('MYIMP' ,'MYSOURCE');
POLICY_TYPE OBJECT_NAME OBJECT_OWNER
-----------------------------------------------------
CONTEXT_SENSITIVE TEST MYSOURCE
Now export the schem using the below command :
>exp mysource/mysource FILE=D:\exp_mysource_as_mysource.dmp
LOG=D:\exp_mysource_as_mysource.log OBJECT_CONSISTENT=y
Import it using the below command :
>imp system/oracle commit=y compile=y GRANTS=y
FILE=D:\exp_mysource_as_mysource.dmp LOG=D:\imp_mysource_as_mysource.log
FROMUSER=MYSOURCE TOUSER=MYIMP
SQL>conn / as sysbda
SQL> SELECT policy_type,OBJECT_NAME,OBJECT_OWNER FROM DBA_POLICIES A WHERE
A.object_owner IN ('MYIMP' ,'MYSOURCE');
POLICY_TYPE OBJECT_NAME OBJECT_OWNER
--------------------------------------------------
DYNAMIC TEST MYIMP <
change
CONTEXT_SENSITIVE TEST MYSOURCE
WORKAROUND:
-----------
none
RELATED BUGS:
-------------
REPRODUCIBILITY:
----------------
TEST CASE:
----------
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 11/13/08 07:16 am *** (CHG: Sta->16)
*** 11/13/08 07:51 am ***
*** 11/13/08 08:07 am *** (CHG: Sta->10)
*** 11/13/08 08:07 am ***
*** 11/17/08 02:36 am *** (CHG: Sta->16)
*** 11/17/08 02:36 am ***
*** 11/17/08 07:27 am ***
*** 11/18/08 06:41 am ***
*** 11/18/08 06:55 am *** (CHG: Sta->10)
*** 11/18/08 06:55 am ***
*** 11/18/08 06:59 pm *** (CHG: Sta->16)
*** 11/18/08 06:59 pm ***
*** 11/19/08 07:10 am ***
*** 11/19/08 07:59 am *** (CHG: Sta->11 SubComp->EXPORT)
*** 11/19/08 07:59 am ***
*** 11/19/08 07:59 am ***
*** 11/19/08 07:59 am ***
*** 11/20/08 10:31 pm ***
*** 11/20/08 10:31 pm ***
*** 11/20/08 10:31 pm ***
*** 11/20/08 10:31 pm ***
*** 11/26/08 07:01 pm ***
RELEASE NOTES:
]]RLS policy is getting changed to dynamic from context sensitive after export/
]]import.
REDISCOVERY INFORMATION:
If RLS policy is getting changed to dynamic from context sensitive after export
/import then that may be this bug.
WORKAROUND:
None
*** 11/26/08 07:06 pm ***
*** 11/26/08 07:06 pm *** (CHG: Sta->80)
*** 11/27/08 05:10 am ***
*** 12/01/08 06:29 am ***
*** 12/01/08 01:26 pm ***
*** 12/02/08 06:21 am ***
*** 12/03/08 05:08 am ***
*** 12/03/08 12:16 pm ***
*** 12/03/08 12:16 pm ***
*** 12/03/08 12:16 pm ***
*** 12/04/08 01:51 am ***
*** 12/04/08 06:07 am ***
*** 12/05/08 12:39 am ***
*** 12/05/08 04:47 am ***
*** 12/05/08 06:35 am ***
*** 12/05/08 07:34 am ***
*** 12/05/08 01:01 pm ***
*** 12/05/08 01:02 pm ***
*** 12/07/08 09:28 pm ***
*** 12/09/08 05:03 am ***
*** 12/09/08 04:08 pm ***
*** 12/10/08 01:45 pm ***
*** 12/17/08 08:12 am ***
*** 12/22/08 05:05 pm ***
*** 12/24/08 12:09 am ***
*** 12/26/08 04:29 am ***
*** 12/27/08 04:06 pm ***
*** 12/29/08 09:27 pm ***
*** 12/31/08 03:30 am ***
*** 01/05/09 10:29 am ***
*** 01/20/09 10:01 am ***
*** 01/21/09 12:26 pm ***
*** 04/16/09 02:01 pm ***
*** 04/22/09 01:37 pm ***
*** 05/11/09 05:26 am ***
*** 05/11/09 06:36 am ***
*** 04/12/10 08:00 am ***
*** 04/12/10 08:00 am ***
*** 04/17/10 02:52 am ***
*** 04/17/10 07:56 pm ***
*** 06/14/10 05:54 pm ***
*** 06/14/10 05:54 pm ***
*** 06/20/10 10:11 pm ***
*** 06/20/10 11:32 pm ***
*** 06/21/10 01:18 pm ***
*** 07/07/10 05:18 pm ***
*** 07/13/10 09:07 am ***
*** 08/16/10 09:49 pm ***
*** 08/18/10 12:22 am ***
*** 09/03/10 06:07 am ***
*** 09/07/10 07:17 am ***
*** 09/15/10 11:11 pm ***
*** 09/16/10 07:40 am ***
*** 12/01/10 12:01 am ***
*** 12/15/10 04:51 pm ***
*** 12/22/10 08:35 am ***
*** 01/04/11 01:49 am ***
*** 01/27/11 12:22 am ***
*** 02/14/11 07:15 am ***
*** 02/23/11 01:57 am ***
*** 02/25/11 07:17 am ***
*** 05/09/11 02:34 am ***
*** 05/09/11 02:35 am ***
*** 05/19/11 02:07 am ***
*** 06/06/11 01:34 pm ***
*** 06/13/11 08:38 pm ***
*** 06/24/11 03:59 am ***
*** 06/24/11 04:44 am ***
5.小結
作為Windows作業系統的Oracle客戶端軟體，這裡建議選擇正確的版本，同時儘量使用較高的Oracle軟體，這樣可以有效的減少遭遇Oracle Bug的機率。
Good luck.
secooler
11.06.23
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-700711/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- exp匯出報錯EXP-00106問題處理
- Go多協程併發環境下的錯誤處理Go
- 錯誤處理
- 資料處理--pandas問題
- MySQL問題處理——1040錯誤Too many connectionsMySql
- Python錯誤處理Python
- PHP 錯誤處理PHP
- php錯誤處理PHP
- Go 錯誤處理Go
- 錯誤處理：如何通過 error、deferred、panic 等處理錯誤？Error
- windows 批處理備份目錄指令碼Windows指令碼
- Python錯誤處理和異常處理（二）Python
- 異常錯誤資訊處理
- linux下gdb如何處理coredump錯誤Linux
- 禪道備份處理
- openGauss 處理錯誤表
- go的錯誤處理Go
- axios 的錯誤處理iOS
- X86環境大記憶體下資料庫啟動問題分析與處理記憶體資料庫
- rust學習十、異常處理(錯誤處理)Rust
- JavaScript 中遇到的錯誤問題，該怎麼處理？JavaScript
- K8S環境的Jenkin效能問題處理K8S
- Serv-u 備份處理
- Oracle異常錯誤處理Oracle
- 淺談前端錯誤處理前端
- ORACLE 異常錯誤處理Oracle
- PHP 核心特性 - 錯誤處理PHP
- 15-錯誤處理（Error）Error
- Go語言之錯誤處理Go
- grpc中的錯誤處理RPC
- laravel9 錯誤處理Laravel
- 學習Rust 錯誤處理Rust
- F3-smart錯誤處理修復fail問題案例AI
- 這個新 Go 錯誤處理提案，能解決問題不？Go
- 程式環境和預處理
- mysqlconnect bug 處理一例。MySql
- 大資料處理需留意哪些問題大資料
- SAP雲平臺CloudFoundry環境裡route 超過quota的錯誤處理Cloud
- TiDB DM同步報錯ErrCode 44006處理一例TiDB