Introduction
During a recent SAP HANA system database refresh activity, we encountered a critical issue while restoring the Production (PRD) database backup into the Quality (QAS) system. The refresh activity is typically straightforward; however, when database encryption is enabled, additional key management steps are required to ensure a successful recovery.
This blog explains the issue faced, its root cause, and the step-by-step resolution that helped us complete the recovery successfully.
Issue Faced
While recovering the PRD database backup in QAS, the recovery process failed with the following error
Root Cause
Our PRD backup was encrypted, as we are running SAP HANA 2.0 SPS07, where backup encryption is enabled by default. However, the required encryption root key was not available in the QAS system. Without this root key, SAP HANA is unable to decrypt or restore the encrypted backup.
Solution
To resolve this issue, we exported the encryption root keys from PRD and imported them into QAS.
Step 1: Set Backup Password for Encryption Root Keys (PRD)
Execute the following command in HANA Studio (PRD system):
ALTER DATABASE <PRD_SID> SET ENCRYPTION ROOT KEYS BACKUP PASSWORD ‘MyStrongPassword123!’;
This password protects the exported root key backup.
Step 2: Back Up Encryption Root Keys (PRD)
BACKUP ENCRYPTION ROOT KEYS FOR <PRD SID> USING FILE (‘backup001’);
The encrypted root key file is generated in the following location:
/usr/sap/<PRD_SID>/HDB<Instance_Number>/backup/sec/DB_<PRD_SID>/backup001_rootkeys
Step 3: Copy Root Key to QAS
Securely copy the generated _rootkeys file from the PRD server to the corresponding directory on the QAS system.
Step 4: Recover Encryption Root Keys in QAS
Run the following command from the QAS OS level :
hdbnsutil -recoverRootKeys /usr/sap/<QAS SID>/HDB<Instance Number>/backup/sec/DB_<PRD_SID>/backup001_rootkeys –dbid=4 –password=MyStrongPassword123! –type=ALL
Permissions :
For the System Database, execution of this statement requires any one of the following system privileges:
BACKUP ADMIN
BACKUP OPERATOR
For a Tenant Database, execution of this statement requires any one of the following system privileges:
DATABASE BACKUP ADMIN
DATABASE BACKUP OPERATOR
Hint :
How to Find the DBID ?
Run the following query in SAP HANA Studio:
SELECT DATABASE_NAME,
CASE WHEN (DBID = ” AND DATABASE_NAME = ‘SYSTEMDB’)
THEN 1
WHEN (DBID = ” AND DATABASE_NAME <> ‘SYSTEMDB’)
THEN 3
ELSE TO_INT(DBID)
END DATABASE_ID
FROM (SELECT DISTINCT DATABASE_NAME,
SUBSTR_AFTER(SUBPATH,’.’) AS DBID
FROM SYS_DATABASES.M_VOLUMES);
Final Step: Retry Database Recovery
After successfully importing the encryption root keys into QAS, the database recovery was retried and completed successfully without any errors.
Key Takeaway :
When performing SAP HANA system refreshes involving encrypted backups, always ensure:
1.Encryption root keys from the source system are backed up
2.The keys are securely transferred and restored in the target system before recovery
3.Please note that from SAP HANA 2.0 SPS 01 version we can create encrypted backup
4.In a newly installed HANA 2.0 SPS07, encryption is enabled by default.
Conclusion :
System refresh activities in SAP HANA environments require careful handling when encryption is enabled. The missing root key error may seem complex initially, but the resolution is straightforward once the root key export and import process is properly executed.
IntroductionDuring a recent SAP HANA system database refresh activity, we encountered a critical issue while restoring the Production (PRD) database backup into the Quality (QAS) system. The refresh activity is typically straightforward; however, when database encryption is enabled, additional key management steps are required to ensure a successful recovery.This blog explains the issue faced, its root cause, and the step-by-step resolution that helped us complete the recovery successfully.Issue FacedWhile recovering the PRD database backup in QAS, the recovery process failed with the following error Root CauseOur PRD backup was encrypted, as we are running SAP HANA 2.0 SPS07, where backup encryption is enabled by default. However, the required encryption root key was not available in the QAS system. Without this root key, SAP HANA is unable to decrypt or restore the encrypted backup.SolutionTo resolve this issue, we exported the encryption root keys from PRD and imported them into QAS.Step 1: Set Backup Password for Encryption Root Keys (PRD)Execute the following command in HANA Studio (PRD system):ALTER DATABASE <PRD_SID> SET ENCRYPTION ROOT KEYS BACKUP PASSWORD ‘MyStrongPassword123!’;This password protects the exported root key backup.Step 2: Back Up Encryption Root Keys (PRD)BACKUP ENCRYPTION ROOT KEYS FOR <PRD SID> USING FILE (‘backup001’);The encrypted root key file is generated in the following location:/usr/sap/<PRD_SID>/HDB<Instance_Number>/backup/sec/DB_<PRD_SID>/backup001_rootkeysStep 3: Copy Root Key to QASSecurely copy the generated _rootkeys file from the PRD server to the corresponding directory on the QAS system.Step 4: Recover Encryption Root Keys in QASRun the following command from the QAS OS level :hdbnsutil -recoverRootKeys /usr/sap/<QAS SID>/HDB<Instance Number>/backup/sec/DB_<PRD_SID>/backup001_rootkeys –dbid=4 –password=MyStrongPassword123! –type=ALLPermissions : For the System Database, execution of this statement requires any one of the following system privileges:BACKUP ADMINBACKUP OPERATORFor a Tenant Database, execution of this statement requires any one of the following system privileges:DATABASE BACKUP ADMINDATABASE BACKUP OPERATORHint : How to Find the DBID ?Run the following query in SAP HANA Studio:SELECT DATABASE_NAME,
CASE WHEN (DBID = ” AND DATABASE_NAME = ‘SYSTEMDB’)
THEN 1
WHEN (DBID = ” AND DATABASE_NAME <> ‘SYSTEMDB’)
THEN 3
ELSE TO_INT(DBID)
END DATABASE_ID
FROM (SELECT DISTINCT DATABASE_NAME,
SUBSTR_AFTER(SUBPATH,’.’) AS DBID
FROM SYS_DATABASES.M_VOLUMES);Final Step: Retry Database RecoveryAfter successfully importing the encryption root keys into QAS, the database recovery was retried and completed successfully without any errors. Key Takeaway : When performing SAP HANA system refreshes involving encrypted backups, always ensure:1.Encryption root keys from the source system are backed up2.The keys are securely transferred and restored in the target system before recovery3.Please note that from SAP HANA 2.0 SPS 01 version we can create encrypted backup4.In a newly installed HANA 2.0 SPS07, encryption is enabled by default.Conclusion : System refresh activities in SAP HANA environments require careful handling when encryption is enabled. The missing root key error may seem complex initially, but the resolution is straightforward once the root key export and import process is properly executed. Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
