Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 to 12.1.0.1 [Release 10.2 to 12.1]
Information in this document applies to any platform.

Symptoms

The customer is trying to perform a transportable Tablespace (TTS) transfer of data from a 10g Release 2 to an 11g Release 2 Database.

After the import he noted that there was a discrepancy in the objects between the two environments.

Cause

Further analysis showed that the objects were all related to LOB's management:

* They were of type LOBSEGMENT and LOBINDEX

* The names of all the objects started with 'SYS_', which indicates that the object names were created by the system

Solution

This is working as expected:

There was an important change between 10 and 11g with regards to the management of LOB data as the option to store data in Securefiles was introduced.

This requires a change in the object definition, resulting in new object names. The following test shows the result:

First a tablespace test and a user test are created. Then a table with a CLOB column is created and filled with some data:

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 3 03:03:18 2014

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

SYS o10gr2> create tablespace test datafile 'test.dbf' size 50m reuse;

Tablespace created.

SYS o10gr2> create user test identified by test default tablespace test tempo rary tablespace temp;

User created.

SYS o10gr2> grant connect,resource to test;

Grant succeeded.

SYS o10gr2> conn test/test
Connected.

test o10gr2> CREATE TABLE test
    ( id NUMBER(6), c CLOB)
    LOB (c) STORE AS
      (TABLESPACE test
       STORAGE (INITIAL 6144 NEXT 6144)
       CHUNK 4000
       NOCACHE LOGGING); 2    3    4    5    6    7

Table created.

test o10gr2> insert into test values(10,'Human Resources');

1 row created.

test o10gr2> insert into test values(20,'Development');

1 row created.

test o10gr2> commit;

Commit complete.

test o10gr2> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000570238C00002$$      LOB
SYS_LOB0000570241C00002$$      LOB
SYS_LOB0000570244C00002$$      LOB
TEST                           TABLE

test o10gr2> conn / as sysdba
Connected.
SYS o10gr2> col segment_name format a30

SYS o10gr2> select owner, segment_name, segment_type from dba_segments where tablespace_name='test';

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
test                          BIN$9hodgK0QQI7gQKUKhfUWFw==$0 TABLE
test                          BIN$9hodgK0RQI7gQKUKhfUWFw==$0 TABLE
test                          TEST                           TABLE
test                          SYS_IL0000570238C00002$$       LOBINDEX
test                          SYS_IL0000570241C00002$$       LOBINDEX
test                          SYS_IL0000570244C00002$$       LOBINDEX
test                          SYS_LOB0000570238C00002$$      LOBSEGMENT
test                          SYS_LOB0000570241C00002$$      LOBSEGMENT
test                          SYS_LOB0000570244C00002$$      LOBSEGMENT

9 rows selected.

Now the tablespace is set to read-only and a TTS export is performed:

test o10gr2> conn / as sysdba
Connected.
SYS o10gr2> alter tablespace test read only;

Tablespace altered.

SYS o10gr2> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ora102@olapserv3 ~]$ expdp directory=temp dumpfile=test logfile=test.log transport_tablespaces=test

Export: Release 10.2.0.4.0 - Production on Thursday, 03 April, 2014 4:04:48

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=temp dumpfile=test logfile=test.log transport_tablespaces=test
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/tmp/test.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 04:06:59

In the target environment of 11.2.0.3, the user is created :

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 3 04:15:30 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@o11gr2> create user test identified by test;

User created.

SYS@o11gr2> grant connect,resource to test;
#
Grant succeeded.

SYS@o11gr2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now a TTS import is performed in the 11.2.0.3 environment:

[o11gr2@olapserv3 ~]$ impdp directory=temp dumpfile=test.dmp logfile=test_imp.log transport_datafiles=/tmp/test.dbf

Import: Release 11.2.0.3.0 - Production on Thu Apr 3 04:16:02 2014

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=temp dumpfile=test.dmp logfile=test_imp.log transport_datafiles=/tmp/test.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 04:16:09

Now the checks are run in the target:

[o11gr2@olapserv3 ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 3 04:17:03 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

test@o11gr2> col object_name format a30
test@o11gr2> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000570312C00002$$      LOB
SYS_IL0000570312C00002$$       INDEX
TEST                           TABLE

test@o11gr2> select * from test;

        ID
----------
C
--------------------------------------------------------------------------------
        10
Human Resources

        20
Development

test@o11gr2> conn / as sysdba
Connected.
SYS@o11gr2> col segment_name format a30
SYS@o11gr2> select owner, segment_name, segment_type from dba_segments where tablespace_name='test';

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
test                          TEST                           TABLE
test                          SYS_IL0000570312C00002$$       LOBINDEX
test                          SYS_LOB0000570312C00002$$      LOBSEGMENT
SYS                            26.11                          TEMPORARY
SYS                            26.19                          TEMPORARY
SYS                            26.27                          TEMPORARY
SYS                            26.35                          TEMPORARY
SYS                            26.43                          TEMPORARY
SYS                            26.51                          TEMPORARY

9 rows selected.

As you can see, the name of the LOB segment and LOB index are different. But the data is still there.

The original DDL for the table TEST was:

CREATE TABLE test
    ( id NUMBER(6), c CLOB)
    LOB (c) STORE AS
      (TABLESPACE test
       STORAGE (INITIAL 6144 NEXT 6144)
       CHUNK 4000
       NOCACHE LOGGING);

When using DBMS_METADATA.GET_DDL you see:

SYS o10gr2> select dbms_metadata.get_ddl('TABLE','TEST','DEUBA') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','TEST')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST"
   (    "ID" NUMBER(6,0),
        "C" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST"
LOB ("C") STORE AS (
TABLESPACE "DEUBA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

When you use the SQLFILE parameter of impdp, you see the following DDL for the table:

CREATE TABLE "TEST"."TEST"
   (    "ID" NUMBER(6,0),
        "C" CLOB
   ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(SEG_FILE 29 SEG_BLOCK 59 OBJNO_REUSE 570244
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 "TEST"
LOB ("C") STORE AS BASICFILE (
TABLESPACE "TEST" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING
STORAGE(SEG_FILE 29 SEG_BLOCK 67 OBJNO_REUSE 570245
INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
INDEX ( INITRANS 2 MAXTRANS 255
STORAGE(SEG_FILE 29 SEG_BLOCK 75 OBJNO_REUSE 570246
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)) ) ;

So this change in the way that the table is defined requires it to be recreated from scratch, resulting in the new names for the system-generated objects.
However, in the later course of the import, the new metadata is remapped accordingly.

In summary, this is working as designed when moving LOB-based data via TTS between RDBMS versions 10g Release 2 and 11g.

Object Difference In TTS Migration From 10g To 11g (文件 ID 1643239.1)

Applies to:

Symptoms

Cause

Solution

相關文章