Document Type | Technical Information
Category | Migration
Applicable Product Versions | 6FS07, 6FS07PS, 7FS02, 7FS02PS
Document Number | TMITI019
Overview
This document describes the checklist before migrating from Oracle to Tibero DB.
Pre-migration tasks is an important procedure to understand the characteristics of Oracle before migrating and to verify the values that need to be applied to Tibero.
Method
1. Check Oracle Version
select * from v$version;
2. Check Oracle Character Set Information
CHARACTERSET Query
--ORACLE CHARACTERSET Query set linesize 150 set pagesize 100 select name, value$ from sys.props$ where name like '%CHARACTERSET' order by name; --TIBERO CHARACTERSET Query select * from _vt_nls_character_set;
Note
When changing from KO16KSC5601, KO16MSWIN949, JA16SJIS to UTF8, AL32UTF8, you must increase the size of the table columns by about 1.5 to 2 times before data conversion.
If Oracle's National CHARACTERSET is UTF8 and Tibero's is UTF16, characters of related types will be corrupted during conversion, so the same setting is required.
Corresponding Character Sets Between Oracle and Tibero
$ tbboot -C Available character set list Charset name Equivalent Oracle Charset name AR8ISO8859P6 AR8ISO8859P6 AR8MSWIN1256 AR8MSWIN1256 ASCII US7ASCII CL8ISO8859P5 CL8ISO8859P5 CL8KOI8R CL8KOI8R CL8MSWIN1251 CL8MSWIN1251 EE8ISO8859P2 EE8ISO8859P2 EL8ISO8859P7 EL8ISO8859P7 EL8MSWIN1253 EL8MSWIN1253 EUCKR KO16KSC5601 EUCTW ZHT32EUC GB18030 ZHS32GB18030 GBK ZHS16GBK IW8ISO8859P8 IW8ISO8859P8 JA16EUC JA16EUC JA16EUCTILDE JA16EUCTILDE JA16SJIS JA16SJIS JA16SJISTILDE JA16SJISTILDE MSWIN949 KO16MSWIN949 RU8PC866 RU8PC866 SJIS SJISTILDE TH8TISASCII TH8TISASCII UTF16 AL16UTF16 UTF8 AL32UTF8 VN8VN3 VN8VN3 WE8ISO8859P1 WE8ISO8859P1 WE8ISO8859P15 WE8ISO8859P15 WE8ISO8859P9 WE8ISO8859P9 WE8MSWIN1252 WE8MSWIN1252 ZHT16BIG5 ZHT16BIG5 ZHT16HKSCS ZHT16HKSCS ZHT16MSWIN950 ZHT16MSWIN950 Available nls_date_lang set list AMERICAN BRAZILIAN PORTUGUESE BULGARIAN CATALAN CROATIAN CZECH DANISH DUTCH ENGLISH ESTONIAN FINNISH FRENCH GERMAN HEBREW HUNGARIAN ICELANDIC INDONESIAN ITALIAN JAPANESE KOREAN LATVIAN LITHUANIAN MEXICAN SPANISH NORWEGIAN POLISH PORTUGUESE ROMANIAN RUSSIAN SIMPLIFIED CHINESE SLOVAK SPANISH SWEDISH THAI TRADITIONAL CHINESE TURKISH VIETNAMESE
3. Query Oracle Users
Query All Users List
set linesize 120 set pagesize 100 select username, account_status, default_tablespace, temporary_tablespace from dba_users order by oracle;
Query Open Users List
set linesize 120 set pagesize 100 select username, default_tablespace, temporary_tablespace from dba_users where account_status='OPEN' order by username;
Oracle Default Users List
ANONYMOUS APEX_050100 APEX_PUBLIC_USER APPQOSSYS AUDSYS CTXSYS DBSFWUSER DBSNMP DIP DVSYS DVF FLOWS_FILES GGSYS GSMADMIN_INTERNAL GSMCATUSER GSMUSER HR LBACSYS MDDATA MDSYS OUTLN ORACLE_OCM REMOTE_SCHEDULER_AGENT SYS SYSTEM SYSBACKUP SYSKM SYSDG SYSRAC SYS$UMF WMSYS XDB XS$NULL
Note
Check the latest updated users in the Database Install Guide.
4. Query Size to be Converted
Query Total Tablespace Size and Usage
select x.a tablespace_name , sum(x.b)/1024 "tot_size(mb)" , sum(x.c)/1024 "used_size(mb)" , sum(x.c)/sum(x.b)*100 rate from ( select b.tablespace_name a ,sum(bytes)/1024 b ,0 c from dba_data_files b group by b.tablespace_name union select d.tablespace_name, 0, sum(bytes)/1024 from dba_segments d group by d.tablespace_name )x group by x.a;
Query Total Segment Size Sum
select sum(x.b)/1024 "tot(mb)", sum(x.c)/1024 "used(mb)" from ( select sum(bytes)/1024 b, 0 c from dba_data_files b union select 0, sum(bytes)/1024 from dba_segments d ) x;
5. Query Oracle Objects
Check Object Count
set linesize 150 col owner format a30 select owner, object_type, status, count(*) from dba_objects where owner in (select username from dba_users where account_status='OPEN' ) group by owner, object_type, status order by owner, object_type, status;
Note
Since dba_object may include deleted objects, it is recommended to also refer to dba_tables and dba_indexes for an accurate count.
For INVALID objects, request the customer's DB administrator to clean them up in advance to shorten the conversion time.
Check Constraint Count
set linesize 150 col owner format a30 select owner , constraint_type , count( constraint_name ) as "con_cnt" from dba_constraints where owner in (select username from dba_users where account_status='OPEN' ) group by owner, constraint_type order by owner ,constraint_type;
6. Query Initial Parameter Information
show parameter <parameter_name>
7. Check Redo Log Configuration
set linesize 150 select * from v$logfile; select group#, bytes/1024/1024 as "size(mb)", members, archived from v$log;
Note
When creating Redo Logs in Tibero, refer to this to create files of the same or larger size.
8. Check UNDO and TEMP Tablespace Configuration
Query UNDO Tablespace Configuration
show parameters undo_tablespace; set linesize 150 col tablespace_name format a40 col file_name format a50 select tablespace_name, file_name, bytes/1024/1024 "size(mb)" , maxbytes/1024/1024 "maxsize(mb)", autoextensible from dba_data_files where tablespace_name = 'UNDOTBS1' -- Enter the result value of show parameters undo_tablespace;
Query TEMP Tablespace Configuration
col file_name format a50 select tablespace_name, file_name, bytes/1024/1024 "size mb", autoextensible from dba_temp_files;
9. Check Log Mode
select log_mode from v$database;
Note
If in ARCHIVE LOG mode, change the mode after the conversion is fully completed to shorten the conversion time.
10. Check Additional Objects
Query DB Links
set linesize 150 col owner format a15 col db_link format a20 col username format a20 col host format a50 select * from dba_db_links;
View tnsnames.ora File
cat $ORACLE_HOME/network/admin/tnsnames.ora
Query JOBs
set linesize 200 select job, schema_user, broken, instance from dba_jobs; set linesize 150 col interval format a50 select job, schema_user, next_date, broken, interval from dba_jobs;
Query Cluster Objects
select *
from dba_objects
where object_type like '%CLUSTER%'
and owner not in ('SYS');
Query XML Types
set linesize 200
select owner, table_name, column_name, data_type
from dba_tab_cols
where data_type like '%XML%'
and owner not in ('SYS');
Query Bit Map Indexes
select * from dba_indexes where index_type like '%BITMAP%';