首页 » Cloud, ORACLE 9i-23ai » 升级Oracle 19c经验: TTS时ORA-39083和ORA-00942案例

升级Oracle 19c经验: TTS时ORA-39083和ORA-00942案例

  • 2020/06/16
  • Cloud, ORACLE 9i-23ai
  • 458 views
  • 升级Oracle 19c经验: TTS时ORA-39083和ORA-00942案例已关闭评论

上周在使用TTS传输表空间从11.2.0.4到19C， 在最后impdp metadata的环节提示ora-942 结果提示有大量索引没有创建成功，但是查看报错的表实际是存在的， 后来发现这是一个收权问题导致的。后分析这种场景是发生在如一开始给了一个用户如create ANY  table/index的系统权限或者dba 角色或是on object上的权限，后来创建了跨SCHEMA的index（indexes and tables  differenct  owners）或FK 约束时，再后来安全整改收回大权限，就导致了这个问题。 下面验证一下。

创建表空间、用户、表和索引

[oracle@oel7db1 tpt-oracle-master]$ ora

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 14 08:14:04 2020
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-anbob19c    oel7db1                     1 36    17455    19.0.0.0.0 20200614 2962       33    2961            000000006A88B4E0 000000006B9069A8

SQL> @cc pdb1
ALTER SESSION SET container = pdb1;

Session altered.


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- -----
CPID            SADDR            PADDR
--------------- ---------------- ----------------
SYS                  PDB1-anbob19c        oel7db1                     1 36    17455    19.0.0.0.0 20200614 2962       33
2961            000000006A88B4E0 000000006B9069A8

SQL> @df

           Container                                                            Free            Alloc
    CON_ID Name            TABLESPACE_NAME                Num Files       Space Meg.       Space Meg.        PCT
---------- --------------- ------------------------------ --------- ---------------- ---------------- ----------
         3 PDB1            SYSAUX                                 1               19              390        .95
                           SYSTEM                                 1                3              280        .99
                           TEMP                                   1                                36
                           UNDOTBS1                               1               61              100        .39
                           USERS                                  1              104            1,548        .93
********** *************** ****************************** --------- ---------------- ----------------
sum                                                               5              186            2,354
                                                          --------- ---------------- ----------------
sum                                                               5              186            2,354

SQL> @ls users

TABLESPACE_NAME                   FILE_ID EXT         MB      MAXSZ
------------------------------ ---------- --- ---------- ----------
FILE_NAME
--------------------------------------------------------------------------------------------------------------
USERS                                  12 YES     1547.5   32767.98
/u01/app/oracle/oradata/ANBOB19C/pdb1/users01.dbf


SQL> create tablespace TBS1 datafile '/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf' size 10m;
Tablespace created.

SQL> create user u1 identified by "anbob.com";
SQL> create user u2 identified by "anbob.com";


SQL> grant unlimited tablespace,create session,create table,create any index  to u1,u2;

Grant succeeded.

SQL> create table u1.test as select column_value id ,'anbob'||column_value name from xmltable('1 to 10');
create table u1.test as select column_value id ,'anbob'||column_value name from xmltable('1 to 10')
*
ERROR at line 1:
ORA-64464: XML event error
ORA-19202: Error occurred in XML processing
In line 1 of orastream:
LPX-00210: expected '<' instead of '1' SQL> create table u1.test(id int,name varchar2(10)) ;
Table created.

SQL> insert into u1.test select to_number(column_value) id ,'anbob'||column_value name from xmltable('1 to 10');
10 rows created.

SQL> commit;
Commit complete.

SQL> create table u2.test as select * from u1.test;
Table created.

SQL> alter table u1.test move tablespace tbs1;
Table altered.

SQL> alter table u2.test move tablespace tbs1;
Table altered.


create index u1.idx_test_id  on u2.test(id) tablespace tbs1;
create index u2.idx_test_name on u2.test(name) tablespace tbs1;
create index u2.idx_test_id on u1.test(id) tablespace tbs1;


SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS1', TRUE);
PL/SQL procedur successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected

SQL> select owner,table_owner,index_name,table_name from dba_indexes where owner in('U1','U2')

OWNER                          TABLE_OWNE INDEX_NAME           TABLE_NAME
------------------------------ ---------- -------------------- --------------------
U2                             U1         IDX_TEST_ID          TEST
U1                             U2         IDX_TEST_ID          TEST
U2                             U2         IDX_TEST_NAME        TEST

SQL> @dirs

DIRECTORY_NAME                           DIRECTORY_PATH
---------------------------------------- ------------------------------------------------------------------------------------------
SDO_DIR_WORK
...
JAVA$JOX$CUJS$DIRECTORY$                 /u01/app/oracle/product/19.2.0/db_1/javavm/admin/
DATAPUMP                                 /home/oracle

14 rows selected.

Note:
我们在两个用户创建了一个表，又分别在对方的表上创建了跨schema的索引和本schema索引，一并排除是按导入schema顺序导致和是否所有索引都有问题。

回收权限

这个脚本查询的是系统角色、系统权限和对象权限三个view

SQL>  revoke create any index from u1,u2;
SQL> @privs u1
no rows selected

GRANTEE                   PRIVILEGE                                ADM
------------------------- ---------------------------------------- ---
U1                        UNLIMITED TABLESPACE                     NO
U1                        CREATE SESSION                           NO
U1                        CREATE TABLE                             NO


no rows selected

SQL> @privs u2
no rows selected


GRANTEE                   PRIVILEGE                                ADM
------------------------- ---------------------------------------- ---
U2                        CREATE SESSION                           NO
U2                        CREATE TABLE                             NO
U2                        UNLIMITED TABLESPACE                     NO


no rows selected

TTS 导出导入元数据

这里简单使用本库做为导出、导入

SQL> alter tablespace TBS1 read only;
Tablespace altered.

SQL> host expdp anbob/anbob@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=tts_tbs1.log  transport_tablespaces=TBS1 exclude=TABLE_STATISTICS,INDEX_STATISTICS REUSE_DUMPFILES=yes

Export: Release 19.0.0.0.0 - Production on Tue Jun 16 05:22:25 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "ANBOB"."SYS_EXPORT_TRANSPORTABLE_01":  anbob/********@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=tts_tbs1.log transport_tablespaces=TBS1 exclude=TABLE_STATISTICS,INDEX_STATISTICS REUSE_DUMPFILES=yes
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Master table "ANBOB"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ANBOB.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/tts_tbs1.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS1:
  /u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf
Job "ANBOB"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Jun 16 05:23:25 2020 elapsed 0 00:00:55


SQL> drop tablespace tbs1 including contents ;
Tablespace dropped.

SQL> select owner,table_owner,index_name,table_name from dba_indexes where owner in('U1','U2');
no rows selected

SQL> host impdp anbob/anbob@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=imp_tts_tbs1.log transport_datafiles=/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf

Import: Release 19.0.0.0.0 - Production on Tue Jun 16 05:25:40 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "ANBOB"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "ANBOB"."SYS_IMPORT_TRANSPORTABLE_01":  anbob/********@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=imp_tts_tbs1.log transport_datafiles=/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
ORA-39083: Object type INDEX:"U2"."IDX_TEST_ID" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
CREATE INDEX "U2"."IDX_TEST_ID" ON "U1"."TEST" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 181 SEG_BLOCK 162 OBJNO_REUSE 73789 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS1" PARALLEL 1

ORA-39083: Object type INDEX:"U1"."IDX_TEST_ID" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
CREATE INDEX "U1"."IDX_TEST_ID" ON "U2"."TEST" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(SEG_FILE 181 SEG_BLOCK 146 OBJNO_REUSE 73787 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS1" PARALLEL 1

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "ANBOB"."SYS_IMPORT_TRANSPORTABLE_01" completed with 2 error(s) at Tue Jun 16 05:25:52 2020 elapsed 0 00:00:09


SQL>  select owner,table_owner,index_name,table_name from dba_indexes where owner in('U1','U2')

OWNER                     TABLE_OWNER          INDEX_NAME           TABLE_NAME
------------------------- -------------------- -------------------- ------------------------------
U2                        U2                   IDX_TEST_NAME        TEST

SQL> @privs anbob

GRANTEE                   GRANTED_ROLE                                                                                                                     ADM DEF
------------------------- --------------------------
ANBOB                     DBA                                                                                                                              NO  YES

GRANTEE                   PRIVILEGE                                ADM
------------------------- ---------------------------------------- ---
ANBOB                     UNLIMITED TABLESPACE                     NO
ANBOB                     CREATE SESSION                           NO


GRANTEE                   OWNER                     TABLE_NAME                     PRIVILEGE
------------------------- ------------------------- ------------------------------ ----------------------------------------
ANBOB                     SYS                       DATAPUMP                       READ
ANBOB                     SYS                       DATAPUMP                       WRITE

Note:
这里你会发现导入的数据中少了两个index和table owner不同的索引，相同schema的索引创建成功。当然我expdp的用户anbob是DBA 角色，不存在导入用户的权限不足。

如果u1/u2 用户不收回create any index系统权限再次做相同的测试

这里省略了相同的数据构建部分，只是没有收回create any index权限。

SQL> alter tablespace TBS1 read only;

Tablespace altered.

SQL> host expdp anbob/anbob@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=tts_tbs1.log  transport_tablespaces=TBS1 exclude=TABLE_STATISTICS,INDEX_STATISTICS REUSE_DUMPFILES=yes

Export: Release 19.0.0.0.0 - Production on Sun Jun 14 09:33:04 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "ANBOB"."SYS_EXPORT_TRANSPORTABLE_01":  anbob/********@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=tts_tbs1.log transport_tablespaces=TBS1 exclude=TABLE_STATISTICS,INDEX_STATISTICS REUSE_DUMPFILES=yes
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Master table "ANBOB"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ANBOB.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/tts_tbs1.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS1:
  /u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf
Job "ANBOB"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jun 14 09:34:06 2020 elapsed 0 00:00:57


SQL> drop tablespace tbs1 including contents ;
Tablespace dropped.

SQL> host impdp anbob/anbob@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=imp_tts_tbs1.log transport_datafiles=/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf

Import: Release 19.0.0.0.0 - Production on Sun Jun 14 09:35:05 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "ANBOB"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "ANBOB"."SYS_IMPORT_TRANSPORTABLE_01":  anbob/********@cdb1pdb1 directory=DATAPUMP dumpfile=tts_tbs1.dmp logfile=imp_tts_tbs1.log transport_datafiles=/u01/app/oracle/oradata/ANBOB19C/pdb1/tbs101.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "ANBOB"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Jun 14 09:35:24 2020 elapsed 0 00:00:12

SQL> select owner,table_owner,index_name,table_name from dba_indexes where owner in('U1','U2');

OWNER                          TABLE_OWNE INDEX_NAME           TABLE_NAME
------------------------------ ---------- -------------------- --------------------
U2                             U1         IDX_TEST_ID          TEST
U2                             U2         IDX_TEST_NAME        TEST
U1                             U2         IDX_TEST_ID          TEST

Note:
导入是成功的。

警示：
以后在TTS前需要检查index和table owner不相同的对象，并检查该user是否权限满足，可以临时授予DBA或CREATE ANY INDEX的权限，导入成功后回收权限即可。

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：