Can I use online backup to encrypt an existing database ?
The answer is YES.
Converting a database to be encrypted is a kind of big change.
Therefore, I still strongly suggest to take an offline backup before the change if business permits the outage time to do that.
On the DB2 knowledge center page "Encrypting an existing database", it shows the example steps using an offline backup.
But that does not necessarily mean we can only use the offline backup.
On this blog, I will show some sample steps using online backup to be prove that.
( Indeed, there were questions about this matter. )
NOTE :
1. Do not refer all commands to implement on your system.
Some steps are just for showing my intention about how I will do the test.
And the detail command could be different depending on the environment.
2. I borrowed partial commands from other blog page "Hands-on example for a new encrypted database".
3. I used local key manager, not by centralized key manager.
But that does not matter for testing this topic.
-Created a non encrypted database for the test and make it archive mode to run online backup.
v111_01@jshadr1:~> db2 create db encdb
DB20000I The CREATE DATABASE command completed successfully.
v111_01@jshadr1:~> db2 update db cfg for encdb using LOGARCHMETH1 DISK:/home/v111_01/archive
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
v111_01@jshadr1:~> db2 backup db encdb to /dev/null
- Run a transaction to generate archived logs.
v111_01@jshadr1:~/history> db2 -tvf 02_import.db2
import from histdata1.ixf of ixf commitcount 10000 insert into history
...
- Run online backup
v111_01@jshadr1:~/backup> db2 backup db encdb online
Backup successful. The timestamp for this backup image is : 20180207013745
- Run other transaction to generate archive logs after the last backup
v111_01@jshadr1:~/history> db2 -tvf 02_import.db2
import from histdata1.ixf of ixf commitcount 10000 insert into history
...
v111_01@jshadr1:~/archive/v111_01/ENCDB/NODE0000/LOGSTREAM0000/C0000000> ls -tlr
total 391224
-rw-r----- 1 v111_01 db2igrp 1859584 2018-02-07 01:23 S0000000.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:23 S0000001.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:23 S0000002.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:23 S0000003.LOG
...
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:41 S0000096.LOG
-rw-r----- 1 v111_01 db2igrp 4202496 2018-02-07 01:41 S0000097.LOG
-rw-r----- 1 v111_01 db2igrp 1667072 2018-02-07 02:02 S0000098.LOG
-Creating keystore and update the instance configuration.
v111_01@jshadr1:~> mkdir test_encrypt
v111_01@jshadr1:~> cd test_encrypt
v111_01@jshadr1:~/test_encrypt> /home/v111_01/sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db ccardskeystore.p12 -pw Str0ngPassw0rd -strong -type pkcs12 -stash
v111_01@jshadr1:~/test_encrypt> ls -tlr
total 4
-rw------- 1 v111_01 db2igrp 129 2018-02-07 01:45 ccardskeystore.sth
-rw------- 1 v111_01 db2igrp 0 2018-02-07 01:45 ccardskeystore.p12
v111_01@jshadr1:~/test_encrypt> db2 "update dbm cfg using keystore_type pkcs12 keystore_location /home/v111_01/test_encrypt/ccardskeystore.p12"
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
SQL1362W One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
v111_01@jshadr1:~/test_encrypt> db2 get dbm cfg | grep -i keystore
Keystore type (KEYSTORE_TYPE) = PKCS12
Keystore location (KEYSTORE_LOCATION) = /home/v111_01/test_encrypt/ccardskeystore.p12
v111_01@jshadr1:~/test_encrypt> db2 terminate
DB20000I The TERMINATE command completed successfully.
v111_01@jshadr1:~/test_encrypt> db2stop ; db2start
02/07/2018 01:46:47 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
02/07/2018 01:46:49 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
- Checking the current configuration of non encrypted
v111_01@jshadr1:~/test_encrypt> db2 get db cfg for encdb | grep -i encr
Encryption Library for Backup (ENCRLIB) =
Encryption Options for Backup (ENCROPTS) =
Encrypted database = NO
v111_01@jshadr1:~/test_encrypt> db2 activate db encdb
DB20000I The ACTIVATE DATABASE command completed successfully.
v111_01@jshadr1:~/test_encrypt> db2pd -db encdb -encrypt
Database Member 0 -- Database ENCDB -- Active -- Up 0 days 00:00:48 -- Date 2018-02-07-01.48.35.879477
Encryption Info:
Object Name: ENCDB
Object Type: DATABASE
Encyrption Key Info: Database encryption not enabled. <===========
KeyStore Info:
KeyStore Type: PKCS12
KeyStore Location: /home/v111_01/test_encrypt/ccardskeystore.p12
KeyStore Host Name: jshadr1
KeyStore IP Address: 192.168.153.38
KeyStore IP Address Type: IPV4
- Drop the database
v111_01@jshadr1:~/test_encrypt> db2 deactivate db encdb
DB20000I The DEACTIVATE DATABASE command completed successfully.
v111_01@jshadr1:~/test_encrypt> db2 drop db encdb
DB20000I The DROP DATABASE command completed successfully.
- Restore with encryption and rollforward the database.
v111_01@jshadr1:~/backup> db2 "restore db encdb from /home/v111_01/backup taken at 20180207013745 encrypt"
DB20000I The RESTORE DATABASE command completed successfully.
v111_01@jshadr1:~/backup> db2 "rollforward db encdb to end of logs"
Rollforward Status
Input database alias = encdb
Number of members have returned status = 1
Member ID = 0
Rollforward status = DB working
Next log file to be read = S0000099.LOG
Log files processed = S0000049.LOG - S0000097.LOG
Last committed transaction = 2018-02-06-14.43.50.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
v111_01@jshadr1:~/backup> db2 "rollforward db encdb complete"
Rollforward Status
Input database alias = encdb
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000049.LOG - S0000097.LOG
Last committed transaction = 2018-02-06-14.43.50.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
- Check the encryption configuration.
v111_01@jshadr1:~/backup> db2 activate db encdb
DB20000I The ACTIVATE DATABASE command completed successfully.
v111_01@jshadr1:~/backup> db2 connect to encdb
Database Connection Information
Database server = DB2/LINUXX8664 11.1.1.1
SQL authorization ID = V111_01
Local database alias = ENCDB
v111_01@jshadr1:~/backup> db2pd -db encdb -encrypt
Database Member 0 -- Database ENCDB -- Active -- Up 0 days 00:00:20 -- Date 2018-02-07-02.03.25.055336
Encryption Info:
Object Name: ENCDB
Object Type: DATABASE
Encyrption Key Info:
Encryption Algorithm: AES
Encryption Algorithm Mode: CBC
Encryption Key Length: 256
Master Key Label: DB2_SYSGEN_v111_01_ENCDB_2018-02-07-01.59.43_14178748
Master Key Rotation Timestamp: 2018-02-07-01.59.44.000000
Master Key Rotation Appl ID: *LOCAL.DB2.180206145837
Master Key Rotation Auth ID: V111_01
Previous Master Key Label: DB2_SYSGEN_v111_01_ENCDB_2018-02-07-01.59.43_14178748
KeyStore Info:
KeyStore Type: PKCS12
KeyStore Location: /home/v111_01/test_encrypt/ccardskeystore.p12
KeyStore Host Name: jshadr1
KeyStore IP Address: 192.168.153.38
KeyStore IP Address Type: IPV4
v111_01@jshadr1:~/backup> db2 get db cfg for encdb | grep -i encr
Encryption Library for Backup (ENCRLIB) = libdb2encr.so
Encryption Options for Backup (ENCROPTS) = CIPHER=AES:MODE=CBC:KEY LENGTH=256
Encrypted database = YES
We see the database is encrypted successfully now.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]