Overview

When planning data migration where the SOURCE and TARGET use the same binary for migration, this document describes how to perform data migration by utilizing the database recovery method through HOT BACKUP as detailed herein.

Method

Preparation for Migration

Check Information Between SOURCE and TARGET Nodes

First, you should verify whether the same binary is compatible between SOURCE and TARGET.

• Tibero OS Supported Platform Manual
• Tibero Required Packages and Kernel Parameter Manual

Also, if there is a significant kernel difference between SOURCE and TARGET, binary compatibility may not be ensured, so caution is required.

• SOURCE: 2.x kernel compiled version / TARGET: 3.x or 4.x kernel compiled version

• Binaries are not compatible between 2.x kernel and 3.x or 4.x kernels.

Additionally, check whether configuration changes are needed due to environmental differences.

• If the path on the TARGET node changes compared to SOURCE, you need to modify the path when creating the CONTROLFILE.

• If the SID on the TARGET node changes compared to SOURCE, related parameter files must be modified.

Migration Scenario

This is a scenario to minimize downtime during database migration.

After the database BEGIN, data files are migrated to the TARGET node, and incremental data is applied in phases.

By stopping the active database service and applying only some ARCHIVE files, database migration can be completed within tens of minutes.

Performing Migration

SOURCE - CF BACKUP

Backup the CONTROLFILE before performing HOT BACKUP.

The CONTROLFILE must be backed up as resetlogs type. If the CONTROLFILE is not resetlogs type when created on TARGET, it will look for REDO files, so it must be applied as resetlogs type.

Also, after the application is stopped, if you perform REDO log switches and archive all transaction logs, you can perform recovery equivalent to complete recovery.

SQL> alter database backup controlfile to trace as 'controlfile_backup.sql' resetlogs;

SOURCE - BEGIN BACKUP

Check archive logs created before BEGIN BACKUP.

SQL> select * from v$archived_log order by sequence#;

Perform BEGIN BACKUP.

SQL> alter database begin backup;

Check BACKUP status.

ARCHIVE LOGs are needed from the minimum CHANGE# TSN in the v$backup view.

-- Check minimum TSN value
SQL> select min(change#) from v$backup;
SQL> select * from v$backup;

SOURCE - FILE COPY

Copy all data files in BEGIN state to the TARGET server.

After copying is complete, perform END BACKUP.

Also, the .passwd file in the DB_CREATE_FILE_DEST path must be copied together.

TARGET - Setting Configuration Files

Set kernel settings, user, and profile.

• Operating System Parameter Manual

Install and configure the Tibero engine.

TARGET - CF Creation

Start the database in NOMOUNT mode to create the CONTROLFILE.

$ tbboot nomount

Create the CONTROLFILE.

The CONTROLFILE must be created as RESETLOGS type.

If the data file path in the TARGET environment differs from SOURCE, modify the path in the CONTROLFILE accordingly.

SQL> @controlfile_backup.sql

SOURCE - END BACKUP

Perform END BACKUP.

SQL> alter database end backup;

Check BACKUP status.

ARCHIVE LOGs are needed from the minimum CHANGE# TSN in the v$backup view.

-- Check minimum TSN value
SQL> select min(change#) from v$backup;
SQL> select * from v$backup;

SOURCE - ARCHIVE SWITCH

Switch REDO containing the DDL of BEGIN BACKUP and END BACKUP to ARCHIVE LOG.

-- Perform as many times as redo groups +1
SQL> alter systsem switch logfile;

TARGET - Prepare Recovery Mode

Stop the database with tbdown to start in MOUNT mode.

$ tbdown

Start the database in MOUNT mode for recovery.

$ tbboot mount

SOURCE - Copy ARCHIVE Files

Secure ARCHIVE files.

• ARCHIVE files corresponding to CHANGE# minimum TSN or higher confirmed from v$backup view are required.

• Also, all ARCHIVE files generated by SWITCH LOGFILE after END BACKUP are needed.

SQL> select * from v$archived_log order by sequence#;

TARGET - Apply 1st Phase ARCHIVE Increment

Recover up to the ARCHIVE files switched as many times as REDO groups after BEGIN ~ END.

Set the recovery TSN based on the NEXT_CHANGE# - 1 value of the last ARCHIVE file required for the 1st phase application.

SQL> alter database recover automatic database until change 688490;
TBR-1070: Incomplete media recovery is finished, but resetlogs is required.  

SOURCE - Copy ARCHIVE Files

Secure ARCHIVE files.

All ARCHIVE files after the last ARCHIVE file of the 1st phase ARCHIVE increment are required.

Copy the secured ARCHIVE files to the TARGET’s LOG_ARCHIVE_DEST path.

SQL> select * from v$archived_log order by sequence# where NEXT_CHANGE# >= 688490;

TARGET - Apply 2nd Phase ARCHIVE Increment

Apply ARCHIVE files after the 1st phase ARCHIVE increment.

Set the recovery TSN based on the NEXT_CHANGE# - 1 value of the last ARCHIVE file required for the 2nd phase application.

SQL> alter database recover automatic database until change 737666;
TBR-1070: Incomplete media recovery is finished, but resetlogs is required.  

SOURCE - Stop Application

SQL> select * form v$session order by sid;

SOURCE - ARCHIVE SWITCH

-- Perform as many times as redo groups +1
SQL> alter systsem switch logfile;

SOURCE - Copy ARCHIVE Files

Secure the final ARCHIVE files.

All ARCHIVE files after the last ARCHIVE file of the 2nd phase ARCHIVE increment are required.

Copy the secured ARCHIVE files to the TARGET’s LOG_ARCHIVE_DEST path.

SQL> select * from v$archived_log order by sequence# where NEXT_CHANGE# >= 737666;

SOURCE - Stop DB

Stop the database to prepare for switching to TARGET.

Also, prevent applications from connecting to SOURCE after switching to TARGET.

$ tbdown immediate

Check if the database has shut down properly.

$ ps -ef |grep -E "tbsvr|tblistener"

TARGET - Apply 3rd Phase ARCHIVE Increment

Apply ARCHIVE files after the 2nd phase ARCHIVE increment.

Set the recovery TSN based on the last ARCHIVE file’s NEXT_CHANGE# - 1 value required for the 3rd phase application.

SQL> alter database recover automatic database until change 784423;
TBR-1070: Incomplete media recovery is finished, but resetlogs is required.  

TARGET - Perform RESETLOGS Startup

After completing the 3rd phase ARCHIVE increment application, shut down the database.

$ tbdown immediate

Start with resetlogs.

$ tbboot resetlogs

TARGET - Create TEMPFILE

Execute the TEMPFILE creation statement commented out in the CONTROLFILE.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'path'  SIZE 32G;

TARGET - Change to NORMAL

Stop the database for NORMAL startup.

$ tbdown immediate

Start the database.

$ tbboot

TARGET - Start Application

The database switch to TARGET is complete.

Start the application and check sessions connected to the database.

SQL> select * form v$session order by sid;