Object Difference In TTS Migration From 10g To 11g (文件 ID 1643239.1)
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:
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:
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:
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:
( 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;
--------------------------------------------------------------------------------
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:
( "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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21754115/viewspace-1732103/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Migration Of An Oracle Database Across OS Platforms [ID 733205.1]OracleDatabaseROSPlatform
- Oracle 11G 安裝文件Oracle
- oracle監聽檔案listener.ora for 10g/11gOracle
- Oracle從10g升級到11g詳細步驟Oracle
- DOM (文件物件模型(Document Object Model))物件模型Object
- Oracle database 升級(文件)to 10.2.0.4 from 10.2.0.1OracleDatabase
- How to redirect to a specific web page after sign out from Entra IDWeb
- How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1OracleGo
- [論文翻譯][1809 09294]Object Detection from Scratch with Deep SupervisionObject
- TypeError: Cannot read private member xxx from an object whose class did not declare itErrorObject
- Oracle GoldenGate 11g官方文件Administrator’s GuideOracleGoGUIIDE
- [20191204]oracle能建立最大object_id是多少.txtOracleObject
- 2.3.4 Migration of an Existing ApplicationAPP
- ORA-8103 Troubleshooting, Diagnostic and Solution (文件 ID 8103.1)
- What is the difference Put and Post and Get?
- Difference between cursor and a ref cursor
- difference with PerlApp 7.0 and PerlApp 6.0APP
- What is the difference between a Homemaker and a Housewife?
- LeetCode 389. Find the DifferenceLeetCode
- Oracle GoldenGate 11g官方文件Administrator’s Guide續二OracleGoGUIIDE
- Oracle GoldenGate 11g官方文件Administrator’s Guide續一OracleGoGUIIDE
- Oracle GoldenGate 11g官方文件Administrator’s Guide續三OracleGoGUIIDE
- Oracle GoldenGate 11g官方文件Administrator’s Guide續四OracleGoGUIIDE
- make:migration 的騷操作
- Laravel migration 逆向生成工具Laravel
- Non-static method 'save(java.long.Object)' cannot be referenced from a static context.JavaObjectContext
- 【MOS】Creating a PDB ... Fails With ORA-17630 (文件 ID 2090019.1)AI
- [20191206]為什麼oracle能建立最大object_id=4254950910.txtOracleObject
- Oracle 9i, 10g, and 11g RAC on Linux所需要的Hangcheck-Timer Module介紹OracleLinuxGC
- milvus-migration安裝使用
- 遷移執行緒migration執行緒
- Oracle 10g/11g下如何將物理Standby庫臨時啟用用於災備測試Oracle 10g
- The SQL vs NoSQL Difference: MySQL vs MongoDBMySqlMongoDB
- Library Cache 診斷:Lock, Pin 以及 Load Lock (文件 ID 1548524.1)
- 文字到語音(tts)TTS
- Coqui TTS合成語音UITTS
- 9i and 10g 透過SQL_ADDRESS 或sql_id查詢執行計劃SQL
- RMAN restore fails with ORA-01180: can not create datafile 1 (文件 ID 1265151.1)RESTAI
- 重啟docker服務後,容器啟動報錯:Error response from daemon: id already in useDockerError