Tibero quick guide
- Print
- PDF
Tibero quick guide
- Print
- PDF
Article Summary
Share feedback
Thanks for sharing your feedback!
Available in Classic and VPC
Introduction to the Tibero quick guide
Tibero quick guide is based on the FAQs of Tibero users.
The following explains about migration, listener, and database, as well as how to create, change, and delete DB users.
Migration
For a migration guide, such as for Tibero version upgrades, see the Tibero 6 Online Manual{target=_blank}.
Perform migration through 2.4.6. tbExport, 2.4.7. tbImport in the text, and for inquiries, register on TechNet{target=_blank}.
Listener
The default listener port for TIBERO is 8629.
How to change the listener port
Change
You can change the listener port setting for Tibero by editing the parameters in the tip file.
The tip file permanently and statically reflects the changes.
(1) su - tibero
(2) tbcfgv or vi $TB_HOME/config/$TB_SID.tip
Enter LISTENER_PORT=[port number to change] and save
(3) tbcliv or vi $TB_HOME/client/config/tbdsn.tbr
Change the port number
tibero=(
(INSTANCE=(HOST=localhost)
(PORT=[port number to change])
(DB_NAME=tibero)
)
)
(4) Restart Tibero - changes will not apply without a restart
Check
Use tbboot to start and check the port number.
Assigning additional dynamic listener ports
The following is how to add listener ports during operation.
You must log in as a user with system permissions.
System permissions can be found in the Tibero online manual > Tibero Administrator Guide > 2.Basics of Management > 2.1. User Created > 2.1.2. SYS.
Use the ALTER SYSTEM LISTENER ADD | DELETE PORT syntax to dynamically add ports.
(1) su - tibero
(2) tbsql sys/tibero
(3) SQL> ALTERT SYSTEM LISTENER ADD PORT 9629;
(4) SQL> q
(5) Use netstat -nap | grep tblistener to check the listener port
- For dynamically added listener ports, you must add listener information to the tbdsn.tbr file according to the How to change the listener port to enable access through tbsql. (tbsql is a utility for accessing Tibero from the shell. )
- To access from outside through an application, you must set access through ACG rule settings of NCP, not tbdsn.tbr settings.
Setting up access through a new port with tbsql
Add a listener to connect through the added port number via Bash Shell.
(6) tbcliv or vi $TB_HOME/client/config/tbdsn.tbr
tibero_2=(
(INSTANCE=(HOST=localhost)
(PORT=9629)
(DB_NAME=tibero)
)
)
// tibero_2 is an alias, so you can change it to the name you want
(7) tbsql sys/tibero@tibero_2
Removing the assignment of dynamic listener ports
The following is how to remove the assignment of a listener port during operation.
You must log in as a user with system permissions.
Use the ALTER SYSTEM LISTENER ADD | DELETE PORT syntax to dynamically add ports.
(1) su - tibero
(2) tbsql sys/tibero
(3) SQL> ALTERT SYSTEM LISTENER DELETE PORT 9629;
(4) SQL> q
(5) Use netstat -nap | grep tblistener to check the listener port
How to perform access control
Access can primarily be blocked through ACG rule settings of NCP, but Tibero also has a feature to set access for specific IPs.
You can set IP access restrictions through the following parameter settings in the tip file.
LSNR_INVITED_IP
LSNR_DENIED_IP
LSNR_INVITED_IP_FILE
LSNR_DENIED_IP_FILE
(1) su - tibero
(2) tbdown
(3) tbcfgv or vi $TB_HOME/config/$TB_SID.tip
LSNR_DENIED_IP="[IP to restrict access]"
(4) tbboot
- To limit a range with CIDR or specific IPs only
LSNR_DENIED_IP="192.168.20.0/24"orLSNR_DENIED_IP="192.168.20.190" - To apply multiple
LSNR_DENIED_IP="192.168.20.100; 192.168.20.101; 192.168.0.0/24"
If you want to use the file option, create a file as follows and proceed with the parameter setting in the tip file.
After performing the first two steps
(3) vi "/db/tibero/denied_ip.list"
#WAS
192.168.22.20
192.168.44.10
### END OF FILE ###
(4) tbcfgv or vi $TB_HOME/config/$TB_SID.tip
LSNR_DENIED_IP_FILE=" /db/tibero/denied_ip.list"
(5) tbboot
- If both INVITED and DENIED are set, only INVITED is applied, and DENIED is ignored.
A specific user can access the meta data of database management and statistics, etc. to acquire database information. To block access, you can revoke system privileges or object permissions through the Revoke command.
According to the operating environment,
see Tibero online manual > SQL Reference Guide > 7. Data Definition Language > 7.75. REVOKE.
Setting up dynamic IP blocking
(1) su - tibero
(2) tbcfgv or vi $TB_HOME/config/$TB_SID.tip
LSNR_DENIED_IP="[IP to restrict access]"
(3) tbsql sys/tibero
(4) SQL> ALTER SYSTEM LISTENER PARAMETER RELOAD;
(1) su - tibero
(2) tbsql sys/tibero
(3) SQL> ALTER SYSTEM SET LSNR_DENIED_IP="[IP to restrict access]";
Check blocked IP
SQL> show param LSNR_DENIED_IP
NAME TYPE VALUE
---------------------------- -------- ----------------------------------------
LSNR_DENIED_IP STRING 192.111.111.111
LSNR_DENIED_IP_FILE STRING
Database
How to add an instance
The multi-instance feature is not recommended if you do not have a deep understanding of the DB.
For various settings, see the following Tibero online manual.
Tibero online manual > Tibero Installation Guide > 6. Multi-instance installation > 6.3. Unix environment
To use multi-instance, you must create an additional database.
TB_SID is required to control multi-instance. You must clearly understand the TB_SID of the existing DB and the TB_SID to be added.
The current TB_SID is applied in ~/.bash_profile. See the file to proceed.
(1) su - tibero
(2) export TB_SID=[new TB_SID]
> export TB_SID=tibero2
(3) $TB_HOME/config/gentip.sh
(4) tbcfgv or vi $TB_HOME/config/$TB_SID.tip
> DB_NAME=tibero2
> LISTENER_PORT=9629
(5) tbcliv or vi $TB_HOME/client/config/tbdsn.tbr
Add listener
> tibero2=(
> (INSTANCE=(HOST=localhost)
> (PORT=9000)
> (DB_NAME=tibero)
> )
> )
(6) tbboot nomount
(7) tbsql sys/tibero
(8) SQL> Database Create query
[ Tibero Online Manual ] - [ Tibero Installation Guide ] - [ 3.3.Manual Installation ] - [ 3.3.2. Unix Environment ] - [ 7 ]
(9) tbboot
> Listener port = 9629
> Tibero 6
> TmaxData Corporation Copyright (c) 2008-. All rights reserved.
> Tibero instance started up (NORMAL mode).
(10) $TB_HOME/scripts/system.sh -p1 tibero -p1 syscat -a1 y -a2 y -a3 y -a4 y
> …
> Done.
Checking multi-instance installation
ps -ef | grep tbsvr
You can check that instances with different SIDs are running as processes.
How to perform backup
Control file backup
Physical backup of the control file
backup the control file itself.
SQL> alter database backup controlfile to '/tbdata/backup/ctrlfile1.ctl';
Altered.
Logical backup of the control file
backup the query for regenerating the control file.
SQL> alter database backup controlfile to trace as '/tbdata/backup/ctrlfile1.sql' reuse
NORESETLOGS;
Altered.
Examples of creation statements for the backed-up control files
CREATE CONTROLFILE REUSE DATABASE "tibero"
LOGFILE
GROUP 1 (
'/tbdata/tibero/system01/redo_01a.redo',
'/tbdata/tibero/system02/redo_01b.redo'
) SIZE 100M,
GROUP 2 (
'/tbdata/tibero/system01/redo_02a.redo',
'/tbdata/tibero/system02/redo_02b.redo'
) SIZE 100M,
GROUP 3 (
'/tbdata/tibero/system01/redo_03a.redo',
'/tbdata/tibero/system02/redo_03b.redo'
) SIZE 100M,
GROUP 4 (
'/tbdata/tibero/system01/redo_04a.redo',
'/tbdata/tibero/system02/redo_04b.redo'
) SIZE 100M,
GROUP 5 (
'/tbdata/tibero/system01/redo_05a.redo',
'/tbdata/tibero/system02/redo_05b.redo'
) SIZE 100M
NORESETLOGS
DATAFILE
'/tbdata/tibero/system01/system001.dbf',
'/tbdata/tibero/system01/undo001.dbf',
'/tbdata/tibero/system01/usr001.dbf',
'/tbdata/tibero/system01/tpr.dbf'
ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 500
MAXARCHIVELOG 500
MAXBACKUPSET 500
MAXLOGHISTORY 500
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF16
;
---- 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 '/tbdata/tibero/system01/temp001.dbf'
-- SIZE 20000M REUSE AUTOEXTEND OFF ;
--ALTER DATABASE ADD BACKUPSET 1
--FLAGS 33
--STARTED '202105292120'/112810
--FINISHED '202105292123'/112893
--SIZE 4885789 BCT_REQ_TSN 0
--BASE_SET 0 FIRST_PIECE 0
--TSID_BM ' 11011'
--BACKUP_PATH '/backup/'
--RESETLOGS 0
Cold Backup(Offline Backup)
Perform after stopping the DB.
Only recovery to the point when the datafile was backed up is possible.
(1) SQL> SELECT NAME FROM V$DATAFILE;
(2) tbdown
(3) Use the OS command Copy to copy the Datafile viewed in
tbcfgv or vi $TB_HOME/config/$TB_SID.tip.
Also copy the hidden .passwd file located in DB_CREATE_FILE_DEST="[path]."
HOT Backup(Online Backup)
It is possible only in Archive Log mode.
It can be performed while running.
It is possible before or after the backup point of the Datafile.
View DATAFILE connected to TABLESPACE
SQL> select name,type from v$tablespace;
SQL> select f.name
from v$tablespace t join v$datafile f on t.ts# = f.ts#
where t.name = 'USER';
Run BEGIN and END BACKUP before and after starting HOT BACKUP.
SQL> ALTER TABLESPACE [TABLESPACE NAME] BEGIN BACKUP
SQL> !cp [DATAFILE path] [backup path]
SQL> ALTER TABLESPACE [TABLESPACE NAME] END BACKUP
For more details, see Tibero online manual > Tibero Administrator Guide > 6. Backup and Recovery.
Backup using tbrmgr
tbrmgr is a backup/recovery manager provided by Tibero. It provides Online Full Backup, Incremental Backup, Automatic Recovery, Tablespace unit backup and recovery, Delete Backup Set features.
For options related to tbrmgr, see Tibero online manual > Tibero Administrator Guide > 6. Backup and Recovery > 6.5 Recovery Manager > 6.5.3 Recovery Manager Options.
Example of tbrmgr backup - Online Full Backup
The default root Disk size is not sufficient at Default 50 GB, so backup may not proceed properly.
Sufficient storage space must be mounted for backup according to the size of the used datafile.
(1) su - tibero
(2) tbrmgr backup -o /home/tbrdb/work/6/backup/
(3) $ tbsql sys/tibero
(4) SQL> select * from V$BACKUP_SET a;
(5) SQL> select * from V$BACKUP_ARCHIVED_LOG;
or
(1) su - tibero
(2) tbrmgr backup --with-archivelog -o /home/tbrdb/work/6/backup/
(3) $ tbsql sys/tibero
(4) SQL> select * from V$BACKUP_SET a;
(5) SQL> select * from V$BACKUP_ARCHIVED_LOG;
The backup is considered complete when this message is displayed.
Recovery
In terms of recovery operations performed according to boot modes, Tibero can be described as follows.
NOMOUNT Mode
In case of failure in the control file, operation in NOMOUNT mode allows for the creation of a control file.
MOUNT Mode
MOUNT mode checks the state between the data file, online log file, and control file. If all are up to date, it can operate in NORMAL mode.
If a physical failure occurs in the file or if it is a restored file, media recovery is required and it operates in MOUNT mode. In MOUNT mode, you can view a limited view and perform media recovery.
Control file recovery
To recover the control file, you must first have backed it up.
See Logical backup of the control file.
(1) tbboot nomount
(2) tbsql sys/tibero
(3) SQL> @/tibero6/backup/ctrlfile1.sql
(4) SQL> exit
(5) tbdown
(6) tbboot mount
(7) tbsql sys/tibero
(8) SQL> ALTER DATABASE RECOVER AUTOMATIC DATABASE;
(9) SQL> exit
(10) tbboot
-- Use the comment script part in the backup control file script to create a temp file
(11) SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/tibero/tbdata/temp001.dtf'
2 SIZE 512M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE 10G;
(12) SQL> exit
Crash Recovery
It means that it is automatically recovered without the command of the user when the database is abnormally terminated.
When recovery is complete, Tibero operates normally.
Media Recovery
The media recovery process does not occur automatically. A series of tasks are required for the DBA to understand the situation and instruct the necessary process. It is performed manually by the command of the DBA and is divided into Complete Recovery and Incomplete Recovery.
It refers to media recovery that incorporates all logs up to the most recent log in the online log file. It recovers in mount mode.
SQL> ALTER DATABASE RECOVER AUTOMATIC DATABASE;
- Time Base Recovery, Change Base Recovery, Cansel Base Recovery
It refers to recovering up to a specific point in time before the most recent online log file, not up to the most recent. After incomplete recovery, you must start Tibero in RESETLOGS mode. RESETLOGS initializes the online log file and is used when you do not start the database with the current online log file.
- If you have performed incomplete recovery or created a control file with RESETLOGS, start with RESETLOGS.
Starting with RESETLOGS is like creating a new database. Files before and after RESETLOGS, including data files and log files, are not compatible. You cannot recover to after RESETLOGS using backup files or log files before RESETLOGS. In addition, performing incomplete recovery to the state before RESETLOGS with files after RESETLOGS is impossible. Therefore, we recommend performing a new backup when starting in RESETLOGS mode.
tbboot -t resetlogs
For more information, see Tibero Administrator Guide on Technet.
Download > Tibero > Tibero 6 Fixset07 > Manual Download > Tibero Administrator Guide
tbrmgr - recovery using online full backup
(1) su - tibero
(2) tbsql sys/tibero
(3) SQL> select * from V$BACKUP_SET a;
(4) SQL> exit
(5) tbrmgr recover -o /backup/
or
(1) su - tibero
(2) tbsql sys/tibero
(3) SQL> select * from V$BACKUP_SET a;
(4) SQL> exit
(5) tbrmgr recover --with-archivelog -o /backup/
For scenarios like tablespace-based recovery, see Tibero online manual > Tibero Administrator Guide > 6. Backup and Recovery > 6.4 Recovery Manager.
How to change the archive log storage location
Change storage location
You can change the listener port setting for Tibero by editing the parameters in the tip file.
Stop Tibero, change the parameter settings, and then restart it.
(1) su - tibero
(2) tbdown
(3) tbcfgv or vi $TB_HOME/config/$TB_SID.tip
LOG_ARCHIVE_DEST="[path to change]" //enter the absolute path.
(4) tbboot
Dynamic storage location change
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST="[path to change]";
The dynamically changed path will revert to $TB_HOME/config/$TB_SID.tip path upon restarting Tibero.
Checking the changed archive path
Check through the database parameters
SQL> SHOW PARAM
…
LOG_ARCHIVE_DEST DIRNAME [changed path]
…
Check the actual ARCHIVE file drop through LOG SWITCH
SQL> AL.TER SYSTEM SWITCH LOGFILE;
SQL> !ls -alrt [changed path]
How to create, change, and delete DB users
To create, change, or delete users, you must connect to Tibero as a user with DBA privileges.
The SYS account of Tibero is the default account that is granted the DBA role upon installation.
Creating a user
(1) su - tibero
(2) tbsql sys/tibero
(3) SQL> CREATE USER Steve ①
IDENTIFIED BY dsjeo134 ②
DEFAULT TABLESPACE USR; ③
- Create a user named Steve
- Set a password
- Specify the tablespace used by the user: the tablespace should be configured and specified according to the environment.
- If you do not specify a tablespace, the system tablespace is used by default.
Granting privileges after creation
SQL> GRANT CONNECT, RESOURCE TO Steve;
or
SQL> GRANT DBA TO Steve;
For more information about privileges, see Tibero online manual > Tibero Administrator Guide > User Management and Database Security > 5.2 Privileges and grant according to the environment.
Changing a user
SQL> ALTER USER Steve
IDENTIFIED BY abcdef
DEFAULT TABLESPACE SYSTEM;
You can change the password and tablespace through ALTER USER.
Viewing a user
SQL> SELECT * FROM V$ALL_USERS;
| Static view | Description |
|---|---|
| ALL_USERS | View that queries basic information of all users in the database |
| DBA_USERS | View that queries detailed information of all users in the database |
| USER_USERS | View that queries information of the current user |
Removing a user
SQL> DROP USER Steve CASCADE;
Schema object privileges
Users must be granted privileges to access specific schema objects in the database.
Schema object privileges control access to schema objects such as tables, views, sequences, and synonyms.
Schema object privileges can be granted to other users using the GRANT command, and the content is recorded in the data dictionary.
| Schema object privileges | Description |
|---|---|
| SELECT | Privilege to view a table |
| INSERT | Privilege to insert rows into a table |
| UPDATE | Privilege to update rows in a table |
| DELETE | Privilege to delete rows from a table |
| ALTER | Privilege to change the characteristics of a schema object |
| INDEX | Privilege to create an index on a table |
| REFERENCES | Privilege to create a constraint that references a table |
| TRUNCATE | Privilege to perform TRUNCATE on a table To use this privilege, you must set the USE_TRUNCATE_PRIVILEGE parameter to "Y" |
Relevant resources
For more information on the software used in NAVER Cloud Platform Tibero, see the following web pages.
Was this article helpful?