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


 


  1. eXERD Which Deals with Logical/Physical Models, Supports CUBRID

  2. Let’s Take a Look at SQLGate for CUBRID!

  3. Utilizing the CUBRID Migration Tool (CMT)

  4. CUBRID Manager Import Wizard Useful Tips!

  5. Running CUBRID Container Services in Docker, Kubernetes Environment

  6. No Image 17Jul
    by
    2020/07/17 in Server

    How to Start CUBRID Automatically upon LINUX Boot

  7. What's New in CUBRID 10: 'String Compression'

  8. No Image 17Jul
    by
    2020/07/17 in Server

    CUBRID GRANT ALL TABLES

  9. No Image 17Jul
    by
    2020/07/17 in Server

    Using Two-way Encryption Functions with Java SP in CUBRID

  10. No Image 17Jul
    by charischau
    2020/07/17 in Server

    Changing the LOB Data Path

Board Pagination Prev 1 2 3 4 Next
/ 4

Join the CUBRID Project on