Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
클라우드 최적화 DBMS Tibero에 대한 소개부터 처음 사용 시 필요한 정보들을 안내합니다.
Linux 환경에서 Tibero DB 설치 방법 (Tibero DBMS Install on Linux)을 안내합니다.
티베로 DBMS 사용 가이드를 주제별로 안내합니다. 설치부터 전환, 관리, 개발, 보안 관련 정보를 확인하세요.
Windows 환경 내 티베로 DB 설치 방법을 안내합니다.
Linux와 Windows 환경 내 티베로 DBMS 클라이언트 설치 관련하여 사전 준비사항, 고려사항, 설치 방법에 대해 설명합니다.
Windows 환경 내 티베로 DBMS 클라이언트 설치 관련하여 사전 준비사항, 고려사항, 설치 방법에 대해 설명합니다.
(SQL, tbPSM, Text and tbESQL)
Linux, Windows 등 다양한 환경에서 Tibero DB 설치 방법을 안내합니다.
타 DB, 대용량 DB 등을 Tibero DBMS 전환 이관 방법에 대해 안내합니다.
DBMS 업그레이드 검증 방법 입니다.
Table - DBA_TABLES
Partition Table - DBA_TAB_PARTITIONS
Subpartition Table - DBA_TAB_SUBPARTITIONS
View - DBA_VIEWS
Mview - DBA_MVIEWS
Synonym - DBA_SYNONYMS
Sequence - DBA_SEQUENCES
Package - DBA_SOURCE(TYPE = 'PACKAGE') / DBA_OBJECTS(OBJECT_TYPE= 'PACKAGE')
Package Body - DBA_SOURCE(TYPE = 'PACKAGE BODY') / DBA_OBJECTS(OBJECT_TYPE= 'PACKAGE BODY')
Function - DBA_SOURCE(TYPE = 'FUNCTION') / DBA_OBJECTS(OBJECT_TYPE= 'FUNCTION')
Procedure - DBA_SOURCE(TYPE = 'PROCEDURE') / DBA_OBJECTS(OBJECT_TYPE= 'PROCEDURE')
Index - DBA_INDEXES
Partition Index - DBA_IND_PARTITIONS
Subpartition Index - DBA_IND_SUBPARTITIONS
Constraint - DBA_CONSTRAINTS
Grant - DBA_TAB_PRIVS / DBA_ROLE_PRIVS / DBA_SYS_PRIVS
Trigger - DBA_TRIGGERSUNIX 환경 내 Tibero Database Client 수동 모드 설치 방법을 안내합니다.
상황별 DBMS 성능 개선 방법에 대해 설명합니다.
Tibero DB SQL Execution Work Area 크기 산정 방법에 대해 설명합니다.
DB 전환 전 고려사항에 대해서 설명합니다.
Tibero DBMS Temp 테이블 스페이스 크기 산정방법에 대해 설명합니다.
티베로 데이터베이스 환경 파일 점검에 대한 위험 분석, 위험 영향, 해결 방안을 설명합니다.
티베로 DBMS 업그레이드 방법을 안내합니다.
$ useradd -d /home/tibero7_client -g dba tibero7_client
$ passwd tibero7_client$ ls
tibero7-bin-FS02-windows64_2008-269353-20240314021215.zip
$ unzip tibero7-bin-FS02-linux64_3.10-254994.tar.gz```shell
$ vi ~/.bash_profile
export TB_HOME=/home/tibero7_client/tibero7
export TB_SID=tibero
export PATH=$TB_HOME/bin:$TB_HOME/client/bin:$PATH
export LD_LIBRARY_PATH=$TB_HOME/client/lib:$LD_LIBRARY_PATH
$ source ~/.bash_profile$ cd $TB_HOME/config
$ sh gen_tip.sh Using TB_SID "tibero"
/home/tibero7_client/tibero7/config/tibero.tip generated
/home/tibero7_client/tibero7/config/psm_commands generated
/home/tibero7_client/tibero7/client/config/tbdsn.tbr generated.
Running client/config/gen_esql_cfg.sh
Done.$ cd $TB_HOME/client/config
$ ls
gen_esql_cfg.sh tbco_file_id tbdsn.tbr tbimport_hl.config.default tbpcb.cfg
tbc_file_id tbdfdump.cfg tbertl.cfg tbpc.cfg
$ vi tbdsn.tbr
#------------------------------------------------------------
# /home/tibero7_client/tibero7/client/config/tbdsn.tbr
# Network Configuration File.
# Generated by gen_tip.sh at 2023. 06. 12. (월) 14:30:16 KST
tibero=(
(INSTANCE=(HOST=192.168.xxx.xxx)
(PORT=8629)
(DB_NAME=tibero)
)
)/tbsql tibero/tmax
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> SELECT
IPADDR, USERNAME
FROM
V$SESSION; 2 3 4
IPADDR
----------------------------------------------------------
USERNAME
-----------------------------------------------------------------------------------
192.168.xxx.xxx
TIBERO# MULTI INSERT INDEXES 적용
$cat control.ctl
LOAD DATA
...
MULTI INSERT INDEXES
...
(...)
# FAS BUILD INDEXES 적용
$cat control.ctl
LOAD DATA
...
FAST BUILD INDEXES
...
(...)Single : jdbc:tibero:thin:@192.168.11.49:8629:tibero
TAC : "jdbc:tibero:thin:@(description=
(failover=on)(load_balance=on)
(address_list=(address=(host=192.168.11.49)(port=8629))
(address=(host=192.168.11.50)(port=8629))
)(DATABASE_NAME=tibero7))”DRIVER={Tibero 6 ODBC Driver};SERVER=127.0.0.1;PORT=8629;UID=tibero;PWD=tmax;DRIVER={Tibero 7 ODBC Driver};SERVER=127.0.0.1;PORT=8629;DB=tb7;UID=tibero;
PWD=tmax;$TB_HOME/client/bin/ (해당 경로에 연결 대상 DB에 따른 Gateway 존재)$TB_HOME/bin/psmjavac
$TB_HOME/client/bin/tbjavaepa$TB_HOME/bin/psmjavac
$TB_HOME/client/bin/tbjavaepajava -classpath migrator_cli.jar:ojdbc6.jar:postgresql-42.2.27.jre6.jar:msllogger-
14.jar:toolcom.jar:internal-jdbc-16.jar:antlr-4.3-complete.jar com.m.migrator.Main "$@”sh migrator.sh SOURCE_TYPE=ORACLE \
SOURCE_URL=jdbc:oracle:thin:@127.0.0.1:1521:orcl \
SOURCE_USER=system SOURCE_PASSWORD=manager SOURCE_LOGIN_AS=NORMAL \
SOURCE_SCHEMA=scott SOURCE_TABLE=emp \
TARGET_URL=jdbc:internal:thin:@127.0.0.2:8629:t7 \
TARGET_USER=dbtech TARGET_PASSWORD=dbtechsh migrator.sh PROPERTY_FILE=migrator.properties$ chmod 640 [Control 파일|Redo 로그 파일|데이터 파일]업그레이드 Target DB인 Tibero 7 업그레이드 작업 절차를 설명합니다.
Tibero DBMS 설치 및 운영하기 전 효율적 시스템 사용을 위한 DB 물리 설계 방법을 제시합니다.
Windows 환경 내 Tibero DB 클라이언트 수동 모드 설치 및 제거방법을 설명합니다.
Tibero Database Undo 테이블스페이스 크기 산정 방법에 대해 설명합니다.
기존 데이터베이스를 Tibero DBMS로 전환하는 Table Migrator 유틸리티에 대해 안내합니다.
DB 권한 관리에 대한 위험 분석, 위험 영향, 해결 방안을 설명합니다.
tb6=(
(INSTANCE=(HOST=192.168.1.129)
(PORT=8629)
(DB_NAME=tb6)
)
hidden=(
(INSTANCE=(HOST=192.168.1.129)
(PORT=8630)
(DB_NAME=tb6)
)$TB_HOME/client/config/tbpc.cfg
$TB_HOME/client/config/tbpcb.cfg$TB_HOME/client/epa/java/config/tbepa.cfg
(Tibero 6의 경우 : $TB_HOME/client/epa/java/config/epa.cfg)$TBGW_HOME/oracle/config/tbgw.cfg
$tbJavaGW/tbgw.cfg(Tibero 6의 경우 : $tbJavaGW/jgw.cfg)create database
user sys identified by tibero
character set MSWIN949
national character set UTF16
logfile group 0 ('log001.log','log002.log') size 50M,
group 1 ('log011.log','log012.log') size 50M,
group 2 ('log021.log','log022.log') size 50M
maxdatafiles 8192
maxlogfiles 100
maxlogmembers 8
noarchivelog
datafile 'system001.dtf' size 1G autoextend on next 256M maxsize unlimited
default tablespace USR
datafile 'usr001.dtf' size 1G autoextend on next 256M maxsize unlimited
default temporary tablespace TEMP
tempfile 'temp001.dtf' size 1G autoextend off
undo tablespace UNDO
datafile 'undo001.dtf' size 1G autoextend off
SYSSUB datafile 'syssub001.dtf' size 1G autoextend on next 32M maxsize unlimited;$TB_HOME/scripts/system.shtbimport username={sys | dba계정} password=<password> port=<port_no> sid=<SID>
file=<file name> log=<log-file name> full=y script=y ignore=ytbimport username=sys password=pwd port=8629 sid=tb7
file=tibero6_all.dat log=tibero7_imp.log full=y ignore=yDECLARE
job_no number;
BEGIN
DBMS_JOB.SUBMIT(job_no,'dbms_output.put_line(''ok'');',SYSDATE,'SYSDATE+1');
END;
/CREATE DATABASE LINK olink CONNECT TO scott IDENTIFIED BY 'tiger' USING 'oragw';CREATE OR REPLACE LIBRARY extproc IS '/home/tibero/cepa/libextproc.so';CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaExtproc" AS
public class SimpleMath {
public static int findMax(int x, int y) {
if (x >= y) return x;
else return y;
}
}
/CREATE OR REPLACE PROCEDURE wrap_test WRAPPING
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/CREATE OR REPLACE DIRECTORY dir_ext AS '/home/tibero/t7/work/external/external'
/tbdown immediatetbboot -t mounttbsql sys/tibero
SQL> alter database archivelog;tbdown immediate
tbboottbsql sys/tibero
SQL> SHOW PARAM LOG_ARCHIVE_DEST
NAME TYPE VALUE
----------------------- -------- --------------------------------------------
LOG_ARCHIVE_DEST DIRNAME /home/tibero/t7/tibero7/database/t7/archive/
SQL> alter system switch logfile;
System altered.
SQL> !ls -la /home/tibero/t7/tibero7/database/t7/archive/
total 23572
-rw-------. 1 tibero dba 24126976 Apr 4 22:40 log-t0-r0-s1.arc
SQL> select log_mode from v$database;
LOG_MODE
-------------- -
ARCHIVELOGUTL_HTTP, UTL_FILE, DBMS_RANDOM, DBMS_LOB, DBMS_SQL, DBMS_JOB,
DBMS_OBFUSCATION_TOOLKITSQL> REVOKE EXECUTE ON <패키지명> FROM PUBLIC;LSNR_INVITED_IP="192.168.20.0/24;192.168.10.101"*** listener started and got into main loop ***
[INVITED-IP] 192.168.20.0 / 255.255.255.0
[INVITED-IP] 192.168.10.101 / 255.255.255.255LSNR_INVITED_IP_FILE="/home/tibero/tibero7/invited.list"# WAS_JEUS
192.168.22.20
192.168.44.10
### END OF FILE ####AUDIT Setting
AUDIT_TRAIL=OS
AUDIT_SYS_OPERATIONS=Y
AUDIT_FILE_DEST=/tibero/tbdata/audit/SQL> select * from v$sga;
NAME TOTAL USED
------------------------------------- -------------------- ---------------
SHARED MEMORY 1258291200 1258291200
FIXED MEMORY 1032962080 1032962080
SHARED POOL MEMORY 225312768 222298112
SHARED POOL ALLOCATORS (LC) 25165824 24048688
SHARED POOL ALLOCATORS (DD) 16777216 3620240
SHARED POOL ALLOCATORS (SLAB) 79691776 76092320
SHARED POOL ALLOCATORS (MISC) 100663296 98956536
SHARED POOL ALLOCATORS (Total) 222298112 202717784
Database Buffers 838860800 838860800
Redo Buffers 10485760 10485760
10 rows selected.
SQL> show param total_shm_size
NAME TOTAL USED
------------------------------------- -------------------- ---------------
TOTAL_SHM_SIZE INT64 1258291200SQL> show param db_cache_size
NAME TOTAL USED
------------------------------------- -------------------- ---------------
DB_CACHE_SIZE UINT64 838860800SQL> show param log_buffer
NAME TOTAL USED
------------------------------------- -------------------- ---------------
FLASHBACK_LOG_BUFFER UINT32 0
LOG_BUFFER UINT32 10485760TOTAL_SHM_SIZE - [Fixed Memory][전체 Shared Pool 크기] = _MIN_SHARED_POOL_SIZE + [CCC 공간] + [CWS 공간]
+ [Free Shared Pool 크기][Free Shared Pool 크기] > ([전체 Shared Pool 크기] - _MIN_SHARED_POOL_SIZE) / 2SQL> REVOKE <권한> ON <object> FROM PUBLICSQL> REVOKE <권한> ON <object명> FROM <user_name>;SQL> GRANT <권한> ON <object명> TO <user_name>;SQL> REVOKE <권한> ON <object명> FROM <user_name>;SQL> GRANT <권한> ON <object명> TO <user_name>;$ echo %TB_HOME% %TB_SID%
$ cd %TB_HOME%\bin
$ tbinstall.vbs %TB_HOME% %TB_SID%$ echo %TB_HOME% %TB_SID%
$ cd %TB_HOME%\config
$ gen_tip.bat\tibero=(
(INSTANCE=(HOST=192.168.xxx.xxx)
(PORT=8629)
(DB_NAME=tibero)
)
)C:\Users\tmax\Desktop\tibero7\config>tbsql tibero/tmax
SQL> SELECT IPADDR, USERNAME FROM V$SESSION;
IPADDR
-----------------------------------------------
USERNAME
---------------------------------------------------------------------------------
192.168.xxx.xxx
TIBERO
1 row selected.$ echo %TB_HOME% %TB_SID%
$ cd %TB_HOME%\bin
$ tbuninstall.vbs(Undo 테이블 스페이스당 Undo Segment 최소 개수 * _USGMT_UNIFORM_EXTSIZE * 2
* Block size(8KB)) + 여유값(Undo 테이블 스페이스당 Undo Segment 최대 개수 * _USGMT_UNIFORM_EXTSIZE * 2
* Block size(8KB)) + 여유값(초당 UNDO Block 소모량 * UNDO retention * Block size(8KB)) + 여유값
INSERT 하는 ROW의 내용(TABLE row 전체 + INDEX key 전체) +
INSERT에 대한 UNDO(delete할 rowid + delete할 INDEX key)UPDATE 내용(ROW의 내용 중 새로 변경되는 내용 + 변경되는 컬럼의 INDEX key delete/insert)
+ UPDATE에 대한 UNDO(row 변경을 되돌리는 내용 + INDEX key delete/insert)DELETE되는 ROW에 대한 내용(rowid 정보 + INDEX key 전체)
+ DELETE에 대한 UNDO(row 전체 + INDEX key 전체)SQL> REVOKE DBA FROM <username>;SQL> ALTER USER <username> ACCOUNT LOCK;SQL> ALTER USER <username> IDENTIFIED BY ‘<new_password>’;SQL> CREATE PROFILE <profile_name>
LIMIT PASSWORD_VERIFY_FUNCTION verify_function
FAILED_LOGIN_ATTEMPTS <횟수>;SQL> ALTER PROFILE <profile_name> LIMIT FAILED_LOGIN_ATTEMPTS <횟수>;SQL> ALTER USER <username> PROFILE <profile_name>;SQL> ALTER PROFILE <profile_name> LIMIT PASSWORD_LOCK_TIME <시간>;SQL> ALTER USER <username> PROFILE <profile_name>;SQL> ALTER PROFILE <profile_name> LIMIT PASSWORD_LIFE_TIME <일수>;SQL> ALTER USER <username> PROFILE <profile_name>;SQL> ALTER PROFILE <profile_name> LIMIT PASSWORD_REUSE_TIME <일수>;SQL> ALTER USER <username> PROFILE <profile_name>;
Tibero6의 업그레이드 진행을 위한 Source DB 업그레이드 사전 작업 절차를 설명합니다.
티베로 DB 데이터 타입별 크기 산정 방법에 대해 설명합니다.
Table Migrator 유틸리티로 Oracle(Source DB)에서 Tibero(Target DB)로 DBMS 전환을 수행하는 예제입니다.
--DBTECH 계정에 Tablespace, User, Table, Table Data 생성
SQL> create tablespace DBTECH_TBS
2 datafile 'dbtech_tbs01.dtf' size 50M
3 AUTOEXTEND ON NEXT 20M MAXSIZE 5G
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.
SQL> create user dbtech identified by dbtech
2 default tablespace DBTECH_TBS;
User created.
SQL> grant connect, resource to dbtech;
Grant succeeded.
CONNECT dbtech/dbtech;
SQL> create table board (no number,
2 empno number(4),
3 title varchar(20),
4 contents clob,
5 regdate date default sysdate,
6 hit number default 0)
7 PCTFREE 10
8 INITRANS 2;
Table created.
SQL> INSERT INTO BOARD VALUES
(1, 7369, 'introduction', 'Hello, my name is SMITH.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(2, 7499, 'introduction', 'Hello, my name is ALLEN.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(3, 7521, 'introduction', 'Hello, my name is WARD.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(4, 7566, 'introduction', 'Hello, my name is JONES.', sysdate, 0);
1 row created.
SQL> INSERT INTO BOARD VALUES
(5, 7654, 'introduction', 'Hello, my name is MARTIN.', sysdate, 0);
1 row created.
SQL> commit;
Commit complete.
--Migration할 때 접속할 계정(DBTECH_TEST) 생성
SQL> CONNECT /as sysdba
Connected.
SQL> create user dbtech_test identified by dbtech_test
2 default tablespace DBTECH_TBS;
User created.
--DBTECH_TEST 계정에 권한 부여(connect)
SQL> grant connect to dbtech_test;
Grant succeeded.
--DBTECH_TEST 계정에 Table 조회 권한 부여(select on dbtech.board)
SQL> grant select on dbtech.board to dbtech_test;
Grant succeeded.
--DBTECH_TEST 계정에 권한 부여(select any dictionary)
SQL> grant select any dictionary to dbtech_test;
Grant succeeded.SQL> create tablespace DBTECH_TBS
2 datafile 'dbtech_tbs01.dtf' size 50M
3 AUTOEXTEND ON NEXT 20M MAXSIZE 5G
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace 'DBTECH_TBS' created.
SQL> create user dbtech identified by dbtech
2 default tablespace DBTECH_TBS;
User 'DBTECH' created.
SQL> grant connect, resource, select any dictionary to dbtech;
Granted.
SQL> conn dbtech/dbtech
Connected to Tibero.
SQL> create table board (no number,
2 empno number(4),
3 title varchar(20),
4 contents clob,
5 regdate date default sysdate,
6 hit number default 0)
7 PCTFREE 10
8 INITRANS 2;
Table 'BOARD' created.
SQL> exit
Disconnected.
$ ls -lrt
-rw-r--r--. 1 tibero dba 1340431 Nov 15 15:29 antlr-4.3-complete.jar
-rw-r--r--. 1 tibero dba 1376 Nov 15 15:29 ANTLR4_License.txt
-rw-r--r--. 1 tibero dba 1604783 Nov 15 15:29 internal-jdbc-16.jar
-rw-r--r--. 1 tibero dba 167 Nov 15 15:29 migrator.bat
-rw-r--r--. 1 tibero dba 8313 Nov 15 15:29 migrator.properties.eg
-rw-r--r--. 1 tibero dba 170 Nov 15 15:29 migrator.sh
-rw-r--r--. 1 tibero dba 167137 Nov 15 15:29 migrator_cli.jar
-rw-r--r--. 1 tibero dba 352917 Nov 15 15:29 msllogger-14.jar
-rw-r--r--. 1 tibero dba 1320 Nov 15 15:29 pgjdbc_License.txt
-rw-r--r--. 1 tibero dba 850563 Nov 15 15:29 postgresql-42.2.27.jre6.jar
-rw-r--r--. 1 tibero dba 4054 Nov 15 15:29 readme.txt
-rw-r--r--. 1 tibero dba 123556 Nov 15 15:29 toolcom.jar
-rwxr-xr-x. 1 tibero dba 4210517 Nov 16 13:54 ojdbc6.jar
$sh migrator.sh SOURCE_TYPE=ORACLE SOURCE_URL=jdbc:oracle:thin:@localhost:1525:oraclesb SOURCE_USER=dbtech_test SOURCE_PASSWORD=dbtech_test SOURCE_LOGIN_AS=NORMAL SOURCE_SCHEMA=dbtech SOURCE_TABLE=board TARGET_URL=jdbc:internal:thin:@localhost:6213:JSH_T7_DB TARGET_USER=dbtech TARGET_PASSWORD=dbtech TARGET_SCHEMA=dbtech TARGET_TABLE=board
SOURCE_USER=dbtech_test SOURCE_PASSWORD=dbtech_test SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech SOURCE_TABLE=board
TARGET_URL=jdbc:internal:thin:@localhost:6213:JSH_T7_DB TARGET_USER=dbtech
TARGET_PASSWORD=dbtech TARGET_SCHEMA=dbtech TARGET_TABLE=board
INFO[16:51:45,676][main]TableMigrator.initAndDoMigrate()(1429)
********************************************************************************
*
* Start Log for table migrator.
* Vendor: tibero
* Product: table migrator
* Version: 263750
*
* Trace is started on 16:51:45.675
* Log level: INFO
*
********************************************************************************
INFO[16:51:45,679][main]TableMigrator.initAndDoMigrate()(1431)
=== Parameters ===
SOURCE_TYPE=ORACLE
SOURCE_DRIVER=oracle.jdbc.OracleDriver
SOURCE_URL=jdbc:oracle:thin:@localhost:1525:oraclesb
SOURCE_USER=DBTECH_TEST
SOURCE_PASSWORD=************
SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech
SOURCE_SCHEMAORIGN=dbtech
SOURCE_TABLE=board
SELECT_SUBPARTITION=
BATCH_THRESHOLD=128
EXTRACT_PARALLEL=N
EXTRACT_THREAD_COUNT=1
SOURCE_TABLE_DEDUPLICATE=N
USE_PARALLEL_HINT=N
PARALLEL_HINT_CNT=-1
USE_OBJECT_PARALLEL_HINT=N
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:6213:JSH_T7_DB
TARGET_USER=DBTECH
TARGET_PASSWORD=************
TARGET_SCHEMA=dbtech
TARGET_TABLE=board
COMMIT_SIZE=-1
INDEX_DISABLE=N
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
INSERT_METHOD=dpl
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
DPL_LOG_UNIT=500
LOG_FILE_NAME=tableMigrator.log
LOG_LEVEL=INFO
CHECK_COLUMN_METADATA=N
==================
INFO[16:51:45,784][TabMig[16:51:45.680]]TbCmdConnection.connect()(58) Connect To The Source- URL : jdbc:oracle:thin:@localhost:1525:oraclesb ID : DBTECH_TEST
INFO[16:51:45,983][TabMig[16:51:45.680]]TbCmdConnection.connect()(58) Connect To The Target- URL : jdbc:internal:thin:@localhost:6213:JSH_T7_DB ID : DBTECH
INFO[16:51:46,228][TabMig[16:51:45.680]]TableMigrator.migrate()(256) SOURCE_TYPE : ORACLE
INFO[16:51:46,228][TabMig[16:51:45.680]]TableMigrator.migrate()(257) SOURCE_SCHEMA : dbtech
INFO[16:51:46,228][TabMig[16:51:45.680]]TableMigrator.migrate()(275) SOURCE_TABLE : board
INFO[16:51:46,229][TabMig[16:51:45.680]]TableMigrator.migrate()(277) TARGET_TYPE : DEFAULT
INFO[16:51:46,229][TabMig[16:51:45.680]]TableMigrator.migrate()(278) TARGET_SCHEMA : dbtech
INFO[16:51:46,229][TabMig[16:51:45.680]]TableMigrator.migrate()(293) TARGET_TABLE : board
INFO[16:51:46,236][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(52) SELECT column_name, data_type, virtual_column, hidden_column FROM DBA_TAB_COLS WHERE OWNER=? AND TABLE_NAME=? order by column_id
INFO[16:51:46,292][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(55) setString(1) : DBTECH
INFO[16:51:46,292][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(58) setString(2) : BOARD
INFO[16:51:46,308][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(125) SELECT iot_type FROM dba_tables WHERE OWNER = ? and TABLE_NAME = ?
INFO[16:51:46,309][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(128) setString(1) : DBTECH
INFO[16:51:46,309][TabMig[16:51:45.680]]OracleTableInfo.getTableInfo()(131) setString(2) : BOARD
INFO[16:51:46,311][TabMig[16:51:45.680]]TableMigrator.migrate()(375) start extracting thread...
INFO[16:51:46,315][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(86) run
INFO[16:51:46,315][TabMig[16:51:45.680].E0]DataExtractor.loggingTabAndColInfo()(531) Extract Information
INFO[16:51:46,316][TabMig[16:51:45.680].E0]DataExtractor.loggingTabAndColInfo()(532) table name : DBTECH.BOARD
INFO[16:51:46,316][TabMig[16:51:45.680].E0]DataExtractor.loggingTabAndColInfo()(538) columns [6] - "NO" "EMPNO" "TITLE" "REGDATE" "HIT" "CONTENTS"
INFO[16:51:46,318][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(93) TabMig[16:51:45.680].E0 - sql : SELECT '0', t."NO", t."EMPNO", t."TITLE", t."REGDATE", t."HIT", t."CONTENTS" FROM DBTECH.BOARD t
INFO[16:51:46,327][TabMig[16:51:45.680]]TableMigrator.migrate()(536) start loading thread...
INFO[16:51:46,328][TabMig[16:51:45.680]]DataLoader.setRemainThr()(85) Set Remain Threads [0], to [1]
INFO[16:51:46,332][L0]TbDPLDataLoader.setDirPathStream()(525) DPL Loading Information
INFO[16:51:46,332][L0]TbDPLDataLoader.setDirPathStream()(526) table name : dbtech.board
INFO[16:51:46,332][L0]TbDPLDataLoader.setDirPathStream()(532) columns [6] - "NO" "EMPNO" "TITLE" "REGDATE" "HIT" "CONTENTS"
INFO[16:51:46,337][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(282)
Total Extract Time : 20ms
Time For Putting Row on Queue : 0ms
Time For Executing Query Time : 12ms
Time For Executing Fetch Time : 4ms
Percentage of Query and Fetch => 80.00%
INFO[16:51:46,337][TabMig[16:51:45.680].E0]OracleDataExtractor.run()(283) TOTAL Extracted ROWS: 5 (source schema = DBTECH, source table = BOARD)
INFO[16:51:46,338][TabMig[16:51:45.680]]TableMigrator.migrate()(580) Extractor termFlag : [true], isError : [false]
INFO[16:51:46,338][TabMig[16:51:45.680].E0]DataExtractor.setTermFlag()(543) Set termFlag and isError : [false]
INFO[16:51:46,338][TabMig[16:51:45.680].E0]DataExtractor.setTermFlag()(544) complete
INFO[16:51:46,354][L0.TbDPLDataLoader0]DataLoaderManager.run()(47) run
INFO[16:51:46,361][L0.TbDPLDataLoader0]DataLoaderManager.run()(240) Total Insert Time : 29ms
Time For Taking Row From Queue: 0ms
Time For Executing Batch : 4ms
Time For Executing Commit : 2ms
Percentage of Batch and Commit Time => 20.69%
INFO[16:51:46,361][L0.TbDPLDataLoader0]DataLoaderManager.run()(242) [L0.TbDPLDataLoader0]1700121106361 TOTAL Loaded ROWS: 5 (target schema = dbtech, target table = board)
INFO[16:51:46,374][TabMig[16:51:45.680]]TableMigrator.migrate()(613) Loader remainThr : [0], isError : [false]
INFO[16:51:46,374][L0.TbDPLDataLoader0]DataLoader.finish()(80) Decrement remainThr : [0] and isError : [false]
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.resetThrs()(1359) terminate extract thread...1
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.resetThrs()(1373) terminate loader thread...1
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.migrate()(627) [VERIFICATION] Source Info : dbtech.board : 5 Rows, Target Info : dbtech.board : 5 Rows [OK]
INFO[16:51:46,375][TabMig[16:51:45.680]]TableMigrator.migrate()(686) extract conneciton close
INFO[16:51:46,377][TabMig[16:51:45.680]]TableMigrator.migrate()(695) loader connection close
INFO[16:51:46,377][TabMig[16:51:45.680]]TableMigrator.migrate()(705) Elapsed Time (milliseconds) : 697
INFO[16:51:46,377][TabMig[16:51:45.680]]TableMigrator.migrate()(707) End MigrationSQL> 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 UTF16SQL> 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 NOSQL> 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 YESSQL> 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 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 YESSQL> 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 60SQL> 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;
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 NSQL> 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/12SQL> 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;SET LINESIZE 120
COL owner FOR a10
COL name FOR a30
COL source FOR a50
SELECT owner, name, source FROM dba_java_classes;SQL> SHOW PARAM JAVA_CLASS_PATH;
NAME TYPE VALUE
---------------- -------- --------------------------
JAVA_CLASS_PATH DIRNAME /data2/tb6/tbdata/java/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 YESSQL> SET LINESIZE 120
SQL> COL name FOR a20
SQL> COL path FOR a60
SQL> SELECT * FROM dba_directories;
NAME PATH
-------------------- ----------------------
SAM /data2/tb6/DIR_SAMSQL> 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 1SQL> 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 1SQL> SELECTcount(*) FROM v$session;
COUNT(*)
---------- -
2BEGIN
DBMS_JOB.BROKEN(100, true);
END;
/tbexport username={sys | dba계정} password=<password> port=<port_no> sid=<SID>
file=<file name> log=<log-file name> full=y script=ytbexport username=sys password=pwd port=8629 sid=tibero6
file=tibero6_all.dat log=tibero6_exp.log full=y script=y0byte16bytes(lob locator)16bytes(lob locator) + 16bytes(lob inode)16bytes(lob locator) + 16bytes(lob inode) + 48bytes(12 page dbas)16bytes(lob locator) + 16bytes(lob inode) + 48bytes * (LOB 컬럼 크기 / 64KB)(lob index)개발, 운영 등 업무에 따라 필요한 티베로 기술정보를 아래 문서 목록에서 확인하세요.
데이터베이스 파라미터 지정 과정을 예시와 함께 설명합니다.
FROM rockylinux:9
# Tibero 설치를 위한 환경 변수 설정
ENV TB_VERSION=7
ENV TB_BASE=/opt
ENV TB_HOME=$TB_BASE/tibero$TB_VERSION
ENV LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib
ENV PATH=$PATH:$TB_HOME/bin:$TB_HOME/client/bin
# Tibero 바이너리 파일 복사
COPY tibero.tar.gz /tmp/tibero.tar.gz
# 필수 패키지 설치
COPY ./scripts/install_dependency.sh /opt/install_dependency.sh
RUN chmod +x /opt/install_dependency.sh
RUN /opt/install_dependency.sh
# Tibero 설치
COPY ./scripts/install_tibero.sh /opt/install_tibero.sh
RUN chmod +x /opt/install_tibero.sh
# Entrypoint 스크립트 복사 및 권한 설정
COPY ./scripts/entrypoint.sh /opt/entrypoint.sh
RUN chmod +x /opt/entrypoint.sh
ENTRYPOINT ["/opt/entrypoint.sh"]#!/bin/bash
set -e
# Install dependencies for Tibero
dnf update -y
dnf install -y gcc
dnf install -y gcc-c++
dnf install -y libaio
dnf install -y libaio-devel
dnf install -y sysstat
dnf install -y glibc-utils
dnf install -y ncurses-devel
dnf install -y libnsl
dnf install -y libnsl2
dnf install -y gdb
dnf install -y gdb-doc
dnf install -y gdb-gdbserver
dnf install -y gdb-headless
dnf install -y gdb-minimal
dnf install -y gdbm-libs
dnf install -y tar
dnf install -y vim
dnf install -y vi
dnf install -y hostname
# Tibero process 확인용 (pgrep)
dnf install -y procps-ng
dnf clean all
curl -O https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm
rpm -ivh epel-release-latest-9.noarch.rpm
dnf install -y nmon
dnf install -y java-1.8.0-openjdk-devel
dnf install -y ncurses-compat-libs
ln -s /usr/lib64/libncurses.so.6.2 /usr/lib64/libncurses.so.5 || true
ln -s /usr/lib64/libtinfo.so.6.2 /usr/lib64/libtinfo.so.5 || true
ln -s /usr/lib64/libform.so.6.2 /usr/lib64/libform.so.5 || true#!/usr/bin/env bash
set -euo pipefail
# BOOT_POLICY=auto(기본) | manual
: "${BOOT_POLICY:=auto}"
: "${INSTALL_TGZ:=/tmp/tibero.tar.gz}"
: "${TB_PROC:=tbsvr}" # Tibero 메인 프로세스명
: "${TB_START_CMD:=tbboot}" # Tibero 기동 명령
: "${TB_DOWN_CMD:=tbdown}" # Tibero 종료 명령
if [ -f /etc/bashrc ]; then
set +u
# /etc/bashrc 내부에서 BASHRCSOURCED를 사용할 수 있으므로 기본값 보장
: "${BASHRCSOURCED:=0}"
source /etc/bashrc || true
set -u
fi
if [ -f "${HOME}/.bashrc" ]; then
set +u
: "${BASHRCSOURCED:=0}"
source "${HOME}/.bashrc" || true
set -u
fi
graceful_shutdown() {
echo "[signal] Shutting down database (if running)..."
${TB_DOWN_CMD} immediate >/dev/null 2>&1 || true
exit 0
}
trap graceful_shutdown SIGINT SIGTERM
# ===== 수동 모드: 설치/기동 모두 생략, 컨테이너만 유지 =======================
if [[ "${BOOT_POLICY}" == "manual" ]]; then
echo "[mode] BOOT_POLICY=manual → skip install/boot. keeping container alive."
echo "[note] even if ${INSTALL_TGZ} exists, will NOT install. will NOT run tbboot."
# 컨테이너 생존 유지
exec sleep infinity
fi
# ===== 자동 모드: 설치 및 기동 시도 ==========================================
# 1) 최초 생성 판단: tar.gz 존재시 설치 시도
if [ -f "${INSTALL_TGZ}" ]; then
echo "[init] Found ${INSTALL_TGZ} → running /opt/install_tibero.sh ..."
/opt/install_tibero.sh
fi
# 2) 서버 기동 시도
if ! pgrep "${TB_PROC}" >/dev/null 2>&1; then
echo "[start] Starting Tibero..."
if ! ${TB_START_CMD} >/dev/null 2>&1; then
echo "[error] Failed to start Tibero (${TB_START_CMD}). Check logs."
exit 1
fi
fi
# 3) 기동 검증
if pgrep "${TB_PROC}" >/dev/null 2>&1; then
echo "#########################"
echo "Tibero is Ready To Use!"
echo "#########################"
else
echo "[error] Tibero process not found after start attempt."
exit 1
fi
# 4) 컨테이너 생존 유지 (로그 tail이 필요 없으면 단순 sleep으로 유지)
exec sleep infinity#!/bin/bash
set -e
if [ -f "/tmp/tibero.tar.gz" ]; then
: "${TB_BASE:?TB_BASE is required}"
mkdir -p "${TB_BASE}"
tar -xzf "/tmp/tibero.tar.gz" -C "${TB_BASE}" && rm -f "/tmp/tibero.tar.gz"
chmod -R 775 $TB_HOME
fi
echo "10% complete"
echo "Setting Environment Variables"
if [[ -n "${TB_USER_FILE:-}" && -f "${TB_USER_FILE}" ]]; then
TB_USER="$(< "${TB_USER_FILE}")"
export TB_USER
fi
if [[ -n "${TB_PASSWORD_FILE:-}" && -f "${TB_PASSWORD_FILE}" ]]; then
TB_PASSWORD="$(< "${TB_PASSWORD_FILE}")"
export TB_PASSWORD
fi
# 루트 패스워드 필수
if [[ -z "${TB_ROOT_PASSWORD:-}" ]]; then
echo "[ERROR] TB_ROOT_PASSWORD environment variable is not set. Exiting." >&2
exit 1
fi
if [[ -n "${TB_ROOT_PASSWORD_FILE:-}" && -f "${TB_ROOT_PASSWORD_FILE}" ]]; then
TB_ROOT_PASSWORD="$(< "${TB_ROOT_PASSWORD_FILE}")"
export TB_ROOT_PASSWORD
fi
: "${TB_HOME:?TB_HOME is required}"
if [ -f "${TB_HOME}/bin/base_env.sh" ]; then
sed -i "s|^svr_sys_password=\"tibero\"$|svr_sys_password=\"${TB_ROOT_PASSWORD}\"|" \
"${TB_HOME}/bin/base_env.sh"
fi
: "${TB_SID:=tibero}"
export TB_SID
echo "export TB_VERSION=\"${TB_VERSION}\"" >> "${HOME}/.bashrc"
echo "export TB_BASE=\"${TB_BASE}\"" >> "${HOME}/.bashrc"
echo "export TB_HOME=\"${TB_HOME}\"" >> "${HOME}/.bashrc"
echo "export LD_LIBRARY_PATH=\"${LD_LIBRARY_PATH}\"" >> "${HOME}/.bashrc"
echo "export PATH=\"${PATH}\"" >> "${HOME}/.bashrc"
echo "export TB_SID=\"${TB_SID}\"" >> "${HOME}/.bashrc"
echo "20% complete"
# Generating Tip file
echo "Generating tip file"
TIP_FILE_PATH=${TB_HOME}/config/${TB_SID}.tip
"${TB_HOME}/config/gen_tip.sh" -s "${TB_SID}"
# LOG_ARCHIVE_DEST 반영 (있으면 교체, 없으면 추가)
if [ -n "${LOG_ARCHIVE_DEST:-}" ]; then
if grep -q "^LOG_ARCHIVE_DEST=" "${TIP_FILE_PATH}"; then
sed -i "s|^LOG_ARCHIVE_DEST=.*|LOG_ARCHIVE_DEST=${LOG_ARCHIVE_DEST}|" "${TIP_FILE_PATH}"
else
printf "\nLOG_ARCHIVE_DEST=%s\n" "${LOG_ARCHIVE_DEST}" >> "${TIP_FILE_PATH}"
fi
fi
echo "30% complete"
# # Setting DB Create environment variables
args=()
[ -n "$CHAR_SET" ] && args+=("-ch" "$CHAR_SET")
[ -n "$NCHAR_SET" ] && args+=("-nch" "$NCHAR_SET")
[ -n "$MAX_DATAFILE" ] && args+=("--max-datafile" "$MAX_DATAFILE")
[ -n "$MAX_LOGGROUP" ] && args+=("--max-loggroup" "$MAX_LOGGROUP")
[ -n "$MAX_LOGMEMBER" ] && args+=("--max-logmember" "$MAX_LOGMEMBER")
[ -n "$LOG_SIZE" ] && args+=("--log-size" "$LOG_SIZE")
[ -n "$LOG_GROUP" ] && args+=("--log-group" "$LOG_GROUP")
[ -n "$LOG_FILE" ] && args+=("--log-file" "$LOG_FILE")
[ -n "$LOG_DIR" ] && args+=("--log-dir" "$LOG_DIR")
[ -n "$SYS_SIZE" ] && args+=("--sys-size" "$SYS_SIZE")
[ -n "$SYS_DIR" ] && args+=("--sys-dir" "$SYS_DIR")
[ -n "$SYS_AUTOEXTEND" ] && args+=("--sys-autoextend" "$SYS_AUTOEXTEND")
[ -n "$SYS_NEXTSIZE" ] && args+=("--sys-nextsize" "$SYS_NEXTSIZE")
[ -n "$SYS_MAXSIZE" ] && args+=("--sys-maxsize" "$SYS_MAXSIZE")
[ -n "$TEMP_SIZE" ] && args+=("--temp-size" "$TEMP_SIZE")
[ -n "$TEMP_DIR" ] && args+=("--temp-dir" "$TEMP_DIR")
[ -n "$TEMP_AUTOEXTEND" ] && args+=("--temp-autoextend" "$TEMP_AUTOEXTEND")
[ -n "$TEMP_NEXTSIZE" ] && args+=("--temp-nextsize" "$TEMP_NEXTSIZE")
[ -n "$TEMP_MAXSIZE" ] && args+=("--temp-maxsize" "$TEMP_MAXSIZE")
[ -n "$UNDO_SIZE" ] && args+=("--undo-size" "$UNDO_SIZE")
[ -n "$UNDO_DIR" ] && args+=("--undo-dir" "$UNDO_DIR")
[ -n "$UNDO_AUTOEXTEND" ] && args+=("--undo-autoextend" "$UNDO_AUTOEXTEND")
[ -n "$UNDO_NEXTSIZE" ] && args+=("--undo-nextsize" "$UNDO_NEXTSIZE")
[ -n "$UNDO_MAXSIZE" ] && args+=("--undo-maxsize" "$UNDO_MAXSIZE")
[ -n "$USR_SIZE" ] && args+=("--usr-size" "$USR_SIZE")
[ -n "$USR_DIR" ] && args+=("--usr-dir" "$USR_DIR")
[ -n "$USR_AUTOEXTEND" ] && args+=("--usr-autoextend" "$USR_AUTOEXTEND")
[ -n "$USR_NEXTSIZE" ] && args+=("--usr-nextsize" "$USR_NEXTSIZE")
[ -n "$USR_MAXSIZE" ] && args+=("--usr-maxsize" "$USR_MAXSIZE")
[ -n "$SYSSUB_SIZE" ] && args+=("--syssub-size" "$SYSSUB_SIZE")
[ -n "$SYSSUB_DIR" ] && args+=("--syssub-dir" "$SYSSUB_DIR")
[ -n "$SYSSUB_AUTOEXTEND" ] && args+=("--syssub-autoextend" "$SYSSUB_AUTOEXTEND")
[ -n "$SYSSUB_NEXTSIZE" ] && args+=("--syssub-nextsize" "$SYSSUB_NEXTSIZE")
[ -n "$SYSSUB_MAXSIZE" ] && args+=("--syssub-maxsize" "$SYSSUB_MAXSIZE")
[ -n "$BACKUP_CTRLFILE" ] && args+=("--backup-ctrlfile" "$BACKUP_CTRLFILE")
[ -n "$SYSTEM_SCRIPT" ] && args+=("--system-script" "$SYSTEM_SCRIPT")
# creating the database
echo "Creating the database"
$TB_HOME/bin/tb_create_db.sh "${args[@]}"
echo "70% complete"
# Setting Archive log mode
if [ -n "$ARCHIVE_LOG_MODE" ] && [ "$ARCHIVE_LOG_MODE" == "Y" -o "$ARCHIVE_LOG_MODE" == "y" ]; then
echo "Setting Archive log mode"
tbdown
tbboot mount
tbsql sys/"$TB_ROOT_PASSWORD" <<EOF
ALTER DATABASE ARCHIVELOG;
EOF
tbdown
tbboot
fi
echo "90% complete"
# Setting up the database user
echo "Setting up the database user"
if { [ -n "${TB_USER:-}" ] && [ -z "${TB_PASSWORD:-}" ]; } || \
{ [ -z "${TB_USER:-}" ] && [ -n "${TB_PASSWORD:-}" ]; }; then
echo "Cannot create database user: both TB_USER and TB_PASSWORD must be provided."
fi
if [ -n "${TB_USER:-}" ] && [ -n "${TB_PASSWORD:-}" ]; then
tbsql "sys/${TB_ROOT_PASSWORD}" <<EOF
create user ${TB_USER} identified by '${TB_PASSWORD}';
grant dba to ${TB_USER};
EOF
fi
echo "100% complete"
echo "Tibero installation and database creation completed successfully."docker build -t <이미지명>:<태그> ..
├── Dockerfile
├── tibero.tar.gz
└── scripts
├── entrypoint.sh
├── install_dependency.sh
└── install_tibero.shSOURCE_TYPE=ORACLE
SOURCE_DRIVER=oracle.jdbc.OracleDriver
SOURCE_URL=jdbc:oracle:thin:@localhost:1539:orcl
SOURCE_USER=sys
SOURCE_PASSWORD=oracle
SOURCE_LOGIN_AS=sysdba
SOURCE_SCHEMA=SCOTT
SOURCE_TABLE=t1
SOURCE_TABLE_DEDUPLICATE=N
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:59166:tibero
TARGET_USER=tibero
TARGET_PASSWORD=tmax
TARGET_SCHEMA=TIBERO
TARGET_TABLE=t1
EXTRACT_PARALLEL=N
EXTRACT_PARALLEL_RANGE_BY_ROWID=N
EXTRACT_THREAD_COUNT=1
SELECT_TSN=17322052
SELECT_CONDITION= c1>1
SELECT_FETCH_SIZE=1024
SELECT_PARTITION=part1
SELECT_SUBPARTITION=subpar1
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
OPTIMIZER_HINT=/*+ FULL(t) PARALLEL(t, 4) */
USE_PARALLEL_HINT=N
USE_OBJECT_PARALLEL_HINT=N
PARALLEL_HINT_CNT=-1
INSERT_METHOD=dpl
COMMIT_SIZE=-1
INDEX_DISABLE=N
INSERT_BATCH=Y
BATCH_THRESHOLD=128
DPL_LOG_UNIT=500
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_PARTITION=
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
TRIM=
BYTEA_TO_BLOB=N
TEXT_TO_CLOB=N
MONITORING_LOG=N
MONITORING_LOG_UNIT=500
MONITORING_SESS_STAT=CPU used by this session;parse time cpu;parse count(hard)
CHECK_COLUMN_METADATA=N
ROW_QUEUE_TYPE=ARRAY(10240)
VERIFY_TABLE=N
VERIFY_TABLE_NAME=VERIFY_TBL
LOG_LEVEL=INFO
LOG_FILE_NAME=tableMigrator.log
LOG_DIR=한 Row 저장에 필요한 공간 = Row Directory + Row Header + Row의 데이터 길이
+ (1 * <250bytes 이하 컬럼개수>) + (3 * <250bytes 초과 컬럼개수>)한 블록의 데이터 공간 = (Block Size - Block Header - ITL공간) * (100 - PCTFREE) / 100/ Row Space ( 한 블록의 데이터 공간 / 한 Row의 저장에 필요한 공간 )총 데이터 Row 개수 = <초기 예상 건수> + <월중 예상 추가 건수> * (<데이터 보관 월 수> + 1)필요한 총 데이터 공간 = Block Size * Total Row Size / Row Size per Block (Block Size
* 총 데이터 Row 개수 / 한 블록에 들어갈 수 있는 Row 개수)대용량 데이터 이관 방법의 예제를 설명합니다.
CREATE TABLE DBTECH.LINEITEM (
L_ORDERKEY NUMBER,
L_PARTKEY NUMBER,
L_SUPPKEY NUMBER,
L_LINENUMBER NUMBER,
L_QUANTITY NUMBER(15,2),
L_EXTENDEDPRICE NUMBER(15,2),
L_DISCOUNT NUMBER(15,2),
L_TAX NUMBER(15,2),
L_RETURNFLAG CHAR(1),
L_LINESTATUS CHAR(1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR(25),
L_SHIPMODE CHAR(10),
L_COMMENT VARCHAR(44)
) NOLOGGING;$ cat migrator.properties
#######################
# Source DB Connection
#######################
# Source DB type (Optional, generic | default | ORACLE)
# ORACLE option needs SOURCE_LOGIN_AS property
# GENERIC option needs SOURCE_DRIVER property
SOURCE_TYPE=ORACLE
# Source DB JDBC driver class name
# Use with SOURCE_TYPE=GENERIC option
SOURCE_DRIVER=oracle.jdbc.OracleDriver
#SOURCE_DRIVER=com.mysql.jdbc.Driver
# Source DB JDBC connection URL (Optional,
Default=jdbc:oracle:thin:@localhost:1521:orcl)
SOURCE_URL=jdbc:oracle:thin:@localhost:1539:orcl
#SOURCE_URL=jdbc:mysql://127.0.0.1:3306/mysql
# Source DB login ID (Optional, Default=sys)
#SOURCE_USER="user1"
SOURCE_USER=tibero
# Source DB login password (Optional, Default=oracle)
SOURCE_PASSWORD=tmax
# Oracle source DB login option (Optional, Default=sysdba)
# Use with SOURCE_TYPE=ORACLE option
SOURCE_LOGIN_AS=NORMAL
# Source table schema (Optional)
# Default value is the same with SOURCE_USER
#SOURCE_SCHEMA=SCOTT
#SOURCE_SCHEMA="dbo"
#SOURCE_SCHEMA=
# Source table name (Mandatory)
#SOURCE_TABLE=tableName1
#SOURCE_TABLE="tableName2"
#SOURCE_TABLE=bt_scen_tran
SOURCE_TABLE=t1
# Ignore deadlock error that occur during migration for the tables created using the dedup option
# Use when BATCH_THRESHOLD=1
# If not, Data integrity is not guaranteed
SOURCE_TABLE_DEDUPLICATE=N
#######################
# Target DB Connection
#######################
# Target DB type (Optional, DEFAULT)
TARGET_TYPE=DEFAULT
# Target DB JDBC driver class name
#TARGET_DRIVER=com.tmax.tibero.jdbc.TbDriver
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
# Target DB JDBC connection URL (Optional,
Default=jdbc:internal:thin:@localhost:8629:tibero)
#TARGET_URL=jdbc:tibero:thin:@192.1.5.63:39208:tibero
TARGET_URL=jdbc:internal:thin:@localhost:59166:tibero
# Target DB login ID (Optional, Default=sys)
#TARGET_USER="user1"
TARGET_USER=tibero
# Target DB login password (Optional, Default=tibero)
TARGET_PASSWORD=tmax
# Target table schema (Optional)
# Default value is the same with TARGET_USER
# TARGET_SCHEMA=TIBERO
# Target table name (Optional)
# Default value is the same with SOURCE_TABLE
# TARGET_TABLE=t1
#################
# Data Extractor
#################
# Use parallel Extracting (Optional, Y | N Default=N)
EXTRACT_PARALLEL=N
# Number of data extractor thread (Optional, Default=1)
EXTRACT_THREAD_COUNT=1
# Use TSN(or SCN) option (Optional)
#SELECT_TSN=17322052
# Where conditions (Optional)
# ex. column1 > 10 AND column2 = 'abc'
#SELECT_CONDITION= c1>1
# Fetch size of source table cursor (Optional, Default=1024)
# Some DB is not supported this feature.
#SELECT_FETCH_SIZE=1024
# Source table partition name (Optional)
#SELECT_PARTITION=
# Source table subpartition name (Optional)
#SELECT_SUBPARTITION=subpar1
#SELECT_SUBPARTITION="subpart2"
#SELECT_SUBPARTITION=
# Ignore source/target DB character set (Optional, y | N )
SELECT_AS_BYTE=N
# db_file_multiblock_read_count (Optinal, Option for Oracle), Defalut=-1)
DB_FILE_MULTIBLOCK_READ_COUNT=-1
# Use parallel excution of SQL statements (Optinal, Option for Oracle), Defalut=N)
USE_PARALLEL_HINT=N
# Use with USE_PARALLEL_HINT=Y (Defalut=-1)
PARALLEL_HINT_CNT=-1
##############
# Data Loader
##############
# Use direct path loading (Optional, cpl | DPL)
# CPL means conventional path loading
# DPL means direct path loading I
NSERT_METHOD=dpl
# Use commit size option(Optinal, Default = commit once after loading)
# Use with INSERT_METHOD=CPL option
# set commit_size-1 to commit only once
COMMIT_SIZE=-1
# Use index disable option(Optional, Default = N, Y/N)
INDEX_DISABLE=N
# Use batch insert (Optional, Y | n)
# Use with INSERT_METHOD=CPL option
INSERT_BATCH=Y
# batch threshold size (Optional, Default=32)
BATCH_THRESHOLD=128
# Use parallel loading (Optional, Y | n)
INSERT_PARALLEL=N
# Number of data loader thread (Optional, Default=4)
# Use with INSERT_PARALLEL=Y option
INSERT_THREAD_COUNT=1
# Target table partition name (Optional)
#INSERT_PARTITION=
# Specifies whether to keep the column names of the source table case-sensitive.
(Optional, Y | n )
# If set to Y, the original column name format will be preserved by enclosing it in
double quotes.
# If set to N, the column name format will follow the database naming rules.
INSERT_COLUMN_NAME_CASE_SENSITIVE=N
# Use zero length string data migration as null (Optional, Y | n, Default=Y, Option ignored for Oracle)
# If set to N, the zero length data will be inserted as ' '.
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
# Trim leading and trailing pad character of CHAR type data. (Optional, Y | n, Default=N)
#TRIM=
##############
# Common
##############
# Use MetaData Checking for DPL. (Optional, Default=Y)
CHECK_COLUMN_METADATA=N
# Log
LOG_LEVEL=INFO
LOG_FILE_NAME=tableMigrator.log
#LOG_DIR=Row Directory + Index Header + Base RowID
+ Key Column 총 길이 + (1 * <250bytes 이하 컬럼개수>) + (3 * <250bytes 초과 컬럼개수>)(Block Size - Block Header - ITL 공간) * (100 - PCTFREE) / 100/Data Space per Block / Index Entry Space (한 블록의 데이터 공간
/ 한 Index Entry의 저장에 필요한 공간)초기 예상 건수 + 월중 예상 추가 건수 * (데이터 보관 월 수 + 1)1 / Entry Size per Block (1 / 한 블록에 들어가는 Entry 개수)Block Size *( Total Row Size / Entry Size per Block + Total Row Size
/ Entry Size per Block ^ 2 )Index Split이 일어난 경우 데이터 공간 = Total Index Size * 2 (필요한 총 데이터 공간 * 2 )$ cat run.sh
# dbtech.lineitem 테이블 (조건 l_linestatus='0'에 해당하는 데이터)
nohup migrator.sh SOURCE_TYPE=ORACLE \
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL \
SOURCE_USER=dbtech SOURCE_PASSWORD=dbtech SOURCE_LOGIN_AS=NORMAL \
SOURCE_SCHEMA=dbtech SOURCE_TABLE=lineitem \
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero \
TARGET_USER=dbtech TARGET_PASSWORD=dbtech SELECT_CONDITION="l_linestatus='O'" >
migrator_a_set.log &
# dbtech.lineitem 테이블 (조건 l_linestatus='F'에 해당하는 데이터)
nohup migrator.sh SOURCE_TYPE=ORACLE \
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL
\ SOURCE_USER=dbtech SOURCE_PASSWORD=dbtech SOURCE_LOGIN_AS=NORMAL \
SOURCE_SCHEMA=dbtech SOURCE_TABLE=lineitem \
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero \
TARGET_USER=dbtech TARGET_PASSWORD=dbtech SELECT_CONDITION="l_linestatus='F'" >
migrator_b_set.log &# dbtech.lineitem 테이블의 조건 l_linestatus='0' migrator 수행 로그 확인
$ cat migrator_a_set.log
=== Parameters ===
PROPERTY_FILE=migrator.properties
SOURCE_TYPE=ORACLE
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL
SOURCE_USER=dbtech
SOURCE_PASSWORD=************
SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech
SOURCE_TABLE=lineitem
TARGET_TYPE=DEFAULT
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero
TARGET_USER=dbtech
TARGET_PASSWORD=************
SELECT_CONDITION=l_linestatus='F'
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
INSERT_METHOD=DPL
INSERT_BATCH=Y
INSERT_PARALLEL=Y
INSERT_THREAD_COUNT=4
==================
[E0]1373322363068 - STARTED
[L0]1373322363189 Loader started
[L1]1373322363192 Loader started
[L2]1373322363196 Loader started
[L3]1373322363198 Loader started
[L1] 1000
[L0] 1000
[L3] 1000
[L2] 1000
... 중략 ...
[E0]1373322793890 - TOTAL Extracted ROWS: 3004998
[L3]1373322793913 TOTAL Loaded ROWS: 617366
[L0]1373322793921 TOTAL Loaded ROWS: 893541
[L1]1373322793924 TOTAL Loaded ROWS: 898889
[L2]1373322793935 TOTAL Loaded ROWS: 595202
Loading is ended.
Elapsed Time : 431636
ADD BATCH : 0
EXECUTE BATCH : 0
[VERIFICATION] Source Info : dbtech.lineitem: 3004998 Rows, Target Info :
dbtech.lineitem : 3004998 Rows [OK]
# dbtech.lineitem 테이블의 조건 l_linestatus='F' migrator 수행 로그 확인
$ cat migrator_b_set.log
=== Parameters ===
PROPERTY_FILE=migrator.properties
SOURCE_TYPE=ORACLE
SOURCE_URL=jdbc:oracle:thin:@localhost:1521:ORCL
SOURCE_USER=dbtech
SOURCE_PASSWORD=************
SOURCE_LOGIN_AS=NORMAL
SOURCE_SCHEMA=dbtech
SOURCE_TABLE=lineitem
TARGET_TYPE=DEFAULT
TARGET_URL=jdbc:tibero:thin:@localhost:8629:tibero
TARGET_USER=dbtech
TARGET_PASSWORD=************
SELECT_CONDITION=l_linestatus='O'
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
INSERT_METHOD=DPL
INSERT_BATCH=Y
INSERT_PARALLEL=Y
INSERT_THREAD_COUNT=4
==================
[E0]1373322363042 - STARTED
[L0]1373322363168 Loader started
[L1]1373322363172 Loader started
[L2]1373322363176 Loader started
[L3]1373322363215 Loader started
[L1] 1000
[L0] 1000
[L3] 1000
[L2] 1000
...(중략)
[E0]1373322795287 - TOTAL Extracted ROWS: 2996217
[L1]1373322795290 TOTAL Loaded ROWS: 767331
[L3]1373322795303 TOTAL Loaded ROWS: 759888
[L2]1373322795312 TOTAL Loaded ROWS: 739667
[L0]1373322795314 TOTAL Loaded ROWS: 729331
Loading is ended.
Elapsed Time : 432951
ADD BATCH : 0
EXECUTE BATCH : 0
[VERIFICATION] Source Info : dbtech.lineitem: 2996217 Rows, Target Info :
dbtech.lineitem : 2996217 Rows [OK]-- nologging 테이블 확인
select logging from user_tables where table_name = 'LINEITEM';
-- DB Link를 통해 ITAS 수행 (appned)
insert /*+ append */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='O';-- dml 병렬 파라미터 활성화
alter session enable parallel dml;
-- DB Link를 통해 ITAS 수행 (parallel)
insert /*+ parallel(b 4) */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='O';-- 세션1과 세션2에서 입력한 최종 데이터
SQL> select l_linestatus, count(*) from dbtech.lineitem@olink group by l_linestatus;
L_LINESTATUS COUNT(*)
------------ --------
F 2996217
O 3004998
2 rows selectedSQL> set timing on
SQL> alter session enable parallel dml;
Session altered.
Total elapsed time 00:00:00.005235
SQL> insert /*+ append parallel(b 4) */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='O';
3004998 rows inserted.
Total elapsed time 00:07:07.806974
SQL> commit;
Commit completed.
Total elapsed time 00:00:00.000307SQL> set timing on
SQL> alter session enable parallel dml;
Session altered.
Total elapsed time 00:00:00.005235
SQL> insert /*+ append parallel(b 4) */ into dbtech.lineitem b
select * from lineitem@olink where l_linestatus='F';
2996217 rows inserted.
Total elapsed time 00:07:01.692934
SQL> commit;
Commit completed.
Total elapsed time 00:00:00.000307SQL> select count(*) from dbtech.lineitem;
COUNT(*)
-----------
6001215
1 row selected.# SAM 파일을 특정 기준으로 N개로 파일을 나눈다.
# 나눈 파일 마다 tbLoader Control 파일을 만들어준다.
# aa 컨트롤 파일
$ cat lineitem.ctl.aa
load data
infile 'lineitem.tbl.aa'
logfile 'lineitem.tbl.aa.log'
badfile 'lineitem.tbl.aa.bad'
append
into table lineitem
fields terminated by '|'
(L_ORDERKEY,
L_PARTKEY,
L_SUPPKEY,
L_LINENUMBER,
L_QUANTITY,
L_EXTENDEDPRICE,
L_DISCOUNT,
L_TAX,
L_RETURNFLAG,
L_LINESTATUS,
L_SHIPDATE,
L_COMMITDATE,
L_RECEIPTDATE,
L_SHIPINSTRUCT,
L_SHIPMODE,
L_COMMENT)
# ab 컨트롤 파일
$ cat lineitem.ctl.ab
load data
infile 'lineitem.tbl.ab'
logfile 'lineitem.tbl.ab.log'
badfile 'lineitem.tbl.ab.bad'
append
into table lineitem
fields terminated by '|'
(L_ORDERKEY,
L_PARTKEY,
L_SUPPKEY,
L_LINENUMBER,
L_QUANTITY,
L_EXTENDEDPRICE,
L_DISCOUNT,
L_TAX,
L_RETURNFLAG,
L_LINESTATUS,
L_SHIPDATE,
L_COMMITDATE,
L_RECEIPTDATE,
L_SHIPINSTRUCT,
L_SHIPMODE,
L_COMMENT)$ cat run.sh
# aa 파일 tbLoader 명령
nohup tbloader userid=dbtech/dbtech@tibero control=lineitem.ctl.aa
direct=y dpl_parallel=y &
# ab 파일 tbLoader 명령
nohup tbloader userid=dbtech/dbtech@tibero control=lineitem.ctl.ab
direct=y dpl_parallel=y &-- lineitem.tbl.aa.log:
tbLoader 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Data File : lineitem.tbl.aa
Bad File : lineitem.tbl.aa.bad Discard File : lineitem.tbl.aa.dsc
Table 'LINEITEM' was loaded from the data file.
COLUMN_NAME POSITION DATATYPE
--------------------- ----------- -----------------
L_ORDERKEY 1 CHARACTER
L_PARTKEY 2 CHARACTER
L_SUPPKEY 3 CHARACTER
L_LINENUMBER 4 CHARACTER
L_QUANTITY 5 CHARACTER
L_EXTENDEDPRICE 6 CHARACTER
L_DISCOUNT 7 CHARACTER
L_TAX 8 CHARACTER
L_RETURNFLAG 9 CHARACTER
L_LINESTATUS 10 CHARACTER
L_SHIPDATE 11 DATE
L_COMMITDATE 12 DATE
L_RECEIPTDATE 13 DATE
L_SHIPINSTRUCT 14 CHARACTER
L_SHIPMODE 15 CHARACTER
L_COMMENT 16 CHARACTER
Table LINEITEM :
------------------
0 Rows were skipped.
3000608 Rows were requested to load.
3000608 Rows were loaded successfully.
0 Rows were failed to load because of some errors
0 Rows were discarded.
0 Rows were failed to load because of some errors
Elapsed time was: 00:00:33.670898-- lineitem.tbl.ab.log:
tbLoader 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Data File : lineitem.tbl.ab
Bad File : lineitem.tbl.ab.bad
Discard File : lineitem.tbl.ab.dsc
Table 'LINEITEM' was loaded from the data file.
COLUMN_NAME POSITION DATATYPE
----------------------------- ----------- -----------
L_ORDERKEY 1 CHARACTER
L_PARTKEY 2 CHARACTER
L_SUPPKEY 3 CHARACTER
L_LINENUMBER 4 CHARACTER
L_QUANTITY 5 CHARACTER
L_EXTENDEDPRICE 6 CHARACTER
L_DISCOUNT 7 CHARACTER
L_TAX 8 CHARACTER
L_RETURNFLAG 9 CHARACTER
L_LINESTATUS 10 CHARACTER
L_SHIPDATE 11 DATE
L_COMMITDATE 12 DATE
L_RECEIPTDATE 13 DATE
L_SHIPINSTRUCT 14 CHARACTER
L_SHIPMODE 15 CHARACTER
L_COMMENT 16 CHARACTER
Table LINEITEM :
------------------
0 Rows were skipped.
3000607 Rows were requested to load.
3000607 Rows were loaded successfully.
0 Rows were failed to load because of some errors
0 Rows were discarded.
0 Rows were failed to load because of some errors
Total Elapsed Time: 00:00:33.795058SQL> conn sys
Enter Password:
Connected to Tibero.
SQL> conn syscat
Enter Password:
Connected to Tibero.SQL> COL owner FORMAT a20
SQL> SET PAGES 500
SQL> SELECT owner, object_type, status, count(*)
FROM dba_objects
GROUP BY owner, object_type, status
ORDER BY owner, object_type, status;SQL> SET LINESIZE 120
SQL> COL object_name FORMAT A30
SQL> COL owner FORMAT a20
SQL> SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status='INVALID';SQL> SET LINESIZE 120
SQL> COL file_name FORMAT A100
SQL> SELECT file_name FROM dba_data_files;ora_link_remote=(
(GATEWAY=(LISTENER=(HOST=12.34.56.78)
(PORT=9999))
(TARGET=orcl)
(TX_MODE=GLOBAL))
)db2_link_remote=(
(GATEWAY=(LISTENER=(HOST=12.34.56.78)
(PORT=9999))
(TARGET=sample)
(TX_MODE=GLOBAL))
)mssql_link_remote=(
(GATEWAY=(LISTENER=(HOST=12.34.56.78)
(PORT=9093))
(TARGET=12.34.56.87:1433:master)
(TX_MODE=LOCAL))
)ase_link_remote=(
(GATEWAY=(LISTENER=(HOST=12.34.56.78)
(PORT=9093)) (TARGET=12.34.56.87:5000:master) (TX_MODE=LOCAL))
)gp_link_remote=(
(GATEWAY=(LISTENER=(HOST=12.34.56.78)
(PORT=9093))
(TARGET=12.34.56.87:5432:mydb)
(TX_MODE=LOCAL))
)mysql_link_remote=(
(GATEWAY=(LISTENER=(HOST=12.34.56.78)
(PORT=9093))
(TARGET=12.34.56.87:3306:mydb)
(TX_MODE=LOCAL))
)SQL> SET LINESIZE 120
SQL> COL owner FORMAT a10
SQL> COL library_name FORMAT a30
SQL> COL file_spec FORMAT a50
SQL> COL status FORMAT a10
SQL> SELECT owner, library_name, file_spec, status
FROM DBA_LIBRARIES;SQL> SET LINESIZE 120
SQL> COL owner FORMAT a10
SQL> COL name FORMAT a30
SQL> COL source FORMAT a50
SQL> SELECT owner, name, source
FROM DBA_JAVA_CLASSES;[os_user$]tbdown immediate
[os_user$]ps -ef | grep tbsvr[os_user$]mv tibero7 tibero7*_<version>_<date>*
[os_user$]gunzip -v new_tibero_binary.tar.gz
[os_user$]tar -xvf new_tibero_binary.tar[tbtest@db1 ~]$ tbdown immediate
Tibero instance terminated (IMMEDIATE mode).
[tbtest@db1 ~]$ tbboot -v
Tibero 7 (DB 7.0 FS02) Build 260633
Linux bistro-build-centos_7 5.15.0-71-generic #78~20.04.1-Ubuntu SMP Wed Apr 19
11:26:48 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
...
Compiled at Jun 1 2023 15:38:28 build seq 260633 init rev {2022-03-10}
[tbtest@db1 ~]$ date
2023. 06. 21. (수) 16:52:41 KST
[tbtest@db1 ~]$ mv tibero7 tibero7_260633_20230621
[tbtest@db1 ~]$ gunzip tibero7-bin-FS02-linux64_3.10-260359-20230518173125.tar.gz
[tbtest@db1 ~]$ tar -xvf tibero7-bin-FS02-linux64_3.10-260359-20230518173125.tar
tibero7/bin/svr_rename.sh
tibero7/bin/alterdd.sh
tibero7/bin/tb_create_db.sh
tibero7/bin/tbiv
tibero7/bin/sm_sysmon_disk_state.sh
tibero7/bin/tbprof
tibero7/bin/tbinfo
tibero7/bin/tbboot
tibero7/bin/tbctl
tibero7/bin/tbcmobs
...[os_user$]cd ~/tibero7*_<version>_<date>*
[os_user$]mv database $TB_HOME$TB_HOME/client/epa/java/config/epa.cfg
$TB_HOME/client/epa/java/config/epalog.properties
$TB_HOME/client/bin/tbjavaepaexport BACKUP_TB_HOME=~/tibero7_<version>_<date>
diff $TB_HOME/client/epa/java/config/epa.cfg
$BACKUP_TB_HOME/client/epa/java/config/epa.cfgcp -p $BACKUP_TB_HOME/client/epa/java/config/epa.cfg
$TB_HOME/client/epa/java/config/epa.cfg[os_user$]cd $TB_HOME/config
[os_user$]sh gen_tip.sh[tbtest@db1 ~]$ cd tibero7/config
[tbtest@db1 config]$ sh gen_tip.sh
Using TB_SID "tibero"
/home/tbtest/tibero7/config/tibero.tip generated
/home/tbtest/tibero7/config/psm_commands generated
/home/tbtest/tibero7/client/config/tbdsn.tbr generated.
Running client/config/gen_esql_cfg.sh
Done.[os_user$]cd ~/tibero7*_<version>_<date>*
[os_user$]cp -rp license $TB_HOME[os_user$]cd ~/tibero7_260633_20230621/config
[os_user$]cp -rp license $TB_HOME[os_user$]cd ~/tibero7_*<version>_<date>*/config
[os_user$]cp -p $TB_SID.tip $TB_HOME/config[os_user$]cd ~/tibero7_260633_20230621/config
[os_user$]cp -p $TB_SID.tip $TB_HOME/config[os_user$]cd ~/tibero7_*<version>_<date>*/client/config
[os_user$]cp -p tbdsn.tbr $TB_HOME/client/config[os_user$]cd ~/tibero7_260633_20230621/client/config
[os_user$]cp -p tbdsn.tbr $TB_HOME/client/config[os_user$]cd ~/tibero7_*<version>_<date>*/client/config
[os_user$]cp -p *.cfg $TB_HOME/client/config[os_user$]cd ~/tibero7_260633_20230621/client/config
[os_user$]cp *.cfg $TB_HOME/client/config[os_user$]tbboot[os_user$]cd $TB_HOME/scripts
[os_user$]sh system.sh
Enter SYS password: tibero
Enter SYSCAT password: syscat
Create default system users & roles?(Y/N): N
Create system tables related to profile?(Y/N): N
Create TPR tables?(Y/N): N
Register dbms_stats job to Job Scheduler?(Y/N): N[os_user$]cd $TB_HOME/instance/$TB_SID/log/
[os_user$]cat system_init.log | **"TBR-17001"**
[os_user$]cat system_init.log | **"TBR-70004"**[os_user$]$ tbsql sys
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Enter Password:
Connected to Tibero.
SQL> alter system recompile all;[os_user$]cd $TB_HOME/scripts
[os_user$]tbsql dbtech/dbtech
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> @revalidate_viewnsyn.sql
PSM completed.SQL> SET LINESIZE 120
SQL> COL object_name FORMAT A30
SQL> COL owner FORMAT a20
SQL> SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status='INVALID';SQL> alter function *<function_name>* compile;
SQL> alter procedure *<procedure_name>* compile;SQL> alter index index_name rebuild;[Tibero_Node1]# tbdown immediate
[Tibero_Node1]# tbcm -d[Tibero_Node2]# tbdown immediate
[Tibero_Node2]# tbcm -d[Tibero_Node1]tbboot -v[Tibero_Node1]# cd ~
[Tibero_Node1]# mv tibero7 tibero7_257078_20230717
[Tibero_Node1]# gunzip tibero7-bin-FS02-linux64_3.10-257446-20230113162027.tar.gz
[Tibero_Node1]# tar -xvf tibero7-bin-FS02-linux64_3.10-257446-20230113162027.tar[Tibero_Node2]# cd ~
[Tibero_Node2]# mv tibero7 tibero7_257078_20230717
[Tibero_Node2]# gunzip tibero7-bin-FS02-linux64_3.10-257446-20230113162027.tar.gz
[Tibero_Node2]# tar -xvf tibero7-bin-FS02-linux64_3.10-257446-20230113162027.tar[Tibero_Node1]# cd $TB_HOME/config
[Tibero_Node1]# sh gen_tip.sh[Tibero_Node2]# cd $TB_HOME/config
[Tibero_Node2]# sh gen_tip.sh[Tibero_Node1]# cd ~/tibero7_*<version>_<date>*
[Tibero_Node1]# cp -rp license $TB_HOME[Tibero_Node2]# cd ~/tibero7_*<version>_<date>*
[Tibero_Node2]# cp -rp license $TB_HOME[Tibero_Node1]# cd ~/tibero7_*<version>_<date>*/config
[Tibero_Node1]# cp -p $TB_SID.tip $TB_HOME/config[Tibero_Node2]# cd ~/tibero7_*<version>_<date>*/config
[Tibero_Node2]# cp -p $TB_SID.tip $TB_HOME/config[Tibero_Node1]# cd ~/tibero7_*<version>_<date>*/client/config
[Tibero_Node1]# cp -p tbdsn.tbr $TB_HOME/client/config[Tibero_Node2]# cd ~/tibero7_*<version>_<date>*/client/config
[Tibero_Node2]# cp -p tbdsn.tbr $TB_HOME/client/config[Tibero_Node1]# cd ~/tibero7*_<version>_<date>*/client/config
[Tibero_Node1]# cp -p *.cfg $TB_HOME/client/config[Tibero_Node2]# cd ~/tibero7*_<version>_<date>*/client/config
[Tibero_Node2]# cp -p *.cfg $TB_HOME/client/config[Tibero_Node1]# tbcm -b
[Tibero_Node1]# tbboot[Tibero_Node2]# tbcm -b
[Tibero_Node2]# tbboot[Tibero_Node1]# cd $TB_HOME/scripts
[Tibero_Node1]# sh system.sh
Enter SYS password: tibero
Enter SYSCAT password: syscat
Create default system users & roles?(Y/N): N
Create system tables related to profile?(Y/N): N
Create TPR tables?(Y/N): N
Register dbms_stats job to Job Scheduler?(Y/N): N[Tibero_Node1]# cd $TB_HOME/instance/$TB_SID/log/
[Tibero_Node1]# cat system_init.log | grep **"TBR-17001"**
[Tibero_Node1]# cat system_init.log | grep **"TBR-70004"**[Tibero_Node1]# tbsql sys
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Enter Password:
Connected to Tibero.
SQL> alter system recompile all;[Tibero_Node1]# cd $TB_HOME/scripts
[Tibero_Node1]# tbsql dbtech/dbtech
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> @revalidate_viewnsyn.sql
PSM completed.[os_user$]mv tibero7 tibero7_*<version>_<date>*
[os_user$]gunzip -v new_tibero_binary.tar.gz
[os_user$]tar -xvf new_tibero_binary.tar[tbtest@db1 ~]$ tbdown immediate
Tibero instance terminated (IMMEDIATE mode).
[tbtest@db1 ~]$ tbboot -v
Tibero 7 (DB 7.0 FS02) Build 260633
Linux bistro-build-centos_7 5.15.0-71-generic #78~20.04.1-Ubuntu SMP Wed Apr 19
11:26:48 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
...
Compiled at Jun 1 2023 15:38:28 build seq 260633 init rev {2022-03-10}
[tbtest@db1 ~]$ date
2023. 06. 21. (수) 16:52:41 KST
[tbtest@db1 ~]$ mv tibero7 tibero7_260633_20230621
[tbtest@db1 ~]$ gunzip tibero7-bin-FS02-linux64_3.10-260359-20230518173125.tar.gz
[tbtest@db1 ~]$ tar -xvf tibero7-bin-FS02-linux64_3.10-260359-20230518173125.tar
tibero7/bin/svr_rename.sh
tibero7/bin/alterdd.sh
tibero7/bin/tb_create_db.sh
tibero7/bin/tbiv
tibero7/bin/sm_sysmon_disk_state.sh
tibero7/bin/tbprof
tibero7/bin/tbinfo
tibero7/bin/tbboot
tibero7/bin/tbctl
tibero7/bin/tbcmobs
...[os_user$]cd ~/tibero7_*<version>_<date>*
[os_user$]mv database $TB_HOME$TB_HOME/client/epa/java/config/epa.cfg
$TB_HOME/client/epa/java/config/epalog.properties
$TB_HOME/client/bin/tbjavaepaexport BACKUP_TB_HOME=~/tibero7_*<version>_<date>*
diff $TB_HOME/client/epa/java/config/epa.cfg
$BACKUP_TB_HOME/client/epa/java/config/epa.cfgcp -p $BACKUP_TB_HOME/client/epa/java/config/epa.cfg
$TB_HOME/client/epa/java/config/epa.cfg[os_user$]cd $TB_HOME/config
[os_user$]sh gen_tip.sh[tbtest@db1 ~]$ cd tibero7/config
[tbtest@db1 config]$ sh gen_tip.sh
Using TB_SID "tibero"
/home/tbtest/tibero7/config/tibero.tip generated
/home/tbtest/tibero7/config/psm_commands generated
/home/tbtest/tibero7/client/config/tbdsn.tbr generated.
Running client/config/gen_esql_cfg.sh
Done.[os_user$]cd ~/tibero7_*<version>_<date>*
[os_user$]cp -rp license $TB_HOME[os_user$]cd ~/tibero7_260633_20230621/config
[os_user$]cp -rp license $TB_HOME[os_user$]cd ~/tibero7_*<version>_<date>*/config
[os_user$]cp -p $TB_SID.tip $TB_HOME/config[os_user$]cd ~/tibero7_260633_20230621/config
[os_user$]cp -p $TB_SID.tip $TB_HOME/config[os_user$]cd ~/tibero7_*<version>_<date>*/client/config
[os_user$]cp -p tbdsn.tbr $TB_HOME/client/config[os_user$]cd ~/tibero7_260633_20230621/client/config
[os_user$]cp -p tbdsn.tbr $TB_HOME/client/config[os_user$]cd ~/tibero7*_<version>_<date>*/client/config
[os_user$]cp -p *.cfg $TB_HOME/client/config[os_user$]cd ~/tibero7_260633_20230621/client/config
[os_user$]cp *.cfg $TB_HOME/client/configSQL> SET LINESIZE 120
SQL> COL object_name FORMAT A30
SQL> COL owner FORMAT a20
SQL> SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status='INVALID';$ tbdown immediate
$ tbcm -d$ mv ~/tibero7 ~/tibero7_260633_20230621$ gunzip tibero7-bin-FS02-linux64_3.10-260359-20230518173125.tar.gz
$ tar -xvf tibero7-bin-FS02-linux64_3.10-260359-20230518173125.tar$ cd $TB_HOME/config
$ sh gen_tip.sh$ cp ~/tibero7_260633_20230621/config/$TB_SID.tip $TB_HOME/config
$ cp ~/tibero7_260633_20230621/client/config/tbdsn.tbr $TB_HOME/client/config
$ cp ~/tibero7_260633_20230621/client/config/*.cfg $TB_HOME/client/config
$ cp -R ~/tibero7_260633_20230621/license $TB_HOME$ tbcm -b
$ tbboot$ cd $TB_HOME/scripts
$ ./system.sh$ tbsql sys
SQL> alter system recompile all;
SQL> quit$ tbsql tibero
SQL> @revalidate_viewnsyn.sqlSQL> SET LINESIZE 120
SQL> COL object_name FORMAT A30
SQL> COL owner FORMAT a20
SQL> SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status='INVALID';ALTER *<OBJECT_TYPE> <OWNER.OBJECT_NAME>* COMPILE;
select * from v$version;$ 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
VIETNAMESEselect username, account_status, default_tablespace, temporary_tablespace
from dba_users order by oracle;set linesize 120
set pagesize 100
select username, default_tablespace, temporary_tablespace
from dba_users where account_status='OPEN' order by username;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;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;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;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;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;set linesize 150
set pagesize 100
column value$ format a100
select
name,
value$
from
sys.props$
where
name like '%CHARACTERSET'
order by
name;select * from _vt_nls_character_set;show parameter <파라미터명>set linesize 150
select * from v$logfile;
select group#, bytes/1024/1024 as "size(mb)", members, archived from v$log;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' -- show parameters undo_tablespace;의 결과값 입력col file_name format a50
select tablespace_name, file_name, bytes/1024/1024 "size mb", autoextensible
from dba_temp_files;select log_mode from v$database;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;cat $ORACLE_HOME/network/admin/tnsnames.oraset 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;SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_TYPE LIKE '%CLUSTER%'
AND OWNER NOT IN ('SYS');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');SELECT *
FROM DBA_INDEXES
WHERE INDEX_TYPE LIKE '%BITMAP%';$ tbsql sys/tibero
tbSQL
Connected to Tibero.
SQL> spool export_ddl.log Spooling is started.
SQL> @export_ddl.sql
-- spool 파일을 통해 오류 확인
SQL> spool off
Spooling is stopped: export_ddl.log$ tbsql sys/tibero
tbSQL
Connected to Tibero.
SQL> spool export_other_ddl.log Spooling is started.
SQL> @export_other_ddl.sql
-- spool 파일을 통해 오류 확인
SQL> spool off
Spooling is stopped: export_other_ddl.logSELECT 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;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;
/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;
/alter trigger trigger_name disable;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;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';$ tbsql sys/tibero
tbSQL
Connected to Tibero.
SQL> exec utl_recomp.recomp_serial(schema=>'MIGRATION_USER');
PSM completed.select * from view_name where rownum < 1;alter function function_name compile;
alter procedure procedure_name compile;show errorsalter index index_name rebuild;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;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;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;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;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;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;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;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;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; 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;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;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;select 'tbl1' as table_name, count(*) as oracle_count, count(*)
- (select count(*) from tbl1 where rownum = 1) as diff from tbl1@ora;select * from tbl1@ora minus
select * from tbl1;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");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");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;DRIVER={Tibero 7 ODBC Driver};SERVER=127.0.0.1;PORT=8629
;DB=t7;UID=dbtech;PWD=dbtech;티베로 Database 백업과 복구( DB Backup & Recovery) 의 기본 개념에 대해서 설명합니다. 논리적인 백업 (Logical Backup), 물리적인 백업 (Physical Backup), Offline 백업 / Cold 백업, Online 백업 / Hot 백업 등 세부 사항에 대해 소개합니다.
v$controlfileSQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<FILENAME>' REUSE NORESETLOGS;SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
--------- --------- ------------
0 ACTIVE 21091
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0SQL> !cp /home/tibero/tbdata/system001.dtf /home/tibero/tbdata_bak/system001.dtfSQL> ALTER TABLESPACE SYSTEM END BACKUP;SQL> select name from v$datafile;
NAME
--------------------------------------------
/home/tibero/tbdata/system001.dtf
/home/tibero/tbdata/undo001.dtf
/home/tibero/tbdata/usr001.dtf
/home/tibero/tbdata/apm_ts.dtf
SQL> select group#, member from v$logfile;
GROUP# MEMBER
------ ----------------------------------
0 /home/tibero/tbdata/redo01.redo
1 /home/tibero/tbdata/redo11.redo
2 /home/tibero/tbdata/redo21.redoSQL> select name from v$controlfile;
NAME
-------------------------------------
/home/tibero/tbdata/c1.ctl
/home/tibero/tbdata/c2.ctltbdown$ cp /home/tibero/tbdata/*.dtf /home/tibero/tbdata_bak/
$ cp /home/tibero/tbdata/*.redo /home/tibero/tbdata_bak/
$ cp /home/tibero/tbdata/*.ctl /home/tibero/tbdata_bak/
또는
$ cp /home/tibero/tbdata/*.* /home/tibero/tbdata_bak/V$RECOVER_FILE
V$RECOVERY_FILE_STATUS
V$LOGFILE V$CONTROLFILE
V$LOG$ tbboot -t resetlogsDB_NAME=tibero
LISTENER_PORT=8629
CONTROL_FILES="/home/tibero/tbdata/c1.ctl", "/home/tibero/tbdata/c2.ctl"
DB_CREATE_FILE_DEST="/home/tibero/tbdata"
LOG_ARCHIVE_DEST="/home/tibero/tbarch"
#CERTIFICATE_FILE="/home/tibero/tibero7/config/svr_wallet/tibero.crt"
#PRIVKEY_FILE="/home/tibero/tibero7/config/svr_wallet/tibero.key"
#WALLET_FILE="/home/tibero/tibero7/config/svr_wallet/WALLET"
MAX_SESSION_COUNT=100
TOTAL_SHM_SIZE=1024MCREATE DATABASE "tibero"
USER SYS IDENTIFIED BY TIBERO
MAXDATAFILES 4096
CHARACTER SET MSWIN949
LOGFILE GROUP 0 ('redo01.redo', 'redo02.redo') SIZE 50M,
GROUP 1 ('redo11.redo', 'redo12.redo') SIZE 50M,
GROUP 2 ('redo21.redo', 'redo22.redo') SIZE 50M
MAXLOGFILES 100
MAXLOGMEMBERS 8 ARCHIVELOG
DATAFILE 'system001.dtf' SIZE 256M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TABLESPACE USR
DATAFILE 'usr001.dtf' SIZE 128M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'temp001.dtf' SIZE 512M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M EXTENT MANAGEMENT
LOCAL AUTOALLOCATE UNDO TABLESPACE UNDO
DATAFILE 'undo001.dtf' SIZE 512M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;DROP TABLESPACE "MY_FILE001" INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE "MY_FILE001" DATAFILE 'my_file001.dtf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE 3G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;DROP USER TIBERO CASCADE;
CREATE USER tibero IDENTIFIED BY tmax
DEFAULT TABLESPACE MY_FILE001;
GRANT CONNECT, RESOURCE, DBA TO tibero;$ tbboot
$ tbsql sys/tibero
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS
'/home/tibero/tbdata/ctl_backup.sql' REUSE NORESETLOGS;
$ rm -f /home/tibero/tbdata/c1.ctl$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
********************************************************
* Warning: Control file open failed
* /home/tibero/tbdata/SYSTEM/c1.ctl
********************************************************
********************************************************
*Critical Warning : Raise svmode failed. The reason is
*TBR-24003 : Unable to read control file.
*Current server mode is NOMOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at NOMOUNT mode.
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> @ctl_backup.sql
Control File created.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot
change core dump dir to /home/tibero/tibero7/bin/prof
Listener port = 8629
********************************************************
Critical Warning : Raise svmode failed. The reason is
TBR-1024 : Database needs media recovery:
media recovery required(/home/tibero/tbdata/system001.dtf).
Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
--------- ------- ------------------------ -------- --------
0 ONLINE media recovery required 18144 2023/07/15
1 ONLINE media recovery required 18144 2023/07/15
2 ONLINE media recovery required 18144 2023/07/15
3 ONLINE media recovery required 18144 2023/07/15
4 ONLINE media recovery required 18144 2023/07/15
rows selected.
SQL> alter database recover automatic database;
Database altered.
SQL> select * from v$recover_file;
0 row selected.
SQL> exit Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot
change core dump dir to /home/tibero/tibero7/bin/prof
Listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode)
$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
--백업한 컨트롤 파일 스크립트내의 주석 스크립트 부분을 활용하여 Temp 파일 생성
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/tibero/tbdata/temp001.dtf'
SIZE 1G REUSE AUTOEXTEND OFF;
Tablespace 'TEMP' altered.
SQL> exit
Disconnected.$ rm -f /home/tibero/tbdata/c1.ctl
$ tbdown
$ tbboot
Change core dump dir to /home/test/tibero7/bin/prof.
Listener port = 8629
********************************************************
* Warning: Control file open failed
* /home/tibero/tbdata/SYSTEM/c1.ctl
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
$ tail -f sys.log
[2023-07-15T16:03:06.098870] [CLC-28] [I] control file write failed: no fd for
'/home/tibero/tbdata/SYSTEM/c1.ctl'
[2023-07-15T16:03:08.099663] [CLC-28] [I] control file read failed :
'/home/tibero/tbdata/SYSTEM/c1.ctl' (blkno=1) (no fd)
[2023-07-15T16:03:08.100278] [CLC-28] [I] control file write failed: no fd for
'/home/tibero/tbdata/SYSTEM/c1.ctl'
[2023-07-15T16:03:10.101093] [CLC-28] [I] control file read failed :
'/home/tibero/tbdata/SYSTEM/c1.ctl' (blkno=1) (no fd)
[2023-07-15T16:03:10.101527] [CLC-28] [I] control file write failed: no fd for
'/home/tibero/tbdata/SYSTEM/c1.ctl'
[2023-07-15T16:03:12.101537] [CLC-28] [I] control file read failed :
'/home/tibero/tbdata/SYSTEM/c1.ctl' (blkno=1) (no fd)
[2023-07-15T16:03:12.102669] [CLC-28] [I] control file write failed: no fd for
'/home/tibero/tbdata/SYSTEM/c1.ctl'
[2023-07-15T16:03:14.103789] [CLC-28] [I] control file read failed :
'/home/tibero/tbdata/SYSTEM/c1.ctl' (blkno=1) (no fd)
[2023-07-15T16:03:14.105566] [CLC-28] [I] control file write failed: no fd for
'/home/tibero/tbdata/SYSTEM/c1.ctl'
[2023-07-15T16:03:16.107421] [CLC-28] [I] control file read failed :
'/home/tibero/tbdata/SYSTEM/c1.ctl' (blkno=1) (no fd)
[2023-07-15T16:03:16.107935] [CLC-28] [I] control file write failed: no fd for
'/home/tibero/tbdata/SYSTEM/c1.ctl'
$ tbdown$ cp /home/tibero/tbdata/c2.ctl /home/tibero/tbdata/c1.ctl
$ tbbootSQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
0 /home/tibero/tbdata/redo02.redo 50 YES 3 INACTIVE
1 /home/tibero/tbdata/redo11.redo 50 NO 4 CURRENT
1 /home/tibero/tbdata/redo12.redo 50 NO 4 CURRENT
2 /home/tibero/tbdata/redo21.redo 50 YES 2 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 2 INACTIVE
6 rows selected.
SQL> !rm -f /home/tibero/tbdata/redo01.redo
SQL> !ls /home/tibero/tbdata/redo01.redo
ls: /home/tibero/tbdata/redo01.redo: No such file or directory
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------ --------------------- ---- ----- ----- --------
0 /home/tibero/tbdata/redo01.redo 50 NO 9 CURRENT
0 /home/tibero/tbdata/redo02.redo 50 NO 9 CURRENT
1 /home/tibero/tbdata/redo11.redo 50 YES 7 INACTIVE
1 /home/tibero/tbdata/redo12.redo 50 YES 7 INACTIVE
2 /home/tibero/tbdata/redo21.redo 50 YES 8 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 8 INACTIVE
6 rows selected.
SQL> !
$vi /home/tibero/tibero7/instance/tibero/log/slog/sys.log
[2023-07-15T17:34:46.359823] [DDL-91] [I] Executing DDL: alter system switch logfile
[2023-07-15T17:34:46.360003] [CLL-26] [I] switch log file 2 -> 0 ...
[2023-07-15T17:34:46.364365] [RLS-26] [I] lf hdrblk write: fno(2) cf_seq(143) seq(36)
nab(1) low(0000.0000c5ed) next(0000.0000c5f2) st(33)
[2023-07-15T17:34:46.365318] [RLS-26] [I] lf hdrblk write: fno(2) cf_seq(143) seq(36)
nab(1) low(0000.0000c5ed) next(0000.0000c5f2) st(33)
[2023-07-15T17:34:46.365433] [CFD-26] [I] open (stat) failed (errno=2, No such file or
directory) (flag=010002) (filename=/home/tibero/tbdata/redo01.redo)
[2023-07-15T17:34:46.365445] [CLL-26] [I] WARNING: LOG MEMBER missing during log switch,
filename: /home/tibero/tbdata/redo01.redo
[2023-07-15T17:34:46.365566] [CFD-26] [I] open (stat) failed (errno=2, No such file or
directory) (flag=010002) (filename=/home/tibero/tbdata/redo01.redo)
[2023-07-15T17:34:46.366158] [RLS-26] [I] Failed to read 1 log blocks in logfile group #0
(member=1, blkno=0)
[2023-07-15T17:34:46.366206] [CLL-26] [I] One of the log member(lf id = 0, lm id = 1) is
invalid, all must be valid
[2023-07-15T17:34:46.366820] [RLS-26] [I] lf hdrblk write: fno(0) cf_seq(143) seq(37)
nab(4294967295) low(0000.0000c5f2) next(ffff.ffffffff) st(129)
[2023-07-15T17:34:46.366850] [RLS-26] [I] lf hdrblk write: fno(0) cf_seq(143) seq(37)
nab(4294967295) low(0000.0000c5f2) next(ffff.ffffffff) st(129)
[2023-07-15T17:34:46.369945] [CLL-26] [I] LOGFILE SWITCHED. LF#2(seqno=36, blocks=0
next=0000.0000c5f2)
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/home/tibero/tbdata/redo01.redo';
Database altered.
SQL> alter database add logfile member '/home/tibero/tbdata/redo01.redo' to group 0;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> !
$vi /home/tibero/tibero7/instance/tibero/log/slog/sys.log
[2023-07-15T10:29:16.500746] [DDL-91] [I] Executing DDL: alter database add logfile
member '/home/tibero/tbdata/redo01.redo' to group 0
[2023-07-15T10:29:16.687006] [DDL-91] [I] Alter database type 1 succeeded.
[2023-07-15T10:29:16.687169] [DDL-91] [I] DDL execution succeeded
[2023-07-15T10:29:35.029699] [DDL-91] [I] Executing DDL: alter system switch logfile
[2023-07-15T10:29:35.029934] [CLL-26] [I] switch log file 1 -> 2 …
[2023-07-15T10:29:35.032529] [RLS-26] [I] lf hdrblk write: fno(1) cf_seq(155) seq(38)
nab(23) low(0000.0000c853) next(0000.0000c8ad) st(33)
[2023-07-15T10:29:35.033078] [RLS-26] [I] lf hdrblk write: fno(1) cf_seq(155) seq(38)
nab(23) low(0000.0000c853) next(0000.0000c8ad) st(33)
[2023-07-15T10:29:35.034520] [RLS-26] [I] lf hdrblk write: fno(2) cf_seq(155) seq(39)
nab(4294967295) low(0000.0000c8ad) next(ffff.ffffffff) st(129)
[2023-07-15T10:29:35.034964] [RLS-26] [I] lf hdrblk write: fno(2) cf_seq(155) seq(39)
nab(4294967295) low(0000.0000c8ad) next(ffff.ffffffff) st(129)
[2023-07-15T10:29:35.038120] [CLL-26] [I] LOGFILE SWITCHED. LF#1(seqno=38, blocks=22
next=0000.0000c8ad)
[2023-07-15T10:29:35.038217] [DDL-91] [I] DDL execution succeeded
[2023-07-15T10:29:35.038881] [CLA-27] [I] LOG GROUP 1 (lsn=38) archive start.
(/home/tibero/tbdata/arch/log-t0-r0-s38.arc)
[2023-07-15T10:29:35.040019] [CLA-27] [I] LOG GROUP 1 (lsn=38) ARCHIVED.
(/home/tibero/tbdata/arch/log-t0-r0-s38.arc)$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------ --------------------------- ---- ---- ----- --------
0 /home/tibero/tbdata/redo01.redo 50 YES 4 INACTIVE
0 /home/tibero/tbdata/redo02.redo 50 YES 4 INACTIVE
1 /home/tibero/tbdata/redo11.redo 50 NO 5 CURRENT
1 /home/tibero/tbdata/redo12.redo 50 NO 5 CURRENT
2 /home/tibero/tbdata/redo21.redo 50 YES 3 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 3 INACTIVE
6 rows selected.
SQL> !rm -f /home/tibero/tbdata/redo01.redo
SQL> !rm -f /home/tibero/tbdata/redo02.redo
SQL> !ls /home/tibero/tbdata/redo01.redo
ls: /home/tibero/tbdata/redo01.redo: No such file or directory
SQL> !ls /home/tibero/tbdata/redo02.redo
ls: /home/tibero/tbdata/redo02.redo: No such file or directory
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> exit
Disconnected.$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter database drop logfile group 0;
Database altered.
SQL> alter database add logfile group 0 ('/home/tibero/tbdata/redo01.redo',
'/home/tibero/tbdata/redo02.redo') size 50M;
Database altered.
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------- -------------------------------- ---- ---- ------ --------
0 /home/tibero/tbdata/redo01.redo 50 YES -1 UNUSED
0 /home/tibero/tbdata/redo02.redo 50 YES -1 UNUSED
1 /home/tibero/tbdata/redo11.redo 50 NO 5 CURRENT
1 /home/tibero/tbdata/redo12.redo 50 NO 5 CURRENT
2 /home/tibero/tbdata/redo21.redo 50 YES 3 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 3 INACTIVE
6 rows selected.$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------- -------------------------------- ---- ---- ------ --------
0 /home/tibero/tbdata/redo01.redo 50 NO 7 CURRENT
0 /home/tibero/tbdata/redo02.redo 50 NO 7 CURRENT
1 /home/tibero/tbdata/redo11.redo 50 YES 5 CURRENT
1 /home/tibero/tbdata/redo12.redo 50 YES 5 CURRENT
2 /home/tibero/tbdata/redo21.redo 50 YES 6 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 6 INACTIVE
6 rows selected.
SQL> CREATE TABLE T1 (C1 VARCHAR(5));
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00014');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------- -------------------------------- ---- ---- ------ --------
0 /home/tibero/tbdata/redo01.redo 50 NO 10 CURRENT
0 /home/tibero/tbdata/redo02.redo 50 NO 10 CURRENT
1 /home/tibero/tbdata/redo11.redo 50 YES 8 INACTIVE
1 /home/tibero/tbdata/redo12.redo 50 YES 8 INACTIVE
2 /home/tibero/tbdata/redo21.redo 50 YES 9 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 9 INACTIVE
6 rows selected.
SQL> !rm -f /home/tibero/tbdata/redo01.redo
SQL> !rm -f /home/tibero/tbdata/redo02.redo
SQL> !ls /home/tibero/tbdata/redo01.redo
/home/tibero/tbdata/redo01.redo not found
SQL> !ls /home/tibero/tbdata/redo02.redo
/home/tibero/tbdata/redo02.redo not found
SQL> exit
Disconnected.
$ tbdown abort
Tibero instance terminated (ABORT mode).$ cp /home/tibero/tbdata_bak/*.dtf /home/tibero/tbdata
$ tbboot mount
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> set line 200 SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------- -------------------------------- ---- ---- ------ --------
0 /home/tibero/tbdata/redo01.redo 50 NO 10 CURRENT
0 /home/tibero/tbdata/redo02.redo 50 NO 10 CURRENT
1 /home/tibero/tbdata/redo11.redo 50 YES 8 INACTIVE
1 /home/tibero/tbdata/redo12.redo 50 YES 8 INACTIVE
2 /home/tibero/tbdata/redo21.redo 50 YES 9 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 YES 9 INACTIVE
6 rows selected.
--next_change# - 1 값 조회
SQL> set line 200
SQL> col name for a40
SQL> col SEQ# for 999
SQL> col TSN for 999999999
SQL> select name, sequence# seq#, next_change#-1 tsn
from v$archive_dest_files order by 2;
NAME SEQ# TSN
-------------------------------------------- ------ -----------
/home/tibero/tbdata/arch/log-t0-r0-s7.arc 7 45589
/home/tibero/tbdata/arch/log-t0-r0-s8.arc 8 45597
/home/tibero/tbdata/arch/log-t0-r0-s9.arc 9 45606
3 rows selected.
--TSN 정보가 없을 경우 Log 파일 덤프를 발생시킨다.
SQL> alter system dump logfile '/home/tibero/tbdata/arch/log-t0-r0-s9.arc';
System altered.
SQL> exit
Disconnected.
$ cd $TB_HOME/instance/$TB_SID/dump/tracedump
$ ls -rlt
total 24
-rw-r--r-- 1 tibero dba 2688 Jul 15 19:08 tb_dump_47120982_37_2.trc
--next_tsn 값을 10진수로 변경 : 0000.0000b227 (hex) => 45607 (dec)
--next_tsn - 1 = 45606
$ grep next tb_dump_47120982_37_2.trc
low=0000.0000b21e 2023-07-10 18:59:28 next=0000.0000b227 2023-07-15 18:59:43
...(이하생략)
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter database recover automatic database until change 45606;
Database altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot -t resetlogs
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from t1;
C1
--------
00011
00012
00013
3 rows selected.$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> CREATE TABLE T1 (C1 VARCHAR(5));
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00014');
1 row inserted.
QL> COMMIT;
Commit completed.
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------- -------------------------------- ---- ---- ------ --------
0 /home/tibero/tbdata/redo01.redo 50 NO 7 CURRENT
0 /home/tibero/tbdata/redo02.redo 50 NO 7 CURRENT
1 /home/tibero/tbdata/redo11.redo 50 NO 5 INACTIVE
1 /home/tibero/tbdata/redo12.redo 50 NO 5 INACTIVE
2 /home/tibero/tbdata/redo21.redo 50 NO 6 INACTIVE
2 /home/tibero/tbdata/redo22.redo 50 NO 6 INACTIVE
6 rows selected.
SQL> !rm -f /home/tibero/tbdata/redo01.redo
SQL> !rm -f /home/tibero/tbdata/redo02.redo
SQL> !ls /home/tibero/tbdata/redo01.redo
/home/tibero/tbdata/redo01.redo not found
SQL> !ls /home/tibero/tbdata/redo02.redo
/home/tibero/tbdata/redo02.redo not found
SQL> exit
Disconnected.
$ tbdown immediate
Tibero instance terminated (IMMEDIATE mode).$ tbboot
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
********************************************************
*Critical Warning : Raise svmode failed. The reason is
*TBR-1042 : Unable to read log member file in group 0, member 1
(/home/tibero/tbdata/redo01.redo), block 0.
* Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
Database altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot resetlogs
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero
SQL> !ls /home/tibero/tbdata/redo01.redo
/home/tibero/tbdata/redo01.redo
SQL> !ls /home/tibero/tbdata/redo02.redo
/home/tibero/tbdata/redo02.redo
/home/tibero/tbdata/redo02.redo
SQL> set line 200
SQL> col group# for 999
SQL> col member for a50
SQL> col SEQ# for 999
SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------- -------------------------------- ---- ---- ------ --------
0 /home/tibero/tbdata/redo01.redo 50 NO 1 CURRENT
0 /home/tibero/tbdata/redo02.redo 50 NO 1 CURRENT
1 /home/tibero/tbdata/redo11.redo 50 NO -1 UNUSED
1 /home/tibero/tbdata/redo12.redo 50 NO -1 UNUSED
2 /home/tibero/tbdata/redo21.redo 50 NO -1 UNUSED
2 /home/tibero/tbdata/redo22.redo 50 NO -1 UNUSED
6 rows selected.
SQL> select * from t1;
C1
-------
00011
00012
00013
00014
4 rows selected$ tbboot
$ tbsql sys/tibero
$ rm –rf /home/tibero/tbdata/temp001.dtf$ tbdown
$ ls -al /home/tibero/tbdata/temp001.dtf
ls: /home/tibero/tbdata/temp001.dtf: No such file or directory
$ tbboot
$ ls -al /home/tibero/tbdata/temp001.dtf
-rw------- 1 tibero dba 536870912 Jun 15 17:25 /home/tibero/tbdata/temp001.dtf$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
$ tbsql tibero/tmax
SQL> CREATE TABLE T1 (C1 VARCHAR(5)) TABLESPACE MY_FILE001;
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> exit
$ tbdown
$ rm –f /home/tibero/tbdata/my_file001.dtf$ cp /home/tibero/tbdata_bak/*.dtf /home/tibero/tbdata
$ tbboot mount
$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> ALTER DATABASE RECOVER AUTOMATIC;
Database altered.
SQL> exit Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot
listener port = 8629
change core dump dir to /home/tibero_td/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
$ tbsql tibero/tmax tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero.
SQL> select * from t1;
C1
------
00011
00012
00013
3 rows selected.
$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
$ tbsql tibero/tmax
SQL> CREATE TABLE T1 (C1 VARCHAR(5)) TABLESPACE MY_FILE001;
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> exit
$ tbdown
$ rm –f /home/tibero/tbdata/my_file001.dtf
$ cp /home/tibero/tbdata_bak/*.dtf /home/tibero/tbdata
$ tbboot mount
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> ALTER DATABASE RECOVER;
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/tbarch/log-t0-r0-s1.arc';
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/tbarch/log-t0-r0-s2.arc';
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/tbarch/log-t0-r0-s3.arc';
Database altered.
SQL> select * from v$log;
THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ------ --------- -------- ------- --------- ------ ------------- -----------
0 0 4 52428800 2 NO CURRENT 14521 2023/07/15
0 1 2 52428800 2 YES CURRENT 14516 2023/07/15
0 2 3 52428800 2 YES CURRENT 14518 2023/07/15
3 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS_TYPE MEMBER
------ ------------ ---------------------------------
0 ONLINE /home/tibero/tbdata/redo01.redo
0 ONLINE /home/tibero/tbdata/redo02.redo
1 ONLINE /home/tibero/tbdata/redo11.redo
1 ONLINE /home/tibero/tbdata/redo12.redo
2 ONLINE /home/tibero/tbdata/redo21.redo
2 ONLINE /home/tibero/tbdata/redo22.redo
6 rows selected.
SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/tbdata/redo01.redo';
Database altered.
SQL> exit Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
$ tbsql tibero/tmax
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from t1;
C1
-------
00011
00012
00013
3 rows selected.
$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
$ tbsql tibero/tmax
SQL> CREATE TABLE T1 (C1 VARCHAR(5)) TABLESPACE MY_FILE001;
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> exit
$ tbdown
$ rm –f /home/tibero/tbdata/my_file001.dtf
$ tbboot mount
$ tbsql sys/tibero
SQL> ALTER DATABASE CREATE DATAFILE '/home/tibero/tbdata/my_file001.dtf';
Database altered.
SQL> ALTER DATABASE RECOVER AUTOMATIC;
Database altered.
SQL> exit
$ tbdown
$ tbboot
$ tbsql tibero/tmax
SQL> SELECT * FROM T1;
C1
-------
00011
00012
00013
3 rows selected.
$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
$ tbsql tibero/tmax
SQL> CREATE TABLE T1 (C1 VARCHAR(5)) TABLESPACE MY_FILE001;
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> exit Disconnected.
$ rm –f /home/tibero/tbdata/my_file001.dtf
$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter tablespace MY_FILE001 offline immediate;
Tablespace 'MY_FILE001' altered.
SQL> exit Disconnected.
$ cp /home/tibero/tbdata_bak/my_file001.dtf /home/tibero/tbdata
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter database recover automatic tablespace MY_FILE001;
Database altered.
SQL> alter tablespace MY_FILE001 online;
Tablespace 'MY_FILE001' altered.
SQL> conn tibero/tmax Connected to Tibero.
SQL> select * from t1;
C1
------
00011
00012
00013
3 rows selected.
alter database recover automatic database until change 15666 allow <BLOCK수> corruption;alter database recover automatic database until time '2023/06/13 15:58:50' allow <BLOCK수
> corruption;alter database recover until cancel allow <BLOCK수> corruption;$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
$ tbsql sys/tibero
SQL> SELECT * FROM V$LOG;
SQL> select * from v$log;
THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ------ --------- -------- ------- -------- -------- -------------- ------------
0 0 1 52428800 2 YES INACTIVE 0
0 1 2 52428800 2 NO CURRENT 15543 2023/07/15
0 2 -1 52428800 2 NO UNUSED 0
3 rows selected.
SQL> CREATE TABLE T1 (C1 VARCHAR(5));
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00021');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00022');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00023');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00031');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00032');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00033');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00041');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00042');
1 row inserted.
SQL> INSERT INTO T1 (C1) VALUES ('00043');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> exit Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ rm /home/tibero/tbdata/*.dtf
$ tbboot
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
********************************************************
*Critical Warning : Raise svmode failed. The reason is
*TBR-1024 : Database needs media recovery:
open failed(/home/tibero/tbdata/system001.dtf).
*Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ tbdown
Tibero instance terminated (NORMAL mode).
$ cp /home/tibero/tbdata_bak/*.dtf /home/tibero/tbdata
$ tbboot mount
Listener port=8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> SELECT * FROM V$LOG;
SQL> select * from v$log;
THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ------ --------- -------- ------- -------- -------- -------------- ------------
0 0 4 52428800 2 YES INACTIVE 15652 2023/07/15
0 1 5 52428800 2 NO CURRENT 15667 2023/07/15
0 2 3 52428800 2 YES INACTIVE 15619 2023/07/15
3 rows selected.
--next_change# - 1 값 조회
SQL> set line 200
SQL> col name for a40
SQL> col SEQ# for 999
SQL> col TSN for 999999999
SQL> select name, sequence# seq#, next_change#-1 tsn
from v$archive_dest_files order by 2;
NAME SEQ# TSN
--------------------------------------- ---------- -------------
/home/tibero/tbarch/log-t0-r0-s1.arc 1 15542
/home/tibero/tbarch/log-t0-r0-s2.arc 2 15618
/home/tibero/tbarch/log-t0-r0-s3.arc 3 15651
/home/tibero/tbarch/log-t0-r0-s4.arc 4 15666
4 rows selected.
--TSN 정보가 없을 경우 Log 파일 덤프를 발생시킨다.
SQL> alter system dump logfile '/home/tibero/tbarch/log-t0-r0-s4.arc';
System altered.
SQL> exit
Disconnected.
$ cd $TB_HOME/instance/$TB_SID/dump/tracedump
$ ls -rlt
total 1
-rw-r--r-- 1 tibero dba 12420 Jun 15 22:37 tb_dump_13457_3_0.trc
--next_tsn 값을 10진수로 변경 : 0000.0000b227 (hex) => 15667 (dec)
--next_tsn - 1 = 15666
$ cat tb_dump_21951_18_2.trc | grep next
low=0000.00003d24 2023-06-14 09:21:26 next=0000.00003d33 2023-06-14 09:21:59
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter database recover automatic database until change 15666;
Database altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot -t resetlogs
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql tibero/tmax
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from t1;
C1
------
00021
00022
00023
00031
00032
00033
00011
00012
00013
9 rows selected.
$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> CREATE TABLE T1 (C1 VARCHAR(5));
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> select * from t1;
C1
-----
00011
1 row selected.
SQL> !date
Thu Jun 15 15:58:50 KST 2023
SQL> INSERT INTO T1 (C1) VALUES ( '00021');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> select * from t1;
C1
------
00011
00021
2 rows selected.
SQL> exit Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ rm /home/tibero/tbdata/*.dtf
$ tbboot
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
********************************************************
* Critical Warning : Raise svmode failed. The reason is
* TBR-1024 : Database needs media recovery:
open failed(/home/tibero/tbdata/system001.dtf).
* Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ cp /home/tibero/tbdata_bak/*.dtf /home/tibero/tbdata
$ tbboot mount
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ export TB_NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> alter database recover automatic database until time '2023/06/13 15:58:50';
Database altered.
SQL> exit Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot resetlogs
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql tibero/tmax
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from t1;
C1
------
00011
1 row selected.$ tbdown
$ cp –R /home/tibero/tbdata /home/tibero/tbdata_bak
$ tbboot
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> CREATE TABLE T1 (C1 VARCHAR(5)) TABLESPACE MY_FILE001;
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> select * from t1;
C1
00011
1 row selected.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> col name format a40;
SQL> select name, sequence# from v$archived_log order by sequence#;
NAME SEQUENCE#
------------------------------------ -----------
home/tibero/tbarch/log-t0-r0-s1.arc 1
1 row selected.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ rm /home/tibero/tbdata/*.dtf
$ tbboot
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
********************************************************
Critical Warning : Raise svmode failed. The reason is
TBR-1024 : Database needs media recovery:
open failed(/home/tibero/tbdata/system001.dtf).
Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ tbdown
Tibero instance terminated (NORMAL mode).
$ cp /home/tibero/tbdata_bak/*.dtf /home/tibero/tbdata
$ tbboot mount
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved..
Connected to Tibero.
SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/tbarch/log-t0-r0-s1.arc';
Database altered.
SQL> ALTER DATABASE RECOVER CANCEL;
Database altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot -t resetlogs
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved..
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero. SQL> select * from t1;
C1
------
00011
1 row selected.
alter database recover database until cancel;alter database recover logfile '<logfile name>';alter database recover continue;alter database recover cancel;$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from v$log;
THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ------ --------- -------- ------- -------- -------- -------------- ------------
0 0 7 52428800 1 NO CURRENT 39873 2023/07/15
0 1 5 52428800 1 YES INACTIVE 39862 2023/07/15
0 2 6 52428800 1 YES INACTIVE 39870 2023/07/15
1 3 7 52428800 1 NO CURRENT 39880 2023/07/15
1 4 5 52428800 1 YES INACTIVE 39875 2023/07/15
1 5 6 52428800 1 YES INACTIVE 39878 2023/07/15
6 rows selected.
SQL> CREATE TABLE T1 (C1 VARCHAR(5));
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode)
$ dd if=/dev/zero of=/dev/rdata4g081 bs=8192k
514+0 records in
512+0 records out
$ dd if=/dev/zero of=/dev/rdata4g082 bs=8192k
514+0 records in
512+0 records out
$ dd if=/dev/zero of=/dev/rdata4g083 bs=8192k
514+0 records in
512+0 records out
$ dd if=/dev/zero of=/dev/rdata4g084 bs=8192k
514+0 records in
512+0 records out
$ dd if=/dev/zero of=/dev/rdata4g085 bs=8192k
514+0 records in
512+0 records out
$ dd if=/dev/zero of=/dev/rdata4g086 bs=8192k
514+0 records in
512+0 records out
$ tbboot
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
********************************************************
Critical Warning : Raise svmode failed. The reason is
TBR-1024:Database needs media recovery:wrong database file(/dev/rdata4g081).
Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
$ dd if=/data1/tstac1/bak/system001.dtf of=/dev/rdata4g081 bs=8192k
512+0 records in
512+0 records out
$ dd if=/data1/tstac1/bak/usr001.dtf of=/dev/rdata4g082 bs=8192k
512+0 records in
512+0 records out
$ dd if=/data1/tstac1/bak/temp001.dtf of=/dev/rdata4g083 bs=8192k
512+0 records in
512+0 records out
$ dd if=/data1/tstac1/bak/undo001.dtf of=/dev/rdata4g084 bs=8192k
512+0 records in
512+0 records out
$ dd if=/data1/tstac1/bak/undo002.dtf of=/dev/rdata4g086 bs=8192k
512+0 records in
512+0 records out
$ dd if=/data1/tstac1/bak/apm_ts.dtf of=/dev/rdata4g085 bs=8192k
512+0 records in
512+0 records out
ftp> ls -rlt
200 PORT command successful.
150 Opening data connection for /bin/ls. total 416
-rw------- 1 tstac2 tsteam 211456 Jun 22 12:33 log-t1-r0-s7.arc
226 Transfer complete.
ftp> bi
200 Type set to I.
ftp> ha
Hash mark printing on (1024 bytes/hash mark).
ftp> mget *.arc
mget log-t1-r0-s7.arc? y
200 PORT command successful.
150 Opening data connection for log-t1-r0-s7.arc (211456 bytes).
################################################################
226 Transfer complete.
211456 bytes received in 0.003092 seconds (6.679e+04 Kbytes/s)
local: log-t1-r0-s7.arc remote: log-t1-r0-s7.arc
$ tbboot
change core dump dir to /home/tibero/tibero7/bin/prof listener port = 8629
********************************************************
*Critical Warning : Raise svmode failed. The reason is
*TBR-1024 : Database needs media recovery: file restored(/dev/rdata4g081).
*Current server mode is MOUNT.
********************************************************
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.
$ tbsql sys/tibero
tbSQL 7
Tibero Corporation Copyright © 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from v$log;
THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ------ --------- -------- ------- -------- -------- -------------- ------------
0 0 7 52428800 1 YES CURRENT 39873 2023/07/15
0 1 8 52428800 1 YES INACTIVE 41124 2023/07/15
0 2 9 52428800 1 NO INACTIVE 41133 2023/07/15
1 3 7 52428800 1 YES CURRENT 39980 2023/07/15
1 4 8 52428800 1 NO INACTIVE 41149 2023/07/15
1 5 6 52428800 1 UES INACTIVE 39878 2023/07/15
6 rows selected.
SQL> select * from v$archive_dest_files;
NAME THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
----------------------------------------- ------- --------- ------------- ---------- ------------ ----------------- ---------------
/home/tibero/tbdata/arch/log-t0-r0-s7.arc 0 7 39873 2023/07/15 41124 0
/home/tibero/tbdata/arch/log-t0-r0-s8.arc 0 8 41124 2023/07/15 41133 0
/home/tibero/tbdata/arch/log-t0-r0-s7.arc 1 7 39980 2023/07/15 41149 0
3 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS_TYPE MEMBER
------- ------------ ---------------------------
0 ONLINE /dev/rdata1g123
1 ONLINE /dev/rdata1g124
2 ONLINE /dev/rdata1g125
3 ONLINE /dev/rdata1g126
4 ONLINE /dev/rdata1g127
5 ONLINE /dev/rdata1g128
6 rows selected.
SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
Database altered.SQL> ALTER DATABASE RECOVER LOGFILE '/data1/tstac1/tbdata/arch/log-t0-r0-s7.arc';
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/data1/tstac1/tbdata/arch/log-t1-r0-s7.arc';
Database altered.
SQL> ALTER DATABASE RECOVER CONTINUE;
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/data1/tstac1/tbdata/arch/log-t0-r0-s8.arc'; D
atabase altered.
SQL> ALTER DATABASE RECOVER CONTINUE;
Database altered.
SQL> ALTER DATABASE RECOVER LOGFILE '/dev/rdata1g125';
Database altered.
SQL> ALTER DATABASE RECOVER CONTINUE;
Database altered.
SQL> ALTER DATABASE RECOVER CANCEL;
Database altered.SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot -t resetlogs
change core dump dir to /home/tibero/tibero7/bin/prof
listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from t1;
C1
-------
00011
00012
00013
3 rows selected.
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> set line 200
SQL> col group# for 999 SQL> col member for a50 SQL> col SEQ# for 999 SQL> col MB for 999
SQL> select a.group#, a.member, b.bytes/1024/1024 "MB", b.archived "ARC",
b.sequence# "SEQ#", b.status from v$logfile a, v$log b where a.group#=b.group# order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------ --------------- ---- ---- ----- ---------
0 /dev/rdata1g123 50 NO 1 CURRENT
1 /dev/rdata1g124 50 NO -1 UNUSED
2 /dev/rdata1g125 50 NO -1 UNUSED
3 /dev/rdata1g126 50 NO 1 CURRENT
4 /dev/rdata1g127 50 NO -1 UNUSED
5 /dev/rdata1g128 50 NO -1 UNUSED
6 rows selected.
SQL> alter database begin backup;
Database altered.
SQL> !dd if=/dev/rdata4g081 of=/data1/tstac1/bak/system001.dtf bs=8192k
512+0 records in
512+0 records out
SQL> !dd if=/dev/rdata4g082 of=/data1/tstac1/bak/usr001.dtf bs=8192k 512+0 records in
512+0 records out
SQL> !dd if=/dev/rdata4g084 of=/data1/tstac1/bak/undo001.dtf bs=8192k
512+0 records in
512+0 records out
SQL> !dd if=/dev/rdata4g086 of=/data1/tstac1/bak/undo002.dtf bs=8192k
512+0 records in
512+0 records out
SQL> !dd if=/dev/rdata4g085 of=/data1/tstac1/bak/apm_ts.dtf bs=8192k
512+0 records in
512+0 records out
SQL> alter database end backup;
Database altered.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS
'/home/tibero/bak/c1.sql' REUSE RESETLOGS;
Database altered.
SQL> CREATE TABLE T1 (C1 VARCHAR(5));
Table 'T1' created.
SQL> INSERT INTO T1 (C1) VALUES ('00011');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> INSERT INTO T1 (C1) VALUES ('00012');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> INSERT INTO T1 (C1) VALUES ('00013');
1 row inserted.
SQL> COMMIT;
Commit completed.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.CONTROL_FILES="/home/tibero/tbdata/tac1/c1.ctl"
DB_CREATE_FILE_DEST="/home/tibero/tbdata"
LOG_ARCHIVE_DEST="/home/tibero/tbdata/arch"#CLUSTER_DATABASE=Y #THREAD=0
#UNDO_TABLESPACE=UNDO0
#CM_CLUSTER_MODE=ACTIVE_SHARED
#CM_PORT=58631
#LOCAL_CLUSTER_ADDR=10.10.1.1
#LOCAL_CLUSTER_PORT=58632
#CM_FILE_NAME=/dev/rdata1g122--Online으로 백업받은 데이터 파일을 Single 서버로 복사
$ cp /data1/tstac1/bak/*.dtf /home/tibero/tbdata
--백업받은 컨트롤 파일을 Single 서버로 복사
$ cp /data1/tstac1/bak/c1.sql /home/tibero/tbdata
--NODE1, NODE2의 Archive 파일을 Single 서버로 복사
$ cp /data1/tstac1/tbdata/arch/*.arc /home/tibero/tbdata/arch
...(ftp 접속절차는 생략)
ftp> bi
200 Type set to I.
ftp> ha
Hash mark printing on (1024 bytes/hash mark).
ftp> prompt off
Interactive mode off.
ftp> mget *.arc
200 PORT command successful.
150 Opening data connection for log-t1-r0-s1.arc (77312 bytes).
############################################################################
226 Transfer complete.
77312 bytes received in 0.001275 seconds (5.922e+04 Kbytes/s)
local: log-t1-r0-s1.arc remote: log-t1-r0-s1.arc
200 PORT command successful.
150 Opening data connection for log-t1-r0-s2.arc (1024 bytes).
##
226 Transfer complete.
1024 bytes received in 5.8e-05 seconds (1.724e+04 Kbytes/s)
local: log-t1-r0-s2.arc remote: log-t1-r0-s2.arc
200 PORT command successful.
150 Opening data connection for log-t1-r0-s3.arc (512 bytes).
#
226 Transfer complete.
512 bytes received in 4.9e-05 seconds (1.02e+04 Kbytes/s)
local: log-t1-r0-s3.arc remote: log-t1-r0-s3.arc
200 PORT command successful.
150 Opening data connection for log-t1-r0-s4.arc (512 bytes).
#
226 Transfer complete.
512 bytes received in 5.2e-05 seconds (9615 Kbytes/s)
local: log-t1-r0-s4.arc remote: log-t1-r0-s4.arc
ftp> bye
221 Goodbye.
$ ls -rlt /home/tibero/tbdata/arch
total 43112
-rw------- 1 tibero dba 512 Jul 15 11:53 log-t0-r0-s5.arc
-rw------- 1 tibero dba 512 Jul 15 11:53 log-t0-r0-s4.arc
-rw------- 1 tibero dba 2560 Jul 15 11:53 log-t0-r0-s3.arc
-rw------- 1 tibero dba 1536 Jul 15 11:53 log-t0-r0-s2.arc
-rw------- 1 tibero dba 21959680 Jul 15 11:53 log-t0-r0-s1.arc
-rw-r--r-- 1 tibero dba 512 Jul 15 11:54 log-t1-r0-s4.arc
-rw-r--r-- 1 tibero dba 512 Jul 15 11:54 log-t1-r0-s3.arc
-rw-r--r-- 1 tibero dba 1024 Jul 15 11:54 log-t1-r0-s2.arc
-rw-r--r-- 1 tibero dba 77312 Jul 15 11:54 log-t1-r0-s1.arc
$ tbboot nomount
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NOMOUNT mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
--백업받은 컨트롤 파일 스크립트에서 Redo 파일, 데이터 파일 경로
--수정 후 컨트롤 파일 재생성
SQL> !vi /home/tibero/tbdata/c1.sql
CREATE CONTROLFILE REUSE DATABASE "tibero" LOGFILE
GROUP 0 '/home/tibero/tbdata/redo01.redo' SIZE 50M,
GROUP 1 '/home/tibero/tbdata/redo02.redo' SIZE 50M,
GROUP 2 '/home/tibero/tbdata/redo03.redo' SIZE 50M
RESETLOGS
DATAFILE
'/home/tibero/tbdata/system001.dtf',
'/home/tibero/tbdata/undo001.dtf',
'/home/tibero/tbdata/usr001.dtf',
'/home/tibero/tbdata/undo002.dtf',
'/home/tibero/tbdata/apm_ts.dtf'
ARCHIVELOG
MAXLOGFILES 100
MAXLOGMEMBERS 8
MAXDATAFILES 1024
CHARACTER SET MSWIN949
NATIONAL CHARACTER SET UTF16
;
--ALTER DATABASE MOUNT
---- Adding Logfiles is required in MOUNT mode.
--ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 '/dev/rdata1g126' SIZE 50M reuse;
--ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 '/dev/rdata1g127' SIZE 50M reuse;
--ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '/dev/rdata1g128' SIZE 50M reuse;
--ALTER DATABASE ENABLE PRIVATE THREAD 1;
---- Recovery is required in MOUNT mode.
--ALTER DATABASE RECOVER AUTOMATIC;
--ALTER DATABASE OPEN RESETLOGS;
---- Adding Tempfiles is required in OPEN mode.
-- ALTER TABLESPACE TEMP ADD TEMPFILE '/data1/tstac1/bak/temp001.dtf'
-- SIZE 512M REUSE AUTOEXTEND OFF ;
SQL> @/home/tibero/tbdata/c1.sql
Control File created.
File finished.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot mount
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
$ tbsql sys/tibero tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from v$archive_dest_files;
NAME THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TIME
----------------------------------------- ------- --------- ------------- ---------- ------------ ----------------- --------------
/home/tibero/tbdata/arch/log-t0-r0-s1.arc 0 1 0 2023/07/15 14135 0
/home/tibero/tbdata/arch/log-t0-r0-s2.arc 0 2 14135 2023/07/15 14140 0
/home/tibero/tbdata/arch/log-t0-r0-s3.arc 0 3 14140 2023/07/15 14604 0
/home/tibero/tbdata/arch/log-t0-r0-s4.arc 0 4 14604 2023/07/15 14606 0
/home/tibero/tbdata/arch/log-t0-r0-s5.arc 0 5 14606 2023/07/15 14609 0
/home/tibero/tbdata/arch/log-t1-r0-s1.arc 1 1 0 2023/07/15 14585 0
/home/tibero/tbdata/arch/log-t1-r0-s2.arc 1 2 14585 2023/07/15 14617 0
/home/tibero/tbdata/arch/log-t1-r0-s3.arc 1 3 14617 2023/07/15 14620 0
/home/tibero/tbdata/arch/log-t1-r0-s4.arc 1 4 14620 2023/07/15 14622 0
9 rows selected.
--복구할 때 사용 된 TSN 번호는 각 노드의 마지막 Archive Log 파일에서
--TSN - 1 값 중 작은 값(예 : min(NODE1, NODE2) = min(14609-1, 14622-1) = 14608)
SQL> alter database recover automatic database until change 14608;
Database altered.
SQL> exit
Disconnected.
$ tbdown
Tibero instance terminated (NORMAL mode).
$ tbboot -t resetlogs
listener port = 8629
change core dump dir to /home/tibero/tibero7/bin/prof Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).
$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/tibero/tbdata/temp001.dtf'
SIZE 512M REUSE AUTOEXTEND OFF ;
Tablespace 'TEMP' altered.
SQL> select * from t1;
C1
------
00011
00012
00013
3 rows selected.