Migration

This section explains the procedures and methods for migrating from other DBMS to Tibero.

Pre-Migration Considerations

This section explains the checklist items to review before migrating from Oracle to Tibero DB.

The pre-migration check is an important step for understanding the features of other DBMS and verifying the values that need to be applied to Tibero.

Note that the SQL statements explained in this section (queries executable in 10g and 11g) may vary slightly depending on the Oracle version.

1) General

Log in as a user with DBA privileges, such as Oracle's sys or system, to verify the information below.

Item	Description
Version Information	For Oracle versions 9i, 10g, and 11g, there are many migration cases, but versions outside of these require further review.
Migration Character Set	Select the Tibero character set that corresponds to the character set currently used in Oracle.
Migration Users	Exclude default users that are automatically created when Oracle is installed. Client confirmation is required for the migration users.
Migration Size	Factors that have the greatest impact on conversion time. In addition, the client's conversion time may vary greatly depending on DISK I/O, NETWORK I/O, etc.
Migration Validation	Information used for comparison after the migration is completed.

Version Information

Check the version of Oracle migrated.

select * from v$version;

Migration Character Set

Use the corresponding character set 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            GB18030
 GBK                ZHS16GBK
 IW8ISO8859P8       IW8ISO8859P8 
 JA16EUC            JA16EUC 
 JA16EUCTILDE       JA16EUCTILDE 
 JA16SJIS
 JA16SJISTILDE
 MSWIN949           KO16MSWIN949
 RU8PC866           RU8PC866
 SJIS               JA16SJIS 
 SJISTILDE          JA16SJISTILDE 
 TH8TISASCII        TH8TISASCII 
 UTF16              AL16UTF16
 UTF8               UTF8
 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 
 JAPANESE
 KOREAN 
 RUSSIAN
 SIMPLIFIED CHINESE 
 THAI
 TRADITIONAL CHINESE
 VIETNAMESE

Select Migration User (Schemas)

Log in with a user account that has DBA access to Oracle's 'sys' or 'system,' and perform the query.

• Retrieve users

The following is the list of all users.

select username, account_status, default_tablespace, temporary_tablespace 
from dba_users order by oracle;

• Retrieve open users

The following is a query to retrieve the currently open users.

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

The following usernames are automatically created when Oracle is installed and should be excluded during migration. (Usernames may vary slightly depending on the Oracle version.)

User Name	Description
ANONYMOUS	Enables HTTP access to Oracle XML DB.
APEX_050100	The account that owns the Oracle Application Express schema and metadata.
APEX_PUBLIC_USER	The minimally privileged account used for Oracle Application Express configuration with Oracle Application Express Listener or Oracle HTTP Server and mod_plsql.
APPQOSSYS	Used for storing and managing all data and metadata required by Oracle Quality of Service Management.
AUDSYS	The account where the unified audit data trail resides.
AURORA$ORB$UNAUTHENTICATED	Used for users who do not authenticate in Aurora/ORB
AWR_STAGE	Used to load data into the AWR from a dump file
CSMIG	User for Database Character Set Migration Utility
CTXSYS	The Oracle Text account.
DBSFWUSER	The account used to run the DBMS_SFW_ACL_ADMIN package.
DBSNMP	The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.
DEMO	User for Oracle Data Browser Demonstration (last version 9.2)
DIP	The account used by the Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.
DMSYS	Data Mining user
DSSYS	Oracle Dynamic Services and Syndication Server
DVF	The account owned by Database Vault that contains public functions to retrieve the Database Vault Factor values.
DVSYS	There are two roles associated with this account. The Database Vault owner role manages the Database Vault roles and configurations. The Database Vault Account Manager is used to manage database user accounts. Note: Part of Oracle Database Vault user interface text is stored in database tables in the DVSYS schema. By default, only the English language is loaded into these tables. You can use the DVSYS. DBMS_MACADM.ADD_NLS_DATA procedure to add other languages to Oracle Database Vault.
EXFSYS	User to hold the dictionary, APIs for the Expression Filter
FLOWS_FILES	The account owns the Oracle Application Express uploaded files.
GGSYS	The internal account used by Oracle GoldenGate. It should not be unlocked or used for a database login.
GSMADMIN_INTERNAL	The internal account that owns the Global Data Services schema. It should not be unlocked or used for a database login.
GSMCATUSER	The account used by Global Service Manager to connect to the Global Data Services catalog.
GSMUSER	The account used by Global Service Manager to connect to the database.
HR (Human Resources)
OE (Order Entry)
SH (Sales History)	Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables
LBACSYS	The Oracle Label Security administrator account. Starting with Oracle Database 18c, the LBACSYS user account is created as a schema-only account.
MDDATA	The schema used by Oracle Spatial and Graph for storing geocoder and router data.
MDSYS	The Oracle Spatial and Graph administrator account.
ORACLE_OCM	This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.
ORDPLUGINS	Object Relational Data (ORD) User used by Time Series, etc.
ORDSYS	Object Relational Data (ORD) User used by Time Series, etc.
OUTLN	The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.
PERFSTAT	Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
REMOTE_SCHEDULER_AGENT	The account to disable remote jobs on a database. This account is created during the remote scheduler agent configuration. You can disable the capability of a database to run remote jobs by dropping this user.
SCOTT
ADAMS
JONES
CLARK
BLAKE	Training/ demonstration users containing the popular EMP and DEPT tables
SYS	The account used to perform database administration tasks.
SYS$UMF	The account used to administer Remote Management Framework, including the remote Automatic Workload Repository (AWR).
SYSBACKUP	The account used to perform backup and recovery tasks.
SYSDG	The account used to administer and monitor Oracle Data Guard.
SYSKM	The account used to perform encryption key management.
SYSRAC	The account used to administer Oracle Real Application Clusters (RAC).
SYSTEM	Another account used to perform database administration tasks.
TRACESVR	Oracle Trace server
TSMSYS	User for Transparent Session Migration (TSM) a Grid feature
WMSYS	The account used to store the metadata information for Oracle Workspace Manager.
XDB	The account used for storing Oracle XML DB data and metadata.
XS$NULL	The internal account that represents the absence of a database schema user in a session, and indicates an application user session is in use. XS$NULL cannot be authenticated to a database, nor can it own any database schema objects, or possess any database privileges.

Source: Database Installation Guide

Migration size

Only check the tablespaces where the tables and indexes of the users targeted for migration are stored. The example below shows how to check the entire tablespace.

• Check 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;

• Total

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;

Migration Validation Items

Only check the information related to the users targeted for migration.

• Check Object count by status

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;

The dba_objects view includes even deleted objects (in $BIN~ format), so for an accurate count, it is recommended to check views like dba_tables and dba_indexes.

Requesting the client’s DB administrator to clean up any INVALID objects can reduce migration time.

• Check Object count

Used for Comparing the object count between Oracle and Tibero after the migration is used for validation.

WITH mig_user AS
(
    SELECT username
    FROM dba_users
    WHERE account_status='OPEN'
    AND username NOT IN('SYS', 'SYSTEM', 'WMSYS','EXFSYS', 'XDB', 'ORDSYS','MDSYS','SYSMAN')
)
SELECT
    O.OWNER
    ,NVL(TBL.TABLE_CNT, 0) AS "TABLE_CNT"
    ,O.VIEW_CNT
    ,O.DBLINK_CNT
    ,O.SEQ_CNT
    ,O.PKG_CNT
    ,O.PKGBODY_CNT
    ,O.PROC_CNT
    ,O.FUNC_CNT
    ,O.TYPE_CNT
    ,O.TYPEBODY_CNT
    ,O.LIB_CNT
    ,TRI.TRIGGER_CNT
    ,NVL(SYN.SYN_CNT,0) AS "SYN_CNT"
    ,NVL(PUB_SYN.PUB_SYN_CNT,0) AS "PUB_SYN_CNT" 
    ,NVL(IDX.IDX_CNT, 0) AS "IDX_CNT"
    ,NVL(G.GRANT_CNT, 0) AS "GRANT_CNT" 
    ,NVL(LOB.LOB_CNT, 0) AS "LOB_CNT" 
    ,NVL(JOB.JOB_CNT, 0) AS "JOB_CNT"
FROM (
    SELECT
        T.OWNER
        ,SUM( DBLINK_CNT ) AS "DBLINK_CNT" 
        ,SUM( VIEW_CNT ) AS "VIEW_CNT"
        ,SUM( SEQ_CNT ) AS "SEQ_CNT" 
        ,SUM( PKG_CNT ) AS "PKG_CNT"
        ,SUM( PKGBODY_CNT ) AS "PKGBODY_CNT" 
        ,SUM( PROC_CNT ) AS "PROC_CNT"
        ,SUM( FUNC_CNT ) AS "FUNC_CNT"
        ,SUM( TYPE_CNT ) AS "TYPE_CNT"
        ,SUM( TYPEBODY_CNT ) AS "TYPEBODY_CNT" 
        ,SUM( LIB_CNT ) AS "LIB_CNT"
    FROM (
            SELECT
                OWNER 
                ,CASE WHEN OBJECT_TYPE = 'DATABASE LINK' THEN COUNT(1) ELSE 0 END
AS "DBLINK_CNT"
                ,CASE WHEN OBJECT_TYPE = 'VIEW' THEN COUNT(1) ELSE 0 END AS
"VIEW_CNT"
                ,CASE WHEN OBJECT_TYPE = 'SEQUENCE' THEN COUNT(1) ELSE 0 END AS
"SEQ_CNT"
                ,CASE WHEN OBJECT_TYPE = 'PACKAGE' THEN COUNT(1) ELSE 0 END AS
"PKG_CNT"
                ,CASE WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN COUNT(1) ELSE 0 END
AS "PKGBODY_CNT" 
                ,CASE WHEN OBJECT_TYPE = 'PROCEDURE' THEN COUNT(1) ELSE 0 END AS
"PROC_CNT"
                ,CASE WHEN OBJECT_TYPE = 'FUNCTION' THEN COUNT(1) ELSE 0 END AS
"FUNC_CNT"
                ,CASE WHEN OBJECT_TYPE = 'TYPE' THEN COUNT(1) ELSE 0 END AS
"TYPE_CNT"
                ,CASE WHEN OBJECT_TYPE = 'TYPE BODY' THEN COUNT(1) ELSE 0 END AS
"TYPEBODY_CNT" 
                ,CASE WHEN OBJECT_TYPE = 'LIBRARY' THEN COUNT(1) ELSE 0 END AS
"LIB_CNT"
            FROM dba_objects
            WHERE owner in (select username from mig_user) 
            GROUP BY owner, OBJECT_TYPE
            )T
        GROUP BY T.OWNER
    )O
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS TABLE_CNT
    FROM DBA_TABLES
    WHERE owner in (select username from mig_user) 
    GROUP BY OWNER
) TBL ON O.OWNER = TBL.OWNER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS GRANT_CNT
    FROM DBA_TAB_PRIVS
    WHERE owner in (select username from mig_user) 
    AND TABLE_NAME NOT LIKE 'BIN$%'
    GROUP BY OWNER
) G ON O.OWNER = G.OWNER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS IDX_CNT
    FROM DBA_INDEXES
    WHERE owner in (select username from mig_user) 
    GROUP BY OWNER
) IDX ON O.OWNER = IDX.OWNER
    LEFT JOIN (
    SELECT OWNER, COUNT(1) AS LOB_CNT
    FROM DBA_LOBS
    WHERE owner in (select username from mig_user)
    GROUP BY OWNER
) LOB ON O.OWNER = LOB.OWNER
LEFT JOIN (
    SELECT SCHEMA_USER, COUNT(1) AS JOB_CNT
    FROM DBA_JOBS
    WHERE SCHEMA_USER in (select username from mig_user) 
    GROUP BY SCHEMA_USER
) JOB ON O.OWNER = JOB.SCHEMA_USER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS TRIGGER_CNT
    FROM DBA_TRIGGERS
    WHERE OWNER in (select username from mig_user)
    AND TRIGGER_NAME NOT LIKE 'BIN$%'
    GROUP BY OWNER
) TRI ON O.OWNER = TRI.OWNER
LEFT JOIN (
    SELECT OWNER, COUNT(1) AS "SYN_CNT"
    FROM DBA_SYNONYMS
    WHERE OWNER in (select username from mig_user)
    AND OWNER != 'PUBLIC'
    GROUP BY OWNER
) SYN ON O.OWNER = SYN.OWNER
LEFT JOIN (
    SELECT TABLE_OWNER as "OWNER", COUNT(1) AS "PUB_SYN_CNT" 
    FROM DBA_SYNONYMS
    WHERE TABLE_OWNER in (select username from mig_user) 
    AND OWNER = 'PUBLIC'
    GROUP BY TABLE_OWNER
) PUB_SYN ON O.OWNER = PUB_SYN.OWNER 
ORDER BY O.OWNER;

• 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;

2) DB Creation-Related Items

Log in as a user with DBA privileges, such as Oracle's sys or system, to check the information below. When executing CREATE DATABASE command during Tibero installation, refer to the information below to create the database.

The following is an explanation of the items related to database creation.

Item	Description
Character Set	Select the character set that corresponds to Oracle.
Initial Parameters	Check the session count, memory settings, etc.
Redo Configuration	Check the number or size of groups and members.
Undo and Temp Configuration	Check the size of the Undo and Temp tablespaces.
Log Mode	As this is not supported in Tibero, consider changing to a general index.

Character Set

Check the character set information.

• Oracle

set linesize 150
set pagesize 100
column value$ format a100

select
    name,
    value$
from
    sys.props$
where
    name like '%CHARACTERSET' 
order by
    name;

• Tibero

select * from _vt_nls_character_set;

The following provides an explanation of the items verified.

Item	Description
NLS_CHARACTERSET	DB Character Set
NLS_NCHAR_CHARACTERSET	National Character Set

The following is the character set mapping information between Oracle and Tibero.

Oracle	Tibero
US7ASCII	ASCII
KO16KSC5601	EUCKR or MSWIN949
KO16MSWIN949	MSWIN949
UTF8, AL32UTF8	UTF8
JA16SJIS[TILDE]	JA16SJIS[TILDE]
JA16EUC[TILDE]	JA16EUC[TILDE]
ZHS16GBK	GBK
VN8VN3	VN8VN3

If the US7ASCII character set contains Korean characters, you may choose to use MSWIN949, depending on the purpose. However, adjustments may be needed for encoding and decoding on the application.

When changing from KO16KSC5601, KO16MSWIN949, and JA16SJIS to UTF8 or AL32UTF8, the column size of the relevant tables should be increased by 1.5 to 2 times before performing data migration.

• National Character set

Oracle	Tibero
UTF8	UTF8
AL16UTF16(Default value)	UTF16(Default value)

If Oracle's national character set is UTF8 and Tibero's is UTF16, characters of the related type may become corrupted during migration, so matching settings are necessary.

Initial Parameters

Check the initial parameter information

show parameter <parameter name>

The following are initial parameters that need to be verified when migrating from Oracle.

Parameter	Description
SESSIONS	Set the number of sessions
OPEN_CURSORS	The number of cursors that can be opened per session.
UNDO_RETENTION	Set the undo retention time.
SGA_MAX_SIZE	Check the total available SGA size.
SGA_TARGET	Set the target size of the SGA
PGA_AGGREGATE_TARGET	Check the total available PGA size
MEMORY_TARGET	Set the total available memory size (for Oracle 11g)

To check the size of memory-related SGA subcomponents, query "V$SGA."

Redo Log Configuration

When creating redo logs in Tibero, refer to this information and create files of the same or larger size.

The following is an example of checking redo logs.

set linesize 150
select * from v$logfile;

select group#, bytes/1024/1024 as "size(mb)", members, archived from v$log;

Undo and Temp Configuration

When creating Undo and Temp in Tibero, refer to this information and create files of the same or larger size.

The following is an example of checking the Undo tablespace.

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' -- input the result value of show parameters undo_tablespace;

The following is an example of checking the Temp tablespace.

col file_name format a50
select tablespace_name, file_name, bytes/1024/1024 "size mb", autoextensible 
from dba_temp_files;

Log mode

The following is an example of checking the log mode.

If it is in ARCHIVE LOG mode, change the mode after the migration is complete to reduce migration time.

select log_mode from v$database;

3) Check additional Objects

The following is an explanation of additional objects that need to be checked.

Item	Description
DB Link	Check the DB Link information for the migration user and identify the type of DB containing the data.
tnsnames.ora	Check the host information used for the DB Link.
Job	Check if there are any jobs for the migration user or the entire system, and if migrating, ensure that they do not change the data during execution.
Cluster Object	Consider converting to a regular table format, as Tibero does not support cluster objects.
XML Type	Change sys.xmltype in the column definitions of table creation statements to xmltype.
BitMap Index	Consider converting to a regular index, as this is not supported in Tibero.

Check DB Link

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;

Check tnsnames.ora file

cat $ORACLE_HOME/network/admin/tnsnames.ora

Check Job

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;

Check Cluster Object

SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_TYPE LIKE '%CLUSTER%' 
AND OWNER NOT IN ('SYS');

Check XML Type

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

Check BitMap Index

SELECT *
FROM DBA_INDEXES
WHERE INDEX_TYPE LIKE '%BITMAP%';

---

2. Migration

This section explains the methods for executing the actual migration. The following is the migration process.

1) Pre-Migration Preparation

2) Extraction of migration DDL Scripts

3) Adjustment of migration DDL Scripts

4) Migration

5) Migration validation

Refer to the content of each section for detailed explanations of each process.

1) Pre-Migration Preparation

The following is the standard order for creating objects during migration.

Steps 4 through 8 may need to be adjusted based on the dependencies of the client’s objects to suit their specific requirements.

Steps	Object	Description
1	Tablespace	-
2	User	-
3	Table	-
4	Synonym	It’s not an issue if the source object doesn’t exist at the time of creation, but it’s recommended to create it first, as it will automatically become VALID when queried later.
5	View	Errors may occur due to dependencies.
6	Sequence	-
7	Data Migration
8	psm(=pl/sql)	Use tools such as T-UP or Table Migrator to migrate the data.
9	Index	This applies to packages, procedures, and functions, where errors may also occur due to dependencies.
10	Constraint	-
11	Trigger	-
12	Grant	-
13	Other (C, Java external procedure, Job , etc.)	-

2) Extract Migration DDL Scripts

Use the T-UP DDL export feature to extract Oracle DDL statements.

Other tools that can extract scripts may also be used.

Extract DDL Scripts for Key Objects of the Database

Extract the DDL statements for tablespace, user, and table.

(1) T-UP > Migrator

Select “Migrator”

[Figure 9] Migrator of T-up

[Figure 9] Migrator of T-up

(2) Source Database > Type Conversion

Select Data Types Corresponding to ORACLE

[Figure 10] Type Conversion

[Figure 10] Type Conversion

(3) Schema Object

• Select “Independent Objects” Tablespace and User.

• Select “Dependent Objects” Table.

• Select “Schema” Table.

[Figure 11] Schema Objection

[Figure 11] Schema Objection

(4) Migration Option

• Deactivate “Data Transfer” and “DDL Execution”.

• Select “Export DDL Script”, Specify “Filename” and “Path”.

• If you want to separate files by schema, select “Create Individual DDL Script” Files.

[Figure 12] Migration Option

[Figure 12] Migration Option

(5) Target Database > Summary

Check the export target.

[Figure 13] Summary

[Figure 13] Summary

(6) Data Migration

Execute “Migrate“.

[Figure 14] Data Migration

[Figure 14] Data Migration

(7) Result > “close”

“In the "Result" stage, execution information is displayed, and DDL export information is not shown.

[Figure 15] Result

[Figure 15] Result

(8) Check file

Check that the DDL statements were generated correctly.

[Figure 16] Check file

[Figure 16] Check file

Extract DDL Scripts for Other Database Objects

Extract the remaining objects (DDL) except for Tablespace, User, and Table.

(1) T-UP > Migrator

Select “Migrator”.

[Figure 17] Select Migrator

[Figure 17] Select Migrator

(2) Source Database > Type Conversion

Select data types corresponding to ORACLE . The table is not included and is therefore not relevant to your selection.

[Figure 18] Type Conversion

[Figure 18] Type Conversion

(3) Schema Object

• Select “Dependent Objects” for extraction objects.

• Select “Schema” Table to include tables for selection and dependency.

[Figure 19] Schema Objection

[Figure 19] Schema Objection

(4) Migration Option

• Deactivate “Data Transfer and “DDL Execution”.

• Select “Export DDL Script”, Specify “Filename” and “Path”.

• If you want to separate files by schema and object, select “Create Individual DDL Script” Files.

[Figure 20] Migration Option

[Figure 20] Migration Option

(5) Target Database > Summary

Check the extraction target.

[Figure 21] Summary

[Figure 21] Summary

(6) Data Migration

Execute “Migrate“.

[Figure 22] Data Migration

[Figure 22] Data Migration

(7) Result > “close”

In the “Result” stage, execution information is displayed, and DDL export information is not shown.

[Figure 23] Result

[Figure 23] Result

(8) Check file

Check that the DDL statements were generated correctly.

[Figure 24] Check file

[Figure 24] Check file

3) Adjust Migration DDL Scripts

Organize and modify the extracted script so that it can be executed in Tibero.

DDL Scripts for Key Objects in the Database

• Adjust Tablespace, User, Table DDL Scripts The filename for the tablespace, user, and table script is export_ddl.sql.

• Tablespace DDL When Tibero DB is created, system, undo, and default temp tablespaces already exist, so don’t need to be created. But, temp tablespaces other than the default temp tablespaces must be migrated. (e.g., TEMP1) Exclude Oracle-specific tablespaces (e.g., SYSAUX) Modify the path, filename, or size of the data file as needed. Comment out items that are not supported in Tibero.

The following explains the items to be commented out in the tablespace script.

Item	Description
BLOCKSIZE 4K	Since Tibero does not specify a block size for each tablespace, it should be commented out. This is fixed when the database is created through initialization parameters, and it is generally set to 8KB.
SEGMENT SPACE MANAGEMENT MANUAL	Since Tibero only supports the AUTO setting, the MANUAL setting should be changed to AUTO.
FLASHBACK ON	Since Tibero supports flashback feature in full mode through parameters, this should be commented out.
TABLESPACE GROUP	Since TABLESPACE GROUP is not supported, it should be commented out. Consider splitting multiple data files within a single tablespace.

• User DDL Check the passwords for each user. (Contact the Oracle DB administrator.) User passwords are set to the default "tibero." This applies only to users targeted for migration. Users that are automatically created during Oracle installation do not need to be migrated. Comment out any items that are unsupported in Tibero.

Item	Description
GRANT UNLIMITED TABLESPACE TO USER_NAME	Tibero uses UNLIMITED by default, and this syntax is not supported, so it should be commented out.
ALTER USER USERNAME QUOTA UNLIMITED ON USERNAME	Since the per-user QUOTA feature is not supported, it should be commented out.

• Table DDL Due to the character set migration from Oracle, an Active Value error may occur.

If an error occurs in the column type, refer to "Oracle" on Data Types.

DDL Scripts for Other Database Objects

• Adjust DDL Scripts for Objects other than Tablespace, User, and Table The extracted filename is export_other_ddl.sql.

• List of Other Objects

  Index View

  Constraint

  Sequence Synonym

  Materialized View

  Library

  Function

  Procedure Package

  Type

  Trigger

  Object Privilege

  Job

  DBMS_SCHEDULER

  External Procedure

Since it is not included in the DDL for other objects, refer to Oracle to create it

4) Migration

DDL Scripts for Major Database Objects

(1) Upload the export_ddl.sql file to the Tibero database server.

If an error occurs in the column type, refer to "Oracle" on Data Types.

(2) Execute export_ddl.sql.

$ tbsql sys/tibero


tbSQL


Connected to Tibero.


SQL> spool export_ddl.log Spooling is started.

SQL> @export_ddl.sql
-- Check for Errors Through the spool File

SQL> spool off
Spooling is stopped: export_ddl.log

Data magration

During the data migration process, various tools are used to perform the migration.

T-UP

• Execute migration from the client PC using T-UP.

• Oracle → T-UP(Client PC) → Tibero

Table Migrator

• Execute migration in Tibero using Table Migrator.

• Oracle → Tibero

DB Link

• Execute migration from Tibero to Oracle using Oracle DB Link.

• Oracle → Tibero

For more information about using each tool, refer to the manual for each tool.

DDL Scripts for Other Database Objects

1) Upload the export_other_ddl.sql file to the Tibero database server.

• If there are many users or if the migration needs to be performed repeatedly, it is recommended to create a shell (or batch) script for the process.

• When performing repeated migrations, clearly organize dependencies and exceptions during the first migration to ensure that errors do not occur during the second or third migrations by including preparatory steps in the script.

• Use a shell program to automatically create objects, and verify the process as follows.

• After all users have been created, compare the object count with Oracle to ensure nothing is missing.

• If any objects were not created successfully, identify and resolve the issue, then manually create the objects and proceed to the next step.

2) Execute export_other_ddl.sql.

$ tbsql sys/tibero


tbSQL


Connected to Tibero.


SQL> spool export_other_ddl.log Spooling is started.

SQL> @export_other_ddl.sql
-- Check for Errors Through the spool File

SQL> spool off
Spooling is stopped: export_other_ddl.log

• Synoym It is not a problem to creat it even though the original object referenced by a synonym does not exist. After the original object is created, it will automatically be VALID when used.

• View When creating views, errors may occur due to dependencies or object permissions. Even if compilation errors occur during the initial creation, proceed to create all objects first, then refer to "Object Status Change" to validate the objects.

The following is an example of checking dependencies.

SELECT OWNER, NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE 
FROM DBA_DEPENDENCIES
WHERE TYPE='VIEW'
AND REFERENCED_TYPE NOT IN ('TABLE', 'SYNONYM') 
AND REFERENCED_NAME NOT IN ('DUAL')
AND REFERENCED_NAME NOT LIKE 'DBA_%'
AND OWNER NOT IN ('SYS', 'ORANGE','MDSYS', 'EXFSYS', 'XDB', 'SYSMAN', 'DBSNMP', 'SYSTEM' )
AND REFERENCED_OWNER NOT IN ('SYS', 'ORDSYS', 'WMSYS', 'MDSYS','EXFSYS', 'XDB',
'SYSMAN','SYSTEM','OLAPSYS', 'APEX_030200', 'CTXSYS') 
ORDER BY 1, 2;

• Sequence To prevent any changes to Oracle’s current values during the final migration, quit the application before proceeding.

• PSM(=PL/SQL) Consider the following points when creating PSM: - Errors may occur due to dependencies or object permissions. Even if compilation errors occur initially, complete the creation of all objects first, then refer to "Object Status Change" to validate the objects. - The error "TBR-8053: Not authorized" may occur if there is no permission for the relevant object. In this case, grant the necessary permissions and recreate the object. - Errors might occur within queries in PSM. For details, refer to "Application Query Modifications" to make the required changes. - Since Tibero does not support object types, bypass this by declaring the type within a package.

The following is to create object type by bypassing it with the type in the package.

• Oracle

CREATE OR REPLACE TYPE DOCUROLE_ID_OBJ IS OBJECT(DOCUROLE_ID VARCHAR2(6));
/
CREATE OR REPLACE TYPE DOCUROLE_ID_TAB IS TABLE OF DOCUROLE_ID_OBJ;
/
CREATE OR REPLACE FUNCTION USER_DOCUROLESET(PARAM IN VARCHAR2) 
RETURN DOCUROLE_ID_TAB
IS
L_DOCUROLE_ID_TAB DOCUROLE_ID_TAB := DOCUROLE_ID_TAB(); 
BEGIN
L_DOCUROLE_ID_TAB.EXTEND; 
L_DOCUROLE_ID_TAB(1).DOCUROLE_ID := 'test'; 
RETURN L_DOCUROLE_ID_TAB;
END;
/

• Tibero

CREATE OR REPLACE PACKAGE "PK_TYPES" AS
TYPE DOCUROLE_ID_OBJ IS RECORD(DOCUROLE_ID VARCHAR2(6)); 
TYPE DOCUROLE_ID_TAB IS TABLE OF DOCUROLE_ID_OBJ;
END;
/
CREATE OR REPLACE FUNCTION USER_DOCUROLESET(PARAM IN VARCHAR2) 
RETURN PK_TYPES.DOCUROLE_ID_TAB
IS
L_DOCUROLE_ID_TAB PK_TYPES.DOCUROLE_ID_TAB := PK_TYPES.DOCUROLE_ID_TAB(); 
N INTEGER := 0;
BEGIN 
L_DOCUROLE_ID_TAB.EXTEND;
L_DOCUROLE_ID_TAB(1).DOCUROLE_ID := 'test'; 
RETURN L_DOCUROLE_ID_TAB;
END;
/

• Index

The following explains the comparison of index counts between Oracle and Tibero after index creation.

Situation	Description
Fewer Indexes in Tibero	Consider that indexes may be automatically created when constraints are generated. Compare after all constraints are created.
More Indexes in Tibero	Check whether the additional indexes were automatically created for LOB columns within tables. (This automatic creation also occurs in Oracle 11g r2, but may not happen in earlier versions of Oracle.)

• Constraint Compare with the items checked in "Constraint Count Check" from "Migration Validation".

• Trigger When migrating a disabled trigger from Oracle, a syntax error may occur, so process it as follows: - Comment out the disable syntax. - Apply the disable setting in Tibero

alter trigger trigger_name disable;

• Grant The following are unsupported Grant items in Tibero, which should be excluded when calculating the Grant count.

Item	Description
COMMIT REFRESH	When creating an Mview, it is supported as a creation syntax and not as a privilege.
QUERY REWRITE	When creating an Mview, it is supported as a creation syntax and not as a privilege.
DEBUG	It is a PL/SQL DEBUGGING privilege currently, but not supported in Tibero.
FLASHBACK	This is supported in the form of parameters, but not as a privilege.

If the above privileges are granted along with regular privileges,, not all permissions will be granted, so it is necessary to find and delete unsupported privileges and then re-execute.

• Others Refer to Oracle to create other items such as external procedures and jobs.

Object Status Change

Due to dependencies and object permissions, an object's status may be INVALID at the time of creation. Process this as follows:

(1) Check Object

• Check the count by status

col owner format a20 
set pages 500
select owner, object_type, status, count(*) 
from dba_objects
group by owner, object_Type, status
order by owner, object_type, status;

• Check INVALID Object

set linesize 120
col object_name format A30 
col owner format a20
select owner, object_name, object_type, status 
from dba_objects
where status='INVALID';

(2) Execute recompile

Log in with the 'sys' account and recompile the invalid objects in a specific schema as shown below.

$ tbsql sys/tibero

tbSQL

Connected to Tibero.

SQL> exec utl_recomp.recomp_serial(schema=>'MIGRATION_USER');

PSM completed.

(3) Recompile and Check for Errors by Object Log in as the relevant user, recompile each object, and check for any errors.

• Recompile View

select * from view_name where rownum < 1;

• Recompile PSM(=PL/SQL)

alter function function_name compile; 
alter procedure procedure_name compile;

• Check PSM(=PL/SQL) error

show errors

• Recompile Index

alter index index_name rebuild;

5) Migration validation

After the migration is complete, the main items to check in the database side are the number of objects and data validation.

Object count

• Table

set linesize 150
column owner format a30

select
    owner,
    count(table_name) as "table_cnt"
from
    dba_tables 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

• View

set linesize 150
column owner format a30

select
    owner,
    count(view_name) as "view_cnt"
from
    dba_views 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

• Synonym

set linesize 150 
column owner format a30

select
    owner,
    count(1) as "synonym_cnt" 
from
    dba_synonyms
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1') 
group by
    owner
order by owner;

• Sequence

set linesize 150
column sequence_owner format a30 

select
    sequence_owner,
    count( sequence_name ) as "sequence_cnt"
from
    dba_sequences
where
    sequence_owner not in
('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1') 
group by
    sequence_owner
order by
    sequence_owner;

• Package

set linesize 150
column owner format a30

select
    owner,
    count( name ) as "package_cnt"
from
    dba_source 
where
    type = 'PACKAGE' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

• Package Body

set linesize 150
column owner format a30


select
    owner,
    count( name ) as "package_body_cnt"
from
    dba_source 
where
    type = 'PACKAGE BODY' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

• Function

set linesize 150
column owner format a30

select
    owner,
    count( name ) as "function_cnt"
from
    dba_source 
where
    type = 'FUNCTION' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1') 
group by
    owner 
order by
    owner;

• Procedure

set linesize 150
column owner format a30


select
    owner,
    count( name ) as "procuedure_cnt"
from
    dba_source 
where
    type = 'PROCEDURE' and
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

• Index

set linesize 150
column owner format a30


select
    owner,
    count( index_name ) as "index_cnt"
from
    dba_indexes 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;    

• Constraint

set linesize 150
column owner format a30


select
    owner, 
    constraint_type,
    count( constraint_name ) as "constraint_cnt"
from
    dba_constraints 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner, constraint_type 
order by
    owner ,constraint_type;

• Grant

set linesize 150 
column col format a30

select
    owner,
    count(1) as "grant_cnt"
from
    dba_tab_privs 
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

• Trigger

set linesize 150
column owner format a30


select
    owner,
    count(trigger_name) as "trigger_cnt"
from
    dba_triggers
where
    owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by
    owner 
order by
    owner;

Data Validation

• Oracle DB Link

Count Comparison

select 'tbl1' as table_name, count(*) as oracle_count, count(*) 
- (select count(*) from tbl1 where rownum = 1) as diff from tbl1@ora;

Comparison of Minus Results

select * from tbl1@ora minus 
select * from tbl1;

• T-UP Data Transfer Verification

(1) T-UP > Migrator

Select “Migrator”

[Figure 25] Migrator of T-up

[Figure 25] Migrator of T-up

(2) Source Database > Type Conversion

Select Data Types Corresponding to Oracle.

[Figure 26] Type Conversion

[Figure 26] Type Conversion

(3) Schema Object

• Select “Dependent Objects” Table.

• Select Schemas and Tables to migrate in "Schemas".

[Figure 27] Schema Objection

[Figure 27] Schema Objection

(4) Migration Option

• Activate “Data Transfer”

• Select “Verification”

[Figure 28] Migration Option

[Figure 28] Migration Option

(5) Target Database > Summary

• Check corresponding schemas and tables between Source and Target.

[Figure 29] Summary

[Figure 29] Summary

(6) Data Migration

• Execute “Migrate“.

[Figure 30] Data Migration

[Figure 30] Data Migration

(7) Result

• Type: Check success rate for designated tables in data.

[Figure 31.1] Result

[Figure 31.1] Result

• Type: Click on "Data" to view verification results.

[Figure 31.2] Result

[Figure 31.2] Result

---

3. Application Migration

After completing the database migration, proceed with the application migration. This may require significant modifications to interfaces and application queries.

1) Interface Modification

JDBC Copy tibero6-jdbc.jar driver to the Application's driver management folder in $TB_HOME/client/lib/jar folder.

• Oracle

String jdbc_url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL"; 
String user = "oracle_username";
String passwd = "oracle_passwd"; 
Class.forName("oracle.jdbc.driver.OracleDriver");

• Tibero

String jdbc_url = "jdbc:tibero:thin:@127.0.0.1:8629:tibero"; 
String user = "tibero_username";
String passwd = "tibero_passwd";
Class.forName("com.tmax.tibero.jdbc.TbDriver");

ODBC

Install Tibero ODBC.

• Oracle

DRIVER={Microsoft ODBC for Oracle}; 
server=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)) 
(CONNECT_DATA=(SERVICE_NAME=utf8)));UID=scott;PWD=tiger;

• Tibero

DRIVER={Tibero 7 ODBC Driver};SERVER=127.0.0.1;PORT=8629
;DB=t7;UID=dbtech;PWD=dbtech;

OLE DB

Install Tibero OLE DB.

• Oracle

Provider=msdaora;Data Source=MyDB;User Id=myUsername
;Password=myPassword;

• Tibero

Provider=tbprov.Tbprov.6;Data Source=MyDB;User ID=dbtech;Password=dbtech
;Updatable Cursor=True;OLE DB Services=-2

2) Application Query Modifications

Application query modification is done when necessary, typically in the following situations:

Case 1. When a Syntax Error occurs

.	Situation	Solution
1	When writing an SQL statement, if you execute a join or similar operation without assigning an alias to the table or subquery clause in the from clause, a syntax error will occur if duplicate columns exist.	Assign alias to avoid duplication.
2	When using a query like UNION ALL to view results, a syntax error will occur if the main query has a specific column as a number type and a subquery as a varchar type.	Use the same data type in the same column clause.
3	A syntax error will occur in PSM (PL/SQL) if variables with the same name are declared within the same scope.	Use the variable name from the final declaration and delete earlier declarations of variables with the same name.

Case 2. When the results are not displayed in a sorted order

.	Situation	Solution
1	In Oracle, results may be displayed in sorted form even without an ORDER BY clause, whereas in Tibero, the results may not. However, in the case of queries to check only some values ​​by forcibly applying indexes, etc. to a large table, Tibero will sort and output them without using order by clause.	If you need sorted results, be sure to apply ORDER BY.

Case 3. When an error occurs when compiling after writing PSM (PL/SQL)

.	상황	대응방법
1	When compiling after writing PSM (PL/SQL), Tibero checks whether the columns, etc., for objects in the internal SQL statements actually exist. If they are not valid, Tibero will throw an error. In contrast, Oracle may still successfully compile in such situations, but a runtime error may occur later.	Use actual existing objects and correct column names.