Live migration TECH PREVIEW
This page describes the steps to perform and verify a successful live migration to YugabyteDB, including changes that continuously occur on the source.
Live migration workflow
The following workflows illustrate how you can perform data migration including changes happening on the source simultaneously. With the export data command, you can first export a snapshot and then start continuously capturing changes occurring on the source to an event queue on the disk. Using the import data command, you similarly import the snapshot first, and then continuously apply the exported change events on the target.
Eventually, the migration process reaches a steady state where you can cut over to a database. You can stop your applications from pointing to your source database, let all the remaining changes be applied on the target YugabyteDB database, and then restart your applications pointing to YugabyteDB.
The following illustration describes how the data export and import operations are simultaneously handled by YugabyteDB Voyager.
The following illustration shows the steps in a live migration using YugabyteDB Voyager.
Step | Description |
---|---|
Install yb-voyager | yb-voyager supports RHEL, CentOS, Ubuntu, and macOS, as well as airgapped and Docker-based installations. |
Prepare source | Create a new database user with READ access to all the resources to be migrated. |
Prepare target | Deploy a YugabyteDB database and create a user with superuser privileges. |
Export schema | Convert the database schema to PostgreSQL format using the yb-voyager export schema command. |
Analyze schema | Generate a Schema Analysis Report using the yb-voyager analyze-schema command. The report suggests changes to the PostgreSQL schema to make it appropriate for YugabyteDB. |
Modify schema | Using the report recommendations, manually change the exported schema. |
Start | Start the phases: export data first, followed by import data and archive changes simultaneously. |
Export data | The export data command first exports a snapshot and then starts continuously capturing changes from the source. |
Import data | The import data command first imports the snapshot, and then continuously applies the exported change events on the target. |
Archive changes | Continuously archive migration changes to limit disk utilization. |
Initiate cutover | Perform a cutover (stop streaming changes) when the migration process reaches a steady state where you can stop your applications from pointing to your source database, allow all the remaining changes to be applied on the target YugabyteDB database, and then restart your applications pointing to YugabyteDB. |
Wait for cutover to complete | Monitor the wait status using the cutover status command. |
Import indexes and triggers | Import indexes and triggers to the target YugabyteDB database using the yb-voyager import schema command with an additional --post-import-data flag. |
Verify migration | Check if the live migration is successful. |
Before proceeding with migration, ensure that you have completed the following steps:
- Install yb-voyager.
- Check the unsupported features and known issues.
- Review data modeling strategies.
- Prepare the source database.
- Prepare the target database.
Prepare the source database
Prepare your source database by creating a new database user, and provide it with READ access to all the resources which need to be migrated.
-
Ensure that your database log_mode is
archivelog
as follows:SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG If not enabled, sqlplus /nolog SQL>alter system set db_recovery_file_dest_size = 10G; SQL>alter system set db_recovery_file_dest = '<oracle_path>/oradata/recovery_area' scope=spfile; SQL> connect / as sysdba SQL> Shutdown immediate SQL> Startup mount SQL> Alter database archivelog; SQL> Alter database open;
-
Create the tablespaces as follows:
-
Connect to Pluggable database (PDB) as sysdba and run the following command:
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
-
Connect to Container database (CDB) as sysdba and run the following command:
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
-
-
Run the following commands from CDB as sysdba:
CREATE USER c##ybvoyager IDENTIFIED BY password DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL; GRANT CREATE SESSION TO c##ybvoyager CONTAINER=ALL; GRANT SET CONTAINER TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$DATABASE to c##ybvoyager CONTAINER=ALL; GRANT FLASHBACK ANY TABLE TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ANY TABLE TO c##ybvoyager CONTAINER=ALL; GRANT SELECT_CATALOG_ROLE TO c##ybvoyager CONTAINER=ALL; GRANT EXECUTE_CATALOG_ROLE TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ANY TRANSACTION TO c##ybvoyager CONTAINER=ALL; GRANT LOGMINING TO c##ybvoyager CONTAINER=ALL; GRANT CREATE TABLE TO c##ybvoyager CONTAINER=ALL; GRANT LOCK ANY TABLE TO c##ybvoyager CONTAINER=ALL; GRANT CREATE SEQUENCE TO c##ybvoyager CONTAINER=ALL; GRANT EXECUTE ON DBMS_LOGMNR TO c##ybvoyager CONTAINER=ALL; GRANT EXECUTE ON DBMS_LOGMNR_D TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$LOG TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$LOG_HISTORY TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$LOGMNR_LOGS TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$LOGFILE TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$ARCHIVED_LOG TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$TRANSACTION TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$MYSTAT TO c##ybvoyager CONTAINER=ALL; GRANT SELECT ON V_$STATNAME TO c##ybvoyager CONTAINER=ALL;
-
Enable supplemental logging in the database as follows:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-
Ensure that your database log_mode is
archivelog
as follows:SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);
-
Connect to your database as an admin user, and create the tablespaces as follows:
CREATE TABLESPACE logminer_tbs DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
-
Run the following commands connected to the admin or privileged user:
CREATE USER ybvoyager IDENTIFIED BY password DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs; GRANT CREATE SESSION TO YBVOYAGER; begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$DATABASE', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / GRANT FLASHBACK ANY TABLE TO YBVOYAGER; GRANT SELECT ANY TABLE TO YBVOYAGER; GRANT SELECT_CATALOG_ROLE TO YBVOYAGER; GRANT EXECUTE_CATALOG_ROLE TO YBVOYAGER; GRANT SELECT ANY TRANSACTION TO YBVOYAGER; GRANT LOGMINING TO YBVOYAGER; GRANT CREATE TABLE TO YBVOYAGER; GRANT LOCK ANY TABLE TO YBVOYAGER; GRANT CREATE SEQUENCE TO YBVOYAGER; begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'DBMS_LOGMNR', p_grantee => 'YBVOYAGER', p_privilege => 'EXECUTE', p_grant_option => true); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'DBMS_LOGMNR_D', p_grantee => 'YBVOYAGER', p_privilege => 'EXECUTE', p_grant_option => true); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$LOG', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$LOG_HISTORY', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$LOGMNR_LOGS', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$LOGMNR_CONTENTS', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$LOGMNR_PARAMETERS', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$LOGFILE', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$ARCHIVED_LOG', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$ARCHIVE_DEST_STATUS', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$TRANSACTION', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$MYSTAT', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; / begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'V_$STATNAME', p_grantee => 'YBVOYAGER', p_privilege => 'SELECT'); end; /
-
Enable supplemental logging in the database as follows:
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); begin rdsadmin.rdsadmin_util.alter_supplemental_logging( p_action => 'ADD', p_type => 'PRIMARY KEY'); end; /
If you want yb-voyager to connect to the source database over SSL, refer to SSL Connectivity.
Connecting to Oracle instances
You can use only one of the following arguments to connect to your Oracle instance.
Prepare the target database
Prepare your target YugabyteDB database cluster by creating a database, and a user for your cluster.
Create the target database
Create the target database in your YugabyteDB cluster. The database name can be the same or different from the source database name.
If you don't provide the target database name during import, yb-voyager assumes the target database name is yugabyte
. To specify the target database name during import, use the --target-db-name
argument with the yb-voyager import
commands.
CREATE DATABASE target_db_name;
Create a user
Create a user with SUPERUSER
role.
-
For a local YugabyteDB cluster or YugabyteDB Anywhere, create a user and role with the superuser privileges using the following command:
CREATE USER ybvoyager SUPERUSER PASSWORD 'password';
-
For YugabyteDB Managed, create a user with
yb_superuser
role using the following command:CREATE USER ybvoyager PASSWORD 'password'; GRANT yb_superuser TO ybvoyager;
If you want yb-voyager to connect to the target database over SSL, refer to SSL Connectivity.
Deleting the ybvoyager user
After migration, all the migrated objects (tables, views, and so on) are owned by the ybvoyager
user. You should transfer the ownership of the objects to some other user (for example, yugabyte
) and then delete the ybvoyager
user. Example steps to delete the user are:
REASSIGN OWNED BY ybvoyager TO yugabyte;
DROP OWNED BY ybvoyager;
DROP USER ybvoyager;
Create an export directory
yb-voyager keeps all of its migration state, including exported schema and data, in a local directory called the export directory.
Before starting migration, you should create the export directory on a file system that has enough space to keep the entire source database. Next, you should provide the path of the export directory as a mandatory argument (--export-dir
) to each invocation of the yb-voyager command in an environment variable.
mkdir $HOME/export-dir
export EXPORT_DIR=$HOME/export-dir
The export directory has the following sub-directories and files:
reports
directory contains the generated Schema Analysis Report.schema
directory contains the source database schema translated to PostgreSQL. The schema is partitioned into smaller files by the schema object type such as tables, views, and so on.data
directory contains CSV (Comma Separated Values) files that are passed to the COPY command on the target database.metainfo
andtemp
directories are used by yb-voyager for internal bookkeeping.logs
directory contains the log files for each command.
Migrate your database to YugabyteDB
Proceed with schema and data migration using the following steps:
Export and analyze schema
To begin, export the schema from the source database. Once exported, analyze the schema and apply any necessary manual changes.
Export schema
The yb-voyager export schema
command extracts the schema from the source database, converts it into PostgreSQL format (if the source database is Oracle or MySQL), and dumps the SQL DDL files in the EXPORT_DIR/schema/*
directories.
Usage for source_db_schema
The source_db_schema
argument specifies the schema of the source database.
- For Oracle,
source-db-schema
can take only one schema name and you can migrate only one schema at a time.
An example invocation of the command is as follows:
# Replace the argument values with those applicable for your migration.
yb-voyager export schema --export-dir <EXPORT_DIR> \
--source-db-type <SOURCE_DB_TYPE> \
--source-db-host <SOURCE_DB_HOST> \
--source-db-user <SOURCE_DB_USER> \
--source-db-password <SOURCE_DB_PASSWORD> \ # Enclose the password in single quotes if it contains special characters.
--source-db-name <SOURCE_DB_NAME> \
--source-db-schema <SOURCE_DB_SCHEMA>
Refer to export schema for details about the arguments.
Analyze schema
The schema exported in the previous step may not yet be suitable for importing into YugabyteDB. Even though YugabyteDB is PostgreSQL compatible, given its distributed nature, you may need to make minor manual changes to the schema.
The yb-voyager analyze-schema
command analyses the PostgreSQL schema dumped in the export schema step, and prepares a report that lists the DDL statements which need manual changes. An example invocation of the command is as follows:
# Replace the argument values with those applicable for your migration.
yb-voyager analyze-schema --export-dir <EXPORT_DIR> --output-format <FORMAT>
The preceding command generates a report file under the EXPORT_DIR/reports/
directory.
Refer to analyze schema for details about the arguments.
Manually edit the schema
Fix all the issues listed in the generated schema analysis report by manually editing the SQL DDL files from the EXPORT_DIR/schema/*
.
After making the manual changes, re-run the yb-voyager analyze-schema
command. This generates a fresh report using your changes. Repeat these steps until the generated report contains no issues.
To learn more about modelling strategies using YugabyteDB, refer to Data modeling.
Manual schema changes
-
CREATE INDEX CONCURRENTLY
is not currently supported in YugabyteDB. You should remove theCONCURRENTLY
clause before trying to import the schema. -
Include the primary key definition in the
CREATE TABLE
statement. Primary Key cannot be added to a partitioned table using theALTER TABLE
statement.
Import schema
Import the schema using the yb-voyager import schema
command.
Usage for target_db_schema
yb-voyager
imports the source database into the public
schema of the target database. By specifying --target-db-schema
argument during import, you can instruct yb-voyager
to create a non-public schema and use it for the schema/data import.
An example invocation of the command is as follows:
# Replace the argument values with those applicable for your migration.
yb-voyager import schema --export-dir <EXPORT_DIR> \
--target-db-host <TARGET_DB_HOST> \
--target-db-user <TARGET_DB_USER> \
--target-db-password <TARGET_DB_PASSWORD> \ # Enclose the password in single quotes if it contains special characters..
--target-db-name <TARGET_DB_NAME> \
--target-db-schema <TARGET_DB_SCHEMA>
Refer to import schema for details about the arguments.
yb-voyager applies the DDL SQL files located in the $EXPORT_DIR/schema
directory to the target database. If yb-voyager terminates before it imports the entire schema, you can rerun it by adding the --ignore-exist
option.
Importing indexes and triggers
Because the presence of indexes and triggers can slow down the rate at which data is imported, by defaultimport schema
does not import indexes and triggers. You should complete the data import without creating indexes and triggers. Only after data import is complete, create indexes and triggers using the import schema
command with an additional --post-import-data
flag.
Export data
Begin exporting data from the source database into the EXPORT_DIR/data
directory using the yb-voyager export data command as follows:
# Replace the argument values with those applicable for your migration.
yb-voyager export data --export-dir <EXPORT_DIR> \
--source-db-type <SOURCE_DB_TYPE> \
--source-db-host <SOURCE_DB_HOST> \
--source-db-user <SOURCE_DB_USER> \
--source-db-password <SOURCE_DB_PASSWORD> \ # Enclose the password in single quotes if it contains special characters.
--source-db-name <SOURCE_DB_NAME> \
--source-db-schema <SOURCE_DB_SCHEMA> \
--export-type snapshot-and-changes
The export data command first ensures that it exports a snapshot of the data already present on the source database. Next, you start a streaming phase (CDC phase) where you begin capturing new changes made to the data on the source after the migration has started. Some important metrics such as the number of events, export rate, and so on, is displayed during the CDC phase similar to the following:
| --------------------------------------- | ----------------------------- |
| Metric | Value |
| --------------------------------------- | ----------------------------- |
| Total Exported Events | 123456 |
| Total Exported Events (Current Run) | 123456 |
| Export Rate(Last 3 min) | 22133/sec |
| Export Rate(Last 10 min) | 21011/sec |
| --------------------------------------- | ----------------------------- |
Note that the CDC phase will start only after a snapshot of the entire table-set is completed. Additionally, the CDC phase is restartable. So, if yb-voyager terminates when data export is in progress, it resumes from its current state after the CDC phase is restarted.
Caveats
- Some data types are unsupported. For a detailed list, refer to datatype mappings.
- For Oracle where sequences are not attached to a column, resume value generation is unsupported.
- --parallel-jobs argument has no effect on live migration.
Refer to export data for details about the arguments, and export data status to track the status of an export operation.
The options passed to the command are similar to the yb-voyager export schema
command. To export only a subset of the tables, pass a comma-separated list of table names in the --table-list
argument.
Import data
After you have successfully imported the schema in the target database, you can start importing the data using the yb-voyager import data command as follows:
# Replace the argument values with those applicable for your migration.
yb-voyager import data --export-dir <EXPORT_DIR> \
--target-db-host <TARGET_DB_HOST> \
--target-db-user <TARGET_DB_USER> \
--target-db-password <TARGET_DB_PASSWORD> \ # Enclose the password in single quotes if it contains special characters.
--target-db-name <TARGET_DB_NAME> \
--target-db-schema <TARGET_DB_SCHEMA> \ # Oracle only.
--parallel-jobs <NUMBER_OF_JOBS>
Refer to import data for details about the arguments.
For the snapshot exported, yb-voyager splits the data dump files (from the $EXPORT_DIR/data directory) into smaller batches. yb-voyager concurrently ingests the batches such that all nodes of the target YugabyteDB database cluster are used. After the snapshot is imported, a similar approach is employed for the CDC phase, where concurrent batches of change events are applied on the target YugabyteDB database cluster.
Some important metrics such as the number of events, ingestion rate, and so on, is displayed during the CDC phase similar to the following:
| ----------------------------- | ----------------------------- |
| Metric | Value |
| ----------------------------- | ----------------------------- |
| Total Imported events | 272572 |
| Events Imported in this Run | 272572 |
| Ingestion Rate (last 3 mins) | 14542 events/sec |
| Ingestion Rate (last 10 mins) | 14542 events/sec |
| Time taken in this Run | 0.83 mins |
| Remaining Events | 4727427 |
| Estimated Time to catch up | 5m42s |
| ----------------------------- | ----------------------------- |
The entire import process is designed to be restartable if yb-voyager terminates when the data import is in progress. If restarted, the data import resumes from its current state.
Importing large datasets
When importing a very large database, run the import data command in a screen
session, so that the import is not terminated when the terminal session stops.
If the yb-voyager import data
command terminates before completing the data ingestion, you can re-run it with the same arguments and the command will resume the data import operation.
Import data status
Run the yb-voyager import data status --export-dir <EXPORT_DIR>
command to get an overall progress of the data import operation.
Archive changes
As the migration continuously exports changes on the source database to the EXPORT-DIR
, the disk utilization continues to grow indefinitely over time. To limit usage of all the disk space, you can use the archive changes
command as follows:
yb-voyager archive changes --export-dir <EXPORT-DIR> --move-to <DESTINATION-DIR> --delete
Refer to archive changes for details about the arguments.
Cut over to the target
Cutover is the last phase, where you switch your application over from the source database to the target YugabyteDB database.
Keep monitoring the metrics displayed for export data and import data processes. After you notice that the import of events is catching up to the exported events, you are ready to perform a cutover. You can use the "Remaining events" metric displayed in the import data process to help you determine the cutover.
Perform the following steps as part of the cutover process:
-
Quiesce your source database, that is stop application writes.
-
Perform a cutover after the exported events rate ("ingestion rate" in the metrics table) drops to 0 using the following command:
yb-voyager cutover initiate --export-dir <EXPORT_DIR>
Refer to cutover initiate for details about the arguments.
The cutover initiate command stops the export data process, followed by the import data process after it has imported all the events to the target YugabyteDB database.
-
Wait for the cutover process to complete. Monitor the status of the cutover process using the following command:
yb-voyager cutover status --export-dir <EXPORT_DIR>
Refer to cutover status for details about the arguments.
Import indexes and triggers
Import indexes and triggers using the import schema
command with an additional --post-import-data
flag as follows:
# Replace the argument values with those applicable for your migration.
yb-voyager import schema --export-dir <EXPORT_DIR> \
--target-db-host <TARGET_DB_HOST> \
--target-db-user <TARGET_DB_USER> \
--target-db-password <TARGET_DB_PASSWORD> \ # Enclose the password in single quotes if it contains special characters.
--target-db-name <TARGET_DB_NAME> \
--target-db-user <TARGET_DB_USER> \
--target-db-schema <TARGET_DB_SCHEMA> \
--post-import-data
Refer to import schema for details about the arguments.
Verify migration
After the schema and data import is complete, the automated part of the database migration process is considered complete. You should manually run validation queries on both the source and target database to ensure that the data is correctly migrated. A sample query to validate the databases can include checking the row count of each table.
Caveat associated with rows reported by import data status
Suppose you have a scenario where,
- import data or import data file command fails.
- To resolve this issue, you delete some of the rows from the split files.
- After retrying, the import data command completes successfully.
In this scenario, import data status command reports an incorrect number of imported rows, because it doesn't take into account the deleted rows.
For more details, refer to GitHub issue #360.
After migration verification, stop archiving changes.
Limitations
- Schema changes on the source Oracle database will not be recognized during the live migration.
- Tables without primary key are not supported.
- Truncating a table on the source database is not taken into account; you need to manually truncate tables on your YugabyteDB cluster.
- Some Oracle datatypes are unsupported - NCHAR, NVARCHAR, VARRAY, BLOB, CLOB, and NCLOB.
- Case-sensitive table names or column names are partially supported. yb-voyager converts them to case-insensitive names. For example, an "Accounts" table in a source Oracle database is migrated as
accounts
(case-insensitive) to a YugabyteDB database. - Reserved keywords such as table name, or column names are unsupported.