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 “”/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= , 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’;

1. 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