Overview
This document describes the standard procedure for safely migrating database objects and data in an Altibase environment.
It covers procedures commonly required in practice, such as version upgrades, server migration, and logical backup and recovery, focusing on the use of the Altibase tools aexport and iloader.
Key Concepts
Data migration refers to the overall process of safely transferring database objects and data to a different environment.
Use Cases
- Altibase server version upgrade
- Reconfiguration of the operating environment or migration of server/platform
- Logical backup and recovery
- Partitioning or relocation of large tables
Main Tools
- aexport: Extracts database object creation DDL and generates data extraction/loading scripts
- iloader: Performs data extraction and loading
Full Migration Process
Altibase data migration involves extracting object definitions and data from the source Altibase and applying them to the target Altibase. The general procedure is as follows.
| Order | Step | Description |
|---|---|---|
| 1 | Preliminary Check | Stop service, check disk space, create working directory, verify source data count |
| 2 | Run aexport | Generate object definitions (DDL) and data extraction/loading scripts |
| 3 | Data Extraction | Create text files (.fmt, .dat) using run_il_out.sh |
| 4 | Configure Target Altibase | Install target Altibase and create objects using run_is.sh |
| 5 | Data Loading | Run run_il_in.sh (apply performance options if necessary) |
| 6 | Verification and Follow-up | Create subsequent objects such as indexes, foreign keys, triggers, and replication; check data count, object status, and service |
Detailed Step-by-Step Process
1. Preliminary Check
1.1 Stop Service
Migration is performed with the service completely stopped. No DML or DDL is allowed during the process.
1.2 Prepare Working Directory and Disk Space
Since text data can be larger than the internal storage size, ensure at least twice the data size in free space.
If backing up the source Altibase, prepare additional space accordingly.
1.3 Record Source Data Counts
Record the data counts for each table in advance for verification after migration.
If you run the following SQL, you can automatically generate SQL to query the count for each user table.
1.4 Install the DBMS_METADATA package
This operation is performed only on Altibase 7.3 or higher.
The DBMS_METADATA package extracts object creation DDL statements and GRANT statements from the database dictionary and is supported starting from Altibase 7.3.
2. Run aexport
2.1 Set the ALTIBASE_NLS_USE environment variable.
Match the Altibase client character set (environment variable ALTIBASE_NLS_USE) to the Altibase server character set.
Altibase 서버 캐릭터셋 확인
서버 캐릭터셋이 UTF8 일 때:
Altibase 클라이언트 캐릭터셋 설정
2.2 Modify aexport.properties.
To prevent errors caused by delimiter conflicts, remove the comments from ILOADER_FIELD_TERM and ILOADER_ROW_TERM to change their default values.
2.3 Run aexport
Execute aexport in the working directory. During the process, enter the server IP, user account, and password to scan objects and generate related scripts.
Example of execution:
2.4 Check the results
When aexport execution is complete, an SQL file for creating database objects, a script to execute the SQL, and scripts for data extraction and loading are generated.
3. Data extraction
3.1 Run run_il_out.sh
Execute the run_il_out.sh script to extract data.
If the processing time is long, run it in the background to maintain the session. Rename and manage the nohup.out file.
3.2 Verification
Check for errors
Search for error messages in the execution log.
Check the number of tables
Verify that the number of .fmt files and .dat files matches the actual number of tables.
Check for record extraction errors
Verify that the Error Row Count value in each log file is 0. If any value is not zero, review the error details and re-extract the data.
4. Target Altibase Configuration
Install the target Altibase and run the object definition scripts extracted by aexport to set up the database.
4.1 Check Source Altibase Information
To make the target environment the same as the source, query the following items.
4.2 Stop and Backup Source Altibase
This procedure is performed when the source and target Altibase are located on the same server.
When migrating on the same server, first stop the source Altibase, then back up the Altibase home directory, disk data files, memory checkpoint image files, log anchor files, and transaction log files. The example below assumes that all files exist in the Altibase home directory.
4.3 Install Target Altibase
Install the target Altibase. For the required input information during installation, refer to the previously gathered '4.1 Check Source Altibase Information.'
4.4 Verify Data File Paths
If the data path of the target Altibase differs from the source, modify the paths of the disk data files and memory checkpoint image files in ALL_CRT_TBS.sql.
4.5 Change Altibase connection address in scripts
In the scripts generated by aexport, change the Altibase connection address to the target Altibase's IP.
Example of change: when the target Altibase's IP is 192.168.1.145
4.6 Create database objects
Run the run_is.sh file to create tablespaces, database users, synonyms, directories, sequences, tables, libraries, views, stored procedures, and database links.
Check for any errors that occurred during the database object creation process.
5. Data Loading
5.1 Data Loading
Run the run_il_in.sh file to load the data.
If there is a large amount of data or the process takes a long time, run it in the background to prevent the session from disconnecting.
To improve data loading speed, add the performance options -array and -commit to the iloader command inside run_il_in.sh when executing.
5.2 Verification
Check for errors
Search for error messages in the execution log.
Check the number of tables
The number of .log and .bad files should match the number of tables.
Record error verification
Check the log files for any failed records during loading. The Error Row Count should be 0 in all logs.
Verify .bad files
Check the log files for any failed records during loading. The Error Row Count should be 0 in all logs.
Find .bad files with size greater than 0:
If there are tables with an Error Row Count or .bad file size greater than 0, check the records and error messages in the username_tablename.log file, address the issues, and then re-extract the data.
Example of execution: Since an error was found in SYS_CUSTOMERS.log, review the SYS_CUSTOMERS.log file.
The .bad files contain the records that failed to load.
6. Verification and Follow-up Actions
After data loading and object creation are complete, the database administrator and business personnel compare the data between the source Altibase and the target Altibase, and verify that key objects are functioning correctly. They also run applications and batch jobs to ensure that services operate normally.
Reference Documents
For detailed features and advanced usage of aexport and iloader, refer to the following manuals.