Background Image

DOCUMENTATION

?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print

Author: Seojin Heo

 

In some cases, DB data path may need to be rerouted during operations, and CUBRID supports rerouting DB data path through DB backup/recovery.

 

However, LOB data is not stored in the DB, but is stored in a file format in an external file system, and only the location information of the file is stored in the DB. Therefore, separate management of LOB data files is required when DB backup/restore.

 

In this post, I will guide you to change the path of LOB data in addition to the DB data path change. The example is created based on CUBRID version 9.3.

 

1. Test Information: 

  • DB Name: testdb
  • Existing Path: /home/cubrid/CUBRID/databases/testdb
  • Change Path: /disk/testdb

2. Path Change Procedure: 

1) Service Termination 


$ cubrid service stop

@ cubrid server stop: testdb

Server testdb notified of shutdown. This may take several minutes.

Please wait. 

++ cubrid server stop: success 

@ cubrid broker stop 

++ cubrid broker stop: success 

@ cubrid manager server stop 

++ cubrid manager server stop: success 

@ cubrid master stop 

++ cubrid master stop: success


 

2) DB backup (refer to manual for backup option)


$ cubrid backupdb -D /disk/BACKUP/ -o backup.log -z --no-check testdb -S

Backup Volume Label: Level: 0, Unit: 0, Database testdb, Backup Time: Thu Jan 16 15:18:31 2020


 

3) Modify the $CUBRID/databases/databases.txt file

  • Modify the vol-path (data volume path), log-path (log volume path), and lob-base-path (lob data storage path) as the path to be changed.

$ vi $CUBRID/databases/databases.txt

 #db-name        vol-path                db-host         log-path                lob-base-path

 testdb          /disk/testdb    localhost       /disk/testdb    file:/disk/testdb/lob


 

4) DB Recovery (Refer to manual for recovery option)

  • Create the path in advance before recovery
  • Create LOB directory in advance
  • The u option is required (u option: the option to read the databases.txt file and recover to the corresponding path)

$ mkdir /disk/testdb

$ mkdir /disk/testdb/lob

$ cubrid restoredb -B /disk/BACKUP/ -u -o restore.txt testdb

CUBRID 9.3


 

5) Check the Data Path 

  • Check the DB data file and LOB path change

$ cubrid spacedb -S testdb Space

description for database 'testdb' with pagesize 16.0K. (log pagesize: 16.0K)

 

Volid  Purpose    total_size   free_size  Vol Name

 

    0   GENERIC      512.0 M     509.6 M  /disk/testdb/testdb

 Space description for temporary volumes for database 'testdb' with pagesize 16.0K.

 

Volid  Purpose    total_size   free_size  Vol Name

 

LOB space description file:/disk/testdb/lob


 

6) Start DB 


$ cubrid server start testdb 

@ cubrid master start 

++ cubrid master start: success 

@ cubrid server start: testdb

 

This may take a long time depending on the amount of recovery works to do.

 

CUBRID 9.3

 

++ cubrid server start: success


 

7) Check the exisitng LOB data path stored in the DB


$ csql -u dba testdb@localhost

        CUBRID SQL Interpreter

Type `;help' for help messages.

 

csql> select * from test_tbl;

 

=== <Result of SELECT Command in Line 1> ===

            i  j

 =================================== 

1  file:/home/cubrid/CUBRID/databases/testdb/lob/ces_658/test_tbl.00001579155238194895_7622            

2  file:/home/cubrid/CUBRID/databases/testdb/lob/ces_043/test_tbl.00001579155241496030_9788            

3  file:/home/cubrid/CUBRID/databases/testdb/lob/ces_051/test_tbl.00001579155244055456_9598

 

3 rows selected. (0.284038 sec) Committed.

 

1 command(s) successfully processed


 

8) Change LOB Data Path

  • LOB data files stored in the existing LOB path must exist
  • Use clob_to_char function for CLOB and blob_to_bit function for BLOB

csql> update test_tbl set j=clob_to_char(j); 

3 rows affected. (0.195812 sec) Committed.

 

csql> update test_tbl2 set j=blob_to_bit(j); 

3 rows affected. (0.008269 sec) Committed.


 

9) Confirm LOB Data Path Change 


csql> select * from test_tbl;

 

=== <Result of SELECT Command in Line 1> ===

 

            i  j 

===================================            

1  file:/disk/testdb/lob/ces_401/test_tbl.00001579156454712630_8701            

2  file:/disk/testdb/lob/ces_520/test_tbl.00001579156454713722_8117            

3  file:/disk/testdb/lob/ces_541/test_tbl.00001579156454714721_4275

 

3 rows selected. (0.007592 sec) Committed.

 

1 command(s) successfully processed.


 

10) Confirm that LOB data was actually generated in the changed path


$ ls -l /disk/testdb/lob/* 

/disk/testdb/lob/ces_002: total 4 

-rw-------. 1 cubrid cubrid 3 Jan 16 15:34 abc.00001579156443619562_9498

 

/disk/testdb/lob/ces_092: total 4 

-rw-------. 1 cubrid cubrid 6 Jan 16 15:35 test_tbl2.00001579156554249465_0008

 

/disk/testdb/lob/ces_310: total 4 

-rw-------. 1 cubrid cubrid 6 Jan 16 15:35 test_tbl2.00001579156554247635_2273

 

/disk/testdb/lob/ces_401: total 4 

-rw-------. 1 cubrid cubrid 4 Jan 16 15:34 test_tbl.00001579156454712630_8701

 

/disk/testdb/lob/ces_520: total 4 

-rw-------. 1 cubrid cubrid 4 Jan 16 15:34 test_tbl.00001579156454713722_8117

 

/disk/testdb/lob/ces_541: total 4 

-rw-------. 1 cubrid cubrid 4 Jan 16 15:34 test_tbl.00001579156454714721_4275

 

/disk/testdb/lob/ces_633: total 4 

-rw-------. 1 cubrid cubrid 6 Jan 16 15:35 test_tbl2.00001579156554248844_8435


 


List of Articles
No. Category Subject Date
37 Tools eXERD Which Deals with Logical/Physical Models, Supports CUBRID file 2020.07.23
36 Tools Let’s Take a Look at SQLGate for CUBRID! file 2020.07.23
35 Tools Utilizing the CUBRID Migration Tool (CMT) file 2020.07.23
34 Tools CUBRID Manager Import Wizard Useful Tips! file 2020.07.23
33 Tools Running CUBRID Container Services in Docker, Kubernetes Environment file 2020.07.23
32 Server How to Start CUBRID Automatically upon LINUX Boot 2020.07.17
31 Server What's New in CUBRID 10: 'String Compression' file 2020.07.17
30 Server CUBRID GRANT ALL TABLES file 2020.07.17
29 Server Using Two-way Encryption Functions with Java SP in CUBRID 2020.07.17
» Server Changing the LOB Data Path 2020.07.17
Board Pagination Prev 1 2 3 4 Next
/ 4

Join the CUBRID Project on