Migrate Databases Using the Migration Tools

12 Migrate Databases Using the Migration Tools

Use the Oracle Cloud Infrastructure Classic Discovery and Translation Tool to migrate Oracle Database Classic Cloud Service instances to Oracle Cloud Infrastructure Database systems. You can migrate one or more database instances at a time.

This tool creates a Recovery Manager (RMAN) backup of your Oracle Database Classic Cloud Service instance. RMAN backups are stored in the associated Oracle Cloud Infrastructure Object Storage Classic container. The migration tool automatically transfers the backup to Oracle Cloud Infrastructure as a standalone backup. You can restore this standalone backup to a new Oracle Cloud Infrastructure Database system. Oracle Cloud Infrastructure offers multiple options for restoring a standalone backup.

This solution is best suited for migrating database instances in your development and test environments. The database is restored only till the point when the backup was taken. Any subsequent transactions which are not part of backup are not available when the database is restored.

Workflow

Here's an overview of the high-level steps required to migrate your database instances from Oracle Database Classic Cloud Service to Oracle Cloud Infrastructure. You can use Oracle Cloud Infrastructure Classic Discovery and Translation Tool to automate this process.

Create an instance in Oracle Cloud Infrastructure Compute Classic using the Oracle Cloud Infrastructure Classic Migration Tools image.
Identify the database instances that you want to migrate.
Run the opcmigrate migrate database migrate command to initiate the migration process.
This command creates a Recovery Manager (RMAN) backup of your Oracle Database Classic Cloud Service instance and transfers the backup to Oracle Cloud Infrastructure as a standalone backup.
Run the opcmigrate migrate database list command to view the status of the migration process.
Restore the standalone backup of the source database. Oracle Cloud Infrastructure offers multiple options for restoring the backup.

Complete the Prerequisites

Before you begin, complete the following prerequisites.

To use the Oracle Cloud Infrastructure Classic Database Backup Migration Tool to migrate your database instances, you must create an instance in your Oracle Cloud Infrastructure Compute Classic account using the Oracle Cloud Infrastructure Classic Migration Tools image. Ensure that you have sufficient quota for this instance to be created. For information about creating your Control-S instance, see Complete the Prerequisites and Launch the Migration Controller Instance (Control-S) in the Source Environment. If you've already created this instance earlier in your migration process, you can use the same instance for this procedure.
The opcmigrate migrate database migrate command of Oracle Cloud Infrastructure Classic Database Backup Migration Tool creates an RMAN backup of the database instance in Oracle Cloud Infrastructure. RMAN backup is temporarily stored in the associated Oracle Cloud Infrastructure Object Storage Classic container before the tool uploads it to Oracle Cloud Infrastructure. Ensure that you have sufficient quota and required storage space to save the backup in your source environment and in Oracle Cloud Infrastructure.
Ensure that you have a virtual cloud network (VCN) with subnets set up in a compartment that you have access to.
Ensure that the API access PEM key does not have a pass phrase. The Oracle Cloud Infrastructure Classic Database Backup Migration Tool requires that you have set up an API user in your Oracle Cloud Infrastructure environment.
Ensure that the oracle user of the source database system has the SYSDBA role and can perform password-less sudo.

Plan for the Migration

To use Oracle Cloud Infrastructure Classic Database Backup Migration Tool to migrate your database instances, you must create and configure an instance in your Oracle Cloud Infrastructure Compute Classic account using the Oracle Cloud Infrastructure Classic Migration Tools image.

Create or update the .opc/profiles/default file on Control-S. The Oracle Cloud Infrastructure Classic Database Backup Migration Tool used to migrate database instances requires access to the Oracle Database Classic Cloud Service account.

Note:
If you run the Control-S setup command for Oracle Cloud Infrastructure Classic VM and Block Storage Migration Tool, it generates the default profile using information provided by you in the secret.yml file. The Control-S setup command overwrites any existing default profile. Verify and update the existing profile, or create a new profile, if required.
1. You’ll need the SID, IP address, edition, and name of the Oracle Database Classic Cloud Service instance that you want to migrate. Look this up in your Oracle Cloud Dashboard. The edition value can be SE for Standard Edition, EE for Enterprise Edition, EE_HP for Enterprise Edition High Performance, and EE_EP for Enterprise Edition Extreme Performance.
2. You'll also need the private SSH key file corresponding to the public SSH key that is associated with your database instance. Ensure the SSH key pair was generated password-less, and is sized -b 4096 or smaller. Use the following command to base64-encode the private SSH key.
```
base64 -i --wrap=0 /home/opc/.ssh/name_of_private_key_file
```
3. Create or locate your profile file. This file contains the credentials and other information required to access your source environment. The default location for this file is ~/.opc/profiles/default. If required, you can create multiple profiles and use the --profile option to specify the profile you want to use when you run the tool. If you create profiles in a location other than ~/.opc/profiles, provide the full path to the profile location by using the --profile-directory option, along with the --profile option to specify the profile file name. If no profile is specified, the ~/.opc/profiles/default profile is used.
4. Use the following template to add the "database" section to your profile file. You can specify details of one or more database instances that you want to migrate. Replace the sample values with values specific to your account.
```
"database": {
  "orcl-db1": {
    "credentials": "LS0tLS1...WS0tLS0tCg==",
    "source_ip": "129...",
    "edition": "SE",
    "sid": "prodenv"
  },
  "orcl-db2": {
    "credentials": "LS0tLS1...WS0tLS0tCg==",
    "source_ip": "129...",
    "edition": "EE",
    "sid": "ORCL"
  }
}
```
  Where,
  
  orcl-db1 and orcl-db2 are the names of the Oracle Database Classic Cloud Service instances that you want to migrate from your source environment.
  
  credentials is the base64-encoded format of the private SSH key file that corresponds to the public SSH key that is associated with the Oracle Database Classic Cloud Service instance that you want to migrate.
  
  source_ip is the IP address of the Oracle Database Classic Cloud Service instance. This IP address is used to SSH to the instance.
  
  edition is the Oracle Database Classic Cloud Service instance software edition that you want to migrate from your source environment.
  
  sid is the SID associated with the source Oracle Database Classic Cloud Service instance.
Get details of your target environment. Log in to the Oracle Cloud Infrastructure Console and collect the required information. You'll need the following:
- The user OCID. From the menu, choose Identity and then Users.
- The API PEM key fingerprint. Click the user to view user details. The API Keys section displays the PEM key fingerprint.
- The compartment OCID. From the menu, choose Identity and then Compartments. The standalone backup is created in this compartment.
- The tenancy OCID and the tenancy region. From the menu, choose Administration and then choose Tenancy Details.
- The Availability Domain. From the menu, choose Networking and then Virtual Cloud Networks. Click the VCN that you've created for this migration. The Subnets section displays the availability domain. The standalone backup is created in this availability domain.
Create or edit the /home/opc/.oci/config file on Control-S. This file contains the credentials and other information required to access your target environment. If you've used Oracle Cloud Infrastructure Classic VM and Block Storage Migration Tool earlier in the migration process, this would already have been created. However, the file might not contain all of the information required by the Oracle Cloud Infrastructure Classic Database Backup Migration Tool. For example, you might have to add information about the availability domain.
If you run the Control-S setup command for Oracle Cloud Infrastructure Classic VM and Block Storage Migration Tool, it generates the .oci/config file, using information provided by you in the secret.yml file. The Control-S setup command overwrites any existing .oci/config file.
Use the following template to create or update the .oci/config file. Replace the sample values with values specific to your tenancy.
```
[DEFAULT]
user=ocid1.user.oc1..aaaaa...
fingerprint=81:45:aa:2...
key_file=<path to api pem key>
compartment=ocid1.compartment.oc1...
tenancy=ocid1.tenancy.oc1..aaaaaaa...
region=us-ashburn-1
ad=ilMx:US-ASHBURN-AD-1
```

Migrate Database Instances

Use the Oracle Cloud Infrastructure Classic Database Backup Migration Tool to migrate database instance to Oracle Cloud Infrastructure. You can run this tool on an instance in Oracle Cloud Infrastructure Compute Classic that was created using the Oracle Cloud Infrastructure Classic Migration Tools image.

Note:

If you download and install Oracle Cloud Infrastructure Classic Discovery and Translation Tool on your local system, you won't be able to use the opcmigrate migrate database commands, as these commands aren't included in that distribution of the tool. To use the opcmigrate migrate database commands, you must create an instance using the Oracle Cloud Infrastructure Classic Migration Tools image.

To create an RMAN back up of the source database instance and to save it as a standalone backup in Oracle Cloud Infrastructure, run the following command:
```
opcmigrate migrate database migrate
```
Sample output:
```
opcmigrate-database: INFO: Launching migration for database: orcl-db
```
Where, orcl-db is the name of the source database system.

To view the status of a migration job, run the following command:

opcmigrate migrate database list

Sample output:

opcmigrate migrate database list
opcmigrate-database: INFO: Get list of migrations in progress
opcmigrate-database: INFO: SID     Source IP       Status                           Backup Name      Backup OCID
opcmigrate-database: INFO: ORCL1   129.150.8...    SUCCESS                          Backup_ORCL_1  ocid1.dbsystem.oc1.iad.re7...
opcmigrate-database: INFO: ORCL2   129.150.1...    CONNECT_FAILURE_FOR_SOURCE_HOST

Note down the backup OCID as you'll have to provide this information later.

In the response, look at the value returned for the Status field. Its value changes to Success when the back up to Oracle Cloud Infrastructure is complete.

The database backup is created and then it is copied to Oracle Cloud Infrastructure. This standalone backup is created in the compartment and the availability domain that you have specified in the .oci/config file. Note down the name of the backup the backup OCID.

After the database backup is available in Oracle Cloud Infrastructure, create a new Oracle Cloud Infrastructure Database instance from the standalone backup.

Restore a Database Instance

Create a new Oracle Cloud Infrastructure Database instance from the standalone backup in Oracle Cloud Infrastructure. Select a method to restore the database and create a new database instance on Oracle Cloud Infrastructure.

Restore a Database Using the Console or the API

Using the Oracle Cloud Infrastructure web console. See Recovering a Database from Object Storage in Oracle Cloud Infrastructure documentation.
Using the Oracle Cloud Infrastructure API. See Using the API in Oracle Cloud Infrastructure documentation.

Restore a Database Using Terraform

Before you begin, you must install and configure Terraform. Instructions to set up, configure, and run Terraform are outside the scope of this document.

To restore Oracle Cloud Infrastructure Database instances from the standalone backup using the terraform file generated by the Oracle Cloud Infrastructure Classic Discovery and Translation Tool:

Update the ~/.opc/profiles/default file on Control-S. You'll need to provide your user name, the identity domain and region in which the Oracle Database Classic Cloud Service instances that you want to migrate has been provisioned, and the REST endpoint to access the PaaS account. Look this up in your Oracle Cloud Dashboard as follows:
1. Sign in to the Oracle Cloud My Services application. The My Services dashboard is displayed.
2. Look for Compute Classic on the Dashboard, and then select View Details from the Actions menu.
3. In the Overview tab, under Additional Information, note down the Identity Service Id. For example, idcs-9cd522ebea844aa1ab64c6773da59a54.
4. Click Open Service Console. The Compute Classic console is displayed.
5. Click Site near the top of the page, and then note down the value selected in the Site drop-down list. This is the region in which your database instance has been provisioned.
6. Identify the endpoint for your PaaS account based on the region or value that you have noted down from the Site drop-down list.
  - If your Oracle cloud account is provisioned in the uscom region, use psm.us.oraclecloud.com.
  - If your Oracle cloud account is provisioned in the aucom region, use psm.aucom.oraclecloud.com.
  - Otherwise, use psm.europe.oraclecloud.com.
7. Locate your profile file, which contains the information required to access your source environment. The default location for this file is ~/.opc/profiles/default.
8. Use the following template to add the "paas" section to your profile file. Replace the sample values with values specific to your account. You'll need to specify the user name that you provided when you logged in to use the service, the Identity Service Id, the region or value that you noted down from the Site drop-down list, and the endpoint.
```
"paas": {
  "user": "acme@example.com",
  "identity_id": "idcs-9bd5....",
  "endpoint": "psm.us.oraclecloud.com",
  "region": "uscom-central-1"
},
```
Generate a list of database resources in your source environment. On the Control-S instance, run the following command. While running this command, you can specify the profile file that you have updated in the previous step.
```
opcmigrate discover
```
The output of this command is a file named resources-default.json, unless you specified a name for the output file while running the command. This file contains details of the database resources in your source environment. It is created in the directory where you run the command.
Log in to the Oracle Cloud Infrastructure Console to gather the OCID of the subnet in which you want to restore the database instances. From the menu, choose Networking and then Virtual Cloud Networks. Click the VCN that you've created for this migration. The Subnets section displays the OCID. Copy the OCID of the subnet as you'll have to provide this information.
Run the following command to generate a plan that includes information about the Oracle Cloud Infrastructure Database instances that you want to create.
Syntax
```
opcmigrate plan create --focus database_cloud --db-subnet-id DB_SUBNET_ID --db-sshkeys DB_SSHKEYS -o plan_file.json
```
Where,
- DB_SUBNET_ID is the OCID of the subnet where you want to restore the database instance. If the plan file contains information about multiple databases, all these databases will be restored in the specified subnet.
- DB-SSHKEYS is the public SSH key that you want to associate with the restored Oracle Cloud Infrastructure Database instances.
- plan_file.json is the path to the output plan file. If you don't provide the -o option, the generated plan is stored in a file named plan-default.json in the current directory. Note that, if a file with the same name already exists in the current directory, it will be overwritten.
Example
```
opcmigrate plan create --focus database_cloud --db-subnet-id ocid1.subnet... --db-sshkeys "ssh-rsa AAA..." -o dbmigrate-plan.json
```
The plan file is created at the location you specify for the -o option. You'll use this plan file to generate a terraform file.
Run the following command to generate a terraform file.
Syntax
```
opcmigrate generate --plan plan_file.json -o terraform_file.tf
```
Where, terraform_file.tf is path to output terraform file. If you don't provide this option, the terraform is available on the console but the terraform file is not created.

Example
```
opcmigrate generate --plan dbmigrate-plan.json -o dbmigrate.tf
```
Open the generated terraform file in any text editor of your choice. Add the following line just before the closing braces (}) for each database system to define the storage size for the database.
```
data_storage_size_in_gb = "storage_size_of_database_in_GB"
```
You must specify only one of the following values as the storage size in GB for the Oracle Cloud Infrastructure Database: 256, 512, 1024, 2048, 4096, 6144, 8192, 10240, 12288, 14336, 16384, 18432, 20480, 22528, 24576, 26624, 28672, 30720, 32768, 34816, 36864, 38912, or 40960. The storage size that you define must be larger than the size of the standalone backup from which you want to restore.

A snippet of a sample terraform file is provided below for your reference to help you identify the place where you have to define the storage size of the database.
```
# database_name
resource "oci_database_db_system" "database_name_xxxxxx" {
....
# Add the storage size for the database system here.
}
```
The terraform file defines three variables for every standalone backup. Note down the names of these variables for every standalone backup that you want to restore. The value for these variables is not assigned in this terraform file. You'll assign values for these variables in the terraform variables file. A snippet of a sample terraform file is provided below for your reference.
```
...
variable "ocic_dbaas_to_oci_database_edition" {
...
}
variable "database_name_xxxxxx_admin_password" {}
variable "database_name_xxxxxx_backup_id" {}
variable "database_name_xxxxxx_backup_tde_password" {}

provider "oci" {
...
}
```
Create or update an existing terraform variables file. Enter the names of the variables that you have noted down from the generated terraform file, and then define values for these variables.
- database_name_xxxxxx_admin_password: Specify a single password for the SYSTEM and SYS users, as well as the backup TDE password on the Oracle Cloud Infrastructure Database instance. Ensure that you conform to the standards set for passwords in Oracle Cloud Infrastructure.
- database_name_xxxxxx_backup_id: Specify the OCID of the standalone backup that was created when you ran the opcmigrate migrate database list command.
- database_name_xxxxxx_backup_tde_password: Specify the password for the SYS admin on the source Oracle Database Classic Cloud Service database that you want to migrate. When the database is created, the password for SYS user and the backup TDE password are the same. If you have not changed these values, then you can provide the SYS admin password.
Apply the terraform configuration to create Oracle Cloud Infrastructure Database instances.
Confirm that your Oracle Cloud Infrastructure Database instances are being created. Open the navigation menu in the Oracle Cloud Infrastructure console. Under Database, click Bare Metal, VM, and Exadata, and then click DB Systems. You'll see that the database is in the provisioning state.

Troubleshooting

Here are a few tips for dealing with errors that might occur while migrating database instances using Oracle Cloud Infrastructure Classic Discovery and Translation Tool.

To view the status of a migration job, run the following command:

opcmigrate migrate database list

In the response, look at the value returned for the Status field.

Code	Status	Description	Troubleshoot
000	Success	The database backup to Oracle Cloud Infrastructure has been completed successfully. This backup is available as a standalone backup in Oracle Cloud Infrastructure tenancy.	Not applicable
001	Verifying	Checks are in progress to ensure that enough space is available in source database system to temporarily store the backup.	Not applicable
002	Backing Up	The database backup is in progress.	Not applicable
003	Uploading	The database backup is in progress.	Not applicable
004	Restoring	The database backup is in progress.	Not applicable
100	Decoding failed for credential	The private key that you have specified in the profile file is not base64 encoded.	You must specify the base-64 encoded private SSH key in the `"database"` section of the profile file located at `~/.opc/profiles/default`.
101	Connect failure for source host	Connection could not be established to the source database host.	Ensure that you can SSH to the source database host using the credentials that you have provided in the profile file.
102	Authentication failure for source host root user	SSH connectivity to the source database host could not be established using the specified credentials.	Ensure that you have provided the correct credentials in the `"database"` section of the profile file located at `~/.opc/profiles/default`.
103	User sudo failed	Password-less access to the source database host as a root user was not possible with the currently specified user.	Ensure that the `oracle` user has password-less access to the source database host as a root user.
104	Oracle user invalid	The provided `oracle` user or the specified user does not exist.	Ensure that you provide the correct `oracle` user and that the user has the required permissions. Also ensure that the credentials are in the required format.
105	User sudo to oracle user failed	The `oracle` user does not have root access	Ensure that the `oracle` user has root permission
106	Oracle user insufficient privilege	Provided `oracle` user does not have sufficient privileges.	Ensure that the `oracle` user you specify has the required privileges.
107	Oracle user not SYSDBA	The `oracle` user or the specified user does not have the SYSDBA role.	Ensure that the `oracle` user you specify has the required roles assigned.
108	Oracle script installation failed	The required CLI script could not be installed from Oracle github.	Ensure that an outbound connection can be established from the source database host. If required, set up the security rules to permit outbound connection.
109	Oracle Cloud Infrastructure configuration file cannot be read	The Oracle Cloud Infrastructure configuration file either does not exist or it can't be read.	Ensure that the Oracle Cloud Infrastructure configuration file exists and it can be read. The expected permission on the file is 400.
110	Oracle Cloud Infrastructure configuration file parsing error	Oracle Cloud Infrastructure configuration file could not be parsed.	Ensure that the configuration file is a valid JSON file and it has the correct structure.
111	Oracle Cloud Infrastructure configuration file missing key_file key	The Oracle Cloud Infrastructure configuration file does not have the `key_file` value or the content of `key_file` is empty.	Ensure that you provide `key_file` value in Oracle Cloud Infrastructure configuration file and the file has valid content.
112	Oracle Cloud Infrastructure script failed to create backup	Failed to create backup in Oracle Cloud Infrastructure. The script could not create the database back up.	View the log file located at `/var/log/dbmigration.log` to identify the errors that occurred, and then identify the corrective measures.
113	Oracle Cloud Infrastructure configuration file missing availability domain	Oracle Cloud Infrastructure configuration file missing availability domain.	Ensure that you specify the availability domain in the Oracle Cloud Infrastructure configuration file.
114	Oracle Cloud Infrastructure configuration key file does not exist or cannot be read	Oracle Cloud Infrastructure key file does not exist or cannot be parsed.	Ensure that you provide a valid key file with the proper access permissions.
115	Invalid source SID	Source SID is invalid.	Ensure that the SID of the source host is valid.
150	Size verification failure	There is not enough space in the source database system to temporarily store the database backup file.	Ensure that the required space is available in the source database system.
151	Backup failure	Unknown error	View the log file located at `/var/log/dbmigration.log` to identify the errors that occurred, and then identify the corrective measures.
500	Internal server error	Internal error	View the log file located at `/var/log/dbmigration.log` to identify the errors that occurred, and then identify the corrective measures.