The chapters in this part explain the procedures for upgrading the source DB, Tibero 6.
The steps for the operations are as follows:
Checklist and Check Methods
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 requred 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.
