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%';