Cross Tenant Database Recovery
Scenario:
Source Tenant DB = TS1 (Multi Container Installation on Host A)
Target Tenant DB = TS3 (Converted to Multi Container DB from Single DB on Host B)
Encountered below error for Multitenant Database Recovery due to the volume_ID is different from source and target DB.
2015-01-22T09:44:15+08:00 P002495 14b0f4fae01 INFO RECOVERY RECOVER DATA started
2015-01-22T09:44:15+08:00 P002495 14b0f4fae01 INFO RECOVERY command: RECOVER DATA FOR TS
3 USING FILE (‘/mnt/cbj2hanasx01_TST/data/DB_TS3/COMPLETE_DATA_BACKUP’) CLEAR LOG
2015-01-22T09:44:15+08:00 P002495 14b0f4fae01 INFO RECOVERY state of service: indexserve
r, hostb:30540, volume: 0, RecoveryExecuteCatalogRecoveryInProgress
2015-01-22T09:44:15+08:00 P002495 14b0f4fae01 INFO RECOVERY state of service: indexserve
r, hostb:30540, volume: 0, RecoveryError
2015-01-22T09:44:15+08:00 P002495 14b0f4fae01 INFO RECOVERY state of service: indexserve
r, hostb:30540, volume: 3, RecoveryExecuteTopologyRecoveryInProgress
2015-01-22T09:44:16+08:00 P002495 14b0f4fae01 INFO RECOVERY state of service: indexserve
r, hostb:30540, volume: 3, RecoveryExecuteTopologyRecoveryFinished
2015-01-22T09:44:16+08:00 P002495 14b0f4fae01 INFO RECOVERY state of service: indexserve
r, hostb:30540, volume: 3, RecoveryPrepared
2015-01-22T09:44:16+08:00 P002495 14b0f4fae01 INFO RECOVERY start of progress monitoring
, volumes: 1, bytes: 0
2015-01-22T09:44:16+08:00 P002495 14b0f4fae01 INFO RECOVERY state of service: indexserve
r, hostb:30540, volume: 3, RecoveryExecuteDataRecoveryInProgress
2015-01-22T09:44:16+08:00 P002495 14b0f4fae01 ERROR RECOVERY RECOVER DATA finished with e
rror: [448] recovery could not be completed, volume 3, reached log position 0, [2000004] Cannot o
pen file “”<root>/COMPLETE_DATA_BACKUP_databackup_3_1” ((mode= R, access= rw-r—–, flags= DIREC
T|MUST_EXIST|MULTI_WRITERS|UNALIGNED_SIZE), factory= (root= “/mnt/hostb/data/DB_TS3/”
(access= rw-r—–, flags= <none>, usage= DATA_BACKUP, fs= nfs, config= (async_write_submit_activ
e=auto,async_write_submit_blocks=new,async_read_submit=off,num_submit_queues=1,num_completion_que
ues=1,size_kernel_io_queue=512,max_parallel_io_requests=64))”, rc=2: No such file or directory
Solution:
By referring to 2101737 – Recovery of a Multitenant Database Container fails, we need to map the volume ID from source DB to target DB to ensure a successful recovery.
Important: Before apply below steps; please ensure a complete SYSTEMDB backup and all TENANT DB Backup (if any). This is to safeguard your HDB in case system topology screwed up.
1) Source TS1 Tenant Indexserver Volume ID which is 2 (look at the data are with hdb00002)
On Target DB: Check the view on SYS_DATABASES.M_VOLUMES to take note on the VOLUME_ID and SUBPATH
2) On Souce DB: To confirm the volume ID by executing “hdbbackupdiag” on the data backup.
hdbbackupdiag -v -d /mnt/hosta/data/DB_TS1 -b COMPLETE_DATA_BACKUP | grep “\ServiceName\|VolumeId”
ServiceName: indexserver
VolumeId: 2
ServiceName: indexserver
VolumeId: 2
3) Determine the volumes and services of the target database. From below query, we know that the VOLUME_ID for indexserver on target DB is 3.
SELECT S.DATABASE_NAME, S.HOST, S.SERVICE_NAME, S.PORT, V.VOLUME_ID
FROM SYS_DATABASES.M_SERVICES S, SYS_DATABASES.M_VOLUMES V
WHERE S.HOST = V.HOST AND S.PORT = V.PORT AND S.DATABASE_NAME = V.DATABASE_NAME AND S.DATABASE_NAME = ‘TS3’;
- 4. Determine the configuration values for every volume of the target database by running the query below.
SELECT
CONCAT(SUBSTRING(PATH,
1,
LOCATE(PATH,
‘/’,
0,
2)),
SUBSTR_BEFORE(NAME,
‘@’)) PATH,
SUBSTR_BEFORE(NAME,
‘@’) DB_VOLUME_ID,
SUBSTR_AFTER(NAME,
‘@’) NAME,
VALUE
FROM M_TOPOLOGY_TREE
WHERE PATH = ‘/volumes/*+|@’
AND NAME LIKE CONCAT((SELECT
SUBSTR_BEFORE(name,
‘@’) DB_ID
FROM M_TOPOLOGY_TREE
WHERE PATH=‘/databases/*+|@’
and NAME like ‘%name’
and value = ‘TS3’),
‘:%’);
Output:
*Run the query again for each available tenant DB to avoid any data volume being overwritten for the same volume_id used by other services. If this happens, append or change the subpath, eg: hdb00002.0000X. (X to higher integer)
5) bring down your target tenant DB is not already down;
ALTER SYSTEM STOP DATABASE TS3
6) Change the volume ids of the services of the target database to match the volume_id of source DB. In our case, the source DB volume_id is 2.
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/host/hostb/indexserver/30540′, ‘volume’)= ‘2’
7) Delete configuration values for every volume from target database
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) UNSET (‘/volumes’, ‘4:3′);
Once the query finished, the result would be empty if you run again the query in (4)
8) Insert new configuration values for every volume into the target database.
- § Here, you will change the source volume_id earlier ‘/volumes/4:3’ to target source volume_id ‘/volumes/4:2’
- § Pay attention to ‘path’, where you just need to change hdb0000X to the new volume_ID. In our case, which is 2. Please use ‘mnt00001/hdb00002.00004′; instead of ‘mnt00001/hdb00002:00004’as instructed in note 2101737.
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘active’)= ‘yes’;
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘catalog’)= ‘yes’;
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘database’)= ‘3’;
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘location’)= ‘hostb:30540’;
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘path’)= ‘mnt00001/hdb00002.00004′;
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘servicetype’)= ‘indexserver’;
ALTER SYSTEM ALTER CONFIGURATION (‘topology.ini’, ‘system’) SET (‘/volumes/4:2’, ‘tenant’)= ‘-‘;
9) Proceed to recovery
10) Once recovery completed successfully, target tenant will be up and running with the changed volume_id.
Hope it helps,
Cheers,
Nicholas Chagnb
Thanks for trying it out! good to know that this works