【问题处理】Windows环境下exp备份数据ORA-00904错误处理一例

  • secooler

    2011-06-23 22:43:55
  • Linux操作系统
  • 原创
  在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    <<----------- Here is the
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 --
请使用浏览器的分享功能分享到微信等