Zero Downtime Migration – Preparations

In the following posts I will demo how to migrate a database into OCI. To make the demo as simple as possible my source database will already be running in OCI and my target will be a VM DB System.

Prepare source environment

I will create a VM DB System that can act as my source database. I will call it srchost and the database is called CDB1. Using OCI CLI it can be done like this:

oci db system launch \
  --compartment-id "..." \
  --availability-domain "..." \
  --subnet-id "..." \
  --shape "VM.Standard2.4" \
  --cpu-core-count 4 \
  --database-edition "ENTERPRISE_EDITION" \
  --admin-password "..." \
  --ssh-authorized-keys-file "/path/to/key-file.pub" \
  --license-model "BRING_YOUR_OWN_LICENSE" \
  --db-name "CDB1" \
  --pdb-name "SALES" \
  --storage-management "ASM" \
  --node-count 1 \
  --initial-data-storage-size-in-gb 2048 \
  --display-name "SRCHOST" \
  --hostname "SRCHOST" \
  --db-version "19.0.0.0"

Once completed, I can log on as opc and switch to root and copy the authorized_keys file to allow SSH connection as oracle:

[opc@srchost]$ sudo su -
[root@srchost]$ rm -f /home/oracle/.ssh/authorized_keys
[root@srchost]$ cp /home/opc/.ssh/authorized_keys /home/oracle/.ssh/authorized_keys
[root@srchost]$ chown -R oracle:oinstall /home/oracle/.ssh

Now, I will ensure that the database is in ARCHIVELOG mode; it is a requirement for using Data Guard:

SELECT log_mode FROM v$database;

Since my database is running 12.2 or higher I have to ensure that there is a TDE Keystore. This applies even if the database is not encrypted. You don’t need a license to create a TDE Keystore – not until you start encrypting stuff. The keystore must be OPEN and the type is either AUTOLOGIN, LOCAL_AUTOLOGIN or PASSWORD. In a CDB, this applies to CDB$ROOT and all PDBs:

SELECT con_id, status, wallet_type FROM v$encryption_wallet;

If status is OPEN_NO_MASTER_KEY it means that no TDE master encryption key has been created and you will need to create one. You can also find instructions on how to create a TDE keystore in the ZDM documentation.

Prepare target environment

In my demo I will be targeting a VM DB System which I will call tgthost. For the migration to work, I have to create a placeholder database on the target host. During migration the placeholder database is overwritten by the source database. On a VM DB System this is easy; I just use the database that gets created automatically as placeholder.

DB_NAME must be the same in the two databases, and hence in my demo I must set db-name in the OCI CLI command below to CDB1 (the name of my source database). Contrary, DB_UNIQUE_NAME must be different but this will likely not be a problem because OCI automatically generates a semi-unique DB_UNIQUE_NAME (if you are a strong believer in Murphy’s Law you should double-check it).

The target database patch level can be the same or higher than the source. If higher, you just have to manually execute datapatch after the switch-over (I will put this information into a later blog post). By setting db-version to 19.0.0.0 I automatically get the latest available Release Update.

Also, the SYS password has to match in the two databases, so be sure to set admin-password to your source database SYS password. Finally, you should set storage-management to ASM. This will cause OCI to install Grid Infrastructure as well and set up a SCAN listener.

In my demo I end up with this OCI CLI command:

oci db system launch \
  --compartment-id  "..." \
  --availability-domain "..." \
  --subnet-id "..." \
  --shape "VM.Standard2.4" \
  --cpu-core-count 4 \
  --database-edition "ENTERPRISE_EDITION" \
  --admin-password "..." \
  --ssh-authorized-keys-file /path/to/key-file.pub \
  --license-model "BRING_YOUR_OWN_LICENSE" \
  --db-name "CDB1" \
  --pdb-name "SALES" \
  --storage-management "ASM" \
  --node-count 1 \
  --initial-data-storage-size-in-gb 2048 \
  --display-name "tgthost" \
  --hostname "tgthost" \
  --db-version "19.0.0.0"

As opc, switch to root and enable oracle to log on via SSH:

[opc@tgthost]$ sudo su -
[root@tgthost]$ rm -f /home/oracle/.ssh/authorized_keys
[root@tgthost]$ cp /home/opc/.ssh/authorized_keys /home/oracle/.ssh/authorized_keys
[root@tgthost]$ chown -R oracle:oinstall /home/oracle/.ssh

The requirements to the TDE Keystore applies to the target environment as well, but since I am using an OCI DB System that will be setup up correctly when the system is created.

Configure Connectivity

I need to ensure that the source host can resolve the network name of the target host. It is important to add two entries – one with the host name and one with the SCAN name (they should both point to the target host):

[root@srchost]$ echo -e "[ip address] tgthost" >> /etc/hosts
[root@srchost]$ echo -e "[ip address] tgthost-scan" >> /etc/hosts

Also, I need to ensure that I can connect to the target database from the source host over SQL*Net. Just use the default service of the target CDB, and you can get it using lsnrctl:

[oracle@tgthost]$ lsnrctl status
[oracle@srchost]$ sqlplus system@tgthost-scan/[target-cdb-service-name]

Now, I do it the other way around. Ensure that the target host can resolve the network name of the source host:

[root@tgthost]$ echo -e "[ip address] srchost" >> /etc/hosts
[root@tgthost]$ echo -e "[ip address] srchost-scan" >> /etc/hosts

Ensure that I can connect to the source database from the target host:

[oracle@srchost]$ lsnrctl status
[oracle@tgthost]$ sqlplus system@srchost-scan/[source-cdb-service-name]

Create Sample Data

I will connect to the source database:

[oracle@srchost]$ export ORACLE_SID=CDB1
[oracle@srchost]$ export ORACLE_PDB_SID=SALES
[oracle@srchost]$ sqlplus / as sysdba

Note, that ORACLE_PDB_SID works from 18.5.0 and beyond. For lower versions you can just use ALTER SESSION SET CONTAINER=SALES instead. And create some sample data, so I can verify the migration actually works:

CREATE USER zdmtest IDENTIFIED BY [your-password];
GRANT CREATE TABLE, CONNECT TO zdmtest;
GRANT SELECT ON v_$instance TO zdmtest;
GRANT SELECT ON v_$database TO zdmtest;
ALTER USER zdmtest DEFAULT TABLESPACE users;
ALTER USER zdmtest QUOTA UNLIMITED ON users;

CREATE TABLE zdmtest.items (
   id      NUMBER,
   item    VARCHAR2(20),
   price   NUMBER
);
INSERT INTO zdmtest.items VALUES (1, 'Apple', 2);
INSERT INTO zdmtest.items VALUES (2, 'Banana', 1);
INSERT INTO zdmtest.items VALUES (3, 'Orange', 4);
COMMIT;

That’s It

My source database is now ready to be migrated to OCI. It will be migrated to a new VM DB System that I just created. In the next blog post I will install and configure ZDM. Stay tuned!

Other Blog Posts In This Series

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s