【問題處理】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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- windows下通過批處理備份oracle資料庫WindowsOracle資料庫
- Go多協程併發環境下的錯誤處理Go
- 【問題處理】Error accessing PRODUCT_USER_PROFILE錯誤一例Error
- Windows 下處理資料庫無法啟動問題Windows資料庫
- 解決Oracle中Exp/Imp大量資料處理問題Oracle
- exp匯出報錯EXP-00106問題處理
- Windows函式錯誤處理 (轉)Windows函式
- 錯誤處理
- IIS配置PHP環境HTTP500錯誤處理方法PHPHTTP
- 【故障處理】EXP-00091: Exporting questionable statistics 問題處理方法Export
- Oracle 11.2.0.2 exp匯出錯誤處理一則Oracle
- PHP錯誤處理和異常處理PHP
- windows下刪除前一天備份批處理Windows
- PHP 錯誤處理PHP
- php錯誤處理PHP
- Go 錯誤處理Go
- Swift錯誤處理Swift
- Zabbix錯誤處理
- mysqldump錯誤處理MySql
- vue2.0 練習中報錯問題處理（資料繫結錯誤）Vue
- 【故障處理】DBCA建庫詭異問題處理--rac環境不能建立rac庫
- windows 批處理備份目錄指令碼Windows指令碼
- OGG-00751錯誤處理一例
- 禪道備份處理
- 關於批處理(bat)資料庫備份BAT資料庫
- 在Oracle11g RAC環境下處理ORA-00845錯誤案例Oracle
- oracle windows下使用批處理進行exp匯出OracleWindows
- X86環境大記憶體下資料庫啟動問題分析與處理記憶體資料庫
- windows的一個問題處理Windows
- 錯誤處理：如何通過 error、deferred、panic 等處理錯誤？Error
- 沒有備份undo segment出現問題的處理
- K8S環境的Jenkin效能問題處理K8S
- go的錯誤處理Go
- Python錯誤處理Python
- 用SQL Server資料庫處理資料層錯誤SQLServer資料庫
- Python錯誤處理和異常處理（二）Python
- 【故障處理】CRS-1153錯誤處理
- 【故障處理】ORA-19809錯誤處理