Source DB Operations

The chapters in this part explain the procedures for upgrading the source DB, Tibero 6.

The steps for the operations are as follows:

1. Checklist and Check Methods

2. Executing Export

1. Checklist and Check Methods

Check below items to upgrade from Tibero 6 to Tibero 7.

• CHARACTERSET

• Check Redo

• Check Undo

• Check Temp Tablespace

• Check User Tablespace

• Check Log mode

• Check Control file

• Check JOB

• Check DB Link (Tibero to Tibero, Tibero to Oracle, etc.)

• Check External Procedure usage

• Check Wrapped PSM(PL/SQL)

• Check Directory

• Check Object Count

• Check Constraint

CHARACTERSET

Check the value for NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET. Refer to below when creating a target DB.

SQL> SET LINESIZE 120 
SQL> COL name FOR a30
SQL> COL value FOR a20 
SQL> SELECT name, value 
FROM sys._dd_props
WHERE name like '%CHARACTERSET%'
ORDER BY 1;

NAME                                VALUE
--------------------------------    ----------
NLS_CHARACTERSET                    UTF8
NLS_NCHAR_CHARACTERSET              UTF16

Check Redo

Check MEMBER per each log group, and size(mb) and MEMBERS per each group.

SQL> SET LINESIZE 120
SQL> COL member FOR a50
SQL> SELECT * FROM v$logfile;

GROUP#             STATUS  TYPE   MEMBER
-----------      --------  -----------------------------
0                  ONLINE /data2/tb6/tbdata/log001.log
0                  ONLINE /data2/tb6/tbdata/log002.log
1                  ONLINE /data2/tb6/tbdata/log003.log
1                  ONLINE /data2/tb6/tbdata/log004.log
2                  ONLINE /data2/tb6/tbdata/log005.log
2                  ONLINE /data2/tb6/tbdata/log006.log

SQL> SELECT group#, bytes/1024/1024 as "size(mb)", members, archived FROM v$log;

GROUP#             size(mb)   MEMBERS    ARCHIVED
-----------      --------  -----------   -----------
0                   50        2          NO
1                   50        2          NO
2                   50        2          NO

Check Undo

Check the TABLESPACE_NAME of the UNDO being used in the database, along with the FILE_NAME, size, maxsize, and AUTOEXTENSIBLE status of the associated data files.

SQL> SET LINESIZE 120
SQL> COL tablespace_name FOR a20
SQL> COL file_name FOR a50
SQL> SELECT tablespace_name 
FROM dba_tablespaces
WHERE contents = 'UNDO';

TABLESPACE_NAME
--------------------
UNDO


SQL> show parameter UNDO_TABLESPACE;

NAME                  TYPE      VALUE
-----------------     --------  ---------
UNDO_TABLESPACE       STRING    UNDO

SQL> SELECT tablespace_name,
file_name,
bytes/1024/1024 "size(mb)", 
maxbytes/1024/1024 "maxsize(mb)", 
autoextensible
FROM dba_data_files
WHERE tablespace_name like 'UNDO%';

TABLESPACE_NAME  FILE_NAME                    size(mb) maxsize(mb) AUTOEXTENSIBLE
---------------- ---------------------------- -------- ----------- --------------
UNDO          /data2/tb6/tbdata/undo001.tdf   400      1024        YES

Check Temp Tablespace

Check the TABLESPACE_NAME using the Temp file, along with the FILE_NAME, size, AUTOEXTENSIBLE status, and maxsize of the respective file.

SQL> SET LINESIZE 150
SQL> COL tablespace_name FOR a30
SQL> COL file_name FOR a50
SQL> SELECT tablespace_name,
file_name, bytes/1024/1024 "size(mb)", 
autoextensible,
maxbytes/1024/1024 "max(mb)" 
FROM dba_temp_files;

TABLESPACE_NAME  FILE_NAME                      size(mb) AUTOEXTENSIBLE max(mb)
---------------- -----------------------------  -------- -------------- --------
TEMP             /data2/tb6/tbdata/temp001.tdf  400      YES            1024        

Check User Tablespace

Check the TABLESPACE_NAME for Tablespaces excluding the Default Tablespace, along with the FILE_NAME, size, maxsize, and AUTOEXTENSIBLE status of the data files used in each Tablespace.

SQL> SET linesize 150
SQL> COL tablespace_name FOR a30
SQL> COL file_name FOR a50
SQL> SELECT tablespace_name, 
file_name,
bytes/1024/1024 "size(mb)", 
maxbytes/1024/1024 "maxsize(mb)", 
autoextensible
FROM dba_data_files
WHERE tablespace_name not in('SYSTEM', 'UNDO', 'USR');

TABLESPACE_NAME  FILE_NAME                    size(mb) maxsize(mb) AUTOEXTENSIBLE
---------------- ---------------------------- -------- ----------- --------------
DBTECH_TBS       /tb6/tbdata/dbtech_tbs01.dtf 50       5120        YES
DBTECH_IDX       /tb6/tbdata/dbtech_idx01.dtf 50       1024        YES

Check Log mode

Check the log mode, and if it is set to ARCHIVELOG, review LOG_ARCHIVE_DEST, LOG_ARCHIVE_FORMAT, LOG_ARCHIVE_OVERWRITE, and LOG_ARCHIVE_RETRY_TIME.

If there are any settings different from the default values except log mode, configure them in the tip file of the Target DB.

SQL> SELECT log_mode FROM v$database; 
LOG_MODE
---------------
ARCHIVELOG

SQL> SHOW PARAM ARCHIVE
NAME                    TYPE     VALUE
----------------------- -------- ------------------------------------------
LOG_ARCHIVE_DEST        DIRNAME  /data2/tb6/tibero6/database/tb4/archive/ 
LOG_ARCHIVE_FORMAT      STRING   log-t%t-r%r-s%s.arc
LOG_ARCHIVE_OVERWRITE   Y_N      NO 
LOG_ARCHIVE_RETRY_TIME  UINT32   60

Check Control file

Refer to the path and file name modified when creating the target DB database and modify the path and file name appropriately.

SQL> alter database backup controlfile to trace 
as '/data2/tb6/tb6_export/controlfile_tb6.sql'; 
Database altered.
SQL> !cat /data2/tb6/tb6_export/controlfile_tb6.sql 
CREATE CONTROLFILE REUSE DATABASE "tb6"
LOGFILE 
GROUP 0 (
'/data2/tb6/tbdata/log001.log', 
'/data2/tb6/tbdata/log002.log' 
) SIZE 50M,
GROUP 1 (
'/data2/tb6/tbdata/log003.log', 
'/data2/tb6/tbdata/log004.log' 
) SIZE 50M,
GROUP 2 (
'/data2/tb6/tbdata/log005.log', 
'/data2/tb6/tbdata/log006.log' 
) SIZE 50M
NORESETLOGS 
DATAFILE
'/data2/tb6/tbdata/system001.tdf', 
'/data2/tb6/tbdata/undo001.tdf',
'/data2/tb6/tbdata/usr001.tdf',
'/data2/tb6/tbdata/dbtech_tbs01.dtf', 
'/data2/tb6/tbdata/dbtech_idx01.dtf' 
ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 100
CHARACTER SET MSWIN949 
NATIONAL CHARACTER SET UTF16 
;
--ALTER DATABASE MOUNT
---- Recovery is required in MOUNT mode. 
--ALTER DATABASE RECOVER AUTOMATIC; 
--ALTER DATABASE OPEN ;
---- Adding Tempfiles is required in OPEN mode.
-- ALTER TABLESPACE TEMP ADD TEMPFILE '/data2/tb6/tbdata/temp001.tdf' 
-- SIZE 400M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

Check JOB

Create JOB manually in the Target DB as it is not migrated.

SQL> SET LINESIZE 150
SQL> COL what FOR a30
SQL> COL interval FOR a30
SQL> COL username FOR a20
SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
SQL> SELECT job as job_no, 
b.username,
what,
to_char(next_date, 'yyyy/mm/dd HH24:MI:SS') as next_date, 
interval,
broken
FROM dba_jobs a, dba_users b 
WHERE a.schema_user=b.user_id;


JOB_NO USERNAME WHAT                        NEXT_DATE           INTERVAL  BROKEN
------ -------- --------------------------- ------------------- --------- ------
1       DBTECH  dbms_output.put_line('ok');  2013/06/30 00:00:00 SYSDATE+1 N

Check DB Link (Tibero to Tibero, Tibero to Oracle, etc.)

Create DB Link manually in the Target DB as it is not migrated.

SQL> SET LINESIZE 150
SQL> COL owner FOR a15
SQL> COL db_link FOR a20
SQL> COL username FOR a20
SQL> COL host FOR a10
SQL> COL created FOR a10
SQL> SELECT owner, db_link, username, host, created FROM dba_db_links;
OWNER           DB_LINK              USERNAME             HOST       CREATED
--------------- -------------------- -------------------- ---------- ----------
DBTECH          TBLINK                DBTECH2             tb6        2013/06/12

Check External Procedure usage

Check the external reference libraries, and if results are found, copy the post-processing files such as so and class files to the same path in the Target DB or recompile them in the Target DB. Libraries are not migrated, so they need to be manually created in the Target DB.

The following is an example of checking external reference libraries.

C External Procedure

SQL> SET LINESIZE 120
SQL> COL owner FOR a10
SQL> COL library_name FOR a30
SQL> COL file_spec FOR a50
SQL> COL status FOR a10
SELECT owner, library_name, file_spec, status FROM dba_libraries;

JAVA External Procedure

SET LINESIZE 120
COL owner FOR a10
COL name FOR a30
COL source FOR a50
SELECT owner, name, source FROM dba_java_classes;

The following is an example checkeing JAVA Class path.

SQL> SHOW PARAM JAVA_CLASS_PATH;
NAME             TYPE     VALUE
---------------- -------- --------------------------
JAVA_CLASS_PATH  DIRNAME  /data2/tb6/tbdata/java/

Check the configuration for $TB_HOME/bin/tbjavac and $TB_HOME/client/bin/tbjavaepa when referring to external libraries in JAVA External Procedure.

Check Wrapped PSM(PL/SQL)

Wrapped PL/SQL cannot be queried from the DB, so it is not possible to obtain the creation statements for manual creation. Therefore, the original creation statement is required for migration.

Create Wrapped PSM (PL/SQL) manually in the Target DB as it is not migrated.

The following is an example of checking Wrapped PSM (PL/SQL).

SQL> SET LINESIZE 120
SQL> COL owner FOR a30
SQL> COL object_name FOR a40
SQL> SELECT owner, object_name, functionable, wrapped 
FROM dba_procedures
WHERE wrapped = 'YES';
OWNER                      OBJECT_NAME              FUNCTIONABLE WRAPPED
------------------------- ------------------------- ------------ -------
DBTECH                     WRAP_TEST                NO           YES

Check Directory

Check if the directory used by the External table exists, and if it does, copy and recreate the corresponding file to match the system path in the Target DB.

SQL> SET LINESIZE 120
SQL> COL name FOR a20
SQL> COL path FOR a60
SQL> SELECT * FROM dba_directories;
NAME                 PATH
-------------------- ----------------------
SAM                  /data2/tb6/DIR_SAM

Check Object Count

Save the query results after execution and check the Object Count.

The saved results above will be used as a reference for verifying the migrated object count in “Validation”

SQL> SET LINESIZE 150
SQL> SET PAGES 500
SQL> COL owner FOR a20
SQL> SELECT owner, object_type, status, count(*)
FROM dba_objects
WHERE owner not in ('SYS', 'SYSCAT', 'SYSGIS', 'OUTLN')
GROUP BY owner, object_Type, status
ORDER BY owner, object_type, status;

OWNER 			 OBJECT_TYPE 		  STATUS  COUNT(*)
-------------------- --------------------        ------- ----------
DBTECH			 DATABASE LINK 		  VALID 	1
DBTECH 			 FUNCTION 		  VALID 	3
DBTECH 			 INDEX 			  VALID 	9
DBTECH 			 JAVA 			  VALID 	1
DBTECH			 LIBRARY 		  VALID 	1
DBTECH 			 PROCEDURE 		  VALID 	3
DBTECH			 SEQUENCE 		  VALID 	3
DBTECH 			 SYNONYM 		  VALID 	6
DBTECH 			 TABLE 			  VALID 	10
DBTECH 			 TRIGGER		  VALID	 	2
DBTECH 		         VIEW 			  VALID		1
DBTECH2			 FUNCTION 		  VALID		1
DBTECH2			 INDEX 			  VALID		7
DBTECH2			 LOB 			  VALID		2
DBTECH2			 PACKAGE 		  VALID		2
DBTECH2			 PACKAGE BODY 		  VALID		2
DBTECH2			 PROCEDURE 	  	  VALID		4
DBTECH2			 SEQUENCE 		  VALID		1
DBTECH2			 TABLE 			  VALID		7
DBTECH2			 TRIGGER 		  VALID 	1
DBTECH2			 VIEW 			  VALID 	1
PUBLIC 			 SYNONYM 		  VALID	        565
TIBERO 			 TABLE 			  VALID 	1

Check Constraint

Save the query results after execution and check the Constraint.

The saved results above will be used as a reference for verifying the migrated Constraint in “Validation”

SQL> SET LINESIZE 150
SQL> COL owner FOR a30
SQL> SELECT owner
,constraint_type
,count(constraint_name) as con_cnt
FROM dba_constraints
WHERE owner not in ('SYS', 'SYSCAT', 'SYSGIS', 'OUTLN')
GROUP BY owner, constraint_type
ORDER BY owner ,constraint_type;
OWNER 				CONSTRAINT_TYPE           CON_CNT
------------------------------ ---------------         ----------
DBTECH				C 				21
DBTECH				O 				0
DBTECH	 			P 				7
DBTECH	 			R 				3
DBTECH	 			U 				1
DBTECH2	 			C 				7
DBTECH2	 			P 				7
DBTECH2	 			R 				1

---

2. Export

1) Exiting all application connected to DB

End all applications connected to the Source DB to prevent data changes during the export.

The following is an example of checking the connected sessions.

SQL> SELECTcount(*) FROM v$session;

COUNT(*)
---------- -
2

2) Stopping JOB execution

Stop JOB execution to prevent data changes during the export.

The following is an example of stopping JOB number 100.

BEGIN
DBMS_JOB.BROKEN(100, true);
END;
/

3) Executing tbexport

Execute the export in Database(FULL) mode of tbexport.

The following is the usage for tbexport.

tbexport username={sys | dba account} password=<password> port=<port_no> sid=<SID> 
file=<file name> log=<log-file name> full=y script=y

Category	Details
Username	The account for exporting DB (Choose one from Sys or DBA account.)
Password	The password for exporting DB account
port	The port number for exporting DB
sid	The name for exporting DB
file	The name for created file
log	The name for created log

The following is an example of tbexport usage for Tibero 6 on Source DB server.

tbexport username=sys password=pwd port=8629 sid=tibero6
file=tibero6_all.dat log=tibero6_exp.log full=y script=y